一、视图

视图：虚拟表,和普通表一样使用。mysql 5.1版本出现的新特性，是通过表动态生成的数据

视图应用场景：

• 多个地方用到同样的查询结果
• 该查询结果使用的sql语句较复杂

（有点函数的味道）

视图的优点：

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

1. 视图的创建

语法：create view 视图名 as 查询语句

案例：

USE myemployees;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建视图
CREATE VIEW myv1 AS
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
JOIN `departments` d ON e.`department_id` = d.`department_id`
JOIN `jobs` j ON e.`job_id` = j.`job_id`;
#②使用视图
SELECT * FROM myv1 WHERE `last_name` LIKE '%a%';

#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2 AS
SELECT AVG(`salary`) ag, `department_id`
FROM `employees` GROUP BY `department_id` 
#②使用视图
SELECT myv2.ag,g.`grade_level`
FROM myv2 JOIN `job_grades` g 
ON myv2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#3.查询平均工资最低的部门信息
#使用创建好的视图2
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和平均工资
#①创建视图查看平均工资最低的部门信息
CREATE VIEW myv3 AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1; # 套娃
#②使用视图3
SELECT d.`department_name`,m.ag
FROM myv3 m JOIN `departments` d
ON m.`department_id` = d.`department_id`;

2. 视图的修改

语法：

#方式一：
create or replace view 视图名 as 查询语句;
#方式二：
alter view 视图名 as 查询语句;

3. 视图的删除

用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限

语法：

drop view 视图1，视图2,…;

4. 视图的查看

语法：

desc 视图名;
show create view 视图名;

5. 视图的更新

视图是一个虚拟表，可以对其结果进行更新（增删改）的操作，但一般只用来查询

更新操作：插入：insert 修改：update 删除：delete

值得注意的是：对视图的增删改操作都会改变视图from的原表

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;

#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';

视图一般是用于查询的，而不是更新的，因为有很多情况是不能更新的，这些情况覆盖了大部分语句，

• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

6. 视图vs表

	关键字	是否占用物理空间	使用场景
视图	view	占用较小，只保存sql逻辑	一般用于查询
表	table	保存实际的数据	增删改查

二、变量

1. 系统变量

系统变量由系统定义，不是用户定义，属于服务器层面，系统变量分为全局变量和会话变量。

全局变量：需要添加global关键字，作用域针对于所有会话（连接）有效，但不能跨重启

会话变量：需要添加session关键字，作用域针对于当前会话（连接）有效，如果不写，默认会话级别。

系统变量使用语法：

• 查看所有系统变量

  show global|【session】 variables;
  

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

  show global|【session】 variables like '%char%';
  

• 查看指定的系统变量的值（只能用点式结构）

  select @@global.|【session.】系统变量名;
  

• 为某个系统变量赋值（两种方式，方便记忆的话可以统一记点式结构）

  #方式一：
  set @@global|【session】.系统变量名=值;
  #方式二：
  set global|【session】系统变量名=值;
  

案例：

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


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

2. 自定义变量

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

2.1 用户变量

用户变量由用户自定义，而不是系统提供的，作用域：针对于当前会话（连接）有效，作用域同于会话变量，即可以应用在当前会话的任何地方，begin end里面和外面都可以

语法：

#赋值操作符：=或:=
#①声明并初始化，不用指定数据类型，会根据初始化的值自动分配数据类型，必须要初始化，只声明不行！！！
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;  #select赋值一般不用，要用的话必须只能搭配:=符号！！！

#②赋值（更新变量的值）
#方式一：
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
#方式二：利用select语句将表的信息赋给某个值
SELECT 字段1,字段2,… INTO @变量名1,@变量名2,…
FROM 表;

#③使用（查看、比较、运算等）
SELECT @变量名;

注意点：要搭配@符号使用、用户变量必须声明时初始化

实例：

#声明并初始化
SET @name = 'john';
SET @num := 100;
SELECT @count := 1;

#赋值(更新值)
#将major表的统计信息赋给count
SELECT COUNT(*) INTO @count
FROM major;

#使用
SELECT @name;
SELECT @num;
SELECT @count;

2.2 局部变量

局部变量的作用域仅在定义它的begin end块中有效，应用在 begin end中的第一句话。它与用户变量不同，声明时需要指定数据类型，不用加@符号

语法：

#①声明，不支持select声明方式
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;

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

实例：

#局部变量声明
DECLARE name1 VARCHAR;
DECLARE num INT DEFAULT 1;
DECLARE count1 INT;

#赋值（更新变量值）
SET name1 = 'tom';
SET num := 2;
SELECT COUNT(*) INTO count1
FROM major;

#使用
SELECT name1;
SELECT num;
SELECT count1;

2.3 用户变量和局部变量的对比

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

#案例：声明两个变量，求和并打印
#用户变量
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;

三、存储过程

类似于Java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字，理解成批处理语句

好处

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

1. 存储过程的创建

1.1 创建语法

CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型)
BEGIN
	存储过程体(一组合法的SQL语句)
END

如果存储过程体仅仅只有一句话，begin end可以省略，存储过程体中的每条sql语句的结尾要求必须加分号。

1.2 参数模式

• in：该参数可以作为输入，也就是该参数被调用时需要传入值
• out：该参数可以作为输出，也就是该参数可以作为返回值 （存储过程没有return，在调用时声明变量接收即可）
• inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

1.3 结束标记

MySQL默认的结束标记是分号; 但有时候并不想让解释器碰到分号就结束，此时可以使用关键字delimiter 指定结束标记，让解释器碰到指定标记时才开始执行所有语句

语法：delimiter 结束标记

实例：delimiter $，即告诉解释器碰到$才执行语句

注意：不要加分号，否则会认为碰到$;才执行

2. 存储过程的调用

调用语法：

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

举例：

#调用in模式的参数
CALL p1('值')

#调用out模式的参数
SET @name;
CALL p2(@name);
SELECT @name;

#调用inout模式的参数
SET @name = 值;
CALL p3(@name);
SELECT @name;

创建和调用过程实例：需要在命令提示符中使用

#设置结束标记
DELIMITER $

#1.空参列表:向major表中插入五条记录
#切换数据库
USE students
#创建存储过程
CREATE PROCEDURE myp1()
BEGIN 
	INSERT INTO major VALUES(1,'java'),(2,'c++'),(3,'python'),(4,'golang'),(5,'sql');	
END $
#调用存储过程
CALL myp1()$


#2.创建带in模式参数的存储过程：根据员工名，查询对应的部门名信息
#切换数据库
USE myemployees
#创建存储过程
CREATE PROCEDURE myp2(IN e_name VARCHAR(20))
BEGIN
	SELECT e.`last_name`,d.`department_name`
	FROM `employees` e
	LEFT JOIN `departments` d ON e.`department_id`=d.`department_id`
	WHERE e.`last_name` = e_name;#e_name属于局部变量，可以在当前作用域直接使用
END$
#调用存储过程
CALL myp2('K_ing')$


#3.创建带多个in模式参数的存储过程：判断输入的部门名和位置ID是否匹配
#创建存储过程
CREATE PROCEDURE myp3(IN d_name VARCHAR(20),IN locat_id INT)
BEGIN
	DECLARE result INT DEFAULT 0;#创建一个变量用于存放判定的结果，创建用户变量来存放也可以
	
	SELECT COUNT(*) INTO result
	FROM `departments` d
	WHERE d.`department_name` = d_name
	AND d.`location_id` = locat_id;
	
	SELECT IF(result>0,'成功','失败');
END$
#调用存储过程
CALL myp3('Adm','1700')$ #成功
CALL myp3('Adm','1800')$ #失败


#4.创建out模式参数的存储过程：根据输入的员工名，返回对应的部门名和location_id
#创建存储过程
CREATE PROCEDURE myp4(IN e_name VARCHAR(20),OUT d_name VARCHAR(20),OUT locat_id INT)
BEGIN
	SELECT d.`department_name`,d.`location_id` INTO d_name,locat_id
	FROM `employees` e
	LEFT JOIN `departments` d
	ON e.`department_id`=d.`department_id`
	WHERE e.`last_name` = e_name;
END$
#调用存储过程
SET @d_name='';#定义用户变量，这行可省略
SET @locat_id=0;#定义用户变量，这行可省略
CALL myp4('Abel',@d_name,@locat_id);#调用存储过程
SELECT @d_name,@locat_id$#查看结果：Sal,2500


留一个问题，调用下面这个时，返回的是两条结果，该用什么变量接收呢
CALL myp4('K_ing',@d_name,@locat_id);


#5.创建带inout模式参数的存储过程：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2; 
END$

#调用存储过程
SET @m=10;
SET @n=20;
CALL myp5(@m,@n);
SELECT @m,@n$ #20,40

3. 存储过程的查看

语法：

show create procedure 存储过程名;

不能使用desc

4. 存储过程的删除

语法：

drop procedure 存储过程名;

每次只能删除一个，如DROP PROCEDURE p1;是可以的，DROP PROCEDURE p2,p3;不可以

存储过程没有修改，想要改就先删除再添加新的

四、函数

一组预先编译好的SQL语句的集合，理解成批处理语句。有且仅有一个返回

1. 与存储过程的区别

• 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新；
• 函数：有且仅有1 个返回，适合做处理数据后返回一个结果。

2. 函数的创建

MySQL有个参数SET GLOBAL log_bin_trust_function_creators=TRUE ，如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。

因此在创建函数时需要将这个参数设置为1

SET GLOBAL log_bin_trust_function_creators=TRUE;

语法：

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

注意点：

• 注意关键字 function 和 returns
• 函数体中需要加上return语句return 值，虽然不加不报错，但没有意义
• 函数体中仅有一句话，则可以省略begin end
• 使用 delimiter语句设置结束标记

3. 函数的调用

语法：

SELECT 函数名(参数列表);

案例：

SET GLOBAL log_bin_trust_function_creators=TRUE;
#1.无参有返回：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0; #定义局部变量
	SELECT COUNT(*) INTO c
	FROM employees;
	RETURN c;
END$

SELECT myf1()$


#2.有参有返回：根据员工名，返回它的工资
CREATE FUNCTION myf2(e_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; #定义用户变量
	SELECT e.`salary` INTO @sal
	FROM `employees` e
	WHERE e.`last_name` = e_name;
	RETURN @sal;
END$

SELECT myf2('Abel')$

返回值定义为局部变量和用户变量都可以，局部变量更常用

4. 函数的查看

语法：

show create function 函数名;

5. 函数的删除

语法：

drop function 函数名;

五、流程控制

• 顺序结构：程序从上往下依次执行
• 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
• 循环结构：程序满足一定条件下，重复执行一组语句

1. 分支结构

1.1 if函数

用于判断简单的情况

语法：

if(表达式1,表达式2,表达式3)

判断表达式1，为真执行表达式2，为假执行表达式3，类似三元表达式

SELECT IF(10<5,'大','小');

SELECT last_name,commission_pct,IF(`commission_pct` IS NULL,'没奖金','有奖金')AS 备注 FROM employees;

1.2 if结构

用于判断复杂的情况

语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;

此结构只能用在begin end 中

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	IF score >90 THEN RETURN 'A';
	ELSEIF score>= 80 THEN RETURN 'B';
	ELSEIF score >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END$

SELECT test_if(86)$

1.3 case结构

case结构有两种用法：

• 第一种类似于Java中switch case的效果
• 第二种类似于多重if的效果

case结构可以放在任何地方，

• 如果放在begin end 外面，作为表达式结合着其他语句使用；（下面第一个案例可以体现）

• 如果放在begin end 里面，一般作为独立的语句使用

1.3.1 when后面加常量

语法：

case 变量或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

/*
案例:查询员工的工资,要求:
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资,按department_id升序
*/
SELECT `salary` 原始工资,`department_id`,  # 这一行最后的逗号容易漏，因为case结构放在begin end之外，作为单独表达式，需要加逗号隔开
CASE `department_id`
WHEN 30 THEN `salary`*1.1
WHEN 40 THEN `salary`*1.2
WHEN 50 THEN `salary`*1.3
ELSE `salary`
END AS 新工资
FROM employees
ORDER BY `department_id` ASC;

1.3.2 when后面加条件

语法：

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

和上一个用法相比就是少了case后面的语句，when后面由常量换为条件

#案例 
#创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100, 显示A，80-90，显示B，60-80，显示c，否则，显示D

CREATE PROCEDURE test_case (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 $

CALL test_case(95)$

2. 循环结构

位置：只能放在begin end中

三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称

2.1 while

类似于Java中的while

语法：

【名称:】while 循环条件 do
		循环体
end while 【名称】;

2.2 repeat

类似于Java中的do-while

语法：

【名称:】repeat
		循环体
until 结束条件 
end repeat 【名称】;

2.3 loop

死循环

语法：

【名称：】loop
		循环体
end loop 【名称】;

2.4 leave/iterate

• leave：类似于break，用于跳出所在的循环
• iterate：类似于continue，用于结束本次循环，继续下一次

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到major表中多条记录
USE students;

CREATE PROCEDURE while_test(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO major VALUES(i,'mysql');
		SET i = i+1;
	END WHILE;
END$

CALL while_test(100)$


#2.添加leave语句
#案例：批量插入，根据次数插入到major表中多条记录，如果次数>20则停止
TRUNCATE TABLE major$

CREATE PROCEDURE while_test1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO major VALUES(i,'mysql');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i = i+1;
	END WHILE a;
END$

CALL while_test1(100)$


#3.添加iterate语句
#案例：批量插入，根据次数插入到major表中多条记录，只插入偶数次
TRUNCATE TABLE major$

CREATE PROCEDURE while_test2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		SET i = i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO major VALUES(i,'mysql');
	END WHILE a;
END$

CALL while_test2(100)$