【数据库】MySQL视图、存储程序、备份、用户权限、存储引擎、事务、设计范式

程序员李林

于 2022-09-24 14:54:36 发布

阅读量340

点赞数 1

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

本文链接：https://blog.csdn.net/weixin_42659457/article/details/127025370

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文参考《MySQL是怎样使用的》进行总结。

第十五章视图

15.1 创建视图

视图：可以看作查询语句的别名，视图并不存储数据；
- 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表；
```
CREATE VIEW 视图名 AS 查询语句;
```

利用视图创建视图：

CREATE VIEW view2 AS SELECT number, name, score FROM view1;

创建视图时指定自定义列名
```
CREATE VIEW view2(no, n, s) AS SELECT number, name, score FROM view1;
```
- 自定义列名要与查询列表中的表达式一一对应；

15.2 使用视图

视图原理：
- 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间；
- 对视图的操作都会被映射到查询语句对应的底层的表上；
查询视图：
- 从视图中查询信息的操作与从真实表中查询信息的原理相同：
```
SELECT * FROM view1;
```
查看有哪些视图
```
SHOW TABLES;
```

查看视图的信息

SHOW CREATE VIEW 视图名称\G #查看视图的详细定义信息
DESC / DESCRIBE 视图名称; #查看视图的结构
SHOW TABLE STATUS LIKE '视图名称'\G #查看视图的属性信息

更新视图
- 可更新视图：可以在视图上使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作，本质仍是修改视图对应的底层表；
- 不可更新视图：在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、聚合函数、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE；

15.4 修改、删除视图

修改视图：CREATE OR REPLACE VIEW 子句

ALTER VIEW 视图名称 AS 查询语句;
CREATE OR REPLACE VIEW 视图名称 AS 查询语句;

删除视图：
- 删除视图只是删除视图的定义，并不会删除基表的数据；
- 删除视图的语法
```
DROP VIEW IF EXISTS 视图名称;
```

15.5 视图总结

优点：操作简单、减少数据冗余、数据安全、适应灵活多变的需求、能够分解复杂的查询逻辑；
不足：
- 如果视图过多，会导致数据库维护成本的问题；
- 如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图）。

第十六章存储程序

16.1 概述

存储程序
- 根据调用方式的不同，可以把存储程序分为存储例程、触发器、事件；
- 存储例程又细分为存储函数和存储过程；

用户自定义变量

定义方式(要使用@符号)

SET @a = 1;
SET @a = 'a'; #同一个变量也可以存储不同类型的值
SET @b = @a; #可以把一个变量赋值给另一个变量
SET @a = (SELECT m1 FROM t1 LIMIT 1); #可以将某个查询结果的值赋值给另一个变量
SELECT n1 FROM t1 LIMIT 1 INTO @b; #可以使用INTO子句代替SET语句进行赋值

16.2 存储函数

创建存储函数
```
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
BEGIN
	函数体 #函数体中肯定有 RETURN 语句
END
```
- 函数体中可以包含一条或多条语句，每条语句要以分号（；）结尾；
- 客户端默认将分号作为语句分隔符，因此要使用DELIMITER语句修改客户端语句分隔符：
```
DELIMITER $
```
存储函数调用
- 自定义的函数和系统内置函数的使用方式相同，在函数名后加小括号进行调用：
```
SELECT 函数名(实参列表);
```
查看
```
SHOW FUNCTION STATUS [LIKE 'pattern'];
```

删除

DROP FUNCTION [IF EXISTS] 存储函数名;

函数体的定义
- 在函数体中定义局部变量：只在该函数体内有用；
```
DECLARE 变量名1, 变量名2, ... 数据类型 [DEFAULT 默认值]; #若不指定默认值，则为NULL
```
  - 在函数体中，DECLARE 语句必须放在其他语句前；
  - 函数体中的局部变量名不允许加@前缀；
- 在函数体中使用用户定义变量
```
SET @abc = 10;
```
- 存储函数的参数
  - 在定义存储函数时，可以指定多个参数，每个参数都要有对应的数据类型
```
参数名 数据类型
```
    - 参数名不能和变量名、列名冲突；
    - 参数不能指定默认值；
流程控制语句
- 判断语句 IF
```
IF 表达式1 THEN 
	操作1
[ELSEIF 表达式2 THEN
	操作2]……
[ELSE 
	操作N]
END IF
```
- 循环语句
```
[while_label:] WHILE 循环条件 DO
	循环体
END WHILE [while_label];
```
```
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label];
```
```
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label];
```
  - 这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称;
  - LOOP：一般用于实现简单的"死"循环 WHILE：先判断后执行；
  - REPEAT：先执行后判断，无条件至少执行一次；
- 跳转语句：LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作：
```
LEAVE 标记名
```
  其中，label参数表示循环的标志，LEAVE和BEGIN … END或循环一起被使用；

16.3 存储过程

创建存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
	存储过程体
END

存储过程的调用
- 存储过程没有返回值，需要用CALL显式调用：
```
CALL 存储过程名(实参列表)
```

查看和删除存储过程

查看目前服务器上一创建的存储过程：
```
SHOW FUNCTION STATUS [LIKE 'pattern'];
```
查看存储过程的定义：
```
SHOW CREATE PROCDEDURE 过程名称\G 
```

删除存储过程：

DROP PROCEDURE [IF EXISTS] 存储过程名;

存储过程中的语句
- 可以使用变量、判断、循环结构等；

存储过程的参数前缀

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)

前缀	实际参数是否必须是变量	描述
IN	否	用于调用者向存储过程传递数据，如果IN参数在存储过程中被修改，则调用者不可见
OUT	是	用于把存储过程运行过程中的产生的数据赋值给OUT参数，存储过程执行结束后，调用者可以访问OUT参数
INOUT	是	综合IN 和OUT 的特点，既可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用

若不写明，默认的前缀为IN；

存储过程和存储函数的不同点
- 存储过程注重过程中的结果集显示、赋值等，而存储函数注重函数的返回值；
- 存储函数在定义时需要显示使用RETURNS语句表名返回的数据类型，且函数体内必须要返回值，存储过程不需要；
- 存储函数不支持IN、OUT、INOUT的参数前缀，存储过程则支持；
- 存储函数只能返回一个值，存储过程可以通过设置多个OUT参数或INOUT参数来返回多个结果；
- 存储函数在执行过程中产生的结果集不会显示到客户端，存储过程中执行产生的结果集会被显示；
- 存储函数直接以函数调用的形式进行调用，存储过程只能同CALL语句来显式调用；

16.4 游标简介

简介
- 游标提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作；
- 游标既可以用在存储函数中，也可以用在存储过程中；
- 使用游标大致分为四步：创建游标、打开游标、通过游标获取记录、关闭游标；
创建游标
- 使用DECLARE关键字来声明游标
```
DECLARE cursor_name CURSOR FOR SELECT m1, m2 FROM t1;
```
  - 需要指定与游标关联的查询语句；
  - 创建游标的语句要放在局部变量声明的后面；
打开和关闭游标
- 打开游标意味着执行查询语句，使游标与查询语句的结果集关联起来；
- 如果不使用CLOSE关闭游标，则在该存储过程的END语句执行完后会自动关闭；
```
OPEN cursor_name;
CLOSE cursor_name;
```

通过游标获取记录

使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 变量中

FETCH cursor_name INTO var_name1 [, var_name2] ...

读取后，游标指针指到下一行；

如果想获取多条记录，需要把FETCH 放进循环语句；

CREATE PROCEDURE cursor_demo()
BEGIN
    DECLARE record_count INT;
    DECLARE i INT;
    DECLARE m INT;
    DECLARE n VARCHAR(5);

    DECLARE cursor_name CURSOR FOR SELECT m1, m2 FROM t1;
    
    SELECT COUNT * FROM t1 INTO record_count;
    
    OPEN cursor_name;
    
    WHILE i < record_count DO
        FETCH cursor_name INTO m1, m2;
        SELECT m1, m2;
        SET i = i + 1;
    END WHILE;

	CLOSE cursor_name;
END

在FETCH语句获取不到记录的时候会默认停止存储函数或存储过程的执行；

可以在存储函数或存储过程中事先声明一种针对某种错误的处理方式：

DECLARE CONTINUE HANDLE FOR NOT FOUND
[BEGIN]
	处理语句;
[END]

在每次执行完FETCH语句后，判断done是否为1，如果为1，说明读取结果集结束，可以退出循环：

CREATE PROCEDURE cursor_demo()
BEGIN
	DECLARE m INT;
    DECLARE n VARCHAR(5);
    DECLARE record_count INT;
    DECLARE i INT;
    DECLARE done INT DEFAULT 0;

    DECLARE cursor_name CURSOR FOR SELECT m1, m2 FROM t1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
    SELECT COUNT * FROM t1 INTO record_count;
    
	OPEN cursor_name;
	
	flag: LOOP
        FETCH cursor_name INTO m1, m2;
        IF done = 1 THEN
        	LEAVE flag;
        END IF;
        SELECT m1, m2;
    END LOOP flag;
    
 CLOSE cursor_name;   
 
 END

16.5 触发器

触发器和事件是MySQL服务器在特定情况下自动调用的；
创建触发器
```
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} 
ON 表名
FOR EACH ROW
BEGIN
	触发器执行的语句块
END
```
- 可以使用NEW和OLD分别代表新记录和旧记录；
  - 对INSERT：NEW 表示准备插入的记录，OLD无效；
  - 对DELETE：OLD表示删除前的记录，NEW无效；
  - 对UPDATE：NEW表示修改后的记录，OLD表示修改前的记录；
  - 触发器命名：
```
bi_t1：bi为before insert的缩写，t1是表名
```

查看和删除触发器

查看

SHOW TRIGGERS\G #查看当前数据库的所有触发器的定义
SHOW CREATE TRIGGER 触发器名\G #查看当前数据库中某个触发器的定义

删除

DROP TRIGGER IF EXISTS 触发器名称;

触发器注意事项
- 触发器内容中不能有输出结果集的语句；
- 触发器内容中NEW代表记录的列的值可以被修改，OLD代表记录的列的值无法被修改；
- 在BEFORE触发器中，可以使用 SET NEW.列名 = 值 的形式来更改待插入记录或者待更新记录的某个列的值，但是不能在AFTER触发器中使用，因为执行AFTER时已完成插入或更新。

16.6 事件

让MySQL服务器在某个时间或每隔一段时间自动地执行一些语句

创建事件

CREATE EVENT [IF NOT EXISTS] 事件名 
ON SCHEDULE
{
	AT 某个确定时间点 |
	EVERY 期望的时间间隔 [STARTS timestamp][ENDS timestamp]
}

DO
BEGIN
	具体语句
END

时间间隔:
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

定时执行事件的功能需要开启后才能使用：
```
SET GLOBAL evend_scheduler = ON;
```

查看和删除事件

查看事件

SHOW EVENTS 事件名 
SHOW CREATE EVENT 事件名

删除事件
```
DROP EVENT 事件名 
```

第十七章备份与恢复

17.1 mysqldump

使用mysqldump可执行文件进行备份数据
```
mysqldump [其他选项] 数据库名 [表1名， 表2名......]
```
- 一般将输出重定向到某个文件，而不是直接在屏幕上输出；
- 其他选项一般为 -uroot -hlocalhost -p等；
```
mysqldump -uroot -hlocalhost -p fuliying student_score > student_score.sql
```

备份指定数据库中的所有表

mysqldump [其他选项] --databases 数据库1名, 数据库2名, ......

备份所有数据库的所有表

mysqldump [其他选项] --all-databases

使用SOURCE语句恢复数据
```
mysql> SOURCE * FROM student_score;
```

17.2 以文本形式导出或导入

导出数据
```
SELECT ... INTO OUTFILE '文件路径' [导入选项];
```
- [导入选项]为可选参数选项，常用参数：
  
  FIELDS TERMINATED BY：设置分隔符，可以为单个或多个字符。默认值是\t
  FIELDS ENCLOSED BY：设置字符来括住字段的值，只能为单个字符。默认不使用任何符号
  FIELDS OPTIONALLY ENCLOSED BY：设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号
  FIELDS ESCAPED BY：设置转义字符，只能为单个字符。默认值为“\”
  LINES STARTING BY：设置每行开头的字符，可以为单个或多个字符。默认不使用任何字符
  LINES TERMINATED BY：设置每行数据结尾的字符，可为单个或多个字符。默认值是\n

导入数据

LOAD DATA [LOCAL] INFILE '文件路径' INTO TABLE 表名 [导入选项];

常用导入选项与SELECT ... INTO OUTFILE类似。

第十八章用户与权限

18.1 用户管理

创建用户

CREATE USER '用户名@主机名' IDENTIFIED BY '密码';

新创建的用户权限非常小；

修改密码

ALTER USER '用户名@主机名' IDENTIFIED BY '新密码';

删除用户
```
DROP USER '用户名@主机名';
```

18.2 权限管理

权限授予

GRANT 权限名称
ON 应用级别
TO '用户名@主机名'
[WITH GRANT OPTION]

权限名称

权限名字	权限说明
CREATE	允许创建新的数据库和表
DROP	允许删除现有数据库、表和视图
DROP	允许删除现有数据库、表和视图
LOCK TABLES	允许使用显式锁表的语句锁表,前提是你必须有SELECT权限
REFERENCES	MySQL 5.7.6之前引用特权是未使用的。5.7.6,创建一个外键约束需要引用父表的特权。
EVENT	The EVENT privilege is required to create, alter, drop, or see events for the Event Scheduler.
ALTER	允许修改权限或重命名表
DELETE	允许从表中或数据库中删除行
INDEX	允许创建或删除索引
INSERT	允许在表和数据库中插入行
SELECT	允许在表和数据库这查询行
UPDATE	允许在表和数据空中更新行
CREATE TEMPORARY TABLES	允许创建和使用临时表
TRIGGER	允许使用触发器相关,前提是你必须拥有这个表的CREATE、DROP、EXECUTE或者Display权限
CREATE VIEW	允许使用CREATE VIEW语句
SHOW VIEW	允许使用SHOW VIEW语句
ALTER ROUTINE	ALTER ROUTINE允许你修改存储过程(过程或函数)前提是你需要在存储过程上拥有ALTER或DROP权限
CREATE ROUTINE	ALTER ROUTINE允许你创建存储过程(过程或函数)前提是你需要在存储过程上拥有ALTER或DROP权限
EXECUTE	允许你执行存储过程(过程或函数)
CREATE USER	允许使用ALTER USER, CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES 语句
PROCESS	允许使用SHOW PROCESSLIST or mysqladmin processlist看到线程
PROXY	使用用户代理的权限
RELOAD	允许使用FLUSH语句
REPLICATION CLIENT	查看主从服务器的权限
REPLICATION SLAVE	是否能够创建连接以复制的方式,连接至数据库服务器端
SHOW DATABASES	允许使用SHOW DATABASES来查看所有数据库列表
SHUTDOWN	允许使用SHUTDOWN语句
SUPER	超级特权可以用于终止其他会话或改变服务器运行。属于特别高级别的一类权限
USAGE	无权限,当您想要创建一个没有权限的用户时，可以指定USAGE
ALL PRIVILEGES	所有权限: ALL PRIVILEGES,可以简写为ALL

应用级别
- *. *：代表全局级别，作用与任何数据库下的对象；
- 数据库名.*：代表数据库级别；
- 数据库名.表名：代表表级别；
WITH GRANT OPTION：表示该用户可以将自己拥有的权限授予其他人；
root用户默认有最高权限；

查看权限

SELECT user,host FROM mysql.user;
SELECT * FROM mysql.user WHERE user='root'\G

SHOW GRANTS FOR '用户名@主机名';

移除权限

REVOKE 权限名称
ON 应用级别
FROM '用户名@主机名';

权限名称、应用级别与权限授予的相同。

第十九章存储引擎

19.1 存储引擎的使用

存储引擎
- 数据库中的各表均被（在创建表时）指定的存储引擎来处理；
- 服务器可用的引擎依赖于以下因素：
  • MySQL 的版本
  • 服务器在开发时如何被配置
  • 启动选项
- 查看引擎
```
SHOW ENGINES\G
SHOW CREATE TABLE emp\G
 SHOW TABLE STATUS LIKE 'emp'\G
```
指定存储引擎
- 在创建表时，可使用ENGINE选项为CREATE TABLE 语句显式指定存储引擎
```
CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;
```
  - 如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎；
- 现有表的存储引擎可使用ALTER TABLE 语句来改变
```
ALTER TABLE TABLENAME ENGINE = INNODB;
```

15.2 常用的存储引擎

MyISAM 存储引擎

MyISAM 存储引擎是MySQL 最常用的引擎，它管理的表具有以下特征：

使用三个文件表示每个表：

格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）
灵活的 AUTO_INCREMENT 字段处理；
可被转换为压缩、只读表来节省空间；
InnoDB 存储引擎

InnoDB 存储引擎是MySQL 的缺省引擎，它管理的表具有以下特征：
- 每个 InnoDB 表在数据库目录中以.frm 格式文件表示；
- InnoDB 表空间 tablespace 被用于存储表的内容；
- 提供一组用来记录事务性活动的日志文件；
- 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理；
- 提供全 ACID 兼容；
- 在 MySQL 服务器崩溃后提供自动恢复；
- 多版本（MVCC）和行级锁定；
- 支持外键及引用的完整性，包括级联删除和更新；
MEMORY 存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，使得 MEMORY 存储引擎非常快；

MEMORY 存储引擎管理的表具有下列特征：

在数据库目录内，每个表均以.frm 格式的文件表示；
表数据及索引被存储在内存中；
表级锁机制；
不能包含 TEXT 或 BLOB 字段；

15.3 选择合适的存储引擎

MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只
读表；
如果查询中包含较多的数据更新操作，应使用 InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制；
可使用 MEMORY 存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

第二十章事务

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的 DML 要么全成功，要么全失败；

20.1 概述

事务的四个特征ACID
- 原子性（Atomicity）：整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）；
- 一致性（Consistency）：在事务开始之前与结束之后，数据库都保持一致状态；
- 隔离性（Isolation）：一个事务不会影响其他事务的运行；
  持久性（Durability）：在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚；
事务中存在一些概念
- 事务（Transaction）：一批操作（一组 DML）；
- 开启事务（Start Transaction）；
- 回滚事务（rollback）；
  
  注意：只能回滚 insert、delete 和 update 语句，不能回滚 select，对于create、drop、alter 这些无法回滚；
- 提交事务（commit）；
- SET AUTOCOMMIT：禁用或启用事务的自动提交模式；

20.2 事务隔离的级别

事务的隔离级别决定了事务之间可见的级别；
当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
- 脏读取（Dirty Read）：一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交；
- 不可重复读（Non-repeatable Read）：在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果；
- 幻像读（Phantom Read）：在同一个事务中以前没有的行，由于其他事务的提交而出现的新行；
四个隔离级别
- InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务
  - 读未提交（READ UMCOMMITTED）：允许一个事务可以看到其他事务未提交的修改；
  - 读已提交（READ COMMITTED）：允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的；
  - 可重复读（REPEATABLE READ）：确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果；
  - 串行化（SERIALIZABLE）【序列化】：将一个事务与其他事务完全地隔离。