目录
SQL优化
插入数据
1、批量插入
insert student_info values(1,'李四',1789090,'189@136.com','陕西咸阳'),(1,'李四',1789090,'189@136.com','陕西咸阳');每一次insert都要和数据库建立连接，可以批量插入，减少连接次数
2、手动事务提交
mysql默认自动提交，每一条insert语句的执行，都会涉及到事务的开启和关闭，可以设置手动提交，减少事务开启关闭次数
3、主键顺序插入
按照主键顺序插入
4、涉及大批量的数据，可以使用mysql的load指令
(将文件内的各种记录加载进入数据库）
打开命令行窗口，进入mysqlbin的目录
如何连接mysql：mysql -u root -p，后输入密码，即可连接本机
- 使用load连接指令时，使用指令 mysql --local-infile -u root -p
2、设置打开本地加载进入数据库的开关
set global local_infile =1;
3、加载数据进入表中
load data local infile ‘文本文件路径' into table 表名 fields terminated by '属性之间的分隔符' lines terminated by'行分隔符';
如果您已经在Windows系统中生成了文本文件，您可能必须使用LINES TERMINATED BY ‘\r\n'来正确地读取文件，因为Windows程序通常使用两个字符作为一个行终止符。部分程序，当编写文件时，可能会使用\r作为行终止符。要读取这样的文件，应使用LINES TERMINATED BY ‘\r\n'。
主键优化
数据的组织形式：在innodb存储引擎中，数据按照主键的顺序存放（聚集索引）
页是磁盘IO默认的最小单位，一个页16k，一个区可以存放64个页
规定一个页最少包含两行数据
一个页满了，申请另外的页，两个页通过双向指针联系
主键顺序插入
主键乱序插入：页分裂
页分裂：当乱序输入时，发生页分裂来存储
主键删除：页合并
删除一行数据，记录并没有被实际上物理删除（类似于数组删除，数组元素前移覆盖），只是被标记（标记位）为删除并且它的空间可以被别的记录声明使用
设置记录的标志位例如flag，删除此纪录修改标志位：flag=1,表示纪录被删除
当删除了的记录达到了页的百分之50，就会看是否进行页合并（前后寻找）
主键设计原则
1、主键值长度尽量小（减少存储空间，减少磁盘IO）
2、主键尽量顺序插入（自增主键）
3、尽量减少主键修改（会更改聚集索引）
order by优化
分类有两种：
1、using index ：排序时可以直接根据索引排序，不必要浪费空间，效率最高
2、using filesort：通过条件选择符合要求的行数据，将这些行数据在缓冲区进行排序，后输出(借用缓冲区空间,效率低)
向使用索引方向优化
explain select * from student_info order by telephone ,email ;--
explain select telephone,email from student_info order by telephone ,email ;--第一个使用：using filesort---*回表扫描
第二个使用：using index--- 根据索引查询
两边排序方法不一样，创建指定排序不同的索引
order by优化方式
1、尽量使用索引排序
2、使用覆盖查询，不要涉及回表查询
3、多字段排序也涉及最左前缀法则
4、排序方式不一样时，注意创建索引的方法
group by优化
不使用索引：
使用索引：
group by优化方法
1、使用索引
2、索引使用也满足最左前缀法则
limit优化
大数据量情况下，例如得到200000后的10条记录，需要扫描200000~200010的数据，效率低
limit优化方法
1、官方说通过覆盖索引和子查询，但是mysql不支持in(limit)
2、通过覆盖索引和联合查询
select * from student_info as a,(select id from student_info force index(primary)limit 2,2 )as b where a.id=b.id;对于主键使用limit，可能出现问题
查看执行计划
存在主键，主键被设计成为聚集索引，以为使用的会是聚集索引。实际上这个使用的索引并不是主键索引，而是辅助索引，这是mysql的优化器优化的结果
这个情况下，强制使用主键索引
select id from student_info force index (`PRIMARY`)limit 1,3;
这个走的是全表扫描，没有使用索引，因此和主键的顺序一致
count优化
MyISAM存储引擎将纪录的总数存入，count(*)时可直接读取该数值（前提没有where条件）
InnoDB存储引擎执行count（*）时，将所有的纪录一一读取，效率低
count没有优化方法
update优化
InnoDB存储引擎给索引列会增加行锁，update时where后面+索引列，如果不是索引列或者索引列失效，会导致行锁升级为表锁，不可以对表进行更改
视图
视图是一个虚拟的表，视图中的所有数据都来自于创建视图时使用的表
视图语法
1、创建视图
create [or replace]view 视图名 as 查询表的记录
create view borr_info_view as select * from borrow_info;2、查询视图
select （列名） from 视图名
select * from borr_info_view;3、修改视图
第一种：
create [or replace] view 视图名 as 查询表的记录
借助 replace来替换视图
create or replace view borr_info_view as select id,book_id from borrow_info;第二种：
alter view 视图名 as 查询表的记录
alter view borr_info_view as select id from borrow_info;4、删除视图
drop view 视图名
drop view borr_info_view;检查选项
create or replace view borr_info_view as select * from borrow_info where book_id<5;
select * from ebook.borr_info_view;
insert ebook.borr_info_view values(null,4,1,'2019-03-12 14:25:00','2019-04-02 16:37:00');
select * from ebook.borr_info_view;查看视图和表，都含有此条记录
insert ebook.borr_info_view values(null,6,2,'2019-09-12 14:25:00','2019-12-02 16:37:00');
select * from ebook.borr_info_view;
查看表，含有此条记录 ，视图却没有（条件限制）
在不添加检查选项时，数据都会通过视图被插入表，只不过是在视图中不一定看得到（限制条件）
引入检查选项，来解决数据全部插入的问题
with cascaded/local check option ：检查数据是否可以插入，不可以插入就会报错
视图允许在一个视图的基础上创建视图，为了明确检查范围，引入cascaded和local ，默认为cascaded
cascaded：同时满足多个视图的要求
local：只需满足当前要求
create or replace view borr_info_view as select * from borrow_info where book_id<5 with cascaded check option ;
select * from ebook.borr_info_view;
insert ebook.borr_info_view values(null,3,2,'2019-09-12 14:25:00','2019-12-02 16:37:00');
select * from ebook.borr_info_view;
insert ebook.borr_info_view values(null,9,2,'2019-09-12 14:25:00','2019-12-02 16:37:00');
select * from ebook.borr_info_view;
检查选项：检查数据是否可以插入，不可以插入就会报错
1、视图1不设置检查选项
- 视图2不设置检查选项
-- 视图1
create or replace view s_v1 as select * from student where id<5;
-- 视图2
create or replace view s_v2 as select * from s_v1 where id>2;
-- 没有检查选项，不会检查，数据全部插入表格
insert s_v2 values(1,'张三丰');-- 不满足s2，满足s1
insert s_v2 values(6,'张三丰');-- 满足s2，不满足s1
insert s_v2 values(3,'赵敏');-- 都满足
s2不设置检查选项，s2不检查自身，s2去递归查看s1，s1也没有设置检查选项，s1不检查自身
记录全部插入student表
- 视图2设置检查选项 with cascaded check option
-- 视图1
create or replace view s_v1 as select * from student where id<5;
-- 视图2
create or replace view s_v2 as select * from s_v1 where id>2 with cascaded check option ;
insert s_v2 values(1,'周芷若');-- 不满足s2，满足s1
-- 查看s_v2，s_v2检查不满足要求，报错
insert s_v2 values(6,'张三丰');-- 满足s2，不满足s1
-- 查看s_v2，s_v2检查满足自身要求，联级查询需要同时满足，s_v2查看s_v1，6不满足s1的条件，报错
insert s_v2 values(3,'赵敏');-- 都满足
with cascaded check option ；当视图2条件不满足时，报错，当视图2条件满足，视图2去查看视图1的条件是否满足，只有当两个同时满足才会成功
- 视图2设置检查选项 with local check option
-- 视图1
create or replace view s_v1 as select * from student where id<5;
-- 视图2
create or replace view s_v2 as select * from s_v1 where id>2 with local check option ;
insert s_v2 values(1,'周芷若');-- 不满足s2，满足s1
-- 查看s_v2，s_v2检查自身，并去检查s_v1,s_v2检查自身不符合要求，报错
insert s_v2 values(6,'张三丰');-- 满足s2，不满足s1
-- 查看s_v2，s_v2检查满足自身要求，不需要同时满足s_v1，s_v2查看s_v1，s_v1没设置检查选项，不进行检查，插入成功
insert s_v2 values(3,'赵敏');-- 都满足
with local check option ：不要求本身视图和所依赖的视图同时满足
2、多重情况的嵌套
create or replace view s_v1 as select * from student where id>=5;
create or replace view s_v2 as select * from s_v1 where id<=30 with cascaded check option ;
create or replace view s_v3 as select * from s_v2 where id>=20 ;
insert s_v3 values(30,'周芷若');-- 三个都满足
insert s_v3 values(40,'周芷若');
-- 满足s_v3，s_v3不设置检查选项，查看s_v2，s_v2是级联检查,不满足s_v2，报错
insert s_v3 values(4,'周芷若');
-- 不满足s_v3，s_v3不设置检查选项，不检查s_v3，去查看s_v2，s_v2是级联检查,满足s_v2，但是不满足s_v1，报错
insert s_v3 values(19,'周芷若');
-- 不满足s_v3，s_v3不设置检查选项，不检查s_v3，去查看s_v2，s_v2是级联检查,满足s_v2，查看s_v1，也满足s_v1 插入成功s_v3不包含检查选项，不检查s_v3，s_v3递归查看s_v2， s_v2是级联检查，检查s_v2后还要检查s_v1 ，s_v2和s_v1同时满足才会插入成功
3、多重情况的嵌套
create or replace view s_v1 as select * from student where id>=5;
create or replace view s_v2 as select * from s_v1 where id<=30 with local check option ;
create or replace view s_v3 as select * from s_v2 where id>=20 ;
insert s_v3 values(30,'周芷若');-- 三个都满足
insert s_v3 values(40,'周芷若');
-- 满足s_v3，s_v3不设置检查选项，查看s_v2，s_v2只检查自身,不满足s2，报错
insert s_v3 values(4,'周芷若');
-- 不满足s_v3，s_v3不设置检查选项，查看s_v2，s_v2只检查自身,满足s_v2，查看s_v1不包含检查选项，成功
insert s_v3 values(19,'周芷若');
-- 不满足s_v3，s_v3不设置检查选项，查看s_v2，满足s_v2，查看s_v1，不包含检查选项，成功
s_v3不包含检查选项，不检查s_v3，查看s_v3的来源s_v2， s_v2只检查自身，s_v2检查后检查s_v1,(不是联合查询，不要求s_v2和s_v1同时满足）
视图的更新
要使视图可以更新，那么视图的行必须和基础表的行属于一对一的关系，即要求视图
- 不含有聚合函数（sum,count,div……）
- 不含有group by（having）
- 不含有distinct
- union 、union all
存储过程
一个更新数据的过程：查看数据，更新数据，查看数据
按照顺序执行这三个SQL语句，涉及到多次网络请求，可以将这三个语句封装到一起，提交一次，一起执行，可以减少网络请求
1、创建存储过程：
create procedure 存储过程名称(参数) begin SQL语句 end;
2、调用存储过程：
call 存储过程名称(参数)
create procedure p1()
begin
select * from student;
update student set name='张三' where id=1;
select * from student;
end;
call p1();结果:返回这个存储过程中所有语句全部执行之后的结果
3、查看
查看某一个数据库的存储过程和状态信息
select * from information_schema.ROUTINES where ROUTINE_NAME='数据库名'
查看存储过程的建立语句
show create procedure 存储过程名称;
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='book';
show create procedure p1;4、删除
drop procedure 存储过程名称;
drop procedure p1;命令行窗口使用会报错：使用 delimiter 设计结束符
变量
mysql的变量分为三种：系统变量，用户自定义变量，局部变量
系统变量
系统变量由mysql数据库本身提供，分为全局变量（global）和会话变量（session）
全局变量：在所有会话有效
会话变量：仅在当前会话有效
不指定时默认级别是session
show variables ;
show global variables ;
show session variables ;
select @@autocommit;
show global variables like 'auto%';
set @@autocommit =1;用户自定义变量
用户自己创建的变量，不需要提前声明，直接使用'@变量名'就可以，作用域是当前连接
给用户自定义变量赋值
set @变量名 =……；
select 字段名 into @变量名 from 表名---从一个表选出信息给变量赋值
(只能选定一个唯一值且唯一类型相同的属性列赋值）
使用变量
select @变量名；
set @myname ='张三';-- 给创建的变量myname赋值为张三
set @mygender='男',@myid=1;
select @myname ,@mygender,@myid;
select count(*) into @mycount from books;
select @mycount;
select * from books;将查询的count结果赋值给用户创建的变量
局部变量
定义在局部范围内生效的变量，访问之前需要使用declare来声明。可以作为存储过程中的局部变量和输入参数，局部变量的作用范围在声明的存储过程之内
声明
declare 变量名 变量类型
给局部变量赋值
set 变量名 =……；
select 字段名 into 变量名 from 表名---从一个表选出信息给变量赋值
create procedure p6()
begin
declare my_id int;
select id into my_id from borrow_info where book_id =13;
select my_id;
end;
call p6();
if判断
create procedure p8()
begin
declare mygrade int default 89;
declare mystatus char(3);
if mygrade>=90 then set mystatus='优秀';
elseif mygrade>=75 then set mystatus='良好';
elseif mygrade>=60 then set mystatus='及格';
else set mystatus='不及格';
end if;
select mygrade,mystatus;
end;
call p8();
存储过程的参数
传入成绩，并返回成绩等级
create procedure p9(in mygrade int,out mystatus char(3))-- in输入，out输出
begin
if mygrade>=90 then set mystatus='优秀';
elseif mygrade>=75 then set mystatus='良好';
elseif mygrade>=60 then set mystatus='及格';
else set mystatus='不及格';
end if;
end;
call p9(89,@result );-- 传入自定义变量，用于接收结果
select @result;
call p9(56,@result );-- 传入自定义变量，用于接收结果
select @result;case
根据月份返回所在季节
create procedure p10(in month int,out season char(2))
begin
case
when month<=3&&month>=1 then set season ='春天';
when month<=6&&month>=4 then set season ='夏天';
when month<=9&&month>=7 then set season ='秋天';
when month<=12&&month>=10 then set season ='冬天';
else
set season='非法';
end case;
end;
call p10(7,@season);
select @season;
循环
while循环---条件满足开始执行
create procedure p11( in n int )
begin
declare total int default 0;
while n>0 do
set total=total+n;
set n=n-1;
end while ;
select concat('从1到n'+n+'的累计和是'+total);
end;
call p11(4 );repeat---条件不满足开始执行，条件满足退出
create procedure p12( in n int )
begin
declare total int default 0;
repeat
set total=total+n;
set n=n-1;
until n<=0 end repeat;-- 循环结束的条件
select concat('从1到n'+n+'的累计和是'+total);
end;
call p12(3);loop循环
1~n求和
create procedure p13( in n int )
begin
declare total int default 0;
loop_sum: loop
set total=total+n;
set n=n-1;
if n<=0 then leave loop_sum ;
end if;
end loop loop_sum ;
select concat('从1到n'+n+'的累计和是'+total);
end;
call p13(2);1~n之间的偶数求和
create procedure p14(in n int)
begin
declare total int default 0;
sumdouble: loop
if n<=0
then leave sumdouble;
end if;
if n%2=1
then
set n=n-1;
iterate sumdouble;
end if;
set total=total+n;
set n=n-1;
end loop sumdouble;
select concat('从1到n'+n+'的偶数累计和是'+total);
end;
call p14(7);游标
用于存储查询结果集的数据类型
游标声明
declare 游标名称 cursor for 查询结果集 --- (查询结果集内的记录会存储进入此游标）
游标开启
open 游标名称
游标数据获取
fetch 游标名称 into 变量名（循环获取）
游标关闭
close 游标名称
实现一个存储过程：查询student表中id<5的学生的id和name，将查询的结果创建进入新的表
-- 查询student表中id<5的学生的id和name，将查询的结果创建进入新的表
-- 查询信息多行多列，需要通过游标存储 1、声明游标(查询结果会存储进入此游标） 2、开启游标
-- 将查询的结果创建进入新的表 3、创建新的表 4、查询游标数据并插入新表 5、关闭游标
create procedure p15()
begin
-- 1、声明游标 所有的声明都放在游标开启之前
declare myid int;
declare myname varchar(20);
-- 变量的声明必须在游标声明之前
declare c_stu cursor for select id,name from student;
-- 2、开启游标
open c_stu;
-- 3、创建新的表
create table newstu (
id int ,
name varchar(20)
);
-- 4、查询游标数据
while true do
fetch c_stu into myid,myname;
insert newstu values(myid,myname);
end while;
-- 5、关闭游标
close c_stu;
end;
call p15();
注意声明 ：所有的声明都放在游标开启之前，并且变量（获取游标时的变量名）的声明必须在游标声明之前
这个问题是因为死循环，如何知道读取游标时读取完了呢？借助mysql的条件处理程序
mysql的条件处理程序
状态码02000对应的就是上面的报错原因
-- 查询student表中id<5的学生的id和name，将查询的结果创建进入新的表
-- 查询信息多行多列，需要通过游标存储 1、声明游标(查询结果会存储进入此游标） 2、开启游标
-- 将查询的结果创建进入新的表 3、创建新的表 4、查询游标数据并插入新表 5、关闭游标
create procedure p15()
begin
-- 1、声明游标 所有的声明都放在游标开启之前
declare myid int;
declare myname varchar(20);
-- 变量的声明必须在游标声明之前
declare c_stu cursor for select id,name from student where id<5;
declare exit handler for SQLSTATE '02000'close c_stu;
-- 条件处理程序：当报错出现02000状态码时触发此退出程序，伴随着关闭游标的操作
-- 2、开启游标
open c_stu;
-- 3、创建新的表
drop table if exists newstu;
create table if not exists newstu (
id int ,
name varchar(20)
);
-- 4、查询游标数据
while true do
fetch c_stu into myid,myname;
insert newstu values(myid,myname);
end while;
-- 5、关闭游标
close c_stu;
end;
call p15();
select *from newstu;
存储函数
存储函数名称以f开头 或者
create function fun1( n int)
returns int deterministic
begin
declare total int default 0;
while n>0 do
set total=total+n;
set n=n-1;
end while;
return total;
end;
select fun1(4);触发器
和表有关的数据结构，执行insert，delete，update后，触发并且执行触发器内定义的SQL语句集合
-
Insert触发器：向表中插入数据时被触发；
-
Update触发器：修改表中数据时被触发；
-
Delete触发器：从表中删除数据时被触发；
触发器创建
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;
Insert触发器
-- 这个触发器的作用：插入student_info记录后，触发并执行触发器，插入记录到s表
create trigger tri_book
after insert -- insert操作执行的触发器
-- after表示在insert语句执行完之后触发 触发器
-- before 表示在insert语句执行完之前触发 触发器
on student.student_info for each row
-- for each row 行级触发器
begin
-- 触发器触发的语句
insert s values(new.id,new.name);-- new：可以获得新的记录的数据 old：可以获得之前记录的数据
end;
-- 这个触发器的作用：插入student_info记录时，插入记录到s表
update触发器
create trigger tri_book_update
after update -- update操作执行的触发器
on student.student_info for each row
-- for each row 行级触发器
begin
-- 触发器触发的语句
update s set id=NEW.id,name=new .name where id=old.id;
end;
update student_info set name='吴丽华' where id=7;
select * from student_info;
select *from s;
delete触发器
create trigger student_info_delete_trigger
after delete
on student.student_info for each row
-- for each row 行级触发器
begin
-- 触发器触发的语句
delete from s where s.id=OLD.id and s.name=old .name;
end;
delete from student_info where id<=2;
select * from student_info;
select *from s;
before和after的区别
创建两张表
create table class (
classid int primary key ,
classname varchar(20)
);
create table student(
class_id int ,foreign key (class_id)references class(classid),
name varchar(20)
);
before级别的触发器
create trigger student_insert_trigger
before insert
on class for each row-- 主键
begin
insert student values(new.classid,'张三');-- 外键
end;
before级别的触发器：是在记录操纵之前触发，是先完成触发，再增删改
执行插入语句到class表的操作，触发了触发器， before级别的触发器，先执行触发器内的语句： insert student values(new.classid,'张三');由于student表class_id是外键，先执行这个语句但是主键没有这个值，会报错
after级别的触发器
drop trigger student_insert_trigger;
create trigger student_insert_trigger_after
after insert
on class for each row
begin
insert student values(new.classid,'张三');
end;
insert class values(1,'java1班');执行插入语句到class表的操作，触发了触发器， after级别的触发器，先执行触发语句 insert class values(1,'java1班');后执行触发器内的语句 insert student values(new.classid,'张三');由于student表class_id是外键，主键元素插入在此语句执行之前，两个表都插入了数据
触发器特性
1、mysql现在只支持行级触发器，不支持语句级触发器
create trigger tri_book_update
after update -- update操作执行的触发器
on student.student_info for each row
-- for each row 行级触发器
begin
-- 触发器触发的语句
insert s set id=NEW.id,name=new .name;
end;
update student_info set name='吴丽华' where id<=2;
select * from student_info;
select *from s;
任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
2、触发器不能关联在临时表和视图上
3、触发器触发条件：insert、delete、update
4、触发时间：在增删改前或者后(before/after)
5、OLD 和 NEW 的作用
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体：
- 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
- 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
- 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；
查看触发器
查看全部触发器
SHOW TRIGGERS;
查看详细的触发器信息
SELECT * FROM information_schema.triggers where ……；
所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。
删除触发器
drop trigger 触发器名称；
3312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
