索引

索引是数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

• 优点
  为了加快搜索速度，减少查询时间
• 缺点
  1.索引是以文件存储的。如果索引过多，占磁盘空间较大。
  2.索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率
• 索引的类型

1.普通索引，最基本的索引，不具备唯一性，就是加快查询速度

# 创建表时添加索引
create table person(
  id int(4),
  name varchar(20),
  passwd varchar(20),
  index psd (passwd)
)
注: 可以使用key代替index, psd是索引名，索引名称可以省略，省略的话字段名作为索引名

# 已经创建的表，使用alter为表添加索引
alter table person add index 索引名称 (name, passwd);

# 查看索引
MariaDB [test]> desc person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| passwd | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

如果Key是MUL, 就是一般性索引，该列的值可以重复

# 删除索引
alter table person drop key psd;

2.唯一索引
与普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，用来约束内容，字段值只能出现一次。应该加唯一索引。唯一性允许有NULL值<允许为空>

# 创建表时加唯一索引
create table 表名(
    列定义：
    unique key 索引名 （字段）;
)
# Key是UNI

# 修改表时添加索引
MariaDB [test]> alter table person add unique(name);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> desc person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | YES  |     | NULL    |       |
| name   | varchar(20) | YES  | UNI | NULL    |       |
| passwd | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.主键索引
查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。主键列要求列的所有内容必须唯一，不允许为空。

# 创建表时创建主键索引
create table person( 
  id int(4) not null auto_increment, 
  name varchar(20) default null,
  primary key(id)
);

# 创建表后创建主键索引
alter table personchange id id int(4) not null primary key auto_increment;

# 查看索引
MariaDB [test]> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

4.复合索引
索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引

# 联合索引
MariaDB [test]> create table person(
    -> id int(4) not null,
    -> name varchar(20),
    -> primary key(id, name));
Query OK, 0 rows affected (0.02 sec)
# 插入数据
MariaDB [test]> insert into person values (1, "aaa");
Query OK, 1 row affected (0.02 sec)
MariaDB [test]> insert into person values (1, "aaa");
ERROR 1062 (23000): Duplicate entry '1-aaa' for key 'PRIMARY'
注: 插入同样的数据就报错

5.全文索引
全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果
mysql在数据量较大的情况下，使用select * from tbname where name like '%搜索内容%'这类通配符搜索时，不通过索引，会直接扫描全表，数据库压力大
mysql在3.2开始支持全文检索，但是无法正确支持中文
mysql在5.7.6开始，内置了ngram全文检索插件，用来支持中文分词
全文检索只能用在varchar text字段上

# 创建全文索引
create table 表名（
    列定义，
    fulltext key 索引名 （字段）;
）

注: mysql自带的全文索引只支持MyISAM数据库引擎
所以一般会使用第三方软件进行全文索引
http://sphinxsearch.com/

索引使用原则
1、索引并非越多越好
2、数据量不大不需要建立索引
3、列中的值变化不多不需要建立索引
4、经常排序（order by 字段）和分组（group by 字段）的列需要建立索引
5、唯一性约束对应使用唯一性索引

外键约束

外键约束就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关连性更强
例如，有二张表，一张是用户表，一张是订单表，如果我删除了用户表里的用户，那么订单表里面与这个用户有关的数据，就成了无头数据了，不完整了。
如果有外键的话，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能让数据完整。

视图

视图就是一个存在于数据库中的虚拟表。
视图本身没有数据，只是通过执行相应的select语句完成获得相应的数据

• 什么时候用视图
  如果某个查询结果出现的非常频繁，也就是，要经常拿这个查询结果来做子查询这种
• 使用视图的好处
  1.视图能够简化用户的操作
  视图机制用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作
  2.视图是用户能以不同的角度看待同样的数据。
  对于固定的一些基本表，我们可以给不同的用户建立不同的视图，这样不同的用户就可以看到自己需要的信息了。
  3.视图对重构数据库提供了一定程度的逻辑性。
  比如原来的A表被分割成了B表和C表，我们仍然可以在B表和C表的基础上构建一个视图A，而使用该数据表的程序可以不变。
  4.视图能够对机密数据提供安全保护
  比如说，每门课的成绩都构成了一个基本表，但是对于每个同学只可以查看自己这门课的成绩，因此可以为每个同学建立一个视图，隐藏其他同学的数据，只显示该同学自己的
  5.适当的利用视图可以更加清晰的表达查询数据。
  有时用现有的视图进行查询可以极大的减小查询语句的复杂程度

# 创建视图
create view view_name as select * from ....
# 查看视图
show create view view_name;
# 查询视图
select * from view_name where id = 1
# 修改视图
alter view view_name select * from .....
# 更新视图数据
update view_name set name = "aaa" where id = 1
# 删除视图
drop view 视图名

存储过程

大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成。存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

• 为什么使用存储过程
  1）增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
  2）标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
  3）较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
  4）减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
  5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全
• 为什么不使用存储过程：
  1）可移植性差
  2）对于简单的SQL语句，存储过程没什么优势
  3）如果存储过程中不一定会减少网络传输
  4）如果只有一个用户使用数据库，那么存储过程对安全也没什么影响
  5）团队开发时需要先统一标准，否则后期维护成本大
  6）在大并发量访问的情况下，不宜写过多涉及运算的存储过程
  7）业务逻辑复杂时，特别是涉及到对很大的表进行操作的时候，不如在前端先简化业务逻辑
• 定义存储过程
  创建存储过程之前我们必须修改mysql语句默认结束符 ; 否则会创建失败；
  因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

# 定义存储过程
# 切换数据库
use test;
# 修改分隔符
delimiter // # 我这里用//, 也可以使用其他符号
# 创建存储过程
CREATE  PROCEDURE selUser()
begin
select * from user;
end %
# 不用的话记得把分隔符改回来
delimiter ;

# 调用存储过程
call selUser();

• 存储过程参数类型

In参数

mysql> use test;
Database changed
mysql> delimiter //

# 创建存储过程
mysql> create procedure selUserById(in uid int)
    -> begin
    -> select * from user where id = uid;
    -> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;

# 调用存储过程
mysql> call selUserById(1);
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+

# 使用变量的方式调用
mysql> set @uid = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> call selUserById(@uid);
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.04 sec)

Out参数
不读取外部变量值，在存储过程执行完毕后保留新值

mysql> delimiter //
mysql> create procedure testOut(out o int)
    -> begin
    -> set o=2;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> set @o=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @o;
+----+
| @o |
+----+
|  1 |
+----+
1 row in set (0.04 sec)

mysql> select @o;
+----+
| @o |
+----+
|  1 |
+----+
1 row in set (0.04 sec)

mysql> call testOut(@o);
Query OK, 0 rows affected (0.00 sec)

mysql> select @o;
+----+
| @o |
+----+
|  2 |
+----+
1 row in set (0.04 sec)

InOut参数
读取外部变量，在存储过程执行完后保留新值
传进来，又传出去

mysql> use test;
Database changed
mysql> delimiter //
mysql> create procedure testInOut(inout io int)
    -> begin
    -> select io;
    -> set io=2;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @io=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call testInOut(@io);
+----+
| io |
+----+
|  1 |
+----+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @io;
+-----+
| @io |
+-----+
|   2 |
+-----+
1 row in set (0.04 sec)

• 批量插入多条数据

mysql> delimiter //
mysql> create procedure test(n int)
    -> begin
    -> set @x=0;
    -> repeat set @x=@x+1;  # 循环
    -> insert into test values (@x);
    -> until @x>n
    -> end repeat;
    -> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call test(5);
Query OK, 6 rows affected (0.03 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.04 sec)

• 存储过程变量的使用

# 变量定义， datatype数据类型
declare name datatype 
# 变量赋值
set name = 表达式

mysql> delimiter //
mysql> create procedure selUser()
    -> begin
    -> declare name varchar(200);
    -> set name=(select * from user);
    -> select name;
    -> end //
mysql> delimiter ;
mysql> call selUser;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
+----+------+
1 row in set (0.06 sec)

• 存储过程中的注释
  "--"：单行注释
  "/* */":一般用于多行注释

• 存储过程中的流程控制语句
  1.变量作用域
  内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，因为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。
  2.条件语句

if-then-else

# 用法1
if a=0 then
....
end if;

# 用法2
if a=1 then
.....
else
....
end if;

case

case a
when 0 then
.....;
when 1 then
....;
else
....;
end case;

3.循环语句
while

while a>5 do
....;
end while;

repeat

begin
declare a int;
set a=0;
repeat
insert into test values(a);
set a=a+1;
until a>=5
end repeat;
end //

loop

begin
declare a int;
set a=0;
LOOP_LABLE:loop
insert into test values (a);
set a=a+1;
if a>=5 then
leave LOOP_LABLE;
end if;
end loop;
end;
//

• 查看存储过程

# 查看存储过程内容
show create procedure procedure_name;

# 查看所有存储过程
show procedure status;

• 修改存储过程
  使用alter，不建议
  可以删除重新创建，建议使用第三方工具进行修改
• 删除存储过程

mysql> drop procedure test;
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure if exists test;
Query OK, 0 rows affected (0.00 sec)

触发器

生产比较少用触发器

• 什么是触发器
  触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力
• 触发器的作用
  1.安全性
  可以基于数据库的值使用户具有操作数据库的某种权利
  可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据
  可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%
  2.审计
  可以跟踪用户对数据库的操作
  审计用户操作数据库的语句
  把用户对数据库的操作写入审计表
  3.实现复杂的数据完整性规则
  实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。
  例如，触发器可回退任何企图吃进超过自己保证金的期货
  4.实现复杂的非标准的数据库相关完整性规则。
  触发器可以对数据库中相关的表进行连环更新。
  例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。
  触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务
  5．实时同步地复制表中的数据
  6．自动计算数据值
  如果数据的值达到了一定的要求，则进行特定的处理。
  例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据
• 创建触发器

# 语法
create  trigger 触发器名称  触发的时机  触发的动作  on 表名 for each row 触发器状态。

触发器名称:  自己定义
触发的时机： before /after  在执行动作之前还是之后
触发的动作 :指的激发触发程序的语句类型<insert ,update,delete>
each row:监控每一行

触发器创建语法四要素
1.监视地点(table) 
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)

# 删除user表的记录就清空test表的数据
mysql> delimiter //
mysql> create trigger delUser after delete on user for each row
    -> delete from test;
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> delete from user where id=1;

• 查看触发器

# 查看所有触发器
show triggers;
# 查看触发器创建过程
 show create trigger delUser;

• 删除触发器

drop trigger delUser;

事务

• 什么是事务
  事务是由一组SQL语句组成的逻辑处理单元，要不全成功要不全失败
  注: MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型不支持
• 事务的四大特性
  1、原子性（Autmic）：事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！
  2、一致性（Consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。在事务开始之前和结束之后，数据库的完整性约束没有被破坏
  3、 隔离性（Isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰，这些通过锁来实现。
  4、 持久性（Durability）：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障（比如说宕机等）不应该对其有任何影响。
• 设置自动提交模式
  默认是自动提交的

set autocommit = 1;
0: 禁止自动提交
1: 开启自动提交

• 提交事务

start transaction;
update books set bName="HA" where bId=1;
update books set bName="LB" where bId=2;
commit;

• 回滚事务

start transaction;
update books set bName="HA" where bId=1;
update books set bName="LB" where bId=2;
rollback;