常用！重要！高级SQL语言MySQL

MYSQL高级SQL语言

一、MYSQL常用查询介绍
1.1按关键字排序
■使用ORDER BY语句来实现排序
■排序可针对一个或多个字段
■ASC∶升序，默认排序方式
■ DESC∶降序
■ORDER BY的语法结构
SELECT column1, column2,. FROM table_name ORDER BY column1, column2, ASC|DESC;
1.1.1按单字段排序
示例
查看分数大于70分的人，并将大于七十分的安降序显示

mysql> select*from info;
+----+--------------+-------+----------+
| id | name         | score | address  |
+----+--------------+-------+----------+
|  1 | 王五         | 88.00 | 苏州     |
|  2 | 王二麻子     | 60.00 | 未知     |
|  3 | 张三丰       | 75.50 | 南京     |
|  4 | lisi         | 90.00 | ningxia  |
|  5 | li           | 90.00 | beijing  |
|  6 | zhang        | 90.00 | beihai   |
|  7 | wang         | 90.00 | shanghai |
|  8 | niuer        | 90.00 | jurong   |
|  9 | chuwu        | 90.00 | jurong   |
+----+--------------+-------+----------+
9 rows in set (0.00 sec)

mysql> select name,score from info where score>=70 order by score desc;
+-----------+-------+
| name      | score |
+-----------+-------+
| lisi      | 90.00 |
| li        | 90.00 |
| zhang     | 90.00 |
| wang      | 90.00 |
| niuer     | 90.00 |
| chuwu     | 90.00 |
| 王五      | 88.00 |
| 张三丰    | 75.50 |
+-----------+-------+
8 rows in set (0.00 sec)

1.1.2按多字段排序
主参考字段在前，辅参考字段
在后面在score相同时，id 按降序排序

mysql> select id,name,score from info where score>=70 order by score desc,,id desc;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  9 | chuwu     | 90.00 |
|  8 | niuer     | 90.00 |
|  7 | wang      | 90.00 |
|  6 | zhang     | 90.00 |
|  5 | li        | 90.00 |
|  4 | lisi      | 90.00 |
|  1 | 王五      | 88.00 |
|  3 | 张三丰    | 75.50 |
+----+-----------+-------+
8 rows in set (0.00 sec)

1.2对结果进行分组
■使用GROUP BY语句来实现分组通常结合聚合函数一起使用
■可以按一个或多个字段对结果进行分组
■ GROUP BY的语法结构
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

1.2.1聚合函数种类
count（）计数 =字段名称
sum（） 求和
avg () 平均值
max（）最大值
min ()最小值
shell函数function 函数名（）{
命令序列
1.2.2示例统计分数大于70分的人数并分组

mysql> select count(name),score from info where score>=70 group by score; 
+-------------+-------+
| count(name) | score |
+-------------+-------+
|           1 | 75.50 |
|           1 | 88.00 |
|           6 | 90.00 |
+-------------+-------+
3 rows in set (0.00 sec)

最大分数

mysql> select max(score) from info ;
+------------+
| max(score) |
+------------+
|      90.00 |
+------------+
1 row in set (0.00 s

求和

mysql> select sum(score) from info ;
+------------+
| sum(score) |
+------------+
|     763.50 |
+------------+
1 row in set (0.00 sec)

1.4限制结果条目
limit限制结果条数
不从第一条开始取值，从第1个开始数值为0
示 例查看前三行

mysql> select * from info limit 3;
+----+--------------+-------+---------+
| id | name         | score | address |
+----+--------------+-------+---------+
|  1 | 王五         | 88.00 | 苏州