MySQL必知必会21-30章学习笔记（DDL，视图，触发器，存储过程，事务管理）

ZW钟文

已于 2023-11-15 15:51:45 修改

阅读量366

点赞数 1

分类专栏：关系型数据库文章标签： mysql 学习数据库

于 2022-07-22 16:25:29 首次发布

本文链接：https://blog.csdn.net/qq_51964209/article/details/125934658

版权

关系型数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL必知必会这本书也算是看完了，它基本上提到MySQL中所有常见的语法以及写法。虽然后面的事务管理，备份和安全，授权与创建用户以及改善数据库性能只是提一下，但其实在往深处看就是MySQL自身的一些实现原理了，而不是语法以及用法的问题了。
综合来说，我觉得从这本书中最应该学习的是如何写出一手好的DML语句（即SELECT, INSERT, DELETE, UPDATE语句），最好是可以解决一些偏难的查询SQL。至于建表，触发器，存储过程这些看似复杂，但实际上核心也就是DML语句。它们的其它语法看看就行，现在的可视化工具可以很方便的给出它们的语法模版。

创建和操纵表

建表

create table [if not exists] table_name (
    column_name datatype [NULL | NOT NULL] [auto_increment] [default] [check] [primary key] [foreign key]
) [engine = ]

建表模版如上所示，其中[]中的值表示可选，这些可选属性在一张略微成熟的表中基本上是包不掉的。
if not exists：该选项表示若要创建的表在数据库中不存在，则执行建表语句，否则将不执行建表语句。它基本上可以加在任何一个建表语句中，因为表存在的情况下执行建表语句会报错。
NULL：对应列的值可以为空，默认选项即它
NOT NULL：对应列的值不允许为空，在一些有实际意义且不可缺少的列上需使用该选项
auto_increment：对于每一个元组，生成一个唯一的（没有实际意义）的标识值（一般为id）。需注意的是，每一张表只能有一列是自增列，且该列必须被索引。虽然，每一个插入表中的元组的该列的值都是自动生成的，但是我们依然可以插入一个元组，并在自增列上设置任意值（只有该值在该列上尚未使用过），并且如果该值比当前的自增列的最大值还大，后序插入元组的自增列将从它开始。

auto_increment的应用场景：
你正在增加一个新订单。这要求在orders表中创建一行，然后在orderitems表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。那么，如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值，如下所示：SELECT last_insert_id()。

default：如果插入行时，数据中没有该列的值时，将采用默认值default后的值进行一个填充。

MySQL不允许函数作为默认值，默认值只允许为常量
建议使用默认值代替NULL

check：对于列中值的一个约束，不满足约束的元组将被禁止插入到表中
primary key：指定该列为主码，主码能够唯一标识元组，同时主码上的值不能为NULL
foreign key：指定该列为外码，后接references table_name(column_name)表示引用某张表某一列的值

引擎类型

MySQL有个具体管理和处理数据的内部引擎，在使用CREATE TABLE时，该引擎创建表。而在你使用SELECT语句时，该引擎在内部处理请求。MySQL与其它DBMS不同，他有多种引擎，这些引擎被打包在DBMS服务器中，同时他们都可以建表以及响应SELECT请求等。

InnoDB是一个可靠的事务处理引擎
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，**速度很快（**特别适合于临时表）
MyISAM是一个性能极高的引擎，支持全文搜索，但不支持事务处理

数据库的表之间引擎可以混用，但是外码是不能跨引擎的。A表中某个属性引用了B表的属性，那么A表和B表不能跨引擎。

更新表

一般情况下，表的设计是要经过深思熟虑的，即表设计后尽可能少的更改表的结构。但有时候迫不得已一定要修改表的结构。这时就需要alter table命令来对表进行一个列属性的增删改，以及索引的创建和删除。

添加字段

格式：ALTER TABLE table_name ADD field_name datatype [约束条件] [FIRST|AFTER 已存在的字段名]；
First为可选参数，将新添加的字段置于表的第一个字段，After为可选字段，将新添加的字段添加到某个已存在字段的后面。默认添加到表的最后一个字段后面。
示例：ALTER TABLE t ADD f VARCHAR(20) FIRST

修改字段的数据类型

格式：ALTER TABLE table_name MODIFY field_name new_datatype
示例：ALTER TABLE t MODIFY f DECIMAL(5,2)

删除字段

格式：ALTER TABLE table_name drop field_name
示例：ALTER TABLE t drop f

修改字段名称

格式：ALTER TABLE table_name change 旧字段名新字段名新数据类型

提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

给表改名称：ALTER TABLE table_name [to] new_table_name
给表添加约束：ALTER TABLE table_name add constraint 约束名字具体约束
删除表：drop table table_name(从物理结构上删除）

视图

视图是虚拟的表（本身不包含数据），与包含数据的表不一样，视图包含SELECT语句，从视图中获取数据时，视图会动态地执行SELECT语句。
视图的一些常见应用：

重用SQL语句
简化复杂的SQL操作，在编写查询时，我们并不知道视图的底层查询及逻辑关系是什么样的
使用表的组成部分，而不是表的整体部分
保护数据，可以给用户授予表的部分访问权限而不是整个表的访问权限
更改数据格式和表示，（SELECT语句自然可以通过函数，计算字段，聚集函数等方式得到底层表不包含的数据格式和表示）

对于视图，我们可以将它当做普通的表进行使用，在它上面进行检索，与其它表进行连接等，同时在一些情况下我们还能对视图进行增删改。
视图的规则和限制：

在一个数据库中，视图必须是唯一的命名
必须拥有相应的权限，才能够创建视图（权限由管理员授予）
视图不能被索引（索引是存储在物理上的数据结构，而视图的结果只存在于内存中），也不能有与之关联的触发器或默认值

创建视图的命令：CREATE VIEW view_name as SELECT语句
查看创建视图语句的命令：SHOW CREATE VIEW view_name
删除视图：DROP VIEW view_name
更新视图：要么先删除再创建，要么使用命令CREATE OR REPLACE VIEW view_name来创建更新视图
对于视图的更新（UPDATE, INSERT, DELETE）实际上都会对应到基表上，因此能够在视图上进行数据更新的前提是，视图中的数据能对应都基表的某一个元组身上。视图的定义中有以下操作，则不能对视图进行更新：

分组（GROUP BY 和 HAVING）
连表
计算属性
聚合函数或DISTINCT
并集或子查询

视图大多是时候还是用来查询的，因此不必太多关注于对视图的更新

存储过程

存储过程是什么

至今为止，我们所写的SQL语句都只有一条，它可能是针对单个表也可能是针对多个表。考虑下面这种情况：

处理订单，需要核对以保证库存中包含订单需要的物品
如果库存中有物品，则这些物品应该预售给该订单的用户，同时库存中这部分的量需要减去，不能再被其它订单订购
库存中没有物品，则需要与卖物品的商家进行买卖
处理买卖问题，物品买到之后是否能直接被订单预订等

如何编写这种针对多个表的多条SQL语句呢？要么就一条一条的编写SQL语句，然后按条件执行。还有就是创建存储过程，它是为以后的使用保存的一条或多条SQL语句，可以将它看作批文件。

创建存储过程

DELIMITER /
CREATE PROCEDURE procedure_name(参数列表:[in a int](外界传入的值),[out b int](存储过程传出的值))
begin
DECLAERE xxxxx;
SELECT xxxxxx;
end/
DELIMITER ;

注意点：

默认的MySQL语句的分隔符为;，MySQL命令行工具或者你使用的MySQL客户端的默认分隔符可能为;，此时你又想在语句中存储;，因此你需要用DELIMITER命令显示的更改客户端工具的分隔符。在存储过程创建完毕后，再将分隔符修改为默认值。

存储过程不允许通过一个参数返回多个行和多个列，MySQL中所有的变量都需要以@开头。

-- 不带参数的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `all_user`()
BEGIN
    SELECT * from `mybatis`.`t_user`;
END

-- 带参数的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `out_test`(out ans int)
BEGIN
    SELECT count(*) into ans from t_user;
END

-- 完成实际业务的存储过程(根据订单情况看要不要带税）
-- 
-- Name: ordertota 1
-- Parameters: onumber = order number
-- taxable = 0 if not taxable， 1 if taxab
-- ototal = order total variable 
CREATE PROCEDURE ordertotal(
IN onumber INT ,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- Get the order total
SELECT Sum(item_ pri ce*quanti ty)
FROM orderi tems
WHERE order_ _num = onumber
INTO total;
-- Is this taxable?
IF taxab1e THEN
-- Yes，so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
-- 最后将得到的值赋值给存储过程要输出的值
SELECT total INTO ototal;
END

使用存储过程

标准格式：CALL procedure_name([参数] @a, @b...)，再之后我们就可以通过SELECT @a;形式将指向存储过程得到的输出变量显示出来。

CALL all_user();
CALL out_test(@a);
select @a;

删除存储过程：DROP PROCEDURE [if exists] procedure_name；

游标

MySQL中利用SELECT语句可以返回一个结果集，但由我们前面所学的知识它也仅仅只是一个结果集，我们不能在它上面进行跳跃性或者是处理一行。因此，就有了游标的出现，游标是存储在MySQL服务器上的一个数据库查询，代表的是查询出来的结果集，在定义游标之后，可以利用OPEN打开游标，再接着就能利用游标检索各行所需要的列数据了。

MySQL中有游标只能用于存储过程和函数。

CREATE PROCEDURE cursor_test()
BEGIN
DECLARE csr CURSOR
FOR
SELECT * from t_user;
OPEN csr;
xxxxxx
xxxxxx
CLOSE csr;

利用FETCH语句可以从游标中抓取行中对应的列元素，FETCH语句自动从第一行开始获取数据，每使用一次FETCH语句，游标向下移动一行。抓取的数据可以放到声明的临时变量中，但需要注意的是该临时变量的声明需要在游标声明之前。
直接来一个存储过程+游标+存储过程+逐行处理的示例吧：

CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8, 2);

DECLARE ordernumbers CURSOR
FOR
SELECT order_num from orders;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;
CREATE TABLE IF NOT EXISTS ordertotals(
  order_num int,
  total DECIAL(8, 2)
);
REPEAT
FETCH ordernums INTO o;
CALL ordertotal(o, 1, t);
INSERT INTO ordertotals(order_num, total) values(o, t);
UNTIL done END REPEAT;
CLOSE ordernums;
END

REPEAT是用来表示循环执行的，循环结束的条件是done的值为1，但是done的初始值为0，在什么时候done的值会变成1呢。这里我们声明了一个CONTINUE HANDLER，它是表示条件出现后要执行的代码。示例代码中的条件是SQLSTATE为02000，即没找到时的状态（具体什么状态码对应什么行业可在官网看到）。而要执行的动作就是将done的值改为1。因此代码中循环的作用就是遍历完游标中的元组。

CONTINUE HANDLER的声明必须要在游标的声明之后

触发器

触发器是指在表发生更改时（执行INSERT, UPDATE, DELETE语句之后)执行的一条MySQL语句。
创建触发器需要给出的信息：

唯一的触发器名
触发器关联的表
触发器监听的动作
触发器的语句是在监听动作执行前还是执行后发生

创建触发器的模版：

CREATE TRIGGER trigger_name [BEFORE | AFTER] [INSERT, UPDATE, DELETE] ON table_name
FOR EACH ROW 需执行的SQL语句

例如，BEFORE INSERT表示触发器在插入任意一行到名为table_name的表之前执行。

MySQL中只有表支持触发器，视图和临时表不支持触发器

删除触发器：DROP TRIGGER trigger_name。触发器不能更新或覆盖，为了修改一个触发器，必须先将它删除，然后再创建新的触发器。

一个触发器只能监听一张表的一个动作并给予响应，因此一张表可以拥有六个触发器。分别为增删改的前和后操作。

INSERT触发器

在INSERT触发器代码中，可以引用一个名为NEW的虚拟表，访问被插入的行
在BEFORE INSERT触发器中，可以通过更新NEW中的值来更改插入的值
对于AUTO_INCREMENT列，NEW在执行之前包含0，在INSERT执行之后包含新的自动生成值

查看插入元组的自增列值的触发器：

CREATE TRIGGER test_after_trigger AFTER INSERT ON t_user
FOR EACH ROW SELECT NEW.id INTO @A;

注意点：MySQL5.几之后触发器就不能返回结果集，因此我们需要将提取到的插入元组自增列的值填到**@A**变量中，后续再通过SELECT语句将其显示出来。

通常，BEFORE用于数据验证和净化（目的是保证插入表中的数据是需要的数据）

DELETE触发器

在DELETE触发器代码类可以访问名为OLD的虚拟表，访问要被删除的行
OLD表中的值只能读，不能更新

用来将即将删除的行保存到一张存档表中的触发器：（存档表的结构与删除行的表结构一致）

DELIMITER $$

CREATE
    TRIGGER `mybatis`.`test_save` BEFORE DELETE
    ON `mybatis`.`t`
    FOR EACH ROW BEGIN
    INSERT INTO `mybatis`.`t_save` VALUES(`OLD`.id);
    END$$

DELIMITER ;

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）是，如果因为事故导致待删除的行不能存档，那么则放弃执行这条DELETE语句。
同时，触发器可以执行多条SQL语句，只要将多条SQL语句置于BEGIN和AND中间即可。注意，多条语句在MySQL服务端的分隔符默认是;，客户端工具的默认分隔符也是;的问题。

UPDATE触发器

在UPDATE触发器代码中，可以引用名为OLD的虚拟表访问待修改行之前的值，引用名为NEW的虚拟表访问待更新的值
在BEFORE UPDATE触发器中，可以更改虚拟表NEW中的值以更改即将在实际表中更改的值
同时OLD虚拟表中的值只读不能写

示例：

DELIMITER $$
DROP TRIGGER `mybatis`.`test_upper`$$
CREATE
    TRIGGER `mybatis`.`test_upper` BEFORE UPDATE
    ON `mybatis`.`t_user`
    FOR EACH ROW BEGIN
    SET `NEW`.username = UPPER(`New`.username);
    END$$

DELIMITER ;

关于触发器：

创建触发器需要一定的权限，但是服务器的执行的自动的
使用触发器来保证数据的一致性（大小，格式等）。使用触发器执行这种类型处理的优点是它总是会记得要怎么处理，同时客户端应用不需要刻意在意它。
使用触发器创建审计跟踪，把更改之前的值记录到另一个存档表（就相对于存档，记录，日志的作用）

事务管理

MySQL服务器中打包了几个适用于不同任务的存储引擎，其中并不是每个引擎都支持事务管理的，比如MYISAM引擎就不支持事务管理。而INNODB存储引擎是支持事务管理的，现实中的应用基本上都是很多用户共同去访问MySQL服务器，为了支持并发访问且不出错，事务管理就显的尤其重要。
很多时候，我们的一个需求或者是功能，它需要通过多条SQL语句前后共同完成。而这个时候，为了保证多条SQL语句的正常执行，就需要事务管理来协作。事务有四个特性：

原子性：事务中的一系列SQL语句对于数据库来说要么全生效要么全不生效
一致性：事务执行前后，数据库中对应的整体状态不应发生变化。但是事务执行过程中，整体状态可以与执行前或执行后不一致
持久性：事务一经提交，它所做的改变一定会映射到物理空间中
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

术语：

事务（transaction）：一组SQL语句
回退（rollback）：撤销指定SQL语句的过程
提交（commit）：将未存储的SQL语句的结果写入数据库表
保留点（savepoint）：事务执行过程中的占位符，可以将事务回退至占位符所处位置

MySQL中默认是开启事务自动提交的，即一条SQL语句为一个事务，执行它即提交一个事务。
可以利用set autocommit = 0命令，取消事务的自动提交，注意该命令是针对当前连接，而不是整个MySQL服务器的。
事务的回退实际上指的是回退增删改（INSERT, DELETE, UPDATE)命令

可以显示的通过start transaction;命令表示一个事务的开始，在这之后，所有的INSERT, DELETE,UPDATE命令对于数据库表的修改都不会直接的映射到真实物理空间。只有通过COMMIT;命令显示的提交事务之后，所做的修改才会映射到物理空间中。或者是使用ROLLBACk;命令回退之前已经执行过的增删改语句。
在事务中间设置临时保留点：SAVEPOINT delete1;。这样在使用事务回退命令时，我们就能根据需求实际情况，将事务回退至某个事先保留点ROLLBACK TO delete1。

全球化和本地化

字符集：字母和符号的集合
编码：某个字符集成员的内部表示
校对：规定字符如何比较的指令
显示MySQL支持的完整字符集的命令：SHOW CHARACTER SET;
显示MySQL支持的完整校对的命令：SHOW COLLATION;
建表时指定相应的字符集和校队：

CREATE TABLE t(
.....  
)DEFAULT CHARACTER SET ...
COLLATION ...

如果指定CHARACTER SET和COLLATE两者，则使用这些值
如果只指定CHARACTER SET，则使用此字符集及其默认的校对（如SHOW CHARACTER SET的结果中所示）
如果既不指定CHARACTER SET，也不指定COLLATE，则使用数据库默认

甚至于可以在一张表中的不同列指定不同的字符集和校对，也可以在ORDER BY排序或者是相关的聚合函数使用时指定临时的校对。

安全管理

数据库服务器通常包含关键的数据，确保这些数据的安全和完整需要利用好权限控制。用户管理+权限管理。

首次使用MySQL时，它会自动创建一个root用户，root用户对于整个DBMS服务器有着全部的控制。但我们希望某个用户对某张表有着xxx权限，而不是想干什么就干什么。

访问控制的目的不仅仅是防止恶意攻击，更是阻止用户的不小心操作。如果用户没有相应的权限，即使它不小心执行了高危操作也不会执行。
应该严肃对待root登录的使用。仅在绝对需要时使用它（或许在你不能登录其他管理账号时使用）。不应该在日常的MySQL操作中使用root

用户账号及信息存储在名为mysql的数据库中的user表中
创建新用户的三种方式
- CREATE USER user_name@host_name IDENTIFIED BY [PASSWORD] ''(MySQL8没有PASSWORD函数了)
- 向mysql.user表中插入一条新用户记录
- 使用grant语句
删除用户：DROP USER username;
用户访问权限：
- SHOW GRANTS FOR user_name;(USAGE on …表示没有任何权限）
- 授权命令：GRANT [权限选项] on 表 to 用户
- 撤销权限：将授权命令中的GRANT改为REVOKE，TO改FROM即可。
权限授予和撤销的维度：
- 整个服务器，GRANT ALL 或者 REVOKE ALL
- 一个数据库
- 一张表
- 一列
可授予权限列表（上网搜）
更改口令（为其它用户，为自己）

数据库维护

数据的备份（防止数据意外丢失）：通常需要写shell脚本

命令行实用程序mysqldump转储所有数据库内容到某个外部文件
命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）

数据库的维护：

ANALYZE TABLE table_name;：检查键是否正确
CHECK TABLE

查看日志文件：
启动时命令行参数意义

改善性能

MySQL是用一系列的默认设置预先配置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等（为查看当前设置，可使用SHOW VARIABLES；和SHOW STATUS;）
MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法
一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快
使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句
必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句
LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
用UNION代替一系列的OR

如果感觉本文对你有所帮助的话，麻烦给笔者一个三连，你的支持是我前进的动力。一起加油！！！
MySQL的下一步学习就是原理，理论部分了，对于MySQL必知必会书中后半部分的一个深入。这里我准备去看MySQL到底是怎样运行的，据说这本书还行，继续坚持记笔记。

ZW钟文

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL必知必会21-30章学习笔记（DDL，视图，触发器，存储过程，事务管理）

MySQL必知必会这本书也算是看完了，它基本上提到MySQL中所有常见的语法以及写法。虽然后面的事务管理，备份和安全，授权与创建用户以及改善数据库性能只是提一下，但其实在往深处看就是MySQL自身的一些实现原理了，而不是语法以及用法的问题了。综合来说，我觉得从这本书中最应该学习的是如何写出一手好的DML语句（即SELECT,INSERT,DELETE,UPDATE语句），最好是可以解决一些偏难的查询SQL。至于建表，触发器，存储过程这些看似复杂，但实际上核心也就是DML语句。...
复制链接

扫一扫