视图、触发器、事务、存储过程、函数

视图

视图是一个虚拟表（非真实存在），其本质是根据SQL语句获取动态的数据集，并为其命名，
用户使用时只需使用名称即可获取结果集，可以将该结果集当做表来使用。

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

#两张有关系的表
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)


mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
5 rows in set (0.00 sec)


#查询李平老师所教的课程名
mysql> select cname from course where teacher_id = 
       (select tid from teacher where tname='李平老师');
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
2 rows in set (0.00 sec)


#子查询出临时表，作为teacher_id等判断依据
select tid from teacher where tname='李平老师'


1）创建视图，不能包含子查询
#语法：create view 视图名称 as  SQL语句

create view teacher_view as select tid from teacher where tname='李平老师';


#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = 
       (select tid from teacher_view);
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
2 rows in set (0.00 sec)


#使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

#而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放
的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在
公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修
改，极其地不方便


2)使用视图

#修改视图，原始表也跟着改
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> create view course_view as select * from course;
Query OK, 0 rows affected (1.82 sec)               ##创建表course的视图

mysql> select * from course_view;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.01 sec)


mysql>  update course_view set cname='xxx';     #更新视图中的数据
Query OK, 4 rows affected (1.87 sec)
Rows matched: 4  Changed: 4  Warnings: 0


mysql> insert into course_view values(5,'yyy',2);
Query OK, 1 row affected (1.89 sec)


mysql> select * from course;           #原始表的记录也跟着变，
+-----+-------+------------+
| cid | cname | teacher_id |           #所以不能直接修改视图中的数据
+-----+-------+------------+
|   1 | xxx   |          1 |
|   2 | xxx   |          2 |
|   3 | xxx   |          3 |
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
5 rows in set (0.00 sec)



#在涉及多个表的情况下是根本无法修改视图中的记录的


3）修改视图

语法：alter view 视图名称 as SQL语句

mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (1.86 sec)

mysql> select * from teacher_view;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
2 rows in set (0.00 sec)


4）删除视图
语法：drop view 视图名称

mysql> drop view teacher_view;
Query OK, 0 rows affected (0.00 sec)

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

1)创建触发器
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
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 $$
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 ;

#往表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');


mysql> select * from cmd;
+----+------+------+-----------------+---------------------+---------+
| id | USER | priv | cmd             | sub_time            | success |
+----+------+------+-----------------+---------------------+---------+
|  1 | egon | 0755 | ls -l /etc      | 2017-11-01 16:23:12 | yes     |
|  2 | egon | 0755 | cat /etc/passwd | 2017-11-01 16:23:12 | no      |
|  3 | egon | 0755 | useradd xxx     | 2017-11-01 16:23:12 | no      |
|  4 | egon | 0755 | ps aux          | 2017-11-01 16:23:12 | yes     |
+----+------+------+-----------------+---------------------+---------+
4 rows in set (0.00 sec)

mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-11-01 16:23:12 |
|  2 | useradd xxx     | 2017-11-01 16:23:12 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)


特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。


2）使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。


3）删除触发器
mysql> drop trigger tri_after_insert_cmd;
Query OK, 0 rows affected (0.01 sec)

触发器

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，
即可回滚到原来的状态，从而保证数据库数据完整性。

mysql> create table user(
    -> id int primary key auto_increment,
    -> name char(32),
    -> balance int
    -> );
Query OK, 0 rows affected (0.85 sec)

mysql>
mysql> insert into user(name,balance)
    -> values
    -> ('wsb',1000),
    -> ('egon',1000),
    -> ('ysb',1000);
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

start transaction;
update user set balance=900 where id=1;
update user set balance=1010 where id=2;
update user set balance=1090 where id=3;
#一旦出现异常应该执行
#rollback;
commit;

事务

存储过程

1）存储过程：包含了一系列可执行的sql语句，存储过程存放于MySQL中，
                   通过调用它的名字可以执行其内部的一堆sql
     优点：用于替代程序写的SQL语句，实现程序与sql解耦；
              基于网络传输，传别名的数据量小，而直接传sql数据量大
     缺点：程序员扩展功能不方便
     程序与数据库结合使用的三种方式：
        ①mysql:存储过程
           程序：调用存储过程
        ②mysql:
           程序：纯sql语句
        ③ mysql:
            程序：类和对象，即ORM(本质还是纯sql语句）

2）创建简单存储过程（无参）       
mysql> create procedure p3()
    -> begin
    ->     declare i int default 1;
    ->     while (i<10) do
    ->         select * from course;
    ->         set i=i+1;
    ->     end while;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call p3();     在mysql中调用
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.00 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.01 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.02 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.02 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.03 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.04 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.04 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.05 sec)

+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | 生物   |
|   2 | 物理   |
|   3 | 体育   |
|   4 | 美术   |
+-----+--------+
4 rows in set (0.06 sec)

Query OK, 0 rows affected (0.06 sec)

#在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())


#创建有参的存储过程：可接收三类参数，in(仅用于传入参数），
     out(仅用于返回值用），inout(既可传入又可作返回值用）。

  in、out 传入、传出参数

mysql> delimiter $$
mysql> create procedure p4(
    ->     in x char(5),
    ->     out y int
    -> )
    -> begin
    ->     select cid from course where cname=x;
    ->     set y=1;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @x='生物';                          # 基于mysql调用
Query OK, 0 rows affected (0.02 sec)

mysql> set @y=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call p4(@x,@y);
+-----+
| cid |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select @y;                 #查看返回值
+------+
| @y   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


基于python调用
cur.callproc('p4',('生物',0))   #set @x='生物' ;set @y=0;
print(cur.fetchone())



#存储过程与事务
delimiter $$
create PROCEDURE p6(
    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=0 where id=1;
        update user1111 set balance=10 where id=2;
        update user set balance=20 where id=3;
        COMMIT;

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

END $$
delimiter ;

存储过程

#在MySQL中执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

#在python中执行存储过程
import pymysql
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='',
    database='day48',
    charset='utf8'
)

cur=conn.cursor()

res=cur.callproc('p2',('生物',0))
#set @x='生物'; #set @_p2_0;
#set @y=0; #set @_p2_1;

# print(res)

#拿到存储过程sql语句的执行结果
# cur.execute('select @_p2_0;')
cur.execute('select @_p2_1;')
print(cur.fetchone())

#拿到存储过程的返回值

cur.close()
conn.close()

执行存储过程

#删除存储过程
mysql> drop procedure p4;
Query OK, 0 rows affected (1.80 sec)

函数：更多函数，中文猛击这里 OR 官方猛击这里

#1 基本使用
mysql>  SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+
1 row in set (0.05 sec)

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277                                   |
+------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');
+-----------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w') |
+-----------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6                        |
+-----------------------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('1999-01-01', '%X %V') |
+------------------------------------+
| 1998 52                            |
+------------------------------------+
1 row in set (0.00 sec)



mysql>  SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00                              |
+---------------------------------+
1 row in set (0.00 sec)


#2 准备表和记录
mysql> CREATE TABLE blog (
    ->     id INT PRIMARY KEY auto_increment,
    ->     NAME CHAR (32),
    ->     sub_time datetime
    -> );
Query OK, 0 rows affected (2.05 sec)


mysql> 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');
Query OK, 9 rows affected (0.67 sec)
Records: 9  Duplicates: 0  Warnings: 0


#3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组
mysql> SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');

#结果
+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03                       |        2 |
| 2016-07                       |        4 |
| 2017-03                       |        3 |
+-------------------------------+----------+
3 rows in set (0.00 sec)

函数date_format

#自定义函数
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程

delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;



delimiter //
create function f5(
    i int
)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;

自定义函数

删除函数
drop function func_name;

# 获取返回值
select UPPER('egon') into @res;
SELECT @res;


# 在查询中使用
select f1(11,nid) ,name from tb2;

执行函数

流程控制

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 ;



#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循环
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

循环语句

posted @ 2017-11-01 19:08 星雨5213 阅读(114) 评论(0) 收藏举报

刷新页面返回顶部

星雨5213

视图、触发器、事务、存储过程、函数

视图、触发器、事务、存储过程、函数

公告