MySQL学习记录day10

#视图是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的
#视图只保存了查询的SQL逻辑，不保存查询结果
#1.创建视图
# create or replace view v_name as select[语句] [with[cascaded|local] check option];
# create or replace view stu_v_1 as select id,name from student where id<=10;
#2.查询视图
# show create view v_name;创建视图时指定的sql语句
# select * from v_name where id<3;查询视图信息
#3.修改视图
# create or replace 后面跟一样的，相当于直接替换掉
# alter view v_name(col) as select xx
#4.删除视图
# drop view v_name;

#视图并不能存储数据，如果对试图进行insert操作，那么数据插入到原来的表中
#加入了检查选项时where后的条件才生效
# with cascaded/local check option可以在对试图插入时进行检查

#视图检查
#使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行
#MySQL允许基于另一个视图创建视图，还会检查依赖视图中的规则以保持一致性
#确定检查范围通过添加cascaded和local，默认为前者
#cascaded:基于视图建立的视图会遵循依赖视图以及之前的视图的where条件
#local:基于视图建立的视图只会遵循带有检查条件的依赖视图的where条件

#视图更新
#视图中的行与基础表之间的行必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新
#1.聚合函数或者窗口函数2.distinct 3.group by 4.having 5.unio或者union all

#视图作用：操作简单、安全：通过视图用户只能查询和修改他们所能见到的数据、数据独立

# create view v_1 as select id,age,gender,name,profession from tb_user;
# create view  v2 as select s.student_name,c.course_name from student s,student_course sc,course c where s.id = sc.id and sc.id = c.id;

#触发器：与表有关的数据库对象，指在insert/update/delete之前或之后，出发并执行触发器中定义的sql语句集合。
#触发器和以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
#使用别名old和new来引用触发器中发生变化的记录内容，现在触发器只支持行级触发，不支持语句级触发
#触发器类型：
#1.insert型触发器：new表示将要或者已经新增的数据
#2.update型触发器：old表示修改之前的数据，new表示将要或者已经修改后的数据
#3.delete型触发器：old表示将要或者已经删除的数据

#创建触发器：
# create trigger trigger_name
#     before /after insert/update/delete
#     on tb_name for each row --行级触发器
# begin
#     trigger_stmt;
# end;
#查看触发器：
# show triggers ;
#删除触发器
# drop trigger [database_name]trigger_name;
#如果没有指定数据库名字，则默认为当前数据库

#通过触发器记录user表的数据变更日志(user_logs)，包含增加，修改，删除
# create table user_logs(
#     id int(11) not null primary key auto_increment,
#     operation varchar(15) not null comment '操作类型,insert/update/delete',
#     operate_time datetime not null comment '操作时间',
#     operate_id int(11) not null comment '操作id',
#     operate_param varchar(50) comment '操作参数',
#
# )
# insert触发器
# create trigger tb_user_insert_trigger
#     after insert on tb_user for each row
# begin
#     insert into user_logs(id, operation, operate_time, operate_id, operate_param) VALUES
#     (null,'insert',now(),new.id,concat('插入的数据内容为：id='));
# end;
#update触发器，old.id是更新之前的，new.id是更新之后的
# create trigger tb_user_update_trigger
#     after update on tb_user for each row
# begin
#     insert into user_logs(id, operation, operate_time, operate_id, operate_param) VALUES
#     (null,'update',now(),new.id,concat('更新之前的数据内容为：id=',old.id,'更新的数据内容为：id=',new.id);
# end;
#delete触发器
# create trigger tb_user_delete_trigger
#     after delete on tb_user for each row
# begin
#     insert into user_logs(id, operation, operate_time, operate_id, operate_param) VALUES
#     (null,'update',now(),new.id,concat('删除的数据内容为：id=',old.id);
# end;

#锁：计算机协调多个进程或线程并发访问某一资源的机制
#1.全局锁，锁定数据库中的所有表2.表级锁，每次锁住整张表3.行级锁，每次所著对应的行数据

#全局锁：对整个数据库实例枷锁，之后整个实例处于只读状态，后续的DML，DDL，已经更新操作的事务提交语句将被阻塞
#使用场景是做全库逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
#加全局锁：
# flush tables with read lock ;加锁
# mysqldump -uroot -p 432900 itcast>/root/itcast.sql;备份
# unlock tables;释放
#使用全局锁进行一致性数据备份
#特点：主库上备份，备份期间不能执行更新，业务无法进行
#从库上备份，从库无法执行主库同步过来的二进制日志，会导致主从延迟
#innodb中，可以在备份时加上---single-transcation参数来完成不加锁的一致性数据备份
# mysqldump --single-stransaction -uroot -p1234 itcast>/root/itcast.sql

#表级锁
#每次锁住整张表，发生锁冲突的概率最高
#1.表锁 2.元数据锁 3.意向锁

#表锁：1.表共享读锁 2.表独占写锁
#加表锁：lock tables 表名 read/write
#释放：unlock tables/客户端断开连接
#读锁：当前和其他客户端都只能读不能写
#写锁：当前客户端能读能写，其他客户端不能读也不能写

#元数据锁(MDL):系统自动控制，无需现时使用，主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以队员数据进行写入操作，为了避免DML和DDL，保证读写的正常行
#selcet/update等增删改查时，系统会自动加上MDL读锁，(前提是手动开启事务);
#alter等变更表结构的操作时，加MDL写锁(排他)，也就是说在手动开启事务的前提下，修改表结构和对表的增删查改不能同时进行
#开启事务不会添加MDL，增删改查和修改表结构操作时才会加锁

#意向锁：避免DML在执行时加的行锁与表锁的冲突，使得表锁不用检查每行数据是否加锁
#若意向锁与表锁兼容，则可以执行表锁，若不兼容则会处于阻塞状态
#意向共享锁IS：由语句select...lock in share mode一天假
#意向排他锁IX：由insert/update/delete...for update添加
#IS与表锁共享锁(读锁)兼容，与写锁互斥
#IX与读锁和写锁都互斥，意向锁之间不会互斥

#行级锁：每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的改率最低
#1.行锁/记录锁 2.间隙锁：锁定索引记录间隙(不含该记录),确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读
#3.临键锁：行锁和间隙锁组合，同时所著数据，并锁住数据前面的间隙

#行锁：1.共享锁 2.排他锁
#共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
#排他锁：允许获取排他锁的十五更新数据，组织其他事务获得相同数据集的共享锁和排他锁
#只有共享锁与共享锁兼容
#事务一旦提交，锁就被释放

#间隙锁/临键锁：1.索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁
#2.索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁
#3.索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止

#间隙锁的唯一目的是防止其他事务插入间隙造成幻读，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁