MySQL資料庫技術

十三、變數

1.分類

系統變數：全域變數和會話變數

自定義變數：用戶變數和區域變數

2.系統變數

由MySQL系統給我們提供的，不是用戶定義的，屬于服務器層面的應用

-- 全域變數
-- 查看所有的全域變數
SHOW GLOBAL VARIABLES;

-- 查看部分全域變數
SHOW GLOBAL VARIABLES LIKE '%char%';

-- 查看指定的全域變數
SELECT @@global.autocommit;

-- 為某個指定的全域變數賦值
SET @@global.autocommit=0;

-- 全域變數的作用域
-- 針對所有的會話(連接)，只有服務器重新啟動將會把所有的全域變數賦默認值，如果想跨啟動，只有修改MYSQL的組態檔，不建議修改


--會話變數
-- 作用域只針對當前會話(連接)是有效的，與全域變數的區別

-- 查看所有的會話變數
SHOW SESSION VARIABLES;-- session 可以省略，默認會話變數
-- 查看部分會話變數
SHOW SESSION VARIABLES LIKE '%char%';
-- 查看指定的會話變數
SELECT @@session.autocommit;
SELECT @@autocommit; 
-- 為某個指定的會話變數賦值
SET @@session.autocommit=0;

3.自定義變數

由用戶定義的變數，不是mysql系統提供的

先宣告，再賦值，后使用

-- 語法：
set @用戶變數名 = 值;
set @用戶變數名 := 值;
select @用戶變數名 := 值;
-- 宣告并初始化用戶變數
SET @name = '張三';
-- 賦值
-- 方式一：SET @用戶變數名 = 值;
SET @name='李四';
-- 方式二: select 欄位 into @name from 表
SELECT COUNT(*) INTO @name FROM student;
-- 使用用戶變數 -查看
SELECT @name;

-- 區域變數
-- 作用域：僅僅在定義它的begin end中有效，應用在begin end中,而且必須放在第一句

-- 宣告
declare 變數名 型別;
declare 變數名 型別 default 值:;
-- 賦值
-- 方式一：SET 區域變數名 = 值;
-- 方式二: select 欄位 into 區域變數名 from 表
-- 查看
SELECT 區域變數名;

十四、存盤程序和函式

類似于在java中學到的方法

1.存盤程序和函式的好處

• 提高代碼的重用性
• 簡化了sql操作
• 減少了編譯次數，提高開發效率[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-YzApFMkd-1608454319982)(C:\Users\24582\AppData\Roaming\Typora\typora-user-images\image-20201220143015161.png)]

2.存盤程序

一組預先編譯好的sql陳述句的集合，理解錯批處理陳述句

創建語法

create procedure 存盤程序名(引數串列)
begin
	存盤程序體(一組合法的sql陳述句)
end;

-- 注意：引數串列包含了三部分： 引數模式，引數名，引數型別
-- 引數模式： in/out/inout sname varchar(20);
-- in:輸入值，呼叫傳入的值
-- out:輸出引數，可以最為回傳值來用
-- inout:既需要傳入值，又可以回傳值

delimiter 設定結束識別符號
delimiter $

呼叫語法

call 存盤程序名(實參串列)

實際操作

撰寫一個存盤程序，該程序可以向表中添加記錄

-- 創建測驗表
create table mytest(
	id int primary key auto_increment,
    sname varchar(50),
    spwd varchar(50),
)
-- 創建存盤程序
DELIMITER $
create procedure myp1()
begin
	insert into mytest(sname,spwd) values('admin','123456'),('趙四','123789');
end;$

-- 呼叫存盤程序
call myp1();

-- 洗掉存盤程序
drop procedure myp1;

創建帶有in模式引數的存盤程序

1.撰寫一個存盤程序，需要輸入學生名，查詢該學生對應的班級名稱

DELIMITER $
create procedure myp2(in myname varchar(30)) -- in可以不寫，默認
begin
	select c.cname,s.sname
	from student s join class c on s.cno=c.cno and s.sname = myname;
end;$
CALL myp2('tom');

2.創建存盤程序，實作用戶登錄功能(多個引數，用戶名，密碼)

DELIMITER $
create procedure myp3(in mname varchar(30),in mpass varchar(30))
begin
	-- 宣告區域變數，初始化
	declare result varchar(20) default '';
	select count(*) into result
	from mytest m where m.sname=mname and m.spwd = mpass
	select result;-- 登錄成功回傳1，失敗回傳0
end;$
CALL myp3('admin','123456');

創建帶有out模式引數的存盤程序

1.通過學生編號，回傳對應的班級名稱；(輸入引數，一個回傳值)

DELIMITER $
CREATE PROCEDURE myp4(IN myid VARCHAR(20),OUT mclassname VARCHAR(20))
BEGIN
	SELECT c.cname INTO mclassname
	FROM student s,class c WHERE s.cno=c.cno AND s.sno=myid;
END;$
CALL myp4('1805',@cname) -- 用自定義變數接收
SELECT @cname;-- 查詢自定義變數

2.通過學生編號，回傳對應的班級名稱,學生姓名；(輸入引數，二個回傳值)

DELIMITER $
CREATE PROCEDURE myp5(IN myid VARCHAR(20),OUT mclassname VARCHAR(20),msname varchar(20))
BEGIN
	SELECT c.cname,s.sname INTO mclassname,msname
	FROM student s,class c WHERE s.cno=c.cno AND s.sno=myid;
END;$
CALL myp5('1805',@cname,@sname)
SELECT @cname,@sname;

創建帶有inout模式引數的存盤程序

-- 案例:傳入兩個數值，輸出回傳這兩個資料的翻倍值
DELIMITER $
create procedure myp6(inout num1 int,inout num2 int)
begin
	set num1 = num1 * 2;
	set num2 = num2 * 2;
end;$
-- 呼叫
SET @m=10;
SET @n=5;
CALL myp6(@m,@n)
SELECT @m,@n;

3.查看存盤程序資訊

show create procedure myp6;

-- 注意點：存盤程序沒用修改操作，要么只能重新創建方式

4.練習

-- 創建存盤程序實作傳入一個日期，格式化成xx年xx月xx日方式回傳
DELIMITER $
CREATE PROCEDURE myp7(IN mDate DATETIME,OUT sDate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mDate,'%Y年%m月%d日') INTO sDate;
END;$
-- 呼叫
CALL myp7(NOW(),@time); -- 輸入當前時間
SELECT @time;

-- 創建分頁的存盤程序，傳入起始索引和每頁顯示的大小，查詢出資料并回傳
DELIMITER $
CREATE PROCEDURE myp8(IN  startindex INT, IN size INT)
BEGIN
	SELECT * FROM student LIMIT startindex,size;
END;$
-- 呼叫
CALL myp8(0,3)
CALL myp8(3,3)

十五、函式

一組預先編譯好的sql陳述句的集合，理解錯批處理陳述句

1.與存盤程序的區別

函式和存盤程序功能可以相同，一樣的，主要看研究他們的區別：

存盤程序：可以沒有回傳值，也可以有多個回傳值，適合做批量插入、批量更新上；

函式：有且僅有一個回傳值，適合做處理資料后的回傳結果(一個)

2.創建方法

create function 函式名(引數串列) returns 回傳型別
begin
	函式體
end

-- 注意：
	--：引數串列包含兩個部分：引數名，引數型別
	-- 函式體：肯定會有return 陳述句，沒有會報錯
	
-- 呼叫語法
select 函式名(引數串列);

3.案例操作

-- 案例：回傳學生表的學生個數
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;-- 定義區域變數
	SELECT COUNT(*) INTO c FROM student;
	RETURN c;
END;$
-- 呼叫
SELECT myf1();

-- 案例：根據學生名，回傳學生成績
DELIMITER $
CREATE FUNCTION myf2(myname VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @score = 0;
	SELECT student.score INTO @score
	FROM student WHERE student.sname=myname;
	RETURN @score;
END;$
-- 呼叫
SELECT myf2('tom');

-- 案例：根據班級名稱，回傳該班級的平均成績
DELIMITER $
CREATE FUNCTION myf3(classname VARCHAR(30)) RETURNS DOUBLE
BEGIN
	SET @avgscore = 0;
	SELECT AVG(s.score) INTO @avgscore
	FROM student s,class c WHERE c.cno = s.cno AND c.cname=classname;
	RETURN @avgscore;
END;$
-- 呼叫
SELECT myf3('classtwo');

4.查看函式

show create function 函式名稱;

5.洗掉函式

drop function 函式名;
-- 和存盤程序一樣都沒有修改程序