一.视图
什么是视图:
视图是一张表或多张表的查询结果构成的一张虚拟表
为什么使用视图
当我们在使用多表查询时 我们的sql语句肯恩会非常的复杂,如果每次都编写一遍sql的话
无论是一件麻烦的事情,这时候就可以使用视图来避免多次编写sql的问题
简言之视图可以帮我们节约sql的编写
视图的另一个作业是,可以不同的属兔来展示开放不同数据的访问
例如,同一张工资表,老板可以查看全部,部门主管可以查看该部门所有,员工只能看自己的
一条记录
使用方法
创建视图
CREATE [OR replace] VIEW view_name [(colum_list)] AS select_statement
加上OR REPLACE时如果已经存在相同视图则替换原有视图
column_list指定那些字段要出现在视图中
注意:由于是一张虚拟表,视图中的数据实际上来源于其他其他表,所以在视图中的数据不会
出现在硬盘
查看视图
1.dese view_name;//查看数据结构
2.show create view view_name;//查看 创建语句
修改视图
alter view_name select_statement
删除视图
drop view view_name
案例1:简化多表sql语句
#准备数据 create database db02 charset utf8; use db02 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 vachar(50), addr varchar(100) ); insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江'); #创建视图包含 编号 学生的姓名 和班级 create view stu_v (编号,姓名,班级) as select student.s_id,student.name ,stu_info.class from student,stu_info where student.s_id=stu_info.s_id; # 查看视图中的数据 select *from stu_v;
二.触发器
什么是触发器
触发器是一段与表有关的mysql程序 当这个表再某个时间点发生某件事情是 将会自动
执行相应的触发器程序
何时使用触发器
当我们想要在一个表登记被更新是做一些操作时就可以使用触发器
但是我们完全可以Python中来完成这个事情,因为Python的拓展性更强,语法更简单
创建触发器
语法 CREATE TRIGGER t_name t_time t_event ON table_name FOR EACH ROW begin stmts....... end
案例
有cmd表和错误日志表,需求:在cmd执行失败时自动将自动将信息存储到初五日志表中
#准备数据 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 triggerl 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中插入记录,触发触发器,根据IF的条件决定是否插入错误日志 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;
delimiter用于修改默认的行结束符,由于在触发器中有多条sql与他们需要使用分号
来结束,但是触发器是一个整体,所以我们需要先更换默认的结束符,在触发器编写玩后在
将结束符设置分号
注意:
外键不能触发事件 主表删除了某主键 从表也会相应删除 但是并不会执行触发器 触发器
中不可能使用事务 相同时间点的相同事件的触发器 不能同时存在
删除触发器
语法: drop trigger trigger_name; 案例: drop trigger trigger1;
三.事务
什么是事务
事务是逻辑上的一组操作,要么都成功,要么都失败
为什么需要事务
很多时候一个数据操作,不是一个sql语句就完成的,可能有很多个sql语句,如果部分sql执行
成功而部分sql执行失败将导致数据错乱!
例如转账操作.
1.从原有账户减去转账金额
2.给目标账户加上转账金额
若中间突然断电了或系统崩溃了,前就不翼而飞了!
使用事务
start transaction;开启事务,在这句语句之后的sql将处在同一事务,并不会立即修改数据库
commit;提交事务,让这个事物中的sql立即执行数的操作
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打错了导致执行失败 #在Python中使用事务处理 sql = 'update account set money = money-1000 where id = 1;' sql2 = 'update account set money + 1000 where id = 2;'#money 打错了导致执行失败 try: cursor.execute(sql) cursor.execute(sql2) except: conn.rollback()
注意:事务的回滚的前提是能捕捉到异常,否则无法决定何时回滚,Python中很简单就实现
了,另外mysql中需要使用存储过程才能补获异常!
事务的四个特性:
原子性:
事务是一组不可分割的单位,要么同时成功,要么同时不成功
一致性:
事务前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有
的数据都符合所有的约束,则称数据库为完整性的状态);
隔离性:
事物的隔离性的指多个用户并发访问数据时,一个用户的事务不能被其它用户的事务所干
扰,多个并发事务之间数据要相互隔离
持久性:
持久性是一个事务一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发送故障
也不应该对其有任何影响
事务的用户隔离级别:
数据库使用者可以控制数据库工作在哪个级别下,就可与防止把不同的隔离性问题
read uncommitted 不做任何隔离,可能脏读,幻读
read committed 可以防止脏读,会出现不可重复读,幻读
repeatable read 可以防止脏读,不可重复读,不能防止幻读
serializable 数据库运行在串行化实现,所有问题都没有,就是性能低
修改隔离级别:
select@@tx_isolation;-查询当前级别
set[session|global]transaction isolation level...;修改级别
实例:
set global transaction isolation level repeatable read;
四.存储过程
什么是存储过程
存储过程是一组任意的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 chines > m and chinese < n; set res = 100; end// delimiter; set @res = 0; call p1(70,80,@res); select @res;
需要注意的是,存储过程的out类参数必须是一个变量,不能是值;
在python中调用存储过程
import pymysql #建立连接 conn = pymysql.connect( host="127.0.0.1", user="root" password="admin" database="db02" ) cursor = conn,cursor(pymysql.cursors.dictcursor) #获取游标 cursor = conn.cursor(pymysql.cursors.dictcursor) # 调用存储的过程 cursor.callproc)"p1",(70,80,0)) #提取执行结果是否有结果取决于存储过程中的sql语句 print(cursor.fetchall()) #获取执行状态 cursor.excute("select @_p1_2") print(cursor.fetchone())
此处pymysql会自动将参数都设置一个变量所以可以直接传入一个值,当然值如果作为输出
参数的话,传入什么都无所谓!
删除存储过程
drop procedure 过程名称;
修改存储过程意义不大,不如删除重写
查看存储过程
#当前库所有存储过程名称 select"name"from mysql.proc where db = "db02" and "type"="procedure"; #查看创建语句 show create procedure p1;
存储过程中的事务应用
存储过程中支持任何的sql语句包括事务!
案例:模拟转账中发送异常,进行回滚
delimiter// create procedire p5( out p_return_code tinyint ) begin declare exit handler for sqlexception begin set p_return_code = 1; rollback; end declare exit handler for sqlwarning begin set p_return_code = 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 p_return_code = 0; #0代表执行成功 END // delimiter ; #在mysql中调用存储过程 set @res=123; call p5(@res); select @res;
总结:抛开沟通成本,学习成本,存储过程无疑是效率最高的处理方式,面试会问,一些公司也有一些现存的存储过程,重点掌握!
五.数据备份与恢复
mysqldump -u -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
使用 mysql 进行恢复
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
六.流程控制
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
重点内容: 存储过程
七 事务
什么是事务
逻辑上的一组操作 要么都成功 要么都失败
如何使用
start transaction 开启事务 mysql 默认一条sql就是一个事务 pymysql默认开启事务
rollback 当其中一条失败 就整体回滚
commit 提交事务
特性
原子性 一致性(完整性一致 约束都是正确的) 隔离性 持久性
并发带来的问题
幻读 脏读 不可重复读
隔离级别:
read uncommintted 读未提交
read committed 读已提交 脏读 不能防止幻读 和不可重复读
repeatable read 可重复读
串行化 安全 效率低
存储过程
什么是存储过程 一系列sql语句 类似于python的函数
可以包含 热河支持语句
有什么用?应用程序可以直接调用存储过程 降低网络传输 简化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
#恢复
use db1;
source /root/db1.sql
流程控制
九.mysql索引
什么是索引
在关系数据库中,索引是一种单独的.物理的对数据库表中一类或多列的值进行排序的一种存储结构
也称为 key
索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容.
为什么需要索引
索引的就是用帮我们加快查询速度的
小白的误区
既然索引如此神奇,那以后只要熟读满了就加索引,
不对!
索引不是越多越好,有了索引后还要考虑索引是否命中
加上索引后写入数据速度回降低
索引的实现原理
就像新华字典,将字按拼音音节顺序排序,提高人的查字速度,索引也是一样
数据库中的索引,实现思路与字典是一致的,需要一个独立的存储结构,专门存储索引数据
本质上索引是通过不断的缩小查询范围来提高查询效率
索引数据结构剖析
在字典的例子中我们知道了,索引是独立于真实数据的一个存储结构,这个结构到底是什么样的?
索引最终的目的是要尽可能降低io次数,减少查找的次数,以最少的io找到需要的数据,此时B+树闪亮登场
光有数据结构还不行,还需要有对应的算法做支持,就是二分查找法
有了B+数据结构后查找数据的方式就不再是逐个的对比了,而是通过二分查找法来查找(流程演示)
另外,其实大多数文件系统都是使用B+是来完成的!
应该选择数据量小的字段作为索引
最左匹配原则,
当b+树的数据项是符合的数据结构,比如(name,age,sex)的时候,从左边开始匹配
聚集索引
叶子节点保存的就是完整的一行记录,如果设置了主键,主键就作为聚集索引,
如果没有主键,则找第一个NOT NULL且QUNIQUE的列作为聚集索引,
如果也没有这样的列,innoDB会在内心自动产生一个聚集索引,它是自增的
辅助索引
除了聚集索引之外的索引都是辅助索引或第二索引,包括 foreign key 与 unique
辅助索引的特点:
其叶子节点保存的是索引数据与所在行的主键值,innoDB用这个 主键值来从聚集索引中
搜查找数据