MySQL 高级

/*  该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！  sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，但是并不作为结束标识。  这个时候就可以使用DELIMITER来指定分隔符了！*/-- 标准语法DELIMITER 分隔符

-- 创建db8数据库CREATE DATABASE db8;
-- 使用db8数据库USE db8;
-- 创建学生表CREATE TABLE student(  id INT PRIMARY KEY AUTO_INCREMENT,  -- 学生id  NAME VARCHAR(20),          -- 学生姓名  age INT,              -- 学生年龄  gender VARCHAR(5),          -- 学生性别  score INT                           -- 学生成绩);-- 添加数据INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);
-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;

-- 修改分隔符为$DELIMITER $
-- 标准语法CREATE PROCEDURE 存储过程名称(参数...)BEGIN  sql语句;END$
-- 修改分隔符为分号DELIMITER ;

-- 修改分隔符为$DELIMITER $
-- 创建存储过程，封装分组查询学生总成绩的sql语句CREATE PROCEDURE stu_group()BEGIN  SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;END$
-- 修改分隔符为分号DELIMITER ;

-- 标准语法CALL 存储过程名称(实际参数);
-- 调用stu_group存储过程CALL stu_group();

-- 查询数据库中所有的存储过程 标准语法SELECT * FROM mysql.proc WHERE db='数据库名称';

-- 标准语法DROP PROCEDURE [IF EXISTS] 存储过程名称;
-- 删除stu_group存储过程DROP PROCEDURE stu_group;

-- 标准语法DECLARE 变量名 数据类型 [DEFAULT 默认值];-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-- 定义一个int类型变量、并赋默认值为10DELIMITER $
CREATE PROCEDURE pro_test1()BEGIN  DECLARE num INT DEFAULT 10;   -- 定义变量  SELECT num;                   -- 查询变量END$
DELIMITER ;
-- 调用pro_test1存储过程CALL pro_test1();

-- 标准语法SET 变量名 = 变量值;
-- 定义字符串类型变量，并赋值DELIMITER $
CREATE PROCEDURE pro_test2()BEGIN  DECLARE NAME VARCHAR(10);   -- 定义变量  SET NAME = '存储过程';       -- 为变量赋值  SELECT NAME;                -- 查询变量END$
DELIMITER ;
-- 调用pro_test2存储过程CALL pro_test2();

-- 标准语法SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 定义两个int变量，用于存储男女同学的总分数DELIMITER $
CREATE PROCEDURE pro_test3()BEGIN  DECLARE men,women INT;  -- 定义变量  SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men  SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women  SELECT men,women;           -- 查询变量END$
DELIMITER ;
-- 调用pro_test3存储过程CALL pro_test3();

-- 标准语法IF 判断条件1 THEN 执行的sql语句1;[ELSEIF 判断条件2 THEN 执行的sql语句2;]...[ELSE 执行的sql语句n;]END IF;

  DELIMITER $    -- 标准语法  CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型)  BEGIN    执行的sql语句;  END$    DELIMITER ;

  /*    输入总成绩变量，代表学生总成绩    输出分数描述变量，代表学生总成绩的描述    根据总成绩判断：      380分及以上  学习优秀      320 ~ 380    学习不错      320以下      学习一般  */  DELIMITER $    CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))  BEGIN    -- 判断总分数    IF total >= 380 THEN       SET description = '学习优秀';    ELSEIF total >= 320 AND total < 380 THEN       SET description = '学习不错';    ELSE       SET description = '学习一般';    END IF;  END$    DELIMITER ;    -- 调用pro_test6存储过程  CALL pro_test6(310,@description);    -- 查询总成绩描述  SELECT @description;

  @变量名:  这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。    @@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量 

-- 标准语法CASE 表达式WHEN 值1 THEN 执行sql语句1;[WHEN 值2 THEN 执行sql语句2;]...[ELSE 执行sql语句n;]END CASE;

-- 标准语法CASEWHEN 判断条件1 THEN 执行sql语句1;[WHEN 判断条件2 THEN 执行sql语句2;]...[ELSE 执行sql语句n;]END CASE;

-- 标准语法初始化语句;WHILE 条件判断语句 DO  循环体语句;  条件控制语句;END WHILE;

-- 标准语法初始化语句;REPEAT  循环体语句;  条件控制语句;  UNTIL 条件判断语句END REPEAT;
-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行

-- 标准语法初始化语句;[循环名称:] LOOP  条件判断语句    [LEAVE 循环名称;]  循环体语句;  条件控制语句;END LOOP 循环名称;
-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！--      如果不加退出循环的语句，那么就变成了死循环。

  -- 标准语法  DECLARE 游标名称 CURSOR FOR 查询sql语句;

  -- 标准语法  OPEN 游标名称;

  -- 标准语法  FETCH 游标名称 INTO 变量名1,变量名2,...;

  -- 标准语法  CLOSE 游标名称;

-- 创建stu_score表CREATE TABLE stu_score(  id INT PRIMARY KEY AUTO_INCREMENT,  score INT);
/*  将student表中所有的成绩保存到stu_score表中*/DELIMITER $
CREATE PROCEDURE pro_test11()BEGIN  -- 定义成绩变量  DECLARE s_score INT;  -- 创建游标,查询所有学生成绩数据  DECLARE stu_result CURSOR FOR SELECT score FROM student;    -- 开启游标  OPEN stu_result;    -- 使用游标，遍历结果,拿到第1行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第2行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第3行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第4行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 关闭游标  CLOSE stu_result;END$
DELIMITER ;
-- 调用pro_test11存储过程CALL pro_test11();
-- 查询stu_score表SELECT * FROM stu_score;

-- ===========================================================/*  出现的问题：    student表中一共有4条数据，我们在游标遍历了4次，没有问题！    但是在游标中多遍历几次呢？就会出现问题*/DELIMITER $
CREATE PROCEDURE pro_test11()BEGIN  -- 定义成绩变量  DECLARE s_score INT;  -- 创建游标,查询所有学生成绩数据  DECLARE stu_result CURSOR FOR SELECT score FROM student;    -- 开启游标  OPEN stu_result;    -- 使用游标，遍历结果,拿到第1行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第2行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第3行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第4行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 使用游标，遍历结果,拿到第5行数据  FETCH stu_result INTO s_score;  -- 将数据保存到stu_score表中  INSERT INTO stu_score VALUES (NULL,s_score);    -- 关闭游标  CLOSE stu_result;END$
DELIMITER ;
-- 调用pro_test11存储过程CALL pro_test11();
-- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错SELECT * FROM stu_score;

/*  当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作  加标记思想：    1.定义一个变量，默认值为0(意味着有数据)    2.当游标结束后，将变量值改为1(意味着没有数据了)*/-- 1.定义一个变量，默认值为0(意味着有数据)DECLARE flag INT DEFAULT 0;-- 2.当游标结束后，将变量值改为1(意味着没有数据了)DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/*  将student表中所有的成绩保存到stu_score表中*/DELIMITER $
CREATE PROCEDURE pro_test12()BEGIN  -- 定义成绩变量  DECLARE s_score INT;  -- 定义标记变量  DECLARE flag INT DEFAULT 0;  -- 创建游标，查询所有学生成绩数据  DECLARE stu_result CURSOR FOR SELECT score FROM student;  -- 游标结束后，将标记变量改为1  DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;    -- 开启游标  OPEN stu_result;    -- 循环使用游标  REPEAT    -- 使用游标，遍历结果,拿到数据    FETCH stu_result INTO s_score;    -- 将数据保存到stu_score表中    INSERT INTO stu_score VALUES (NULL,s_score);  UNTIL flag=1  END REPEAT;    -- 关闭游标  CLOSE stu_result;END$
DELIMITER ;
-- 调用pro_test12存储过程CALL pro_test12();
-- 查询stu_score表SELECT * FROM stu_score;

  DELIMITER $    -- 标准语法  CREATE FUNCTION 函数名称([参数 数据类型])  RETURNS 返回值类型  BEGIN    执行的sql语句;    RETURN 结果;  END$    DELIMITER ;

  -- 标准语法  SELECT 函数名称(实际参数);

  -- 标准语法  DROP FUNCTION 函数名称;