视图

1、变量

分类

• 系统变量
• 自定义变量

1.1、系统变量

系统变量分为全局变量和会话变量

• 全局变量：作用域：针对于所有会话（连接）有效，但不能跨重启(重启mysql恢复默认)
• 会话变量：作用域：针对于当前会话（连接）有效

变量由系统定义，不是用户定义，属于服务器层面

注意

全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

使用步骤

• 查看所有系统变量

show global|[session] variables;

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

show global|[session] variables like '%char%';

• 查看指定的系统变量的值

select @@global|[session].系统变量名;

• 为某个系统变量赋值

# 方式一
set global|[session] 系统变量名=值;
# 方式二
set @@global|[session].系统变量名=值;

例子练习

#1》全局变量
#作用域：针对于所有会话（连接）有效，但不能跨重启(重启mysql恢复默认)

#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

#2》会话变量
#作用域：针对于当前会话（连接）有效

#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

1.2、自定义变量

自定义变量分为用户变量和局部变量

• 用户变量：作用域：针对于当前会话（连接）有效，作用域同于会话变量
• 局部变量：作用域：仅仅在定义它的begin end块中有效

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

#1》用户变量
#作用域：针对于当前会话（连接）有效，作用域同于会话变量

#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
#②赋值（更新变量的值）
#方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：
	SELECT 字段 INTO @变量名 FROM 表; #将查出来的一个值赋值给@变量名
#③使用（查看变量的值）
SELECT @变量名;


#2》局部变量
#作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话

#①声明
DECLARE 变量名 类型 【DEFAULT 值】;

#②赋值（更新变量的值）
#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT @局部变量名:=值;
#方式二：
	SELECT 字段 INTO 局部变量名 FROM 表;
  
#③使用（查看变量的值）
SELECT 局部变量名;

 
#案例：声明两个变量，求和并打印
#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

1.3、两者比较

变量	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

2、函数

2.1、函数介绍

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

函数好处

• 提高代码的重用性
• 简化操作
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

2.2、函数创建

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

注意

• 参数列表包含两部分：参数名、参数类型
• 函数体肯定会有return语句，没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议return值;
• 函数体中仅有一句话，则可以省略begin end
• 使用 delimiter 语句设置结束标记

2.3、函数查看

SHOW CREATE FUNCTION 函数名;

2.4、函数调用

SHOW CREATE FUNCTION 函数名;

例子练习

use myemployees;
delimiter $
#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;	#定义局部变量
    SELECT COUNT(*)
    INTO c#赋值
    FROM employees;
    RETURN c;
END $
SELECT myf1() $


#2.有参有返回

#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal = 0;	#定义用户变量
    SELECT salary
    INTO @sal			#赋值
    FROM employees
    WHERE last_name = empName;

    RETURN @sal;
END $
SELECT myf2('k_ing') $

#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE;
    SELECT AVG(salary)
    INTO sal
    FROM employees e
             JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = deptName;
    RETURN sal;
END $
SELECT myf3('IT')$

2.5、函数删除

DROP FUNCTION 函数名;

例子练习

DROP FUNCTION myf3;
#案例
#创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
    DECLARE SUM FLOAT DEFAULT 0;
    SET SUM = num1 + num2;
    RETURN SUM;
END $
SELECT test_fun1(1, 2)$

3、存储过程

3.1、存储过程介绍

一组预先编译并存储在数据库的SQL语句的集合，理解成批处理语句，可以配合函数使用

存储过程的好处

• 提高代码的重用性
• 简化操作
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

3.2、创建存储过程

语法

CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN 
    存储过程体（一组合法的SQL语句）
END

注意

• 如果存储过程体仅仅只有一句话，begin end可以省略
• 存储过程体中的每条sql语句的结尾要求必须加分号
• 存储过程的结尾可以使用delimiter重新设置
• 参数列表包含三部分:参数模式、参数名、参数类型

参数模式

• in：该参数可以作为输入，也就是该参数需要调用方传入值
• out：该参数可以作为输出，也就是该参数可以作为返回值
• inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型, ...)
BEGIN
    sql语句1;
    sql语句2;
END $

3.3、调用存储过程

CALL 存储过程名(实参列表)结束标记

例子练习

#1.空参列表
#调用
CALL myp1()$


#2.创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
             RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name = beautyName;
END $
#调用
CALL myp2('柳岩')$

#案例2 ：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;	#声明并初始化

    SELECT COUNT(*)
    INTO result	#赋值
    FROM admin
    WHERE admin.username = username
      AND admin.password = PASSWORD;
      
    SELECT IF(result > 0, '成功', '失败');	#使用
END $
#调用
CALL myp3('张飞', '8888')$


#3.创建out 模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyname
    INTO boyname
    FROM boys bo
             RIGHT JOIN
         beauty b ON b.boyfriend_id = bo.id
    WHERE b.name = beautyName;
END $

SET @bname$
CALL myp6('小昭',@bname)$
SELECT @bname$

#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT usercp INT)
BEGIN
    SELECT boys.boyname, boys.usercp
    INTO boyname,usercp
    FROM boys
             RIGHT JOIN
         beauty b ON b.boyfriend_id = boys.id
    WHERE b.name = beautyName;
END $
#调用
CALL myp7('小昭', @name, @cp)$
SELECT @name, @cp$

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
    SET a = a * 2;
    SET b = b * 2;
END $
#调用
SET @m = 10$
SET @n = 20$
CALL myp8(@m, @n)$
SELECT @m, @n$

3.4、删除存储过程

语法

drop procedure 存储过程名

补充

DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#❌一次只能删一个

DESC myp2;#❌
SHOW CREATE PROCEDURE myp2;