MySQL高级

视图

创建视图

CREATE view v_eptu
as 
select id,empname,dpname,dpid from emp e LEFT JOIN dept d on e.epno=d.dpid

视图创建好以后就已一个表格的形式存在

查看视图内容

SELECT * FROM v_eptu

删除视图

drop view v_eptu

查看表格

show tables

存储过程-函数

创建存储函数

create procedure p_p1()
BEGIN
SELECT 'hello';
end

查看函数内容

call 函数名

查看函数的状态信息和有哪些函数

show PROCEDURE STATUS

删除函数

drop PROCEDURE 函数名

语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

DECLARE，set

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

set 对里面的变量进行赋值

CREATE PROCEDURE p_p1()
BEGIN
DECLARE num int;
SET num=10;
SELECT num;
END

也可以通过select ... into 方式进行赋值操作 :

CREATE PROCEDURE pro_test5()

BEGIN

declare countnum int;

select count(*) into countnum from city;

select countnum;

END

if条件判断

if使用时必须要配合end if;

then后面不可以加  ;

CREATE PROCEDURE p_p3()
BEGIN
DECLARE height int default 130;
declare zhi VARCHAR(200);
if height>=180 then
set zhi='高';
elseif height>=165 then
set zhi='适中';
else 
set zhi='未成年';
end if;
SELECT zhi;
END

传递参数

IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认

OUT: 该参数作为输出，也就是该参数可以作为返回值

INOUT: 既可以作为输入参数，也可以作为输出参数

CREATE PROCEDURE p_p4(in height int,out zhi varchar(200))
begin
    if height>=180 then
        set zhi='高挑';
    elseif height>=165 then
        set zhi='适中';
    else
        set zhi='未成年';
    end if;
end

call p_p4(177,@z)
select @z

变量名前加一个 @         叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

变量名前添加两个@  叫做 系统变量

case结构

case使用时要和end case；配合一起使用

create procedure p_p5(in months int)
    begin
        declare ji varchar(200);
        case
            when months>=1 and months<=3 THEN
                set ji='第一季度';
            when months<=6 and months>3 then 
                set ji='第二季度';
            when months<=9 and months>6 then
                set ji='第三季度';
            when months<=12 and months>9 then
                set ji='第四季度';
            when months!=1 and months!=12 then
                set ji='请输入正确的日期';
        end case;
        select concat('您输入的日期为:',months,'季度为:',ji) as content;
    end

call p_p5(0)

while循环

while使用时要和end while；配合一起使用

create procedure p_p6(in i int)
begin
    declare total int default 0;
  declare num int default 1;
        while num<=i do
            set total = total + num;
            set num = num + 1;
        end while;
  select total; 
end

call p_p6(100)

repeat结构

repeat满足条件就退出循环

有条件的循环控制语句, 当满足条件的时候退出循环 。

while 是满足条件才执行然后就要退出执行

repeat使用时要和end repeat；配合一起使用

create procedure p_p7(in i int,out d int)
    begin
        declare n int default 0;
        set d=0;
         repeat
            set d=d+n;
            set n=n+1;
            until n>i
         end repeat;
    end

call p_p7(100,@z)
select @z

loop语句，leave语句

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现

用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用

create procedure p_p8(in n int)
    begin
     declare i int default 0;
     rro: loop
        if n<=0 then
            leave rro;
        end if;
        set i=i+n;
        set n=n-1;
     end loop rro;
     select i;
    end

游标/光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用循环将光标存储的值展现出来。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下

声明光标：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 光标：

OPEN cursor_name ;

FETCH 光标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光标：

CLOSE cursor_name ;

-- 使用循环将游标中的数据循环出来
create procedure p_p10()
begin
    declare id int;
    declare empname varchar(200);
    declare epno int;
    declare e_hast int default 1;
--     定义游标
    declare e_you cursor for select * from emp;
--     退出之后让e_hast=0
    declare exit handler for not found set e_hast=0;
--     打开游标
        open e_you;
--         开启循环
            e:loop
                if e_hast=0 then
                    leave e;
                end if;
        fetch e_you into id,empname,epno;
            select concat('id:',id,'姓名:',empname,'部门号:',epno);
        fetch e_you into id,empname,epno;
            select concat('员工编号:','姓名:',empname,'部门标号:',epno);
--             展示时两种形式交替展现
            end loop e;
        close e_you;
end
call p_p10
drop procedure p_p10

存储函数

CREATE FUNCTION function_name([param type ... ])

RETURNS type // type代表返回值的类型；

BEGIN

...

END;

案例 :

-- 函数 根据部门的编号查看部门中的人数
create function h_h1(n int)
    returns int
    begin
        declare h int default 0;
         select count(*) into h from emp where epno=n;
        return h;
    end;

-- 调用
select h_h1(1)

mybatis调用存储过程

接口：

Map getPro(Map map);

接口：

Map getPro(Map map);

xml文件：

<select id="getPro" resultType="map" statementType="CALLABLE" parameterType="map">

{call pro_getdes(#{height,jdbcType=INTEGER,mode=IN},#{describtion,jdbcType=VARCHAR,mode=OUT})}

</select>

测试类：

@RunWith(SpringRunner.class)

@SpringBootTest(classes = OrderDemo.class)

public class SpringTest {

@Resource

private TorderMapper torderMapper;

@Test

public void aaa(){

System.out.println("ddd");

Map m=new HashMap();

m.put("height",150);

m.put("describtion",null);

torderMapper.getPro(m);

System.out.println(m);

}

}