实训总结-mysql-day05
事务：MySQL (innodb存储引擎)
定义：事务指的是一组逻辑操作单元。也就是说，事务指的是一组sql语句。这一组sql语句作为整体来进行执行。只有这组sql语句全部执行成功，事务才算执行成功。要有一个sql语句执行失败，事务也就执行失败。
特点(ACID)
原子性(atomicity)：事务是一个整体，不可分割的工作单位，事务中的sql要么全部执行成功。要么都不执行。
一致性 (consistency)：事务的执行是让数据库从一个一致性状态装换为另一个一致性状态。
隔离性(lsolation)：事务之间的执行是被隔离的。即事务执行时是相互独立，互不干扰的。
持久性(durability)：事务一旦提交，它对数据库的改变是永久性的。
隔离级别：(并发与并行：并发是在同一时间段内运行的任务。并行是同一时刻运行的任务)
并发问题：(对于在同一时间段内运行的多个事务，当这些事务访问数据库相同数据时。如果没有采取必要的隔离机制，会产生各种并发问题。)
并发问题 影响 脏读 读取到未被提交的数据 不可重复读(针对表中记录) 前后时间内读取的数据表字段不同 幻读(针对整张表) 前后读取数据库表，表中记录数不同 脏读：读取到未被提交的数据(两个事务t1,t2。t2对数据进行修改但未commit,此时t1对数据进行读取，读取到了t2未提交的数据。此问题就是脏读。)
不可重复读：(针对于表中的记录,主要是update,delete操作)
两次读取相同字段结果不同(两个事务t1,t2。t1先读取了一个字段，然后t2更新并提交该字段的修改。此时t1在次进行读取数据，数据结果与第一次读取不同。对于t1产生了不可重复读问题,不可重复读指的是数据库表中的一行记录)
幻读:(针对于整张表,主要是insert操作)
两次读取数据库表结果不同(两个事务t1,t2,t1先读取了数据库表中字段。然后t2向数据库表中添加了一些新的行记录,此时t1在对数据库进行读取，就会多出几条记录。t1产生了幻读问题)为了解决事务并发出现的上述问题。数据库通过设置隔离级别和对数据加锁来解决问题。通过设置隔离级别，可以降低不同事务的并发干扰。来保证数据库的一致性。
数据库锁机制(两段锁机制，事务加锁阶段和解锁阶段):
锁分为:
锁分类 作用 性能 表锁 对整张表加锁，加入表锁后，除了本事务，其它事务不可对表进行操作 并发效率低(不常用)DDL使用 行锁 针对表中一行记录加锁 并发性高 表锁:表锁相当于对整张表加锁。并发性极低。可分为读锁和写锁。一般是在DDL语句时使用
通过表锁可以解决上述并发问题，但并发性极低，基本没有。相当于访问数据时按照队列(FIFO)访问
行锁:对数据表中的一行记录加锁。DML语句。锁住有限数据。并发能力强。一般用行锁处理并发事务。
锁类型 限制 使用场景 共享锁(share) s锁 加了共享锁还可以在加共享锁 在读取数据时加共享锁 排它锁(exclude) x锁 加了排他锁不可以在加其他任何类型的锁 在对数据进行更新，删除，删除时加排它锁 共享锁(s share 锁): 共享锁被称为读锁。在读取数据时加共享锁。加了共享锁还可以在加其他共享锁 不能在加排它锁。 排它锁(x exclude 锁): 排它锁被称为写锁。添加了排它锁。不可以加其他锁。其他锁进入等待阶段通过行锁可以解决并发中针对数据一条记录出现脏读,不可重复读问题。不能解决幻读问题。
数据库的隔离级别:
隔离类型 隔离解释 解决问题 read uncommited(读未提交) 可以读取未被提交的数据 任何并发问题都不能解决 read commited(读以提交) 读取已经提交的数据 解决脏读问题，不能解决不可重复读和幻读问题 repeatableread(可重复读) 确保事务执行时间段内前后读取数据相同 解决脏读，不可重复读问题。不能解决幻读问题。mysql默认隔离级别 serializable(串行化) 事务在对数据读取时，任何对数据表和表数据修改、删除、添加操作都不支持，相当于访问表时构建队列(FIFO)。 解决脏读，不可重复读，幻读问题。 read uncommitted(读未提交)事务可以读取未被其他事务提交的修改:脏读,不可重复读,幻读。相当于对数据
库没有进行任何处理。没有添加任何锁。
read commited(读以提交)解决了脏读问题。不能解决不可重复度，幻读问题。相当于只能读取已经提交的数据。读取数据不加锁。对数据写入，修改，删除进行加锁
repeatable read(可重复读)确保事务可以多次读取相同字段值。解决了脏读，不可重复读问题。不能解决幻读
问题。相当于对一条记录进行读写操作时，其他事务不能对这条数据进行修改删除。相当于一把共享锁s锁。行级共享锁。
serializable(串行化)解决了脏读，不可重复度，幻读问题，相当于在使用数据库数据时加了表锁。此时除了对表读取的事务可以进行。其它对表进行插入，更新，删除的事务全加入队列(FIFO)中。并发效率极低。相当于对整张表进行了关于插入，更新，删除数据操作加锁。表级锁，读加共享锁，写加表级排他锁oracle 支持2中事务隔离级别read commit,serilizable。默认支持read commit级别的事务隔离。
mysql 支持上述四种事务隔离级别，默认支持 reperatable read级别的事务隔离。
注意:MySQL一条基本的sql语句就是一条事务。默认情况下事务自动提交。
-- 查看事务隔离级别
select lower(@@tx_isolation);
set global transaction isolation level read uncommitted;
-- 事务编写
desc admin;
set autocommit=0; # 取消事务自动提交
start transaction; # 开启事务
insert into admin(username,password) values("Transaction","transaction");
# rollback; # 事务回滚
commit; # 事务提交，事务一旦提交对数据库表的改变便是持久性的,不可逆。
set autocommit=0;
start transaction;
insert into admin(username,password) values("test","test");
commit;
set autocommit=0;
start transaction;
delete from admin where username="test";
commit; # rollback;
select * from admin;视图
视图：视图是一张虚拟表，视图中并不存储真实的数据，真实的数据依然保存在基本表中，视图数据是在使用数据时动态生成的。视图存储的是sql的查询逻辑。通过sql查询逻辑得到数据。
应用场景：经常需要重复查询的sql。也就是经常访问的数据。 查询逻辑比较复杂的sql可以保存为视图。
视图的特点：使用简单，数据安全，独立(没有真实访问基本表)
视图更新：视图由于存储的是sql查询逻辑，所以一般情况下,基本表数据更新，查询视图时数据也会获得更新。
特殊情况：包含分组查询结构的视图不会自动更新。常量视图不会自动更新。包含join,子查询，where 条件用到虚拟表不会自动更新。
因为视图在数据更新上局限性较大，所以通常可以使用存储过程来进行替代。
/**
视图：(5.0.1开始,视图的查询与使用与基本表类似) 视图是一张虚拟表。表中存储的是查询逻辑结构,视图中的
行和列来自查询结构的定义,真实的数据依然保存在基本表。视图中的数据可以
来自多张表。
应用场景：多个地方用到相同的查询(频繁使用的sql->结果集)
进行查询sql十分复杂(省化编写sql的成本)
通过视图可以提高开发效率，简化频繁的sql编写
视图更新性:
视图的可更新性和视图中的查询定义有关系。以下类型的视图不可更新的。
(1): 包含以下关键字的sql语句：分组函数，distinct ,group by ,having ,union ,union all
(2): 常量视图
(3): 包含子查询
(4)： from 一个不可更新的视图
(5)：where 子句查询中引用了from子句中的表。
*/
select b.`name`,b.sex,b.borndate,b.phone,o.boyName from beauty as b left join boys as o on b.boyfriend_id=o.id where boyName is not null order by length(b.`name`) asc; #asc 升序
-- 创建视图 create [or replace] view viewname as 查询结构(注意点：视图相当于虚拟保存了查询结果所以采用 as 关键字来连接视图)
create view testview as select * from beauty;
create VIEW view1 as select b.`name`,b.sex,b.borndate,b.phone,o.boyName from beauty as b left join boys as o on b.boyfriend_id=o.id where boyName is not null order by length(b.`name`) asc;
-- 修改视图 alter view 视图名 as 查询结构
alter view testview as select * from boys;
-- 查看存在的视图(视图当表来用)
show tables; # 因为视图相当于虚拟表，所以查看有哪些视图归结到查看有哪些表
-- 视图查询 (因为视图与基本表类似，所以可以通过查询表的方式查询视图)
select * from testview;
select * from view1 where name like "%A%";
-- 删除视图 (与删除基本表类似)
drop view if exists testview;存储过程与自定义函数
定义：存储过程指的是事先定义好的一段sql语句的集合，在使用时可以直接调用存储过程。
使用好处：
减少应用开发人员的编写sql语句的工作
减少了数据(sql)在数据库和应用服务器之间的传输
提高了数据处理效率自定函数与存储过程相似，只需要把存储过程的关键字由procedure 改为 function
参数类型
参数类型 参数作用 使用方法 in 向存储过程或函数中传递参数 in 参数名 参数类型 out 用来接收存储过程与函数的返回值 out 参数名 参数类型 inout 可以用来向存储过程或函数传递参数，也可用来接收返回值 inout 参数名 参数类型
# 创建无参的存储过程
delimiter $$ -- 声明存储过程创建的起始符和结束符号，防止与 ; 结束符冲突 在存储过程的定义中
create procedure mypro1()
begin
select * from beauty;
end
$$
# 调用存储过程
call mypro1();
# 根据女神名称查询男友
delimiter $$
create procedure mypro2(in bname varchar(20))
begin
select bo.* from boys bo
join beauty b on bo.id=b.boyfriend_id
where b.name=bname;
end $$
call mypro2("Angelababy");
# 根据账号密码判断账户是否存在
delimiter $$
create procedure mypro3(in username varchar(30),in password varchar(30))
begin
select count(*) from admin as a where a.username=username and a.`password`=password;
end $$
call mypro3("john",8888);
# 创建out类型的存储过程
delimiter $$
create procedure mypro4(in username varchar(30),in password varchar(20),out result int)
begin
select count(*) into result from admin as a where a.username=username and a.`password`=password;
end $$
-- 定义参数
set @result:=0;
-- 修改参数值
set result:=0;
call mypro4("john",8888,@result); -- call用来调用存储过程；
-- 获取查询结果()
select @result;
# 创建多out 类型的存储过程
delimiter $$ -- 定义结束符
create procedure mypro5(in a int,in b int,out c int,out d int)
begin
set c:=a*2;
set d:=b*2;
end
$$
call mypro5(1,2,@c,@d); -- call 参数用来调用储存过程
select @c,@d;
# 创建inout参数类型的存储过程
delimiter $$ -- 声明结束符
create procedure mypro6(inout age int)
begin -- 开始存储过程
set age=age*2; -- sql语句集合
end $$ -- 存储过程结束
-- 易错点存储过程调用
set @age:=2;
call mypro6(@age);
select @age;
# 展示存在的存储过程 show create procedure 存储过程名。
show create procedure mypro1;
# 删除存储过程 drop procedure 存储过程名。
drop procedure if exists mypro1;
# 创建函数
delimiter $
create function my_f1(id int) returns varchar(20) -- 函数定义声明返回值不声明不返回参数
begin
declare bname varchar(20);
select boys.`boyname` into bname
from beauty ,boys
where beauty.`boyfriend_id`=boys.`id`
and beauty.`id`=id;
return bname;
end$
select my_f1(3); -- 调用函数存储过程与函数对比
名称 功能 关键字 返回值 调用方法 存储过程(procedure) 提供对外执行的一组sql,提高sql语句的效率和重用性 procedure 可以有多个返回值 call 存储过程(参数) 函数(function) 提高对外执行的一组sql,提高sql语句的效率和重用性 function 只能有一个返回值，需要在定义时通过return 类型声明。函数结束时通过return 返回。 select 函数名(参数)
流程控制结构
变量定义，分支结构IF，循环结构
# 变量定义与声明
-- 变量
set @a:=1; # 定义变量
select @a; # 查询变量
-- case 结构
CASE
WHEN score>90 THEN SELECT 'A';
WHEN score>80 THEN SELECT 'B';
WHEN score>60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
-- ifelse 结构
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
ELSEIF 条件3 THEN 语句3;
ELSE 语句n;
END IF ;
-- 循环结构
create procedure loop_pro6(in mycount int) # repeat循环结构与do-while 类似，至少执行一次
begin
declare i int default 1;
a:repeat
select i;
if i=2 then leave a;
end if;
set i=i+1;
until i>mycount end repeat a;
end $
call loop_pro6(10)
create procedure loop_pro1(in mycount int)
begin
declare i int default 1;
while i<=mycount do
select i;
set i=i+1;
end while;
end $
create procedure loop_pro3(in mycount int,out result int)
begin
declare i int default 1;
declare sum int default 0;
a:while i<=mycount do
set sum = sum+i;
if sum>20 then leave a;
end if;
set i=i+1;
end while a;
select sum into result;
end $
call loop_pro3(10,@r) $
select @r $
create procedure loop_pro2(in mycount int)
begin
declare i int default 1;
a:while i<=mycount do
select i;
set i=i+1;
end while a;
end $
call lopp_pro2(10) $数据库备份与恢复
数据库的备份与恢复可通过数据库可视化工具实现，通过数据库可视化工具实现数据库备份操作比较方便，可以自行百度。
未来需要补充学习的(索引机制,分库分表，主从复制,数据库权限控制,sql查询优化)
通过一周的实训学习，学习到了MySQL基础核心概念。重点需要把握MySQL的基本概念和基础sql语句,sql语言的分类(DDL,DQL,DML,DCL).各种查询操作。事务(面试必问)，视图创建，编写存储过程和定义函数。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
