中级SQL

1. 连接（join）

什么是连接？
将多个表中的元组经过特定方式结合在一起，以便进行特定的查询
为什么使用连接？
单纯的笛卡尔积连接会生成大量无意义的元组

1.1 自然连接

natural join：自动基于两个表中同名的属性来匹配元组

-- 找出学生的姓名以及他们选修课程的ID
select student.ID,takes.ID,name,course_id
from student,takes
where student.ID = takes.ID;
-- 等价于
select student.ID,takes.ID,name,course_id
from student natural join takes;

relation1 natural join relation2,relation3
不等价于
relation1 natural join relation2 natural join relation3
因为前者是与relation3的笛卡尔积，后者是与relation3的自然连接

join...using()：指定匹配的属性名，避免自动匹配所有同名属性，以提供更好的控制

-- 列出学生的姓名以及他们所选课程的名称
select name,title
from (student natural join takes) join course using (course_id);

1.2 内连接

inner join...on predicate：设置通用的谓词来明确连接条件，而不是局限于自然连接只能根据属性匹配

-- 自然连接
select student.ID,takes.ID,name,course_id
from student natural join takes;
-- 内连接
select student.ID,takes.ID,name,course_id
from student join takes on student.ID = take.ID;
-- 使用where子句
select student.ID,takes.ID,name,course_id
from student,takes
where student.ID = takes.ID;

join缺省情况下是内连接inner join，因此可以省略inner
内连接和使用where子句很相似，但一般将连接条件都放在on子句，而将过滤条件放在where子句，这样的SQL查询更容易读懂

1.3 外连接

自然连接和内连接的局限性：仅保留匹配的记录，而丢失其他元组，但有时候我们又希望保留这些元组

例如，id为70557的学生Snow没有选修任何课程，所以Snow的ID号不会出现在takes的ID列中，从而Snow并不会出现在自然连接或内连接的结果中

outer join：通过在结果中创建包含空值的元组，来保留那些在连接中会丢失的元组

• 左外连接（left outer join）：只保留出现在左外连接运算左边的关系中的元组
• 右外连接（right outer join）：只保留出现在右外连接运算右边的关系中的元组
• 全外连接（full outer join）：保留出现在两个关系中的元组

使用左外连接

使用右外连接

1.4 总结

实际上，连接可以分为连接类型和连接条件，任意的连接类型可以和任意的连接条件进行组合

自然连接natural join实际上是连接类型为inner join，连接条件为natural的自然内连接natural inner join！

2. 视图（view）

2.1 定义

视图是数据库中的一种虚拟表，表现为一个查询定义，不存储数据本身，而是在使用的时候动态生成，提供了一种抽象层，使用户能够以更简单的方式访问意义相同的表，不必重复编写复杂的SQL语句

view可以看做成是永久性的with语句，除非被显式删除

格式：create view <视图名> as <查询表达式>

-- 创造物理系在2017年秋季开设的所有课程信息视图
create view physics_fall_2017 as
  select course.course_id,sec_id,building,room_number
  from course,section
  where course.course_id = section.course_id
    and course.dept_name = 'Physics'
    and section.semester = 'Fall'
    and section.year = 2017;
-- 查看视图
select *
from physics_fall_2017;
-- 使用视图
select course_id
from physics_fall_2017;

物化视图（materialized view）：是一种特殊类型的视图，它在数据库中存储实际的数据，而不是简单地存储查询定义，物化视图会一直保持在数据的最新状态

2.2 视图更新

操作格式

-- 插入
insert into view(col1,col2) values
  (value1,value2);

-- 删除
delete from view
where condition;

-- 更新
update view
set col1 = newvalue
where condition;

视图更新的限制

• 简单视图：没有GROUP BY、DISTINCT、ODDERED BY和HAVING等
• 单一表：定义视图时不允许使用连接，子查询，和集合运算
• 没有计算列：没有计算列、表达式和聚集函数

视图更新的特殊情况

情况	结果
添加的元组属性数量少于视图对应关系的属性数量	拒绝插入或者设为null
添加的元组属性数量多于视图选择的属性	虽然被插入到关系中，但不会显示在视图中
插入的属性值可能违反外码约束	需要先在被引用关系中插入后才能执行

总而言之，慎用视图更新！

3. 事务（transaction）

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

原子性（atomic）：要么事务的所有影响都反映到数据库中，要么不产生任何影响

• commit work：提交当前事务，将事务执行的更新永久保存在数据库中
• rollback work：回滚当前事务，撤销事务中SQL语句执行的所有更改，回到执行第一条语句之前的状态

考虑A将钱通过银行转给B，转出的瞬间，A账户的钱减少了，但转钱过程中出现故障导致钱并没有到账，B账户的钱并没有增加。这种情况是致命的

4. 完整性约束（integrity constraint）

4.1 not null

非空约束：某些属性的值设为空是不合适的，主码不必显示声明为非空

-- 姓名和预算不允许为空
name varchar(20) not null
budget numeric(12,2) not null

4.2 unique

唯一性约束：指出属性集合构成一个超码，也就是说，在关系中没有两个元组在这些属性中的取值相同

-- 同一学年同一学期同一建筑同一房间同一时间不允许有两个课程
unique(year,semester,building,room_number,time_slot_id)

4.3 check

check(P)约束：指定一个谓词，关系中的每个元组都必须满足这个谓词

-- 学期必须是四个季节之一
check(semester in ('Fall','Winter','Spring','Summer'))

4.4 references

引用完整性约束：确保在一个表中引用另一个表的行时，引用的值在被引用的表中是有效的

-- 显式声明外码
foreign key (dept_name) references department(dept_name)
-- 在定义中声明
dept_name varchar(20) references department

级联（cascade）：允许定义在被引用表中数据变更时，外键约束的行为

foreign key (dept_name) references department(dept_name) 
  -- 删除被引用的元组时，自动删除所有引用该元组的元组
  on delete cascade
  -- 更新被引用的元组时，自动更新所有引用该元组的元组
  on update cascade

4.5 constraint

约束命名：明确约束的含义，当违反约束时可以根据名称来明确错误类型

-- 给check约束命名为minsalary
salary numeric(8,2), constraint minsalary check(salary > 20000)
-- 删除约束名称
alter table instructor drop constraint minsalary

4.6 assertion

断言约束：希望数据库总能满足一个条件

-- 用于检查所有学生的总学分是否与他们实际获得的学分一致
create assertion tot_credits_constraint check(\
  not exists(
    select ID
    from student
    where tot_cred <> (
      select coalesce(sum(credits), 0)
      from takes natural join course
      where student.ID = takes.ID and grade is not null
    )
  )
)

SQL不提供for all X, P(X)的结构，所以一般采用not exists X that not P(X)，即对全部X都有P==不存在X不满足P

5. 数据类型（Data Types）

5.1 日期和时间类型

date：存储年月日，YYYY-MM-DD

time：存储时分秒，HH:MM:SS

timestamp：存储时间戳，YYYY-MM-DD HH:MM:SS

extract(field from d)：获取单独的域，field值有year,month,day,hour,minute,second

current_date：返回当前日期

current_time：返回当前时间（带有时区）

local_time：返回本地时间（不带时区）

now：获取时间戳

-- 创建一个示例表
CREATE TABLE events (
  event_id INT PRIMARY KEY,
  event_date DATE,
  event_time TIME,
  event_timestamp TIMESTAMP
);

-- 插入示例数据
INSERT INTO events (event_id, event_date, event_time, event_timestamp) VALUES
(1, CURRENT_DATE, CURRENT_TIME, NOW()),
(2, '2024-09-23', '14:30:00', '2024-09-23 14:30:00');

-- 查询数据
SELECT EXTRACT(YEAR FROM event_timestamp) AS year,
FROM events;

5.2 类型转换

5.2.1 cast

cast(e as t)：将表达式e转换为类型t

-- 将字符串类型的ID转换为数值型并升序排序
select cast(ID as numeric(5)) as inst_id
from instructor
order by inst_id;

5.2.2 format

format(number, x) as formatted_number：将number保留x位小数
date_format(date, '%d-%m-%Y') as formatted_date：将date转换为“日月年”的格式

5.2.3 coalesce

可以设置在查询结果中输出空值的方式，接受多个相同类型的参数

-- 显示教师的ID和工资，但是将空工资显示为0
select ID,coalesce(salary,0) as salary
from instructor;

上例中不允许使用coalesce(salary,'N/A')，因为salary不是字符串类型的

5.2.4 decode

decode(value,match,replacement,default-replacement)：可以设置不同类型的转换输出

select ID, decode(salary, null, 'N/A', salary) as decode_salary 
from instructor;

5.3 缺省值

SQL允许为属性指定缺省值

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)
);

5.4 大对象类型

大对象（large object,lob）：字符数据（clob）和二进制数据（blob），如文本文件、图像、视频等

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

5.5 用户自定义类型

create type type_name as data_type：自定义一个数据类型

-- 创建美元数据类型并使用
create type Dollars as numeric(12,2);
create table department(
  dept_name varchar(20),
  building  varchar(15),
  budget    Dollars
);

create domain domain_name domain_definition：自定义一个域类型

create domain Dollars as numeric(12,2) not null;
create domain YearlySalary numeric(8,2) constraint salary.value_test check(value >= 29000.00);

5.6 自动生成唯一码值

我们希望给新元组赋值唯一ID的操作是由系统自动进行的，而不是人为去一个个设置

-- Oracle
ID number(5) generated always as identity
-- PostgreSQL
ID SERIAL
-- MySQL
ID number(5) AUTO_INCREMENT

5.7 复制表

create table like t：想要一个与现有表完全相同结构的空表，包括约束

create table as t：想要创建一个表并填充现有表的数据时，是否复制约束在不同数据库之间存在差异

6. 索引（index）

定义：是一个特殊的数据结构，通常包含指向表中实际数据行的指针，用于加速数据检索操作

意义：索引不是人为使用的，是系统自动使用的，如果某些属性经常被检索到，可以为其创建一个索引以帮助系统快速定位到所需的数据行

操作

• 创建索引：create index <索引名> on <关系名> (<属性列表>);
• 删除索引：drop index <索引名>;

索引对于正确性来说不是必需的，因为它们是冗余的数据结构，只是用于提升效率
索引构成数据库物理模式的一部分，而不是数据库逻辑模式的一部分

7. 授权（authorization）

7.1 授予权限

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

• (属性列表)是可选的
• all previleges表示授予全部权限
• public表示所有用户

-- 给数据库用户Amit授予了在department关系上的选择权限
grant select on department to Amit;
-- 给数据库用户Satoshi授予了在department关系上关于budget属性的更新权限
grant update (budget) on department to Satoshi;
-- 授予关于department的全部权限给全部人
grant all previleges on department to public;

7.2 角色权限

角色（role）：不必给每一个用户都使用相同命令来设置权限，而是先给角色授予权限，再给用户授予角色即可

-- 创建老师和教导主任角色
create role dean;
create role teacher;
-- 授予角色权限
grant update on teaches to dean;
grant select on teaches to teacher;
-- 将老师的权限授予教导主任
grant teacher to dean;
-- 将教导主任的权限授予用户
grant dean to dasi;
-- 授予用户权限
grant delete on department to dasi;

角色可以授予用户，也可以授予其他角色，谁说一个用户只能有一个角色？

7.3 视图权限

创建视图要求：如果当前用户希望创建一个视图，他们必须在视图引用的底层表上至少有选择权限

授权视图要求：当前用户在授权视图权限给其他用户之前，必须自己拥有相应的权限

-- 创建视图
create view geo_instructor as(
  select * 
  from instructor 
  where dept_name = 'Geology'
);
-- 给用户视图授权
grant select on geo_instructor to dasi;

7.4 引用权限

用户必须拥有某个表的引用权限才能在新表中定义对该表的外键约束

-- 允许dasi引用department关系的dept_name属性
grant references (dept_name) on department to dasi;

7.5 转移权限

with grant option：允许用户/角色将得到的权限授予给其他人，即不仅赋予它权限，还赋予它授权的权利

-- 授予Amit在department上的选择权限，并且允许Amit将该权限授予其他用户
grant select on department to Amit with grant option;

7.6 收回权限

与授予的形式相似，其中restrict是禁止级联收权，cascade表示进行级联收权（缺省）

考虑如下授权图，管理员决定收回U1的权限，那么U4和U5的权限也应该被收回，但是U5仍保留它从U2获得的权限

-- 非级联
revoke select on department from Amit restrict;
-- 级联
revoke select on department from dasi cascade;
-- 收回授权的权利
revoke grant option for select on department from Amit;

角色和用户的独立性：如果用户B的权限是通过用户A的角色授权的，那么收回用户A的权限，不会影响用户B的权限

-- 通过dean的角色给dasi授权
grant select on department to dasi granted by dean;

可以理解为，教导主任在任期内给一位老师提拔到了级长，当教导主任离职后，老师应该仍然保持为班主任而不是因为教导主任的离职又退回级长