MySQL 学习笔记——基础 视图、变量、存储过程、函数、流程控制

MySQL 学习笔记——基础 视图、变量、存储过程、函数、流程控制

一、视图

1、概念及应用场景

概念：在 SQL 中视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。视图只保存SQL逻辑，不操作查询结果，查询结果根据数据表动态生成。

应用场景：

• 多个地方使用相同的查询结果
• 该查询结果使用的SQL语句较为复杂

2、视图的创建与使用

语法：

-- 创建视图
create view 视图名 as 查询语句;
-- 使用视图，将视图当成普通表就行。
select * from 视图名。

案例：

-- 1、查询每个部门的平均工资的工资等级以及部门名。from后面的子查询，在接一个左连接
create view avg_salary_department as 
select d.department_name, g.grade_level, s.`平均工资` from 
(select avg(e.salary) 平均工资, e.department_id from employees e group by e.department_id) s 
inner join job_grades g on s.`平均工资` between g.lowest_sal and g.highest_sal left join departments d on s.department_id = d.department_id;

-- 2、使用视图
select * from avg_salary_department;

3、视图的修改与删除

-- 1、修改 方法一
create or replace view 视图名 as 查询语句。

-- 2、修改 方法二
alter view 视图名 as 查询语句。

-- 3、删除
drop view 视图名, 视图名····;

-- 4、查看表和视图的两种方式
desc 表名或视图名;
show create view 表名或视图名;

4、视图数据的增删改查

-- 1、插入
insert into 视图名 values(值1, 值2, 值3···);

-- 2、更新
update 视图名 set 字段名=值 where 查询条件;

-- 3、删除
delete from 视图名 where 字段名=值;

-- 具备以下特点的视图不允许更新：
-- ① 视图或更新sql语句内包含以下关键字的sql语句：分组函数、distinct、group by、having、union或union all
-- ② 常量视图 如myname为常量视图。
create view myname as select 'john' name;  
-- ③ select中包含子查询不允许查询。
select or replace view myv3 as select (select max(salary) from employees) 最高工资;
-- ④ 创建视图的sql语句中包含join关键字，不能更新
-- ⑤ 由视图创建的视图不能更新
create or replace myv4 as select * from myv3;
-- ⑥ where子句的子查询引用了from子句中的表

5、视图和表的比较

	视图	表
创建语法的关键字	create view	create table
是否实际占用物理空间	没有，只保存了sql逻辑	占用，保存了数据
使用	增删改查，一般不能增删改	增删改查

二、变量

分类：

• 系统变量
  • 全局变量
  • 会话变量
• 自定义变量
  • 用户变量
  • 局部变量

1、系统变量

-- 1、查看所有的系统变量
show global variables;

-- 2、查看会话变量
show variables;
show session variables;

-- 3、查看满足条件的部分系统变量和部分会话变量
show global variables like 查询条件;
show [session] variables like 查询条件;

-- 4、查看某个具体的系统变量的值
select @@global.系统变量名;
select @@[session.]变量名

-- 5、为某个系统变量赋值
-- 方式一：
set global 系统变量名 = 值;
set [session] 变量名 = 值;
-- 方式二：
set @@global.系统变量名;
set @@[session.]变量名;

注意：如果是全局变量，需要添加global关键字，如果是会话级别，则需要添加session关键字。如果不写默认为session级别。

2、自定义变量

a、用户变量

作用域：针对于当前会话（连接）有效，同于会话变量的作用域。

-- 1、声明并初始化
-- 方式一
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
-- 方式二
select 字段 into @变量名 from 表名;
-- 案例：
set @name='john';
set @name=100;
set @count=1;
select count(*) into @count from employees;


-- 2、使用
select @变量名;
-- 案例：
select @count;   -- 107

-- 3、删除用户变量，设置空为删除。
set @变量名:=null;

b、局部变量

作用域：仅仅在begin end中有效，应用在begin end中的第一句话。并且变量的值要和类型要符合。

-- 1、声明
declare 变量名 类型;
declare 变量名 类型 default 值;

-- 2、赋值
-- 方式一
set 局部变量名=值
set 局部变量名:=值
select @局部变量名:=值
-- 方式二
select 字段 into 局部变量名 from 表名;

-- 3、使用
select @变量名;

c、用户变量和局部变量的对比

	用户变量	局部变量
作用域	当前会话	在begin end中
定义和使用位置	会话中的任何地方	在begin end中，且为第一句话
语法	必须加@符号，不用限定类型	一般不用加@符号，需要限定类型

三、存储过程

存储过程：一组预先编译好的SQL语句的集合，类似于批处理语句。简化操作，提高代码的重用性，减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。需要注意的是存储过程和函数需要在命令行客户端中运行。

1、存储过程创建

语法

create procedure 存储过程名 (参数列表)
begin 
存储过程体（一组合法的SQL语句）
end;

注意：

① 参数列表包含三部分：参数模式 参数名 参数类型。如：in stu_name varchar(20)

• 参数模式：
  • in：该参数可以作为输入
  • out：该参数可以作为输入，等同于返回值。
  • inout：该参数既可以作为输入，又可以作为返回值。

② 如果存储过程体仅仅只有一句话，那么begin end关键字可以省略。

③ 存储过程体中的每条SQL语句的结尾要求必须加分号，存储过程的结尾可以使用delimiter重新设置，语法：delimiter 结束标记。需要注意的是如果设置为delimiter 结束标记;，那么在其他语句结尾时应该用结束标记;而不是结束标记。

2、存储过程调用

a、无参数的存储过程

语法：

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

案例：

-- 1、创建测试表
create table test (
	`name` varchar(20),
	`password` varchar(20)
);

-- 2、设置终止符
delimiter $

-- 3、编写存储过程
create procedure myp1 () 
begin 
insert into test values('test1', '111'), ('test2', '111'), ('test3', '111');
end $

-- 4、调用存储过程
call myp1 ()$
-- +-------+----------+
-- | name  | password |
-- +-------+----------+
-- | test1 | 111      |
-- | test2 | 111      |
-- | test3 | 111      |
-- +-------+----------+

b、带in模式参数的存储过程

案例1：

-- 1、设置终止符
delimiter $

-- 2、编写存储过程，查询部门数量大于3的城市
create procedure myp2 (in count int) 
begin 
select l.city 城市, d.department_name 部门名, count(*) 部门数量 from departments d inner join locations l on d.location_id = l.location_id group by 城市 having 部门数量 > count;
end $

-- 3、调用存储过程
call myp2 (3)$
-- +---------+--------+----------+
-- | 城市    | 部门名 | 部门数量 |
-- +---------+--------+----------+
-- | Seattle | Adm    |       21 |
-- +---------+--------+----------+
call myp2 (0)$
-- +---------------------+--------+----------+
-- | 城市                | 部门名 | 部门数量 |
-- +---------------------+--------+----------+
-- | Seattle             | Adm    |       21 |
-- | Toronto             | Mar    |        1 |
-- | London              | Hum    |        1 |
-- +---------------------+--------+----------+

案例2：

-- 1、设置终止符
delimiter $

-- 2、编写存储过程，返回job_id与141号员工相同，工资比143号员工多的员工的姓名、job_id和工资。
create procedure myp3 (in e_id int, in e_salary int) 
begin  
-- 自定义变量，保存工种ID
declare employe_job varchar(20);
-- 自定义变量，保存工资
declare employe_salary int;
-- 为自定义变量 employe_job 赋值，将141号员工的工种id保存在id中
select job_id into employe_job from employees where employee_id = e_id;
-- 为自定义变量 employe_job 赋值，将141号员工的工种id保存在id中
select salary into employe_salary from employees where employee_id = e_salary;
-- 查询job_id与141号员工相同，工资比143号员工多的员工的姓名、job_id和工资。
select last_name 姓名, job_id 工种ID, salary 工资 from employees where job_id = employe_job and salary > employe_salary;
end $

-- 3、调用存储过程
call myp3 (141, 143)$
-- +-------------+----------+---------+
-- | 姓名        | 工种ID   | 工资    |
-- +-------------+----------+---------+
-- | Nayer       | ST_CLERK | 3200.00 |
-- | Mikkilineni | ST_CLERK | 2700.00 |
-- | Davies      | ST_CLERK | 3100.00 |
-- +-------------+----------+---------+

c、带out模式参数的存储过程

案例：

-- 1、设置终止符
delimiter $

-- 2、编写存储过程，查询员工号等于102的部门号
create procedure myp4 (in e_id int, out d_name varchar(20)) 
begin 
-- 定义局部变量。
declare d_id int;
-- 为自定义变量 d_id 赋值，将102号员工的部门号保存到d_id中
select department_id into d_id from employees where employee_id = 102;
-- 查询 d_id 对应的部门名，保存在返回值 d_name 中返回
select department_name into d_name from departments where department_id = d_id;
end $

-- 3、调用存储过程，并获取返回值。
call myp4 (102, @d_name)$

-- 4、使用返回值;
select @d_name as 部门名$
-- +--------+
-- | 部门名 |
-- +--------+
-- | Exe    |
-- +--------+

d、带inout模式参数的存储过程

案例1：

-- 1、设置终止符
delimiter $

-- 2、编写存储过程，查询员工号等于102的部门号
create procedure myp5 (inout a int, inout b int)
begin 
set a = a * 2;
set b = b * 2;
end $

-- 3、调用存储过程，并获取返回值。
set @m = 10$;
set @n = 20$;                 
call myp5 (@m, @n)$

-- 4、使用返回值;
select @m, @n$
-- +------+------+
-- | @m   | @n   |
-- +------+------+
-- |   20 |   40 |
-- +------+------+

3、删除存储过程

语法：drop procedure 存储过程名;

4、查看存储过程的信息

语法：show create procedure 存储过程名;

四、函数

函数：一组预先编译好的SQL语句的集合，类似于批处理语句。简化操作，提高代码的重用性，减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。需要注意的是存储过程和函数需要在命令行客户端中运行。

区别：

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

1、函数创建

语法：

create function 函数名 (参数列表) returns 返回类型
begin
函数体
-- 不一定要将return写在最后，但是推荐写在最后。
return 返回值;
end

注意：

① 由于MySQL 8.0默认不允许创建函数。如果需要创建SQL函数需要使用set global log_bin_trust_function_creators = 1，后才能使用SQL函数。

② 参数列表包含两个部分：参数名 参数类型。如：stu_name varchar(20)。

• 参数列表
  • 参数名
  • 参数类型
• 函数体：必须要有return语句，如果返回会报错。

③ 如果存储过程体仅仅只有一句话，那么begin end关键字可以省略。

④ 存储过程体中的每条SQL语句的结尾要求必须加分号，存储过程的结尾可以使用delimiter重新设置，语法：delimiter 结束标记。需要注意的是如果设置为delimiter 结束标记;，那么在其他语句结尾时应该用结束标记;而不是结束标记。

2、函数调用

语法：select 函数名 (参数列表)

a、无参数有返回值

-- 1、设置终止符
delimiter $

-- 2、编写函数，返回员工个数
create function myf1() returns int
begin 
-- 申明局部变量，保存员工个数
declare c int default 0;
-- 将查询的员工数量保存在c中
select count(*) into c from employees;
-- 返回查询结果
return c;
end $

-- 3、调用存储过程，并获取返回值。
select myf1()$
-- +--------+
-- | myf1() |
-- +--------+
-- |    107 |
-- +--------+

b、有参数有返回值

案例1：

-- 1、设置终止符
delimiter $

-- 2、编写函数，根据员工名，返回他的工资。
create function myf2(e_name varchar(20)) returns int
begin 
-- 申明一个用户变量，保存员工工资
set @e_salary:=0;
-- 将查询的员工数量保存在c中
select salary into @e_salary from employees where last_name = e_name;
-- 返回查询结果
return @e_salary;
end $

-- 3、调用存储过程，并获取返回值。
select myf2('Kochhar')$
-- +-----------------+
-- | myf2('Kochhar') |
-- +-----------------+
-- |           17000 |
-- +-----------------+

案例2：

-- 1、设置终止符
delimiter $

-- 2、编写函数，根据部门名，返回该部门的平均工资。
create function myf3(d_name varchar(20)) returns int
begin 
-- 申明变量，保存员工个数
declare e_salary int default 0;
-- 将查询的员工数量保存在c中
select avg(e.salary) into e_salary from employees e left join departments d on e.department_id = d.department_id where d.department_name = d_name;
-- 返回查询结果
return e_salary;
end $

-- 3、调用存储过程，并获取返回值。
select myf3('IT')$
-- +------------+
-- | myf3('IT') |
-- +------------+
-- |       5760 |
-- +------------+

3、删除函数过程

语法：drop function 函数名;

4、查看函数的信息

语法：show create function 函数名;

五、流程控制

分类：

• 分支结构
  • if 函数
  • case 结构
  • if 结构
• 循环结构
  • while 结构
  • loop 结构
  • repeat 结构
  • 循环控制关键字
    • iterate：类似于Java中的continue，结束本次循环，继续下一次。
    • leave：类似于Java中的break，结束循环，没有下一次。

1、分支结构

a、if 函数

语法：if(表达式1, 表达式2, 表达式3)

执行顺序：如果表达式1成立，则返回表达式2的值，否则返回表达式3的值。

b、case 结构

语法：

-- 实现等值判断
case 变量 | 表达式 | 字段
when 要判断的值1 then 返回的值1或语句1;
when 要判断的值2 then 返回的值2或语句2;
···
else 要返回的值n或语句n;
end case;

-- 实现多重if判断
case 
case 变量 | 表达式 | 字段
when 要判断的值1 then 返回的值1或语句1;
when 要判断的值2 then 返回的值2或语句2;
···
else 要返回的值n或语句n;
end case;

特点：

① ： 作为表达式：嵌套在其他语句中使用，可以放在任何地方，begin end中或者begin end的外面。作为独立的语句：只能放在begin end。

② ：如果when中的值满足或者条件成立，则执行then后面的语句，并结束case。如果都不满足，则执行else中的值或者语句。

③ ：else可以省略，如果else省略，并且所有的when中的值都不满足，则返回null。

案例：

-- 1、设置终止符
delimiter $

-- 2、编写存储过程，根据年龄分段。
create procedure test_case (in age int)
begin 
case
when age < 140 and age >= 60 then select '老人' as 年龄段;
when age < 60 and age >= 30 then select '中年' as 年龄段;
when age < 30 and age >= 18 then select '青年' as 年龄段;
when age < 18 and age >= 6 then select '少年' as 年龄段;
when age < 6 and age > 0 then select '幼儿' as 年龄段;
else select '这还是人嘛！' as 年龄段;
end case;
end $

-- 3、调用
call test_case(20)$
-- +--------+
-- | 年龄段 |
-- +--------+
-- | 青年   |
-- +--------+
call test_case(220)$
-- +--------------+
-- | 年龄段       |
-- +--------------+
-- | 这还是人嘛！ |
-- +--------------+

c、if 结构

语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
elseif 条件3 then 语句3;
···
[else 语句n;]
end if;

特点：只能应用在begin end中。

案例：

-- 1、设置终止符
delimiter $

-- 2、编写函数，根据传入的成绩，返回成绩等级
create function test_if (score int) returns char
begin 
if score >= 90 and score <= 100 then return 'A';
elseif score >= 80 and score < 90 then return 'B';
elseif score >= 60 and score < 80 then return 'C';
elseif score >= 0 and score < 60 then return 'D';
else return 'E';
end if;
end $

-- 3、调用
select test_if(80) as 成绩等级$
-- +----------+
-- | 成绩等级 |
-- +----------+
-- | B        |
-- +----------+

2、循环结构

a、while 循环

语法：

[标签:] while 循环条件 do 
循环体;
end while [标签];

案例：

-- 1、设置终止符
delimiter $

-- 2、编写存储过程，想test表批量插入数据。
create procedure test_while (in count int)
begin 
-- 定义变量
declare i int default 0;
-- 开始循环
t_w: while (i < count) do
insert into test values (concat('test_name_', i), concat('test_password_', i));
set i = i + 1;
end while t_w;
end $

-- 3、调用
call test_while(5)$
select * from test $
-- +-------------+-----------------+
-- | name        | password        |
-- +-------------+-----------------+
-- | test_name_0 | test_password_0 |
-- | test_name_1 | test_password_1 |
-- | test_name_2 | test_password_2 |
-- | test_name_3 | test_password_3 |
-- | test_name_4 | test_password_4 |
-- +-------------+-----------------+

b、loop 循环

语法：

[标签:] loop 
循环体;
end loop [标签];

c、repeat 循环

语法：

[标签:] repeat 
循环体;
until 结束循环的条件
end repeat [标签];