mysql-视图、触发器、事务、存储过程、内置函数、流程控制、索引

一、视图

  1.啥是视图?

    视图就是通过查询得到的一张虚拟表,然后保存下来,直接使用即可

  2.为啥要用视图呢?

    如果要频繁使用一张虚拟表,可以不用重复查询

  3.如何用视图

    创建:

    create view teacher_courus as

    select * from teacher inner join course on teacher.tid = course.teacher_id;

    删除:

    drop view teacher_course;

  强调

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

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

    ps:开发过程中一般不会去使用视图

 

二、触发器

  在满足对某张表数据的增、删、改的情况下,自动触发的功能称之为触发器。

  1.为何要用触发器?

    触发器专门针对我们对某一张表数据增insert、删delete、改updata的行为,这类行为一旦执行,就会触发触发器的执行,即自动运行另外一段sql代码

  2.创建触发器语法(六种)

    create trigger 触发器名字 after/before insert/update/delete on 表名 for each row

    begin

      sql 语句

    end

  增前/后  2种

  删前/后  2种

  改前/后  2种

    可以修改MySQL默认的结束符( ; )

    delimiter $$    # 只对当前窗口有效

    create trigger tri_before_insert_user before insert on user for each row

    begin

      select * from user;

    end $$

    delimiter ;

# 往cmd添加数据,如果success enum为no,则向errlog添加数据
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
);

#补充:NEW对象指代的就是当前记录
#触发器语法
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

 

三、事务 (同步完成几件事)

  事务包含一大堆sql语句,这些sql要么同事成功,要么一个也别想成功

  1.事务四大特性:ACID

    A :原子性

      一个事务是不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。

    C :一致性

      事务必须是使数据库从一个一致性状态变到另一个一致性状态,一致性与原子性密切相关。

    I :隔离性

      一个事务的执行不能被其他事务干扰。

    D:持久性

      一个事物一旦提交成功,他对数据库中数据改变是永久性的。

  2.开启事务:start transaction

   事务滚回:rollback

   永久性更改:commit

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

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元

# 回滚到上一个状态
rollback;

# 开启事务之后,只要没有执行commit操作,数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整,不完整主动回滚到上一个状态,如果完整就应该执行commit操作"""
代码实现

 

四、存储过程

  类似于python中的自定义函数

    内部封装了操作数据库的sql语句,后续想要实现相应的操作,只需要调用存储过程即可

  1.语法结构

    delimiter $$

    create procedure p1(

      in m int,   # 不能被返回出去

      in n int,

      out res int,  # 可以被返回

      inout xxx int,  # 既可以进又可以出

    )

    begin

      select * from user

    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 ;
'''
set @res = 10   # 设置一个变量与值的绑定关系

调用存储过程 将变量@res传入

之后通过
select @res
查看存储过程执行完成后的返回结果
'''
View Code

  2.存储过程在哪个库下面定义,就只能在哪个库下面使用

delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;


  DECLARE exit handler for sqlwarning
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;

  START TRANSACTION;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功


END //
delimiter ;
View Code

  3.如何使用

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据,所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功,@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理:@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')


# 3、存储过程与事务使用举例(了解)
delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;


  DECLARE exit handler for sqlwarning
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;

  START TRANSACTION;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功


END //
delimiter ;
View Code

 

五、函数

  注意与存储过程的区别,mysql内置的函数只能在sql语句中使用

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');

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
View Code

 

六、流程控制

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 ;
if条件语句
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 ;
while循环

 

七、索引与慢查询优化

  数据存储在硬盘上,查询数据不可避免的需要进行IO操作

  1.索引在mysql中也叫作 ‘键‘ ,是存储引擎用于快速找到记录的一中数据结构

    ● primary key

    ● unique key  

    ● index key  无约束功能,只帮忙加速查询

    ps:foreign key 不是用来加速查询,

  2.索引的影响

    在表中有大量数据的前提下,创建索引速度会很慢

    在索引创建完毕后,对表的查询性能会大幅度提升,但写的性能会降低

  3.b+树

    3.1只有叶子节点存放真是数据,根和树枝节点存放的仅仅是虚拟数据

    3.2查询次数有树的层级决定,层级越低次数越少

    3.3一个磁盘大小是一定,以为存放数据量是一定,所以我们一般用主键id字段建立索引,可以降低树的层级高度

  4.聚焦索引(primary key)

    用表的主键建立索引

   辅助索引(unique、index)

    查询数据不可能都用id作为筛选条件,用其他字段建立索引,就叫辅助索引

 

 

posted @ 2019-08-23 17:24  小王八+1  阅读(161)  评论(0编辑  收藏  举报