数据库基础-MySql8.0(第二篇)--DML和DQL_mysql8.0 dml 设置变量

MySql基础篇

数据操纵语言DML

/*
插入数据
 方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
 方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
 方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);
 方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)
*/

INSERT INTO student(NAME,sex,birthday,grade,score,phone,address,reg_time)
	    VALUE('tom','男','2000-1-2',1,98,'15929101866','xian',NOW())

INSERT INTO student SET NAME='张三',sex='男',birthday='2020-1-3',grade=2,score=97,phone='15959421585',address='陕西',reg_time=NOW()

INSERT INTO student(NAME,sex,birthday,grade,score,phone,address,reg_time)
	    VALUES('李四','男','2000-1-2',3,98,'15929101899','咸阳',NOW()),
	          ('王五','女','2000-1-4',3,98,'15929101888','咸阳',NOW()),
	          ('李六','男','2000-1-5',3,98,'15929101877','咸阳',NOW())

-- 表名后不描述,表示向表中所有的列插入数据  值得数量应与列的数量相匹配	          
INSERT INTO stu(num,NAME,sex,birthday,grade,score,phone,address,reg_time)
	    SELECT * FROM student
	    
-- 修改表数据
   UPDATE student SET NAME = '李四',sex='男'
-- 添加条件
   UPDATE student SET NAME = '赵六',sex='女' WHERE num =2233
   
-- 删除语句
   DELETE FROM student WHERE num = 2237
   
-- 清空表数据
   TRUNCATE TABLE student
 
ALTER TABLE student MODIFY num INT AUTO_INCREMENT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

DQL数据查询语句

-- select 查询结果 from 表 where 条件 [分组][分组条件] 排序
/*
  关键字查询
  函数查询
       单行函数
       字符
       数字
       日期
       分组函数
   条件查询
*/

-- 查询结果是常量
   SELECT 100
   
-- 表达式
   SELECT 10+10
   SELECT grade+1 FROM student
   
-- 函数
   SELECT VERSION() #查询版本
   SELECT NOW() #查询时间
   
-- 查询整张表数据
   SELECT * FROM student
   
-- 查询特定列
   SELECT num,NAME,sex FROM student
   
-- 去除重复数据(多行数据所有列相同)只针对查询出来的结果去重
   SELECT DISTINCT sex FROM student
   
-- 算术运算符+(简单的加法运算,没有字符拼接功能) - * /
   SELECT grade+score FROM student
-- 所有人班级+1
   SELECT grade+1 FROM student
 
-- 单行函数(对每行进行处理)
-- LENGTH(列名) 返回字符字节数量长度
   SELECT LENGTH(NAME) FROM student 

-- 字符函数
-- CHAR_LENGTH(NAME) 以字符为单位
   SELECT CHAR_LENGTH(NAME) FROM student 

-- 拼接 CONCAT (tr1,str2)
   SELECT CONCAT ('aa','bb','cc')
   SELECT CONCAT (NAME,'是',sex) FROM student
   
-- 转大写
   SELECT UPPER(NAME) FROM student
   SELECT UPPER('abc') 
   
-- 转小写
   SELECT UPPER('ABC')  
   
-- 字符串截取  SUBSTRING(列名,开始位置,截取长度)
   SELECT SUBSTRING(NAME,1,1) FROM student
   
-- 指定字符串首次出现位置查找 INSTR(列名,str)
    SELECT INSTR(NAME,'o') FROM student
    
-- 去前后空格 TRIM(列名)
   SELECT TRIM(NAME) FROM student
-- 去前后指定字符 TRIM(str from 列名)
   SELECT TRIM('o' FROM NAME) FROM student
   
-- 填充字符到指定长度 LPAD(列名,补充到指定长度,填充内容)
   SELECT LPAD(NAME,6,'z') FROM student 
   
-- 将列中替换成指定的字符 REPLACE(列名 ,旧字符串,新字符串)
   SELECT REPLACE(NAME , 'a','z') FROM student
   
-- 逻辑处理
-- case when 条件 then 成立的结果 else 不成立的额结果 END from table
SELECT 
	CASE WHEN score>=90 THEN 'A' ELSE 'B' END FROM student
	
	
SELECT 
        NAME,
	(CASE WHEN score>=90 THEN 'A' 
	     WHEN score>=80 AND score<90 THEN 'B'
	     WHEN score>=70 AND score<80 THEN 'C'
	     ELSE 'D' 
	END ) score  # 定义查询出结果的别名
	FROM student	
	
	
-- IFNULL(判断为空的列,如果为空的默认值) 别名
SELECT IFNULL(phone,'未登记手机号') Moblie FROM student
SELECT IFNULL(phone,NAME) Moblie FROM student

-- IF(条件,满足结果,不满足结果)
SELECT IF(score>=90,'优秀','良好')score FROM student


-- 数学函数
-- round四舍五入
SELECT ROUND(5.9)
-- ceil 向上取整
SELECT CEIL(5.4)
-- floor 向下取整
SELECT FLOOR(5.9)
-- 保留指定小数位数,不进位
SELECT TRUNCATE(5.5363,2)
-- mod取余
SELECT MOD(10,3)
-- rand 获取0-1 之间的随机数
SELECT RAND()


-- 日期处理函数
SELECT NOW() #返回当前系统的时间+日期
SELECT CURDATE() #返回当前系统的日期
SELECT CURTIME() #返回当前系统的时间

-- 日期格式化
-- 只获取年份
SELECT YEAR(reg_time ) FROM student

-- 只获取月份
SELECT MONTH(reg_time ) FROM student

-- 将字符串格式转换为数据库中的日期格式
SELECT STR_TO_DATE('1999-5-23','%Y-%m-%d')

-- 日期格式转指定格式的字符串
SELECT DATE_FORMAT(reg_time,'%Y年%m月%d日')m FROM student

SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y年%m月%d日')m FROM student GROUP BY m

-- 计算两个日期的时间差
SELECT DATEDIFF(reg_time,NOW()) FROM student



-- 组函数,聚合函数,统计函数
/*
分类:sum求和,avg平均值,max最大值,min最小值,count计数
1.sum,avg一般用于处理数值型;  max,min,count可以处理任何类型
2.已上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count函数的一般使用count(*)用做统计行数
*/
-- 求总和
SELECT SUM(score) FROM student 
SELECT SUM(DISTINCT score) FROM student 
-- 求平均
SELECT AVG(score) FROM student

-- 最大值
SELECT MAX(score) FROM student

--  最小值
SELECT MIN(score) FROM student

-- 统计数量
SELECT COUNT(*) FROM student
SELECT COUNT(DISTINCT score) FROM student
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160