数据库系统概论复习 第四章中级SQL

第4章 中级SQL

4.1连接方式

4.1.1 INNER JOIN (内连接)

natural join (自然连接)

作用：将两个表中具有相同名称的列进行匹配

eg:

	表1
A	B	C
1	2	3
5	6	7

	表2
C	D	E
3	4	5
8	9	1

如果是笛卡尔积运算则是

A	B	C	C	D	E
1	2	3	3	4	5
5	6	7	8	9	1
1	2	3	8	9	1
5	6	7	3	4	5

---

自然连接结果：

表1 natural join 表2

A	B	C	D	E
1	2	3	4	5

---

内连接（inner join）

与自然连接相同，不同之处自然连接则是同名属性比较，内连接不要求两属性列同名，可以用通过using或on 来指定某两列字段相同的连接条件。

select ... from 表1 inner join 表2 on 表1.A=表2.E

结果：

A	B	1.C	2.C	D	E
5	6	7	3	4	5

少了一个数据，自然连接时某些属性值不同导致这些元组会被舍弃。

4.1.3 OUTER JOIN（外连接）

左外连接（left outer join)

左外连接是在两表进行自然连接，只把左表要舍弃的保留在结果集中，右表对应的列上填null。

Select …… from 表1 left outer join 表2 on 表1.C=表2.C

结果：

A	B	C	D	E
1	2	3	4	5
5	6	7	null	null

右外连接(rignt outer join)

右外连接是在两表进行自然连接，只把右表要舍弃的保留在结果集中，左表对应的列上填null。

Select …… from 表1 rignt outer join 表2 on 表1.C=表2.C

结果：

A	B	C	D	E
1	2	3	4	5
null	null	8	9	1

全外连接(full join)

全外连接是在两表进行自然连接，只把左表和右表要舍弃的都保留在结果集中，相对应的列上填null。

Select …… from 表1 full join 表2 on 表1.C=表2.C

结果：

A	B	C	D	E
1	2	3	4	5
5	6	7	null	null
null	null	8	9	1

---

4.1.4 连接类型和条件

 select * from student join takes using (ID)
=select * from student inner join takes using (ID)

---

4.2 视图

4.2.1 视图定义

sql允许通过查询来定义一种虚拟关系。

with子句允许我们为子查询指定一个名称，以便经常根据需要来使用，但只能在一个特定的查询中使用。

4.2.2 SQL中查询使用视图

创建视图：

 CREATE VIEW VU_STUDENT AS SELECT * FROM STUDENT WHERE ID > 2;

也可以自定义视图列的名字

CREATE 
VIEW VU_STD (S_ID, S_NAME, S_GENDER, S_AGE, S_CLASS_ID, S_ADDRESS) 
AS 
SELECT * FROM STUDENT WHERE ID > 2;

4.2.3 物化视图

如果用于定义视图的实际关系发生改变，那么视图也跟着修改以保持最新。这样的视图被称为物化视图（materialized view)

4.2.4 视图更新

INSERT INTO STUDENT VALUES (14, '范冰冰', '01', 39, 5	, '上海浦东陆家嘴1124号');

如果需要对视图进行更新，那么需要满足以下的要求：

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

在以上限制，允许在下面的视图上执行update、insert和delete操作。

修改视图

ALTER VIEW VU_STUDENT AS SELECT * FROM STUDENT WHERE ID > 10; 

删除视图

DROP VIEW VU_STUDENT;

查看视图结构

DESC VU_STUDENT;

---

4.3 事务

事务是由查询和更新语句的序列组成。

• commit work 提交当前事务 **使事务执行的更新在数据库中成为永久性。**在事务被提交后，一个新的事务会自动开始。
• rollback work 回滚当前事务 会撤销事务中SQL语句执行的所有更新。因此，数据库状态被恢复到它执行到该事务的第一条语句之前的状态。

​ 在一个事务的执行期间检测到某种错误状态，事务回滚是有用的。在某种意义上，提交类似于对正在编辑的文档保存更改，而回滚类似于退出编辑会话而且不保存更改。

​ 一旦，一个事务执行了commit ，它的影响就不能用rollback 来撤销了。

​ 数据库系统发生语句错误、断电、系统崩溃这些故障下，如果一个事务还没有commit，那么其影响将被回滚。在断电和其他系统崩溃的情况下，回滚会在系统重启时执行。

事务的特性

1. 原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；例如转账的这两个关键操作（将张三的余额减少200元，将李四的余额增加200元）要么全部完成，要么全部失败。
2. 一致性： 确保从一个正确的状态转换到另外一个正确的状态，这就是一致性。例如转账业务中，将张三的余额减少200元，中间发生断电情况，李四的余额没有增加200元，这个就是不正确的状态，违反一致性。又比如表更新事务，一部分数据更新了，但一部分数据没有更新，这也是违反一致性的；
3. 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性：一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

---

4.4 完整性约束

完整性约束：保证授权用户对数据库所做的修改不会导致数据一致性的丢失。因此，完整性约束防止的是对数据的意外破坏。这与安全性约束不同，安全性约束防止未经授权的用户访问数据库。

​ 完整性约束通常被视为数据库模式设计的一部分，并作为用于创建关系的命令的一部分生命。也可以通过alter table table-name add constraint 命令将完整性约束施加到已有关系上，其中constraint可以是该关系上的任意约束。当这样一条命令被执行时，系统首先保证该关系满足指定的约束。如果满足，那么约束被施加到关系上；如果不满足，则上述命令被拒绝执行。

4.4.1 单个关系上的约束

create table命令允许的完整性约束包括

• not null
• unique
• check(<谓词>)

4.4.2 非空约束

非空（not null）

4.4.3 唯一性约束

unique(Aj1,Aj2,…,Ajm)

指出属性Aj1,Aj2,…Ajm形成了一个超码。也就是说，在关系中没有一个元组能在所有列出的属性上取值相同。然而声明了唯一性的属性允许null，除非他们被显示地声明为非空。

4.4.4 check子句

check（budget>0)保障budget的取值是非负的。

4.4.5 引用完整性

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