MySQL 之【视图】【触发器】【存储过程】【函数】【事物】【数据库锁】【数据库备份】

1.视图

 视图:是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据

视图有如下特点; 
　　1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。 
　　2. 视图是由基本表（实表）产生的表（虚表）。 
　　3. 视图的建立和删除不影响基本表。 
　　4. 对视图内容的更新（添加、删除和修改）直接影响基本表。 
　　5. 当视图来自多个基本表时，不允许添加和删除数据。

　1.创建视图

1	create view 视图名称  as sql 查询语句

　2.使用视图

1	select * from 视图名称;

　3.更新视图

1	alter view 视图名称 AS SQL语句

　4. 删除视图

1	drop view ren_view;

2.触发器-trigger

触发器：监视某种情况，并触发某种操作。

触发器创建语法四要素：1.监视地点(table)

　　　　　　　　　　　2.监视事件(insert/update/delete)

　　　　　　　　　　   3.触发时间(after/before)

　　　　　　　　　　　4.触发事件(insert/update/delete)

1.创建触发器语法

1 2 3 4 5 6 7

create trigger triggerName  after/before  insert/update/delete      on 表名 for each row #这句话是固定的  begin      #需要执行的sql语句  end 注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发 注意2:insert/update/delete:只能选一个

创建两张表

#商品表 create table goods(   id int primary key auto_increment,   name varchar(20),   num int );

#订单表 create table order_table(     oid int primary key auto_increment,     gid int,     much int );

添加3条商品数据

1	insert into goods(name,num) values('商品1',10),('商品2',10),('商品3',10);

如果我们在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事

1.往订单表插入一条记录

1	insert into order_table(gid,much) values(1,3);

2.更新商品表商品1的剩余数量

1	update goods set num=num-3 where id=1;

 

现在，我们来创建一个触发器：

1 2 3 4 5

create trigger tg1 after insert on  order_table for each row begin       update goods set num = num -3 where id = 1; end

这时候我们只要执行：

1	insert into order_table(gid,much) values(1,3);

 

会发现商品1的数量变为7了，说明在我们插入一条订单的时候，

触发器自动帮我们做了更新操作。

但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：

1	insert into order_table(gid,much) values(2,3);

 

执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。

 

我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。

对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。

所以现在我们可以这样来改我们的触发器:

1 2 3 4 5

create trigger tg2 after insert on order_table for each row begin  update goods set num = num-new.much  where id = new.gid; end

第二个触发器创建完毕，我们先把第一个触发器删掉

1	drop trigger tg1;

再来测试一下，插入一条订单记录：

1	insert into order_table(gid,much) values(2,3)

执行完发现商品2的数量变为7了，现在就对了。

现在还存在两种情况：

1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

 对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示旧表中的值，old.列名可以引用原(旧)表中的值。

那我们的触发器就该这样写：

1 2 3 4 5

create trigger tg3 afert delete  on order_table for each row bigen     update goods set num = num + old.much where id = old.gid;-- (注意这边的变化) end

2.当用户修改一个订单的数量时，我们触发器修改怎么写?

1 2 3 4 5

create trigger tg4 after update on order_table for each row begin     update goods set num = num+old.much-new.much where id = old.gid; end

　　

3.存储过程

 MySQL数据库在5.0版本后开始支持存储过程，那么什么是存储过程呢？怎么创建、查看和删除存储过程呢？存储过程有什么优点？

　存储过程：类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。

 1. 查看现有的存储过程

1	show procedure status;

　2 .删除存储过程

1	drop procedure 存储过程名称;

   3. 调用 存储过程

1	call 存储过程名称(参数入/出类型 参数名 数据类型);

 4.创建存储过程

1.体会封装

1 2 3 4 5

#1.体会封装 create procedure p1 () begin     select * from account;   end

2.SQL 体会参数

1 2 3 4 5 6 7 8 9

create procedure p2(in i int,out n varchar(50)) begin  select name into n from account where id = i; end   -- 调用 set @name =null; CALL p2(1,@name); select @name;

注意1: mysql中有三种出入参数类型:分别为:1. in 入参类型  2.out 出参类型   3. inout 出入参类型 

注意2: into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量.

 in入参示例

 out出参示例

 inout出入参示例

3.SQL 体会控制

1 2 3 4 5 6 7 8 9

#3.SQL 体会控制  create procedure p3(in x int,in c char(1))  begin     if c ='d' then          select * from account where money >x;    else          select * from account where money <x;      　　end if; end

 4.体会循环:计算1-100累加的和,并且返回计算结果.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

#4.体会循环:计算1-100累加的和,并且返回计算结果. create procedure p4(inout n int) begin       DECLARE sum int default 0; -- 设置总和变量,并且指定初始值0       declare i int; -- 声明变量       set i = 0;    -- 通过set为变量设置值 　　  while i<=n DO  -- 开始循环             set sum = sum +i;             set i = i+1;       end while; -- 结束循环   　　  select sum; -- 提供结果                            set n = sum;--将计算结果提供给 输出变量 n; end;                    -- 调用:  set @n = 100;  call p4(@n);  select @n;

存储过程优点：
        1、存储过程增强了SQL语言灵活性。

　　　　　　存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
        2、减少网络流量，降低了网络负载。

　　　　　　存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
        3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。

　　　　　　一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：     

　 　1、扩展功能不方便

　　 2、不便于系统后期维护

4.函数　

 MySQL提供的内建函数:

 MySQL内建函数

 

更多函数: 官方猛击这里

1、自定义函数

1 2 3 4 5 6 7 8 9

CREATE FUNCTION fun1(i1 int,i2 int)   RETURNS INT //设置返回类型   BEGIN     DECLARE sum int default 0;     set sum = i1+i2;     RETURN(sum); //返回结果 end

2.调用自定义函数

1 2 3 4 5

#直接调用自定义函数 select fun1(1,5);   #在sql语句中使用自定义函数 select fun1(参数1,参数2),name from 表名

3.删除自定义函数

1	DROP FUNCTION fun_name;

 4.函数与存储过程的区别:

 

5.事物处理

 一、 什么是事务

　　   一组sql语句批量执行,要么全部执行成功,要么全部执行失败

二、为什么出现这种技术

　　为什么要使用事务这个技术呢？ 现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。这样很抽象，举个例子： 

A 给B 要划钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况很难处理。

三、事物的特性　

　　80年代中国人结婚四大件：手表、自行车、缝纫机、收音机（三转一响）。要把事务娶回家同样需要四大件，所以事务很刻薄（ACID），四大件清单：原子性(Atom）、一致性（Consistent）、隔离性（Isolate）、持久性（Durable）。ACID就是数据库事务正确执行的四个特性的缩写。

• 原子性：要么不谈，要谈就要结婚！

　　　　对于其数据修改，要么全都执行，要么全都不执行。

• 一致性：恋爱时，什么方式爱我；结婚后还得什么方式爱我；

　　　　数据库原来有什么样的约束，事务执行之后还需要存在这样的约束，所有规则都必须应用于事务的修改，以保持所有数据的完整性。

• 隔离性：闹完洞房后，是俩人的私事。

　　　　一个事务不能知道另外一个事务的执行情况（中间状态）

• 持久性：一旦领了结婚证，无法后悔。

　　　　即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

另外需要注意:

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

四、事务控制语句：

• BEGIN 或 START TRANSACTION；显式地开启一个事务；

• COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

• ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT : 保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时 可以指定在什么位置上进行回滚操作.

注意: SET AUTOCOMMIT=0 ;禁止自动提交 和 SET AUTOCOMMIT=1 开启自动提交.

五、例子: 鲁班转账给后羿

1.创建表 

1 2 3 4 5

create table account(     id int(50) not null auto_increment primary key,     name VARCHAR(50) not null,     money DOUBLE(10,2) not NULL );

2.插入数据

1	insert into account (id,name,money) values(1,'鲁班',250),(2,'后羿',5000);

3.执行转账

1 2 3 4 5 6 7 8 9 10

start transaction; -- 开启事物  --  执行sql语句操作  update account set money = money - 500 where id =1;    update account set money = money+500 where id = 2;   commit;  -- 手动提交事物 rollback; -- 回滚事物   --  查看结果 select * from account;

 4.保存点使用

1 2 3 4 5 6 7 8 9

START TRANSACTION ;   insert into account (name,money) values('李元芳',1000);   SAVEPOINT s1; -- 设置保存点   insert into account (name,money) values('张桂枝',1500);   ROLLBACK to s1; -- 事物回滚到保存点<br>COMMIT; --提交事物

　　

6.数据锁

需求: 有一个账户,两个人在同一时间要对此账户操作,A要对账户充值100块,B要从账户中取出100块.操作前都要先看一下账户的 余额然后再操作.

 示例

1. 锁的基本概念

　　当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2. 锁的基本类型

　　多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

　　2.1 共享锁（Shared Lock，也叫S锁）

　　　　共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

　　2.2 排他锁(Exclusive Lock，也叫X锁)

　　　　排他锁(X)表示对数据进行写操作。如果一个事务对 对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了).

 

3. 实际开发中常见的两种锁:

　　3.1悲观锁 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.

注意：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性.因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- 0.开始事务 start transaction;   -- 1.查询账户余额 set @m = 0; -- 账户余额 select money into @m from account where id = 1 for update; select @m;   -- 2.修改账户余额 update account set money = @m -100 where id = 1;   select * FROM account where id = 1; -- 3. 提交事务 commit;

在另外的查询页面执行:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- 0.开始事务 start transaction;   -- 1.查询账户余额 set @m = 0; -- 账户余额 select money into @m from account where id = 1 for update; select @m;   -- 2.修改账户余额 update account set money = @m +100 where id = 1;   select * FROM account where id = 1; -- 3. 提交事务 commit;

会发现当前查询会进入到等待状态,不会显示出数据,当上面的sql执行完毕提交事物后,当前sql才会显示结果.

注意1:在使用悲观锁时,如果表中没有指定主键,则会进行锁表操作.

注意2: 悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。　　

　　 3.2 乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。　

使用乐观锁的两种方式:

　　1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现 方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数 据。

 代码示例:

1 2 3 4 5 6 7 8 9 10 11 12 13

-- 1.查询账户余额 set @m = 0; -- 账户余额 select money into @m from account where id = 1 ; select @m; -- 2.查询版本号 set @version = 0; -- 版本号 select version into @version from account where id = 1 ; select @version;   -- 3.修改账户余额 update account set money = @m -100,version=version+1 where id = 1 and version = @version;   select * FROM account where id = 1;

　　2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳 （datatime）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

 

悲观锁与乐观锁的优缺点:

　　两种锁各有其有点缺点,不能单纯的讲哪个更好.

　　　　乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

　　　　但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.

 7.数据库备份

　　mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

　　mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

1.使用mysqldump实现逻辑备份

1 2 3 4 5 6 7 8 9 10 11 12 13

#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql   #示例： #单库备份 mysqldump -uroot -p123456 db1 > c:/db1.sql mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql   #多库备份 mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql   #备份所有库 mysqldump -uroot -p123456 --all-databases > c:/all.sql

 参数说明

 unknown option '--no-beep'

2.恢复逻辑备份　

1 2 3 4 5

#在mysql命令下，用source命令导入备份文件： mysql>  USE 数据库名; mysql>  source 备份文件.sql;   注意：只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令，会报错，因为cmd是直接调用mysql.exe来执行命令的。