變量

分類：

系統變量：全局變量（global variable）會話變量（session variable）

自定義變量：使用者變量局部變量

一、系統變量

說明：變量由系統定義，不是使用者定義，屬于伺服器層面

注意：全局變量需要添加global關鍵字，會話變量需要添加session關鍵字，如果不寫，預設會話級别

使用步驟：

1、檢視所有系統變量
show global|【session】variables;
2、檢視滿足條件的部分系統變量
show global|【session】 variables like '%char%';
3、檢視指定的系統變量的值
select @@global|【session】系統變量名;
4、為某個系統變量指派
方式一：
set global|【session】系統變量名=值;
方式二：
set @@global|【session】系統變量名=值;

1.全局變量

作用域：針對于所有會話（連接配接）有效，但不能跨重新開機

①檢視所有全局變量

②檢視滿足條件的部分系統變量

③檢視指定的系統變量的值

④為某個系統變量指派

SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

2.會話變量

作用域：針對于目前會話（連接配接）有效

①檢視所有會話變量

②檢視滿足條件的部分會話變量

③檢視指定的會話變量的值

SELECT @@autocommit;
SELECT @@session.transaction_isolation;

④為某個會話變量指派

SET @@session.transaction_isolation='read-uncommitted';
SET SESSION transaction_isolation='read-committed';

二、自定義變量

說明：變量由使用者自定義，而不是系統提供的

使用步驟：

1、聲明

2、指派

3、使用（檢視、比較、運算等）

1.使用者變量

作用域：針對于目前會話（連接配接）有效，作用域同于會話變量

指派操作符：=或:=

①聲明并初始化

SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;

②指派（更新變量的值）
方式一：
	SET @變量名=值;
	SET @變量名:=值;
	SELECT @變量名:=值;
方式二：
	SELECT 字段 INTO @變量名
	FROM 表;
③使用（檢視變量的值）
SELECT @變量名;

2.局部變量

作用域：僅僅在定義它的begin end塊中有效

應用在 begin end中的第一句話

①聲明

DECLARE 變量名 類型;
DECLARE 變量名 類型 【DEFAULT 值】;

②指派（更新變量的值）

方式一：

SET 局部變量名=值;
SET 局部變量名:=值;
SELECT 局部變量名:=值;

方式二：

SELECT 字段 INTO 具備變量名
FROM 表;

③使用（檢視變量的值）

案例：聲明兩個變量，求和并列印

使用者變量

SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

局部變量

DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

注意

局部變量隻能作用于begin end中也就是函數和存儲過程中是以不能直接定義在外部否則會報錯

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DECLARE i INT DEFAULT 2’ at line 1

使用者變量和局部變量的對比

作用域	文法	定義位置
使用者變量	目前會話	加@符号，不用指定類型	會話的任何地方
局部變量	begin end中	一般不用加@,需要指定類型	BEGIN END的第一句話

存儲過程

概念：

事先經過編譯并存儲在資料庫中的一段sql語句的集合。

優點：

提高代碼的重用性
簡化操作
減少了編譯次數并且減少了和資料庫伺服器的連接配接次數，提高了效率

一、建立文法

CREATE PROCEDURE 存儲過程名(參數清單)
BEGIN
	存儲過程體（一組合法的SQL語句）
END

1、參數清單包含三部分

參數模式參數名參數類型

舉例：

參數模式：

in：該參數可以作為輸入，也就是該參數需要調用方傳入值

out：該參數可以作為輸出，也就是該參數可以作為傳回值

inout：該參數既可以作為輸入又可以作為輸出，也就是該參數既需要傳入值，又可以傳回值

2、如果存儲過程體僅僅隻有一句話，begin end可以省略存儲過程體中的每條sql語句的結尾要求必須加分号。存儲過程的結尾可以使用 delimiter 重新設定。

3、delimiter 詳解

delimiter作用其實就是為了告訴mysql解釋器，該段指令是否已經結束了，mysql是否可以執行了。預設情況下，delimiter是分号;。在指令行用戶端中，如果有一行指令以分号結束，那麼回車後，mysql将會執行該指令。

看下面一個例子：

DELIMITER $$ 
DROP TRIGGER IF EXISTS `updateegopriceondelete`$$ 
CREATE 
    TRIGGER `updateegopriceondelete` AFTER  DELETE ON  `customerinfo` 
    FOR EACH ROW BEGIN 
DELETE FROM egoprice  WHERE customerId=OLD.customerId; 
    END$$ 
DELIMITER ;

其中DELIMITER 定好結束符為"$$", 然後最後又定義為";", MYSQL的預設結束符為";".

詳細解釋:

其實就是告訴mysql解釋器，該段指令是否已經結束了，mysql是否可以執行了。預設情況下，delimiter是分号;。在指令行用戶端中，如果有一行指令以分号結束，那麼回車後，mysql将會執行該指令。如輸入下面的語句

然後回車，那麼MySQL将立即執行該語句。

但有時候，不希望MySQL這麼做。在為可能輸入較多的語句，且語句中包含有分号。

如試圖在指令行用戶端中輸入如下語句

mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) 
mysql>     RETURNS varchar(255) 
mysql> BEGIN 
mysql> IF ISNULL(S) THEN 
mysql>     RETURN ''; 
mysql> ELSEIF N<15 THEN 
mysql>     RETURN LEFT(S, N); 
mysql> ELSE 
mysql>     IF CHAR_LENGTH(S) <=N THEN 
mysql>    RETURN S; 
mysql>     ELSE 
mysql>    RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); 
mysql>     END IF; 
mysql> END IF; 
mysql> END;

預設情況下，不可能等到使用者把這些語句全部輸入完之後，再執行整段句。因為 mysql一遇到分号，它就要自動執行。即，在語句RETURN ‘’;時，mysql解釋器就要執行了。

這種情況下，就需要事先把delimiter換成其它符号，如//或$$

mysql> delimiter // 
mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) 
mysql>     RETURNS varchar(255) 
mysql> BEGIN 
mysql> IF ISNULL(S) THEN 
mysql>     RETURN ''; 
mysql> ELSEIF N<15 THEN 
mysql>     RETURN LEFT(S, N); 
mysql> ELSE 
mysql>     IF CHAR_LENGTH(S) <=N THEN 
mysql>    RETURN S; 
mysql>     ELSE 
mysql>    RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); 
mysql>     END IF; 
mysql> END IF; 
mysql> END;//

這樣隻有當//出現之後，mysql解釋器才會執行這段語句 .

MySQL delimiter 符号是可以自由設定的，但是MySQL存儲過程中比較常見的用法是“//”和“ $$”。

二、調用文法

三、案例示範

對下表進行示範

MySQL學習第四天函數和存儲過程變量函數流程控制結構

1.空參清單

#插入到t_account表中四條記錄
DELIMITER $$
CREATE PROCEDURE pro1()
BEGIN
  INSERT INTO t_account VALUES(NULL,'關羽',800),(NULL,'張飛',800),(NULL,'諸葛亮',900),(NULL,'趙雲',800);
END $$;

#調用
CALL pro1()$$;

delimiter ;
SELECT * from t_account;

MySQL學習第四天函數和存儲過程變量函數流程控制結構

2.建立帶in模式參數的存儲過程

#查詢所有魏國英雄的資訊
DELIMITER $$
CREATE PROCEDURE pro(IN cname VARCHAR(10))
BEGIN
SELECT a.* FROM t_account AS a INNER JOIN t_country AS c
ON a.c_id=c.c_id
WHERE c.c_name=cname; 
END $$;

CALL pro('魏');

MySQL學習第四天函數和存儲過程變量函數流程控制結構

3.建立out 模式參數的存儲過程

DELIMITER $$
CREATE PROCEDURE pro8(IN cname VARCHAR(10),OUT hero VARCHAR(10))
BEGIN
SELECT a_name FROM t_account AS a INNER JOIN t_country AS c
ON a.c_id=c.c_id
WHERE c.c_name=cname; 
END $$;

CALL pro8('吳',@name);
SELECT @name;

MySQL學習第四天函數和存儲過程變量函數流程控制結構

4.建立帶inout模式參數的存儲過程

#傳入a和b兩個值，最終a和b都翻倍并傳回

CREATE PROCEDURE pro9(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#調用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

删除存儲過程

文法：

drop procedure 存儲過程名

檢視存儲過程的資訊

顯示存儲過程建立的具體過程資訊

MySQL學習第四天函數和存儲過程變量函數流程控制結構

函數

含義：

一組預先編譯好的SQL語句的集合，了解成批處理語句

優點：

提高代碼的重用性
簡化操作
減少了編譯次數并且減少了和資料庫伺服器的連接配接次數，提高了效率

存儲過程與函數的差別：

存儲過程：可以有0個傳回，也可以有多個傳回，适合做批量插入、批量更新

函數：有且僅有1 個傳回，适合做處理資料後傳回一個結果

一、建立文法

CREATE FUNCTION 函數名(參數清單) RETURNS 傳回類型
BEGIN
	函數體
END

注意：

1.參數清單包含兩部分：參數名參數類型

2.函數體：肯定會有return語句，如果沒有會報錯

如果return語句沒有放在函數體的最後也不報錯，但不建議

return 值;

3.函數體中僅有一句話，則可以省略begin end

4.使用 delimiter語句設定結束标記

二、調用文法

三、------------------------------案例示範----------------------------

1.無參有傳回

#檢視有多少個蜀國英雄
 CREATE FUNCTION fun10() RETURNS INT
 BEGIN
 DECLARE c INT DEFAULT 0;#定義局部變量
 SELECT COUNT(*)  INTO c#指派
 FROM t_account a INNER JOIN t_country c
 ON a.c_id=c.c_id
 WHERE c.c_name='蜀';
 RETURN c;
 END $$;
 
 SELECT fun10();

MySQL學習第四天函數和存儲過程變量函數流程控制結構

2.有參有傳回

#根據英雄名傳回他的武力值
 CREATE FUNCTION fun(hero VARCHAR(11)) RETURNS DECIMAL
 BEGIN
 SET @sal=0;#定義使用者變量 
 SELECT a_balance INTO @sal
 FROM t_account
 WHERE a_name=hero;
 RETURN @sal;
 END $$;
 
 SELECT fun('趙雲');

MySQL學習第四天函數和存儲過程變量函數流程控制結構

#建立函數，實作傳入兩個float，傳回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$

四、檢視函數

MySQL學習第四天函數和存儲過程變量函數流程控制結構

五、删除函數

MySQL學習第四天函數和存儲過程變量函數流程控制結構

流程控制結構

一、分支結構

1.if函數

文法：

if(條件,值1，值2)

功能：實作雙分支類似三元運算符

應用在begin end中或外面

2.case結構

文法：

情況1：類似于switch

case 變量或表達式
when 值1 then 語句1;
when 值2 then 語句2;
...
else 語句n;
end

情況2：

case 
when 條件1 then 語句1;
when 條件2 then 語句2;
...
else 語句n;
end

應用在begin end 中或外面

3.if結構

文法：

if 條件1 then 語句1;
elseif 條件2 then 語句2;
....
else 語句n;
end if;

功能：類似于多重if

隻能應用在begin end 中

案例1：建立函數，實作傳入成績，如果成績>90,傳回A，如果成績>80,傳回B，如果成績>60,傳回C，否則傳回D

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
	
	
END $

SELECT test_if(87)$

案例2：建立存儲過程，如果工資<2000,則删除，如果5000>工資>2000,則漲工資1000，否則漲工資500

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees 
	WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees
    SET salary=salary+1000
	 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 
	WHERE employees.`salary`=sal;
	END IF;
	
END $

CALL test_if_pro(2100)$

案例3：建立函數，實作傳入成績，如果成績>90,傳回A，如果成績>80,傳回B，如果成績>60,傳回C，否則傳回D

CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$

二、循環結構

分類：

while、loop、repeat

循環控制：

iterate類似于 continue，繼續，結束本次循環，繼續下一次

leave 類似于 break，跳出，結束目前所在的循環

1.while

文法：

【标簽:】while 循環條件 do
	循環體;
end while【 标簽】;

聯想：

while(循環條件){

	循環體;
}

2.loop

文法：

【标簽:】loop
	循環體;
end loop 【标簽】;

可以用來模拟簡單的死循環

3.repeat

文法：

【标簽：】repeat
	循環體;
until 結束循環的條件
end repeat 【标簽】;

三者對比

MySQL學習第四天函數和存儲過程變量函數流程控制結構

示例：

1.沒有添加循環控制語句

#案例：批量插入，根據次數插入到admin表中多條記錄
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
	
END $

CALL pro_while1(100)$

2.添加leave語句（相當于break）

#案例：批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $
CALL test_while1(100)$

3.添加iterate語句（相當于continue）

#案例：批量插入，根據次數插入到admin表中多條記錄，隻插入偶數次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`) 
		      VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $


CALL test_while1(100)$

經典案例：

已知表stringcontent

其中字段：

id 自增長

content varchar(20)

向該表插入指定個數的，随機的字元串

DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)	
);

DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT;#代表初始索引
	DECLARE len INT;#代表截取的字元長度
	WHILE i<=insertcount DO
		SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随機範圍1-26
		SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取長度，随機範圍1-（20-startIndex+1）
		INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i=i+1;
	END WHILE;

END $

CALL test_randstr_insert(10)$

MySQL學習第四天函數和存儲過程變量函數流程控制結構

變量

存儲過程

函數

流程控制結構

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述