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
