视图
视图是一个虚拟表（非真实存在的），其本质就是根据SQL语言获取动态的数据集，并为其命名，用户使用时只需要使用名称即可获得结果集，可以将结果集当做表来使用。
视图是存在数据库中的，如果我们程序中使用的sql过分依赖数据库的视图，即强耦合，那就意味着扩展不方便。
创建视图：
create view view_name as select * from table1 inner join table2 on table1.id=table2.sid;
使用视图以后就无需每次都重写子查询的sql，但是效率并不高，不如我们写子查询的效率高。
致命问题是视图是存放在数据库的，如果我们 程序中的sql过分依赖数据库中存放的视图 ，就意味着一旦sql需要修改且涉及视图的部分 就必须去数据库中修改，通常是由专门的DBA负责的，要是想要修改，必须付出大量的沟通成本才会帮我们去修改。
我们不应该修改视图中的记录的，而且涉及多个表的情况下根本是无法修改视图中的记录的。
修改视图：
alter view view_name as sql语句
删除视图：
drop view view_name;
触发器
触发器是在满足某种条件，自动触发的功能
应用场景：专门针对我们某一张表数据进行增删改的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另一段sql代码。
创建触发器：
1.插入前
create trigger tri_before_insert_tb1 before insert on tb1 for each row begin …… end
2.插入后
create trigger tri_after_insert_tb1 after insert on tb1 for each row begin …… end
3.例子
#准备表
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
('aaaa','','ls -l /etc',NOW(),'yes'),
('aaaa','','cat /etc/passwd',NOW(),'no'),
('aaaa','','useradd xxx',NOW(),'no'),
('aaaa','','ps aux',NOW(),'yes');
注意：NEW表示即将插入的数据行，mysql每插入一条数据就会将其封装为一个对象new。OLD表示即将删除的数据行。
使用触发器
触发器是无法由用户直接使用的，对表的改数据操作是被动引发的。
删除触发器
drop trigger tri_after_insert_cmd;
事务
开启一个事务可以包含一系列sql语句，这些语句要么 同时成功，要么一个都不成功，这称为事务的原子性。事务主要用于交易类。
事务将某些操作的多个SQL作为原子操作，一旦出现某些错误，即可回滚到原来状态（commit了就不能改了），从而保证数据的完整性。
开启事务：
#数据表准备
create table user(
id int primary key auto_increment,
name char(32),
balance int
); #数据准备
insert into user(name,balance) values
('aaa',1000),
('bbb',1000),
('ccc',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元
commit;
#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='aaa'; #买支付100元
update user set balance=1010 where name='bbb; #中介拿走10元
uppdate user set balance=1090 where name='ccc'; #卖家拿到90元,出现异常没有拿到
rollback;
#rollback只有在commit之前才能回滚到原来状态，commit之后数据在硬盘上改变了，就无法回滚了 commit;
存储过程
存储过程是完全有专门的开发人员开发管理，程序开发人员需要跨部门沟通，导致扩展性差。
存储过程包含了一系列可执行的sql语句，存储过程存放在mysql中，通过调用它的名字可以执行其内部的一堆sql。
使用存储过程的优点：
- 用于替代程序写的SQL语句，实现程序与SQL解耦
- 基于网络传输，用存储过程只需要传输封装好的别名，而直接传输数据量大，网络IO慢。
缺点：程序员扩展不方便
三种开发模型：
1.应用程序：只需开发应用程序的逻辑
mysql编写好存储过程，以供应用程序调用
优点：开发效率、执行效率高
缺点：考虑到人为因素、跨部门沟通导致扩展性差
2.应用程序：除了开发应用程序的逻辑，还需要编写原生的sql
优点：比方式一，扩展性高（非技术）
缺点：开发效率、执行效率都不如方式1
编写原生sql太过复杂，需要考虑sql优化问题
3.应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人写的框架来处理数据，ORM
优点：不用再编写原生sql，比原生开发效率高，同时兼容扩展性高的好处
缺点：执行效率连方式2都比不过3
创建存储过程
delimiter $$
create procedure p1( #创建存储过程p1
in m int, #mysql中参数必须先规定类型和用途（in，out，inout）
in n int,
out res int #res用于接收返回值
)
begin
select tname from teacher where tid > m and tid < n;
set res=0;
end $$
delimiter ;
如何用存储过程
a、直接在mysql中调用
set @res=10 #mysql中变量的定义要用@abc的形式
call p1(2,4,@res);
select @res; #查看结果
b、在python程序中调用
import pymysql
conn=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='',
charset='utf8',
database='db42'
)
cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10 #pymysql帮助对传入变量进行以上变形
print(cursor.fetchall())
cursor.execute('select @_p1_2;') #查看返回值，确认执行结果
print(cursor.fetchone())
cursor.close()
conn.close()
事务的使用 （事务+存储过程）
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 ;
在python中调用存储过程
import pymysql conn=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='root',
charset='utf8',
database='mydb' )
cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p6',(100,)) #@_p5_0 = 100
cursor.execute('select @_p6_0')
print(cursor.fetchone())
cursor.close()
conn.close()
删除存储过程：
prop procedure proc_name;
函数
1、强调：mysql内置的函数只能在sql语句中使用
mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
2、补充
a、select * from s1 \G #表字段太多（字段行显示不全）时，用\G将表竖着显示出来： row1 哪些字段： 对应的内容，row2。。。
b、视图、触发器、事务、存储过程、函数、流程控制皆是在库下面建立
流程控制 （if，while，case）
#case
select
case
when name = 'aaa' then
name
when name = 'bbb' then
concat(name,'_BIGSB')
else
concat(name,'_SB')
end
from emp;