数据库SQL学习笔记_sql数据库学习-优快云博客

本文链接：https://blog.youkuaiyun.com/chen030209/article/details/142322966

第 1 章绪论

1.1 数据库系统概述

1.1.1 四个基本概念

1.1.2 数据管理技术的产生和发展

数据的逻辑结构是用户可见的数据组织方式

1.2 数据模型

1.2.1 两类数据模型

(1) 概念模型（信息模型）

(2) 逻辑模型和物理模型

1.2.2 数据模型的组成要素

数据结构

数据操作

数据的约束条件

1.2.3 概念模型

概念模型

信息世界中的基本概念：实体，属性，码，域，实体型，实体值，实体集，联系

实体型：用实体名及其属性名集合来抽象合刻画同类实体

信息世界中的基本概念

概念模型的表示方法

E-R图（实体-联系方法）

实体型：矩形

属性：椭圆形

联系：菱形，同时在无向边旁标上联系的类型（1:1、1:n 或 m:n），联系本身也是一种实体性，也可以有属性

1.2.4 最常用的数据模型

非关系模型：层次模型、网状模型

关系模型

面向对象模型

对象关系模型

1.2.5 层次模型

树形结构

缺点：多对多联系表示不自然，对插入和删除操作的限制多，查询子女节点必须通过双亲节点

1.2.6 网状模型

允许一个以上的结点无双亲，一个结点可以有多于一个的双亲

层次模型实际上是网状模型的一个特例

缺点：结构比较复杂

1.2.7 关系模型

关系：一个关系对应一张表

元组：表中一行即为一个元组

属性：表中一列即为一个属性

主码：表中某个属性组，可以唯一确定一个元组

域：属性的取值范围

关系模式：关系名（属性1，属性2，…，属性n）

例如：

学生（学号，姓名，年龄，性别，系，年级）

最基本的规范条件：关系的每一个分量必须是一个不可分的数据项

1.3 数据库系统结构

1.3.1 数据库系统模式的概念

型：对某一类数据的结构和属性的说明

例如：

记录型（学号，姓名，性别，系别，年龄，籍贯）

记录值（900201，李明，男，计算机，22，江苏）

1.3.2 数据库系统的三级模式结构

模式

数据库中全体数据的逻辑结构和特征的描述

一个数据库只有一个模式

外模式

内模式

一个数据库只有一个内模式

内模式是数据在数据库内部的表示方式

三模式的关系

模式是内模式的逻辑表示

模式表示概念级数据库，体现对数据库的总体观

采用映像技术的优点：保证数据独立性，方便用户使用数据库，利于数据共享，利于数据的安全与保密

1.3.3 数据库的二级映像功能与数据独立性

为了能够在系统内部实现三个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两层映像：外模式 / 模式映像和模式 / 内模式映像。

考点：

E-R图

数据独立性，三级模式，二级映像

1.4 数据库系统的组成

1.5 小结

1.6 作业

什么叫数据与程序的物理独立性？

什么叫数据与程序的逻辑独立性？

为什么数据库系统具有数据与程序的独立性？

第 2 章关系数据库

2.1 关系数据结构及形式化定义

关系模型由关系数据结构、关系操作集合和关系完整性约束三部分组成。

2.1.1 关系

若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码

若一个关系有多个候选码，则选定其中一个为主码。主码属于候选码

候选码的诸属性称为主属性。不包含在任何候选码中的属性称为非主属性或非码属性。

在最极端的情况下，关系模式的所有属性是这个关系模式的候选码，称为全码。

关系可以有三种类型：基本关系 (通常又称为基本表或基表)、查询表和视图表

基本表是实际存在的表，它是实际存储数据的逻辑表示；

查询表是查询结果对应的表；

视图表是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据。

2.1.2 关系模式

2.1.3 关系数据库

2.1.4 关系模型的存储结构

2.2 关系操作

2.2.1 基本的关系操作

2.2.2 关系数据语言的分类

2.3 关系的完整性

2.3.1 实体完整性

实体完整性规则：若属性 A 是基本关系 R 的主属性，则属性 A 不能取空值

2.3.2 参照完整性

关系间的引用

例：学生（学号，姓名，性别，专业号，年龄）

专业（专业号，专业名）

学生关系引用了专业关系的主码“专业号”

外码

设 F 是基本关系 R 的一个或一组属性，但不是关系 R 的码，如果 F 与基本关系 S 的主码 $K_S$ 相对应，则称 F 是基本关系 R 的外码

基本关系 R 称为参照关系，基本关系 S 称为被参照关系或目标关系，外码是被参照关系的主码

例：学生关系的“专业号”与专业关系的主码“专业号”相对应

“专业号”属性是学生关系的外码

专业关系是被参照关系，学生关系为参照关系

参照完整性

参照完整性规则：

若属性（或属性组）F 是基本关系 R 的外码，它与基本关系 S 的主码 $K_S$ 相对应（基本关系 R 和 S 不一定是不同的关系），则对于 R 中每个元组在 F 上的值必须为：

或者取空值（F 的每个属性值均为空值）

或者等于 S 中某个元组的主码值，不能取和 S 中主码值都不同的值

例：学生关系中每个元组“专业号”属性只取两类值：

（1）空值，表示尚未给该学生分配专业

（2）非空值，这时该值必须是专业关系中某个元组的“专业号”值，表示该学生不可能分配一个不存在的专业

2.3.3 用户定义的完整性

2.4 关系代数

一种抽象的查询语言

是关系数据操纵语言的一种传统表达方式用对关系的运算来表达查询

关系代数运算的三个要素：

运算对象：关系

运算结果：关系

运算符：集合运算符，专门的关系运算符，算术比较符，逻辑运算符

运算符

含义

运算符

含义

集合

运算符

∪

∩

并

差

交

逻辑

运算符

∧

∨

非

与

或

专门的

关系

运算符

$\Join$

广义笛卡尔积

选择

投影

连接

除

比较

运算符

≥

≤

≠

大于

大于等于

小于

小于等于

等于

不等于

2.4.1 传统的集合运算

传统的集合运算是二目运算，包括：并、差、交、广义笛卡尔积

设关系 R 和关系 S 具有相同的目 n (即两个关系都有 n 个属性)，且相应的属性取自同一个域，则称关系 R 和关系 S 是相容的

2.4.2 专门的关系运算

$t[A_i]$ 表示元组 t 中相应于属性 $A_i$ 的一个分量，属性相当于索引

象集 $Z_x$ ：给定一个关系 R(X, Z)，X 和 Z 为属性组，当 t[X]=x 时，x 在 R 中的象集为： $Z_x=\{t[Z]|t\in R,t[X]=x\}$ ，它表示 R 中属性 X 值为 x 的元组在 Z 上分量的集合

例：

A	B	C
a1	b1	c1
a1	b2	c2
a2	b2	c1
a3	b3	c2
a1	b1	c3
a3	b2	c2

$a_1$ 的象集为 $\{(b_1,c_1),(b_2,c_2),(b_1,c_3)\}$

关系运算的定义：

选择：在关系 R 中选择满足给定条件的元组，记作 $\sigma _F(R)=\{t|t\in R\wedge F(t)=True\}$

其中 F:选择条件，是一个逻辑表达式

元组的连接

R 为 n 目关系，S 为 m 目关系， $t_r\in R,t_s\in S$ ， $\widehat{t_rt_s}$ 称为元组的连接， $\widehat{t_rt_s}$ 是一个 n+m 列的元组，前 n 个分量为 R 中的一个 n 元组，后 m 个分量为 S 中的一个 m 元组

连接

从两个关系的笛卡尔积中选取属性间满足一定条件的元组 $R \Join_{A\theta B} S=\{\widehat{t_rt_s} | t_r\in R\wedge t_s\in S\wedge t_r [A]\theta t_s[B]\}$

$\theta$ ：比较运算符

连接运算从 R 和 S 的广义笛卡尔积 $R\times S$ 中选取 (R 关系) 在 A 属性组上的值与 (S 关系) 在 B 属性组上值满足比较关系 $\theta$ 的元组

等值连接： $\theta$ 为 = 的连接运算称为等值连接，从关系 R 与 S 的广义笛卡尔积中选取 A、B 属性值相等的那些元组，即等值连接为：

$R\Join_{A=B}S=\{\widehat{t_rt_s}|t_r\in R\wedge t_s\in S\wedge t_r[A]=t_s[B]\}$

自然连接：一种特殊的等值连接，两个关系中进行比较的分量必须是相同的属性组，在结果中把重复的属性列去掉，R 和 S 具有相同的属性组 B

$R\Join S=\{\widehat{t_r t_s}|t_r\in R\wedge t_s\in S\wedge t_r[B]=t_s[B]\}$

一般的连接是从行的角度进行运算，自然连接还需要取消重复列，所以是同时从行和列的角度进行运算

外连接：在自然连接基础上，把舍弃的元组也保存在结果关系中，而在其他属性上填空值

左外连接：只保留左边关系 R 中要舍弃的元组

右外连接：只保留右边关系 S 中要舍弃的元组

例：上述关系 R 和关系 S 的外连接

除

给定关系 R(X, Y) 和 S(Y, Z)，其中 X，Y，Z 为属性组，R 中的 Y 与 S 中的 Y 可以有不同的属性名，但必须出自相同的域集。R 和 S 的除运算得到一个新的关系 P(X)，P 是 R 中满足下列条件的元组在 X 属性列上的投影：元组在 X 上分量值为 x 的象集 $Y_x$ 包含 S 在 Y 上投影的集合，记作：

$R\div S=\{t_r[X]|t_r\in R\wedge \pi _Y(S)\subseteq Y_x\}$

例：设关系 R、S 分别为 (a) 和 (b)， $R\div S$ 的结果为图 (c)

在关系 R 中，A 各个值的象集分别为：

a1{(b1, c2)，(b2,c3)，(b2, c1)}

a2{(b3, c7)，(b2, c3)}

a3{(b4, c6)}

a4{(b6, c6)}

S 在 (B, C) 上的投影为 {(b1, c2)，(b2, c3)，(b2, c1)}

例：

关系代数的基本运算有五种 $U,-,\sigma,\pi,\times$

其他运算可由上述 5 种运算导出

交： $A\cap B=A-(A-B)$

连接： $R \Join S=\sigma_{A\ominus B}(R\times S)$

自然连接： $R\Join S=\pi_{[S.B]}(\sigma_{R.B=S.B}(R\times S))$

除： $R\div S=\pi_x(R)-\pi_x((\pi_x(R)\times \pi_y(S))-R)$ ，假定 $R(X,Y),S(Y,Z)$

*2.5 关系演算

以数理逻辑中的谓词演算为基础

*2.5.1 元组关系演算语言 ALPHA

*2.5.2 元组关系演算

*2.5.3 域关系演算语言 QBE

2.6 小结

习题

设有一个 SPJ 数据库，包含 S、P、J 及 SPJ 4 个关系模式

供应情况表 SPJ 由供应商代码 (SNO)、零件代码 (PNO)、工程项目代码 (JNO)、供应数量 (QTY) 组成

用关系代数完成如下查询

(1) 求供应工程 JI 零件的供应商号码 SNO；

$\Pi _{SNO}(\sigma_{JNO='J1'}(SPJ))$

(2) 求供应工程 J1 零件 P1 的供应商号码 SNO；

$\Pi _{SNO}(\sigma_{JNO='J1'\wedge PNO='P1'}(SPJ))$

(3) 求供应工程 J1 零件为红色的供应商号码 SNO；

$\Pi _{SNO}(\Pi_{SNO,PNO}(\sigma _{JNO='J1'}(SPJ))\Join\Pi_{PNO}(\sigma _{COLOR='red'}(P)))$

'red' 应该是 '红'，但是显示不了中文

自然连接也可以理解为交集

(4) 求没有使用天津供应商生产的红色零件的工程号 JNO；

$\Pi _{JNO}(J)-\Pi _{JNO}(\Pi _{SNO}(\sigma_{CITY='tianjin'}(S))\Join\Pi_{SNO,PNO,JNO}(SPJ)\\ \Join\Pi_{PNO}(\sigma_{COLOR='red'}(P)))$

(5) 求至少用了供应商 S1 所供应的全部零件的工程号 JNO。

$\Pi_{JNO,PNO}(SPJ)\div \Pi_{PNO}(\sigma_{SNO='s1'}(SPJ))$

一个零部件 JNO 可以有多个供应商

第 3 章关系数据库标准语言SQL

3.1 SQL 概述

3.1.1 SQL 的产生与发展

3.1.2 SQL 的特点

其功能包括：数据查询、数据操纵、数据定义、数据控制

综合统一

SQL 可以独立完成数据库生命周期中的全部活动，包括以下一系列操作要求：

• 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库。

• 对数据库中的数据进行查询和更新。

• 数据库重构和维护。

• 数据库安全性、完整性控制，以及事务控制。

• 嵌入式 SQL 和动态 SQL 定义。

高度非过程化

非关系数据模型的数据操纵语言“面向过程”，必须指定存储路径

用 SQL 进行数据操作时，只要提出“做什么”，而无须指明“怎么做”，因此无须了解存取路径。

存取路径的选择以及 SQL 的操作过程由系统自动完成，有利于提高数据独立性。

面向集合的操作方式

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

例如查询所有平均成绩在 80 分以上的学生姓名，用户必须一条一条地把满足条件的学生记录找出来（通常要说明具体处理过程，即按照哪条路径，如何循环等）。

而 SQL 采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

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

SQL 既是独立的语言，又是嵌入式语言。

作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入 SQL 命令对数据库进行操作；

作为嵌入式语言，SQL 语句能够嵌入到高级语言（例如 C、C++、Java）程序中，供程序员设计程序时使用。

语言简洁，易学易用，9个动词

3.1.3 SQL 的基本概念

SQL 语言支持关系数据库的三级模式体系结构

基本表：

本身独立存在的表

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

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

存储文件：

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

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

视图：

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

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

视图是一个虚表

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

3.2 学生-课程数据库

3.3 数据定义

操作对象	操作方式
操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX

3.3.1 模式的定义与删除

定义模式

定义模式实际上定义了一个命名空间

在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。

也就是说用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。

模式定义语句：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];

例：定义一个学生-课程模式S-T

CREATE SCHEMA "S-T" AUTHORIZATION WANG;

为用户 WANG 定义了一个模式 S-T

如果没有指定〈模式名〉，那么〈模式名〉默认为〈用户名〉。

例：

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

为用户 ZHANG 创建了一个模式 TEST，并在其中定义了一个表 TAB1。

删除模式

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

其中 CASCADE 和 RESTRICT 必选其一。

CASCADE (级联)，表示在删除模式的同时把该模式中所有的数据库对象全部删除；

RESTRICT (限制)，表示如果该模式中已经定义了下属的数据库对象 (如表、视图等)，则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行 DROP SCHEMA 语句。

例：

DROP SCHEMA ZHANG CASCADE;

删除模式 ZHANG，同时该模式中定义的表 TAB1 也被删除

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

定义基本表

CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件]
                    [,<列名><数据类型>[列级完整性约束条件]]
                    ...
                    [,<表级完整性约束条件>]);

完整性规则主要有三种子句：

主键子句(PRIMARY KEY)：实体完整性

外键子句(FOREIGN KEY)：参照完整性

检查子句(CHECK)：用户定义完整性

完整性约束条件涉及到该表的多个属性，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

当用户操作表中数据时自动检查该操作是否违背这些完整性约束条件。

例：建立一个学生表Student

学号是主码，姓名取值唯一

CREATE TABLE Student
    (Sno CHAR(9) PRIMARY KEY,     /*列级完整性约束条件，Sno 是主码*/
    Sname CHAR(20) UNIQUE,     /* Sname 取唯一值*/
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

例：

CREATE TABLE Course
    ( Cno  CHAR(4) PRIMARY KEY,
      Cname CHAR(40),  
      Cpno  CHAR(4),  /*先修课*/
      Ccredit  SMALLINT,
      FOREIGN KEY (Cpno) REFERENCES Course(Cno) 
            );

FOREIGN KEY (Cpno) REFERENCES Course (Cno) 是一个外键约束定义。

FOREIGN KEY (Cpno)：表示正在定义一个外键，外键列是名为 Cpno（在当前表中）的列。

REFERENCES Course(Cno)：“REFERENCES” 关键字用于指定外键的参照关系。这里表示 Cpno 列参照（引用）了名为 Course 的表中的 Cno 列。

这一约束的目的是维护数据的完整性。它确保在当前表（包含Cpno列的表）中，Cpno列中的每个值要么为NULL，要么是Course表中Cno列已经存在的值。

例如，如果Course表中有课程编号为'C001'、'C002'等，那么在当前表中，Cpno列的值只能是'C001'、'C002'或者NULL，不能是其他不存在于Course表Cno列中的值。

例：建立一个“学生选课”表SC

CREATE TABLE SC
   (Sno CHAR(9),
    Cno CHAR(4),  
    Grade SMALLINT,
    PRIMARY KEY (Sno，Cno),    
    /* 主码由两个属性构成，必须作为表级完整性进行定义 */
    FOREIGN KEY (Sno) REFERENCES Student(Sno),    
    /* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
    /* 表级完整性约束条件， Cno是外码，被参照表是Course */
		);

数据类型

定义表的属性时，需要指明其数据类型及长度

模式与表

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

定义基本表所属模式

方法一：在表名中明显地给出模式名

Create table "S-T".Student (...);    /*模式名为S-T*/

方法二：在创建模式语句中同时创建表

方法三：设置所属的模式

创建基本表（其他数据对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式，若搜索路径中的模式名都不存在，系统将给出错误

修改基本表

ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型>[完整性约束]
[ADD<表级完整性约束>]
[DROP [COLUMN]<列名>[CASCADE | RESTRICT]]
[DROP CONSTRAINT〈完整性约束名〉[RESTRICT|CASCADE]]
[ALTER COLUMN <列名><数据类型>];

其中〈表名〉是要修改的基本表

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

DROP COLUMN 子句用于删除表中的列，如果指定了 CASCADE 短语，则自动删除引用了该列的其他对象，比如视图；如果指定了 RESTRICT 短语，则如果该列被其他对象引用，RDBMS 将拒绝删除该列。

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

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

例：向 Student 表增加“入学时间”列，其数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE；

例：将年龄的数据类型由字符型改为整数

ALTER TABLE Student ALTER COLUMN Sage INT;

例：增加课程名称必须取唯一的约束条件

ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

DROP TABLE<表名>[RESTRICT|CASCADE];

RESTRICT：有限制条件，欲删除的基本表不能被其他表的约束所引用(如 CHECK，FOREIGN KEY 等约束)，不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。

CASCADE：没有限制条件，在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。默认情况是 RESTRICT。

例：若表上建有视图，选择 RESTRICT 时表不能删除

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

DROP TABLE Student RESTRICT;   
--ERROR: cannot drop table Student because other objects depend on it

如果选择 CASCADE 时可以删除表，视图也自动被删除

DROP TABLE Student CASCADE;   
--NOTICE: drop cascades to view IS_Student

3.3.3 索引的建立与删除

建立索引

目的：加快查询速度

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>]] …)；

每个〈列名〉后面还可以用〈次序〉指定索引值的排列次序，可选 ASC (升序) 或 DESC (降序)，默认值为 ASC。

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

CLUSTER 表示要建立的索引是聚簇索引。

例：在 Student 表的 Sname（姓名）列上建立一个聚簇索引

CREATE CLUSTER INDEX Stusname ON Student (Sname);

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

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

经常更新的列不宜建立聚簇索引

例：为学生-课程数据库中的 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);    /*按学号升序和课程号降序建唯一索引*/

修改索引

ALTER INDEX<旧索引名>RENAME TO<新索引名>;

删除索引

DROP INDEX<索引名>;

例：删除 Student 表的 Stusname 索引

DROP INDEX Stusname;

3.3.4 数据字典

3.4 数据查询

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

整个 SELECT 语句的含义是：根据 WHERE 子句的条件表达式，从 FROM 子句指定的基本表或视图中找出满足条件的元组，再按 SELECT 子句中的目标列表达式，选出元组中的属性值形成结果表。

如果有 GROUP 子句，则将结果按 <列名1> 的值进行分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数。

如果 GROUP 子句带 HAVING 短语，则只有满足指定条件的组才有输出。

如果有 ORDER 子句，则结果表还要按 <列名2> 的值的升序或降序排序。

关系表达式与SQL语句的对比

关系表达式： $\sqcap _{A_1,A_2,\cdots,A_n}(\sigma_F(R1\times \cdots \times Rm))$

SQL 语句

SELECT A1,A2,...An
FROM R1 x ... x Rm
WHERE F

3.4.1 单表查询

选择表中若干列

(1) 查询指定列

例：查询全体学生的学号与姓名

SELECT Sno,Sname
FROM Student;

(2) 查询全部列

例：查询全体学生的详细记录

SELECT *
FROM Student;

等价于

SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;

(3) 查询经过计算的值

SELECT 子句的〈目标列表达式〉不仅可以是表中的属性列，也可以是表达式，字符串常量，函数，列别名。

例：查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)
FROM Student;

使用列别名改变查询结果的列标题

SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;

选择表中的若干元组

(1) 消除取值重复的行

SELECT DISTINCT Sno
FROM SC;

(2) 查询满足条件的元组

查询满足指定条件的元组可以通过 WHERE 子句实现。

例：查询计算机科学系全体学生的名单

SELECT Sname
FROM Student
WHERE Sdept='CS';

例：查询考试成绩不及格的学生的学号

SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

这里使用了 DISTINCT 短语，当一个学生有多门课程不及格，他的学号也只列一次。

例：查询年龄不在 20~23 岁之间的学生姓名、系别和年龄。

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

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

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

谓词 LIKE 可以用来进行字符串的匹配。其一般语法格式如下：

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

其含义是查找指定的属性列值与〈匹配串〉相匹配的元组。〈匹配串〉可以是一个完整的字符串，也可以含有通配符％和 _。其中：

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

例如 a%b 表示以 a 开头，以 b 结尾的任意长度的字符串。如 acb、addgb、ab 等都满足。

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

例如 a_b 表示以 a 开头，以 b 结尾的长度为 3 的任意字符串。如 acb、afb 等都满足。

例：查询所有姓刘的学生的姓名、学号和性别。

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

例：查询 DB_Design 课程的课程号和学分

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

ESCAPE '\' 表示 “\” 为换码字符。这样匹配串中紧跟在 “\” 后面的字符不再为通配符，转义为普通的字符。

例：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;     /*分数 Grade 是空值*/

ORDER BY 子句

ORDER BY 对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。

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

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

对于空值，排序时显示的次序由具体系统实现来决定。例如按升序排，含空值的元组最后显示；按降序排，空值的元组则最先显示。即空值视为最大

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

SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

因为默认升序，Sdept 后面的 ASC 可以省略

聚集函数

COUNTS(*)	统计元组个数
COUNT([DISTINCT \| ALL]<列名>）	统计一列中值的个数
SUM([DISTINCT \| ALL]<列名>)	计算一列值的总和 (此列必须是数值型)
AVG([DISTINCT \| ALL]<列名>)	计算一列值的平均值 (此列必须是数值型)
MAX([DISTINCT \| ALL]<列名>)	求一列值中的最大值
MIN([D1STINCT \| ALL]<列名>)	求一列值中的最小值

例：查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno)
FROM SC;

学生每选修一门课，在 SC 中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在 COUNT 函数中用 DISTINCT 短语。

例：计算选修 1 号课程的学生平均成绩

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

例：查询学生 201215012 选修课程的总学分数。

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

当聚集函数遇到空值时，除 COUNT (*) 外，都跳过空值而只处理非空值。

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

聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句

GROUP BY 子句

GROUP BY 子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

例：求各个课程号及相应的选课人数

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

该语句对查询结果按 Cno 的值分组，所有具有相同 Cno 值的元组为一组，然后对每一组作用聚集函数 COUNT 进行计算，以求得该组的学生人数。

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用 HAVING 短语指定筛选条件。

例：查询选修了三门以上课程的学生学号

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

这里先用 GROUP BY 子句按 Sno 进行分组，再用聚集函数 COUNT 对每一组计数；HAVING 短语给出了选择组的条件，只有满足条件 (即元组个数>3，表示此学生选修的课超过 3 门) 的组才会被选出来。

WHERE 与 HAVING 的区别在于作用对象不同。

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

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

例：查询平均成绩大于等于 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.4.2 连接查询

前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。

连接查询的 WHERE 子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为

[<表名 1>.]<列名 1><比较运算符>[<表名 2>.]<列名 2>

其中比较运算符主要有=、>、=、<=、!= (或<>) 等。

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

[<表名 1>.]<列名 1> BETWEEN[<表名 2>.]<列名 2> AND[<表名 2>.］<列名 3>

连接操作的执行过程

嵌套循环法：首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，以此类推。

排序合并法：常用=连接，首先按连接属性对表1和表2排序，对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组，当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续。找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，以此类推。

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

等值与非等值连接查询

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

这两个表之间的联系是通过公共属性 Sno 实现的。

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;     /* 将 Student 与 SC 中同一学生的元组连接起来 */

本例中，SELECT 子句与 WHERE 子句中的属性名前都加上了表名前缀，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。

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

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND /*连接谓词 */
      SC.Cno='2' AND SC.Grade>90; /*其他限定条件 */

自身连接

一个表与其自己进行连接，需要给表起别名以示区别，由于所有属性名都是同名属性，因此必须使用别名前缀。

例：查询每一门课的间接先修课（即先修课的先修课）

在 Course 表中只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号查找它的先修课程。这就要将 Course 表与其自身连接。

为此，要为 Course 表取两个别名，一个是 FIRST，另一个是 SECOND。

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

使用别名时，在 FROM 中要加上原名 (Course FIRST)，WHERE 中不需要加原名

外连接

外连接与普通连接的区别：普通连接操作只输出满足连接条件的元组，外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

有时想以 Student 表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍把 Student 的悬浮元组保存在结果关系中，而在 SC 表的属性上填空值 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(Sno)：*/

左外连接：列出左边关系（如本例Student）中所有的元组

右外连接：列出右边关系中所有的元组

多表连接

例：查询每个学生的学号、姓名、选修的课程名及成绩。

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

不是三个一起相等 Student.Sno=SC.Sno=Coures.Cno ，前面是Sno，后面是Cno

3.4.3 嵌套查询

在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。

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

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

SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

需要特别指出的是，子查询的 SELECT 语句中不能使用 ORDER BY 子句，ORDER BY 子句只能对最终查询结果排序。

带有 IN 谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所以谓词 IN 是嵌套查询中最经常使用的谓词。

例：查询与“刘晨”在同一个系学习的学生。

先分步查询，再嵌套查询

先确定“刘晨”所在系名

SELECT Sdept
FROM Student
WHERE Sname='刘晨';

再查找这个系的学生

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

将第一步查询嵌入到第二步查询的条件中，构造嵌套查询如下：

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

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。

一种求解方法是由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。

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

SELECT Sno,Sname
FROM Student
WHERE Sno IN
    (SELECT Sno
    FROM SC
    WHERE Cno IN
        (SELECT Cno
        FROM Course
        WHERE Cname='信息系统'
        )
    );

先在 Course 关系中找出“信息系统”的课程号，再在 SC 关系中找出选修该课程号的学生学号，最后在 Student 关系中取出 Sno 和 Sname

本查询同样可以用连接查询实现：

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
    SC.Cno=Course.Cno AND
    Course.Cname='信息系统';

如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询语句。

带有比较运算符的子查询

当确切知道内层查询返回的是单个值时，可以用>、＜、=、＞=、＜=、!= 或＜＞等比较运算符。

与ANY或ALL谓词配合使用

由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用 = 代替 IN：

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

子查询一定要在比较符之后

错误示范：

SELECT Sno,Sname,Sdept 
FROM Student
WHERE (SELECT Sdept
    FROM Student
    WHERE Sname='刘晨')
        = Sdept;    /*错误*/

例：找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade) /*某学生的平均成绩*/
            FROM SC y
            WHERE y.Sno=x.Sno);

x 是表 SC 的别名，又称为元组变量，可以用来表示 SC 的一个元组。

内层查询是求一个学生所有选修课程平均成绩的，至于是哪个学生的平均成绩要看参数 x.Sno 的值，而该值是与父查询相关的，因此这类查询称为相关子查询。

这个语句的一种可能的执行过程采用以下三个步骤。

(1) 从外层查询中取出 SC 的一个元组 x，将元组 x 的 Sno 值 (201215121) 传送给内层查询。

SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121';

(2) 执行内层查询，得到值 88 (近似值)，用该值代替内层查询，得到外层查询：

SELECT Sno, Cno
FROM SC x
WHERE Grade>=88;

(3) 执行这个查询，得到

(201215121,1)
(201215121,3)

求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。

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

子查询返回单值时可以用比较运算符，但返回多值时要用 ANY (有的系统用 SOME) 或 ALL 谓词修饰符。而使用 ANY 或 ALL 谓词时则必须同时使用比较运算符。其语义如下所示：

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

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

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY (SELECT Sage
                FROM Student
                WHERE Sdept='CS')
AND Sdept<>'CS';     /* 注意这是父查询块中的条件 */

带有 EXISTS 谓词的子查询

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

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

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

使用存在量词 EXISTS 后，若内层查询结果非空，则外层的 WHERE 子句返回真值，否则返回假值。

使用存在量词 NOT EXISTS 后，若内层查询结果为空，则外层的 WHERE 子句返回真值，否则返回假值。

例：查询选修了全部课程的学生姓名

SQL 中没有全称量词 (forall)，但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： $(\forall x)P\equiv \neg(\exists x(\neg P))$

由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。其 SQL 语句如下：

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

从而用 EXIST/NOT EXIST 来实现带全称量词的查询

例：查询至少选修了学生 201215122 选修的全部课程的学生号码

本查询可以用逻辑蕴涵来表达：查询学号为 x 的学生，对所有的课程 y，只要 201215122 学生选修了课程 y，则 x 也选修了 y。形式化表示如下：

用 p 表示谓词“学生 201215122 选修了课程 y”

用 q 表示谓词“学生 x 选修了课程 y”

则上述查询为 $(\forall y)p\rightarrow q$

SQL 语言中没有蕴涵逻辑运算，但是可以利用谓词演算将一个逻辑蕴涵的谓词等价转换为

$p\rightarrow q\equiv \neg p\vee q$

该查询可以转换为如下等价形式：

$(\forall y)p\rightarrow q\equiv \neg (\exists y(\neg (p\rightarrow q)))\equiv \neg (\exists y(\neg (\neg p\vee q)))=\neg \exists y(p\wedge \neg q)$

它所表达的语义为：不存在这样的课程 y，学生 201215122 选修了 y，而学生 x 没有选。

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

3.4.4 集合查询

SELECT 语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操作。

集合操作主要包括并操作 UNION、交操作 INTERSECT 和差操作 EXCEPT。

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

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

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

本查询实际上是求计算机科学系的所有学生与年龄不大于 19 岁的学生的并集。使用 UNION 将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用 UNION ALL 操作符。

例：查询计算机科学系的学生与年龄不大于 19 岁的学生的交集

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

这实际上就是查询计算机科学系中年龄不大于 19 岁的学生。

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

3.4.5 基于派生表的查询

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

例：找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade) 
          FROM SC 
          GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >= Avg_sc.avg_grade

这里 FROM 子句中的子查询将生成一个派生表 Avg_sc。该表由 avg_sno 和 avg_grade 加两个属性组成，记录了每个学生的学号及平均成绩。主查询将 SC 表与 Avg_sc 按学号相等进行连接，选出修课成绩大于其平均成绩的课程号。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询 SELECT 子句后面的列名为其默认属性

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

SELECT Sname
FROM Student, (SELECT Sno 
               FROM SC 
               WHERE Cno='1') AS SC1
WHERE Student.Sno=SC1.Sno;

通过 FROM 子句生成派生表时，AS 关键字可以省略，但必须为派生关系指定一个别名。

3.4.6 SELECT 语句的一般格式

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

目标列表达式的可选格式

(1) *
(2) <表名>.*
(3) COUNT([DISTINCT | ALL]*)
(4) [<表名>.]<属性列名表达式> [，[<表名>].<属性列名表达式>] …

其中，<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算 (+, -，*，/) 组成的运算公式。

3.5 数据更新

3.5.1 插入数据

SQL 的数据插入语句 INSERT 通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。

插入元组

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

3.5.2 修改数据

3.5.3 删除数据

3.6 空值的处理

3.7 视图

3.7.1 定义视图

3.7.2 查询视图

3.7.3 更新视图

3.7.4 视图的作用

3.8 小结

第 4 章数据库安全性

4.1 计算机安全性概述

4.1.1 计算机系统的三类安全性问题

技术安全类，管理安全类，政策法律类

4.1.2 安全标准简介

TCSEC标准

描述安全性级别划分的指标：安全策略，责任，保证，文档

TCSEC/TDI安全级别划分

各安全级别之间：偏序向下兼容

CC标准

提出国际公认的表述信息技术安全性的结构

把信息产品的安全要求分为：安全功能要求，安全保证要求

CC评估保证级划分

评估保证级	定义	TCSEC安全级别（近似相当）
EAL1	功能测试（functionally tested）
EAL2	结构测试（structurally tested）	C1
EAL3	系统地测试和检查（methodically tested and checked）	C2
EAL4	系统地设计、测试和复查（methodically designed， tested， and reviewed）	B1
EAL5	半形式化设计和测试（semiformally designed and tested）	B2
EAL6	半形式化验证的设计和测试（semiformally verified design and tested)	B3
EAL7	形式化验证的设计和测试（formally verified design and tested）	A1

4.2 数据库安全性控制

常用方法：用户标识和鉴定，存取控制，视图，审计，密码存储

4.2.1 用户身份鉴别

用户标识与鉴别：

系统提供的最外层安全保护措施

口令：系统核对口令以鉴别用户身份

4.2.2 存取控制

存取控制机制组成：定义用户权限，合法权限检查

用户权限定义和合法权检查机制一起组成DBMS的安全子系统

常用存取控制方法：自主存取控制，强制存取控制

4.2.3 自主存取控制方法

通过SQQL的GRANT语句和REVOKE语句实现

用户权限组成：数据对象，操作类型

4.2.4 授权：授予与收回

授予GRANT

GRANT语句的一般格式

GRANT <权限>[,<权限>]...
[ON<对象类型><对象名>]
TO<用户>[,<用户>]...
[WITH GRANT OPTION];

语义：将对指定操作对象的指定操作权限授予指定的用户

例：把查询Student表权限授给用户U1

GRANT SELECT
ON TABLE Student
TO U1;

例：把对Student表和Course表的全部权限授予用户U2和U3

GRANT ALL PRIVILIGES
ON TABLE Student,Course
TO U2,U3;

把对表SC的查询权限授予所有用户

GRANT SELECT
ON TABLE SC
TO PUBLIC;

例：把查询Student表和修改学生学号的权限授给用户U4

GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U4;

对属性列的授权时必须明确指出相应属性列名

例：把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

U5可以传播此权限给U6，并且允许U6再将此权限授予其他用户

GRANT INSERT 
ON TABLE SC 
TO U6 
WITH GRANT OPTION;

同样U6还可以将此权限授予U7

GRANT INSERT 
ON TABLE SC 
TO U7;

但U7不能再传播此权限

回收REVOKE

授予的权限可以由DBA或其他授权者用REVOKE语句收回

REVOKE <权限>[,<权限>]...
[ON <对象类型> <对象名>]
FROM <用户名>[,<用户>]...;

例：把用户U4修改学生学号的权限收回

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

例：收回所有用户对表SC的查询权限

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

例：把用户U5对SC表的INSERT权限收回

REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;

将用户U5的INSERT权限收回的时候必须级联(CASCADE)收回

系统只收回直接或间接从U5处获得的权限

创建数据库模式的权限

CREATE USER <username>
[WITH] [DBA|RESOURCE|CONNECT]

拥有的权限	可否执行的操作
拥有的权限	CREATE USER	CREATE SCHEMA	CREATE TABLE	登录数据库执行数据查询和操纵
DBA	可以	可以	可以	可以
RESOURCE	不可以	不可以	可以	可以
CONNECT	不可以	不可以	不可以	可以，但必须拥有相应权限

4.2.5 数据库角色

被命名的一组与数据库操作相关的权限

角色是权限的集合

可以为一组具有相同权限的用户创建一个角色

简化授权的过程

角色的创建

CREATE ROLE <角色名>

给角色授权

GRANT <权限> [,<权限>]...
ON <对象类型>对象名
TO <角色> [,<角色>]...

将一个角色授予其他的角色或用户

GRANT <角色1>[,<角色2>]...
TO <角色3>[,<用户1>]...
[WITH ADMIN OPTION]

角色权限的收回

REVOKE <权限>[,<权限>]...
ON <对象类型><对象名>
FROM <角色>[,<角色>]...

例：通过角色来实现将一组权限授予一个用户

首先创建一个角色R1

CREATE ROLE R1;

然后使用GRANT语句，使角色R1拥有Studennt表的SELECT、UPDATE、INSERT权限

GRANT SELECT,UPDATE,INSERT
ON TABLE Student
TO R1;

将这个角色授予王平，张明，赵玲，使他们具有角色R1所包含的全部权限

GRANT R1
TO 王平,张明,赵玲;

可以一次性通过回收王平的这3个权限

REVOKE R1
FROM 王平;

例：角色权限修改

GRANT DELETE
ON TABLE Student
TO R1

REVOKE SELECT
ON TABLE Student
FROM R1;

4.2.6 强制存取控制方法

4.3 视图机制

例：建立计算系学生的视图，把对该视图的SELECT权限授予王平，把该视图上的所有操作权限授予张明

先建立计算机系学生的视图CS_Student

CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS';

在视图上进一步定义存取权限

GRANT SELECT
ON CS_Student
TO 王平;

GRANT ALL PRIVILIGES
ON CS_Student
TO 张明;

4.4 审计

用户级审计：

针对自己创建的数据库表或视图进行审计

记录所有用户对这些表或视图的一切成功和（或）不成功的访问要求以及各种类型的SQL操作

系统级审计：

DBA设置

监测成功或失败的登录要求

监测GRANT和REVOKE操作以及其他数据库级权限下的操作

AUDIT语句：设置审计功能

NOAUDIT语句：取消审计功能

例：对修改SC表结构或修改SC表数据的操作进行审计

AUDIT ALTER,UPDATE
ON SC;

例：取消对SC表的一切审计

NOAUDIT ALTER,UPDATE
ON SC;

4.5 数据加密

4.6 其他安全性保护

4.7 小结

第 5 章数据库完整性

数据的完整性：防止数据库中存在不正确的数据

数据的安全性：保护数据库防止恶意的破坏和非法的存取

5.1 实体完整性

5.1.1 定义实体完整性

例：将Student表中的Sno属性定义为码

在列级定义主码

CREATE TABLE Student
  (Sno CHAR(9) PRIMARY KEY,
   Sname CHAR(20) NOT NULL,
   Sage SMALLINT,
   Sdept CHAR(20));

在表级定义主码

CREATE TABLE Student
  (Sno CHAR(9),
   Sname CHAR(20) NOT NULL,
   Sage SMALLINT,
   Sdept CHAR(20),
   PRIMARY KEY(Sno)
);

例：将SC表中Sno，Cno属性组定义为码

CREATE TABLE SC
 (Sno CHAR(9) NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY(Sno,Cno)    /*只能在表级定义主码*/
);

有两个及以上的主码时只能在表级定义

5.1.2 实体完整性检查和违约处理

5.2 参照完整性

5.2.1 定义参照完整性

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为主码

用REFERENCES短语指明这些外码参照哪些表的主码

例：关系SC中一个元组表示一个学生选修的某门课程的成绩，(Sno, Cno)是主码，Sno，Cno分别参照引用Student表的主码和Course表的主码

CREATE TABLE SC
    (Sno CHAR(9) NOT NULL,
     Cno CHAR(4) NOT NULL,  
     Grade SMALLINT,
     PRIMARY KEY (Sno, Cno),   /*在表级定义实体完整性*/
     FOREIGN KEY (Sno) REFERENCES Student(Sno),    /*在表级定义参照完整性*/
     FOREIGN KEY (Cno) REFERENCES Course(Cno)     /*在表级定义参照完整性*/
);

5.2.2 参照完整性检查和违约处理

例：显式说明参照完整性的违约处理示例

CREATE TABLE SC
    (Sno   CHAR(9)  NOT NULL,
     Cno   CHAR(4)  NOT NULL,
     Grade  SMALLINT,
     PRIMARY KEY(Sno, Cno),			
     FOREIGN KEY (Sno) REFERENCES Student(Sno) 
	 ON DELETE CASCADE     /*级联删除SC表中相应的元组*/
     ON UPDATE CASCADE,    /*级联更新SC表中相应的元组*/
     FOREIGN KEY (Cno) REFERENCES Course(Cno) 	                    
         ON DELETE NO ACTION 	
         /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
         ON UPDATE CASCADE
         /*当更新course表中的cno时，级联更新SC表中相应的元组*/
);

5.3 用户定义的完整性

5.3.1 属性上的约束条件

CREATE TABLE时定义

列值非空NOT NULL

列值唯一UNIQUE

检查列值是否满足一个布尔表达式CHECK

例：建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

CREATE TABLE DEPT
   (Deptno NUMERIC(2),
    Dname CHAR(9) UNIQUE,    /*要求Dname列值唯一*/
    Location CHAR(10),
    PRIMARY KEY (Deptno)
);

例：Student表的Ssex只允许取“男”或“女”

CREATE TABLE Student
   (Sno  CHAR(9) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,                     
    Ssex  CHAR(2) CHECK (Ssex IN (‘男’,‘女’)),    /*性别属性Ssex只允许取'男'或'女'*/
    Sage SMALLINT,
    Sdept CHAR(20)
);

5.3.2 元组上的约束条件

例：当学生的性别是男时，其名字不能以Ms.打头

CREATE TABLE Student
   (Sno CHAR(9),
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY (Sno),
    CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
    /*定义了元组中Sname和Ssex两个属性值之间的约束条件*/
);

5.4 完整性约束命名子句

CONSTRAINT 约束

CONSTRAINT <完整性约束条件名> [PRIMARY KEY短语 | FOREIGN KEY短语 | CHECK短语]

例：建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”

CREATE TABLE Student
   (Sno NUMERIC(6)
    CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
    Sname  CHAR(20)  
    CONSTRAINT C2 NOT NULL,
    Sage  NUMERIC(3)
    CONSTRAINT C3 CHECK (Sage < 30),
    Ssex CHAR(2)
    CONSTRAINT C4 CHECK (Ssex IN ('男','女')),
    CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

*5.5 域中的完整性限制

5.6 断言

5.7 触发器

5.7.1 定义触发器

CREATE TRIGGER <触发器名>  
       {BEFORE | AFTER} <触发事件> ON <表名>
        FOR EACH  {ROW | STATEMENT}
       [WHEN <触发条件>]
        <触发动作体>

例：假设在TEACHER表上创建了一个AFTER UPDATE触发器，如果表TEACHER有1000行，执行如下语句：

UPDATE TEACHER SET Deptno=5;

如果该触发器为语句级触发器，那么执行完该语句后，触发器只发生一次

如果是行级触发器，触发动作将执行1000次

例：定义一个BEFORE行级触发器，为教师表TEACHER定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”

CREATE TRIGGER Insert_Or_Update_Sal 
  BEFORE INSERT OR UPDATE ON Teacher  
  /*触发事件是插入或更新操作*/
  FOR EACH ROW    /*行级触发器*/
  AS BEGIN    /*定义触发动作体，是PL/SQL过程块*/
    IF (new.Job='教授') AND (new.Sal < 4000) THEN   
    new.Sal :=4000;                
    END IF;
END;

例：定义AFTER行级触发器，当教师表TEACHER的工资发生变化后就自动在工资变化表Sal_log中增加一条相应记录

首先建立工资变化表 Sal_log

CREATE TABLE Sal_log
  (Eno  NUMERIC(4)  references teacher(eno),
   Sal  NUMERIC(7,2),
   Username  char(10),
   Date  TIMESTAMP
);

CREATE TRIGGER Insert_Sal               	
  AFTER INSERT ON Teacher      	/*触发事件是INSERT*/
  FOR EACH ROW
  AS BEGIN
    INSERT INTO Sal_log VALUES(
      new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
END;

CREATE TRIGGER Update_Sal            	
   AFTER UPDATE ON Teacher    /*触发事件是UPDATE */
   FOR EACH ROW
   AS BEGIN 
     IF (new.Sal <> old.Sal) THEN INSERT INTO Sal_log VALUES(
          new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
     END IF;
END;

5.7.2 激活触发器

例：执行修改某个教师工资的SQL语句，激活上述定义的触发器

UPDATE Teacher SET Sal=800 WHERE Ename='陈平';

执行顺序：

执行触发器Insert_Or_Update_Sal

执行SQL语句"UPDATE Teacher SET Sal=800 WHERE Ename='陈平';"

执行触发器Insert_Sal;

执行触发器Update_Sal

5.7.3 删除触发器

DROP TRIGGER <触发器名> ON <表名>;

例：删除教师表Teacher上的触发器Insert_Sal

DROP TRIGGER Insert_Sal ON Teacher;

5.8 小结

第 6 章关系数据理论数据库设计

6.1 问题的提出

针对具体问题，如何构造一个适合于它的数据模式

6.1.1 概念回顾

6.1.2 关系模式的形式化定义

关系模式是一个 5 元组：R(U, D, DOM, F)

R：关系名

U：组成该关系的属性名集合

D：属性组U中属性所来自的域

DOM：属性向域的映像集合

F：属性间数据的依赖关系集合

6.1.3 什么是数据依赖

一个关系内部属性与属性之间的约束关系

语义的体现

数据依赖的类型：函数依赖(简记FD)，多值依赖(简记MVD)

6.1.4 关系模式的简化定义

R(U, F)

当且仅当U上的一个关系 r 满足 F 时，r 称为关系模式 R(U, F) 的一个关系

6.1.5 数据依赖对关系模式影响

6.2 规范化

6.2.1 函数依赖

函数依赖

设 R(U) 是一个属性集 U 上的关系模式，X 和 Y 是 U 的子集。

若对于 R(U) 的任意一个可能的关系 r，r 中不可能存在两个元组在 X 上的属性值相等，而在 Y 上的属性值不等，则称 "X 函数确定 Y" 或 "Y 函数依赖于 X"，记作 X→Y

平凡函数依赖与非平凡函数依赖

在关系模式 R(U) 中，对于 U 的子集 X 和 Y

如果 X→Y，但 $Y\nsubseteq X$ ，则称 X→Y 是非平凡的函数依赖

若 X→Y，但 $Y\subseteq X$ ，则称 X→Y 是平凡的函数依赖

例：在关系 SC(Sno, Cno, Grade) 中

非平凡函数依赖：(Sno, Cno)→Grade

平凡函数依赖：(Sno, Cno)→Sno, (Sno, Cno)→Cno

若 X→Y，则 X 称为这个函数依赖的决定属性组，也称为决定因素

若 X→Y，Y→X，则记作 X←→Y

若 Y 不函数依赖于 X，则记作 X\→Y

例：Student(Sno, Sname, Ssex, Sage, Sdept)

假设不允许重名，则有：

Sno→Ssex，Sno→Sage，Sno→Sdept，Sno←→Sname，Sname→Ssex，Sname→Sage，Sname→Sdept

但Ssex\→Sage

完全函数依赖与部分函数依赖

在 R(U) 中，如果 X→Y，并且对于 X 的任何一个真子集 X'，都有 X'\→Y，则称 Y 对 X 完全函数依赖，记作 $X\overset{F}{\rightarrow}Y$

若 X→Y，但 Y 不完全函数依赖于 X，则称 Y 对 X 部分函数依赖，记作 $X\overset{P}{\rightarrow}Y$

例：(Sno, Cno) $\overset{F}{\rightarrow}$ Grade 是完全函数依赖，(Sno, Cno) $\overset{P}{\rightarrow}$ Sdept 是部分函数依赖

因为 Sno→Sdept 成立，且 Sno 是 (Sno, Cno) 的真子集

传递函数依赖

在 R(U) 中，如果 X→Y，( $Y \nsubseteq X$ )，Y\→X，Y→Z，则称 Z 对 X 传递函数依赖，记为：X(传递)→Z

注：如果 Y→X，即 X←→ Y，则 Z 直接依赖于 X

例：在关系 Std(Sno, Sdept, Mname)中，有：

Sno→Sdept，Sdept→Mname，Mname传递函数依赖于Sno

6.2.2 码

设 K 为 R<U, F> 中的属性或属性组合

若 K $\overset{F}{\rightarrow}$ U，则 K 称为 R 的候选码

主属性：包含在任何一个候选码中的属性

非主属性：不包含在任何码中的属性称为非主属性或非码属性

全码：整个属性组是码

关系 S(Sno, Sdept, Sage)，单个属性 Sno 是码，SC(Sno, Cno, Grade) 中，(Sno, Cno) 是码

例：关系模式 R(P, W, A)

P：演奏者，W：作品，A：听众

一个演奏者可以演奏多个作品，一作品可被多个演奏者演奏，听众可以欣赏不同演奏者的不同作品

码为 (P, W, A)，即全码

外部码：关系模式 R 中属性或属性组 X 并非 R 的码，但 X 是另一个关系模式的码，则称 X 是 R 的外部码，也称外码

例：在 SC(Sno, Cno, Grade) 中，Sno 不是码，但 Sno 是关系模式 S(Sno, Sdept, Sage) 的码，则 Sno 是关系模式 SC 的外部码

6.2.3 范式

范式是符合某一种级别的关系模式的集合

各种范式之间存在联系：

1NF $\supset$ 2NF $\supset$ 3NF $\supset$ BCNF $\supset$ 4NF $\supset$ 5NF

1NF的定义：如果一个关系模式 R 的所有属性都是不可分的基本数据项，则 R∈1NF

6.2.4 2NF

若 R∈1NF，且每一个非主属性完全函数依赖于码，则 R∈2NF

6.2.5 3NF

关系模式 R<U, F> 中若不存在这样的码 X、属性组 Y 及非主属性 Z(Z $\nsubseteq$ Y)，使得X→Y，Y→Z 成立， Y \→ X，则称 R<U, F> ∈ 3NF。

若R∈3NF，则每一个非主属性既不部分依赖于码也不传递依赖于码

一个关系模式 R 属于第三范式，当且仅当它满足以下两个条件：

满足第二范式（2NF）：即关系模式 R 已经消除了非主属性对候选关键字的部分函数依赖。例如，假设有一个关系表 学生选课（学号，课程号，课程名，成绩），候选键是 （学号，课程号），这里 “课程名” 只依赖于 “课程号”，而 “课程号” 是候选键 （学号，课程号） 的一部分，存在部分函数依赖，就不满足 2NF。

关系模式 R 中的每一个非主属性都不传递依赖于候选键：比如有一个关系表 员工（员工编号，部门编号，部门名称，员工工资），候选键是 “员工编号”，“部门名称” 通过 “部门编号” 来决定，而 “部门编号” 由 “员工编号” 决定，即 “部门名称” 传递依赖于 “员工编号”，这种情况就不符合 3NF 要求。

示例：
假设一开始有一个关系表设计如下：
| 员工编号 | 部门编号 | 部门名称 | 部门电话 | 员工工资 |
| 001 | 1001 | 研发部 | 88888888 | 8000 |
| 002 | 1001 | 研发部 | 88888888 | 8500 |

这里 “部门名称” 和 “部门电话” 通过 “部门编号” 传递依赖于 “员工编号”，不符合 3NF。

可以将其拆分为以下两个表来满足 3NF 要求：

员工表：
| 员工编号 | 部门编号 | 员工工资 |
| 001 | 1001 | 8000 |
| 002 | 1001 | 8500 |

部门表：
| 部门编号 | 部门名称 | 部门电话 |
| 1001 | 研发部 | 88888888 |

通过这样的拆分，消除了传递依赖，达到了 3NF，数据存储和操作会更加合理、高效。

6.2.6 BCNF

关系模式 R<U, F>∈1NF，若 X→Y 且 $Y\nsubseteq X$ 时 X 必含有码，则 R<U, F>∈BCNF。

即每一个决定属性因素都包含码

6.2.7 多值依赖

设 R(U) 是一个属性集 U 上的一个关系模式， X、 Y 和 Z 是 U 的子集，并且 Z＝U－X－Y。

R(U) 中多值依赖 X→→Y成立，当且仅当对 R(U) 的任一关系 r，给定的一对 (x, z) 值，有一组 Y 的值，这组值仅仅决定于 x 值而与 z 值无关

6.2.8 4NF

关系模式 R<U, F>∈1NF，如果对于 R 的每个非平凡多值依赖 X→→Y（Y $\nsubseteq$ X），X 都含有码，则 R∈4NF

如果 R ∈ 4NF，则 R ∈ BCNF

不允许有非平凡且非函数依赖的多值依赖

允许的非平凡多值依赖是函数依赖

6.2.9 规范化小结

6.3 数据依赖的公理系统

逻辑蕴含：对于满足一组函数依赖 F 的关系模式 R<U, F>，其任何一个关系 r，若函数依赖 $X\to Y$ ，都成立，(即 r 中任意两元组t, s，若 t[X]=s[X]，则 t[Y]=s[Y])，则称 F 逻辑蕴含 $X\to Y$

6.3.1 Armstrong 公理系统

关系模式R <U, F > 来说有以下的推理规则：

自反律：若 $Y\subseteq X \subseteq U$ ，则 X →Y 为 F 所蕴含。

增广律：若 X→Y 为 F 所蕴含，且 $Z\subseteq U$ ，则 XZ→YZ 为 F 所蕴含。

传递律：若 X→Y 及 Y→Z 为 F 所蕴含，则 X→Z 为 F 所蕴含。

F+：在关系模式 R(U, F) 中为 F 所逻辑蕴含的函数依赖的全体叫做 F 的闭包，记作 F+

$F^A$ ：在关系模式 R(U, F) 中，由F出发，根据Armstrong公理导出的所有函数依赖组成的集合记作FA。

6.3.2 导出规则

6.3.3 函数依赖闭包

求闭包的算法：求属性集 $X(X\subseteq U)$ 关于 U 上的函数依赖集 F 的闭包 $X^+_F$

输入：X, F，输出： $X^+_F$

步骤：

(1) 令 $X^{(0)}=X,i=0$

(2) 求 B，这里 $B=\{A|(\exists V)(\exists W)(V\to W\in F\wedge V\subseteq X^{(i)}\wedge A\in W)\}$ ；

(3)

例：已知关系模式 R<U, F>，其中 U={A, B, C, D, E}；

$F=\{AB\to C,B\to D,C\to E,EC\to B,AC\to B\}$ ，求 $(AB)^+_F$

解：设 $X^{(0)}=AB$

(1) $X^{(1)}=AB\cup CD=ABCD$

(2) $X^{(0)}\neq X^{(1)},X^{(2)}=X^{(1)}\cup BE=ABCDE$

(3) $X^{(2)}=U$ ，算法终止

故 $(AB)^+_F=ABCDE$

给定R(U, F)，X $\subseteq$ U，若满足：

(1) $X^+_F$ = U (X能决定所有属性集)

(2) $\forall A\in X$ ， $(X-A)^+_F\neq U$ (没有冗余的属性)，则称 X 是 R(U, F) 的候选关键字

对于给定的关系模式 R(U, F)，可以将其属性分为 4 类：

仅在 F 函数依赖左部出现的属性 L 类

仅在 F 函数依赖右部出现的属性 R 类

在 F 函数依赖左右两边均未出现的属性 N 类

在 F 函数依赖左右两边均出现的属性 LR 类

6.3.5 函数依赖集等价

如果 G+=F+，就说函数依赖集 F 覆盖 G(F 是 G 的覆盖，或 G 是 F 的覆盖），或 F 与 G 等价。

6.3.6 最小依赖集

如果函数依赖集F满足下列条件，则称F为一个极小函数依赖集。亦称为最小依赖集或最小覆盖。

(1) F中任一函数依赖的右部仅含有一个属性。

(2) F中不存在这样的函数依赖X→A，使得F与F-{X→A}等价。

即 X→A 可通过 X→...→A 得到

(3) F中不存在这样的函数依赖 X→A， X有真子集Z使得 F-{X→A}∪{Z→A} 与 F 等价

6.3.7 极小化过程

确定 AC 为候选关键字后就不用考虑含 AC 的属性组了

*6.4 模式的分解

6.4.1 模式分解的三个定义

6.4.2 分解的无损连接性和保持函数依赖性

6.4.3 模式分解的算法

6.5 小结

第 7 章数据库设计

7.1 数据库设计概述

7.1.1 数据库设计的特点

7.1.2 数据库设计方法

7.1.3 数据库设计的基本步骤

7.1.4 数据库设计过程中的各级模式

7.2 需求分析

7.2.1 需求分析的任务

7.2.2 需求分析的方法

7.2.3 数据字典

7.3 概念结构设计

7.3.1 概念结构

7.3.2 概念结构设计的方法与步骤

7.3.3 数据抽象与局部视图设计

数据抽象

局部视图设计

7.3.4 视图的集成

基本E-R图：消除不必要的冗余后的初步E-R图

7.4 逻辑结构设计

7.4.1 E-R 图向关系模型的转换

7.4.2 数据模型的优化

7.4.3 设计用户子模式

7.5 物理结构设计

7.5.1 数据库物理设计的内容和方法

7.5.2 关系模式存取方法选择

7.5.3 确定数据库的存储结构

7.5.4 评价物理结构

7.6 数据库的实施和维护

7.6.1 数据的载入和应用程序的调试

7.6.2 数据库的试运行

7.6.3 数据库的运行和维护

7.7 小结

7.8 作业

学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生，每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此学校的概念模型。

某工厂生产若干产品，每种产品由不同的零件组成，有的零件可用在不同的产品上。这些零件由不同的原材料制成，不同零件所用的材料可以相同。这些零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中。请用 E-R 图画出此工厂产品、零件、材料、仓库的概念模型。

第 8 章数据库编程

8.1 嵌入式 SQL

8.1.1 嵌入式 SQL 的处理过程

8.1.2 嵌入式 SQL 语句与主语言之间的通信

例：依次检查某个系的学生记录，交互式更新某些学生年龄

/*这段代码主要用于在嵌入式 SQL 编程环境里进行变量声明以及引入相关的通信区域定义*/
EXEC SQL BEGIN DECLARE SECTION;    /*主变量说明开始*/ 
	char Deptname[20];
	char Hsno[9];
	char Hsname[20]; 
	char Hssex[2];
	int HSage;
	int NEWAGE;
EXEC SQL END DECLARE SECTION;    /*主变量说明结束*/
long SQLCODE;
EXEC SQL INCLUDE SQLCA;    /*定义SQL通信区*/

8.1.3 不用游标的 SQL 语句

8.1.4 使用游标的 SQL 语句

8.1.5 动态 SQL

8.2 过程化 SQL

8.2.1 过程化 SQL 的块结构

8.2.2 变量和常量的定义

8.2.3 流程控制

8.3 存储过程和函数

8.3.1 存储过程

存储过程：由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。

创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>;

例：利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount INT,amount FLOAT) 
       /*定义存储过程 TRANSFER，其参数为转入账户、转出账户、转账额度*/
AS DECLARE		/*定义变量*/
    totalDepositOut Float;
    totalDepositIn Float;
    inAccountnum INT;
BEGIN    /*检查转出账户的余额 */	                       
    SELECT Total INTO totalDepositOut FROM Accout 
    WHERE accountnum=outAccount; 
    IF totalDepositOut IS NULL THEN   
                                   		   /*如果转出账户不存在或账户中没有存款*/
        ROLLBACK; 	   /*回滚事务*/
        RETURN;
    END IF;

8.3.2 函数

*8.3.3 过程化 SQL 中的游标

8.4 ODBC 编程

8.4.1 ODBC 概述

8.4.2 ODBC 工作原理概述

8.4.3 ODBC API 基础

8.4.4 ODBC 的工作流程

*8.5 OLE DB

*8.6 JDBC 编程

8.7 小结

第 9 章关系查询处理和查询优化

9.1 关系数据库系统的查询处理

9.1.1 查询处理步骤

查询分析

查询检查

查询优化

查询执行

9.1.2 实现查询操作的算法示例

9.2 关系数据库系统的查询优化

9.2.1 查询优化概述

9.2.2 一个实例

9.3 代数优化

9.3.1 关系代数表达式等价变换规则

9.3.2 查询树的启发式优化

9.4 物理优化

9.4.1 基于启发式规则的存取路径

9.4.2 基于代价估算的优化

*9.5 查询计划的执行

9.6 小结

第 10 章数据库恢复技术

10.1 事务的基本概念

10.1.1 事务的定义

一个数据库操作序列

一个不可分割的工作单位

恢复和并发控制的基本单位

BEGIN TRANSACTION
    SQL 语句1
    SQL 语句2
    ……
    COMMIT

10.1.2 事务的特性

原子性

事务是数据库的逻辑工作单位

一致性

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态

隔离性

对并发执行而言一个事务的执行不能被其他事务干扰

持续性

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

10.2 数据库恢复概述

10.3 故障的种类

事务内部的故障

有的是可以通过事务程序本身发现的，有的是非预期的(运算溢出等)

例：银行转账事务，这个事务把一笔金额从一个账户甲转给另一个账户乙。

BEGIN TRANSACTION 
/*开启事务，意味着后续操作视作一个整体*/
/*数据库系统会确保这些操作要么全部成功执行并提交，要么在中间出现问题时全部撤销*/
    读账户甲的余额BALANCE;
    BALANCE=BALANCE-AMOUNT;/*(AMOUNT 为转账金额)*/
    写回BALANCE；
    IF(BALANCE < 0 ) THEN
     {
         打印'金额不足，不能转账';
         ROLLBACK；(撤销刚才的修改，恢复事务)
     }
     ELSE
     {
          读账户乙的余额BALANCE1;
          BALANCE1=BALANCE1+AMOUNT;
          写回BALANCE1;
          COMMIT;/*表示整个事务顺利完成，将事务过程中对数据库所做的所有修改永久性保存到数据库*/
      }

系统故障

称为软故障，是指造成系统停止运转的任何事件，使得系统要重新启动。

介质故障

计算机病毒

10.4 恢复的实现技术

10.4.1 数据转储

动态转储

需要把动态转储期间各事务对数据库的修改活动登记下来，建立日志文件

后备副本加上日志文件才能把数据库恢复到某一时刻的正确状态

10.4.2 登记日志文件

10.5 恢复策略

10.5.1 事务故障的恢复

事务故障：事务在运行至正常终止点前被终止

恢复方法：由恢复子系统应利用日志文件撤消（UNDO）此事务已对数据库进行的修改

10.5.2 系统故障的恢复

系统故障：未完成事务对数据库的更新已写入数据库，已提交事务对数据库的更新还留在缓冲区没来得及写入数据库

恢复方法：Undo 故障发生时未完成的事务，Redo 已完成的事务

10.5.3 介质故障的恢复

10.6 具有检查点的恢复技术

10.7 数据库镜像

10.8 小结

第 11 章并发控制

11.1 并发控制概述

11.2 封锁

封锁就是事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁。

加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。

排它锁又称为写锁

若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁

共享锁又称为读锁

若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁

保证其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改

11.3 封锁协议

11.4 活锁和死锁

11.4.1 活锁

事务T1封锁了数据R

事务T2又请求封锁R，于是T2等待。

T3也请求封锁R，当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待。

T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求……

T2有可能永远等待，这就是活锁的情形

11.4.2 死锁

事务T1封锁了数据R1

T2封锁了数据R2

T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁

接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1上的锁

这样T1在等待T2，而T2又在等待T1，T1和T2两个事务永远不能结束，形成死锁

死锁的预防

一次封锁法：要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行

顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁

死锁的诊断与解除

超时法：如果一个事务的等待时间超过了规定的时限，就认为发生了死锁

等待图法：并发控制子系统周期性地（比如每隔数秒）生成事务等待图，检测事务。如果发现图中存在回路，则表示系统中出现了死锁。

解除死锁：选择一个处理死锁代价最小的事务，将其撤消，释放此事务持有的所有的锁，使其它事务能继续运行下去

11.5 并发调度的可串行性

11.5.1 可串行化调度

11.5.2 冲突可串行化调度

11.6 两段锁协议

两段锁协议：指所有事务必须分两个阶段对数据项加锁和解锁

事务Ti遵守两段锁协议，其封锁序列是：

Slock A Slock B Xlock C Unlock B Unlock A Unlock C；

|← 扩展阶段 →| |← 收缩阶段 →|

事务Tj不遵守两段锁协议，其封锁序列是：

Slock A Unlock A Slock B Xlock C Unlock C Unlock B；

事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件

不表示不会发生死锁

11.7 封锁的粒度

封锁对象的大小称为封锁粒度

封锁的粒度越大，数据库所能够封锁的数据单元就越少，并发度就越小，系统开销也越小；

封锁的粒度越小，并发度较高，但系统开销也就越大

11.7.1 多粒度封锁

多粒度封锁：在一个系统中同时支持多种封锁粒度供不同的事务选择

多粒度树：以树形结构来表示多级封锁粒度，根结点是整个数据库，表示最大的数据粒度，叶结点表示最小的数据粒度

显式封锁: 直接加到数据对象上的封锁

隐式封锁: 该数据对象没有独立加锁，是由于其上级结点加锁而使该数据对象加上了锁

11.7.2 意向锁

*11.8 其他并发控制机制

11.8.1 多版本并发控制

11.8.2 改进的多版本并发控制

11.9 小结

第 12 章数据库技术发展概述

12.1 数据库管理系统的基本功能

12.2 数据库管理系统的系统结构

12.2.1 数据库管理系统的层次结构

12.2.2 关系数据库管理系统的运行

12.3 语言处理层

12.3.1 语言处理层的任务和工作步骤

12.3.2 解释方法

12.3.3 预编译方法

12.4 数据存取层

12.4.1 数据存取层的系统结构

12.4.2 数据存取层的功能子系统

12.5 缓冲区管理

12.6 数据库的物理组织

12.7 小结

第 13 章大数据管理

13.1 数据库技术发展历史回顾

13.2 数据库发展的三个阶段

13.2.1 第一代数据库系统

13.2.2 第二代数据库系统

13.2.3 新一代数据库系统

13.3 数据库系统发展的特点

13.3.1 数据模型的发展

13.3.2 数据库技术与相关技术相结合

13.3.3 面向应用领域的数据库新技术

13.4 数据管理技术的发展趋势

13.4.1 数据管理技术面临的挑战

13.4.2 数据管理技术的发展与展望

13.5 小结

第 14 章数据仓库与数据挖掘

14.1 大数据概述

14.1.1 什么是大数据

14.1.2 大数据的特征

14.2 大数据的应用

14.2.1 感知现在预测未来——互联网文本大数据管理与挖掘

14.2.2 数据服务实时推荐一基于大数据分析的用户建模

14.3 大数据管理系统

14.3.1 NoSQL 数据管理系统

14.3.2 NewSQL 数据库系统

14.3.3 MapReduce 技术

14.3.4 大数据管理系统的新格局

14.4 小结

第 15 章内存数据库系统

15.1 概述

15.2 内存数据库的发展历程

15.3 内存数据库的特性

15.4 内存数据库的关键技术

15.4.1 数据存储

15.4.2 查询处理及优化

15.4.3 并发与恢复

15.5 小结

第 16 章数据仓库与联机分析处理技术

16.1 数据仓库技术

16.2 联机分析处理技术

16.3 数据挖掘技术

16.4 大数据时代的新型数据仓库

16.5 小结

附录

测试题

SELECT DISTINCT AVG(ua.score * 1.0)  AS AvgScore
-- * 1.0 是为了将整数型转为浮点型
FROM user_action_tb ua    -- 取别名 ua
JOIN recommend_tb rt     -- 默认和 FROM 的表连接
ON rt.rec_user = ua.user_id    -- 在 JOIN 后面表示连接的具体条件
WHERE rt.rec_info_l = ua.hobby_l

大厂数据库及SQL面试题

数据库系统与文件系统的主要区别是文件系统不能解决数据冗余和数据独立性问题，而数据库系统可以解决

1.数据库系统与文件系统的主要区别是( )

(A) 数据库系统复杂，而文件系统简单

(B) 文件系统不能解决数据冗余和数据独立性问题，而数据库系统可以解决

(D) 文件系统管理的数据量较少，而数据库系统可以管理庞大的数据量

例：SQL Server 是一个基于( D )

(A) 层次模型的 DBMS

(B) 网状模型的 DBMS

SQL Server 本身不是一个单纯的应用程序，它是数据库管理系统（DBMS，Database Management System）。

(D) 关系模型的 DBMS

关系模型是以二维表的形式来组织和存储数据，表中的每一行代表一条记录，每一列代表一个属性（字段），通过定义表之间的关系（如通过主键、外键等机制）来关联不同的表，实现复杂的数据结构表示。SQL Server 正是基于关系模型的数据库管理系统。

例：要保证数据库物理数据独立性，需要修改的是( B )

(A) 模式

模式也被称作逻辑模式，是所有用户的公共数据视图，主要定义了数据的结构（如数据库中有哪些表、每个表有哪些字段、字段的数据类型等内容）。修改模式意味着改变数据库中数据的逻辑结构，比如增加新表、修改表中字段定义等，但这与保证物理数据独立性并无直接关联，它更多的是从逻辑层面去组织和定义数据。

(B) 模式/内模式映射

物理数据独立性指的是用户的应用程序与数据库中数据的物理存储结构是相互独立的，当数据库的物理存储结构（内模式）发生改变时，只要调整模式与内模式之间的映射关系，就能使应用程序不用修改，仍然按照原来的模式（逻辑结构）去访问数据。例如，数据库原本的数据存储在磁盘的某个区域，由于存储设备升级或者存储策略调整等原因，数据被迁移到了新的磁盘区域、采用了新的存储格式等（内模式改变），这时通过修改模式 / 内模式映射，让逻辑层面的模式能正确对应到新的物理存储情况，就能保证应用程序不受影响，继续正常运行。

模式 / 外模式映射主要用于保证逻辑数据独立性。当对数据库的逻辑结构（模式）进行修改时，比如增加了新字段，但只要调整模式与外模式之间的映射关系，就可以保证各个用户基于自己的外模式访问数据不受影响，也就是保证了逻辑数据独立性。

(D) 内模式

直接修改内模式只是改变了数据的物理存储情况，但如果不相应地调整模式 / 内模式映射，应用程序就无法正确访问数据了，所以单纯修改内模式不能保证物理数据独立性。

例：在 E-R 图中，下面哪个符号用于表示实体( B )

(A) 菱形

(B) 矩形

(D) 三角形

例：SQL 语句中与 Having 子句同时使用的语句是( A )

(A) group by

(B) left join

(D) limit

例：关于 SQL，说法正确的是( A )

(A) 可对数据进行定义、查询、操纵和控制

数据定义：可以通过语句如 CREATE（用于创建数据库、表、视图等数据库对象）、ALTER（修改表结构等对象结构）、DROP（删除数据库对象）等来定义数据库的结构以及其中包含的各种对象

数据查询：SELECT 语句能从数据库的表或视图等中检索出符合特定条件的数据

数据操纵：像 INSERT（向表中插入数据行）、UPDATE（更新表中已有数据）、DELETE（从表中删除数据行）这些语句都是用于对数据进行具体的操纵，改变数据的内容或者数量等情况。

数据控制：通过 GRANT（授予用户权限）和 REVOKE（收回用户权限）等语句来控制不同用户对数据库中数据以及相关对象的访问权限等情况。

(B) 一种高度过程化编程语言

过程化编程语言通常有明确的顺序执行流程，像 C、Java 等语言，需要详细地编写代码来描述每一步的操作顺序以及数据处理逻辑，例如通过循环、条件判断等语句来精确控制程序的执行过程。而 SQL 更多的是一种声明式语言，用户只需声明想要获取什么样的数据或者执行什么样的数据库操作（如查询、修改等），数据库管理系统会自行去确定执行的具体步骤和方式来实现这些操作，不需要像过程化编程那样细致地编排执行顺序。

SQL 整体对大小写不敏感。例如，关键字（像 SELECT、FROM、WHERE 等）通常在大部分数据库中无论大写小写都能被正确识别和执行，表名、列名等在很多情况下也不区分大小写（但有些数据库在特定配置下可能区分）。

(D) 最常见的操作是对数据增、删、改

最核心的是查询

例：在创建完一张数据表后，发现少创建一列，需要修改表结构，应该用哪个语句进行操作( C )

(A) MODIFY TABLE

没有这种语句

(B) INSERT TABLE

插入一行，不是一列

用于修改表结构

(D) UPDATE TABLE

用于更新表中已有行里的数据值，通常会配合 “WHERE” 子句来指定要更新哪些行的数据

例：在数据库视图中，不能( A )

(A) 修改字段的类型

视图是基于表（或其他视图）的虚拟表，本身并不实际存储数据。视图的结构（包括字段类型等）是由定义它时的查询语句以及关联的表结构来决定的，通常不允许直接在视图层面去修改字段的类型，因为修改字段类型涉及到对底层数据存储结构的改变。

(B) 修改字段的名称

通过使用别名（例如在 SELECT 语句中利用 AS 关键字给字段取别名）等方式，可以改变视图呈现出来的字段名称。

在重新定义视图（例如使用CREATE OR REPLACE VIEW 语句等方式）时，可以调整视图所选取的字段，相当于可以删除某些原来包含的字段，只选取需要展示的部分。

(D) 删除一天记录

通过在视图上执行一些符合规则的删除操作语句（例如配合合适的 WHERE 子句来定位要删除的记录对应的条件等），是有可能间接删除底层表中对应的一条记录的。

例：要求删除商品表中价格大于 3000 的商品，下列 SQL 语句正确的是( A )

(A) DELETE FROM 商品 WHERE 价格>3000

DELETE 语句用于从表中删除数据行，其基本语法格式为 DELETE FROM 表名 WHERE 条件。

(B) DELETE * FROM 商品 WHERE 价格>3000

在 DELETE 关键字后面直接跟 * 是错误的，* 通常在 SELECT 语句中用于表示选择所有列，而在 DELETE 只要指明从哪个表中删除（FROM 表名）以及添加相应的删除条件（WHERE 条件）即可。

DELETE FROM 商品 只是指定了从 “商品” 表中删除数据，但没有添加任何 WHERE 条件限定，它会将 “商品” 表中的所有记录全部删除。

(D) UPDATE 商品 SET *=NULL WHERE 价格>3000

UPDATE 的作用是用于更新表中已有的数据记录，其基本语法格式一般是 UPDATE 表名 SET 列名=值 [, 列名=值,...] WHERE 条件，用于修改满足特定条件的记录中指定列的值。

该选项中 SET *=NULL 的写法是完全错误的。

例：已知数据表 STU，现需创建视图 view_s，显示所有男同学的信息。下列 SQL 语句正确的是( )

(A) CREATE VIEW AS SELECT * FROM STU

(B) CREATE VIEW view_s AS SELECT * FROM STU WHERE 性别=‘男’

(D) CREATE view_s AS SELECT * FROM STU

例：检索销量表中销量最好的商品 id 和销量，下列 SQL 语句正确的是( D )

(A) SELECT 商品 id, 销量 FROM 销量表 WHERE 销量=MAX(销量)

WHERE 子句中不能直接使用聚合函数（如 MAX()、MIN()、SUM() 等）作为条件来筛选数据，因为在执行 WHERE 筛选的时候，数据库系统还没有开始进行聚合计算呢。

(B) SELECT 商品 id, MAX(销量) FROM 销量表 GROUP BY 销量

GROUP BY 子句通常用于按照指定的列对数据进行分组操作，目的是将数据按照某些相同特征（比如这里按照 销量 分组，但这样分组通常不符合实际需求，一般是按照商品相关的维度如 商品 id 分组更合理）归为一组，然后配合聚合函数（像 MAX()）来对每组数据进行统计分析。但此选项想要检索的是销量最好的商品 id 和销量，按照 销量 分组并不能准确达到这个目的，而且还缺少合适的筛选条件进一步提取出最好销量对应的那一组数据，所以该选项错误。

这样的查询结果会返回所有商品各自的最大销量以及对应的 商品 id（每个商品一组数据），而不是题目要求的整体销量表中销量最好的那一个商品的 id 和销量。

(D) SELECT 商品 id, 销量 FROM 销量表 WHERE 销量=(SELECT MAX(销量) FROM 销量表)

内层的 (SELECT MAX(销量) FROM 销量表) 子查询先独立地计算出整个销量表中的最大销量值，然后外层查询 SELECT 商品 id, 销量 FROM 销量表 WHERE 销量=(内层子查询得到的最大销量值) 筛选出销量等于这个最大销量值的记录。

SELECT MAX(销量) 是可行的，MAX() 只是不能在 WHERE 后面用

例：假设有一个名为“customers”的表，其中包含以下列：

|customer_id | customer_name | city | state

| 1 | Alice | New York | NY

| 2 | Bob | Los Angeles | CA

| 3 | Charlie | Chicago | IL

| 4 | Dave | Houston | TX

请回答以下问题：下面哪个SQL语句可以查询所有来自纽约的客户？( )

(A) SELECT * FROM customers WHERE city = 'New York'

(B) SELECT * FROM customers WHERE state = 'NY'

(D) SELECT * FROM customers WHERE state = 'New York'

例：将学生信息表的班级列默认值设为“暂未输入”，下列语句正确的是( C )

(A) ALTER TABLE student MODIFY class CHAR(10) VALUES ('暂未输入');

ALTER TABLE 语句用于修改表结构，MODIFY 关键字用于修改表中列的定义（比如修改数据类型、长度等属性）。其基本语法格式为：ALTER TABLE 表名 MODIFY 列名数据类型 [其他属性]。

在 MODIFY 子句后面直接跟 VALUES ('暂未输入') 是不符合语法规则的，VALUES 通常是用在 INSERT INTO 语句中用于指定要插入的数据值。

(B) INSERT INTO student (class) VALUES ('暂未输入');

INSERT INTO 的语法格式一般是：INSERT INTO 表名 (列名1, 列名2, …) VALUES (值1, 值2, …)，表示向指定表的对应列中插入给定的值。

该选项只是向 student 表的 class 列插入了一个 '暂未输入' 的值，但并没有设置该列的默认值。

ALTER TABLE 语句结合 MODIFY 关键字来修改列的定义，这里指定了要修改的表为 student，列是 class，数据类型为 CHAR(10)，然后通过 DEFAULT '暂未输入' 为 class 列设置了默认值为 '暂未输入'。

(D) INSERT INTO student class CHAR(10) DEFAULT '暂未输入';

这个语句从语法上来说就是错误的，INSERT INTO 后面接表名后应该紧跟着用括号列出要插入数据的列名（可选，如果插入所有列的数据则可省略列名部分），然后再用 VALUES 关键字跟上要插入的具体值。

例：若要“查询选修了3门以上课程的学生的学号”，则正确的 SQL 语句是( B )

(A) SELECT Sno FROM SC GROUP BY Sno WHERE COUNT(*)>3

WHERE 子句是用于在对数据进行分组（GROUP BY）之前筛选满足特定条件的原始数据行。 COUNT(*) 这类聚合函数是在分组之后才能计算得到相应结果的，不能在 WHERE 子句中基于分组聚合后的结果来设置条件。

(B) SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3

GROUP BY Sno 表示按照 Sno 列对 SC 表中的数据进行分组操作，把 Sno 相同的记录归为一组。然后 HAVING 用于对分组后的结果进行筛选，它可以基于分组聚合函数（像 COUNT(*) 用于统计每组的记录数量）的计算结果来设置条件。

ORDER BY Sno 的作用是对查询结果按照 Sno 列进行排序操作，使结果集中的数据按照 Sno 的升序或者降序排列。而 WHERE 子句如前面所说，是用于在分组、排序等操作之前筛选原始数据行的。

(D) SELECT Sno FROM SC ORDER BY Sno HAVING COUNT(*)>3

ORDER BY Sno 只是负责对查询结果进行排序，正常情况下是先进行分组（GROUP BY）操作，再用 HAVING 子句对分组结果筛选，最后可以用 ORDER BY 对最终筛选出来的结果进行排序。

例：Mysql 中表 student_table(id, name, birth, sex)，插入如下记录：

(‘1001’ , ‘’ , ‘2000-01-01’ , ‘男’);

(‘1002’ , null , ‘2000-12-21’ , ‘男’);

(‘1003’ , NULL , ‘2000-05-20’ , ‘男’);

(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);

(‘1005’ , ‘李四’ , ‘2001-12-01’ , ‘女’);

执行 select * from student_table where length(name) >= 0 的结果行数是( C )

(A)1 (B)2 (C)3 (D)4

NULL 的长度也是 NULL，不能和 0 比较

例：请取出 BORROW 表中日期(RDATE 字段)为当天的所有记录？(RDATE 字段为 datetime 型，包含日期与时间)。SQL 语句实现正确的是：( A )

(A) select * from BORROW where datediff(dd, RDATE, getdate())=0

datediff() 函数是用于计算两个日期差值的函数。datediff(dd, RDATE, getdate()) 表示计算 RDATE（代表表中的日期时间值）和 getdate()（获取当前日期时间的函数）之间相差的天数（dd 表示按天计算差值）。

差值等于 0 意味着 RDATE 字段所代表的日期与当前的日期是同一天，也就是取出了 BORROW 表中日期（RDATE 字段）为当天的所有记录。

(B) select * from BORROW where RDATE=getdate()

RDATE 字段是 datetime 型，它不仅包含日期还包含具体的时间信息，而 getdate()获取到的也是包含日期和时间的一个具体时间戳。即使日期部分是同一天，由于时间部分的存在，直接用 RDATE=getdate() 进行比较往往很难匹配成功。

RDATE-getdate() 这样直接对两个日期时间类型的值做减法操作是不合法的。

(D) select * from BORROW where RDATE > getdate()

RDATE > getdate() 是筛选出 RDATE 字段代表的日期时间大于当前日期时间的记录，并不是获取日期为当天的记录。

例：假定学生关系是 S(SNo, SName, Sex, Age)，课程关系是 C(CNo, CName, Teacher), 学生选课关系是 SC(SNo,CNo,Grade)。要查找选修4门以上(含4门)课程的学生总成绩(不统计不及格的课程)，正确的SQL语句是( )

(A) SELECT Sno, SUM(Grade) FROM SC WHERE Grade>=60 GROUP BY Sno HAVING COUNT(*)>=4

(B) SELECT Sno, COUNT(*), SUM(Grade) FROM SC GROUP BY Sno HAVING Grade>=60 and COUNT(*)>=4

(D) SELECT Sno, SUM(Grade) FROM SC WHERE Grade>=60 and COUNT(*)>=4 GROUP BY Sno

例：SQL 中，下列涉及空值的操作不正确的是( C )

(A) AGE IS NULL

判断用 IS

(B) AGE IS NOT NULL

因为 NULL 代表的是未知、不确定的值，它不能与任何值（包括另一个 NULL 值）进行比较操作（如等于、不等于等）。

(D) NOT(AGE IS NULL)

例：已知存在以下表：

S 表保存着学生关系，有 2 列，其中 SNO 为学号，SNAME 为姓名

C 表保存着课程关系，有 3 列，其中 CNO 为课程号

CNAME 为课程名，CTEACHER 为老师

SC 表保存着选课关系，有 3 列，其中 SNO 为学号，CNO 为课程号，SCORE 为成绩

1. 找出没有选“小易”老师课程的所有学生姓名

2. 列出有 3 门(包括 3 门)以上课程分数 >90 的学生姓名及其平均成绩

例：现有一个Products表，推荐出每个user_id，和他相似的用户所购买过的product。

要求：该用户已经购买过的不再推荐。

相似用户的定义：曾经购买过2种或2种以上的相同商品。

SELECT su.User1 AS User_id, p.Product_id
-- 将 su.User1 重命名为 User_id，选择 p.Product_id，即最终要返回的用户 ID 和产品 ID。
-- 先执行 FROM，不要先看 SELECT
FROM (
    SELECT p1.User_id AS User1, p2.User_id AS User2
    -- 从 Products 表两次引用分别命名为 p1 和 p2，选择 p1 的 User_id 命名为 User1，p2 的 User_id 命名为 User2。
    FROM Products p1
    -- 分别重命名 Products 为 p1 和 p2
    JOIN Products p2 ON p1.Product_id = p2.Product_id AND p1.User_id < p2.User_id
    -- 连接 p1 和 p2 ，连接条件是 Product_id 相同
    -- 且 p1 的 User_id 小于 p2 的 User_id，避免重复计算
    GROUP BY p1.User_id, p2.User_id
    -- 按照 User1 和 User2 进行分组，将 2 列 id 相同的归为一组，其他为另一组
    HAVING COUNT(DISTINCT p1.Product_id) >= 2
    -- 只有当两个用户共同购买的不同产品数量大于等于 2 时，才保留这一对用户。
) su
JOIN Products p ON su.User2 = p.User_id
-- 将子查询的结果（命名为 su）与 Products 表再次连接，连接条件是 su 中的 User2 和 p 表中的 User_id 相同。
WHERE NOT EXISTS (
    SELECT 1
    -- 这里使用子查询进行条件判断，判断是否不存在这样的记录。
    FROM Products p2
    WHERE p2.User_id = su.User1 AND p2.Product_id = p.Product_id
    -- 条件是在 Products 表中，对于当前的 su.User1 和 p.Product_id 的组合不存在记录，即确保推荐的产品是用户未购买过的。
);

找到相似用户

找到未买的但相似用户买过的商品