1.视图
概述：视图就相当于一个临时表，但是只定义了对应关系，用这个视图的时候，就会自行执行视图里定义好的sql语句，视图是只能去查原表的数据，不能去修改原表里面的数据的；
视图的增删改查：
1.创建视图：
语法：CREATE VIEW 视图名称 AS SQL语句
示例：
CREATE VIEW v1 AS
SELECT nid, name FROM user
WHERE nid > 3;
2.删除视图：
语法：DROP 视图名称
3.修改视图
语法：ALTER VIEW 视图名称 AS sQL语句
示例：
ALTER VIEW v1 AS
SELECT sex, birthday FROM user
WHERE nid > 1
4.使用视图：
示例：
select nid, name from v1;
2.触发器
概述：对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
触发器的创建
# 插入前
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
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
示例1：
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. NAME == 'alex' THEN
INSERT INTO tb2 (NAME)
VALUES
('aa')
END
END//
delimiter ;
示例2：
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. NAME == 'alex' THEN
INSERT INTO tb2 (NAME)
VALUES
('aa')
END
END//
delimiter ;
注意：delimiter // 把默认以“；”作为结束符改为了以//作为结束符，NEW表示即将插入的数据行，OLD表示即将被删除的数据行
触发器的删除
语法：DROP TRIGGER 触发器的名称;
使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的
3.存储过程
概述：存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行
创建存储过程
1.无参数的存储过程
示例：
-- 创建存储过程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
-- 执行存储过程
call p1()
2.有参数的存储过程
对于存储过程，可以接收三类参数：
- in:仅用于传入参数用
- out：仅用于返回值用
- inout：既可以传入又可以当作返回值
创建有参数的存储过程示例：
-- 创建存储过程
delimiter \
create procedure p1(
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 p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
事务型存储过程示例：
delimiter \
create PROCEDURE p1(
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 tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 0;
END\
delimiter ;
删除存储过程
drop procedure 存储过程名称
4.函数
MySQL中的内置函数
- INSTR(str, len):返回字符串str从开始的len位置的子序列字符
- LEFT（str, len）:返回字符串str从开始的len位置的子序列字符
- LOWER（str）：变小写
- UPPER（str）：变大写
- ...
自定义函数
1.定义一个函数：
delimiter \
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END \
delimiter ;
-- 执行函数：
f1(1, 3);
2.删除函数：
drop function 函数名称
5.事务
概述：事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性
6.索引
概述：索引是数据库中专门用于帮助用户快速查询数据的一种数据结构，类似于字典中的目录，查找字典内容时可以根据目录查找找到数据的存放位置，然后直接获取即可
MySQL中常见索引有：
1.普通索引：仅有一个功能，那就是加快查询
- create index 索引名 ob 表名(列名)
2.唯一索引：加速查询和唯一约束（可为null）
- create unique index 索引名 on 表名(列名)
3.主键索引：加速查询和唯一约束（不可为null）
- alter table 表名 add primary key(列名);
4.联合索引：将n个列组合成一个索引
- create index 索引名 on 表名(列名1,列名2。。。);
联合索引需要注意的点：
- 联合索引的最左匹配原则，即最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
- 多字段的联合索引在查询单个字段时是否可以用到索引：如下面的联合索引的语法所示，如果你查询单个字段时，查询的是列名1和列名2或者查询列名1的话，就能命中索引，如果只查询列名2就中不了索引
索引相关命令
查看表结构：
desc 表名
查看生成表的sql
show create table 表名
查看执行时间：
set profilling = 1;
SQL语句...
show profiles
查看执行计划：
explain select * from tb2;
执行计划里面type属性的值代表的意思
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
7.动态执行sql语句
示例：
delimiter \
DROP PROCEDURE IF EXISTS proc_sql \
CREATE PROCEDURE proc_sql ()
BEGIN
declare p1 int;
set p1 = 11;
set @p1 = p1;
PREPARE prod FROM 'select * from tb2 where nid > ?';
EXECUTE prod USING @p1;
DEALLOCATE prepare prod;
END\
delimiter ;
8.如何优化SQL语句
1.列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等
2.建立单列索引
3.根据需要建立多列联合索引
- 当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低，那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。
4.根据业务场景建立覆盖索引
- 只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率
5.多表连接的字段上需要建立索引这样可以极大的提高表连接的效率
6.where条件字段上需要建立索引
7.排序字段上需要建立索引
8.分组字段上需要建立索引
9.where条件上不要使用运算函数，以免索引失效