1 索引

什么是索引

1: 快速查詢數(shù)據(jù)庫表中的特定記錄，由數(shù)據(jù)庫表中一列或多列組合而成。
2: 通過索引,查詢數(shù)據(jù)時(shí)可以不必讀完記錄的所有信息,而只是查詢索引列。
3: 對(duì)索引進(jìn)行的I/O 操作比對(duì)表進(jìn)行操作要少很多
4: 索引一旦被創(chuàng)立將被 MySQL 系統(tǒng)自動(dòng)維護(hù)，查詢語句中不用指定使用哪個(gè)索引。
5: 索引是一種提高查詢效率的機(jī)制。
6: 索引可以提高查詢的速度，但是會(huì)影響插入記錄的速度。

索引的分類

1：普通索引：
   可以創(chuàng)建在任何數(shù)據(jù)類型中，其值是否唯一和非空由字
段本身的完整性約束條件決定。

2：唯一性索引：
   使用 UNIQUE 參數(shù)可以設(shè)置索引為唯一性索引。創(chuàng)建唯
一性索引的字段的值，必須是唯一的。主鍵就是一種特殊唯
一性索引。

3：全文索引：
   使用 FULLTEXT 參數(shù)可以設(shè)置索引為全文索引，全文索
引只能創(chuàng)建在 CHAR、VARCHAR 或 TEXT 類型的字段上，且只
有 MYISAM 存儲(chǔ)引擎支持全文檢索。

4：單列索引：
   在表中的單個(gè)字段上創(chuàng)建索引。單列索引只根據(jù)該字段
進(jìn)行索引。 單列索引可以是普通索引， 也可以是唯一性索引，
還可以是全文索引。只要保證該索引只對(duì)應(yīng)一個(gè)字段即可。

5：多列索引：
   多列索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引。該索引指
身創(chuàng)建時(shí)對(duì)應(yīng)的多個(gè)字段，可以通過這幾個(gè)字段查詢。但是
查詢條件中使用了第一個(gè)字段，索引才會(huì)被使用。

6：空間索引：
   使用 SPATIAL 參數(shù)可以設(shè)置索引為空間索引?？臻g索引
只能建立在空間數(shù)據(jù)類型上。MySQL 中的空間數(shù)據(jù)類型包括
GEOMETRY 和 POINT、LINESTRING 和 POLYGON 等，目前只有
MyISAM存儲(chǔ)引擎支持空間檢索，而且索引字段不能為空值。

創(chuàng)建索引

在創(chuàng)建表的時(shí)候創(chuàng)建索引

CREATE TABLE 表名 (屬性名 數(shù)據(jù)類型 [完整性約束條件],
屬性名 數(shù)據(jù)類型 [完整性約束條件],
...
屬性名 數(shù)據(jù)類型
[UNIQUE | FFULLTEXT | SPATIAL]
INDEX | KEY [別名] (屬性名 1 [(長度)] [ASC | DESC]) );

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

2：查看表的結(jié)構(gòu)
SHOW CREATE TABLE index1;
SHOW INDEX FROM book; //查看索引分布

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

4：在已經(jīng)存在的表上創(chuàng)建索引

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

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

5: 通過修改表創(chuàng)建索引，索引可以只是字段內(nèi)容的一部分

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

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

刪除索引

一些不再使用的索引會(huì)降低表的更新速度，影響數(shù)據(jù)庫性能,需要?jiǎng)h除。
例：
DROP INDEX index13_name ON book;
SHOW INDEX FROM book; //查看索引分布

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

1: 為經(jīng)常出現(xiàn)在WHERE 子句中的列創(chuàng)建索引
2: 為經(jīng)常出現(xiàn)在ORDER BY、DISTINCT 后面的字段建立索引。
   如果建立的是復(fù)合索引，索引的字段順序要和這些關(guān)鍵
   字后面的字段順序一致。
3: 為經(jīng)常作為表的連接條件的列上創(chuàng)建索引
4: 不要在經(jīng)常做DML 操作的表上建立索引
5: 不要在小表上建立索引
6: 限制表上的索引數(shù)目，索引并不是越多越好
7: 刪除很少被使用的、不合理的索引

2 約束

什么是約束

1：約束（CONSTRAINT）的全稱是約束條件，也稱作完整性約束條件
2：約束是在數(shù)據(jù)表上強(qiáng)制執(zhí)行的一些數(shù)據(jù)檢驗(yàn)規(guī)則，當(dāng)執(zhí)行 DML操作時(shí)，
   數(shù)據(jù)必須符合這些規(guī)則，如果不符合則無法執(zhí)行。
3：約束條件可以保證表中數(shù)據(jù)的完整性，保證數(shù)據(jù)間的商業(yè)邏輯。

約束的類型

1：非空約束（NOT NULL）,簡稱 NN

   創(chuàng)建表時(shí)添加：
   CREATE TABLE employees(eid INT, NAME VARCHAR(30) NOT NULL,
   salary INT, hiredate DATE);

   修改表時(shí)添加：
   ALTER TABLE employees MODIFY eid INT NOT NULL;

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

2：唯一性約束（Unique），簡稱 UK，不可重復(fù)的數(shù)據(jù) 如id。

   創(chuàng)建表時(shí)添加：兩種方式，一種不指定名字, 一種指定名字
   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 為約束名）

   修改表時(shí)添加：
   ALTER TABLE employees
   ADD CONSTRAINT employees_name_uk UNIQUE(NAME);
   
   查看約束：
   SHOW CREATE TABLE employees;
   
   刪除：當(dāng)做索引來刪除
   ALTER TABLE employees
   DROP INDEX employees_name_uk;
   
   
3：主鍵約束（Primary Key）,簡稱 PK

   ◆主鍵（Primary Key）約束條件從功能上看相當(dāng)于非空
   （NOT NULL）且唯一（UNIQUE 的組合）
   ◆主鍵應(yīng)是對(duì)系統(tǒng)無意義的數(shù)據(jù)
   ◆一個(gè)表上只允許建立一個(gè)主鍵，而其它約束條件則沒有明確的個(gè)數(shù)限制。
   ◆主鍵盡量建立在單列上

   創(chuàng)建表時(shí)，添加主鍵約束
   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

   ◆外鍵約束條件定義在兩個(gè)表的字段或一個(gè)表的兩個(gè)字
     段上，用于保證相關(guān)兩個(gè)字段的關(guān)系

   創(chuàng)建外鍵約束之前，必須先把主表的主鍵建立
   ALTER TABLE department
   ADD CONSTRAINT department_eid_pk PRIMARY KEY(d_id);
   
   建表之后，添加外鍵約束
   ALTER TABLE employee
   ADD CONSTRAINT employee_deptno_fk  // 添加約束
   FOREIGN KEY(d_id)                  // 設(shè)置外鍵
   REFERENCES department(d_id);       // 關(guān)聯(lián)

5：檢查約束（Check）,簡稱 CK

6：外鍵約束對(duì)一致性的維護(hù)

   ◆外鍵約束條件包括兩個(gè)方面的數(shù)據(jù)約束
   ◆從表上定義的外鍵的列值，必須從主表被參照的列值中選取，或者為NULL;
   ◆當(dāng)主表參照列的值被從表參照時(shí)，主表的該行記錄不允許被刪除。
   
   例子：
   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 觸發(fā)器

什么是觸發(fā)器

1: 觸發(fā)器（TRIGGER）是由事件來觸發(fā)某個(gè)操作。
2: 事件包括 INSERT語句、UPDATE語句和 DELETE語句。
3: 當(dāng)數(shù)據(jù)庫系統(tǒng)執(zhí)行這些事件時(shí)，滿足觸發(fā)器的觸發(fā)條
   件時(shí)，數(shù)據(jù)庫系統(tǒng)就會(huì)執(zhí)行觸發(fā)器中定義的程序語句。

觸發(fā)器作用

1: 安全性。可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利。
2: 審計(jì)?？梢愿櫽脩魧?duì)數(shù)據(jù)庫的操作
3: 實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則
4: 實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫相關(guān)完整性規(guī)則。
5: 同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)
6: 自動(dòng)計(jì)算數(shù)據(jù)值，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。

創(chuàng)建觸發(fā)器

   ◆一個(gè)表在相同觸發(fā)時(shí)間的相同觸發(fā)事件，只能創(chuàng)建一個(gè)觸發(fā)器。
   
   創(chuàng)建只有一個(gè)執(zhí)行語句的觸發(fā)器的基本形式如下：  
   CREATE TRIGGER 觸發(fā)器名 BEFORE | AFTER 觸發(fā)事件
   ON 表名 FOR EACH ROW 執(zhí)行語句;
   
   BEFORE 和 AFTER 參數(shù)指定了觸發(fā)器執(zhí)行的時(shí)間，
   “BEFORE”指在觸發(fā)事件之前執(zhí)行觸發(fā)語句，
   “AFTER”表示在觸發(fā)事件之后執(zhí)行觸發(fā)語句。
   
   觸發(fā)事件參數(shù)指觸發(fā)的條件。
   
   FOR EACH ROW表示任何一條記錄上操作滿足觸發(fā)事件都會(huì)觸發(fā)該觸發(fā)器

   執(zhí)行語句，指觸發(fā)器被觸發(fā)后執(zhí)行的程序

    //建表 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 記錄觸發(fā)時(shí)間
    CREATE TABLE trigger_time(insert_time DATETIME);
    
例：創(chuàng)建一個(gè)由INSERT觸發(fā)的觸發(fā)器person_trig.  
    CREATE TRIGGER person_trig
    BEFORE INSERT ON person
    FOR EACH ROW
    INSERT INTO trigger_time VALUES(NOW());


    向表person插入一條數(shù)據(jù)，查看是否觸發(fā)了觸發(fā)器。
    INSERT INTO person VALUE('西門慶','男',26);

    查看觸發(fā)器執(zhí)行結(jié)果
    SELECT * FROM trigger_time; //2017-11-30 21:13:39

創(chuàng)建有多個(gè)執(zhí)行語句的觸發(fā)器

    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刪除一條數(shù)據(jù)，查看是否觸發(fā)了觸發(fā)器。
    DELETE FROM person WHERE NAME = '西門慶'; 
    
    查看觸發(fā)器執(zhí)行結(jié)果
    SELECT * FROM trigger_time; //觸發(fā)了兩次

查看觸發(fā)器

SHOW TRIGGERS;
該語句無法查詢指定的觸發(fā)器，只能查詢所有觸發(fā)器的信息。

觸發(fā)器的使用

1: 觸發(fā)器執(zhí)行的順序是BEFORE 觸發(fā)器、 表操作 （INSERT、UPDATE和 DELETE）和 AFTER 觸發(fā)器。
2: 在激活觸發(fā)器時(shí)，對(duì)觸發(fā)器中的執(zhí)行語句存在一些限制。
   例如，觸發(fā)器中不能包含 START TRANSACTION、COMMIT或 ROLLBACK等關(guān)鍵詞，也不能包含 CALL 語句。
3: 在觸發(fā)器執(zhí)行過程中，任何步驟出錯(cuò)都會(huì)阻止程序向下執(zhí)行。

刪除觸發(fā)器

DROP TRIGGER dept_trig1;