1.索引
2.函数
3.存储过程
4.视图
5.触发器
1.索引
在数据库中索引最核心的作用是：加速查找。：例如：在含300万条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒
1.1索引原理
为什么加上索引后速度能有这么大的提升呢？
因为索引的底层是基于B+Tree的数据结构存储的
数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同
myisam引擎，非聚簇索引（数据和索引结构分开存储）
innodb引擎，聚簇索引（数据和主键索引结构存储到一起）
1.1.1非聚簇索引（myisam引擎）
create table 表名(
in int not null auto_increment primary key,
name varchar(32) not null,
age int
)engine=myisam default charset=utf8;
1.1.2聚簇索引
create table 表名(
in int not null auto_increment primary key,
name varchar(32) not null,
age int
)engine=innodb default charset=utf8;
一般使用innodb引擎，聚簇索引
1.2常见索引
在innodb引擎下，索引底层等都是基于B+Tree的数据结构存储的（聚簇索引）
主键索引：加速查找，不能为空，不能重复。+联合主键索引
唯一索引：加速查找，不能重复。+联合唯一索引
普通索引：加速查找。+联合索引
1.2.1
主键和联合主键索引
create table 表名(
id int not null auto_increment primary key, --主键
name varchar(32) null
);
create table 表名(
id int not null auto_increment,
name varchar(32) not null,
primary key(id)
);
create table 表名(
id int not null auto_increment,
name varchar(32) not null,
primary key(列1，列2)
); --如果有多列，称为联合主键（不常用且myisam引擎支持）
如果表结构已经创建完成：
alter table 表名 add primary key(列名);
alter table 表名 drop primary key;
注意：删除索引是时可能会报错，自增列必须定义为键。
如果删除键，必须连带把那列的自增取消：alter table 表 change id int not null;
唯一和联合唯一索引
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique ix_name(name),
unique ix_email(email) --两个单列的唯一索引
);
reate table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique (列1，列2) --如果有多列称为联合唯一索引
);
create unique index 索引名 on 表名（列名）;
drop unique index 索引名 on 表名;
1.2.3索引和联合索引
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_name(name)
);
create table 表名(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_email_name(列1，列2) --如果有多列称为联合索引
);
create index 索引名 on 表名（列名）;
drop index 索引名 on 表名;
1.3操作表————查询是要命中索引呀
一般情况下，我们针对只要通过索引去搜搜都可以命中索引（通过索引结构加速查找）
特殊情况下，让我们无法命中索引（即使创建了索引），这也是大家在开发中需要注意的：
类型不一致：
select * from big where name=123; --未命中
select * from big where email=123; --未命中
特殊的主键：
select * from big where id='123';
使用不等于：
select * from big where name!="wupeiqi"; --未命中
select * from big where email!="111@qq.com" --未命中
特殊的主键：
select * from big where id!=123 --命中
or,当or条件中有为建立索引的列才失效
select *from big where id=123 or password='xx'; --未命中
特别的：
select * from big where id=10 or password='xx' and name='xx'; --命中
排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引
select * from big order by name asc; --未命中
特殊的：
select name from big order by name asc; --命中
特别的主键：
select * from big order by id desc;
like,模糊匹配时
select * from big where name like "%u-12-19999"; --未命中
select * from big where name like "_u-12-19999"; --未命中
select * from big where name like "wu-%-19999"; --未命中
特别的：
select * from big where name like "u-12-19999%"; --命中，通配符在最后
select * from big where name like "u-%"; --命中，通配符在最后
使用函数：
select *from big where reverse(name)="wupeiqi"; --未命中
特别的：
select *from big where name=reverse("wupeiqi"); --命中,函数在最后
最左前缀，如果是联合索引，要遵循最左前缀原则：
如果联合索引为：（name,password）:
name and password --命中
name --命中
password --未命中
name or password --未命中
常见的无法命中索引的情况就是上述示例。
1.4执行计划
MySQL中提供了执行计划，让你能够预判SQL的执行（只能给到一定的参考，不一定完全能预判准确）
explain+sql语句
其中比较重要的是type,它是SQL性能比较重要的标志，性能从低到高依次是
all < index < range < index_merge <ref_or_null< ref < eq_ref < system / const
全盘扫描 全索引扫描 对索引范围扫描 索引合并 索引查找 连表操作 系统表仅有一行/（根据主键/唯一索引且结果只有一行）
小结：
根据情况创建合适的索引（加速查找）
有索引，则查询时要命中索引。
2.函数
2.1常用函数
selelct count(id),max(id),min(id),avg(id) from d1;
个数 最大值 最小值 平均值
reverse(name)反转
concat(name,name)字符串拼接
now()时间
date_format(now(),"%Y-%m-%d %H:%i:%s")格式化时间
select concat('alex','sb')
select sleep(1)等两秒钟
char_length(str)
concat_ws(separator,str1,str2,...)
字符串拼接（自定义连接符）
concat_ws()不会忽略任何空字符串。（然而会忽略所有的NULL）。
conv(N,from_base,to_base)
进制装换
例如：
select conv('a',16,2);表示将a由16进制转换为2进制字符串表示
2.2创建函数
delimiter $$
create function f1(
i1 int,
i2 int)
return int
begin
declare num int;
declare maxid int;
select max(id) from big into maxid;
set num=i1+i2+maxid;
return(num)
end $$
delimiter ;
执行函数
select f1(11,22);
select f1(11,id),name from d1;
删除函数
drop function fi;
delimiter $$将默认的终止符换成$$
3.存储过程
存储过程，是一个存储在MySQL中的sql语句集合，当主动去调用内存时，其中内部的SQL语句会按照逻辑执行。
创建存储过程
delimiter $$
create procedure p1()
begin
select * from d1;
end $$
delimiter ;
执行存储过程
call p1();
删除存储过程：
drop procedure p1;
3.1参数类型
存储过程的参数可以有如下三种：
in，仅用于传入参数用
out,仅用于返回值用
inout,既可以传入又可以当做返回值
delimiter $$
create procedure p2(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
begin
declare temp1 int;
declare temp2 int default 0;
set temp1=1;
set r1=i1+i2+temp1+temp2;
set i3=i3+100;
end $$
delimiter ;
set @t1=4;
set @t2=0;
call p2(1,2,@t1,@t2);
select @t1,@t2;
3.2返回值&结果集
3.3事物和异常
事物，成功都成功，失败都失败
delimiter $$
create procedure p4(
out p_return_code tinyint
)
begin
declare exit handler for sqlexception
begin
--error
set p_return_code=1;
rollback;
end;
declare exit handler for sqlwarning
begin
--warning
set p_return_code=2;
rollback; --回滚，删除的数据在恢复过来
end;
start transaction; --开启事物
delete from d1'
insert into tb(name)values('seven');
commit;--提交事务
--success
set p_return_code=0;
end $$
delimiter ;
set @ret=100;
call p4(@ret);
select @ret;
3.4游标（效率比较低）
delimiter $$
create procedure p5()
begin
declare sid int;
declare sname varchar(50);
declare done int default false;
declare my_cursor cursor for select id,name from d1;
declare continue handler for not found set done=True;
open my_cursor;
xxoo:loop
ferch my_cursor into sid,sname;
if done then
leave xxoo;
end if;
insert into t1(name) values(sname);
end loop xxoo;
close my_cursor;
end $$
delimiter ;
call p5();
4.视图
视图其实是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当做表来使用。
子查询
select
*
from
(select nid,name from tb1 where nid>2) as A
where
A.name>"alex"
创建视图
create view v1 as select id,name from d1 where id>1;
使用视图
select *from va;
删除视图
drop view v1;
修改视图
alter view v1 as Sql语句
注意：基于视图只能查询，针对视图不能执行增加，修改，删除。如果原表发生变化，视图表也会发生变化
5.触发器
对某个表进行【增删改】操作的前后如果希望触发某个特定的行为时，可以使用触发器
#插入前
create trigger tri_before_insert_tb1 before insert on tb1 for each row
begin
...
end
#插入后
create trigger tri_after_insert_tb1 after insert on tb1 for each row
begin
...
end
删除
更新
...
示例：
在T1表中插入数据之前，先在T2表中插入一行数据。
delimiter $$
create trigger tri_before_insert_tb1 before insert on t1 for each row
begin
if new.name='alex' then
insert into t2(name) values(new.id);
end if;
end $$
delimiter ;
insert into t1(id,name,email) values(1,'alex','111@qq.com')
在t1表中删除数据之后，再在t2 表中插入一行数据。
delimiter $$
create trigger tri_after_insert_tb1 after delete on t1 for each row
begin
if new.name='alex' then
insert into t2(name) values(old.id);
end if;
end $$
delimiter ;
特别的：new表示新数据，old表示原来的数据
