PyMySQL

PyMySQL

一、函数及存储过程

1、函数

-- 直接返回查询语句的结果
delimiter $$
create function aa() returns int
begin 
	return (select age from author where a_name='阿宸');
end $$
delimiter ;

select aa();

-- 通过变量进行返回
delimiter $$
create function a1() returns int
BEGIN
	declare num int;
	set num=(select age from author where a_name='小川');
	return num;
end $$
delimiter ;

select a1();

-- 删除函数
drop function a1;

-- 函数参数
delimiter $$
create function a2(str char(5)) returns int
BEGIN
	declare num int;
	set num=(select age from author where a_name=str);
	return num;
end $$
delimiter ;

select a2('阿宸');
select a2('老舍');

2、存储过程

存储过程是一个sql语句的集合

delimiter $$
create procedure b()
begin 
	select * from author where age<30;
	select a_name from author where age>30;
end $$
delimiter ;

call b();

-- in 是接受常量以及变量的
-- out 只能接受变量
-- 在sql中定义一个变量
set @变量名=值
select @变量名

delimiter $$
create procedure b()
begin 
	select * from author where age<30;
	select a_name from author where age>30;
end $$
delimiter ;

call b();


set @name='阿宸';
select @name;

delimiter $$
create procedure c(in num int)
begin 
	select * from author where age<num;
	select a_name from author where age>num;
end $$
delimiter ;

-- 使用常量传参
call c(30);

-- 通过变量传参
set @number=90;
call c(@number);

二、触发器

在数据中设置一段静态的代码 ， 这个代码并不会自己执行也不行手动调用，是当某一个操作触发到这个设置的条件的时候，就会自动的执行。

delimiter $$
create trigger 触发器名称 触发时间 触发条件 on 表名 for each row
begin
	触发事件
end $$
delimiter ;

-- 触发时间（有两个）：before在触发条件之前 ；after 在触发条件之后
-- 触发条件 ：insert、 update、 delete
-- new 新数据  insert  update
-- old 旧数据  update  delete


-- 增加学生信息 ， 对应班级人数自动增加
delimiter $$
create trigger tri after insert on student for each row
begin
	-- 1、获的新增学生对应班级的人数
	declare num int;
	set num=(select class_num from class where class_id=new.class_id);
	-- 对应班级人数+1
	update class set class_num=num+1 where class_id=new.class_id;
end $$
delimiter ;


insert into student values(20,'阿宸',2201,'男');
-- 下面会触发两次
insert into student values
(22,'小川',2202,'男'),
(23,'七零',2203,'男');


-- 修改学生班级，对应班级人数触发操作
delimiter $$
create trigger tri_update after update on student for each row
begin
	-- 1、获的学生对应班级的人数
	declare num_old int;
	declare num_new int;
	-- 获取到班级人数
	set num_new=(select class_num from class where class_id=new.class_id);
	set num_old=(select class_num from class where class_id=old.class_id);
	-- 对应班级人数+1/-1
	update class set class_num=num_new+1 where class_id=new.class_id;
	update class set class_num=num_old-1 where class_id=old.class_id;
end $$
delimiter ;

update student set class_id=2201 where name='七零';

三、视图

视图：视图是一个虚拟表 ， 其内容由查询结果定义。

视图的结果与真实的表结构是一样的，视图最好只做查询操作。

视图可以简化高频，复杂的查询语句操作。

-- 创建视图
create view 视图名称 as select查询语句;

-- 删除视图
drop view 视图名称;
-- 如果视图不存在不会报错
drop view if exists 视图名称;

-- 查看数据库所有视图
show full tables in 数据库名称 where table_type like 'VIEW';

select name ,graden from student;

create view s_name as select name ,graden from student;
create view s_x as select id ,name ,class_id from student;

show full tables in class5 where table_type like 'VIEW';

select * from s_name;

四、索引

索引：数据库的性能调优 ； 提升数据库的工作效率

1、主键索引

2、普通索引

3、唯一索引

使用索引：索引可以在一定情况下加快查询的数据

当表的查询大于修改，删除的操作的，可以创建索引

当表查询操作很少，表的数据很少，不建议创建索引

-- 在创建表的时候创建索引
create table 表名(
	字段名 数据类型，
    字段名 数据类型，
    index 索引名(字段名)，
    unique 索引名(字段名)
);

-- 已有表创建普通索引
create index 索引名 on 表名(字段名);
-- 已有表创建唯一索引
create unique index 索引名 on 表名(字段名);

-- 删除索引
drop index 索引名 on 表名;

-- 查询索引
show keys from 表名;


create table t1(
	id int ,
	index i(id)
);
show keys from t1;

create table t2(
	num int,
	name char(5)
);

create unique index number on t2(num);

show keys from t2;

五、事务、存储引擎

1、事务

事务主要是处理数据的增删改查操作。确保数据在操作过程中是安全。

-- 开启事务
begin;

-- 提交事务
commit;

-- 回滚,回滚到上一条操作语句
rollback;

-- 开启事务
begin;

insert into student values(30, '武则天', 2201 ,'女');

-- 提交数据
commit;

update student set name='阿宸' where id=20;

commit;
update student set name='ac' where id=20;
update student set name='阿宸' where id=20;
rollback;

事务的好处：事务的四大特性：原子性、一致性、隔离性、持久性、简称为ACID

事务隔离级别

• 隔离级别

  • 读未提交：read uncommitted

    事物A和事物B，事物A未提交的数据，事物B可以读取到
    这里读取到的数据叫做“脏数据”
    这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

  • 读已提交：read committed

    事物A和事物B，事物A提交的数据，事物B才能读取到
    这种隔离级别高于读未提交
    换句话说，对方事物提交之后的数据，我当前事物才能读取到
    这种级别可以避免“脏数据”
    这种隔离级别会导致“不可重复读取”

  • 可重复读：repeatable read

    事务A和事务B，事务A提交之后的数据，事务B读取不到
    事务B是可重复读取数据
    这种隔离级别高于读已提交
    MySQL默认级别
    虽然可以达到可重复读取，但是会导致“幻像读”

  • 串行化：serializable

    事务A和事务B，事务A在操作数据库时，事务B只能排队等待
    这种隔离级别很少使用，吞吐量太低，用户体验差
    这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

2、存储引擎

就是MySQL是如何存储数据的

-- 查看存储引擎
show engines;

InnoDB -- 默认的存储引擎，比较平衡的读写效率都是可以的
MyISAM -- 注重查询，对于些的效率不好