mysql光标如何指向上一级_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())