【数据库】SQL

更新于 分类于 阅读次数： 本文字数： 3.8k 阅读时长 ≈ 3 分钟

学习数据库系统概念（第六版）笔记，第三、四章SQL

SQL不区分大小写，字符串中大小写保留

数据定义

关系模式、属性取值类型、完整性约束

数据类型

基本类型

  • char(n)，不足可能会添空格导致判断等于时有问题
  • varchar(n)，nn 是最大值
  • int
  • smallint
  • numeric(p,d)，pp 个数字，小数点后dd 位
  • real, double
  • float(n)，精度nn 位

日期

  • date，年（四位）月日，'2023-02-16'
  • time，时分秒，22:03:04，可以time(p)指定秒的位数，可以time with timezone加时区信息
  • timestamp，date time，'2023-02-16 22:03:04.45'位数和时区信息同time
  • interval ，加减时间间隔，DATE '2020-07-20' + interval '1 31' hour_second结果是2020-07-20 00:01:31
  • cast e as t将字符串e转换成类型t（以上三种类型）
  • extract (year/month/day/hour/minute/second/timezone_hour/timezone_minute from e)

大对象

  • clob blob（后者是二进制）

用户定义类型

  • 独特类型、结构化数据类型（不学）
1
create type AA as numeric(12, 2) final

域

与用户定义类型类似，但是可以加约束，还可以加constraint

1
create domain AAA char(20) not null

索引

数据结构，不用扫描关系中的所有元组就能找到给定值对应的元组

1
create index xxx on R(A1)

基本模式定义

创建

1
2
3
4
5
6
7
8
9
10
11
create table R1(
    A1, D1 not null, 
    A2, D2 check (A2 > 0),
    A3, D3 default 0
    ...
    primary key (Ai1, Ai2,...),
    foreign key (Aj1, Aj2,...) references R2
    	on delete cascade
    	on update set null / default,
    unique (Ak1, Ak2,...),
	check (Al in ('A','B',...)));
  • 参照完整性(外码)约束：在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现
    • on delete/update cascade 级联删除/更新，即R2中删掉/更新后R1中的同步删除/更新元组；如果是set null/default，约束违反时置null或默认值
    • references后可以是显式的子句，但必须是候选码（主码或unique）
  • 完整性约束：保证用户对数据库所做的修改不会破坏数据一致性，单表约束：
    • not null：禁止插入空值
    • check：可写最后也可写里面，可以是谓词/子查询（实际中基本没有支持子查询的）
    • unique：形成候选码
    • 主码

去除

1
drop table R1;

增删属性

（不是都支持）

1
2
alter table R1 add A1 D1;
alter table drop A1;

查询

理解来说顺序是from、select、where

1
2
3
4
select distinct A1 as A11, A2*1.5, R3.*
from R1 as R11, R2, ...
where E1 and E2 ...
order by A1 desc
  • select设置属性
    • distinct去重（不加不去重）
    • 属性可使用+ - * /
    • *是全部
  • where后谓词语句
    • 可用and or not连接
    • 可用< <= > >= = <>
    • 可用A1 between 100 and 1000 ，也有not between
    • 字符串用like模式匹配，%匹配任意子串，_匹配任意字符，字符串中的%_\前使用\并在字符串后加escape \来转义
    • 字符串还可用upper(s)、lower(s)转大小写
    • 可用元组比较(A1, A2, ...) = (666, 'abc', ...) ，如果是不等号元组各项用且连接
    • 可使用A1 (not) in ('666', '111') 表示集合关系，这个集合可以是子查询的结果，属性也可以是元组形式(A1, A2, ...)
  • from后
    • R1,R2,...是笛卡尔积，使用R1.ID R2.ID区分不同关系的同名属性
    • R1 natural join R2 是自然连接，同名属性只出现一次，小心同名不同义
    • R1 join R2 using (Ai, Aj,...)是指定列的“自然连接”，忽略没有指定的同名属性
    • R1 join R2 on R1.Ai=R2.Ai是指定列同值的“连接”，同名属性重复出现（效果与笛卡尔积+where一样）
    • (natural) left/right/full outer join是左/右/全外（自然）连接：保留失配元组，出现在左/右/全部关系中的元组会被全部保留，另一个关系没有对应元组的会在结果中被填入null信息
  • order by设置显示次序
    • 默认升序，降序是desc
    • 可以多个值排序
  • as可以重命名

子查询

  • 至少比某一个大、比所有都大这类表述：> some (...)、> all (...)连接子查询
  • 相关子查询（correlated subquery）：子查询中可以使用外层查询的相关名字
  • 标量子查询（scalar subquery）：子查询结果是单个属性的单个元组，可以出现在select、where、having中
  • exists(...)表示子查询是否非空，将R1⊆R2R1\subseteq R2 写成not exists (R1 except R2)
  • unique(...)表示子查询是否有重复元组
  • with Rtemp(value) as (...)可以创建临时关系Rtemp

集合运算

  • 用在查询结果间

  • 并：union自动去重，保留重复用union all

  • 交：intersect自动去重，保留重复用intersect all

  • 差：except自动去重，保留重复用except all

聚集函数

  • 平均值：select avg(A1)
  • 最值：select min(A1),max(A1)
  • 求和：select sum(A1)
  • 计数：count(A1)，count(*)计算元组个数但不能去重
  • 可在属性前加distinct对聚集属性去重
  • 除了count(*)，都忽略null值
1
2
3
4
5
select A1,avg(A2)
from R1,...
where ...
group by A1
having ...;
  • 理解执行顺序：from、where、group by、having、select
  • group by对前面得到的元组按属性分组，select 的属性必须出现在order by后
  • 如果需要根据分组的某些属性要求筛选分组则用having子句，该子句可用聚集函数

修改

插入

1
2
3
insert into R1 values (1, 'ABC', ...);
insert into R1 (A2, A1, ...) values (1, 'ABC', ...);
insert into R1 子查询

用子查询要注意会不会死循环

删除

1
2
delete from R1;
delete from R1 where ...;

删除元组而不是删除表，where跟查询的where一样

更新

1
2
3
update R1
set A1=...
where ...;

where跟查询的where一样，set可用标量子查询赋值，还可用case：

1
2
3
4
5
case 
	when E1 then A1=...
	when E2 then A2=...
	else A2=...
end

注意update的顺序

视图

  • 虚关系，即概念上包含查询结果，不是逻辑关系的一部分，但对用户所见
  • 用户不知道数据库原貌
1
2
create view v as
	SQL语句
  • SQL语句可以嵌套其他视图，在实际执行时不断找到语句中的视图并展开
    • depend directly on 某视图
    • depend on间接
    • recursive递归
  • vv 可当作普通关系被SQL语句使用，虚关系、虚表
  • vv 也可加括号写全属性
  • 物化视图：允许物理存储视图关系，如果使用到的关系发生了变化，物化视图结果就过期了，需要维护，好处是查询已保存的结果更快（尤其对于那些大型数据库）
  • 视图更新：一般不允许通过修改视图修改数据库（不在视图里的约束值、多关系、修改值不在视图里等），可更新（update 、insert、delete）的条件如下：
    • from只有一个关系
    • select只有属性名，不是表达式、聚集或distinct，且没有被select的其他属性没有约束（主码、null）
    • 没有group by或having

授权

  • 给用户授权读取、插入、更新、删除数据

授予

1
2
3
grant 权限列表
on 关系名/视图名
to 用户/角色列表
  • 权限可以是select insert update delete，其中update/insert可以带括号指明属性列表
  • 外码权限references(A1)
  • 视图创建者要有对应权限，使用者不需要
  • 用户可以是public，指系统的所有用户和未来的所有用户
  • 加with grant option这个角色可以将在这个关系上获得的权限授予其他角色

收回

1
2
3
revoke 权限列表
on 关系名/视图名
from 用户/角色列表
  • 默认级联收回，防止级联收回用户后加restrict
  • 角色授予权限的收回，权限叫grant option

角色

  • 有角色集
1
create role r1
  • 用户被分配角色，user1的角色是r1，r2继承r1的所有权限
1
2
grant r1 to user1
grant r1 to r2