MySQL学习笔记——TCL

最新推荐文章于 2024-02-29 14:41:56 发布

MCC莫大大

最新推荐文章于 2024-02-29 14:41:56 发布

阅读量241

点赞数

分类专栏：笔记文章标签： mysql sql

本文链接：https://blog.csdn.net/lemon6564562/article/details/109104850

版权

笔记专栏收录该内容

71 篇文章 6 订阅

订阅专栏

TCL

库和表
TCL

MySQL源码

库和表

在这里插入图片描述

myemployees表

在这里插入图片描述

girls表

在这里插入图片描述

student表

在这里插入图片描述

TCL

视图

#视图
/*
含义：是一种虚拟的表，使用时和普通的表一样使用，是通过普通表动态生成的数据

视图和表的对比：

		    创建	是否占用物理空间	         使用
	
	视图	create view	 只保存sql逻辑		增删改查，但一般不允许增删改
	
	表	create table	   保存数据		       增删改查

*/
#一、创建视图
/*
语法：
	create view 视图名
	as
	查询语句 ;

好处：
	(1)提高了sql语句的重用性
	(2)简化了复杂的sql操作
	(3)保护数据，提高安全性
	
*/
#案例1：查询姓名中包含字符a的员工名、部门名和工种信息
#(1)创建视图
CREATE VIEW v1
AS
SELECT e.last_name, d.department_name, j.job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id ;
#(2)在视图v1中查找符合要求的信息
SELECT * FROM v1
WHERE last_name LIKE '%a%' ;

#案例2：查询各部门的平均工资级别
#(1)创建视图v2，查看每个部门的平均工资
#即先将平均工资封装成一个表，与子查询类似，但子查询不能保存子查询生成的表
CREATE VIEW v2 
AS 
SELECT 
  department_id d_id,
  AVG(salary) ag
FROM
  employees e 
GROUP BY department_id ;
#(2)查询
SELECT 
  v2.`d_id`,
  v2.`ag`,
  j.`grade_level` 
FROM
  v2 
  INNER JOIN job_grades j 
    ON v2.`ag` BETWEEN j.`lowest_sal` 
    AND j.`highest_sal` ;
#-------------------------------------
SELECT 
  j.grade_level,
  avg_sal.d_id 
FROM
  job_grades j 
  INNER JOIN 
    (SELECT 
      AVG(salary) ag,
      department_id d_id 
    FROM
      employees e 
    GROUP BY department_id) avg_sal 
    ON avg_sal.ag BETWEEN j.lowest_sal 
    AND j.highest_sal ;

#案例3：查询平均工资最低的部门信息
SELECT 
  d.* 
FROM
  departments d 
  INNER JOIN v2 
    ON d.`department_id` = v2.`d_id` 
ORDER BY v2.`ag` 
LIMIT 1 ;

#4.查询平均工资最低的部门名和工资
SELECT 
  d.*,
  v2.`ag` 
FROM
  departments d 
  INNER JOIN v2 
    ON d.`department_id` = v2.`d_id` 
ORDER BY v2.`ag` 
LIMIT 1 ;

#二、修改视图
/*
方式一：若视图存在，则更改视图，若视图不存在，则创建视图
	create or replace view 视图名
	as 
	查询语句 ;

方式二：
	alter view 视图名 as 查询语句 ;
	
*/
#示例1：更改视图v1
CREATE OR REPLACE VIEW v1 AS 
SELECT 
  e.last_name,
  d.department_name 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.department_id = d.department_id ;

#示例2：更改视图v1
ALTER VIEW v1 
  AS 
  SELECT 
    e.last_name,
    j.job_title 
  FROM
    employees e 
    INNER JOIN jobs j 
      ON e.job_id = j.job_id ;

#三、删除视图
/*
语法：
	drop view 视图名1, 视图名2, ... ;
	
*/
#示例：
DROP VIEW v1, v2 ;

#四、查看视图
/*
语法：
	desc 视图名 ;
	show create 视图名 ;

*/
#示例：
DESC v1 ;

#练习
#1.创建视图emp_v1，要求查询电话号码以 011 开头的员工的姓名、工资
#(1)创建视图保存员工姓名和工资
CREATE VIEW emp_v1 AS 
SELECT 
  last_name,
  salary,
FROM
  employees 
WHERE phone_number LIKE '011%' ;
  
#(2)查询信息
SELECT  * FROM emp_v1 ;

#2.创建视图emp_v2，要求查询部门的最高工资大于12000的部门信息
#(1)创建视图保存每个部门中员工的最高工资
CREATE VIEW emp_v2 AS 
SELECT 
  MAX(salary) max_sal,
  department_id d_id
FROM
  employees 
GROUP BY department_id 
HAVING MAX(salary) > 12000 ;

#(2)查询信息
SELECT 
  d.* , emp_v2.`max_sal`
FROM
  departments d 
  INNER JOIN emp_v2 
    ON d.`department_id` = emp_v2.d_id ;

#--------------------------------------
CREATE VIEW emp_v3 AS 
SELECT 
  d.* , max_sal.m_s
FROM
  departments d 
  INNER JOIN 
    (SELECT 
      MAX(salary) m_s,
      department_id d_id 
    FROM
      employees 
    GROUP BY department_id 
    HAVING MAX(salary) > 12000) max_sal 
    ON d.`department_id` = max_sal.d_id ;

SELECT * FROM emp_v3 ;

#五、更新视图
#注意：原始表也会做出相应改变
CREATE OR REPLACE VIEW my_v1 AS 
SELECT 
  last_name,
  email
FROM
  employees ;

SELECT * FROM my_v1 ;

#1.插入：原始表也会插入新数据
INSERT INTO my_v1 VALUES ('张飞', 'zf@qq.com') ;

#2.修改：原始表也会修改数据
UPDATE my_v1 SET last_name = '张无忌' WHERE last_name = '张飞' ;

#3.删除：原始表也会删除数据
DELETE FROM my_v1 WHERE last_name = '张无忌' ;

#具有以下特点的视图不允许更新
/*
(1)包含以下关键字的sql语句：
	分组函数、distinct、grouo by、having、union、union all
(2)常量视图
(3)select中包含子查询
(4)join
(5)from一个不能更新的视图
(6)where子句的子查询引用了from子句中的表

*/

事务

#TCL
#一、事务
/*
事务控制语言

事务：
	一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

事务的属性：ACID
	(1)原子性：事务是一个不可分割的基本单位，事务中的操作要么都发生，要么都不发生
	(2)一致性：事务必须使数据库从一个一致性状态切换到另一个一致性状态
	(3)隔离性：一个事务的执行不会被其他事务干扰，即并发执行的各事务之间是互不影响的
	(4)持久性：一个事务一旦被提交，它对数据库的改变是永久的，接下来的其他操作和
		   数据库障碍不会对其有任何影响

事务的创建：
	隐式事务：事务没有明显的开启和结束标记，如insert、update、delete语句
	显式事务：事务具有明显的开启和结束标记
		  前提：必须先关闭自动提交功能
		  set autocommit = 0 ;
		  
		  创建显式事务的步骤：
		  (1)开启事务
		  set autocommit = 0 ;
		  strat transaction ;(可选)
		  
		  (2)编写事务中的sql语句
		  select
		  insert
		  update
		  delete
		  只包括增删改查
		  
		  (3)结束事务
		  commit ;(提交事务)
		  rollback ;(回滚事务)

事务的隔离级别：
			       脏读	不可重复读	幻读	
	read uncommited		√	    √		 √
	read commited		×	    √		 √
	repeatable read		×	    ×		 √
	serializable		×	    ×		 ×
	
	MySQL中默认 repeatable read
	Oracle默认 read commited
	
	查看当前隔离级别：select @@transaction_isolation ;
	设置当前隔离级别：set transation isolation level 隔离级别 ;
	设置全局隔离级别：set global transation isolation level 隔离级别 ; 
	
*/	
#演示示例：
#(1)开启事务
SET autocommit = 0 ;
START TRANSACTION ;

#(2)编写事务语句
INSERT INTO book 
VALUES
  (1, '白雪公主', 10, 2, '1990-1-1'),
  (2, '小红帽', 8, 1, '1989-1-1') ;

UPDATE 
  book 
SET
  price = 7 
WHERE id = 2 ;

#(3)结束事务
COMMIT ;
#或rollback ;

#二、回滚点
/*
语法：
	savepoint a(自定义名称);
	...
	rollback to a ;

*/
#演示示例
SET autocommit = 0 ;
START TRANSACTION ;
DELETE FROM book WHERE id = 2 ;
SAVEPOINT a ;
DELETE FROM book WHERE id = 1 ;
ROLLBACK TO a ;#如果出现错误，则回滚到a，最终结果是id=2被删除，id=1没有被删除 

#三、delete和truncate在事务使用时的区别
/*
delete删除的数据经过rollback后会回滚到未删除状态
truncate删除的数据一经删除就是永久删除，rollback不会回滚到未删除状态

*/
#演示delete
SET autocommit = 0 ;
START TRANSACTION ;
DELETE TABLE book ;
ROLLBACK ;#会恢复到未删除book表的状态

#演示truncate
SET autocommit = 0 ;
START TRANSACTION ;
TRUNCATE TABLE book ;
ROLLBACK ;#book表不会恢复

变量

#变量
/*
系统变量：由系统提供，属于服务器层面
	全局变量
	会话变量

自定义变量：
	用户变量
	局部变量
	
*/
#一、系统变量
/*
语法：
	(1)查看全局/会话变量
	show global|(session) variables ;
	
	(2)查看满足条件的部分系统变量
	show global|(session) variables like '%char%' ;
	
	(3)查看指定的某个系统变量的值
	select @@global|(session).系统变量名 ;
	
	(4)为某个具体的系统变量赋值
	set @@global|(session).系统变量名 = 值 ;
	set global|(session) 系统变量名 = 值 ;
	
	注意：全局变量要加global
	      会话变量可加session，也可以省略
	      什么的后不写默认session

*/
#1.全局变量
/*
作用范围：
	服务器每次启动都会为全局变量初始化，当服务器的连接没有断开(或重启)时，
	修改的全局变量对所有的连接都有效，一旦服务器断开(或重启)，那么新接入
	服务器的连接的全局变量都会恢复为初始值
		
*/
#(1)查看所有的全局变量
SHOW GLOBAL VARIABLES ;

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

#(3)查看某个指定的全局变量的值
SELECT @@global.autocommit ;
SELECT @@global.transaction_isolation ;

#(4)为某个指定的全局变量赋值
SET @@global.autocommit = 0 ;
SET GLOBAL autocommit = 0 ;

#2.会话变量
/*
作用范围：
	只对当前会话(或连接)有效
		
*/
#(1)查看所有的会话变量
SHOW SESSION VARIABLES ;
SHOW VARIABLES ;

#(2)查看部分会话变量
SHOW SESSION VARIABLES LIKE '%char%' ;
SHOW VARIABLES LIKE '%char%' ;

#(3)查看某个指定的会话变量的值
SELECT @@autocommit ;
SELECT @@session.transaction_isolation ;

#(4)为某个指定的会话变量赋值
SET @@session.autocommit = 0 ;
SET SESSION autocommit = 0 ;

#二、自定义变量
#1.用户变量
/*
语法：
	声明并初始化：
		set @用户变量名 = 值 ;
		set @用户变量名 := 值 ;
		select @用户变量名 := 值 ;
		
	赋值：
		set @用户变量名 = 值 ;
		set @用户变量名 := 值 ;
		select @用户变量名 := 值 ;
		
		select 字段 into @用户变量名 from 表名 ; —— 字段应为一个值 
		
	使用(查看)：
		select @变量名 ;
	
作用范围：
	只对当前会话(或连接)有效，与会话变量作用范围相同
	
应用范围：
	可以应用在任何地方，如begin end内部或外部
	
*/
#(1)声明并初始化
SET @name := 'my' ;

#(2)赋值
SET @name := 'your' ;
#select方法
SELECT COUNT(*) INTO @name FROM employees ;

#(3)查看
SELECT @name ;

#2.局部变量
/*
语法：
	声明(并初始化)：
		declare 局部变量名 类型 ;
		declare 局部变量名 类型 default 值 ;
		
	赋值：
		set 局部变量名 = 值 ;
		set 局部变量名 := 值 ;
		select @局部变量名 := 值 ;
		
		select 字段 into 局部变量名 from 表名 ; —— 字段应为一个值 
		
	使用(查看)：
		select 局部变量名 ;

作用范围：
	只在定义该变量的 begin end 内部有效
	
应用范围：
	只能用在begin end内部的第一句话

*/

#用户变量和局部变量的对比
/*
		作用范围	定义和使用的位置	                 语法

用户变量	当前会话	会话中的任何位置	声明的语法不同，必须加@符号，不用限定类型

局部变量	begin end中 	只能放在begin end中	除select外一般不用加@符号，需要限定类型
				的第一句话
					
*/
#案例：声明两个变量并赋初值，进行求和操作
#(1)使用用户变量
SET @v1 := 1 ;
SET @v2 := 2 ;
SET @sum := @v1 + @v2 ;
SELECT @sum ;

#(2)使用局部变量
#下面代码错误，因为局部变量必须放在begin end中的第一句使用
DECLARE v3 INT ;
SET v3 := 2 ;

DECLARE v4 INT DEFAULT 1 ;

DECLARE my_sum INT ;
SET my_sum := v3 + v4 ;

SELECT my_sum ;

存储过程

#存储过程
/*
类似于Java中的方法

含义：
	一组预先编译好的sql语句的集合，可以理解成批处理语句
好处：
	(1)提高了代码的重用性
	(2)简化操作
	(3)减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
	
语法：
	一、创建：
		create procedure 存储过程名(参数列表)
		begin
			存储过程体
		end
		
	    注意：
		(1)参数列表包含三部分：(参数模式 参数名 参数类型)
		   例如(in stuName varchar(20))
		   
		(2)参数模式：
			in：调用该存储过程时需要传入的参数，即调用方需要提供传入值
			out：调用该存储过程时返回的参数，即该参数可以作为返回值
			inout：该参数既可以作为输入又可以作为输出，即既可以传入值又可以返回值
			
		(3)如果存储过程体内只有一句话，则begin end可以省略
		(4)存储过程体中的每条sql语句结尾都要加分号(;)
		   整个存储过程的结尾使用 delimiter 定义新的结束标记
		   语法：delimiter 结束标记(注意，后面不要加分号，否则会认为结束标记中有分号)
		  
	二、调用：
		call 存储过程名(实参列表) ;
		
	三、删除：
		drop procedure 储存过程名 ;
		注意：只能一次删除一个
		
	四、查看：
		show create procedure 存储过程名 ;
		
*/
#1.空参列表
#案例：向admin表中插入5条记录
#创建
DELIMITER $
CREATE PROCEDURE myp1 () 
BEGIN
  INSERT INTO admin (username, `password`) 
  VALUES
    ('john', 0000),
    ('Lily', 0001),
    ('Rose', 0002),
    ('Jack', 0003),
    ('Tom', 0004)) ;
END $

#调用
CALL myp1 () $

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

#调用
CALL myp2 ('周芷若') $

#案例2：创建存储过程，实现用户登录过程
#创建
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 ('张飞', 1111) $

#2.out模式
#案例1：根据女性名，返回对应的男朋友名
#创建
CREATE PROCEDURE myp4 (
  IN beautyName VARCHAR (20),
  OUT boyName VARCHAR (20)
) 
BEGIN
  SELECT 
    bo.boyName INTO boyName #将该值赋值给返回值 
  FROM
    boys bo 
    RIGHT JOIN beauty be 
      ON bo.id = be.boyfriend_id 
  WHERE be.name = beautyName ;
END $

#调用
#定义用户变量，接收返回值
SET @boyName $
CALL myp4('周芷若', @boyName) $

#案例2：根据女性名，返回对应的男朋友名和魅力值
#建立
CREATE PROCEDURE myp5 (
  IN beautyName VARCHAR (20),
  OUT boyName VARCHAR (20),
  OUT CP INT 
) 
BEGIN
  SELECT 
    bo.boyName, bo.userCP INTO boyName, CP #将该值赋值给返回值 
  FROM
    boys bo 
    RIGHT JOIN beauty be 
      ON bo.id = be.boyfriend_id 
  WHERE be.name = beautyName ;
END $

#调用
SET @userCP $
CALL myp5('周芷若', @boyName, @userCP) $

#input模式
#案例1：传入a和b两个值，返回a和b的2倍
#创建
CREATE PROCEDURE myp6 (INOUT a INT, INOUT b INT) 
BEGIN
    SET a = a * 2 ;
    SET b = b * 2 ;
END $

#调用
SET @m = 10 $
SET @n = 20 $
CALL myp6(@m, @n) $

SELECT @m, @n $

#删除存储过程
DROP PROCEDURE myp6 $

#查看存储过程
SHOW CREATE PROCEDURE myp3 $

#=============================================
#练习1：
#创建存储过程实现：传入用户名和密码，并插入到admin表中
CREATE PROCEDURE my_upload (
  IN userName VARCHAR (20),
  IN `password` VARCHAR (20)
) 
BEGIN
  INSERT INTO admin (
    admin.`username`,
    admin.`password`
  ) 
  VALUES
    (userName, `password`) ;
END $

CALL my_upload('张飞', '1010') $

SELECT * FROM admin $

#练习2：
#创建存储过程实现：传入女性编号，返回女性名称和电话
CREATE PROCEDURE my_getBeauty (
  IN id INT,
  OUT beautyName VARCHAR (20),
  OUT phoneNumber VARCHAR(20)
) 
BEGIN
  SELECT 
    be.`name`,
    be.phone INTO beautyName,
    phoneNumber 
  FROM
    beauty be 
  WHERE be.id = id ;
END $

SET @name $
SET @phone $
CALL my_getBeauty(2, @name, @phone) $

SELECT @name, @phone $

#练习3：
#创建存储过程实现：传入两个女性的生日，比较大小
CREATE PROCEDURE my_compareDate (
  IN birth1 DATETIME,
  IN birth2 DATETIME,
  OUT result INT
) 
BEGIN
  SELECT 
    DATEDIFF(birth1, birth2) INTO result ;
END $

CALL my_compareDate('1998-1-1', '1999-2-1', @result) $

SELECT @result $

#练习4：
#创建存储过程实现传入一个日期，格式化成xxxx年xx月xx日并返回
CREATE PROCEDURE my_format (
  IN `date` DATETIME,
  OUT formatDate VARCHAR (20)
) 
BEGIN
  SELECT 
    DATE_FORMAT(`date`, '%Y年%m月%d日') INTO formatDate ;
END $

CALL my_format(NOW(), @myDate) $

SELECT @myDate $

#练习5：
#创建存储过程实现传入一个女性名，返回：女性名 and 男性名
CREATE PROCEDURE test5 (
  IN beautyName VARCHAR (20),
  OUT str VARCHAR (40)
) 
BEGIN
  SELECT 
    CONCAT(beautyName, ' and ', IFNULL(bo.boyName, 'null')) INTO str
  FROM
    boys bo 
    RIGHT JOIN beauty be 
      ON bo.id = be.boyfriend_id 
  WHERE be.name = beautyName ;
END $

CALL test5('周芷若', @str) $

SELECT @str $

#练习6：
#创建存储过程实现传入一个女性名，根据输入的条目数和起始索引，查询beauty表中的记录
CREATE PROCEDURE test6 (IN `begin` INT, IN num INT) 
BEGIN
  SELECT 
    * 
  FROM
    beauty 
  LIMIT `begin`, num ;
END $

CALL test6(2, 3) $

函数

#函数
/*
含义：
	一组预先编译好的sql语句的集合，可以理解成批处理语句
	
好处：
	(1)提高了代码的重用性
	(2)简化操作
	(3)减少了编译次数以及和数据库服务器的连接次数，提高了效率
	
和存储过程的区别：
	存储过程：可以有0个返回值，也可以有多个返回值
		  适合做批量插入、批量更新
		  
	函数：只能有一个返回值
	      适合处理数据之后得到一个结果

*/
#一、创建
/*
语法：
	create function 函数名(参数列表) returns 返回类型
	begin 
		函数体
	end

参数列表：
	参数名 参数类型


函数体：
	必须有return语句，
	如果return没有放在函数体最后也不会报错，但应该放在最后
	
注意：
	(1)当函数体中只有一句话时，可以省略begin end
	(2)使用delimiter语句设置结束标记
	
*/
#二、调用
/*
语法：
	select 函数名(参数列表) ;
	
*/
SET GLOBAL log_bin_trust_function_creators = TRUE ;
DELIMITER $
#案例1：使用函数返回公司的员工个数
CREATE FUNCTION myF1 () RETURNS INT 
BEGIN
  DECLARE myCount INT DEFAULT 0 ;#定义局部变量，接收返回值
  SELECT 
    COUNT(*) INTO myCount #为局部变量赋值
  FROM
    employees ;
  RETURN myCount ;
END $

SELECT myF1() $

#案例2：根据员工名返回员工工资
CREATE FUNCTION myF2 (employeeName VARCHAR (20)) RETURNS DOUBLE
BEGIN
# set @salary = 0 ; #也可以使用用户变量
  DECLARE mySalary DOUBLE DEFAULT 0 ;
  SELECT 
#   salary into @salary
    salary INTO mySalary 
  FROM
    employees 
  WHERE last_name = employeeName ;
# return @salary ;
  RETURN mySalary ;
END $

SELECT myF2('Greenberg') $

#案例3：根据部门名返回该部门的平均工资
CREATE FUNCTION myF3 (departmentName VARCHAR (20)) RETURNS DOUBLE 
BEGIN
  SET @avgSalary = 0 ;
  SELECT 
    AVG(e.salary) INTO @avgSalary 
  FROM
    employees e 
    INNER JOIN departments d 
      ON e.department_id = d.department_id 
  WHERE d.department_name = departmentName;
  RETURN @avgSalary ;
END $

SELECT myF3('IT') $

#三、查看
/*
语法：
	show create function 函数名 ;

*/
DELIMITER ;
SHOW CREATE FUNCTION myF1 ;

#四、删除
/*
语法：
	drop function 函数名 ;

*/
DROP FUNCTION myF3 ;

#案例4：创建函数，实现传入两个float值，返回二者之和
DELIMITER $
CREATE FUNCTION myF4 (num1 FLOAT, num2 FLOAT) RETURNS FLOAT 
BEGIN
  DECLARE mySum FLOAT DEFAULT 0 ;
# set mySum = num1 + num2 ;
  SELECT 
    num1 + num2 INTO mySum ;
  RETURN mySum ;
END $

SELECT myF4(1.23, 3.14) $

流程控制

#流程控制结构
/*
顺序结构：程序从上往下依次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环结构：程序在满足一定条件的基础上，重复执行一段代码

*/
#一、分支结构
/*
1.if函数：
	select if(表达式1, 表达式2, 表达式3)
	
	如果表达式1为真，则返回表达式2的值，否则返回表达式3的值

2.case：
	(1)类似于java中的switch case语句，用于实现等值判断
		case 变量|表达式|字段 
		when 值1 then 返回值1
		when 值2 then 返回值2
		...
		else 返回值n
		end ;
		------------------------
		case 变量|表达式|字段 
		when 值1 then 语句1 ;
		when 值2 then 语句2 ;
		...
		else 语句n ;
		end case ;
	
	(2)类似于java中的多重if语句，用于实现区间判断
		case 
		when 条件1 then 返回值1
		when 条件2 then 返回值2
		...
		else 返回值n
		end ;
		------------------------
		case 
		when 条件1 then 语句1 ;
		when 条件2 then 语句2 ;
		...
		else 语句n ;
		end case ;

	case的特点：
		(1)作为表达式，嵌套在其他语句中使用，放在任何地方，如begin end内或外
		(2)作为独立的语句，只能放在begin end中使用
		(3)else可以省略，若都不满足，则返回null
		
*/
#案例：创建存储过程，根据传入的成绩，显示等级，A：90-100 
DELIMITER $

CREATE PROCEDURE myGrade (IN stuGrade INT) 
BEGIN
  CASE
    WHEN stuGrade BETWEEN 90 
    AND 100 
    THEN 
    SELECT 
      'A' ;
    WHEN stuGrade BETWEEN 80 
    AND 90 
    THEN 
    SELECT 
      'B' ;
    WHEN stuGrade BETWEEN 70 
    AND 80 
    THEN 
    SELECT 
      'C' ;
    WHEN stuGrade BETWEEN 60 
    AND 70 
    THEN 
    SELECT 
      'D' ;
  END CASE ;
END $

CALL myGrade(95) $

#if结构
/*
功能：实现多重分支

语法:
	if 条件1 then 语句1 ;
	elseif 条件2 then 语句2 ;
	...
	else 语句n ; #可以省略
	end if ;

注意：
	只能放在begin end中使用

	简单判断：if函数
	等值判断：case语句
	多重分支：if结构
	case语句也可以实现多重分支，但多使用if结构

*/
#案例：创建函数，根据传入的成绩，返回等级，A：90-100 
SET GLOBAL log_bin_trust_function_creators = TRUE ;

CREATE FUNCTION getGrade (grade INT) RETURNS CHAR 
BEGIN
  IF grade BETWEEN 90 
  AND 100 
  THEN RETURN 'A' ;
  ELSEIF grade BETWEEN 80 
  AND 90 
  THEN RETURN 'B' ;
  ELSEIF grade BETWEEN 70 
  AND 80 
  THEN RETURN 'C' ;
  ELSEIF grade BETWEEN 60 
  AND 70 
  THEN RETURN 'D' ;
  ELSE RETURN 'E' ;
  END IF ;
END $

SELECT getGrade(97) $

#二、循环结构
/*
分类：
	while 、loop、repeat

循环控制：
	iterate类似于continue：结束本次循环，继续下一次循环
	leave类似于break：结束当前循环
	想要使用循环控制，必须给循环起别名
	
1.while：

	语法：
		[标签:] while 循环条件 
			do 循环体 ;
		end while [标签] ;
	
2.loop：

	语法：
		[标签:] loop 
			循环体 ;
		end loop [标签] ;

	注意：
		如果不搭配循环条件，则是一个死循环
	
3.repeat：

	语法：
		[标签:] repeat
			循环体 ;
		until 结束条件 ;
		end repeat [标签] ;

*/
#案例1：将数据批量插入到admin表中
CREATE PROCEDURE myInsert (IN myCount INT) 
BEGIN
  DECLARE i INT DEFAULT 1 ;
  WHILE
    i <= myCount DO 
    INSERT INTO admin (username, `password`) 
    VALUES
      (CONCAT('Rose', i), 123123) ;
    SET i = i + 1 ;
  END WHILE ;
END $

CALL myInsert(100) $

#案例2：将数据批量插入到admin表中，如果添加记录数大于20则停止
CREATE PROCEDURE myInsert (IN myCount INT) 
BEGIN
  DECLARE i INT DEFAULT 1 ;
  a :
  WHILE
    i <= myCount DO 
    INSERT INTO admin (username, `password`) 
    VALUES
      (CONCAT('Rose', i), 123123) ;
    #添加循环控制语句
    IF i > 20 
    THEN LEAVE a ;
    END IF ;
    SET i = i + 1 ;
  END WHILE a ;
END $

CALL myInsert(100) $

#案例3：将数据批量插入到admin表中，只添加偶数次的记录
CREATE PROCEDURE myInsert (IN myCount INT) 
BEGIN
  DECLARE i INT DEFAULT 0 ;
  a :
  WHILE
    i <= myCount DO SET i = i + 1 ;
    #添加循环控制语句
    IF i % 2 <> 0 
    THEN ITERATE a ;
    END IF ;
    INSERT INTO admin (username, `password`) 
    VALUES
      (CONCAT('Rose', i), 123123) ;
  END WHILE a ;
END $

CALL myInsert(100) $

#练习：已知表stringcontent，有字段：id(自增长)，content varchar(20)，向该表中插入指定个数的随机字符
#1.建表
DROP TABLE IF EXISTS stringcontent ;

CREATE TABLE stringcontent (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content VARCHAR (20)
) ;

#2.建立存储过程
DELIMITER $

CREATE PROCEDURE practice (IN `count` INT) 
BEGIN
  #设置循环变量
  DECLARE i INT DEFAULT 0 ;
  #初始字符串
  DECLARE str VARCHAR (26) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
  #起始索引
  DECLARE startIndex INT DEFAULT 1 ;
  #截取字符的长度
  DECLARE len INT DEFAULT 1 ;
  WHILE
    i < `count` DO 
    #产生一个随机整数，代表随机索引1~26
    SET startIndex = FLOOR(RAND() * 26+1) ;
    #产生一个随机整数，代表随机长度1~(26-startIndex+1)，最大长度为20
    SET len = FLOOR(RAND() * (20- startIndex + 1) + 1) ;
    #在str中随机截取子串
    INSERT INTO stringcontent (content) VALUES (SUBSTR(str, startIndex, len)) ;
    SET i = i + 1 ;
  END WHILE ;
END $

#3.调用
CALL practice (20) $

#查看
SELECT * FROM stringcontent $

MCC莫大大

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记——TCL

TCL库和表myemployees表girls表student表TCL视图事务变量存储过程函数流程控制库和表myemployees表girls表student表TCL视图#视图/*含义：是一种虚拟的表，使用时和普通的表一样使用，是通过普通表动态生成的数据视图和表的对比：创建是否占用物理空间使用视图 create view 只保存sql逻辑增删改查，但一般不允许增删改表 create table 保存数据
复制链接

扫一扫