MySQL学习之路（5） - 数据库事务、视图、触发器、函数

目录

一、什么是事务#%E4%B8%80%E3%80%81%E4%BB%80%E4%B9%88%E6%98%AF%E4%BA%8B%E5%8A%A1

事务控制语句

怎么设置自动提交

回滚运用的场景

脏读

二、什么是视图#%E4%BA%8C%E3%80%81%E4%BB%80%E4%B9%88%E6%98%AF%E8%A7%86%E5%9B%BE

代码部分

储存过程

三、触发器#%E4%B8%89%E3%80%81%E8%A7%A6%E5%8F%91%E5%99%A8

触发器类别

触发器格式

四、函数#%E5%9B%9B%E3%80%81%E5%87%BD%E6%95%B0

函数分类

窗口函数

四大排名函数(NTILE、row_number、rank、dense_rank)

一、什么是事务

        事务（Transaction）是指访问并更新数据库中各种数据的一个程序执行单元(unit)。 MySQL 事务主要用于处理操作量大，复杂度高的数据。

        （一）、四大特点（ACID）：★

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

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

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

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

        事务控制语句：

                开启事务：BEGIN 或 START TRANSACTION

                提交事务：COMMT 或 COMMT WORK

                回滚事务：ROLLBACK 或 ROLLBACK WORK

                创建事务标记点：SAVEPOINT identifier

                设置事务隔离级别：SET TARNSACTION

                回滚事务标记点：ROLLBACK TO identifier

                删除事务标记点：REKEASE SAVEPOINT identifier

        步骤：

                开启事务(begin)→写SQL语句(insert、update、delate、标记点)→commit

        标记点作用：当你一个事物中有大量sql操作的时候，如果rollback(默认回到原始状态)效率极低，可以考虑回到某个标记点。

        （二）、事务控制语句：

                mysql数据库默认自动提交事物，和变量autocommit有关，默认为1，即开启(ON)，可以通过黑窗口(cmd窗口查看)，效果如下：

        命令：show variables like 'autocommit' ;

         注：不管autocommit是1还是0；STARTTRANSACTION后，只有当commit数据才会生效，ROLLBACK后就会回滚。

        当autocommit为0时；不管有没有STARTTRANSACTION，只有当commit数据才会生效，ROLLBACK后就会回滚。

create database if not exists db_0918;
use db_0918;
create table if not exists new_date(
    id int,
    name varchar(15),
    age int
);
-- 先创个数据表

-- 假设autocommit为0时，插入数据
begin;
insert into new_date values (1, 'AA', 17);
insert into new_date values (1, 'BB', 16); -- 如果执行上面语句，插入数据后数据库不会提交数据，但是已经执行，见下图一、二。
commit;  -- 当这一句语句运行后，数据将会被提交，见下图三、四。

 

begin;
insert into new_date values (1, 'AA', 17);
rollback;  # 回滚会将上面数据取消插入
insert into new_date values (1, 'BB', 16);
commit;  # 运行结果只是插入了一个BB的数据


# 在遇见rpllback后其实commit有没有效果是一样的
begin;
insert into new_date values (1, 'AA', 17);
rollback;
insert into new_date values (1, 'BB', 16);
# 插入结果是一样的

        回滚作用：

begin;
insert into new_date values (1, 'AA', 17);
savepoint p1;  # 插入一个还原点
insert into new_date values (1, 'BB', 16);
rollback to p1;  # 回滚到还原点
commit;  # 这种提交下就只有一个数据。

        怎么设置自动提交：

                黑窗口（cmd）下启动mysql再输入：SET AUTOCOMMIT=0;

                注：设置后仅限当前连接，当数据库断开连接后，再次进入，这个值将恢复到默认值，即恢复为1。

         注：在黑窗口下设置的关闭自动提交，即使插入数据，那么别人的数据库中还是不存在这个数据，这个数据暂时在自己的数据库提交，只有输入commit后，数据才会更新到数据库中，这个是数据库的隔离机制，即在黑窗口下输入插入数据，在黑窗口下查看数据是看的到的，但是在数据库中，这条数据刷新不存在，除非运行了commit，如下:

 

 

        回滚运用的场景：

                 如银行转账，当一个用户向另外一个用户转钱的时候，会从转账用户扣除，并加入到接收用户的卡上，但是当两方任意一方失败后，就都会进行回滚，即交易失败。

        脏读：

                脏读，在读取未提交隔离级别时会出现的现象。当一个事务正在访问数据且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

时间	转帐	取款
1		开始事务
2	开始事务
3		查询余额为2000
4		取款1000，余额已经被改为1000（未提交）
5	查询余额为1000（这就是脏数据）
6		取款发生错误，事务回滚，余额回到2000
7	转入2000，余额实际上为3000（脏读1000+2000）
8	提交
	按正常来说，此账户应该为4000

        不可重复读取：
                不可重复读，在读取已提交隔离级别时会出现的现象。前后多次读取同一个数据，数据内容不一致。

        幻读：

                幻读，在可重复读隔离级别时会出现的现象。前后多次读取，读取到其他事务插入的数据。

时间	事务A	事务B
1	开始
2	第一次查询，数据总量为100条
3		开始事务
4	其他操作
5		新增数据100条
6		提交事务
7	第二次查询，数据总量为200条
	按照正常来说，事务A前后两次读取的数据总量应该一致

二、什么是视图

        视图是从一个或几个基本表（或视图）中导出的虚拟的表。视图不可以和表名重名。数据多用作查询，一般不会通过视图去修改数据。

        特点：

                1、视图可以简化用户操作

                2、视图能增加安全性（视图一般多用查询语句）

                3、视图对重构数据库提供了一定程度的逻辑独立性

        视图操作：

                创建视图：creat view 视图名字 as SQL语句(大都是select) ;

                修改视图：alter view 视图名字 as SQL语句;

                删除视图：drop view 视图名字;

        代码部分：先创建一个表

create database if not exists db_0918;
use db_0918;
create table if not exists People(
    id int not null auto_increment primary key,
    age int,
    sid char(20),
    sex bit,
    name char(20),
    isDelete bit default 0
) ;

        插入部分数据：

insert into People(age,sex,name) values (18, 1, 'Cleo');
insert into People(age,sid,sex,name) values (18, '', 1, 'Abra');
insert into People(age,sex,name) values (36, 1, 'Louis'),(20, 1, 'Burt'),(30, 1, 'Mason'),(30, 1, 'lilia');
insert into People(age,sex,name) values (34, 1, 'Jane');

        创建视图：

create view VPeople as select * from People where id>3;  -- 过滤出id大于3的信息，并创建视图VPeople

select * from VPeople where age > 30;  -- 展示id>3且年龄大于30岁的信息
select * from (select * from People where id>3) as stu where age > 30;  -- 等价于上面的查询，所以视图查询在这种情况下更简单。

        修改视图：

alter view VPeople as
    select * from People where People.name like 'A%';  -- 查询以A开头的名字
select * from VPeople;  -- 展示视图

         删除视图：

drop view VPeople;  -- 删除视图

        视图多用于查询频次较高的语句或者逻辑较为复杂的场景，可以考虑建立视图。

        比如：网站首页数据；网站顶部筛选过滤。

   

        储存过程：

                存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

        语法（类似SQL写程序）：

                create procedure 存储过程名()

                BEGIN

                        sql语句集

                END

                call 储存过程名();  -- 调用进程

-- 创建一个名称为dba的库文件，在dba库中创建一张名称为tb1的表，表中有id、name这两个字段。创建一个名称为ad1的存储过程，ad1存储过程的功能是插入三条记录到tb1表中。
create database if not exists dba;
use dba;
create table if not exists tb1(
    id int,
    name varchar(255)
);
delimiter $$  -- 修改结尾符（这个结尾符可以是任意，一般用两个$结尾），不然会以为;语句是一个整体，进行报错
create procedure ad1()  -- 定义储存过程
begin
    insert into tb1(id, name) values (1, 'Joke');
end;$$
delimiter ;  -- 用完了将这个结尾符回复为;

call ad1()  -- 调用语句

        扩展，使用while循环插入三个数据：

create database if not exists dba;  -- 创建库
use dba;  -- 使用库
create table if not exists tb1(  -- 创建表
    id int,
    name varchar(255)
);

delimiter $$  -- 定义符号
create procedure ad1()  -- 定义储存
begin
    declare i int default 1;  -- 声明一个变量i，整数类型，默认值为1
    while (i < 3) do
        insert into tb1(id, name) VALUES (i, 'Andy');
        set i = i + 1;  -- 每运行一次i值+1
        end while;
end; $$
delimiter ;  -- 设置回结尾符号

call ad1(); -- 调用

三、触发器

        （一）、触发器类别：

                1、更新前触发器

                2、更新后触发器

                3、删除前触发器

                4、删除后触发器

                5、插入前触发器

                6、插入后触发器

        （二）、触发器格式：

                create trigger 触发器名字

                after/befor        触发时间，后/前

                insert/update/delete        触发事件，插入/更新/删除

                on 要监视的表

                for each row        行级触发器

                begin        这里写SQL语句，可以多条，每条语句必须使用;结尾

                end;

-- 以下语句会报错，因为在mySQL中遇见分号意思为结束
create trigger tr after insert on grade for each row 
    begin 
        insert into teacher(name) values ('tom');
    end

-- 解决方法
delimiter //  -- 修改终止符为 //
select * from teacher // -- 使用 // 结束一条语句
delimiter ;  -- 使用完成后记得将符号重新设置回去

        例：有一个富豪榜，和一个平民榜，有id、姓名和身价 当有一个平民创业成功晋级为富豪时(拥有50身价就是富豪了) 但是突然你拥有了200身价那么怀疑你金钱来路不明，抛出异常 如何创建一个触发器，实现这个功能？

create database if not exists Money;
use Money;
create table wealthy(
    id int primary key auto_increment,
    age int,
    name varchar(20),
    money int
);
insert into wealthy values (1,20,'张三',110),
                           (2,35,'李四',110),
                           (3,35,'王五',90),
                           (4,20,'赵六',90);
create table person(
    id int primary key auto_increment,
    age int,
    name varchar(20),
    money int
);
insert into person values(0,20,'十一',30);

-- 触发器
drop trigger if exists people_trigger_update_after; -- 存在就删除
delimiter $$  -- 修改终止符
create trigger people_trigger_update_after after update
    on person for each row
    begin
        if NEW.money > 200 then -- 资金来路不明
            signal sqlstate 'ERR01' set message_text = '资金来路可能不明，数据异常';
            elseif NEW.money > 50 then -- 如果大于50，插入
                insert into wealthy VALUES (OLD.age, OLD.id, OLD.name, NEW.money);
        end if;
    end; $$
delimiter ;

四、函数

        函数一般是在数据上执行的，为数据的转换和处理提供了方便。 
SQL函数存在的问题：DBMS都支持SQL语句（如SELECT），但DBMS有特定的函数，函数名不同。 
使用函数应保证代码注释，以便以后你（或其他人）能确切地知道所编写的SQL代码的含义。

        （一）、函数分类：

                1、处理文本字符串的文本函数（删除、填充、转值、大小写）

                2、计算函数，绝对值、代数等

                3、日期函数，返回时间差，提取日期或者时间特定成分

                4、返回特殊信息（用户登录信息）的系统函数

        文本处理函数

函数	说明
LEFT()	返回字符串左边的字符
LENGTH()	返回字符串的长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格

        数值处理函数

函数	说明
ABC()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率
SIN()	返回一个角度的正弦
SORT()	返回一个数的平方根
TAN()	返回一个角度的正切

        日期处理函数

函数	说明
now()	获取当前时间
year(),month(),dayofmonth()	日期或中提取年月日
monthname()	输出月份的英文单词
timestampdiff()	比较两个日期间的差值

select now();  -- 显示现在日期
select curdate(),curtime();  -- 当前年月日，时间
select year(now()),year('2020-06-06');  -- 今年年份，2020-06-06的年份
select dayofmonth(now()),dayofyear(now());  -- 今天是当前月份第几天，当前年份第几天
select monthname(now());  -- 返回当前月份的英文单词
select timestampdiff(hour ,'2003-2-1',now());  -- 计算两个日期时间差，可以是年月日时分秒，其中hour计算小时，改为day计算天数，month计算月份，改为year计算年，值是由后面减去前面，如果后面更小会显示负数

         计算时间差值

select date_sub(now(),INTERVAL -200 day ); -- 整数：向历史的时间去数，负数：向未来的时候计算；可以是年月日时分秒

        （二）、窗口函数

                MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库中早已支持，也 叫分析函数。 开窗函数与分组聚合比较像，分组聚合是通过制定字段将数据分成多份，每 一份执行聚合函数，每份数据返回一条结果。 开窗函数也是通过指定字段将数据分成多份，也就是多个窗口，对每个窗口 的每一行执行函数，每个窗口返回等行数的结果。

        语法：

                结合COUNT\SUM\MIN\MAX\AVG等聚合函数使用，分析语句的写法 over(partition by 列名1 order by 列名2)        注：partition by和order by 可以只出现一个

        注意：注意: 如果不指定ROWS BETWEEN,默认为从起点到当前行; 如果不指定ORDER BY，则将分组内所有值累加; 关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：

函数	含义
PRECEDING	往前
FOLLOWING	往后
CURRENT ROW	当前行
UNBOUNDED	无界限（起点或终点）
UNBOUNDED PRECEDING	表示从前面的起点
UNBOUNDED FOLLOWING	表示到后面的终点
COUNT、AVG，MIN，MAX	和SUM用法一样

create database if not exists cookie;
use cookie; 
drop table if exists cookie1; 
create table cookie1(
    cookieid varchar(255), 
    createtime date,
    pv int
);
-- UV（Unique visitor）：一天内同个访客多次访问仅计算一个UV 
-- PV(Page View)即页面浏览量或点击量,用户每1次对网站中的每个网页访问均被记录1个

        数据：

create database if not exists cookie;
use cookie;
drop table if exists cookie1;
create table cookie1(cookieid varchar(255), createtime date, pv int);
insert into cookie1 (cookieid, createtime, pv) VALUES ('cookie1','2015-04-10',1),
                                                      ('cookie1','2015-04-11',5),
                                                      ('cookie1','2015-04-12',7),
                                                      ('cookie1','2015-04-13',3),
                                                      ('cookie1','2015-04-14',2),
                                                      ('cookie1','2015-04-15',4),
                                                      ('cookie1','2015-04-16',4);

        实例：

-- 这个解释有点麻烦，相当于一个累加器，格式大概如下：
-- over(partition by 列名 order by 列名 rows between unbounded(前面取多少数据，输入数字也行) preceding and current row(current row 当前列，也可以写为 数据长度(如1) following))
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as pv1, -- 切片累加，即第一次为第0个+第一个数据，第二个为第0个+第一个+第二个数据，以此类推
-- as 给这一列换名字
       sum(pv) over(partition by cookieid order by createtime 
           rows between unbounded preceding and current row ) as pv2, -- 从以前的数据到当前行的数据，计算结果和第一个一样
       sum(pv) over(partition by cookieid order by createtime 
            rows between 3 preceding and current row ) as pv3, -- 前三条数据到现在的，在这里，如果是第一条数据，那么就是一，因为前面两天都没有数据
-- 第二条取的数据就变为一和二，三就是一二三，四就是一二三四，五就是二三四五，即取四个，窗口宽度为4，以此类推
       sum(pv) over(partition by cookieid order by createtime 
            rows between 1 preceding and 1 following ) as pv4,  -- 昨天今天加明天，即前取一个，取本身一个，再后取一个
       sum(pv) over (partition by cookieid) as pv5, -- 如果没有加order by 则全部累加
from cookie1;

        结果：

        （三）、四大排名函数(NTILE、row_number、rank、dense_rank)

                NTILE(n)        将函数切为N片，相对于把数据平均分，比如有9个数据，n写3，那么就会将9个数据分为3组，每组3个，如果不能平均分，那么会在第一个数据上+1，如n为2，即第一个数据5，第二个数据4，ntile函数可以将数据取出一定比例，如1/2数据，分两组即可

use cookie;
drop table if exists cookie2;
create table cookie2(cookieid varchar(255), createtime date, pv int);
insert into cookie2 (cookieid, createtime, pv) VALUES
('cookie1','2015-04-10',1),
('cookie1','2015-04-11',5),
('cookie1','2015-04-12',7),
('cookie1','2015-04-13',3),
('cookie1','2015-04-14',2),
('cookie1','2015-04-15',4),
('cookie1','2015-04-16',4),
('cookie2','2015-04-10',2),
('cookie2','2015-04-11',3),
('cookie2','2015-04-12',5),
('cookie2','2015-04-13',6),
('cookie2','2015-04-14',3),
('cookie2','2015-04-15',9),
('cookie2','2015-04-16',7);

        按ID分为两组：（分组后查询只需要where rn1 = 1即可取出其中要的一部分数据）

select cookieid,createtime,pv,
       ntile(2) over (partition by cookieid order by createtime asc) as rn1, -- 每组切成2片
from cookie2;

        row_number：（组内排序）

select cookieid,createtime,pv,
       row_number() over (partition by cookieid order by pv desc ) as rn
from cookie2;
-- partition by cookieid 按cookieid分组,
-- order by pv 按pv排序，（desc降序,asc升序）列取名为rn

        输出：

         rank、dense_rank

select cookieid,createtime,pv,
       rank() over (partition by cookieid order by pv desc ) as rn1,  -- 留空位
        dense_rank() over (partition by cookieid order by pv desc ) as rn2  -- 不留空位
from cookie2;
-- 留空位意思是说假设一个班级成绩是 91,91,90,88，那么排序就是1,1,3,4，即并列直接跳过
-- 不留空位排序就是1,1,2,3，即并列后继续往下排