一、SQL概述

SQL（Structured Query Language）: 结构化查询语言，是关系数据库的标准语言。SQL是一个通用的、功能极强的关系数据库语言。

1. 产生与发展

SQL标准的进展过程：

标准大致页数发布日期标准大致页数发布日期
SQL/861986年10月SQL20033600页2003年
SQL/89(FIPS 127-1)120页1989年SQL20083777页2006年
SQL/92622页1992年SQL20112010年
SQL99(SQL3)1700页1999年

2. SQL特点

  1. 综合统一

    1. 集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。

    2. 可以独立完成数据库生命周期中的全部活动： 定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护；数据库安全性、完整性控制等。

    3. 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。

    4. 数据操作符统一。

  2. 高度非过程化

    1. 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径。

    2. SQL只要提出“做什么”，无须了解存取路径。

    3. 存取路径的选择以及SQL的操作过程由系统自动完成。

  3. 面向集合的操作方式

    1. 非关系数据模型采用面向记录的操作方式，操作对象是一条记录。

    2. SQL采用集合操作方式 操作对象、查找结果可以是元组的集合； 一次插入、删除、更新操作的对象可以是元组的集合。

  4. 以同一种语法结构提供多种使用方式

    1. SQL是独立的语言 能够独立地用于联机交互的使用方式。

    2. SQL又是嵌入式语言 SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用。

  5. 语言简洁，易用易学 SQL功能极强，完成核心功能只用了9个动词。

SQL功能动词
数据查询SELECT
数据定义CREATE，DROP，ALTER
数据操纵INSERT，UPDATE，DELETE
数据控制GRANT，REVOKE

3. SQL基本概念(三层结构)

SQL支持关系数据库三级模式结构。

  1. 基本表

    • 本身独立存在的表；

    • SQL中一个关系就对应一个基本表；

    • 一个（或多个）基本表对应一个存储文件；

    • 一个表可以带若干索引。

  2. 存储文件

    • 逻辑结构组成了关系数据库的内模式；

    • 物理结构是任意的，对用户透明。

  3. 视图

    • 从一个或几个基本表导出的表；

    • 数据库中只存放视图的定义而不存放视图对应的数据；

    • 视图是一个虚表；

    • 用户可以在视图上再定义视图。

二、数据定义

SQL的数据定义功能：模式定义、表定义、视图和索引的定义。

操作对象创建删除修改
模式CREATE SCHEMEDROP SCHEME
表CREATE TABLEDROP TABLEALTER TABLE
视图CREATE VIEWDROP VIEW
索引CREATE INDEXDROP INDEXALTER INDEX

1. 模式的定义与删除

(1) 模式的定义

  1. 定义模式 格式：

    CREATE SCHEMAAUTHORIZATION；

说明：

  1. 若没有指定模式名，那么隐含为。

  2. 在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。格式如下：

CREATE SCHEMAAUTHORIZATION[]||]；CREATE SCHEMA TEST AUTHORIZATION ZHANGCREATE TABLE TAB1 ( COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10.3), cOL5 DECIMAL(5.2));
(2) 模式的删除
DROP SCHEMA ；

说明：

  1. CASCADE和RESTRICT必须二选一。

  2. CASCADE（级联）：删除模式的同时把该模式中所有的数据库对象全部删除。

    即使模式中有数据，也会全部删除。级联的内容都会删除的，在后面删除列的时候也会这样

  3. RESTRICT（限制）：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。

    如果模式中有数据，则删除失败

执行创建模式语句必须拥有DBA权限，或者DBA授予在CREATE SCHEMA的权限。

2. 基本表的定义、删除与修改

(1) 数据类型
数据类型含义说明
CHAR(n)长度为n的定长字符串CHAR(9)，即使存储’1’，实际也会使用9个字符的存储单元，定长
VARCHAR(n)最大长度为n的变长字符串VARCHAR(9)，即使存储’1’，只会使用1个字符的存储单元，变长
INT长整数也可以写作（INTEGER）
SMALLINT短整数
NUMERIC(p,d)定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字NUMERIC(10,3)，那么就有7位整数，3位小数。大精度的小数使用这个
REAL取决于机器精度的浮点数
Double Precision取决于及其精度的双精度浮点数
FLOAT(n)浮点数，精度至少为n位数字
DATE日期，包含年、月、日，格式为YYYY-MM-DD
TIME时间，包含一日的时、分、秒，格式为HH-MM-SS
(2) 模式与表

可以认为一个数据库有多个模式，一个模式有多个表

每一个基本表都属于某一个模式，一个模式包含多个基本表。

搜索路径

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式。

  1. 显示当前的搜索路径：

SHOW search_path;

  1. 搜索路径的当前默认值是：

$user，PUBLIC；

  1. DBA用户可以设置搜索路径。

SET search_path TO "S-T"，PUBLIC；（"S-T"是模式名）

实际上这些不用管的，因为在登录的时候就会为用户匹配一个模式

  • 若搜索路径中的模式名都不存在，系统将给出错误。

  • 若搜索路径中的存在模式，RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名。

创建基本表

（以定义一个学生-课程模式S-T为例）

  1. 创建表是给出模式名。

CREATE TABLE "S-T" .Student(......)；/*模式名为S-T*/CREATE TABLE "S-T" .CourdeStudent(......)；CREATE TABLE "S-T" .SCt(......)；

  1. 再创建模式语句中同时创建表。

CREATE SCHEME TEST AUTHORIZATION ZHANGCREATE TABLE TAB1( COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10,3), COL5 DECIMAL(5,2));

  1. 设置所属模式，再创建表名中不必给出模式名。

    DBA用户设置搜索路径，然后定义基本表。

SET search_path TO "S-T"，PUBLIC；CREATE TABLE Student(......);
(4) 修改基本表

语句格式：

ALTER TABLE[ADD [COLUMN][完整性约束]][ADD][DROP[COLUMN][CASCADE|RESTRICT]][DROPCONSTRAINT[RESTRICT|CASCADE]][ALTERCOLUMN]；

说明：

  1. 是要修改的基本表。

  2. ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。

  3. DROP COLUMN子句用于删除表中的列。

    • 如果指定了CASCADE短语 ，则自动删除引用该列的其他对象。（会把将这列作为外键的所有的列都删除）

    • 如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列。

  4. DROP CONSTRAINT子句用于删除指定的完整性约束条件。

  5. ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。

  6. 不论基本表中原来是否已有数据，新增加的列一律为空值。

注意，这里的SQL语句与mysql的语句还是有区别的

  1. 只修改列的数据类型的方法

alter table 表名 modify column 列名 新的列的类型

这里使用的是modify

  1. 同时修改列名和列的数据类型的方法

alter table 表名 change column 旧列名 新列名 新的列类型

这里使用的是change

(5) 删除基本表
DROP TABLE[RESTRICT|CASCADE]；

说明：

  1. RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用。如果存在依赖该表的对象，则此表不能被删除。

  2. CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除。

    SC表的Sno依赖于Student的Sno，如果使用Cascade方式删除student表，SC表也会一并去掉

  3. 基本表定义被删除，数据被删除，表上建立的索引、视图、触发器等一般也将被删除。

    这不管是RESTRICT还是CASCADE方式都是如此

3. 索引的建立与删除

(1) 概述

  1. 建立索引的目的：加快查询速度。

  2. 谁可以建立索引：DBA或表的属主（即建立表的人）。

  3. DBMS一般会自动建立以下列上的索引：

    • PRIMARY KEY；

    • UNIQUE；

  4. 谁维护索引：DBMS自动完成。（删除行或者添加行，索引会自动更新）

  5. 使用索引：DBMS自动选择是否使用索引及使用那些索引。

  6. RDBMS(R是关系的意思)中索引一般采用B+树、HASH索引来实现。

    • B+树索引具有动态平衡的优点

    • HASH索引具有查找速度快的特点。

    采用B+树还是HASH索引则由具体的RDBMS来决定。

  7. 索引是关系数据库内部实现技术，属于内模式的范畴。

  8. CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引。

索引就是一个目录，目录记录着数据的物理存储地址，假如我们以Student的表的Sno列作为索引，那么就会形成以下目录

目录号Sno(索引添加的列)实际物理地址
1001E:/xxxx
2003C:/xxxx
3123D:/xxxx
(2) 建立索引

语句格式

CREATE [UNIQUE] [CLUSTER] INDEX  ON ([][,[] ]…)；

  • 这个次序是指最终目录的次序

  • 如果不写[UNIQUE] [CLUSTER]，默认是非唯一索引

唯一索引

  1. UNIQUE表明此索引每一个索引值只对应唯一的数据。

【例】为S-T数据库中的Student，Course，SC三个表建立索引

CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
索引的次序

上面的SC表的索引设置了属性

假如原来表的结构如下

SnoCno
0020001
0010000
0020003
0010002
0010004

最终索引的顺序就会先按照Sno顺序，Cno倒序

目录号Sno(顺序)Cno(倒序)物理地址
10010004
20010002
30010000
40020003
50020001
聚簇索引

  1. CLUSTER表示要建立的索引是聚簇索引。聚簇索引是指索引顺序与表中记录的物理顺序一致的索引组织。

相当于书的目录

在建立聚簇索引时回生成一个目录，然后更改数据对应的物理地址，使得对应数据存储地址的先后数据与索引的先后顺序一致

原先数据的存储地址一般是不连续的，先后顺序也没有确定

  • 在最经常查询的列上建立聚簇索引以提高查询效率；

    因为索引更改了数据的物理地址，使其前后顺序与索引一致

  • 一个基本表上最多只能建立一个聚簇索引；

    因为数据的物理地址是会改变的，只能跟随一个列进行改变

  • 经常更新的列不宜建立局促索引。

    否则会经常变动物理地址

(3) 删除索引

语句格式：

DROP INDEX ；DROP INDEX  ON ;DROP INDEX .;

删除索引时，系统会从数据字典中删去有关该索引的描述。

4. 数据字典

  1. 数据字典是关系数据库管理系统内部的一组系统表。

  2. 数据字典记录了数据库中所有的定义信息，包括模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。

  3. RDBMS执行SQL数据定义时，实际就是更新数据字典。

三、数据查询

语法格式

SELECT [ALL|DISTINCT][，]...FROM [，]...[WHERE][GROUP BY[HAVING]][ORDER BY[ASC|DESC]]；

注意：

  1. 语句中的字母不分大小写。

  2. 语句中的“，；”等标点符号为英文状态下的半角字符。

  3. [ ]中的内容，不是语句必须的内容，只有为了实现某些功能时才添加。

1. 单表查询

  • 常用的查询条件

查询条件谓词说明
比较=，＞，＜，≥，≤，，!>，!<，!=；NOT+上述比较运算符一般不等于是
确定范围BETWEEN AND，NOT BETWEEN AND
确定集合IN，NOT IN
字符匹配LIKE，NOT LIKE
空值IS NULL，IS NOT NULLNULL不是0，不能用=来判断
多重条件（逻辑运算）AND，OR，NOT
a. 比较大小
b. 确定范围[NOT] BETWEEN AND

确定范围的时候多使用between，而不是用两个号

谓词：

  • BETWEEN … AND …

  • NOT BETWEEN … AND …

c. 确定集合[NOT] IN

谓词：

  • IN ,

  • NOT IN

d. 字符匹配[NOT] LIKE（通配符的使用）

谓词：

  • [NOT] LIKE ‘’ [ESCAPE ‘ ’]

  1. 匹配串为含通配符的字符串

%和_的区别
通配符说明
%可以匹配任意多个的字符，比如 ‘刘%’ 刘后面只能跟一个字符
_只能匹配一个的字符，比如 ‘刘‘ 刘前后都只有一个字符

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

ESCAPE '＼' 表示“ ＼” 为换码字符

这个换码字符是可以自定义的

e. 涉及空值的查询

谓词：

  • IS NULL

  • IS NOT NULL

  • “IS” 不能用 “=” 代替

f. 多重条件查询

逻辑运算符：

  • AND和 OR来联结多个查询条件

  • AND的优先级高于OR

  • 可以用括号改变优先级

可用来实现多种其他谓词

  • [NOT] IN

  • [NOT] BETWEEN … AND …

ORDER BY子句

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

  • 升序：ASC；

  • 降序：DESC；

  • 缺省值为升序；

当排序列含空值时：（空值默认为最大值）

  • ASC：排序列为空值的元组最后显示。

  • DESC：排序列为空值的元组最先显示。

聚集函数
函数说明
COUNT （[DISTINCT| ALL]*）统计元组个数
COUNT ([DISTINCT|ALL])统计一列中值的个数
SUM （[DISTINCT|ALL]）计算一列值的总和（此列必须是数值型）
AVG （[DISTINCT|ALL]）计算一列值的平均值（此值必须是数值型）
MAX （[DISTINCT|ALL]）求一列值的最大值
MIN （[DISTINCT|ALL]）求一列值的最小值
GROUP BY子句

GROUP BY子句分组，细化聚集函数的作用对象

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

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

  • 作用对象是查询的中间结果表

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

HAVING & WHERE：

  • 作用对象不同

    • WHERE子句作用于基表或视图，从中选择满足条件的元组

    • HAVING短语作用于组，从中选择满足条件的组

  • WHERE子句中是不能用聚集函数作为条件表达式的。

HAVING也可以对没有被SELECT选中的列操作

总结：如果要求对聚集函数进行判断，一定会使用HAVING的，只有HAVING后面可以使用聚集函数

2. 连接查询

  • 连接查询：同时涉及多个表的查询

  • 连接条件或连接谓词：用来连接两个表的条件

  1. 在进行多表查询的时候，先要使用等值连接或者自然连接将两个表连接在一起，然后进行操作

  2. 但实际上，进行连接的时候系统并不会在物理上创建一个表，而是仅仅生成一个中间表在内存中，使用结束之后表就没有了

(1) 连接操作的执行方法
① 嵌套循环法(NESTED-LOOP)

  • 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。

  • 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。

  • 重复上述操作，直到表1中的全部元组都处理完毕

  • 就相当于嵌套循环遍历两个表

  • 也就是说，连接操作时并不会生成笛卡尔集

② 排序合并法(SORT-MERGE)

常用于=连接

  1. 首先按连接属性对表1和表2排序

  2. 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续

  3. 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续

  4. 重复上述操作，直到表1或表2中的全部元组都处理完毕为止

这个算法值得学习

③ 索引连接(INDEX-JOIN)

  1. 对表2按连接字段建立索引

  2. 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

这样是通过索引提高了查询表二的速度，而不用全部遍历了

(2) 等值连接与非等值连接

一般格式：

格式一：

[.]  [.]

其中，比较运算符有：=、>、=、<=、!=

[.] BETWEEN [.] AND [.]

连接字段：连接谓词中的列名称

  • 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的

当连接运算符为“=”称为等值连接，其他运算符称为非等值连接。

等值连接：连接运算符为=

【例】查询每个学生及其选修课程的情况

SELECTStudent.*,SC.*FROM Student,SCWHEREStudent.Sno = SC.Sno;

自然连接

【例】用自然连接完成上例题

自然连接就是将重复的列去掉，select后面不用通配符，而是全一个一个写出来

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student,SCWHEREStudent.Sno = SC.Sno;

在多表连接的时候，建议在SELECT都写成table.column的形式，这样提高了可阅读性。养成这个习惯

(3) 自身连接

自身连接：一个表与其自己进行连接

  • 需要给表起别名以示区别

  • 由于所有属性名都是同名属性，因此必须使用别名前缀

(4) 外连接

普通连接与外连接的区别：

  • 普通连接操作只输出满足连接条件的元组。

  • 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

外连接分为：

  • 左外连接：列出左边关系中所有的元组。 LEFT OUT JOIN 表名 ON

  • 右外连接：列出右边关系中所有的元组。 RIGHT OUT JOIN 表名 ON

  • 内连接：INNER JOIN (JOIN)

  • 全外连接：FULL JOIN (FULL OUTER JOIN)

(5) 多表查询

也叫复合条件连接：WHERE子句中含多个连接条件

有n个表连接，where后面就至少得有n-1个查询条件，避免生成笛卡尔集

3. 嵌套查询

概述

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

  • 嵌套查询定义：是指将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。

    嵌套查询只能放在WHERE和HAVING中，不能放在SELECT和FROM后面

    在后面基于派生表的查询中，就可以放在那里么，总之那个就不叫嵌套查询了

先看一个例子

 SELECT Sname/*外层查询/父查询*/ FROM Student WHERE Sno IN(SELECT Sno/*内层查询/子查询*/ FROM SC WHERE Cno= '2');

说明：

  • 子查询中不能使用ORDER BY子句。

  • 层层嵌套方式反映了SQL语言的结构化。

  • 有些嵌套查询可以用连接运算替代。

    嵌套查询实际上就是先将一个表中要用的数据查出来，根据这些数据在另一个表中查询，那么就可以先将两个表连接起来，最终只要一个表的数据即可

  • 术语：外层查询（父查询）、内层查询（子查询）。

相关子查询和不相关子查询及其执行过程

下面来说一下这两种子查询及其执行原理

  • 不相关子查询：子查询的查询条件不依赖于父查询。

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

  • 相关子查询：子查询的查询条件依赖于父查询，整个查询语句称为嵌套查询。

    • 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表

    • 然后再取外层表的下一个元组

    • 重复这一过程，直至外层表全部检查完为止

(1) 带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是个集合，用IN谓词表示父查询的条件在子查询结果的集合中。

(2) 带有比较运算符的子查询

  • 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用>、=、<= 、!=或等比较运算符。

    要求，要明确知道返回值是单个值

  • 与ANY或ALL谓词配合使用

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

谓词语义：

  • ANY：任意一个值

  • ALL：所有值

需要配合使用比较运算符:

运算符说明
>ANY大于子查询结果中的某个值
>ALL大于子查询结果中的所有值
小于子查询结果中的某个值
小于子查询结果中的所有值
≥ANY大于等于子查询结果中的某个值
≥ALL大于等于子查询结果中的所有值
≤ANY小于等于查询结果中的某个值
≤ALL小于等于子查询结果中的所有值
=ANY等于查询结果中的某个值
=ALL等于子查询结果中的所有值（通常没有实际意义）
!=（或）ANY不等于子查询结果中的某个值
!=（或）ALL不等于子查询结果中的任何一个值

用聚集函数实现子查询要比直接用ANY、ALL效率更高。ANY、ALL谓词与聚集函数、IN谓词的等价

=或!=<<=>>=
ANYIN–<MAX<=MAX>MIN>=MIN
ALL–NOT IN< MIN<=MIN>MAX>=MAX

总之多用聚集函数来代替ANY和ALL

(4) 带有EXISTS谓词的子查询

之前使用ALPHA语句的时候就有存在量词∃与全称量词∀，EXISTS就是在SQL语句中实现这个逻辑的

EXISTS

EXISTS谓词代表存在量词∃，带有EXISTS谓词的子查询只返回逻辑真值“TRUE”或逻辑假值”FALSE“。

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

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

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

    EXISTS的执行逻辑：

    从外表中拿出一个元组到EXIST里面进行判断，如果判断条件为真，则将这个元组放到结果中去；否则进行下一个

    反复执行直到所有的元组都判断完成为止

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

EXISTS和连接查询的区别

  1. EXISTS是从外表不断拿一个元组到EXISTS里面进行判断

  2. 连接查询是会先生成一张大表作为中间表，在中间表中查询

选择

  1. EXISTS的效率更高，使用内存更少，但是语义不如连接查询清晰

  2. 连接查询反之

  3. 根据情况选择

NOT EXISTS
EXISTS与其他符号的替换

  • 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换

  • 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

EXISTS/NOT EXISTS实现全称量词

SQL语言中没有全称量词∀。可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

下面使用图形来解释一下

  1. 我们要求都满足P的x

  2. ￢P就是黄色的部分，可以先求出黄色的部分

  3. 然后再取反，￢(∃x(￢P)) = (∀x)P

[例46] 查询选修了全部课程的学生姓名。

可以查出所有的课号，然后用ALL来进行比较

思路：

  • 使用谓词来表示例题：选择一个学生，对于任意一个课程，都选择了

  • 那么可以等价于选择一个学生，不存在一个课程他没选

SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno= Student.SnoAND Cno= Course.Cno) );

理解

  • 首先从Student里面取出第一个学生，此时Student.Sno = 1，代入到最内层的判断中

    • 然后从Course中取出第二个课程，此时Course.Cno = 1，带入到内圈的判断中

      • 然后从SC中取出第一个元组，Sno = 1， Cno = 1，满足条件，说明存在，则EXISTS为真，NOT EXISTS为假的；由于是只要存在一个为真，就不用再往下查找SC的元素了

    • 然后从Course里取出第二个课程，此时Course.Cno = 2，代入到内圈的判断中

      • 然后从SC中取出第一个元组，Sno = 1，Cno = 1，不满足条件，继续找下一个元组

      • 遍历所有的SC之后，发现没有Sno = 1， Cno = 2的条件，说明这个为假

      • 则最内层的NOT EXISTS返回true

    • 然后由于返回true，则Course不会再遍历，直接返回true

  • 最终最外层的NOT EXISTS返回false，则说明这个学生不符合条件，继续遍历下一个学生

由上可得，EXISTS只要满足一个存在条件，就会停止遍历，但是NOT EXISTS会遍历所有的条件。利用两个NOT EXISTS就会进行三重循环。

Student放在外层是因为最终要查找的是在Student中，事实上三个表的顺序可以更变

这道题也可以不使用EXISTS来完成

在做软件编程的时候，追求清晰第一，效率第二，因此还是不用EXIST了

当然可以用COUNT来进行比较，但是这样的方式貌似不是通用的

EXISTS/NOT EXISTS实现逻辑蕴函

  • SQL语言中没有蕴函(Implication)逻辑运算

  • 可以利用谓词演算将逻辑蕴函谓词等价转换为：

(￢p)∨q

4. 集合查询

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

(1) 并操作UNION

  • UNION：将多个查询结果合并起来时，系统自动去掉重复元组。（UNION会默认去掉重复元素）

  • UNION ALL：将多个查询结果合并起来时，保留重复元组

(2) 交操作INTERSECT
(3) 差操作

[例52] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

  1. 方法一：集合查询

SELECT *FROM StudentWHERE Sdept='CS'EXCEPTSELECT*FROM StudentWHERE Sage <=19;

  1. 方法2：连接查询

SELECT *FROM StudentWHERE Sdept= 'CS' ANDSage>19;

5. 基于派生表的查询

  • 子查询可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。

四、数据更新

1. 插入数据INSERT INTO VALUES

(1) 插入元组

语句格式

INSERT INTO  [([，…)]VALUES ( [，]… )

功能:将新元组插入指定表中

说明：

  • INTO子句：属性列的顺序可与表中的顺序不一致，没有指定属性列的默认插入全部。

  • VALUES子句：提供的值必须与INTO子句匹配，值与属性列的个数和值的类型要一致。

INTO如果后面没有属性列，那么VALUES的属性列的个数和数据类型必须与原表一致，不过不确定的可以用NULL。

不能认为VALUES后面如果少了的话会自动认为是前面补齐，后面NULL

如果是主码的话，是不能省略或用NULL赋值的

插入元组可以同时插入多条的，中间用逗号隔开

(2) 插入子查询结果

将子查询结果插入指定表中

要求:

SELECT子句目标列必须与INTO子句匹配，值的个数、类型都要一致。

插入子查询结果也可以同时插入多条的

RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

  • 实体完整性

  • 参照完整性

  • 用户定义的完整性

  • NOT NULL约束

  • UNIQUE约束

  • 值域约束

2. 修改数据 UPDATE SET

语法格式

UPDATESET=[，=]…[WHERE ];

功能：修改指定表中满足WHERE子句条件的元组。 说明：

  • SET子句：指定修改方式，修改的列，修改后取值。

  • WHERE：指定要修改的元组，缺省表示修改所有元组。

  • 在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则。

    比如说Student和SC表，SC表中的Sno外键引用了Student的Sno，不能修改了Student里面的Sno但不修改SC中的Sno

    可以先删除SC中的Sno，然后修改Student的Sno，然后插入

  1. 修改某一个元组的值

3. 删除数据 DELETE FROM

语句格式:

DELETE FROM [WHERE]；

功能：删除指定表中满足WHERE子句条件的元组。 说明： WHERE子句：指定要删除的元组，缺省表示要删除表中的全部元组，表的定义仍在。

  1. 六、空值的处理

空值的存在是因为取值有不确定性，对关系运算带来特殊的问题，所以需要做特殊的处理。 SQL语言中允许某些元组的某些属性取空值，一般有以下几种情况：

  • 该属性有值，但当前不知道它的具体值。

  • 该属性不应该有值。

  • 由于某种原因不便于填写。

1. 空值的产生

INSERT INTO SC(Sno,Cno)# RDBMS将在新插入记录的Grade列上自动地赋空值VALUES ('200215128','1');# 等价INSERT INTO SCVALUES ('200215128','1',NULL);

在更新数据的时候也可以产生控制

2. 空值的判断

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示

3. 空值的约束条件

  1. 属性定义（或者域定义）中有NOT NULL约束条件的不能取空值；

  2. (主)码属性不能取空值。

  3. 加了UNIQUE限制的属性不能取空值（这里老师讲错了）

    加了UNIQUE限制的不能取相同值，如果取相同值，一定是NULL，也就是UNIQUE列中可以有多个NULL

  4. 外键可以为NULL，如果有值，一定是引用的列的值

4. 空值的运算

(1) 算数运算

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

(2) 比较运算

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

注意这个UNKNOWN，放在where后面既不算true，也不算false，在完成业务的时候要考虑值为null的情况

(3) 逻辑运算

逻辑运算主要是两个boolean值比较，这里主要讲解UNKNOW的逻辑运算

XYX AND YX OR YNOT X
UNKNOWTUNKNOWTUNKNOW(U的反还是U)
FUNKNOW(U和F的AND是U，不确定U是不是正确的)UNKNOW(U和F的OR是U，因为不确定是不是正确的)
UNKNOWUNKNOWUNKNOW

总之UNKNOW的值按着理解来就行，比如T AND U，有一个正确必定是正确的

五、视图

1. 概述

(1) 视图的特点

视图的特点

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

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

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

(2) 视图的作用

视图的作用：

  1. 视图能够简化用户的操作

  2. 视图使用户能以多种角度看待同一数据

    不同的应用可能有相同的数据

  3. 视图对重构数据库提供了一定程度的逻辑独立性

  4. 视图能够对机密数据提供安全保护

    比如身份证号存在基本表中，视图可以不显示这个

  5. 适当的利用视图可以更清晰的表达查询

(3) 基于视图的操作

基于视图的操作

  • 查询

  • 删除

  • 受限更新

  • 定义基于该视图的新视图

视图的UPDATE、DELETE、INSERT INTO(有受限)，与基本表同步。

2. 定义视图

(1) 建立视图
语句格式
CREATEVIEW [([，]…)]AS[WITHCHECKOPTION]；

  • 组成视图的属性列名：全部省略或全部指定

    如果全部省略的话，就会用子查询的属性列名来代替，但是不能选择性的给定属性名

  • 子查询不允许含有ORDER BY子句和DISTINCT短语

    后面涉及更改视图，如果视图用这两个定义，那么更改就会出错

  • RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

  • 在对视图查询时，按视图的定义从基本表中将数据查出。

WITH CHECK OPTION的作用：受限更新，也就是在更新视图的时候会受到限制（对视图执行修改语句实际上也就是对基本表执行修改语句，修改视图的行反映到基本表上，所以应该将where后面的语句给填上）

  • 修改操作：自动加上Sdept= ‘IS’的条件

  • 删除操作：自动加上Sdept= ‘IS’的条件

  • 插入操作：自动检查Sdept属性值是否为’IS’

    • 如果不是，则拒绝该插入操作

    • 如果没有提供Sdept属性值，则自动定义Sdept为’IS’

基于多个基表的视图
基于视图的视图
带表达式的视图

SELECT语句里面可以有表达式，建立视图的时候也可以有。

分组视图

同样的，分组视图也不会实际上建立一个表，但是注意，这个表是动态生成的，如果修改基本表的值，AVG也会响应的修改

每次打开这个视图都会执行这个SELECT语句创建视图

不指定属性列
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)ASSELECT *FROMStudentWHERE Ssex='女';

这里SELECT语句是没有属性列的，就会自动按顺序匹配

缺点：

  • 修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。

    但是如果指定了属性列就会好多

(2) 删除视图

语句的格式:

DROP VIEW  [CASCADE];

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

  • 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除(但是不会删除基本表)

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

tips:如果我们在删除视图A的时候不确定是否从视图A导出了视图B，那么就可以直接加上[CASCADE]

也就是在删除视图的时候都加上[CASCADE]

3. 查询视图

(1) 视图消解法

  1. RDBMS实现视图查询的方法—-视图消解法

    • 进行有效性检查。

      比如视图S1来源于Student，在查询S1的时候是会检查Student是否存在的，如果被删除了，查询失败

    • 转换(修正)成等价的对基本表的查询。

    • 执行修正后的查询。

关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(view resolution)。

(2) 视图消解法的局限性

  • 有些情况下，视图消解法不能生成正确查询。

[例11]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT *FROM S_GWHEREGavg>=90;

S_G视图的子查询定义：

CREATE VIEW S_G (Sno,Gavg)AS SELECTSno,AVG(Grade)FROMSCGROUP BY Sno;

S_G是Sno与平均成绩的表

视图消解法修改后的语句为

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

但是前面所学，聚集函数不能放在where后面的，只能放在having后面

SELECTSno,AVG(Grade)FROMSCGROUP BY SnoHAVING AVG(Grade)>=90;

4. 更新视图

说明：

  • 更新视图是指通过视图来插入、删除和数据，因为视图不适宜存储数据，因此对视图的更新操作将通过视图消解，转化为对实际表的更新操作。

  • 注意：

    为防止在更新视图时出错，定义视图时要加上WITH CHECK OPTION子句。

更新视图的时候，是会依赖建立视图时的条件

(1) 更新数据

[例12] 将信息系学生视图IS_Student中学号201215125的学生姓名改为“刘辰”。

UPDATEIS_StudentSETSname= '刘辰'WHERESno= '201215125';

转换后的语句：

UPDATEStudentSET Sname= '刘辰'WHERE Sno= '201215125' AND Sdept= 'IS';
(2) 插入数据

[例13] 向信息系学生视图IS_Student中插入一个新的学生记录：200215111，赵新，20岁

  1. 视图IS_Student的定义

CREATE VIEW IS_StudentAS SELECT Sno,Sname,SageFROMStudentWHERESdept= 'IS';

  1. 插入数据

INSERT INTO IS_StudentVALUES('200215111','赵新',20);

  1. 转换为对基本表的更新：

INSERT INTO Student(Sno,Sname,Sage,Sdept)VALUES('200215129','赵新2',20,'IS');
(3) 删除数据

[例14]删除信息系学生视图IS_Student中学号为200215129的记录

DELETE FROM IS_StudentWHERE Sno= '200215129';

转换为对基本表的更新：

DELETEFROM StudentWHERE Sno= '200215129' AND Sdept= 'IS';
(4) 更新视图的限制

  • 更新视图的限制：一些视图是不可更s新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例：视图S_G为不可更新视图。

CREATE VIEW S_G(Sno, Gavg)ASSELECT Sno, AVG(Grade)FROM SCGROUP BY Sno;

将学生的学号及他的平均成绩定义为一个视图

UPDATES_GSET Gavg = 90WHERESno= '200215121';

这个对视图的更新无法转换成对基本表SC的更新，因为平均成绩是由计算得出的，系统无法修改各科成绩，使平均成绩到达90分

  • 允许对行列子集视图进行更新

  • 对其他类型视图的更新不同系统有不同限制