Erlo

Mysql 视图、触发器、事务、存储过程、流程控制、索引

时间:2020-09-16   阅读:23次   来源:博客园
页面报错
点赞

 一、视图:

本质:就是根据sql语句获取的动态数据集合,也就是一张虚拟表

注意:视图一般只用来查询数据,不作修改,因为涉及多张表的数据

基本语法:

创建:
create view 视图名字 as sql语句
修改:
alter view 视图名称 as sql语句
删除:
drop view 视图名字

 二、触发器:

本质:定制用户对表进行增删改操作时前后的行为

注意:触发器要在开头修改结束符号,结束后再修改回来

基本语法:

#创建触发器
delimiter $$ #修改mysql的结束符,只作用于当前窗口。
create trigger 触发器名字 before/after insert/update/delete on 表名 for each row
begin 
sql语句
end $$
delimiter ; #将mysql的结束符修改回默认的
​
#删除触发器
drop trigger 触发器名字

三、事务

作用:保证数据操作的安全性

四大特性(ACID):

原子性(A):事务里包含的所有操作要么一起成功,要么一起失败

一致性(C):事务必须是使数据库从一个一致性的状态变到另外一个一致性 的状态,该特性跟原子性密切相关。

隔离性(I): 并发事务内部操作是隔离的,不会互相干扰的。

持久性(D): 事务一旦执行成,对数据库中的修改是永久的。

三大关键字:

1、开始事务:start transaction

2、回滚(回到事务执行之前的状态): rollback

3、确定(确定后就无法回滚):commit

#例子:(持卡人去银行取1000,原来存款(cunkuan=3000),现金(xianjin=0)
start transaction
update user set xianjin=1000 where name="持卡人";
update user set cunkuan=2000 where name="持卡人储户";
#rollbak 若事务执行异常就可以添加回滚。
commit

四、存储过程

本质:类似于我们自定义的函数,将一系列可执行的sql语句封装在存储过程内部。

基本语法:

#创建存储过程,可以接收三类参数:
#in 仅用于传入参数使用
#out 仅用于返回值使用
#inout 既可以用于传入又可以当作返回值。
delimiter //
create procedure 存储过程的名字 (
in 形参名 形参类型,
out 形参名 形参类型,
inout 形参名 形参类型,
)
begin
sql代码
end //
delimiter ;
#调用存储过程
call 存储过程的名字();
#在python中基于pymysql模块调用
cusor.callproc('存储过程名字')

实例代码:

delimiter //
create procedure p1(
in n1 int,
out res int
)
begin 
select * from user where id>n1;
set res=1; #res用来判断存储过程是否执行
end //
delimiter ;
#在mysql 中调用
set @res=0; #定义一个变量res用来接收res的值
call p1(2,@res);
select @res; #查看#res的值,若@res=1,则存储过程执行了,=0,则没有执行

#在python中基于pymysql模块调用
cursor.callproc('p1',(2,0)) #0相当于set @res=0
#@_p1_0,@_p1_1,表示第一个参数,第二个参数,依此类推。。。。
cursor.execute('select @_p1_1');
print(cursor.fetchall())

五、流程控制:

1、条件语句:

#基本语法:
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、循环语句:

delimiter //
create procedure proc_while()
begin
  declare num int;
  set num =0;
  whiele num <10 do
    select 
      num;
    set num= num+1;
  end while;
end //
delimiter ;

#repeat循环
delimiter //
create procedure proc_repeat()
begin
  declare i int;
  set i=0;
  repeat
    select i;
    set i=i+1;
    until i>=5;
  end repeat;
end //
delimiter ;

#loop循环
delimiter //
create procedure proc_loop()
begin 
  declare i int default 0;
  loop_label: loop
    set i=i+1;
    if i<8 then
      iterate loop_label;
    end if;
    if i>=10 then
      leave loop_label;
    end if;
    select i;
  end loop loop_label;
end //
delimiter ;

六、索引:

本质:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果。
索引结构(b+树)
注意:
(1)最下面一行是叶子结点,只有叶子结点存放的是真实数据,其他结点存放的是虚拟数据。

 

 

 

b+树查找过程:
磁盘块1,红色表示数据项,黄色表示指针,数据项17,35,表示小于17的去P1找,大于35的去P3找,剩下的去P2找。依次类推,往下寻找,直到叶子结点。

b+ 树的性质:
(1)索引字段要尽量小:因为数据页大小是固定的,索引字段越小,占用空间越小,数据项存放的数据就会越多,树的高度就越低
(2)索引的最左匹配特性:比如查一个数据(姓名,性别,年龄),会先通姓名来确定搜索方向,若出现两个名字相同的,则会再根据性别来确定搜索方向,若性别相同,则会继续根据年龄来确定方向。若出现没有姓名的数据项(性别,年龄)则会不知往那个方向查。因为一开始是以姓名为比较因子的。


PS: 日常数据建表之所以建议将id字段作为主键(索引),是因为id占用空间少,这样一个磁盘块存储的数据就会多,进而可以降低树的高度,从而减少查询次数


1、聚集索引(primary key)

定义:就是按照每张表的主键构造一颗B+树,同时,叶子结点存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。
好处:对主键的范围查找和排序查找的速度非常快,叶子结点的数据就是 用户要查询的数据
可以根据范围查询,通过叶子节点的上层中间节点就可以得到页的范围,最后直接读取数据页。


2、辅助索引(unique,index)

定义:查询数据的时候,使用的是非主键字段的时候,就可以给该字段设置成辅助索引。
注意:叶子节点存放的是数据对应的主键值
辅助索引拿到的是数据的主键值,最终,还是需要去主键的聚集索引 里面查数据

3、覆盖索引

在辅助索引的叶子结点就已经拿到了需要的数据

#覆盖索引
#给name设置辅助索引
select name from user where name='hell';
#非覆盖索引
select age from user where name='hell';

 

相关推荐

提交留言

评论留言

还没有评论留言,赶紧来抢楼吧~~

吐槽小黑屋()

* 这里是“吐槽小黑屋”,所有人可看,只保留当天信息。

  • Erlo吐槽

    Erlo.vip2020-09-26 03:26:30Hello、欢迎使用吐槽小黑屋,这就是个吐槽的地方。
  • 返回顶部