Mysql基础篇-06-视图-变量-存储过程-函数-流程控制结构

1.视图

1.1 含义

mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。好处：

• 简化sql语句
• 提高了sql的重用性
• 保护基表的数据，提高了安全性

1.2 创建

create view 视图名
as
查询语句;

1.3 修改

方式一：

create or replace view 视图名
as
查询语句;

方式二：

alter view 视图名
as
查询语句

1.4 删除

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

1.5 查看

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

1.6 使用

• 1.插入
  insert
• 2.修改
  update
• 3.删除
  delete
• 4.查看
  select

注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图

2. 视图和表的对比

• 视图view占用较小, 只保存sql逻辑，一般用于查询
• 表table，保存实际的数据，增删改查

2.1 创建

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 j.job_id  = e.job_id;

2.2 使用

SELECT * FROM myv1 WHERE last_name LIKE '%a%'

3. 变量

3.1 系统变量

说明：变量由系统提供的，不用自定义
语法：

• ①查看系统变量
  show 【global|session 】variables like ‘’; 如果没有显式声明global还是session，则默认是session
• ②查看指定的系统变量的值
  select @@【global|session】.变量名; 如果没有显式声明global还是session，则默认是session
• ③为系统变量赋值
  • 方式一：
    set 【global|session 】 变量名=值; 如果没有显式声明global还是session，则默认是session
  • 方式二：
    set @@global.变量名=值;
    set @@变量名=值；

3.2 系统变量

服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效

3.3 会话变量

服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

3.4 自定义变量

说明：
1、用户变量
作用域：针对于当前连接（会话）生效
位置：begin end里面，也可以放在外面
使用：

①声明并赋值：
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;

• ②更新值

  • 方式一：
    set @变量名=值;或
    set @变量名:=值;或
    select @变量名:=值;
  • 方式二：
    select xx into @变量名 from 表;

• ③使用
  select @变量名;

3.5 局部变量

作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：

• ①声明
  declare 变量名 类型 【default 值】;
• ②赋值或更新
  • 方式一：
    set 变量名=值;或
    set 变量名:=值;或
    select @变量名:=值;
  • 方式二：
    select xx into 变量名 from 表;
    ③使用
    select 变量名;

4.存储过程

4.1 创建

create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end

注意：

• 参数模式：in、out、inout，其中in可以省略
• 存储过程体的每一条sql语句都需要用分号结尾

4.2 调用

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

举例：

• 调用in模式的参数：call sp1（‘值’）;
• 调用out模式的参数：set @name; call sp1(@name);select @name;
• 调用inout模式的参数：set @name=值; call sp1(@name); select @name;

4.3 查看

show create procedure 存储过程名;

4.4 删除

drop procedure 存储过程名;

4.5 创建存储过程快速往两张表插入数据

-- 存储过程要是存在，先进行删除
DROP PROCEDURE IF EXISTS my_insert;
-- 定义存储过程
CREATE PROCEDURE my_insert(N countC int)
BEGIN
   DECLARE m,n int DEFAULT 0;
           START TRANSACTION;   -- 开启事务，关闭自动提交
                loopname1:LOOP
                        INSERT INTO t1(t1_col,t1_col2) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)),CONCAT('t11_',FLOOR(1+RAND()*1000)));
            SET n=n+1;
        IF n=countC THEN
            LEAVE loopname1;
        END IF;
        END LOOP loopname1;

        loopname2:LOOP
                        INSERT INTO t4(t4_col) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
            SET m=m+1;
        IF m=countC THEN
            LEAVE loopname2;
        END IF;
        END LOOP loopname2;
COMMIT;  -- 数据插入完毕，手动提交
END;

5. 函数

当创建函数失败的时候可以执行一下下面的命令

1. 当创建函数不成功时可以加上这行设置

set global log_bin_trust_function_creators=true

5.1 创建

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

注意：函数体中肯定需要有return语句

5.2 调用

select 函数名(实参列表);

5.3 查看

show create function 函数名;

5.4 删除

drop function 函数名；

5.5 生成随机字符串函数

-- 打开修改器
SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS rand_string;

CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END ;

6. 流程控制结构

说明：

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

6.1 分支结构

特点：

• if函数
  功能：实现简单双分支
  语法：
  if(条件，值1，值2)
  位置：
  可以作为表达式放在任何位置
• case结构
  功能：实现多分支
  语法1：
  case 表达式或字段
  when 值1 then 语句1;
  when 值2 then 语句2；
  …
  else 语句n;
  end [case];
  语法2：
  case
  when 条件1 then 语句1;
  when 条件2 then 语句2；
  …
  else 语句n;
  end [case];
  位置：
  可以放在任何位置，
  如果放在begin end 外面，作为表达式结合着其他语句使用
  如果放在begin end 里面，一般作为独立的语句使用
• if 结构
  功能：实现多分支
  语法：
  if 条件1 then 语句1;
  elseif 条件2 then 语句2;
  …
  else 语句n;
  end if;
  位置：
  只能放在begin end中

6.2 循环结构

位置：
只能放在begin end中
特点：都能实现循环结构

对比：

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

• ②loop 一般用于实现简单的死循环
  while 先判断后执行
  repeat 先执行后判断，无条件至少执行一次

• while
  语法：
  【名称:】while 循环条件 do
  循环体
  end while 【名称】;

• loop
  语法：
  【名称：】loop
  循环体
  end loop 【名称】;

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

6.3 循环控制语句

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