MySQL学习笔记（四）

MySQL

发布日期: 2023-05-03

MySQL的视图

介绍

视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

作用

简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。

创建视图

创建视图的语法：

create [or replace] [algorithm = {undefined | merge | temptable}]

view view_name [(column_list)]

as select_statement

[with [cascaded | local] check option]

参数说明：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement
：表示一个完整的查询语句，将查询记录导入视图中。
（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。

数据准备

创建数据库mydb6_view,然后在该数据库下执行sql脚本view_data.sql 导入数据

create database mydb6_view;

操作

create or replace view view1_emp
as 
select ename,job from emp; 

-- 查看表和视图 
show full tables;

创建视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

格式

alter view 视图名 as select语句

操作

alter view view1_emp
as 
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where a.deptno = b.deptno;

更新视图

某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：

聚合函数（SUM(), MIN(), MAX(), COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL
位于选择列表中的子查询
JOIN
FROM子句中的不可更新视图
WHERE子句中的子查询，引用FROM子句中的表。
仅引用文字值（在该情况下，没有要更新的基本表）

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

操作

--  ---------更新视图-------
create or replace view view1_emp
as 
select ename,job from emp;

update view1_emp set ename = '周瑜' where ename = '鲁肃';  -- 可以修改
insert into view1_emp values('孙权','文员');  -- 不可以插入


-- ----------视图包含聚合函数不可更新--------------
create or replace view view2_emp
as 
select count(*) cnt from emp;

insert into view2_emp values(100);
update view2_emp set cnt = 100; 


-- ----------视图包含distinct不可更新---------
create or replace view view3_emp
as 
select distinct job from emp;

insert into view3_emp values('财务');


-- ----------视图包含goup by 、having不可更新------------------
create or replace view view4_emp
as 
select deptno ,count(*) cnt from emp group by deptno having  cnt > 2;

insert into view4_emp values(30,100);


-- ----------------视图包含union或者union all不可更新----------------
create or replace view view5_emp
as 
select empno,ename from emp where empno <= 1005
union 
select empno,ename from emp where empno > 1005;

insert into view5_emp values(1015,'韦小宝');


-- -------------------视图包含子查询不可更新--------------------
create or replace view view6_emp
as 
select empno,ename,sal from emp where sal = (select max(sal) from emp);

insert into view6_emp values(1015,'韦小宝',30000);


-- ----------------------视图包含join不可更新-----------------
create or replace view view7_emp
as 
select dname,ename,sal from emp a join  dept b  on a.deptno = b.deptno;

insert into view7_emp(dname,ename,sal) values('行政部','韦小宝',30000);


-- --------------------视图包含常量文字值不可更新-------------------
create or replace view view8_emp
as 
select '行政部' dname,'杨过'  ename;

insert into view8_emp values('行政部','韦小宝');

其他操作

重命名视图

-- rename table 视图名 to 新视图名; 
rename table view1_emp to my_view1

删除视图

-- drop view 视图名[,视图名…];
drop view if exists view_student;

删除视图时，只能删除视图的定义，不会删除数据。

示例

-- 1：查询部门平均薪水最高的部门名称
select dname from dept  a ,(select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1) b
where a.deptno = b.deptno;  

-- 2：查询员工比所属领导薪资高的部门名、员工名、员工领导编号
select * from dept x,
(select a.ename aname ,a.sal asal,b.ename bname,b.sal bsal,a.deptno
from emp a, emp b 
where a.mgr = b.empno and a.sal > b.sal) y
where x.deptno = y.deptno;

-- 3：查询工资等级为4级，2000年以后入职的工作地点为北京的员工编号、姓名和工资，并查询出薪资在前三名的员工信息
create view xxx
as       
SELECT e.empno,e.ename,e.sal,e.hiredate
FROM emp e,dept d,salgrade s
WHERE (e.sal BETWEEN  losal AND hisal) AND s.GRADE = 4
AND year(e.hiredate) > '2000'
AND d.loc = '北京';

select * from 
(
select 
 *,
 dense_rank() over(order by sal desc ) rn
from xxx
) t
where t.rn <=3;

MySQL的存储过程

存储过程

MySQL 5.0 版本开始支持存储过程。
简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
存储过就是数据库 SQL 语言层面的代码封装与重用。

特性

有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
函数的普遍特性：模块化，封装，代码复用；
速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

示例

格式

 delimiter 自定义结束符号
 create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
 begin
   sql语句
 end 自定义的结束符合
 delimiter ;

操作-数据准备

-- 1：创建数据库 
create database mydb7_procedure; 

-- 2：在该数据库下导入sql脚本:procedure_data.sql

操作-创建存储过程

delimiter $$
create procedure proc01()
begin
  select empno,ename from emp; 
end  $$
delimiter ;

-- 调用存储过程
call proc01();

MySQL操作-变量定义

局部变量

格式

用户自定义，在begin/end块中有效

语法： 
声明变量 declare var_name type [default var_value]; 

示例：
declare nickname varchar(32);

操作

delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default ‘aaa’;  -- 定义局部变量
    set var_name01 = ‘zhangsan’;  
    select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();

MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下：

select col_name [...] into var_name[,...] 
from table_name wehre condition 

其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
-- 注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列

delimiter $$
create procedure proc03()
begin

  declare my_ename varchar(20) ;
  select ename into my_ename from emp where empno=1001;
  select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();

用户变量

格式

用户自定义，当前会话（连接）有效。类比java的成员变量

语法： 
@var_name
不需要提前声明，使用即声明

操作

delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01  ;  --可以看到结果

系统变量

介绍

系统变量又分为全局变量与会话变量
全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

系统变量-全局变量

由系统提供，在整个数据库有效。

介绍

语法：
@@global.var_name

操作

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;

系统变量-会话变量

由系统提供，当前会话（连接）有效

格式

语法：
@@session.var_name

操作

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

存储过程

存储过程传参-in

in 表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
        select * from emp where empno = param_empno;
end $$

delimiter ;
call dec_param01('1001');

-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
        select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$

delimiter ;
call dec_param0x('学工部',20000);

存储过程传参-out

out 表示从存储过程内部传值给调用者

-- ---------传出参数：out---------------------------------
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
  select ename into out_ename from emp where emp.empno = empno;
end $$

delimiter ;

call proc08(1001, @o_ename);
select @o_ename;

-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
  select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$

delimiter ;

call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

存储过程传参-inout

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
begin
  select  concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;
  set inout_sal = inout_sal * 12;  
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

存储过程传参-in、out、inout

in 输入参数: 意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
out 输出参数: 该值可在存储过程内部被改变，并向外输出
inout 输入输出参数: 既能输入一个值又能传出来一个值)

流程控制

流程控制-判断

格式

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：

-- 语法
if search_condition_1 then statement_list_1
    [elseif search_condition_2 then statement_list_2] ...
    [else statement_list_n]
end if

操作

-- 输入学生的成绩，来判断成绩的级别：
/*
  score < 60 :不及格
  score >= 60  , score <80 :及格
    score >= 80 , score < 90 :良好
    score >= 90 , score <= 100 :优秀
    score > 100 :成绩错误
*/
delimiter  $$
create procedure proc_12_if(in score int)
begin
  if score < 60 
      then
          select '不及格';
    elseif  score < 80
      then
          select '及格' ;
    elseif score >= 80 and score < 90
       then 
           select '良好';
  elseif score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end if;
end $$
delimiter  ;
call proc_12_if(120)

-- 输入员工的名字，判断工资的情况。
delimiter $$
create procedure proc12_if(in in_ename varchar(50))
begin
    declare result varchar(20);
    declare var_sal decimal(7,2);
        select sal into  var_sal from emp where ename = in_ename;
    if var_sal < 10000 
        then set result = '试用薪资';
    elseif var_sal < 30000
        then set result = '转正薪资';
    else 
        set result = '元老薪资';
    end if;
    select result;
end$$
delimiter ;
call proc12_if('庞统');

流程控制-case

CASE是另一个条件判断的语句，类似于编程语言中的switch语法

操作

-- 语法一（类比java的switch）：
case case_value
    when when_value then statement_list
    [when when_value then statement_list] ...
    [else statement_list]
end case
-- 语法二：
case
    when search_condition then statement_list
    [when search_condition then statement_list] ...
    [else statement_list]
end case

-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begin
  case pay_type
        when  1 
          then 
              select '微信支付' ;
        when  2 then select '支付宝支付' ;
        when  3 then select '银行卡支付';
      else select '其他方式支付';
    end case ;
end $$
delimiter ;

call proc14_case(2);
call proc14_case(4);

-- 语法二
delimiter  $$
create procedure proc_15_case(in score int)
begin
  case
  when score < 60 
      then
          select '不及格';
    when  score < 80
      then
          select '及格' ;
    when score >= 80 and score < 90
       then 
           select '良好';
  when score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end case;
end $$
delimiter  ;

call proc_15_case(88);

流程控制-循环

概述

循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环

循环分类

while
repeat
loop

循环控制

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

流程控制-循环-whiile

格式

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

操作

-- 创建测试表
create table user (
    uid int primary_key,
    username varchar ( 50 ),
    password varchar ( 50 )
);

-- -------存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        set i=i+1;
    end while label;
end $$
delimiter ;

call proc16_while(10);

-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        if i=5 then leave label;
        end if;
        set i=i+1;
    end while label;
end $$
delimiter ;

call proc16_while2(10);

-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        set i=i+1;
        if i=5 then iterate label;
        end if;
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
    end while label;
end $$
delimiter ;
call proc16_while3(10);

流程控制-循环-repeat

格式

[标签:]repeat 
 循环体;
until 条件表达式
end repeat [标签];

操作

-- -------存储过程-循环控制-repeat 
use mysql7_procedure;
truncate table user;


delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
     declare i int default 1;
     label:repeat
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         until  i  > insertCount
     end repeat label;
     select '循环结束';
end $$
delimiter ;

call proc18_repeat(100);

流程控制-循环-loop+leave

格式

[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;

操作

-- -------存储过程-循环控制-loop
truncate table user;

delimiter $$
create procedure proc19_loop(in insertCount int) 
begin
     declare i int default 1;
     label:loop
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         if i > 5 
          then 
           leave label;
         end if;
     end loop label;
     select '循环结束';
end $$
delimiter ;

call proc19_loop(10);

游标

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.

格式

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

操作

use mysql7_procedure;
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
 -- 定义局部变量
 declare var_empno varchar(50);
 declare var_ename varchar(50);
 declare var_sal  decimal(7,2);

 -- 声明游标
 declare my_cursor cursor for
  select empno , ename, sal 
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = in_dname;

    -- 打开游标
  open my_cursor;
  -- 通过游标获取每一行数据
  label:loop
        fetch my_cursor into var_empno, var_ename, var_sal;
        select var_empno, var_ename, var_sal;
    end loop label;

    -- 关闭游标
    close my_cursor;
end

 -- 调用存储过程
 call proc20_cursor('销售部');

异常处理

异常处理-HANDLER句柄

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现.

官方文档：https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

格式

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

特别注意：

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

存储过程中的hander

格式

use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
  -- 定义局部变量
    declare var_empno int;
    declare var_ename varchar(50);
    declare var_sal decimal(7,2);

    declare flag int default 1; -- ---------------------

    -- 声明游标
    declare my_cursor cursor for
        select empno,ename,sal
        from dept a, emp b
        where a.deptno = b.deptno and a.dname = in_dname;

    -- 定义句柄，当数据未发现时将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0;   
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 判断标志位
        if flag = 1 then
            select var_empno, var_ename,var_sal;
        else
            leave label;
        end if;
    end loop label;

    -- 关闭游标
    close my_cursor;
end $$;

delimiter ;
call proc21_cursor_handler('销售部');