尚硅谷李玉婷老师MySQL课程--视图、变量、存储过程和函数、流程控制结构

一、视图

1.视图的创建

CREATE VIEW 视图名
AS
查询语句;

例1：

create view myv
as
select avg(salary),job_id
from employees
group by job_id;

例2：创建视图myv1，要求查询电话号码一’011’开头的员工姓名和工资、邮箱

create or replace view myv1
as
select last_name,salary,email
from employees
where phone_number like '011%';

例3：创建视图myv3，要求查询部门的最高工资高于12000的部门信息

create or replace view myv2
as
select max(salary) mx,department_id
from employees
group by department_id
having max(salary)>12000;

select d.*,m.mx
from departments d
inner join myv2 m
on d.department_id=m.department_id
where m.mx>12000;

2. 视图的修改

方式一:

CREATE OR REPLACE VIEW 视图名
AS
查询语句;

例：

create or replace view myv
as
select avg(salary),job_id
from employees
group by job_id;

方式二:

ALTER  视图名
AS
查询语句;

例：

alter view myv
as
select avg(salary),job_id
from employees
group by job_id;

3. 视图的删除

DROP VIEW 视图名,视图名;

4. 视图的查看

DESC 视图名;

5. 视图的更新

具备以下特点的视图不允许更新：
1.包含以下关键字：distinct、group up、having、union\union all
2.常量视图

create or replace view myv
as
select 'john' NAME;

3.select中包含子查询
4.多表连接
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表

二、变量

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

1.系统变量

说明： 变量由系统提供，不是用户自定义，属于服务器层面
会话变量
作用域：仅仅针对与当前会话（连接）有效
语法：
说明：全局变量（global）、会话变量（session）。session可省略

1. 查看所有系统变量
show global|【session】 variables;

2. 查看满足条件的部分系统变量
show global|【session】 variables like 条件;

3. 查看指定的某个系统变量的值
select @@global|【session】.系统变量名;

4.为某个系统变量赋值
方式一：
set global|【session】 系统变量名 = 值;
方式二：
set @@global|【session】.系统变量名 = 值;

2.自定义变量

说明： 变量是用户自定义的，不由系统提供
使用步骤： 声明、赋值、使用（查看、比较、运算等）

(1)用户变量

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

• 声明并初始化

   set @用户变量名=值;
   或
   set @用户变量名:=值;
   或
   select @用户变量名=值;
  

• 赋值（更新用户变量的值）

   方式一：
   set @用户变量名=值;
   或
   set @用户变量名:=值;
   或
   select @用户变量名=值;
   方式二：
   select 字段 into 用户变量名
   from 表;
  

(2)局部变量

**作用域：**仅仅在定义它的begin end中有效
应用在begin end中的第一句话

• 声明并初始化

   declare 变量名 类型;
   declare 变量名 类型 default 值;
  

• 赋值

   方式一：
   set 局部变量名=值;
   或
   set 局部变量名:=值;
   或
   select 局部变量名=值;
   方式二：
   select 字段 into 局部变量名
   from 表;
  

• 使用

   select 局部变量名;
  

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

三、存储过程和函数

存储过程和函数：类似于Java中的方法
好处：

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

1.存储过程

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

• 无返回无参
• 仅仅带in类型，无返回有参
• 仅仅带out类型，有返回无参
• 既带in又带out，有返回有参
• 带inout，有返回有参
  注意：in、out、inout都可以在一个存储过程中带多个

(1)创建存储过程

语法

create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
begin
	存储过程体
end

注意
1、存储过程的结尾需要设置新的结束标记

delimiter 新的结束标记
示例：
delimiter $

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

END $

2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end

3、参数模式：参数前面的符号的意思
in:该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出

(2)调用存储过程

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

2.函数

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

(1)创建

语法

create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

注意
1.参数列表 包含两部分：参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错。return 值
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记

(2)调用

select 函数名(参数列表)

四、流程控制结构

1.分支结构

(1)if函数

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

如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

(2)case结构

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

   case 变量|表达式|字段
   when 要判断的值 then 返回的值1;
   when 要判断的值 then 返回的值2;
   ...
   else 返回的值n;
   end case;
  

• 情况2：类似Java的嵌套if语句，一般用于实现区间判断

   case
   when 条件1 then 返回的值1或语句1;
   when 条件2  then 返回的值2或语句1;
   ...
   else 返回的值n或语句n;
   end case;
  

特点
1.
可以作为表达式嵌套在其他的语句中使用，可以放在 任何地方；
可以作为独立的语句去使用，只能放在begin end中
2.else可以省略，如果省略了else，并且所有when条件都不满足，则返回null

(3)if结构

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

只能应用在begin end中

2.循环结构

分类： while、loop、repeat
循环控制：
iterate：类似于continue
leave：类似于break

(1)while

【标签：】while  循环条件 do
	循环体;
end while 【标签】;

(2)loop

【标签：】loop
	循环体;
end loop 【标签】; 

不使用leave为死循环

(3)repeat

【标签：】repeat
	循环体;
until 结束循环条件
end repeat 【标签】;