数据库系统原理:第三章关系数据库语言(二)

数据库系统原理

关于数据查询的知识点。

数据查询

选择查询

格式:
SELECT [ ALL | DISTINCT ] <目标列表达式>,<目标列表达式> …
FROM <表名或视图名>, <表名或视图名> …
WHERE <条件表达式>
GROUP BY <列名>
HAVING <条件表达式>
ORDER BY < ASC | DESC >

  • SELECT:指定要显示的属性列,相当于“投影”操作
  • FROM:指定查询对象
  • WHERE:指定查询条件
  • GROUP BY:对查询结果按指定列的值分组,该列中属性值相等的元组并为一个组,即去除重复值
  • HAVING:在有GROUP BY的情况下才能使用,等效于WHERE,但WHERE中无法使用“MAX”之类的聚集函数,HAVING中可以
  • ORDER BY:对查询结果按指定列值的升序或降序排序
  • 若WHERE和HAVING同时存在,先执行WHERE,对WHERE得到的值进行GROUP BY分组,再根绝HAVING 条件进行筛选
  • ALL:选择所有值
  • DISTINCT:去掉所有重复值

SELECT

【例1】查询全体学生的学号与姓名

SELECT Sno, Sname
FROM Student;

【例2】查询全体学生的详细记录(全部信息)
全部信息用*代替

SELECT *
FROM Student;

SELECT子句的<目标列表达式>还可以是表达式
【例3】查询全体学生的姓名及其出生年份

SELECT Sname, 2020-Sage
FROM Student;

WHERE

常用的查询条件

查询条件 谓词
比较 =, >, <, >=, <=, !=, <>, !>, !<; NOT + 上述比较运算符
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN,NOT IN
字符匹配 LIKE, NOT LIKE
空值 IS NULL, IS NOT NULL
多重条件 AND, OR, NOT

比较

【例4】查询计算机科学系全体学生的名单

SELECT Sname
FROM Student
WHERE Sdept="CS"

【例5】查询所有年龄在20岁以下的学生姓名及其年龄

SELECT Sname, Sage
FROM Student
WHERE Sage<20;

【例6】查询考试成绩有不及格的学生的学号

SELECT DISTINCT Sno
FROM SC
WHERE Grage<60;

确定范围
【例7】查询年龄在20-23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄

SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

//等效于

SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage >=20 AND Sage<=23;

确定集合
【例8】查询计算机科学系CS、数学系MA和信息系IS学生的姓名和性别

SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');

//等效于

SELECT Sname, Ssex
FROM Student
WHERE Sdept = 'CS' OR Sdept = 'MA' OR Sdept = 'IS';

字符匹配
格式:
[ NOT ] LIKE ‘<匹配字符串>’ [ ESCAPE ‘<转义字符>’]

  • 匹配字符串:可以是完整字符串也可以是带有通配符%_的字符串
    • %:代表任意长度(也可以是0)的字符串
    • _:代表单个字符
  • ESCAPE:之后的字符作为转义字符
    • 例如ESCAPE '\'就说明'\'用作转义字符

【例9】查询所有姓刘学生的姓名、学号和性别

SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';

【例10】查询以“DB_”开头,且倒数第3个字符为i的课程的详细信息

SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\';

涉及空值的查询
【例11】查询缺少成绩的学生的学号和相应的课程号

SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;

其中 “IS” 不能用 “=” 代替

多重条件查询

  • AND的优先级高于OR
  • 可以用括号改变优先级

ORDER BY子句

可以按一个或多个属性列排序

【例12】查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列

SELECT *
FROM Student
ORDER BY Sdept, Sage DESC;

聚集函数

  • 统计元组个数
    COUNT(*)
  • 统计一列中值得个数
    COUNT( [ DISTINCT | ALL ] <列名> )
    DISTINCT说明统计得是不同得值
  • 计算一列值得总和(数据类型必须为数值型)
    SUM( [ DISTINCT | ALL ] <列名> )
  • 计算一列值得平均值(数据类型必须为数值型)
    AVG( [ DISTINCT | ALL ] <列名> )
  • 求一列中的最大值和最小值
    MAX( [ DISTINCT | ALL ] <列名> )
    MIN( [ DISTINCT | ALL ] <列名> )

【例13】查询学生总人数

SELECT COUNT(*)
FROM Student;

【例14】查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno)
FROM SC;

【例15】计算1号课程的学生平均成绩

SELECT AVG(Grade)
FROM SC
WHERE Cno="1";

【例16】查询学生201215012选修课程的总学分数

SELECT SUM(Ccredit)
FROM SC, Course
WHERE Sno="201215012" AND SC.Cno=Course.Cno;

GROUP BY

细化聚集函数的作用对象

  • 如果未对查询结果分组,聚集函数将作用于整个查询结果
  • 对查询结果分组后,聚集函数将分别作用于每个组
  • 按指定的一列或多列值分组,值相等的为一组

【例17】求各个课程号及相应的选课人数

SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;

那么输出结果可能为:

Cno COUNT(Sno)
1 22
2 34
3 44
4 33
5 15

连接查询

一般格式:
<表名1>.<列名1> 比较运算符 <表名2>.<列名2>

等值与非等值连接查询(自然连接)

自然连接

WHERE Student.Sno = SC.Sno;

【例18】查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

SELECT Student, Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND SC.Cno = '2' AND SC.Grade > 90;

自身连接
因为是自身与自身连接,所以所有的属性名都是同名属性,因此必须使用别名前缀进行区分。

【例19】查询每一门课的直接先修课的名称

SELECT FIRST.Cname, SECOND.Cname
FROM Course As FIRST, Course As SECOND     /*取别名为FIRST和SECOND*/
WHERE FIRST.Cpno = SECOND.Cno        /*Cpno为先修课*/

外连接
通过外连接来实现“连接”操作中的悬浮元组

  • 左外连接
    • 列出左边关系中的所有元组,即保留左侧悬浮元组
  • 右外连接
    • 列出右边关系中的所有元组,即保留右侧悬浮元组

【例20】

SELECT Student.Sno, Sname, Cno, Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno = SC.Sno);

多表连接
即使用多个自然连接

【例21】查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno

嵌套查询

  1. 查询块:一个SELECT-FROM—WHERE 语句
  2. 嵌套查询:一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句中
  3. 外层查询/父查询:上层/外层的查询块
  4. 内层查询/子查询:下层/内层的查询块
SELECT Sno, Sname, Sdept        /*外层查询/父查询*/
FROM Student
WHERE Sdept IN
            (
                SELECT Sdept    /*内层查询/子查询*/
                FROM Student
                WHERE Sname='刘晨'
            );

分类

  • 不相关查询:子查询的查询条件不依赖于夫查询
    由里向外逐层处理。即每个子查询可以独立完成查询,子查询的结果用于建立其父查询的查找条件
    执行过程:
    (1)执行子查询,其结果不被显示,而是传递给外部查询,作为外部查询的条件使用。
    (2)执行外部查询,并显示整个结果
  • 相关查询:子查询的查询条件依赖于父查询
    子查询不能独立完成查询,多数情况下是子查询的WHERE子句中引用了外部查询的表。
    执行过程:
    (1)从外层查询中取出一个元组,将元组相关列的值传给内层查询。
    (2)执行内层查询,得到子查询操作的值。
    (3)外查询根据子查询返回的结果或结果集得到满足条件的行。
    (4)然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。

【示例】查询Booka表中大于该类图书价格平均值的图书信息

SELECT 图书名,出版社,类编号,价格 
FROM Books As a
  WHERE 价格 >
  (
    SELECT AVG(价格)
    FROM Books AS b
    WHERE a.类编号=b.类编号
  )

这里的子查询中用到了父查询的表a,所以依赖于父查询中的数据,不能独立执行

参考自 https://blog.csdn.net/shiyong1949/article/details/80923083

带有IN谓词的子查询

【例22】查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno, Sname                           /*(三)最后在Student关系中取出Sno和Sname*/
FROM Student
WHERE Sno IN
    (
        SELECT Sno                          /*(二)然后在SC关系中找出选修了3号课程的学生学号*/
        FROM SC
        WHERE Cno IN
            (
                SELECT Cno                  /*(一)首先在Course关系中找出“信息系统的课程号”*/
                FROM Course
                WHERE Cname = '信息系统'
            )
    )   

带有比较运算符的子查询

【例23】找出每个学生超过他选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC As x
WHERE Grade >=                         /*这是相关子查询*/
    (
        SELECT AVG(Grade)
        FROM SC As y
        WHERE y.Sno = x.Sno
    );

带有ANY(SOME)或ALL谓词的子查询

ANY——存在量词
ALL——全称量词

使用ANY或ALL谓词时必须同时使用比较运算,例如
> ANY     大于子查询结果中的某个值
> ALL     大于子查询结果中的所有值

【例24】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

SELECT Sname, Sage
FROM Student
WHERE Sdept != 'CS' AND Sage < ANY
    (
        SELECT Sage
        FROM Student
        WHERE Sdept = 'CS'
    ); 

/*也可以用聚集函数来实现*/

SELECT Sname, Sage
FROM Student
WHERE Sdept != 'CS' AND Sage <
    (
        SELECT MAX(Sage)
        FROM Student
        WHERE Sdept = 'CS'
    ); 

ANY,ALL谓词与聚集函数、IN谓词的等价转换关系

= <>、!= < <= > >=
ANY IN \ <MAX <=MAX > MIN >=MIN
ALL \ NOT IN <MIN <=MIN >MAX >=MAX

带有EXISTS谓词的子查询

  • 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”
    • 若内层查询结果非空,则外层的WHERE子句返回真值
    • 若内层查询结果为空,则外层的WHERE子句返回假值
  • 由EXISTS引出的子查询,其目标列表达式通常都用*,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。

【例25】查询所有选修了1号课程的学生姓名

SELECT Sname
FROM Student
WHERE EXISTS
    (
        SELECT *
        FROM SC
        WHERE Sno=Student.Sno AND Cno='1'
    );

上述代码执行思路:
从Student中提取第一个元组,带入子查询中,若结果不为空,则返回真值,说明该学生选修了1号课程,输出,再读取下一个值。

用EXISTS实现全称量词
EXISTS表示存在,任意全称量词都可以转换成等价的带有存在量词的谓词:
xP¬x¬p(\forall x)P \equiv \lnot(\exists x (\lnot p))

用EXISTS实现逻辑蕴含
pq¬pqp \to q \equiv \lnot p \bigvee q

【例26】查询所有选修了学生201215122选修的所有课程的学生的学号
p:学生201215122选了课程y
q:学生x选了课程y
那么就是只要学生201215122选了,学生x就也选修了该门课
得到逻辑关系为(y)pq(\forall y)p \to q
对逻辑关系进行等价变换:
(y)pq¬(y(¬(pq)))(\forall y) p \to q \equiv \lnot (\exists y (\lnot ( p \to q)))
¬(y(¬(¬pq)))\equiv \lnot (\exists y (\lnot (\lnot p \bigvee q)))
¬y(p¬q)\equiv \lnot \exists y (p \bigwedge \lnot q)
得到可以用EXISTS表示的式子,语义为不存在这样的课程y,学生201215122选修了,而学生x没有选

SELECT DISTINCT SCX.Sno
FROM SC As SCX
WHERE NOT EXISTS
    (
        SELECT *
        FROM SC As SCY
        WHERE SCY.Sno = '201215122' AND NOT EXISTS
            (
                SELECT *
                FROM SC As SCZ
                WHERE SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno
            )
    );