mysql视图、触发事务、存储过程

视图

视图是一个虚拟表(非真实存在的),其本质就是根据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。

使用存储过程的优点:

  1. 用于替代程序写的SQL语句,实现程序与SQL解耦
  2. 基于网络传输,用存储过程只需要传输封装好的别名,而直接传输数据量大,网络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;
上一篇:用ethtool 命令解决Linux 网卡丢包【转】


下一篇:JAVA-堆区,栈区,方法区。