数据库—DML2(排序和分组，聚集函数和连接查询）

数据库—DML(排序和分组）

---

排序

• 查询的结果按一定的次序显示更便于观察
• ORDER BY子句可以将查询的结果按一定次序显示
• ORDER BY子句可以将查询的结果按一定次序显示，其形式如下：
• ORDER BY <排序列> [ASC︱DESC] {, <排序列> [ASC︱DESC]}
• 其中，<排序列>是必须出现在SELECT子句中的属性名或属性的别名
• ORDER BY后可以有一个或多个<排序列>，中间用逗号隔开
• 每个<排序列>都可以独立指定按升序（ASC）还是按降序（DESC）排序，缺省时为升序
• 如果指定多个<排序列>，则查询结果按指定的次序，首先按第一个<排序列>的值排序，第一个<排序列>值相同的结果元组按第二个<排序列>的值排序，如此下去

SELECT *
FROM SC
WHERE Cno=’CS202’
ORDER BY Grade DESC; 
//在表SC中查询课程号为CS202的并且以GRADE的降序排列并输出详细信息

SELECT *
FROM SC 
ORDER BY Cno, Grade DESC; 
//在表SC中查询每位课程每门课程的成绩，并将查询结果按课程号升序、成绩降序排序
//cno后排列方式缺省，默认升序

---

聚集函数

• 实际应用中，常常需要计算一些统计量
• 例如，统计学生的总人数、女生的人数、学生的平均成绩等等。SQL语言提供了一些聚集函数
• 使用聚集函数可以方便的进行各种统计查询

常用聚集函数

聚集函数名	含义
COUNT(ALL|DISTINCT]*)	统计元组个数
COUNT(ALL|DISTINCT]列名)	统计一列中值的个数
SUM(ALL|DISTINCT]列名)	统计一列值的总和
AVG(ALL|DISTINCT]列名)	计算一列值的平均值
MAX(ALL|DISTINCT]列名)	求一列值中的最大值
MIN(ALL|DISTINCT]列名)	求一列值中的最小值

• 短语ALL或DISTINCT是可选的，缺省时为ALL。
• 设f是聚集函数，e是值表达式。f(ALL e)或f(e)对每个分组，首先对该分组中每个元组计算e，得到e值的多重集，然后，将f作用于该多重集得到聚集函数值。
• f(DISTINCT e)与f (e)的唯一不同是，f(DISTINCT e)在得到函数值之前要删除多重集中的重复元素

eg:
SELECT MIN (Grade), AVG (Grade), MAX (Grade)
FROM SC
WHERE Cno = ‘CS302’;
//从表SC中选择课程号为CS202的课程并输出这门课程成绩的最小值，平均值和最大值

SELECT COUNT (*)
FROM SC
WHERE Cno = ‘CS102’;
//查询选择了CS102课程的学生人数

---

group by语句

• SQL语言提供了GROUP BY子句，其一般形式如下：
• GROUP BY <分组列> {,<分组列>} [HAVING <分组选择条件>]
• 其中，<分组列>是属性（可以带表名前缀），它所在的表出现在FROM子句中
• 可选的HAVING短语用来过滤掉不满足<分组选择条件>的分组，缺省时等价于HAVING TRUE
• <分组选择条件>类似于WHERE子句的查询条件，但其中允许出现聚集函数
  PS：WHERE字句中不允许出现聚集函数

SELECT Cno, AVG (Grade)
FROM SC
GROUP BY Cno;
//在表SC中以Cno为分组条件查询课程号和平均成绩，即查询每门课程的课程号和平均成绩

SELECT Sno, AVG (Grade)
FROM SC
GROUP BY Sno HAVING AVG (Grade)>85; 
//查询每个学生的平均成绩，并输出平均成绩大于85的学生学号和平均成绩

PS：对于带GROUP BY子句的SELECT语句，SELECT子句中的结果列必须是GROUP BY子句中的<分组列>或聚集函数

---

连接查询

• 查询需要的信息和查询条件涉及的属性分布在多个表中
• SQL支持多表查询，允许FROM子句中包括多个表
• 当FROM子句中包含多个表时，相当于求这些表的笛卡尔积
• 可以在WHERE子句中说明连接条件，并通过SELECT子句选取所需要的属性来实现各种连接

SELECT Cname, Grade
FROM SC, Courses
WHERE SC.Cno=Courses.Cno AND Sno = ‘201705001’
//在表SC和Courses上通过Cno连接起来，并输出学号为201705001的学生各科的课程名和成绩

SELECT Students.Sno, Sname, Grade
FROM Students, SC
WHERE Students.Sno = SC.Sno AND Cno= ‘CS202’ AND Grade>90；
//查询选修CS202课程，并且成绩在90分以上的所有学生的学号、姓名和成绩

SELECT S2.Sname
FROM Students S1, Students S2//把表Students命名为S1和S2
WHERE S1.Speciality=S2. Speciality AND 
S1.Sname=’林艳’ AND S2.Sname<>’林艳’;
//查询和林艳同一个专业的其他学生的姓名

---