21/7/26 读书笔记 SQL数据查询

21/7/26 读书笔记

目录

• 21/7/26 读书笔记
  • 数据库系统概论 SQL数据查询
    • 单表查询
      • 获取表中若干列中所有对象
      • 按条件获取表中的若干对象
      • 查询结果排序
      • 聚集查询
    • 多表查询（连接查询）
      • 等值与非等值连接
      • 自连接
      • 外连接
    • 嵌套查询
      • 不相关子查询
      • 相关子查询
        • EXISTS谓词
      • 在子查询的结果上执行比较
    • 集合查询
    • 派生表

数据库系统概论 SQL数据查询

SQL数据查询功能主要依赖SELECT……FROM……的语法结构来执行。我们可以将一条查询语句分割为多条子句，包括：

• SELECT子句：指明目标列表达式
• FROM子句：指明数据来源的基本表、视图、派生表
  • FROM中指明的名称，在当做数据来源分析时看做表（即元组集合的名称），在实际语句执行分析中可以看做来自表中的一个元组
• WHERE子句（可选）：指明选取的条件表达式
• GROUP BY子句（可选）：将查询结果按某一列或多列的值进行分组，常搭配聚集函数
• ORDER BY子句（可选）：指明查询结果按某一列或多列的值以何种顺序排序

对于举例说明，我们依旧沿用昨天的学生管理系统的例子：

• Student(Sno,Sname,Ssex,Sage,Sdept)
• Course(Cno,Cname,Cpno,Credit)
• SC(Sno,Cno,Grade)

接下来我们基于具体场景进行查询语句的介绍：

单表查询

对一个表进行操作，而不涉及多表。

获取表中若干列中所有对象

SELECT Sno,Sname NAME,LOWER(Sdept),100-Sage
FROM Student;

基于此例介绍SELECT子句的一些规则：

• 各列的次序任意
• 采用<列名> <别名>来指定返回的数据表中的列名，比如Sname NAME。
• 允许采用函数和表达式来对列进行操作，比如LOWER(Sdept),100-Sage

按条件获取表中的若干对象

首先是SELECT中的DISTINCT短语指定返回的元组不会重复

SELECT DISTINCT Sno
FROM Student;

然后是利用WHERE子句来设置查询条件，包括：

• 比较：各种比较运算符

• 确定范围：BETWEEN……AND……短语

• 确定集合：IN短语

  • 利用(元素1,元素2,……)表示集合:

    SELECT Sname
    FROM Student
    WHERE Sage IN (15,18);
    

• 字符匹配：LIKE短语

  • 利用'%'匹配任意长度（可以为0）字符串，利用'_'匹配任意一个字符。注意字符集会影响汉字的字符长度！

    SELECT Sno
    FROM Student
    WHERE Sname LIKE '%明'; # 选择姓名最后一个字是'明'的记录
    

  • 利用ESCAPE短语设置换码字符：

    SELECT Sno
    FROM Student
    WHERE Sname LIKE 'j%明' ESCAPE 'j'; # 选择姓名包含%明的记录，其中指定j为换码字符，因此j%被解释为%而不是任意长度字符串
    

• 空值判断：NULL短语

• 多重逻辑条件组合：AND、OR、NOT短语

查询结果排序

利用ASC和DESC指定排序顺序

SELECT Sno,Grade
FROM SC
ORDER BY Grade DESC;

聚集查询

SQL提供的聚集查询功能够方便地对多个元组进行操作，比如求取元组数量、对某一列进行求和等统计操作，具体提供的聚集函数包括：

• COUNT计数、SUM求和、AVG平均值、MAX最大值、MIN最小值

聚集函数均需要指定列，并且可以嵌入DISTINCT短语指定是否考虑重复元组。

聚合函数除了COUNT(*)对所有元组计数时，均忽略空值。

SELECT AVG(Grade)
FROM SC
GROUP BY Cno; # 求取每门课（按Cno分组）的平均分，执行顺序是先执行分组，再对每组执行聚合函数

聚合函数只能在SELECT子句和GROUP BY的HAVING子句中出现。

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) > 90; # 求取所有课程的总平均分高于90的学生，先分组聚合，再利用HAVING语句筛选满足要求的记录

WHERE子句作用于表，而HAVING子句作用于组，这是它们最为本质的区别。因此不能使用WHERE来进行上述场景的筛选！

多表查询（连接查询）

在连接查询中，FROM子句的对象是多个表。

等值与非等值连接

在等值连接的情况下，如果等值的目标列在两个表中重名，那么在SELECT语句中可以只写一次（因为查询结果的该列在两表中的值肯定是一致的），但是在其他地方需要额外以<表名>.<列名>指明来源

SELECT Sno,Cno,Sname
FROM Student,SC
WHERE Student.Sno = SC.Sno AND Sname = 'Norton' AND Cno IN ('OS','OO'); # 两表连接，找到Norton的OS和OO成绩

自连接

连接可以在一个表内进行，比如课程表中的一个课程的先修课程本身也来自于课程表。

SELECT A.Cno, B.Cpno
FROM Course A,Course B
WHERE A.Cno = B.Cpno; # 获取各个课程的先修课程关系

这里我们为Course表指定了两个别名，并利用别名实现了区分。

外连接

外连接中有左右表之分，使得没有匹配连接的记录也能呈现在查询结果中（为空值）。

SELECT Student.Sno Cno
FROM Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno);

这是利用关系代数实现的，可看做先求取了Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno)，再从中进行SELECT子句的执行。

嵌套查询

嵌套查询使得用户能够将多个简单查询组合在一起形成一条语句。通常来说，称一个完整的SELECT-FROM-WHERE为一个查询块，而查询块能够内嵌在另一个查询块的WHERE或HAVING子句中。上层查询块称为父查询，下层查询块称为子查询。

子查询中不能使用ORDER BY，该子句应该只在最终查询结果中使用。

不相关子查询

不相关子查询指子查询不需要父查询给予信息，因此在整个查询过程中只执行一次即可，比如：

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN 
(SELECT Sdept 
FROM Student 
WHERE Sname = 'Norton'); # 先在子查询中找到Norton的专业X，然后查询所有专业是X的学生

这里我们的子句只需要执行一次就能获得专业X，然后利用专业X去查询。

子查询通常可以用连接来实现，而且通常地，连接方式会提高效率，因此利用不同语句方式来提高效率称为数据库性能调优。

比如上例可以看成：

SELECT S1.Sno, S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept = S2.Sdept AND S2.Sname = 'Norton';

相关子查询

相关子查询中需要用到父查询的信息，比如

SELECT Sno,Cno
FROM SC x
WHERE x.Grade >=
(SELECT AVG(Grade)
FROM SC y
WHERE x.Sno=y.Sno);

这条语句大概会以下列方式执行：

1. 从SC中取出元组x
2. 利用x.Sno去SC中找到所有y.Sno=x.Sno的记录，通过聚合函数得到平均分
3. 对比x.Grade和平均分，执行父查询的WHERE子句的筛选

因此相关子查询会对父查询中每条语句都执行一次。

EXISTS谓词

EXISTS谓词修饰的子查询，只返回布尔值，且肯定是相关子查询。EXISTS谓词表示一个子查询是否得到了结果。用途主要在于筛选父查询中记录，比如：

SELECT Sname
FROM Student
WHERE NOT EXISTS 
(SELECT *
FROM SC
WHERE Sno = Student.Sno); # 查询那些一个课都没上的学生

EXISTS谓词修饰的SELECT子句的对象通常是*，因为子查询返回值是布尔值，而与实际列无关

在子查询的结果上执行比较

SQL没有定义比较运算在集合上的行为。

当子查询返回一个集合时，如果要执行比较，比如WHERE中指定父查询结果的某个属性比子查询中任何一个数都大，则有两种方式：

• 在子查询中利用聚合函数先求出最大值，然后返回到父查询利用比较运算符

• 利用ALL或ANY谓语修饰符，将子查询返回的结果修饰后进行对比

  SELECT Sname,Sage
  FROM Student A
  WHERE A.Sage >= ANY
  (SELECT Sage
  FROM Student B
  WHERE Sdept='CS')
  AND Sdept <> 'CS';# 子查询中得出所有CS专业学生的年龄，然后父查询找出非CS专业学生中年龄大于所有CS学生的记录
  

  通常来说，聚合函数比ANY或ALL效率更高

集合查询

集合查询是指元组集合的交（UNION）、并（INTERSECT）、差（EXCEPT）。其操作对象是多个查询结果，注意查询结果的列数和对应的数据类型必须相同！

SELECT *
FROM Student
WHERE Sage<19
UNION
SELECT *
FROM Student
WHRER Sdept='CS'; # 查询Student中属于CS专业或小于19岁的同学

通常来说，集合查询用于同构的表中进行查询，而同一张表中则只利用逻辑运算符即可。

派生表

因为SQL操作对象和操作结果都是表，因此可以将原查询语句中的一个表，表示为一条查询语句，这种表就是派生表

SELECT Sno, Cno
FROM SC,
(SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno) AS Avg_SC(Sno,Avg_Grade)
WHERE SC.Sno = Avg_SC.Sno AND SC.Grade >= Avg_SC.Avg_Grade; # 找出每个学生在哪些课程中获得的分数高于自己平均分

利用AS可以给派生表指定表名和各列属性名。