MySQL复习值代码知识点(2)
目录
[group by使用举例](#group by使用举例)
[group by 与order by 联合使用](#group by与order by联合使用)
[SQL运行结果查询](# SQL运行结果查询 )
总汇表
| order by | 排序 desc降序 asc升序 |
|---|---|
| group by | 分组 （一般与聚集函数连用） |
| having | 对组进行限制与聚集函数连用 |
| Limit | 分页 |
| Avg | 聚集函数：求平均值 |
| Sum | 聚集函数：求和（数值的加法） |
| Max | 聚集函数：求最大值 |
| Min | 聚集函数：求最小值 |
| count | 聚集函数：相同列值求行数的和 |
where子句作用于表和视图，HAVING 子句作用于组；
where子句中不能使用聚集函数，而having子句中可以。
有如下表：
CREATE TABLE `grade` (
`id` int(11) NOT NULL PRIMARY KEY,
`name` varchar(20),
`grades` int(11) ,
) ;
order by使用举例
按照姓名升序排列：
mysql> select * from grade
-> order by name asc;
按照姓名降序排序：
mysql> select * from grade
-> order by name desc;
group by使用举例
group通常是和别函数一起调用的。（count,sum）
求每个学生姓名的所有成绩总分：
mysql> select name,sum(grades)
-> from grade
-> group by name;
（解释：首先，要求每个学生的总分数，要把每个学生聚集到一起，然后去把聚集在一起的每个学生的成绩求和）
求每个学生一共有几个成绩：
mysql> select name,count(name)
-> from grade
-> group by name;
（解释：首先把相同name的行聚集到一起，然后用count求出每个name相同的一共有几行）
求每个学生的总成绩：
mysql> select name,sum(grades)
-> from grade
-> group by name;
求每个学生的平均成绩：
mysql> select name,avg(grades)
-> from grade
-> group by name;
求每个学生的成绩最低分：
mysql> select name,min(grades)
-> from grade
-> group by name;
limit举例
limit 参数1：显示查询结果的前‘参数1’个数据；
limit 参数1 参数2：跳过查询结果的前‘参数1’个数据，显示剩下的前‘参数2’个数据
取前三个查询结果：
mysql> select * from grade
-> limit 3;
group by 与order by 联合使用
求总成绩排名前二的学生：
mysql> select name,sum(grades) as a
-> from grade
-> group by name
-> order by a desc
-> limit 2;
having的使用
查询所有学生总成绩大于100的学生：
mysql> select name,sum(grades)
-> from grade
-> group by name
-> having sum(grades)>100;
SQL运行结果查询
数据输入
INSERT INTO `grade` VALUES (0, '1', 10);
INSERT INTO `grade` VALUES (1, '1', 10);
INSERT INTO `grade` VALUES (2, '2', 20);
INSERT INTO `grade` VALUES (3, '3', 30);
INSERT INTO `grade` VALUES (4, '4', 40);
INSERT INTO `grade` VALUES (5, '1', 100);
INSERT INTO `grade` VALUES (6, '2', 100);
运行结果查询：
按照姓名升序排列：
mysql> select * from grade
-> order by name asc;
+----+------+--------+
| id | name | grades |
+----+------+--------+
| 0 | 1 | 10 |
| 1 | 1 | 10 |
| 5 | 1 | 100 |
| 2 | 2 | 20 |
| 6 | 2 | 100 |
| 3 | 3 | 30 |
| 4 | 4 | 40 |
+----+------+--------+
按照姓名降序排序：
mysql> select * from grade
-> order by name desc;
+----+------+--------+
| id | name | grades |
+----+------+--------+
| 4 | 4 | 40 |
| 3 | 3 | 30 |
| 2 | 2 | 20 |
| 6 | 2 | 100 |
| 0 | 1 | 10 |
| 1 | 1 | 10 |
| 5 | 1 | 100 |
+----+------+--------+
求每个学生姓名的所有成绩总分：
mysql> select name,sum(grades)
-> from grade
-> group by name;
+------+-------------+
| name | sum(grades) |
+------+-------------+
| 1 | 120 |
| 2 | 120 |
| 3 | 30 |
| 4 | 40 |
+------+-------------+
求每个学生一共有几个成绩：
mysql> select name,count(name)
-> from grade
-> group by name;
+------+-------------+
| name | count(name) |
+------+-------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
+------+-------------+
求每个学生的总成绩：
mysql> select name,sum(grades)
-> from grade
-> group by name;
+------+-------------+
| name | sum(grades) |
+------+-------------+
| 1 | 120 |
| 2 | 120 |
| 3 | 30 |
| 4 | 40 |
+------+-------------+
求每个学生的平均成绩：
mysql> select name,avg(grades)
-> from grade
-> group by name;
+------+-------------+
| name | avg(grades) |
+------+-------------+
| 1 | 40.0000 |
| 2 | 60.0000 |
| 3 | 30.0000 |
| 4 | 40.0000 |
+------+-------------+
求每个学生的成绩最低分：
mysql> select name,min(grades)
-> from grade
-> group by name;
+------+-------------+
| name | min(grades) |
+------+-------------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
+------+-------------+
取前三个查询结果：
mysql> select * from grade
-> limit 3;
+----+------+--------+
| id | name | grades |
+----+------+--------+
| 0 | 1 | 10 |
| 1 | 1 | 10 |
| 2 | 2 | 20 |
+----+------+--------+
求总成绩排名前二的学生：
mysql> select name,sum(grades) as a
-> from grade
-> group by name
-> order by a desc
-> limit 2;
+------+-----+
| name | a |
+------+-----+
| 1 | 120 |
| 2 | 120 |
+------+-----+
查询所有学生总成绩大于100的学生：
mysql> select name,sum(grades)
-> from grade
-> group by name
-> having sum(grades)>100;
+------+-------------+
| name | sum(grades) |
+------+-------------+
| 1 | 120 |
| 2 | 120 |
+------+-------------+
