第五篇：其余内容

本部分主要介绍MySQL基础中的其余内容，主要包含四大模块：视图，变量，存储过程和函数以及流程控制。
本章是数据库MySQL基础篇的最后一篇。

Part 1: 视图

视图是将一部分MySQL代码封装起来，做成类似于函数的形式，进行调用。视图的内容是生成一张表格的逻辑，结果是一个虚拟表。

备注：视图只是逻辑，并不存储数据，当需要调用的时候，系统运行即可产生数据，组成虚拟表格。

1.1 视图的创建

视图创建的语法如下：
createview 视图名
as
DQL查询语言；

例1：查询员工表employees中员工邮箱名字包含字母a的员工的姓名，工号和工资
首先创建视图 

createview e
as
select name, id, salary
from employees
where email like '%a%';

1.2 视图的使用

视图创建完毕之后，会产生相关的字段，这里的字段与原来的表格已经不同了，新生成的字段取决于创建视图的时候的字段名。
例1：查询员工表employees中员工邮箱名字包含字母a的员工的姓名，工号和工资
然后使用视图 

select * from e;

1.3 视图的修改

视图修改的语法有两种：

  1. create or replace view 视图名 as DQL查询语言；
  2. alter view 视图名 as DQL查询语言；

    备注：究其本质，视图的修改和创建没有什么区别，只是关键字换了。

1.4 视图的删除

删除视图的语法格式如下：
drop view 视图名；
这个语法可以一次删除多个，形式如下：
drop view 视图名1， 视图名2,…;
例2：删除例1创建的视图e

drop view e;

1.5 视图的查看

查看视图的语法格式如下：
show create view 视图名；

1.6 视图的更新

1.插入
插入语法与DML语言讲解的并无差别；
例3：在视图e中插入一个员工信息：张三，007,10000； 

insert into e values('张三','007',10000);

备注：这里的插入会使得原始表的数据发生变化。

2.修改
修改语法与DML语言讲解的并无差别；
例4：在视图e中修改员工张三的工资为12000；

update e set salary = 12000 where name = '张三';

3.删除
删除语法与DML语言讲解的并无差别；
例5：在视图e中删除员工张三的信息；

delete from e where name = '张三';

备注：一般不建议更新视图，因为视图本身的保存为逻辑，更新数据的话，会给视图的保存带来困难。
备注：当具备以下几种情形时，不可以更新视图：

  1. 包含分组函数，distinct, group by, having, union 或者 union all;
  2. select 包含子查询;
  3. join

Part 2：变量

之前曾在MySQL基础4中介绍过MySQL的数据类型，这里对变量进行介绍。

备注：根据命名渠道，变量可以分为两种：系统变量和自定义变量。
根据作用范围，系统变量可分为：全局变量和会话变量
根据作用范围，自定义变量可分为：用户变量和局部变量

2.1 系统变量

是由系统定义在服务器层面的变量。

  1. 查看全局系统变量：show global variables;
  2. 查看会话全局变量：show session variables;
  3. 当需要增加查看的筛选条件时：
    show global/session variables 筛选条件;
  4. 查看某个系统指定的变量的值：select @@系统变量名；
  5. 为某个系统变量赋值
    set global/session 系统变量名 = 值；
    set global/session.系统变量名 = 值；

2.2 自定义变量

是自己设置的，遵循“声明”-“赋值”-“使用”的顺序。
2.2.1 用户变量
用户变量是在整个会话中都可以使用的变量，

  1. 声明并初始化：set @变量名 = value 或者 set @变量名 ：= value 或者 select @用户变量名 ：= value;
  2. 赋值语句：select 字段 into 变量名 from 表名；
    2.2.2 局部变量
    局部变量是在begin end中间的变量
  3. 声明 declare 变量名 类型； 或者 declare 变量名 类型 default 值；
  4. 赋值与用户变量的赋值一样

备注：用户变量和局部变量的区别

  1. 用户变量作用在整个会话中，而局部变量只能作用在begin end中；
  2. 用户变量的语法必须加@符号不用限定类型，而局部变量则不用加@但是需要限定类型；

Part 3:存储过程和函数

存储过程和函数，类似于Python中的方法，具有提高代码的可重复性，简化操作的优点。

3.1 存储过程

存储过程可以理解为一组预先编译好的sql语言集合，类似于批处理文件。
存储过程的优点如下：

  1. 提高了代码的可重复性；
  2. 简化了操作；
  3. 减少了编译次数以及和服务器连接的次数，从而提高了效率；

存储过程的创建语法：
create process 存储过程名（参数列表）
begin
存储过程体（一般的SQL语法）
end

备注：参数列表中的参数包含三部分（参数模式， 参数名， 参数类型）
参数模式：in out inout分表表示传入参数，传出参数以及都可以的参数；
备注：如果存储过程体只有1句话，那么begin 和 end 可以省略；
存储过程体中的每句SQL语言必须加分号；
存储过程的结尾可以用delimiter重新设置；

存储过程的调用语法：call 存储过程名(实参列表）；
例6：向employees表中插入5条记录： 

delimiter $  #首先设置结束标记
create procedure pro1()
begin 
     insert into employees(字段名） values(值）；
end $

例7：创建存储过程，实现输入女生名，返回她的男朋友的信息： 

delimiter $    
create procedure pro2(in name varchar(20))    
begin     
      select boys.name    
      from boys   
      right join girls   
      on girls.boyfreind_id = boys.id   
      where girls.name = name;   
end $   
call pro2('小龙女')$;# 调用过程也要加上结尾符号

例8：创建存储过程，实现输入女生名，返回她的男朋友的信息： 

delimiter $
create procedure pro3(in grils_name varchar(20)， out boys_name varchar(20))
begin 
      select boys.name into boys_name
      from boys
      right join girls
      on girls.boyfreind_id = boys.id
      where girls.name = name;
end $    
call pro3('小龙女',@boyname)$ # 调用过程也要加上结尾符号    
select @boyname$  # 查看用户变量的时候，需要加@

例9：传入a和b两个值，将它们翻倍： 

delimier $
create procedure pro4(inout a int, inout b int)
begin 
     set a = a*2;
     set b = b*2;
end $
set @m = 10$
set @n = 20$
call pro4(@m, @n)$
select @m $
select @n $

存储过程的删除：drop procedure 存储过程名；

备注：每次只能删除一个存储过程；

存储过程的查看：show creat procedure 存储过程名；

3.2 函数

与存储过程非常类似，也可以理解为一组SQL语言的批处理形式。
函数与存储过程的区别在于：函数必须有1个返回值，存储过程对返回值不做要求。

函数的创建语法：
create function 函数名(参数列表) returns 返回类型
begin
函数体
end

备注：参数列表包含两部分：参数名，参数类型；
备注：函数体必须要有return命令，可以不放在函数体的最后；
备注：依然可以通过设置delimiter $设置结尾；

例10：根据员工表employes返回公司的员工个数:

create function numCounting() returns int
begin 
    declare c int default 0;
    select count(*) into c
    from emplpoyees;
    return c;
end $
select numCounting() $ # 调用的时候，直接用select

例11：根据员工名返回他的工资:

create function salaryFind(name varchar(20)) returns double
begin 
      sel @sal = 0;
      select salary into @sal
      from emplpoyees
      where employees.name = name;
      return @sal;
end $
select salaryFind('张三') $ #调用

函数的查看：show create function 函数名;
函数的删除：drop function 函数名；

Part 4:流程控制简介

之前在C++或者Python中学到的流程为：顺序流程，分支流程，循环流程。在这里，流程也一样。

4.1 分支结构

  1. if函数
    语法：if（表达式1， 表达式2， 表达式3）
    与c++三元结构组一致，表达式1成立则执行2，否则执行3；
  2. case函数
    case有两种用法：
    类型1：
    case 变量/表达式/字段
    when 要判断的值 then
    …
    else 操作
    end case;
    类型2：
    case
    when 要判断的条件1 then 操作1
    when 要判断的条件2 then 操作2
    …
    else 操作n
    end case;
    例12：输入成绩，判断成绩等级： 
    create procedure scoreLevel(in score int)
begin 
  case 
  when score>90 then select 'A';
  when score>=80 then select 'B';
  when score>=60 then select 'C';
  else select 'D';
  end
end $

  3. if结构
    if结构用于多重分支结构，语法如下：
    if 要判断的条件1 then 操作1；
    elseif 要判断的条件2 then 操作2；
    …
    end if;
    这种if结构要用在begin end内部。
    例12：输入成绩，判断成绩等级： 

    create function scoreLevel(score int) 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 $

    4.2 循环结构

    可能出现的形式：while loop repeat
    循环控制：iterate类似于continue, leave类似于break;

  4. while
    语法：
    while 循环条件 do
    循环体
    end while

  5. loop
    语法：
    loop
    循环体
    end loop;

  6. repeat
    语法：
    repeat
    循环体
    until 结束循环的条件
    end repeat

例13：向员工表employees中多次插入张三的信息： 

create procedure pro6(in insertCount int)
begin 
     declare i int default 1;
     while i < insertCount do
         insert into employees('name', 'salary') values('张三','1000')
         set i = i + 1
     end while;
end $

备注：三种循环结构的比较
while 先判断后执行； repeat先执行后判断； loop没有限制的死循环，必须加限制条件；

Part 5:MySQL基础结语

MySQL是必备技能，基础部分的内容比较好学。后续将继续更新MySQL高级的内容。


本博客所有文章除特别声明外，均采用 CC BY-SA 3.0协议 。转载请注明出处！