day4 MySql数据库基础及其存储过程和函数和流程控制结构

一、存储过程和函数

1.变量

1.1 系统变量：变量由系统提供，不是用户自定义，属于服务器层面，
		    分为两种全局变量和会话变量，
		    全局变量的作用域：服务器每次启动将为所有的全局变量赋初始值，
		    			   针对有所有的会话(连接)有效，但不能跨重启。
		    会话变量的作用域：仅仅针对于当前会话（连接）有效
 	使用语法：1）查看所有的系统变量 SHOW global |【session】VARIABLES;
 			2)查看满足条件的部分系统变量 SHOW global | 【session】VARIABLES like ‘%char%’
 			3)查看指定的某个系统变量的值 select @@ global |【session】.系统变量名
 			4)为某个系统变量赋值 set global | session 系统变量名 = 值
 							  set @@ global |【session】.系统变量名 = 值
 			如果是全局级别，加global；如果是会话级别，加seesion；如果不写，默认session级别
	
1.2 自定义变量：变量是用户自定义的，不是由系统提供，分为用户变量和局部变量
	用户变量：
			作用域：针对于当前会话（连接）有效，同于会话变量的作用域
				   应用在任何地方，在begin end里面或者是begin end的外面		
			用法：1）声明并初始化 set @用户变量名 = 值
					  		   set @用户变量名 := 值
					   		   select @用户变量名 := 值
		 		 2）赋值（更新用户变量的值）
		 			方式一：通过set或select 更声明初始化一样
					方式二：select into
						select 字段 into 变量名 from 表
				3）使用（查看用户变量的值）
					select @用户变量名;
	局部变量：
		作用域：仅仅在定义它的begin end块中有效 应用在 begin end中的第一句话
		用法：①声明
				DECLARE 变量名 类型;
				DECLARE 变量名 类型 【DEFAULT 值】;
			②赋值（更新变量的值）
				方式一：
					SET 局部变量名=值;
					SET 局部变量名:=值;
					SELECT 局部变量名:=值;
				方式二：
					SELECT 字段 INTO 具备变量名
					FROM 表;
			③使用（查看变量的值）
				SELECT 局部变量名;

#案例：声明两个变量，求和并打印
#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

#用户变量和局部变量的对比
			作用域				 定义位置				语法
用户变量	   当前会话		        会话的任何地方	    加@符号，不用指定类型
局部变量	  定义它的BEGIN END中  BEGIN END的第一句话	  一般不用加@,需要指定类型

2.存储过程和函数

两者都类似于java中的方法，好处是提高代码的重用性，简化操作。

2.1 存储过程

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

2.1.1、创建语法

	CREATE PROCEDURE 存储过程名(参数列表)
	BEGIN
		 存储过程体（一组合法的SQL语句）
	END
		
#注意：
1、参数列表包含三部分
	参数模式  参数名  参数类型
	举例：in stuname varchar(20)

  参数模式：
	in：该参数可以作为输入，也就是该参数需要调用方传入值
	out：该参数可以作为输出，也就是该参数可以作为返回值
	inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
		
2、如果存储过程体仅仅只有一句话，begin end可以省略
	存储过程体中的每条sql语句的结尾要求必须加分号。
	存储过程的结尾可以使用 delimiter 重新设置
	  	语法：delimiter 结束标记
		案例：delimiter $

2.1.2、调用语法

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

--------------------------------案例演示-----------------------------------		
1.空参列表
	#案例：插入到admin表中五条记录
		#创建
		DELIMITER $
		CREATE PROCEDURE myp1()
		BEGIN
			INSERT INTO admin(username,`password`) 
			VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
		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,'成功','失败');#使用if语句，然后输出
		END $

		#调用
		CALL myp3('张飞','8888')$
		
3.创建out 模式参数的存储过程
	#案例1：根据输入的女生名，返回对应的男生名和魅力值
		CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
		BEGIN
			SELECT bo.boyname INTO boyname
			FROM boys bo
			RIGHT JOIN
			beauty b ON b.boyfriend_id = bo.id
			WHERE b.name=beautyName ;
		END $
			
		CALL myp7('小昭',@name,@cp)$   #调用
		SELECT @name,@cp$     #输出
		
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$

2.1.3、删除存储过程

语法：drop procedure 存储过程名
	DROP PROCEDURE p1;
	DROP PROCEDURE p2,p3;  这样是行不通的，一次只能删一个

2.1.4、查看存储过程的信息

DESC myp2; 这样是行不通的
SHOW CREATE PROCEDURE  myp2;

2.2 函数

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

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

2.2.1 创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
	return语句
END

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

2.2.2 调用语法

SELECT 函数名(参数列表)

------------------------------案例演示----------------------------
1.无参有返回
	案例：返回公司的员工个数
	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('k_ing') $     #调用
	#案例2：根据部门名，返回该部门的平均工资
		CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
		BEGIN
			DECLARE sal DOUBLE ;
			SELECT AVG(salary) INTO sal
			FROM employees e
			JOIN departments d ON e.department_id = d.department_id
			WHERE d.department_name=deptName;
			RETURN sal;
		END $
		SELECT myf3('IT')$

2.2.3 查看函数

SHOW CREATE FUNCTION 函数名;

2.2.4 删除函数

DROP FUNCTION 函数名;

#案例
#一、创建函数，实现传入两个float，返回二者之和
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(1,2)$

二、流程控制结构

主要有三种流程控制结构：顺序、分支、循环

1、分支结构

1.1 if函数

功能：实现双分支 可以应用在begin end中或外面
语法：if(条件,值1，值2)

1.2 case结构

可以应用在begin end 中或外面
语法：
	情况1：类似于switch
		case 变量或表达式
		when 值1 then 语句1;
		when 值2 then 语句2;
		...
		else 语句n;
		end
	情况2：类似于多重if...else if...
		case 
		when 条件1 then 语句1;
		when 条件2 then 语句2;
		...
		else 语句n;
		end 

1.3 if结构

功能：类似于多重if，只能应用在begin end 中
语法：
	if 条件1 then 语句1;
	elseif 条件2 then 语句2;
	....
	else 语句n;
	end if;

1.4 案例：

#1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $
SELECT test_if(87)$

用case结构
	CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
	BEGIN 
		DECLARE ch CHAR DEFAULT 'A';
		CASE 
		WHEN score>90 THEN SET ch='A';
		WHEN score>80 THEN SET ch='B';
		WHEN score>60 THEN SET ch='C';
		ELSE SET ch='D';
		END CASE;
		RETURN ch;
	END $
	SELECT test_case(56)$

#2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
END $

CALL test_if_pro(2100)$

2、循环结构

分类：while、loop、repeat

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

2.1 while循环

语法：
【标签:】while 循环条件 do
	循环体;
end while【 标签】;

先判断再执行

2.2 loop循环

语法：
【标签:】loop
	循环体;
end loop 【标签】;

可以用来模拟简单的死循环

2.3 repeat循环

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

2.4 案例

#1.没有添加循环控制语句
	#批量插入，根据次数插入到admin表中多条记录
	DROP PROCEDURE pro_while1$
	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('Rose',i),'666');
			SET i=i+1;
		END WHILE a;
	END $
	CALL pro_while1(100)$

#2.添加leave语句
	#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
	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('xiaohua',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表中多条记录，只插入偶数次
	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('xiaohua',i),'0000');	
		END WHILE a;
	END $
	CALL test_while1(100)$

总结：

拖拖拉拉，总算都过了一遍，怕忘所以把视频笔记啊都整理上来了，以后再复习就方便多了。