SQL的数据查询(2)

SQL的数据查询(2)

数据查询

• 单表查询
• • 选择表中的若干列
  • • 查询指定列
    • 查询全部列
    • 查询经过计算的值
  • 选择表中的若干元组
  • • 消除重复的行（DISTINCT）
    • 查询满足条件的元组
    • • 比较大小
      • 确定范围（BETWEEN…AND）
      • 确定集合（IN）
      • 字符匹配[NOT]LIKE<>[EXCAPE<>]
      • 涉及空值的查询
      • 多重条件查询（AND、OR）
    • ORDER BY语句
    • 聚集函数
    • GROUP BY语句
• 连接查询
• • 等值与非等值连接查询
  • 自身连接
  • 外连接
  • 多表连接
• 嵌套查询（子查询）
• • 带有IN谓词的子查询
  • • 不相关子查询
  • 带有比较运算符的子查询
  • • 相关子查询
  • 带有ANY（SOME）或ALL谓词的子查询
  • 带有EXISTS谓词的子查询
• 集合查询
• • 并操作UNION
  • 交操作INTERSECT
  • 差操作EXCEPT
• 基于派生表的查询

单表查询

以学生—课程数据库为例
student

学号（Sno）	姓名（Sname）	性别（Ssex）	年龄（Sname）	所在系（Sdept）
201215121	李勇	男	20	CS
201215122	刘晨	女	19	CS
201215123	王敏	女	18	MA
201215125	张立	男	19	IS

Course

课程号(Cno)	课程名(Cname)	先行课(Cpno)	学分(Ccredit)
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

Sc

学号（Sno）	课程号 （Cno）	成绩（Grade）
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80

基本的格式

--基本的数据查询格式
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ...
FROM <表名或视图名> [,<表名或视图名>..] |(<SELECT语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]] ;

选择表中的若干列

查询指定列

--从学生总获得学号和名字
SELECT Snamm,Sno
FROM Student;
--处理顺序如下：
--从student表中取出元组，取出该元组的Sno和Sname的值，形成一个新的元组
--再依此对所有的元组进行遍历

查询全部列

--查询Student上的所有的列
SELECT*
FROM Student;

查询经过计算的值

SELECT <目标列表达式> 中SELECT后面不仅可以是属性也可以是表达式

--在获得的结果中可以得到2014-Sage（作为属性名）得结果
--旨在获得学生们得出生日期
SELECT Sname，2014-Sage
FROM Student；

选择表中的若干元组

消除重复的行（DISTINCT）

SELECT Sno
FROM SC；
--此时会获得SC中所有Sno属性中所有得值（包括重复学号）

SELECT DISTINCT Sno
FROM SC；
--不会显示重复得学号

查询满足条件的元组

比较大小

比较运算符：= ， > ， < ，>= ，<= ，!=或<>，!>（不大于），!<（不小于）

--获得年龄小于二十岁的学生得姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage<20;

确定范围（BETWEEN…AND）

确定范围：BETWEEN…AND，NOT BETWEEN…AND

--获得年龄在20至30岁之间得学生得名字
SELECT Sname
FROM Student
WHERE Sage BETWEEN 20 AND 30;

确定集合（IN）

确定集合： IN

--获得在CS，MA，IS学院得学生得名字
SELECT Sname
FROM Student
WHERE Sdept IN('CS','MA','IS');

字符匹配[NOT]LIKE<>[EXCAPE<>]

字符匹配：LIKE‘<匹配串>’
<匹配串>中可以是完整的字符串
也可以是通识符%和 _
%： 代表任意长度的字符串。eg：a%b，可以是以a开头b结尾的任意字符
_ ：代表任意的单个字符。eg：a_b，以a开b结的三字母字符串

--从student表中查询学号为201215121的学生的所有信息
SELECT*
FROM Student
WHERE Sno LIKE '201215121';
--匹配串为完整的字符串

--获得学生表中张姓学生的所有信息
SELECT*
FROM Student
WHERE Sname LIKE '张%';

--获得学生表中某三二字学生的所有信息
SELECT*
FROM Student
WHERE Sname LIKE '_三';

EXSCAPE<换码字符>

--查询DB_为开头的课程名
SELECT Cno，Credit
FROM Course
WHERE Cname LIKE'DB_%i_' ESCAPE'';
--前一个_用转义字符为普通的_

涉及空值的查询

IS NULL或者 IS NOT NULL
用于替代在表格中对空值比较时=和！=的功能

--获得成绩为空时学生的学号
SELECT Sno
FROM SC
WHERE Grade IS NULL;
--Grade为空时

多重条件查询（AND、OR）

AND的优先级要大于OR

--获得计算机科学系年龄在20岁以下的学生姓名
SELECT Sname
FROM Student
WHERE Sdept ='CS' AND Sage < 20;

ORDER BY语句

[ORDER BY <列名> [ASC|DESC]]
ASC为升序 （含空值的元组最后显示）
DESC为降序（含空值的元组最先显示）

--按所在系的系号升序排序，同一系中的学生按年龄降序？？？？？？
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

聚集函数

这里是引用
COUNT(*)—— 统计元组个数
COUNT( [DISTINCTI|ALL] <列名>) ——统计一列中值的个数
SUM([DISTINCTIALL] <列名>) ——计算- -列值的总和(此列必须是数值型)
AVG( [DISTINCT[ALL] <列名>)——计算一列值的平均值(此列必须是数值型)
MAX( [DISTINCT|ALL] <列名>) ——求一列值中的最大值
MIN(DISTINCT|ALL] <列名>) ——求一列值中的最小值

--查询学生的个数
SELECT COUNT(*)
FROM Student;

聚集函数遇见空值时，除了COUNT（*），其他都直接跳过空值不处理
聚集函数只能用于SELECT子句和GROUP BY中的HAVING语句中，WHERE语句不可用

GROUP BY语句

GROUP BY 属性名：根据属性列的内容将值相等的分到同一组

--查询平均成绩大于等于90分的学生学号和平均成绩
SELECT Sno,AVG(Grade) 
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >=90;
--可以从中间两行先开始看，先获得一个新的表格再按惯例看

连接查询

两个表以上

等值与非等值连接查询

[表名.]<列名> <比较运算符>[表名.]<列名>
比较运算符为=时，为等级连接，其他运算符则为非等值连接
[表名.]<列名> BETWEEN[表名.]<列名>AND [表名.]<列名>

--查询学生的学修课程情况
SELECT Student.*,SC.*
--加上表名前缀，是为了避免混淆（如果属性名在连接的各表中都是唯一的di一个，则可以省略）
FROM Student, SC
WHERE Student.Sno =SC.Sno
--寻找的顺序为：
--在student中找到第一个元组，然后扫描SC表，有符合条件的进行拼接形成新的元组
--再在student中遍历依此循环得到一个新的表格

如果在SC与Sno中建立索引的话，就无需每次都遍历Sc表
若是将重复的属性列取消掉则为自然连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
--后面那写些属性名都单独存在，无需加上表名前缀
FROM Student,Sc
WHERE Student.Sno =Sc.Sno;

自身连接

表与自身连接

--将Course的表别名为first和second
SELECT FIRSI.Cno,SECOND.Cpno
FROM Course FIRST, Course SECOND
WHERE FIRST.Cpno =SECOND.Cno;

外连接

在以上的连接中，只有满足连接条件的才会作为结果输出，如在Student.Sno =Sc.Sno中，只会输出两个学号的学生的信息。可是想把每一个学生的情况都输出，即使后面的属性上填上NULL。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
From Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno);
--可以用USING来去掉结果中的重复性：
--From Student LEFT OUTER JOIN SC USING (Student.Sno = SC.Sno);

多表连接

两个以上的表

--查询每个学生的学号、姓名、选修的课程名和成绩
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Smo AND SC.Cno = Course.Cno
--执行方式可能为先将Student和Sc表进行连接，然后再将结果与Course连接

嵌套查询（子查询）

子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终的查询结果排序。

带有IN谓词的子查询

不相关子查询

--查询与刘晨在同一系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN(SELECT SdeptFROM StudentWHERE Sname ='刘晨'	);
--在其中先执行子查询再执行父查询

SELECT Sno,Sname
FROM Student
WHERE Sno IN(SELECT SnoFROM SCWHERE Cno IN(SELECT CnoFROM CourseWHERE Cname ='信息系统'）);

相比于连接运算，商用关系数据库对嵌套查询的优化做的还不够完善，所以在实际中能够用连接运算表达的查询尽可能采用连接运算

带有比较运算符的子查询

比较运算符，><=!等

相关子查询

--找出每个学生超过他自己选修课平均成绩的课程号
SELECT Sno,Cno
FROM SC x	--x为元组变量 会在SC选一个元组进入子运算
WHERE Grade>=(SELECT AVG(Grade)FROM SC y  --同理y也是元组变辆，在SC中遍历学号相同的学生WHERE y.Sno =x.Sno);

带有ANY（SOME）或ALL谓词的子查询

.>ANY .----------大于子查询结果中的某个值
.>ALL ------------大于子查询结果中的所有值
< ANY -----------小于子查询结果中的某个值
< ALL ------------小于子查询结果中的所有值
.>=ANY ---------大于等于子查询结果中的某个值
.>=ALL ----------大于等于子查询结果中的所有值
<=ANY ----------小于等于子查询结果中的某个值
<=ALL -----------小于等于子查询结果中的所有值
=ANY ------------等于子查询结果中的某个值
=ALL -------------等于子查询结果中的所有值(通常没有实际意义)
!= (或<>) ANY -不等于子查询结果中的某个值
!= (或<>) ALL --不等于子查询结果中的任何-一个值

--查询非计算机科学系中比计算机科学系所有学生年龄都小的学生的姓名和年龄
SELECT Sname, Sage
FROM Student
WHERE Sage < ALL(SELECT SageFROM StudentWHERE Sdept ='CS')AND Sdept<>'CS';

--查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生的姓名和年龄
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY(SELECT SageFROM StudentWHERE Sdept ='CS')AND Sdept<>'CS';

带有EXISTS谓词的子查询

EXISTS只产生逻辑真值”ture“或逻辑假值”FALSE“

--查询选修所有门课的学生
--由于没有全称谓词，可以反过来成没有一门不选的学生
SELECT Sname
FROM Student --1、选一名学生下放
WHERE NOT EXISTS	(SELECT *FROM Course --2、选一门课程下放WHERE NOT EXISTS --如果子查询成立则返回FALSE，否则返回TURE(SELECT *   FROM SCWHERE Sno =Student.Sno  --3、将学生的学号在Sc中进行遍历以及课程AND	Cno =Course.Cno)); --进行比较--重复1到2内循环到3内循环遍历

集合查询

参加集合查询的各查询结果的列数必须相等；对应项的数据类型也必须相等

并操作UNION

--查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept ='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19

系统会自动去掉重复的元组。如果想要保留可以用UNION ALL

交操作INTERSECT

--查询计算机科学系中年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept ='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19

差操作EXCEPT

--查询计算机科学系中年龄大于19岁的学生
SELECT *
FROM Student
WHERE Sdept ='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19

基于派生表的查询

子查询还可以出现在FROM语句中，此时子查询变成临时的派生类

--找出每个学生超过他自己的选修课平均成绩的课程号
SELECT Sno,Cno
FROM SC,(SELECT Sno,Avg(Grade)FROM SC GROUP BY Sno)AS Avg_sc(avg_sno,avg_grade) --Avg_sc为一个承载派生表的新表WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade

子查询中没有聚集函数，派生表可以不指定属性列，SELECT之后的列名为默认属性名
FROM子句生成派生类时，AS关键词可以省略，但必须给派生类指定一个别名。

——

全文参考高等教育出版社《数据库系统概论 第5版》