MY-SQL-----数据库---索引---储存过程（后）

   索引

索引是一种特殊的文件,它们包含着对数据表里所有记录的引用指针。
它是对数据库表中一列或多列的值进行排序的一种结构。
简单理解
数据库索引好比是一本书前面的目录,能够加快数据库的查询速度,
数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

建立索引的目的是加快对表中记录的查找或排序。
创建索引可以大大提高系统的性能
1,通过创建唯一家引,可以保证教据库表中每一行数据的唯一性。
2,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
3,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
5,通过使用索引,可以在查询的过程中,使用优化器,提高系统的性能。

索引的类型
1,普通索引
这是最基本的索引,它没有任何限制。分别为单列索引和组合索引.

单列索引:即一个索引只包含单个列,一个表可以有多个单列索引 .
它有以下几神创建方式：创建索引语法 create index  索引名 on 表名(表中的列名)

组合家引：
多列索引多列索引也称组合索引，即一个索引巴含多个列。 
创建多列索引的方式与创建单列索引的方式几乎相同,
只是创建单列索引时括号中只写上了一个列。
创建多列索引时在拮号中写入多个列,列与列之间用退号隔开。
例如：为学生表(student_9)的姓名,电话号码,所在地址创建组合案引，两行代码效果相同
create index idx name phoneNo ads on student 9(name,phoneNo, address)
alter table student_9 add index idx_name_phoneNo_ads(name, phoneNo, address)

如果使用多列索引, where条件中字段的顺序非常重要,需要满足最左前缀列
最左前缀:查询条件中的所有字段需要从最左边起按顺序出现在多列象引中,
需要按照最左家引列查找,不能跳过中间列,
查询条件的字段数要小于等于多列索引的字段数,
中间字段不能存在范围查询的字段(s,like等),
这样的sql语句可以使用该多列索引。

mysal多列索引适合的场景
1,匹配全值,对索引中的所有列都指定具体的值(例如: names='xxx' and phoneNo='xxx' andaddresse='xxx )
2.匹配最左前缀(例如: names='xxx,使用索引中的第一列)
3.匹配部分最左前缀(例如: names='xx' and phoneNo='xx')
4.匹配第一列范围查询(可用like a%,但不能使用like %b或则 like %b%)

2,唯一索引
唯一索引它与前面的音通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。
如果是组合索引,则列值的组合必须唯一.

创建唯一索引
create unique index 索引名 on 表名 (列名)
修改表结构
alter table 表名 add unique index(列名)
创建表的时候直接指定
create table 表名(
列名  列的数据类型  列的约束, 
unique index  索引名 (列名) ,

3.主键索引
  特殊的唯一索引，不允许有空值
  为表定义主键将自动创建主键索引

4,全文索引

全文搜索中，mycql制定了最小字符长度，默认是4必须要匹配大于4的才会有返回结果，可以使用：
SHOW VARIABLESLIKE'ft_min_word_len'来查看制定的字符长度，
也可在mysql配置文件my.ini更改最小字符长度，
方法是在my.ini增加一行  比如：ft_ini_word_len=2,改完后重启mycql即可
另外mysql还会计算一个词的权值，已决定是否出现在结果集中，mysql默认的阀值是50%
mysql不支持中文的全文索引

MYISAM引擎
    不支持事务和外键
    mycql中全文索引类型fullText只能用于MYISAM引擎的表
    create table 表名（列名1，......，）engine=myisam
创建以mysiam为引擎的表只需在后面加上：
engine = mysiam

INNODB
    支持事务和外键
显示创建表时使用的数据库引擎：
show create table 表名

创建全文索引
    1.create fulltext index 索引名 on 表名（列名）
    2.alter table 表名 add fulltext 索引名（列名）
    3.create table 表名（列名 列的数据类型 列的约束，
fulltext 索引名（列名）

使用全文索引
    match（列名列表）against（搜索的值）

存储过程（无参）

mycql存储过程是一组为了完成特定功能的sql语句集

优点：
1.存储过程能实现较快的执行速度
2.存储过程可以重复使用，可减少数据库开发人员的工作量
3.存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
4.安全性高，可设定只有某些用户才具有对制定存储过程的使用权
5.存储过程能减少网络流量

基本语法
如何创建存储过程：
create proecdure 存储过程名称（[参数列表]）
begin
过程体（sql代码）；
end；
    例如显示3+2结果的存储过程：
create proecdure pro_showNum()
begin
select 2+3 from dual；
end；
dual为伪表
查询某个数据库下的所有存储过程：
show procedure status where db=数据库名称
如何调用存储过程：
call 存储过程名称（）
如何删除存储过程：
drop procedure 存储过程名称

存储过程中的变量

1、变量分类:
    局部变量
    begin到end语句之间，用declare申明
    声明局部变量：
create proecdure 存储过程名称（[参数列表]）
begin
declare 变量名 数据类型 [default 值] 
过程体；
end；
            改变变量的值：
create proecdure 存储过程名称（[参数列表]）
begin
declare 变量名 数据类型 [default 值] 
set 变量名=值||表达式；
（set 变量名：=值||表达式）
过程体；
end；
局部变量只在begin  end之间有效

2、用户变量
   用户自定义的变量
    声明用户变量：
set @变量名 ：= 值
    使用select声明变量值：
select @变量名 ：= 变量值
    查询用户变量：
select @a from dual；
    用户变量全局对指定用户有用

3、会话变量
打开客户端连接到MySQL服务器到关闭连接的过程叫会话，会话变量只对连接的客户端有效

4、全局变量
    对所有客户端生效，只有具有super权限才可以设置全局变量
    select into
        把选定的列值直接存储到变量，只有单一的行可以被取回：
select 列1，列2...... into 变量1，变量2...... from 表名 where 过滤条件
使用delimiter修改结束标识符
    一般我们修改结束标识符为$符号，或者双\\

      存储过程（有参）

参数类型
1、in 输入类型
create proecdure 存储过程名称（in 参数名 参数数据类型）
begin
过程体；
end；

2、out 输出类型
定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程（存储过程内部，该参数初始值为null，无论调用者是否给存储过程参数设置值）
可在存储过程内部被改变并返回
create proecdure 存储过程名称（参数类型 参数名 参数数据类型，out 参数名 参数数据类型......）
begin
过程体；
end；
调用该存储过程时要先在外部声明一个用户变量

3、inout输入输出参数
既是输入参数又是输出参数
create proecdure 存储过程名称（inout  参数名 参数数据类型）
begin
过程体；
end；
调用该存储过程也需要先在外部声明用户变量

语法：
create proecdure 存储过程名称（参数类型 参数名 参数数据类型，......）
begin
过程体；
end；

参数类型使用
仅将数据传递给mycql存储过程，使用in参数类型
仅从mycql存储过程返回值，使用out参数类型
需要把数据传给mycql存储过程，还要经过一些计算后再传回给我们，此时要用inout
mycql存储过程参数如果不显示制定参数类型，则默认为"in"

流程控制语句
1、if判断语句
语法：
if 条件表达式 then sql语句集 
end if;

2、双分支if
if 条件表达式 then sql语句集 
else sql语句集
end if;

3、多分支if
if 条件表达式 then sql语句集 
elseif 条件表达式 then sql语句集
else sql语句集
end if;

case判断语句
语法：
case 表达式
when 表达式值 then sql语句集；
[when 表达式值 then sql语句集]......
[else sql语句集]
end case;

语法2：
case
when 表达式 比较运算符 表达式值 then sql语句集；
[when 表达式 比较运算符 表达式值 then sql语句集]......
[else sql语句集]
end case;

在普通sql中使用：
select 列明列表，case 值 when 比较值 then 结果
while语句
        
repaet语句
触发器是与事件相关的特殊的存储过程，
优点
1.自动
2.安全性
3.审计  跟踪用户对数据库的操作
4.业务逻辑封装性号，修改方便
5.实现复杂的数据

基本语法：
create tigger 触发器名称
before/after
insert/update/delete
on 表名
for each row
begin
sql语句集
end
查看触发器语法：
show tiggers
删除触发器：

old关键字与new关键字
    old表示旧值
    new表示新值