MySQL 视图 触发器 存储过程 函数 流程控制

视图

视图就是一张虚拟表,虚拟表都是通过查询得到的

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

强调:
1.在硬盘中虚拟表只有 .frm文件(表结构),没有 .idb文件(数据)
2.使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高
3.视图通常用来辅助查询,不要修改视图中的数据!!!

案例表

create table course(
  cid int primary key auto_increment,
  cname char(20),
  teacher_id int
);
create table teacher(
  tid int primary key auto_increment,
  tname char(20)
);

insert into course(cname, teacher_id) values
    ('生物',1),
    ('物理',2),
    ('体育',3),
    ('美术',2);

insert into teacher(tname) values 
  ('张磊老师'),
  ('李平老师'),
  ('刘海燕老师'),
  ('朱云海老师'),
  ('李杰老师');

 select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+

select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+

• 创建视图

创建一张老师与课程内连接的虚拟表
create view course_teacher as
    select * from course inner join teacher on course.teacher_id = teacher.tid;

select * from course_teacher;
+-----+--------+------------+-----+-----------------+
| cid | cname  | teacher_id | tid | tname           |
+-----+--------+------------+-----+-----------------+
|   1 | 生物   |          1 |   1 | 张磊老师        |
|   2 | 物理   |          2 |   2 | 李平老师        |
|   3 | 体育   |          3 |   3 | 刘海燕老师      |
|   4 | 美术   |          2 |   2 | 李平老师        |
+-----+--------+------------+-----+-----------------+

• 修改视图

ALTER VIEW 视图名称 AS SQL语句

• 删除视图 

DROP VIEW 视图名称

触发器

在满足对某张表数据 增删改 的情况下,自动触发的功能称之为触发器(一种监测机制)

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

创建触发器语法:
# 针对增
create trigger tri_after_insert_ti after insert on 表名 for each row
begin
    sql代码...
end
create trigger tri_before_insert_ti before insert on 表名 for each row
begin
    sql代码...
end
​
# 针对删
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
    sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
    sql代码。。。
end
​
# 针对改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
    sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
    sql代码。。。
end
​

 

# 案例
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
);
​
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);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了
​
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');
​
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;

存储过程

存储过程包含了一系列可执行的sql语句,存储过程放于MySQL中,通过调用它的名字可以执行其内部的一堆sql(像在对象里封装了一堆方法,直接用对象调用方法即可)

存储过程在哪个库下创建的只能在对应的库下面才能使用

优点

1.用于替代程序写的sql语句,实现程序与sql解耦

2.给予网络传输,传别名的数据量小,而直接传sql数据量大

缺点

1.程序员扩展功能不方便

创建存储过程 procedure

create procedure 名(参数)
begin
    sql语句
end

使用存储过程

• 无参数

delimiter $$
create procedure p1()
begin
    select * from user;
end $$

delimiter ;

#在mysql中调用
call p1() 

 call p1();
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | xiongda | 123      |
|  2 | xionger | 456      |
|  3 | xxx     | 789      |
+----+---------+----------+

• 有参数

in 用于传入参数使用

out 用于返回值用

inout 既可以传入也可以当做返回值

delimiter $$
create procedure p2(
    in n int,
    in m int,
    out res int
)
begin
    select * from user where id > n and id < m;
    set res=0;
end $$

delimiter ;

#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p2(1, 3, @res);  # 注意:返回值只能接收变量,所以要先定义变量再传入
select @res;

set @res=0; 

call p2(1, 3, @res);
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  2 | xionger | 456      |
+----+---------+----------+

select @res;
+------+
| @res |
+------+
|    0 |
+------+

python中调用存储过程 callproc

python中基于pymsql调用存储过程

游标.callproc(存储过程名, (参数们))

cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p2',(1, 3, 1))  # @_p2_0=1,@_p2_1=3,@_p2_2=1,
print(cursor.fetchall())  # [{'id': 2, 'name': 'xionger', 'password': '456'}]

cursor.execute('select @_p2_2;')
print(cursor.fetchall())  # [{'@_p2_2': 0}]

查看存储过程

show create procedure 名;

删除存储过程

drop function 名;

函数

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

参考博客：http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2

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

流程控制

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 ;

​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 ;