1 索引

什么是索引

1: 快速查询数据库表中的特定记录，由数据库表中一列或多列组合而成。
2: 通过索引,查询数据时可以不必读完记录的所有信息,而只是查询索引列。
3: 对索引进行的I/O 操作比对表进行操作要少很多
4: 索引一旦被创立将被 MySQL 系统自动维护，查询语句中不用指定使用哪个索引。
5: 索引是一种提高查询效率的机制。
6: 索引可以提高查询的速度，但是会影响插入记录的速度。

索引的分类

1：普通索引：
   可以创建在任何数据类型中，其值是否唯一和非空由字
段本身的完整性约束条件决定。

2：唯一性索引：
   使用 UNIQUE 参数可以设置索引为唯一性索引。创建唯
一性索引的字段的值，必须是唯一的。主键就是一种特殊唯
一性索引。

3：全文索引：
   使用 FULLTEXT 参数可以设置索引为全文索引，全文索
引只能创建在 CHAR、VARCHAR 或 TEXT 类型的字段上，且只
有 MYISAM 存储引擎支持全文检索。

4：单列索引：
   在表中的单个字段上创建索引。单列索引只根据该字段
进行索引。 单列索引可以是普通索引， 也可以是唯一性索引，
还可以是全文索引。只要保证该索引只对应一个字段即可。

5：多列索引：
   多列索引是在表的多个字段上创建一个索引。该索引指
身创建时对应的多个字段，可以通过这几个字段查询。但是
查询条件中使用了第一个字段，索引才会被使用。

6：空间索引：
   使用 SPATIAL 参数可以设置索引为空间索引。空间索引
只能建立在空间数据类型上。MySQL 中的空间数据类型包括
GEOMETRY 和 POINT、LINESTRING 和 POLYGON 等，目前只有
MyISAM存储引擎支持空间检索，而且索引字段不能为空值。

创建索引

在创建表的时候创建索引

CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
...
属性名 数据类型
[UNIQUE | FFULLTEXT | SPATIAL]
INDEX | KEY [别名] (属性名 1 [(长度)] [ASC | DESC]) );

1 :创建普通索引 
CREATE TABLE index1 (
  id INT,
  NAME VARCHAR (20),
  sex CHAR(2),
  INDEX (id) //普通索引
);

2：查看表的结构
SHOW CREATE TABLE index1;
SHOW INDEX FROM book; //查看索引分布

3：查看索引是否被使用
EXPLAIN SELECT * FROM index1 WHERE id = 1;

4：在已经存在的表上创建索引

语法如下：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
ON 表名 （属性名 [(长度)] [ASC | DESC]）;

例：
CREATE INDEX book_index ON book(id);//在book表中id字段创建普通索引

5: 通过修改表创建索引，索引可以只是字段内容的一部分

语法如下：
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL | INDEX 索引名 
（属性名 [(长度)] [ASC | DESC]）;

例：
ALTER TABLE book ADD INDEX index13_name(NAME(5));

删除索引

一些不再使用的索引会降低表的更新速度，影响数据库性能,需要删除。
例：
DROP INDEX index13_name ON book;
SHOW INDEX FROM book; //查看索引分布

合理使用索引提升查询效率

1: 为经常出现在WHERE 子句中的列创建索引
2: 为经常出现在ORDER BY、DISTINCT 后面的字段建立索引。
   如果建立的是复合索引，索引的字段顺序要和这些关键
   字后面的字段顺序一致。
3: 为经常作为表的连接条件的列上创建索引
4: 不要在经常做DML 操作的表上建立索引
5: 不要在小表上建立索引
6: 限制表上的索引数目，索引并不是越多越好
7: 删除很少被使用的、不合理的索引

2 约束

什么是约束

1：约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件
2：约束是在数据表上强制执行的一些数据检验规则，当执行 DML操作时，
   数据必须符合这些规则，如果不符合则无法执行。
3：约束条件可以保证表中数据的完整性，保证数据间的商业逻辑。

约束的类型

1：非空约束（NOT NULL）,简称 NN

   创建表时添加：
   CREATE TABLE employees(eid INT, NAME VARCHAR(30) NOT NULL,
   salary INT, hiredate DATE);

   修改表时添加：
   ALTER TABLE employees MODIFY eid INT NOT NULL;

   取消非空约束：
   ALTER TABLE employees MODIFY eid INT NULL;

2：唯一性约束（Unique），简称 UK，不可重复的数据 如id。

   创建表时添加：两种方式，一种不指定名字, 一种指定名字
   CREATE TABLE employees(eid INT UNIQUE, NAME VARCHAR(30),
   email VARCHAR(50), salary INT, hiredate DATE,
   CONSTRAINT employees_email_uk UNIQUE(email));
   
   （上面 employees_email_uk 为约束名）

   修改表时添加：
   ALTER TABLE employees
   ADD CONSTRAINT employees_name_uk UNIQUE(NAME);
   
   查看约束：
   SHOW CREATE TABLE employees;
   
   删除：当做索引来删除
   ALTER TABLE employees
   DROP INDEX employees_name_uk;
   
   
3：主键约束（Primary Key）,简称 PK

   ◆主键（Primary Key）约束条件从功能上看相当于非空
   （NOT NULL）且唯一（UNIQUE 的组合）
   ◆主键应是对系统无意义的数据
   ◆一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。
   ◆主键尽量建立在单列上

   创建表时，添加主键约束
   CREATE TABLE employees (eid INT PRIMARY KEY, //主键
   NAME VARCHAR(30),
   email VARCHAR(50),
   salary INT,
   hiredate DATE );
   
   在建表之后增加主键约束条件：
   ALTER TABLE book
   ADD CONSTRAINT book_eid_pk PRIMARY KEY(id);
   
   删除主键：
   ALTER TABLE employees
   DROP PRIMARY KEY;
   
4：外键约束（Foreign Key），简称 FK

   ◆外键约束条件定义在两个表的字段或一个表的两个字
     段上，用于保证相关两个字段的关系

   创建外键约束之前，必须先把主表的主键建立
   ALTER TABLE department
   ADD CONSTRAINT department_eid_pk PRIMARY KEY(d_id);
   
   建表之后，添加外键约束
   ALTER TABLE employee
   ADD CONSTRAINT employee_deptno_fk  // 添加约束
   FOREIGN KEY(d_id)                  // 设置外键
   REFERENCES department(d_id);       // 关联

5：检查约束（Check）,简称 CK

6：外键约束对一致性的维护

   ◆外键约束条件包括两个方面的数据约束
   ◆从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL;
   ◆当主表参照列的值被从表参照时，主表的该行记录不允许被删除。
   
   例子：
   INSERT INTO employees2(eid, NAME, d_id)
   VALUES(1001, 'rose', 1001); //成功
   
   INSERT INTO employees2(eid, NAME, d_id)
   VALUES(1001, 'rose', NULL); //成功
   
   INSERT INTO employees2(eid, NAME, d_id)
   VALUES(1001, 'rose', 11);  //失败，不存在部门 11
   
   DELETE FROM department WHERE d_id=1001; //失败，1001 被参照，不允许删除

3 触发器

什么是触发器

1: 触发器（TRIGGER）是由事件来触发某个操作。
2: 事件包括 INSERT语句、UPDATE语句和 DELETE语句。
3: 当数据库系统执行这些事件时，满足触发器的触发条
   件时，数据库系统就会执行触发器中定义的程序语句。

触发器作用

1: 安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
2: 审计。可以跟踪用户对数据库的操作
3: 实现复杂的数据完整性规则
4: 实现复杂的非标准的数据库相关完整性规则。
5: 同步实时地复制表中的数据
6: 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

创建触发器

   ◆一个表在相同触发时间的相同触发事件，只能创建一个触发器。
   
   创建只有一个执行语句的触发器的基本形式如下：  
   CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件
   ON 表名 FOR EACH ROW 执行语句;
   
   BEFORE 和 AFTER 参数指定了触发器执行的时间，
   “BEFORE”指在触发事件之前执行触发语句，
   “AFTER”表示在触发事件之后执行触发语句。
   
   触发事件参数指触发的条件。
   
   FOR EACH ROW表示任何一条记录上操作满足触发事件都会触发该触发器

   执行语句，指触发器被触发后执行的程序

    //建表 person
    CREATE TABLE person(NAME VARCHAR(20),sex CHAR(2),age INT);
    INSERT INTO person VALUE('武松','男',28);
    INSERT INTO person VALUE('大朗','男',29);
    INSERT INTO person VALUE('潘金莲','女',22);
    
    //建表 trigger_time 记录触发时间
    CREATE TABLE trigger_time(insert_time DATETIME);
    
例：创建一个由INSERT触发的触发器person_trig.  
    CREATE TRIGGER person_trig
    BEFORE INSERT ON person
    FOR EACH ROW
    INSERT INTO trigger_time VALUES(NOW());


    向表person插入一条数据，查看是否触发了触发器。
    INSERT INTO person VALUE('西门庆','男',26);

    查看触发器执行结果
    SELECT * FROM trigger_time; //2017-11-30 21:13:39

创建有多个执行语句的触发器

    DELIMITER &&
    CREATE TRIGGER person_trig2
    AFTER DELETE ON person
    FOR EACH ROW
    BEGIN
    INSERT INTO trigger_time VALUES(NOW());
    INSERT INTO trigger_time VALUES(NOW() + 1);
    END
    &&
    DELIMITER;

    向表person删除一条数据，查看是否触发了触发器。
    DELETE FROM person WHERE NAME = '西门庆'; 
    
    查看触发器执行结果
    SELECT * FROM trigger_time; //触发了两次

查看触发器

SHOW TRIGGERS;
该语句无法查询指定的触发器，只能查询所有触发器的信息。

触发器的使用

1: 触发器执行的顺序是BEFORE 触发器、 表操作 （INSERT、UPDATE和 DELETE）和 AFTER 触发器。
2: 在激活触发器时，对触发器中的执行语句存在一些限制。
   例如，触发器中不能包含 START TRANSACTION、COMMIT或 ROLLBACK等关键词，也不能包含 CALL 语句。
3: 在触发器执行过程中，任何步骤出错都会阻止程序向下执行。

删除触发器

DROP TRIGGER dept_trig1;