MySQL Lecture 6: Process Control

Free learning recommendation: mysql video tutorial

##Article directory

Branch structure
• IF function
  • case structure
  • IF structure
Loop structure

---

The process control in mysql is similar to that in other programming languages, and is also divided into three types:

Sequential structure, Branch structure, Loop structure. Basically, the sequential structures used in the previous articles (except the IF function) are mainly introduced below. The branch structures and loop structures are mainly introduced below.

Branch structure

IF function

Syntax:

IF(expression 1, expression 2, expression Formula 3) Description: If expression 1 is true, execute expression 2, otherwise execute expression 3

case structure

case structure has two syntaxes:

Features:

• # is used as an expression, nested in other statements, and used in BEGIN- It can be used in END and BEGIN-END.
Use
• as an independent statement and can only be placed in BEGIN-END. If ELSE is omitted and all WHEN are not satisfied, null will be returned.

• 【演示case作为独立的语句】# 创建存储过程，根据传入的成绩，显示等级：90-100显示A 、 80-90显示B、60-80显示C、否则显示DCREATE PROCEDURE test_case(IN score INT)BEGIN
  	CASE
  	WHEN score>=90 AND score=80 THEN SELECT 'B';
  	WHEN score>=60 THEN SELECT 'C';
  	ELSE SELECT 'D';
  	END CASE;END $CALL test_case(95)$

IF structure

Syntax:

if condition 1 then statement 1;
elseif condition 2 then statement 2;
...
[else statement n;]
end if;
<pre class="brush:php;toolbar:false">【演示IF结构】# 根据传入的成绩，显示等级：90-100显示A 、 80-90显示B、60-80显示C、否则显示DCREATE FUNCTION test_if(score INT) RETURNS CHARBEGIN IF score&gt;=90 AND score=80 THEN RETURN 'B'; ELSEIF score&gt;=60 THEN RETURN 'C'; ELSE RETURN 'D'; END IF;END $SELECT test_if(61)$</pre>Loop structure

There are three types of loop structures in mysql:

Name##whilerepeat, similar to the do-wile statement in JavaloopThe keywords that can be used in loop control are iterate

Grammar	Features
	Judge first Then execute
	Execute first and then judge
	Unconditional infinite loop

(similar to continue in Java) and

leave (similar to break in Java)

【演示简单的循环结构】# 案例:批量插入,根据次数插入到admin表中多条记录.CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i20则停止TRUNCATE TABLE admin$DROP PROCEDURE test_while1$CREATE PROCEDURE test_while1(IN insertCount INT)BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;END $CALL test_while1(100)$--------------------------------------------------------------------------------------------【演示添加了iterate的语句】# 案例:批量插入,根据次数插入到admin表中,直插入偶数次CREATE PROCEDURE test_while2(IN insertCount INT)BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<code></code> After learning the process control of mysql, try to complete the following exercises<blockquote>
<p><br></p>
<pre class="brush:php;toolbar:false">【习题答案】DROP TABLE IF EXISTS stringcontent;CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
	);DELIMITER $CREATE PROCEDURE test_pro1(IN insertCount INT)BEGIN
	DECLARE i INT DEFAULT 1;#定义一个循环变量i,表示插入次数
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE strIndex INT DEFAULT 1;#代表起始索引
	DECLARE len INT DEFAULT 1;#代表截取的字符的长度
	WHILE i<img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/052/824caf6adae3f27ef5f57d1863fc12a6-4.png?x-oss-process=image/resize,p_40" class="lazy" alt="MySQL Lecture 6: Process Control">

More related free learning recommendations:

mysql tutorial(Video)

The above is the detailed content of MySQL Lecture 6: Process Control. For more information, please follow other related articles on the PHP Chinese website!