# 【mysql的編程專題①】流程控制與其他語法
[TOC]
---
> 流程控制與內置函數,一般用在select的field字段上,或者用在函數,存儲過程,觸發器中;
> 如果用在select上就會隨著query出來的row來隱式迭代;
## 注釋與語句結束符
### 語句結束符
默認有兩個:``;`` 和 ``\g(只能在命令行中使用)``
可以使用delimiter 命令來修改語句結束符,例如: delimiter $$(注意,一般手工修改結束符后再手工改回原來默認值 ;)
### 注釋
行注釋: ``#`` 和 ``--[空格]``
塊注釋: ``/* */``
## 變量的定義與輸出
### 定義變量
MySQL中可以使用DECLARE關鍵字來定義變量。定義變量的基本語法如下:
```sql
DECLARE var_name[,...] type [DEFAULT value]
```
+ 其中, DECLARE關鍵字是用來聲明變量的;var_name參數是變量的名稱,這里可以同時定義多個變量;type參數用來指定變量的類型;DEFAULT value子句將變量默認值設置為value,沒有使用DEFAULT子句時,默認值為NULL。**只能用在存儲過程或者函數內部**
> 在過程中定義的變量并不是真正的定義,你只是在BEGIN/END(即復合語句)塊內定義了而已。注意這些變量和會話變量不一樣,不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。變量一旦聲明,你就能在任何能使用會話變量、文字、列名的地方使用。還需要注意的一點是,在一個塊內,我們需要把所有要使用的變量先聲明,才能在后面使用,并且不能在聲明變量的語句間夾雜其他使用變量的語句,否會報語法錯誤。
```sql
CREATE PROCEDURE P5()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 5;
INSERT INTO t VALUES(a);
SELECT s1 FROM t WHERE s1>= b;
END;
-------------------------------------------------
mysql> CALL p5();
+----+
| s1 |
+----+
| 5 |
| 5 |
+----+
2 rows in set
Query OK, 0 rows affected
```
MySQL中可以使用SET關鍵字來為變量賦值。SET語句的基本語法如下:
```sql
SET var_name = expr [, var_name = expr] ...
```
MySQL中還可以使用SELECT…INTO語句為變量賦值。其基本語法如下:
```sql
SELECT col_name[,…] INTO var_name[,…]
FROM table_name WEHRE condition
```
> 其中,col_name參數表示查詢的字段名稱;var_name參數是變量的名稱;table_name參數指表的名稱;condition參數指查詢條件。
```sql
-- 查看系統變量 show variables [like pattern]
show variables like "innodb%";
-- set 變量名=變量值; 注意:為了區分用戶自定義變量和系統變量,需要在用戶自定義變量名稱前加@符號。例如 set @name=’John’;
-- 如果在存儲過程或者函數中用DECLARE來預先定義了某個變量,后面的set可以不用加@,詳見后文例子;
set @userTotel = (select count(*) from users); -- Set賦值用法的變量值也可是標量查詢的結果
-- select 字段 from 表名 into @變量名
select nickname from users ORDER BY user_money desc limit 1 into @richName;
SELECT id,data INTO x,y FROM test.t1 LIMIT 1; -- 這個SELECT語法把選定的列直接存儲到變量。因此,只有單一的行可以被取回
-- select @變量名:=變量值 與 select @變量名=變量值
set @who = 'zhouzhou';
select @who='小李'; -- 注意:此處不是賦值操作,而是變量的判斷,如果@who變量是已經存在了,那就判斷@who中的值是否等于'小李',返回0或1;如果@who的變量不存在就返回NULL;
```
**注意**
+ 變量的有效期為會話結束后,變量就失效(即斷開連接后,變量失效)!
+ 變量的作用域: 用戶定義的變量是全局的。但在函數內定義的變量則是局部的。
+ 變量的數據類型與字段的數據類型一致!
### 輸出變量
```sql
select @who;
```
## 分支語句
### **IF**
**語法**
```sql
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF;
```
> statement_list: 多條語句由``;``號隔開
**實例**
```sql
delimiter $
CREATE PROCEDURE `hd`(IN `arg` TINYINT)
BEGIN
DECLARE `age` TINYINT DEFAULT 0;
SET `age` = `arg`;
IF `age`<20 THEN
SELECT "年輕人";
ELSEIF `age`<40 THEN
SELECT "青年人";
ELSELF
SELECT "OLD MAN";
END IF;
END$
```
```sql
create procedure proc_getGrade
(stu_no varchar(20),cour_no varchar(10))
begin
declare stu_grade float;
select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no;
if stu_grade>=90 then
select stu_grade,'a';
elseif stu_grade<90 and stu_grade>=80 then
select stu_grade,'b';
elseif stu_grade<80 and stu_grade>=70 then
select stu_grade,'c';
elseif stu_grade<70 and stu_grade>=60 then
select stu_grade,'d';
else
select stu_grade,'e';
end if;
end
```
### 三元表達式
```sql
SELECT IF(@a=1,'真','失敗');
```
### IFNULL(字段,值)
```sql
select age,ifnull(age,"空") from c; -- 如果age的值為null就返回空;
```
### NULLIF(expr1,expr2)
```sql
-- 如果表達式1=表達式2,則返回null,否則返回第1個表達式
SELECT NULLIF(5,5); -- null
SELECT NULLIF(10,4); -- 10
```
### case
> case有兩種語法,下面這種語法的case_value必須是整數值
```sql
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
或者
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
```
**Example1**
```sql
delimiter $
CREATE PROCEDURE `pro2`(INOUT `arg` INT)
BEGIN
DECLARE `i` INT DEFAULT 0;
SET `i` = `arg`;
CASE `i`
WHEN 1 THEN
SELECT "sina";
WHEN 2 THEN
SELECT "baidu";
ELSE
SELECT "163";
END CASE;
END;
$
delimiter ;
```
**Example2**
```sql
delimiter $
CREATE PROCEDURE `pro3`(INOUT `arg` INT)
BEGIN
DECLARE `i` INT DEFAULT 0;
SET `i` = `arg`;
CASE
WHEN i = 1 THEN
SELECT "sina";
WHEN i = 2 THEN
SELECT "baidu";
ELSE
SELECT "163";
END CASE;
END;
$
delimiter ;
```
## 循環
### leave
退出循環
```sql
LEAVE label -- 退出循環,注意如果要使用leave的話,循環就必須得帶上leave;
```
### while
```sql
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
```
**Example1**
```sql
delimiter $
CREATE PROCEDURE `createstu`(IN `num` INT)
BEGIN
DECLARE `i` INT DEFAULT 0;
DECLARE `yeard` DATE;
WHILE `num`>0 DO
SET `yeard` = DATE_SUB("2000-1-1",INTERVAL `i` DAY);
INSERT INTO `test` (`sname`,`birthday`) VALUES(MD5(`i`),`yeard`);
SET `i`=`i`+1;
SET `num`=`num`-1;
END WHILE;
END$
```
### loop
```sql
[begin_label:] LOOP
statement_list
END LOOP [end_label]
```
**Example1**
```sql
delimiter $
create procedure t_loop()
begin
declare i int;
set i = 0;
loop_label:loop
insert into test(sname,birthday) values(md5(i),2005);
set i = i + 1;
if i > 100 then
leave loop_label; -- 注意這里的label是必須的哦;
end if;
end loop;
end$
delimiter ;
```
> loop是在執行后檢查結果,while是在執行前檢查結果
### repeat
```sql
[begin_label:] REPEAT
statement_list
UNTIL search_condition -- REPEAT語句內的語句或語句群被重復,直至search_condition 為真。
END REPEAT [end_label]
```
**Example1**
```sql
delimiter $
create procedure t_repeat()
begin
declare i int;
set i = 100;
repeat
insert into test(sname,birthday) values(md5(i),1988);
set i = i + 5;
until i > 10000 -- 注意until此處沒有分號,是為和下面的end鏈接一起的;
end repeat;
end;$
```
## 其他
### INSERT INTO SELECT
**語法**
```sql
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
```
> 要求目標表Table2**必須存在**,如果目標table2已經存在了,并且和table1的結構一樣的話,可以直接 ``Insert into Table2 select * from Table1``,如果結構不一樣,就要Table2的字段對應Table1的字段
### SELECT INTO FROM
**語法**
```sql
SELECT vale1, value2 into Table2 from Table1
```
> 1. 要求目標表Table2**不存在**,因為在插入時會自動創建表Table2,并將Table1中指定字段數據復制到Table2中。
> 2. **注意:** MySQL不支持Sybase SQL擴展:``SELECT ... INTO TABLE ....``。只支持``select 字段 from 表名 into @變量名``。
### replace into
```sql
replace into table (id,name) values('1','aa'),('2','bb') -- 此語句的作用是向表table中插入兩條記錄。如果主鍵id為1或2不存在就相當于insert into table (id,name) values('1','aa'),('2','bb') ,如果存在相同的值則不會插入數據
```
### create...select
```sql
-- 創建表并插入它表的數據進來;
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR (40) NOT NULL
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '用戶信息表' SELECT
brand_name
FROM
tdb_goods
GROUP BY
brand_name;
```
- 【mysql的編程專題①】流程控制與其他語法
- 【mysql的編程專題②】觸發器
- 【mysql的編程專題③】內置函數
- 【mysql的編程專題④】存儲過程
- 【mysql的編程專題⑤】自定義函數
- 【mysql的編程專題⑥】視圖
- 【mysql的設計與優化專題(1)】ER圖,數據建模與數據字典
- 【mysql的設計與優化專題(2)】數據中設計中的范式與反范式
- 【mysql的設計與優化專題(3)】字段類型與合理的選擇字段類型
- 【mysql的設計與優化專題(4)】表的垂直拆分和水平拆分
- 【mysql的設計與優化專題(5)】慢查詢詳解
- 【mysql的設計與優化專題(6)】mysql索引攻略
- 【Mysql問題集錦(1)】mysql不能使用innodb存儲引擎
- 【Mysql進階技巧(2)】利用mysql生成唯一序號
- 【Mysql進階技巧(1)】MySQL的多表關聯與自連接
- 【Mysql高可用架構(1)】基于日志點的主從復制
- 【Mysql高可用架構(2)】主從管理的系統視圖
- 【Mysql高可用架構(3)】基于GTID的主從復制
- 【Mysql高可用架構(4)】在線變更復制類型
- 【Mysql高可用架構(5)】多源復制(多主一從)
- 【Mysql高可用架構(6)】多線程復制
- 【Mysql高可用架構(7)】在線設置復制過濾
- 【Mysql高可用架構(8)】解決主從不一致
- 【Mysql高可用架構(9)】初識mycat以及制作mycat鏡像
- 【Mysql高可用架構(10)】mycat配置mysql讀寫分離
- MyCat 集群部署(HAProxy + MyCat)
- 常用復雜sql語句整理