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 | xing