07 MySQL基础（七）

1 变量（了解）

1.1 分类

1.2 系统变量

1.2.1 概念

• 系统变量是由系统提供的，不是用户自定义的，是属于服务器层面的。

1.2.2 使用的语法

• 查看所有的系统变量：

-- 显示所有全局变量
SHOW GLOBAL VARIABLES;
-- 显示所有会话变量
SHOW SESSION VARIABLES;

• 查看满足条件的部分系统变量：

-- 查看满足条件的部分全局变量
SHOW GLOBAL VARIABLES LIKE '%character%';
-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%character%';

• 查看指定的某个系统变量的值：

-- 查看某个指定的全局变量
SELECT @@GLOBAL.系统变量名;
-- 查看某个指定的会话变量
SELECT @@SESSION.系统变量名;

• 为某个系统变量赋值：

-- 为全局变量设置值
SET GLOBAL 系统变量名 = 值;
-- 为会话变量设置值
SET SESSION 系统变量名 = 值;

-- 为全局变量设置值
SET @@GLOBAL.系统变量名 = 值;
-- 为会话变量设置值
SET @@SESSION.系统变量名 = 值;

如果是全局级别需要加GLOBAL，如果是会话级别需要加SESSION。如果什么都不写，默认是SESSION。

1.2.3 应用示例

• 显示所有的全局变量：

SHOW GLOBAL VARIABLES;

• 查看部分的全局变量：

SHOW GLOBAL VARIABLES LIKE '%character%';

• 查看指定的全局变量的值：

SELECT @@GLOBAL.autocommit;

SELECT @@GLOBAL.tx_isolation;

• 为某个指定的全局变量赋值：

SET @@GLOBAL.autocommit = 1;

SET GLOBAL autocommit = 1;

• 显示所有的会话变量：

SHOW SESSION VARIABLES;

• 查看部分的会话变量：

SHOW SESSION VARIABLES LIKE '%character%';

• 查看指定的会话变量的值:

SELECT @@SESSION.autocommit;

SELECT @@SESSION.tx_isolation;

• 为某个指定的会话变量赋值：

SET @@SESSION.autocommit = 1;

SET SESSION autocommit = 1;

1.2.4 全局变量的作用域

• 服务器每次启动将为所有的全局变量赋初始值，针对所有的会话有效，但是不能跨重启。如果需要跨重启，需要修改服务器的配置文件。

1.2.5 会话变量的作用域

• 仅仅针对当前的会话（连接）有效。

1.3 用户自定义变量

1.3.1 概念

• 变量是用户自定义的，不是由系统提供的。

1.3.2 用户变量

1.3.2.1 作用域

• 针对于当前会话（连接）有效，同于会话变量的作用域。
• 用户变量可以放在任何地方，可以放在begin...end里面或begin...end外面。

1.3.2.2 使用的语法

• 声明并初始化值：

SET @用户变量名 = 值;

-- 推荐方式
SET @用户变量名 := 值;

• 赋值(更新用户变量的值)

SET @用户变量名 = 值;

-- 推荐方式
SET @用户变量名 := 值;

SELECT 字段 into 用户变量名 FROM 表名;

• 使用用户变量（查看用户变量）

SELECT @用户变量名;

1.3.3 局部变量

1.3.3.1 作用域

• 仅仅在定义它的begin...end中有效。
• 应用在begin..end中，而且必须是第一句。

1.3.3.2 使用的语法

• 声明：

DECLARE 局部变量名 类型;
DECLARE 局部变量名 类型 DEFAULT 值;

• 赋值：

SET 局部变量名 = 值;

SET 局部变量名 := 值;

SELECT 字段 into局部变量名 FROM 表名;

• 使用局部变量（查看局部变量）

SELECT 局部变量名;

2 存储过程和函数（了解）

2.1 概述

• 类似于Java中的方法。

2.2 好处

• 简化了应用开发人员的很多工作。
• 减少了数据在数据库和应用服务器之间的传输。
• 提供了数据处理的效率。

2.3 存储过程

2.3.1 概念

• 一组预先编译好的SQL语句的集合，可以理解成批处理语句。

2.3.2 创建存储过程语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

注意：

①参数列表包含三部分。

参数模式 参数名 参数类型

例如： IN sut_name varchar(255)

参数模式： IN(该参数可以作为输入，即该参数需要调用方传入值)、OUT(该参数可以作为输出，即该参数可以作为返回值)、INOUT(该参数可以既作为输入又可以作为输出)

②如果存储过程体仅仅只有一句话，那么BEGIN...END可以省略

存储过程体的每条SQL语句结尾要求必须加上分号;

存储过程的结尾可以使用DELIMITER重新设置。

语法：

DELIMITER 结束标记

例如：DELIMITER $

2.3.3 调用存储过程语法

CALL 存储过程名(实参列表);

2.3.4 应用示例

• 查询员工名为K_ing的所有记录。

-- 创建存储过程
DELIMITER $;
create PROCEDURE test()
BEGIN
	SELECT * FROM employees WHERE employees.last_name = 'K_ing';
END $;
DELIMITER ;

-- 调用存储过程
CALL test();

• 查询员工名为K_ing的所有记录。

-- 创建存储过程
DELIMITER $;
create PROCEDURE test(IN last_name VARCHAR(255))
BEGIN
	SELECT * FROM employees WHERE employees.last_name = last_name;
END $;
DELIMITER ;

-- 调用存储过程
CALL test('K_ing');

2.3.5 删除存储过程语法

DROP PROCEDURE 存储过程名;

2.3.6 查看创建存储过程信息语法

SHOW CREATE PROCEDURE 存储过程名;

2.4 函数

2.4.1 概念

• 一组预先编译好的SQL语句的集合，可以理解成批处理语句。
• 存储过程可以有0个返回，也可以有多个返回，而函数有且仅有一个返回。

2.4.2 创建函数的语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意：

①参数列表包含两个部分：参数名 参数类型

②函数体：必须有return语句。

③函数体重仅仅有一句的时候，可以省略BEGIN...END。

④需要使用DELIMITER语句设置结束标记。

2.4.3 调用函数的语法

SELECT 函数名(实参列表);

2.4.4 应用示例

• 查询公司的员工个数。

-- 创建函数
DELIMITER $;
create FUNCTION test() RETURNS int
BEGIN
  DECLARE count int DEFAULT 0; 
  SELECT count(*) into count from employees;
	return count;
	
END $;
DELIMITER ;

-- 调用函数
SELECT test();

2.4.5 删除函数的语法

DROP FUNCTION 函数名;

2.4.6 查看创建函数的语法

SHOW CREATE FUNCTION 函数名;

3 流程控制结构（了解）

3.1 分支结构

3.3.1 case结构

• 情况1：类似于Java中的switch语句，一般用于实现等值判断。
• 语法：

CASE 变量|表达式|字段
WHEN 要判断的值1 THEN 返回的值1或语句1;
WHEN 要判断的值2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END [CASE;] -- 如果是语句的话，需要加CASE;

• 情况2：类似于Java中的多重if语句，一般用于实现区间判断。
• 语法：

CASE 
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END [CASE;] -- 如果是语句的话，需要加CASE;

注意：

case结构可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN...END中或BEGIN...END外面。

case结构也可以作为独立的语句去使用，只能放在BEGIN...END中。

​

• 创建一个存储过程，根据传入的成绩，来显示等级。比如传入的成绩：90-100，显示A；80-90，显示B;60-80,显示C;否则，显示D。

DELIMITER $
CREATE PROCEDURE test(IN score INT)
BEGIN
	CASE 
	WHEN score >= 90 and score <=100 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;

END $
DELIMITER ;

CALL test(55);

3.2 循环结构

• 在MySQL中有while、loop和repeat三种循环结构。

• 在MySQL中iterate类似于continue，结束本次循环。leave类似于break，结束当前循环。

• while语法：

[标签:] WHILE 循环条件
DO
	循环体
END WHILE [标签];

• loop语法：可以用来模拟简单的死循环

[标签:] LOOP
    循环体
END LOOP [标签];   

• repeat语法：类似于do...while

[标签:] REPEAT
   循环体
UNTIL 结束循环条件
END repeat [标签];