MySQL 学习笔记——级联、存储过程、函数、变量、流程控制

级联

含义：
级联：(cascade)在计算机科学里指多个对象之间的映射关系，建立数据之间的级联关系提高管理效率
应用场景：重复性的操作十分烦琐，尤其是在处理多个彼此关联对象情况下，此时我们可以使用级联（Cascade）操作。级联 在关联映射中是个重要的概念，指当主动方对象执行操作时，被关联对象（被动方）是否同步执行同一操作。

DESC major;
DESC stuinfo;

SHOW INDEX FROM major;
SHOW INDEX FROM stuinfo;
#删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
ALTER TABLE stuinfo DROP INDEX fk_stuinfo_major;

#传统的方式添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

SELECT * FROM major;

SELECT * FROM stuinfo;
INSERT INTO stuinfo
SELECT 1,'john','女',NULL,NULL,1 UNION ALL
SELECT 2,'john','女',NULL,NULL,2 UNION ALL
SELECT 3,'john','女',NULL,NULL,3 UNION ALL
SELECT 4,'john','女',NULL,NULL,3 UNION ALL
SELECT 5,'john','女',NULL,NULL,2 UNION ALL
SELECT 6,'john','女',NULL,NULL,2 UNION ALL
SELECT 7,'john','女',NULL,NULL,3 UNION ALL
SELECT 8,'john','女',NULL,NULL,2 UNION ALL
SELECT 9,'john','女',NULL,NULL,1 ;

#删除专业表的3号专业
#报错 应该先删从表的
DELETE FROM major WHERE id=3;

#方式一：级联删除
#添加级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#删除专业表的3号专业
#可以删除,且主表和从表的都删了
DELETE FROM major WHERE id=3;

#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
#删除专业表的3号专业
#可以删除,且主表删除,从表的置空
DELETE FROM major WHERE id=2;

存储过程

含义：一组经过预先编译的sql语句的集合
好处：
1、提高了sql语句的重用性，减少了开发程序员的压力
2、减少了编译次数提高了效率
3、减少了与数据库的传输次数

分类：
1、无返回无参
2、仅仅带IN类型，无返回有参
3、仅仅带OUT类型，有返回无参
4、既带IN又带OUT，有返回有参
5、带INOUT，有返回有参
注意：IN、OUT、INOUT都可以在一个存储过程中带多个

创建存储过程

语法：
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END;
MySQL中不能单独使用BEGIN END语句块

注意：
1、存储过程体的每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用 DELIMITER 重新设置新的结束标记
示例：
DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,…)
BEGIN
sql语句1;
sql语句2;
END $
2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略BEGIN END
3、参数前面的符号的意思
IN:该参数只能作为输入 （该参数不能做返回值）
OUT：该参数只能作为输出（该参数只能做返回值）
INOUT：既能做输入又能做输出

调用存储过程

CALL 存储过程名(实参列表)

#1.空参列表
#案例：插入admin表中五条记录
SELECT * FROM admin;

#SQLyog不能运行,在命令行中执行
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,PASSWORD)
	VALUES('john',0000),('Tomi',1111),('jack',2222),('Tom',1100),('rose',0220),('jackson',0330);
END $

#调用
CALL myp1() $

#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#调用
CALL myp2('刘涛') $

#案例2：创建存储过程实现，用户是否登录成功
CREATE  PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	SELECT COUNT(*) INTO result #赋值
	FROM admin
	WHERE admin.username=username
	AND admin.password = PASSWORD;
	
	SELECT IF (result>0,'成功','失败') ;#使用
END$

#调用
CALL myp3('张飞',1111)$

#3、创建带out模式的存储过程
#案例1：根据女神名，返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN 
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#调用
#set @bName$ #不定义也行
CALL myp5('小昭',@bName)$
SELECT @bName$

#案例2：根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
	SELECT bo.boyName , bo.userCP INTO boyName,userCP 
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#调用
CALL myp6('小昭',@bName,@userCP)$

SELECT @bName,@userCP$

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
#查看
SELECT @m,@n$

#二、删除存储过程
#语法：DROP PROCEDURE 存储过程名 ;#只能一个一个删除
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#错误的

#三、查看存储过程的信息
DESC myp1;#错误的
SHOW CREATE PROCEDURE myp1;

函数

创建函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

与存储过程的区别：
存储过程：可以有0个返回,也可以有多个返回，适合做批量的插入，批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果

语法：
CREATE FUNCTION 函数名(参数名 参数类型,…) RETURNS 返回类型
BEGIN
函数体
END

注意：

1. 参数列表 包含两部分：
   参数名 参数类型
2. 函数体：肯定会有RETURN 语句，如果没有会报错。
   如果RETURN 语句没有放在函数体的最后也不会报错，但不建议。
3. 函数体中仅有一句话，则可以省略BEGIN END
4. 使用DELIMETER语句设置结束标记

调用函数

SELECT 函数名（实参列表）

#1.无参有返回
#案例：返回公司的员工个数
SET GLOBAL log_bin_trust_function_creators=TRUE;

CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c #赋值
	FROM employees;
	RETURN c;
END $

SELECT myf1() $

#2.有参有返回
#案例1：根据员工名，返回他的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN 	
	SET @sal = 0;#定义用户变量
	SELECT salary INTO @sal  #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

SELECT myf2('Kochhar')$

#案例2:根据部门名，返回该部门的平均工资
DROP FUNCTION myf3;
CREATE FUNCTION myf3(depName VARCHAR(50)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON d.department_id = e.department_id
	WHERE d.department_name=depName;
	RETURN sal;
END $

SELECT myf3('IT')$	

#三、查看函数
SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;
#案例：创建函数，实现传入两个float，返回iangzhe之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM = num1 + num2;
	RETURN SUM;
END $
SELECT test_fun1(2,4)$

流程控制结构

变量

系统变量

一、全局变量
作用域：服务器每次启动将为所有的全局变量赋初始值，针对所有的会话（连接）有效，但不能跨重启

注意：
如果是全局级别，则需要加 GLOBAL ，如果是会话级别，则需要加 SESSION ，如果不写则默认 SESSION
说明：
变量由系统提供，不是用户自定义，属于服务器层面

使用的语法：
1、查看所有的系统变量
SHOW SESSION VARIABLES ; #默认是session
SHOW GLOBAL VARIABLES ;
2、查看满足条件的部分系统变量
SHOW GLOBAL | 【 SESSION 】 VARIABLES LIKE ‘%char%’;
3、查看指定的某个系统变量的值
SELECT @@GLOBAL | 【SESSION】. 系统变量名;
SELECT @@session.transaction_isolation;
SELECT @@global.transaction_isolation;
4、为某个系统变量赋值
方式一：
SET GLOBAL | 【SESSION】 系统变量名 = 值;
方式二：
SET @@GLOBAL | 【SESSION】.系统变量名 = 值;

#①查看所有的全局变量
SHOW GLOBAL VARIABLES ;

#2、查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';

#③查看执行的全局变量的值
SELECT @@global.autocommit;
SELECT @@transaction_isolation;

#④为某个指定的全局变量赋值
#方式一:
SET @@global.autocommit=0;
#方式二:
SET GLOBAL autocommit =1;

二、会话变量

作用域：针对于当前会话（连接）有效

#①查看所有的会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;

#②查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';

#③查看指定的会话变量
SELECT @@transaction_isolation;
SELECT @@session.transaction_isolation;

#④为某个会话变量赋值
#方式一：
SET @@session.transaction_isolation='read-uncommitted';
#方式二：
SET SESSION transaction_isolation = 'read-committed';

自定义变量

一、用户变量
说明：变量是用户自定义的，不是系统的
使用步骤：
①声明
②赋值
③使用(查看、比较、运算等)

作用域：针对于当前会话（连接）有效，同于会话变量的作用域
应用在任何地方，也就是BEGIN END里面，或BEGIN END外面

赋值的操作符：=或:=
①声明并初始化
SET @用户变量名 = 值;
SET @用户变量名:= 值;
SELECT @用户变量名:= 值;
②赋值（更新用户变量的值）
方式一：通过SET或SELECT
SET @用户变量名 = 值;
SET @用户变量名:= 值;
SELECT @用户变量名:= 值;
方式二：通过SELECT INTO
要求查询结果只有一个值
SELECT 字段 INTO @用户变量名
FROM 表;
③使用（查看用户变量的值）
SELECT @用户变量名;

#案例：
#声明并初始化
#弱类型不用声明参数类型
SET @name='john';
SET @name=100;
SET @count=1;

#报错
SET @name = ;

#赋值
SELECT COUNT(*) INTO @count
FROM employees;

#查看
SELECT @count;

二、局部变量
作用域：仅仅在定义它的begin end中有效应用在 begin end 中的第一句话!!
声明：
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
赋值：
方式一：一般用于赋简单的值
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二：一般用于赋表 中的字段值
要求查询结果只有一个值
SELECT 字段名或表达式 INTO 局部变量名
FROM 表;
使用：
SELECT 局部变量名;

对比用户变量和局部变量:
		    作用域			定义和使用的位置						语法
用户变量 	当前会话			会话中的任何地方						必须加@符号，不用限制类型

局部变量		BEGIN END 中	只能在 BEGIN END 中，且为第一句话		不用加@符号，需要限制类型

#案例：声明变量并赋初始值，求和，并打印
#1.用户变量
SET @m=1;
SET @n=2;
SET @sum = @m + @n;
SELECT @sum;

#2.局部变量
#报错,只能在begin end中
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM = m+n;
SELECT SUM;

流程控制

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码

分支结构

一、IF函数
功能：实现简单的双分支
语法：
IF(表达式1,表达式2,表达式3)
执行顺序：
如果表达式1成立,则IF函数返回表达式2的值,否则返回表达式3的值
应用：任何地方

二、CASE语句

情况1：类似于java中的switch语句,一般用于实现等值判断
语法：
CASE 变量 | 表达式 | 字段
WHEN 要判断的值 THEN 返回的值1或语句1;
WHEN 要判断的值 THEN 返回的值2或语句2;
…
ELSE 要返回的值n或语句n;
END CASE;

情况2：类似于java中的多重IF语句,一般用于实现区间判断
语法：
CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
…
ELSE 要返回的值n或语句n;
END CASE;

特点：
①可以作为表达式，嵌套在其他语句中使用，可以放在任何地方， BEGIN END 中或 BEGIN END 的外面
可以作为独立的语句去使用，只能放在 BEGIN END 中
②如果 WHEN 中的值满足或条件成立，则执行对应的 THEN 后面的语句，并且结束 CASE。如果都不满足，则执行 ELSE 中的语句或值
③ ELSE 可以省略，如果 ELSE 省略了，并且所有 WHEN 条件都不满足，则返回 NULL

#案例
#创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A，80-90，显示B，60-80，显示C，否则，显示D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE
	WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $
CALL test_case(91)$

三、IF结构
功能：实现多重分支
语法：
if 情况1 then 语句1;
elseif 情况2 then 语句2;
…
else 语句n;
end if;
特点：
只能用在begin end中！！！

三者应用场合比较：

if函数		简单双分支
case结构	等值判断 的多分支
if结构		区间判断 的多分支

循环结构

分类：
while、loop、repeat
循环控制：
iterate类似于 continue ，继续，结束本次循环，继续下一次
leave 类似于 break，跳出，结束当次所在的循环

1. while
   语法：
   【标签:】while 循环条件 do
   循环体;
   end while 【标签】;
   联想java中的while:
   while(循环条件){
   循环体;
   }

2. loop
   语法:
   【标签：】loop
   循环体;
   end loop 【标签】;
   可以用来模拟简单的死循环

3. repeat
   语法：
   【标签：】 repeat
   循环体;
   until 结束循环的条件
   end repeat 【标签】;

特点：
只能放在BEGIN END里面
如果要搭配leave跳转语句，需要使用标签，否则可以不用标签
leave类似于java中的break语句，跳出所在循环！！！

1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin (username,`password`) VALUES(CONCAT('Bug',i),777);
		SET  i = i+1;
	END WHILE a ;
END$
CALL pro_while1(100)$

/*
int i =1;
while(i<=insetCount){
	//插入
	i++;

}

*/

#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Tom',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

CALL test_while1(100)$

#3.添加iterate
#案例：批量插入，根据次数插入到admin表中多条记录，直插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Tom',i),'0000');
		
	END WHILE a;
END $

CALL test_while1(100)$

/*
Java中：

int i = 0;
while (i<=insertCount){
	i++;
	if(i%2==0){
		continue;
	}
	插入
	
}
*/