MySQL一些常用的高级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 以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 m y s q l > s e l e c t x u e h a o , x i n g m i n g , c h e n g j i f r o m t e s t w h e r e x i n g m i n g r e g e x p ′ n ) 12 mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'n )12mysql>selectxuehao,xingming,chengjifromtestw​herexingmingregexp′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、常用比较运算符
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、常用的逻辑运算符
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)
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)