• MySQL视图

一.视图的概念

1.什么是视图：

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

2.视图的优点：

1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

3.创建视图：

语法：

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

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

总结:视图能尽量少用就尽量少用

• 触发器

一.触发器基本概念：

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行

触发器：trigger，是指事先为某张表绑定一段代码，当表中的数据发生改变（增、删、改）的时候，系统会自动触发代码并执行。

1.创建触发器

创建语法：

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

2.对触发器的基础操作

2.1查看全部触发器:

show trigger;

2.2删除触发器

drop trigger 触发器名字;

2.3触发触发器

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

先创建两张表：

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代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

View Code

 创建触发器：

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

View Code

往表cmd中插入记录，触发触发器

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    (\'kevin\',\'0755\',\'ls -l /etc\',NOW(),\'yes\'),
    (\'kevin\',\'0755\',\'cat /etc/passwd\',NOW(),\'no\'),
    (\'kevin\',\'0755\',\'useradd xxx\',NOW(),\'no\'),
    (\'kevin\',\'0755\',\'ps aux\',NOW(),\'yes\');
查询errlog表记录结果：
+----+-----------------+---------------------+
| id   | err_cmd          | err_time               |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2022-08-19 15:31:13|
|  2 | useradd xxx      | 2022-08-19 15:31:13|
+----+-----------------+---------------------+

• 事务

一.事务的四大特征

二.开启事务的步骤

# 1.创建一个表t1;
+--------+---------+
| uname  | balance |
+--------+---------+
| 王二      |      50   |
| 李四      |     150  |
+--------+---------+
# 2.开启事务
commit;
# 3.编写事务中的sql语句（insert、update、delete）这里实现一下\"王二给李五转账\"的事务过程
update t1 set balance = 30 where uname = \"王二\";
update t1 set balance = 170 where uname = \"李四\";
# 4.提交事务
commit;
# 开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作
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表，那么就会出现并发问题，

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会发生以下各种并发问题。

五.事务的隔离级别

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

设置事务的隔离级别： 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）、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

• 储存过程

类似于python中的自定义函数

delimiter 临时结束符
create procedure 名字(参数,参数)
begin
    sql语句;
end 临时结束符
delimiter ;

delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

\"\"\"
查看存储过程具体信息
    show create procedure pro1;
查看所有存储过程
    show procedure status;
删除存储过程
    drop procedure pro1;
\"\"\"

• 内置函数

一.字符串函数

二.日期函数

三.数字函数

• 流程控制

一.分支结构之IF

# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;

二.循环结构之WHILE

# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

来源：https://www.cnblogs.com/tai-yang77/p/16602137.html
本站部分图文来源于网络，如有侵权请联系删除。