MySQL一些常用的高级SQL语句（集合）
MySQL一些常用的高级SQL语句
为了方便数据库的使用，本篇文章通过列举高级SQL语句的语法及使用来帮助你更快的学习SQL语句
一、按关键字排序
1.1、使用ORDER BY语句来实现排序
1.2、排序可针对一个或多个字段
1.3、ASC：升序，默认排序方式
1.4、DESC：降序
1.5、ORDER BY的语法结构
语法：
1 select column1，column2，... from 库名 order by column1,column,... asc|desc;
1 语句使用
2 mysql -uroot -p123123
3 mysql> create database score;
4 mysql> use score;
5 mysql> create table test(xuehao int(6) not null primary key,nianling int(3) not null,xingming char(20) not null,chengji int(3) not null);
6 mysql> insert into test values(201001,17,'zhangsan',60),(201002,17,'zhaoliu',95),(201003,18,'lisi',70),(201004,18,'wangwu',80),(201005,19,'tianqi',55);
7 mysql> select * from test;
8
9 升序
10 mysql> select chengji from test order by chengji asc;
11 +---------+
12 | chengji |
13 +---------+
14 | 55 |
15 | 60 |
16 | 70 |
17 | 80 |
18 | 95 |
19 +---------+
20 5 rows in set (0.00 sec)
21 mysql> select chengji from test order by chengji; #默认是ASC
22 +---------+
23 | chengji |
24 +---------+
25 | 55 |
26 | 60 |
27 | 70 |
28 | 80 |
29 | 95 |
30 +---------+
31 5 rows in set (0.00 sec)
32
33 降序
34 mysql> select chengji from test order by chengji desc;
35 +---------+
36 | chengji |
37 +---------+
38 | 95 |
39 | 80 |
40 | 70 |
41 | 60 |
42 | 55 |
43 +---------+
44 5 rows in set (0.00 sec)
1.6、按单字段排序
1 mysql> select xuehao,xingming,chengji from test order by chengji; 2 +--------+----------+---------+ 3 | xuehao | xingming | chengji | 4 +--------+----------+---------+ 5 | 201005 | tianqi | 55 | 6 | 201001 | zhangsan | 60 | 7 | 201003 | lisi | 70 | 8 | 201004 | wangwu | 80 | 9 | 201002 | zhaoliu | 95 | 10 +--------+----------+---------+ 11 5 rows in set (0.00 sec)
1.7、按多字段排序
1 mysql> select xingming,chengji from test order by nianling desc,chengji desc; 2 +----------+---------+ 3 | xingming | chengji | 4 +----------+---------+ 5 | tianqi | 55 | 6 | wangwu | 80 | 7 | lisi | 70 | 8 | zhaoliu | 95 | 9 | zhangsan | 60 | 10 +----------+---------+ 11 5 rows in set (0.00 sec)
二、对结果进行分组
2.1、使用GROUP BY语句来实现分组
2.2、通常结合聚合函数一起使用
2.3、可以按一个或多个字段对结果进行分组
2.4、GROUP BY分组
1 mysql> insert into test values(201006,18,'zhangsan',80),(201007,19,'lisi',70); 2 3 mysql> select * from test; 4 +--------+----------+----------+---------+ 5 | xuehao | nianling | xingming | chengji | 6 +--------+----------+----------+---------+ 7 | 201001 | 17 | zhangsan | 60 | 8 | 201002 | 17 | zhaoliu | 95 | 9 | 201003 | 18 | lisi | 70 | 10 | 201004 | 18 | wangwu | 80 | 11 | 201005 | 19 | tianqi | 55 | 12 | 201006 | 18 | zhangsan | 80 | 13 | 201007 | 19 | lisi | 70 | 14 +--------+----------+----------+---------+ 15 7 rows in set (0.00 sec) 16 17 mysql> select count(xingming),nianling from test group by nianling; 18 +-----------------+----------+ 19 | count(xingming) | nianling | 20 +-----------------+----------+ 21 | 2 | 17 | 22 | 3 | 18 | 23 | 2 | 19 | 24 +-----------------+----------+ 25 3 rows in set (0.00 sec)
2.2、GROUP BY结合ORDER BY
1 mysql> select count(xingming),nianling from test group by nianling order by nianling desc; 2 +-----------------+----------+ 3 | count(xingming) | nianling | 4 +-----------------+----------+ 5 | 2 | 19 | 6 | 3 | 18 | 7 | 2 | 17 | 8 +-----------------+----------+ 9 3 rows in set (0.00 sec)
三、限制结果条目
3.1、只返回select查询结果的第一行或第几行
3.2、使用limit语句限制条目
3.3、limit语法结构
1 语法： 2 select column1，column2，... from 库名 limit 位置偏移量 3 4 mysql> select * from test limit 3; 5 +--------+----------+----------+---------+ 6 | xuehao | nianling | xingming | chengji | 7 +--------+----------+----------+---------+ 8 | 201001 | 17 | zhangsan | 60 | 9 | 201002 | 17 | zhaoliu | 95 | 10 | 201003 | 18 | lisi | 70 | 11 +--------+----------+----------+---------+ 12 3 rows in set (0.00 sec) 13 14 mysql> select * from test limit 3,3; 15 +--------+----------+----------+---------+ 16 | xuehao | nianling | xingming | chengji | 17 +--------+----------+----------+---------+ 18 | 201004 | 18 | wangwu | 80 | 19 | 201005 | 19 | tianqi | 55 | 20 | 201006 | 18 | zhangsan | 80 | 21 +--------+----------+----------+---------+ 22 3 rows in set (0.00 sec) 23 #3，3表示从第三行开始数，显示后三行
四、设置别名
4.1、使用AS语句设置别名，关键字AS可省略
4.2、设置别名时，保证不能与库中其他表或字段名称冲突
4.3、别名的语法结构
1 列的别名： 2 select 列名 as 列名别名 from 库名； 3 表的别名： 4 select 列名 from 库名 as 库名别名； 5 6 mysql> select t.xuehao as 学号,t.nianling as 年龄,t.xingming as 姓名,t.chengji as 成绩 from test as t; 7 +--------+--------+----------+--------+ 8 | 学号 | 年龄 | 姓名 | 成绩 | 9 +--------+--------+----------+--------+ 10 | 201001 | 17 | zhangsan | 60 | 11 | 201002 | 17 | zhaoliu | 95 | 12 | 201003 | 18 | lisi | 70 | 13 | 201004 | 18 | wangwu | 80 | 14 | 201005 | 19 | tianqi | 55 | 15 | 201006 | 18 | zhangsan | 80 | 16 | 201007 | 19 | lisi | 70 | 17 +--------+--------+----------+--------+ 18 7 rows in set (0.00 sec)
4.4、as作为连接语句
1 mysql> create table test1 as select * from test; 2 3 mysql> select * from test1; 4 +--------+----------+----------+---------+ 5 | xuehao | nianling | xingming | chengji | 6 +--------+----------+----------+---------+ 7 | 201001 | 17 | zhangsan | 60 | 8 | 201002 | 17 | zhaoliu | 95 | 9 | 201003 | 18 | lisi | 70 | 10 | 201004 | 18 | wangwu | 80 | 11 | 201005 | 19 | tianqi | 55 | 12 | 201006 | 18 | zhangsan | 80 | 13 | 201007 | 19 | lisi | 70 | 14 +--------+----------+----------+---------+ 15 7 rows in set (0.00 sec)
五、通配符的使用
5.1、用于替换字符串中的部分字符
5.2、通常配合like一起使用，并协同where完成查询
5.3、常用通配符
5.3.1、%：表示0个，1个或多个
5.3.2、_：表示单个字符
1 mysql> select xuehao,xingming from test where xingming like 'z%'; 2 +--------+----------+ 3 | xuehao | xingming | 4 +--------+----------+ 5 | 201001 | zhangsan | 6 | 201002 | zhaoliu | 7 | 201006 | zhangsan | 8 +--------+----------+ 9 3 rows in set (0.00 sec) 10 11 mysql> select xuehao,xingming from test where xingming like 'lis_'; 12 +--------+----------+ 13 | xuehao | xingming | 14 +--------+----------+ 15 | 201003 | lisi | 16 | 201007 | lisi | 17 +--------+----------+ 18 2 rows in set (0.00 sec)
六、子查询
6.1、也称作内查询或者嵌套查询
6.2、先于主查询被执行，其结果将作为外层查询的条件
6.3、在增删改查中都可以使用子查询
6.4、支持多层嵌套
6.5、IN语句是用来判断某个值是否在给定的结果集中
6.6、子查询的用法
1 查询： 2 mysql> select xuehao as 学号,chengji as 成绩 from test where chengji in (select chengji from test where chengji >=60); 3 +--------+--------+ 4 | 学号 | 成绩 | 5 +--------+--------+ 6 | 201001 | 60 | 7 | 201002 | 95 | 8 | 201003 | 70 | 9 | 201007 | 70 | 10 | 201004 | 80 | 11 | 201006 | 80 | 12 +--------+--------+ 13 6 rows in set (0.00 sec) 14 15 查询结合降序使用： 16 mysql> select xuehao as 学号,chengji as 成绩 from test where chengji in (select chengji from test where chengji >=60) order by chengji desc; 17 +--------+--------+ 18 | 学号 | 成绩 | 19 +--------+--------+ 20 | 201002 | 95 | 21 | 201004 | 80 | 22 | 201006 | 80 | 23 | 201007 | 70 | 24 | 201003 | 70 | 25 | 201001 | 60 | 26 +--------+--------+ 27 6 rows in set (0.01 sec) 28 29 插入： 30 mysql> create table test_ as select * from score; 31 mysql> delete from test_; 32 mysql> select * from test_; 33 mysql> insert into test_ select * from test where chengji in (select chengji from test where chengji >=80); 34 mysql> select * from test_; 35 +--------+----------+----------+---------+ 36 | xuehao | nianling | xingming | chengji | 37 +--------+----------+----------+---------+ 38 | 201002 | 17 | zhaoliu | 95 | 39 | 201004 | 18 | wangwu | 80 | 40 | 201006 | 18 | zhangsan | 80 | 41 +--------+----------+----------+---------+ 42 3 rows in set (0.00 sec) 43 44 修改：、 45 mysql> alter table test_ add column num int(3); 46 mysql> desc test_ ; 47 mysql> update test_ set num=101 where chengji in (select chengji from test where chengji >=80); 48 mysql> select * from test_; 49 +--------+----------+----------+---------+------+ 50 | xuehao | nianling | xingming | chengji | num | 51 +--------+----------+----------+---------+------+ 52 | 201002 | 17 | zhaoliu | 95 | 101 | 53 | 201004 | 18 | wangwu | 80 | 101 | 54 | 201006 | 18 | zhangsan | 80 | 101 | 55 +--------+----------+----------+---------+------+ 56 3 rows in set (0.00 sec) 57 58 删除： 59 mysql> delete from test where chengji in(select chengji from (select *from test where chengji >=75)a); 60 mysql> select * from test; 61 +--------+----------+----------+---------+ 62 | xuehao | nianling | xingming | chengji | 63 +--------+----------+----------+---------+ 64 | 201001 | 17 | zhangsan | 60 | 65 | 201003 | 18 | lisi | 70 | 66 | 201005 | 19 | tianqi | 55 | 67 | 201007 | 19 | lisi | 70 | 68 +--------+----------+----------+---------+ 69 4 rows in set (0.00 sec)
七、NULL值
7.1、表示缺失的值
7.2、与数字0或者空白（spaces）是不同的
7.3、使用IS NULL或IS NOT NULL进行判断
7.4、NULL值和空值的区别
7.4.1、空值长度为0，不占空间；NULL值的长度为NULL，占用空间
7.4.2、IS NULL无法判断空值
7.4.3、空值使用“=”或者“<>”来处理
7.4.4、COUNT（）计算时，NULL会忽略，空值会加入计算
1 插入空值： 2 mysql> alter table test_ add column class varchar(16); 3 mysql> select * from test_; 4 mysql> insert into test_ values(201007,19,'lisi',80,102,''); 5 mysql> select * from test_; 6 +--------+----------+----------+---------+------+-------+ 7 | xuehao | nianling | xingming | chengji | num | class | 8 +--------+----------+----------+---------+------+-------+ 9 | 201002 | 17 | zhaoliu | 95 | 101 | NULL | 10 | 201004 | 18 | wangwu | 80 | 101 | NULL | 11 | 201006 | 18 | zhangsan | 80 | 101 | NULL | 12 | 201007 | 19 | lisi | 80 | 102 | | 13 +--------+----------+----------+---------+------+-------+ 14 15 null的用法： 16 mysql> select * from test_ where class is null ; 17 +--------+----------+----------+---------+------+-------+ 18 | xuehao | nianling | xingming | chengji | num | class | 19 +--------+----------+----------+---------+------+-------+ 20 | 201002 | 17 | zhaoliu | 95 | 101 | NULL | 21 | 201004 | 18 | wangwu | 80 | 101 | NULL | 22 | 201006 | 18 | zhangsan | 80 | 101 | NULL | 23 +--------+----------+----------+---------+------+-------+ 24 3 rows in set (0.00 sec) 25 mysql> select * from test_ where class is not null; 26 +--------+----------+----------+---------+------+-------+ 27 | xuehao | nianling | xingming | chengji | num | class | 28 +--------+----------+----------+---------+------+-------+ 29 | 201007 | 19 | lisi | 80 | 102 | | 30 +--------+----------+----------+---------+------+-------+ 31 1 row in set (0.00 sec)
八、正则表达式
8.1、根据指定的匹配模式匹配记录中符合要求的特殊字符
8.2、使用REGEXP关键字指定匹配模式
8.3、常用匹配模式
|
字符 |
说明 |
|
^ |
匹配开始字符 |
|
$ |
匹配结束字符 |
|
. |
匹配任意单个字符 |
|
* |
匹配任意个前面的字符 |
|
+ |
匹配前面字符至少1次 |
|
p1|p2 |
匹配p1或p2 |
|
[...] |
匹配字符集中括号内的任何字符 |
|
[^...] |
匹配不在括号内的任何字符 |
|
{n} |
匹配前面的字符串n次 |
|
{n,m} |
匹配前面的字符串至少n次，至多m次 |
1 以z开头的姓名：(^)
2 mysql> select xuehao,xingming,chengji from test_ where xingming regexp '^z';
3 +--------+----------+---------+
4 | xuehao | xingming | chengji |
5 +--------+----------+---------+
6 | 201002 | zhaoliu | 95 |
7 | 201006 | zhangsan | 80 |
8 +--------+----------+---------+
9 2 rows in set (0.00 sec)
10
11 以n结尾的姓名：($)
12 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'n$';
13 +--------+----------+---------+
14 | xuehao | xingming | chengji |
15 +--------+----------+---------+
16 | 201006 | zhangsan | 80 |
17 +--------+----------+---------+
18 1 row in set (0.00 sec)
19
20 匹配单个字符（.）
21 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'lis.';
22 +--------+----------+---------+
23 | xuehao | xingming | chengji |
24 +--------+----------+---------+
25 | 201007 | lisi | 80 |
26 +--------+----------+---------+
27 1 row in set (0.00 sec)
28
29 匹配前面字符至少1次（+）
30 mysql> insert into test_ values(201008,20,'lio',75,103,''),(201009,20,'lioo',85,104,''),(201009,20,'liooo',55,105,'');
31 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'lioo+';
32 +--------+----------+---------+
33 | xuehao | xingming | chengji |
34 +--------+----------+---------+
35 | 201009 | lioo | 85 |
36 | 201009 | liooo | 55 |
37 +--------+----------+---------+
38 2 rows in set (0.00 sec)
39
40 匹配任意个前面的字符(*)
41 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'lio*';
42 +--------+----------+---------+
43 | xuehao | xingming | chengji |
44 +--------+----------+---------+
45 | 201002 | zhaoliu | 95 |
46 | 201007 | lisi | 80 |
47 | 201008 | lio | 75 |
48 | 201009 | lioo | 85 |
49 | 201009 | liooo | 55 |
50 +--------+----------+---------+
51 5 rows in set (0.00 sec)
52
53 匹配p1或p2(p1|p2)
54 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'l|n';
55 +--------+----------+---------+
56 | xuehao | xingming | chengji |
57 +--------+----------+---------+
58 | 201002 | zhaoliu | 95 |
59 | 201004 | wangwu | 80 |
60 | 201006 | zhangsan | 80 |
61 | 201007 | lisi | 80 |
62 | 201008 | lio | 75 |
63 | 201009 | lioo | 85 |
64 | 201009 | liooo | 55 |
65 +--------+----------+---------+
66 7 rows in set (0.00 sec)
67
68 匹配字符集中括号内的任何字符([...])
69 mysql> select xuehao,xingming,chengji from test_ where xingming regexp '[hz]';
70 +--------+----------+---------+
71 | xuehao | xingming | chengji |
72 +--------+----------+---------+
73 | 201002 | zhaoliu | 95 |
74 | 201006 | zhangsan | 80 |
75 +--------+----------+---------+
76 2 rows in set (0.00 sec)
77
78 匹配前面的字符串n次{n}
79 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'o{2}';
80 +--------+----------+---------+
81 | xuehao | xingming | chengji |
82 +--------+----------+---------+
83 | 201009 | lioo | 85 |
84 | 201009 | liooo | 55 |
85 +--------+----------+---------+
86 2 rows in set (0.00 sec)
87
88 匹配前面的字符串至少n次，至多m次({n,m})
89 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'o{2,3}';
90 +--------+----------+---------+
91 | xuehao | xingming | chengji |
92 +--------+----------+---------+
93 | 201009 | lioo | 85 |
94 | 201009 | liooo | 55 |
95 +--------+----------+---------+
96 2 rows in set (0.00 sec)
九、算术运算符
9.1、MySQL支持的算术运算符
|
字符 |
说明 |
|
+ |
加法 |
|
- |
减法 |
|
* |
乘法 |
|
/ |
除法 |
|
% |
取余数 |
1 mysql> select 5+2 as addition,8-5 as subtraction,6*7 as multiplication,8/2 as division,9%4 as remainder; 2 +----------+-------------+----------------+----------+-----------+ 3 | addition | subtraction | multiplication | division | remainder | 4 +----------+-------------+----------------+----------+-----------+ 5 | 7 | 3 | 42 | 4.0000 | 1 | 6 +----------+-------------+----------------+----------+-----------+ 7 1 row in set (0.00 sec)
9.2、比较运算符
9.2.1、字符串的比较默认不区分大小写，可使用binary来区分
9.2.2、常用比较运算符
|
运算符 |
说明 |
|
= |
等于 |
|
> |
大于 |
|
< |
小于 |
|
>= |
大于或等于 |
|
<= |
小于或等于 |
|
!=或<> |
不等于 |
|
IN |
在集合中 |
|
LIKE |
通配符匹配 |
|
IS NULL |
判断一个值是否为NULL |
|
IS NOT NULL |
判断一个值是否不为NULL |
|
BETWEEN AND |
两者之间 |
|
GREATEST |
两个或多个参数时返回最大值 |
|
LEAST |
两个或多个参数时返回最小值 |
1 mysql> select 2=4,2='2','e'='e',(4+4)=(5+3),'n'=NULL; 2 +-----+-------+---------+-------------+----------+ 3 | 2=4 | 2='2' | 'e'='e' | (4+4)=(5+3) | 'n'=NULL | 4 +-----+-------+---------+-------------+----------+ 5 | 0 | 1 | 1 | 1 | NULL | 6 +-----+-------+---------+-------------+----------+ 7 1 row in set (0.01 sec)
从以上查询可以看出：
①如果两者都是整数，则按整数值进行比较
②如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
③如果两者都是字符串，则按照字符串进行比较
④如果两者中至少有一个值是NULL，则比较的结果是NULL
1 mysql> select 2>4,2<'2', 'e'>='e',(4+4)<=(5+3); 2 +-----+-------+----------+--------------+ 3 | 2>4 | 2<'2' | 'e'>='e' | (4+4)<=(5+3) | 4 +-----+-------+----------+--------------+ 5 | 0 | 0 | 1 | 1 | 6 +-----+-------+----------+--------------+ 7 1 row in set (0.00 sec) 8 9 mysql> select 2!=4,null is null,null is not null,2 between 1 and 4; 10 +------+--------------+------------------+-------------------+ 11 | 2!=4 | null is null | null is not null | 2 between 1 and 4 | 12 +------+--------------+------------------+-------------------+ 13 | 1 | 1 | 0 | 1 | 14 +------+--------------+------------------+-------------------+ 15 1 row in set (0.00 sec) 16 17 mysql> select greatest (5,8,12),least (1,5,4); 18 +-------------------+---------------+ 19 | greatest (5,8,12) | least (1,5,4) | 20 +-------------------+---------------+ 21 | 12 | 1 | 22 +-------------------+---------------+ 23 1 row in set (0.00 sec)
十、逻辑运算符
10.1、又称为布尔运算符
10.2、用来判断表达式的真假
10.3、常用的逻辑运算符
|
运算符 |
说明 |
|
NOT或！ |
逻辑非 |
|
AND或&& |
逻辑与 |
|
OR或|| |
逻辑或 |
|
XOR |
逻辑异或 |
1 逻辑非 2 mysql> select not 2,!3,not 0,!(4-4); 3 +-------+----+-------+--------+ 4 | not 2 | !3 | not 0 | !(4-4) | 5 +-------+----+-------+--------+ 6 | 0 | 0 | 1 | 1 | 7 +-------+----+-------+--------+ 8 1 row in set (0.00 sec) 9 10 逻辑与 11 mysql> select 2 and 3,4 && 0,0 && NULL,1 and NULL; 12 +---------+--------+-----------+------------+ 13 | 2 and 3 | 4 && 0 | 0 && NULL | 1 and NULL | 14 +---------+--------+-----------+------------+ 15 | 1 | 0 | 0 | NULL | 16 +---------+--------+-----------+------------+ 17 1 row in set (0.00 sec)
十一、位运算符
11.1、对二进制数进行计算的运算符
11.2、常用的位运算符
|
运算符 |
说明 |
|
& |
按位与 |
|
| |
按位或 |
|
~ |
按位取反 |
|
^ |
按位异或 |
|
<< |
按位左移 |
|
>> |
按位右移 |
1 mysql> select 4&5,4|5,4&~3,3^4,2<<2,2>>1; 2 +-----+-----+------+-----+------+------+ 3 | 4&5 | 4|5 | 4&~3 | 3^4 | 2<<2 | 2>>1 | 4 +-----+-----+------+-----+------+------+ 5 | 4 | 5 | 4 | 7 | 8 | 1 | 6 +-----+-----+------+-----+------+------+ 7 1 row in set (0.00 sec)
十二、连接查询
MySQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接，首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上，使用较多的连接查询包括：内连接、左连接和右连接
12.1、内连接及示意图
1 mysql> select t.xuehao,t.xingming,t.chengji from test_ t inner join test t1 on t.xingming=t1.xingming; 2 +--------+----------+---------+ 3 | xuehao | xingming | chengji | 4 +--------+----------+---------+ 5 | 201006 | zhangsan | 80 | 6 | 201007 | lisi | 80 | 7 | 201007 | lisi | 80 | 8 +--------+----------+---------+ 9 3 rows in set (0.00 sec)
12.2、左连接及示意图
1 mysql> select t.xuehao,t.xingming,t.chengji from test_ t left join test t1 on t.xingming=t1.xingming; 2 +--------+----------+---------+ 3 | xuehao | xingming | chengji | 4 +--------+----------+---------+ 5 | 201006 | zhangsan | 80 | 6 | 201007 | lisi | 80 | 7 | 201007 | lisi | 80 | 8 | 201002 | zhaoliu | 95 | 9 | 201004 | wangwu | 80 | 10 | 201008 | lio | 75 | 11 | 201009 | lioo | 85 | 12 | 201009 | liooo | 55 | 13 +--------+----------+---------+ 14 8 rows in set (0.00 sec)
12.2、右连接及示意图
1 mysql> select t.xuehao,t.xingming,t.chengji from test_ t right join test t1 on t.xingming=t1.xingming; 2 +--------+----------+---------+ 3 | xuehao | xingming | chengji | 4 +--------+----------+---------+ 5 | 201006 | zhangsan | 80 | 6 | 201007 | lisi | 80 | 7 | 201007 | lisi | 80 | 8 | NULL | NULL | NULL | 9 +--------+----------+---------+ 10 4 rows in set (0.00 sec)
十三、函数
13.1、数学函数
|
函数 |
含义 |
|
abs(x) |
返回x的绝对值 |
|
rand() |
返回0到1的随机数 |
|
mod(x,y) |
返回x除以y以后的余数 |
|
power(x,y) |
返回x的y次方 |
|
round(x) |
返回离x最近的整数 |
|
round(x,y) |
保留x的y位小数四舍五入后的值 |
|
sqrt(x) |
返回x的平方根 |
|
truncate(x,y) |
返回数字x截断为y位小树的值 |
|
ceil(x) |
返回大于或等于x的最小整数 |
|
floor(x) |
返回小于或等于x的最大整数 |
|
greatest(x1,x2...) |
返回集合中最大的值 |
|
least(x1,x2...) |
返回集合中最小的值 |
常用的数学函数举例说明
1 mysql> select abs(-12),rand(),rand(),mod(4,5),power(2,6); 2 +----------+--------------------+--------------------+----------+------------+ 3 | abs(-12) | rand() | rand() | mod(4,5) | power(2,6) | 4 +----------+--------------------+--------------------+----------+------------+ 5 | 12 | 0.7755411516647238 | 0.7329282013074757 | 4 | 64 | 6 +----------+--------------------+--------------------+----------+------------+ 7 1 row in set (0.00 sec) 8 9 mysql> select round(2.4),round(2.5),round(2.4235,2),sqrt(2),truncate(2.4652,2); 10 +------------+------------+-----------------+--------------------+--------------------+ 11 | round(2.4) | round(2.5) | round(2.4235,2) | sqrt(2) | truncate(2.4652,2) | 12 +------------+------------+-----------------+--------------------+--------------------+ 13 | 2 | 3 | 2.42 | 1.4142135623730951 | 2.46 | 14 +------------+------------+-----------------+--------------------+--------------------+ 15 1 row in set (0.00 sec) 16 17 mysql> select ceil(2.2),floor(2.8),greatest(1,2,3,4,5),least(1,2,3,4,5); 18 +-----------+------------+---------------------+------------------+ 19 | ceil(2.2) | floor(2.8) | greatest(1,2,3,4,5) | least(1,2,3,4,5) | 20 +-----------+------------+---------------------+------------------+ 21 | 3 | 2 | 5 | 1 | 22 +-----------+------------+---------------------+------------------+ 23 1 row in set (0.00 sec)
13.2、聚合函数：对表中数据记录进行集中概括而设计的一类函数
|
函数 |
含义 |
|
avg() |
返回指定列的平均值 |
|
count() |
返回指定列中非NULL值的个数 |
|
min() |
返回指定看列的最小值 |
|
max() |
返回指定列的最大值 |
|
sum() |
返回指定列的所有值之和 |
1 聚合函数举例 2 mysql> select avg(chengji) from test; 3 +--------------+ 4 | avg(chengji) | 5 +--------------+ 6 | 63.7500 | 7 +--------------+ 8 1 row in set (0.00 sec) 9 10 mysql> select sum(chengji) from test; 11 +--------------+ 12 | sum(chengji) | 13 +--------------+ 14 | 255 | 15 +--------------+ 16 1 row in set (0.00 sec) 17 18 mysql> select min(chengji) from test; 19 +--------------+ 20 | min(chengji) | 21 +--------------+ 22 | 55 | 23 +--------------+ 24 1 row in set (0.00 sec) 25 26 mysql> select max(chengji) from test; 27 +--------------+ 28 | max(chengji) | 29 +--------------+ 30 | 70 | 31 +--------------+ 32 1 row in set (0.00 sec) 33 34 mysql> alter table test add sex char(2); 35 mysql> select * from test; 36 +--------+----------+----------+---------+------+ 37 | xuehao | nianling | xingming | chengji | sex | 38 +--------+----------+----------+---------+------+ 39 | 201001 | 17 | zhangsan | 60 | 男 | 40 | 201003 | 18 | lisi | 70 | NULL | 41 | 201005 | 19 | tianqi | 55 | NULL | 42 | 201007 | 19 | lisi | 70 | NULL | 43 +--------+----------+----------+---------+------+ 44 4 rows in set (0.00 sec) 45 mysql> update test set sex='男' where xingming='lisi'; 46 mysql> update test set sex='男' where xingming='zhangsan'; 47 mysql> select * from test; 48 +--------+----------+----------+---------+------+ 49 | xuehao | nianling | xingming | chengji | sex | 50 +--------+----------+----------+---------+------+ 51 | 201001 | 17 | zhangsan | 60 | 男 | 52 | 201003 | 18 | lisi | 70 | 男 | 53 | 201005 | 19 | tianqi | 55 | NULL | 54 | 201007 | 19 | lisi | 70 | 男 | 55 +--------+----------+----------+---------+------+ 56 4 rows in set (0.00 sec) 57 mysql> select count(sex) from test; 58 +------------+ 59 | count(sex) | 60 +------------+ 61 | 3 | 62 +------------+ 63 1 row in set (0.00 sec)
13.3、字符串函数
|
函数 |
含义 |
|
length(x) |
返回字符串x的长度 |
|
trim() |
返回去除指定格式的值 |
|
concat(x,y) |
将提供的参数x和y拼接成一个字符串 |
|
upper(x) |
将字符串x的所有字母变成大写字母 |
|
lower(x) |
将字符串x的所有字母变成小写字母 |
|
left(x,y) |
返回字符串x的前y个字符 |
|
right(x,y) |
返回字符串x的后y个字符 |
|
repeat(x.y) |
将字符串x重复y次 |
|
space(x) |
返回x个空格 |
|
replace(x,y,z) |
将字符串z替代字符串x中的字符串y |
|
strcmp(x,y) |
比较x和y，返回的值可以位-1，0，1 |
|
substring(x,y,z) |
获取从从字符串x中的第y个位置开始长度为z的字符串 |
|
reverse(x) |
将字符串x反转 |
1 举例说明
2 mysql> select length('abc'),trim( 'ab' ),concat('ab','cd'),upper('abc'),lower('ABC');
3 +---------------+---------------+-------------------+--------------+--------------+
4 | length('abc') | trim( 'ab' ) | concat('ab','cd') | upper('abc') | lower('ABC') |
5 +---------------+---------------+-------------------+--------------+--------------+
6 | 3 | ab | abcd | ABC | abc |
7 +---------------+---------------+-------------------+--------------+--------------+
8 1 row in set (0.00 sec)
9
10 mysql> select left('abcd',2),right('abcd',2),repeat('abc',3);
11 +----------------+-----------------+-----------------+
12 | left('abcd',2) | right('abcd',2) | repeat('abc',3) |
13 +----------------+-----------------+-----------------+
14 | ab | cd | abcabcabc |
15 +----------------+-----------------+-----------------+
16 1 row in set (0.00 sec)
17
18 mysql> select space(2),replace('abcde','e','g'),strcmp('a','b'),strcmp('a','a'),strcmp('b','a');
19 +----------+--------------------------+-----------------+-----------------+-----------------+
20 | space(2) | replace('abcde','e','g') | strcmp('a','b') | strcmp('a','a') | strcmp('b','a') |
21 +----------+--------------------------+-----------------+-----------------+-----------------+
22 | | abcdg | -1 | 0 | 1 |
23 +----------+--------------------------+-----------------+-----------------+-----------------+
24 1 row in set (0.00 sec)
25
26 mysql> select substring('abcdefg',3,3),reverse('abcdefg');
27 +--------------------------+--------------------+
28 | substring('abcdefg',3,3) | reverse('abcdefg') |
29 +--------------------------+--------------------+
30 | cde | gfedcba |
31 +--------------------------+--------------------+
32 1 row in set (0.00 sec)
13.4、日期时间函数
|
函数 |
含义 |
|
curdate() |
返回当前时间的年月日 |
|
curtime() |
返回当前时间的时分秒 |
|
now() |
返回当前时间的日期和时间 |
|
month() |
返回日期x中的月份值 |
|
week(x) |
返回日期x是年度第几个星期 |
|
hour(x) |
返回x中的小时值 |
|
minute(x) |
返回x中的分钟值 |
|
second(x) |
返回x中的秒钟值 |
|
dayofweek(x) |
返回x是星期几，1星期日，2星期1 |
|
dayofmonth(x) |
计算日期x是本月的第几天 |
|
dayofyear(x) |
计算日期x是本年的第几天 |
1 mysql> select curdate(),curtime(),now(),month('2020-08-15'),week('2020-08-15');
2 +------------+-----------+---------------------+---------------------+--------------------+
3 | curdate() | curtime() | now() | month('2020-08-15') | week('2020-08-15') |
4 +------------+-----------+---------------------+---------------------+--------------------+
5 | 2020-11-02 | 19:03:03 | 2020-11-02 19:03:03 | 8 | 32 |
6 +------------+-----------+---------------------+---------------------+--------------------+
7 1 row in set (0.00 sec)
8
9 mysql> select hour('20:15:45'),minute('20:15:45'),second('20:15:45');
10 +------------------+--------------------+--------------------+
11 | hour('20:15:45') | minute('20:15:45') | second('20:15:45') |
12 +------------------+--------------------+--------------------+
13 | 20 | 15 | 45 |
14 +------------------+--------------------+--------------------+
15 1 row in set (0.00 sec)
16
17 mysql> select dayofweek('2020-08-15'),dayofmonth('2020-08-15'),dayofyear('2020-08-15');
18 +-------------------------+--------------------------+-------------------------+
19 | dayofweek('2020-08-15') | dayofmonth('2020-08-15') | dayofyear('2020-08-15') |
20 +-------------------------+--------------------------+-------------------------+
21 | 7 | 15 | 228 |
22 +-------------------------+--------------------------+-------------------------+
23 1 row in set (0.00 sec)
十四、存储过程
14.1、简介
14.1.1、是一组为了完成特定功能的SQL语句集合
14.1.2、比传统的SQL速度更快、执行效率更高
14.1.3、存储过程的优点
①执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
②SQL语句加上控制语句的集合，灵活性高
③在服务器端存储，客户端调用时，降低网络负载
④可多次重复被调用，可随时修改，不影响客户端调用
⑤可完成所有的数据库操作，也可控制数据库的信息访问权限
14.2、创建存储过程
14.2.1、使用CREATE PROCEDURE语句创建存储过程
14.2.2、创建存储过程的语法结构
CREATE PROCEDURE <过程名> （过程参数[...]）<过程体> [过程参数[...]] 格式 [IN|OUT|INOUT] <参数名> <类型>
14.3、参数分为
14.3.1、输入参数：IN
14.3.2、输出参数：OUT
14.3.3、输入/输出参数：INOUT
14.4、存储过程的主体部分，被称为过程体
14.5、以BEGIN开始，以END结束，若只有一条SQL语句
14.6、以DELIMITER开始和结束
14.7、存储过程
1 mysql> delimiter $$ 2 mysql> create procedure a() 3 -> begin 4 -> select * from test limit 3; 5 -> end $$ 6 Query OK, 0 rows affected (0.00 sec) 7 8 mysql> delimiter ; 9 mysql> call a(); 10 +--------+----------+----------+---------+------+ 11 | xuehao | nianling | xingming | chengji | sex | 12 +--------+----------+----------+---------+------+ 13 | 201001 | 17 | zhangsan | 60 | 男 | 14 | 201003 | 18 | lisi | 70 | 男 | 15 | 201005 | 19 | tianqi | 55 | NULL | 16 +--------+----------+----------+---------+------+ 17 3 rows in set (0.01 sec) 18 19 Query OK, 0 rows affected (0.01 sec)
1 mysql> use score; 2 mysql> set @num1=1,@num2=2,@num3=3; 3 mysql> delimiter $$ 4 mysql> create procedure p(in num1 int,out num2 int,inout num3 int) 5 -> begin 6 -> select num1,num2,num3; 7 -> set num1=10,num2=20,num3=30; 8 -> select num1,num2,num3; 9 -> end $$ 10 Query OK, 0 rows affected (0.00 sec) 11 12 mysql> delimiter ; 13 mysql> call p(@num1,@num2,@num3); 14 +------+------+------+ 15 | num1 | num2 | num3 | 16 +------+------+------+ 17 | 1 | NULL | 3 | 18 +------+------+------+ 19 1 row in set (0.00 sec) 20 21 +------+------+------+ 22 | num1 | num2 | num3 | 23 +------+------+------+ 24 | 10 | 20 | 30 | 25 +------+------+------+ 26 1 row in set (0.00 sec) 27 总结1：in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中，在全局过程使用中，参数值in、out、inout都会发生改变 28 29 mysql> select @num1,@num2,@num3; 30 +-------+-------+-------+ 31 | @num1 | @num2 | @num3 | 32 +-------+-------+-------+ 33 | 1 | 20 | 30 | 34 +-------+-------+-------+ 35 1 row in set (0.00 sec) 36 总结2：调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量，in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须是变量
14.8、修改存储过程
14.8.1、存储过程的修改分为特征修改和内容修改
14.8.2、特征修改的方法
ALTER PROCEDURE <过程名> [<特征>...]
14.8.3、内容修改可先删除原有存储过程，之后再创建方法
14.9、删除存储过程
14.9.1、删除存储过程的语法
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
14.9.2、删除的过程
1 mysql> drop procedure a; 2 mysql> call a(); 3 ERROR 1305 (42000): PROCEDURE score.a does not exist
浙公网安备 33010602011771号