準備資料
/*建庫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種寫法
-
while:類似於java中的while迴圈
-
repeat:類似於java中的do while迴圈
-
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/,如需轉載,請註明出處,否則將追究法律責任。