SQL基础

structured query language

分类	全称	说明
DDL	data definition language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	data manipulation language	数据操作语言，用来对数据库表的数据进行增删改
DQL	data query language	数据查询语言，用来查询数据库表的记录
DCL	data control language	数据控制语言，用来创建数据库用户，控制数据库的访问权限

其中：DQL和DCL可以划分到DML中去。

关系模型介绍

在关系模型中，术语关系（relation）被用来指代表，术语元组（tuple）被用来指代行，术语属性（attribute）则指代表中的列。关系是元组的集合。对于关系的每个属性都存在一个允许取值的集合，称为该属性的域（domain）。

如果一个域中的元素被认为是不可再分的单元，则该域是原子的。

数据库模式和数据库实例

数据库模式：数据库的逻辑设计

数据库实例：在某时刻数据库中数据的一个快照

关系模式和关系

关系模式的概念对应于程序设计语言中类型定义的概念，例如C++ struct定义数据类型

关系的概念对应于程序设计语言中变量的概念，例如C++ struct的一个变量

码

我们必需有一种方式来区分一个给定关系中的不同元组。一个元组的所有属性必需能够唯一标识一个元组。

超码

super key

形式：一个或多个属性的集合

作用：在一个关系中唯一地标识一个元组

特点：

• 可能包含无关属性，超码的任意超集也是超码。（通俗讲，就是同一个关系r上，如果某个属性集合S包含超码K，即K是S的子集，那么S也是 r 的超码）
  一张表（一个关系）一定有超码，因为至少所有属性的组合一定是超码，能唯一确定一个元组
• 不唯一

候选码

candidate key

• 是超码的一个子集
• 任意真子集都不可能是超码，候选码就是最小的超码
• 没有无关属性
• 不唯一

主码

primary key

• 是候选码之一
• 由数据库设计者指定，不指定的话表就没有主码

外码

foreign key

关系 r1 的属性中包含关系 r2的主码 ，设这个属性为 k，k 就是 r1 的外码

r1 是 k 依赖的引用关系（referencing relation)

r2 是 k 的被引用关系（referenced relation)

引用完整性约束

referential integrity constraint

要求引用关系中的任意元组在指定属性上出现的取值也必然出现在被引用关系中的至少一个元组的指定属性上。

外码约束是引用完整性约束的一种特例，其中被引用的属性构成被引用关系的主码。

关系代数

中文	符号
选择	$\sigma$
投影	$\Pi$
重命名	$\rho$
笛卡尔乘积	$\times$
聚集函数	$\gamma$
交	$\cap$
并	$\cup$
集差	$-$
自然连接	⋈
左外连接	⟕
右外连接	⟖
全外连接	⟗
赋值	$\leftarrow$
除法	$\div$

除法：(96条消息) 数据库-——关系代数的除法运算最白话解析Imo星星呐的博客-CSDN博客数据库除运算举例详解

基本语法

注释

• #号
• --双横线 + 一个空格
• /* */ C++风格注释

CREATE

CREATE TABLE tableName(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ...
    字段n 字段n类型 [COMMENT 字段n注释],
    [< 完整性约束 >]
)[ COMMENT 表注释];

完整性约束

这里简单提及，完整的请看之后的约束一节

关键字	描述
NOT NULL	非空约束，指定的列不允许为空值
UNIQUE	唯一约束，指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的
PRIMARY KEY(字段1，字段2)	主键约束，唯一的标识出表的每一行,且不允许空值,一个表只能有一个主键约束
FOREIGN KEY(字段3，字段4) references ref_table	外键约束，一个表中的列引用了其它表中的列，使得存在依赖关系，可以指向引用自身的列
CHECK(<谓词>)	条件约束，指定该列是否满足某个条件
DEFAULT()	默认约束，当不插入数据时，默认值生效

sql不允许破坏完整性约束的任何更新出现。

SELECT

单关系查询

SELECT [DISTINCT ALL] * FROM table1;
SELECT 字段1, 字段2, 字段3 FROM table2;

DISTINCT和ALL

DISTINCT对选择项去重，ALL显示所有查询结果,ALL是SELECT查询的默认参数，可省略不写

• DISTINCT需要放到所有列名的前面，否则会报错
• DISTINCT其实是对后面所有列名的组合进行去重

多关系查询

SELECT * FROM table1, table2;

对table1和table2做笛卡儿积后，在进行查询。

扩展：内连接inner join，在简单的多关系查询基础上添加限制条件，即可得到和内连接等价的结果

SELECT * FROM table1, table2 WHERE table1.id = table2.id;

WHERE

使用 SQL 从单个表或者多表联合查询数据时，可以使用 WHERE 子句指定查询条件。当给定查询条件时，只有满足条件的数据才会被返回。建议您使用 WHERE 子句来过滤记录，以获取必要的结果集。

WHERE 子句不仅可以用在 SELECT 语句中，还可以用在 UPDATE、DELETE 等语句中。

BETWEEN

为了简化WHERE子句，SQL提供了BETWEEN比较运算符来说明一个值小于等于某个值，同时大于等于另外一个值。例如如下两种查询等价

SELECT * FROM table1 WHERE salary >= 500 AND salary <= 1000;

SELECT * FROM table1 WHERE salary BETWEEN 500 AND 1000;

AS

更名运算

old—name AS new-name

as子句可出现在select中，也可出现在from中。

在sql的早期版本不包括as关键字，导致了在一些sql实现中，例如oracle中，不允许在from中使用关键字as，在oracle的from子句中，old-name AS new-name被写作old-name new-name。new-name也可被称作相关名称、表别名、相关变量、元组变量。

字符串运算

sql使用一对单引号来标示字符串，在sql标准中，字符串上的相等运算是大小写敏感的。

但是在一些数据库系统中，例如mysql和sql server，在匹配字符串时不区分大小写，不过这可以在数据库级或特定属性级修改。

函数名	描述
upper(s)	将字符串s转换为大写
lower(s)	将字符串s转换为小写
trim(s)	去除字符串前后的空格

模式匹配

LIKE运算符可以实现字符串的模式匹配，使用通配符来描述模式。

通配符	说明
百分号（%）	代表零个、一个或者多个任意的字符。
下划线（_）	代表单个字符或者数字。
[charlist]	字符列表中的任何单一字符。可以使用连字符（-）根据 ASCII 编码指定一个字符范围，例如： [0-9] 表示从 0 到 9 的任一数字； [a-z] 表示小写英文字母； [a-zA-Z] 表示英文字母，不区分大小写； [a-zA-Z0-9] 表示英文字母和阿拉伯数字。
[^charlist] 或 [!charlist]	不在字符列表中的任何单一字符。同上，也可以使用连字符（-）指定一个字符范围。

mysql和sqlite只支持百分号和下划线通配符，而SQL server支持上述四种通配符。

模式匹配是大小写敏感的。例外情况是在mysql中使用LIKE时不区分大小写的，不过在mysql中可以使用LIKE BINARY来区分大小写。

扩展：在PostgreSQL中，LIKE也是大小写敏感的，可以使用ILIKE来使得模式不对大小写敏感。

SELECT * FROM table1 WHERE name LIKE 'wu%'; # 匹配所有wu开头的姓名

SQL还允许使用NOT LIKE比较运算符来搜索不匹配项。

转义字符

在LIKE比较运算中使用ESCAPE关键字来自定义转义字符。

SELECT * FROM table1 WHERE name LIKE 'wu%%' ESCAPE '%'; # 匹配字符串 wu%

在某些数据库系统中也使用\来表示转义字符。例如sql。

ORDER BY

SQL ORDER BY子句用于根据一个或者多个字段对查询结果（结果集）进行排序，可以是降序，也可以是升序。默认情况下，大部分数据库将查询结果按照升序排序。

ASC:ascending，升序，默认参数

DESC:descending，降序

SELECT *
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

行构造器

行构造器的出现主要是用于WHERE表达式的优化。SQL允许使用符号(a1, a2, a3, ···, an)来表示一个包含a1, a2, a3, ···, an的n维元组，该符号被称为行构造器。在比较时按照字典序进行比较。

例如，当a1 <= b1 且 a2 <= b2时，(a1, a2) <= (b1, b2) 为真

SELECT * FROM table1
WHERE (a1, a2) <= (b1, b2);

集合运算

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，集合运算 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。字段名可以不同，但是列数量必需相同。

集合运算 和 join连接的区别在于集合运算的结果集是上下连接，join连接是左右连接。

运算名称	关键字
并集	UNION [ALL]
交集	INTERSECT [ALL]
集差	EXCEPT [ALL]

mysql中没有INTERSECT和EXCEPT运算符，但是可以使用IN和EXISTS来替代；而sql server均支持上述三种操作。

为了保持集合关系，集合运算的结果默认会去除重复项，这在集合的概念上是非常自然的。如果需要保留这些项，请使用ALL可选关键字以保留重复项。

SELECT * FROM table1 WHERE semester = 'Fall' AND year = '2017'
UNION
SELECT * FROM table1 WHERE semester = 'Spring' AND year = '2018';

三值逻辑

由于空值(null value)的存在，导致给算术运算和比较运算等运算带来了特殊的问题，因此引入三值逻辑，即True、False、unknown

• and：true and unknown结果是unknown，false and unknown结果是unknown，uknown and unknown结果是unknown
• or：true or unknown结果是true，false or unknown结果是unknown，uknown or unknown结果是unknown
• not：not unknown结果是unknown

sql允许使用IS UNKNOWN和IS NOT UNKNOWN子句来测试一个比较运算符的结果是否为UNKNOWN

SELECT * FROM instructor WHERE salay > 1000 IS UNKNOWN; # 得到比较结果为unknown的元组，也就是salay为空的元组
SELECT 1 = NULL IS UNKNOWN;     # true
SELECT NULL * NULL IS UNKNOWN;  # 特殊，结果为true

在谓词中，null = null的结果是unknown，而非true

聚集函数

基本聚集

聚集函数是以值是一个集合（集或者多重集）为输入、返回单个值得函数。SQL提供了五个固有聚集函数。

• 平均值：avg
• 最小值：min
• 最大值：max
• 总和：sum
• 计数：count

sum和avg的运算对象必需是数字集。而其他聚集函数的作用对象可以是非数字集。

有的在计算聚集函数前必需去重，如果确实想去除重复项可以使用关键字DISTINCT,例如寻找2018年春季授课的教师总数。

SELECT count(distinct ID)
FROM teaches
WHERE semester = 'Spring' AND year = '2018';

• SQL不允许在用count(*)时使用DISTINCT。
• 在使用max() or min()使用DISTINCT时是合法的，因为可能有多个值相同的结果。
• 仍然可以使用ALL代替DISTINCT来显示表明要保留重复项，但是ALL默认是缺省的，这么做也就没有必要了。

分组聚集

GROUP BY

如果希望将聚集函数作用在单个元组集上，也希望作用到一组元组集上，此时可以利用group by子句来实现。

group by 子句作用： 对给出的一个或多个属性来构造分组，将属性上取值相同的元组分到同一组中。

# 找出每个系的教师平均工资
SELECT dept_name, avg(salary) AS avg_salary
FROM instructor
GROUP BY dept_name;

当SQL查询使用分组的时候，需要保证出现在select语句中但没有被聚集的属性只能是出现在group by 子句中的那些属性。换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数的内部，否则这样的查询就是错误的。

过滤分组

HAVING

有时候限定分组条件比对元组限定条件更有用。比如我们只对工资超过15000某一个系感兴趣。该条件并不针对某个元组，而是针对group by子句构成的分组。即是说，having子句是在分组之后才生效的，可以使用聚集函数。

SELECT dept_name,avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name
HAVING avg(salary)>15000;

注意： 与select子句的情况类似，任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中，否则查询就被当成是错误的。

当一个查询没有GROUP BY子句时，使用HAVING的效果就相当于在一个分组（SELECT * FROM table1）中进行过滤。

对空值和布尔值的聚集

空值的出现对聚集运算带来了麻烦，例如下列句子：

SELECT SUM(salary) FROM instructor;

当instructor关系有些元组在salary属性的值为空，则在查询待求和的值中就包含了空值。SQL标准并不认为总和本身为null，而是认为sum运算符应忽略输入中的null值（因为算术表达式如果有null，那么结果为null）。

所以，聚集函数根据以下原则处理空值：

除了count（*）外，所有的聚集函数都忽略输入集合中的空值。由于空值被忽略，可能会造成参加聚集函数的输入值集合为空集。规定空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。

IN

集合成员资格

sql允许测试元组在关系中的成员资格。连接词IN测试集合成员资格，这里的集合是由SELECT子句产生的一组值构成的，连接词NOT IN测试集合成员资格的缺失。

单成员测试

示例：查找在2017年秋季开课的所有课程，在看他们是否也是2018年春季开课的课程集合中的成员。

先写出子查询，查出2018年春季开课的所有课程id

SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = '2018'

然后再查询2017年秋季的所有课程，并将这些课程与上述子查询结果进行测试，检测其中存在上述子查询结果集的相同项目，并放入最终结果集

SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year = '2018' AND
    course_id IN (
        SELECT course_id
        FROM section
        WHERE semester = 'Spring' AND year = '2018'
    );

IN查询先查询内表，之后与外表笛卡尔积连接，然后对结果进行筛选，输出结果集，适合内表小外表大的情况。

枚举集合测试

IN和NOT IN运算符也能用于枚举集合，经常用于CREATE的完整性约束中CHECK来检查。

CREATE TABLE newtable(
    id int,
    sex char(5) CHECK(sex IN ('man', 'woman'))
);

多成员资格测试

在前面的例子中,我们是在单属性关系中测试成员资格。在SQL中测试任意关系的成员资格也是可以的。

例如,我们可以这样来表达查询”找出(不同的)学生总数,他们选修了ID为10101的教师所讲授的课程段”

SELECT COUNT(DISTINCT ID)
FROM takes
WHERE (course_id, sec_id, semester, year) IN (
    SELECT course_id, sec_id, semester, year
    FROM teaches
    WHERE teaches.ID = 10101
);

集合比较

SOME

> SOME:在A集合中寻找比B集合中某一个元组要大的元组，并放入结果集。

在早期的SQL中通常使用ANY，后来的版本为了避免和英语中的any一词在语言上产生混淆，因而添加了SOME，但实际上ANY和SOME等价。

SQL允许< SOME,<= SOME,= SOME,!= SOME or <> SOME,>= SOME, > SOME的比较。

= SOME等价于IN，但是!= SOME不等价与NOT IN

示例：找出工资至少比Biology系某位教师的工资要高的所有教师姓名

SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > SOME (
    SELECT salary
    FROM instructor
    WHERE dept_name = 'Biology'
);

ALL

> ALL:在A集合中寻找比B集合中所有元组要大的元组，并放入结果集。

允许的比较方式类似于SOME。不同之处在于!= ALL等价于NOT IN,但是= ALL不等价于IN

示例：找出平均工资最高的系。

SELECT dept_name
FROM instructor
GROUP BY dept_name
HAVING avg(salary) >= ALL (
    SELECT avg(salary)
    FROM instructor
    GROUP BY dept_name
);

首先先计算所有系的平均工资，然后在寻找一个系的平均工资大于所有系的系。

空关系测试

sql包含一个特性，可测试一个子查询的结果中是否存在元组。EXISTS结构在作为参数的子查询非空时返回true，也可以通过使用NOT EXISTS结构来测试子查询结果集中是否不存在元组。

底层实现，对外表做LOOP循环，之后到内表测试，测试为真就放入结果集。适合外表小，内表大的查询（内表不一定会全部遍历）。

使用该例题集合成员资格-单关系测试-示例

SELECT course_id
FROM section AS S
WHERE semester = 'Fall' AND year = '2017' AND EXISTS(
    SELECT *
    FROM section AS T
    WHERE semester = 'Spring' AND year = '2018' AND S.course_id = T.course_id
);

解析：先寻找2017年秋季的课程，然后检测该课程是否在2018年春季中存在，如果存在放入结果集，不存在继续下一个课程中去。

重复元组存在性测试

UNIQUE 和 NOT UNIQUE​该结构尚未被广泛实现

sql提供一个布尔函数，用于测试在一个子查询的结果集中是否存在重复元组。如果在作为参数的子查询结果中没有重复的元组，则UNIQUE返回True,反之返回False.

同时我们也可以使用NOT UNIQUE结构测试在一个子查询结果中是否存在重复元组。

示例：找出在2017年最多开设一次的课程

SELECT T.course_id
FROM course AS T
WHERE UNIQUE (
    SELECT R.course_id
    FROM section AS R
    WHERE T.course_id = R.course_id AND R.year = 2017
);

解释：对外层做loop，每个元组检查在2017年开设的场次，如果场次数不是唯一的，则不放入结果集。

from子句中的子查询

SQL允许在from子句中使用子查询表达式。因为任何select-from-where表达式返回的结果都是关系,所以子查询可以被插入到另一个select-from-where中任何关系可以出现的位置。

考虑査询”找出系平均工资超过42000美元的那些系中教师的平均工资“。在3.7节我们使用了having子句来书写此查询。现在我们可以不用having子句来重写这个查询,而是通过如下这种在from子句中使用子查询的方式:

SELECT dept_name, avg_salary
FROM (
    SELECT dept_name, avg(salary) AS avg_salary
    FROM instructor
    GROUP BY dept_name
    )
WHERE avg_salary > 42000;

解释：首先按系分组，并计算每个系的平均工资，然后筛选平均工资大于42000的系

重命名from子查询的结果关系

我们可以用as​子句重命名子查询的结果关系,也可以使用as子句重命名子查询的属性

SELECT dept_name, avg_salary
FROM (
    SELECT dept_name, avg(salary) AS avg_salary
    FROM instructor
    GROUP BY dept_name
    ) AS dept -- 或者是 AS dept(dept_name, avg_salary)，对属性更名
WHERE avg_salary > 42000;

很多(但并非全部)SQL实现都支持在from子句中嵌套子查询。

请注意,某些​SQL​ (特别是MYSQL和PostgreSQL)实现要求对每一个​from​子查询结果关系都给一个名字,即使该名字从不被引用;

Oracle允许（以省略关键字AS的形式）对子查询的结果命名，但是不支持对此关系的属性更名，对于此的应对措施是在子查询的select语句中对属性更名。

访问同级相关变量

作为另一个例子,假设我们想要找出在所有系中工资总额最大的系。在此having子句是无能为力的,但我们可以用from子句中的子查询轻易地写出如下查询

SELECT max(tot_salary)
FROM(
    SELECT dept_name, sum(salary) as tot_salary
        FROM instructor
        GROUP BY dept_name
)as dept_total;

我们注意到在from子句嵌套的子查询中不能使用来自from子句其他关系的相关变量。

lateral关键词

子查询访问外层查询的相关变量。从SQL2003允许from子句中的子查询用关键词lateral作为前缀,以便访问from子句中在它前面的表或子查询中的属性。

例如,如果我们想打印每位教师的姓名,以及他们的工资和所在系的平均工资,可书写查询如下:

SELECT name, salary, avg_salary
FROM instructor I1, LATERAL(
    SELECT avg(salary) as avg_salary
    FROM instructor I2
    WHERE I1.dept_name = I2.dept_name
);

没有lateral子句的话,子查询就不能访问来自外层查询的相关变量I1。

WITH子句

with子句提供定义临时关系(没有创建一个实体)的方法,这个定义只对包含with子句的查询有效。

考虑下面的查询,它找出具有最大预算值的系。

WITH max_budget(value) AS (
    SELECT max(budget)
    FROM department
)
SELECT budget
FROM department, max_budget
WHERE department.budget = max_budget.value;

该查询中，WITH定义了临时关系max_budget(value)，此关系包含定义了此关系的子查询的结果元组。此关系只能在同一查询的后面部分使用。

类似于AS子句的相反用法，同理，在不支持属性重命名的数据库实现中，也可以省略属性命名或者改用SELECT中的属性重命名，例如

WITH max_budget AS (
    SELECT max(budget) AS `value`
    FROM department
)

标量子查询

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方,只要该子查询只返回包含单个属性的单个元组;这样的子查询称为标量子查询。

例如,一个子查询可以用到下面例子的select子句中,这个例子列出所有的系以及它们拥有的教师数

SELECT dept_name, (
    SELECT COUNT(*)
    FROM instructor
    WHERE department.dept_name = instructor.dept_name
) AS num_instructors
FROM department;

+------------+-----------------+
| dept_name  | num_instructors |
+------------+-----------------+
| Biology    |               1 |
| Comp. Sci. |               3 |
| Elec. Eng. |               1 |
| Finance    |               2 |
| History    |               2 |
| Music      |               1 |
| Physics    |               2 |
+------------+-----------------+

解释：根据运算符优先级：先FROM后SELECT，在外层SELECT选择时输出第一个字段，然后输出第二个字段，由于第二个字段是子查询，于是先查询后输出。

上面例子中的子查询保证只返回单个值,因为它使用了不带group by的count(*)聚集函数。

从技术上讲标量子查询的结果类型仍然是关系,尽管其中只包含单个元组。

不带from的子查询

某些查询需要计算，但不需要引用任何关系。

例如：统计男女比例；检查三值运算符

SELECT (SELECT COUNT(*) FROM student WHERE sex = 'man') / (SELECT COUNT(*) FROM student WHERE sex = 'woman');
SELECT 1 = NULL IS UNKNOWN; # True

在一些系统中这种写法是合法的，但是在某些系统会由于缺少FROM子句而报错。因此规定了一个特殊的虚拟关系DUAL。

DUAL

它是一个包含单个元组，单个属性的关系。

上述查询可以改写为：

SELECT (SELECT COUNT(*) FROM student WHERE sex = 'man') / (SELECT COUNT(*) FROM student WHERE sex = 'woman') FROM DUAL;
SELECT 1 = NULL IS UNKNOWN FROM DUAL; # True

两种写法得到的结果完全一致。

DELETE

删除请求的表达与査询非常类似。我们只能删除整个元组,而不能只删除某些属性上的值。SQL用如下语句表示删除:

DELETE FROM R WHERE P;

其中P代表一个谓词,R代表一个关系。 delete语句首先从关系R中找出所有使P(t)为真的元组t,然后把它们从R中删除。

注意delete命令只能作用于一个关系。如果我们想从多个关系中删除元组,必须在每个关系上使用一条delete命令。

where​子句中的谓词可以和​select​命令的​where​子句中的谓词一样复杂（使用​IN​或者​SOME​等等）。 在另一种极端情况下, where子句可以为空,例如:

DELETE FROM instructor;

将删除instructor关系中的所有元组。 instructor关系本身仍然存在,只是它变成空的关系而已了.

例题

删除所有工资在13000美元到15000美元之间的教师。

DELETE FROM instructor
WHERE salary BETWEEN 13000 AND 15000;

从instructor关系中删除所有在位于 Watson大楼的系工作的教师元组。

DELETE FROM instructor
WHERE dept_name IN(
    SELECT dept_name
    FROM department
    WHERE building ='Watson'
);

特殊：​删除工资低于大学平均工资的教师记录

DELETE FROM instructor
WHERE salary < (
    SELECT avg(salary) FROM instructor
);

该​delete​语句首先测试​instructor​关系中的每一个元组,检查其工资是否小于大学教师的平均工资。然后删除所有符合条件的元组,即所有低于平均工资的教师。 在执行任何删除之前先进行所有元组的测试是至关重要的,因为若有些元组在其余元组未被测试前先被删除,则平均工资将会改变,这样delete的最后结果将依赖于元组被处理的顺序

上面的代码在MySQL里测试会报错:You can't specify target table 'instructor' for update in FROM clause

因为在MYSQL里，不能先SELECT一个表的记录，在按此条件进行更新和删除同一个表的记录，解决办法是，将​select​得到的结果，再通过中间表​select​一遍，这样就规避了错误。

DELETE FROM instructor
WHERE salary < (
    SELECT avg_salary
    FROM (
        SELECT avg(salary) AS avg_salary
        FROM instructor
    ) AS temp
);

原理简介：mysql的SELECT操作会给表添加读锁（共享锁），进而导致DELETE和UPDATE无法操作表，因此我们需要创建一个中间表存储结果在进行其他操作。

INSERT

要往关系中插入数据,我们可以指定待插入的元组,或者写一条查询语句来生成待插入的元组集合。显然,待插入元组的属性值必须在相应属性的域中。同样,待插入元组的分量数也必须是正确的。

单元组插入

完整插入

INSERT INTO course VALUES('CS-437', 'Database Systems', 'Comp. Sci. ',4);

在此例中,元组属性值的排列顺序和关系模式中属性排列的顺序一致。

部分插入

考虑到用户可能不记得关系属性的排列顺序,SQL允许在insert语句中指定属性，未书写的属性在未破坏完整性约束的前提下将设置为NULL

INSERT INTO course(course_id, title, dept_name, credits) VALUES('CS-437', 'Database Systems','Comp. Sci. ', 4);

在查询的基础上插入

更通常的情况是,我们可能想在查询结果的基础上插入元组。假设我们想让Music系每个修满144学分的学生成为Music系的教师,其工资为18000美元。我们可写作:

INSERT INTO instructor
    SELECT ID, name, dept_name, 18000
    FROM student
    WHERE dept_name ='Music' AND tot_cred>144;

和本节前面的例子不同的是,我们没有指定一个元组,而是用select选出一个元组集合。SQL​先执行这条​select​语句,求出将要插入到​instructor​关系中的元组集合。每个元组都有ID、dept_name(Music)和工资(18000美元)。

扩展：在这条语句中也存在共享锁机制，不过由于对student表加锁对instructor表无影响，因此不用额外考虑锁的影响。

在执行插入之前先执行完select语句是非常重要的。

如果在执行select语句的同时执行插入动作如果在student上没有主码约束的话,像:

INSERT INTO student
    SELECT * FROM student;

如果不是在查询的基础上在进行操作，将导致无限循环。

UPDATE

在某些情况下,我们可能希望在不改变整个元组的情况下改变其部分属性的值。为达到这一目的,可以使用update语句。

假设要进行年度工资增长,所有教师的工资将增长5%。我们写出:

UPDATE instructor SET salary=salary * 1.05;

上面的更新语句将在instructor关系的每个元组上执行一次。

UPDATE instructor
    SET salary=salary*1.05
    WHERE salary<70000;

只给那些工资低于70000美元的教师涨工资。

where子句嵌套查询语句

总之, update语句的where子句可以包含select语句的where子句中的任何合法结构(包括嵌套的select)。

和insert、 delete类似, update语句中嵌套的set可以引用待更新的关系。

同样,SQL首先检查关系中的所有元组,看它们是否应该被更新,然后才执行更新。

例如,请求”对工资低于平均数的教师涨5%的工资“可以写为如下形式:

UPDATE instructor
    SET salary=salary*1.05
    WHERE salary < (
            SELECT avg(salary) AS avg_salary
            FROM instructor
    );

注意，这条语句在MYSQL中会报错，因为MYSQL会对子查询加锁，进而导致无法使用UPDATE，可以通过一个中间表来规避这种情况。

case结构

SQL提供case结构,我们可以利用它在一条update语句对不同的条件执行不同的更新。

case​语句可以用在任何应该出现​值​的地方。例如在select子句中。

case语句的一般格式如下:

CASE
    WHEN pred_1, THEN result_1
    WHEN pred_2, THEN result_2
    ...
    WHEN pred_n, THEN result_n
    ELSE result0
END

• 如果第一个满足的是谓词pred_i,则返回结果result_i.
• 如果没有一个谓词可以满足,则返回result0

示例：给工资超过100000美元的教师涨3%的工资,其余教师涨5%

UPDATE instructor
SET salary = 
CASE
    WHEN salary < 100000 THEN salary*1.05
    ELSE salary*1.03
END;

JOIN

思维导图

常见连接

NATURAL

自然连接（natural） 运算作用于两个关系，并产生一个关系作为结果，与两个关系的笛卡尔积不同，自然连接只考虑在两个关系的模式中都出现的那些属性上取值相同的元组对，而笛卡尔积是将第一个关系的每个元组与第二个关系的每个元组进行串接。

自然连接的列出顺序：首先是两个关系模式中的公共属性，其次是只出现在第一个关系中的那些属性，最后是只出现在第二个关系中的那些属性。

注意：只有自然连接遵循该顺序，其他连接方式遵循谁在前谁先出现的顺序。

例如：对于大学中已经选课的所有学生，找出他们的姓名以及他们选修的所有课程的id

SELECT name, course_id
FROM student,takes
WHERE student.ID = takes.ID;

等价于

SELECT name, course_id
FROM student NATURAL JOIN takes;

等价前提：两个关系的公共属性只有ID

from的多个自然连接

在一个SQL查询的from子句中,可以用自然连接将多个关系结合在一起

例如：列出教师的名字以及他们所讲授课程的名称

select name,title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;

instructor和teaches自然连接后和course做笛卡尔积

USING

sql提供了一种自然连接的构造形式，它允许你来指定究竟需要那些列相等。对于两个关系，存在多个相同字段，如果直接使用natural join，比较的是所有相同属性，而使用using，我们可以指定需要比较的公共属性。例如:

r1(a1, a2, a3, a4)
r2(a1, a2, a3, a5, a6)

r1 natural join r2  等价于  r1 join r2 using(a1, a2, a3)
r1 natural join r2  不等价于  r1 join r2 using(a1)

USING(a1,a2)等价于ON r1.a1 = r2.a1 AND r1.a2 = r2.a2，它是ON的特殊写法。

示例：列出学生的姓名和他们所选课程的名称

select name, title
from student natural join takes, course
where takes.course_id = course_id;

但下面的sql查询得不到正确答案

select name, title
from student natural join takes natural join course;

原因在于：student和takes的自然连接结果包含的属性是（ID, name，dept_name，tot_cred，course_id，sec_id），而course关系包含的属性是（course_id，title，dept_name，credits），使用自然连接，比较的是（dept_name，course_id）这两个属性，而我们实际上只想比较course_id，因此得到的结果是错误的。我们可以使用using来指定比较的列，如下

select name, title
from (student natural join takes) join course using(course_id);

ON

ON的引入是为外连接做铺垫。

SQL支持另外一种形式的连接,其中可以指定任意的连接条件。on条件允许在参与连接的关系上设置通用的谓词。该谓词的写法与where子句谓词类似,只不过使用的是关键词on而不是where。与using条件一样,on​条件出现在连接表达式的末尾。

ON的连接条件可以是不同关系的不同属性。

考虑下面的查询,它具有包含on条件的连接表达式:

select *
from student join takes on student.ID=takes.ID;

在上例中的连接表达式与连接表达式student natural join takes几乎是一样的,因为自然连接运算也需要studen元组和takes元组是匹配的。

这两者之间的一个区别在于:在上述连接查询结果中,ID属性出现两次,一次是student中的,另一次是takes中的,即便它们的ID属性值是相同的。即没有去掉重复属性。

实际上,上述查询与以下查询产生的结果是完全相同的。

select *
from student,takes
where student.ID=takes.ID;

on和自然连接

on条件可以表示任何SQL谓词,从而使用​on​条件的连接表达式就可以表示比自然连接更为丰富的连接条件。

on和where

误区：然而,正如上例所示,使用带on条件的连接表达式的查询可以用不带on条件的等价表达式来替换,只要把on子句中的谓词移到where子句中即可。这样看来,on条件似乎是一个冗余的SQL特征，但实际上对被称作外连接的这类连接来说,on条件的表现与where条件是不同的。其次,如果在on子句中指定连接条件,并在where子句中出现其余的条件,这样的SQL查询通常更容易让人读懂。

内连接

内连接（inner join）是应用程序中用的普遍的”连接”操作，它一般都是默认连接类型。内连接基于连接谓词将两张表（如 A 和 B）的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合（并排组合）成结果集中的一行。

连接产生的结果集，可以定义为首先对两张表做笛卡尔积（交叉连接）将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的。

• 等值连接
• 不等值连接
• 交叉连接（无条件的内连接）

外连接

保留未匹配元组的连接运算被称作外连接运算(outer join)。

实际上有三种形式的外连接:

• 左外连接(left outer join)只保留出现在左外连接运算之前(左边)的关系中的元组。
• 右外连接(right outer join)只保留出现在右外连接运算之后 (右边)的关系中的元组。
• 全外连接(full outer join)保留出现在两个关系中的元组。

假设我们要显示一个所有学生的列表,显示他们的ID、name、 dept_name和tot_cred,以及他们所选修的课程。下面的查询好像检索出了所需的信息:

select *
from student natural join takes;

遗憾的是,上述查询与想要的结果是不同的。假设有一些学生,他们没有选修任何课程。那么这些学生在student关系中所对应的元组与takes关系中的任何元组配对,都不会满足自然连接的条件,从而这些学生的数据就不会出现在结果中。这样我们就看不到没有选修任何课程的学生的任何信息。

由此引入外连接。外连接(outer join)运算与我们已经学过的连接运算类似,但通过在结果中创建包含空值元组的方式,保留了那些在连接中丢失的元组。

左外连接

我们现在详细解释每种形式的外连接是怎样操作的。我们可以按照如下方式计算左外连接运算

• 首先,像前面那样计算出内连接的结果
• 然后,对于在内连接的左侧关系中任意一个与右侧关系中任何元组都不匹配的元组​t,向连接结果中加入一个元组r,r的构造如下
  • 元组r从左侧关系得到的属性被赋为元组t中的值。
  • 元组r的其他属性被赋为空值。

右外连接

右外连接和左外连接是对称的。

来自右侧关系中不匹配左侧关系任何元组的元组被补上空值并加入到右外连接的结果中。

全外连接

全外连接是左外连接与右外连接类型的组合。在内连接结果计算出来之后:

• 左侧关系中不匹配右侧关系任何元组的元组被添上空值并加到结果中.
• 右侧关系中不匹配左侧关系任何元组的元组也被添上空值并加到结果中。

换言之：全外连接是左外连接和相应的右外连接的并运算

Mysql不支持全外连接，不过MySQL支持并运算(union),所以可以通过左外连接​和​右外连接​的并运算得到全外连接.

on子句和外连接

on子句可以和外连接一起使用。下述查询与使用select * from student natural left outer join takes的查询结果是相同的（假定student和takes的公共属性只有ID）,只不过属性ID在结果中出现两次。

select *
from student left outer join takes
on student.ID = takes.ID;

由此可见，ON和WHERE对于外连接而言是不同的，原因在于，外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果。也就是on子句为false时,外连接才会补上空值.on​条件是外连接声明的一部分,但​where​子句却不是。

简而言之：ON智能判断，如果出现student.id = null时，若元组来自于student，ON会将该元组加入结果集，并对其余属性补空值。

连接类型和条件

为了把常规连接和外连接区分开来,SQL中把常规连接称作内连接。连接子句就可以用inner join来说明使用的是常规连接。

连接类型	连接条件
inner join	natural
left outer join	on
right outer join	using (A1, A2, … An )
full outer join
cross join(特殊)

在上表中，除cross join外，任意的连接类型（内连接、左外连接、右外连接或全外连接）可以和任意的连接条件（自然连接、using条件连接或on条件连接）进行组合。

cross join是一种特殊的连接形式，是为了符号对称而出现的，等价于FROM table_a, table_b逗号连接，它是inner join连接的无条件形式。

默认连接方式

然而关键词inner是可选的，当join子句中没有使用outer/left/right/full前缀,默认的连接类型是inner join

内连接：不保留未匹配元组​的连接运算被称作内连接运算(inner join)

例如：

select *
from student join takes using(ID);
# 等价于
select *
from student inner join takes using(ID);

类似地,natural join等价于natural inner join。

视图

SQL允许通过查询来定义一种“虚拟关系”，它在概念上包含查询的结果。

该虚拟关系并不预先计算和存储，而是在使用虚拟关系的时候从通过执行查询计算出来。

数据库系统存储的是与视图相关联的查询表达式。

任何像这种不是逻辑模型的一部分,但作为虚拟关系对用户可见的关系称为视图(​view​ ) 。在任何给定的实际关系集合上能够支持大量视图。

视图定义

CREATE VIEW view_name AS <查询表达式>; # 隐式属性名
# 或
CREATE VIEW view_name(attr1,attr2) AS <查询表达式>; # 显式属性名

其中<查询表达式>可以是任何合法的查询表达式，例如SELECT-FROM-WHERE等等。

视图和WITH

视图和WITH的不同之处在于，视图一旦创建，在被显式删除之前一直都是可用的。由WITH定义的命名子查询对于定义它的查询来说，只是本地的或者只是本次查询可用的。

视图关系的结果通过计算产生

视图通常这样来实现:当我们定义一个视图时,数据库系统存储视图的定义本身,而不存储定义该视图的查询表达式的执行结果。一旦视图关系出现在查询中,它就被已存储的查询表达式代替。因此,无论我们何时执行这个查询,视图关系都被重新计算。

物化视图

物化视图(materialized view)是视图的特殊情况，某些数据库允许存储视图关系实体，但是他们保证：如果用于定义视图的关系发生改变，则视图也相应的发生改变，则视图也跟着修改以保持最新，这样的视图称为物化视图。

作用：如果某个视图是物化的，那么可以允许使用该视图的查询可以通过预计算的视图结果来更快的运行，而不是重新计算该视图的结果。

物化视图维护

保持物化视图一直在最新状态的过程称为物化视图维护(materialized view maintenance),或者通常简称视图维护(view maintenance)

视图维护的时机

SQL没有指定物化视图的标准方式，很多数据库系统提供了他们自己的SQL扩展来实现这项任务，大致分为如下三种。

• 当构成视图定义的任何关系被更新时,立即进行视图维护
• 只有在视图被访问时才执行视图维护
• 采用周期性的物化视图更新方式在这种情况下,当物化视图被使用时,其中的内容可能是陈旧的,或者说过时的。如果应用需要最新数据的话,这种方式是不适用的。

不过好在某些数据库系统允许数据库管理员来控制在每个物化视图上需要采取上述的哪种方式。

物化视图的优点

• 频繁使用视图的应用将会从视图的物化中获益。
• 那些需要快速响应基于大关系上聚集计算的特定查询也会从创建与查询相对应的物化视图中受益良多。在这种情况下,聚集结果很可能比定义视图的大关系要小得多,其结果是利用物化视图来回答査询就很快,它避免了读取大的底层关系。

当然,物化视图查询所带来的好处还需要与存储代价和增加的更新开销相权衡。

视图更新

尽管对查询而言,视图是一个有用的工具,但如果我们用它们来表达更新、插入或删除,它们可能带来严重的问题。困难在于:

用视图表达的数据库修改必须被翻译为对数据库逻辑模型中实际关系的修改

因此一般不允许对视图关系进行修改，不同的数据库系统指定了不同的条件，在满足这些条件的前提下，才允许更新视图关系，请参考数据库系统手册以获得详细信息。

可更新的视图要满足的条件

一般说来,如果定义视图的查询对下列条件都能满足，我们称SQL视图是可更新的(即视图上可以执行插入、更新或删除)

1. from子句中只有一个数据库关系。
2. select子句中只包含关系的属性名,不包含任何表达式、聚集或distinct声明。
3. 任何没有出现在​select​子句中的属性可以取空值;即这些属性上没有not null约束,也不构成主码的部分。
4. 查询中不含有group by或having子句。

在这些限制下,下面的视图上允许执行update、 insert和delete操作:

create view history_instructors
as
select *
from instructor
where dept_name = 'History';

可更新的视图存在的问题

假设一个用户尝试向视图history_instructors中插入元组('25566','Brown','Biology',100000):

insert into history_instructors values ('25566','Brown','Biology',100000);

这个元组可以被插入到instructor关系中,但是由于它不满足视图所要求的选择条件,它不会出现在视图history_instructors中。因此在视图表中看来这条记录并没有插入表，但实际上这条记录已经插入表中了。

扩展

在默认情况下,SQL允许执行上述更新。但是,可以通过在视图定义的末尾包含​with check option​子句的方式来定义视图。

这样,如果向视图中插入(insert)一条不满足视图的where子句条件的元组,数据库系统将拒绝该插入操作。

类似地,如果更新的新值不满足where子句的条件,更新(update)也会被拒绝。

SQL:1999对视图更新的规则

SQL:1999对于何时可以在视图上执行插入、更新和删除有更复杂的规则集,该规则集允许通过类更大视图进行更新,但是这些规则过于复杂,这里就不介绍了。

事务

事务(transaction)由查询和(或)更新语句的序列组成。SQL​标准规定当一条​SOL​语句被执行,就隐式地开始了一个事务。

结束事务

下列SQL语句之一会结束一个事务:

• Commit work:提交当前事务,也就是将该事务所做的更新在数据库中持久保存。在事务被提交后,一个新的事务自动开始。
• Rollback work:回滚当前事务,即撤销该事务中所有SQL语句对数据库的更新。这样,数据库就恢复到执行该事务第一条语句之前的状态。

关键词​work​在两条语句中都是可选的

在某种意义上,事务提交就像对编辑文档的变化存盘,而回滚就像不保存变化退出编辑。一旦某事务执行了​commit work​ ,它的影响就不能用​rollback work​来撤销了。
数据库系统保证在发生诸如某条SQL语句错误、断电、系统崩溃这些故障的情况下,如果一个事务还没有完成​commit work​ ,其影响将被回滚。在断电和系统崩溃的情况下,回滚会在系统重启后执行。

事务的作用

举例：考虑一个银行应用,我们需要从一个银行账户上把钱转到同一家银行的另一个账户。为了这样做,我们需要更新两个账户的余额,把需要转移的资金额从一个账户划走,并把它加到另一个账户上。如果在从第一个账户上划走资金以后,但在把这笔资金加入第二个账户之前发生了系统崩溃,那么银行账户就会不一致。如果在第一个账户划走资金之前先往第二个账户存款,并且在存款之后马上发生系统崩溃,那么也会出现类似的问题。

如果我们把这个例子看作一个事务，在没有完成这个转账操作前，如果事务中断，再次启动时，就撤销事务。而不会导致一个账户中已经出账，而另一种账户中没有入账的情况。

一个事务或者在完成所有步骤后提交其行为,或者在不能成功完成其所有动作的情况下回滚其所有动作,通过这种方式数据库提供了对事务具有原子性(atomic)的抽象,原子性也就是不可分割性,即

• 要么事务的所有影响被反映到数据库中
• 要么任何影响都没有(在回滚之后)

如果把事务的概念应用到上述应用中,那些更新语句就会作为单个事务执行。在事务执行其某条语句时出错会导致事务早先执行的语句的影响被撒销,从而不会让数据库处于部分更新状态。

如果程序没有执行Commit work或Rollback work两条命令中的任何一条而终止了,那么更新要么被提交要么被回滚。SQL标准并没有指出究竟执行哪一种,如何选择依赖于具体的SQL实现。

自动提交

在包括mysql和postgreSQL在内的很多SQL实现中，在默认方式下，每条语句自成一个事务，且语句一旦执行完就提交该事务。如果某次操作由多条SQL语句组成，就必需关闭单条SQL语句的这种自动提交，防止出现部分语句提交成功而其余语句提交不成功的状况出现。如果关闭自动提交依赖于特定的SQL实现，尽管很多数据库都支持关闭自动提交的命令。

另一个方法是,在SQL:1999标准中规定,允许多条SQL语句包含在关键字begin atomic … end之间。所有在关键字之间的语句构成了一个单一事务。但是只有例如SQL server的数据库支持上述语法，例如mysql和postgreSQL的其他几个数据库只支持begin，该语句包含所有后续SQL语句的事务，但并不支持end语句。事务必需通过commit work或rollback work命令来结束。

将多条语句包含在一个事务中的好处在于，如果某条语句违反了完整性约束或者语法不正确，这个事务就会终止并回滚，不会对数据库造成任何影响。反之，在默认情况下每条语句自成一个事务，如果某条语句失败，那么之前的语句就会提交成功，到中断点之后的就不会提交，那么就造成了本次操作的部分提交，给后续处理带来了麻烦。举例如下（id设定为主键）

# begin;
INSERT INTO text.abcd (id, sex, weight) VALUES (1, 'F', 728);
INSERT INTO text.abcd (id, sex, weight) VALUES (2, 'M', 183);
INSERT INTO text.abcd (id, sex, weight) VALUES (1, 'F', 853); # 中断点，之前的数据已经提交入数据库
INSERT INTO text.abcd (id, sex, weight) VALUES (2, 'F', 920);
INSERT INTO text.abcd (id, sex, weight) VALUES (3, 'M', 374);
# commit;

每条语句自成一个事务，导致部分提交。对于这种情况我们应该使用begin包裹这些语句，使之构成一个事务。但即使构成一个事务，仍然会执行每条语句的完整性约束检查。

事务和语句的约束检查是互相独立的，一次事务!=一次约束检查，如果事务由n条语句构成，那么约束检查的次数也会更多。

扩展：如果使用诸如 Oracle 那样的数据库，其中自动提交并不是DML语句的默认设置，请确保在添加或修改数据后发出commit work命令，否则当你断开连接时，将回滚你的所有数据库修改！你应该清楚，虽然在缺省情况下 Oracle 已关闭自动提交，但缺省设置可能会被本地设置所覆盖。

约束

完整性约束

完整性约束保证授权用户对数据库所做的修改不会破坏数据的​一致性。因此,完整性约束防止的是对数据的意外破坏。

一般说来,一个完整性约束可以是属于数据库的任意谓词。但检测任意谓词的代价可能太高。因此,大多数数据库系统允许用户指定那些只需极小开销就可以检测的完整性约束。

完整性约束通常被看成是数据库模式设计过程的一部分,它作为用于创建关系的create table命令的一部分被声明。然而,完整性约束也可以通过使用

alter table table_name add constraint constraint_name <约束>;

命令施加到已有关系上,其中<约束>可以是关系上的任意约束。

非空约束

空值是所有域的成员,因此在默认情况下是SQL中每个属性的合法值。然而对于一些属性来说,空值可能是不合适的。

使用not null约束禁止在该属性上插入空值。这会使得任何可能导致向一个声明为not null的属性插入空值的数据库修改都会产生错误诊断信息。

许多情况下我们希望避免空值。尤其是SQL​禁止在关系模式的​主码​中出现​空值。因此,在我们的大学例子中,在department关系上如果声明属性dept_name为department的主码,那它就不能为空。因此主码(​dept_name​ )不必显式地声明为​not null

唯一性约束

SQL还支持下面这种完整性约束:

unique(A1,A2,...,An)

unique声明指出属性A1,A2,...,An形成了一个超码;即在关系中没有两个元组能在所有列出的属性​A1,A2,...,An​上取值相同。

声明了唯一性的属性允许为null，除非它被显示的声明为非空，但是需要注意，空值并不等于其他的任何值。例如：

select null = null is unknown; # True

check子句

当应用于关系声明时, check(P)子句指定一个谓词P,关系中的每个元组都必须满足谓词P。

通常用​check​子句用来​保证属性值满足指定的条件。

不满足check子句的插入一般报错，而满足的则会成功插入。

注意点：

• check子句检测原理：如果​check​子句不为假(​False​ )，则它是满足约束的，因此计算结果为未知(​unknown​ )的子句也是满足的。
• 如果不需要空值（null）,则必需单独指定非空约束。
• check子句的位置取决于编码风格。通常的规范是对单个属性值约束与该属性一同列出，而更复杂的check子句则在create table的末尾列出。

根据SQL标准, check子句中的谓词可以是包括子查询在内的任意谓词。然而,当前还没有一个广泛使用的数据库产品允许check子句包含子查询的谓词。

默认值

SQL允许为属性指定默认值(default)，如下代码

create table table_name(
    id int,
    name char(4),
    score int default 0 # 设置默认成绩为0
);

引用完整性约束

我们常常希望保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现。这种情况称为引用完整性约束(referential integrity)。

外码是引用完整性约束的一种特殊形式，它要求被引用的属性构成被引用关系的主码。

我们用大学数据库SQL DLL定义的一部分来说明外码声明:

create table course(
    course_id varchar(8), 
    title varchar(50), 
    dept_name varchar(20),
    credits numeric(2,0) check (credits > 0),
    primary key (course_id),
    foreign key (dept_name) references department(dept_name)
                                    on delete set null
);

course表的定义中有一个声明foreign key(dept_name) references department。这个外码声明表示,在每个课程元组中指定的系名必须在department关系中存在。如果没有这个约束,就可能会为一门课程指定一个不存在的系名。

如果在外码约束的列中输入非 NULL 值，则此值​必须在被引用的列中存在​，否则将返回违反外码约束的错误信息。

引用完整性约束和外码约束

默认情况下,foreign key(dept_name)中外码引用的是​被引用表中的​主码属性

不过引用完整性约束不同于外码约束,引用完整性约束通常不要求引用字段是被引用关系的主码，但必需是超码

sql还支持一个可以显式指定被引用关系的属性列表的引用子句的版本（这是由于某些系统不支持省略被引用关系的属性，例如mysql），如下

foreign key(dept_name) references department(dept_name)

这个被指定的属性列表，必需声明为被引用关系的超码。

声明单属性构成外码

我们可以使用如下的简写形式作为属性定义的一部分,并声明该属性为外码:

depr_name varchar(20) references department

违反引用完整性约束

当违反参照完整性约束时,通常的处理是拒绝执行导致完整性破坏的操作(即回滚事务)。

但是,在foreign key子句中可以指明:如果被参照关系上的删除或更新动作违反了约束,那么系统可以采取一些步骤通过修改参照关系中的元组来恢复完整性约束,而不是拒绝这样的操作。

# 举例
create table course(
    ...
    foreign key (dept_name) references department(dept_name)
                                on delete cascade
                                on update cascade,
    ...
);

级联删除

通过使用与外码声明相关联的on delete cascade子句,如果删除department中的元组导致了此引用完整性约束被违反,则删除并不被系统拒绝,而是对course关系作级联删除,即删除course关系中引用被删除系名的元组。

级联更新

类似地,如果更新被引用字段时违反了约束,则更新操作并不被系统拒绝,而是将course中引用该字段的元组的dept_name字段也改为新值。

设置为空或默认值

SQL还允许foreign key子句指明除cascade以外的其他动作,例如:

• 用set null代替上面的cascade,这样如果约束被违反:可将引用域(dept_name)置为null,
• 用set default代替上面的cascade,这样如果约束被违反:可将引用域(dept_name)置为域的默认值。

级联和事务

如果一个级联更新或删除导致的对约束的违反不能通过进一步的级联操作解决,则系统中止该事务。其结果是,该事务所做的所有改变及级联动作将被撤销。

外码可以为null

外码中的属性允许为null,只要它们没有被声明为not null。外码约束检查方式如下：

• 如果给定元组中外码的所有列上存在非空值,则对该元组检查外码约束
• 如果任何外码列均为null,则该元组自动被认为满足约束

约束命名

我们可以对完整性约束赋予名称，这样带来的好处是当我们想删除一个先前定义的约束时会非常方便。

为了命名约束，我们使用关键字constraint对约束赋予名称

示例，将minsalary名称赋给salary属性上的check约束

create table text (
    ···
    salary int,
    constraint minsalary check(salary > 1000),  # 对check命名为 minsalary
    constraint prKey primary key(salary),   # 对主键约束命名为 prKey
    ···
);

删除约束

alter table text drop constraint minsalary;

如果名称缺失，那我们需要使用系统自动给约束分配的名称。但实际上不上所有的数据库都支持这种操作。

事务中违反约束

事务可能包括几个步骤,在某一步之后完整性约束也许会暂时被违反,但是后面的某一步也许就会消除这个违反。

例如,假设我们有一个主码为name的person关系,还有一个属性是ouse,并且spouse是在person上的一个外码。

也就是说,约束要求spouse属性必须包含在person表里出现的名字假设我们希望在上述关系中插入两个元组,一个是关于John的,另一个是关于Mary的,这两个元组的配偶属性分别设置为Mary和John,以此表示John和Mary彼此之间的婚姻关系。

无论先插入哪个元组,插入第一个元组的时候都会违反外码约束。在插入第二个元组后,外码约束又会满足了。

小技巧：如果spouse属性可以被赋为null,我们可以用另一种方式来避开在上面例子中的问题:在插入John和Mary元组时,我们设置其spouse属性为null,然后再更新它们的值。然而,这个技术需要更大的编程量,而且如果属性不能设为null的话,此方法就不可行。

这个person关系，大部分数据库是无法生成的，在mysql中，即使开启事务也不行，需要在设置中关闭外键约束检查，才可以插入数据。因为大部分数据库不支持延迟约束检查。

SET FOREIGN_KEY_CHECKS = 0;

延迟约束检查

为了处理上述情况，因此我们需要一个操作，即在事务即将结束时进行约束检查。即延迟约束检查。

SQL标准允许将initially deferred子句加入到约束声明中;这样完整性约束不是在事务的中间步骤上检查,而是在事务结束的时候检查。

然而,读者应该注意的是默认的方式是立即检查约束,而且许多数据库实现（包括mysql）不支持延迟约束检查。

其他约束

SQL标准还支持的另外一些用于声明完整性约束的结构。例如：

• 包含子查询的check子句
• 断言

但是，目前还没有一个广泛使用的数据库系统支持在check子句的谓词中使用子查询或创建断言（create assertion）结构 。

然而,如果数据库系统支持触发器的话, 可以通过使用触发器来实现等价的功能。

包含子查询的check子句

如果一个数据库实现支持在check子句中出现子查询,我们就可以在关系section上声明如下所示的引用完整性约束:

check( time_slot_id in( select time_slot_id from time_slot ))

这个check条件检测在section关系中每个元组的time_slot_id的确是在time_slot关系中某个时间段的标识。

因此这个条件不仅在section中插入或修改元组时需要检测,而且在time_slot关系改变时也需要检测(如在time_slot关系中,当一个元组被删除或修改的情况下)。

复杂check条件在我们希望确保数据完整性的时候是很有用的,但其检测开销可能会很大。例如check子句中的谓词不仅需要在section关系发生更新时计算,而且也可能在time_slot关系发生更新时检测。

断言

一个断言(​assertion​ )就是一个谓词,它表达了我们希望数据库总能满足一个条件。SQL中的断言为如下形式:

create assertion <assertion-name> check <predicate>;

举例：对于student关系中的每个元组,它在属性tot_cred上的取值必须等于该生所成功修完课程的学分总和。

create assertion credits_earned_constraint check(
 not exists(
     select ID
     from student
     where tot_cred<>(
         select sum(credits)
         from takes natural join course
         where student.ID = takes.ID and grade is not null and grade <> 'F'
     )
))
# 检查 不存在某个学生，它的tot_cred上的取值不等于学分总和

当创建断言时,系统要检测其有效性。如果断言有效,则今后只有不破坏断言的数据库修改才被允许。如果断言较复杂,则检测会带来相当大的开销。因此,使用断言应该特别小心。但是由于检测和维护断言的开销较大,一些系统开发者省去了对一般性断言的支持,或者只提供易于检测的特殊形式的断言。

数据类型

SQL标准支持多种固有类型,包括:

数据类型	描述
char(n)	固定长度的字符串,用户指定长度n。也可以使用全称character,不足在后补空格
varchar(n)	可变长度的字符串,用户指定最大长度n,等价于全称character varying
int	整数类型(和机器相关的整数的有限子集),等价于全称integer
smallint	小整数类型(和机器相关的整数类型的子集)
numeric(p,d)	定点数,精度由用户指定。这个数有p位数字(加上一个符号位),其中d位数字在小数点右边。 所以在一个这种类型的字段上, numeric(3,1)可以精确储存44.5,但不能精确存储444.5或0.32这样的数。
real,double precision	浮点数与双精度浮点数,精度与机器相关
float(n)	精度至少为n位的浮点数。

定点小数numeric在其他数据库系统中可能会有其他的关键字代替，例如mysql中使用decimal替代numeric

SQL标准还支持与日期和时间相关的几种数据类型:

date:日历日期,包括年(四位)、月和日。

time:一天中的时间,包括小时、分和秒。可以用变量time(p)来表示秒的小数点后的数字位数(这里默认值为0)。通过指定time with timezone,还可以把时区信息连同时间一起存储。

timestamp:date和time的组合。可以用变量timestamp(p)来表示秒的小数点后的数字位数(这里默认值为0)。如果指定with timezone,则时区信息也会被存储。

在其他数据库系统中，例如mysql中也使用datetime数据类型来表示date和time的组合，它和timestamp的区别在于，它不会存储时区。

datetime 和 timestamp

• 因为 timestamp 存储的是 数据库定义的时区 时间，所以会有时区的概念，这也是区别于 datetime 地方之一

• MySQL 对于 timestamp 字段值，会将客户端插入的时间从当前时区转换为 数据库定义的时区 再进行存储；查询时，会从 数据库定义的时区 转换回客户端当前时区再进行返回

• 默认情况下，每个连接的当前时区是服务器的时间

• 可以在每个连接的基础上设置时区，只要时区设置保持不变，该 timestamp 字段读写的值就会保持一致

• datetime 没有时区概念，客户端传什么时间就存什么时间

• 数据库的时区由time_zone变量决定

use mysql;
show variables like 'time_zone';
set @@time_zone = "+08:00";
set @@time_zone = "SYSTEM"; # 默认值

需要注意：某些数据库可视化软件在显示timestamp时，会自动转换为系统时区。

类型转换

使用cast(e as t)形式的表达式来将一个字符串(或字符串表达式)e转换成类型t,其中t可以是date、time、timestamp、int等。

select cast(id as numeric(5,0)) as inst_id
from instructor
order by

域的提取

我们可以利用extract(field from d),从date或time值d中提取出单独的域,这里的域可以是year、month、day、hour、minute或者second中的任意一种。时区信息可以用timezone_hour和timezone_minute来提取。

select extract(year from current_date());

其他

SQL定义了一些函数以获取当前日期和时间。例如,

• current_date()返回当前日期,
• current_time()返回当前时间(带有时区),
• localtime()返回当前的本地时间(不带时区)。
• current_timestamp()返回日期和时间(带有时区)
• 以及localtimestamp()(本地日期和时间,不带时区)

interval类型

SQL还支持interval(时间间隔)数据类型,它允许在日期、时间和时间间隔上进行计算。

例如,假设x和y都是date类型,那么x-y就是时间间隔类型,其值为从日期x到日期y​间隔​的天数。类似地,在日期或时间上加减一个​时间间隔​将分别得到新的日期或时间

空值的处理

在某些情况下，我们需要结果中不出现空值null，因此我们可以使用coalesce()函数来规避空值。

该函数的参数可以为任意多个，但所有的参数必需是相同的数据类型，并返回第一个非空参数。

select coalesce(salary, 0) from instructor; # 当salary未定义，即为null时，输出0
select coalesce(salary, salary2, 0) from instructor; # 输出第一个非空值，如果salary和salary2都为空，输出0

在oracle中，在该函数上做了进一步扩展，允许参数的数据类型不同，同时给了更多的选择，即decode函数

decode(value, match-1, replacement-1, match-2, replacement-2, ···, default-replacement)

它将value依次与match-i做比较，如果找到一个匹配项，就返回对应的replacement-i，如果没有一个match-i与之匹配，那么就返回默认值default-replacement。示例：

select decode(salary, null, "N/A", salary) from instructor;

如果salary等于null，那么输出’N/A’，反正输出salary

大对象类型

许多当前的数据库应用需要存储可能很大(KB级)的属性,例如一张照片;或者非常大的属性(MB级甚至GB级)。例如高清晰度的医学图像或视频片断。

因此SQL提供字符数据的大对象数据类型(clob)和二进制数据的大对象数据类型(blob)。

在这些数据类型中字符lob代表Large OBject。例如,我们可以声明属性:

book_rewiew clob(10KB)
image blob(10MB)
movie blob(2GB)

对于包含大对象(好几个MB甚至GB)的结果元组而言,把整个大对象放入内存中是非常低效和不现实的。

因此,一个应用通常用一个SQL查询来检索出一个大对象的定位器,然后在宿主语言中用这个定位器来操纵对象。例如JDBC应用编程接口允许获取一个定位器而不是整个大对象;然后用这个定位器来一点一点地取出这个大对象,而不是一次取出全部,这很像用一个read函数调用从操作系统文件中读取数据。

时态有效性

在应用程序开发中，有时会碰到这样一个场景：设置一条记录的生效时间范围，比如某个产品信息，在某个时间段内是有效的，一般设计表时会在表中增加valid_start和valid_end这两个字段来限制，并通过设置这两个字段来实现具体产品的生效范围，如果查看当前生效的记录，则需要通过时间进行过滤。

而某些数据库系统（例如Oracle 12）引入了Temporal Validity（时间有效性），通过这一特性，在不增加列的情况下可以实现时间有效性的的判断。DDL如下：

create table instructor(
    ···
    start_date date,
    end_date date,
    period for valid_time(start_date, end_date),
    ···
)

语法结构

• valid_time_column：用于指定有效时间维度的名称，Oracle将会使用该名称创建数据类型为number的不可见虚拟列；
• start_time_column：指定起始时间列的名称，数据类型为date或timestamp；
• end_time_column：指定结束时间列的名称，数据类型为date或timestamp；
• 如果不指定start_time_column和end_time_column，则会自动创建valid_time_column_START和valid_time_column_END列。

查询

oracle 12提供一些查询语句以查询时态有效性数据，例如as of period for结构可以获取其有效时间段包括特定时间的那些元组。

例如寻找2014年1月20日的教师及其工资

select name, salary, start_date, end_date
from instructor
as of period for valid_time '20-JAN-2014';

寻找时间区间的数据，例如2014年1月20日到2014年1月30日期间的所有时间段的元组

select name, salary, start_date, end_date
from instructor
versions period for valid_time between '20-JAN-2014' and '30-JAN-2014';

用户定义的类型

SQL支持两种形式的用户定义数据类型。

• 第一种称为独特类型(distinct type),我们将在这里介绍。
• 另一种称为结构化数据类型(structured data type),允许创建具有嵌套记录结构、数组和多重集的复杂数据类型。

在本章我们不介绍结构化数据类型,而是在后面第8.2节描述。

创建

可以用create type子句来定义新类型。例如,下面的语句:

create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;

把两个用户定义类型Dollars和Pounds定义为总共12位数字的十进制数,其中两位放在十进制小数点后。

(在此关键字final并不是真的有意义,它是SQL:1999标准要求的,其原因我们不在这里讨论了;一些系统实现允许忽略final关键字。)

然后新创建的类型就可以用作关系属性的类型。例如,我们可以把department表定义为:

create table department (
    dept_name varchar(20),
    building varchar(15),
    budget Dollars
);

由于有强类型检查,表达式(department.budget + 20)将不会被接受,因为属性和整型常数20具有不同的类型。一种类型的数值可以被转换(也即cast)到另一个域,如下所示:

cast(department.budget as numeric(12, 2))

我们可以在数值类型上做加法,但是为了把结果存回到一个Dollars类型的属性中,我们需要用另一个类型转换表达式来把数值类型转换回Dollars类型。

删除

SQL提供了drop type和alter type子句来删除或修改以前创建过的类型。

域定义

在用户自定义类型加入SQL之前，sql有一个相似但不同的概念：域（domain）,他可以在基本类型上施加完整性约束。

例如,我们可以定义一个域DDollars,如下所示:

create domain DDollars as numeric(12, 2) not null;

域和自定义类型的区别

• 在域上可以声明约束 ,例如​not null​ ,也可以为域类型变量定义默认值 ,然而在用户自定义类型上不能声明约束或默认值**。设计用户定义类型不仅是用它来指定属性类型,而且还将它用在不能施加约束的地方对SQL进行过程扩展。
• 域并不是强类型的**。因此一个域类型的值可以被赋给另一个域类型,只要它们的基本类型是相容的

当把check子句应用到域上时,允许关系设计者指定一个谓词,被声明为来自该域的任何变量都必须满足这个谓词。

例如, check子句可以保证教师工资域中只允许出现大于给定值的值:

create domain YearlySalary as numeric(8,2) constraint salary_value_test check(value>29000.00);

YearlySalary域有一个约束来保证年薪大于或等于290000美元。 constraint salary_value_test子句是可选的,它用来将该约束命名为salary_value_test。系统用这个名字来指出一个更新违反了哪个约束。

在数据库实现中对类型和域的支持

尽管本节描述的create type和create domain结构是SQL标准的部分,但这里描述的这些结构形式还没有被大多数数据库实现完全支持。

• PostgreSQL

  PostgreSQL支持create domain结构,但是其create type结构具有不同的语法和解释。

• SQL Server

  微软的SQL Server实现了create type结构的一个版本,支持域约束,与SQL的create domain结构类似。

• Oracle 和 MySQL

  均不支持在此描述的任何一种结构。

面向对象类型

SQL还定义了一个更复杂的面向对象类型系统,我们将在后面第8章学习。类型可以在他们的内部具有结构，例如：Person类型可能具有子结构Student、Instructor等。继承规则类似于面向对象编程语言中的规则。

生成唯一码值

我们在插入数据时对于不同的元组，我们要求要有一个唯一码可以代表这个元组，例如在使用ID int作为主码约束时，我们在插入数据时如何确定新的ID是唯一的呢？

默认的方法是先检查之前存在的所有ID，当没有重复时插入该条记录，但是这种方法会损害系统性能。

另一种方法是可以设置一个特殊的表，其中包含迄今为止所发布的最大ID值，然后当需要一个新的ID时，可以按照顺序将该值递增到下一个值并将其存储为新的最大值。

数据库系统提供了对生成唯一码值的自动管理，其语法在不同的系统之中是不同的。不过相同之处在于该功能只使用于数字型的码值数据类型。

以mysql为例：

create table test(
    id int auto_increment primary key,
    name varchar(5) null
);

mysql> select * from test;
    +-----+------+
    | id  | name |
    +-----+------+
    |   1 | wuq  |
    | 100 | www  |
    +-----+------+
mysql> insert into test(name) values("abc"); # 主键id会自动生成，对当前的最大值递增1后在存储
    +-----+------+
    | id  | name |
    +-----+------+
    |   1 | wuq  |
    | 100 | www  |
    | 101 | abc  |
    +-----+------+

当然，也可以使用identify规范来指定其他选项，包括设置最小值和最大值、选择起始值等等，但具体的方式取决于数据库。

创建表的扩展

create table like

应用常常要求创建与现有的某个表的模式相同的表。SQL提供了一个create table like的扩展来支持这项任务（不是所有系统都支持，经测试mysql支持）:

create table temp_instructor like instructor;

上述语句创建了一个与instructor具有相同模式的新表temp_instructor。

like方法其实是专门复制表结构的方法，但是它只复制表的结构和相关属性并不复制数据。

create table as

当书写一个复杂查询时,把查询的结果存储成一个新表通常是很有用的;这个表通常是临时的。这里需要两条语句,一条用于创建表(具有合适的列),另一条用于把查询结果插入到表中。SQL:2003提供了一种更简单的技术来创建包含查询结果的表。例如,下面的语句创建了表t1,该表包含一个查询的结果。

create table t1 as (
    select * 
    from instructor
    where dept_name ='Music')
with data;

在默认情况下,列的名称和数据类型是从查询结果中推导出来的。通过在关系名后面列出列名,可以给列显式指派名字，例如t1(a1, a2, ···, an)。

正如SQL:2003标准所定义的,如果省略with data子句,表会被创建,但不会载入数据。但即使在省略​with data​子句的情况下,很多数据库实现还是通过默认方式往表中加载了数据。注意几种数据库实现都用不同语法支持create table…like和create table…as的功能;请参考相应的系统手册以获得进一步细节。

MySQL中的写法

MySQL不执行with data子句,省略上面的with data子句也可以将查询的结果存储成新的表:

create table t1 as (
    select * 
    from instructor
    where dept_name ='Accounting'
);

两种方法的区别

• 相同点：
  • 都是创建一个新表
• 不同点
  • create table as 只是复制原数据，其实就是新建一个表来保存查询的结果，表结构不一定和原表相同
  • create table like 产生与源表相同的表结构，包括索引和主键,但是create table like只是复制表结构,并没有复制表中的数据.

create table as和create view的区别

上述create table … as语句与create view语句非常相似,并且都用查询来定义。
两者主要的区别在于:

• create table … as语句创建的表的内容在建时表的时候就确定下来了
• create view语句创建的视图的内容总是反映当前查询的结果。

模式、目录与环境

要理解模式和目录的形成,需要考虑文件系统中文件是如何命名的。早期的文件系统是平面的,也就是说,所有的文件都存储在同一个目录下。当代的文件系统有一个目录(或者文件夹)结构,文件都存储在子目录下。要单独命名一个文件,我们必须指定文件的完整路径名,例如/usersavi/db-book/chapter3.tex​。

早期数据库系统跟早期文件系统一样,早期数据库系统也只为所有关系提供一个命名空间。用户不得不相互协调以保证他们没有对不同的关系使用同样的名字。当代数据库系统提供了三层结构的关系命名机制。

1. 最顶层由目录(catalog​)构成,一些数据库实现用术语”数据库”代替术语”目录”
   我们也可以将目录的概念看作是一个数据库系统下的一个服务，一个数据库系统可以产生多个服务，每个服务所分配的端口互不相同
2. 每个目录都可以包含模式(schema​)，在某些数据库系统中也称之为database​
3. 诸如关系​和视图​那样的SQL​对象都包含在模式中。

要在数据库上做任何操作,用户(或程序)都必须先连接到数据库。为了验证用户身份,用户必须提供用户名以及密码(通常情况下)。每个用户都有一个默认的目录和模式,这个组合对用户来说是唯一的。当一个用户连接到数据库系统时,将为该连接设置好默认的目录和模式。这对应于当用户登录进一个操作系统时,把当前目录设置为用户的主(home​)目录。

关系名字的全称

为了唯一标识出一个关系,必须使用一个名字,它包含三部分,例如:catalog5.univ_shema.course

默认目录中的关系可以省略目录名

当名字的目录部分被认为是连接的默认目录时,可以省略目录部分。这样如果catalog5是默认目录,我们可以用univ_shema.course来唯一标识上述关系。

访问其他非默认模式中的关系必须指定模式名

如果用户想访问存在于另外的模式中的关系,而不是该用户的默认模式,那就必须指定模式的名字。

默认模式中的关系也省略模式名

然而,如果一个关系存在于特定用户的默认模式中,那么连模式的名字也可以省略。这样,如果catalog5是默认目录并且univ_schema是默认模式,我们可以只用course。

使用目录和模式可以解决命名冲突

当有多个目录和模式可用时,不同应用和不同用户可以独立工作而不必担心命名冲突。不仅如此,一个应用的多个版本(一个产品版本,其他是测试版本)可以在同一个数据库系统上运行。

这种方式可以使得例如mysql​5.7和8.0共存，同时也使得mysql 8.0​可以创建多个端口不同的服务。

SQL环境

• 默认目录和默认模式是为每个连接建立的SQL​环境(SQL environment​)的一部分。
• 除了默认目录和默认模式,SQL​环境还包括用户标识(也称为授权标识符)。
• 所有通常的​SQL​语句,包括DDL和DML语句,都在一个模式的环境中运行。

如何创建和删除模式

我们可以用create schema和drop schema语句来创建和删除模式。

在某些数据库系统中还可以可以使用create database 或 drop database​来替代上述语句。

• 在大多数数据库系统中,模式还随着用户账户的创建而​自动创建,此时模式名被置为用户账号名。
• 模式要么建立在默认目录中,要么建立在创建用户账户时所指定的目录中。
• 新创建的模式成为用户账户的默认模式。

创建和删除目录依据实现的不同而不同,这不是SQL标准中的一部分。

mysql的默认目录可以使用show variables like '%datadir%'来查看。

索引

许多查询只涉及文件中的少量记录。例如,像这样的查询找出​Physics​系的所有教师,或找出​ID​为22201的学生的​tot_cred​值,只涉及学生记录中的一小部分。如果系统读取每条记录并逐个检查其ID​域是否为”22201”,或者dept_name​域是否取值为Physics​,这样的方式是很低效的。由此引出索引。

关系属性上的索引（​index​ ）是一种数据结构，它允许数据库系统高效的找到在关系中具有该属性指定值的那些元组，而不用扫描关系的所有元组。 索引是冗余的数据结构，索引构成数据库物理模式的一部分，而不是数据库逻辑模式的一部分。

很多数据库系统还提供一种方式来指定要使用的索引的数据结构，如B+树或者散列索引等。

原则上数据库系统可以自动决定创建何种索引，但是，由于索引的空间代价以及索引对更新处理的影响，因此大多数SQL实现允许程序员通过DDL命令对索引的创建和删除进行控制。sql给出的索引创建语法被大多数数据库系统采用，但该语法并不是SQL标准的一部分。

创建

create index <索引名> on <关系名> (<属性列表>);

属性列表是构成索引搜索码的关系属性上的列表。

举例：在instructor关系上定义dept_name为搜索码，名为dept_index的索引

create index dept_index on instructor (dept_name);

如果用户提交的SQL查询可以从索引的使用中获益,那么SQL​查询处理器就会自动使用索引。

如果我们想声明一个搜索码是一个候选码，那么在索引的定义中增加属性unique

create unique index dept_index on instructor (dept_name);

如果在数据库中dept_name并不是一个候选码，那么在创建时系统会产生错误消息，并且索引创建失败，如果创建成功，则后面违反候选码声明的任何元组的插入企图都会失败。

删除

drop index <索引名>;

授权

我们可能会给一个用户在数据库的某些部分授予几种形式的权限。对数据的授权包括:

• 授权读取数据。
• 授权插入数据。
• 授权更新数据。
• 授权删除数据。

每种类型的授权都称为一个权限(privilege)。我们可以在数据库的某些特定部分(如一个关系或视图)上授权给用户所有这些类型的权限,或者完全不授权,或者这些权限的一个组合。

当用户提交查询或更新时,SQL执行先基于该用户曾获得过的权限来检查此查询或更新是否是授权过的。如果查询或更新没有经过授权,那么将拒绝执行这个查询或更新。

除了在数据上的授权之外,用户还可以被授予在数据库模式上的权限,例如,可以允许用户创建、修改或删除关系。拥有某些形式的权限的用户还可以把这样的权限转授(授予)给其他用户,或者撤销收回)一种此前授出的权限。

权限的授予与收回

SQL​标准包括select​、insert​、update​和delete​权限。(all [privileges]​)可以用作全部允许的权限的简写形式。一个创建了新关系的用户将自动被授予该关系上的全部权限

SQL​数据定义语言包括授予​和收回权限​的命令。

授权

grant <权限列表> on <关系名或视图名> to <用户或角色列表>;

权限列表使得一个命令可以授予多个权限。

select权限

关系上的select权限用于读取关系中的元组。下面的grant语句授予数据库用户Amit和Satoshi在partment关系上的select权限:

grant select on department to Amit,Satoshi;

该授权使得这些用户可以在department关系上执行查询。

update权限

关系上的update权限允许用户修改关系中的任意元组。

update权限既可以在关系的全部的属性上授予,又可以只在某些属性上授予。(忽略属性列表时默认授予关系中全部属性的update权限)

grant update on department to Amit,Satoshi;     # 授予Amit，Satoshi在department关系中所有属性的更新权限
grant update(budget) on department to Amit,Satoshi; # 授予Amit，Satoshi在department关系中budget属性的更新权限

insert权限

关系上的insert权限允许用户往关系中插入元组。

insert权限也可以指定属性列表;对关系所作的任何插入必须只针对这些属性,系统将其余属性要么赋默认值(如果这些属性上定义了默认值),要么赋null;

delete权限

关系上的delete权限允许用户从关系中删除元组。

其他

用户名public指系统的所有当前用户和将来的用户。你可以理解为所有数据库用户的集合。因此,对public的授权隐含着对所有当前用户和将来用户的授权。

如果某个权限赋于了public，那么所以数据库的用户都可以有这个权限（当然有些用户可能连connect的权限都可以没有）。

注意：public不是所有数据库系统都共有的。例如：mysql没有，而oracle 或 sql server有

比如让所有用户对关系t1有select权限

grant select on user1.t1 to public;

那么所有的public用户都可以访问该表t1

grant dba to public;

那么所有的用户都有dba权限了

在默认情况下，被授予权限的用户或者角色无权把此用户授予另外一个角色或用户，但是sql允许一个权限授予来指定接受者可以进一步把此权限授予另一个用户。

收权

我们使用revoke语句来收回权限。此语句的形式与grant几乎是一样的:

revoke <权限列表> on <关系名或视图名> from <用户或角色列表>;

因此,要收回前面我们所授予的那些权限,我们书写下列语句:

revoke select on department from Amit,Satoshi;
revoke update(budget) on department from Amit,Satoshi;

如果被收回权限的用户已经把权限授予了其他用户,权限的收回会更加复杂。

在包括mysql的一些数据库系统中，使用'user'@'host'​​来表示一个用户。

视图的授权

举例：教师A需要知道一个university​​关系中给定系T的所有教师工资，但是该教师A无权看见除了给定系T之外的教师工资。因此，该教师A对university关系的直接访问必须被禁止。但是,如果他要访问T系的信息，就必须得到在一个视图上的访问权限，我们称该视图为T_university​​

create view T_university as (select * from university where dept_name = 'T'); # 视图定义
select * from T_university; # 教师A的查询

当查询处理器将此查询转换为数据库中实际关系上的査询时，它产生了一个在university​​上的查询。这样，系统必须在开始查询处理以前，需要检查该教师查询的权限。

• 创建视图的用户不需要获得该视图上的全部权限。 他得到的那些权限不会为他提供超越他已有权限的额外授权。
• 用户对视图的权限从定义视图的关系中继承得到。
  • 如果一个创建视图的用户在用来定义视图的关系上没有update​​权限，那么他不能得到视图上的update​​权限。
  • 如果用户创建一个视图，而此用户在该视图上没有获得任何权限，系统会拒绝这样的视图创建请求。
  • 在我们的university​​视图例子中，视图的创建者必须在instructor​​关系上具有select​​权限。

模式的授权

SQL​​标准为数据库模式指定了一种基本的授权机制：只有模式的拥有者才能够执行对模式的任何修改，诸如：创建或删除关系、增加或删除关系的属性、以及增加或删除索引。

举例：SQL​​提供一种引用权限（references​​），它允许用户在创建关系时声明外码。可以与update​​权限类似的方式将references​​权限授予到特定属性上。如下：

grant references(dept_name) on deparment to Mariano;

由于外码约束限制了被参照关系上的删除和更新操作。 所以如果Mariano​​​在关系r​​​中创建了一个外码，它参照department​​​关系的dept_name​​​属性，然后在r​​​中插入一条属于Geology​​​系的元组。那么就再也不可能从department​​​关系中将Geology​​​系删除，除非同时也修改关系r​​​。因此，需要有references​​​权限。

权限的转移

获得了某些操作授权的用户可能被允许将此授权传递给其他用户。

• 在默认方式下,被授予权限的用户/角色无权把得到的权限再授予给另外的用户/角色。
• 如果我们希望在授权时允许接受者把得到的权限再传递给其他用户，我们可以在相应的grant​命令后面附加with grant option​（具有授予权）子句。

例如：允许Amit​用户将得到的选择权限传递给其他用户。

grant select on department to Amit with grant option;

需要注意的是：一个对象(关系/视图/角色)的创建者拥有该对象上的全部权限,包括给其他用户授权的权限。

授权图

指定权限从一个用户到另一个用户的传递可以表示为授权图(authorization graph​)。图中节点代表用户。

一个用户具有权限的充要条件是：当且仅当存在从授权图的根（DBA）到代表该用户的节点的路径。

‍

权限的回收

假设DBA决定收回用户U1的授权。由于U4从U1处获得过授权，因此其被U1授予权限也应该被收回。同理U5也会被收回被U1授予的权限。由此引出级联收权的概念。
引入原因：对狡猾的用户可能企图通过相互授权来破坏权限收回规则。例如,如果U2最初由DBA授予了一种权限，U2进而把此权限授予给U3。假设U3现在把此权限授回给U2。如果DBA从U2收回权限,看起来好像U2保留了通过U3获得的授权。然而，注意一旦DBA从U2收回权限，在授权图中就不存在从根到U2或U3的路径了。这样,SQL​保证从这两个用户那里都收回了权限。

级联收权

从一个用户/角色那里收回权限可能导致其他用户/角色也失去该权限。这一行为称作级联收回。在大多数的数据库系统中，级联是默认行为。

使用关键字cascade​表面需要级联收权，不过由于级联收权是默认行为，我们可以省略该关键字。

revoke select on department from Amit,Satoshi cascade;

级联收权产生的问题

假定Satoshi​具有dean​角色,他将instructor​授给Amit​,后来dean​角色从Satoshi​收回(也许由于Satoshi​离开了大学);Amit​继续被雇佣为教职工,并且还应该保持dean​角色。而如果我们使用默认行为，那么Amit​的dean​角色将不复存在。因此我们需要一个方法来指定不需要级联收权。

revoke​语句可以声明restrict​来防止级联收回：

revoke select on department from Amit,Satoshi restrict;

收回用户权限转移行为

使用grant option for (select, insert)​语句，收回某个用户将某权限授给其他用户的行为。

revoke grant option for select on department from Amit;

注意一些数据库实现不支持上述语法；它们采用另一种方式：收回权限本身，然后不带grant option​重新授权。

通过角色授权

为了处理以上情况，SQL​允许权限由一个角色授予,而不是由用户来授予。SQL​有一个与会话所关联的当前角色的概念。默认情况下，一个会话所关联的当前角色是空的(某些特殊情况除外)。

将会话和当前角色进行关联

执行​set role role_name​可以设置一个会话所关联的当前角色。这个指定的角色必须已经授予给用户，否则set role​语句执行失败。

如果要在授予权限时将授权人设置为一个会话所关联的当前角色，并且当前角色不为空的话,我们可以在授权语句后面加上：

granted by current_role

授权给角色可以避免级联回收的问题

假设将角色instructor​(或其他权限)授给Amit​是用granted by current_role​子句实现的，当前角色被设置为dean​而不是授权人(用户Satoshi​)，那么从Satoshi​处收回角色/权限（包括dean​）就不会导致收回以dean​角色作为授权人所授予的权限， 即使Satoshi​是执行该授权的用户，这样即使Satoshi​的权限被回收后，Amit​仍然能够保持instructor​角色。

‍

行级授权

引入原因：例如在大学关系中，我们希望允许学生在takes​关系中查看他自己的数据，但是不允许查看其它用户的数据。因此我们需要行级授权。

Oracle​以及PostgreSQL​和SQL server​支持类似的机制来实现行级授权，但使用了不同的语法。

Oracle​虚拟私有数据库功能支持如下所示的行级授权，它允许系统管理员将函数与关系相关联，该函数返回一个谓词，该谓词会自动添加到使用该关系的任何查询中。

ID = sys_context('USERENV', 'SESSION_USER');

系统会将此谓词添加到使用takes​关系的每个查询的where​子句中，其结果是，每名学生只能看到ID值与其ID相匹配的那些takes元组。

缺点：

• 它可能会显著的改变查询的含义。如果一个用户编写查询来查找所有课程的平均成绩，则他最终会得到他的成绩的平均值，而不上所有用户成绩的平均值。

角色

‍

考虑在一个大学里不同人所具有的真实世界角色。每个教师必须在同一组关系上具有同种类型的权限。有两种方式可以解决该问题：

• 单独给每一个新的教师授予这些权限
• 指明所有教师应该被授予的权限，并单独标出那些数据库用户是教师

由此，我们可以指定一个教师角色，该角色有相应的权限，当雇佣了一位新的教师时，将他表示为该角色，这样就可以不用单独对该教师授予权限了。

在数据库中建立一个​角色（role）集​​ ,可以给角色授予权限,就和给每个用户授权的方式完全一样。每个数据库用户被授予一组他有权扮演的角色(也可能是空的)。

在SQL​中创建角色如下所示:

create role instructor;

任何可以授予给用户的权限都可以授予给角色。给用户授予角色就跟给用户授权一样。

grant select on takes to instructor; # 给instructor用户授予select权限
create role dean; # 创建dean角色
grant instructor to dean; # 将instructor角色权限给dean角色

因此,一个用户或一个角色的拥有的权限包括：

• 所有直接授予​该用户或该角色的权限。
• 其他角色授予​给该用户或该角色的权限。

由此产生角色链： 例如，

角色a​授予给角色b​。接着，角色b​授予给角色c​。这样，角色c​就继承了角色a​和b​的权限。

值得注意的是，基于角色的授权概念并没有在​SQL​中指定， 但在很多的共享应用中，基于角色的授权被广泛应用于存取控制。当然在包括mysql​的一些数据库实现中也有角色的概念。

‍

高级数据库知识

可以通过以下两种方法从通用编程语言连接数据库：

动态​**SQL**​：程序设计语言可以通过函数(对于过程式语言)或者方法(对于面向对象的语言)来连接数据库服务器并与之交互。利用动态SQL​可以在运行时以字符串形式构建SQL​查询，提交查询，然后把结果存入程序变量中，每次一个元组。动态SQL​的SQL​组件允许程序在运行时构建和提交SQL​查询。

连接数据库的标准

• JDBC​：（java database connectivity​），基于Java​语言的数据库连接标准。该标准定义了Java​用来连接数据库的API​
• ODBC​：（open database connectivity​），最初由C语言开发，后来扩展到C++、C#、python​的数据库连接标准。该标准同样定义了用来连接数据库的API​

不同的语言还可以使用不同的驱动程序连接数据库，不一定非要使用上述的两种标准提供的API​，例如python​还可以使用psycopg2​来连接PostgreSQL​，使用MySQLdb​驱动连接MySql​（python2​使用MySQLdb​，python3​使用pyMySQL​），当然最好使用pyodbc​，他可以连接支持ODBC​标准的大多数数据库。

嵌入式​**SQL**​​ : 与动态SQL​​类似，嵌入式SQL​​提供了另外一种使程序与数据库服务器交互的手段。然而，嵌入式S0L​​语句必须在编译时全部确定，并交给预处理器。预处理程序提交SQL​​语句到数据库系统进行预编译和优化，然后它把应用程序中的SQL​​语句替换成相应的代码和函数，最后调用程序语言的编译器进行编译。

import pyodbc
import pymysql # 使用特定驱动连接mysql
print(pyodbc.drivers()) # 打印odbc安装了的数据库驱动程序
cont = pyodbc.connect('DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=forum;UID=root;PWD=Wql247360')
cont = pymysql.connect(host='localhost', user='root', password='Wql247360', database='forum') # 特定驱动连接mysql
cur = cont.cursor()
cur.execute("SELECT * FROM tags;")
result = cur.fetchall()
cur.close()
cont.close()
print(result)

['SQL Server', 'MySQL ODBC 8.0 ANSI Driver', 'MySQL ODBC 8.0 Unicode Driver']
[('STL', 1), ('test', 1), ('其他', 1), ('哈哈', 1)]

嵌入式SQL

SQL​标准定义了嵌入SQL​到许多不同的语言中,例如C​、C++​、 Cobol​、 Pascal​、Java​、PL/I​和Fortran​。

SQL​查询所嵌入的语言被称为宿主语言，宿主语言中使用的SQL​结构被称为嵌入式SQL​，使用宿主语言写出的程序可以通过嵌入式SQL​的语法访问和修改数据库中的数据。

一个使用嵌入式​SQL​的程序在编译前必须先由一个特殊的预处理器进行处理。嵌入的SQL​请求被宿主语言的声明以及允许运行时刻执行数据库访问的过程调用所代替。然后,所产生的程序由宿主语言编译器编译。这是嵌入式SQL​与JDBC/ODBC​的主要区别。

• 在JDBC​中,SQL​语句是​在运行时被解释的​(即使是利用预备语句特性对其进行准备也是如此)
• 当使用嵌入式SQL​时,一些SQL​相关的错误(包括数据类型错误​ )可以在编译过程中被发现​。

嵌入式数据库

嵌入式数据库的名称来自其独特的运行模式。这种数据库嵌入到了应用程序进程中，消除了与客户机与服务器配置相关的开销。同时该类应用程序只为内部使用而维护数据库，并且除非通过应用程序本身，否则无法访问到数据库。在这种情况下可以使用嵌入式数据库（embedded database​）

嵌入式数据库是轻量级的，在运行时，它们需要较少的内存。热门的选择包括Java DB​、SQLite​、HSQLBD​，还有MySQL​的嵌入式版本。

嵌入式数据库系统缺少完全基于服务器的数据库系统的许多特性，但是它们为那些可以从数据库抽象中受益而无需支持超大型数据库或者大规模事务处理的应用程序提过了优势。

函数和过程

在本节中,我们将演示开发者如何来编写他们自己的函数和过程，并把它们存储在数据库里并在SQL​语句中调用。
函数对于​特定的数据类型​比如图像和几何对象来说特别有用。例如，用在地图数据库中的一个线段数据类型可能有一个相关函数用于判断两个线段是否交叠，一个图像数据类型可能有一个相关函数用于比较两幅图的相似性。

优点：

函数和过程允许”业务逻辑”作为存储过程记录在数据库中,并在数据库内执行。
例如,大学里通常有许多规章制度，规定在一个学期里每个学生能选多少课，在一年里一个全职的教师至少要上多少节课等等。尽管这样的业务逻辑能够被写成程序设计语言过程并完全存储在数据库以外，但把它们定义成数据库中的存储过程有几个优点。例如，它允许多个应用访问这些过程，允许当业务规则发生变化时进行单个点的改变，而不必改变应用系统的其他部分。然后应用程序代码可以调用存储过程，而不是直接更新数据库关系

SQL​​允许定义函数、过程和方法。可以通过SQL​​的有关过程的组件来定义，也可以通过外部的程序设计语言来定义,例如Java​​、C​​或C++​​。我们在这里介绍的是SQL​​标准所定义的语法，但Oracle​​、SQL Sever​​和PostgreSQL​​所支持的过程语言都与我们在这里描述的标准语法有所差别。

函数

返回值

示例：给定一个系的名字,返回该系的教师数目。我们可以定义如下所示的函数：

create function dept_count(dept_name varchar(20))
returns integer
begin
    declare d_count integer;
        select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_name;
    return d_count;
end;

调用示例：返回教师数大于12的所有系的名称和预算

select dept_name,budget
from department
where dept_count(dept_name)>12;

返回表

SQL​标准支持返回关系作为结果的函数;这种函数称为表函数(table functions​)：

该函数返回一个包含某特定系的所有教师的表。注意,使用函数的参数时需要加上函数名作为前缀( **​**instructor_of.dept_name**​ **)。

create function instructor_of(dept_name varchar(20))
    returns table(
        ID varchar(5),
        name varchar(20),
        dept_name varchar(20),
        salary numeric(8,2))
return table(
    select ID,name,dept_name,salary
        from instructor
        where instructor.dept_name=instructor_of.dept_name;
);

调用示例：

select *
from table(instructor_of('Finance'));

经测试，MySQL不支持返回结果为关系的函数

‍

过程

SQL也支持过程（procedure​）

dept_count函数也可以写成一个过程，如下所示

create procedure
    dept_count_proc(in dept_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name;
end;

• 关键字in​表示待赋值的参数（用C语言的的话来说就是形参）
• 关键字out​表示返回结果

可以从一个SQL​过程中或者从嵌入式SQL​中使用call​语句调用过程：

declare d_count integer; -- 声明返回的结果
call dept_count_proc('Physics', d_count); -- 调用过程

同时，过程和函数可以通过动态SQL​触发。

函数和过程的重载

• SQL​允许多个过程同名，只要同名过程的参数个数不同。名称和参数个数共同用于标识一个过程。
• SQL​也允许多个函数同名，只要这些同名的不同函数的参数个数不同，或者对于那些有相同参数个数的函数，至少有一个参数的类型不同。

‍