MySQL高级-03-授课笔记_声明一个变量,用于存储学生的总成绩,然后变量赋值,计算学生总成绩

MySQL高级-03-授课笔记

一、MySQL存储过程和函数

1. 存储过程和函数的概念

• 存储过程和函数是：事先经过编译并存储在数据库中的一段 SQL 语句的集合
• 存储过程和函数，类似于java里的方法
  • 存储过程和函数，是存储了多行sql语句
  • java是存储了多行java代码
• 存储过程与函数的应用场景：
  • 如果某N条sql语句，比较复杂，比较常用，有业务逻辑，这时候就可以将这N条sql语句放入存储过程或函数中

2. 存储过程和函数的好处

• 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
• 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
• 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
• 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理

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

• 函数必须有返回值
• 存储过程没有返回值

4. 创建存储过程

• 小知识

/*
	该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！
	sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，但是并不作为结束标识。
	这个时候就可以使用DELIMITER来指定分隔符了！
*/
-- 标准语法
DELIMITER 分隔符
delimiter：  [dɪ'lɪmɪtə]  分隔符
因为在存储过程中会有多条SQL语句，那如果还是以分号分隔，那mysql就会认为这存储多过程中的第一条sql语句执行完就结束了，所以我们需要在使用存储过程的时候修改分隔符
1
2
3
4
5
6
7
8
9

• 数据准备

-- 创建db8数据库
CREATE DATABASE db8;

-- 使用db8数据库
USE db8;

-- 创建学生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 学生id
	NAME VARCHAR(20),					-- 学生姓名
	age INT,							-- 学生年龄
	gender VARCHAR(5),					-- 学生性别
	score INT                           -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);

-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• 创建存储过程语法

-- 修改分隔符为$  （注意：创建存储过程之前修改分隔符为$）
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)  -- procedure：[prəˈsi:dʒə(r)] 过程
BEGIN
	sql语句;  -- 注意：数据库不会认为这里是结束
END$  -- 注意：数据库认为这里才是结束

-- 修改分隔符为分号
DELIMITER ;  -- 注意：只有在存储过程中不能用;结束，既然存储过程都创建好了，我们就再改为以;结束
1
2
3
4
5
6
7
8
9
10
11

• 创建存储过程

-- 修改分隔符为$
DELIMITER $

-- 创建存储过程，封装分组查询学生总成绩的sql语句
CREATE PROCEDURE stu_group()
BEGIN
	-- 注意：我们这里是简单使用一些存储过程，所以只写了一句sql，不过即使只有一句sql，放到存储过程之后，可以重复使用
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- 修改分隔符为分号
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12

5. 调用存储过程

• 调用存储过程语法

-- 标准语法
CALL 存储过程名称(实际参数);

-- 调用stu_group存储过程
CALL stu_group();
1
2
3
4
5

6. 查看存储过程

• 查看存储过程语法

-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';
-- 注意： proc：procedure
-- 理解： 相当于存储过程是存储在mysql.proc这个表中，这个表中有一个字段db，是当前存储过程所属的数据库
1
2
3
4

7. 删除存储过程

• 删除存储过程语法

-- 标准语法（注意： 语法中，中括号的内容是可选内容）
DROP PROCEDURE [IF EXISTS] 存储过程名称;  -- 注意： 中括号中的if exists ， 可写可不写

-- 删除stu_group存储过程
DROP PROCEDURE stu_group; -- 注意： 删除存储过程，不需要写小括号，调用的时候需要
1
2
3
4
5

8. 存储过程语法

8.1 存储过程语法介绍

• 存储过程是可以进行编程的。意味着可以使用变量、表达式、条件控制语句、循环语句等，来完成比较复杂的功能！

8.2 变量的使用

• 定义变量

-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-- declare :  [dɪˈkleə(r)]  声明

-- 定义一个int类型变量、并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	DECLARE num INT DEFAULT 10;   -- 定义变量
	SELECT num;                   -- 查询变量
END$

DELIMITER ;

-- 调用pro_test1存储过程
CALL pro_test1();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• 变量的赋值1

-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量，并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE NAME VARCHAR(10);   -- 定义变量
	SET NAME = '存储过程';       -- 为变量赋值
	SELECT NAME;                -- 查询变量
END$

DELIMITER ;

-- 调用pro_test2存储过程
CALL pro_test2();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 变量的赋值2： 将查询结果输入到变量

-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件]; -- into 输入

-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- 定义变量
	SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men
	SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women
	SELECT men,women;           -- 查询变量
END$

DELIMITER ;

-- 调用pro_test3存储过程
CALL pro_test3();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

8.3 if语句的使用

• 标准语法

-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;    -- 注意： then 然后， 如果满足添加，然后执行xx
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
1
2
3
4
5
6

• 案例演示

/*
	定义一个int变量，用于存储班级总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上    学习优秀
		320 ~ 380     学习不错
		320以下       学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
	-- 定义总分数变量
	DECLARE total INT;
	-- 定义分数描述变量
	DECLARE description VARCHAR(10);
	-- 为总分数变量赋值
	SELECT SUM(score) INTO total FROM student;
	-
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18