order by
Order by,可以使用一列或者多个列对结果进行排序。
如果存在多个排序字段,在前一个不能比较出结果后,后边的才起作用
可以分别指明是升序还是降序:asc(ascending) desc(descending)
用法:
如果存在多个排序字段,在前一个不能比较出结果后,后边的才起作用
可以分别指明是升序还是降序:asc(ascending) desc(descending)
用法:
[ORDER BY {col_name | expr | position} [ASC | DESC] , ...]
注意:
- 是对检索出来的字段进行排序,一定要在where之后
- 如果是分组,则应该使用对分组字段进行排序的groupby语法。
mysql> select * from teacher_class where 1 order by days;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 6 | 韩非 | secret | php0115 | 207 | 15 | 2013-03-27 | 2013-04-18 |
| 2 | 韩信 | male | php0228 | 106 | 18 | 2013-02-28 | 2013-03-30 |
| 4 | 李白 | male | php0115 | 207 | 20 | 2013-02-22 | 2013-03-25 |
| 1 | 韩信 | male | php0115 | 207 | 21 | 2013-01-15 | 2013-02-20 |
| 5 | 李白 | male | php0228 | 204 | 21 | 2013-03-31 | 2013-04-29 |
| 3 | 韩信 | male | php0331 | 102 | 24 | 2013-03-31 | 2013-05-05 |
+----+--------+--------+---------+------+------+------------+------------+
6 rows in set (0.00 sec)
mysql> select * from teacher_class where 1 order by days desc;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 3 | 韩信 | male | php0331 | 102 | 24 | 2013-03-31 | 2013-05-05 |
| 1 | 韩信 | male | php0115 | 207 | 21 | 2013-01-15 | 2013-02-20 |
| 5 | 李白 | male | php0228 | 204 | 21 | 2013-03-31 | 2013-04-29 |
| 4 | 李白 | male | php0115 | 207 | 20 | 2013-02-22 | 2013-03-25 |
| 2 | 韩信 | male | php0228 | 106 | 18 | 2013-02-28 | 2013-03-30 |
| 6 | 韩非 | secret | php0115 | 207 | 15 | 2013-03-27 | 2013-04-18 |
+----+--------+--------+---------+------+------+------------+------------+
6 rows in set (0.00 sec)
上述代码可以看出,默认是升序的。
mysql> select t_name, days from teacher_class where 1 group by t_name order by days desc;
+--------+------+
| t_name | days |
+--------+------+
| 韩信 | 21 |
| 李白 | 20 |
| 韩非 | 15 |
+--------+------+
3 rows in set (0.00 sec)
order by对分组的数据进行排序是无效的,必须经过group by内部的排序才有效!
解决方法:
mysql> select * from
-> (select t_name as name, sum(days) as day from teacher_class group by t_name) as tb
-> order by day;
+------+------+
| name | day |
+------+------+
| 韩非 | 15 |
| 李白 | 41 |
| 韩信 | 63 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from
-> (select t_name as name, sum(days) as day from teacher_class group by t_name) as tb
-> order by day desc;
+------+------+
| name | day |
+------+------+
| 韩信 | 63 |
| 李白 | 41 |
| 韩非 | 15 |
+------+------+
3 rows in set (0.00 sec)
先通过t_name对表进行分组,(注意,分组中的表一定要使用别名),然后再对分组的表进行group by排序,注意,这里使用的是别名!
对多个字段进行排序(只有前一个字段排序无效的时候,才会在后一个字段进行排序):
limit
Limit子句可以被用于限制被SELECT语句返回的行数.
用法:
用法:
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
- Offset 偏移量,从0开始。可以省略,默认为0.(也就是下边索引是从0开始的)
- Row_count 总记录数,如果数量大于,余下的记录数,则获取所有余下的即可:
表示从offset索引位置开始获取row_count条记录。
可以省略offset,默认为0. limit row_count == limit 0, row_count;
mysql> select * from teacher_class limit 3,4;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 4 | 李白 | male | php0115 | 207 | 20 | 2013-02-22 | 2013-03-25 |
| 5 | 李白 | male | php0228 | 204 | 21 | 2013-03-31 | 2013-04-29 |
| 6 | 韩非 | secret | php0115 | 207 | 15 | 2013-03-27 | 2013-04-18 |
+----+--------+--------+---------+------+------+------------+------------+
3 rows in set (0.00 sec)
从索引行第三行开始,截取四行数据,也就是截取3,4,5,6行数据(索引从0开始)。
mysql> select * from teacher_class limit 5;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 1 | 韩信 | male | php0115 | 207 | 21 | 2013-01-15 | 2013-02-20 |
| 2 | 韩信 | male | php0228 | 106 | 18 | 2013-02-28 | 2013-03-30 |
| 3 | 韩信 | male | php0331 | 102 | 24 | 2013-03-31 | 2013-05-05 |
| 4 | 李白 | male | php0115 | 207 | 20 | 2013-02-22 | 2013-03-25 |
| 5 | 李白 | male | php0228 | 204 | 21 | 2013-03-31 | 2013-04-29 |
+----+--------+--------+---------+------+------+------------+------------+
5 rows in set (0.00 sec)
从第0行开始,截取前五行数据;
mysql> select * from teacher_class limit 5, 100;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 6 | 韩非 | secret | php0115 | 207 | 15 | 2013-03-27 | 2013-04-18 |
+----+--------+--------+---------+------+------+------------+------------+
1 row in set (0.00 sec)
当行数大于剩下的所有行数时,获取所有剩下的行数。
mysql> select * from teacher_class order by days desc limit 1;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 3 | 韩信 | male | php0331 | 102 | 24 | 2013-03-31 | 2013-05-05 |
+----+--------+--------+---------+------+------+------------+------------+
1 row in set (0.00 sec)
获取天数最多的一行数据。
distinct
去除重复记录,
所谓重复的记录,指的是 字段值都相同的记录,而不是部分字段相同的记录。
所谓重复的记录,指的是 字段值都相同的记录,而不是部分字段相同的记录。
mysql> select * from teacher_class;
+----+--------+--------+---------+------+------+------------+------------+
| id | t_name | gender | c_name | room | days | begin_date | end_date |
+----+--------+--------+---------+------+------+------------+------------+
| 1 | 韩信 | male | php0115 | 207 | 21 | 2013-01-15 | 2013-02-20 |
| 2 | 韩信 | male | php0228 | 106 | 18 | 2013-02-28 | 2013-03-30 |
| 3 | 韩信 | male | php0331 | 102 | 24 | 2013-03-31 | 2013-05-05 |
| 4 | 李白 | male | php0115 | 207 | 20 | 2013-02-22 | 2013-03-25 |
| 5 | 李白 | male | php0228 | 204 | 21 | 2013-03-31 | 2013-04-29 |
| 6 | 韩非 | secret | php0115 | 207 | 15 | 2013-03-27 | 2013-04-18 |
+----+--------+--------+---------+------+------+------------+------------+
mysql> select distinct t_name from teacher_class;
+--------+
| t_name |
+--------+
| 韩信 |
| 李白 |
| 韩非 |
+--------+
3 rows in set (0.00 sec)
所有名字都不相同的记录。
mysql> select distinct t_name,gender from teacher_class;
+--------+--------+
| t_name | gender |
+--------+--------+
| 韩信 | male |
| 李白 | male |
| 韩非 | secret |
+--------+--------+
3 rows in set (0.00 sec)
名字和型别都不相同的记录。
all
和distinct相似,只不过他是返回所有的记录。
mysql> select all t_name from teacher_class;
+--------+
| t_name |
+--------+
| 韩信 |
| 韩信 |
| 韩信 |
| 李白 |
| 李白 |
| 韩非 |
+--------+
6 rows in set (0.00 sec)
其实默认的就是all操作,所以加不加all都是一样的。
mysql> select t_name from teacher_class;
+--------+
| t_name |
+--------+
| 韩信 |
| 韩信 |
| 韩信 |
| 李白 |
| 李白 |
| 韩非 |
+--------+
6 rows in set (0.00 sec)
union
UNION用于把来自许多SELECT语句的结果组合到一个结果集合中。
用法:
获得每一个班级内代课最多的讲师。
用法:
SELECT ...UNION [ALL | DISTINCT]SELECT ...[UNION [ALL | DISTINCT]SELECT ...]
获得每一个班级内代课最多的讲师。
mysql> select t_nam