数据库系统概念学习笔记

SQL 语句

/* 创建表 */
create table course
  course_id varchar(20),
  title varchar(20) not null,
  price numeric(5,2) default 100.00,
  primary key(course_id));

/* 删除表 */
drop table course;

/* 增加列 */
alter table course add credits numeric(2,0);

/* 删除列 */
alter table course drop credits;

/* 创建视图 */
create view v as <query expression> 

/* 插入数据 */
insert into course
  values('CS-437', 'Database Systems', 'Comp.Sci.', 4);

/* 删除数据 */
delete from course
  where course_id = 'CS-445';

/* 更新数据 */
update instructor
  set salary = salary * 1.05
  where salary < 70000;

/* 查询数据 */
select distinct dept_name
  from instructor
  where salary > 80000 and dept_name <> 'Biology';

/* 用 as  给属性别名(as 可以省略)*/
select name as instructor_name from instructor;

/* 用 as  给关系别名(as 可以省略)*/
select * from instructor as T,  instructor as S
	where T.sarary > S.sarary and S.dept_name = 'Biology';
    
 /* 创建索引 */
create index sid on student(ID);

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

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

/* 创建角色 */
create role instructor;

字符串

• %：匹配任意字符串
• _：匹配一个字符
• ||：串联
• trim()：去掉后面的空格
• upper()、lower()：转大小写
• 查找相似：

select * from department 
where buidling like '%cat\_room%' escape '\';

排序

select * from instructor
order by salary desc, name asc; /* asc 可以省略 */

where 中的谓词

select * from instructor
where salary between 9000 and 10000; 
/* 等价于 salary >= 9000 and salary <= 10000;
   也有 not between */

集合

union intersect except

(select course_id from section where year = 2009)
union
(select course_id from section where semester = 'Spring')
去并集，会自动去重（union all 不去重）

空值

涉及空值的比较，返回 unknown

判断空值用 is null，不能用 = null

聚集函数 Aggregate Functions

• min、max、sum、avg、count
• count(distinct ID)
• group by 分组聚集
• having 分组的限定条件
• in 集合成员资格, not in
• > some 至少比某个大，< all, >=, <=, <>
• exists 空关系测试
• not exists(B except A) “关系 A 包含关系 B”
• unique 重复元组存在性测试
• with 定义临时关系

Join

• Cross Join：即笛卡尔积
• Inner Join：第一个关系的每一个元组必须在第二个关系里有对应项
• Natural Join：\(R\Join S\)
• Theta/Equi Join ：\((R\Join_\theta S)\) 元组需要满足的断言
• Semi Join: 只返回第一个关系的记录
• Outer Join：保留了对应为空的元组

select * from a join b on a.id=b.id;
select * from a natural left outer join b where course_id is null;

• left outer join 左外连接
• right outer join 右外连接
• full outer join 全外连接
• join... using (A1,A2) 只需要在某些属性上相同的连接
• join... on .. 类似 where 约束的连接

码/键 Key

create table section
(
  course_id varchar(8),
  sec_id varchar(8),
  
/* check 约束，not null 约束*/
  semester varchar(6), check (semester in('Fall','Spring')),
  year numeric(4,0), check (year>1759 and year < 2100),
  room_number varchar(7) not null,
  primary key (course_id, sec_id, semester, year),
  foreign key (course_id) references course,
)

• 超码（superkey）：一组能唯一标识一个元组的属性集合
• 候选码（candidate key）：最小超码
• 主码（primary key）：用来在一个关系中区分不同元组的候选码
• 外码（foreign key）：一个关系的属性中包括另一个关系的主码，这个属性就是参照另一个关系的外码。

候选码用 unique 或者 primary key 约束。

参照完整性

create table course(
  ...
  foreign key(dept_name) references department
    on delete cascade
    on update cascade,
  );

cascade代表级联，当删除 department 元组时，course 的对应元组也会被级联删除。 类似的还有 set null、set default。

数据类型

• varchar(n)
• char(n)
• int
• numeric(p,d)
• real
• double precision
• float(n) 时间相关
• date
• time
• timestamp
• timestamp with timezone
• interval
• current_date() 当前日期
• currrent_time()
• current_timestamp() 带时区
• localtime() 本地时间
• extract(filed from d) 从 date 或 time 类型的 d 中提取year,month,day,hour,minute,second的任意一种

关系代数运算

• \(\sigma\) Select 选择
• \(\Pi\) Projection 投影
• \(\rho\) Rename 更名
• \(\gets\) Assignment 赋值
• \(\cup\) Union 并集
• \(\cap\) Intersection 交集
• \(-\) Difference 集合差
• \(\times\) (Cartesian) Product 笛卡尔积
• \(\Join\) (Natural) Join 自然连接
• \(\mathcal{G}\) Aggregate 聚集

select A1, A2, sum(A3)
from r1, r2, ..., rm
where P
group by A1, A2

等价于 \(_{A_1,A_2}\mathcal{G}_{sum(A_3)}(\Pi_{A_1,A_2,\cdots,A_n}(\sigma_P(r_1\times r_2\times \cdots \times r_m)))\)

函数依赖

• 关系实例满足函数依赖 ：\(\alpha \rightarrow \beta\)若\(t_1[\alpha]=t_2[\alpha]\),则\(t_1[\beta]=t_2[\beta]\)
• 所有合法的实例都满足，则该函数依赖在模式 r 上成立。
• 若\(\beta\subseteq\alpha\) ，则依赖\(\alpha \rightarrow \beta\)是平凡的。
• Armstrong 公理：
  • 自反律：平凡依赖
  • 增补律：若\(\alpha \rightarrow \beta\)，\(\gamma\)是属性集，则 \(\gamma\alpha \rightarrow \gamma\beta\)。
  • 传递律：若\(\alpha \rightarrow \beta,\beta \rightarrow \gamma\)，则\(\alpha \rightarrow \gamma\)
  • \(ID,dept\_name \rightarrow name,salary,building\)
• \(F^+\) 表示函数依赖集合F的闭包
• Boyce-Codd范式 BCNF：关系 R 是 BCNF 当它的函数依赖集 F 满足：\(F^+\) 中的所有非平凡依赖的都是 R 的超码。

可以将关系分解为 BCNF 模式集合。

• 第三范式 3NF：BCNF 的条件 || \(\beta-\alpha\)中的每个属性包含于 R 的一个候选码中。
• F 的正则覆盖\(F_c\)满足：所有函数依赖不含无关属性 且 \(\alpha\)都是唯一的。
• \(R_1 \cap R_2\) 是 R 上的超码，则是无损分解
• 保持依赖：分解后总的函数依赖集与原函数依赖集保持一致
• BCNF 分解：每次找出一个不满足 BCNF 的关系r，将 r 分解为\(r-\beta\) 和\(\{\alpha,\beta\}\)两个关系。
• 3NF 分解：\(F_c\)的每个函数依赖，构成关系\(\{\alpha,\beta\}\), 若所有关系都不包含 R 的候选码，任意候选码构成一个关系，删除冗余。
• 求候选码：
  1. 只在右边的一定不属于候选码，
  2. 只在左边的一定包含于候选码，
  3. 不在函数依赖集中出现的一定包含于候选码，
  4. 其它属性与2，3的属性的组合中（必须包含2，3的属性），闭包等于全集 U 的为候选码。