mysql limit括号_MySQL学习 (三) Limit-Distinct-Union

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)