事务 视图 触发器及存储过程

一、事务

事务的概念

所有的SQL语句要么全部执行成功，要么全部执行失败

事务的四大特性

事务特性	含义
原子性（Atomicity）	事务是工作的最小单元，整个工作单元要么全部执行成功，要么全部执行失败
一致性（Consistency）	事务执行前与执行后，数据库中数据应该保持相同的状态。如：转账前总金额与转账后总金额相同。
隔离性（Isolation）	事务与事务之间不能互相影响，必须保持隔离性。
持久性（Durability）	如果事务执行成功，对数据库的操作是持久的。

事务的应用场景

• 转账的操作

drop database if exists db04;

create database db04;

use db04;

-- 创建数据表  
CREATE TABLE account (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(10),  
    money DOUBLE  
);  

-- 添加数据  
INSERT INTO account (name, money) VALUES ('a', 1000), ('b', 1000);

• 转账需求

模拟a给b转500元钱，一个转账的业务操作最少要执行下面的2条语句：

a账号-500

b账号+500

-- 转账操作

-- 有两条UPDATE的更新操作

-- a转500给b
update account set money=money-500 where name='a';

update account set money=money+500 where name='b';

-- 还原
update account set money=1000;

假设当a账号上-500元，服务器崩溃了。b的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

手动提交事务

MySQL中可以有两种方式进行事务的操作：

1.手动提交事务

2.自动提交事务（默认是自动提交）

手动提交事务的SQL语句

功能	SQL语句
开启事务	start transaction/begin
提交事务	commit
回滚事务	rollback

手动提交事务使用过程：

 案例演示1：事务提交

模拟a给b转500元钱（成功）目前数据库数据如下：

1. 使用DOS控制台进入MySQL
2. 执行以下SQL语句： 1.开启事务， 2.xiaodong账号-500， 3.xiaobiao账号+500
3. 使用Navicat查看数据库：发现数据并没有改变
4. 在控制台执行commit提交任务：
5. 使用Navicat查看数据库：发现数据改变

 案例演示2：事务回滚

首先还原数据：

update account set money=1000;

模拟a给b转500元钱（失败）

1. 在控制台执行以下SQL语句：1.开启事务， 2.a账号-500
2. 使用Navicat查看数据库：发现数据并没有改变
3. 在控制台执行rollback回滚事务：
4. 使用Navicat查看数据库：发现数据没有改变

• 结论：
• 如果事务中SQL语句没有问题，那就commit提交事务，会对数据库数据的数据进行改变。
• 如果事务中SQL语句有问题，那就rollback回滚事务，会回退到开启事务时的状态。

自动提交事务

MySQL默认每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务

案例演示3：自动提交事务

update account set money=1000;			-- 默认情况下,每一条DML都是一个独立的事务

取消自动提交

• 查看MySQL是否开启自动提交事务

select @@autocommit;

如果是1：表示自动提交，默认值

如果是0：表示关闭了自动提交

• 取消自动提交事务

set @@autocommit = 0;

select @@autocommit;

 再次执行SQL语句：

select * from account;

update account set money=money-500 where name='a';

select * from account;

rollback;

select * from account;

 事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中，事务日志只有在得到commit命令才会同步到数据表中，执行完commit或rollback

都会清空事务日志(rollback，断开连接)

原理图

 事务的原来解释：

1. 如果没有开启事务，用户不使用日志文件，而是直接写到数据库
2. 如果查询，数据从表中查询出来以后，经过日志文件加工以后返回。
3. 如果回滚，清除日志文件，不会写到数据库中。

1.6 回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

回滚点的操作语句

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

具体操作：

1. 将数据还原到1000
2. 2开启事务
3. 3让a账号减2次钱，每次10块
4. 4设置回滚点：savepoint p1;
5. 5让a账号减2次钱，每次10块
6. 6回到回滚点：rollback to p1;
7. 7分析执行过程

-- 开启事务
begin;

-- a账号-10元
update account set money=money-10 where name='a';

-- a账号-10元
update account set money=money-10 where name='a';

-- 设置保存点
savepoint p1;

-- 查询账号信息
select * from account;

-- a账号-10元
update account set money=money-10 where name='a';

-- a账号-10元
update account set money=money-10 where name='a';

-- 回滚到指定的保存点
rollback to p1;

-- 查询账号信息
select * from account;

• 总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

二、事务的隔离级别

并发访问下事务产生的问题：

当同时有多个用户在访问同一张表中的记录，每个用户在访问的时候都是一个单独的事务。

事务在操作时的理想状态是：事务之间不应该相互影响，实际应用的时候会引发下面三种问题。应该尽量避免这些问题的发生。通过数据库本身的功能去避免，设置不同的隔离级别。

• 脏读： 一个事务（用户）读取到了另一个事务没有提交的数据
• 不可重复读：一个事务多次读取同一条记录，出现读取数据不一致的情况。一般因为另一个事务更新了这条记录而引发的。
• 幻读：在一次事务中，多次读取到的条数不一致

设置隔离级别

四种隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是
3	可重复读	repeatable read	否	否	是/否
4	串行化	serializable	否	否	否

• 1）Read uncommitted (读未提交)： 简称RU隔离级别，所有事务中的并发访问问题都会发生，可以读取到其他事务没有提交的数据
• 2）Read committed (读已提交)：简称RC隔离级别，会引发不可重复读和幻读的问题，读取的永远是其他事务提交的数据
• 3）Repeatable read (可重复读)：简称RR隔离级别，会引发幻读的问题，一次事务读取到的同一行数据，永远是一样
• 4）Serializable (串行化)： 可以避免所有事务产生的并发访问的问题 效率及其低下

安全和性能对比

• 隔离级别越高，安全性就越高，性能越低
• 隔离级别越第，安全性就越低，性能越高。

mysql相关的命令

查询全局事务隔离级别

 设置全局事务隔离级别

set global transaction isolation level 四种隔离;		-- 服务器只要不关闭一直有效

修改隔离级别后需要重启会话

脏读

在并发情况下，一个事务读取到另一个事务没有提交的数据，这个数据称之为脏数据，此次读取也称为脏读。

只有read uncommitted（读未提交）的隔离级别才会引发脏读。

• 将MySQL的事务隔离级别设置为read committed（读已提交）：

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

将数据还原：

 脏读演示

session-01	session-02
begin;
	begin;
select * from account where name='a';
	update account set money=money-200 where name='a';
select * from account where name='a';
	rollback;

 解决脏读

将全局的隔离级别进行提升
1.打开命令行a，设置全局的隔离级别为read committed：

set global transaction isolation level read committed;

再次执行：

session-01	session-02
begin;
	begin;
select * from account where name='a';
	update account set money=money-200 where name='a';
select * from account where name='a';
	rollback;

 不可重复读

概念： 在同一个事务中的多次查询应该出现相同的结果，两次读取不能出现不同的结果。

和脏读的区别：

脏读是读取前一事务未提交的脏数据，不可重复读是重复读取了前一事务已提交的数据，但2次读取的结果不同。

应用场景：比如银行程序需要将查询结果分别输出到电脑屏幕和写到文件中，结果在一个事务中针对输出的目的地，两次输出结果却不一致，导致文件和屏幕中的结果不同，银行工作人员就不知道以哪个为准了。

不可重复读演示

1). 将数据进行恢复，并关闭窗口重新登录。

update account set money=1000;

session-01	session-02
begin;
	begin;
select * from account where name='a';
	update account set money=money-200 where name='a';
	commit;
select * from account where name='a';

 两次查询输出的结果不同，到底哪次是对的？

解决不可重复读

1）将数据进行恢复

update account set money=1000;

-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

记得要重启窗口

session-01	session-02
begin;
	begin;
select * from account where name='a';
	update account set money=money-200 where name='a';
	commit;
select * from account where name='a';

观察变化：

 结论：为了保存多次查询数据一致，必须使用repeatable read隔离级别

幻读

概念：一次事务多次读取到的条数不一致而引发的问题；

在InnoDB（暂时理解是MySQL）中幻读在很多地方都得到了解决，但在一些特殊的情况下，还是会引发幻读问题；

为什么有的情况下能解决，有的情况下解决不了？因为一次事务多次读取到的条数不一致会导致有很多情况发生！

幻读解决情况1：

还原数据

update account set money=1000;

-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

session-01	session-02
begin;
	begin;
select * from account
	insert into account values(3,'c',1000);
	commit;
select * from account

观察变化：

 幻读问题得到解决

幻读解决情况2：

还原数据

案例：

session-01	session-02
begin;
	begin;
select sum(money) from account;
	insert into account values(3,'c',1000);
	commit;
select sum(money) from account;

观察变化：

 幻读问题出现情况1：

案例：

session-01	session-02
begin;
	begin;
select count(id) from account;
	insert into account values(3,'c',1000);
	commit;
select count(id) from account;
update account set money=0;
select count(id) from account;

观察变化：

 特殊情况：

案例：

session-01	session-02
begin;	begin;
select * from account;
	select * from account;
	insert into account values(3,"c",1000);
	commit;
select count(id) from account;
insert into account values(3,"c",1000);

观察变化：

Tips：严格意义来说，上述案例是MySQL的快照机制导致的，不能算幻读；关于幻读我们理解概念就行，即：两次读取到的条数不一致！这就是幻读

串行化

想要彻底的解决幻读，那么我们必须再把隔离级别调高，数据库的最高隔离级别为串行化（serializable）

串行化相当于锁表操作，即一个事务如果操作了某张表(增加、删除、修改)，那么就不允许其他任何事务操作此表，也不允许查询，等第一个事务提交或者回滚之后才可以操作，这样做效率及其低下，因此一般不会采用serializable隔离级别

串行化演示

开启一个银行窗口

-- 还原数据
truncate account;

INSERT INTO account (name, money) VALUES ('a', 1000), ('b', 1000);

set global transaction isolation level serializable; 	-- 设置隔离级别为串行化

2）执行案例：

session-01	session-02
begin;
	begin;
update account set money=money-500 where name='a';
	select * from account;

 在串行化隔离级别中，相当于锁表的操作，在一个事务对表进行任何的insert/update/delete等操作时，其他事务均不可对其进行操作；在读写上是串行的，并发能力极差；

三、触发器和视图

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

• MySQL中触发器共有3种：

触发器类型	NEW和OLD的使用
insert触发器	NEW 表示将要或者已经新增的数据
update触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
delete触发器	OLD 表示将要或者已经删除的数据

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容

触发器语法

create trigger trigger_name [after/before] [insert/update/delete] 
on table_name
for each row
begin
	......
end;

• after：在插入表之后执行
• before：在插入表之前执行（两种触发器效果一致）

建立一张日志表，存储日志

CREATE TABLE log  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

建立一张测试表：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 ;

• insert触发器

create trigger test1 after insert
on student
for each row
begin
	insert into log values(null,new.name,now());
end;

insert into student values(1,'小灰',18);

select * from log;  -- 查看日志表

• update触发器

  create trigger test2 after update
  on student
   for each row
  begin
  	insert into log values(null,concat('之前的值: ',old.name,';之后的值: ',new.name),now());
  end;
  
  
  update student set name='小蓝' where id=6; 
  
  select * from log;  -- 查看日志表

  

• delete触发器

create trigger test3 after delete
on student
 for each row
begin
	insert into log values(null,concat('删除的值: ',old.name),now());
end;

delete from student where id=6;
select * from log;  -- 查看日志表

• 查看当前数据库中的触发器

show triggers;

• 删除触发器

drop trigger trigger_name;

-- 示例
drop trigger test1;

视图

视图（View）是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图其实就是一个select返回的结果集，用于方便我们查询而创建的"临时表"，简化我们的查询语句。

• 视图的优势：

• ==方便：==使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• ==安全：==使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• ==数据独立：==一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

Tips：使用视图的大部分情况是为了保障数据安全性，提高查询效率；

创建测试表：

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'产品');


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`class_id`) values 
(1,'张三',1),
(2,'李四',1),
(3,'王五',2),
(4,'赵六',1),
(5,'孙七',3),
(6,'王八',3);

 创建视图

• 语法

CREATE [OR REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)] 
AS select_statement  
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

• OR REPLACE：如图视图名重复，则表示替换原有视图
• ALGORITHM：表示创建视图时所使用的算法
  • UNDEFINED：默认值，没有指定算法时一般采用merge算法
  • MERGE：合并算法，在基于视图创建新的视图时，将创建视图时所使用的select语句与将要创建新的视图的select语句进行合并处理，效率较高。
  • TEMPTABLE：临时表算法，在基于视图创建新的视图时，先执行原有视图的select语句，然后再执行新视图的select语句，查询两次，效率较低。

create or replace ALGORITHM=MERGE view test1 as 
select * from student;

• 查询视图：

• 修改视图：

需要注意的是：对视图进行任何的新增、修改、删除将会对直接作用到源表

• 删除视图中的记录：

• 新增记录到视图：

视图创建的其他参数

DEFINER：指出谁是视图的创建者

• USER：指定创建视图的用户，
• CURRENT_USER：当前登录的用户为创建视图的用户（默认值）

create or replace definer='root'@'localhost' view test2 as 
select * from student;

• SQL SECURITY：设置操作的视图的安全策略

•         INVOKER：验证查询的用户是否拥有权限访问视图及视图所引用的对象，也就是即需要拥有对视图的权限，又要拥有视图中锁涉及到的表的权限；
  • DEFINER：访问该视图时验证是否拥有对视图本身的权限；（默认值）

• 给zhangsan用户赋予访问student的权限：

测试完毕删除用户：

drop user 'zhangsan'@'%';

• WITH：更改视图数据时，检查数据
  • LOCAL：更改表中的数据时，如更改后的数据不符合当初创建视图时的条件时，则不予修改，并报如下错误：

CHECK OPTION failed 'dbName.viewName'

示例：

•  CASCADED：更改视图时，如更改后的数据不符合当初创建视图时的条件时，则数据将在视图中清除，默认值

```sql
-- 创建视图test5,当视图创建后,当基表修改的条件不符合此次创建视图的语句时数据清空
create view test5 as select * from student where class_id=1 with CASCADED check option;

 查看test5视图，发现id=2的记录在视图中已经被清空（源表的记录还有）

视图实质上是对源表的引用，对视图的任何操作都会落实到对应的表，如insert、update、delete等语句，drop删除视图不会影响源表，但源表如果被删除了（drop），那么视图将变得不可用。

修改视图

创建时修改

• 语法

CREATE [OR REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]  
AS select_statement  
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

• 示例：

create or replace view test4 as select * from student where class_id=1;

使用alter语句更改视图

• 语法

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { USER | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

• 示例：

alter ALGORITHM=TEMPTABLE view test4 as select * from student where class_id=3;

更新视图

• 语法：

update viewName set column=newValue

• 示例：

update test4 set name='qq' where id=5;

创建视图时，有下列内容之一，视图将不能做DML操作：

1）select子句中包含distinct

2）select子句中包含组函数

3）select语句中包含group by子句

4）select语句中包含order by子句

5）select语句中包含union 、union all等集合运算符

6）where子句中包含相关子查询

7）from子句中包含多个表

8）如果视图中有计算列，则不能更新

9）如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

删除视图

删除视图时，只能删除视图的定义，不会删除数据，也就是说不动基表

• 语法

DROP VIEW [IF EXISTS]   
view_name [, view_name] ...

drop view test1,test2,test3,test4;

四、存储过程和存储函数

MySQL中提供存储过程与存储函数机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

存储程序的优缺点

• 优点

通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程需要编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本

1）性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

2）复用：存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

3）安全：存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

• 缺点

1）如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加。

2）很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

3）我们在开发中，性能的瓶颈往往就是数据库层，我们应该想尽办法来优化数据库的性能，而不是徒增数据库的压力。

4）业务耦合，如果编写存储程序，那么业务逻辑滞后到了数据库端

存储过程的使用

• 语法

CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL语句
end ;

• 调用存储过程

call test1();

• 查看存储过程

-- 查看db01数据库中的所有存储过程
select name from mysql.proc where db='db04';

-- 查看存储过程的状态信息
show procedure status;

-- 查看存储过程的创建语句
show create procedure test1;

• 删除存储过程

drop procedure test1;

存储过程的语法

变量

• declare：声明变量

CREATE PROCEDURE test2 ()
begin
	
	declare num int default 0;		-- 声明变量,赋默认值为0
	select num+10;
	
end ;

call test2();			-- 调用存储过程

• set：赋值操作

CREATE PROCEDURE test3 ()
begin
	
	declare num int default 0;
	set num =20;			-- 给num变量赋值
	select num;
	
end ;

call test3();

• into：赋值

CREATE PROCEDURE test4 ()
begin
	
	declare num int default 0;			
	select count(1) into num from student;			-- 从student表中查询count(1)的结果,然后将结果赋值给num变量
	select num;
	
end ;

call test4();

 if语句

• 需求：

根据class_id判断是Java还是UI还是产品

CREATE PROCEDURE test5 ()
begin
	
	declare id int default 1;			
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
	select class_name;
	
end ;

call test5();

 传递参数

• 语法：

create procedure procedure_name([in/out/inout] 参数名  参数类型)

• in：该参数可以作为输入，也就是需要调用方传入值 , 默认
• out：该参数作为输出，也就是该参数可以作为返回值
• inout：既可以作为输入参数，也可以作为输出参数

in-输入参数

-- 定义一个输入参数
CREATE PROCEDURE test6 (in id int)
begin
	
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
	select class_name;
end ;

call test6(3);

 out-输出参数

-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
begin
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
end ;


call test7(1,@class_name);	-- 创建会话变量		

select @class_name;		-- 引用会话变量

 @xxx：代表定义一个会话变量，整个会话都可以使用，当会话关闭（连接断开）时销毁

@@xxx：代表定义一个系统变量，永久生效，除非服务器重启。

case语句

• 需求：

传递一个月的份值，返回所在的季节。

CREATE PROCEDURE test8 (in month int,out season varchar(10))
begin
	
	case 
		when month >=1 and month<=3 then
			set season='spring';
		when month >=4 and month<=6 then
			set season='summer';
		when month >=7 and month<=9 then
			set season='autumn';
		when month >=10 and month<=12 then
			set season='winter';
	end case;
end ;

call test8(9,@season);			-- 定义会话变量来接收test8存储过程返回的值

select @season;

whine循环

• 需求：

计算任意数的累加和

CREATE PROCEDURE test10 (in count int)
begin
	declare total int default 0;
	declare i int default 1;
	
	while i<=count do
		set total=total+i;
		set i=i+1;
	end while;
	select total;
end ;

call test10(10);

 repeat循环

计算任意数的累加和

CREATE PROCEDURE test11 (count int)		-- 默认是输入(in)参数
begin
	declare total int default 0;
	repeat 
		set total=total+count;
		set count=count-1;
		until count=0				-- 结束条件,注意不要打分号
	end repeat;
	select total;
end ;

call test11(10);

 loop循环

计算任意数的累加和

CREATE PROCEDURE test12 (count int)		-- 默认是输入(in)参数
begin
	declare total int default 0;	
	sum:loop							-- 定义循环标识
		set total=total+count;
		set count=count-1;
		
		if count < 1 then
			leave sum;					-- 跳出循环
		end if;
	end loop sum;						-- 标识循环结束
	select total;
	
end ;

call test12(10);

 存储过程和存储函数的区别

• 存储函数的限制比较多：例如不能用临时表、不能执行查询语句、只能用表变量等；而存储过程的限制较少，存储过程的实现功能要复杂些，而函数的实现功能针对性比较强。
• 返回值不同：存储函数必须有返回值，且仅返回一个结果值，存储过程可以没有返回的值，但是能返回结果集（out，inout）
• 调用时的不同：存储函数嵌入在SQL中使用，可以在select存储函数名（变量值），存储过程通过call语句调用call存储过程名。
• 参数的不同：存储函数的参数类型类似于in参数，没有类似于out和inout的参数。存储过程的参数类型有三种，in、out和inout：
  • in：数据只是从外部传入内部使用（值传递），可以是数值也可以是变量
  • out：只允许过程内部使用（不用外部数据），给外部使用的（引用传递：外部的数据会被先清空才会进入到内部），只能变量
  • inout：外部可以在内部使用，内部修改的也可以给外部使用，典型的引用 传递 只能传递变量。

---

• 创建一个普通的存储函数：

create function test1()
returns int			
begin

	declare num int default 0;
	
	set num=10;
	
	return num;
end;

• 执行存储函数：

select test1()

 

•  创建一个有参数的存储函数：

create function test2(num int)
returns int			
begin
	return num;
end;

• 执行存储函数：

select test2(10);

 五、数据库的三大范式

第一范式

概念：第一范式强调每一列的原子性，每列的数据必须保证其原子性，即每列的数据必须细化到不可再拆分

案例：

学号	姓名	班级
001	张三	Java01班
002	李四	Java02班
003	王五	UI01班
004	赵六	产品02班

在上述表中，班级字段存在数据冗余，如果我们想统计java学科的人数或者01班级的人数岂不是很尴尬？根据第一大范式条件必须保证每一列数据的原子性，我们可细化分如下：

学号	姓名	学科	班级
001	张三	Java	01班
002	李四	Java	02班
003	王五	UI	01班
004	赵六	产品	02班

第二范式

概念：在满足第一范式的条件下，每一列的数据都完全依赖于主键，不产生局部依赖，每张表都只描述一件事物，每一列都和主键相关联

也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

案例：

借阅表

借阅ID	书籍ID	书籍名称	出版社	数量	学号	学生姓名	手机号
001	1	高性能MySQL	清华大学出版社	1	zs-001	张三	110
001	2	MySQL技术内幕	北京大学出版社	2	ls-002	李四	120

缺点：大量重复数据，每存一条数据都会有重复的出版社、年龄、手机号等重复数据

根据第二大范式，表中的数据不能产生局部依赖，上述表中很明显出版社、书籍名称依赖于书籍id，而学生姓名、手机号等依赖于学号

根据第二范式细化：拆分成学生表、书籍表、借阅表

学生信息表：

学号	姓名	年龄	手机号
zs-001	张三	21	110
ls-002	李四	22	120

书籍表：

书籍ID	书籍名称	出版社
1	高性能MySQL	清华大学出版社
2	MySQL技术内幕	北京大学出版社

借阅表：

借阅ID	借阅书籍ID	借阅人学号	借阅数量
001	1	zs-001	1
002	2	zs-002	2

第三范式

概念：在满足第二范式的条件下，表中的每一列不存在传递依赖，每一列都直接依赖于主键

案例：

ID	姓名	年龄	所属部门	部门地点
001	张三	21	研发部	石家庄
002	李四	22	销售部	郑州
003	王五	25	研发部	济南

根据第三范式，每一列应该直接依赖于主键

我们应该拆分成一张用户名和一张部门表，通过建立外键来建立两表之间的关系

部门表：

部门id	部门名称	部门地点	部门简码	部门等级
001	研发部	石家庄	dev	1
002	行政部	郑州	admin	2
003	销售部	济南	sale	2

员工表：

ID	姓名	年龄	部门ID
001	张三	21	001
002	李四	22	002
003	王五	25	001

反范式化

一般我们设计表都会按照数据库的三大范式，但是在某些情况下我们查询的数据在多张表中，例如我们需要查询员工的信息并且希望带出员工的部门名称，这个时候我们必须使用join关联表查询，如果这些数据是查询非常频繁的，那么无疑会降低数据库的读性能

反范式设计表：

编号	姓名	年龄	部门id	部门名称
001	张三	21	001	研发部
002	李四	22	002	运营部

此时数据都在一张表，查询也不用join关联查询，以此提高读取性能

部门表：

部门id	部门名称	部门地点	部门简码	部门等级
001	研发部	石家庄	dev	1
002	运营部	郑州	admin	2
003	销售部	济南	sale	2

过分范式化带来的弊端

1. 过分的满足第一范式设计：即保证每一列的原子性，会给表带来非常多的列

ID	姓名	年龄	地址
001	张三	20	江西省南昌市洪城路128号8栋601
002	李四	23	江西省南昌市青云谱区洪都大道118号9栋301

过分满足第一范式：

ID	姓名	年龄	省份	城市	县区	道路	牌号
001	张三	20	江西省	南昌市	西湖区	洪城路	128号
002	李四	23	江西省	南昌市	青云谱区	洪都大道	118号

过分的满足第一范式带来非常多的列，导致查询性能下降

2.过分的满足第三范式：表中的每一列不存在传递依赖，每列都直接依赖于主键

反范式化明显就不符合第三范式