索引，触发器，存储过程和存储函数及游标的基本使用和了解

索引

口述并理解

1. 什么是索引

• 索引：类似目录，给一张表添加了一个目录。
• 索引是一个单独的、物理的数据结构，是某个表中一列或若干列值的集合和相应的指向物理标识这些值的数据页的逻辑指针清单。索引依赖于表建立的，提供了编排表中数据的内部方法。
• 目的是为了数据的查询效率。

2. 索引的作用有哪些

1. 为了提高查询效率
2. 通过快速定位数据的方法，减少磁盘I/O操作

3. 索引的分类有哪些

• 普通索引：

  不需要添加任何限制条件，可以创建在任何数据类型中，由字段本身的完整性约束决定

• 唯一索引：

  使用 unique 参数进行设置，该值必须是唯一的。（主键是一种特殊的唯一索引）

• 全文索引：

  使用 fulltext 参数进行设置，只能创建在 char,varchar 或者 text 类型的字段 上（适用于查询数 据量较大的字符串类型的字符时）

• 单列索引:

  在表中单个字段上创建，只能根据该字段进行索引查询，只要保证该索引只对应一个字段即 可。多列索引在表中多个字段上创建，可根据多个字段进行索引查询（注意：只有查询条件中使用了这些 字段中的第一个字段时，索引才会被使用）。例如：id ，name， age，查询条件使用了 id 字段时该索引 才会被使用.

• 空间索引（用的比较少）:

  使用 spatial 参数进行设置，只能建立在空间数据类型上。（geometry、 point、linestring 和 polygon 等）

4. 创建索引的原则有哪些

1. 选择唯一索引 因为唯一索引的值是唯一的，可快速通过该索引来确定某条记录 例如：人–>身份证号 学 生–>学号

2. 为经常需要排序、分组和联合操作的字段建立索引 频繁使用 order by、group by、distinct 和 union 等 来操作字段时。

3. 经常作为查询条件的字段建立索引，WHERE 条件经常使用的字段可以创建索引。可以给外键创建索引。

4. 限制索引的数目: 索引的数目并不是越多越好，每个索引都要占用磁盘空间，修改表时，对索引的重构和更新比较麻烦。

5. 尽量使用数据量少的索引

6. 尽量使用前缀来索引检索值很长时，比如 text、blog，只检索前面的若干个字符

7. 不使用或使用频率低的，应尽快删除

   注意

   不适合建立索引的情况：

   1. 表很小
   2. 字段不经常出现在WHERE子句中
   3. 每次访问的数据量大于记录总数的2%–4%
   4. 字段经常更新

动手做

1. 如何创建索引

语法格式：

CREATE INDEX 索引名称 ON 表名（字段1，字段2）;

-- 任务 给员工表的字段（部门号）创建索引。
CREATE INDEX index_emp ON emp(deptno);

2. 如何删除索引

语法格式：

DROP INDEX 索引名称 ON 表名;

-- 任务 删除索引index_emp。
DROP INDEX index_emp on emp;

触发器

1. 什么是触发器

• 触发器是对表进行插入、更新、删除的时候会自动执行的特殊存储过程。简单的说，就是一张表发生了某件事 （插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行。 触发器一般用在check约 束更加复杂的约束上面。例如在执行update、insert、delete这些操作的时候，系统会自动调用执行该表上对 应的触发器。主要是为了保证数据的完整性，起到约束的作用。

• MySQL中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句 而激发，这些语句有create、alter、drop语句。

• DML触发器分为：

  1. after触发器（之后触发） (可使用如下)

     其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。

     • insert触发器

     • update触发器

     • delete触发器

  2. before触发器 （之前触发）

  2. 如何创建一个触发器

  语法格式：

  DELIMITER $$

  CREATE TRIGGER 数据库名称 . 触发器名称 AFTER|BEFORE INSERT|UPDATE|DELETE

  ON 数据库名称 . 对哪一 张表进行监控

  FOR EACH ROW

  BEGIN

  这里写对应的SQL

  END$$

  DELIMITER ;

  注意：

  MySQL中需要通过delimiter声明一个$$是用户定义的结束符，表示遇到$$才表示程序运行结束，否则 遇到分号程序就结束了。通常这个结束符号可以是一些特殊的符号。另外应避免使用反斜杠,因为它是转义字 符。

• 例如：创建一个触发器,当删除数据表t_student数据时，触发器被触发向数据表t_time里插入当前时间(一旦有满足条 件的删除操作，就会执行BEGIN和END中的语句)

  -- 创建一个before触发器
  -- 创建一个触发器,当删除数据表t_student数据时，触发器被触发向数据表t_time里插入当前时间(一旦有满足条件的删除操作，就会执行BEGIN和END中的语句)
  DELIMITER $$
  CREATE TRIGGER trig_emp1 BEFORE DELETE 
    ON emp1
    FOR EACH ROW
    BEGIN
      INSERT INTO t_time(tdate) VALUES(NOW());
    END$$
  DELIMITER;
  -- 删除操作，该操作执行之前，触发器会被触发
  DELETE FROM emp1 WHERE empno=7369;
  

  注意：慎用触发器，尽量少使用触发器，不建议使用。触发器是针对每一行的；对增删改非常频繁的表上切记 不要使用触发器，因为它会非常消耗资源。

存储过程和存储函数

1. 什么是存储过程

• 存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并 用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。
• 一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不 同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。
• 存储过程是数据库中的一个重要功能，存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成 功，就可以随时被调用，完成指定的功能操作。
• 使用存储过程不仅可以提高数据库的访问效率，同时也可以提高数据库使用的安全性。

2. 什么是存储函数（自定义函数）

• 存储函数和存储过程一样，也是 SQL 语句组成的代码块。
• 存储函数可以有输入参数，并且可以直接调用，不需要call语句，且必须有一条包含RETURN语句。

3. 存储过程与存储函数的区别

1. 存储函数和存储过程统称为存储例程(store routine),存储函数的限制比较多,例如不能用临时表,只能用表变量,而 存储过程的限制较少,存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。

2. 返回值不同：

   存储函数必须有返回值,且仅返回一个结果值;

   存储过程可以没有返回值,但是能返回结果集(out,inout).

3. 调用时的不同：

   存储函数嵌入在SQL中使用,可以使用select 存储函数名(变量值) 来调用;

   存储过程通过call语句调用 call 存储过程名;

4. 参数不同：

   存储函数的参数类型类似于IN参数

   存储过程的参数类型有三种:

   (1) in 数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

   (2) out 只允许过程内部使用(不用外部数据),

   ​ 给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

   (3) inout 外部可以在内部使用,内部修改的也可以给外部使用,典型的引用传递,只能传递变量

4. 如何创建及调用一个存储过程

创建存储过程的语法：

CREATE PROCEDURE 存储过程名称(参数列表)

BEGIN

SQL语句

END ;

调用存储过程的语法：

call 存储过程名()

备注：

1. 可以不带参数列表。也可以带参数列表，可以使用IN，OUT,INOUT参数。
2. ：MySQL中需要通过delimiter声明一个$$是用户定义的结束符，表示遇到$$才表示程序运行结束，否则 遇到分号程序就结束了。通常这个结束符号可以是一些特殊的符号。另外应避免使用反斜杠,因为它是转义字 符。

-- 创建一个存储过程查看所有员工信息。
DELIMITER $$
CREATE PROCEDURE proc_emp()
READS SQL DATA 
BEGIN
  SELECT * FROM emp;
END
$$
-- 调用存储过程
CALL proc_emp();

-- 创建一个带参数的存储过程，根据员工编号查询员工的年薪.
DELIMITER $$
CREATE PROCEDURE proc_getEmpYearSal(IN eno int,OUT yearSal double(7,2))
READS SQL DATA
BEGIN
SELECT sal*12 INTO yearSal FROM emp WHERE empno=eno;
END
$$
-- 调用存储过程
SET @empSal=0;
call proc_getEmpYearSal(7369,@empSal);
SELECT @empSal;

备注：@empSal是定义的用户变量，SET @empSal=0 表示设置初始值。

5. 如何常见及调用一个存储函数

创建存储函数的语法：

CRETAE FUNCTION 函数名()

RETURNS 返回类型

BEGIN [DECLARE 变量 变量类型 ;] – 如果需要定义局部变量就需要先声明。

sql 语句集合;

RETURN 变量值;

END;

调用存储函数的语法：

SELECT 函数名（参数）

备注

在函数中必须要有RETURN返回值；

而且MySQL中的存储函数的参数只有IN类型，默认就是IN类型。

-- 创建一个带参数的存储函数，根据员工编号查询员工的年薪.
DELIMITER $$
CREATE FUNCTION func_getEmpYearSal(eno int)
RETURNS double
READS SQL DATA
BEGIN
DECLARE yearSal double(7,2);
SELECT sal*12 INTO yearSal FROM emp WHERE empno=eno;
RETURN yearSal;
END
$$

-- 调用存储函数的语法： SELECT 函数名（参数）
SELECT func_getEmpYearSal(7369);

6. 如何删除存储过程

语法：

DROP PROCEDURE [IF EXISTS] 存储过程名称

注意：它是不带括号的。

-- 删除存储过程
DROP PROCEDURE proc_getEmpYearSal;

7. 如何删除存储函数

语法：

DROP FUNCTION [IF EXISTS] 存储函数名称;

-- 删除存储函数
DROP FUNCTION func_getEmpYearSal;

游标

1. 什么是游标

• MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象)，它可以用来存储 SELECT语句查询到的结果集，这个结果集可以包含多行数据，从而使我们可以使用迭代的方法从游标中依次取 出每行数据。

2. 使用游标的四个步骤：

1. 声明游标

• 游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。

  游标必须始终与 SELECT语句相关联。

  语法： DECLARE cursor_name CURSOR FOR select_statement;

2. 打开游标

• 使用OPEN打开游标，只有先打开游标才能读取数据

  语法： OPEN cursor_name;

3. 读取游标

• 使用FETCH语句来检索游标指向的一行数据，并将游标移动到结果集中的下一行。

  语法：FETCH cursor_name INTO var_name;

4. 关闭游标

• 使用CLOSE语句关闭游标。

  语法：CLOSE cursor_name;