Mysql_视图及事务等_刘益长

一、视图

1.1、什么是视图？

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

1.2、视图是干什么用的？

通过视图，可以展现基表的部分数据； 视图数据来自定义视图的查询中使用的表，使用视图动态生成。 基表：用来创建视图的表叫做基表

1.3、为什么要使用视图？

因为视图的诸多优点，如下

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

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

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

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

2、因为视图是需要基表才能构建，因此在讲解视图的时候，我们需要先创建两张数据表用于后面演示视图操作，下面是测试表和测试数据创建的SQL语句。

2.1、创建博客表和作者表

CREATE DATABASE IF NOT EXISTS info DEFAULT CHARSET utf8;
USE info;
#创建作者表
CREATE TABLE IF NOT EXISTS author(
id INT NOT NULL AUTO_INCREMENT,
author_name VARCHAR(50) DEFAULT NULL,
PRIMARY KEY(id)
);
INSERT INTO author(author_name)VALUES('naamman'),
('lucy'),('lily'),('jack');
#创建博客表
CREATE TABLE IF NOT EXISTS blog(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50) DEFAULT NULL,
content VARCHAR(100) DEFAULT NULL,
author_id INT DEFAULT NULL
);
INSERT INTO blog(title,content,author_id)VALUES
('测试博客01','博客内容01',1),
('测试博客02','博客内容02',2),
('测试博客03','博客内容03',3),
('测试博客04','博客内容04',4);

2.2、创建视图

#创建视图:逻辑上的虚拟表
CREATE VIEW v_author(编号,姓名)
AS
SELECT * FROM author WITH CHECK OPTION;

#查询视图
SELECT * FROM v_author;

创建多表视图

#创建多表视图(自己写)
CREATE VIEW v_a_b(作者名字,博客标题,博客内容)
AS
SELECT a.author_name,b.title,b.content FROM author a LEFT JOIN blog b ON a.id=b.author_id;
SELECT * FROM v_a_b;

2.3、修改视图

#修改视图:没有就创建,有就替换
CREATE OR REPLACE VIEW v_blog(编号,标题,内容,作者编号)
AS SELECT * FROM blog
WITH CHECK OPTION;

#修改视图的数据->修改基表数据
UPDATE v_blog SET 内容 = '修改后的内容' WHERE 编号 = 1;
SELECT * FROM v_blog

2.4、with check option:where条件约束

#部分数据创建视图
CREATE OR REPLACE VIEW v_blog_1(编号,标题,内容,作者编号)
AS SELECT * FROM blog WHERE author_id=1
WITH CHECK OPTION;

--with check option:where条件约束
INSERT INTO v_blog_1(标题,内容,作者编号)VALUES('123','123',1);
UPDATE v_blog_1 SET 内容 = '博客内容05' WHERE 编号 = 5;
SELECT * FROM v_blog_1;

二、事务

2.1、什么是事务

事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。
事务的使用场景:
在日常生活中，有时我们需要进行银行转账，这个银行转账操作背后就是需要执行多个SQL语句，假
如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，
要想解决这个问题就需要通过事务来完成。

2.2、四大特点

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2.3、事务控制语句

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。

2.4、MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认

CREATE TABLE runoob_transaction_test(id INT(5));

# 开启事务
BEGIN;
# 插入数据
INSERT INTO runoob_transaction_test VALUES(1);
INSERT INTO runoob_transaction_test VALUES(2);
# 提交事务
COMMIT;

# 开启事务
BEGIN;
# 插入数据
INSERT INTO runoob_transaction_test VALUES("aaa");
INSERT INTO runoob_transaction_test VALUES(4);
# 事务回滚
ROLLBACK;

SELECT * FROM runoob_transaction_test;

2、直接用 SET 来改变 MySQL 的自动提交模式:

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

三、存储过程

3.1、什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

3.2、存储过程的优点

存储过程可封装，并隐藏复杂的商业逻辑。 存储过程可以回传值，并可以接受参数。 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看 表，数据表或用户定义函数不同。 存储过程可以用在数据检验，强制实行商业逻辑等。

3.3、创建存储过程

# 创建存储过程
DELIMITER $
CREATE PROCEDURE testa()
BEGIN
    SELECT * FROM student;
    SELECT * FROM cj;
END $
DELIMITER ;

调用

# 调用
CALL testa();

3.4、存储过程的变量以及作用范围

# 创建存储过程
DELIMITER $
CREATE PROCEDURE test2()
BEGIN
    # 声明变量类型
    DECLARE un VARCHAR(32) DEFAULT '';
    # 给username赋值
    SET un='xiaoxiao';
    # 将查询结果赋值给un变量
    SELECT username INTO un FROM student WHERE stuid=3;
    # 查询un变量，返回
    SELECT un;
END $
DELIMITER ;
# 调用存储过程
CALL test2();

# 变量的作用范围
DELIMITER $
CREATE PROCEDURE test3()
BEGIN
    BEGIN
        # 声明变量类型
        DECLARE un VARCHAR(32) DEFAULT '';
        # 给un赋值
        SET un='xiaoxiao';
        # 将查询结果赋值给un变量
        SELECT username INTO un FROM student WHERE stuid=3;
        # 查询un变量，返回
        SELECT un;
    END;
    BEGIN
        # 声明变量类型
        DECLARE un VARCHAR(32) DEFAULT '';
        # 给un赋值
        SET un='xiaoxiao';
        # 将查询结果赋值给un变量
        SELECT username INTO un FROM student WHERE stuid=4;
        # 查询un变量，返回
        SELECT un;
    END;
    
END $
DELIMITER ;
# 调用存储过程
CALL test3();

四、触发器

4.1、什么是触发器      

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，

它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：

1.监视地点(table)

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

3.触发时间(after/before)

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

4.2、创建触发器

USE info;
# 学生表
CREATE TABLE IF NOT EXISTS student(
    username VARCHAR(50),
    PASSWORD VARCHAR(50),
    stuid INT PRIMARY KEY AUTO_INCREMENT,
    birthday DATE
);
INSERT INTO student(username,PASSWORD,birthday)VALUES
('王二','111111','2016-08-23'),
('李四','123456','2016-07-23'),
('杨三','123456','2016-08-17'),
('刘五','000000','2016-08-18'),
('黄六','666666','2016-08-23');
SELECT * FROM student;
# 成绩表
CREATE TABLE IF NOT EXISTS cj(
    num INT PRIMARY KEY AUTO_INCREMENT,
    stu_id INT,
    stu_name VARCHAR(50),
    math FLOAT,
    chinese FLOAT,
    english FLOAT
);
SELECT * FROM cj;
# 触发器
DELIMITER $
CREATE TRIGGER ins_stu
AFTER INSERT ON student FOR EACH ROW
BEGIN
    INSERT INTO cj(stu_id,stu_name,math,chinese,english)
    VALUES(new.stuid,new.username,88,88,88);
END $
DELIMITER ;
# 执行触发过程
INSERT INTO student(username,PASSWORD,birthday)VALUES
('张三','222222','2016-08-23');