Chapter4 Intermediate SQL

4.1 Joins 连接

Inner Join 内连接:

内连接相当于笛卡尔积，与使用逗号连接两张表的效果相同，使用时可直接写成join。

on类似于where，用于约束，常与join联用。

Example

写法一：

select *
from student join takes on student_ID=takes_ID

写法二：

select *
from student,takes
where student_ID=takes_ID

写法一和写法二等价。

Natural Join 自然连接:

如果两张表有相同名字的属性，各自有元组对应该属性的值相同（所有共同属性的对应值都相同），则natural join会将这两个元组连接形成一个新的元组，并且相同的属性只保留一个（列）。

Example

查询学生与其对应上过的课的编号：

写法一：

select name,course_id
from students,takes
where student.ID=takes.ID

写法二：

select name,course_id
from student natural join takes

alt text

值得注意的是，natural join会自行寻找相同的属性合并，但相同的属性在两张表里的含义可能不同。

Example

查询学生与其对应上过的课的名称：

正确写法：

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

错误写法：

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

错误原因在于：student的dept_name属性与course的dept_name属性含义不同，但被连接。

为了避免这类情况，使用using可以指定合并的属性名。

Example

上例的错误写法可改为：

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

Outer Join 外连接:

outer join有三种类型：

left outer join：保留前一张表格的信息，剩余用null填充，符号为$r_1⟕r_2$
right outer join：保留后一张表格的信息，剩余用null填充，符号为$r_1⟖r_2$
full outer join：保留两张表格的信息，剩余用null填充，符号为$r_1⟗r_2$

outer join有四种用法：

什么都不加：退化为笛卡尔积或报错
与on联用：进行相应的null填充，但不合并相同列
与using联用：进行相应的null填充，且合并相同列
与natural联用：进行相应的null填充，且合并相同列

Example

假设有以下两张表： alt text 其中，BIO-301和CS-190在两张表中都有出现，CS-315只在course表中出现，CS-347只在prereq表中出现。

natural left outer join：
course表的内容全部保留，因此即使CS-315只在course表中出现，也会保留并用null填充其他属性。 alt text

natural right outer join：
prereq表的内容全部保留，因此即使CS-347只在prereq表中出现，也会保留并用null填充其他属性。 alt text

natural full outer join：
两张表的内容全部保留，因此即使CS-315和CS-347只在一张表中出现，也会保留并用null填充其他属性。 alt text

Example

course inner join prereq on course.course_id=prereq.course_id:

alt text

course left outer join prereq on course.course_id=prereq.course_id:

alt text

course natural right outer join prereq:

alt text

course full outer join prereq using (course_id):

alt text

4.2 Views 视图

View Definition 视图定义:

视图（view）是一种虚拟表，与数据库中原本就存在的真实表相对，其基于查询结果构建，本质上是一个重命名的查询表达式。

语法：

create view <name> as <query expression>

视图的使用与真实表格类似，其也能进行查询，定义时可以依赖其他视图，也可以依赖自己（递归）。

Example

建立instructor的视图，不包括工资属性：

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

在faculty视图上查询所有生物系的教授：

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

View Expansion 视图展开:

由于视图的本质是查询表达式，因此当需要解析的视图中包含其他视图时，会进行视图的展开，最终展开为不包含任何视图的查询表达式（递归除外）。

Materialized Views 物化视图:

物化视图（materialized view）与普通的视图不同，其对应的数据会真实地存储在硬件上，而不是表达式的抽象。因此，其解析时不需要展开，而是直接使用结果。

物化视图的优点：
- 频繁查询时速度更快
物化视图的缺点：
- 维护代价：一旦原始数据更新，物化视图也要进行对应的更新
- 存储代价

Update View 视图更新:

问题一：属性缺失

假设faculty视图是instructor表忽略salary属性的视图，现在更新faculty视图，插入一条新的数据，但这条数据没有salary属性，那么instructor表应该如何更新？

当遇到属性缺失的情况，通常有两种做法：

拒绝操作
空值填充

问题二：更新不唯一

Example

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

insert into instructor_info
    values('69987','White','Taylor');

出现的问题是，如果有多个院系位于Taylor楼中，那么插入的数据应该属于哪个院系？如果没有院系在Taylor楼中，那么插入的数据又该何去何从？

因此，大部分SQL只支持简单视图的更新，这些视图可能需要满足：

from子句后面只有一张表格
select子句后面的属性不含表达式、聚合函数、distinct关键字等
没有列在select子句后面的属性可以设为null
查询表达式中没有group by或having子句

4.3 Index 索引

索引（index）是用来提高查询性能的数据结构。在查询范围很大，但目标数据很小的情况下，可以为表格的某个属性建立索引（单维索引），加速查询。

语法：

create index <name> on <relation-name> (<attribute>)

Example

create table student
(
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3,0) default 0,
    primary key (ID)
);

create index studentID_index on student(ID);

select *
from student
where ID='12345';

通过索引能更快地找到ID为12345的学生。

4.4 Integrity Constraints 完整性约束

Unique Constraints:

unique确保表中的某一列或某组列的数据具有唯一性，即对应的数据不能重复。

语法：

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

从数据库理论的角度来看，这些属性组成的集合构成了超键。

Check Constraints:

check确保数据插入或更新时符合特定的规则，从而保持数据的一致性和完整性。

Example

确保semester属性为Spring, Summer, Fall, Winter之一：

create table section
(
    course_id varchar(8),
    sec_id varchar(8),
    semester varchar(6),
    year numeric(4,0),
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    primary key (course_id,sec_id,semester,year),
    check (semester in ('Spring','Summer','Fall','Winter'))
);

Integrity Constraint Violation During Transactions:

Example

create table person
(
    ID char(10),
    name varchar(40),
    mother char(10),
    father char(10),
    primary key (ID),
    foreign key (mother)    references person(ID),
    foreign key (father) references person(ID)
);

在这个例子中，外键是自己参照自己，为了不违反完整性约束，有以下几种方法：

在插入一个人的数据之前先插入其父母的数据，即考虑顺序
插入数据时父母属性默认为空，所有的数据插入完后再更新父母属性
延迟检查

Assertions 断言:

断言（assertion）确保数据库中的数据满足特定的条件。

语法：

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

Triggers 触发器:

触发器（trigger）是一种自动执行的语句，遵循ECA原则（Event事件、Condition条件、Action动作）。

触发的事件可以是insert，delete或update
触发的时间可以是before或after
触发的数据可以是old row或new row

Example

将空白的grade改为null：

create trigger setnull_trigger before update of takes on (grade)
referencing new row as nrow
for each row
    when (nrow.grade='')
    begin atomic
        set nrow.grade=null;
    end;

Example

更新学生的总学分：

create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade<>'F' and nrow.grade is not null and (orow.grade='F' or orow.grade is null)
begin atomic
    update student
    set tot_cred=tot_cred+(select credits
                           from course
                           where course.course_id=nrow.course_id)
    where student.id=nrow.id;
end;

4.5 Data Types 数据类型

Built-in Data Types 内置数据类型:

date: date '2005-7-27'
time: time 09:00:30
timestamp: timestamp '2005-7-27 09:00:30.75'
interval: interval '1' day

Large-Object Types 大对象类型:

图像、视频、CAD文件等大型数据对应特殊的数据类型：

blob: binary large object
clob: character large object

查询large object时使用的是pointer（指针、定位器）。

User-Defined Types 用户自定义类型:

使用create type创建自定义数据类型。

Example

create type Dollars as numeric (12,2) final; # final表示这个类型是最终类型，不能被进一步继承或修改

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

Domains 域:

使用create domain创建域（domain）。

Example

create domain person_name char(20) not null;
create domain degree_level varchar(10) 
    constraint degree_level_test    # 设置约束
        check (value in ('Bachelors','Masters','Doctorate'));

Note

类型和域类似，但域会有更多的约束条件，如非空等。

4.6 Authorization 授权

SQL在以下几个方面可以设置权限：

read
insert
update
delete
reference
all：所有权限

每一种类型的授权称为一个权限（privilege）。

Grant Statement:

grant用于授权，语法如下：

grant <privilege list> on <relation or view> to <user list>

Example

grant select on department to Amit,Satoshi;

授予视图的权限并不意味着授予视图底下表格的权限。

授权者自己要有对应的权限（或者是database administrator）。

Revoke Statement:

revoke用于撤回授权，语法如下：

revoke <privilege list> on <relation or view> from <user list>

Example

revoke select on student from U1,U2,U3;

对于相同权限，如果A和B都将该权限授予给C，那么A撤回权限后，C的权限可能仍然保留，需要考虑级联。

若A授予B权限，B授予C相同权限，那么当A撤回权限时，C的权限也会被撤回。

Roles 角色:

role是对user的分组，语法如下：

create role <name>

设置user为指定的role：

grant <role> to <users>

授予指定的role权限：

grant <privilege> on <relation> to <role>

role也可以继承：

grant <role1> to <role2>

Transfer of Privileges:

grant <privilege> on <relation> to <role/user> with grant option:
允许user再次给其他人授权
revoke <privilege> on <relation> from <role/user> cascade:
级联撤回，之前再次给出去的授权也撤回
revoke <privilege> on <relation> from <role/user> restrict:
拒绝操作

Chapter4 Intermediate SQL

4.1 Joins 连接

4.2 Views 视图

4.3 Index 索引

4.4 Integrity Constraints 完整性约束

4.5 Data Types 数据类型

4.6 Authorization 授权

评论