视图

  • 视图
    • 概念：在mysql5之后出现的，是⼀种虚拟表，⾏和列的数据来⾃于定义视图时使⽤的⼀些表中，视图的数据是在使⽤视图的时候动态⽣成的，视图只保存sql的逻辑，不保存查询的结果。
    • 使⽤场景：多个地⽅使⽤到同样的查询结果，并且该查询结果⽐较复杂的时候，我们可以使⽤视图来隐藏复杂的实现细节
  • 视图的好处
    • 简化复杂的sql操作，不⽤知道他的实现细节
    • 隔离了原始表，可以不让使⽤视图的⼈接触原始的表，从⽽保护原始数据，提⾼了安全性
  • 创建视图 create view 视图名 as 查询语句;
  • 视图的使⽤步骤
    • 创建视图
    • 对视图执⾏查询操作
  • 更新视图【基本不⽤】
    • 视图的更新是更改视图中的数据，⽽不是更改视图中的sql逻辑
    • 当对视图进⾏更新后，也会对原始表的数据进⾏更新
    • 为了防⽌对原始表的数据产⽣更新，可以为视图添加只读权限，只允许读视图，不允许对视图进⾏更新
    • ⼀般情况下，极少对视图进⾏更新操作
  1. -- 查询姓名中包含a字符的员⼯名、部门、⼯种信息
  2. CREATE VIEW myv1
  3. AS
  4.     SELECT
  5.         t1.last_name,
  6.         t2.department_name,
  7.         t3.job_title
  8.     FROM employees t1, departments t2, jobs t3
  9.     WHERE t1.department_id = t2.department_id AND t1.job_id = t3.job_id;
  10. SELECT * FROM myv1 a where a.last_name like 'a%';
  12. -- 查询各部门的平均⼯资级
  13. CREATE VIEW myv2
  14. AS
  15.     SELECT
  16.         t1.department_id 部门id,
  17.         t1.ag 平均⼯资,
  18.         t2.grade_level ⼯资级别
  19.     FROM (SELECT
  20.                 department_id,
  21.                 AVG(salary) ag
  22.             FROM employees
  23.             GROUP BY department_id)
  24.         t1, job_grades t2
  25.     WHERE t1.ag BETWEEN t2.lowest_sal AND t2.highest_sal;
  26. SELECT * FROM myv2;
  28. -- 修改视图
  29. -- ⽅式1：如果该视图存在，就修改，如果不存在，就创建新的视图
  30. CREATE OR REPLACE VIEW myv3
  31. AS
  32.     SELECT
  33.         job_id,
  34.         AVG(salary) javg
  35.     FROM employees
  36.     GROUP BY job_id;
  37. -- ⽅式2：alter 视图
  38. ALTER VIEW myv3
  39. AS
  40.     SELECT *
  41.     FROM employees;
  43. -- 删除视图 `drop view 视图名1 [,视图名2] [,视图名n];`
  44. drop view myv1,myv2,myv3;
  46. -- 查询视图结构
  47. -- 方式1：desc
  48. desc 视图名称;
  49. -- 方式2：show create view（显⽰了视图的创建语句）
  50. show create view 视图名称;

变量

  • 变量分类
    • 系统变量：系统变量由系统定义的，不是⽤户定义的，属于mysql服务器层⾯的
      • 全局变量：每次启动都会为所有的系统变量设置初始值；针对所有会话（连接）有效，可以跨连接，但不能跨重启，重启之后，mysql服务器会再次为所有系统变量赋初始值
      • 会话变量：针对当前会话（连接）有效，不能跨连接；会话变量是在连接创建时由mysql⾃动给当前会话设置的变量
    • ⾃定义变量：变量由⽤户⾃定义的，⽽不是系统提供的
      • 使用步骤：声明、赋值、使⽤（查看、⽐较、运算）
      • 分类：⽤户变量、局部变量
      • ⽤户变量：针对当前会话（连接）有效，作⽤域同会话变量；⽤户变量可以在任何地⽅使⽤也就是既可以在begin end⾥⾯使⽤，也可以在他外⾯使⽤
      • 局部变量：declare⽤于定义局部变量变量，在存储过程和函数中通过declare定义变量在begin…end中，且在语句之前。并且可以通过重复定义多个变量；作⽤范围同编程⾥⾯类似，在这⾥⼀般是在对应的begin和end之间。在end之后这个变量就没有作⽤了，不能使⽤了。
  • 赋值
    • 上⾯使⽤中介绍的，全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认为session级别
    • 全局变量的使⽤中⽤到了@@关键字，后⾯会介绍⾃定义变量，⾃定义变量中使⽤了⼀个@符号，这点需要和全局变量区分⼀下
  • delimiter关键字
    • 结束符，当mysql看到这个结束符的时候，表⽰可以执⾏前⾯的语句了，mysql默认以分号为结束符
    • 当我们创建存储过程或者⾃定义函数的时候，写了很⼤⼀⽚sql，⾥⾯包含了很多分号，整个创建语句是⼀个整体，需要⼀起执⾏，此时我们就不可⽤⽤分号作为结束符了
    • 那么我们可以通过delimiter关键字来⾃定义结束符
    • 默认结束符是分号
    • ⽤法：delimiter 分隔符
  1. -- 查看全局变量
  2. show global variables;
  3. -- 查看会话变量
  4. show session variables;
  5. show variables;
  7. -- 查看满⾜条件的系统变量量(like模糊匹配)
  8. show [global|session] like '%变量名%';
  10. -- 查看指定的系统变量(select和@@关键字，global和session后⾯有个.符号)
  11. select @@[global.|session.]系统变量名称;
  13. -- 赋值⽅式1
  14. set [global|session] 系统变量名=值;
  16. -- 赋值⽅式2
  17. set @@[global.|session.]系统变量名=值;
  19. -- 用户变量
  20. -- 声明并初始化(要求声明时必须初始化) 
  21. -- ⽅式1
  22. set @变量名=值;
  23. -- ⽅式2
  24. set @变量名:=值;
  25. -- ⽅式3
  26. select @变量名:=值;
  28. -- 赋值（更新变量的值）
  29. -- ⽅式1：这块和变量的声明⼀样
  30. set @变量名=值;
  31. set @变量名:=值;
  32. select @变量名:=值;
  33. -- ⽅式2
  34. select 字段 into @变量名 from 表;
  36. -- 使⽤
  37. select @变量名;
  39. -- 综合示例
  40. /*set⽅式创建变量并初始化*/
  41. set @username='路⼈甲java';
  42. /*select into⽅式创建变量*/
  43. select 'javacode2018' into @gzh;
  44. select count(*) into @empcount from employees;
  45. /*select :=⽅式创建变量*/
  46. select @first_name:='路⼈甲Java',@email:='javacode2018@163.com';
  47. /*使⽤变量*/
  48. insert into employees (first_name,email) values (@first_name,@email);
  50. -- 局部变量
  51. -- 声明
  52. declare 变量名 变量类型;
  53. declare 变量名 变量类型 [default 默认值];
  55. -- 赋值（注意：局部变量前⾯没有@符号）
  56. /*⽅式1*/
  57. set 局部变量名=值;
  58. set 局部变量名:=值;
  59. select 局部变量名:=值;
  60. /*⽅式2*/
  61. select 字段 into 局部变量名 from 表;
  63. -- 使⽤（查看变量的值）
  64. select 局部变量名;
  66. -- 示例
  67. /*创建表test1*/
  68. drop table IF EXISTS test1;
  69. create table test1(a int PRIMARY KEY,b int);
  70. /*声明脚本的结束符为$$*/
  71. DELIMITER $$
  72. DROP PROCEDURE IF EXISTS proc1;
  73. CREATE PROCEDURE proc1()
  74. BEGIN
  75.  /*声明了⼀个局部变量*/
  76.  DECLARE v_a int;
  77.  select ifnull(max(a),0)+1 into v_a from test1;
  78.  select @v_b:=v_a*2;
  79.  insert into test1(a,b) select v_a,@v_b;
  80. end $$
  81. /*声明脚本的结束符为;*/
  82. DELIMITER ;
  83. /*调⽤存储过程*/
  84. call proc1();
  85. /*查看结果*/
  86. select * from test1;
  • 总结
    • 系统变量可以设置系统的⼀些配置信息，数据库重启之后会被还原
    • 会话变量可以设置当前会话的⼀些配置信息，对当前会话起效
    • declare创建的局部变量常⽤于存储过程和函数的创建中
    • 作⽤域：全局变量对整个系统有效、会话变量作⽤于当前会话、⽤户变量作⽤于当前会话、局部变量作⽤于begin end之间
    • 注意全局变量中⽤到了@@，⽤户变量变量⽤到了@，⽽局部变量没有这个符号
    • delimiter关键字⽤来声明脚本的结束符

存储过程&⾃定义函数详解

  • 需求背景介绍
    • 线上程序有时候出现问题导致数据错误的时候，如果⽐较紧急，我们可以写⼀个存储来快速修复这块的数据，然后再去修复程序
  • 存储过程
    • 概念：⼀组预编译好的sql语句集合，理解成批处理语句
    • 好处
      • 提⾼代码的重⽤性
      • 简化操作
      • 减少编译次数并且减少和数据库服务器连接的次数，提⾼了效率
  • 创建存储过程
    • 参数模式有3种：
    • in：该参数可以作为输⼊，也就是该参数需要调⽤⽅传⼊值
    • out：该参数可以作为输出，也就是说该参数可以作为返回值
    • inout：该参数既可以作为输⼊也可以作为输出，也就是说该参数需要在调⽤的时候传⼊值，又可以作为返回值
    • 参数模式默认为IN
    • ⼀个存储过程可以有多个输⼊、多个输出、多个输⼊输出参数
  1. -- 创建存储过程
  2. create procedure 存储过程名([参数模式] 参数名 参数类型)
  3. begin
  4.     存储过程体
  5. end
  7. -- 调⽤存储过程（关键字是call）
  8. call 存储过程名称(参数列表);
  10. -- 删除存储过程（存储过程只能⼀个个删除，不能批量删除）
  11. drop procedure [if exists] 存储过程名称;
  13. -- 修改存储过程
  14. -- 存储过程不能修改，若涉及到修改的，可以先删除，然后重建
  16. -- 查看存储过程
  17. show create procedure 存储过程名称;
  19. -- 创建存储过程：空参列表
  20. /*设置结束符为$*/
  21. DELIMITER $
  22. /*如果存储过程存在则删除*/
  23. DROP PROCEDURE IF EXISTS proc1;
  24. /*创建存储过程proc1*/
  25. CREATE PROCEDURE proc1()
  26.  BEGIN
  27.  INSERT INTO t_user VALUES (1,30,'路⼈甲Java');
  28.  INSERT INTO t_user VALUES (2,50,'刘德华');
  29.  END $
  30. /*将结束符置为;*/
  31. DELIMITER ;
  32. /*调⽤存储过程*/
  33. CALL proc1();
  35. -- 创建存储过程：带in参数的存储过程
  36. /*设置结束符为$*/
  37. DELIMITER $
  38. /*如果存储过程存在则删除*/
  39. DROP PROCEDURE IF EXISTS proc2;
  40. /*创建存储过程proc2*/
  41. CREATE PROCEDURE proc2(id int,age int,in name varchar(16))
  42.  BEGIN
  43.  INSERT INTO t_user VALUES (id,age,name);
  44.  END $
  45. /*将结束符置为;*/
  46. DELIMITER ;
  47. /*创建了3个⾃定义变量*/
  48. SELECT @id:=3,@age:=56,@name:='张学友';
  49. /*调⽤存储过程*/
  50. CALL proc2(@id,@age,@name);
  52. -- 创建存储过程：带out参数的存储过程
  53. delete a from t_user a where a.id = 4;
  54. /*如果存储过程存在则删除*/
  55. DROP PROCEDURE IF EXISTS proc3;
  56. /*设置结束符为$*/
  57. DELIMITER $
  58. /*创建存储过程proc3，前2个参数，没有指定参数模式，默认为in*/
  59. CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out
  60. user_count int,out max_id INT)
  61.  BEGIN
  62.  INSERT INTO t_user VALUES (id,age,name);
  63.  /*查询出t_user表的记录，放⼊user_count中,max_id⽤来存储t_user中最⼩的id*/
  64.  SELECT COUNT(*),max(id) into user_count,max_id from t_user;
  65.  END $
  66. /*将结束符置为;*/
  67. DELIMITER ;
  68. /*创建了3个⾃定义变量*/
  69. SELECT @id:=4,@age:=55,@name:='郭富城';
  70. /*调⽤存储过程*/
  71. CALL proc3(@id,@age,@name,@user_count,@max_id);
  73. -- 创建存储过程：带inout参数的存储过程
  74. /*如果存储过程存在则删除*/
  75. DROP PROCEDURE IF EXISTS proc4;
  76. /*设置结束符为$*/
  77. DELIMITER $
  78. /*创建存储过程proc4*/
  79. CREATE PROCEDURE proc4(INOUT a int,INOUT b int)
  80.  BEGIN
  81.  SET a = a*2;
  82.  select b*2 into b;
  83.  END $
  84. /*将结束符置为;*/
  85. DELIMITER ;
  86. /*创建了2个⾃定义变量*/
  87. set @a=10,@b:=20;
  88. /*调⽤存储过程*/
  89. CALL proc4(@a,@b);
  91. -- 查看存储过程
  92. show create procedure proc4;
  • 函数
    • 概念：⼀组预编译好的sql语句集合，理解成批处理语句，但是必须有返回值
  1. -- 创建函数（参数是可选的，返回值是必须的）
  2. create function 函数名(参数名称 参数类型)
  3. returns 返回值类型
  4. begin
  5.     函数体
  6. end
  8. -- 调⽤函数
  9. select 函数名(实参列表);
  11. -- 删除函数
  12. drop function [if exists] 函数名;
  14. -- 查看函数详细
  15. show create function 函数名;
  17. -- 创建函数：⽆参函数
  18. /*删除fun1*/
  19. DROP FUNCTION IF EXISTS fun1;
  20. /*设置结束符为$*/
  21. DELIMITER $
  22. /*创建函数*/
  23. CREATE FUNCTION fun1()
  24.  returns INT
  25.  BEGIN
  26.  DECLARE max_id int DEFAULT 0;
  27.  SELECT max(id) INTO max_id FROM t_user;
  28.  return max_id;
  29.  END $
  30. /*设置结束符为;*/
  31. DELIMITER ;
  32. /*调⽤效果*/
  33. SELECT fun1();
  35. -- 创建函数：有参函数
  36. /*删除函数*/
  37. DROP FUNCTION IF EXISTS get_user_id;
  38. /*设置结束符为$*/
  39. DELIMITER $
  40. /*创建函数*/
  41. CREATE FUNCTION get_user_id(v_name VARCHAR(16))
  42.  returns INT
  43.  BEGIN
  44.  DECLARE r_id int;
  45.  SELECT id INTO r_id FROM t_user WHERE name = v_name;
  46.  return r_id;
  47.  END $
  48. /*设置结束符为;*/
  49. DELIMITER ;
  50. /*调⽤效果*/
  51. SELECT get_user_id(name) from t_user;
  • 存储过程和函数的区别
    • 存储过程的关键字为procedure，返回值可以有多个，调⽤时⽤call，⼀般⽤于执⾏⽐较复杂的的过程体、更新、创建等语句
    • 函数的关键字为function，返回值必须有⼀个，调⽤⽤select，⼀般⽤于查询单个值并返回

流程控制语句介绍

  • if函数
  • case语句
  • if结构
  • while循环：类似于java中的while循环
  • repeat循环：类似于java中的do while循环
  • loop循环：类似于java中的while(true)死循环，需要在内部进⾏控制
  • 循环体控制语句
    • 结束本次循环：iterate 循环标签，类似于java中的continue
    • 退出循环：leave 循环标签，类似于java中的break
  1. -- if函数语法：if函数有3个参数，当参数1为true的时候，返回值1，否则返回值2
  2. if(条件表达式,值1,值2);
  3. -- 实例如下：
  4. SELECT id 编号,if(sex=1,'男','⼥') 性别,name 姓名 FROM t_user;
  6. -- CASE结构语法
  7. case 表达式
  8. when 值1 then 结果1或者语句1（如果是语句需要加分号）
  9. when 值2 then 结果2或者语句2
  10. ...
  11. else 结果n或者语句n
  12. end [case] （如果是放在begin end之间需要加case，如果在select后则不需要）
  14. -- 示例1：select中使⽤，查询t_user表数据，返回：编号、性别（男、⼥）、姓名
  15. SELECT id 编号,(CASE sex WHEN 1 then '男' WHEN 2 then '⼥' END) 性
  16. 别,name 姓名 FROM t_user;
  18. -- 示例2：begin end中使⽤
  19. /*删除存储过程proc1*/
  20. DROP PROCEDURE IF EXISTS proc1;
  21. /*删除id=6的记录*/
  22. DELETE FROM t_user WHERE id=6;
  23. /*声明结束符为$*/
  24. DELIMITER $
  25. /*创建存储过程proc1*/
  26. CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16))
  27.  BEGIN
  28.  /*声明变量v_sex⽤于存放性别*/
  29.  DECLARE v_sex TINYINT UNSIGNED;
  30.  /*根据sex_str的值来设置性别*/
  31.  CASE sex_str
  32.  when '男' THEN
  33.  SET v_sex = 1;
  34.  WHEN '⼥' THEN
  35.  SET v_sex = 2;
  36.  END CASE ;
  37.  /*插⼊数据*/
  38.  INSERT INTO t_user VALUES (id,v_sex,name);
  39.  END $
  40. /*结束符置为;*/
  41. DELIMITER;
  42. CALL proc1(6,'男','郭富城');
  44. -- 示例3：函数中使⽤
  45. /*删除存储过程proc1*/
  46. DROP FUNCTION IF EXISTS fun1;
  47. /*声明结束符为$*/
  48. DELIMITER $
  49. /*创建存储过程proc1*/
  50. CREATE FUNCTION fun1(sex TINYINT UNSIGNED)
  51.  RETURNS varchar(8)
  52.  BEGIN
  53.  /*声明变量v_sex⽤于存放性别*/
  54.  DECLARE v_sex VARCHAR(8);
  55.  CASE sex
  56.  WHEN 1 THEN
  57.  SET v_sex:='男';
  58.  ELSE
  59.  SET v_sex:='⼥';
  60.  END CASE;
  61.  RETURN v_sex;
  62.  END $
  63. /*结束符置为;*/
  64. DELIMITER ;
  65. select sex, fun1(sex) 性别,name FROM t_user;
  67. -- CASE结构语法第2种⽤法
  68. case
  69. when 条件1 then 结果1或者语句1（如果是语句需要加分号）
  70. when 条件2 then 结果2或者语句2
  71. ...
  72. else 结果n或者语句n
  73. end [case] （如果是放在begin end之间需要加case，如果是在select后⾯case可以省
  74. 略）
  76. -- if结构语法（只能使⽤在begin    end之间）
  77. if 条件语句1 then 语句1;
  78. elseif 条件语句2 then 语句2;
  79. ...
  80. else 语句n;
  81. end if;
  83. -- 示例：写⼀个存储过程，实现⽤户数据的插⼊和新增，如果id存在，则修改，不存在则新增，并返回结果
  84. /*删除id=7的记录*/
  85. DELETE FROM t_user WHERE id=7;
  86. /*删除存储过程*/
  87. DROP PROCEDURE IF EXISTS proc2;
  88. /*声明结束符为$*/
  89. DELIMITER $
  90. /*创建存储过程*/
  91. CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name
  92. varchar(16),OUT result TINYINT)
  93.  BEGIN
  94.  DECLARE v_count TINYINT DEFAULT 0;/*⽤来保存user记录的数量*/
  95.  /*根据v_id查询数据放⼊v_count中*/
  96.  select count(id) into v_count from t_user where id = v_id;
  97.  /*v_count>0表⽰数据存在，则修改，否则新增*/
  98.  if v_count>0 THEN
  99.    BEGIN
  100.      DECLARE lsex TINYINT;
  101.      select if(lsex='男',1,2) into lsex;
  102.      update t_user set sex = lsex,name = v_name where id = v_id;
  103.      /*获取update影响⾏数*/
  104.      select ROW_COUNT() INTO result;
  105.    END;
  106.  else
  107.    BEGIN
  108.      DECLARE lsex TINYINT;
  109.      select if(lsex='男',1,2) into lsex;
  110.      insert into t_user VALUES (v_id,lsex,v_name);
  111.      select 0 into result;
  112.    END;
  113.  END IF;
  114.  END $
  115. /*结束符置为;*/
  116. DELIMITER ;
  118. -- while循环语法
  119. -- 标签：是给while取个名字，标签和iterate、leave结合⽤于在循环内部对循环
  120. 进⾏控制，如：跳出循环、结束本次循环
  121. -- 这个循环先判断条件，条件成⽴之后，才会执⾏循环体，每次执⾏都会先进⾏判断
  122. [标签:]while 循环条件 do
  123. 循环体
  124. end while [标签];
  126. -- 示例：添加iterate控制语句
  127. /*删除test1表记录*/
  128. DELETE FROM test1;
  129. /*删除存储过程*/
  130. DROP PROCEDURE IF EXISTS proc5;
  131. /*声明结束符为$*/
  132. DELIMITER $
  133. /*创建存储过程*/
  134. CREATE PROCEDURE proc5(v_count int)
  135.  BEGIN
  136.  DECLARE i int DEFAULT 0;
  137.  a:WHILE i<=v_count DO
  138.  SET i=i+1;
  139.  /*如果i不为偶数，跳过本次循环*/
  140.  IF i%2!=0 THEN
  141.    ITERATE a;
  142.  END IF;
  143.  /*插⼊数据*/
  144.  INSERT into test1 values (i);
  145.  END WHILE;
  146.  END $
  147. /*结束符置为;*/
  148. DELIMITER ;
  150. -- repeat循环语法
  151. -- repeat不管如何循环都会先执⾏⼀次，然后再判断结束循环的条件，不满⾜结束条件，循环体继续执⾏。这块和while不同，while是先判断条件是否成⽴再执⾏循环体
  152. [标签:]repeat
  153. 循环体;
  154. until 结束循环的条件 end repeat [标签];
  156. -- 示例：⽆循环控制语句
  157. /*删除存储过程*/
  158. DROP PROCEDURE IF EXISTS proc6;
  159. /*声明结束符为$*/
  160. DELIMITER $
  161. /*创建存储过程*/
  162. CREATE PROCEDURE proc6(v_count int)
  163.  BEGIN
  164.  DECLARE i int DEFAULT 1;
  165.  a:REPEAT
  166.  INSERT into test1 values (i);
  167.  SET i=i+1;
  168.  UNTIL i>v_count END REPEAT;
  169.  END $
  170. /*结束符置为;*/
  171. DELIMITER ;
  173. -- loop循环语法
  174. -- 相当于⼀个死循环，需要在循环体中使⽤iterate或者leave来控制循环的执⾏
  175. [标签:]loop
  176. 循环体;
  177. end loop [标签];
  179. -- 示例：⽆循环控制语句
  180. /*删除存储过程*/
  181. DROP PROCEDURE IF EXISTS proc7;
  182. /*声明结束符为$*/
  183. DELIMITER $
  184. /*创建存储过程*/
  185. CREATE PROCEDURE proc7(v_count int)
  186.  BEGIN
  187.  DECLARE i int DEFAULT 0;
  188.  a:LOOP
  189.  SET i=i+1;
  190.  /*当i>v_count的时候退出循环*/
  191.  IF i>v_count THEN
  192.  LEAVE a;
  193.  END IF;
  194.  INSERT into test1 values (i);
  195.  END LOOP a;
  196.  END $
  197. /*结束符置为;*/
  198. DELIMITER ;
  • 总结
    • if函数常⽤在select中
    • case语句有2种写法，主要⽤在select、begin、end中，select中end后⾯可以省略case，begin end中使⽤不能省略case
    • if语句⽤在begin end中
    • 3种循环体的使⽤，while类似于java中的while循环，repeat类似于java中的do while循环，loop类似于java中的死循环，都⽤于begin end中
    • 循环中体中的控制依靠leave和iterate，leave类似于java中的break可以退出循环，iterate类似于java中的continue可以结束本次循环