一、按关键字排序
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;
语句使用
mysql -uroot -p123123
mysql> create database score;
mysql> use score;
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);
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);
mysql> select * from test;
升序
mysql> select chengji from test order by chengji asc;
+---------+
| chengji |
+---------+
| 55 |
| 60 |
| 70 |
| 80 |
| 95 |
+---------+
5 rows in set (0.00 sec)
mysql> select chengji from test order by chengji; #默认是ASC
+---------+
| chengji |
+---------+
| 55 |
| 60 |
| 70 |
| 80 |
| 95 |
+---------+
5 rows in set (0.00 sec)
降序
mysql> select chengji from test order by chengji desc;
+---------+
| chengji |
+---------+
| 95 |
| 80 |
| 70 |
| 60 |
| 55 |
+---------+
5 rows in set (0.00 sec)
1.6、按单字段排序
mysql> select xuehao,xingming,chengji from test order by chengji;
+--------+----------+---------+
| xuehao | xingming | chengji |
+--------+----------+---------+
| 201005 | tianqi | 55 |
| 201001 | zhangsan | 60 |
| 201003 | lisi | 70 |
| 201004 | wangwu | 80 |
| 201002 | zhaoliu | 95 |
+--------+----------+---------+
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.5、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 &#