视图

提高了重用性，就像一个函数。如果要频繁获取user表的name和account的name
```
select a.name as username, b.name as goodsname from user,account as b where a.id=b.userid
```
但有了视图就不一样了，创建视图user_view。示例
create view user_view as select a.name as username, b.name as goodsname from user,account as b where a.id=b.userid;
创建好视图后，就可以这样获取user的name。示例：
select * from user_view;
对数据库重构，却不影响程序的运行
提高了安全性能。可以对不同的用户，设定不同的视图。

存储过程

何为存储过程

存储过程是存储在数据库服务器的一组sql语句，通过在查询中调用一个指定名称来执行这些sql语句命令。

为什么要使用

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。
当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
存储过程可以重复使用,可减少数据库开发人员的工作量。

创建mysql存储过程、存储函数
```
create procedure 存储过程名(参数）
```
下面是存储过程的例子：

mysql> DELIMITER //
mysql> CREATE PROCEDURE proc1(OUT s int)
-> BEGIN
-> SELECT COUNT(*) INTO s FROM user;
-> END
-> //
mysql> DELIMITER ;

注：

（1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入in、输出out、输入输出inout参数，这里有一个输出参数s，类型是int型，如果有多个参数用”,”分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。

声明分割符

其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用MySQL的Administrator管理工具时，可以直接创建，不再需要声明。

参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT。
形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类形…])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回

存储过程的删改查

调用存储过程

Call sp_name([parameter[,…]]);
Sp_name被调用存储过程的名称
Parameter:指定调用存储过程所要使用的参数。

MySQL存储过程的查询

我们可以查看存储过程的详细，但是需要用另一种方法：

SHOW CREATEPROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。

MySQL存储过程的修改

ALTER PROCEDURE
更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROP PROCEDURE
从MySQL的表格中删除一个或多个存储过程。

存储函数

什么是存储函数: 封装一段sql代码，完成一种特定的功能，返回结果。

存储函数的语法:

create function 函数([函数参数[,….]]) Returns 返回类型
Begin
If(
Return (返回的数据)
Else
Return (返回的数据)
end if;
end;

与存储过程返回参数不同的是存储函数在定义时没用直接声明哪个变量是返回参数，而只是使用了returns声明了返回参数所属的数据类型，返回参数是在函数体中使用return返回要返回的数据变量的形式来表示的。这就需要注意的是：
存储函数只支持输入参数，并且输入参数前没有IN或INOUT.

存储函数中的限制

流控制（Flow-of-control）语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.
变量声明(DECLARE)以及指派(SET)是合法的.
允许条件声明.
异常处理声明也是允许的.
但是在这里要记住函数有受限条件:不能在函数中访问表.因此在函数中使用以下语句是非法的。

ALTER ‘CACHE INDEX’ CALL COMMIT CREATE DELETE
DROP ‘FLUSH PRIVILEGES’ GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT ‘SELECT FROM table’
‘SET system variable’ ‘SET TRANSACTION’
SHOW ‘START TRANSACTION’ TRUNCATE UPDATE

存储函数与存储过程的区别

存储函数有且只有一个返回值，而存储过程不能有返回值。
函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用
insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
存储过程可以调用存储函数。但函数不能调用存储过程。
存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

索引失效

使用了or的情况

select * from USER where name=‘xzz’ or age=16；

使用or连接的两个字段，如果两个字段都是索引字段索引才会生效，否则索引无效。

组合索引失效

select * from USER where name=‘xzz’ and age =11；

以age，name顺序为联合索引，此语句并不会使用索引，不满足最左前缀原则

使用like %XXXX

select *  from  USER where name like‘%xzz’ ；

做模糊匹配的时候把%加在最前边的时候，索引无效，加在后面可以

类型转换的时候失效

select * from USER where name=123;

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

使用NOT IN

使用NOT IN的时候索引可能会失效，进而使用全表查询

查询条件使用函数计算的时候

select * from USER where char_length(name)=5  limit 10;

where语句中索引列使用了负向查询，可能会导致索引失效

负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。

某“军规”中说，使用负向查询一定会索引失效，笔者查了些文章，有网友对这点进行了反驳并举证。

其实负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低了。

索引字段可以为null，使用is null或is not null时，可能会导致索引失效

其实单个索引字段，使用is null或is not null时，是可以命中索引的，但网友在举证时说两个不同索引字段用or连接时，索引就失效了，笔者认为确实索引失效，但这个锅应该由or来背，属于第一种场景~~

假设USER表中的user_id列有索引且允许null，age列有索引且允许null。

select * from `user` where user_id is not null or age is not null;

不过某些“军规”和规范中都有强调，字段要设为not null并提供默认值，是有原因值得参考的。

null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化。
null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。
null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识。
对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录。

对索引列进行运算，一定会导致索引失效

运算如+，-，*，/等，如下：

select * from `user` where age - 1 = 10;

优化的话，要把运算放在值上，或者在应用程序中直接算好，比如：

select * from `user` where age = 10 - 1;

Mysql笔记

视图