数据库6.高级

1.视图：
什么是视图：
视图是有一张或多张表的查询结果构成的一张虚拟表
create view dept_view as select *from dept;
为什么使用视图：
使用权限控制 只能开放某一列的数据访问 对于目前的查看工资而言是没有意义的
我们使用虎能够查看整条语句
1.多次使用时，可以简化书写
2.隔离数据，可以修改原表中的数据；但是我们一般不会这么做。
总结：mysql可以分担程序中的部分逻辑，但这样一来后续的维护变得麻烦；如果需要改表结构，
那意味着视图也需要相应修改，没有直接在sql语句中修改方便
查询条件下的数据：
slect *from emp inner join dept on emp.d_id = dept.id;
建立查询虚拟表（在sql中存放的是语句，修改源代码会同步视图中；修改虚拟表时，原表同步）：
create view dept_view as select *from salarys（原表） where dept="财务"
2.触发器：
什么是触发器：
触发器是一段与表有关的mysql程序 当这个表在某个时间点发生了某种事件时 将会自动执行相
应的触发器程序何时使用触发器。
触发器中无法使用事物。
准备数据：
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
);
举例建立一个触发器
create trigger cmd_insert_triger after insert on cmd for each row
begin
if new.success = "no" then
insert into errlog values(null,new.cmd.sub_time);#直接;默认结束 单语句未完成 需更改；意义
end if;
end//
delimiter // # delimiter先重新设置一个 行结束符号
插入数据：
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');
查看错误日志表中的记录是否有自动插入：
select *from errlog;
注意：
1：外键不能触发事件 主表删除了某个主键 从表也会相应删除 但是并不会执行触发器
2：触发器中不能使用事物时间点 即相同时间，相同事件的触发器 不能同时存在
触发器删除语法:
drop trigger trigger_name;
3.事物：
什么是事物：
事务是逻辑上的一组操作,要么都成功,要么都失败。
什么时候使用事物
当需要保证一堆sql 要么都成功要么都失败时。
事物的使用：
start transaction; # 开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库
commit; # 提交事务,让这个事物中的sql立即执行数据的操作
rollback; # 回滚事务,取消这个事物,这个事物不会对数据库中的数据产生任何影响转账过程中发生
异常
注意：#开启事物后只要执行commit 或者 rollback 事物就结束了。
事物的特点：
1. 原子性:事务是一组不可分割的单位,要么同时成功,要么同时不成功；
2. 一致性:事物前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有的
数据都符合所有的约束,则称数据库为完整性的状态);
3. 隔离性: 事物的隔离性是指多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,
多个并发事务之间数据要相互隔离
4. 持久性:持久性是指一个事物一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障
也不应该对其有任何影响
转账举例：
start transaction;
update account set money = money -1000 where name ="刘大牛";
rollback;
#准备数据
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values(1,'赵大儿子',1000);
insert into account values(2,'刘大牛',1000);
insert into account values(3,'猪头三',1000);
insert into account values(4,'王进',1000);
insert into account values(5,'黄卉',1000);
​
# 赵大儿子刘大牛佳转账1000块
# 未使用事务
update account set money = money - 1000 where id = 1;
update account set moneys = money - 1000 where id = 1; # money打错了导致执行失败
​
在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")
sql1 = 'update account set money = money - 1000 where id = 1;'
sql2 = 'update account set moneys = money + 1000 where id = 2;' # money打错了导致执行失败
try:
cursor.execute(sql)
cursor.execute(sql2)
conn.commit()
except:
conn.rollback()
把你需要放在同意事务的sql执行 放在try中 最后加上commit
如果捕获到异常则执行rollback
在mysql客户端中，如果遇到了sql语句
什么是脏读？
读取一个事物未提交的数据
什么是不可重复度？
一个事物在查询，一个在updata 第一次查 和第二次查有可能数据不一致
什么是幻读
一个事物在查 另一个insert 或 delete 第一次查 和第二次查有可能数据不一致
事物的用户隔离：
read uncommitted：不做任何隔离,可能会脏读,幻读
read committed：可以防止脏读,但不能防止不可重复读和幻读
Repeatable read：可以防止脏读和不可重复读，但无法防止幻读
Serializable：数据库运行实现串行化，可以避免以上所有数据问题，但会降低性能
修改隔离级别：
select @@tx_isolation;--查询当前级别
set[session|global] transaction isolation level .... ;修改级别
实例:
set global transaction isolation level Repeatable read ;
4.存储过程：
什么是存储过程？
存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的
所有sql语句;与python中函数类似;
为什么使用存储过程？
回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过
程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句
中完全解放,mysql可以替代应用程序完成数据相关的的逻辑处理!
三种开发方式对比：
1.数据库操作全都放在mysql中，应用程序不需要编写sql语句 直接调用存储过程
优点：1.应用程序开发者工作量降低
2.提高程序的执行效率 因为网络io减少了
1.查询用户名是否存在的sql
2.根据查询结果 在发送一条注册的sql语句
一共经历两次网络访问
换成存储过程的话 只需要一次网络访问
缺点：学习成本高，扩展性，维护性差；部门间沟通成本高。
2.应用程序完全自己编写sql语句
优点：扩展性，维护性高；部门间沟通成本低。
缺点：工作量大，sql语句的优化需要应用程序开发发完成
今后常用：
3.使用ORM（对象关系映射）框架，可以直接使用面向对象的方式完成对数据库的CRUD；
简单来讲就是帮你封装了sql语句的生成。
优点：不需要写sql语句，开发效率高。不需要考虑sql优化问题。
缺点：执行效率略低于第二种方式 完全可以忽略不记。
删除存储过程：
drop procedure p1;
在mysql中创建一个存储过程：
create table stu (id int,name char(10));          
insert into stu values(1,'egon'),
(2,'alex'),
(3,'kive'),
(4,'kive1'),
(5,'kive2'),
(6,'kive3'),
(7,'aaci');
set @res=11;
delimiter//
create procedure p1(in n int,in m int,out res int)
begin
select *from stu where id>n and id<m;
set res=0; 
end//
delimiter ;
select @res;
call p1(2,5,@res);
select @res;#查看
在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())
注：在pymython中是自动实现事务的
5.函数：
自定义函数语法：
CREATE FUNCTION f_name(paramters)
returns dataType
return value;
例子：
create function addf(a int,b int)
returens int
return a+b;
# 执行函数
select addf(1,2)
注意：在定义函数的时候 只能写mysql的逻辑语言 但不能出现sql语句
mysql 有自己的编程语言
sql 结构化语句
6.数据的备份与恢复
使用mysqldump -uroot -p密码 db_name [table_name,,,]>fileName.sql
#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
​
#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
​
#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql
1.退出数据库后
mysql -u -p < filename.sql;
2.不用退出数据库
​ 2.1 创建空数据库
​ 2.2选择数据库
​ 2.3然后使用source filename; 来进行还原
use db1;
source /root/db1.sql
数据库迁移：
务必保证在相同版本之间迁移：
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456
7.流程控制
if语句的使用：
if 条件 then 语句; end if;
第二种 if elseif if 条件 then 语句1; elseif 条件 then 语句2; else 语句3; end if;
案例:编写过程 实现 输入一个整数type 范围 1 - 2 输出 type=1 or type=2 or type=other;
create procedure showType(in type int,out result char(20))
begin
if type = 1 then
set result = "type = 1";
elseif type = 2 then
set result = "type = 2";
else
set result = "type = other";
end if;
end
CASE 语句：
大体意思与Swtich一样的 你给我一个值 我对它进行选择 然后执行匹配上的语句 语法:
create procedure caseTest(in type int)
begin
CASE type
when 1 then select "type = 1";
when 2 then select "type = 2";
else select "type = other";
end case;
end
定义变量：
declare 变量名 类型 default 值; 例如: declare i int default 0;
WHILE循环
循环输出10次hello mysql
create procedure showHello()
begin
declare i int default 0;
while i < 10 do
select "hello mysql";
set i = i + 1;
end while;
end
LOOP循环的：
没有条件 需要自己定义结束语句 语法:
输出十次hello mysql;
create procedure showloop()
begin
declare i int default 0;
aloop: LOOP
select "hello loop";
set i = i + 1;
if i > 9 then leave aloop;
end if;
end LOOP aloop;
end
REPEAT循环:
#类似do while
#输出10次hello repeat
create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end
​
#输出0-100之间的奇数
create procedure showjishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end