视图
什么是试图
试图是由一张表或多张表的查询结果构成的一张虚拟表
为什么使用视图
我们在使用多表查询时 我们的sql语句可能会非常的复杂,如果每次都编写一遍sql'的话无疑是一件麻烦的事情,这时候就可以使用视图来避免多次编写sql的问题;
简答的说可以帮我们节省sql的编写,
视图的另一个作用是,可以不同的视图来展示开放不同数据的访问
例如,同一张工资表,老板可以查看全部,部门主管可以查看该部门所有人,员工只能看自己的一条记录
使用方法
语法:
create [or replace] view view_name as 查询语句;
or replace 如果视图已经存在了 就替换里面的查询语句;
# 修改视图
alter view view_name as 新的语句;
# 删除视图
drop view view_name;
# 查看
desc view_name;
show create view view_name;
# 限制可以查看的记录
create table salarys(id int,name char(10),money float);
insert into salarys values(1,"张三丰",50000),(2,"张无忌",40000);
# 创建视图 限制只能查看张无忌的工资
create view zwj_view as select *from salarys where name = "张无忌";
# 简化sql编写
create table student(
s_id int(3),
name varchar(20),
math float,
chinese float
);
insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);
create table stu_info(
s_id int(3),
class varchar(50),
addr varchar(100)
);
insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');
# 查询班级和学员的对应关系做成一个视图 方便后续的查询
create view class_info as select student.s_id,name,class from student join stu_info on student.s_id = stu_info.s_id;
select *from class_info;
注意: 修改视图 也会引起原表的变化,我们不要这么做,视图仅用于查询
触发器
什么是触发器
触发器是一段与表有关的mysql程序
当这个表在某个时间点发生了某种事件时 将会自动执行相应的触发器程序
创建触发器
语法：
create trigger t_name t_time t_event on table_name for each row
begin
#sql语句。。。。。：
end
案例：
#准备数据
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
);
#需求： 当插入cmd表 的时候 如果执行状态时失败的 那么将信息插入到errlog中
# 将结束符设置为|
delimiter |
create trigger cmd_insert after insert on cmd for each row
begin
if new.success = "no" then
insert into errlog values(null,new.cmd,new.sub_time);
end if;
end|
# 在还原之前的结束符
delimiter ;
# 创建一个触发器 叫cmd_insert
# 触发器会在 插入数据到cmd表后执行
# 当插入的记录的success为no时 自动插入记录到errlog中
# 错误原因 遇到分号自动提交了 , 需要重定义 行结束符
delimiter |
# 删除触发器
drop trigger cmd_insert;
#查看 所有触发器
show triggers;
# 查看某个触发器的语句
show create trigger t_name;
事务
什么是事务
事务就是一系列sql语句的组合,是一个整体
为什么要有事务
很多时候一个数据操作,不是一个sql语句就完成的,可能有很多个sql语句,如果部分sql执行成功而部分sql执行失败将导致数据错乱!
例如转账操作,
1.从原有账户减去转账金额
2.给目标账户加上转账金额
若中间突然断电了或系统崩溃了,钱就不翼而飞了!
事务的四个特性:
原子性:
事务是一组不可分割的单位,要么同时成功,要么同时不成功
一致性:
事物前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有的数据都符合所有的约束,则称数据库为完整性的状态);
隔离性:
事物的隔离性是指多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离
持久性:
持久性是指一个事物一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
使用事务
start transaction: 开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库
commit:提交事务,让这个事物中的sql立即执行数据的操作,
rollback:回滚事务,取消这个事物,这个事物不会对数据库中的数据产生任何影响
#开启事务
start transaction
#sql 语句......
#sql 语句......
rollback #回滚操作 即撤销没有提交之前的所有操作
#sql 语句......
commit #提交事务 一旦提交就持久化了
CREATE TABLE `account` (
`name` char(10),
`money` float
);
start transaction;
update account set money = money - 100 where name = "一只穿云箭";
update account set money = money + 100 where name = "千军万马";
commit;
# 何时应该回滚 当一个事务执行过程中出现了异常时
# 何时提交 当事务中所有语句都执行成功时
# 保存点 可以在rollback指定回滚到某一个savepoint ,也就是回滚一部分
start transaction;
update account set money = money - 100 where name = "一只穿云箭";
savepoint a;
update account set money = money - 100 where name = "一只穿云箭";
savepoint b;
update account set money = money - 100 where name = "一只穿云箭";
savepoint c;
select * from account;
# 回滚至某个保存点a,b,c
rollback to 保存点名称(a,b,c)
read committed
修改隔离级别
数据库使用者可以控制数据库工作在哪个级别下,就可与防止不同的隔离性问题
read uncommitted --不做任何隔离,可能脏读,幻读
read committed----可以防止脏读,不能防止不可重复读,和幻读,
Repeatable read --可以防止脏读,不可重复读,不能防止幻读
Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低
修改全局的
set global transaction isolation level read committed;
或者:
set @@tx_isolation = "asasasasas-read";
修改局部
set session transaction isolation level read committed;
@@系统内置变量
@表示用户自定义的变量
# 修改后重新连接服务器生效
存储过程
什么是存储过程
存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的所有sql语句;与python中函数类似;
为什么使用存储过程
回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句中完全解放,mysql可以替代应用程序完成数据相关的的逻辑处理!
那我们以后都是用存储过程不就完了?
三种开发方式对比
1.应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给mysql来完成,通过存储过程(推荐使用)
优点:
应用程序与数据处理完解耦合,一堆复杂的sql被封装成了一个简单的存储过程,考虑到网络环境因素,效率高
应用程序开发者不需要编写sql语句,开发效率高
缺点:
python语法与mysql语法区别巨大,学习成本高
并且各种数据库的语法大不相同,所以移植性非常差
应用程序开发者与BDA的跨部门沟通成本高,造成整体效率低
2.应用程序不仅编写业务逻辑,还需要编写所有的sql语句
优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都有所提高
缺点:sql语句过于复杂,导致开发效率低,且需要考虑sql'优化问题
3.应用程序仅负责业务逻辑,sql语句的编写交给ORM框架,(常用解决方案)
优点:应用程序开发者不需要编写sql语句,开发效率高
缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql
创建存储过程
create procedure pro_name(p_Type p_name data_type)
begin
sql语句......流程控制
end
p_type 参数类型
in 表示输入参数
out 表示输出参数
inout表示既能输入又能输出
p_name 参数名称
data_type 参数类型 可以是mysql支持的数据类型
案例:使用存储过程完成对student表的查询
delimiter //
create procedure p1(in m int,in n int,out res int)
begin
select *from student where chinese > m and chinese < n;
#select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败
set res = m+n;
end//
delimiter ;
set @res = 0;
#调用存储过程
call p1(70,80,@res);
#查看执行结果
select @res;
存储过程中的事务应用
存储过程中支持任何的sql语句包括事务!
案例:模拟转账中发生异常,进行回滚
delimiter //
create PROCEDURE transfer(in aid int,in bid int,in m float,out res int)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
# ERROR
set res = 1;
rollback;
END;
# exit 也可以换成continue 表示发生异常时继续执行
DECLARE exit handler for sqlwarning
BEGIN
# WARNING
set res = 2;
rollback;
END;
START TRANSACTION;
update account set money = money - 1000 where id = 1;
update account set moneys = money - 1000 where id = 1; # moneys字段导致异常
COMMIT;
# SUCCESS
set res = 0; #0代表执行成功
END //
delimiter ;
#在mysql中调用存储过程
set @res=123;
call transfer(1,2,90,@res);
select @res;