数据库复习 - PART1 - 数据库基本运用

最新推荐文章于 2022-01-14 14:20:22 发布

DeadPool loves Star

最新推荐文章于 2022-01-14 14:20:22 发布

阅读量1.5k

点赞数 4

分类专栏：数据库系统文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_44465434/article/details/116448067

版权

数据库系统专栏收录该内容

3 篇文章 1 订阅

订阅专栏

0. 数据库概述

0.1 数据库系统的组成

由数据库（DB） + 数据库管理系统（DBMS）+ 数据库管理员（DBA） + 数据库应用程序（DBAP）+ 计算机系统/网络基础构成

0.2 数据库管理系统

从用户角度

DBMS就是通过数据库语言让用户操作进而提供数据库定义（DDL）、数据库操纵（DML）和数据库控制（DCL）功能的系统，同时提供了一系列程序能够实现对数据库的各种存储和维护（DBA也是用户的一种）；
从系统角度

DBMS不仅仅包含了查询实现与查询优化、事务处理、数据存储与索引功能，还包括了其他众多功能；
DBMS的功能
- 故障恢复
- 并发控制
- 通信控制
- 访问控制
- 完整性控制
- ……

1. 关系模型与关系运算

1.1 概述

关系 <=> Table（表）

1.2 关系与关系模型

关系的严格定义
1. 是一组域D1、D2……Dn的笛卡尔积的子集；
2. 或者：笛卡尔积（狭义笛卡尔积，同一关系域属性的笛卡尔积）中具有某一方面意义的那些元组被称为一个关系；
关系模式

R(A1：D1, A2：D2, …, An：Dn)，其中Ai（Attribute）表示属性，Di（Domain）表示属性的域，n表示关系的度或目；

其中，域的基数为域集合可取值个数；关系的基数为关系中元组的个数；
关系与关系模式的联系

关系模式是关系的结构，关系是某一时刻的值，可随时间变化而变化；
重要概念：
- 候选码（Candidate Key）
  
  关系中的一个属性组，能够唯一确定一个元组，且若去掉该属性组中的任一属性，它不再具有这个性质。在一个关系模式中，可能有多个候选键；
- 主键（Primary Key）
  
  当有多个候选码时，选定其中一个作为主键；
- 主属性/非主属性：
  
  包含在任何一个候选码中的属性都被称为主属性，否则被称为非主属性；
- 外键（Foreign Key）
  
  关系R的一个属性组，在关系R中为非候选码，但在关系S中为候选码，则该键为R的一个外键；
  
  可以简单理解为，我们有一个基础表User，其他所有表都必须建立在该User存在的基础上才行。例如：我的好友之类的表
关系完整性
- 实体完整性：
  
  主键不应出现空值；
- 参照完整性：
  
  外键必须满足约束，或者为空；
- 用户自定义完整性：
  
  用户针对具体的应用环境定义的完整性约束条件，例如：年龄必须是[0,200]区间内的整数；

1.3 关系代数运算

概述

并、交、差、选择σ、投影Π、笛卡尔积（广义笛卡尔积，不同关系元组间笛卡尔积）、连接、关系除；
并相容性：

属性个数相同，每个属性对应的域相同；
关系代数运算，：
- 并：
  
  并操作后去重；
- 差：
  
  R - S，R中存在，S中不存在的，即，R - R ∩ S，去重；
- 交：
  
  R ∩ S，去重；
- 广义笛卡尔积：
  
  R × S，即关系R的每一个元组和S中的每一个元组组合，若R的度为r，S的度为s，则
  
  R × S的度为r+s。注意，广义笛卡尔积的结果不会把关系R与关系S中相同属性列合并，例如R(A,B) × S(A,B)得到的结果是R×S(A,B,A,B)
- 选择：
  
  σ cond（R），其中，cond可为逻辑运算符连接或者算术运算符连接；
  
  需要着重注意运算符优先级：
  
  ()，算数运算符，单目逻辑运算符（非运算），逻辑与，逻辑或
- 投影：
  
  ΠA（R），A = {A1，A2……An}；
  
  需要注意，投影后一定要去重！！！
- 除：
  
  R ÷ S，用于回答**“查询… 全部的…”**问题；
- θ-Join（θ-连接）：
  
  R（⋈ A θ B）S ，选取关系R中的属性A与关系S中的属性B之间满足θ条件的元组构成；其实质为：
  
  σ（A θ B）R×S
  
  注意，θ-连接是基于广义笛卡尔积的，因此不需要合并关系R与关系S中相同的属性列
- 更多连接总结：
  - 等值连接（Equal Join）
    
    即θ-连接，其中θ = “=”；
  - 自然连接（Natural Join）
    
    特殊的等值连接：σ（A = A）R×S，A为关系R和关系S的共有属性组，当且仅当A1=A1, A2=A2, An = An才能连接；
    
    需要特别注意，自然连接后，需要在最终结果中去除重复的属性列
  - 外连接（Outer Join）
    
    左外连接：自然连接 + 左侧表中失配的元组；（保留左边的表）
    
    右外连接：自然连接 + 右侧表中失配的元组；（保留右边的表）
    
    全外连接：自然连接 + 两侧表中失配的元组；（两边表都保留）
    
    其余都被称为内连接：即，θ-连接、等值连接以及自然连接

2. Sql语句

2.1 概述

DML（Database Manipulation Language）, DDL（Database Definition Language）, DCL（Database Control Language）

2.2 语言

2.2.1 DDL

修饰符：

NOT NULL，属性列值非空；

UNIQUE，属性为候选键；

PRIMARY KEY，属性为主键

Create：创建 Table 或者创建 Schema

Create Table DEADPOOL(id parimary key, NAME char(12));
Alter：修改 Table 或者修改 Schema

Alter Table DEADPOOL DROP COLUMN id;
Drop：删除 Table 或者删除Schema

DROP DEADPOOL;
Exp:在学生表Student(S#,Sname,Ssex,Sage,D#,Sclass)基础上增加二列Saddr, PID
```
Alter TABLE STUDENT
ADD Saddr char(40), PID char(18);
```
将上例表中PID列的数据类型增加两个字符
```
Alter TABLE STUDENT
MODIFY PID char(20);
```
删除学生姓名必须取唯一值的约束
```
Alter TABLE STUDENt
Drop Unique(Sname)
```

2.2.2 DML

2.2.2.1 DML基本操作集合

Insert

Insert into DEADPOOL(id, name) values (1, "Star");
Update

Update DEADPOOL SET id = 2 where name = "Star"
Delete

delete from DEADPOOL where id = 1
Select

select * from DEADPOOL where name like %STAR%

2.2.2.2 DML查询专题

Select …… From …… Where，SFW语句；

注意，SELECT后的结果不能保证没有重复

Exp:求既学过001号课程, 又学过002号课程的学生的学号？

SELECT S#
FROM SC as SC1, SC as SC2
WHERE SC1.S# = SC2.S# and SC1.C# = "001" and SC2.C# = "002";

空值检查（is null）

SELECT id FROM DEADPOOL
WHERE name is not null;

去重操作（DISTINCT）
```
SELECT S#
FROM SC
WHERE SCORE > 60
```
排序操作（ORDER BY 升序 ASC，降序DESC）
```
SELECT *
FROM S
ORDER BY Sage ASC
```
模糊查询（LIKE）

匹配规则：
- %：0个或多个
- _：1个
- \：转义字符
多表联合查询

Exp:求年龄有差异的任意两位同学的姓名

SELECT S1.SName as Name1, S2.SName as Name2
FROM S as S1, S as S2
WHERE S1.Sage > S2.Sage;

Exp:求“001”号课成绩比“002”号课成绩高的所有学生的学号

SELECT S#
FROM SC as SC1, SC as SC2
WHERE SC1.S# = SC2.S# and SC1.C# = "001" and SC2.C# = "002" and SC1.Score > SC2.Score

Exp:列出没学过李明老师讲授课程的所有同学的姓名

SELECT Sname
FROM S
WHERE S.S# not in (
	SELECT DISTINCT S.S#
    FROM SC, T, C
    WHERE SC.C# = C.C# and C.T# = T.T# and T.Tname = "李明"
)

并、交、差

UNION, INTERSECT, EXCEPT；

关键字ALL说明是否保留重复元组；

Exp: 求学过002号课的同学或学过003号课的同学学号
```
SELECT S# FROM SC WHERE C# = "002"
UNION
SELECT S# FROM SC WHERE C# = "003"
```
聚集专题
- 结果计算
  
  COUNT, SUM, AVG, MAX, MIN
  
  Exp:求计算机系教师的工资总额
```
SELECT SUM(Salary)
FROM T
WHERE T.D# in (
	SELECT D#
    FROM D
    WHERE Dname = "计算机"
);
```
- 聚集函数（注意：聚集函数不可用于WHERE子句）
  
  分组 GROUP BY
  
  Exp:求每一个学生的平均成绩
```
SELECT S#, AVG(Score)
FROM SC
GROUP BY S#
```
  分组过滤：HAVING子句
  
  Exp:求不及格课程超过两门的同学的学号
```
SELECT S#
FROM SC
WHERE Score < 60
GROUP BY S#
HAVING COUNT(*) > 2;
```

子查询专题

[not] IN

θ - some

θ - all

[not] exist

相关子查询：内外查询信息相关；

Exp:求学过001号课程的同学的姓名

SELECT Sname
FROM S, SC
WHERE SC.S# = S.S# and SC.C# = "001"

or

SELECT Sname
FROM S
WHERE S.S# in (
	SELECT S#
    FROM SC
    WHERE SC.C# = "001" and SC.S# = S.S#
)

非相关查询：内外查询信息不相关；

Exp:找出001号课成绩不是最高的所有学生的学号

SELECT S#
FROM SC
WHERE C# = "001" and Score < some(
	SELECT Score
    FROM SC
    WHERE C# = "001"
);

Exp:找出张三同学成绩最低的课程号

SELECT C#
FROM S, SC
WHERE S.S# = SC.S# and S.SName = "张三" and SC.Score <= all(
	SELECT SCORE
	FROM S, SC
	WHERE S.S# = SC.S# and S.SName = "张三"
);

NOT exist例子

Exp:检索学过001号教师主讲的所有课程的所有同学的姓名

不存在任何一门001号教师主讲的课该同学没学过；

SELECT Sname
FROM S
WHERE not exists (							//不存在
	SELECT *
    FROM C									//有一门001号教师讲的课
    WHERE C.T# = "001" and not exists (		//该同学没有学过
    	SELECT *
        FROM SC
        WHERE SC.C# = C.C# and SC.S# = S.S#
    )
)

Exp:列出没学过李明老师讲授任何一门课程的所有同学的姓名

NOT 学过李明老师教授任何一门课程的所有同学姓名
NOT （不存在任何一门李明老师教授的课该同学没学过）

SELECT Sname
FROM S
WHERE S# not in (
	SELECT S#
    FROM S
    WHERE not exists (
    	SELECT *
        FROM C, T
        WHERE C.T# = T.T# and T.Tname = "李明" and not exists(
        	SELECT *
            FROM SC, T, C
            WHERE SC.S# = S.S# and T.T# = C.C# and SC.C# = C.C# 
            	  and T.Tname = "李明"
        )
    )
);

SELECT Sname
FROM S
WHERE not exists(					//不存在
	SELECT *						//学过一门李明教的课
	FROM SC, T, C
	WHERE SC.S# = S.S# and T.T# = C.C# and SC.C# = C.C# 
		  and T.Tname = "李明"
)

Exp: 列出至少学过98030101号同学学过所有课程的同学的学号

不存在98030101号同学学过的一门课程，该同学没学过

SELECT S#
FROM S
WHERE not exists(
	SELECT *
    FROM SC
    WHERE S# = "98030101" and not exists(
    	SELECT *
        FROM SC as SC1
        WHERE SC1.C# = SC.C# and SC.S# = S.S#
    )
)

2.2.2.3 DML更新专题

插入（insert）

插入硬编码
```
insert into DEADPOOL values(1, "STAR")
```

插入子查询

insert into DEADPOOL 
SELECT * 
FROM DEADPOOL

删除（delete）
```
DELETE FROM DEADPOOL WHERE id = 1
```

更新（update）

UPDATE DEADPOOL
SET name="DEADPOOL"
WHERE id > 0

Exp:删除有四门不及格课程的所有同学

DELETE FROM S 
WHERE S.S# in (
	SELECT S#
    FROM SC
    WHERE Score < 60
    GROUP BY S#
    HAVING COUNT(*) >= 4
)

Exp:将所有计算机系的教师工资上调10%

UPDATE T
SET T.salary = T.salary * 1.1
WHERE D# in (
	SELECT D#
    FROM D
    WHERE Dname = "计算机"
);

当某同学001号课的成绩低于该课程平均成绩时，将该同学该门课成绩提高5%

UPDATE SC
SET SC.Score = SC.Score * 1.05
WHERE SC.C# = "001" and Score < some(
	SELECT Avg(SC.Score)
    FROM SC
    WHERE SC.C# = "001" 
)

some代表存在

将张三同学001号课的成绩置为其班级该门课的平均成绩

思路为：选课信息拼接，找班级，课程号对应等……

UPDATE SC
SET Score = (
	SELECT Avg(SC2.Score)
    FROM S as S1, S as S2, SC as SC1, SC as SC2
    WHERE S1.Sclass = S2.Sclass and 
    	  S1.S# = SC1.S# and 
    	  S2.S# = SC2.S# and
    	  SC1.C# = SC2.C# and
    	  SC1.C# = "001" and S1.Sname = "张三"          
)
WHERE C# = "001" and S# in (
	SELECT S#
    FROM S
    WHERE Sname = "张三"
)

2.3 SQL视图

2.3.1 基本语法

create view View_PORN as(
  SELECT * 
  FROM PRON
  WHERE sexility > 10
)

2.3.2 基本性质

视图更新有时是不可逆的，例如：修改聚集函数产生的View，插入无主键视图等，所有情况如下：

如果视图的select目标列包含聚集函数，则不能更新;
如果视图的select子句使用了unique或distinct，则不能更新;
如果视图中包括了group by子句，则不能更新;
如果视图中包括经算术表达式计算出来的列，则不能更新;
如果视图是由单个表的列构成，但并没有包括主键，则不能更新;

很容易理解 !

但并非所有视图都不能更新

3. 数据库完整性与安全性控制

3.1 基本概念

完整性概念
- 广义完整性：语义完整性、并发控制、安全控制、故障恢复等；
- 狭义完整性：语义完整性；
- SQL支持约束：
  - 静态约束：
    
    列完整性：域完整性约束；（仅针对某列，例如：not null，check等）
    
    表完整性：关系完整性的约束；（不同列之间的约束，例如：Primary Key，Foreign Key等）
  - 动态约束：
    
    触发器
    
    Exp: 设计一个触发器当进行Teacher表更新元组时, 使其工资只能升不能降
```
CREATE TRIGGER teacher_chgsal 
before
update of salary(Col Name)
on Teacher(Table Name)
Referencing new row x, old row y		//要么是元组，要么是表	
for each row 
	when(x.salary < y.salary)
		begin
			raise_error();
		end
```
    Exp: 假设student(S#, Sname, SumCourse), SumCourse为该同学已学习课程的门数，初始值为0，以后每选修一门都要对其增1 。设计一个触发器完成上述功能
```
CREATE TRIGGER trigger
after
insert 
on SC
referencing new row x
for each row
begin
	UPDATE S
	SET SumCourse = SumCourse + 1
	WHERE S.S# =: x.S#;
end
```
    Exp: 假设student(S#, Sname, Sage, Ssex, Sclass)中某一学生要变更其主码S# 的值，如使其原来的98030101变更为99030131, 此时sc表中该同学已选课记录的S#也需自动随其改变。设计一个触发器完成上述功能
```
CREATE TRIGGER trigger
after
UPDATE of S#
on student
referencing new row newS, old row oldS
for each row
begin
	UPDATE SC
	SET SC.S# =: newS.S#
    WHERE SC.S# =: oldS.S#
end
```
    Exp:假设student(S#, Sname, SumCourse), 当删除某一同学S#时，该同学的所有选课也都要删除。设计一个触发器完成上述功能
```
CREATE TRIGGER trigger
after
DELETE
on student
referencing old row oldS
for each row
begin
	DELETE SC
    WHERE SC.S# =: oldS.S#
end
```
    Exp:假设Dept(D#, Dname, Dean), 而Dean一定是该系教师Teacher(T#, Tname, D#, Salary)中工资最高的教师。设计一个触发器完成上述功能
```
CREATE TRIGGER trigger
before
UPDATE of Dean
on Dept
referencing new row newD
for each row when (
	newD.Dean not in (
    	SELECT Tname
        FROM T
        WHERE T.D# =: newD.D# and Salary >= all(
        	SELECT Salary
            FROM T
            WHERE T.D# =: newD.D#
        )
    )
)
begin
	raise_error();
end
```
安全性控制
- 数据安全级别
  
  绝密（Top Secret，TS）、机密（Secret，S）、可信（Confidential，C）以及无分类（Unclassified，U）
- DBMS安全机制
  1. 存取控制，通过权限在用户之间传递，使用户自行管理数据库安全性；
  2. 强制安全机制，通过对数据和用户强制分类，使得不同类别用户能够访问不同类别数据；
- 规则表定义
  
  AccessRule（AR） ::= （S，O，t，P）
  
  S：主体；
  
  O：访问对象；
  
  t：访问权利；
  
  P：谓词；
- 访问权利
  1. 读（SELECT）
  2. 更新（INSERT、UPDATE、DELETE）
  3. 创建（CREATE、ALTER、DROP）
- 两种方式
  1. 存取控制矩阵（主体、数据对象访问权限矩阵）
  2. 视图
- 存取控制 - DCL专题
  - 基本语法
```
GRANT SELECT | UPDATE ... ON DEADPOOL(TABLE) TO USER1
```
  - 授权传播
    
    水平方向：树的分支
    
    垂直方向：树的深度
  - 授权收回
    
    当一个用户的权利被撤回时，通过其传播给其他用户的权利也将一并回收；
- 强制安全性机制
  - 访问规则：
    
    用户S，不能读取对象O，除非LEVEL（O）<= LEVEL（S）；
    
    用户S，不能写对象O，除非LEVEL（O）>= LEVEL（S）；
    
    如何理解？
    
    事实上，如果权限比O高的人写了O，于是所有 LEVEL（O）<= LEVEL（USER）<= LEVEL（S）的人都能够读取到数据O，然而，LEVEL（USER）按道理来说不应该看到LEVEL（S）的改动。
  - 拓展关系模式实现强制安全性机制
    
    R(A1: D1, C1, A2: D2, C2…, An:Dn, Cn, TC)
    
    其中，C1，C2……Cn分别为属性D1，……Dn的安全分类特性；TC为元组的分类特性，如下图所示：