数据库（七）

本篇博客学习内容为视图、触发器、事务、存储过程、函数、数据备份及流程控制。

什么是视图？

视图是由一张表或多张表的查询结果构成的一张虚拟表，建立一张视图后会在数据库中保留一个以 frm 后缀结尾的文件，只保留了数据结果，所有的数据都来自 sql 语句。

为什么使用视图？

在进行多表查询的时候，sql 语句会非常非常长，比如

select t1.student_id from (select student_id,num from score where course_id = (select cid from course where cname = '物理')) as t1 join(select student_id,num from score where course_id = (select cid from course where cname = '生物')) as t2 on t1.student_id = t2.student_id where t1.num > t2.num;

看是不是很长，这还只是三表查询，如果遇到更加复杂的表结构肯定会更长，不过那样的话对表的维护困难也加大了。如果每次都编写需要得到相同数据的 sql 语句会是一件很麻烦的事，可以把经常需要查询的 sql 语句转变为视图就可以避免重复写 sql 语句的问题。

视图除了可以减少 sql 语句的编写次数，还可以使用不同的视图来展示不同数据的访问，那么给某些用户设置权限不就可以了吗？注意，设置的权限要么只能看某张表的全部数据，要么只能看某张表中的某个 column 的数据，也就是列数据，列数据只是保存了字段名，比如说我要查看我的当月工资，是需要查看一行数据的，这样权限就帮不了忙了。（当然可以加 where 条件，在这里是介绍视图）

使用方法

创建视图

Mysql> create [or replace] view 视图名 [(column_list)] as select_statement;

加上 or replace 时如果已经存在相同视图则替换原有视图，column_list 指定哪些字段要出现在视图中。注意：由于是一张虚拟表，视图中的数据实际来源于其他表，所以在视图中的数据不会出现在硬盘上，也就是只会保存一份数据结构。

使用视图

视图是一张虚拟表，所以使用方式与普通表没有区别。

查看视图

1. 查看数据结构

mysql> desc view_name;

1. 查看创建语句

mysql> show create view view_name;

修改视图

mysql> alter view_name select_statement;

删除视图

mysql> drop view view_name;

具体使用

案例一：简化多表 sql 语句

# 准备数据
mysql> create database db02 charset utf8;
mysql> use db02;
mysql> create table student(
       s_id int(3),
       name varchar(20),
       math float,
       chinese float);
mysql> insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);
mysql> create table stu_info(
       s_id int(3),
       class varchar(50),
       addr varchar(100));
mysql> insert into stu_info values(1,'二班','安徽')，(2，'二班','湖南'),(3,'三班','黑龙江')；
# 创建视图包含编号、学生姓名、班级
mysql> create view stu_v (编号,姓名,班级) as select student.s_id,student.name,stu_info.class from student,stu_info where student.s_id = stu_info.s_id;
# 查看视图中的数据
mysql> select * from stu_v;

案例二：隔离数据

# 创建工资表
mysql> create table salarys(
       id int primary key,
       name char(10),
       salary double,
       dept char(10));
mysql> insert into salarys values
       (1,'刘强东',800000,'市场'),
       (2,'马云',899990,'市场'),
       (3,'李彦宏',989090,'市场'),
       (4,'马化腾',88889999,'财务');
# 创建市场部视图
mysql> create view dept_sc as select * from salarys where dept = '市场';
mysql> select * from dept_sc;

注意：对视图数据的 insert update delete 会同步到原表中，但由于视图可能是部分字段，很多时候会失败。

总结：mysql 可以分担程序中的部分逻辑，但这样一来后续的维护会变得更麻烦。如果需要改表结构，那意味着视图也需要相应的修改，没有直接在程序中修改 sql 来的方便。

什么是触发器？

触发器是一段与表有关的 mysql 程序，当这个表在某个时间点发生了某种事件时，将会自动执行相应的触发器程序。

何时使用触发器

当我们想要在一个表记录被更新时做一些操作时就可以说使用触发器，但是完全可以在 python 中来完成这个事情。

创建触发器

语法

mysql> create trigger t_name t_time t_event on table_name for each row
begin
stmts...
end

支持的时间点（t_time）：事件发生之前和之后 before|after

支持的事件（t_event）：update、insert、delete

在触发器中可以访问到将被修改的那一行数据，根据事件不同能访问的也不同，update 可用 old 访问旧数据，new访问新数据，insert 可用 new 访问新数据，delete 可用 old 访问旧数据。

可以将 new 和 old 看做一个对象，其中封装了修改的数据的所有字段。

使用触发器

案例

有 cmd 表和错误日志表，需求：在 cmd 执行失败时自动将信息存储到错误日志表中。

# 准备数据
mysql> create table cmd(
       id int primary key auto_increment,
       user char(32),
       priv char(10),
       cmd char(64),
       sub_time datetime, # 提交时间
       success enum('yes','no')); # 0代表执行失败
# 错误日志表
mysql> create table errlog(
       id int primary key auto_increment,
       err_cmd char(64),
       err_time datetime);
# 创建触发器
mysql> delimiter //
mysql> create trigger trigger1 after insert on cmd for each row
begin
if new.success = 'no' then
    insert into errlog values(null,new.cmd,new.sub_time);
end if;
end //
delimiter;
# 往表 cmd 中插入记录，触发触发器，根据 if 条件决定是否需要插入错误日志
mysql> insert into cmd(
       user,
       priv,
       cmd,
       sub_time,
       success
) values
       ('thales','0755','ls-l /etc',now(),'yes'),
       ('thales','0755','cat /etc/passWord',now(),'no'),
       ('thales','0755','user ass xxx',now(),'no'),
       ('thales','0755','ps aux',now(),'yes');
# 查看错误日志表中的记录是否有自动插入
mysql> select * from errlog;

delimiter用于修改默认的行结束符，由于在触发器中有多条 sql 语句需要使用分号来结束，但是触发器是一个整体，所以需要先更换默认的结束符（这里修改的只是客户端的结束符，服务端还是以分号结束），在触发器编写完后再讲结束符设置回分号

注意：外键不能触发事件，主表删除了某个主键，从表也会相应的删除数据，但是并不会执行触发器，并且触发器中不能使用事务，相同时间点的相同事件的触发器，不能同时存在。

删除触发器

语法

mysql> drop trigger trigger_name;
# 删除上面创建的触发器
mysql> drop trigger trigger1;

什么是事务？

mysql 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也需要删除和该人员相关的信息，如信箱、文章等，这样，这些数据库操作就构成一个事务。事务是逻辑上的一组操作，要么都成功，要么都失败。

• 在 mysql 中只有使用了 InnoDB 数据库引擎的数据库或表才支持事务；
• 事务处理可以用来维护数据库的完整性，保证成批的 sql 语句要么都执行，要么都不执行；
• 事务用来管理 insert、update、delete语句

事务的四个特性

一般来说，事务必须满足四个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样；
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性的完成预定的工作；
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（Read committed）、可重复读（Repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 mysql 命令行的默认设置下，事务都是自动提交的，即执行 sql 语句后就会马上执行 commit 操作。因此要显式的开启一个事务必须使用命令 begin 或 start transaction，或者执行命令 set autocommit=0，用来禁止使用当前会话的自动提交。

事务控制语句

• begin 或 start transaction：显式的开启一个事务；
• commit：也可以使用 commit work，不过二者是等价的。commit 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• rollback：也可以使用 rollback work，二者也是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• savepoint identifier：savepoint 允许在事务中创建一个保存点，一个事务中可以有多个 savepoint；
• release savepoint identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• rollback to identifier：把事务回滚到标记点；
• set transaction：用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 read uncommitted、read committed、repeatable read和 serializable。

mysql 事务处理的两种方式

1. BEGIN，ROLLBACK，COMMIT 来实现

• BEGIN：开始一个事务
• ROLLBACK：事务回滚
• COMMIT：事务确认

1. 直接使用 set 来改变 mysql 的自动提交模式

• SET AUTOCOMMIT=0：禁止自动提交
• SET AUTOCOMMIT=1：开启自动提交

事务的用户隔离级别

数据库使用者可以控制数据库工作在哪个级别下，就可以防止不同的隔离性问题。

• read uncommitted：不做任何隔离，可能脏读、幻读；
• read committed：可以防止脏读，不能防止不可重复读和幻读；
• repeatable read：可以防止脏读，不可重复读，不能防止幻读；
• serializable：数据库运行在串行化实现，所有问题都没有，就是性能低。

修改隔离级别

查询当前级别

mysql> select @@tx_isolation;

修改级别

mysql> set global transaction isolation level Repeatable read;

使用事务

start transaction：开启事务，在这条语句之后的 sql 将处在同一事务，不会立即修改数据库

commit：提交事务，让这个事务中的 sql 立即执行数据的操作

rollback：回滚事务，取消这个事务，这个事务不会对数据库中的数据产生任何影响。

案例：转账过程中发生异常

# 准备数据
mysql> create table account(
       id int primary key auto_increment,
       name varchar(20),
       money double);
insert into account values(1,'赵大儿子',1000);
insert into account values(2,'刘大牛',1000);
insert into account values(3,'猪头三',1000);
insert into account values(4,'王进',1000);
insert into account values(5,'黄卉',1000);

# 赵大儿子刘大牛佳转账1000块
# 未使用事务
update account set money = money - 1000 where id = 1;
update account set moneys = money - 1000 where id = 1; # money打错了导致执行失败

# 在Python中使用事务处理
sql = 'update account set money = money - 1000 where id = 1;'
sql2 = 'update account set moneys = money + 1000 where id = 2;' # money打错了导致执行失败
try:
    cursor.execute(sql)
    cursor.execute(sql2)
    conn.commit()
except:
    conn.rollback()

注意：事务的回滚的前提是能捕捉到异常，否则无法决定何时回滚，python 中很简单就可以实现，另外 mysql 中需要使用存储过程才可以捕获异常。

什么是存储过程？

存储过程是一组任意的 sql 语句集合，存储在 mysql 中，调用存储过程时将会执行其包含的所有 sql 语句，与 python 中的函数类似。

为什么使用存储过程？

回顾触发器与视图其实都是为了简化应用程序中 sql 语句的书写，但是还是需要编写，而存储过程中可以包含任何的 sql 语句，包括视图、事务、控制流程等，这样一来，用用程序可以从 sql 语句中完全解放出来，mysql 可以替代应用程序完成数据相关的逻辑处理。

三种开发方式对比

1. 应用程序仅负责业务逻辑编写，所有与数据相关的逻辑都交给 mysql 来完成，通过存储过程（推荐使用）

   优点：应用程序与数据处理完全解耦合，一对复杂的 sql 被封装成了一个简单的存储过程，考虑到网络环境因素，效率高，应用程序开发者不需要编写 sql 语句，开发效率高。

   缺点：python 语法与 mysql 语法区别巨大，学习成本高，并且各种数据库的语法大不相同，所以移植性非常差，应用程序开发者与 DBA 的跨部门沟通成本高，造成整体效率低。

2. 应用程序不仅编写业务逻辑，还需要编写所有的 sql 语句

   优点：扩展性高，对于应用程序开发者而言，扩展性和维护性相较于第一种都有所提高。

   缺点：执行效率低，由于需要将对象的操作转化为 sql 语句，且需要通过网络发送大量的 sql 语句。

创建存储过程

语法

mysql> create procedure pro_name(p_type p_name data_type)
begin
sql 语句......流程控制
end

p_type：参数类型

in：表示输入参数

out：表示输出参数

inout：表示既能输入又能输出

p_name：参数名称

data_type：参数类型 mysql 支持的所有数据类型

案例：使用存储过程完成对 student 表的查询

delimiter //
create procedure p1(in m int,in n int,out res int)
begin
    select *from student where chinese > m and chinese < n;
    #select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败
    set res = 100;
end//
delimiter ;
set @res = 0;
#调用存储过程
call p1(70,80,@res);
#查看执行结果
select @res;

注意：存储过程的 out 类参数必须是一个变量，用来装输出数据的，不可是一个值

python 中调用存储过程

import  pymysql
#建立连接
conn = pymysql.connect(
    host="127.0.0.1",
    user="root",
    password="admin",
    database="db02"
)
# 获取游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
​
# 调用用存储过程
cursor.callproc("p1",(70,80,0)) #p1为存储过程名 会自动为为每个值设置变量,名称为 @_p1_0,@_p1_1,@_p1_2
# 提取执行结果是否有结果取决于存储过程中的sql语句
print(cursor.fetchall())
# 获取执行状态
cursor.execute("select @_p1_2")
print(cursor.fetchone())

此处 pymysql 会自动将参数都设置一个变量所以可以直接传入一个值，当然值如果作为输出参数的话，传入什么都可以。

删除存储过程

drop procedure 过程名;

修改存储过程的意义不大，不如删除重写。

查看存储过程

# 当前库所有存储过程名称
mysql> select 'name' from mysql.proc where db = 'db02' and 'type' =      'procedure';
# 查看创建语句
mysql> show create procedure p1;

存储过程中的事务应用

存储过程中支持任何的 sql 语句也包括事务。

案例：模拟转账中发送异常，进行回滚操作

delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN 
    DECLARE exit handler for sqlexception 
    BEGIN 
        -- ERROR 
        set p_return_code = 1; 
        rollback; 
    END; 
    # exit 也可以换成continue 表示发送异常时继续执行
    DECLARE exit handler for sqlwarning 
    BEGIN 
        -- WARNING 
        set p_return_code = 2; 
        rollback; 
    END; 

    START TRANSACTION; 
    update account set money = money - 1000 where id = 1;
    update account set moneys = money - 1000 where id = 1; # moneys字段导致异常
    COMMIT; 

    -- SUCCESS 
    set p_return_code = 0; #0代表执行成功

END //
delimiter ;

#在mysql中调用存储过程
set @res=123;
call p5(@res);
select @res;

总结：抛开沟通成本、学习成本，存储过程无疑是效率最高的处理方式。

内置函数

在SQL 语句中，表达式可用于一些诸如SELECT语句的ORDER BY 或 DELETE或 UPDATE语句的 WHERE 子句或 SET语句之类的地方。使用文本值、column值、NULL值、函数、 操作符来书 写 表达式。 本章叙述了可用于书写MySQL表达式的函数和操作符。

这些内置函数大大提高了我们的开发效率

字符相关函数

数学相关函数

日期相关函数

其他函数

自定义函数

语法

mysql> create function f_name(paramters)
return dataType
return value;

说明：paramters 只能是 in 输入参数、参数名、类型必须有返回值，不能加 begin 和 end，returns 后面是返回值的类型，这里不加分号，return 后面是要返回的值。

案例：将两数相加

mysql> create function addfuntion(a int,b int)
returns int return a + b;
# 执行函数
mysql> select addfuntion(1,1);

注意：函数只能返回一个值，函数一般不涉及数据的增删查改，就是一个通用的功能，调用自定义的函数与调用系统的一直，不需要 call 使用 select 可获得返回值，函数中不能使用 sql 语句，就像在 java 中不能识别 sql 语句一样（没学过java。。。）

使用 mysqldump 程序进行备份

mysqldump -u -p db_name [table_name,,,] > fileName.sql

注意：这是命令行命令

可以选则要备份那些表，如果不指定代表全部备份

# 示例
# 单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table table2 > db1-table1-table2.sql

# 多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

# 备份所有
mysqldump -uroot -p123 --all-databases > all.sql

使用 mysql 进行恢复

1. 退出数据库后

mysql -u -p < filenam.sql

1. 不用退出数据库
   1. 创建空数据库
   2. 选择数据库
   3. 然后使用 source filename 来进行还原

mysql> use db1;
mysql> source /root/db1.sql

数据库迁移

# 务必保证在相同版本之间迁移
mysqldump -h 源ip -uroot -p123 --databases db1 | mysql -h 目标ip -uroot -p456

if 语句

if 条件 then 语句;end if;第二种 if else if 条件 then 语句1;else if 条件

then 语句2;else 语句3;end if;

案例：编写过程实现 输入一个整数 type 范围1-2 输出 type=1 or type=other;

mysql> create procedure showType(in type int,out result char(20))
begin
if type = 1 then 
set result = "type = 1";
elseif type = 2 then 
set result = "type = 2";
else 
set result = "type = other";
end if;
end

case 语句

与 switch 一样，进行选择执行

mysql> create procedure caseTest(in type int)
begin
CASE type 
when 1  then select "type = 1";
when 2  then select "type = 2";
else select "type = other";
end case;
end

定义变量

mysql> declare 变量名 类型 default 值;
mysql> declare i int default 0;

while 循环

# 循环输出10次 hello mysql
mysql> create procedure showHello()
begin
declare i int default 0;
while i < 10 do
select 'hello mysql';
end while;
end

loop 循环

没有条件，需要自己定义结束语句

# 输出10次 hello mysql
mysql> create procedure showLoop()
begin
declare i int default 0;
aloop:loop
select 'hello loop';
set i > 9 then leave aloop;
end if;
end loop aloop;
end

repeat 循环

# 类似do while
# 输出10次hello repeat
mysql> create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end

# 输出0-100之间的奇数
mysql> create procedure showjishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end