文章目录
数据库（基础篇）
本文基于王珊、萨师煊编著的《数据库与系统概论（第五版）》
关系数据库
基本概念
-
域(domain)：一组具有相同数据类型的值的集合
- 整数域
- 实数域
- {1,2,3,4}
- 类似于集合
-
笛卡尔积(cartesian product)
- 域D1={A1,B1}，D2={A2,B2}，D1,D2的笛卡尔积为D1×D2={(A1,A2),(A1,B2),(B1,A2),(B1,B2)}
- 没有交换律
-
关系(relation)：笛卡尔积的子集在域上的关系，表示为R(D1,D2···Dn)
- n是关系的目或度(degree)
- 关系中的行称为元组(tuple)
- 关系中的列称为属性(attribute)
-
码(key)
- 某一属性组能唯一地标识一个元组，而其子集不行，则称该属性为候选码(candidate key)。
- 若有多个候选码，则选择其中一个为主码(primary key)
关系的每一个分量必须是一个不可分的数据项（不能表中有表）
关系必须是笛卡尔积的真子集才有实际价值
关系的完整性
实体完整性
若属性A是基本关系R的主属性，则A不能取空值(null value)
参照完整性
- 设F是基本关系R的一个或一组属性，但不是关系R的码，Ks是基本关系S的主码。如果F和Ks相对应，则称F是R的外码(foreign key)，并称基本关系R为参照关系(referencing relation)，基本关系S为被参照关系(referenced relation)或目标关系(target relation)。
- F是基本关系R的外码，它与基本关系S的主码Ks相对应，则F的值必须为
- 或者取空值
- 或者等于S中某个元组的主码值
用户自定义完整性
关系代数
传统集合运算符
并（∪，union），差（－，except），交（∩，intersection），笛卡尔积（×，广义笛卡尔积，cartesian product）
专门关系运算符
- 选择（selection，又叫限制，restriction）：在关系R中选择满足给定条件的元组。
- 投影（projection）：关系R上的投影是从R中选择出若干属性列组成新关系。
- 连接（join）：从两个关系的笛卡尔积中选取属性间满足一定条件的元组。
- 在连接操作中被舍弃的元组被称为悬浮元组(dangling tuple)
- 把悬浮元组也保存在结果关系中，则在其他属性上填空值(NULL)，那么这种连接就叫做外连接(outer join),如果只保留左边关系中的悬浮元组叫左外连接(left join)，只保留右侧关系中的悬浮元组就叫做右外连接(right join)
- 除（division）：设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合都在R中。
关系数据库标准语言SQL
| 操作对象/操作方式 | 创建 | 删除 | 修改 |
|---|---|---|---|
| 模式 | CREATE SCHEMA | DORP SCHEMA | |
| 表 | CREATE TABLE | DORP TABLE | ALTER TABLE |
| 视图 | CREATE VIEW | DROP VIEW | |
| 索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
模式的定义与删除
模式类似于C++中的命名空间
定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
同时CREATE SCHEMA还接受创建基本表、视图，定义授权字句。
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COL1 SMALLINT,
COL2 INT,
COL3 CHAR(20),
COL4 NUMBERIC(10,3),
COL5 DECIMAL(5,2)
);
删除模式
DROP SCHEMA<模式名><CASCADE|RESTRICT>
CASCADE和RESTRICT必选其一。
CASCADE（级联）：删除模式时把该模式中所有的数据库对象全部删除。RESTRICT（限制）：若该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。
基本表的定义、删除和修改
定义基本表
CREATE TABLE <表名>（<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
···
[,<表级完整性约束条件>]）;
举个例子：建立一个“课程“表Course
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,/*列级完整性约束条件，Cno是主码*/
Cname CHAR(40) NOT NULL,/*列级完整性约束条件，Cname不能取空值*/
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);
参照表和被参照表可以是同一个表
数据类型
| 数据类型 | 含义 |
|---|---|
| CAHR(n),CHARACTER(n) | 长度为n的定长字符串 |
| VARCHAR(n),CHARACTERVARYING(n) | 最大长度为n的变长字符串 |
| CLOB | 字符串最大对象 |
| BLOB | 二进制大对象 |
| INT,INTEGER | 长整数（4字节） |
| SMALLINT | 短整数（2字节） |
| BIGINT | 大整数（8字节） |
| NUMERIC(p,d) | 定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字 |
| DECIMAL(p,d),DEC(p,d) | 同NUMERIC |
| REAL | 取决于机器精度的单精度浮点数 |
| DOUBLE PRECISION | 取决于机器精度的双精度浮点数 |
| FLOAT(n) | 可选精度的浮点数，精度至少为n位数字 |
| BOOLEAN | 逻辑布尔量 |
| DATE | 日期，包含年、月、日，格式为YYYY-MM-DD |
| TIME | 时间，包含一日的时、分、秒，格式为HH:MM:SS |
| TIMESTAMP | 时间戳类型 |
| INTERVAL | 时间间隔类型 |
模式与表
-
在表中显式地给出模式名
CREATE TABLE "S-T".Student(···);/*Student所属的模式是S-T*/ -
所创建模式语句中同时创建表
参见“模式的定义与删除”章节
-
设置所属模式
使用下面的语句可以显示当前搜索路径
SHOW search_path;默认值为$user,PUBLIC。意为首先搜索与用户名相同的模式名，否则使用PUBLIC模式。
DBA也可以设置搜索路径
SET search_path TO "S-T",PUBLIC;
修改基本表
使用ALTER TABLE来修改基本表
ALTER TABLE<表名>
[ADD [COLUMN] <新列名><数据类型>[完整性约束]]
[ADD<表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束>[RESTRICT|CASCADE]]
[ALTER COLUMN<列名><数据类型>];
删除基本表
使用DROP TABLE来删除基本表
DROP TABLE<表名>[RESTRICT|CASCADE];
索引的建立和删除
当数据量比较大时，建立索引可加快查询时间。常见的索引包括顺序文件上的索引、B+树索引、散列（hash）索引、位图索引等。详细内容请参照操作系统相关知识。SQL标准中不涉及索引，各个数据库具体使用的索引不尽相同，索引的建立和删除由DBA或表的属主（owner）负责，用户不必也不能显式地选择索引。
建立索引
CREATE [UNIQUE][CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<ASC|DESC>][,<列名>[<ASC|DESC>]]···);
-
索引可以建立在该表的一列或多列上。
-
列明后面还可以指定排列次序
ASC为升序，DESC为降序，默认为ASC。 -
UNIQUE表示此索引的每一个索引值只对应唯一的数据记录。 -
CLUSTER表示要建立的索引是聚簇索引
修改索引
使用下面语句对已经建立的索引重命名
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
删除索引
DROP INDEX <索引名>;
数据字典
数据字典记录了数据库中所有的定义信息。关系数据库系统在执行SQL数据定义语句时，实际上就是在更新数据字典中的响应信息。
数据查询
数据查询的一般格式为
SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]···
FORM <表名或视图名>[,<表名或视图名>···]|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>][ASC|DESC]
WHERE子句：条件表达式，从FORM子句指定的基本表、视图或派生表中找出的元组必须满足WHERE子句的约束条件。GROUP BY子句：将结果按照列名1的值进行分组，比如学生成绩单中有多个相同的名字，该子句就可以把这些相同名字的学生合成一个，通常和聚集函数一起使用。HAVING限定输出元组满足的条件。ORDER BY子句：结果表按照列名2的值升序或降序排列。
单表查询
-
查询全体学生的学号和姓名
SELECT Sno,Sname FROM Student; -
查询全体学生的详细记录
SELECT * FROM Student;*代表所查询表中的全部属性 -
查询语句中定义列别名
SELECT Sname AS NAME,'Year of Birth:' AS BRITH,24-Sage AS BIRTHDAY FROM Student;注意：王珊的书中并没有使用
AS，不过我在多方查找资料后觉得这是一种不太规范的的写法，最好还是加上AS -
消除取值重复的行
SELECT DISTINCT Sno FROM SC;不使用
DISTINCT则默认为ALL。 -
条件查询
使用
WHERE子句。如查询CS、MA、IS学生的姓名和性别SELECT Sname,Ssex FROM Student WHERE Sdept IN('CS','MA','IS');查询条件 谓词 比较 =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符 确定范围 BETWEEN AND,NOT BETWEEN AND 确定集合 IN,NOT IN 字符匹配* LIKE,NOT LIKE 空值 IS NULL,NOT IS NULL 多重条件（逻辑运算） AND,OR,NOT *字符匹配可以使用正则表达式
-
ORDER BY子句默认为升序
-
聚集函数
函数名称 函数功能 COUNT(*) 统计元组的个数 COUNT([DISTINCT|ALL]<列名>) 统计一列中值的个数 SUM([DISTINCT|ALL]<列名>) 计算一列值的总和（次列必须是数值型） AVG([DISTINCT|ALL]<列名>) 计算一列值的平均值（同上） MAX([DISTINCT|ALL]<列名>) 求一列值中的最大值 MIN([DISTINCT|ALL]<列名>) 求一列值中的最小值 比如查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM Student;为避免重复计算学生人数，需要使用
DISTINCT。- 当聚集函数遇到空值时，除
COUNT(*)外，都跳过空值。 - 聚集函数只能用于
SELECT子句和GROUP BY中的HAVING子句,不能用于WHERE子句。
- 当聚集函数遇到空值时，除
-
GROUP BY子句求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;
连接查询
-
等值与非等值连接查询
查询每个学生及其选修课程的情况。
SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;- 在等值连接中把目标列中的重复属性列去掉则为自然连接。
- 若属性在查询的表中是唯一的，则可以去掉表明前缀
-
自身连接
需要先为表取两个别名
FIRST和SECONDSELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno; -
外连接
外链接的概念请参照“关系数据库-关系代数-专门关系运算符”章节
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno); /*可以使用`USING`去掉结果中重复的值,两个语句等价*/ FROM Student LEFT OUTER JOIN SC USING(Sno); -
多表连接
涉及两个以上的表的连接
SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
嵌套查询
-
带有
IN谓词的子查询查询“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='刘晨');子查询的条件不依赖于父查询，称为不相关子查询。
-
带有比较字符的子查询
找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno,Cno FROM SC AS x WHERE Grade>=(SELECT AVG(Grade) FROM SC AS y WHERE y.Sno=x.Sno);参数
y.Sno的值是与父查询相关的，这种查询叫做相关子查询。 -
带有
ANY(SOME)或ALL谓词的子查询ANY代表查询结果中的某个值，ALL代表查询结果中的所有值。查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept<>'CS';ANY、ALL谓词与聚集函数、IN谓词之间存在转换关系= <>或!= < <= > >= ANY IN – <MAX <=MAX >MIN >=MIN ALL – NOT IN <MIN <=MIN >MAX >=MAX -
带有
EXISTS谓词的子查询EXISTS代表存在量词，其子查询不返回任何数据，只产生"true"或"false"。查询所有选修了1号课程的学生姓名
SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1')EXISTS引出的子查询，其目标列表表达式通常为*。与
EXISTS相对应的是NO EXISTS，其结果正好与EXISTS相反。
集合查询
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。
集合的操作包括并-UNION，交-INTERSECT，查-EXCEPT.其含义参照“关系数据库-关系代数-专门关系运算符”章节
查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
使用UNION时，系统会自动去掉重复元组。如果要保留则用UNION ALL操作符。
基于派生表的查询
当子查询出现在FROM子句中时，这个子查询生成的临时**派生表(derived table)**就变成了主查询的查询对象。
找出每个学生超过他自己选修课程平均成绩的课程号
SELECT Sno,Cno
FROM SC,(
SELECT Sno,Avg(Grade)
FROM SC
GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;
数据更新
插入数据
-
插入元组
基本格式为
INSERT INTO <表名>[(<属性列1>[,<属性列2>]···)] VALUES(<常量1>[,<常量2>]···);插入一条选课记录（‘201215128’，‘1’）
INSERT INTO SC(Sno,Cno) VALUES('201215128','1');数据库系统将在新插入记录的未赋值的列上自动赋空值。若
INTO后没有指出任何属性值，则要在未赋值的列上明确地给出空值。 -
插入子查询结果
基本格式为
INSERT INTO <表名>[(<属性列1>[,<属性列2>]···)] 子查询;对每一个系，求学生的平均年龄，并把结果存入数据库。
INSERT INTO Dept_age(Sdept,Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;
修改数据
一般格式为
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]···
[WHERE <条件>];
将计算机科学系全体同学的成绩置零
UPDATE SC
SET Grade=0
WHERE Sno IN(SELECT Sno
FROM Student
WHERE Sdept='CS');
修改数据
删除计算机科学系所有学生的选课记录
DELETE
FROM SC
WHERE Sno IN(SELECT Sno
FROM Student
WHERE Sdept='CS');
视图
视图是由表（或视图）所导出的表，它是一个虚表，数据库中只保存视图的定义。视图仅在使用时才会运行视图的定义。
定义视图
CREATE VIEW <视图名>[(<列名>[,<列名>]···)]
AS <子查询>
[WITH CHECK OPTION];
建立信息系学生的视图，并要求进行修改和插入操作时仍保证该视图只有信息系的学生
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
WITH CHECK OPTION子句保证了以后对该视图进行修改时系统自动加上WHERE子句的约束条件。
删除视图
DROP VIEW <视图名>[CASCADE];
查询视图
查询语句和基本表一样。
查询视图时，先从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转化成等价的对基本表的查询，然后执行修正了的查询。这个过程叫做视图消解(view resolution)。
注意：在对非行列子集视图的查询要直接对基本表进行。因为在进行视图消解时可能会发生错误。
更新视图
与更新表基本一致。
由于视图消解的存在，有些视图是不可更新的。
数据库的安全性
授权
授予
GRANT <权限>[,<权限>]···
ON <对象类型><对象名>[,<对象类型><对象名>]···
TO <用户>[,<用户>]···
[WITH GRANT OPTION];
WITH GRANT OPTION子句意为该用户还可以把权限授予其他用户。
用户可以是PUBLIC。
不允许循环授权。
收回
REVOKE <权限>[,<权限>]···
ON <对象类型><对象名>[,<对象类型><对象名>]
FROM <用户>[,<用户>]···[CASCADE|RESTRICT];
收据库角色
数据库角色是权限的集合。
创建角色格式
CREATE ROLE <角色名>;
给角色授权语句和收回语句和用户一样。
角色也可以授予其他角色和用户。
审计
对修改SC表结构或修改SC表数据的操作进行审计
AUDIT ALTER,UPDATE
ON CS;
取消对SC表的一切审计
NOAUDIT ALTER,UPDATE
ON SC;
数据库的完整性
若无显示定义违约处理，违约操作一律拒绝。
实体完整性
实体完整性约束即定义PRIMARY KEY。
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SAMLLINT,
PRIMARY KEY(Sno,Cno)
);
参照完整性
参照完整性是关于外键的约束。
定义参照完整性
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SAMLLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student /*在表级定义参照完整性*/
);
违约处理
参照完整性有三种违约处理，为拒绝(NO ACTION，默认策略)，级联(CASCADE)，设置位空值。
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SAMLLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno) REFERENCES Student /*在表级定义参照完整性*/
ON DELETE [CASCADE|NO ACTION|SET NULL]
ON UPDATE CASCADE /*级联更新*/
);
用户定义完整性
属性上的约束条件
- 列值非空（NOT NULL）
- 列值唯一（UNIQUE）
- 检查列值是否满足一个条件表达式（CHECK短语）
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK(Ssex IN ('男','女')),/*性别只允许取男或女*/
Sage SAMLLINT,
Sdept CHAR(20)
);
元祖上的约束条件
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2),
Sage SAMLLINT,
Sdept CHAR(20),
CHECK(Ssex='女' OR Sname NOT LIKE 'Ms.%')/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/
);
完整性约束命名子句
CONTRAINT <完整性约束条件名><完整性约束条件>
删除使用DROP。
我感觉这个东西没什么大用。
断言
每次对表进行操作就会触发断言检查，若违反断言则拒绝该操作。
限制课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK(60>=(SELECT COUNT(*)
FROM Course,SC
WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库')
);
删除断言
DROP ASSERTION <断言名>;
触发器
触发器类似于C/C++中的if子句。建立触发器的格式为
CREATE TRIGGER <触发器名>
{BEFORE|AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS <变量> /*指出引用的变量*/
FOR EACH {ROW|STATEMENT} /*定义触发器类型*/
[WHEN <触发条件>] <触发动作体>
- 只有表的拥有者才可以在表上建立触发器。
AFTER|BEFORE是触发时机，指明触发器激活时间是在执行触发事件前或后。- 触发器类型可定义为行级触发器
ROW和语句级触发器STATEMENT。
删除触发器
DROP TRIGGER <触发器名> ON <表名>
触发器这一章太复杂了，很多东西我也看不懂，先记着。
1348
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
