1.视图
2.触发器***
在某个时间发生了某个事件时 会自动触发一段sql语句
3.事务*****
4.存储过程*****
5.函数
6.备份与恢复***
mysqldump -u -p (库名 [表名] | --all--databases --databases 库名1 库名2) > 文件路径
恢复
1.mysql -u -p < 文件路径
2.source 文件路径
7.流程控制
----------------
1. 视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，
用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，
这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，
但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，
即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用
100个SQL：
88: v1
select .. from v1
select asd from v1
某个查询语句设置别名，日后方便使用
- 创建
create view 视图名称 as SQL
PS: 虚拟
- 修改
alter view 视图名称 as SQL
- 删除
drop view 视图名称;
2. 触发器
当对某张表做：增删改操作时，可以使用触发器自定义关联行为
insert into tb (....)
-- delimiter //
-- create trigger t1 BEFORE INSERT on student for EACH ROW
-- BEGIN
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- INSERT into teacher(tname) values(NEW.sname);
-- END //
-- delimiter ;
--
-- insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根');
-- NEW,代指新数据
-- OLD,代指老数据
3.事务*****
是逻辑上的一组sql语句 他们要么都成功 要么都失败 今后只要执行sql就会有事务
使用事务
start transaction; --开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库
commit;--提交事务,让这个事物中的sql立即执行数据的操作,
rollback;--回滚事务,取消这个事物,这个事物不会对数据库中的数据产生任何影响
四个特性
原子性:事务是一组不可分割的单位,要么同时成功,要么同时不成功
一致性: 事物前后的数据完整性应该保持一致
隔离性:多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离
持久性:一个事物一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
四个隔离级别
读未提交:read uncommitted --不做任何隔离,可能脏读,幻读
读已提交:read committed----可以防止脏读,不能防止不可重复读,和幻读,
可重复读:Repeatable read --可以防止脏读,不可重复读,不能防止幻读
序列化执行(串行):Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低
修改隔离级别:
select @@tx_isolation;--查询当前级别
set[session|global] transaction isolation level .... ;修改级别
实例:
set global transaction isolation level Repeatable read ;
pymysql事务测试
import pymysql
conn = pymysql.connect(
user="root",
password="root",
database="day48",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor.execute("delete from account where id =1")
# conn.commit()
sql = "update account set money = money - 100 where id = 2"
sq2 = "update account set moneys = money + 100 where id = 3"
try:
cursor.execute(sql)
cursor.execute(sq2)
conn.commit()
print("提交了!")
except:
print("回滚了!")
conn.rollback()
# 把你需要放在同一事务的sql执行 放在try中 最后加上commit
# 如果捕获到异常则执行rollback
4. 函数
def f1(a1,a2):
return a1 + a2
f1()
bin()
内置函数：
执行函数 select CURDATE();
blog
id title ctime
1 asdf 2019-11
2 asdf 2019-11
3 asdf 2019-10
4 asdf 2019-10
select ctime,count(1) from blog group ctime
select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
2019-11 2
2019-10 2
自定义函数(有返回值)：
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int default 0;
set num = i1 + i2;
return(num);
END \\
delimiter ;
SELECT f1(1,100);
4. 存储过程
保存在MySQL上的一个别名 => 一坨SQL语句
别名()
用于替代程序员写SQL语句
方式一：
MySQL: 存储过程
程序：调用存储过程
方式二：
MySQL：。。
程序：SQL语句
方式三：
MySQL：。。
程序：类和对象（SQL语句）
是一堆sql语句的集合,相当于一个py的函数
优点:
应用程序开发者,工作量降低,
提高程序的执行效率 因为网络io减少了
缺点:学习成本高,扩展性 维护性差
部门间沟通成本
pymysql调用存储过程
import pymysql
conn = pymysql.connect(
user="root",
password="root",
database="day48",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc("p1",(2,5,1)) #pymysql 会给参数全都创建对应的变量
# 命名方式 @_p1_0 @_p1_1 @_p1_2
print(cursor.fetchall()) # 如果过程中包含多个查询语句 得到的是第一条查询语句的结果
cursor.execute("select @_p1_2")
print(cursor.fetchone())
1. 简单
create procedure p1()
BEGIN
select * from student;
INSERT into teacher(tname) values("ct");
END
call p1()
cursor.callproc('p1')
2. 传参数（in,out,inout）
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from student where sid > n1;
END //
delimiter ;
call p2(12,2)
cursor.callproc('p2',(12,2))
3. 参数 out
delimiter //
create procedure p3(
in n1 int,
inout n2 int
)
BEGIN
set n2 = 123123;
select * from student where sid > n1;
END //
delimiter ;
set @v1 = 10;
call p2(12,@v1)
select @v1;
set @_p3_0 = 12
ser @_p3_1 = 2
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
=======> 特殊
a. 可传参： in out inout
b. pymysql
cursor.callproc('p3',(12,2))
r1 = cursor.fetchall()
print(r1)
cursor.execute('select @_p3_0,@_p3_1')
r2 = cursor.fetchall()
print(r2)
为什么有结果集又有out伪造的返回值？
delimiter //
create procedure p3(
in n1 int,
out n2 int 用于标识存储过程的执行结果 1,2
)
BEGIN
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
END //
delimiter ;
5. 游标
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int;
declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimter ;
6. 动态执行SQL（防SQL注入）
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL =格式化 tpl + arg
3. 执行SQL语句
set @xo = arg;
PREPARE xxx FROM 'select * from student where sid > ?';
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimter ;
call p7("select * from tb where id > ?",9)
===>
delimiter \\
CREATE PROCEDURE p8 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;
