理论+实验·MySQL高级SQL语句
文章目录
一、MySQL进阶查询
1.1 按关键字排序
- 使用ORDER BY语句来实现排序
- 排序可针对一个或多个字段
- ASC:升序,默认排序方式
- DESC:降序
- ORDER BY的语法结构
mysql> select * from test order by level desc;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 4 | zhaoliu | 63 |
| 7 | tom | 63 |
| 3 | wangwu | 55 |
| 6 | heiba | 52 |
| 1 | zhangsan | 46 |
| 5 | tianqi | 43 |
| 8 | jerry | 43 |
| 2 | lisi | 35 |
+----+----------+-------+
8 rows in set (0.00 sec)
- 按单字段排序
mysql> select * from test where level >= 45 order by level desc;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 4 | zhaoliu | 63 |
| 7 | tom | 63 |
| 3 | wangwu | 55 |
| 6 | heiba | 52 |
| 1 | zhangsan | 46 |
+----+----------+-------+
5 rows in set (0.00 sec)
- 按多字段排序
主参考字段放在前面;辅助参考字段放在后面
主参考字段与辅助参考字段之间用逗号’,'隔开
mysql> select * from test where level>=45 order by level desc,id desc;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 7 | tom | 63 |
| 4 | zhaoliu | 63 |
| 3 | wangwu | 55 |
| 6 | heiba | 52 |
| 1 | zhangsan | 46 |
+----+----------+-------+
5 rows in set (0.00 sec)
1.2 对结果进行分组
- 使用GROUP BY语句来实现分组
- 通常结合聚合函数一起使用
- 可以按一个或多个字段对结果进行分组
- GROUP BY的语法结构
mysql> select count(name) 数量,level from test where level>=40 group by level;
+--------+-------+
| 数量 | level |
+--------+-------+
| 2 | 43 |
| 1 | 46 |
| 1 | 52 |
| 1 | 55 |
| 2 | 63 |
+--------+-------+
5 rows in set (0.00 sec)
- 聚合函数=一个值
count (字段名称) 计数
sum (字段名称) 求和
avg (字段名称) 平均值
max (字段名称) 最大值
min (字段名称) 最小值
1.3 限制结果条目
-
只返回SELECT查询结果的第一行或前几行
-
使用LIMIT语句限制条目
-
LIMIT语法结构
[1,3] 从0开始,1开始那行的三行
mysql> select column1,column2,... from table_name limit [offset,] number;
- LIMIT限制结果条数
mysql> select * from test limit 3;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 1 | zhangsan | 46 |
| 2 | lisi | 35 |
| 3 | wangwu | 55 |
+----+----------+-------+
3 rows in set (0.00 sec)
- 不从第一条开始取值
mysql> select * from test limit 2,3; //从第三条记录开始显示之后的三条数据
+----+---------+-------+
| id | name | level |
+----+---------+-------+
| 3 | wangwu | 55 |
| 4 | zhaoliu | 63 |
| 5 | tianqi | 43 |
+----+---------+-------+
3 rows in set (0.00 sec)
1.4 设置别名
- 使用AS语句设置别名,关键字AS可省略
- 设置别名时,保证不能与苦衷其他表或字段名称冲突
- 别名的语法结构
mysql> select column_name AS alias_name from table_name; //列的别名
mysql> select column_name(s) from table_name AS alias_name; //表的别名
</