数据库系统概论--MySQL

23DAY

已于 2022-12-16 21:05:08 修改

阅读量2k

点赞数 2

分类专栏： SQL 文章标签： sql mysql

于 2021-11-23 11:04:49 首次发布

本文链接：https://blog.csdn.net/TengChuanB/article/details/121488680

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

数据库系统概论--MySQL

一、简介
二、关系数据库
三、SQL
四、视图和存储过程
五、数据库安全性
六、数据库完整性
七、事务
八、关系查询处理和查询优化
九、并发控制
十、MySQL语句例题

博客链接：https://www.blog.23day.site/articles/55

一、简介

1.数据库系统概论

四个基本概念：数据、数据库、数据库管理系统、数据库系统

1.数据（data）

数据是数据库中存储的基本对象

2.数据库（DB）

数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合

3.数据库管理系统（DBMS）

数据库管理系统是位于用户与操作系统之间的一层数据管理软件，例如navicat等

数据库管理系统和操作系统一样是计算机的基础软件，但是数据库管理系统是构建在操作系统之上的系统软件

DBMS将数据库以表的形式呈现给用户，受理用户的数据操作请求，对数据库进行操作

用户访问数据库的基本过程就是先建立一个与DBMS的连接然后发送数据操作请求，等待DBMS的执行结果。用户完成数据操作之后，关闭连接，向DBMS表明数据操作完成

主要功能：

数据定义（DDL）：对数据库中的数据对象的组成与结构进行定义
数据组织、存储、管理
数据操纵（DML）：操纵数据，实现对数据库的基本操作
数据库事务管理和运行管理
数据库的建立和维护

4.数据库系统（DBS）

数据库系统是由数据库、数据库管理系统、应用程序和数据库管理员组成的存储、管理、处理、维护数据的系统

用户并不直接和DBMS交互，而是通过数据库应用程序来完成对数据库的访问

存储数据库并运行数据库管理系统软件的计算机被称作数据库服务器

其中数据库应用程序功能：

为用户提供表达数据操作的界面
以SQL语言表达数据操作，向数据库服务器发出请求
显示用户所做数据操作的执行结果

操作系统将数据存放在文件中，而DBMS则将数据存放在表中。操作系统提供访问文件的编程接口，DBMS提供访问表的编程接口。磁盘上可存储多个文件，数据库中可以有多个表。文件是对操作系统的用户而言，表是对DBMS的用户而言。文件是连续的字节流，而表是类的实例的集合。DBMS内部要做的个工作就是把文件塑造成表，正如操作系统内部要将磁盘块塑造成文件一样。一个数据库可存放在一个文件中。在这种方案中，文件被DBMS分成多个段，每个段存放一个表。每个段分成多个节，每个节存储表中的一行数据。每个节再分成多个片，每个片存储一行数据中的一个列。有的DBMS则是为每个表都创建一个文件

在这里插入图片描述

2.数据模型

数据模型分为两类：概念模型（主要用于数据库设计）、逻辑模型（主要用于数据库管理系统的实现）、物理模型（数据库设计人员要了解）

概念模型：信息模型

逻辑模型：主要包括层次模型、网状模型、关系模型、半结构化数据模型、关系数据模型（最流行）等

物理模型：对数据最底层的抽象

1.概念模型

主要分为一下概念：

实体（行）

客观存在并可相互区别的事物
属性（列）

实体所具有的某一特性
码（key）

唯一标识实体的属性集
实体型
实体集
联系

2.数据模型的组成要素

数据模型是严格定义一组概念的集合

数据结构

数据结构描述数据库的组成对象以及对象之间的联系
数据操作

数据操作是指对数据库中各种对象的实例允许执行的操作的集合，包括操作及相关的操作规则
数据的完整性约束条件

数据的完整性约束条件是一组完整性规则

3.关系模型

关系数据库是现代的重点

关系模式要求关系必须是规范化的（范式）

3.数据库系统结构

模式（schema）是数据库中全体数据的逻辑结构和特征的描述

模式是相对稳定的，而实例是相对变动的 —> 数据库设计出来就是那样了，但是插入的数据是多样的，但必须满足约束

1.数据库的三级模式结构

在这里插入图片描述

1.模式

模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图

一个数据库只有一个模式（在MySQL中schema和database没有区别）

2.外模式

外模式也称用户模式，他是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

外模式是模式的子集

对于模式中的同一数据，在外模式中的结构、类型、保密级别等都可以不同

同一外模式可以为某一用户的多个应用系统所使用，但一个应用程序只能使用一个外模式

外模式是保证数据库安全性的一个有利措施

每个用户只能看到和访问所对应的外模式中的数据，对数据库中的其余数据是不可见的

3.内模式

内模式也称存储模式，一个数据库只有一个内模式。他是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式（记录的存储：按照某个属性升序存储、按照某个属性聚簇存储等；索引组织：B+树、hash）

2.数据库的二级映象功能与数据独立性

数据库的三级模式是数据的三个抽象级别

数据库管理系统在三级模式之间提供了两层映像：外模式/模式映像，模式/内模式映像

这两层映像保证了数据库系统中的数据能够具体较高的逻辑独立性和物理独立性

1.外模式/模式映像

当模式改变时，由数据库管理员对各个外模式/模式映像作响应改变，可以使外模式保持不变，从而应用程序不必修改，保证了数据与程序的逻辑独立性

2.模式/内模式映像

数据库中只有一个模式，也只有一个内模式，因此模式/内模式映像是唯一的，它定义了数据全局逻辑结构与存储结构之间的对应关系

当数据库存储结构改变时，由数据库管理员对模式/内模式映像作响应改变，可以使模式保持不变，从而应用程序不必改变。

3.注意：

在数据库的三级模式结构中，数据库模式即全局逻辑结构是数据库的中心与关键，它独立于数据库的其他层次。因此设计数据库模式结构时应首先确定数据库的逻辑模式

内模式依赖于数据库的全局逻辑结构，但独立于数据库的用户视图（外模式），也独立于具体的存储设备。它是将全局逻辑结构中所定义的数据结构及其联系按照一定的物理存储策略进行组织，以达到较好的时间与空间效率

外模式面向具体的应用程序，它定义在逻辑模式之上，但独立于存储模式和存储设备。当应用需求发生较大变化，相应的外模式不能满足其视图要求时，该外模式就得做相应改动，所以设计外模式时应充分考虑到应用的扩充性

特定的应用程序是在外模式描述的数据结构上编制的，它依赖于特定的外模式，与数据库的模式和存储结构独立。不同的应用程序有时可以共用同一个外模式

数据库的二级映像保证了数据库外模式的稳定性，从而从底层保证了应用程序的稳定性，除非应用需求本身发生变化，否则应用程序一般不需要修改

最重要的思想就是**API**

4.数据库系统组成

1.硬件平台及数据库

足够大内存，存放操作系统、数据库管理系统的核心模块、数据缓冲区和应用程序
足够大磁盘或磁盘阵列等设备存放数据库，足够大磁带作数据备份
系统有较高的通道能力，提高数据传送率

2.硬件

数据库管理系统，为数据库的建立、使用和维护配置的系统软件
支持数据库管理系统运行的操作系统
具有与数据库接口的高级语言及其编译系统，便于开发应用程序
以数据库管理系统为核心的应用开发工具
为特定应用环境开发的数据库应用系统

3.人员

数据库管理员
系统分析员和数据库设计人员
应用程序员
用户

5.数据库应用程序的模式

传统的数据库应用程序采用C/S（Client Server，客户端服务器）模式开发。源程序经编译链接后生成一个二进制可执行文件（app
），要安装在用户的机器上。

现代数据库采用B/S（Browser Server，浏览器服务器）模式。应用程序分为前后端，安装在web服务器上。

二、关系数据库

1.关系数据结构及形式化定义

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wTeWbYNE-1637636387217)(C:\Users\guo'tian'hao\AppData\Roaming\Typora\typora-user-images\image-20211120172130904.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uoJvZp6G-1637636387221)(C:\Users\guo'tian'hao\AppData\Roaming\Typora\typora-user-images\image-20211120172148155.png)]$

以这个数据库为例，介绍有关概念

员工的编号列的值有唯一性，编号叫做员工表的**主键（primary key）**

员工表中有部门编号，表示员工与部门的**联系（relationship）**

员工表中有部门编号，而部门编号是部门表的主键，那么这个属性叫做**外键（foreign key）**

如果表中某几个属性（列）的值能唯一的标识一个实体，而其子集不能，则该属性组为**候选码**

如果一个表中有多个候选码，则选定一个为**主键**

给定术语：

表：关系
列：属性、字段
行：元组

2.关系的完整性

关系模型中有三类完整性约束：实体完整性、参照完整性

1.实体完整性（主键约束）

主键不能为null

主键不能重复

不允许没有指明主键的表

2.参照完整性

外键的值必须存在

如果包含外键，则一定要指明

3.约束

1.域约束

对于一个表，其中每个字段都有域约束要求

性别只能是男或女
年龄必须在一定范围内
是否可以为null

2.业务规则约束

对于企业数据库，数据必须满足企业的业务规则

学生学期总学分不超过25
员工薪资不超过10000
教师不能同时给3个班上课

4.关系数据理论

1.数据依赖

函数依赖：对于x，y，给定x的值则y值也确定了
- 传递函数依赖：x决定y，y决定z，于是x决定z
- 完全函数依赖：给定（x，y）决定z，如果x不能单独确定z
- 部分函数依赖：给定（x，y）决定z，如果x能单独确定z
多值依赖：对于一个元组（x，y）决定z，但仅仅x与z有关，y无关

2.异常问题

数据冗余
更新异常：当更新某一数据时，会付出很大代价
插入异常
删除异常

3.范式

根据阿里规范：关联查询的表不得超过三张表

考虑商业化的需求和目标，数据库的性能更加重要，我们有时会故意在表中增加一些冗余的字段来提高性能

1.第一范式

每一个分量必须是不可再分的数据项

2.第二范式

消除非主属性对码的部分函数依赖

确保数据库中的每一列都和所有主键相关，而不能只与某一部分主键相关

3.第三范式

消除非主属性对码的传递函数依赖

确保数据表中的每一列数据都和主键之间相关，而不能间接相关

4.BCNF

消除主属性对逐渐的部分与传递函数依赖

5.第四范式

消除多值依赖

三、SQL

1.分类

关系型数据库：(SQL)：
- MySQL, Oracle, SQL Server, DB2, SQLite
- 通过表和表之间，行和列之间的关系进行数据的存储
非关系型数据库：(NoSQL) Not Only SQL
- Redis, MongDB
- 对象存储，通过对象自身的属性来决定。

2.概念

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f90ZNWs9-1637636387225)(C:\Users\guo'tian'hao\AppData\Roaming\Typora\typora-user-images\image-20211120175557122.png)]$

基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。

一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构对最终用户是隐蔽的。

视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中，因此视图是个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。

3.启动关闭连接

net start -- 查看mysql80是否开启
net start mysql80 -- 开启
net stop mysql80  -- 关闭

mysql -u 用户名 -p密码  -- 连接数据库
quit  -- 退出数据库

4.操作数据库

数据库里的操作是大小写不敏感的

-- 创建数据库（模式）
CREATE DATABASE IF NOT EXISTS mysql01; 
-- 删除数据库（模式）
DROP DATABASE IF EXISTS mysql01;
-- 使用数据库
USE mysql01;
-- 查看数据库
SHOW DATABASES;

5.变量类型

数值：
- int：标准的整数 4字节
- float：浮点数 4字节
- double：浮点数 8字节
字符串：
- char：字符串长度大小固定 0~55
- varchar：可变字符串 0~65535
- text：文本串 2^16-1
时间：
- data：YYYY-MM-DD
- time：HH:mm:ss
- datatime：YYYY-MM-DD HH:mm:ss
- timestamp：时间戳 1970.1.1到现在的毫秒

6.字段属性

每个表都必须存在五个字段：
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
unsigned：无符号整数
zerofill：int(3) 1–>001
auto_increment：自增，自动在上一条记录的基础上+1。可以设置起始值和步长。通常用来设计主键
NULL not null：非空

7.表

数据表引擎（ENGINE）：INNODB和MYISAM

创建表

-- 格式
CREATE TABLE [IF NOT EXISTS] `表名`(	
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
PRIMARY KEY(`id`)
)[表类型][表字符集设置][注释]

-- 举例
CREATE TABLE IF NOT EXISTS `student` (    	
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

修改表

ALTER TABLE <表名> [修改选项] -- 其中选项语法如下 
{ 
  ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> 
}

-- 举例：
-- 修改表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段
ALTER TABLE teacher ADD age INT(11)
-- 修改表的字段  
ALTER TABLE teacher1 MODIFY age VARCHAR(10)
-- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1)
-- 删除表的字段
ALTER TABLE teacher1 DROP age1

8.CRUD

insert

-- insert into `表名` (`字段1`,`字段2`,`字段3`) values('值1'),('值2'),('值3')
INSERT INTO `user` (`id`) VALUES ('1'),('2');-- 匹配一个字段,插入两个值
INSERT INTO `user` (`name`,`id`,`pwd`) VALUES('a','1','a');-- 匹配多个字段,插入一个值
INSERT INTO `user` (`name`,`id`,`pwd`) VALUES('a','1','a'),('b','2','b');-- 匹配多个字段,插入多个值

update

-- UPDATE `表名` SET `字段名`='value',[`字段名`='value',...] where [条件]
UPDATE `user` SET `pwd`='a' WHERE `name` = 'a';

delete

-- delete from 表名 [where 条件]
DELETE FROM `user` WHERE id=1;

select

-- SELECT 字段,[字段2],[字段3] FROM 表
-- 数据库的列可以是表达式：文本值，列，null，函数，计算表达式，系统变量…
SELECT `id`+ 1 AS 编号,`name` AS 姓名 FROM user;
-- 顺序
SELECT * FROM user;
[left | right | inner join table_name]  --联合查询
[where ...]  	-- 指定结果满足的条件
[group by ...]  -- 指定结果按照哪几个字段来分组
[having]    -- 过滤分组的记录必须满足的次要条件
[order by ...]  -- 指定查询记录按一个或多个条件排序
[limit {[offset,]row_count | row_countOFFSET offset}];

9.其他语法

操作符

操作符	含义	范围	结果
=	相等	5=6	false
<>或!=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于等于	5<=6	true
>=	大于等于	5>=6	false
BETWEEN … AND …	在某个范围内，闭合区间	[2,5]	true
AND	相当于 &&	5>1 and1>2	false
OR	相当于 \|\|	5>1 or 1>2	true

运算符

运算符	语法	描述
is null	a is null	如果操作符null，则为真
is not null	a is not null	如果操作符为 not null ，则为真
between	a between b and c	若a在b和c之间，则为真
like	a like b	%(代表0到任意个字符) ，_(代表一个字符)
in	a in(a2,a3,a4…)	假设a在a2,a3…其中的某一个值中，则结果为真

where

SELECT * FROM `user` where 'id' BETWEEN 1 AND 3 AND 'name' != 'a';
SELECT * FROM `user` where 'name' like '%a_' AND 'id' in (1,2,3);
SELECT * FROM `user` where 'name' IS NOT NULL OR 1 = 1;

join
分为三种操作：

left join
right join
inner join

每种操作都可以用where限制查询条件
在这里插入图片描述

-- 嵌套查询
SELECT s.`studentno`, `studentname`
FROM `student` s
         INNER JOIN `result` r
                    ON s.`studentno` = r.`studentno`
         INNER JOIN `subject` sub
                    ON r.`subjectno` = sub.`subjectno`
WHERE `SubjectName` = 'a'
  AND `studentresult` > 80
ORDER BY `studentresult`
        DESC;
        
-- 子查询
SELECT s.`studentno`, `studentname`
FROM `student` s
         INNER JOIN `result` r
                    ON s.`studentno` = r.`studentno`
WHERE `studentresult` > 80
  AND `subjectno` = (
    SELECT `subjectno`
    FROM `subject`
    WHERE `SubjectName` = 'a')
ORDER BY `studentresult` DESC;

order by

-- 升序ASC(默认)，降序DESC
SELECT * FROM `user`  where 'id' BETWEEN 1 AND 5 ORDER BY 'id' DESC;

limit

-- limit(查询的起始下标,pageSize)
SELECT * FROM `user`  where 'id' IS NOT NULL limit 0,2;

group by
```
SELECT * from user GROUP BY 'name';
```

10.函数

常用函数

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(6.6) -- 向上取整
SELECT FLOOR(6.6) -- 向下取整
SELECT RAND()  -- 返回一个0~1的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1  正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('length') -- 字符串长度
SELECT CONCAT('con','cat')  -- 拼接字符串
SELECT INSERT() -- 从某个位置开始替换某个长度
SELECT LOWER() -- 转小写
SELECT UPPER() -- 转大写
SELECT INSTR() -- 返回字符第一次出现的索引
SELECT REPLACE() -- 替换出现的指定字符
SELECT SUBSTR() -- 返回指定的字符串(原字符串、截取的位置、截取的长度)
SELECT REVERSE() -- 反转字符串-- 时间和日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW()  -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW) -- 获取当前日期的年份
SELECT SYSTEM_USER()  -- 获取当前用户
SELECT USER()SELECT VERSION()

聚合函数

SELECT COUNT(studentname) FROM student; -- count(指定字段)，会忽略null值
SELECT COUNT(*) FROM student; -- 不会忽略null值
SELECT COUNT(1) FROM result; -- 不会忽略null值
SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均分 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`

四、视图和存储过程

1.视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据。

-- 创建视图
CREATE VIEW <视图名> [(列名)]
AS <子查询>
[WITH [CASCADED | LOCAL] CHECK OPTION]

-- 查询视图
DESCRIBE <视图名>

-- 删除视图
DROP VIEW [IF EXISTS] <视图名> [, view_name2]

关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等效的对基本表的查询，然后在执行修正了的查询。这个转换过程叫做视图消解

2.存储过程

应用程序或者用户见到的是存储过程，视图，而不是表

有很强的适应性，通用性

能够将业务规则约束，放在存储过程中加以实现

CREATE PROCEDURE my_students(@semesterV IN VARCHAR, @courserV IN VARCHAR, @teacherV IN VARCHAR）AS
BEGIN
    SELECT studentNo, name, class 
    FROM student AS S, enroll AS E, course AS C, teacher AS T
    WHERE S.studentNo = E.studentNo 
                   AND C.courseNo = E.courseNo 
                   AND T.teacherNo =E.teacherNo 
                   ANDＣ.name = @courseV 
                   AND  Ｅ.semester =@semesterV 
                   AND T.name=@teacherV;
END; 
                             
CALL my_students('a','b','c');

3.差异

在用户看来，视图是一个表，存储过程是一个具备特定功能的函数

视图既然是一个表，用户就可在其上进行五大数据操作；对存储过程，则不具备这种特性；

存储过程尽管它的功能固定，但是带有变量，因此具有适配性，能够满足不同参数下的同一功能要求；

五、数据库安全性

数据库的安全性是指保护数据库防止不合法使用所造成的数据泄露、更改和破坏

1.数据库的不安全因素

非授权用户对数据库的恶意存取和破坏
数据库中重要或敏感的数据被泄露
安全环境的脆弱性

2.数据库安全性控制

下面讨论与数据库有关的安全性，主要包括用户身份鉴别、多层存取控制、审计、视图和数据加密等安全技术

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nUP4vgml-1637636387261)(C:\Users\guo'tian'hao\AppData\Roaming\Typora\typora-user-images\image-20211122172427255.png)]$

首先先给出数据库角色的概念：

数据库觉得是被命名的一组与数据库操作相关的权限，角色是权限的集合

-- 创建角色
GRANT ROLE <角色名>

-- 给角色授权
Grant <权限>[,<权限>]...
ON <对象类型>对象名
TO <角色>[,<角色>]

-- 讲角色授予其他角色或用户
GRANT <角色1>[,<角色2>]
TO <角色3>[,<用户1>]
[WITH ADMIN OPTION]

--角色权限的收回
REVOKE <权限>[,<权限>]
ON<对象类型><对象名>
FROM<角色>[,<角色>]

1.用户身份鉴别

1.静态口令鉴别

常用的鉴别方法

例如：平常各个app、网站用的个人密码

2.动态口令鉴别

较为安全的鉴别方式，一次一密

例如：短信密码、动态令牌方式

3.生物特征鉴别

通过生物特征进行认证

例如：指纹、虹膜、掌纹

4.智能卡鉴别

智能卡是一种不可复制的硬件，内置集成电路的芯片

2.存取控制

存取控制机制主要包括定义用户权限和合法权限检查两部分

定义用户权限和合法权限检查机制一起组成了数据库管理系统的存取控制子系统

1.自主存取控制

C2级数据库管理系统支持自主存取控制：用户对不同数据库对象有不用的存取权限，不同的用户对同一对象也有不同的权限，用户还可以将自己拥有的权限转授给其他用户

用户权限的两个要素组成：数据库对象和操作类型

存取控制的对象不仅有数据本身（基本表中的数据、属性列上的数据），还有数据库模式（包括模式、基本表、视图、索引的创建等）

其中，主要的存取权限如下：

授权：

1.授予

Grant <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]
TO <用户>[,<用户>]
[WITH GRANT OPTION]

如果指定了**[WITH GRANT OPTION]**语句，则获得某种权限的用户还可以把这种权限再授予其他角色，否则则不能传播该权限

SQL标准规定不允许循环授权

2.收回

REVOKE<权限>[,<权限>]
ON<对象类型><对象名>[,<对象类型><对象名>]...
FROM<用户>[,<用户>]...
[CASCADE|RESTRICT]

默认为CASCADE，也就是自动执行级联操作。

把传播出去的权限全部回收，包括人传人的权限

2.强制存取控制

B1级的数据库管理系统支持强制存取控制：每一个数据库对象被标以一定的密级，每个用户也被授权某个级别的许可证。具有合法许可证的用户才可以存取

强制存取控制适用于那些对数据有严格而固定密级分类的部门，例如军事部门或政府部门

这里给定定义：

主体：系统中的活动实体。包括数据库管理系统所管理的实际用户，代表用户的各进程

客体：系统中的被动实体。包括文件、基本表、索引、视图等

对于主体和客体，DBMS为他们每个实例都指派一个敏感度标记

敏感度标记被分为若干级别：绝密（TS）、机密（S）、可信（C）、公开（P）

密级次序为TS>=S>=C>=P

主体的敏感度标记称为许可证，客体称为密级

给出如下规则：

仅当主体的许可证级别大于或等于客体的密级时，主体才能读取相应的客体
仅当主体的许可证级别小于或等于客体的密级时，主体才能写相应客体

3.视图机制

通过视图机制把要保密的数据对无权存取的用户隐藏起来

4.审计

审计功能把用户对数据库的所有操作自动记录下来放入审计日志。审计员可以利用审计日志监控数据库中的各种行为，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间、内容等

5.数据加密

加密的基本思想是根据一定的算法将原始数据变化为不可直接识别的格式。

数据加密主要包括存储加密和传输加密

1.存储加密

透明存储加密：内核级加密保护方式，对用户完全透明
非透明存储加密：通过多个加密函数实现

2.传输加密

在C/S架构中，数据库用户与服务器之间信息传输存在安全隐患

链路加密：对传输数据在链路层加密
端到端加密：发送端加密，接收端解密

六、数据库完整性

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

1.实体完整性（主键约束）

主键不能为null

主键不能重复

不允许没有指明主键的表

2.参照完整性

外键的值必须存在

如果包含外键，则一定要指明、

3.用户定义完整性

1.属性上的约束条件

NOT NULL：列值非空
UNIQUE：列值唯一
CHECK：检查列是否满足条件表达式

2.元组上的约束条件

CHECK

4.触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程

因此业务规则可以利用触发器来定义

CREATE [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name 
FOR EACH ROW 
[trigger_order]
trigger_body

-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

BEFORE和AFTER：指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次

Tigger event：

INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)
UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发
DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发

七、事务

1.事务基本概念

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

COMMIT：提交事务的所有操作。将事务中所有对数据库的更新写回到磁盘上的雾里数据库中去，事务正常结束
ROLLBACK：回滚，事务运行过程中发生了某种故障，事务不能继续执行，系统将对事务中对数据库的所有已完成的操作全部不撤销，回滚到事务开始时的状态

ACID特性

原子性（atomicity)：要么都成功，要么都失败
一致性（consistency)：事物前后得数据完整性要保证一致
持久性（durability）：事物一旦提交则不可逆，被持久化到数据库中
隔离性（isolation）：事物得隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干预，事务之间要相互隔离

2.故障的种类

1.事务内部的故障

事务内部更多的故障是非预期的，是不能由应用程序处理的，如运算溢出、并发事务发生死锁而被选中撤销该事务等

事务故障意味着事务没有到达预期终点，因此需要强行回滚该事务。这类恢复操作称为事务撤销（UNDO）

2.系统故障

系统故障是指的造成系统停止运行的任何事务，使得系统要重新启动。例如，CPU故障、操作系统故障、DBMS代码错误等。

这类故障影响正在运行的所有事务，但不破坏数据库。此时主存内容，尤其是数据库缓冲区(在内存)中的内容都被丢失，所有运行事务都非正常终止。发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，从而造成数据库可能处于不正确的状态。为保证数据一致性，需要清除这些事务对数据库的所有修改。恢复子系统必须在系统重新启动时让所有非正常终止的事务回滚，强行撤销所有未完成事务。

另一方面，发生系统故障时，有些已完成的事务可能有一部分甚至全部留在缓冲区，尚未写回到磁盘上的物理数据库中，系统故障使得这些事务对数据库的修改部分或全部丢失，这也会使数据库处于不一致状态，因此应将这些事务已提交的结果重新写入数据库。所以系统重新启动后，恢复子系统除需要撤销所有未完成的事务外，还需要重做(REDO)所有已提交的事务，以将数据库真正恢复到一致状态。

3.介质故障

系统故障常称为软故障(soft crash)，介质故障称为硬故障(hard crash)。

4.故障总结

各类故障对数据库的影响有两种可能性，一是数据库本身被破坏，二是数据库数据不正确

恢复的基本原理十分简单，就是根据存储再系统别处的冗余数据来重建数据库。

3.恢复的实现技术

恢复机制涉及两个问题：建立冗余数据、利用冗余数据实施数据库恢复

建立冗余数据最常用的技术就是数据转储和登记日志文件

1.数据转储

定义：数据库管理员定期地将整个数据库复制到磁盘或磁带等存储介质上保存起来

静态转储：系统中无运行事务时进行的转储操作

动态转储：转储期间允许对数据库进行存取或修改，必须把转储期间各事务对数据库的修改活动记下来，建立日志文件，这样副本加日志就能恢复到某一时刻的正确状态

海量转储：每次转储全部数据库

增量转储：每次只转储上一次转储后更新的数据

2.日志文件

1.日志文件的格式和内容

日志文件是用来记录事务对数据库的更新操作的文件

登记记录：

各个事务的开始（BEGIN TRANSACTION）
各个事务的结束（COMMIT或ROLLBACK）
各个事务的所有更新操作

日志记录内容：

事务标识
操作类型
操作对象
更新前数据的旧值
更新后数据的新值

2.日志文件的作用

事务故障恢复和系统故障恢复
动态转储时建立日志文件

3.登记日志文件

原则：

登记的次序严格按并发事务执行的时间次序
必须先写日志文件，后写数据库

4.恢复策略

1.事务故障的恢复

事务故障是指的事务在运行至终点前被终止，这时恢复子系统应利用日志文件撤销事务已对数据库进行的修改

反向扫描日志文件，查找该事务的更新操作
对该事务的更新操作执行逆操作
继续反向扫描日志文件
直到读到此事务的开始标志

2.系统故障的恢复

正向扫描日志文件，找出在故障发生前已经提交的事务（有BEING和COMMIT），将其事务标识写入重做队列（REDO LIST）。同时找出故障发生时尚未完成的事务（只有BEGIN），将其失误标识写入撤销队列（UNDO LIST）
对撤销队列做撤销操作
对重做队列做重做操作

3.介质故障的恢复

重装数据库，重做已完成的事务

5.具有检查点的恢复技术

利用日志技术进行数据库恢复时,恢复子系统必须搜索日志，确定哪些事务需要重做,哪些事务需要撤销。一般来说,需要检查所有日志记录。这样做有两个问题，一是搜索整个日志将耗费大量的时间，二是很多需要重做处理的事务实际上已经将它们的更新操作结果写到了数据库中，然而恢复子系统又重新执行了这些操作，浪费了大量时间。为了解决这些问题，发展了具有检查点的恢复技术。这种技术在日志文件中增加一类新的记录——检查点（ checkpoint ）记录，增加一个重新开始文件，并让恢复子系统在登录日志文件期间动态地维护日志。

检查点记录：

建立检查点时刻所有正在执行的事务清单
这些事务最近一个日志记录的地址

动态维护日志文件的方法是，周期性的执行建立检查点、保存数据库状态的操作

系统使用检查点进行恢复的方法：

从重新开始文件中找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点记录。
由该检查点记录得到检查点建立时刻所有正在执行的事务清单 ACTIVE － LIST 。这里建立两个事务队列:
- UNDO - LIST :需要执行 UNDO 操作的事务集合
- REDO - LIST :需要执行 REDO 操作的事务集合
把 ACTIVE - LIST 暂时放入 UNDO - LIST 队列， REDO 队列暂为空
从检查点开始正向扫描日志文件。
- 如有新开始的事务 T ，把 T 暂时放入 UNDO - LIST 队列
- 如有提交的事务 T ，把 T 从 UNDO - LIST 队列移到 REDO - LIST 队列：直到口志文件结束
对 UNDO - LIST 中的每个事务执行 UNDO 操作，对 REDO - LIST 中的每个事务执行 REDO 操作

6.数据库镜像

随着技术的发展，磁盘容量越来越大，价格越来越便宜。为避免磁盘介质出现故障影响数据库的可用性，许多数据库管理系统提供了数据库镜像( mirror )功能用于数据库恢复。即根据数据库管理员的要求，自动把整个数据库或其中的关键数据复制到另一个磁盘上，每当主数据库更新时，数据库管理系统自动把更新后的数据复制过去，由数据库管理系统自动保证镜像数据与主数据库的一致性。这样，一旦出现介质故障，可由镜像磁盘继续提供使用，同时数据库管理系统自动利用镜像磁盘数据进行数据库的恢复，不需要关闭系统和重装数据库副本。在没有出现故障时，数据库镜像还可以用于并发操作，即当一个用户对数据加排他锁修改数据时，其他用户可以读镜像数据库上的数据，而不必等待该用户释放锁。

由于数据库镜像是通过复制数据实现的，频繁地复制数据自然会降低系统运行效率，因此在实际应用中用户往往只选择对关键数据和日志文件进行镜像，而不是对整个数据库进行镜像

八、关系查询处理和查询优化

1.查询处理

查询分析

首先对查询语句进行扫描、语法分析和词法分析
查询检查

对合法的查询语句进行语义检查，即根据数据字典中有关的模式定义检查语句中的数据库对象是否存在和有效、根据数据字典用的用户权限和完整性约束定义对用户的存取权限进行检查
查询优化

代数优化：关系代数表达式的优化

物理优化：存取路径和底层操作算法的选择
查询执行

2.实现查询操作的算法示例

1.全表扫描

假设可以使用的内存为M块：

按照物理次序读表的M块到内存
检查内存的每个元组t，如果满足选择条件，则输出t
如果表中还有其他块未被处理，则重复1、2

全表扫描只需要很少的内存就可以进行，而且控制简单，但是算法效率低

2.索引扫描

通过索引找到满足条件的元组指针，再通过元组指针在查询的基本表中找到元组

3.连接操作的实现

1.嵌套循环

最简单可行的算法

对外层循环的每个元组，检索内层循环的每个元组，并检查两个元组在连接属性上是否相等

2.排序合并

等值连接常用的算法

如果参与连接的表没有排好序，首先对表按照连接属性排序
取a表的第一个元组，依次扫描b表具有相同连接属性的元组，把他们连接起来
当扫描到连接属性不同的b表的第一个元组时，返回a表扫描他的下一个元组，再次扫描b表具有相同连接属性的元组，并连接

这样a、b表都只要扫描一遍即可

3.索引连接

在b表上已经建立了连接属性的索引
对a表的每一个元组，由连接属性通过索引查找对应的元组
把这些元组连接起来

2.查询优化

查询优化的优点不仅在于用户不必考虑如何最好的表达查询以获得较高的效率，而且在于系统可以比用户程序的优化做的更好

在集中式数据库中：总代价=I/O代价+CPU代价+内存代价+通信代价

3.代数优化

代数优化的策略是通过对关系代数表达式的等价变化来提高查询效率

1.查询树的启发式优化

选择运算尽可能先做
投影运算和选择运算同时进行
投影同其前或后的双目运算结合
某些选择同在他前面要执行的笛卡尔积结合起来成为一个连接运算
找出公共子表达式

4.物理优化

1.基于启发式规则的存取路径选择优化

1.选择操作

对于小关系，使用全表顺序扫描，即使列上有索引

对于大关系，由启发式规则判断使用全表扫描或者索引

2.连接操作

两个表已经按照连接属性排序：排序合并算法
如果一个表在连接属性上有索引：索引连接算法
如果上面两个规则都不符合，且其中一个表较小：hash join算法
嵌套循环算法

2.基于代价估算的优化

九、并发控制

1.概述

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

保证事务的隔离性与一致性

并发操作带来的数据的不一致性包括丢失修改、不可重复读和读“脏”数据

丢失修改

事务T1和T2同时读入数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改丢失
不可重复读

T1读取数据后，T2执行更新操作，T1无法读取前一次读取的结果
脏读

T1修改数据，但事务未提交，T2读取数据后，T1回滚，T2读取的就是脏数据

并发控制机制就是要用正确的方式调度并发操作，使一个事务的执行不受其他事务的干扰

并发控制的技术有封锁、时间戳、乐观控制、多版本并发控制等

2.封锁

基本的封锁类型：

排他锁（X锁）：写锁，只允许本事务读取或修改数据A，其他事务不可对数据A加任何类型的锁
共享锁（S锁）：读锁，只允许本事务读取数据A（不能修改），其他事务可以读取数据A（不能修改），其他事务可以加S锁

3.封锁协议

一级封锁协议：写前加X锁，事务结束释放
二级封锁协议：在一级的基础上，读前加S锁，读完释放
三级封锁协议：在一级的基础上，读前加S锁，事务结束释放

4.活锁和死锁

1.活锁

如果事务T1封锁了数据R，事务T2又请求封锁R，于是T2等待；T3也请求封锁R。当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待：然后T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求…… T2 有可能永远等待，这就是活锁的情形

避免活锁的简单方法就是采用先来先服务的策略

2.死锁

两个事务要同时封锁两个数据，但是T1封锁了A，T2封锁了B。此时T1申请封锁B，T2申请封锁A，陷入了死循环，形成死锁

预防：

一次封锁法
顺序封锁法

诊断与接触：

超时法：对于给定的时限，超过即断定为产生了死锁
等待图法：事务之间连线，如果存在环即产生了死锁

5.并发调度的可串行性

1.可串行化调度

可串行化调度：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行的执行这些事务的结果相同

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

2.冲突可串行化调度

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

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

冲突可串行化的调度：一个调度在保证冲突操作次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度，如果其是串行的，则称原来的调度为冲突可串行化调度

6.两段锁协议

数据库管理系统的并发控制机制必须提供一定的手段来保证调度是可串行化的，普遍采用两段锁协议

两段锁协议：

在对任何数据进行读写操作之前，首先要申请并获得对该数据的封锁
在释放一个封锁之后，事务不再申请和获得任何其他封锁

其含义即为封锁和解锁分为两个阶段进行

若并发执行的所有事务均遵循两段锁协议，则对这些事务的任何并发调度策略都是可串行化的

7.封锁的粒度

封锁对象的大小称为封锁粒度

封锁对象可以是逻辑单元（属性值、属性值集合、元组、关系等），也可以是物理单元（页、物理记录等）

1.多粒度封锁

在一个系统中同时支持多种封锁粒度供不同的事务选择是比较理想的，这种封锁方法叫做多粒度封锁

多粒度封锁协议允许多粒度树中的每个节点被独立的加锁

对一个节点加锁意味着这个节点的所有后裔节点也被加同样类型的锁

2.意向锁

含义：如果对一个节点加意向锁，则说明该节点的下层节点正在被加锁

对任意节点加锁时，必须先对他的上层节点加意向锁

常用意向锁：

意向共享锁（IS锁）

如果对一个数据对象加IS锁，表示他的后裔节点拟加S锁
意向排他锁（IX锁）

如果对一个数据对象加IX锁，表示他的后裔节点拟加X锁
共享意向排他锁（SIX锁）

如果对一个数据对象加SIX锁，表示对他加S锁，再加IX锁

8.其他并发控制机制

1、时间戳

给每个事务盖上一个时标，即事务开始执行的时间。如果发生冲突操作，回滚开始时间较早的事务，并且被回滚的事务被赋予新的时间戳，并从头开始执行

2.乐观控制

乐观控制认为事务很少发生冲突，因此仅在事务提交前进行正确性检查，如果事务执行过程中出现冲突并影响了可串行性，则拒绝提交并回滚事务

十、MySQL语句例题

# 表格：s（学生表） c（课程表） sc（学生选课表）

# 不选修C语言课程的学生学号
select s.sno from s where not exists
	(select * from sc,c where sc.sno=s.sno and sc.cno=c.cno and c.cname='c语言')
     
# 查询平均成绩在60分以上的学生姓名
select s.snamefrom s,sc
where sc.sno-s.snogroup by s.sno
having avg(sc.grade)>60

# 求这样的学生姓名：该学生选修了全部课程并且其中一门课在90分以上
select s.sno from s,sc where not exists
	(select * from c where not exists
		(select * from sc where sc.sno=s.sno and c.cno=sc.cno))
and s.sno=sc.sno
group by s.sno
having max(sc.grade) > 90

# 查询成绩及格的学生人次
select count(distinct sno)
from sc as sc1 
where not exists(select * from sc as sc2 where grade<50 and sc1.sno=sc2.sno);

# 查询所有学生都没有选修的课程名称
select cname
from c
where not exists(select * from sc where c.cno=sc.cno);

# 查询每个学生的平均成绩，并按平均成绩的降序排序
select sc.sno,avg(sc.grade)
from sc,s
where sc.sno=s.sno
group by sc.sno
order by avg(sc.grade)

# 求恰好有两门课程不及格的学生信息
select s.* from sc, s
where sc.grade  <60
and sc.sno=s.sno
group by sc.sno
having count(sc.cno) =2

23DAY

关注

2
点赞
踩
22

收藏

觉得还不错? 一键收藏
1
评论
数据库系统概论--MySQL

四个基本概念：数据、数据库、数据库管理系统、数据库系统以这个数据库为例，介绍有关概念员工的编号列的值有唯一性，编号叫做员工表的**主键（primary key）**员工表中有部门编号，表示员工与部门的**联系（relationship）**员工表中有部门编号，而部门编号是部门表的主键，那么这个属性叫做**外键（foreign key）**如果表中某几个属性（列）的值能唯一的标识一个实体，而其子集不能，则该属性组为**候选码**如果一个表中有多个候选码，则选定一个为**主键**
复制链接

扫一扫

专栏目录