数据库学习之六

Santa
2021-11-22 / 0 评论 / 1 点赞 / 23 阅读 / 10,998 字
温馨提示:
本文最后更新于 2021-11-22,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

常见数据库对象

子程序

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

创建存储过程

关键语法

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

对存储过程进行参数定义时,多个参数用 分割,共有三种参数类型:INOUTINOUT

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

beginend 对过程体的开始和结束进行标识。

注意: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

0

评论