关系数据库标准语言SQL

数据查询

标准语句：

 SELECT [ALL默认|DISTINCT去重] <目标列表达式>[,<目标列表达式>]
 FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)[AS]<别名>
 [ WHERE <条件表达式> ]
 [ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
 [ ORDER BY <列名2> [ ASC|DESC ] ];

• SELECT子句：指定要显示的属性列

• FROM子句：指定查询对象（基本表或视图）

• WHERE子句：指定查询条件

• GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。

• HAVING短语：只有满足指定条件的组才予以输出

• ORDER BY子句：对查询结果表按指定列值的升序或降序排序

单表查询

LOWER()：输出小写

'':输出固定值

取别名：

WHERE条件语句

常用的查询条件：

确定范围：

BETWEEN … AND …

NOT BETWEEN … AND …

[例3.25] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

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

确定集合

IN <值表>, NOT IN <值表>

[例]查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

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

[例]查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。

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

字符匹配

[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

% （百分号） 代表任意长度（长度可以为0）的字符串

例如a%b表示以a开头，以b结尾的任意长度的字符串

_ （下横线） 代表任意单个字符。

例如a_b表示以a开头，以b结尾的长度为3的任意字符串

关键字为LIKE:

 SELECT *    
 FROM  Student  
 WHERE Sno LIKE ‘201215121';

使用换码字符将通配符转义为普通字符:

[例3.34] 查询DB_Design课程的课程号和学分。

 SELECT Cno，Ccredit
 FROM     Course
 WHERE  Cname LIKE 'DB\_Design' ESCAPE '\ ' ;

[例3.35] 查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

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

涉及空值的查询

IS NULL 或 IS NOT NULL

多重条件查询

AND和OR来连接多个查询条件

• AND的优先级高于OR

• 可以用括号改变优先级

ORDER BY子句

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

• 升序：ASC;降序：DESC;缺省值为升序

[例]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

 SELECT Sno, Grade
 FROM    SC
 WHERE  Cno= ' 3 '
 ORDER BY Grade DESC;

[例]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

 SELECT  *
 FROM  Student
 ORDER BY Sdept ASC, Sage DESC;  

聚集函数

• 统计元组个数 COUNT(*)

• 统计一列中值的个数 COUNT([DISTINCT|ALL] <列名>)

• 计算一列值的总和（此列必须为数值型） SUM([DISTINCT|ALL] <列名>)

• 计算一列值的平均值（此列必须为数值型） AVG([DISTINCT|ALL] <列名>)

• 求一列中的最大值和最小值 MAX([DISTINCT|ALL] <列名>) MIN([DISTINCT|ALL] <列名>)

GROUP BY子句

• 如果未对查询结果分组，聚集函数将作用于整个查询结果

• 对查询结果分组后，聚集函数将分别作用于每个组

• 按指定的一列或多列值分组，值相等的为一组

[例3.46] 求各个课程号及相应的选课人数。

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

[例3.48 ]查询平均成绩大于等于90分的学生学号和平均成绩 下面的语句是不对的：

 SELECT Sno, AVG(Grade)
 FROM  SC
 WHERE AVG(Grade)>=90
 GROUP BY Sno;

因为WHERE子句中是不能用聚集函数作为条件表达式 正确的查询语句应该是：

 SELECT  Sno, AVG(Grade)
 FROM  SC
 GROUP BY Sno
 HAVING AVG(Grade)>=90;

连接查询

等值连接

[例 3.49] 查询每个学生及其选修课程的情况

 SELECT  Student.*, SC.*
 FROM     Student, SC
 WHERE  Student.Sno = SC.Sno;

自然连接

  SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
  FROM     Student,SC
  WHERE  Student.Sno = SC.Sno;

等值与非等值连接查询

[例 3.51 ]查询选修2号课程且成绩在80分以上的所有学生的学号和姓名。

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

自身连接

[例 3.52]查询每一门课的间接先修课（即先修课的先修课）

 SELECT  FIRST.Cno, SECOND.Cpno
 FROM  Course  FIRST, Course  SECOND
 WHERE FIRST.Cpno = SECOND.Cno;

外连接

外连接与普通连接的区别

普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出 左外连接 列出左边关系中所有的元组 Left out join sc on 右外连接 列出右边关系中所有的元组 Right out join sc on 全连接(FULL JOIN) 列出左右边关系中所有的元组

[例 3. 53] 改写[例 3.49]

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

多表连接

[例3.54]查询每个学生的学号、姓名、选修的课程名及成绩

 SELECT Student.Sno, Sname, Cname, Grade
 FROM    Student, SC, Course    /*多表连接*/
 WHERE Student.Sno = SC.Sno 
 AND SC.Cno = Course.Cno;

嵌套查询

• 一个SELECT-FROM-WHERE语句称为一个查询块

• 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

• 上层的查询块称为外层查询或父查询

• 下层查询块称为内层查询或子查询

• SQL语言允许多层嵌套查询

  • 即一个子查询中还可以嵌套其他子查询

• 子查询的限制

  • 不能使用ORDER BY子句

不相关子查询

子查询的查询条件不依赖于父查询

由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

相关子查询

子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表。然后再取外层表的下一个元组。重复这一过程，直至外层表全部检查完为止

[例]找出每个学生超过他选修课程平均成绩的课程号。

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

此例是相关子查询

1. 初始化查询环境 数据库引擎开始解析这条SQL语句，识别出这是一个带有子查询的SELECT语句。它会将SC表作为主要的数据源，并为外层查询的SC表取别名x，为子查询的SC表取别名y。

2. 外层查询的执行 数据库引擎开始逐行扫描SC表（别名x），从表的第一行开始，逐条处理每条记录。对于每条记录，它会提取Sno（学生编号）、Cno（课程编号）和Grade（成绩）字段的值。

3. 子查询的触发 对于外层查询中的每一条记录（假设当前处理的记录为x），数据库引擎会触发子查询的执行。子查询的作用是计算当前学生x.Sno的所有课程成绩的平均值。

数据库引擎会再次扫描SC表（别名y），但这次扫描的目的是找到所有与外层记录x的学生编号Sno相同的记录。 对这些记录的Grade字段进行平均值计算，得到一个平均成绩AVG(Grade)。

4. 继续处理下一条记录 数据库引擎继续处理外层查询的下一条记录，重复步骤3和4，直到SC表中的所有记录都被处理完毕。

5. 生成最终结果 所有满足条件的记录（即Grade大于或等于其所在学生平均成绩的记录）都被收集到结果集中。最终，数据库引擎返回这些记录的Sno和Cno字段。

带有IN谓词的子查询

[例] 查询与“刘晨”在同一个系学习的学生学号、姓名，所在系。 此查询要求可以分步来完成

① 确定“刘晨”所在系名

 SELECT  Sdept  
 FROM     Student                            
 WHERE  Sname= ' 刘晨 ';
 结果为： CS

② 查找所有在CS系学习的学生。

 SELECT   Sno, Sname, Sdept     
 FROM      Student                 
 WHERE   Sdept= ' CS '; 

将第一步查询嵌入到第二步查询的条件中

 SELECT Sno, Sname, Sdept
 FROM Student
 WHERE Sdept IN
 (SELECT Sdept
 FROM Student
 WHERE Sname= ' 刘晨 ');

此查询为不相关子查询。

带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。

[例 3.55]查询与“刘晨”在同一个系学习的学生学号、姓名，所在系，由于一个学生只可能在一个系学习，则可以用 = 代替IN ：

 SELECT Sno,Sname,Sdept
 FROM Student
 WHERE Sdept =
 (SELECT Sdept
 FROM Student
 WHERE Sname= '刘晨');

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

语义理解

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

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

带有EXISTS谓词的子查询

EXISTS谓词

• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

  • 若内层查询结果非空，则外层的WHERE子句返回真值

  • 若内层查询结果为空，则外层的WHERE子句返回假值

• 由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS谓词

• 若内层查询结果非空，则外层的WHERE子句返回假值

• 若内层查询结果为空，则外层的WHERE子句返回真值

集合查询

集合操作的种类：

并操作UNION 交操作INTERSECT 差操作EXCEPT 参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同

[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。

 SELECT *
 FROM Student
 WHERE Sdept= 'CS'
 UNION
 SELECT *
 FROM Student
 WHERE Sage<=19;

基于派生表的查询

SELECT语句的一般格式

  SELECT [ALL|DISTINCT]  
    <目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
  FROM     <表名或视图名> [别名] 
           [ ,<表名或视图名> [别名]] …
           |(<SELECT语句>)[AS]<别名>
  [WHERE <条件表达式>]
  [GROUP BY <列名1>[HAVING<条件表达式>]]
  [ORDER BY <列名2> [ASC|DESC]];

数据更新

插入数据

INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>]… );

插入子查询：

INSERT INTO <表名> [(<属性列1> [,<属性列2>… )] 子查询;

修改数据

UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>];

• 修改指定表中满足WHERE子句条件的元组

• SET子句给出<表达式>的值用于取代相应的属性列

• 如果省略WHERE子句，表示要修改表中的所有元组

删除数据

DELETE FROM <表名> [WHERE <条件>];

空值的处理

• 有NOT NULL约束条件的不能取空值

• 加了UNIQUE限制的属性不能取空值

• 码属性不能取空值

空值运算：

• 空值与另一个值（包括另一个空值）的算术运算的结果为空值

• 空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN

• 有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑

视图

视图的特点：

• 虚表，是从一个或几个基本表（或视图）导出的表

• 只存放视图的定义，不存放视图对应的数据

• 基表中的数据发生变化，从视图中查询出的数据也随之改变

定义视图

建立视图

CREATE VIEW <视图名> [(<列名> [,<列名>]…)] AS <子查询> [WITH CHECK OPTION];

WITH CHECK OPTION：

对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。

关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

[例] 建立信息系学生的视图。

 CREATE VIEW IS_Student
 AS 
 SELECT Sno,Sname,Sage
 FROM     Student
 WHERE  Sdept= 'IS';

删除视图

DROP VIEW <视图名>[CASCADE];

• 该语句从数据字典中删除指定的视图定义

• 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除

• 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

查询视图

更新视图

第七章

附录