关系模型

关系模型由：关系数据结构、关系操作集合和关系完整性约束三部分组成。

关系模式

对关系的描述，R（D,U,Dom,F）,R 为关系名，U为属性集合，D为属性组U中属性的域，Dom为属性向域的映像集合，F为属性间数据的依赖关系的集合。相当于关系数据库的型。

关系

关系模式在某一时刻的状态或内容，相当于关系数据库的值。

关系数据库

有值和型之分，型也称关系数据库模式，是对关系数据库的描述，包括若干域的定义和这些域上定义的若干关系模式。值为这些关系模式在某一时刻对应的关系的集合，通常成称为关系数据库。

关系模式	关系
对关系的描述	关系模式在某一时刻的状态或内容
静态的、稳定的	动态的、随时间不断变化的

关系代数

传统关系代数：并、差、交、笛卡尔积
专门的关系运算符：选择、投影、连接、除
其中 并、差、笛卡尔积、选择、投影为五种基本关系代数运算，其他三种：交、连接、除可以由基本运算来表达，不增加语言能力，但能简化表达。

并（union）

关系R并关系S得关系T，T中的元组属于R或S

差（except）

关系R与关系S的差，结果为关系T，T中的元组属于R并且不属于S

交（intersection）

关系R与关系S的交集，结果为关系T，T中的元组同时属于R和S

笛卡尔积（cartesian product）

此处指广义笛卡尔积，因为此处元素是元组。
设R有n列k1个元组，S有m列k2个元组，RxS=T。则T中有（n+m）列，（k1*k2）个元组，且元组的前n列为R的元组，后m列为S的元组。

选择（selection）

又称为限制（restriction），选择行,关系R在选择条件B下进行选择操作得到关系T，则T中的元组均满足条件B，R个属性个数不变。

投影（projection）

选择列，关系R中选取若干属性组成新的关系，并去除重复元组。

连接（join）

关系R和关系S，在基于条件B的情况下进行连接操作得到关系T。等价于关系（RxS）在条件B下进行选择操作。

• 连接条件的比较运算符为=称为等值连接，它是从（RxS）中选择出指定属性值相同的元组。
• 自然连接是一种特殊的等值连接，其比较分量必须是同名属性组，并且结果将重复的属性列去掉。
• 自然连接过程中，因为比较分量不一致的元组被舍弃，该元组称为悬浮元组（dangling tuple）

除运算（division）

设关系R除以关系S的结果为关系T，则T包含所有在R中且不在S中的属性和值，且T的元组与S的元组的所有组合属于R

象集（images set）

设关系R（X，Y），s是X中的一个值，则s在R中的象集定义为：R中属性组X上，值为s的所有元组在Y分量上的集合。

用象集定义除运算

设关系 R（X，Y）和 S（Y，Z），其中R的Y属性和S的Y属性可以有不同属性名，但是必须出自相同域集。
R除以S得到关系P，P是R中满足以下条件的元组在X属性上的投影：元组在X上的分量值x的象集Yx包含S在Y上投影的集合。

• 例1:是查询至少选择1号和3号课程的学生号码Sno。

  首先建立一个临时关系K，K中一个属性（Cno），两个元组（1，3）
  则问题的答案为：从关系SC中对Sno和Cno投影，然后除以关系K
  原理：SC在Sno和Cno的投影，然后逐一求出各个Sno的象集，并依次检查这些象集是否包含K。

• 例2
  R

A	B	C
a1	b1	c2
a2	b3	c7
a3	b4	c6
a1	b2	c3
a4	b6	c6
a2	b2	c3
a1	b2	c1

S

B	C	D
b1	c2	d1
b2	c1	d1
b2	c3	d2

R除以S

A
a1

• A表中a1的象集为{（b1,c2），（b2，c3），（b2，c1）}
• S表在B、C分量投影的集合为{（b1,c2），（b2，c3），（b2，c1）}
• a1的象集包含了S表在B、C分量投影的集合，因此a1满足条件。a2、a3、a4象集不满足条件，因此结果只有a1

关系演算

关系演算参考博客

查询语言

SQL（DDL、DML）

SQL特点:

1. 综合统一

   SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。

2. 高度非过程化

   只要提出”做什么”，无需指定”怎么做”，存储路径的选择和SQL的操作过程由系统自动完成，提高数据独立性，减轻用户负担。

3. 面向集合的操作方式

   操作对象、查找结果、插入、删除、更新的对象都可以是元组的集合。

4. 以同一种语法结构提供多种使用方式

   支持嵌入其他高级语言

5. 语言简洁、易学易用

   接近英语口语，语言简洁。

DDL（(Data Definition Language）

数据定义语言是用来定义数据库外模式、模式、内模式的语言

模式

• 创建
  create schema <模式名> authorization <用户名>[<表定义字句>|<视图定义字句>|<授权定义字句>]
• 删除
  drop schema <模式名> <cascade|restrict>
  cascade: 级联，删除模式的同时将该模式中的所有数据库对象全部删除
  restrict：限制，没有任何下属的对象时才能执行

表

• 创建
  create table <表名>
  (<列名> <数据类型> [<列级完整性约束条件>],
  <列名> <数据类型> [<列级完整性约束条件>],
  …
  [<表级完整性约束条件>]
  )

数据类型

数据类型	含义
char(n)	定长字符串
varchar(n)	变长字符串
int	整数（4B）
smallint	短整数（2B）
bigint	大整数（8B）
clob	字符串大对象
blob	二进制大对象
numeric(p,d)	定点数，p位数，小数点后又d位数
real	取决用户机器精度的单精度浮点数
double recision	取决于机器精度的双精度浮点数
float(n)	可选精度浮点数
date	日期，YYYY-MM-DD
time	某一日的时间 HH:MM:SS
timestamp	时间戳
interval	时间间隔

• 创建模式后创建表
  create table “模式A”.student (…)

• 创建模式时创建表
  create schema “模式A” authorization user1 crate table student(…)

• 设置所属模式，创建表自动分配
  show search_path;// 查看搜索路径
  set search_path to “模式A”,public;

• 修改表
  alter table <表名>
  [add [column] <新列名> <数据类型> [完整性约束]]
  [add <表级完整性约束>]
  [drop [column] <列名> [cascade|restrict]]
  [drop constraint <完整性约束名> [cascade|restrict]]
  [alter column <列名> <数据类型>] // 修改数据类型

• 删除表
  drop table table <表名> [cascade|restrict]

索引

• 创建索引
  create [unique] [cluster] index <索引名> on <表名>(<列名>[<次序>][,<列名>[<次序>]]…)
  一个索引可以建立在一个表的一个列或多个列上，排列次序:默认asc升序，desc降序
  unique:表明此索引的每一个索引值只对应唯一的数据记录
  cluster:聚簇索引

• 为SC表建立以学号升序，课程号降序的唯一索引
  create unique index scno on sc(sno asc,cno desc)

• 修改索引
  alter index <旧索引名> rename to <新索引名>

• 删除索引
  drop index <索引名>

数据字典

关系数据库的系统表，记录了所有定义信息：
关系模式、视图、索引、完整性约束、用户权限、统计信息等。
在执行DDL时，本质是在操作数据字典。
进行查询优化和处理时，数据字典的内容是重要依据。

DML（Data Manipulation Language）

数据操纵语言是用来对数据库进行查询插入删除和修改的语言。

查找

select [all/distinct] <目标列表达式>,<目标列表达式>…
from <表名>,[select 子句] [as 别名]…
where <条件表达式>
group by <列名> [having <条件表达式>]
order by <列名> [asc/desc]

where 条件表达式常用的查询条件

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;Not + 其他比较运算符
确定范围	between and,not between end
确定集合	in,not in
字符匹配	like,not like
空值	IS NULL,IS NOT NULL
多重条件	and,or,not

集合查询

• union（并）
• intersect（交）
• except(差)

经典案例

• 查询选了所有课程的学生姓名

-- 查询这样的学生，条件是每个课程这个学生都选了
-- 关系演算
RANGE   Course  CX
RANGE   SC      SCX
GET W(Student.Sname): 全部 CX 存在 SCX(SCX.Sno=Student.Sno ∧ SCX.Cno=CX.Cno)

-- 由于SQL没有全称量词 for all，因此可以转成等价的存在量词
`for all A == not exists not A`
-- 即转换成以下等价逻辑

-- 查询这样的学生，条件是不存在这样的课程这个学生未选
-- 关系演算
RANGE   Course  CX
RANGE   SC      SCX
GET W(Student.Sname): 不存在 CX 不存在 SCX(SCX.Sno=Student.Sno ∧ SCX.Cno=CX.Cno)

-- SQL
select sname 
from student where not exists(
    select * from course where not exists(
        select * from sc where sc.sno=student.sno 
            and course.cno = sc.cno))

-- 另一个思路
-- 查询这样的学生，条件是该学生选课数等于总课数
select sname from student
where sno in (
    select sno from sc 
    group by cno 
    having count(sno) = (select count(*) from course))

-- 关系代数
π sname ((π cno,sno(sc) ÷ π cno(course)) ⋈ student)

• 查询至少选修了学生201215122选修的全部课程的学生号码

-- 查询这样的学生，条件是对于全部课程，若学生201215122选了，则该学生也选了
-- 关系演算
RANGE   Course  CX
RANGE   SC      SCX
RANGE   SC      SCY
GET W(Student.sno): 
任意CX(存在 SCX(SCX.sno = '201215122' ∧ SCX.cno = CX.cno ) => 存在 SCY(Student.sno = SCY.sno ∧ SCY.cno = CX.cno))

-- 通过逻辑蕴含的等价形式`p => q` == `not p V q`，转换上述逻辑。
-- 查询这样的学生，条件是对于全部课程，学生201215122没选或者该学生选了
RANGE   Course  CX
RANGE   SC      SCX
RANGE   SC      SCY
GET W(Student.sno): 
任意CX(不存在 SCX(SCX.sno = '201215122' ∧ SCX.cno = CX.cno ) V 存在 SCY(Student.sno = SCY.sno ∧ SCY.cno = CX.cno))

-- 再将全称量词转换为存在量词
-- 查询这样的学生，条件是不存在这样的课程，学生201215122选了并且该学生没选
RANGE   Course  CX
RANGE   SC      SCX
RANGE   SC      SCY
GET W(Student.sno): 
不存在 CX(存在 SCX(SCX.sno = '201215122' ∧ SCX.cno = CX.cno ) ∧ 不存在 SCY(Student.sno = SCY.sno ∧ SCY.cno = CX.cno))

-- 翻译成SQL
-- 最外层循环遍历学生x
-- 不存在这样的课，y中存在，x没选
select distinct sno from sc scx
where not exists(  -- 不存在这样的课
    -- 201215122所选的课程scy.cno
    select * from sc scy  
    where scy.sno = '201215122'
    and not exists(select * from sc scz     -- scy.cno中x没修，这里的z相当于x
                    where scx.sno = scz.sno
                    and scy.cno = scz.cno)
);

-- 关系代数
π sno,cno(sc) ÷ π cno(σ sno=201215122 (student ⋈ sc))

完整栗子

use study;
drop table if EXISTS  sc ;
drop table if EXISTS  student ;
drop table if EXISTS  course ;

create table  if not EXISTS student(
sno int PRIMARY key,
sname VARCHAR(20)
);

create table if not EXISTS course (
cno int PRIMARY key,
cname VARCHAR(20)

);

create table if not EXISTS sc(
sno int,
cno int,
score int,
PRIMARY key (sno,cno),
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
);

INSERT INTO `course`(`cno`, `cname`) VALUES (1, 'java');
INSERT INTO `course`(`cno`, `cname`) VALUES (2, 'c++');
INSERT INTO `course`(`cno`, `cname`) VALUES (3, 'go');
INSERT INTO `course`(`cno`, `cname`) VALUES (4, 'php');
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (1, 1, 100);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (1, 2, 88);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (2, 1, 90);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (2, 3, 88);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (3, 1, 22);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (3, 2, 89);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (3, 3, 56);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (3, 4, 55);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (4, 1, 99);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (4, 2, 99);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (4, 3, 66);
INSERT INTO `sc`(`sno`, `cno`, `score`) VALUES (4, 4, 99);
INSERT INTO `student`(`sno`, `sname`) VALUES (1, '张三');
INSERT INTO `student`(`sno`, `sname`) VALUES (2, '李四');
INSERT INTO `student`(`sno`, `sname`) VALUES (3, '王五');
INSERT INTO `student`(`sno`, `sname`) VALUES (4, '周六');

select distinct sno from sc scx
where not exists(
    select * from sc scy
    where scy.sno = '1' 
    and not exists(select * from sc scz
                    where scx.sno = scz.sno 
                    and scy.cno = scz.cno)
);
-- 上述例子的子查询
select * from sc scy
    where scy.sno = '1' 
    and not exists(select * from sc scz
                    where '4' = scz.sno 
                    and scy.cno = scz.cno)


select sname from student x
where not exists(
    select * from course y
    where not exists(select * from sc z
                    where x.sno = z.sno and z.cno = y.cno)
)

数据更新

1.插入元组

insert 
[into] <表名>[(属性1，属性2...)]
values(<常量1>,<常量2>...)

into子句中属性的顺序可以自定义，也可以不写，默认为表定义的顺序，values提供的值必须与into中一致

2.修改数据

update <表名>
set <列名1>=<表达式1>,<列名2>=<表达式2>...
where <条件>

3.删除数据

delete
from <表名>
where <条件>

视图

create view is_student as (select ...) [with check option]

with check option: 表示进行删除、插入、修改操作时候，会检查新的元组是否满足视图的where条件

• 视图消解：用户对视图的查询，数据库系统基于查询语句和视图的定义语句合并，形成新的完整的查询语句
• 一般行列子集视图可更新
• 若设视图设涉及多表、distinct、group、聚集函数等不允许更新

视图的作用

• 简化用户操作
• 可以从不同角度看同一数据
• 对重构数据库提供一定程度的逻辑独立性
• 视图可以对机密数据提供安全保护
• 适当利用视图可以更清晰地表达

授权

用户权限两要素：数据库对象、操作类型

对象类型	对象	操作类型
数据库模式	模式	create schema
数据库模式	基本表	create table, alter table
数据库模式	视图	create view
数据库模式	索引	create index
数据	基本表和视图	select,insert,update,delete,references,all privileges
数据	属性列	select,insert,update,references,all privileges

grant语句

grant <权限>[,<权限>]…
on <对象类型> <对象名>[,<对象类型> <对象名>]…
to <用户>[,<用户>]…
[with grant option];

如果指定了with grant option则获得该权限的用户可以把这种权限授予给其他用户。

• 把学生表的查询权限给U1

grant select
on table student
to u1;

• 把学生表的全部权限给U2，并运行u2授予给其他人

grant all privileges
on table student
to u2
with grant option;

revoke语句

revoke <权限>[,<权限>]…
on <对象类型> <对象名>[,<对象类型> <对象名>]…
from <用户>[,<用户>]… [cascade|restrict]

• 把u4修改学生学号的权限收回

revoke update(sno)
on table student
from u4;

• 收回所有用户对SC的查询权限

revoke select
on table sc
from public;

角色

• 创建
  create role <角色名>

• 授权
  grant 权限 on 对象类型 对象名 to 角色名

• 角色1和角色2的权限授予给角色3
  grant 角色1 角色2 to 角色3 [with admin option]
  with admin option则允许角色3继续授予该权限给其他角色

• 收回
  revoke 权限 on 对象类型 对象名
  from 角色名

例题

关系模型的三个组成部分

关系数据结构、关系数据操作、关系完整性约束

简述关系数据库语言的特点和分类

• 关系代数语言
• 关系演算语言：元组关系演算语言ALPHA、域关系演算语言QBE(Query By Example)
• SQL：具有关系代数和关系演算语言的双重特点

共同特点是：语言具有完备的表达能力，是非过程化的集合操作语言，功能强，能嵌入高级语言中