MySQL6天学习笔记——day06

程序员文章站 2022-05-30 13:15:21

...

文章目录

10.变量
11.存储过程
- 11.1 存储过程练习
12.函数
13.流程控制结构
- 13.1 流程控制结构练习

10.变量

-- 变量
/*
系统变量：
			全局变量
			会话变量

自定义变量：
			用户变量
			局部变量
*/

-- 1.系统变量
/*
说明：变量由系统定义，不是用户定义，属于服务器层面，必须拥有super权限才能为系统变量赋值，作用域为整个服务器
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
1、查看所有系统变量
show global|[session] variables;
2、查看满足条件的部分系统变量
show global|[session] variables like '%char%';
3、查看指定的系统变量的值
select @@global|[session] 系统变量名;
4、为某个系统变量赋值
方式一：
set global|[session] 系统变量名=值;
方式二：
set @@global|[session] 系统变量名=值;
*/

-- 1.1 全局变量
/*
服务器每次启动都将为所有的全局变量初始化值
作用域：针对于所有会话（连接）有效，但不能跨重启
*/
-- ①查看所有全局变量
SHOW GLOBAL VARIABLES;
-- ②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
-- ③查看指定的系统变量的值
SELECT @@global.autocommit;
select @@transaction_isolation; #查看隔离级别
-- ④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

-- 1.2 会话变量
/*
服务器为每一个连接的客户端都提供了系统变量
作用域：针对于当前会话（连接）有效
*/
-- ①查看所有会话变量
SHOW SESSION VARIABLES;
-- ②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
-- ③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.transaction_isolation;
SELECT @@transaction_isolation;
-- ④为某个会话变量赋值
SET @@session.transaction_isolation='read-uncommitted';
SET SESSION transaction_isolation='read-committed';

-- 2.自定义变量
/*
说明：变量由用户自定义，而不是系统提供的
使用步骤：
		1、声明
		2、赋值
		3、使用（查看、比较、运算等）
*/

-- 2.1 用户变量
/*
作用域：针对于当前会话（连接）有效，作用域和会话变量一样
*/
-- 赋值操作符：=或:=
-- ①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

-- ②赋值（更新变量的值）
-- 方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
-- 方式二：
	SELECT 字段 INTO @变量名
	FROM 表;
-- ③使用（查看变量的值）
SELECT @用户变量名;

-- 案例：
/*
set @count = 1;
*/
SELECT COUNT(*) INTO @count 
FROM employees;

SELECT @count; #107

-- 2.2 局部变量
/*
作用域：仅仅在定义它的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的第一句话				一般不用加@,需要指定类型
*/

11.存储过程

-- 存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作
*/

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

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

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/

-- 2.调用语法
CALL 存储过程名(实参列表);

-- 3.案例演示
-- 3.1 空参列表
-- 案例：插入到admin表中五条记录
SELECT * FROM admin;

delimiter $ #自定义结束标记符
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`) 
VALUE ('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $

-- 调用
CALL myp1()$

-- 3.2 创建带in模式参数的存储过程
-- 案例1：创建存储过程实现 根据女神名，查询对应的男神信息

delimiter $ #自定义结束标记符
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT b.*,bo.* FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.`name` = beautyName;
END $

-- 调用
CALL myp2('柳岩')$
CALL myp2('小昭')$

-- 案例2：创建存储过程实现，用户是否登录成功
delimiter $ #自定义结束标记符
CREATE PROCEDURE myp3(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('张飞','8888')$

-- 3.3 创建带out模式参数的存储过程
-- 案例1：根据输入的女神名，返回对应的男神名
delimiter $ #自定义结束标记符
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：根据输入的女神名，返回对应的男神名和魅力值
delimiter $ #自定义结束标记符
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$  #使用变量

-- 3.4 创建带inout模式参数的存储过程
-- 案例1：传入a和b两个值，最终a和b都翻倍并返回
delimiter $ #自定义结束标记符
CREATE PROCEDURE myp7(INOUT a INT,INOUT b INT)
BEGIN
SET a = a * 2; #局部变量赋值
SET b = b * 2;
END $

-- 调用
SET @m=10$ #定义用户变量
SET @n=20$
CALL myp7(@m,@n)$
SELECT @m,@n$ #使用变量

-- 4.删除存储过程
/*
语法：drop procedure 存储过程名
*/
DROP PROCEDURE test_pro3;
DROP PROCEDURE p2,p3;  #报错,不能一次删除多个

-- 5.查看存储过程的信息
DESC myp2; #报错
SHOW CREATE PROCEDURE myp2;

11.1 存储过程练习

-- 1.创建存储过程实现传入用户名和密码，插入到admin表中
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_pro1(IN `username` VARCHAR(20),IN `loginPwd` VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,admin.`password`)
VALUES(username,loginPwd);
END $

-- 调用
CALL test_pro1('xiexu','283619')$

-- 2.创建存储过程实现传入女神编号，返回女神名称和女神电话
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_pro2(IN `bid` INT,OUT `uname` VARCHAR(20),OUT `uphone` VARCHAR(20))
BEGIN
SELECT b.name,b.phone INTO `uname`,`uphone`
FROM beauty b 
WHERE b.id = `bid`;
END $

-- 调用
CALL test_pro2(2,@uname,@uphone)$
SELECT @uname,@uphone$

-- 3.创建存储过程或函数实现传入两个女神生日，返回大小
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_pro3(IN `birth1` DATETIME,IN `birth2` DATETIME,OUT `result` INT)
BEGIN
SELECT DATEDIFF(`birth1`,`birth2`) INTO `result`;
END $

-- 调用
CALL test_pro3('1987-12-30',now(),@m)$
SELECT @m$

-- 4.创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_pro4(IN `mydate` DATETIME,OUT `strDate` VARCHAR(10))
BEGIN
SELECT DATE_FORMAT(`mydate`,'%y年%m月%d日') INTO `strDate`;
END $

-- 调用
CALL test_pro4(now(),@str)$
SELECT @str$

-- 5.创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
/*
如 传入 ：小昭
返回： 小昭 AND 张无忌
*/
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_pro5(IN `beautyName` VARCHAR(20),OUT `str` VARCHAR(50))
BEGIN
SELECT CONCAT(`beautyName`,' and ',IFNULL(bo.boyName,'NULL')) INTO `str`
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = `beautyName`;
END $

-- 调用
CALL test_pro5('柳岩',@str)$
SELECT @str $

-- 6.创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_pro6(IN `startIndex` INT,IN `size` INT)
BEGIN
SELECT * FROM beauty LIMIT `startIndex`,`size`;
END $

-- 调用
CALL test_pro6(3,5)$

12.函数

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

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

-- 1.创建语法
/*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意：
1.参数列表 包含两部分：
									参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
*/

-- 2.调用语法
/*
SELECT 函数名(参数列表)
*/

-- 3.案例演示

-- 3.1 无参有返回
-- 案例：返回公司的员工个数
delimiter $ #自定义结束标记符
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量，并且加上默认值0

SELECT count(*) INTO c #为局部变量赋值
FROM employees;
RETURN c;
END $

-- 调用
SELECT myf1()$

-- 3.2 有参有返回
-- 案例1：根据员工名，返回它的工资
delimiter $ #自定义结束标记符
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：根据部门名，返回该部门的平均工资
delimiter $ #自定义结束标记符
CREATE FUNCTION myf3(`deptName` VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE av DOUBLE; #定义局部变量

SELECT AVG(salary) INTO av #为局部变量赋值
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = `deptName`;

RETURN av;
END $

-- 调用
SELECT myf3('IT')$

-- 4.查看函数
SHOW CREATE FUNCTION myf3;

-- 5.删除函数
DROP FUNCTION myf3;

-- 案例：创建函数，实现传入两个float，返回二者之和
delimiter $ #自定义结束标记符
CREATE FUNCTION test_fun1(`num1` FLOAT,`num2` FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0; #定义局部变量，并且有默认值0
	
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$

13.流程控制结构

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

-- 1.分支结构

-- 1.1 case结构
/*
情况一：类似于switch，一般用于实现等值判断
语法：
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end case;

情况2：类似于多重if语句，一般用于实现区间判断。
语法：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end case;

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

③ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL。

位置：
可以放在任何位置，
如果放在begin end 外面，作为表达式结合着其他语句使用
如果放在begin end 里面，一般作为独立的语句使用
*/

-- 案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100, 显示A，80-90，显示B，60-80，显示c，否则，显示D
delimiter $ #自定义结束标记符
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(55)$

-- 1.2 if结构
/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
[else 语句n;]
end if;

功能：类似于多重if
位置：只能应用在begin end中
*/

-- 案例：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
delimiter $ #自定义结束标记符
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
if score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;

END $

-- 调用
SELECT test_if(86)$

-- 2.循环结构
/*
分类：
while、loop、repeat

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

-- 2.1 while
/*
语法：
[标签:]while 循环条件 do
				循环体;
end while [标签];

联想：
while(循环条件){
	循环体;
}
*/

-- 没有添加循环控制语句
-- 案例1：批量插入，根据次数插入到admin表中多条记录
delimiter $ #自定义结束标记符
CREATE PROCEDURE pro_while1(IN `insertCount` INT)
BEGIN
DECLARE i INT DEFAULT 1; #定义局部变量，并且有默认值1

WHILE i<=`insertCount` DO
INSERT INTO admin(username,`password`) VALUES (CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;

END $

-- 调用
CALL pro_while1(100)$

SELECT * FROM admin$

-- 添加leave语句
-- 案例2：批量插入，根据次数插入到admin表中多条记录，如果次数大于20则停止
TRUNCATE TABLE admin$ #删除admin表中的全部数据

delimiter $ #自定义结束标记符
CREATE PROCEDURE pro_while2(IN `insertCount` INT)
BEGIN
DECLARE i INT DEFAULT 1; #定义局部变量，并且有默认值1

a:WHILE i<=`insertCount` DO
INSERT INTO admin(username,`password`) VALUES (CONCAT('xiaohua',i),'666');
IF i>=20 THEN LEAVE a; #如果i>=20，就结束当前所在的循环，类似于break
END IF;
SET i=i+1;
END WHILE a;

END $

-- 调用
CALL pro_while2(100)$

-- 添加iterate语句
-- 案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$ #删除admin表中的全部数据

delimiter $ #自定义结束标记符
CREATE PROCEDURE pro_while3(IN `insertCount` INT)
BEGIN
DECLARE i INT DEFAULT 0; #定义局部变量，并且有默认值1

a:WHILE i<=`insertCount` DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a; #如果i%2!=0，就结束此次循环，继续执行下一次循环，类似于continue
END IF;

INSERT INTO admin(username,`password`) VALUES (CONCAT('xiaohua',i),'000');

END WHILE a;

END $

-- 调用
CALL pro_while3(100)$

-- 2.2 loop
/*
语法：
[标签:]loop
	循环体;
end loop [标签];

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

-- 2.3 repeat
/*
语法：
[标签:]repeat
	循环体;
until 结束循环的条件
end repeat [标签];
*/

13.1 流程控制结构练习

/*
已知表stringcontent
其中字段：
id 自增长
content varchar(20)

向该表插入指定个数的，随机的字符串
*/

DROP TABLE IF EXISTS stringcontent;

-- 创建表stringcontent
CREATE TABLE stringcontent(
				id INT PRIMARY KEY AUTO_INCREMENT,
				content VARCHAR(20)
);

-- 操作
delimiter $ #自定义结束标记符
CREATE PROCEDURE test_randstr_insert(IN `insertCount` INT)
BEGIN
#定义一个循环变量i，表示插入次数
DECLARE i INT DEFAULT 1; 
#定义一个具有26个字母的字符串
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
#代表初始索引
DECLARE startIndex INT;
#代表截取的字符长度
DECLARE len INT;

WHILE i<=`insertCount` DO
#floor表示向下取整，rand()*26+1表示产生[1,27)之间的随机数
SET startIndex=FLOOR(RAND()*26+1); #代表初始索引，随机范围1-26
#因为content定义varchar(20)，所以最多只能截取20个
#代表截取长度,随机范围 1 - (20-startIndex+1)
SET len=FLOOR(RAND()*(20-startIndex+1)+1);

INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));

SET i=i+1; #循环变量更新
END WHILE;

END $

-- 调用
CALL test_randstr_insert(10)$

相关标签：数据库数据库 mysql sql

上一篇： DTD基础一个XML只能有一个DTD

下一篇： 1019

MySQL6天学习笔记——day06

文章目录

10.变量

11.存储过程

11.1 存储过程练习

12.函数

13.流程控制结构

13.1 流程控制结构练习

python学习笔记：第17天面向对象03 类与类之间的关系

python学习笔记：第16天面向对象02--对象中的成员

Vue.js第二天学习笔记（vue-router）

Vue.js第四天学习笔记（组件）

Vue.js第四天学习笔记

Vue.js第三天学习笔记（计算属性computed）

python学习笔记：第14天内置函数补充和递归

python学习笔记：第13天内置函数（一）

[python学习笔记]Python语言程序设计(北理工嵩天)

python学习笔记：第15天初识面向对象

MySQL6天学习笔记——day06

文章目录

10.变量

11.存储过程

11.1 存储过程练习

12.函数

13.流程控制结构

13.1 流程控制结构练习

python学习笔记：第17天 面向对象03 类与类之间的关系

python学习笔记：第16天 面向对象02--对象中的成员

Vue.js第二天学习笔记（vue-router）

Vue.js第四天学习笔记（组件）

Vue.js第四天学习笔记

Vue.js第三天学习笔记（计算属性computed）

python学习笔记：第14天 内置函数补充和递归

python学习笔记：第13天 内置函数（一）

[python学习笔记]Python语言程序设计(北理工 嵩天)

python学习笔记：第15天 初识面向对象

python学习笔记：第17天面向对象03 类与类之间的关系

python学习笔记：第16天面向对象02--对象中的成员

python学习笔记：第14天内置函数补充和递归

python学习笔记：第13天内置函数（一）

[python学习笔记]Python语言程序设计(北理工嵩天)

python学习笔记：第15天初识面向对象