数据库概论复习

Create table SC(
       Sno char(9),
       Cno char(4),
       Grade smallint,
    Primary key(Sno,Cno),
    Foreign key (Sno) reference Student(Sno),
    Foreign key(Cno) reference Course(Cno));

3.1.2修改基本表

新增列：

Alter table Student add S_entrance date;

修改原属性的数据类型：

Alter table Student alter column Sage int;

新增属性约束条件：

Alter table Course add unique(Cname);

3.1.3删除基本表

Drop table Student cascade;

3.1.4建立索引

Create unique index 索引名 on Student(Sno);

3.1.5修改索引

Alter index 旧索引名 rename to 新索引名；

3.1.6删除索引

Drop index 索引名；

3.2数据查询

3.2.1单表查询

Select Sno,Sname
From Student
Where Sno='1001';
Select distinct Sno    -- distinct做去重处理
From SC;

3.2.2模糊查询 like

% 代表任意长度

_ 代表一个单位长度

数据库字符集为ASCII时一个汉字需要两个_；当字符集为GBK时只需要一个_

Select Sname,Sno,Ssex
From Student
Where Sname like '刘%'；


Select Sname,Sno,Ssex
From Student
Where Sname like '刘_'；


Select Sname,Sno,Ssex
From Student
Where Sname like '_洋%'；

3.2.3 order by 子句

Select Sno,Grade
From SC
Where Sno='3'
Order by Grade desc;

Where子句中不能用聚集函数作为条件表达式

3.2.4 嵌套查询

子查询的select语句中不能使用order by 子句，order by 子句只能对最终的查询结果排序

Select Sname
From Student
Where Sno in (
       Select Sno
       From SC
       Where Cno='2');

3.2.5 带有any (some) 或 all 谓词的子查询

> any

>= all 最大值

<= all 最小值

Select Sname,Sage
From Student
Where Sage<any (
       Select Sage
From Student
Where Sdept='CS'
) And Sdept <> 'CS';

3.2.6 带有exists谓词的子查询

此查询不返回任何数据，只返回true ，false

Select Sname
From student
Where exists
(    Select *
    From SC
    Where Sno=Student.Sno and Cno='1');

3.3数据更新

3.3.1 插入数据

插入元组

Insert into Student values('2010001','王伟','IS');

Insert into Student values('2010001','王伟','IS'),
                            ('2010002','张伟','MA'),
                            ('2010003','赵伟','CS');

插入子查询结果

Insert into Dept_age(Sdept,Avg_age)
Select Sdept,AVG(Sage)
From Student
Group by Sdept;

更新元组

Update Student
Set Sage=22
Where Sno='2020001';

删除元组

Delete
from Student
where Sno='2020001';

3.4视图

创建视图

Create view view_student
As select Sno,Sname,Sage
From Student
Where Sdept='IS';

删除视图

Drop view view_student;

更新视图

Update is_Student
Set Sname='刘晨'
Where Sno='20201202';

3.5练习

SELECT SN,AGE,SEX
FROM S
WHERE AGE>(
    SELECT AGE
    FROM S
    WHERE SN='王华');

SELECT S#
FROM SC
WHERE C#='C2' AND GRADE>=ALL(
    SELECT GRADE
    FROM SC
    WHERE C#='C2');

SELECT SN,C#,GRADE
FROM S,SC
WHERE S.S#=SC.S#;

SELECT SN,SUM(GRADE)
FROM SC
WHERE GRADE>60
GROUP BY S#
HAVING COUNT(*)>4 
ORDER BY SUM(GRADE) DESC;

INSERT INTO R VALUES(25,'李明','男',21,'95031');

INSERT INTO R(CLASS,NO,NAME) VALUES('95031',30,'郑和');

UPDATE R
SET NAME='王华'
WHERE SN=10;

UPDATE R
SET CLASS='95091'
WHERE CLASS='95101';

DELETE
FROM R
WHERE NO=20;

DELETE
FROM R
WHERE NAME LIKE '王%';

# 方法一
SELECT S.S#,SNAME
FROM S,SC
WHERE C# = (
       SELECT C#
       FROM C
       WHERE CNAME='MATHS');

# 方法二
SELECT S#,SNAME
FROM S.SC,C
WHERE S.S#=SC.S# 
    AND C.C#=SC.C#
    AND CNAME='MATHS';

(2)检查至少学习了课程号为C1和C2的学生的学号

SELECT S#
FROM SC
WHERE C#='C1' AND S# IN(
	SELECT S#
	FROM SC
	WHERE C#='C2'
);

SELECT S#,SNAME,AGE
FROM S
WHERE SEX='女' AND BETWEEN 18 AND 20;

(4)检索平均成绩超过80分的学生学号和平均成绩

SELECT S#,AVG(GRADE)
FROM SC
GROUP BY S#
HAVING AVG(GRADE)>80;

SELECT SNAME
FROM S
WHERE NOT EXISTS(
    SELECT *
    FROM C
    WHERE NOT EXISTS(
        SELECT *
        FROM SC
        WHERE S#=S.S# AND C#=C.C#));

SELECT SNAME
FROM S,SC
WHERE S.S#=SC.S#
GROUP BY SNAME
HAVING COUNT(*)>3

SELECT distinct Sno
FROM SC
WHERE Cno IN (
    SELECT Cno
    FROM SC
    WHERE Sno='1042');

create view S_CS_VIEW as
select Sno,Sname,Sex
from Student
where Sdept='CS';

Update S_CS_VIEW
Set Sname='王慧平'
where Sname='王平';

Create view datascore_view as
Select Student.Sno,Sname,Grade
From Student,SC
Where Student.Sno=SC.Sno and Cno=(
       Select Cno
       From Course
       Where Cname='数据库'
);

不要忘记做连接，不然会变成笛卡尔积

4.数据库安全性

4.1存取控制

授权：定义存取权限

4.1.1授予权限

Grant select          -- 授予的权限
On table Student  -- 对某一张表的操作
To U1;                  -- 将权限授予U1

Grant update(Sno),select     -- 授予对Student表Sno更新的操作和表Student的查询操作
On table Student
To U4;

4.1.2收回权限

Revoke update(Sno)
On table Student
From U4;

4.1.3创建用户

Create user 用户名；

4.1.4数据库角色

角色是权限的集合

Create role R1；   -- 创建角色

Grant update,insert,select    -- 使角色R1拥有update,insert,select权限
On table Student
To R1;

Grant R1        -- 将角色授予王平和张明
To 王平，张明； -- 使他们拥有R1的全部权限

Revoke R1
From 王平；

角色权限的修改

Grant delete         -- 在原来的基础上增加了delete权限
On table Student
To R1;

Revoke select        --减少了select权限
On Student
From R1;

4.2练习

REVOKE UPDATE(XH)
ON TABLE STUD
FROM ZHAO;

GRANT UPDATE(GRADE)
ON TABLE SC
TO ZHAO;

5.数据库完整性

5.1三大完整性

用户定义完整性

用check短语指定列值应该满足的条件


CREATE TABLE Student(
       Sno CHAR(9) PRIMARY KEY,
       Sname CHAR(9) NOT NULL,
        -- Student表中的Ssex只允许取‘男’或‘女’    
       Ssex CHAR(2) CHECK (Ssex IN (‘男’,’女’)),
Sage SMALLINT,
Sdept CHAR(20)
);

5.2修改完整性限制

Alter table Student
Add constraint C1 check (Sno between 900 and 999);    -- 增加对学号的限制

Alter table Student
Drop constraint C1;      -- 删除对学号的限制

域：一组具有相同数据类型的值的集合

5.3触发器

触发器又叫事件-条件-动作规则

触发器只能定义在基本表上，不能定义在视图上

5.3.1定义触发器

Delimiter $$
Create trigger T
before insert or update on teacher
For each row
Begin
       If( new.Job=’教授’) and (new.Sal < 4000)
       Then new.Sal:=4000;
       End if;
End $$
Delimiter;

5.3.2删除触发器

Drop trigger T on teacher;

6.关系数据理论

6.1规范化

关系规范化中

删除操作异常——不该删除的数据被删除

插入操作异常——应该插入的数据未被插入

6.1.1函数依赖

R(U)时U的关系模式，X,Y是U的子集

R(U)中任何一个关系中不存在在X上的属性值相等，而在Y上的属性值不相等

则称X函数确定Y，Y函数依赖于X 记作： X-->Y

例如：y=f(x) 一个x能确定唯一的y

非平凡函数依赖：X-->Y ，Y ⊈ X 例如：（Sno,Cno）--> Grade

平凡函数依赖：X-->Y , Y ⊆ X 例如：（Sno,Cno）--> Sno

完全函数依赖：如果X-->Y，并且对X的任何真子集都推不出Y 例如：（Sno,Cno） $\overset{F}{\rightarrow}$ Grade

$X\overset{F}{\rightarrow}Y$

部分函数依赖：例如： SnoàSdept,（Cno,Sno） $\overset{P}{\rightarrow}$ Sdept

传递函数依赖：例如：XàY ，Y ⊈ X，Y -/->X，Y $\rightarrow$ Z ，Z ⊈ Y

6.1.2 范式

1NF：每一个分量必须是不可分的数据项

2NF: 消除非主属性对主属性的部分函数依赖

3NF: 在2NF的基础上，消除非主属性对主属性的传递函数依赖

BCNF: 消除主属性对候选码的部分函数依赖、传递函数依赖

６.２练习

７.数据库设计

7.1 概述

7.1.1 设计步骤

1. 需求分析

数据字典是在需求分析阶段建立；

数据项是不可再分的数据单位；

数据结构反映了数据之间的组合关系；

数据流是数据结构在系统内传输的路径；

2. 概念结构设计

“实体-联系”的三个演变过程：

现实世界 $\rightarrow$ 信息世界 $\rightarrow$ 机器世界

E-R模型：用E-R图来描述现实世界的概念模型，包括实体、属性和实体之间的联系

属性不能与其他实体具有联系

合并E-R图会产生的冲突：

属性冲突——属性域冲突、属性值单位冲突
命名冲突——同名异义、异名同义
结构冲突

逻辑结构设计

E-R图向关系模型的转换：

1：1的联系——建议和一端的关系模式合并

1：n的联系——建议和n端的关系模式合并

m：n的联系——建议转换为一个独立的关系模式

物理结构设计

数据库的实施

数据库的运行与维护

练习

8.数据库编程

SQL有两种使用方式——交互式、嵌入式

对于嵌入式SQL，DBMS一般采取预编译方法处理

当主语言为C语言：

EXEC SQL<SQL语句>;

当主语言为java：

＃SQL{<SQL语句>};

８.１嵌入式SQL语句与主语言之间的通信

主变量

SQL语句中使用的主语言程序变量简称为主变量

一个主变量可以附带一个任选的指示变量（整型变量）

指示输入主变量是否为空值
值是否被截断
检测输出主变量是否为空值

所有主变量和指示变量必须在SQL语句

BEGIN DECLARE SECTION　和 END DECLARE SECTION之间说明

SQL语句中的主变量名和指示变量前要加冒号（：）作为标志

游标

是系统为用户开设的数据缓冲区

８.２使用游标的SQL语句

查询结果为多条记录的SELECT语句

说明游标--- 只起声明作用，不执行SELECT语句

EXEC SQL DECLARE <游标名>　CURSOR FOR <SELECT语句>；

打开游标 --- 执行SELECT语句，把查询结果放入缓冲区

EXEC SQL OPEN <游标名>;

推进游标指针向前推进一条记录

EXEC SQL FETCH <游标名>

INTO <主变量>．．．；

关闭游标

EXEC SQL CLOSE <游标名>；

８.３过程化SQL

过程化SQL程序的基本结构是块

常量的定义：

常量名数据类型 CONSTANT:=常量表达式

赋值语句：

变量名：=表达式

流程控制：

IF THEN

END IF;

IF THEN

ELSE

END IF;

LOOP

END LOOP;

WHILE LOOP

END LOOP;

FOR LOOP

END LOOP;

8.4存储过程

存储过程和触发器的区别：

存储过程有参数和返回值
触发器触发即可执行，无需调用

创建存储过程

CREATE PROCEDURE 过程名(参数1，参数2，…)

BEGIN

END;

执行存储过程

Call <过程名>；

8.5练习

9.查询优化

9.1代数优化

1.连接、笛卡尔积的交换律

2.连接、笛卡尔积的结合律

3. 投影的串接定律

在这里插入图片描述

4.选择的串接定律

5.选择与投影操作的交换律

在这里插入图片描述

6.选择与笛卡尔积的交换律

在这里插入图片描述

7.选择与并的交换

在这里插入图片描述
8.选择与差的交换

在这里插入图片描述
9. 投影与笛卡尔积的交换

在这里插入图片描述
10.投影与并的交换

在这里插入图片描述

9.2构造查询树

1.把用高级语言定义的查询转换为关系代数表达式
★ 以 SELECT子句对应投影操作，以FROM子句，对应笛卡尔积以 WHERE子句对应选择操作，生成原始查询树
★ SQL语句转化为原始查询树
2.把关系代数表达式转换为查询树。

注：在一个查询树中，叶子结点表示关系，内结点表示关系代数操作；查询树以自底向上的方式执行，所以把只和某个表有关的选择下移

3.利用等价转换规则反复地对查询表达式进行尝试性转换，将原始的语法树转換成“优化”的形式

10.事务的恢复技术

10.1事务的基本概念

事务是用户定义的一个数据库造作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位

事务是恢复和并发控制的基本单位

一般讲，一个程序包含多个事务

定义事务的语句一般有三条：

BEGIN TRANSACTION;
COMMIT;
ROLLBACK;

事务的ACID特性

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

10.2故障的种类

1.事务内部的故障

某个事务在运行过程中由于种种原因未运行至正常终止点

恢复：事务撤销（UNDO）

2.系统故障

由于某种原因造成整个系统的正常运行突然停止，致使所有正在运行的事务都以非正常方式终止。

恢复：清除尚未完成的事务对数据库的所有修改；将已完成事务提交的结果写入数据库

3.介质故障

硬件故障使存储在外存中的数据部分丢失或全部丢失

恢复：装入数据库发生介质故障前某个时刻的数据副本，重做自此时始的所有成功事务，将这些事务已提交的结果重新记入数据库

4.计算机病毒

人为的故障或破坏，是一种计算机程序

恢复：杀毒软件

10.3恢复的实现技术

最常用的技术是数据转储和日志文件

数据库恢复是将数据库从错误状态恢复到某一已知的正确状态的功能

11.并发控制

并发操作带来的数据不一致性：

丢失数据
不可重复读
读‘脏’数据——未提交的随后又被撤消的数据

11.1封锁

排他锁（X锁）—— T对A上X锁后，其他事务不能再对A上锁，且无法读取和修改A

共享锁（S锁）—— T对A上S锁后，其他事务只能对A再上S锁

简述三级封锁协议的内容以及不同级别的封锁协议能解决哪些数据不一致性

11.2 并发调度的可串行性

冲突操作是指不同事务对同一个数据的读写操作和写写操作

不同事物的冲突操作和同一事务的两个操作是不能交换的

11.3两段锁协议

2PL: 是指所有的事务必须分两个阶段对数据项加锁和解锁

第一阶段是获得封锁的阶段，称为扩展阶段：其实也就是该阶段可以进入加锁操作，在对任何数据进行读操作之前要申请获得S锁，在进行写操作之前要申请并获得X锁，加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。就是加锁后就不能解锁了。

第二阶段是释放封锁的阶段，称为收缩阶段：当事务释放一个封锁后，事务进入封锁阶段，在该阶段只能进行解锁而不能再进行加锁操作