数据库复习

第一章

数据库的四个基本概念

  • 数据：描述事物的符号称为数据
  • 数据库：长期存储在计算机内，有组织的，可共享的大量数据集合。
  • 数据库管理系统：位于用户和操作系统之间的一个系统软件
    • 功能：数据定义、数据组织、数据操纵、数据库的事务管理运行管理（保证安全性、完整性、并发控制），数据库的建立和维护功能
  • 数据库系统：由数据库、数据库管理系统、应用程序、数据库管理员组成的存储、管理、处理和维护数据的系统。

数据库系统的特点

（独立性）

  • 数据结构化：是数据库系统与文件系统的本质区别

  • 数据的共享性高、冗余度低且易扩充

数据共享可以大大减少数据冗余，数据共享还能避免数据之间的不相容性和不一致性

  • 数据冗余可能导致存储空间的浪费和不一致性
  • 数据独立性高：物理独立性和逻辑独立性
  • 数据由数据库管理系统统一管理和控制

数据的安全性只是保护数据以防止不合法使用造成的数据泄露破坏

数据完整性：数据确定性、有效性和相容性

数据模型

第一类是概念模型，第二类是逻辑模型和物理模型

数据模型的组成要素

  • 数据结构：描述数据库的组成对象以及对象之间的联系
  • 数据操作
  • 数据的完整性约束条件

常用的数据模型

  • 层次模型
  • 网状模型
  • 关系模型
  • 面向对象数据模型（x)

数据库系统的三级模式结构

  • 模式：全体数据逻辑结构和特征的描述
  • 外模式：局部数据逻辑结构和特征的描述
  • 内模式：数据在数据库内部的组织方式

数据库的二级映像

  • 外模式/模式映像：对于每一个外模式都有一个外模式/模式映像

数据的逻辑独立性：当模式改变时，DBA对外模式/模式改变，这样外模式就不用改变了，应用程序也不变

  • 模式/内模式映像：只有一个，定义了数据全局逻辑结构和存储结构之间的关系

数据的物理独立性：当存储结构改变时，只需要对模式/内模式进行改变，这样模式就不必改变了，应用程序也不变。

第二章

关系的三种类型

  • 基本关系
  • 查询表
  • 视图表

基本关系的性质

  1. 列是同质的
  2. 不同列可出自同一个域
  3. 列的顺序无所谓
  4. 任意两个元组的候选码不能取相同的值
  5. 行的顺序无所谓
  6. 分量必须取原子值

关系完整性

  • 实体完整性：若属性是基本关系的主属性，则这个属性不能取空值
  • 参照完整性：被参照关系，外码可以是空的或者是另一个关系的主码
  • 用户定义的完整性

关系代数

  • 选择：$\sigma$ 选择一个属性的学生，按照元组输出
  • 投影：$\Pi$ 从R中选择出若干属性组成新的关系
  • 连接：$\theta$
    • 等值连接 A R.B C S.B E ，自然连接A B C E （这两个只是两个表都存在的值）
    • 外连接，在其他没有的属性上加上NULL，左外连接，右外连接

第四章：数据库安全性

  1. 用户身份鉴别
  2. 多层存取控制
  3. 审计
  4. 视图
  5. 数据加密

自主存取控制方法

对存取权限的定义称为：授权

1
2
3
GRANT INSERT/SELECT/UPDATA
ON TABLE Student
TO U1;

收回

1
2
3
REVOKE INSERT
ON TABLE Student
FROM U1;

创建数据库模式的权限

1
CREATE USER U1 WITH CONECT/RESOURRCE/DBA

只有管理员可以创建新的数据库用户

数据库角色

创建

1
CREATE ROLE R1;

授权

1
2
3
GRANT SELECT,UPDATE
ON TABLE Student
TO R1;

强制存取控制方式

绝密–机密–可信–公开

主体>= 客体 可读

主体 <=客体 可写

第五章：数据库完整性

数据库的完整性是指数据的正确性和相容性

定义实体完整性：

1
PRIMARY KEY(Sno,Cno);

定义参照完整性：

1
2
FOREIGN KEEY(Sno) REFERENCES Student(Sno)
	ON DELETE CASCADE

CASCADE 级联删除

用户定义完整性：

1
2
3
NOT NULL
UNIQUE
Ssex CHAR(2) CHECK(Ssex IN('nan','nv'))

完整性约束命名子句

1
2
CONSTRAINT C1 FOREIGN KEY(Deptno);
CONSTRAINT C2 CHECK(Sno BETWEEN 90000 AND 9999999)

第六章

函数依赖

  • 完全函数依赖
  • 部分函数依赖
  • 传递函数依赖

规范化

规范化主要为了克服数据库插入异常、删除异常以及冗余度大

  • 1NF：每个分量都是不可分割的数据项
  • 2NF：属于1NF且每个非主属性都完全函数依赖于任何一个候选码
  • 3NF：属于1NF且不存在传递函数依赖
  • BCNF：属于1NF，且不能有主属性对码的部分依赖和传递依赖，要求关系模式中的每个函数依赖的左侧都必须是候选键，这样可以避免属性对候选键的部分依赖和传递依赖

第七章

数据库设计基本步骤

  1. 需求分析
  2. 概念结构设计
  3. 逻辑结构设计：设计关系模型
  4. 物理结构设计
  5. 数据库实施
  6. 数据库运行和维护

数据字典

  • 数据项、数据结构、数据流、数据存储和处理。
  • 它是关于数据库中数据的描述，即元数据，而不是数据本身

E-R图的冲突

  • 属性冲突
  • 命名冲突
  • 结构冲突

第十章：数据库恢复技术

事物的定义

事物是用户定义的一个数据库操作系列，要么全做要么全部做

1
2
3
4
BEGIN TRANSACTION;

COMMIT;
ROLLBACK;

事物的特点

  • 原子性
  • 一致性
  • 隔离性
  • 持续性

故障种类

  • 事物内部故障
  • 系统故障
  • 介质故障
  • 计算机病毒

恢复技术

数据转储和登记日志文件

  • 事物内部故障恢复：反向扫描文件日志，查找到该事务的更新操作对该事务的更新操作执行逆操作，直到读到改事务的开始标记
  • 系统故障恢复：正向扫描日志文件，找出在故障前已经提交的事务队列和未完成队列，对未完成队列执行中各个事务执行UNDO操作，对已经提交的各个事务执行REDO操作
  • 介质故障恢复：重装数据库，然后重做已完成的事物

  • UNDO是恢复到第一个失败的事务就OK了，正向做不到。REDO是恢复到最后一个成功的事务之后。

  • UNDO保证原子性，REDO保证持续性

十一章：并发控制

事物是并发控制的基本单元

  • 并发控制保证了事务的一致性和隔离性

并发控制带来

并发控制带来的数据不一致性

  • 丢失修改
  • 不可重复读
  • 读“脏”数据

活锁

当一系列封锁操作无法按照其正确顺序执行时，就可能导致事务无限等待某个封锁。

  • 避免活锁的方法就是使用FCFS

死锁

只有出现并发操作才会出现死锁

封锁协议

  • 一级封锁协议：加X锁，事务结束后释放X锁，避免丢失修改

  • 二级封锁协议：一级+ 加S锁，操作结束后释放S锁，避免读脏数据

  • 三级封锁协议：一级+ 加S锁，事务结束后释放S锁，可以重复读

并发调度的可串行性

可串行性是并发事物正确调度的准则

两段锁协议

  • 第一阶段获得封锁，不能释放任何锁
  • 第二阶段释放封锁，不能申请任何锁

实验二：SQL语言使用

一、实验内容

第一部分：

1 使用上次实验创建的数据库和表，利用企业管理器或查询分析器向基本表中插入实验数据。

第二部分：

2 查询全体学生学号和姓名

3 查询全体学生学号、姓名、性别、年龄和所在系

4 查询全体学生选课情况，即学号、课程号、成绩

5 显示所有选课学生的学号，并取掉重复行

6 查询成绩大于80分的学生的学号、课程号、成绩

7 查询计算机系全体学生的学号

8 查询计算机系年龄在18岁以上的学生的学号、姓名

9 查询选修了数据库课程或数据结构课程学生的学号、成绩

10 查询选修了C1课程的学生的学号和成绩，查询结果按成绩降序排序

11 查询每个学生的学号、姓名、选修的课程名、成绩

12 查询与‘李勇’在同一个系学习的学生学号、姓名

13 查询选修了3门以上课程的学生学号

14 查询没有选修课程的学生的基本信息

15 找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列

16查询选修课程平均成绩在80分以上的学生学号

17查询数据结构课程的先行课的先行课课程名

18 查询同时选修《数据库》和《数据结构》两门课程的学生姓名

19 查询每个系的平均年龄，并建立视图

20 建立课程的先修课程的视图

二、思考题

1、对于实验任务的完成，SQL Server 提供了哪些方法或工具？

2、请考虑SQL Server 的企业管理器和查询分析器中用SQL语句两种方法完成任务的场合和优缺点。

题目1

1 使用上次实验创建的数据库和表，利用企业管理器或查询分析器向基本表中插入实验数据。

第二部分：

题目2

2 查询全体学生学号和姓名

1
2
SELECT Sno, Sname
FROM Student;

3 查询全体学生学号、姓名、性别、年龄和所在系

1
2
SELECT Sno, Sname, Ssex, Sage, Sdept
FROM Student;

题目4

4 查询全体学生选课情况，即学号、课程号、成绩 

1
2
SELECT Sno, Cno, Grade
FROM SC;

题目5

5 显示所有选课学生的学号，并取掉重复行

1
2
SELECT DISTINCT Sno
FROM SC;

题目6

6 查询成绩大于80分的学生的学号、课程号、成绩

1
2
3
SELECT Sno, Cno, Grade
FROM SC
WHERE Grade > 80;

题目7

7 查询计算机系全体学生的学号

1
2
3
4
SELECT Sno
FROM Student
WHERE Sdept = 'CS';   --单引号

题目8

8 查询计算机系年龄在18岁以上的学生的学号、姓名

1
2
3
SELECT Sno, Sname
FROM Student
WHERE Sdept = 'CS' AND Sage > 18;  --用AND

题目9

9 查询选修了数据库课程或数据结构课程学生的学号、成绩

1
2
3
SELECT Sno, Grade
FROM SC
WHERE Cno = '1' OR Cno = '5';

题目10

10 查询选修了C1课程的学生的学号和成绩，查询结果按成绩降序排序

1
2
3
4
SELECT Sno, Grade
FROM SC
WHERE Cno = '1'
ORDER BY Grade DESC;

题目11

11 查询每个学生的学号、姓名、选修的课程名、成绩

1
2
3
4
SELECT Student.Sno, Student.Sname, Course.Cname, SC.Grade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno;

看了一下书，书上是用where语句，并没有用join

按照书上可以这样写：

1
2
3
4
SELECT Student.Sno, Student.Sname, Course.Cname, SC.Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno
  AND SC.Cno = Course.Cno;

题目12

12 查询与‘李勇’在同一个系学习的学生学号、姓名

1
2
3
SELECT Sno, Sname
FROM Student
WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = '李勇')

题目13

13 查询选修了3门以上课程的学生学号

1
2
3
4
SELECT Sno
FROM SC
GROUP BY Sno //根据学号进行分组
HAVING COUNT(Cno) > 3; //筛选学号中满足特定条件的组

HAVING 子句用于 过滤分组后的数据，通常和 GROUP BY 子句一起使用。它的作用类似于 WHERE，但不同之处在于：

  • WHERE 子句用于在 分组之前 对数据进行过滤。
  • HAVING 子句用于在 分组之后 对分组结果进行过滤。
对比维度 GROUP BY ORDER BY
作用 对查询结果按指定列进行分组。 对查询结果按指定列进行排序。
常用场景 与聚合函数一起使用，如 COUNT()、SUM()、AVG() 等。 用于控制查询结果集的显示顺序。
是否影响结果内容 是，对结果进行分组后，每组只返回一条汇总数据。 否，不改变结果内容，只改变结果的顺序。
常用子句/函数 常与 HAVING 和聚合函数（如 SUM()、COUNT()）结合使用。 常与 ASC（升序）、DESC（降序）结合使用。
位置 位于 SELECT 之后，ORDER BY 之前。 通常是查询语句的最后一个子句。
示例 sql SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept; sql SELECT Sname, Sage FROM Student ORDER BY Sage DESC;
应用的列 必须是 SELECT 中未聚合的列或计算结果。 可以是任意列或表达式，即使它不在 SELECT 中出现。
  • GROUP BY 用于对数据进行分组，并计算每个分组的汇总信息。
  • ORDER BY 用于对查询结果进行排序，不改变数据，只改变结果的排列顺序。

题目14

14 查询没有选修课程的学生的基本信息

1
2
3
SELECT *
FROM Student
WHERE Sno NOT IN (SELECT Sno FROM SC);

题目15

15 找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列

1
2
3
4
5
6
7
SELECT Student.Sdept, Student.Sno, AVG(SC.Grade) AS AvgGrade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
GROUP BY Student.Sdept, Student.Sno 
--根据学生的系别（`Sdept`）和学号（`Sno`）对结果进行分组。
HAVING AVG(SC.Grade) > 85
ORDER BY AvgGrade ASC;

//**AVG(SC.Grade) AS AvgGrade**：计算每个学生的平均成绩，并将结果命名为 AvgGrade。

  • 使用 JOIN 关键字将 Student 表和 SC 表连接在一起。
  • ON Student.Sno = SC.Sno：指定连接条件，即通过学号（Sno）将两张表关联起来。这样可以获取每个学生的成绩信息。

用where语句：

1
2
3
4
5
6
SELECT Student.Sdept, Student.Sno, AVG(SC.Grade) AS AvgGrade
FROM Student, SC 
WHERE Student.Sno = SC.Sno
GROUP BY Student.Sdept, Student.Sno 
HAVING AVG(SC.Grade) > 85
ORDER BY AvgGrade ASC;

题目16

16 查询选修课程平均成绩在80分以上的学生学号

1
2
3
4
SELECT Sno
FROM SC
GROUP BY Sno
HAVING AVG(Grade) > 80;

题目17

17查询数据结构课程的先行课的先行课课程名

用where语句：

1
2
3
4
SELECT C2.Cname AS PrePreCourse
FROM Course C1, Course C2
WHERE C1.Cpno = C2.Cno
  AND C1.Cno = '5';

题目18

18 查询同时选修《数据库》和《数据结构》两门课程的学生姓名

1
2
3
4
5
SELECT Sname 
FROM SC AS SC1
JOIN SC AS SC2 ON SC1.Sno = SC2.Sno
JOIN Student  ON SC1.Sno = Student.Sno
WHERE SC1.Cno = '1' AND SC2.Cno = '5';

JOIN SC AS SC2 ON SC1.Sno = SC2.Sno：

  • 通过这个 JOIN，我们确保 SC1 和 SC2 中的 Sno（学生编号）是相同的，表示同一个学生。

JOIN Student ON SC1.Sno=Student.Sno ：

  • 在这一步，我们将 SC 表和 Student 表连接起来，关联 SC1.Sno 和 Student 表中的 Sno。

用where语句：

1
2
3
4
5
6
SELECT Sname
FROM SC AS SC1, SC AS SC2, Student
WHERE SC1.Sno = SC2.Sno
  AND SC1.Sno = Student.Sno
  AND SC1.Cno = '1'
  AND SC2.Cno = '5';

题目19

19 查询每个系的平均年龄，并建立视图

1
2
3
4
CREATE VIEW AvgAgeByDept AS
SELECT Sdept, AVG(Sage) AS AvgAge
FROM Student
GROUP BY Sdept;
  • GREATE VIEW创建视图，后面是自定义的名称，AS表示后面讲定义这个视图的查询内容
    1
    SELECT * FROM AvgAgeByDept;

题目20

20 建立课程的先修课程的视图

1
2
3
4
5
CREATE VIEW PrerequisiteCourses AS
SELECT C1.Cno AS CourseNo, C1.Cname AS CourseName, C2.Cname AS PrerequisiteName
FROM Course C1
LEFT JOIN Course C2 ON C1.Cpno = C2.Cno;

一、实验内容

该实验项目实验内容包括：SQL数据库更新操作，定义视图和视图操作，其他操作，重点是SQL数据库更新操作。

1 向S表中添加一个学生纪录，学号为S7, 姓名为高大全，性别为男，年龄为20，系别为计算机

1
2
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('','','',20,'CS');

2 将所有学生的成绩加5

1
2
UPDATE SC
SET Grade+=5;

3 将高大全同学所在系改为‘信息’

1
2
3
UPDATE Student
SET Sdept='信息'
WHERE Sname='';

删除是delete from

4 删除姓名为高大全同学的学生纪录

1
2
DELETE  FROM Student
WHERE Sname='';

5 李晨选修了1号课程，把这个选修关系插入到基本表

1
2
3
4
INSERT INTO SC
SELECT Sno,'1',NULL
FROM Student
WHERE Sname='';

6 把没有考试成绩的学生的成绩改为30分

1
2
3
UPDATE SC
SET Grade=30
WHERE Grade is NULL;

7 把李晨1号课程的成绩改为78；

1
2
3
4
UPDATE SC
SET Grade=78
WHERE Sno=(SELECT Sno FROM Student WHERE Sname='李晨') 
	AND Cno=1;

8 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；

1
2
3
UPDATE SC
SET Grade=Grade *1.05
WHERE Cno=2 AND Grade < (SELECT AVG(Grade) FROM SC WHERE Cno=2);

9 把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉

1
2
DELETE FROM SC
WHERE Cno=2 AND Grade <(SELECT AVG(Grade) FROM SC WHERE Cno=2);

10 建立一个名为StudentSumView的视图，包含计算机系学生所有学生学号和总成绩

1
2
3
4
5
6
CREATE VIEW StudentSunView AS
SELECT Sno,SUM(Grade) AS SUMA 
FROM SC,Student
WHERE Student.sno=SC.Sno
	AND Student.Sdept='CS'
ORDER BY Sno;

11 建立选修了课程‘程序设计’并且成绩大于80分的学生的姓名、成绩

1
2
3
4
5
6
7
8
CREATE VIEW SSSSS AS
SELECT Student.Sname,SC.Grade
FROM SC,Student,Course
WHERE SC.Sno=Student.Sno
	AND SC.Cno=Course.Cno
	AND Course.Cname=''
	AND Grade > 80;

12 将“CS”系全体学生的成绩置零；

1
2
3
UPDATE SC
SET Grade=0
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept='CS');

13 删除StudentSumView视图

1
DROP VIEW StudentSumView

14 删除S所有记录

1
DELETE FROM S

15 删除S表

1
DROP TABLE S

16 删除STUDENT数据库

1
DROP DATABASE  STUDENT

二、思考题

1、怎样保持数据库中数据的完整性？

2、视图的的操作和基本表的操作有何异同点？

题目1

向S表中添加一个学生纪录，学号为S7, 姓名为高大全，性别为男，年龄为20，系别为计算机

1
2
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('S7','高大全','男','20','计算机');

题目2

将所有学生的成绩加5

1
2
UPDATE SC
SET Grade=Grade+5

也就是要更新SC表

题目3

将高大全同学所在系改为‘信息’

1
2
3
UPDATE Student
SET Sdept='信息'
WHERE Sname='高大同';

题目4

删除姓名为高大全同学的学生纪录

1
2
DELETE FROM Student
WHERE Sname='高大同';

题目5

李晨选修了1号课程，把这个选修关系插入到基本表

1
2
3
4
INSERT INTO SC(Sno,Cno,Grade)
SELECT Sno,'1',NULL
FROM Student
WHERE Sname='刘晨';

题目6

把没有考试成绩的学生的成绩改为30分

1
2
3
UPDATE SC
SET Grade=30
WHERE Grade IS NULL;

题目7

把李晨1号课程的成绩改为78；

1
2
3
UPDATE SC
SET Grade=78
WHERE Sno=(SELECT Sno FROM Student WHERE Sname='李晨') AND Cno='1';

题目8

把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；

1
2
3
UPDATE SC
SET Grade=Grade*1.05
WHERE Cno='2' AND Grade < (SELECT AVG(Grade) FROM SC WHERE Cno='2');

题目9

把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉

1
2
DELETE FROM SC
WHERE Cno='2' AND Grade < (SELECT AVG(Grade) FROM SC WHERE Cno='2');

题目10

建立一个名为StudentSumView的视图，包含计算机系学生所有学生学号和总成绩、

1
2
3
4
5
CREATE VIEW StudentSumView AS
SELECT Student.Sno, SUM(SC.Grade) AS TotalGrade
FROM Student, SC
WHERE Student.Sno = SC.Sno AND Student.Sdept = 'CS'
GROUP BY Student.Sno;

题目11

建立选修了课程‘程序设计’并且成绩大于80分的学生的姓名、成绩

1
2
3
4
5
6
7
CREATE VIEW HighGradeStudent AS
SELECT Student.Sname,SC.Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno
	AND SC.Cno = Course.cno
	AND Course.Cname='课程设计'
	AND SC.Grade>80;

题目12

将“CS”系全体学生的成绩置零

1
2
3
UPDATE SC
SET Grade=0
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept='CS');

IN 用来检查 SC 表中的 Sno 是否在这个学号列表中。

题目13

删除StudentSumView视图

1
DROP VIEW StudentSumView;

题目14

删除S所有记录

1
DELETE FROM Student;

题目15

删除S表

1
DROP TABLE Student;

题目16

删除STUDENT数据库

1
DROP DATABASE STUDENT