今日内容 视图，触发器，事务 内置函数及流程控制

• MySQL视图
  

一.视图的概念

1.什么是视图：

SQL语句的执行结果是一张虚拟表 我们可以基于该表做其他操作
如果这张虚拟表需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后就称之为"视图"(本质就是一张虚拟表)
2.视图的优点：

1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
3.创建视图：

语法：

1. CREATE VIEW <视图名> AS <SELECT语句>;

复制代码

1、在硬盘中，视图只有表结构文件，没有表数据文件
2、视图通常是用于查询，尽量不要修改视图中的数据
总结:视图能尽量少用就尽量少用

• 触发器
  

一.触发器基本概念：

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行
触发器：trigger，是指事先为某张表绑定一段代码，当表中的数据发生改变（增、删、改）的时候，系统会自动触发代码并执行。
1.创建触发器

创建语法：

1. delimiter 自定义结束符号
2. create trigger 触发器名字 触发时间 触发事件 on 表名 for each row
3. begin
4.     -- 触发器内容主体，sql语句
5. end
6. 自定义的结束符合
7. delimiter ;
8. -- 触发时间：before/after  表中数据发生改变前的状态/表中数据发生改变后的状态
9. -- 触发事件：insert/update/delet

复制代码

2.对触发器的基础操作

2.1查看全部触发器:

1. show trigger;

复制代码

2.2删除触发器

1. drop trigger 触发器名字;

复制代码

2.3触发触发器
触发不是自动手动触发的，而是在对应的事件发生后才会触发。比如下面创建的触发器，只有在对表进行数据操作的时候，触发器才会执行
先创建两张表：

1. CREATE TABLE cmd (
2.     id INT PRIMARY KEY auto_increment,
3.     USER CHAR (32),
4.     priv CHAR (10),
5.     cmd CHAR (64),
6.     sub_time datetime, #提交时间
7.     success enum ('yes', 'no') #0代表执行失败
8. );
10. CREATE TABLE errlog (
11.     id INT PRIMARY KEY auto_increment,
12.     err_cmd CHAR (64),
13.     err_time datetime
14. );

复制代码

View Code 创建触发器：

1. delimiter $$  # 将mysql默认的结束符由;换成$$
2. create trigger tri_after_insert_cmd after insert on cmd for each row
3. begin
4.     if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
5.         insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
6.     end if;
7. end $$
8. delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

复制代码

View Code

1. 往表cmd中插入记录，触发触发器<br>

复制代码

1. INSERT INTO cmd (
2.     USER,
3.     priv,
4.     cmd,
5.     sub_time,
6.     success
7. )
8. VALUES
9.     ('kevin','0755','ls -l /etc',NOW(),'yes'),
10.     ('kevin','0755','cat /etc/passwd',NOW(),'no'),
11.     ('kevin','0755','useradd xxx',NOW(),'no'),
12.     ('kevin','0755','ps aux',NOW(),'yes');
13. 查询errlog表记录结果：
14. +----+-----------------+---------------------+
15. | id   | err_cmd          | err_time               |
16. +----+-----------------+---------------------+
17. |  1 | cat /etc/passwd | 2022-08-19 15:31:13|
18. |  2 | useradd xxx      | 2022-08-19 15:31:13|
19. +----+-----------------+---------------------+

复制代码

• 事务
  

一.事务的四大特征

原子性（Atomicity）	指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况
一致性（Consistency）	事务必须使数据库从一个一致状态变换到另外一个一致状态，
隔离性（Isolation）	一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）	一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

二.开启事务的步骤

1. # 1.创建一个表t1;
2. +--------+---------+
3. | uname  | balance |
4. +--------+---------+
5. | 王二      |      50   |
6. | 李四      |     150  |
7. +--------+---------+
8. # 2.开启事务
9. commit;
10. # 3.编写事务中的sql语句（insert、update、delete）这里实现一下"王二给李五转账"的事务过程
11. update t1 set balance = 30 where uname = "王二";
12. update t1 set balance = 170 where uname = "李四";
13. # 4.提交事务
14. commit;
15. # 开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作
16. rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态

复制代码

View Code三.拓展知识点

MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务处理中有几个关键词汇会反复出现
                                        事务(transaction)
                                      回退(rollback)
                                      提交(commit)
                                      保留点(savepoint)
                为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    创建占位符可以使用savepoint：
                                                 savepoint sp01;
    回退到占位符地址：
                                                 rollback to sp01;
保留点在执行rollback或者commit之后自动释放
四.事务并发时出现的问题

因为某一刻不可能总只有一个事务在运行，可能出现A在操作t1表中的数据，B也同样在操作t1表，那么就会出现并发问题，
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会发生以下各种并发问题。

脏读	对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
不可重复读	对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了
幻读	对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现神不知鬼不觉的多出几行了…

五.事务的隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别

read uncommitted(未提交读)	允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。
read committed(提交读)	只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）
repeatable read(可重复读，MySQL默认)	确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。 （可以避免脏读和不可重复读，但幻读仍然存在InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题）
serializable(可串行读)	强制事务串行执行，很少使用该级别

设置事务的隔离级别： set transaction isolation level 级别；
设置全局的隔离级别：set global transaction isolation level 级别；

• MVCC多版本并发控制
  

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
        一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
MVCC解决的问题
前提数据库并发场景有三种，分别为∶
1)、读读∶ 不存在任何问题，也不需要并发控制
2)、读写∶有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
3)、写写∶ 有线程安全问题，可能存在更新丢失问题
MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
因此，MVCC可以为数据库解决以下问题∶
1）、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。
2）、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

• 储存过程
  

1. 类似于python中的自定义函数
3. delimiter 临时结束符
4. create procedure 名字(参数,参数)
5. begin
6.     sql语句;
7. end 临时结束符
8. delimiter ;
10. delimiter $$
11. create procedure p1(
12.     in m int,  # in表示这个参数必须只能是传入不能被返回出去
13.     in n int,  
14.     out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
15. )
16. begin
17.     select tname from teacher where tid > m and tid < n;
18.     set res=0;  # 用来标志存储过程是否执行
19. end $$
20. delimiter ;
23. # 针对res需要先提前定义
24. set @res=10;  定义
25. select @res;  查看
26. call p1(1,5,@res)  调用
27. select @res  查看
29. """
30. 查看存储过程具体信息
31.     show create procedure pro1;
32. 查看所有存储过程
33.     show procedure status;
34. 删除存储过程
35.     drop procedure pro1;
36. """

复制代码

 

• 内置函数
  

一.字符串函数

函数名	示例	函数功能
CONCAT	SELECT CONCAT(‘今天’，‘晴天’) 返回：今天晴天	连接成字符串
LENGTH	LENGTH(‘hello world’) 结果为11	返回字符串的长度
UPPER	Upper(‘abcd’)返回ABCD	将字符串转为大写
LTRIM	Ltrim（‘ abc’）返回为‘abc’	去除字符串左边的空格
RTRIM	Rtrim(‘abv’) 返回为‘abc’	去除字符串右边的空格
TRIM	TRIM(‘ ABC ’)返回‘ABC’	去除字符串左右两边的空格
REPLACE	Peplace(‘abccd’,‘c’,‘x’) 返回为‘abxxd’	将abccd中c替换为x
STRCMP	STRCMP(S1,S2)	比较s1和s2，如果s1等于s2，返回0，如果s1小于s2，返回-1
SUBSTRIMG	SUBSTRING(‘Johnson’ ，5，3)返回son	从第五个位置开始截取长度为3的字符串

 
二.日期函数

函数名	函数功能
NOW()	获取当前日期+时间 函数
CURDATE()	获取当前日期函数
CURRIME()	获得当前时间函数
MONTH(DATE)	返回日期对应的月份（数字类型，返回1到12的整数）
MONTHNAME(DATE)	返回日期对应的月份（字符串，返回月份的英文全名，如：2月是February）
DAYNAME(DATE)	返回日期对应的工作日的英文名称
DAYOFWEEK(DATE)	返回日期对应的一周中的索引，1表示周日，2表示周一
WEEKDAY(DATE	返回日期对应的工作日索引，0表示周一，1表示周二…6表示周六
WEEK(DATE)	计算日期是一年中的第几周， 范围从1到52
DAYOFYEAR(DATE)	计算日期是一年中的第几天，范围从1到366
DAYOFMONTH(DATE)	计算日期是一个月中的第几天，范围从1到31
SECOND	返回时间的秒部分，范围从0到59

三.数字函数

函数名	函数功能
format(x,y)	将一个数字x，保留y为小数，并且整数部分用逗号分隔千分位，小数部分进行四舍五入
abs()	求一个数的绝对值；absolute
sqrt（）	求一个数的平方跟，sqrt是sqruar(平方),root(根）的缩写
mod(x,y)	x除数，y被除数。结束是余数
ceil（）	向上取整
floor（）	向下取整
rand()	用来生成随机数用的
sign()	返回当前结果得符号，如果是附属返回-1，如果是0，返回0，如果是整数，返回1

• 流程控制
  

 
 
一.分支结构之IF

1. # if条件语句
2. delimiter //
3. CREATE PROCEDURE proc_if ()
4. BEGIN
5.    
6.     declare i int default 0;
7.     if i = 1 THEN
8.         SELECT 1;
9.     ELSEIF i = 2 THEN
10.         SELECT 2;
11.     ELSE
12.         SELECT 7;
13.     END IF;
15. END //
16. delimiter ;

复制代码

 
二.循环结构之WHILE

1. # while循环
2. delimiter //
3. CREATE PROCEDURE proc_while ()
4. BEGIN
6.     DECLARE num INT ;
7.     SET num = 0 ;
8.     WHILE num < 10 DO
9.         SELECT
10.             num ;
11.         SET num = num + 1 ;
12.     END WHILE ;
14. END //
15. delimiter ;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！