《数据库系统概论》 -- 3.4 DML之数据查询
SELECT语句一般格式
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>...]
FROM <表名或视图名> [,<表名或视图名>...]|(<SELECT语句>)[AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[,<列名2>...] [HAVING <条件表达式>]]
[ORDER BY <列名3>[,<列名4>...] [ASC|DESC]];
SELECT语句注意事项
1.distinct后跟多个列，结果为多个列的一起去重后的结果
2.目标列表达式
*
<表名>.*
COUNT([DISTINCT|ALL] *)
[<表名>.]<属性列名表达式>
属性列名表达式可以是由属性列、做用于属性列的聚集函数和常量的任意算术运算(+-*/)组成的运算公式
3.聚集函数的一般格式
COUNT(*)计算时空值也计算在内，其他的不统计空值
聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句
聚集函数用于SELECT子句：对象为基本表或视图
聚集函数用于GROUP BY的HAVING子句：对象为GROUP BY分出来的组
4.WHERE子句的条件表达式可选格式
BETWEEN AND包括边界值，NOT BETWEEN AND不包括边界值，需注意如果使用between and 限定日期需要注意，如果and后的日期是到天的，那么默认为00:00:00 例如：and 后的日期为2013年3月24日，就等价于2013-3-24 00:00:00 ，那么2013-3-24 18:28:38的数据就差不到了，需要进行to_char处理。
AND的优先级大于OR
5.其他关键词：
连接查询
<表1> [<LEFT|RIGHT> OUTER] JOIN <表2> [ON <条件表达式> | USING <公共列名>]
集合查询
<SELECT查询> <UNION|INTERSECT|EXCEPT> <SELECT查询>
参加集合查询的各查询结果的列数必须相同；对应项的数据类型也必须相同
并集UNION会自动去重
并集会将两个关系整合到一个关系中
6.子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序
7.聚集函数实现子查询的效率要比直接用ANY/ALL的效率高
8.mysql的FROM子句中不支持as t(d1,d2...)，主要是不支持t(d1,d2...)
9.如果表格数据多，可以在要查找的列上建立索引
举例：（很长---------------------------------------------------------------）
单表查询
查询指定列
查询全体学生的学号与姓名
SELECT
Sno, Sname FROM Student;
查询全体学生的姓名、学号、所在系
SELECT Sname, Sno, Sdept FROM Student;
查询全部列
查询全体学生的详细记录
SELECT * FROM Student;
查询经过计算的值--算术运算
查询全体学生的姓名及其出生年份
SELECT Sname, 2017-Sage FROM Student;
查询全体学生的姓名、出生年份和所在院系，所在院系用小写字母表示
SELECT Sname, 'Year of Birth:', 2017-Sage,LOWER(Sdept) FROM Student;
<目标列表达式>别名
SELECT Sname, 'Year of Birth:' BIRTH,2017-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT from Student;
选择表中的若干元组
比较大小--逻辑运算
查询计算机科学系全体学生的名单
SELECT Sname FROM Student WHERE Sdept='CS';
查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname, Sage FROM Student WHERE Sage<20;
查询考试成绩不及格的学生的学号
SELECT DISTINCT Sno FROM SC WHERE Grade<60;
确定范围--[NOT] BETWEEN AND
查询年龄在20~23岁之间的学生姓名、系别和年龄（包括20和23）
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
确定集合--[NOT] IN
查询既不是计算科学系、数学系，也不是信息系的学生的姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ('CS','MA','IS');
字符匹配--[NOT] LIKE
查询学号为201215121的学生的详细情况
SELECT * FROM Student WHERE Sno LIKE '201215121';
查询所有姓刘的学生的姓名、学号和性别
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';
查询姓“欧阳”且全名为三个汉字的学生的姓名
SELECT Sname FROM Student WHERE Sname LIKE '欧阳_';
查询名字中第二个字为“阳”的学生的姓名和学号
SELECT Sname, Sno FROM Student WHERE Sname LIKE '_阳%';
查询所有不姓刘的学生的姓名、学号和性别
SELECT Sname, Sno,Ssex FROM Student WHERE Sname NOT LIKE '刘%';
查询DB_Design课程的课程号和学分
SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
查询以'DB_'开头，且倒数第三个字符为i的课程的详细情况
SELECT * FROM Course WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';
涉及空值的查询--IS [NOT] NULL
查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
查询所有有成绩的学生学号和课程号
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
多重条件查询--<AND|OR|NOT>
查询计算机科学系年龄在20岁以下的学生姓名
SELECT Sname FROM Student WHERE Sdept='IS' AND Sage<20;
AND的优先级大于OR
元组排序--ORDER
BY
查询选修了3号课程的学生的学号及其成绩，结果按分数降序排列
SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;
默认升序ASC，降序用DESC表示
查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按其年龄降序排列
SELECT * FROM Student ORDER BY Sdept, Sage DESC;
聚集函数
元组分组--GROUP BY [HAVING]
查询各个课程号及相应的选课人数
SELECT Cno,Count(Sno) FROM SC ORDER BY Cno;
查询选修了三门以上的课程的学生序号
SELECT Sno FROM SC ORDER BY Sno HAVING COUNT(Cno)>=3;
查询平均成绩大于90分的学生的学号和他的平均成绩
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>90;
多表查询--连接查询
查询每个学生及其选课课程的情况（等值连接查询）保留重复列
SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;
查询每个学生及其选课课程的情况（自然连接查询）去除重复列
SELECT Student.Sno,Sname,Ssex,Ssage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno;
查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno='2' AND Grade>90;
自身连接
查询每一门课的间接先修课（即先修课的选修课）
SELECT C1.Cno,C2.Cpno FROM Course C1,Course C2 WHERE C2.Cno=C1.Cpno;
外连接--[LEFT|RIGHT] [OUTER] JOIN [ON <条件表达式>|USING <列名>]
查询每个学生及其选修课程的情况
SELECT Student.Sno,Sname,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
SELECT [Student.]Sno,Sname,Cno,Grade FROM Student LEFT OUTER JOIN SC USING (Sno);
多表连接
查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno;
查询过程：先进行两个表的连接，再将其结果与第三个表进行连接
嵌套查询
带有IN谓词的子查询
不相关子查询
查询与“刘晨”在同一个系学习的学生
SELECT Sname From Student WHERE Sdept IN
(SELECT Sdept FROM Student WHERE Sname='刘晨');
不相关子查询
查询选修了“信息系统”的学生学号和姓名
SELECT Sno,Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno IN
(SELECT Cno FROM Course WHERE Cname='信息系统'));
带有比较运算符的子查询（子查询返回单值）
相关子查询
找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno,Cno FROM SC SC1 WHERE
Grade>=(SELECT AVG(Grade) FROM SC SC2 WHERE SC2.Sno=SC1.Sno);
带有ANY(SOME)或ALL谓词的子查询（子查询返回多值）--
<ANY|SOME>
查询非计算机科学系中，比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM Student WHERE Sage<ANY(
SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept!='CS';
查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名
SELECT Sname From Student WHERE Sage<ALL(
SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept!='CS';
带有EXISTS谓词的子查询--[NOT] EXISTS
查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student WHERE EXISTS (
SELECT * FROM SC WHERE Sno=Student.Sno AND Cno ='1');
查询选修了全部课程的学生姓名
拿所有课程去这个学生的选修课程里面对，不存在没有的，即可
SELECT Sname FROM Student WHERE NOT EXISTS (
(SELECT * FROM Course WHERE Cno NOT IN (
SELECT Cno FROM SC WHERE Sno=Student.Sno)));
查询至少选修了学生201215122选修的全部课程的学生号码
拿201215122选秀的全部课程去每个学生选修的课程里面对，如果每个都有，则返回该学生的号码
SELECT Sno FROM Student WHERE NOT EXISTS (
SELECT Cno FROM SC WHERE Sno='201215122' AND Cno NOT IN (
SELECT Cno FROM SC WHERE Sno=Student.Sno
));
集合查询--<UNION|INTERSECT|EXCEPT>
查询计算机科学系的学生及年龄不大于19岁的学生
SELECT * FROM Student WHERE Sdept='IS'
UNION
SELECT * FROM Student WHERE Sage<=19;
查询选修了课程1或者选修了课程2的学生
SELECT Sno FROM SC WHERE Cno='1'
UNION
SELECT Sno FROM SC WHERE Cno='2';
查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT * FROM Student WHERE Sdept='IS'
INTERSECT
SELECT * FROM Student WHERE Sage<=19;
查询计算机科学系的学生与年龄不大于19岁的学生的交集
SELECT * FROM Student WHERE Sdept='IS'
INTERSECT
SELECT * FROM Student WHERE Sage<=19;
查询计算机科学系的学生与年龄不大于19岁的学生的差集
SELECT * FROM Student WHERE Sdept='IS'
EXCEPT
SELECT * FROM Student WHERE Sage<=19;
基于派生表的查询--<select查询> AS <别名>
SELECT子查询出现在FROM子句中，该子查询称为临时派生表
找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno FROM SC, (SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno ) AS Avg_sc(avg_sno,avg_grade)
WHERE Sno=avg_sno AND Grade>=avg_grade;
注：mysql中写法：mysql不支持as t(d1,d2...)
SELECT Sno,Cno FROM SC, (SELECT Sno avg_sno, AVG(Grade) avg_grade FROM SC GROUP BY Sno ) AS Avg_sc
WHERE Sno=avg_sno AND Grade>=avg_grade;* FROM Student;
