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 的为候选码。
┆凉┆暖┆降┆等┆幸┆我┆我┆里┆将┆　┆可┆有┆谦┆戮┆那┆　┆大┆始┆　┆然┆
┆薄┆一┆临┆你┆的┆还┆没┆　┆来┆　┆是┆来┆逊┆没┆些┆　┆雁┆终┆　┆而┆
┆　┆暖┆　┆如┆地┆站┆有┆　┆也┆　┆我┆　┆的┆有┆精┆　┆也┆没┆　┆你┆
┆　┆这┆　┆试┆方┆在┆逃┆　┆会┆　┆在┆　┆清┆来┆准┆　┆没┆有┆　┆没┆
┆　┆生┆　┆探┆　┆最┆避┆　┆在┆　┆这┆　┆晨┆　┆的┆　┆有┆来┆　┆有┆
┆　┆之┆　┆般┆　┆不┆　┆　┆这┆　┆里┆　┆没┆　┆杀┆　┆来┆　┆　┆来┆