瞭解使用mysql 的檢視、儲存過程、觸發器、函式....

mysql 客戶端 5.7 sqlyog

一、檢視 (簡化)

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE USER='root';  --查詢當前使用者是否具有建立檢視的許可權


CREATE VIEW mpView AS SELECT * FROM t_city;    ---建立檢視
 
SELECT * FROM mpView;   --使用檢視

二、儲存過程

1、無引數()

DELIMITER $$
CREATE PROCEDURE proc_search_user()；

BEGIN
	SELECT * FROM t_city;
END$$
 
-- 將結束標誌符更改回分號
DELIMITER ;

CALL proc_search_user(); ------呼叫儲存過程proc_search_user()

2、建立變數 declare [變數名] [型別] …

DELIMITER $$
CREATE PROCEDURE test02()
BEGIN
	DECLARE counts INT DEFAULT 0;
	DECLARE counts01 INT DEFAULT 0;
	SELECT COUNT(*) INTO counts FROM t_city;
	SELECT COUNT(*) INTO counts01 FROM t_city;
	SELECT counts,counts01;
   
END$$  
DELIMITER ;

CALL test02();

3、有引數(in \ out \ inout) — 預設in

DELIMITER $$

CREATE PROCEDURE test03(uid INT(10))
BEGIN
	DECLARE counts VARCHAR(100) CHARACTER SET utf8 DEFAULT '' ;    ------改變字符集utf8，否則有錯誤
	SELECT NAME INTO counts FROM t_student WHERE id = uid ;
	SELECT counts;
   
END$$  

DELIMITER ;
###注意中文問題,使用CHARACTER SET utf8 改為uft-8編碼
CALL test03(1);

IN OUT INOUT 預設in

DELIMITER $$
CREATE PROCEDURE test04(uid INT , OUT username VARCHAR(30)  CHARACTER SET utf8 )
BEGIN 
	SELECT NAME INTO username FROM t_student WHERE id = uid;
END $$

DELIMITER ;

呼叫有參儲存過程

SET @uname = '';
CALL test04(1,@uname);
SELECT @uname AS username;