sql

简单查询

基本语法

FROM 子句确定查询表；
WHERE 子句约束元组查询条件；
SELECT 约束查询属性；

SELECT *
FROM Movies
WHERE studioName="Disney";

投影操作

限定 SELECT 子句；

SELECT title, length
FROM Movies
WHERE studioName="Disney";

属性别名

SELECT title AS name,length AS duration
FROM Movies
WHERE studioName="Disney";

选择操作

限定 WHERE 子句；

SELECT title AS name,length AS duration
FROM Movies
WHERE studioName="Disney" AND year = 1990;

排序

使用 ORDER BY 子句；
使用 ASC 和 DESC 设置排序顺序，默认为 ASC；
排序操作最后执行；

SELECT title AS name,length AS duration
FROM Movies
WHERE title LIKE "Star__";
ORDER BY length, title;

运算表达式

比较运算

=；
<>；
<；
> ；
<=；
>=；

布尔运算

AND；
OR；
NOT；

字符串比较

使用比较运算符；
比较字母顺序；

模式匹配

使用 LIKE 操作符；
% 表示任意长度的字符；
_ 表示任意一个字符；

SELECT title AS name,length AS duration
FROM Movies
WHERE title LIKE "Star__";

空值

空值解释

未知值；
不适用的值；
保留值；

运算规则

NULL 进行算术运算结果仍为 NULL；
NULL 比较结果为 UNKNOWN；

布尔值

TRUE；
FALSE；
UNKNOWN：或真或假；

多关系查询

笛卡尔积

FROM 子句中引入多个表；

SELECT title AS name,length AS duration
FROM Movies, MovieExec
WHERE title="StarWars" AND producerC=cert;

消除同名属性

R.A 表示关系 R 的属性 A；

SELECT MovieStar.name, MovieExec.name
FROM MovieStar, MovieExec
WHERE MovieStar.address = MovieExec.address;

表重命名

修改 FROM 子句；

SELECT Star1.name,Star2.name
FROM MovieStar Star1, MovieStar Star2
WHERE Star1.address = Star2.address

查询的并, 交, 差

并

(SELECT name,address FROM MovieStar WHERE gender="F")
UNION
(SELECT name,address FROM MovieExec WHERE netWorth>10000000);

交

(SELECT name,address FROM MovieStar WHERE gender="F")
INTERSECT
(SELECT name,address FROM MovieExec WHERE netWorth>10000000);

差

(SELECT name,address FROM MovieStar WHERE gender="F")
EXCEPT
(SELECT name,address FROM MovieExec WHERE netWorth>10000000);

子查询

查询 A 是查询 B 的一部分；
查询 A 称作子查询；
子查询可有下一级子查询；

产生标量值的子查询

首先执行子查询；
子查询返回一个原子值；
返回值作为外层查询的查询条件；

SELECT name
FROM MovieExec
WHERE cert=(
    SELECT producerC FROM Movies WHERE title="Star Wars"
    );

关系的条件表达式

条件表达式	含义
EXISTS R	R 非空返回真值
s IN R	s 为 R 中的某个值返回真值
s NOT IN R	s 不为 R 中的某个值返回真值
s > ALL R	s 大于 R 中的所有值返回真值, 其余比较运算符同理
NOT (EXISTS/ALL/ANY)	结果取反

元组的条件表达式

括号包裹的原子值或属性；
比较元组的条件表达式时，根据顺序依次比较；

-- 子查询 1
-- 找出 StarsIn 中 starName 为 Harrison Ford 的元组
-- 提取所有的 (movieTitle，movieYear) 集合
-- 子查询 2
-- 检索 Movies 所有元组，其 (title，year) 在子查询 1 的查询结果中
-- 提取子查询 2 所有满足条件的元组的 producerC 属性值集合
-- 主查询
-- 找出 MovieExec 中 cert 等于子查询 2 属性值集合任意值的元组
-- 提取所有满足查询条件的元组的 name 属性值
SELECT name
FROM MovieExec
WHERE cert=(
    SELECT producerC
    FROM Movies
    WHERE (title, year) IN (
        SELECT movieTitle, movieYear
        FROM StarsIn
        WHERE starName="Harrison Ford"
        )
    );

关联子查询

一个子查询计算多次；
子查询中某项为上级查询中某个元组变量的值；

SELECT title
FROM MoviesOld WHERE year < ANY (
    SELECT year
    FROM Movies
    WHERE title=Old.title
    );

FROM 子句中的子查询

查询结果必须使用别名

SELECT name
FROM MovieExec, (
    SELECT producerC#
    FROM Movies, StarsIn
    WHERE title=movieTitle AND year =movieYear
    ) Prod
WHERE cert#=Prod.producerC#;

连接

交叉连接

作用于 FROM 子句；
使用 CROSS JOIN 操作符；
等效于笛卡尔积；

SELECT name
FROM Movies CROSS JOIN StarsIn

$\theta$ 连接

作用于 FROM 子句；
使用 JOIN 。。.ON 操作符；
JOIN 笛卡尔积两张表；
ON 为筛选条件；

SELECT title, length
FROM Movies JOIN StarsIn ON title=movieTitle AND year=movieYear;

自然连接

作用于 FROM 子句；
使用 NATURAL JOIN 操作符；

SELECT name
FROM Movies NATURAL JOIN StarsIn

外连接

作用于 FROM 子句；
使用 FULL/RIGHT/LEFT OUTER JOIN 操作符；
NULL 填充悬浮元祖；

SELECT name
FROM Movies FULL OUTER JOIN StarsIn

全关系操作

消除重复

使用 DISTINCT 关键字；
耗费极大；

SELECT DISTINCT name
FROM Movies FULL OUTER JOIN StarsIn

交, 并, 差中的重复

默认消除重复；
若保留重复使用 ALL 关键字；

(SELECT name,address FROM MovieStar WHERE gender="F")
UNION ALL
(SELECT name,address FROM MovieExec WHERE netWorth>10000000);

聚集操作符

SUM：求和；
AVG：平均值；
MIN：最小值；
MAX：最大值；
COUNT：元组数量；

SELECT AVG(netWorth)
FROM MovieExec;

-- 消除重复
SELECT COUNT(DISTINCT starName)
FROM StarsIn;

分组

使用 GROUP BY 子句；
关系根据 GROUP BY 子句中的属性分组；
聚集操作符作用于各分组上；

SELECT studioName,SUM(length)
FROM Movies
GROUP BY studioName;

分组, 聚集和空值

NULL 在聚集操作中被忽视，COUNT 为 0，其余均为 NULL；
NULL 分组中作为一般值看待；

HAVING 子句

作用于 GROUP BY 之后；
筛选指定条件的分组；

SELECT name,SUM(length)
FROM MovieExec,Movies
WHERE producerC#=cert#
GROUP BY name
HAVING MIN(year)<1930;

SQL 查询子句顺序

SELECT；
FROM；
WHERE；
GROUP BY；
HAVING；
ORDER BY；

数据库更新

插入元组

INSERT INTO R(A1,...,An) VALUES (U1,...,Un)；

INSERT INTO StarsIn
VALUES("The Maltese Falcon",1942,"Sydney Greenstreet");

删除元组

DELETE FROM R WHERE <条件>；

DELETE FROM MovieExec
WHERE netWorth<10000000;

修改元组

UPDATE R SET <新值赋值> WHERE <条件>；

UPDATE MovieExec
SET name = "Pres"
WHERE cert# IN (
    SELECT presC#
    FROM Studio
    );

事务

原子性的数据库操作的集合；
- 事务中的操作要么全部执行，要么都不执行；
事务是可串行化的；
- 同一时刻只有一个事务执行；
数据库通过事务解决并发操作导致的错误问题；

只读事务

只有读操作的事务；
不会修改数据库；
只读事务可以并行执行；

-- 下一条事务为只读事务
SET TRANSACTION READ ONLY;
-- 下一条事务可写
SET TRANSACTION READ WRITE

脏数据

事务中写入，但未提交的数据；
事务一旦失败，脏数据立刻移除；

脏读

对于事务产生的脏数据的读取操作；

-- 设置数据库事务读取其他事务脏数据
SET TRANSACTION READ WRITE
ISOLATION LEVEL READ UNCOMMITTED;

隔离层次

规定事务之间的操作；
- 可串行化：默认隔离层次；
- 读未提交：允许脏读；
- 读提交：禁止脏读，允许该隔离层次的事务多次进行查询操作得到不同答案；
- 可重复读：禁止脏读，假定该隔离层次的事务多次进行查询操作得到相同答案；

-- 可串行化
ISOLATION LEVEL SERIALIZABLE;
-- 读未提交
ISOLATION LEVEL READ UNCOMMITTED;
-- 读提交
ISOLATION LEVEL READ COMMITTED;
-- 可重复读
ISOLATION LEVEL REPEATABLE READ;

简单查询​

基本语法​

投影操作​

投影操作​

属性别名​

选择操作​

选择操作​

排序​

运算表达式​

比较运算​

布尔运算​

字符串比较​

模式匹配​

空值​

空值解释​

运算规则​

布尔值​

多关系查询​

笛卡尔积​

消除同名属性​

表重命名​

查询的并, 交, 差​

并​

交​

差​

子查询​

子查询​

产生标量值的子查询​

关系的条件表达式​

元组的条件表达式​

关联子查询​

FROM 子句中的子查询​

连接​

交叉连接​

θ\thetaθ 连接​

自然连接​

外连接​

全关系操作​

消除重复​

交, 并, 差中的重复​

聚集操作符​

分组​

分组, 聚集和空值​

HAVING 子句​

SQL 查询子句顺序​

数据库更新​

插入元组​

删除元组​

修改元组​

事务​

事务​

只读事务​

脏数据​

脏数据​

脏读​

隔离层次​

简单查询

基本语法

投影操作

投影操作

属性别名

选择操作

选择操作

排序

运算表达式

比较运算

布尔运算

字符串比较

模式匹配

空值

空值解释

运算规则

布尔值

多关系查询

笛卡尔积

消除同名属性

表重命名

查询的并, 交, 差

并

交

差

子查询

子查询

产生标量值的子查询

关系的条件表达式

元组的条件表达式

关联子查询

FROM 子句中的子查询

连接

交叉连接

$\theta$ 连接

自然连接

外连接

全关系操作

消除重复

交, 并, 差中的重复

聚集操作符

分组

分组, 聚集和空值

HAVING 子句

SQL 查询子句顺序

数据库更新

插入元组

删除元组

修改元组

事务

事务

只读事务

脏数据

脏数据

脏读

隔离层次