MySQL视图、触发器、事务

一、视图

1.什么是视图

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

1、在硬盘中，视图只有表结构文件，没有表数据文件

2、视图通常是用于查询，尽量不要修改视图中的数据

3、视图尽量少用

2.创建视图的语法

创建试图语法:
    create view 视图名 as SQL语句;

'一般我们查询一张表的结果的时候都是这样写的'
select * from teacher inner join course on teacher.tid = course.teacher_id;
上述表查询的结果是一个虚拟的表一般情况下我们是找不到这表的,每次要查询时在把上述语句敲下即可 但是这样的话就态麻烦了我们只需把这个虚拟表做成视图即可:
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
这样我们在查询这个表的时候就能看到这个表了
show tables;
+----------------+
| Tables_in_db10 |
+----------------+
| class          |
| course         |
| score          |
| student        |
| teacher        |
| teacher2course |
+----------------+

二、触发器

1.触发器介绍

'
触发器作用:
    触发器就是针对表的数据进行增删改的操作自动触发的功能
触发器功能:
   触发器有一下功能
        对数据增之前、增之后
        对数据删之前、删之后
        对数据改之前、改之后   
'
语法结构:
    create tirgger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
    SQL语句
end
'主意:
    触发器内部SQL语句需要用到; 但是;又是SQL语句默认的结束符 如果直接使用就会直接结束
    为了能够完整的写出触发器代码 所以我们需要把SQL的结束符临时替换
    而如果想要替换SQL的结束符需要用到关键字:'
    delimiter $$   # 这样我们就把SQL的结束符替换成了 $$  这样我们就能够编写触发器的代码了

    但是主意编写完触发器代码后最好把结束符替换回去
    delimiter $$  # 替换结束符
        编写需要用到;的各种语句
    delimiter ;

2.代码实现

'数据准备'
创建两张表:
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  # 提交时间
);
'需求: 如果用户在插入cmd表数据的时候 如果最后一个数据是NO那么就往errlog插入数据'
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);  # NEW相当于对象 cmd本身
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

'往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志'
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');
'现在我们只往cmd表中插入了数据 按理来说errlog表中应该是没有数据的
查询errlog表记录'
select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2022-08-19 15:28:38 |
|  2 | useradd xxx     | 2022-08-19 15:28:38 |
+----+-----------------+---------------------+

'触发器命名:
  tri_after_insert_t1  给表t1增加之后触发
  tri_after_update_t2  给表t2修改之后触发
  tri_after_delete_t3  给表t3删除数据之后
'
'删除触发器'
drop trigger tri_after_insert_cmd;

三、事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.代码实现

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | jason |    1000 |
|  2 | kevin |    1000 |
|  3 | tank  |    1000 |
+----+-------+---------+
'模拟jason给tank转钱 kevin收取%10手续费'
'修改数据之前先开启事务操作'
start transaction;
'修改操作'
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | jason |     900 |
|  2 | kevin |    1010 |
|  3 | tank  |    1090 |
+----+-------+---------+
'这个时候还可以回滚 那数据会回到上一个的状态 就像操作数据没有发生一样'
rollback;
select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | jason |    1000 |
|  2 | kevin |    1000 |
|  3 | tank  |    1000 |
+----+-------+---------+
'而如果我们对结果已经很满意了我们可以使用二次确认功能 这样数据就保存下来了 在怎么回滚都没用了
开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘'
commit;
这个时候想在回退到上一步就再也回不去了
rollback;
select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | jason |     900 |
|  2 | kevin |    1010 |
|  3 | tank  |    1090 |
+----+-------+---------+

"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

如果站在python角度,应该实现的伪代码逻辑
try:
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='kevin'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;
'异常就回退 否则就确认'

2.扩展知识点

事务处理中有几个关键词汇会反复出现
  事务(transaction)
  回退(rollback)
  提交(commit)
  保留点(savepoint)
        为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    创建占位符可以使用savepoint
        savepoint sp01;
    回退到占位符地址
        rollback to sp01;
    # 保留点在执行rollback或者commit之后自动释放
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别

1.read uncommitted(未提交读)

事务A读取到了事务已经修改但未提交的数据，这种数据就叫脏数据，是不正确的

2.read committed(提交读)

对于事务A多次读取同一个数据时，由于其他是事务也在访问这个数据，进行修改且提交，对于事务A，读取同一个数据时，有可能导致数据不一致，也叫不可重复读

3.repeatable read(可重复读)

能够解决"脏读"问题，但是无法解决"幻读"

所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题

4.serializable(可串行读)

事务A和事务B同时访问时，在事务A修改了数据，而没有提交数据时，此时事务B想增加或修改数据时，只能等待事务A的提交，事务B才能够执行。

3.持久性

事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

更多知识点:https://zhuanlan.zhihu.com/p/29166694

存储过程

'类似于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;
'

内置函数

'ps：我们可以借助help 函数名   查看函数的用法'
'1.移除指定字符'
Trim、LTrim、RTrim
跟python中的strip、lstrip、rstrip一样都是取出指定字符
'2.大小写转换'
Lower、Upper
跟python完全一样 都是大小转换
'3.获取左右起始指定个数字符'
Left、Right
跟python中的切边取值一样
'4.返回读音相似值(对英文效果)'
Soundex
'eg:客户表中有一个顾客登记的用户名为J.Lee
        但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
        where Soundex(name)=Soundex('J.Lie')'

' 5.日期格式:date_format
在MySQL中表示时间格式尽量采用2022-11-11形式'
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
+----+---------+---------------------+
| id | NAME    | sub_time            |
+----+---------+---------------------+
|  1 | 第1篇   | 2015-03-01 11:31:21 |
|  2 | 第2篇   | 2015-03-11 16:31:21 |
|  3 | 第3篇   | 2016-07-01 10:21:31 |
|  4 | 第4篇   | 2016-07-22 09:23:21 |
|  5 | 第5篇   | 2016-07-23 10:11:11 |
|  6 | 第6篇   | 2016-07-25 11:21:31 |
|  7 | 第7篇   | 2017-03-01 15:33:21 |
|  8 | 第8篇   | 2017-03-01 17:32:21 |
|  9 | 第9篇   | 2017-03-01 18:31:21 |
+----+---------+---------------------+
'现在需要把相同的年月 的篇数计数'
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
+-------------------------------+-----------+
| date_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       |         2 |
| 2016-07                       |         4 |
| 2017-03                       |         3 |
+-------------------------------+-----------+

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
    adddate    增加一个日期 
    addtime    增加一个时间
    datediff    计算两个日期差值

流程控制

'流程控制跟python一样只不过MySQL没有for循环'
'1.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 ;

'2.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 ;

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。


索引的底层数据结构是b+树
    b树 红黑树 二叉树 b*树 b+树
      上述结构都是为了更好的基于树查找到相应的数据

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

"""
聚集索引(primary key)
辅助索引(unique key,index key)
    查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据

覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
    select name from user where name='春游去动物园';
非覆盖索引:虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
    select age from user where name='春游去动物园';
"""

posted @ 2022-08-19 16:48 stephen_hao 阅读(72) 评论(0) 编辑收藏举报

刷新页面返回顶部

stephen_wzh