mysql数据库从删库到跑路之mysql:视图、触发器、事务、存储过程、函数

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

一、视图

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

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

create table dep(
id int primary key auto_increment,
name char(32)
)engine=innodb default charset utf8;

mysql> select * from dep;
+----+--------+
| id | name   |
+----+--------+
|  1 | 外交部 |
|  2 | 销售   |
|  3 | 拆迁部 |
+----+--------+
3 rows in set (0.00 sec)

create table user(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
)engine=innodb default charset utf8;
#
mysql> select * from user;
+----+---------+--------+
| id | name    | dep_id |
+----+---------+--------+
|  1 | egon    |      1 |
|  2 | alex    |      3 |
|  3 | yuanhao |      2 |
+----+---------+--------+
3 rows in set (0.00 sec)

#建立视图
CREATE VIEW user_dep AS SELECT
	USER.id uid,
	USER. NAME uname,
	dep.id depid,
	dep.NAME depname
FROM
	USER
LEFT JOIN dep on dep_id = dep.id;

mysql> select * from user_dep;
+-----+---------+-------+---------+
| uid | uname   | depid | depname |
+-----+---------+-------+---------+
|   1 | egon    |     1 | 外交部  |
|   3 | yuanhao |     2 | 销售    |
|   2 | alex    |     3 | 拆迁部  |
+-----+---------+-------+---------+
3 rows in set (0.00 sec)

 

 

二、使用视图

#先创建一个视图
mysql> create view course_view as select * from user;
Query OK, 0 rows affected (0.05 sec)
#多了一个刚刚建立的视图
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| course_view   |
| dep           |
| user          |
| user_dep      |
+---------------+
4 rows in set (0.00 sec)
#查看视图数据
mysql> select * from course_view;
+----+---------+--------+
| id | name    | dep_id |
+----+---------+--------+
|  1 | egon    |      1 |
|  2 | alex    |      3 |
|  3 | yuanhao |      2 |
+----+---------+--------+
3 rows in set (0.00 sec)

mysql> update course_view set name='李平';    #更新视图里面的数据
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3  Changed: 3  Warnings: 0
#查看刚刚改的视图
mysql> select * from course_view;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 李平 |      1 |
|  2 | 李平 |      3 |
|  3 | 李平 |      2 |
+----+------+--------+
3 rows in set (0.03 sec)

mysql> insert into course_view values(5,'张三',2);
Query OK, 1 row affected (0.05 sec)

mysql> select * from user;              #原始表的记录也跟着改了
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 李平 |      1 |
|  2 | 李平 |      3 |
|  3 | 李平 |      2 |
|  5 | 张三 |      2 |
+----+------+--------+
4 rows in set (0.00 sec)

 

 

三、修改视图

 alter view user_dep as select * from  user where id=3;       #修改为只有id=3的字段
 mysql> select * from user_dep;                 #在查看视图，发现就只有id=3的了
+----+---------+--------+
| id | name    | dep_id |
+----+---------+--------+
|  3 | yuanhao |      2 |
+----+---------+--------+
1 row in set (0.03 sec)

 

四、删除视图

语法：DROP VIEW 视图名称

DROP VIEW teacher_view

 

 

二、触发器

 

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

一 创建触发器

# 插入前
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
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

 

第一步创建表
mysql> create table cmd_log(
    -> id int primary key auto_increment,
    -> cmd_name char(64),
    -> sub_time datetime,            #提交时间
    -> user_name char(32),         
    -> is_success enum('yes','no')    #0代表执行失败
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> create table err_log(
    -> id int primary key auto_increment,
    -> cname char(64),
    -> stime datetime
    -> );
Query OK, 0 rows affected (0.27 sec)
#创建触发器
mysql> delimiter //
mysql> CREATE
    ->     TRIGGER tri_after_insert_cmd_log
    ->     after insert
    ->     ON cmd_log FOR EACH ROW
    -> BEGIN
    ->     if new.is_success = 'no' then            #等值判断如果登陆on则插入到err_log的表里面
    ->         insert into err_log(cname,stime) values(new.cmd_name,new.sub_time);
    ->     end if;     #if语句必须end if;结束
    -> END //            #END表示语句结束
mysql> delimiter ;
Query OK, 0 rows affected (0.17 sec)

#往表中插入数据触发触发器，根据if的条件决定是否插入错误日志
mysql> insert into cmd_log(cmd_name,sub_time,user_name,is_success) values    
    -> ('ls -l /etc | grep *.conf',now(),'root','no'), #NEW.id,NEW.cmd_name,NEW.sub_time   
    -> ('ps aux |grep mysqld',now(),'root','yes'),
    -> ('cat /etc/passwd |grep root',now(),'root','yes'),
    -> ('netstat -tunalp |grep 3306',now(),'egon','no');
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0
            
#查询错误日志的表，
mysql> select * from err_log;
+----+----------------------------+---------------------+
| id | cname                      | stime               |
+----+----------------------------+---------------------+
|  1 | ls -l /etc | grep *.conf   | 2017-09-18 19:33:06 |        #发现果然有两张表记录了插入的数据内容，插入的时间
|  2 | netstat -tunalp |grep 3306 | 2017-09-18 19:33:06 |
+----+----------------------------+---------------------+
2 rows in set (0.00 sec)

 

二、删除触发器

drop trigger tri_after_insert_cmd;

 

三、事务

#事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
create table user1(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user1(name,balance) values
('庞文废',200),
('萧pt',200),
('八哥',200);

start transaction;
update user1 set balance=100 where name='ss';
update user1 set balance=210 where name='pt';
update user1 set balance=290 where name='bage';
rollback; #如果任意一条sql出现异常都应该回滚到初始状态
commit; #如果所有的sql都正常，应该执行commit

start transaction;
update user1 set balance=100 where name='ss';
update user1 set balance=210 where name='pt';
updssssssate user1 set balance=290 where name='bage';
rollback; #如果任意一条sql出现异常都应该回滚到初始状态
commit; #如果所有的sql都正常，应该执行commit

 

 

四、存储过程

一 介绍

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

使用存储过程的优点：

#使用存储过程的优点
    #1 程序与数据实现解耦
    #2 减少网络传输的数据量

 

使用存储过程的缺点：

#1. 执行效率低

#2. 程序员扩展功能不方便

 

补充：程序与数据库结合使用的三种方式

 

二、创建存储过程的方式

#方式一：
    MySQL：存储过程
    程序：调用存储过程

#方式二：
    MySQL：
    程序：纯SQL语句

#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）

 

三、创建简单存储的过程

#===============================================
#创建无参存储过程
delimiter //
create procedure p1()
begin
    select * from test;
    insert into test(username,dep_id) values('wsb',2);
end //
delimiter ;

#调用存储过程
call p1(); #在mysql中调用
cursor.callproc('p1') #在python中通过pymysql模块调用

 

四、创建有参存储的过程

对于存储过程，可以接收参数，其参数有三类：

#in          仅用于传入参数用
#out        仅用于返回值用
#inout     既可以传入又可以当作返回值

 

#创建有参存储过程之in的使用
delimiter //
create procedure p2(
    in m int,
    in n int,
)
begin
    select * from test where id between m and n;
end //
delimiter ;

#调用存储过程
call p2(3,7); #在mysql中调用
cursor.callproc('p2',args=(3,7)) #在python中通过pymysql模块调用

#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值

 

#创建有参存储过程之inout的使用

delimiter //
create procedure p4(
    inout m int
)
begin
    select * from test where id > m;
    set m=1;
end //
delimiter ;

#在mysql中
set @x=2;
call p4(@x);
select @x;



delimiter //
create procedure p5(
    inout m int
)
begin
    select * from test11111111 where id > m;
    set m=1;
end //
delimiter ;

set @x=2;
call p5(@x);
select @x;

 

#===============================================
#创建有参存储过程之out的使用
delimiter //
create procedure p3(
    in m int,
    in n int,
    out res int
)
begin
    select * from test where id between m and n;
    set res=1;
end //
delimiter ;

#调用存储过程
#在mysql中
set @x=11111111111
call p3(3,7,@x); #在mysql中调用, 查看结果：select @x;

#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值

 

#====================捕捉异常+事务===========================
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;
        insert into test(username,dep_id) values('egon',1);
        DELETE from tb1111111; #执行失败
    COMMIT;

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

END //
delimiter ;


#用python模拟
try:
    START TRANSACTION;
        DELETE from tb1; #执行失败
        insert into blog(name,sub_time) values('yyy',now());
    COMMIT;
    set p_return_code = 0; #0代表执行成功
except sqlexception:
    set p_return_code = 1;
    rollback;
except sqlwaring:
    set p_return_code = 2;
    rollback;




mysql> show procedure status like 'p1%'; #查看某一类存储过程

 

四、执行存储过程

import pymysql

conn=pymysql.connect(host='localhost',user='root',password='',database='day48',charset='utf8')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123

conn.commit()
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果

cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall())
cursor.close()
conn.close()

 

五、删除存储过程

drop procedure proc_name;

 

 

 

 

 

五、函数

CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

    CONV(N,from_base,to_base)
        进制转换
        例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写

    LTRIM(str)
        返回字符串 str ，其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ，结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列

    LOCATE(substr,str,pos)
        获取子序列索引位置

    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL，则返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ，顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始，返回从后边开始len个字符组成的子序列

    SPACE(N)
        返回一个由N空格组成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

部分内置函数

 

#需要掌握的函数
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');
# 提取sub_time字段的值，按照格式输出出来
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)

 

一 自定义函数

#自定义函数
mysql> select f1(1,2)
    -> ;
+---------+
| f1(1,2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select f1(1,2) into @res;
Query OK, 1 row affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

 

自定义函数

#自定义函数
mysql> select f1(1,2)
    -> ;
+---------+
| f1(1,2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

mysql> select f1(1,2) into @res;
Query OK, 1 row affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

 

删除函数

 

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 ;

 

二、循环语句

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

 

 

 

 

 

 

<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">

 

 

 

 

来自为知笔记(Wiz)