关系数据库标准语言

一、数据定义

1、模式

• 一个关系数据库管理系统的实例中可以建立多个数据库；
• 一个数据库中可以建立多个模式；
• 一个模式下通常包括多个表、视图和索引等数据库对象。

模式的定义与删除：

CREATE SCHEMA [<模式名>] AUTHORIZATION <用户名>;

如果没有指定<模式名>，那么<模式名>隐含为<用户名>。要创建模式，调用该命令的用户必须拥有DBA权限，或者获得了DBA授予的CREATE SCHEMA的权限。定义模式实际上定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。

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

其中 CASCADE 和 RESTRICT 两者必选其一。选择了 CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部删除。选择了 RESTRICT（限制），表示如果该模式中已经定义了数据库对象（如表、视图等），则拒绝该删除语句的执行；只有当该模式中没有任何数据库对象时才能执行 DROP SCHEMA 语句。

2、基本表

基本表的定义与删除

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

常用列级完整性约束

• 主码约束： PRIMARY  KEY
• 唯一性约束：UNIQUE
• 非空值约束：NOT NULL

常用表级完整性约束

• 多主码约束：PRIMARY  KEY (主码1,主码2…)
• 参照完整性约束：FOREIGN KEY (外码) REFERENCES 被参照表(被参照列)

当用户创建基本表（其他数据库对象也一样）时若没有指定模式，系统根据搜索路径来确定该对象所属的模式。搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出错误提示。

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

该语句的默认选项是 RESTRICT。若选择 RESTRICT，则该表的删除有限制条件，即该表不能被其他表的约束所引用（如 CHECK、FOREIGN KEY 等约束），不能有视图，不能有触发器（trigger），不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。若选择 CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，都可能被一起删除。

基本表的修改

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

添加列级完整性约束时将约束转换为表级完整性约束。

3、索引

索引的建立与删除

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

其中，<表名>是要建索引的基本表的名称。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选 ASC（升序）或 DESC（降序），默认值为 ASC。
UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER 表示要建立的索引是聚簇索引。一张表上只能建立一个聚簇索引，多个有联系的表可以建立聚簇索引。

DROP INDEX <索引名>;

索引的修改

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

二、查询

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

• SELECT 语句的含义是，根据 WHERE 子句的条件表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。
• 如果有 GROUP BY 子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，通常会在每组中作用聚集函数。如果 GROUP BY 子句带 HAVING 短语，则只有满足指定条件的组才予以输出。
• 如果有 ORDER BY 子句，则结果表还要按<列名2>的值的升序或降序排序。
• 如果有 LIMIT 子句，则限制 SELECT 语句查询结果的数量为<行数1>行，OFFSET <行数2>表示在计算<行数1>行前忽略<行数2>行。OFFSET 子句可省略，代表不忽略任何行。LIMIT是和 GROUP BY、ORDER BY 并列的子句。

1、单表查询

查询仅涉及一个表，是一种最简单的查询操作

选择列

• 查询指定列 SELECT Sno，Sname FROM Student;
• 查询全部列 SELECT * FROM Student;
• 查询经过计算的值，SELECT 语句的＜目标列表达式＞可以是算术表达式、字符串常量、函数等。

选择行

• 消除取值重复的行：在 SELECT 子句中使用 DISTINCT 短语，作用范围是所有目标列。
• 查询满足条件的元组：使用 WHERE 子句。

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<； NOT＋上述比较运算符
确定范围	BETWEEN AND，NOT BETWEEN AND
确定集合	IN，NOT IN
字符匹配	LIKE，NOT LIKE
空值	IS NULL，IS NOT NULL
多重条件（逻辑运算）	AND，OR，NOT
谓词 LIKE 可以用来进行字符串的匹配。其一般语法格式如下：

[NOT] LIKE '＜匹配串＞' [ESCAPE'＜换码字符＞']

即查找指定的属性列值与＜匹配串＞相匹配的元组。＜匹配串＞可以是一个完整的字符串，也可以含有通配符 % 和 _ 。其中： ％（百分号）代表任意长度（长度可以为 0 ）的字符串。 _ （下划线）代表任意单个字符。＂ESCAPE＇\＇＂表示＂\＂为换码字符。这样匹配串中紧跟在＂\＂后面的字符＂＿＂不再具有通配符的含义，转义为普通的＂＿＂字符。

查询结果排序

• ORDER BY子句可以按一个或多个属性列排序
• 升序：ASC；降序：DESC；缺省值为升序
• 对于空值，排序时显示的次序由具体系统实现来决定

使用聚集函数

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

如果指定 DISTINCT 短语，则表示在计算时要取消指定列中的重复值。如果不指定 DISTINCT 短语或指定 ALL 短语（ALL 为默认值），则表示不取消重复值。
当遇到空值时，聚集函数中除 COUNT(＊)函数外，其他函数都跳过空值而只处理非空值。 COUNT( * ) 是对元组进行计数，某个元组的一个或部分列取空值不影响其统计结果。
注意：WHERE 子句不能直接用聚集函数作为条件表达式。聚集函数只能用于 SELECT 子句和 GROUP BY 子句中的 HAVING 短语。

查询结果分组

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

• 未对查询结果分组，集函数将作用于整个查询结果
• 对查询结果分组后，集函数将分别作用于每个组
• 分组方法：按指定的一列或多列值分组，值相等的为一组
• 若使用GROUP BY子句，SELECT子句的列名列表中只能出现分组属性和集函数

对分组结果筛选

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用 HAVING 短语指定筛选条件。
HAVING短语与WHERE子句的区别在于作用对象不同，WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

查询平均成绩大于或等于 90 分的学生学号和平均成绩。

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

上面的语句是错误的，因为 WHERE 子句不能用聚集函数作为条件表达式。应该使用HAVING短语：

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

LIMIT子句限制查询结果的（元组）数量

LIMIT <行数1>[OFFSET <行数2>]

语义是取<行数1>，忽略前<行数2>行，作为查询结果数据。OFFSET 可以省略，代表不忽略任何行。

2、连接查询

同时涉及多个表的查询为连接查询，用来连接两个表的条件为连接条件或连接谓词

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

比较运算符：=、>、<、>=、<=、!=

等值连接

连接运算符为 = 的连接操作为等值连接。

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;

非等值连接

连接运算符不是 = 的连接操作为非等值连接。

自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接，此时的连接查询称为表的自身连接查询。

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST，Course SECOND
WHERE FIRST.Cpno = SECOND.Cno and SECOND.Cpno IS NOT NULL;

外连接

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。一些元组只在进行连接操作的其中一个表中出现，这些元组在连接时被舍弃了（这些被舍弃的元组称为悬浮元组）。
但是，有时我们想以某个表为主体列出所有情况，若某个元组没有在其他表中出现，则只输出其基本情况的数据，而把缺失的数据填为空值 NULL，这时就需要使用外连接查询。

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

复合条件连接

WHERE子句同时含连接谓词和选择谓词，称为复合条件连接

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

多表连接

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

3、嵌套查询

在 SQL 中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询（nested query）。
SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别指出的是，子查询的 SELECT 语句中不能使用 ORDER BY 子句，该子句只能对最终查询结果排序。

带有IN谓词的子查询

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

带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符，例如，假设一个学生只可能在一个系学习，并且必须属于一个系，则可以用 = 代替IN。

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

带有ANY或ALL谓词的子查询

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

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。由EXISTS引出的子查询，其目标列表达式通常都用" * " ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

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

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

4、集合查询

集合操作的种类有：并操作(UNION)，交操作(INTERSECT)，差操作(EXCEPT)。参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

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

5、基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象。
如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。

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;

三、数据更新

1、插入数据

INSERT语句有两种形式，插入单个元组或者插入子查询结果，后者可以一次插入多个元组。

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

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

INSERT INTO Student
VALUES ('95020','陈冬','男','IS',18);

INSERT INTO Deptage(Sdept，Avgage)
SELECT Sdept，AVG(Sage)
FROM Student
GROUP BY Sdept;

2、修改数据

UPDATE语句的功能是修改指定表中满足WHERE子句条件的元组。

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

3、删除数据

WHERE子句指定要删除的元组，缺省表示要删除表中的所有元组，表的定义仍在字典中。

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

四、视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

1、定义视图

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

• 其中，子查询可以是任意的 SELECT 语句，是否包含 ORDER BY 子句和 DISTINCT 短语取决于具体系统的实现。
• WITH CHECK OPTION 表示对视图进行 UPDATE、INSERT 和 DELETE 操作时，要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
• 组成视图的属性列名或者全部省略，或者全部指定，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中 SELECT 子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名：
  1. 某个目标列不是单纯的属性名，而是聚集函数或列表达式。
  2. 多表连接时选出了几个同名列作为视图的字段。
  3. 需要在视图中为某个列启用新的更合适的名字。

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

2、删除视图

DROP VIEW <视图名> [CASCADE];

该语句从数据字典中删除指定的视图定义，如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除，删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除。

3、查询视图

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

4、更新视图

更新视图是指通过视图来插入、删除和修改数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

5、视图的作用

1. 视图能够对机密数据提供安全保护
2. 视图对重构数据库提供了一定程度的逻辑独立性
3. 视图能够简化用户的操作