Day19 - MySQL视图、索引与存储过程

# MySQL数据库强化

## 一、视图

#### 1、视图的定义

简单来说，**MySQL视图是一个虚拟表**。它本身不存储数据，而是基于一个或多个实际表（或其它视图）的查询结果，存放在`view`文件夹下。

可以将视图想象成一个**预定义的查询窗口**或**一个保存的SQL查询语句**。当对这个“虚拟表”进行查询时，数据库系统会实时执行定义视图时所用的SQL查询，并返回结果。

**简单应用**

假如有一个包含所有员工信息的`EMP`表（有姓名、部门、工资、电话等字段）。如果只想让经理查看员工的姓名和部门，而不希望他看到敏感的工资信息，就可以创建一个视图`v_EMP_info`。这个视图只从`EMP`表中选取`ename`和`DEPTno`两列。经理只需要查询`SELECT * FROM v_EMP_info;`，他看到的就是一个只包含姓名和部门的“表”，而原始的`EMP`表结构被隐藏和保护了。

#### 2、创建视图

##### 语法

```sql
CREATE VIEW VIEWNAME AS SELECT(查询语句);
```

#### 3、查询视图

##### 语法

```sql
SELECT * FROM VIEWNAME;
```

#### 4、练习

将员工编号、员工薪资、员工薪资等级、员工部门编号、员工部门名称创作成视图`v1`

```sql
CREATE VIEW v1 AS
SELECT EMP.EMPno,EMP.sal,SALGRADE.grade,EMP.DEPTno,DEPT.dname
FROM EMP,DEPT,SALGRADE
WHERE EMP.DEPTno=DEPT.DEPTno
AND EMP.sal BETWEEN SALGRADE.losal AND SALGRADE.hisal;
-- 查询
SELECT * FROM v1;
```

查询员工的所有信息，部门的所有信息，薪资的所有信息创作成视图`v2`

```sql
CREATE VIEW v2 AS
SELECT EMP.*,SALGRADE.*,DEPT.dname,DEPT.loc
FROM EMP,DEPT,SALGRADE
WHERE EMP.DEPTno=DEPT.DEPTno
AND EMP.sal BETWEEN SALGRADE.losal AND SALGRADE.hisal;
-- 查询
SELECT * FROM v2;
```

#### 5、查看所有视图

查看所有表

```sql
SHOW TABLES;
```

#### 6、视图和表单的区别

| 特性         | MySQL视图（View）                                                | MySQL表单（Table）                             |
| ------------ | ---------------------------------------------------------------- | ---------------------------------------------- |
| **定义**     | 虚拟表，基于SELECT查询定义，不存储实际数据                       | 物理表，实际存储数据在磁盘上                   |
| **数据存储** | 不存储数据，仅存储查询定义；数据实时从基表获取                   | 直接存储数据行，占用物理存储空间               |
| **数据更新** | 通常只读（简单视图可更新，但有限制，如涉及聚合或连接时不可更新） | 可直接插入、更新、删除数据                     |
| **性能**     | 查询时动态生成数据，可能因复杂查询而较慢；但可简化复杂查询       | 数据直接访问，通常查询更快；但需维护索引和存储 |
| **用途**     | 简化复杂查询、增强安全性（隐藏敏感数据）、逻辑数据抽象           | 存储实际业务数据，支持事务和数据处理           |
| **依赖性**   | 依赖于基表（基础表单），基表结构变化可能影响视图                 | 独立存在，可被其他表或视图引用                 |
| **索引**     | 无法直接创建索引（但可基于索引的基表优化查询）                   | 可创建索引以提高查询性能                       |
| **存储空间** | 占用少量空间存储查询定义，不存储数据                             | 占用物理存储空间，随数据量增长而增加           |
| **示例场景** | 创建视图`v_EMP_public`隐藏工资列，供普通用户查询                 | 创建表单`EMP`存储员工ID、姓名、工资等详细数据  |

#### 7、插入数据

可以通过视图向基表(主表)中插入数据，只要视图中不出出现以下情况：

* 分组函数，分组语句，`DISTINCT`关键字；
* 使用表达式定义的列；
* 基表中未在视图中选择的其他列定义非空并且没有默认值。

当插入数据时会直接插入到基表中，若插入的数据为满足视图建立的条件时，不会再视图中显示。

例如

```sql
INSERT INTO v2(EMPno,ename) VALUES(8000,'张三');
-- 查询v2视图，不会显示张三
SELECT * FROM v2;
-- 查询EMP基表，会显示张三
SELECT * FROM EMP;
```

会显示的例子

```sql
INSERT INTO v2(EMPno,ename,DEPTno,sal) VALUES(8001,'李四',10,4000);
-- 查询v2视图，会显示李四
SELECT * FROM v2;
-- 查询EMP基表，会显示李四
SELECT * FROM EMP;
```

#### 8、更新数据

可以通过视图向基表(主表)中更新数据，只要视图中不出出现以下情况：

* 分组函数，分组语句，`DISTINCT`关键字；
* 使用表达式定义的列。

当更新数据时会直接更新基表中的数据。

##### 语法

```sql
UPDATE VIEWNAME SET FIELDNAME=FIELDVALUE WHERE CONDITIONS;
```

#### 9、删除数据

可以通过视图向基表(主表)中删除数据，只要视图中不出出现以下情况：

* 分组函数，分组语句，`DISTINCT`关键字。

当删除数据时会直接删除基表中的数据。

##### 语法

```sql
DELETE FROM VIEWNAME WHERE CONDITIONS;
```

#### 10、删除视图

##### 语法

```sql
DROP VIEW VIEWNAME;
```

## 二、索引

#### 1、索引的定义

索引是一种特殊的**数据结构**（如B+Tree），它存储了表中某些列的值以及这些值所在行的物理地址的指针。当数据库需要查找数据时，它可以先在索引中快速定位，然后直接去磁盘上读取对应的数据行，从而避免全表扫描。

#### 2、索引的优缺点

##### 优点

大大加快数据的检索速度

- 这是最主要的好处。对于有大量数据的表，使用索引可以将查询速度提升几个数量级。特别是对于`WHERE`、`ORDER BY`、`GROUP BY`和`JOIN`子句。

确保数据的唯一性

- 唯一索引（如主键索引）可以保证表中每一行数据的唯一性，避免重复。

加速表与表之间的连接

- 在进行表连接（JOIN）时，如果连接字段上有索引，会显著提高连接效率。

减少服务器需要扫描的数据量

- 索引让数据库服务器不必扫描整个表，只需查找索引即可。

##### 缺点

占用额外的磁盘空间

- 索引需要被创建并存储，这会占用额外的磁盘空间。对于大表，索引本身可能也会很大。

降低数据增、删、改的速度

- 当对表中的数据进行增加、删除、修改操作时，数据库不仅需要更新表数据，还需要更新与之相关的所有索引。索引越多，这个维护成本就越高。

需要维护成本

- 索引不是一劳永逸的。随着数据的增删，索引可能会变得碎片化，需要定期重建或优化以保持性能。

**权衡：** 因此，创建索引的**核心原则**是：对于查询操作远多于写操作（增、删、改）的表，索引带来的查询性能提升远大于维护索引的代价。

#### 3、索引的分类

普通索引（INDEX）：最基本的索引，没有任何限制，仅用于加速查询。

唯一索引（UNIQUE）：索引列的值必须唯一，但允许有空值（NULL）。主键索引是一种特殊的唯一索引，不允许有空值。

主键索引（PRIMARY KEY）：特殊的唯一索引，不允许有空值。每个表只能有一个主键索引。

全文索引（FULLTEXT）：用来查找文本中的关键字，只能再CHAR、VARCHAR或TEXT类型的列上创建。在MySQL中只有MyISAM储存索引支持全文索引。

空间索引（SPATIAL）：对地理空间数据类型`GEOMETRY`的字段键列的索引。创建空间索引的。

#### 4、导入表数据

一个包含超多条数据的表：

香肠狗网盘

#### 5、创建索引

##### 语法

```sql
CREATE INDEX [INDEXTYPE] INDEXNAME ON TABLE(FIELDNAME);
```

##### 为`uname`字段创建索引

```sql
CREATE INDEX index_uname ON t1(uname);
```

##### 通过索引查询

```sql
-- 使用普通键查询:0.417s
SELECT * FROM t1
WHERE uname = 'VRFKLI';
-- 使用主键索引进行查询:0.323s
SELECT * FROM t1
WHERE uid = 130851;
-- 使用普通索引查询:0.333s
SELECT * FROM t1
WHERE uname = 'VRFKLI';
```

#### 6、查看索引

##### 语法

```sql
SHOW INDEX FROM TABLENAME;
```

#### 7、删除索引

##### 语法

```sql
DROP INDEX INDEXNAME ON TABLENAME;
```

#### 8、最左原则

当有联合主键时，建立主键索引时只会将位置左侧的部分主键字段建立成索引。

##### `EXPLAIN`

作用：可以分析查询的效率和查询有没有根据索引查询。

`type`字段：

* ALL：表示全表查询
* INDEX：表示通过索引
* RANGE：表示使用索引范围查询
* EQ_REF/REF：表示使用部分索引查询
* COUNST：表示性能良好使用索引

##### 创建测试表

```sql
-- 创建有联合主键的t2
CREATE TABLE t2(
a INT,
b INT,
c INT,
d INT
);
ALTER TABLE t2 ADD PRIMARY KEY (a,b,c);
-- 写入数据
INSERT INTO t2 VALUES(1,1,1,1),
(2,2,2,2),
(3,3,3,3),
(4,4,4,4),
(5,5,5,5),
(6,6,6,6),
(7,7,7,7),
(8,8,8,8),
(9,9,9,9);
```

##### 测试查询

```sql
EXPLAIN SELECT * FROM t2 WHERE a = 1; -- TYPE:REF表示使用了索引进行查询
EXPLAIN SELECT * FROM t2 WHERE b = 1; -- TYPE:ALL表示没用索引进行查询,最左原则
EXPLAIN SELECT * FROM t2 WHERE c = 1; -- TYPE:ALL表示没用索引进行查询,最左原则
EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b = 1; -- TYPE:REF表示使用了索引进行查询
EXPLAIN SELECT * FROM t2 WHERE a = 1 OR b = 1; -- TYPE:ALL表示没用索引进行查询,OR有相当于两次查询,最左原则
EXPLAIN SELECT * FROM t2 WHERE b = 1 AND c = 1; -- TYPE:ALL表示没用索引进行查询,不包含索引字段,最左原则
```

## 三、存储过程

#### 1、存储过程的定义

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

它是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程只需在第一次创建时编译，后续使用后不会再次编译。

相当于Java中的方法，可以传入参数并输出内容。

#### 2、优缺点

#### 3、创建存储过程

##### 低版本语法

```sql
CREATE PROCEDURE 存储过程名称(参数列表)
begin
	-- SQL操作
end;
```

##### 高版本语法

```sql
-- 重新定义结束符
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE 存储过程名称(参数列表)
begin
	-- SQL操作
end$$
-- 恢复默认结束符
DELIMITER ;
```

**注意**：

存储过程参数有三种模式：in，out和inout。

- in：传入参数（默认）
- out：传出参数
- inout：此参数是in和out参数的组合。这意味着存储过程可以修改此参数并将新值传递回调用程序。

参数名不能与字段名重名。

在select语句中，使用into关键词给传出参数赋值

#### 4、调用存储过程

##### 语法

```sql
-- 例如有in var1 INT、out var2 DECIMAL(7,2)参数
CALL 存储过程名称(var1,@var2);
SELECT * FROM @var2;
```

#### 5、查看存储过程

##### 查看所有存储过程

```sql
SHOW PROCEDURE STATUS;
```

##### 查看某个存储过程

```sql
SHOW CREATE PROCEDURE 存储过程名称; 
```

#### 6、删除存储过程

##### 语法

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

#### 7、声明全局变量

**位置**：应放到存储过程外部

##### 语法1

```sql
SET @VARNAME = VALUE;
```

##### 语法2

```sql
SET @VARNAME = SELECT(单行单列的数据);
```

#### 8、声明局部变量

**位置**：应放到存储过程内部

##### 语法

```sql
DECLARE VARNAME TYPE DEFAULT VALUE;
```

#### 9、IF分支

##### 语法

```sql
IF CONDITIONS THEN 满足条件后的语句
ELSEIF CONDITIONS THEN 满足条件后的语句
ELSE 未满足上面所有条件后的语句
END IF;
```

#### 10、CASE分支

##### 等值判断语法

```sql
CASE VARNAME
WHEN FINALVAR THEN 满足条件的语句;
WHEN FINALVAR THEN 满足条件的语句;
ELSE 为满足上面所有条件后的语句;
END CASE;
```

##### 非等值判断语法

```sql
CASE 
WHEN CONDITIONS THEN 满足条件的语句;
WHEN CONDITIONS THEN 满足条件的语句;
ELSE 为满足上面所有条件后的语句;
END CASE;
```

#### 11、WHILE循环

##### 语法

```sql
WHILE CONDITIONS DO
-- SQL语句
END WHILE;
```

#### 12、LOOP循环

##### 语法

```sql
循环名:LOOP
-- SQL语句
	IF 退出循环的条件 THEN
		LEAVE 循环名;
	END IF;
END LOOP 循环名;
```

#### 13、存储过程示例

##### 无参数存储过程

```sql
CREATE PROCEDURE getEMPAll()
BEGIN
  SELECT * FROM EMP;
END;
-- 调用
CALL getEMPAll();
```

##### 修改入参变量值

```sql
CREATE PROCEDURE getEmpBySal(IN esal DECIMAL(7,2))
BEGIN
  SET esal = esal + 500;
  SELECT * FROM EMP WHERE EMP.sal>esal;
END;
CALL getEmpBySal(1000);
```

##### 用全局变量传入参数

```sql
CREATE PROCEDURE getDeptById(IN dno INT)
BEGIN
  SELECT * FROM DEPT WHERE DEPTno=dno;
END;
SET @dno = 20;
CALL getDeptById(@dno);
```

##### 有出参、入参数存储过程

```sql
CREATE PROCEDURE getDnameByEMPno(IN emp_no INT,OUT d_name VARCHAR(14))
BEGIN
  SELECT dname INTO d_name FROM EMP,DEPT WHERE EMP.DEPTno=DEPT.DEPTno AND EMP.EMPno=emp_no;
END;
CALL getDnameByEMPno(7788,@d_name);
SELECT @d_name;
```

##### 有双向参数存储过程

```sql
CREATE PROCEDURE func(INOUT arg INT)
BEGIN
  SET arg = arg * 2;
END;
SET @arg = 20;
CALL func(@arg);
SELECT @arg;
```

##### IF分支判断分数等级

```sql
CREATE PROCEDURE getGradeByScore(IN score INT,OUT grade VARCHAR(2))
BEGIN
  IF score>90 THEN SET grade = 'A';
  ELSEIF score>80 THEN SET grade = 'B';
  ELSEIF score>70 THEN SET grade = 'C';
  ELSEIF score>60 THEN SET grade = 'D';
  ELSE SET grade = 'E';
  END IF;
END;
CALL getGradeByScore(85,@grade);
SELECT @grade;
```

##### CASE分支等值判断部门

```sql
CREATE PROCEDURE getDnameById(IN dno INT,OUT dname VARCHAR(10))
BEGIN
  CASE dno
	WHEN 10 THEN
		SET dname='研发部';
	WHEN 20 THEN
		SET dname='销售部';
	WHEN 30 THEN
		SET dname='财务部';
	ELSE
		SET dname='无对应部门';
END CASE;
END;
CALL getDnameById(10,@dname);
SELECT @dname;
```

##### 插入数据返回自增ID

```sql
CREATE PROCEDURE AddNewUser(IN username VARCHAR(14),IN email VARCHAR(14),OUT uid INT)
BEGIN
  INSERT INTO usertable(u_name,u_email) VALUES(username,email);
  SET uid = LAST_INSERT_ID();
END;
```

##### 循环插入DEPT

```sql
CREATE PROCEDURE addDept()
BEGIN
  DECLARE arg INT DEFAULT 1;
  WHILE arg < 10 DO
    INSERT INTO DEPT VALUES(arg,CONCAT('test',arg),CONCAT('test',arg));
    SET arg = arg + 1;
  END WHILE;
END;
CALL addDept();
```

##### 循环插入EMP

```sql
CREATE PROCEDURE addEmp(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  loop1: LOOP
	INSERT INTO EMP(ename) VALUES(CONCAT('name',i));
  	SET i = i + 1;
	IF i > n THEN
		LEAVE loop1; 
	END IF; 
END LOOP loop1;
END;
CALL addEmp(10);
```

##### 综合示例

```sql
DROP PROCEDURE IF EXISTS `backup`;
CREATE PROCEDURE `backup`(IN eno INT)
BEGIN
    DECLARE tex INT DEFAULT 0;
    SELECT 1 INTO tex FROM information_schema.tables WHERE table_name='EMP_copy';
    IF tex<>1 THEN
        CREATE TABLE EMP_copy SELECT * FROM EMP WHERE 1=0;
    END IF;
    INSERT INTO EMP_copy SELECT * FROM EMP WHERE EMPno=eno;
    DELETE FROM EMP WHERE EMPno=eno;
END;
```

调用

```sql
CALL backup(7369);
```

作用是传入EMPno，创建一个tex变量默认值为0，从系统表中查询表名称为EMP_copy的表是否存在，若存在则将1写入tex变量，如果tex不为1，那么将创建一个表EMP_copy并将EMP表中的所有字段复制过去，之后插入一条从EMP通过查询EMPno=eno的数据，并从EMP表中删除这条数据。

## **未完待续**

# MySQL数据库强化

## 一、视图

#### 1、视图的定义

简单来说，**MySQL视图是一个虚拟表**。它本身不存储数据，而是基于一个或多个实际表（或其它视图）的查询结果，存放在`view`文件夹下。

**简单应用**

#### 2、创建视图

##### 语法

```sql
CREATE VIEW VIEWNAME AS SELECT(查询语句);
```

#### 3、查询视图

##### 语法

```sql
SELECT * FROM VIEWNAME;
```

#### 4、练习

将员工编号、员工薪资、员工薪资等级、员工部门编号、员工部门名称创作成视图`v1`

查询员工的所有信息，部门的所有信息，薪资的所有信息创作成视图`v2`

#### 5、查看所有视图

查看所有表

```sql
SHOW TABLES;
```

#### 6、视图和表单的区别

#### 7、插入数据

可以通过视图向基表(主表)中插入数据，只要视图中不出出现以下情况：

* 分组函数，分组语句，`DISTINCT`关键字；
* 使用表达式定义的列；
* 基表中未在视图中选择的其他列定义非空并且没有默认值。

当插入数据时会直接插入到基表中，若插入的数据为满足视图建立的条件时，不会再视图中显示。

例如

```sql
INSERT INTO v2(EMPno,ename) VALUES(8000,'张三');
-- 查询v2视图，不会显示张三
SELECT * FROM v2;
-- 查询EMP基表，会显示张三
SELECT * FROM EMP;
```

会显示的例子

```sql
INSERT INTO v2(EMPno,ename,DEPTno,sal) VALUES(8001,'李四',10,4000);
-- 查询v2视图，会显示李四
SELECT * FROM v2;
-- 查询EMP基表，会显示李四
SELECT * FROM EMP;
```

#### 8、更新数据

可以通过视图向基表(主表)中更新数据，只要视图中不出出现以下情况：

* 分组函数，分组语句，`DISTINCT`关键字；
* 使用表达式定义的列。

当更新数据时会直接更新基表中的数据。

##### 语法

```sql
UPDATE VIEWNAME SET FIELDNAME=FIELDVALUE WHERE CONDITIONS;
```

#### 9、删除数据

可以通过视图向基表(主表)中删除数据，只要视图中不出出现以下情况：

* 分组函数，分组语句，`DISTINCT`关键字。

当删除数据时会直接删除基表中的数据。

##### 语法

```sql
DELETE FROM VIEWNAME WHERE CONDITIONS;
```

#### 10、删除视图

##### 语法

```sql
DROP VIEW VIEWNAME;
```

## 二、索引

#### 1、索引的定义

#### 2、索引的优缺点

##### 优点

大大加快数据的检索速度

- 这是最主要的好处。对于有大量数据的表，使用索引可以将查询速度提升几个数量级。特别是对于`WHERE`、`ORDER BY`、`GROUP BY`和`JOIN`子句。

确保数据的唯一性

- 唯一索引（如主键索引）可以保证表中每一行数据的唯一性，避免重复。

加速表与表之间的连接

- 在进行表连接（JOIN）时，如果连接字段上有索引，会显著提高连接效率。

减少服务器需要扫描的数据量

- 索引让数据库服务器不必扫描整个表，只需查找索引即可。

##### 缺点

占用额外的磁盘空间

- 索引需要被创建并存储，这会占用额外的磁盘空间。对于大表，索引本身可能也会很大。

降低数据增、删、改的速度

- 当对表中的数据进行增加、删除、修改操作时，数据库不仅需要更新表数据，还需要更新与之相关的所有索引。索引越多，这个维护成本就越高。

需要维护成本

- 索引不是一劳永逸的。随着数据的增删，索引可能会变得碎片化，需要定期重建或优化以保持性能。

**权衡：** 因此，创建索引的**核心原则**是：对于查询操作远多于写操作（增、删、改）的表，索引带来的查询性能提升远大于维护索引的代价。

#### 3、索引的分类

普通索引（INDEX）：最基本的索引，没有任何限制，仅用于加速查询。

唯一索引（UNIQUE）：索引列的值必须唯一，但允许有空值（NULL）。主键索引是一种特殊的唯一索引，不允许有空值。

主键索引（PRIMARY KEY）：特殊的唯一索引，不允许有空值。每个表只能有一个主键索引。

全文索引（FULLTEXT）：用来查找文本中的关键字，只能再CHAR、VARCHAR或TEXT类型的列上创建。在MySQL中只有MyISAM储存索引支持全文索引。

空间索引（SPATIAL）：对地理空间数据类型`GEOMETRY`的字段键列的索引。创建空间索引的。

#### 4、导入表数据

一个包含超多条数据的表：

香肠狗网盘

#### 5、创建索引

##### 语法

```sql
CREATE INDEX [INDEXTYPE] INDEXNAME ON TABLE(FIELDNAME);
```

##### 为`uname`字段创建索引

```sql
CREATE INDEX index_uname ON t1(uname);
```

##### 通过索引查询

#### 6、查看索引

##### 语法

```sql
SHOW INDEX FROM TABLENAME;
```

#### 7、删除索引

##### 语法

```sql
DROP INDEX INDEXNAME ON TABLENAME;
```

#### 8、最左原则

当有联合主键时，建立主键索引时只会将位置左侧的部分主键字段建立成索引。

##### `EXPLAIN`

作用：可以分析查询的效率和查询有没有根据索引查询。

`type`字段：

* ALL：表示全表查询
* INDEX：表示通过索引
* RANGE：表示使用索引范围查询
* EQ_REF/REF：表示使用部分索引查询
* COUNST：表示性能良好使用索引

##### 创建测试表

##### 测试查询

## 三、存储过程

#### 1、存储过程的定义

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

它是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程只需在第一次创建时编译，后续使用后不会再次编译。

相当于Java中的方法，可以传入参数并输出内容。

#### 2、优缺点

#### 3、创建存储过程

##### 低版本语法

```sql
CREATE PROCEDURE 存储过程名称(参数列表)
begin
	-- SQL操作
end;
```

##### 高版本语法

```sql
-- 重新定义结束符
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE 存储过程名称(参数列表)
begin
	-- SQL操作
end$$
-- 恢复默认结束符
DELIMITER ;
```

**注意**：

存储过程参数有三种模式：in，out和inout。

- in：传入参数（默认）
- out：传出参数
- inout：此参数是in和out参数的组合。这意味着存储过程可以修改此参数并将新值传递回调用程序。

参数名不能与字段名重名。

在select语句中，使用into关键词给传出参数赋值

#### 4、调用存储过程

##### 语法

```sql
-- 例如有in var1 INT、out var2 DECIMAL(7,2)参数
CALL 存储过程名称(var1,@var2);
SELECT * FROM @var2;
```

#### 5、查看存储过程

##### 查看所有存储过程

```sql
SHOW PROCEDURE STATUS;
```

##### 查看某个存储过程

```sql
SHOW CREATE PROCEDURE 存储过程名称; 
```

#### 6、删除存储过程

##### 语法

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

#### 7、声明全局变量

**位置**：应放到存储过程外部

##### 语法1

```sql
SET @VARNAME = VALUE;
```

##### 语法2

```sql
SET @VARNAME = SELECT(单行单列的数据);
```

#### 8、声明局部变量

**位置**：应放到存储过程内部

##### 语法

```sql
DECLARE VARNAME TYPE DEFAULT VALUE;
```

#### 9、IF分支

##### 语法

```sql
IF CONDITIONS THEN 满足条件后的语句
ELSEIF CONDITIONS THEN 满足条件后的语句
ELSE 未满足上面所有条件后的语句
END IF;
```

#### 10、CASE分支

##### 等值判断语法

```sql
CASE VARNAME
WHEN FINALVAR THEN 满足条件的语句;
WHEN FINALVAR THEN 满足条件的语句;
ELSE 为满足上面所有条件后的语句;
END CASE;
```

##### 非等值判断语法

```sql
CASE 
WHEN CONDITIONS THEN 满足条件的语句;
WHEN CONDITIONS THEN 满足条件的语句;
ELSE 为满足上面所有条件后的语句;
END CASE;
```

#### 11、WHILE循环

##### 语法

```sql
WHILE CONDITIONS DO
-- SQL语句
END WHILE;
```

#### 12、LOOP循环

##### 语法

```sql
循环名:LOOP
-- SQL语句
	IF 退出循环的条件 THEN
		LEAVE 循环名;
	END IF;
END LOOP 循环名;
```

#### 13、存储过程示例

##### 无参数存储过程

```sql
CREATE PROCEDURE getEMPAll()
BEGIN
  SELECT * FROM EMP;
END;
-- 调用
CALL getEMPAll();
```

##### 修改入参变量值

```sql
CREATE PROCEDURE getEmpBySal(IN esal DECIMAL(7,2))
BEGIN
  SET esal = esal + 500;
  SELECT * FROM EMP WHERE EMP.sal>esal;
END;
CALL getEmpBySal(1000);
```

##### 用全局变量传入参数

```sql
CREATE PROCEDURE getDeptById(IN dno INT)
BEGIN
  SELECT * FROM DEPT WHERE DEPTno=dno;
END;
SET @dno = 20;
CALL getDeptById(@dno);
```

##### 有出参、入参数存储过程

##### 有双向参数存储过程

```sql
CREATE PROCEDURE func(INOUT arg INT)
BEGIN
  SET arg = arg * 2;
END;
SET @arg = 20;
CALL func(@arg);
SELECT @arg;
```

##### IF分支判断分数等级

##### CASE分支等值判断部门

##### 插入数据返回自增ID

##### 循环插入DEPT

##### 循环插入EMP

##### 综合示例

调用

```sql
CALL backup(7369);
```

## **未完待续**

最后修改：2025 年 10 月 26 日

👆请简单粗暴地爱我。

Day19 - MySQL视图、索引与存储过程

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

CentOS一键换源脚本

Java文件读写操作

Tomcat9.0安装与IDEA配置

Vue3使用TypeScript编写时注意事项

ElasticSearch分布式搜索框架

Day19 - MySQL视图、索引与存储过程

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Day19 - MySQL视图、索引与存储过程

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款