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类型变量、并赋默认值为10
DELIMITER $
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;

-- 标准语法
CASE
WHEN 判断条件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 函数名称;