一,视图
1,什么是视图?
视图就是通过查询得到一张虚拟表,然后保存下来,下次用的话直接使用即可
2,为什么要用视图
如果要频繁使用一张虚拟表,可以不用重复查询
3,怎么用?
在查询前面加 create view 视图名称 as sql语句
create view teacher_view as select tid from teacher where tname='李平老师';
强调:在硬盘中,视图只有表结构文件,没有数据文件
视图开发尽量不用 因为是在mysql里面 ,视图通常用于插叙,尽量不要修改视图中的数据
2,修改视图:alter view 视图名称 as sql语句
3,删除视图:drop view 视图名称
二,触发器
1,触发器
在满足对某张表增删改的情况下,自动触发的功能称之为触发器 注意:没有查询
2,为什么用触发器
触发器专门针对我们对某一张表数据增insert,删delete,改update的行为,这类行为一旦执行
就会触发触发器的执行,即自动运行另外一段sql代码
3,创建触发器语法
create trigger tril after
针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
sql 代码。
end
create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
sql代码。。
end
针对删除
create trigger tri_after_delete_t1 after 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
04 案例
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 ;
drop trigger tri_after_insert_cmd;
触发器的两个关键字:new ,old
-- new :表示新的记录
-- old:表示旧的那条记录
-- 什么情况下才往里面插记录
-- 当命令输入错误的时候就把错误的记录插入到err_log表中
三,事务(重重重)
1,什么是事务?
开启一个事务可以包含一些sql语句要么同时成功,这些sql语句要么成功
要么一个都别想成功,称为事务的原子性·
2,事务的作用
务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,
即可回滚到原来的状态,从而保证数据库数据完整性。
事务也就是要么都成功,要么都不成功
事务就是由一堆sql语句组成的
3,如何用?
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values('wsb',1000),
('egon',1000),
('ysb',1000);
try:
update user set balance=900 where name='wsb';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='ysb';
except 异常:
rollback;
else:
commit;
如果都成功就执行commit,,,如果不成功就执行rollback。
rollback; #如果任意一条sql出现异常,都应该回归到初始状态
四,存储过程(重重重)
1,存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
存储过程的优点:
1.程序与数据实现解耦
2.减少网络传输的数据量
但是看似很完美,还是不推荐你使用
Mysql当中的函数不能单独调用
表竖着显示G
2, 三种开发模型
1、
应用程序:只需要开发应用程序的逻辑
mysql:编写好存储过程,以供应用程序调用
优点:开发效率,执行效率都高
缺点:考虑到人为因素、跨部门沟通等问题,会导致扩展性差
2、
应用程序:除了开发应用程序的逻辑,还需要编写原生sql
mysql:
优点:比方式1,扩展性高(非技术性的)
缺点:
1、开发效率,执行效率都不如方式1
2、编写原生sql太过于复杂,而且需要考虑到sql语句的优化问题
3、
应用程序:开发应用程序的逻辑,不需要编写原生sql,基于别人编写好的框架来处理数据,ORM
mysql:
优点:不用再编写纯生sql,这意味着开发效率比方式2高,同时兼容方式2扩展性高的好处
缺点:执行效率连方式2都比不过
03 创建存储过程
delimiter $$
create procedure p1(
in m int,
in n int,
out res int
)
begin
select tname from teacher where tid > m and tid < n;
set res=0;
end $$
delimiter ;
# 如何用存储过程
#1、直接在mysql中调用
set @res=10
call p1(2,4,10);
#查看结果
select @res;
#2、在python程序中调用
#3、事务的使用
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 ;
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=900 where id =1;
update user set balance=1010 where id = 2;
update user set balance=1090 where id =3;
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
4,在python 中调用存储过程
# import pymysql
#
# conn=pymysql.connect(
# host='127.0.0.1',
# port=3306,
# user='root',
# password='123',
# 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
#
#
# print(cursor.fetchall())
#
# cursor.execute('select @_p1_2;')
# print(cursor.fetchone())
#
# cursor.close()
# conn.close()
# import pymysql
#
# conn=pymysql.connect(
# host='127.0.0.1',
# port=3306,
# user='root',
# password='123',
# charset='utf8',
# database='db44'
# )
#
# 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()
五,函数
1、强调:mysql内置的函数只能在sql语句中使用
mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
六,流程控制
1.条件语句
delimiter //
create procedure proc_if()
begin
declare i int default 0;
if i=1then
select 1;
elseif i = 2 then
select 2;
else
select 7;
end if;
end //
delimiter;
2,循环语句
delimiter //
create procedure proc_while()
being
declare num int;
set num = 0;
where num<10 do
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
3,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 ;
4,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
select
case
when name = 'egon' then
name
when name = 'alex' then
concat(name,'_BIGSB')
else
concat(name,'_SB')
end
from emp;