数据库系统概念4

中级SQL

其他连接谓词

除了自然连接外，SQL还提供了连接运算的其他形式，能够包含被自然连接排除在外的元组。

连接条件on

select *
from student join takes 
on student.ID = takes.ID;
#等价于
select *
from student,takes
where student.ID = takes.ID;

看上去on条件有些冗余，到那时在进行外连接的时候可以表示的与where不同。

外连接

• 左外连接：只保留该运算符之前（左边）的关系中的元组
• 右外连接：只保留该运算符之后（右边）的关系中的元组
• 全外连接：保留出现在两个关系中的元组

#左外连接
select *
from student left join takes;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. |      102 | CS-101    | 1      | Fall     | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. |       32 | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. |       32 | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. |       32 | CS-315    | 1      | Spring   | 2010 | A     |
| 19991 | Brandt   | History    |       80 | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    |      110 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    |       56 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    |       46 | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    |       46 | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    |       46 | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. |       54 | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. |       54 | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      |       38 | MU-199    | 1      | Spring   | 2010 | A-    |
| 70557 | Snow     | Physics    |        0 | NULL      | NULL   | NULL     | NULL | NULL  |
| 76543 | Brown    | Comp. Sci. |       58 | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. |       58 | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. |       60 | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. |       98 | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. |       98 | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    |      120 | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    |      120 | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
21 rows in set (0.00 sec)

可以看到，虽然physics没有加入到takes当中，但是左连接之后也被保留

视图

不能让所有用户都看到整个逻辑模型。出于安全考虑，需要对用户隐藏特定数据。

视图(view)：不是逻辑模型的一部分，但作为虚关系对用户可见的关系

create view v as <query expression>;

简单来说，v通过数据字典存储了后面的表达式。其本质是，在调用v的时候执行后面的语句。

视图使用方法

1.创建faculty(院、系)视图（隐藏老师的工资）：

create view faculty as
select ID,name,dept_name
from instructor;

2.找出所有生物系老师的名字：

select name from faculty where dept_name = 'Biology';

视图属性名指定

create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum (salary)
from instructor
group by dept_name;

使用视图定义视图

这个不说啦~很好理解

视图的更新

一个关系的视图

比如说，假设执行以下的语句：

insert into faculty values (’30765’, ’Green’, ’Music’);

我们不禁想问，这条语句能执行成功吗？如果成功会插入到哪里？

事实上，该插入操作会插入到关系instructor中。但是这里没有给出薪资的值呀~

解决办法有两种：

• 拒绝插入，向用户返回一个错误信息
• 向instructor关系插入元组(’30765’, ’Green’, ’Music’,null)

两个关系的视图

如果是关于两个表的视图呢？又该如何插入？

create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name= department.dept_name;

这种情况下，系统就不知道该如何插入数据了，因为dept_name是一个主键，不能默认为null。

定义

可更新的SQL视图：

• from子句中只有一个数据库关系
• select子句中只包含关系的属性名，不包含任何表达式、聚集或distinct声明
• 任何没有出现在select子句中的属性可以取空值；即没有not null约束，也不构成主码一部分
• 查询中不包含group by或having子句

这样的视图允许update、insert、delete

课上一个有趣的问题

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

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

image-20210918104943894

物化视图

物化视图(materialized view)：创建一个物理表，其中包含定义视图的查询结果中的所有元组

物化视图维护/视图维护：保持物化视图一直在最新状态的过程。

为什么需要将视图实例化呢？当然是因为创建一个表之后，查询效率会更高。但是为了保证视图处于最新状态，需要进行维护。

事务

事务(transcation)：由查询和更新语句的序列组成。

• Commit work：提交当前事务
• Rollback work：回滚当前事务

完整性约束

完整性约束(Integrity Constraints)：保证授权用户对数据库所做的修改不会破坏数据的一致性。完整性约束防止的是对数据的意外破坏。

• 教师的名字不能为null
• 任意两位教师不能有相同的标识
• course关系中每个系名必须对应department关系中有一个对应的系名
• 一个系的预算必须大于0.00美元

单个关系上的约束

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

参照完整性

参照完整性(referential integrity)即保证一个关系中给定属性集上的取值，也在另一个关系的特定属性集的取值中出现。

foreign key中的约束：

• on delete cascade：级联删除
• on update cascade：级联更新
• set null：
• set default：

一个复杂的问题：

复杂check条件

……

断言

……

SQL的数据类型

时间

与日期时间的数据类型：

• date：日历日期
• time：小时、分合秒
• timestamp：date合time的组合
• interval：允许在日期、时间、时间间隔上进行计算

索引

用户定义的类型

• 独特类型(distinct type)
• 结构化数据类型(structured data type)

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

大对象类型

• blob：二进制数据的大对象数据类型
• clob：字符数据的大对象数据类型

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

查询大对象的时候，返回的是大对象的指针，而不是其本身。之后使用定位器来一点点取出该大对象，类似于read函数。

授权

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

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

select

授予select权限：

grant select on department to Amit,Satoshi;

授予更新某个属性(可选项)的权限：

grant update(budget) on department to Amit,Satoshi;

授予所有权限：

grant all privileges on department to Amit,Satoshi;

revoke

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

如果一个权限重复授予给某人两次，那么可能需要两次revoke才可以收回。

roles

是一个组的概念。有点类似于UNIX编程中的群组(group)。

create role instructor;    #创建角色
grant select on takes to instructor; #角色授权

grant dean to Amit;
create role dean;
grant instructor to dean;
grant dean to Satoshi;    #角色可以授予给用户，也可以授予给其他角色

角色链

teaching_assistance ——> instructor
instructor ——> dean

这样角色dean就继承了所有被授予给角色instructor和teaching_assistance的权限，还包括直接赋给dean的权限。