MySQL学习 (三) Limit-Distinct-Union
Limit
简介
限制获得的记录数量
mysql> SELECT * FROM teacher_class limit 1;
+----+--------+--------+-----------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+-----------+------+------+------------+------------+
| 1 | Gene | male | python001 | 334 | 22 | 2013-03-31 | 2013-05-05 |
+----+--------+--------+-----------+------+------+------------+------------+
1 row in set (0.00 sec)
语法
Limit offset, row_count
# offset 偏移量,从0开始;
# row_count,总记录数,长度,不是开始和结束, 如果数量大于余下的记录数,则获取所有余下的记录数,默认值0
mysql> SELECT * FROM teacher_class limit 3,4;
+----+---------+--------+-----------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+---------+--------+-----------+------+------+------------+------------+
| 4 | Emma | female | python004 | 338 | 20 | 2013-03-15 | 2013-04-05 |
| 5 | Janice | female | python007 | 332 | 27 | 2013-04-15 | 2013-05-10 |
| 6 | Jessica | female | python008 | 332 | 28 | 2013-05-15 | 2013-06-08 |
+----+---------+--------+-----------+------+------+------------+------------+
3 rows in set (0.00 sec)
Distinct
简介
distinct: 去掉重复记录, 指的是字段值,都相同的记录,而不是部分字段相同的记录
All: 表示所有,默认就是all的行为
mysql> SELECT days FROM teacher_class;
+------+
| days |
+------+
| 22 |
| 22 |
| 55 |
| 20 |
| 27 |
| 28 |
+------+
6 rows in set (0.00 sec)
mysql> SELECT DISTINCT days FROM teacher_class;
+------+
| days |
+------+
| 22 |
| 55 |
| 20 |
| 27 |
| 28 |
+------+
5 rows in set (0.00 sec)
mysql> SELECT days, begin_date FROM teacher_class;
+------+------------+
| days | begin_date |
+------+------------+
| 22 | 2013-03-31 |
| 22 | 2013-05-31 |
| 55 | 2013-02-15 |
| 20 | 2013-03-15 |
| 27 | 2013-04-15 |
| 28 | 2013-05-15 |
+------+------------+
6 rows in set (0.01 sec)
mysql> SELECT DISTINCT days, begin_date FROM teacher_class;
+------+------------+
| days | begin_date |
+------+------------+
| 22 | 2013-03-31 |
| 22 | 2013-05-31 |
| 55 | 2013-02-15 |
| 20 | 2013-03-15 |
| 27 | 2013-04-15 |
| 28 | 2013-05-15 |
+------+------------+
6 rows in set (0.00 sec)
union联合查询
简介
将多条select语句的结果,合并到一起,称为联合
使用union关键字,联合两个select语句即可
注意 如果union查询结果有重复,会自动去掉重复的数据;如果想得到全部数据,通过UNION ALL来显示数据
多个select语句检索时,字段要一致; 数据类型,尽量保持一致;如果不一致,应该能让MySQL能够做类型转换
检索结果列名称的问题:根据第一条select语句的字段,进行名称排列
场景
获得数据的条件,出现逻辑冲突,或者很难在一个逻辑内表示,不好拆分,分成多个逻辑,分别实现,然后合并到一起
mysql> SELECT t_name, days FROM teacher_class WHERE c_name = "python002" ORDER BY days DESC LIMIT 1;
+---------+------+
| t_name | days |
+---------+------+
| Jessica | 55 |
+---------+------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT t_name, days FROM teacher_class WHERE c_name = "python008" ORDER BY days DESC LIMIT 1;
+---------+------+
| t_name | days |
+---------+------+
| Jessica | 28 |
+---------+------+
1 row in set (0.00 sec)
# 联合查询语法
mysql>(SELECT t_name, days FROM teacher_class WHERE c_name = "python002" ORDER BY days DESC LIMIT 1) UNION (SELECT t_name, days FROM teacher_class WHERE c_name = "python008" ORDER BY days DESC LIMIT 1);
+---------+------+
| t_name | days |
+---------+------+
| Jessica | 55 |
| Jessica | 28 |
+---------+------+
2 rows in set (0.00 sec)
子语句的结果排序
将子语句包裹在括号里
子语句的order by需要使用limit才能生效,否则,排序失效
所有结果进行排序
只需要在最后一个select子语句后,使用ORDER BY进行排序
mysql> (SELECT t_name, days FROM teacher_class WHERE c_name = "python002" ) \
-> UNION (SELECT t_name, days FROM teacher_class WHERE c_name = "python008" ) ORDER BY days;
+---------+------+
| t_name | days |
+---------+------+
| Mona | 22 |
| Emma | 28 |
| Jessica | 28 |
| Mona | 55 |
| Jessica | 55 |
+---------+------+
5 rows in set (0.00 sec)