Mysql系列第十七講 流程控制語句(高手進階)

qwer1030274531發表於2020-10-10

準備資料

/*建庫javacode2018*/drop database if exists javacode2018;create database javacode2018;/*切換到javacode2018庫*/use javacode2018;/*建立表:t_user*/DROP TABLE IF EXISTS t_user;CREATE TABLE t_user(
  id int PRIMARY KEY COMMENT '編號',
  sex TINYINT not null DEFAULT 1 COMMENT '性別,1:男,2:女',
  name VARCHAR(16) not NULL DEFAULT '' COMMENT '姓名')COMMENT '使用者表';/*插入資料*/INSERT INTO t_user VALUES(1,1,'路人甲Java'),(2,1,'張學友'),(3,2,'王祖賢'),(4,1,'郭富城'),(5,2,'李嘉欣');SELECT * FROM t_user;DROP TABLE IF EXISTS test1;CREATE TABLE test1 (a int not null);DROP TABLE IF EXISTS test2;CREATE TABLE test2 (a int not null,b int NOT NULL );1234567891011121314151617181920212223242526

if函式

語法

if(條件表示式,值1,值2);1

if函式有3個引數。

當引數1為true的時候,返回值1,否則返回值2。

示例
需求:查詢t_user表資料,返回:編號、性別(男、女)、姓名。

分析一下:資料庫中性別用數字表示的,我們需要將其轉換為(男、女),可以使用if函式。

mysql> SELECT id 編號,if(sex=1,'男','女') 性別,name 姓名 FROM t_user;+--------+--------+---------------+| 編號   | 性別   | 姓名          |+--------+--------+---------------+|      1 | 男     | 路人甲Java    ||      2 | 男     | 張學友        ||      3 | 女     | 王祖賢        ||      4 | 男     | 郭富城        ||      5 | 女     | 李嘉欣        |+--------+--------+---------------+5 rows in set (0.00 sec)1234567891011

CASE結構

第1種用法

類似於java中的switch語句。

case 表示式when 值1 then 結果1或者語句1(如果是語句需要加分號)when 值2 then 結果2或者語句2...else 結果n或者語句nend [case] (如果是放在begin end之間需要加case,如果在select後則不需要)123456

示例1:select中使用

查詢t_user表資料,返回:編號、性別(男、女)、姓名。

/*寫法1:類似於java中的if else*/SELECT id 編號,(CASE sex WHEN 1 THEN '男' ELSE '女' END) 性別,name 姓名 FROM t_user;/*寫法2:類似於java中的if else if*/SELECT id 編號,(CASE sex WHEN 1 then '男' WHEN 2 then '女' END) 性別,name 姓名 FROM t_user;1234

示例2:begin end中使用

寫一個儲存過程,接受3個引數:id,性別(男、女),姓名,然後插入到t_user表

建立儲存過程:

/*刪除儲存過程proc1*/DROP PROCEDURE IF EXISTS proc1;/*s刪除id=6的記錄*/DELETE FROM t_user WHERE id=6;/*宣告結束符為$*/DELIMITER $/*建立儲存過程proc1*/CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16))
  BEGIN
    /*宣告變數v_sex用於存放性別*/
    DECLARE v_sex TINYINT UNSIGNED;
    /*根據sex_str的值來設定性別*/
    CASE sex_str      when '男' THEN
      SET v_sex = 1;
    WHEN '女' THEN
      SET v_sex = 2;
    END CASE ;
    /*插入資料*/
    INSERT INTO t_user VALUES (id,v_sex,name);
  END $/*結束符置為;*/DELIMITER ;1234567891011121314151617181920212223

呼叫儲存過程:

CALL proc1(6,'男','郭富城');1

檢視效果:

mysql> select * from t_user;+----+-----+---------------+| id | sex | name          |+----+-----+---------------+|  1 |   1 | 路人甲Java    ||  2 |   1 | 張學友        ||  3 |   2 | 王祖賢        ||  4 |   1 | 郭富城        ||  5 |   2 | 李嘉欣        ||  6 |   1 | 郭富城        |+----+-----+---------------+6 rows in set (0.00 sec)123456789101112

示例3:函式中使用

需求:寫一個函式,根據t_user表sex的值,返回男女

建立函式:

/*刪除儲存過程proc1*/DROP FUNCTION IF EXISTS fun1;/*宣告結束符為$*/DELIMITER $/*建立儲存過程proc1*/CREATE FUNCTION fun1(sex TINYINT UNSIGNED)
  RETURNS varchar(8)
  BEGIN
    /*宣告變數v_sex用於存放性別*/
    DECLARE v_sex VARCHAR(8);
    CASE sex    WHEN 1 THEN
      SET v_sex:='男';
    ELSE
      SET v_sex:='女';
    END CASE;
    RETURN v_sex;
  END $/*結束符置為;*/DELIMITER ;1234567891011121314151617181920

看一下效果:

mysql> select sex, fun1(sex) 性別,name FROM t_user;+-----+--------+---------------+| sex | 性別   | name          |+-----+--------+---------------+|   1 | 男     | 路人甲Java    ||   1 | 男     | 張學友        ||   2 | 女     | 王祖賢        ||   1 | 男     | 郭富城        ||   2 | 女     | 李嘉欣        ||   1 | 男     | 郭富城        |+-----+--------+---------------+6 rows in set (0.00 sec)123456789101112

第2種用法

類似於java中多重if語句。

casewhen 條件1 then 結果1或者語句1(如果是語句需要加分號)when 條件2 then 結果2或者語句2...else 結果n或者語句nend [case] (如果是放在begin end之間需要加case,如果是在select後面case可以省略)123456

if結構

if結構類似於java中的 if…else if…else的語法,如下:

if結構類似於java中的 if..else if…else的語法,如下:if 條件語句1 then 語句1;elseif 條件語句2 then 語句2;...else 語句n;end if;1234567

只能使用在begin end之間。

示例

寫一個儲存過程,實現使用者資料的插入和新增,如果id存在,則修改,不存在則新增,並返回結果

/*刪除id=7的記錄*/DELETE FROM t_user WHERE id=7;/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc2;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name varchar(16),OUT result TINYINT)
  BEGIN
    DECLARE v_count TINYINT DEFAULT 0;/*用來儲存user記錄的數量*/
    /*根據v_id查詢資料放入v_count中*/
    select count(id) into v_count from t_user where id = v_id;
    /*v_count>0表示資料存在,則修改,否則新增*/
    if v_count>0 THEN
      BEGIN
        DECLARE lsex TINYINT;
        select if(lsex='男',1,2) into lsex;
        update t_user set sex = lsex,name = v_name where id = v_id;
        /*獲取update影響行數*/
        select ROW_COUNT() INTO result;
      END;
    else
      BEGIN
        DECLARE lsex TINYINT;
        select if(lsex='男',1,2) into lsex;
        insert into t_user VALUES (v_id,lsex,v_name);
        select 0 into result;
      END;
    END IF;
  END $/*結束符置為;*/DELIMITER ;1234567891011121314151617181920212223242526272829303132

看效果:

mysql> SELECT * FROM t_user;+----+-----+---------------+| id | sex | name          |+----+-----+---------------+|  1 |   1 | 路人甲Java    ||  2 |   1 | 張學友        ||  3 |   2 | 王祖賢        ||  4 |   1 | 郭富城        ||  5 |   2 | 李嘉欣        ||  6 |   1 | 郭富城        |+----+-----+---------------+6 rows in set (0.00 sec)mysql> CALL proc2(7,'男','黎明',@result);Query OK, 1 row affected (0.00 sec)mysql> SELECT @result;+---------+| @result |+---------+|       0 |+---------+1 row in set (0.00 sec)mysql> SELECT * FROM t_user;+----+-----+---------------+| id | sex | name          |+----+-----+---------------+|  1 |   1 | 路人甲Java    ||  2 |   1 | 張學友        ||  3 |   2 | 王祖賢        ||  4 |   1 | 郭富城        ||  5 |   2 | 李嘉欣        ||  6 |   1 | 郭富城        ||  7 |   2 | 黎明          |+----+-----+---------------+7 rows in set (0.00 sec)mysql> CALL proc2(7,'男','梁朝偉',@result);Query OK, 1 row affected (0.00 sec)mysql> SELECT @result;+---------+| @result |+---------+|       1 |+---------+1 row in set (0.00 sec)mysql> SELECT * FROM t_user;+----+-----+---------------+| id | sex | name          |+----+-----+---------------+|  1 |   1 | 路人甲Java    ||  2 |   1 | 張學友        ||  3 |   2 | 王祖賢        ||  4 |   1 | 郭富城        ||  5 |   2 | 李嘉欣        ||  6 |   1 | 郭富城        ||  7 |   2 | 梁朝偉        |+----+-----+---------------+7 rows in set (0.00 sec)1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162

迴圈

mysql中迴圈有3種寫法

  1. while:類似於java中的while迴圈

  2. repeat:類似於java中的do while迴圈

  3. loop:類似於java中的while(true)死迴圈,需要在內部進行控制。

迴圈控制

對迴圈內部的流程進行控制,如:

結束本次迴圈

類似於java中的continue

iterate 迴圈標籤;1

退出迴圈

類似於java中的break

leave 迴圈標籤;1

下面我們分別介紹3種迴圈的使用。

while迴圈

類似於java中的while迴圈。

語法

[標籤:]while 迴圈條件 do迴圈體end while [標籤];123

標籤:是給while取個名字,標籤和iterate、leave結合用於在迴圈內部對迴圈進行控制:如:跳出迴圈、結束本次迴圈

注意:這個迴圈先判斷條件,條件成立之後,才會執行迴圈體,每次執行都會先進行判斷。

示例1:無迴圈控制語句

根據傳入的引數v_count向test1表插入指定數量的資料。

/*刪除test1表記錄*/DELETE FROM test1;/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc3;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc3(v_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:WHILE i<=v_count DO
      INSERT into test1 values (i);
      SET i=i+1;
    END WHILE;
  END $/*結束符置為;*/DELIMITER ;1234567891011121314151617

見效果:

mysql> CALL proc3(5);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+---+| a |+---+| 1 || 2 || 3 || 4 || 5 |+---+5 rows in set (0.00 sec)1234567891011121314

示例2:新增leave控制語句

根據傳入的引數v_count向test1表插入指定數量的資料,當插入超過10條,結束。

/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc4;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc4(v_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:WHILE i<=v_count DO
      INSERT into test1 values (i);
      /*判斷i=10,離開迴圈a*/
      IF i=10 THEN
        LEAVE a;
      END IF;
      SET i=i+1;
    END WHILE;
  END $/*結束符置為;*/DELIMITER ;1234567891011121314151617181920

見效果:

mysql> DELETE FROM test1;Query OK, 20 rows affected (0.00 sec)mysql> CALL proc4(20);Query OK, 1 row affected (0.02 sec)mysql> SELECT * from test1;+----+| a  |+----+|  1 ||  2 ||  3 ||  4 ||  5 ||  6 ||  7 ||  8 ||  9 || 10 |+----+10 rows in set (0.00 sec)12345678910111213141516171819202122

示例3:新增iterate控制語句

根據傳入的引數v_count向test1表插入指定數量的資料,只插入偶數資料。

/*刪除test1表記錄*/DELETE FROM test1;/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc5;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc5(v_count int)
  BEGIN
    DECLARE i int DEFAULT 0;
    a:WHILE i<=v_count DO
      SET i=i+1;
      /*如果i不為偶數,跳過本次迴圈*/
      IF i%2!=0 THEN
        ITERATE a;
      END IF;
      /*插入資料*/
      INSERT into test1 values (i);
    END WHILE;
  END $/*結束符置為;*/DELIMITER ;12345678910111213141516171819202122

見效果:

mysql> DELETE FROM test1;Query OK, 5 rows affected (0.00 sec)mysql> CALL proc5(10);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+----+| a  |+----+|  2 ||  4 ||  6 ||  8 || 10 |+----+5 rows in set (0.00 sec)1234567891011121314151617

示例4:巢狀迴圈

test2表有2個欄位(a,b),寫一個儲存過程(2個引數:v_a_count,v_b_count),使用雙重迴圈插入資料,資料條件:a的範圍[1,v_a_count]、b的範圍[1,v_b_count]所有偶數的組合。

/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc8;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc8(v_a_count int,v_b_count int)
  BEGIN
    DECLARE v_a int DEFAULT 0;
    DECLARE v_b int DEFAULT 0;
    a:WHILE v_a<=v_a_count DO
      SET v_a=v_a+1;
      SET v_b=0;
      b:WHILE v_b<=v_b_count DO
        SET v_b=v_b+1;
        IF v_a%2!=0 THEN
          ITERATE a;
        END IF;
        IF v_b%2!=0 THEN
          ITERATE b;
        END IF;
        INSERT INTO test2 VALUES (v_a,v_b);
      END WHILE b;
    END WHILE a;
  END $/*結束符置為;*/DELIMITER ;123456789101112131415161718192021222324252627282930313233

程式碼中故意將ITERATE a;放在內層迴圈中,主要讓大家看一下效果。

見效果 anhui/

mysql> DELETE FROM test2;Query OK, 6 rows affected (0.00 sec)mysql> CALL proc8(4,6);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test2;+---+---+| a | b |+---+---+| 2 | 2 || 2 | 4 || 2 | 6 || 4 | 2 || 4 | 4 || 4 | 6 |+---+---+6 rows in set (0.00 sec)123456789101112131415161718

repeat迴圈

語法

[標籤:]repeat迴圈體;until 結束迴圈的條件 end repeat [標籤];123

repeat迴圈類似於java中的do…while迴圈,不管如何,迴圈都會先執行一次,然後再判斷結束迴圈的條件,不滿足結束條件,迴圈體繼續執行。這塊和while不同,while是先判斷條件是否成立再執行迴圈體。

示例1:無迴圈控制語句

根據傳入的引數v_count向test1表插入指定數量的資料。

/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc6;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc6(v_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:REPEAT
      INSERT into test1 values (i);
      SET i=i+1;
    UNTIL i>v_count END REPEAT;
  END $/*結束符置為;*/DELIMITER ;123456789101112131415

見效果

mysql> DELETE FROM test1;Query OK, 1 row affected (0.00 sec)mysql> CALL proc6(5);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+---+| a |+---+| 1 || 2 || 3 || 4 || 5 |+---+5 rows in set (0.00 sec)1234567891011121314151617

loop迴圈

語法

[標籤:]loop迴圈體;end loop [標籤];123

loop相當於一個死迴圈,需要在迴圈體中使用iterate或者leave來控制迴圈的執行。

示例1:無迴圈控制語句

根據傳入的引數v_count向test1表插入指定數量的資料。

/*刪除儲存過程*/DROP PROCEDURE IF EXISTS proc7;/*宣告結束符為$*/DELIMITER $/*建立儲存過程*/CREATE PROCEDURE proc7(v_count int)
  BEGIN
    DECLARE i int DEFAULT 0;
    a:LOOP
      SET i=i+1;
      /*當i>v_count的時候退出迴圈*/
      IF i>v_count THEN
        LEAVE a;
      END IF;
      INSERT into test1 values (i);
    END LOOP a;
  END $/*結束符置為;*/DELIMITER ;12345678910111213141516171819

見效果

mysql> DELETE FROM test1;Query OK, 5 rows affected (0.00 sec)mysql> CALL proc7(5);Query OK, 1 row affected (0.01 sec)mysql> SELECT * from test1;+---+| a |+---+| 1 || 2 || 3 || 4 || 5 |+---+5 rows in set (0.00 sec)


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30239065/viewspace-2725929/,如需轉載,請註明出處,否則將追究法律責任。

相關文章