写在前面：
以下材料均为个人从互联网各处整理而成，不保证所含信息完全正确，考点基于个人学校答疑课和相关考试资料总结，不保证考点和您的实际考点相吻合。
仅供学习分享，如需转载还请私信告知。
word版文档与相关资源有偿提供，白嫖党绕道。
目录
一、根据数据管理需求用sql语句实现
1.1表的定义 完整性约束的实现 主键外键
1.1.1、创建带约束的表
---------- BEGIN ----------
CREATE TABLE course(
cno CHAR(4) PRIMARY KEY,
cname VARCHAR(30) NOT NULL,
credit TINYINT CHECK(credit>=1 AND credit<=10),
hours INTEGER,
examination CHAR(6)
);
CREATE TABLE class(
gno CHAR(7) PRIMARY KEY,
gname VARCHAR(21) UNIQUE,
grade CHAR(5),
dept VARCHAR(12),
gnum TINYINT CHECK(gnum>=15 AND gnum<=40)
);
---------- END ----------
1.1.2、约束的增删改
- 在 TESTDB 数据库中给教师表（teacher）添加主键约束，工号（tno）作为主键，给教师表姓名（tname）列添加唯一约束
---------- BEGIN ----------
ALTER TABLE teacher ADD PRIMARY KEY(tno);
ALTER TABLE teacher ADD UNIQUE(tname);
---------- END ----------
- 在 TESTDB 数据库中修改教师表（teacher）姓名列（tname）的空/非空约束，改为非空，修改教师表性别列（sex）的默认值约束，默认值改为“男”
---------- BEGIN ----------
alter table teacher
alter
column tname set not null;
alter table teacher
alter
column sex set default '男';
---------- END ----------
- 在 TESTDB 数据库中删除教师表（teacher）姓名列的唯一约束（约束名：TEACHER_TNAME_KEY）。
---------- BEGIN ----------
alter table teacher drop constraint teacher_tname_key;
---------- END ----------
1.1.3 主键外键的数据表建立
---------- BEGIN ----------
create table teaching(
cno char(4),
tno char(4),
gno char(7) ,
term tinyint,
classroom char(7),
PRIMARY KEY(cno,tno,gno),
foreign key(cno) references course(cno),
foreign key(tno) references teacher(tno),
foreign key(gno) references class(gno)
);
---------- END ----------
1.2 添加修改删除字段
1、在 TESTDB 数据库的教师表（teacher）中添加教师的电子邮箱（email，VARCHAR(6)）和办公地点（address，VARCHAR(20)）两列。
---------- BEGIN ----------
alter table teacher add email VARCHAR(6);
alter table teacher add address VARCHAR(20);
---------- END ----------
- 在 TESTDB 数据库中将教师表（teacher）中办公地点（address）的列名改为 office，电子邮箱（email）的数据类型改为VARCHAR(20)。
---------- BEGIN ----------
alter table teacher rename column address to office;
alter table teacher alter column email type VARCHAR(20);
---------- END ----------
- 在 TESTDB 数据库中删除教师表（teacher）中的办公地点（office）和电子邮箱（email）两个列。
---------- BEGIN ----------
alter table teacher drop column office;
alter table teacher drop column email;
---------- END ----------
1.3 数据、记录的增删改
1、在 TESTDB 数据库中给班级表（class）添加一条记录，班级号为 '0051807'，班级名为'电气18级7班'，年级为 '18 级'，学院为'电气'，班级人数为 '38'。
---------- BEGIN ----------
INSERT INTO class VALUES ('0051807','电气18级7班','18级','电气','38');
---------- END ----------
- 在 TESTDB 数据库中给教师表（teacher）中，添加一条教师记录，其中教师工号为'0250'，姓名为'任远'，性别为'男'。
---------- BEGIN ----------
INSERT INTO teacher(tno,tname,sex) VALUES ('0250','任远','男');
---------- END ----------
3、添加多条记录
---------- BEGIN ----------
INSERT INTO teacher VALUES
('0014','李欣','男','教授','1969-07-25'),
('0078','张云','女','副教授','1975-11-25'),
('0118','王立','男','高级工程师','1985-04-28'),
('0193','赵玲','女','讲师','1992-09-26'),
('0213','杨梅','女','副教授','1986-06-07'),
('0030','覃刚','男','副教授','1980-02-15');
---------- END----------
4、修改信息
大学物理课程的考核方式发生了改变，从原先“考试”改为“考察”，请修改课程表（course）中对应的数据。
新学年开始，有 3 名同学转专业进入 软件19级3班，更新班级表（class）中班级人数。
---------- BEGIN ----------
UPDATE course
SET examination = '考察'
WHERE cname = '大学物理';
UPDATE class
SET gnum = '38'
WHERE gname = '软件19级3班';
---------- END ----------
5、新学年开始，17级班级已毕业，从班级表（class）中删除17级班级的信息。
---------- BEGIN ----------
DELETE FROM class where grade = '17级';
---------- END ----------
1.4 查询语句
1.3.1 一般查询语句
查询课程表的全部信息。
---------- BEGIN ----------
SELECT * FROM course;
---------- END ----------
查询各位教师的工号、姓名和性别。
---------- BEGIN ----------
SELECT tno,tname,sex FROM teacher;
---------- END ----------
通过班级表查询数据库中有哪些学院。（去重查询）
---------- BEGIN ----------
SELECT DISTINCT xueyuan FROM class;
---------- END ----------
查询职称为副教授的教师的工号和姓名。
---------- BEGIN ----------
SELECT tno,tname FROM teacher WHERE title = '副教授';
---------- END ----------
查询1980年1月1日之后出生的女教师信息。
---------- BEGIN ----------
SELECT * FROM teacher WHERE birthday >= 1980-01-01 AND sex = '女';
---------- END ----------
查询 1970年 - 1980年之间出生的教师信息。
---------- BEGIN ----------
SELECT * FROM teacher WHERE EXTRACT(YEAR FROM birthday) BETWEEN 1970 AND 1980;
---------- END ----------
查询17级和19级的班级信息。（确定集合）、
---------- BEGIN ----------
SELECT * FROM class WHERE grade IN('17级','19级');
---------- END ----------
查询姓杨的老师的工号、姓名和职称。（模糊查询）
---------- BEGIN ----------
SELECT tno,tname,title FROM teacher WHERE tname LIKE '杨%';
---------- END ----------
查询全校课程最高的学分数。
查询软件学院学生的总数。
---------- BEGIN ----------
SELECT MAX(credit) FROM course;
SELECT SUM(gnum) FROM class WHERE dept = '软件';
---------- END ----------
统计软件学院的班级数量。（计数）
---------- BEGIN ----------
SELECT COUNT(gname) FROM class WHERE dept = '软件';
---------- END ----------
查询每个教师的工号及其任课的班级数。（group by）
---------- BEGIN ----------
SELECT tno,COUNT(*) FROM teaching GROUP BY tno;
---------- END ----------
查询人数超过70人（大于70）的学院的名称和人数。
查询第1学期授课数量多于1门（大于1）的教师工号和数量。（having）
---------- BEGIN ----------
SELECT dept,SUM(gnum) FROM class GROUP BY dept HAVING (SUM(gnum)>70);
SELECT tno,COUNT(tno) FROM teaching GROUP BY tno HAVING (COUNT(tno)>1);
---------- END ----------
查询软件学院班级的班号、班级名和人数，并按人数升序排列。
---------- BEGIN ----------
SELECT gno,gname,gnum FROM class WHERE dept = '软件' ORDER BY gnum ASC;
---------- END ----------
查询教师授课情况，结果先按学期升序排列，再按工号降序排列
---------- BEGIN ----------
SELECT * FROM teaching ORDER BY term ASC,tno DESC;
---------- END ----------
1.3.2 子连接 多个表的连接 子查询
查询参加了G001课程的学生人数。
---------- BEGIN ----------
select sum(gnum) from class where gno in(select gno from teaching where cno='G001')
---------- END ----------
查询“杨梅”老师所讲授的课程，要求列出教师号、教师姓名和课程号。
---------- BEGIN ----------
Select teacher.tno,tname,cno from teacher,teaching where tname='杨梅'and teacher.tno=teaching.tno
---------- END ----------
查询18级所有班级的上课信息，要求列出班级号、课程号、开课学期和教室名。
---------- BEGIN ----------
select class.gno,cno,term,classroom from class,teaching where grade='18级' and class.gno=teaching.gno
---------- END ----------
查询比“软件19级3班”人数少的班级信息（班级号、班级名、班级人数）。
---------- BEGIN ----------
SELECT
s2.gno,s2.gname,s2.gnum
FROM
class s1,
class s2
WHERE
s1.gname = '软件19级3班'
AND s2.gnum<s1.gnum;
---------- END ----------
查询讲授课程号‘R003’的教师的姓名。
---------- BEGIN ----------
select tname from teacher where teacher.tno in (select tno from teaching where cno='R003');
---------- END ----------
查询比“软件19级3班”人数多的班级上课信息，要求列出课程号、班级号和教师工号。
---------- BEGIN ----------
SELECT teaching.cno,teaching.gno,teaching.tno FROM
teaching,class, (SELECT gnum FROM class WHERE gname='软件19级3班'
) tmp
WHERE
class.gnum > tmp.gnum
and class.gno=teaching.gno
---------- END ----------
查询参加课程号为G001班级的班级号、班级名。
---------- BEGIN ----------
SELECT class.gno,class.gname FROM teaching,class WHERE teaching.cno='G001'AND teaching.gno=class.gno
---------- END ----------
查询给19级讲授课程的教师工号、姓名，查询结果按工号升序排列。
---------- BEGIN ----------
SELECT DISTINCT teacher.tno,teacher.tname FROM teacher,teaching,class WHERE class.grade='19级'AND teaching.gno=class.gno AND teaching.tno=teacher.tno order by teacher.tno asc
---------- END ----------
将讲授‘离散数学’课程的教室改为‘B001’
---------- BEGIN ----------
UPDATE teaching SET classroom='B001' WHERE cno IN
(SELECT cno FROM course
WHERE cname='离散数学');
---------- END ----------
1.5 视图
1.5.1 创建视图
SQL CREATE VIEW 语法:
CREATE VIEW <视图名>
AS SELECT <字段名1>, <字段名2>...
FROM <表名>
WHERE <条件>;
1 创建一个副教授职称的教师视图 T_Sub（工号、姓名、性别和职称）。
---------- BEGIN ----------
create view T_Sub(tno,tname,sex,title) as
select tno,tname,sex,title from teacher where title='副教授';
---------- END ----------
2 创建软件学院班级上课情况视图 C_S（班级名、课程名、教师号）。
---------- BEGIN ----------
create view C_S(gname,cname,tno) as
select gname,cname,tno
from course,class,teaching
where course.cno=teaching.cno and teaching.gno=class.gno and class.dept='软件';
---------- END ----------
1.5.2 查询视图
SELECT <字段名1>, <字段名2>...
FROM <视图名>;
查询视图C_S中的班级名和课程名。
---------- BEGIN ----------
select gname,cname from C_S;
---------- END ----------
1.5.3 删除视图
您可以通过 DROP VIEW 命令来删除视图。
SQL DROP VIEW 语法:
DROP VIEW <视图名>；
删除班级上课情况视图C_S。
---------- BEGIN ----------
drop view C_S;
---------- END ----------
1.5.4 视图内增改删
1 视图内添加
INSERT INTO 语句
INSERT INTO 语句用于向视图中插入新记录，与向表中插入数据相似。
INSERT INTO <视图名> (<字段名1>, <字段名2>...)
VALUES (<值1>, <值2>, <值3>,...);
---------- BEGIN ----------
insert into T_Sub(tno,tname,sex,title) values('0296','赵梦','女','副教授');
---------- END ----------
2 视图内修改
UPDATE 语句
可以使用 UPDATE 语句来修改、更新一个或多个视图的数据。
使用 UPDATE 语句修改视图与修改表类似，语法格式为：
UPDATE <视图名>
SET <列名>=<表达式> [,<列名>=<表达式>]…
[WHERE (<条件>)]；
语法说明如下：
<视图名>：用于指定要更新的视图名称。
SET 子句：用于指定视图中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定视图中要修改的行。若不指定，则修改表中所有的行。
将 T_Sub 的教师视图中 赵梦 的职称改为“教授”。
---------- BEGIN ----------
update T_Sub set title='教授' where tname='赵梦';
---------- END ----------
3 视图内删除
可以使用 DELETE 语句可以删除视图中的一行或多行记录，其语法格式为：
DELETE FROM <视图名>
[WHERE <条件>]；
<视图名>：用于指定删除数据的表。
WHERE 子句：指定待删除的记录应当满足的条件，WHERE 子句省略时，则删除视图中所有记录。
删除 T_Sub 的教师视图中教师 覃刚 的记录。
---------- BEGIN ----------
delete from T_Sub where tname='覃刚';
---------- END ----------
1.6 索引
1.6.1 创建索引
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。
语法格式：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX <索引名>
ON <表名> (<列名> [DESC|ASC]] [,...]);
语法说明如下：
UNIQUE ：表示唯一索引。
CLUSTERED ：表示创建聚集性索引。
NONCLUSTERED ：表示创建非聚集性索引。
<表名>：指定要创建索引的表名。
<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
ASC|DESC ：可选项。 ASC 指定索引按照升序来排列， DESC 指定索引按照降序来排列，默认为 ASC 。
为教师表在姓名列上建立B-Tree索引TI。
---------- BEGIN ----------
create unique index TI on teacher(tname);
---------- END ----------
1.6.2 查看索引
查看教师表的索引信息
---------- BEGIN ----------
SELECT * FROM user_indexes WHERE table_name='TEACHER';
---------- END ----------
使用简单的查询语句就可以对视图进行查询操作:
SELECT *
FROM user_indexes
WHERE table_name = <表名>;
1.6.3 修改和删除
1将索引 TI 重命名为 TTI
---------- BEGIN ----------
alter index TI rename to TTI;
---------- END ----------
修改索引和修改表的语句相似:
ALTER INDEX [IF EXISTS] <旧索引名> RENAME TO <新索引名>;
IF EXISTS :判断所要修改的索引名是否存在。
2删除索引 TTI。
---------- BEGIN ----------
drop index TTI;
---------- END ----------
1.7 授权语句
1.7.1 创建用户和授权
1.创建用户 U1，密码均为 rjxy1234!；
2.把教师表查询和插入数据的权限，班级表查询和更新班级人数数据的权限授给用户 U1。
---------- BEGIN ----------
CREATE USER U1 PASSWORD 'rjxy1234!';
grant select(teacher),INSERT(teacher),update(class),select(class)
on table teacher,class
to U1;
---------- END ----------
1.登录数据库并创建用户
创建用户：
CREATE USER <所创建用户> PASSWORD '<密码>';
2.授权表
GRANT <授权的功能> ON TABLE <表名> TO <用户>;
1.7.2 创建角色和授权
1.创建用户 U2、U3，密码均为 rjXY1234!；
2.创建角色 R1，增加角色R1对教师表查询和删除数据的权限（允许级联）。将角色 R1 授予 U2。
---------- BEGIN ----------
CREATE USER U2 PASSWORD 'rjxy1234!';
CREATE USER U3 PASSWORD 'rjxy1234!';
CREATE ROLE R1;
GRANT select,delete ON TABLE teacher TO R1 WITH GRANT OPTION;
GRANT R1 TO U2 WITH ADMIN OPTION;
---------- END ----------
1.创建用户
CREATE USER <所创建用户> PASSWORD '<密码>';
2.创建角色
CREATE ROLE <角色名>;
3.数据库对象的授权
GRANT <SELECT|DELETE|UPDATE|INSERT|ALL> ON TABLE <表名> TO <用户名|角色名> [WITH GRANT OPTION]
注：如果指定了WITH GRANT OPTION，特权的接收者可以接着把它授予给其他人。如果没有授权选项，接收者就不能这样做。
4.角色的授权
GRANT <角色名> TO <用户名|角色名> [WITH ADMIN OPTION];
注：如果指定了WITH ADMIN OPTION，成员接着可以把该角色中的成员关系授予给其他用户，也可以撤回该角色中的成员关系。如果没有此选项，普通用户就不能做这些工作。
1.7.3 收回权限
收回U1、U2对教师表的所有操纵权限（ALL）。
注：角色R1向用户U2级联授权教师表的查询和删除数据。
---------- BEGIN ----------
REVOKE all on TABLE teacher from U1;
REVOKE all on TABLE teacher from R1 CASCADE;
---------- END ----------
REVOKE <SELECT|DELETE|UPDATE|INSERT|ALL> ON TABLE <表名> FROM <用户名|角色名> [CASCADE];
注：如果一个用户被指定了授予特权（例如WITH GRANT OPTION），那么如果要回收该用户的特权，就要指定CASCADE，实现依赖特权的连带回收，否则会导致回收失败。
二、er图与关系模式
通过语句描述画er图
将er图转换为关系模式
一对一和一对多的联系转换时和一端或多端合并
只有多对多的联系需要单独建立关系
（1）1:1
当转换为关系模型时，在两个实体任选一个添加另一个实体的主键即可。
如图（a）情况，当我们转换成关系模式时，我们可以在实体型A模式中添加实体型B的主键；或者在实体型B模式中添加实体型A的主键。
例子：
校里一个班级只有一个正班长，而一个班长只在一个班中任职，则班级与班长之间具有一对一联系。
当转换为关系模式时，我们可以在班长关系中添加班级的主键（班级号）；或者在班级关系中添加班长的主键（学号）。
（2）1:N
当转换为关系模型时，在N端添加另一端的主键。
如图（b）情况，当我们转换成关系模式时，我们得在实体型B模式中添加实体型A的主键。
例子：
一个班级中有若干名学生，而每个学生只在一个班级中学习，则班级与学生之间具有一对多联系。
当转换为关系模式时，我们得在学生关系中添加班级的主键（班级号）。
假设平台和管理员之间的联系同时存在1:1和1:N）
对于1:1联系“聘用”，可以在“平台”关系模式中加入管理员账号ID（管理员账号ID是外键）；
对于1:N联系“聘用”，则在“管理员”关系模式中加入商标和聘期两个属性（商标是1端的键）:
①平台（商标，名称，所属公司，管理员的账号ID）
②管理员（账号ID，账号密码，用户名，商标、聘期）
（3）M:N
当转换为关系模型时，需要将联系转换为实体，然后在该实体上加上另外两个实体的主键，作为联系实体的主键，然后再加上该联系自身带的属性即可。
如图（c）情况，当我们转换成关系模式时，我们要把“联系名”转换为实体模式，添加另外两个实体的主键，这两个主键合起来为“联系名”的主键，然后添加该联系自带的属性即可。
对于M:N联系“下单”和“上传发布”，则生成两个新的关系模式：
①下单（商品编号，顾客账号ID，订单编号，订单数量，下单时间）
②上传发布（商品编号，管理员账号ID，发布时间）
三、规范化
规范化： 一个低一级的关系模式通过模式分解可以转化为若干个高一级范式的关系模式的集合，这个过程叫做规范化。
消除决定属性集非码的非平凡函数依赖
1NF
定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。
理解： 第一范式强调数据表的原子性，是其他范式的基础。如下图所示数据库就不符合第一范式
2NF
定义：若某关系R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关系R属于第二范式。
理解： 第二范式是指每个表必须有一个（有且仅有一个）数据项作为关键字或主键（primary key），其他数据项与关键字或者主键一一对应，即其他数据项完全依赖于关键字或主键。由此可知单主属性的关系均属于第二范式。
3NF
定义： 非主属性既不传递依赖于码，也不部分依赖于码。
理解： 第三范式要求在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即在第二范式的基础上，消除了传递依赖。
BCNF
定义： 关系模式R<U,F>中，若每一个决定因素都包含码，则R<U,F>属于BCFN。
理解： 根据定义我们可以得到结论，一个满足BC范式的关系模式有：
所有非主属性对每一个码都是完全函数依赖；
所有主属性对每一个不包含它的码也是完全函数依赖；
没有任何属性完全函数依赖于非码的任何一组属性。
就是要在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。
4NF
定义： 限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。
理解： 显然一个关系模式是4NF，则必为BCNF。也就是说，当一个表中的非主属性互相独立时（3NF），这些非主属性不应该有多值，若有多值 就违反了4NF
5NF
第五范式有以下要求：
（1）必须满足第四范式；
（2）表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。
第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。
1NF-消除非主属性对码的部分函数依赖
→2NF-消除非主属性对码的传递函数依赖
→3NF-消除主属性对码的部分和传递函数依赖
→BCNF-消除非平凡且非函数依赖的多值依赖
→4NF-消除不是由候选码所蕴含的连接依赖
→5NF
关系模式的分解
如果一个分解具有无损连接性,则它能够保证不丢失信息
如果一个分解保持了函数依赖，则它可以减轻或解决各种异常情况
分解具有无损连接性和分解保持函数依赖是两个互相独立的标准
-具有无损连接性的分解不一定能够保持函数依赖
-同样,保持函数依赖的分解也不一定具有无损连接性
-若要求分解具有无损连接性，那么模式分解一定能够达到4NF
-若要求分解保持函数依赖，那么模式分解一定能够达到3NF，但不一定能够达到BCNF
-若要求分解既具有无损连接性,又保持函数依赖,则模式分解一定能够达到3NF，但不一定能够达到BCNF
阿姆斯特朗定律
设R（U）是属性集U={A1,A2,…,An}上的一个关系模式，F为R（U）的一组函数依赖，记为R（U, F）, 则有如下规则成立
**自反律：**若Y包含于X包含于U，则X→Y被F逻辑蕴含；（X→Y为平凡依赖）
**增广率：**若X → Y ∈ F，且Z包含于U，则 XZ → YZ被F逻辑蕴含；（**或：**若X → Y ∈ F，且V包含于W，则 XW → YV被F逻辑蕴含）
**传递率：**若X → Y ∈ F，且Y → Z，则X → Z 被F逻辑蕴含；
阿姆斯特朗定律推论
**合并律：**若 X → Y 且 X → Z，则X → YZ；
**伪传递律：**若X→Ｙ且WY→Z，则XW→Z；
**分解律：**若X→Y，且Z包含于Y，则X→Z；
4006
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
