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 -- 注重查询,对于些的效率不好