数据库Chap4 中级SQL
#sql 2023-11-24

目录：

连接表达式

join

一个连接操作是两个关系中的某些元组在符合某些条件下相匹配的笛卡尔积，同时还指定了连接结果中出现的属性有哪些

1
2
3
4
5
6
7
select *
from student join takes on student.ID = takes.ID;
与
select *
from student,takes
where student.ID = takes.ID
查询等价

select * from student natural join takes

外连接

外连接过程：先执行连接操作，然后将两个关系中不匹配的元组都加入到最后的结果关系中，并使用null 作为属性值补全，从而保留了在连接中丢失的元组

内连接（Inner Join）：我们此前学的不保留未匹配元组的连接运算，连接操作默认为内连接

  • 左外连接(left outer join)只保留出现在左外连接运算之前(左边)的关系中的元组
  • 右外连接(right outer join)只保留出现在右外连接运算之后(右边)的关系中的元组。
  • 全外连接(full outer join)保留出现在两个关系中的元组。
1
2
3
4
5
6
select ID
from student natural left outer join takes
where course_id is null;

select *
from takes natural right outer join student;

视图

视图 提供了向用户隐藏特定数据的机制

创建视图

1
create view <视图名>(<列名>,<列名>) as <SQL语句>;
  • 在执行CREATE VIEW语句时并不执行其中的子查询语句，只是把视图的定义存入数据字典。
  • 在建立完视图后，就可以像对表一样对视图进行查询了。
  • 只有在对视图进行查询时，才会按照视图的定义去查询数据。
1
2
3
create view faculty as
select ID,name,dept_name
from instructor;

  • "departments_total_salary" 是视图的名称。
  • "(dept_name, total_salary)" 告诉数据库系统，在创建名为 "departments_total_salary" 的视图时，这个视图将包含两列，分别命名为 "dept_name" 和 "total_salary"。

一个视图可能被用到定义另一个视图的表达式中

  • 如果视图v2 用于v1 的定义中，我们称 v1 直接依赖 v2
  • 如果v1 直接依赖v2 或者从v1 到 v2 有一条依赖路径，我们称v1依赖 v2
  • 如果一个视图v依赖其自身，我们称该视图v是递归的

视图展开

物化视图

物化视图：如果用于定义视图的实际关系改变，视图也会跟着修改。

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

物化视图，说白了，就是物理表，只不过这张表通过oracle的内部机制可以定期更新，将一些大的耗时的表连接用物化视图实现，会提高查询的效率。

1
CREATE MATERIALIZED VIEW <物化视图名> [<列名>,...,<列名>] AS <子查询>;

视图更新

  • 一般情况下，不允许对视图关系进行更新update（insert、delete）
  • 如果定义视图的查询语句对下列条件都能满足，我们称SQL视图是可更新的：
    • from子句只有一个关系；
    • select子句只包含关系的属性名，不包含任何表达式、聚集函数或distinct声明；
    • 任何没有出现在select子句中的属性内容允许取空值；
    • 查询中不包含group by或者having子句

可在视图定义加入with check option子句，用于拒绝不满足视图的where子句条件的元组更新、插入

1
2
3
4
5
create  view  history_instructors  as
select    *
from     instructor
where   dept_name= ’History’
with check option;

with check option：(检查视图更新SQL语句是否满足视图定义中的where子句)

1
2
3
4
5
6
7
8
9
10
11
update  history_instructors  set salary=80000
where ID= ’25566’;
# 转换为
update  instructors  set  salary=80000
where ID= ’25566’ and  dept_name= ’History’;
##################
insert into   history_instructors (ID, name, salary) 
            values (’69987’, ’White’,  80000);
# 转换为
insert into instructors (ID, name, salary, dept_name) 
            values (’69987’, ’White’, ‘80000’, ‘History’);

修改视图

1
ALTER VIEW <视图名> AS <子查询>;

删除视图

1
DROP VIEW <视图名> [CASCADE];

CASCADE：该视图和由该视图导出的视图都会被删除

事务

  • 事务transaction由查询和（或）更新语句的序列组成。
  • 当一条SQL语句被执行，就隐式的开始了一个事务
  • 以commit或rollback结束一个事务
    • commit [work]：提交当前事务，将该事务所做的更新在数据库中持久保存。事务被提交后，一个新的事务自动开始
    • rollback [work]：回滚当前事务，撤销该事务中所有SQL语句对数据库的更新

事务具有ACID特性

  • 原子性
    • 事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。
  • 一致性
    • 数据库要一直处于一致的状态，事务开始前是一个一致状态，事务结束后是另一个一致状态，事务将数据库从一个一致状态转移到另一个一致状态。
  • 隔离性
    • 并发的事务之间不会互相影响。换句话说，一个事务的影响在该事务提交前对其它事务是不可见的。
  • 持久性
    • 一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

完整性约束

防止的是对数据的意外破坏，它保证授权用户对数据库所做的修改不会破坏数据的一致性

在已有表中增加约束

1
alter table table-name add <constraint>

单个关系上的约束

not null 约束 和 unique 约束

check(P)

check (P)，使得关系中每个元组都必须满足谓词P

P 可以是包括子查询在内的任意谓词，但实现开销较大

例如: 确保semester是fall, winter, spring, summer中的一个：

1
2
3
4
5
6
7
8
9
10
11
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 (’Fall’, ’Winter’, ’Spring’, ’Summer’))
);

参照完整性

  1. ON DELETE CASCADE：当主表中的记录被删除时，相关联的从表中的行也会被自动删除。这意味着如果删除主表中的某个记录，则从表中具有相应外键的所有行也会被删除。
  2. ON UPDATE CASCADE：当主表中的记录被更新时，相关联的从表中的外键值也会被自动更新。这意味着如果在主表中更新了某个记录的主键值，从表中具有相应外键的行的外键值也会相应地更新。

事务中对完整性约束的违反

如何在不违反完整性约束的情况下插入一个元组?

  • 先将配偶信息设为null，在插入配偶元组后再更新该配偶信息（当配偶信息设为not null 时，该操作不可行）
  • 推迟完整性约束检查到事务结束时进行
    • 在约束声明后加 initially deferred;
    • 或者对约束条件加入语句set constraints <constraint-list> deferred;

复杂check条件与断言

断言就是一个谓词，它表达了我们希望数据库总能满足的一个条件；

属性域约束和参照完整性约束是断言的特殊形式

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

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

1
2
3
4
5
6
7
8
9
10
11
12
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’
                     )
         )
    );

SQL的数据类型与模式

SQL中的日期和时间类型

  • date: 日历日期，包括年（四位）、月和日
    • 举例: date ‘2014-01-01’
  • time: 一天中的时间，包括小时、分和秒
    • 举例: time ‘09:00:30’ time(2) ‘09:00:30.75’
  • timestamp: date 和 time的组合
    • 举例: timestamp(2) ‘2014-7-27 09:00:30.75’
  • interval: 一段时间
    • 举例: interval ‘1’ day
  • SQL允许对上述日期时间类型进行算术运算和比较运算
    • 一个date/time/timestamp的值减去另一个date/time/timestamp值，得到一个interval类型的值
    • interval类型的值可以加date/time/timestamp类型的值上

类型转换cast

使用cast(expression as type)表达式：表示将表达式e转换为类型t

例如：

1
2
3
select cast(ID as numeric(5)) as inst_id
from instructor
order by inst_id;

使用coalesce()函数解决输出空值的情况:

该函数接收任意数量的参数（所有参数必须是相同类型），并返回第一个非空参数

例如：

1
2
select ID, coalesce(salary, 0) as salary
from instructor;

默认值default

1
2
3
4
5
6
7
8
9
10
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),
    foreign key (dept_name) references department);

insert  into student(ID, name, dept_name)
	values (’3003’, ’Green’, ’Finance’);

索引

索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
  • 空间方面：索引需要占物理空间。

创建索引

在关系的属性上所创建的索引是一种数据结构（例如B+树），它允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组。

1
2
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名>[<次序>] , <列名>[<次序>],...,<列名>[<次序>]);

在ON子句中指定的一列或多列上建立索引，还可以指定某列索引值的排列次序

  • 表名、索引名：要建索引的表的名字、要建索引的名字
  • 次序：ASC（升序）或DESC（降序）
  • UNIQUE：此索引列的值必须唯一
  • CLUSTER：此索引是聚集/聚簇索引（有关索引的内容见第9章）

【例】：

1
2
3
4
5
create index studentID_index on student(ID);

select * 
from student
where ID = ‘12345’；

不用读取student关系中的所有元组，可以直接找有指定ID‘12345’的这条记录。

【例4.9】在学生表的Sno属性上建立聚集索引，命名为Sno_index，并说明Sno属性满足唯一约束，索引按照Sno的值递增排序。

1
CREATE UNIQUE CLUSTER INDEX Sno_index ON Student (Sno ASC);

【例4.10】在学生选课表的Sno和Cno属性上建立唯一索引

1
CREATE UNIQUE INDEX Sno_Cno_index ON SC (Sno,Cno);

修改、删除索引

索引重命名：

1
2
3
4
ALTER INDEX <OLD_NAME> RENAME TO <NEW_NAME>;

# 将学生表的Sno_index重命名为Sno_ind
ALTER INDEX Sno_index RENAME TO Sno_ind;

删除索引：

1
2
3
4
DROP INDEX<索引名>;

# 删除学生表的Sno_index索引
DROP INDEX Sno_index;

大对象类型

用户定义的类型

create type 构造于SQL中，创建用户自定义类型

1
create type Dollars as numeric (12,2) final；

Notes: final无实际意义 (p80)

1
2
3
4
create table department
 ( dept_name varchar (20),
  building varchar (15),
  budget  Dollars );

类似于C语言中的typedef

强制类型转换cast (department.budget as numeric(12,2) );

drop type Dollars 删除用户自定义类型

alter type Dollars修改用户自定义类型

域（！和属性域的概念不同）

  1. create domain 构造于SQL-92中，创建用户自定义域类型
1
create domain person_name char(20) not null；

用户自定义域和用户自定义类型有两大区别（p80）

  1. 域可以有约束或默认值：

    • 例如not null

    • 在域上应用check子句约束

1
2
3
create domain degree_level varchar(10)
  constraint  degree_level_test
  check(value in(‘Bachelors’, ’Masters ’, ’Doctorate’));
  1. 域不是强类型。基本类型相容的一个域类型的值可以被赋给另一个域类型

create table的扩展

创建与现有某个表模式相同的表

1
create table temp_instructor like instructor；

SQL:2003

1
2
3
4
5
create table tl as
    select  * 
    from instructor
    where dept_name = ‘Music’
    [with data];

SQL:2003标准：省略with data，创建表，但是不载入数据

但实际上，很多数据库还是默认载入数据

模式、目录与环境

授权

对数据库用户在某些数据上的权限形式：

  • Select – 允许读取，但不允许修改数据
  • Insert – 允许插入新数据，但不允许修改已存在的数据
  • Update – 允许修改数据，但不允许删除数据
  • Delete – 允许删除数据

修改数据库模式的权限类型：

  • Index – 允许创建和删除索引
  • Resources – 允许创建新的关系
  • Alteration – 允许添加或删除关系中的属性
  • Drop – 允许删除关系

最高权限：数据库管理员，类似于Linux root用户

权限的授予grant与收回revoke

1
2
3
4
grant <权限列表>
        on <关系名或视图名> 
        to <用户/角色列表>
        [with  grant  option];

<用户/角色列表> 是：

  • 用户ID
  • public, 当前和将来所有有效用户
  • 角色

对视图的授权并不代表对视图相关的实际关系的授权

权限授予人必须已经具有对指定项目的权限（或者是数据库管理员）

权限：

  • select: 允许读取关系，或者使用视图完成查询的权限
  • insert: 插入元组的权限，可指定属性列
  • update: 使用SQL update语句更新的权限，可指定属性列
  • delete: 删除元组的权限
  • all: 允许所有权限

权限的授予：

1
2
3
4
5
6
7
8
9
grant select on instructor  to  U1, U2, U3;

grant update(salary) on instructor to  U1, U2, U3;

grant all  on instructor  to  U1, U2, U3;

grant select on instructor, student  to  public;

grant insert (ID)  on instructor to  U4 with  grant  option;

with grant option允许用户 U4 将这个权限授予给其他用户（即使用 grant option）

权限的收回：

revoke语句用来收回权

1
2
3
revoke <权限列表>
on <关系名或视图名> from <用户/角色列表>
[ restrict | cascade ];

<权限列表> 可能是all，收回被收回用户所持有的所有权限

<用户列表> 是public, （除了那些隐含授权的用户）其他用户的权限将都被收回

  • CASCADE：支持级联收回，即由这些用户授予了以上权限的用户的这些权限也会被收回（默认选项）
  • RESTRICT：不支持级联收回

默认级联收回权限（cascade），restrict可用于避免一些不合适的权限级联收回

  • 如果你想要撤销一个用户对某个对象的权限，并且希望同时撤销该用户将这个权限授予其他用户的能力，可以使用级联收回权限。

示例:

1
2
3
revoke update （budget） on department from U1, U2, U3;

revoke grant option for select on department from U1, U2, U3;
  • 如果某些权限被不同的授权者授予同一个用户两次，那么在一次权限回收后该用户可能仍保有这个权限
  • 一个权限被回收后，基于这一权限的其他权限（如视图）也将被回收

image-20231115225128071

角色

权限可以被授予给角色:

1
grant select on takes to instructor;

角色可以被授予给用户，同时也可以被授予给其他角色

1
2
3
4
5
grant instructor to Amit;#角色授予给用户

create role teaching_assistant;# 创建角色

grant teaching_assistant to instructor;# 角色授予给角色

Instructor 具有teaching_assistant 的所有权限

角色链

1
2
3
4
5
create role dean;

grant instructor to dean;

grant dean to Satoshi;

SQL允许权限由一个角色授予（p86）

[granted by current_role]

视图的授权

大学地理系工作人员的视图授权示例

1
2
3
4
5
6
create view geo_instructor as
 ( select *
  from instructor
  where dept_name = ’Geology’);

grant select on geo_instructor to geo_staff ;

一个geo_staff 成员的查询操作可以写为：

1
2
select *
from geo_instructor ;

SQL提供了references权限，允许用户在创建关系时声明外码

1
2
grant references (dept_name) on department 
to Mariano;