1、索引

（1）数据库中使用索引，目的在于提高查询效率，本质上就是针对设置了索引的属性列按照特定顺序排序，便于查询

以一本书为例，索引就像书的目录一样，要找到第8章：如果没有目录，需要从头开始，一页一页翻到第8章；如果有了目录，就可以按照目录的标识，翻到对应的页码

创建索引的语法：CREATE INDEX 索引名称 ON 表名(属性名称)

例子： CREATE INDEX index_a ON test_a(name);

也可以在使用CREATE语句创建数据表时，直接创建索引；例子：

CREATE TABLE test

(

id INT,

name CHAR(10),

INDEX index_test(id)

);

或者在使用ALTER语句更新数据表时，新增索引（例子：ALTER TABLE test ADD INDEX index_test(id);）

（2）也可以在使用CREATE语句创建数据表时，直接创建索引；例子：

CREATE TABLE test

(

id INT,

name CHAR(10),

INDEX index_test(id)

);

(3)或者在使用ALTER语句更新数据表时，新增索引（例子：ALTER TABLE test ADD INDEX index_test(id);）

需要注意的是数据表在定义时，如果指定了主键，会自动在主键上创建索引

同一个数据表上可以定义多个索引，但实际上在查询时，也只能使用一个索引：

如果数据表中有主键，则按照主键上的索引查询；如果没有主键，会选择限制最为严格的索引

要查看某个数据表上创建的索引，可以使用SHOW命令

语法：SHOW INDEX FROM 表名                                   例子：SHOW INDEX FROM test;

要删除索引，可以使用DROP命令                                  语法：DROP INDEX 索引名称 ON 表名

例子：DROP INDEX index_test ON test;

（4）索引的优势是提高查询效率

但同样也有缺点：索引的使用会降低更新表的速度；如果要对数据表进行INSERT、UPDATE、DELETE等更新操作，数据库不仅仅要保存更新之后的数据，也需要更新对应的索引，因此会导致更新效率降低

索引的使用会创建对应的索引文件，占用磁盘空间；一般这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的膨胀速度会很快，占用过多的磁盘空间

2、视图：一个虚拟的表，在数据库中并不实际存在

（1）视图是由查询数据库中的数据表产生，因此视图的内容由查询定义，实际上也可以把视图理解为保存查询结果的虚拟表

虽然视图是一个虚拟的表，但是它和像真实的数据表一样使用

使用视图的原因主要有：

安全性更强，通过视图的使用可以将一些数据隐藏，仅将视图中的数据提供给用户使用

提高查询性能，将经常访问的数据提前保存在视图中，后续的数据查询可以直接在视图上进行

使复杂的查询易于理解和使用，将问题进行分解，利用多个视图获取数据并最终联合起来就能得到需要的结果

（2）可以使用CREATE语句来创建视图

语法：CREATE VIEW 视图名称 AS SELECT语句；例子：

CREATE VIEW view_test

AS

SELECT * FROM table1 WHERE id=1;

创建视图时，需要遵循的规则：视图也是一个表（虚拟表），因此不能与已有的表或视图名称重复

如果要查看数据库上的视图，可以使用命令：SHOW TABLE STATUS WHERE COMMENT='VIEW';

（3）可以使用ALTER或者CREATE OR REPLACE语句更新视图

语法： ALTER VIEW 视图名称 AS SELECT语句   语法： CREATE OR REPLACE VIEW 视图名称 AS SELECT语句

例子：ALTER VIEW view_test AS SELECT * FROM table1 WHERE id=2;

CREATE OR REPLACE VIEW view_test AS SELECT * FROM table1 WHERE id=2;

可以使用DROP语句删除视图；语法：DROP VIEW 视图名称              例子：DROP VIEW view_test;

（4）在实际应用中，视图的另外一个主要用途：通过视图来更新真实表

因为视图本身是虚拟表，实际上是对原表的引用，因此在视图中做的数据更新操作会直接反馈到原表中

例子：UPDATE view_test SET name=‘zhangsan';

将视图view_test中的name更新为’zhangsan’后，原表中对应的数据也会相应的更新

同样因为这一特性，在原表中做的数据更新，也会反馈到视图中

*需要注意的是，通过视图来更新真实表这一特性是有限制的：

1、如果视图中的属性来自于两个以上的数据表，则此视图不允许更新

2、如果视图中的属性来自于函数结果，则此视图不允许更新

3、如果视图定义中含有GROUP BY子句，则此视图不允许更新

4、如果视图定义中含有DISTINCT语句，则此视图不允许更新

5、一个不允许更新的视图上定义的视图也不允许更新

3、触发器：是一种与表操作有关的数据库对象，当触发器所在的表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行

（1）使用CREATE语句来创建触发器

语法：CREATE TRIGGER 触发器名称 触发器时机 触发事件 ON 表名

          FOR EACH ROW

      触发器程序体

触发器时机：可以指定BEFORE或AFTER                       触发事件：可以指定INSERT、UPDATE或DELETE

一共可以创建6种触发器：

BEFORE INSERT-插入前触发      BEFORE UPDATE-更新前触发      BEFORE DELETE-删除前触发

AFTER INSERT-插入后触发         AFTER UPDATE-更新后触发         AFTER DELETE-删除后触发

（2）注意在创建触发器时，限制：

1、不能同时在一个表上建立2个相同类型的触发器，因此一个表上最多建立6个触发器

2、触发器的程序体中不能包含对触发器所在表做的操作

3、MySQL限制触发器中不能使用返回结果集的语句，例如SELECT

（3）以AFTER INSERT触发器为例：

CREATE TRIGGER trig1 AFTER INSERT ON student FOR EACH ROW INSERT INTO class VALUES(1,'a');

在上面的触发器trig1创建完成后，向student表中执行插入操作会触发触发器的执行，在插入操作完成后，执行触发器程序体中的INSERT INTO class VALUES(1,‘a’)语句

触发器的程序体也可以包含在BEGIN和END中，需要注意程序体中的每一条SQL语句都要使用分号结尾；例子：

CREATE TRIGGER trig1 AFTER INSERT ON student FOR EACH ROW

BEGIN

INSERT INTO class VALUES(1,'a');

END;

（4）可以使用SHOW命令查看触发器        语法：SHOW TRIGGERS

触发器创建后，在MySQL数据库中不能修改，只能删除重建

使用DROP命令删除触发器                         语法：DROP TRIGGER 触发器名称

触发器的优点：自动被事件触发，不需要主动调用；能够通过触发器的使用，确保数据的完整性

4、函数：在数据库使用过程中，MySQL自带的函数可能不能完成我们的业务需求，这个时候就需要自定义函数

1、在自定义函数前，需要先确认创建函数功能是否开启                 语法： SHOW VARIABLES LIKE '%func%';

2、如果返回的log_bin_trust_function_creators变量值为OFF，需要将其开启

语法：SET GLOBAL log_bin_trust_function_creators=1;

3、开始创建函数功能后，就能够进行自定义函数的创建

语法：CREATE FUNCTION 函数名称(参数1 数据类型,参数2 数据类型,…..)

      RETURNS 返回值类型

      函数体

4、函数体可以是一条SQL语句，也可以是多条SQL语句；函数体以BEGIN开始，END结束。例子：

创建一个函数，将’2016-11-03 10:10:10’这样格式的datetime时间转化为’2016年11月3日10时10分10秒’

CREATE FUNCTION get_date(d datetime)

RETURNS VARCHAR(50)

BEGIN

RETURN DATE_FORMAT(d,'%Y年%m月%d日 %h时%i分%s秒');

END;

5、函数定义完成后，使用SELECT语句调用:SELECT get_date(‘2016-11-03 10:10:10’);

6、也可以给自定义的函数传递其他的值来完成格式转换。例子：SELECT get_date(now()) AS '当前时间';

7、查看数据库中所有函数的基本信息                                 语法：SHOW FUNCTION STATUS;

     查看某个具体函数                                                           语法：SHOW CREATE FUNCTION 函数名称;

自定义函数的函数体不能被修改，当函数不再被使用时，使用DROP语句删除。语法：DROP FUNCTION 函数名称;

8、自定义函数使用时，有两个关键点：

参数：自定义函数可以有参数，也可以没有参数

返回值：自定义函数必须有并且只能有一个返回值

5、存储过程：是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户可以通过存储过程的名称来调用并执行它

1、存储过程的优点：

增强了SQL语言的功能和灵活性，可以用控制流语句编写，完成负责的判断和运算

实现较快的执行速度，因为存储过程是经过预编译后存储在数据库中的，调用并执行存储过程不需要再次编译

减少网络传输量，因为在调用执行存储过程时，只需要将存储过程的名称和参数进行传送

增强了数据的安全性，用户需要知道的仅仅是存储过程的名称和参数，避免了对数据的直接操作

需要注意的是MySQL在5.0以前并不支持存储过程

2、使用CREATE语句创建存储过程

（1）语法：CREATE PROCEDURE 存储过程名称(参数列表)

      程序体

存储过程的参数列表中每一个参数由3部分构成：输入输出类型、参数名称、参数类型

输入输出类型有3种：IN：输入参数、OUT：输出参数、INOUT：既可以是输入，也可以是输出

存储过程的程序体以BEGIN开始，END结束，可以包含一组语句集。例子：

（2）创建一个存储过程，能够根据输入的部门编号，统计出部门员工人数

CREATE PROCEDURE pro(IN dno INT,OUT count_num INT)

BEGIN

SELECT COUNT(empno) INTO count_num FROM hzdl_emp WHERE deptno=dno;

END;

（3）存储过程创建完成后，使用CALL来调用存储过程。语法：CALL 存储过程名称(参数列表);

存储过程调用时，需要传递具体数据给输入参数，使用变量接收输出参数

例子：统计编号为1的部门员工人数                        CALL pro(1,@cn);     SELECT @cn;

（4）查看数据库中所有存储过程基本信息           语法：SHOW PROCEDURE STATUS;

         查看某个具体存储过程                                 语法：SHOW CREATE PROCEDURE 存储过程名称

存储过程的程序体也不能被修改，当存储过程不再被使用时，使用DROP语句删除

语法：DROP PROCEDURE 存储过程名称;

6、触发器、函数、存储过程的区别：

触发器不能被主动调用，只能通过事件触发，而函数和存储过程都需要主动调用执行

触发器和函数中不能包含返回结果集的语句，例如SELECT，而存储过程可以

触发器没有返回值，函数必须定义返回值且只能返回一个值，存储过程可以有返回值，也可以没有返回值，还可以返回多个值

函数可以作为SQL语句的一部分来调用，存储过程作为独立的部分来执行，不能用于SQL语句中

7、事务处理：事务是一种机制，是一种操作序列，包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行，事务是一个不可分割的工作逻辑单元

事务机制主要用于维护数据库的完整性、一致性。典型事务应用场景：

在银行转账交易过程中，要从A账户向B账户转1000元（在对应的数据库中，要依次使用UPDATE语句分别更新A账户、B账户的对应信息；成功更新A账户信息后，在更新B账户信息时，可能由于某种原因导致B账户信息更新失败。此时，就造成了数据库中数据出现不一致问题）

如果增加了事务机制，就可以让两次更新操作要么全部执行，要么全部不执行，避免了数据不一致问题的产生。需要注意的是MySQL只有InnoDB类型的数据库才支持事务处理。将多条SQL语句放到一个事务中，需要使用下面的命令：

START TRANSACTION

COMMIT

SAVEPOINT

ROLLBACK

首先开启事务，使用命令：START TRANSACTION

执行SQL语句，执行过程中建立保存节点，使用命令：SAVEPOINT 节点名称判断SQL语句是否执行成功

如果全部成功则提交事务，使用命令：COMMIT

如果出现异常则不提交事务，执行回滚操作，使用命令：ROLLBACK或ROLLBACK TO 保存节点