mysql高级操作部分

1.视图

视图是由一张表或多张表的查询结果构成的一张虚拟表。

作用：1.可以帮我们节省sql语句的编写。

​ 2.可以以不同的视图展示不同的数据访问

使用方法：

语法：
create [or replace] view view_name as  查询语句;
or replace 如果视图已经存在就替换里面的查询语句;

#修改视图
alter view view_name as 新的语句;
#删除视图
drop view view_name;
#查看
desc view_name;
show create view view_name;

案例1：
#限制可以查看的记录
create table salarys(id int,name char(10),money float);
insert into salarys values (1,'张三丰'，5000),(2,'张无忌',3000);

#创建张无忌视图
create view zwj_view as select * from salarys where name='张无忌';
#查看张无忌视图
select * from zwj_view;

案例2：
create table student(s_id int(3),name varchar(10),math float,chinese float);
insert into student values(1,'tom',80.40),(2,'jack',60,70),(3,'rose',90,89);

create table stu_info(s_id int,class varchar(10),addr varchar(50));
insert into stu_info values (1,'三班','湖南'),(2,'二班','安徽'),(3,'三班','湖北');
#查询班级和学员的对应关系做一个视图，方便后续查询
create view class_info as select student.s_id,name,class from student join stu_info on student.s_id=stu_info.s_id;

select * from class_info;


#原表数据发生变化视图的数据也会跟着变化，视图仅用于查询，尽量不要修改

2 .触发器

触发器是一段与某个表相关的sql语句，会在某个时间点满足某个条件后自动触发执行，其中有两个关键因素：

• 时间点：事件发生前 before，事件发生后 after
• 事件：update、delete、insert

触发器中包含两个对象：

• old ：update，delete中可用
• new：update，insert中可用

触发器的作用：当表的数据被修改时，自动记录一些数据，执行一些 sql 语句

#语法：
create trigger t_name t_time t_event on table_name for each row
begin
#sql语句。。。
end

案例：

#准备数据
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
);

#需求： 当插入cmd表 的时候 如果执行状态时失败的 那么将信息插入到errlog中


#将结束符设置位|
delimiter |
create trigger cmd_insert after insert on cmd for each row
begin
if new.sucess = 'no' then
	insert into errlog values(null,new.cmd,new.sub_time);
end if;
#还原之前的结束符
delimiter ;

#创建触发器叫 cmd_insert
#触发器会在插入数据到cmd表后执行
#当插入的记录的sucess位no时，自动插入记录到errlog中



#删除触发器
drop trigger cmd_insert;

#查看所有触发器
show trigger;

#查看某个触发器的语句
show create trigger t_name;

3.事务（重要）

定义：事物就是一系列的sql语句的组合，是一个整体。

事务的特点：

• 原子性：一个事务中的sql语句是一个整体，不能拆分，要么都执行成功，要么都执行失败。
• 一致性：事务前后的数据完整性应该保持一致，表的关联关系一定是正确的，不会发生数据错乱
• 隔离性：多个事务并发访问数据时，一个用户的事务不会被其他用户的事务所干扰，多个并发之间的数据要相互隔离。
• 持久性：事务一旦提交，对数据的改变是永久性的，无法恢复。

语法：

#开启事务
start transaction
#sql语句。。。。
rollback;#回滚操作，撤销没有提交之前的所有操作

commit #提交事务，一旦提交就不能撤销


案例：
create table acount(name char(10),money float);
isnert into acount values ('你',1000),('我',500);

start transaction;
update acount set money = money - 100 where name='你';
update acount set money = money + 100 where name ='我';
commit;

#何时回滚：当一个事务执行过程中出现异常时
#何时提交：当事务中所有语句都执行成功时
#保存点：可以在rollback 指定回滚到某一个save point

start transaction;
update acount set money = money - 100 where name='你';
savepoint a;
update acount set money = money - 100 where name = '你';
savepoint b;

rollback to 保存点名称 #回滚到某一个保存点

修改隔离级别

read uncommitted --不做任何隔离,可能脏读,幻读
read committed----可以防止脏读,不能防止不可重复读,和幻读,
Repeatable read --可以防止脏读,不可重复读,不能防止幻读
Serializable--数据库运行在串行化实现,所有问题都没有,就是性能低


修改全局的  
 set global transaction isolation level read committed;
 或者:
 set @@tx_isolation = "asasasasas-read";
 修改局部
 set session transaction isolation level read committed;
 

 @@系统内置变量
 @表示用户自定义的变量

4.存储过程（需掌握）

定义：存储过程是一组任意的sql语句集合，存储在mysql中，调用存储过程将会执行其包含色所有sql语句，与python中的函数类似。其中可以包含任意的sql语句，逻辑处理，事务处理。

三种数据处理方式：

1.应用程序只负责业务逻辑，所有的与数据相关的逻辑封装到MySQL中

优点：应用程序要处理的事情变少了，可以减少网络传输

缺点：增加了人力成本，沟通成本，降低整体开发效率

2.应用程序既要处理业务逻辑还要自己编写sql语句

优点：降低了沟通成本，人力成本

缺点：网络传输增加，sql语句编写非常繁琐，容易出错

3.通过ORM框架对象关系映射自动生成sql语句并执行

优点：不需要再编写SQL语句，明显提升效率和开发速度

缺点：不够灵活，应用程序开发者和数据库完全隔离了，可能导致仅关注上层开发而不清楚底层原理

语法：
create procedure p_name(p_type p_name p_date_type)
begin
sql.....
end

p_type 参数的类型 in输入 out输出  inout即可输入又可以输出
p_name 参数的名字
p_date_type 参数的数据类型 如 int float
##out参数必须是一个变量 不能是一个值

案例：
delimiter |
create procedure add1(in a float,in b float,out c float)
begin
set c = a + b;
end |
delimiter ;

#调用
set @res=0;
call add1(100,10,@res);

#删除
drop procedure 名称;

#查看
show create procedure 名称;

#查看某一个库下的所有过程
select name from mysql.proc where db='库名' and type='PROCEDURE';




delimiter |
create procedure transfer(in aid int,in bid int,in m float,out res int)
begin
	declare exit handler for sqlexception
	begin
	#异常处理代码
		set res=99;
		rollback;
	end;
	
	start transaction;
	update acount set money = money - m where id = aid;
	update acount set money = money + m where id = bid;
	commit;
	set res = 1;
end |
delimiter ;

5. 函数

自定义函数

create function func_name(参数 类型)
函数体
returns 返回值的类型
return 返回值


delimiter |
create function add2(a int,b int)
return int
return a+b |
delimiter ;

#查看创建语句
show create function name;

#查看所有函数的状态
show function status;

#查看某个库下的所有函数
select name from mysql.proc where db='库名称' and type = 'FUNCTION'；

#删除
drop function name;

6.备份与恢复

#备份
mysqldump.exe
mysqldump -u用户名 -p密码 数据库 表名1，表名2，。。。。> 文件路径
#第一个表示数据库 后面全是表名
mysqldump -uroot -p day40 student >


#备份多个数据库
mysqldump -uroot -p --databases day40 day41 > xxxx.sql
#指定 --database 后导出的文件包含创建库的语句 ，上面的方式不包含

#备份所有数据库
mysqldump -uroot -p --all-databases > all.sql


#自动备份
linux crontab 指令可以定时执行某一个指令


#恢复数据
没有登陆mysql
mysql < 文件的路径

已经登陆了mysql
source 文件路径

注意：如果导出的sql中没有包含选择数据库的语句，需要手动加上

7.流程控制

delimiter | 
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|
delimiter ;

正则匹配

语法：
select * from table where 字段名 regexp '表达式';

create table info(name char(20));
insert into  info values("jack sbaro"),("jack rose"),("jerry sbaro"),("sbaro jerry"),("jerry");

# 注意: 不能使用类似 \w 这样的符号   需要找其他符号来代替  

8.用户管理

创建mysql账户

create user 用户名@主机地址 identified by '密码';
#操作用户只能由root账户进行

#删除  将同时删除所有权限
drop user 用户名@主机地址;

权限管理

#涉及到的表
user 与用户信息相关
db  用户的数据库权限信息
tables_priv 用户的表权限
columns_priv  用户的字段权限

语法：

#  all 表示所有字段的增删改查   *.*表示所有的表和所有的库
grant all on *.* to 用户名@主机地址 identified by '密码';
#如果用户不存在则自动创建用户
grant all on *.* to jack@localhost identified by '123';

#控制只能访问某个库下所有的表
grant all on day40.* jack@localhost identified by '123';

#控制只能访问某个库下的某张表
grant all  on day42.table1  to rose2@localhost  identified by "123";

#只能访问某个库下的某个表的某些字段
grant select(name),update(name)  on day42.table1  to rose2@localhost  identified by "123";




#收回权限
revoke all on *.* from 用户名@主机地址;
#刷新权限
flush privileges;

#with grant option 表示可以将他拥有的权限授予其他用户
grant all on *.* to root1@localhost identified by '123' with grant option;


#授予某个用户可以在任意主机上登陆
grant all on *.* to jack@'%' identified by '123';
grant all on *.* to jack@localhost identified by '123'

9.pymsql的基本使用

pymysql是一个第三方模块，帮我们封装了建立连接，用户认证，sql的执行以及结果的获取。

基本使用：

import mysql

# 1.连接服务器，获取连接对象
conn=pymysql.connect(
	host='127.0.0.1',
    port=3306,
    user='root',
    password='密码',
    database='库名'
)

# 2.通过连接拿到游标对象  默认的游标返回的是元组类型，不方便使用，需要更换字典类型的游标
course=conn.cursor(pymysql.cursors.DictCursor)

# 3.执行sql语句
sql='select * from table_name'
res=conn.execute(sql)  #返回的是查询结果的数量

# 4.提取结果
print(res)
print(conn.fetchall())

# 5.关闭连接
conn.close()
cursor.close()




# 移动光标 参数1位移动的位置   mode 指定 相对或绝对
# c.scroll(1,mode="absolute")

# print(c.fetchall())

# print(c.fetchmany(1))
print(c.fetchone())
print(c.fetchone())