mysql学习笔记06

​数据准备

CREATE TABLE dept  (
  deptno int NOT NULL,
  dname varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  loc varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

CREATE TABLE emp  (
  empno int NOT NULL,
  ename varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  job varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  mgr int NULL DEFAULT NULL,
  hiredate date NULL DEFAULT NULL,
  sal decimal(7, 2) NULL DEFAULT NULL,
  COMM decimal(7, 2) NULL DEFAULT NULL,
  deptno int NULL DEFAULT NULL
) ;
drop table emp;
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

CREATE TABLE salgrade  (
  grade int NOT NULL,
  losal int NULL DEFAULT NULL,
  hisal int NULL DEFAULT NULL
) ;

INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

 

存储过程

存储过程就是数据库sql语言层面的代码封装与重用

特性：有输入输出参数，可以声明变量，if_else ，while 等控制语句

模块化，封装，代码复用

速度快，只需要首次记过编译和优化，后记调用可以直接执行

格式：

/*
delimiter 自定义结束符号
​
create procedure 存储名 （ [in,out,inout] 参数名 数据类型）
begin  
        sql语句
end  自定义结束符号
​
delimiter；
*/
create database test04_procedure;
use test04_procedure;
-- 创建存储过程
delimiter $$
create procedure procedure01 ()
begin 
        select empnon,ename from emp;
END $$delimiter $$
create procedure procedure01 ()
begin 
        select empnon,ename from emp;
END $$
DELIMITER;
DELIMITER;
​
-- 调用存储过程
call procedure01;

变量定义

局部变量在begin和end之间存在

​
​
​
​

-- 变量定义
-- 语法1： declare 变量名 数据类型  ；
delimiter $$
create procedure proc01()
begin 
declare name varchar(20) default '李白';  -- 声明变量
set  name = '张山';  -- 变量赋值
select name ;
end $$
delimiter;
​
call proc01;
-- 语法2：select col_name [……]  into 变量名
--       from table_name where condintion
/* col_name 参数表示查询的字段名称
var_name 表示变量的名称
table_name 表示表的名字
condition 表示查询条件
当将查询的数据给变量时，该查询语句的返回结果只能是单行单列
*/
​
delimiter $$
create procedure proc02()
begin 
      declare name1 varchar(20);
            select ename into name1 from emp where empno = 1001;  -- 变量赋值
            select name1;
end $$
delimiter ;
​
call proc02;
​

用户变量：在当前对话连接有作用

系统变量：系统变量mysql已经提供好了，又分为全局变量和会话变量，全局变量回在mysql初始化为默认值，可以更改全局变量和会话变量的区别在于，对全局变量修改回影响整个MySQL服务器，会话变量值影响当前会话也就是本次连接，有些系统变量可以修改，有些只读

​

-- 格式：
--              @var_name 不需要提前声明，使用即声明
​
​
-- 用户变量使用
delimiter $$
create procedure pro02()
begin 
  set @name2 = '北京';
    select @name2;
end $$
delimiter;
​
select @name2;  
call pro02; 
​
​
-- 系统变量：
​
​
-- 系统变量-全局变量
-- 语法：@@var_name
​
--  查看全局变量
-- show global variables;
-- 查看某一个全局变量
-- select @@global.auto_increment_increment;
​
​
-- 修改全局变量的值
-- set global sort_buffer_size = 40000;
-- set @@global.sort_buffer_size = 4000;

传入参数--in

-- 封装有参数的存储过程，传入员工编号，查找员工信息
--  create procedure pro03（in  参数名 数据类型）
delimiter $$
create procedure pro03(in para_no)
begin 
        select ename from emp where emp.empno = para_no;
end $$
delimiter;
call pro03(1001); -- 甘宁
​
​
 -- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且大于指定的员工信息
 delimiter $$
 create procedure pro04(in dt varchar(50),in salary decimal(7, 2))
 begin
        select * from emp,dept where emp.deptno = dept.deptno and  emp.deptno = dt and emp.sal > salary;
 end $$
 delimiter;
 
 
 call pro04('学工部',20000);
  
 call pro04('销售部',10000);

传入参数--out

in 用来把数据传输到函数，out 传出结果

-- 封装有参数的存储过程，传入员工编号，查找员工信息
--  create procedure pro05（out  参数名 数据类型）
delimiter $$
create procedure pro05(in in_empno int,out out_name varchar(50))
begin 
        select ename into out_name from emp where in_empno = empno;
end $$
delimiter;
​
call pro05(1001,@o_ename);
select @o_ename;

传入参数--inout

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

-- 传入一个数字，返回这个数字的十倍
delimiter $$
create procedure pro06(inout num_10 int)
begin 
        set num_10 =  num_10 *10;
end $$
delimiter;
set @num_10 = 3;
call pro06(@num_10);
select @num_10;
-- 传入员工名，拼接部门号，传入工资，求出年薪
​
delimiter $$
create procedure pro07(inout name_deptno varchar(50),inout sal_12 int)
begin 
     SELECT CONCAT(deptno,'-',ename) from emp  where ename = name_deptno ;
     set sal_12 = sal_12 *12;
end $$
delimiter;
​
​
set @name1 = '关羽';
set @salary = 1001;
call pro07(@name1,@salary);
select @name1,@salary;

存储过程-if语句

use test04_procedure;
-- 输入成绩，判断成绩情况
delimiter $$
create procedure pro08(in score int)
begin 
        if score < 60 then select '不及格';
        elseif  score>=60 and score< 80
        then  select '及格' as '级别';
        elseif  score>=80 and score< 90
        then  select '良好' as '级别';
        elseif score>=90 and score<=100
        then select '优秀' as '级别';
        
        else 
                SELECT  '成绩错误' as '级别';
        end if;
end $$
delimiter;
 
call pro08(88);
​
-- 输入员工的名字，判断薪资情况
delimiter $$
create procedure pro09(in empname varchar(50) )
begin 
            declare  judeg_sal decimal(7,2); 
            declare result varchar(50);
            SELECT sal into judeg_sal from emp where ename = empname;
            if judeg_sal < 10000 then set result = '实习工资';
            elseif judeg_sal <20000 then   set result = '起步工资';
            else  set result = '元老工资';
            end if;
            select result;
end $$
delimiter;
​
call pro09('甘宁');

存储过程-case-when语句

/*
-- 流程控制语句：case
-- 格式1
case 变量名
when  变量值  then  ……
end case;
*/
​
-- 支付方式
-- 1.微信支付
-- 2.支付宝
-- 3.银行卡
delimiter $$
create procedure pro10(in type int)
begin 
        declare result varchar(20);
​
        case type
        when 1 then  set result =  '微信支付';
        when 2 then set result =  '支付宝';
        when 3 then set result = '银行卡';
        else set result = '无支付方式';
        end case;
        select result as type;
end $$
delimiter;
call pro10(2); -- 支付宝
​
​
​
/*
-- 格式2
case 
when  变量名 = 变量值 then 
end case;
​
*/
-- 支付方式
-- 1.微信支付
-- 2.支付宝
-- 3.银行卡
delimiter $$
create procedure pro11(in type int)
begin 
        declare result varchar(20);
​
        case 
        when type=1 then  set result =  '微信支付';
        when type=2 then set result =  '支付宝';
        when type=3 then set result = '银行卡';
        else set result = '无支付方式';
        end case;
        select result as type;
end $$
delimiter;
call pro11(1);-- 微信支付

存储过程-循环语句

概述：循环是一段程序在程序中出现一次或者多次的代码，循环会允许执行特定次数，达到条件结束循环

分类：while

​ repeat

​ loop

循环控制：leave 类似于break ，结束所在全部循环

​ iterate类似与continue，结束当前一次循环

while循环

/* 
while 循环条件 do
    循环体；
end while【标签】；
-- 向表中加入10条数据 
*/
-- 创建表
create table user (
 uid  int primary key auto_increment,
 username varchar(50),
 password varchar(50)
);
-- 存储过程
delimiter $$
create procedure pro12_while(in n int)
begin 
    declare x int;
    set x=1;
    lable:
      while  x<= n do
            insert into user values(null,concat('user',x),"123456");
            set x = x+1;
    end while lable;
end $$
delimiter;
-- 调用
call pro12_while(10);
select *from user;
truncate user;
​
-- 循环10次插入数据到5条跳出循环  -- leave 跳出
create table user (
 uid  int primary key auto_increment,
 username varchar(50),
 password varchar(50)
);
delimiter $$
create procedure pro12_while(in n int)
begin 
    declare x int;
    set x=1;
    lable:
      while  x<= n do
            insert into user values(null,concat('user',x),"123456");
            if x = 5 then leave lable;
            end if;
            set x = x+1;
    end while lable;
end $$
delimiter;
​
call pro12_while(10);
select *from user;
truncate  user;
​
-- 跳过5的单步循环  -- iterate 跳出
delimiter $$
create procedure pro12_while(in n int)
begin 
    declare x int;
    set x=0;
    lable:
      while  x<= n do
      set x=x+1;
            if x = 5 then iterate lable;
            end if;         
            insert into user values(null,concat('user',x),"123456");
    end while lable;
end $$
delimiter;
​
call pro12_while(9);
select *from user;
truncate  user;

repeat循环

/*
[标签：] repeat 
​
            循环体
​
until  条件表达式
​
end  repeat  [标签]；
*/
-- 插入10条数据
-- 创建表
create table user1 (
 uid  int,
 username varchar(50),
 password varchar(50)
);
-- 存储过程
delimiter $$
create procedure pro13_repeat(in n int )
begin 
    declare x int ;
    set x = 1;
    repeat 
    insert into user1 values(x,concat('user-',x),'123456');
    set x = x+1;
    until x>10
    end repeat;
    select '添加成功';
end $$
delimiter;
​
-- 调用
call pro13_repeat(10);
​
select * from user1;

loop循环

/*
[标签：] loop
            循环体
        if 条件表达式 then 
        leave [标签]
        end if ;
        end loop;
*/
​
create  table user2(
​
    UID INT,
    NAME VARCHAR(50),
    PASSWORD VARCHAR(50));
    
DELIMITER $$
CREATE PROCEDURE pro14_loop(in n int)
begin 
            DECLARE x int ;
            set x = 1;
         la:
            loop 
            insert into user2 values(x,concat('user',x),'123456');
            set x = x+1;
            if x > n then leave la;
            end if;
            end loop;
end $$
delimiter;
​
​
-- 调用
call pro14_loop(10);
select * from user2;
truncate user2;