数据库之mysql视图、触发器、事务、存储过程、函数等相关内容-47

1.视图

# =============================创建视图
​
select * from emp inner join dep on emp.dep_id = dep.id;
​
​
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id;
​
​
mysql> update emp2dep set name="EGON" where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from emp2dep;
+----+-----------+--------+------+--------+--------------+
| id | name      | sex    | age  | dep_id | dep_name     |
+----+-----------+--------+------+--------+--------------+
|  1 | EGON      | male   |   18 |    200 | 技术         |
|  2 | alex      | female |   48 |    201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 | 技术         |
+----+-----------+--------+------+--------+--------------+
5 rows in set (0.00 sec)
​
mysql>
mysql>
mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | EGON       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
|  7 | lili       | female |   48 |   NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)
​
mysql>
​
# =============================修改视图
alter view emp2dep as 查询语句;
​
# =============================删除视图
drop view emp2dep;

2.触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
# 增=》insert
delimiter //
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
    sql语句;
end //
delimiter ;
​
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
    sql语句;
end
​
# 删除=》delete
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
    sql语句;
end
​
create trigger tri_after_delete_t1 after delete on t1 for each row
begin
    sql语句;
end
​
​
# 修改=》update
​
​
​
​
​
# 例如
insert into tt1 values(1,"egon",'male');
​
​
delimiter //
create trigger tri_before_insert_tt1 before insert on tt1 for each row
begin
    insert into tt2 values(NEW.name);
end //
​
delimiter ;
​
​
​
insert into tt1 values(2,"tom",'female');
​
​
​
​
​
# 练习
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 ;
​
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');
​
drop trigger tri_after_insert_cmd ;

3.事务

create table user1(
    id int primary key auto_increment,
    name varchar(10),
    balance int
);
​
​
insert into user1(name,balance) values
('liuhongwei',1000),
('鸨哥',1000),
('力哥',1000);
​
start transaction;
update user1 set balance=800 where id=1;
update user1 set balance=1100 where id=2;
update user1 set balance=1100 where id=3;
​
# 回滚
rollback;
​
# 真正提交到数据库内
commit;

4.存储过程

程序与数据库结合使用的三种方式
​
复制代码
#方式一：
    MySQL：存储过程
    程序：调用存储过程
​
#方式二：
    MySQL：
    程序：纯SQL语句
​
#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）
​
​
# 创建无参存储过程
delimiter $$
create procedure p1()
begin
    select * from emp;
end $$
​
delimiter ;
​
call p1();
​
# 创建有参存储过程
delimiter $$
create procedure p2(
    in n int,
    out res int
)
begin
    select * from emp where id > n;
    set res=1;
end $$
​
delimiter ;
​
​
​
==========================>在mysql里如何调用存储过程
mysql> set @x=1111;
Query OK, 0 rows affected (0.00 sec)
​
mysql> call p2(3,x);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine db4.p2 is not a variable or NEW pseudo-variable in BEFORE trigger
mysql> call p2(3,@x);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
|  7 | lili       | female |   48 |   NULL |
+----+------------+--------+------+--------+
4 rows in set (0.00 sec)
​
Query OK, 0 rows affected (0.01 sec)
​
mysql> select @x;
+------+
| @x   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
​
mysql>
==========================>在pymysql里如何调用存储过程

5.应用程序执行存储过程

import pymysql  # pip3 install pymysql
​
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123", db="db4", charset="utf8mb4")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
​
​
cursor.callproc('p2',(3,0))  # @_p2_0=3,@_p2_1=0
'''
set @_p2_0=3
set @_p2_1=0
​
call p2(@_p2_0,@_p2_1);
​
'''
​
print(cursor.fetchall())
​
cursor.execute("select @_p2_1;")
print(cursor.fetchall())
​
cursor.execute("select @_p2_0;")
print(cursor.fetchall())
​
cursor.close()
conn.close()

6.函数

准备表和记录
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") as t,count(id) from blog group by t;

7.索引原理

# 1、为何要用索引？
创建索引的目的就是为了优化查询速度
但是一张表一旦创建了索引，会降低写速度
​
# 2、什么是索引？
索引是mysql数据库的一种数据结构，在mysql里称之为key
​
select * from 表 where id=30000;