MySQL基础(七)

变量(了解)

分类

变量的分类

系统变量

概念

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

使用的语法

  • 查看所有的系统变量:
-- 显示所有全局变量
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。

应用示例

  • 显示所有的全局变量:
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;

全局变量的作用域

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

会话变量的作用域

  • 仅仅针对当前的会话(连接)有效。

用户自定义变量

概念

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

用户变量

作用域

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

使用的语法:

  • 声明并初始化值:
SET @用户变量名 = 值;
-- 推荐方式
SET @用户变量名 := 值;
  • 赋值(更新用户变量的值)
SET @用户变量名 = 值;
-- 推荐方式
SET @用户变量名 := 值;
SELECT 字段 into 用户变量名 FROM 表名;
  • 使用用户变量(查看用户变量)
SELECT @用户变量名;

局部变量

作用域

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

使用的语法:

  • 声明:
DECLARE 局部变量名 类型;
DECLARE 局部变量名 类型 DEFAULT 值;
  • 赋值:
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT 字段 into局部变量名 FROM 表名;
  • 使用局部变量(查看局部变量)
SELECT 局部变量名;

存储过程和函数(了解)

概述

  • 类似于Java中的方法。

好处

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

存储过程

概念

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

创建存储过程语法

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

注意:

①参数列表包含三部分。

参数模式 参数名 参数类型

例如: IN sut_name varchar(255)

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

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

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

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

语法:

DELIMITER 结束标记

例如:DELIMITER $

调用存储过程语法

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

应用示例

  • 查询员工名为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');

删除存储过程语法

DROP PROCEDURE 存储过程名;

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

SHOW CREATE PROCEDURE 存储过程名;

函数

概念

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

创建函数的语法

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

注意:

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

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

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

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

调用函数的语法

SELECT 函数名(实参列表);

应用示例

  • 查询公司的员工个数。
-- 创建函数
DELIMITER $;
create FUNCTION test() RETURNS int
BEGIN
  DECLARE count int DEFAULT 0; 
  SELECT count(*) into count from employees;
	return count;
	
END $;
DELIMITER ;

-- 调用函数
SELECT test();

删除函数的语法

DROP FUNCTION 函数名;

查看创建函数的语法

SHOW CREATE FUNCTION 函数名;

流程控制结构(了解)

分支结构

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);

循环结构

  • 在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 [标签];