MySQL学习笔记 08 - 存储过程、触发器、索引、视图

最新推荐文章于 2021-12-16 10:30:10 发布

法随

最新推荐文章于 2021-12-16 10:30:10 发布

阅读量662

点赞数

分类专栏： Java持久化技术文章标签：数据库 sql mysql 触发器

本文链接：https://blog.csdn.net/Class013/article/details/105875051

版权

Java持久化技术专栏收录该内容

9 篇文章

订阅专栏

本文深入讲解数据库中的存储过程、触发器、索引和视图等高级特性，探讨它们的定义、使用方法及优缺点，帮助读者提升数据库管理和优化技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、存储过程

如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句集编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。
存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程类似于JAVA语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT 三种类型。IN类型的参数表示接收调用者传入的数据，OUT类型的参数表示向调用者返回数据，INOUT类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据。
现以如下管理系统为例，该系统的数据库名称为user_manager，在user_manager数据库中创建了用户表，命名为user_info。用户表中记录用户名和密码。

创建数据库和表

CREATE DATABASE user_manager DEFAULT CHARSET utf8mb4;
	CREATE TABLE user_info (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
	username VARCHAR (20) NOT NULL COMMENT '用户名称',
	userpass VARCHAR (20) NOT NULL COMMENT '用户密码'
);

1、存储过程的定义

创建存储过程的语法格式

create procedure 存储过程名（参数1，参数2，…）
	begin
		存储过程语句块;
	end;

语法解析：

存储过程的参数分为in，out，inout三种类型。
in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。
out代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。
inout代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将inout参数的计算结果返回给调用程序。
存储过程中的语句必须包含在begin和end之间。

实例：定义注册用户的存储过程

CREATE PROCEDURE register(IN username VARCHAR(20),IN userpass VARCHAR(20),OUT id INT)
	BEGIN
		 INSERT INTO user_info(username,userpass)VALUES(username,userpass);
 		SELECT LAST_INSERT_ID() INTO id;
	END;

说明：

本任务使用CREATE PROCEDURE命令定义了一个存储过程，该存储过程的名称为register，其功能是实现用户注册。
register存储过程中包含了三个参数，username表示要注册用户名，userpass表示要注册的密码，id是注册后生成的主键。其中username和userpass是IN类型的参数，表示接收调用者传入的参数，id是OUT类型的参数，表示向调用者输出的数据。
在begin和end块中实现用户注册的业务。
begin和end块中的INSERT语句用于将传入的用户名和密码插入到用户信息表中。
“SELECT LAST_INSERT_ID() INTO id;”表示获取最后生成的自增长的主键值，并将主键值赋值给id。LAST_INSERT_ID()是系统函数，用于获取最后生成的自增长的值。id是OUT类型的参数，因此该值将输出给调用者。

2、存储过程的调用

2.1、调用存储过程的语法格式：

call 存储过程名()

调用register存储过程前，需要先定义一个int类型的变量，该变量用于接收register存储过程输出的id。
在MySQL中变量分为用户变量和系统变量，用户变量用@开头，系统变量用@@开头。定义变量使用Set语句，例如：SET @temp = 0;表示定义了变量@temp并初始化为0。
存储过程调用举例：

SET @userId=0;
	CALL register('林冲', '123456',@userId);
	SELECT @userId;

说明：

首先使用Set语句定义变量@userId，@userId用于保持register存储过程输出的用户主键值。
使用CALL语句调用了存储过程register，并向register存储过程传入了用户名“林冲”和密码“123456”，而且使用变量@userId 接收输出的用户主键值。
最后使用SELECT @userId 显示生成的主键值。

2.2、使用JDBC调用存储过程。

在实际的开发中，存储过程并不是在MySQL中通过CALL语句调用的，而是在Java程序中通过JDBC调用的。JDBC中提供了CallableStatement接口，该接口用于调用存储过程。CallableStatement接口是PreparedStatement接口的子接口，PreparedStatement接口是Statement接口的子接口。

二、触发器

user_info表中存储了用户名和密码，但用户的属性中还应包括年龄、电话、性别等，这些属性是不可或缺的。由于用户每次登陆都未必使用年龄、电话、性别属性，多数用户是在完善个人信息时使用这些属性，为此将用户信息设计成user_info表和user_details表，user_info表中存储用户每次登陆都需要的属性，user_details表中存储用户每次登陆不需要的属性，并将两表设置为一对一的关系。user_details表的参考代码如下：

CREATE TABLE user_details (
		id INT NOT NULL PRIMARY KEY COMMENT 'id',
		age INT COMMENT '年龄',
		tel VARCHAR(20) COMMENT '用户电话',
		gender VARCHAR(20) COMMENT '用户性别'
	);	 
	ALTER TABLE user_details ADD CONSTRAINT fk_UserInfo_UserDetails_Id FOREIGN KEY (id) REFERENCES user_info(id);

1、触发器

触发器（trigger）是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加、修改、删除之前或者之后自动执行的存储过程。
使用触发器实现注册业务时，JDBC可以对user_info表实现添加用户信息，添加成功后自动激活触发器，由触发器向user_details表中添加用户详细信息，完成注册业务。
触发器具有以下特点：

与表相关联：触发器定义在特定的表上，这个表称为触发器表。
自动激活触发器：当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。
不能直接调用：与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。
作为事务的一部分：触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

在触发器中可以使用两个特殊的临时表，即OLD表和NEW表。OLD表用于存储UPDATE语句和DELETE语句中影响的记录，NEW表用于存储INSERT语句和UPDATE语句影响的记录。
定义触发器的语法规则：

CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 表名称 
	FOR EACH ROW 
	BEGIN
	语句
	END;

语法解析：

触发器名称：是用来标识触发器的，由用户自定义。
触发时机：其值是before或after。
触发事件：其值是insert，update和delete。
表名称：标识建立触发器的表名，即在哪张表上建立触发器。
语句：是触发器程序体，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句。

2、定义和测试触发器

定义触发器

CREATE TRIGGER tr_register AFTER INSERT ON user_info FOR EACH ROW
	BEGIN
		INSERT INTO user_details(id) VALUES(NEW.id);
	END;

测试触发器：
使用JDBC调用register存储过程，存储过程向user_info表中添加一条用户记录，添加成功后激活tr_register触发器，tr_register触发器实现向user_details表中添加用户详细信息。

三、索引

3.1、索引简介

索引（index）是数据库对象，是用来提高查询效率的。索引的数据结构 B-Tree。索引是如何提高查询效率的呢？MySQL数据库的索引使用了B-Tree数据结构存储数据，来提高查询效率的。了解B-Tree数据结构有助于理解索引提高查询效率的原理。
下图是B-Tree数据结构的示意图，分为上下两部分，下部分数据页，上部分是索引页。数据页中存储的是表中的记录，索引页存储的是索引数据。
在这里插入图片描述
对于任何DBMS（DataBase Manager System数据库管理系统），索引都是进行查询优化的最主要的因素
如上图是一颗B-Tree数据结构，其中包含了数据页和索引页，数据页和索引页中虚线框存储的是数据项，实线框中的P1、P2、P3是指针。可以看到每个数据页或索引页包含多个数据项和指针，如“索引页1”包含数据项17和35，包含指针P1、P2、P3。P1指向小于17的索引页，P2指向在17和35之间的索引页，P3指向大于35的索引页。真实的数据存在于叶子节点（数据页），即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
如果要查找数据项29，那么首先会把索引页1由磁盘加载到内存，此时发生一次IO（Input/Output，即读写磁盘）操作，在内存中用二分查找确定29在17和35之间，锁定“索引页1”的P2指针，通过索引页1的P2指针把索引页3由磁盘加载到内存，发生第二次IO操作，29在26和30之间，锁定索引页3的P2指针，通过指针加载索引页8到内存，发生第三次IO操作，同时内存中做二分查找找到29，结束查询，总计三次读取硬盘。真实的情况是，3层的B-Tree结构可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高是巨大的。如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常高。

3.2、使用索引

创建索引的语法是：

CREATE INDEX 索引名称 ON 表名称(字段名)

实例：

CREATE INDEX ix_userinfo_username ON user_info(username);

说明：

在user_info表的username字段上创建了索引。
索引的名称为ix_userinfo_username。

3.3、测试索引

如果在SELECT语句的WHERE查询条件中使用了创建索引的列，那么MYSQL在查询时就会使用索引进行查询。MySQL提供了EXPLAIN，EXPLAIN能够显示MySQL如何使用索引来处理SELECT语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。
MYSQL用不用索引其中有一个因素就是条件的选择性。user_info表中有3条记录，其中username索引列的值都是林冲，如果被查询的值在所有行中占据的比例过大，那么MYSQL就不会用索引，因为MySQL的优化器会判断用索引和不用索引的开销，发现用了索引开销更大，所以就不用了。
另外，只有在表中的记录数量很大时，创建索引才有意义。如果数据库中只有少量数据是没有必要创建索引的。就像一本书只有5页，就没有必要做目录一样。

3.4、索引的分类：

普通索引
这是最基本的索引，也是我们大多数情况下用到的索引。
语法：CREATE INDEX index_name ON table(column(length))
唯一索引
与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。
语法：CREATE UNIQUE INDEX indexName ON table(column(length))
主键索引
它是一种特殊的唯一索引，但不允许有空值。数据库管理系统自动为主键列添加主键索引。
全文索引
全文索引（FULLTEXT索引）是对CHAR、VARCHAR或TEXT列创建的索引，通常应用于对大文本字段。例如在文章内容列搜索，就需要对文章内容列创建全文索引。
语法：CREATE FULLTEXT INDEX index_content ON article(content)
组合索引
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步提升MySQL 的效率，就要考虑建立组合索引。可根据查询的条件组合创建组合索引。
语法：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))

3.5、索引的优缺点:

使用索引的优点是提高查询效率。使用索引的缺点是当对表的数据进行 INSERT、UPDATE、DELETE 的时候,索引也要动态的维护,这样就会降低INSERT、UPDATE、DELETE的执行效率。
除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。在建立索引的时候应该考虑索引应该建立在数据表中的哪些列上面，一般来说：

在经常需要搜索的列上创建索引,可以加快索引的速度
在表与表的连接条件上加上索引,可以加快连接查询的速度
like语句不使用索引
在列上进行运算，索引会失效。

实例：

import java.sql.*;
/**
 * 注册批量用户
 *
 * @author DingYi
 * date 2020/5/1 14:38
 */
public class BatchRegisterUser {
    public static void main(String[] args) {
        register();
    }

    /**
     * 注册用户的方法
     */
    private static void register(){
        Connection conn = null;
        CallableStatement cst = null;

        try {
            // 1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2.建立连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user_manager?useSSL=false&characterEncoding=utf8", "root", "root");

            // 3.准备语句
            String sql = "{CALL register(?,?,?)}";


            // 4.发送并执行语句
            cst = conn.prepareCall(sql);

            for(int i = 0; i < 100000; i ++){
                cst.setObject(1, getRandomName());
                cst.setObject(2, "123456");
                // 注册输出参数
                cst.registerOutParameter(3, Types.INTEGER);
                // 执行
                cst.executeUpdate();
                // 5. 处理执行结果
                int id = cst.getInt(3);
                System.out.println("注册用户的id值是:" + id);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if(cst != null){
                try {
                    cst.close();
                    cst = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                    conn = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static String getRandomName() {
        int length = ((int) (Math.random() * 2)) + 2;
        String name = "";
        for(int i = 0; i < length; i ++){
            // 0x4e00 - 0x9fa5
            name += ((char)(int)(Math.random() * (0x9fa5 - 0x4e00 + 1) + 0x4e00));
        }
        return name;
    }

}

四、视图

视图是基于查询的虚拟表。通俗的理解，视图就是一条SELECT语句执行后返回的结果集。
SELECT语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。
使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。

4.1、定义视图

定义视图的基本语法为：

CREATE VIEW 视图名
	AS
	SELECT列1，列2...
	FROM 表;

注意：

视图的主要作用是与查询相关。
可以通过视图插入数据，但只能基于一个基表进行插入，不能同时向多张基表插入数据。
可以通过视图修改数据，但只能基于一个基表进行修改，不能同时修改多张基表的数据。
可以通过视图删除数据，但只能删除单表查询的视图，不能删除多表连接查询视图中的数据。
虽然通过视图也可以对数据进行添加、删除、修改，但不推荐这样做。
使用drop view 语句可以删除视图