一、视图
1、什么是视图
视图就是通过查询得到一张虚拟表,然后保存下来,下次用的直接使用即可
2、为什么要用视图
查询出来 的表打印出来就没了,再次需要的话还要再次查询
如果要频繁使用一张虚拟表,可以不用重复查询
3、如何用视图
create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;
强调
1、在硬盘中,视图只有表结构文件,没有表数据文件
2、视图通常是用于插叙,尽量不要修改视图中的数据
drop view teacher2course;删除
二、触发器
1、什么是触发器
在满足对某张表数据的增、删、改的情况下,自动触发的功能称之为触发器
2 为何要用触发器?
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为,这类行为一旦执行
就会触发触发器的执行,即自动运行另外一段sql代码
3 创建触发器语法
(1)针对插入
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
tri_after_insert_t1为触发器的名字
for each row监测每一条改动
(2)针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row begin sql代码。。。 end create trigger tri_after_delete_t2 before delete on 表名 for each row begin sql代码。。。 end
(3)针对修改
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
(4)案例
#创建触发器
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;
#验证触发器
#往表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 errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2017-09-14 22:18:48 | | 2 | useradd xxx | 2017-09-14 22:18:48 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec)
三、事务
01 什么是事务
开启一个事务可以包含一些sql语句,这些sql语句要么同时成功,要么一个都别想成功,称之为事务的原子性
start transaction
rollback 出现错误则执行回滚
commit 没有错误则提交
(1)四个特性:
原子性:
一致性:完整性一致,约束条件都一样的
隔离性:
持久性:
(2)四个隔离级别:
读未提交
读已提交:防止脏读,不能防止幻读和不可重复读
可重复读
序列化执行(串行) 安全,效率低
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'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 except 异常: rollback; else: commit;
和捕捉异常一样
四、存储过程
1、什么是存储过程
存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
2、三种开发模型
1、 应用程序:只需要开发应用程序的逻辑 mysql:编写好存储过程,以供应用程序调用 优点:开发效率,执行效率都高 缺点:考虑到人为因素、跨部门沟通等问题,会导致扩展性差 2、 应用程序:除了开发应用程序的逻辑,还需要编写原生sql mysql: 优点:比方式1,扩展性高(非技术性的) 缺点: 1、开发效率,执行效率都不如方式1 2、编写原生sql太过于复杂,而且需要考虑到sql语句的优化问题 3、 应用程序:开发应用程序的逻辑,不需要编写原生sql,基于别人编写好的框架来处理数据,ORM mysql: 优点:不用再编写纯生sql,这意味着开发效率比方式2高,同时兼容方式2扩展性高的好处 缺点:执行效率连方式2都比不过
通常我们开发选第二种和第三种
3、创建存储过程(好比Python中的函数)
elimiter $$ 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 ;
关键字procedure m和n为参数,数据格式为整型 ,out是参数,也是返回值,数据类型为整型
4、如何调用存储过程
(1)在mysql中直接调用
call p1(2,4,10);直接传值不合理的,在报错,第三个必须要是变量
set @res=10
call p1(2,4,@res);
#查看结果
select @res;
(2)在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()
五、数据库函数
1、定义:
在SQL 语句中,表达式可用于一些诸如SELECT语句的ORDER BY 或 DELETE或 UPDATE语句的 WHERE ⼦句或 SET语句之类的地放。使用文本值、column值、NULL值、函数、 操作符来书 写 表达式。这些内置函数极大提高了我们的开发效率。
2、自定义函数
!!!注意!!!
#函数中不要写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 ;
3、删除函数:
drop function func_name;
4、执行函数
# 获取返回值 select UPPER('egon') into @res; SELECT @res; # 在查询中使用 select f1(11,nid) ,name from tb2;
六、控制流程
1、条件语句:
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 ;
2、循环语句:
(1)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 ;
(2)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 ;
(3)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