2345浏览器网站大全,宁波企业网站开发公司,创建网站代码是什么,广西网站建设-好发信息网前言#xff1a;
学习和使用数据库可以说是程序员必须具备能力#xff0c;这里将更新关于MYSQL的使用讲解#xff0c;大概应该会更新30篇#xff0c;涵盖入门、进阶、高级(一些原理分析);这一篇和上一篇差不多#xff0c;当做扩展#xff0c;用到的时候再查即可(毕竟数据… 前言
学习和使用数据库可以说是程序员必须具备能力这里将更新关于MYSQL的使用讲解大概应该会更新30篇涵盖入门、进阶、高级(一些原理分析);这一篇和上一篇差不多当做扩展用到的时候再查即可(毕竟数据库最多的还是增删改查 )。这一篇很像我们常用的高级语言的基础课程。虽然MYSQL命令很多但是自己去多敲一点到后面忘记了查一下就可以回忆起来使用了这一系列也是本人学习MYSQL做的笔记也是为了方便后面忘记查询参考资料黑马、csdn和知乎博客欢迎收藏 关注本人将会持续更新后端和AI算法的学习笔记。 文章目录 1. 流程控制1.1 分支结构之 IF1.2 分支结构之 CASE1.3 循环结构之 LOOP1.4 循环结构之 WHILE1.5 循环结构之 REPEAT1.6 跳转语句之 LEAVE语句1.7 跳转语句之 ITERATE 语句 2. 游标2.1 什么是游标或光标4.2 使用游标步骤2.3 举例2.4 小结
1. 流程控制
解决复杂问题不可能通过一个 SQL 语句完成我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序是我们完成复杂操作必不可少的一部分。只要是执行的程序流程就分为三大类
顺序结构 程序从上往下依次执行分支结构 程序按条件进行选择执行从两条或多条路径中选择一条执行循环结构 程序满足一定条件下重复执行一组语句
针对于MySQL 的流程控制语句主要有 3 类。注意只能用于存储程序。
条件判断语句 IF 语句和 CASE 语句循环语句 LOOP、WHILE 和 REPEAT 语句跳转语句 ITERATE (iterate)和 LEAVE (leave)语句
1.1 分支结构之 IF
IF 语句的语法结构是
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]
...
[ELSE 操作N] #注意else没有then
END IF根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。
特点① 不同的表达式对应不同的操作
②使用在begin end中
范例1
IF val IS NULL
THEN SELECT val is null;
ELSE SELECT val is not null; #注意else没有then
END IF**范例2**声明存储过程“update_sal_by_eno”定义IN参数eno输入员工编号。判断该员工 薪资如果低于2000元并且入职时间超过5年就涨薪500元否则就不变。
CREATE PROCEDURE update_sal_by_eno1(IN eno INT)
BEGINDECLARE emp_sal DOUBLE; DECLARE hire_year DOUBLE;SELECT sal INTO emp_sal FROM emps WHERE empno eno;SELECT DATEDIFF(CURDATE(),hiredate)/365 INTO hire_year FROM emps WHERE empno eno;IF emp_sal 2000 AND hire_year 5THEN UPDATE emps SET sal sal 500 WHERE empno eno; END IF;
END ;**范例3**声明存储过程“update_sal_by_eno2”定义IN参数eno输入员工编号。判断该员工 薪资如果低于3000元就更新薪资为3000元薪资如果大于等于3000元且低于5000的但是奖金 比例为NULL的就更新奖金比例为0.01其他的涨薪100元。
DELIMITER //
CREATE PROCEDURE update_sal_by_eno2(IN eno INT)
BEGINDECLARE emp_sal DOUBLE; DECLARE emp_comm DECIMAL(5,2);SELECT sal INTO emp_sal FROM emps WHERE empno eno; SELECT comm INTO emp_comm FROM emps WHERE empno eno;IF emp_sal 3000THEN UPDATE emps SET sal 3000 WHERE empno eno;SELECT 1;ELSEIF emp_sal 5000 AND emp_comm IS NULLTHEN UPDATE emps SET comm 0.01*sal WHERE empno eno;SELECT 2;ELSE #注意else没有thenUPDATE emps SET sal sal 100 WHERE empno eno; SELECT 3;END IF;
END //
DELIMITER //1.2 分支结构之 CASE
CASE 语句的语法结构1
#情况一类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句需要加分号)
...
ELSE 结果n或语句n(如果是语句需要加分号)
END [case]如果是放在begin end中需要加上case如果放在select后面不需要CASE 语句的语法结构2
#情况二类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句需要加分号)
...
ELSE 结果n或语句n(如果是语句需要加分号)
END [case]如果是放在begin end中需要加上case如果放在select后面不需要**范例1**使用CASE流程控制语句的第1种格式判断val值等于1、等于2或者两者都不等。
CASE valWHEN 1 THEN SELECT val is 1; WHEN 2 THEN SELECT val is 2;ELSE SELECT val is not 1 or 2;
END CASE;**范例2**使用CASE流程控制语句的第2种格式判断val是否为空、小于0、大于0或者等于0。
CASEWHEN val IS NULL THEN SELECT val is null; WHEN val 0 THEN SELECT val is less than 0;WHEN val 0 THEN SELECT val is greater than 0; ELSE SELECT val is 0;
END CASE;**范例3**声明存储过程“update_salary_by_eno4”定义IN参数eno输入员工编号。判断改员工所属部门如果在10号部门工资加500如果在20号部门工资加600如果在30号部门工资加700;其他部门加300。
CREATE PROCEDURE update_salary_by_eno4(IN eno INT)
BEGINDECLARE dept_no,emp_sal INT; SELECT sal,deptno INTO emp_sal,dept_no FROM emps WHERE empno eno; CASE deptnoWHEN 10THEN UPDATE emps SET salemp_sal500 WHERE empno eno; WHEN 20THEN UPDATE emps SET salemp_sal600 WHERE empno eno; WHEN 30THEN UPDATE emps SET salemp_sal700 WHERE empno eno;ELSE UPDATE emps SET salemp_sal300 WHERE empno eno; END CASE;
END //**范例4**声明存储过程pro_sal_grade定义IN参数eno输入员工编号。判断该员工的薪资等级如果在[700,1200]之间为等级一如果在[12011400]之间为等级二如果在[1401,2000]之间为等级三如果在[2001,3000]之间为等级四如果在[3001,9999]之间为等级五
CREATE PROCEDURE sal_grade(IN eno INT)
BEGINDECLARE emp_sal INT; SELECT sal INTO emp_sal FROM emps WHERE empno eno;CASE WHEN emp_sal700 AND emp_sal1200THEN SELECT 等级一; WHEN emp_sal1201 AND emp_sal1400 THEN SELECT 等级二; WHEN emp_sal1401 AND emp_sal2000 THEN SELECT 等级三;WHEN emp_sal2001 AND emp_sal3000 THEN SELECT 等级四;WHEN emp_sal3001 AND emp_sal999 THEN SELECT 等级五;END CASE;
END //1.3 循环结构之 LOOP
LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出使用LEAVE子 句跳出循环过程。
LOOP语句的基本格式如下
[loop_label:] LOOP循环执行的语句
END LOOP [loop_label] 其中loop_label表示LOOP语句的标注名称该参数可以省略。
**范例1**使用LOOP语句进行循环操作id值小于10时将重复执行循环过程。
DECLARE id INT DEFAULT 0;
add_loop:LOOPSET id id 1;IF id 10 THEN LEAVE add_loop; END IF;END LOOP add_loop;**范例2**当市场环境变好时公司为了奖励大家决定给大家涨工资。声明存储过程“update_sal_loop()”声明OUT参数num输出循环次数。存储过程中实现循环给大家涨薪薪资涨为原来的1.1倍。直到全公司的平均薪资达到8000结束。并统计循环次数。
CREATE PROCEDURE update_sal_loop(OUT num INT)
BEGINDECLARE avg_sal DOUBLE; DECLARE loop_count INT DEFAULT 0;SELECT AVG(sal) INTO avg_sal FROM emps;label_loop:LOOPIF avg_sal 8000 THEN LEAVE label_loop; #离开语句END IF;UPDATE emps SET sal sal * 1.1; SET loop_count loop_count 1;SELECT AVG(sal) INTO avg_salary FROM emps; END LOOP label_loop;SET num loop_count;
END ;1.4 循环结构之 WHILE
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时先对指定的表达式进行判断如 果为真就执行循环内的语句否则退出循环。WHILE语句的基本格式如下
[while_label:] WHILE 循环条件 DO循环体
END WHILE [while_label];while_label为WHILE语句的标注名称如果循环条件结果为真WHILE语句内的语句或语句群被执行直 至循环条件为假退出循环。
**范例1**WHILE语句示例i值小于10时将重复执行循环过程代码如下
CREATE PROCEDURE test_while()
BEGINDECLARE i INT DEFAULT 0;WHILE i 10 DO SET i i 1;END WHILE;SELECT i;
END ;
#调用
CALL test_while();**范例2**市场环境不好时公司为了渡过难关决定暂时降低大家的薪资。声明存储过程“update_salary_while()”声明OUT参数num输出循环次数。存储过程中实现循环给大家降薪薪资降为原来的90%。直到全公司的平均薪资达到3000结束。并统计循环次数。
CREATE PROCEDURE update_sal_while(OUT num INT)
BEGINDECLARE avg_sal DOUBLE ;DECLARE while_count INT DEFAULT 0;SELECT AVG(sal) INTO avg_sal FROM emps; WHILE avg_sal 3000 DOUPDATE emps SET sal sal * 0.9; SET while_count while_count 1;SELECT AVG(sal) INTO avg_sal FROM emps; END WHILE;SET num while_count;
END //1.5 循环结构之 REPEAT
REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是REPEAT 循环首先会执行一次循环然后在 UNTIL 中进行表达式的判断如果满足条件就退出即 END REPEAT如果条件不满足则会就继续执行循环直到满足退出条件为止。
REPEAT语句的基本格式如下
[repeat_label:] REPEAT循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]repeat_label为REPEAT语句的标注名称该参数可以省略REPEAT语句内的语句或语句群被重复直至expr_condition为真。
范例1
CREATE PROCEDURE test_repeat()
BEGINDECLARE i INT DEFAULT 0;REPEATSET i i 1; UNTIL i 10END REPEAT;SELECT i;
END ;**范例2**当市场环境变好时公司为了奖励大家决定给大家涨工资。声明存储过程“update_salary_repeat()”声明OUT参数num输出循环次数。存储过程中实现循环给大家涨薪薪资涨为原来的1.15倍。直到全公司的平均薪资达到9000结束。并统计循环次数。
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGINDECLARE avg_sal DOUBLE ;DECLARE repeat_count INT DEFAULT 0;SELECT AVG(sal) INTO avg_sal FROM emps; REPEATUPDATE emps SET sal sal * 1.15;SET repeat_count repeat_count 1;SELECT AVG(sal) INTO avg_sal FROM emps; UNTIL avg_sal 9000END REPEAT;SET num repeat_count;
END //对比三种循环结构
1、这三种循环都可以省略名称但如果循环中添加了循环控制语句LEAVE或ITERATE则必须添加名称。
2、 LOOP一般用于实现简单的死循环 WHILE先判断后执行 REPEAT先执行后判断无条件至少执行一次
1.6 跳转语句之 LEAVE语句
LEAVE语句可以用在循环语句内或者以 BEGIN 和 END 包裹起来的程序体内表示跳出循环或者跳出程序体的操作(相当于break)
基本格式如下
LEAVE 标记名其中label参数表示循环的标志。LEAVE和BEGIN … END或循环一起被使用。
**范例1**创建存储过程 “leave_begin()”声明INT类型的IN参数num。给BEGIN…END加标记名并在BEGIN…END中使用IF语句判断num参数的值。
如果num0则使用LEAVE语句退出BEGIN…END如果num1则查询“emps”表的平均薪资如果num2则查询“emps”表的最低薪资如果num2则查询“emps”表的最高薪资。
IF语句结束后查询“emp”表的总人数。
CREATE PROCEDURE leave_begin(IN num INT) begin_label: BEGINIF num0THEN LEAVE begin_label; ELSEIF num1THEN SELECT AVG(sal) FROM emps; ELSEIF num2THEN SELECT MIN(sal) FROM emps; ELSESELECT MAX(sal) FROM emps; END IF;SELECT COUNT(*) FROM emps;
END ;**范例2**当市场环境不好时公司为了渡过难关决定暂时降低大家的薪资。声明存储过程“leave_while()”声明OUT参数num输出循环次数存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%直到全公 司的平均薪资小于等于10000并统计循环次数。
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN#DECLARE avg_sal DOUBLE;#记录平均工资DECLARE while_count INT DEFAULT 0; #记录循环次数SELECT AVG(sal) INTO avg_sal FROM emps; #① 初始化条件while_label:WHILE TRUE DO #② 循环条件#③ 循环体IF avg_sal 10000 THEN LEAVE while_label;END IF;UPDATE emps SET sal sal * 0.9; SET while_count while_count 1;#④ 迭代条件SELECT AVG(sal) INTO avg_sal FROM emps; END WHILE;#赋值SET num while_count;
END ;1.7 跳转语句之 ITERATE 语句
ITERATEiterate语句只能用在循环语句LOOP、REPEAT和WHILE语句内表示重新开始循环将执行顺序 转到语句段开头处(continue).
语句基本格式如下
ITERATE labellabel参数表示循环的标志。ITERATE语句必须跟在循环标志前面。
范例 定义局部变量num初始值为0。循环结构中执行num 1操作。
如果num 10则继续执行循环如果num 15则退出循环结构
CREATE PROCEDURE test_iterate()
BEGINDECLARE num INT DEFAULT 0;my_loop:LOOPSET num num 1;IF num 10THEN ITERATE my_loop; ELSEIF num 15THEN LEAVE my_loop; END IF;SELECT 顿开教育让每个学员都学有所成; END LOOP my_loop;
END //2. 游标
2.1 什么是游标或光标
虽然我们也可以通过筛选条件 WHERE 和 HAVING或者是限定返回记录的关键字 LIMIT 返回一条记录 但是却无法在结果集中像指针一样向前定位一条记录、向后定位一条记录或者是随意定位到某一条记录 并对记录的数据进行处理。
这个时候就可以用到游标。在 SQL 中游标是一种临时的数据库对象可以指向存储在数据库表中的数据行指针。说白了就是可以逐行读取。 MySQL中游标可以在存储过程和函数中使用。
比如我们查询了 emps数据表中工资高于1500的员工都有哪些
SELECT empno,ename,sal FROM emps WHERE sal 1500;这里我们就可以通过游标来操作数据行如图所示此时游标所在的行是“7698”的记录我们也可以在结果集上滚动游标指向结果集中的任意一行。
4.2 使用游标步骤
游标必须在声明处理程序之前被声明并且变量和条件还必须在声明游标或处理程序之前被声明。 如果我们想要使用游标一般需要经历四个步骤。
第一步声明游标
在MySQL中使用DECLARE关键字来声明游标其语法的基本形式如下
DECLARE cursor_name CURSOR FOR select_statement;这个语法适用于 MySQLSQL ServerDB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL需要写成
DECLARE cursor_name CURSOR IS select_statement;要使用 SELECT 语句来获取数据结果集而此时还没有开始遍历数据这里 select_statement 代表的是
SELECT 语句返回一个用于创建游标的结果集。
比如
DECLARE cur_emp CURSOR FOR
SELECT empno,sal FROM emps;DECLARE cursor_dept CURSOR FOR
SELECT deptno,dname, loc FROM depts ;第二步打开游标
打开游标的语法如下
OPEN cursor_name当我们定义好游标之后如果想要使用游标必须先打开游标。
打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区为后面游标的 逐条读取 结果集中的记录做准备。
OPEN cur_emp ;第三步使用游标从游标中取得数据
语法如下
FETCH cursor_name INTO var_name [, var_name] ...这句的作用是使用 cursor_name 这个游标来读取当前行并且将数据保存到 var_name 这个变量中游标指针指到下一行。如果游标读取的数据行有多个列名则在 INTO 关键字后面赋值给多个变量名即可。
注意var_name必须在声明游标之前就定义好。
FETCH cur_emp INTO emp_no, emp_sal ;注意游标的查询结果集中的字段数必须跟 INTO 后面的变量数一致否则在存储过程执行的时候MySQL 会提示错误。
第四步关闭游标
CLOSE cursor_name有 OPEN 就会有 CLOSE也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源 如果不及时关闭游标会一直保持到存储过程结束影响系统运行的效率。而关闭游标的操作会释放游标占用的系统资源。
关闭游标之后我们就不能再检索查询结果中的数据行如果需要检索只能再次打开游标。
CLOSE cur_emp; 2.3 举例
创建存储过程“get_count_by_limit_total_salary()”声明IN参数 limit_total_salary声明OUT参数total_count。函数的功能可以实现累加薪资最高的几个员工的薪资值直到薪资总和 达到limit_total_salary参数的值返回累加的人数给total_count。
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_sal INT,OUT total_count INT)
BEGIN#声明变量DECLARE sum_sal INT DEFAULT 0; #记录累加的总工资DECLARE emp_count INT DEFAULT 0; #记录循环总次数DECLARE emp_sal INT; #当前员工的工资#声明游标DECLARE cursor_emp CURSOR FOR SELECT sal FROM emp ORDER BY sal DESC;#打开游标OPEN cursor_emp;#使用游标WHILE sum_sal limit_total_sal DOFETCH cursor_emp INTO emp_sal;SET sum_sal emp_salsum_sal;SET emp_count emp_count1;END WHILE;#关闭游标CLOSE cursor_emp;#设置传出参数值SET total_count emp_count;
END;2.4 小结
游标是 MySQL 的一个重要的功能为逐条读取结果集中的数据提供了完美的解决方案。跟在应用层面实现相同的功能相比游标可以在存储程序中使用效率高程序也更加简洁。
但是在并发过程会对数据进行加锁影响速度故最好用完就解锁。
