数据库复习3——关系模型

数据库复习笔记汇总

数据库复习1——基本概念
数据库复习2——数据模型
数据库复习3——关系模型
数据库复习4——数据库设计
数据库复习5——数据库基本原理
数据库复习6——事务处理

---

关系模型

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

关系模式

对关系的描述，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 int
字符匹配	like,not like
空值	IS NULL,IS NOT NULL
多重条件	and,or,not

集合查询

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

经典案例

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

-- 不存在这样的课程,course中有,x没选
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)
)

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

-- 最外层循环遍历学生x
-- 不存在这样的课，y中存在，x没修
select distinct sno from sc scx
where not exists(
    -- 中间循环遍历201215122所选的课程y
    -- 找出所有y中学生x没选的课程
    select * from sc scy  
    where scy.sno = '201215122' 
    and not exists(select * from sc scz     
                    where scx.sno = scz.sno 
                    and scy.cno = scz.cno)
);

完整栗子

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、聚集函数等不允许更新

视图的作用

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