常见数据库对象

子程序

子程序包括存储过程、自定义函数、游标、触发器。可以被编译和存储在数据库中，它具有模块化、重用性、可维护性、可扩展性、安全性等特点。其目的是完成特定的功能，能被程序和客户端工具直接调用。子程序也属于数据库对象，可以被授权能否执行。

创建存储过程

关键语法

create procedure 存储过程名([IN|OUT|INOUT] 参数名 数据类型)
begin
……
end

对存储过程进行参数定义时，多个参数用 ， 分割，共有三种参数类型：IN，OUT，INOUT：

  • IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不会影响调用环境的数据值；
  • OUT:该值可在存储过程内部被改变，同时引起调用环境中数据值的改变；
  • INOUT:调用时指定，兼具 IN 和 OUT 类型参数的特点。

以 begin 和 end 对过程体的开始和结束进行标识。

注意：MySQL 中存储过程默认以 ； 结束符，如果不改变结束符，编译器会把存储过程当成 SQL 语句进行处理，因此编译过程会报错。所以要事先用 DELIMITER // （//可以替换任意字符）声明当前的分隔符，其目的是让编译器把两个 // 之间的内容当作一个存储过程，使用 DELIMITER ; 则恢复结束符为 ;

完整的过程如下：

delimiter //    #声明结束符
create procedure demo1()
begin
select * from city limit 5;
end // #存储过程定义结束

结果：

mysql> delimiter //    #声明结束符
mysql> create procedure demo1()
    -> begin
    -> select * from city limit 5;
    -> end // #存储过程定义结束
Query OK, 0 rows affected (0.00 sec)

存储创建成功使用call demo1() //调用

结果：

mysql> call demo1() //
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District            | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul                | AFG         | Kabol                 |    1780000 |
|  2 | Qandahar         | AFG         | Qandahar          |     237500 |
|  3 | Herat       	        | AFG         | Herat                 |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh                 |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

创建带有一个参数的存储过程，其作用是获取 city 表的前 n 条记录，n 是参数

delimiter //
create procedure demo2(in n int)
begin
select * from city limit n;
end//   #存储过程定义结束

调用demo2()结果：

mysql> call demo2(6) //
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
+----+----------------+-------------+---------------+------------+
6 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

注意：存储过程的默认参数类型是 in

删除存储过程

删除存储过程使用 drop procedure 语句，其语法如下：

drop procedure 存储过程名;

删除存储过程 demo2，其 SQL 语句如下：

drop procedure demo2//

结果：

mysql> drop procedure demo2//
Query OK, 0 rows affected (0.00 sec)

mysql>

自定义函数

一般我们使用的函数是 MySQL 内置函数（已经写好的），直接调用即可完成某个特定功能。但有些功能内置函数并不能实现，所以出现了自定义函数的实现。

创建自定义函数使用 create function 语句，语法如下：

create function 函数名([变量名1 变量类型1，……，变量名n 变量类型n]) returns 数据类型
begin
sql语句;
   return 值;
end;

使用过程例子如下：

  1. 最简单的函数，仅有一条语句，如下所示：

    delimiter ;    #恢复结束符
create function myfunc1() returns int return 123;

    调用 myfunc1() 函数，结果如下：

    select myfunc1();

    输出结果：

    mysql> select myfunc1();
+-----------+
| myfunc1() |
+-----------+
|       123 |
+-----------+
1 row in set (0.00 sec)

mysql>

  2. 自定义一个函数，实现两个数相加，并返回结果，其 SQL 语句如下：

    create function myfunc2(a int,b int) returns int
return a+b;

    调用该函数，其结果如下：

    select myfunc2(2,6);

    mysql> select myfunc2(2,6);
+--------------+
| myfunc2(2,6) |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

  3. 自定义函数，实现日期固定格式输出，其 SQL 语句如下：

    delimiter //
create function dateDemo(fdate datetime)
returns varchar(255)
begin
declare x varchar(255) default '';
set x = date_format(fdate,'%Y年%m月%d日%h时%i分%s秒');
return x;
end//
delimiter ;

    创建此函数时，使用declare x varchar(255) default ' '语句定义了一个varchar类型的变量 x，长度为 255 字节，默认值为空。set 语句表示给变量 x 赋值。 以当前时间为参数，调用dateDemo()函数，其结果如下：

    select dateDemo(now());

    mysql> select dateDemo(now());
+----------------------------+
| dateDemo(now())            |
+----------------------------+
| 2021年11月22日09时21分42秒 |
+----------------------------+
1 row in set (0.00 sec)

  4. 删除自定义函数

    删除自定义函数使用drop function语句，其语法如下：

    drop function 函数名;

    需要注意，删除自定义函数时，函数名后面不能加括号，如下所示：

    drop function dateDemo;

    mysql> drop function dateDemo;
Query OK, 0 rows affected (0.00 sec)

mysql>

视图

视图检查约束

创建视图的时候，我们可以使用 WITH CHECK OPTION 子句进一步限制 DML。

  1. 首先通过 city 为源表建立一个名为 v1 的视图，其 SQL 语句如下：

    create view v1(id,code,population)
as
select id,countrycode,population from city where population > 9000000;

    该视图存放人口数超过 9000000 城市的 ID、城市代码（code）、人口数量（population）。 查看 v1 结果如下：

    select * from v1;

    +------+------+------------+
| id   | code | population |
+------+------+------------+
|  206 | BRA  |    9968485 |
|  939 | IDN  |    9604900 |
| 1024 | IND  |   10500000 |
| 1890 | CHN  |    9696300 |
| 2331 | KOR  |    9981619 |
| 2822 | PAK  |    9269265 |
+------+------+------------+
6 rows in set (0.00 sec)

  2. 然后以 v1 为基础视图建立视图 v2，条件设置为人口数量小于 9600000。其 SQL 语句如下：

    create view v2(id,code,population)
as
select * from v1 where population < 9600000;

    查看 v2 结果如下：

    select * from v2;

    +------+------+------------+
| id   | code | population |
+------+------+------------+
| 2822 | PAK  |    9269265 |
+------+------+------------+
1 row in set (0.00 sec)

  3. 我们往 v2 中插入一条 population>9600000 的记录，其 SQL 语句如下：

    insert into v2 values(null,'PAK',9710000);

    查看结果如下所示：

    select * from v2;

    +------+------+------------+
| id   | code | population |
+------+------+------------+
| 2822 | PAK  |    9269265 |
+------+------+------------+
1 row in set (0.00 sec)

    插入数据正常，但是因为插入的数据不符合 v2 视图原本的查询条件，此时结果中并没有显示出新的数据。

  4. 用建立 v2 视图的方式建立 v3 视图，添加 WITH CHECK OPTION，其 SQL 语句如下：

    create view v3(id,code,population)
as
select * from v1 where population < 9600000
with check option;

    查看结果如下：

    select * from v3;

    +------+------+------------+
| id   | code | population |
+------+------+------------+
| 2822 | PAK  |    9269265 |
+------+------+------------+
1 row in set (0.00 sec)

    和 v2 中插入数据一样，我们往 v3 中插入一条相同的记录，SQL 语句如下：

    insert into v3 values(null,'PAK',9710000);

    输出结果：

    ERROR 1369 (HY000): CHECK OPTION failed 'world.v3'

    错误原因在于，声明 v3 时添加了 WITH CHECK OPTION 约束，对 v3 进行 DML 操作时需以 v3 的 where 条件为 DML 限制条件

对于视图 DML 检查约束，还可以用 LOCAL/ CASCADED 做更精细的控制。

WITH LOCAL CHECK OPTION：对如此声明的视图实施 DML 操作，只考虑当前视图的查询条件为 DML 操作限制。当然，若在视图创建链路上还有基础视图，且基础视图也做了检查约束，DML 也要受相应的限制。

WITH CASCADED CHECK OPTION（CASCADED 为默认，可以省略）：对如此声明的视图实施 DML 操作，不仅要满足当前视图的查询条件，也要满足当前视图创建链路上所有基础视图的查询条件，即便基础视图没有声明 WITH CHECK OPTION。

序列

序列的基本操作

  1. 创建序列表，其 SQL 语句如下：

    delimiter $    #设置结束符
create table sequence(    #序列表名
seq_name varchar(20) not null,    #序列名
current_value int not null,    #序列当前值
increment_value int not null default 1,#序列步长，并设置默认值为1
primary key(seq_name));    #设置序列表名为主键
$    #结束SQL语句

  2. 在创建好的序列表中插入两条记录，如下所示：

    insert into sequence values('seq_num1',0,1)$    #注意此时我没有恢复结束符，所以结束符仍然#是$，本小节后面的演示代码结束符均是$，直到给出说明。序列名seq_num1，当前值为0，步长为1。

    insert into sequence values('seq_num2',0,2)$#序列名seq_num2，当前值为0，步长为2。

    输出结果：

    select * from sequence $

    +----------+---------------+-----------------+
| seq_name | current_value | increment_value |
+----------+---------------+-----------------+
| seq_num1 |             0 |               1 |
| seq_num2 |             0 |               2 |
+----------+---------------+-----------------+
2 rows in set (0.00 sec)

  3. 实现自增，需要用到序列的当前值、下一个值和步长等数据。接下来写一个函数来获取序列当前值，其 SQL 语句如下：

    create function current_value(val_seq_name varchar(20)) returns int(11)
begin
declare val int;
set val = 0;
select current_value into val from sequence where seq_name = val_seq_name;
return val;
end; $

    该函数中最重要的一句 select current_value into val from sequence where seq_name = val_seq_name;意思是，通过传入的参数（序列名）查询出序列，并把序列名赋值给 val 变量。

  4. 调用函数查看序列的当前值，其 SQL 语句如下：

    select current_value('seq_num1')$

    +---------------------------+
| current_value('seq_num1') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

  5. 创建函数获取序列的下一个值，序列的下一个值是由当前值加步长得到。其 SQL 语句如下：

    create function next_value(v_seq_name varchar(20)) returns int
begin
update sequence set current_value = current_value + increment_value where seq_name = v_seq_name;
return current_value(v_seq_name);
end;
$

    解析：通过当前值和步长得到了下一个值后，需要更新当前值，所以函数返回的时候调用了current_value()函数。

  6. 调用获取下一个值函数，查看结果：

    select next_value('seq_num1')$

    +-------------------------+
| next_vallue('seq_num1') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

  7. 创建一张新表来测试序列功能，其 SQL 语句如下：

    create table test_seq(
id int primary key,
name varchar(20) not null
)$

  8. 先在序列表中新增一个序列，当前值设置为 100，步长为 10，其 SQL 语句如下：

    insert into sequence values('seq_num3',100,10)$

  9. 序列要作用到 test_seq 表中，需要触发器来实现，即自动调用 next_value 函数为新行的 id 赋值，SQL 语句如下：

    create trigger tri_seq before insert on test_seq for each row
begin
set new.id = next_value('seq_num3');
end;
$

  10. 接下来尝试往 test_seq 表中插入 3 条记录，其 SQL 语句如下：

    insert into test_seq(name) values('test1')$
insert into test_seq(name) values('test2')$
insert into test_seq(name) values('test3')$

    3 条不指定 id 值的语句执行完毕。

  11. 查询 test_seq 表的所有数据，查看 id 是否按照设定的方式进行自增长：

    select * from test_seq$

    +-----+-------+
| id  | name  |
+-----+-------+
| 110 | test1 |
| 120 | test2 |
| 130 | test3 |
+-----+-------+
3 rows in set (0.00 sec)

    这里简单的说明一下，因为触发器是在插入之前执行的，所以序列的值也是在插入之前改变的，所以此时插入表的第一个值就是 110。

索引

在 MySQL 中常用的索引可以分为三类，分别是：普通索引、唯一索引、联合索引。

普通索引

普通索引使用关键字 INDEX 定义，根据建立索引的时机不同，书写方式有细微差别。分为以下 3 种情况：

  • 创建表的时候创建索引；
  • 创建表后创建索引；
  • 修改表的时候添加索引。

唯一索引

唯一索引不仅加速查找，还具有约束性。

  • 主键索引 primary key
  • 唯一键索引 unique

联合索引

联合索引即为索引同时设置多个字段。

  • primary key(id,name)，联合主键索引
  • index(id,name)，联合普通索引
索引基本操作

  1. 在建表的时候创建索引。其语法如下：

    index 索引名(字段名)

    创建一张 teacher 表，该表有 id、name、age 三个字段，给字段 id 创建索引。其 SQL 语句如下：

    delimiter ;
create table teacher(
id int,
name varchar(20),
age int,
index ix_id(id)#给id创建的索引名为ix_id
);

  2. 给已经存在的表中某字段添加索引，其语法如下：

    create index 索引名 on 表名(字段名);

    给 teacher 表的 name 字段添加索引，其 SQL 语句如下：

    create index ix_name on teacher(name);

  3. 修改表时创建索引，其语法如下：

    alter table 表名 add index 索引名(字段名)

    修改 teacher 表，给 age 字段添加索引，其 SQL 语句如下：

    alter table teacher add index ix_age(age);

    以上 3 例仅做语法示范，实际上，不可能给每个字段都加索引。

  4. 查看索引，借助表信息查看表中是否存在索引，其语法如下：

    show create table 表名;

    查看 teacher 表中的索引，其 SQL 语句如下：

    show create table teacher;

    +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

  5. 删除索引，使用的关键字是drop index…on…，其语法如下：

    drop index 索引名 on 表名

    删除 teacher 表中 age 字段的索引，其 SQL 语句如下：

    drop index ix_id on teacher;
索引基本体验

  1. 创建一张员工表（employee），包含编号（id）、姓名（name）、年龄（age）三个字段，其 SQL 语句如下：

    create table employee(
id int,
name varchar(20),
age int
);

  2. 创建存储过程，实现往 employee 表中批量添加数据，其 SQL 语句如下：

    delimiter $
create procedure auto_employee()
begin
declare i int default 1;
while(i<100000)do
insert into employee values(i,'emp_demo',20);
set i = i + 1;
end while;
end
$

  3. 恢复结束符并调用存储过程，其 SQL 语句如下：

    delimiter ;
call auto_employee();

    输出结果：

    mysql> call auto_employee();
Query OK, 1 row affected (2 min 53.31 sec)

  4. 在没有建立索引的情况下，搜索一条不存在的记录，其 SQL 语句如下：

    select * from employee where id = 200000;

    输出结果：

    mysql> select * from employee where id = 200000;
Empty set (0.03 sec)

  5. 在已有大量数据的基础上创建索引会比较慢。接下来我们给 id 创建索引，看看耗时情况，其 SQL 语句如下：

    create index ix_id on employee(id);

  6. 接下来，在建立好索引后，执行之前的 SQL 语句，查看结果如下：

    select * from employee where id = 200000;

    输出结果：

    mysql> select * from employee where id = 200000;
Empty set (0.00 sec)

同样的条件进行查询，速度明显提升。其原理是，没有索引时会发生全表扫面，有索引时，会在表外单独为索引数据建立 B+ 树之类的数据结构；根据 id 查询时，会先在树上进行高效查询，获取对应数据行的地址进而提取数据行中的列。

索引注意事项
  1. 位数越小的数据类型越好。因为位数越小的数据类型在磁盘、内存和 CPU 缓存中所需要的空间更少，处理起来更快；
  2. 越简单的数据类型越好。整型数据比起字符，处理开销更小，因为字符串的大小比较更复杂。在 MySQL 中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；或是用整型数据类型存储 IP 地址等；
  3. 尽量避免使用 NULL。应该指定列为 NOT NULL，除非你想存储 NULL。在 MySQL 中，含有空值的列很难进行查询优化，NULL 使得索引、索引的统计信息以及比较运算更加复杂。应该用 0、一个特殊的值或者一个空串代替 NULL。
  4. 列中包含 NULL 值将导致引擎放弃使用索引而进行全表扫描。

总结

  1. 子程序包括存储过程、自定义函数、游标、触发器。可以被编译和存储在数据库中，它具有模块化、重用性、可维护性、可扩展性、安全性等特点；
  2. 存储过程是一种存储复杂程序，方便外部程序调用的数据库对象。是为了完成某个特定功能的 SQL 语句集合，创建存储过程使用的关键字是 create procedure ；
  3. 自定义函数是一种对 MySQL 的扩展，其用法和内置函数相同，创建自定义函数使用的关键字是 create function ；
  4. 游标是一个存储在 MySQL 服务器上面的数据库查询机制，类似于数组的下标。使用游标后，可以根据需要按照特定要求取出数据，创建游标使用的关键字是 declare…cursor ，使用游标前需要先打开；
  5. 触发器（TRIGGER）是一种特殊的存储过程，它在插入、修改或删除表中的数据时触发执行，拥有更精细、更复杂的数据控制能力。创建触发器使用的关键字是 create trigger ；
  6. 视图是从一个或多个表中 “糅合” 出来的虚拟表。一个视图并不包含真实的数据，它提供了另一个视角去查看或改变表中的数据；
  7. 序列就是一组有特定变化规律的整数，其最主要的用途是确保主键数据的唯一性。序列是一个数据库对象，独立于表进行存储，可以为多个表使用；MySQL 中并不支持直接的序列对象创建，而是利用表、函数和触发器来实现类似功能；
  8. 索引在 MySQL 中又叫作 “键” ，英文名 key ，是存储引擎用于快速找到记录的一种数据结构，索引对于性能的提升非常关键。

另外，通过本章的学习，读者朋友们可以进行扩展学习以下内容：

  1. 利用游标循环读取多条记录，或指定记录；
  2. 学习其它数据库（支持序列对象）中序列对象的创建和使用方法；

索引.png