資料庫系統（六）---MySQL語句及存儲過程

一、DDL、DML、DCL常用語句 1、DDL（Data Definition Language）資料庫定義語言（1）資料庫模式定義（2）表定義 2、DML（data manipulation language）資料庫操作語言 3、DCL（Data Control Language）資料庫控制語 ...

一、DDL、DML、DCL常用語句

1、DDL（Data Definition Language）資料庫定義語言

（1）資料庫模式定義

#創建資料庫　　
create database if exsites db_name;

#選定資料庫
use db_name;

#刪除資料庫
drop database if exists db_name;

#修改資料庫
alter database db_name set ...;

#展示所創建的資料庫
show databases;

（2）表定義　　　　

#創建表
create table test_table
(
s_id int not null auto_increment,
s_name char(50) not null default "hanmei",
s_age int not null,
primary key(s_id),
index index_name(s_name)
);

#刪除表
drop table if exists test_table; 

#展示表結構
desc test_table;

2、DML（data manipulation language）資料庫操作語言

insert into test_table(s_age) values(18);

insert into test_table set s_age=19; #插入部分列值數據

inert ...select...;

#case...when 匹配條件
select s_name as name,s_sex
case
　　when  'f' then ‘女’
　　else '男'
end as sex
from test_table;

#使用內置函數
select count(*) from customers;
select max(cust_id) from customers;
select min(cust_id) from customers;
select sum(cust_id) from customers;
select avg(cust_id) from customers;
 

#交叉連接（笛卡爾積）
select * from tb1 cross join tb2;

#內連接
#---左外連接
select * from stu_info inner join stu_score on stu_info.sno=stu_score.sno;
select stu_info.sno,stu_info.sname,stu_score.sscore from stu_info left join stu_score on stu_info.sno=stu_score.sno;
#---右外連接
select stu_info.sno,stu_info.sname,stu_score.sscore from stu_score right join stu_info on stu_score.sno=stu_info.sno;
 
#比較運算符
select * from customers where cust_id!=2;
select * from customers where cust_id<>2;

#邏輯運算符
#---and 與
select * from customers where cust_id>2 and cust_sex=1;
#---or 或
select * from customers where cust_id>2 or cust_sex=1;
 
#兩者之間 範圍
select * from customers where cust_id between 2 and 4;
select * from customers where cust_id>=2 and cust_id<=4;

#in
select * from customers where cust_id in(2,4);
select * from customers where cust_id=2 or cust_id=4;

#子查詢
select * from stu_info where sno in(select sno from stu_score);
 
#分組查詢
select ssex,count(*)from stu_info group by ssex;
select saddress,ssex,count(*) from stu_info group by saddress,ssex;
select saddress,ssex,count(*) from stu_info group by saddress,ssex with rollup;
 
#having 篩選---過濾分組後的數據
select saddress,ssex ,count(*) from stu_info group by saddress,ssex having count(*)>1;

3、DCL（Data Control Language）資料庫控制語言

安全與訪問控制

-- 查看 mysql 資料庫的使用者賬號
select user from mysql.user;

-- 密碼加密
select password(456);

-- 創建用戶
create user 'zhangsan'@'localhost' identified by '123',
'lisi'@'localhost' identified by password '*531E182E2F72080AB0740FE2F2D68
9DBE0146E04';

-- 刪除用戶賬號
drop user lisi@localhost;

-- 重命名
rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';

-- 修改密碼
set password for 'wangwu'@'localhost'='*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119';

-- 設置許可權
grant select n test1.customers o 'wangwu'@'localhost';

-- 創建兩個用戶
grant select,update on test1.customers to 'liming'@'localhost' identified by '123', 'huang'@'localhost' identified by '789';

--執行所有資料庫操作的許可權
grant all on test1.* to 'wangwu'@'localhost';

-- 添加用戶的許可權
grant create user on *.*to 'wangwu'@'localhost';

-- 許可權轉移
grant select,update on test1.customers to 'zhou'@'localhost' identified by '123' with grant option;

-- 許可權撤回
revoke select on test1.customers from 'zhou'@'localhost';

二、存儲過程 1、存儲過程是一組為了完成某項特定功能的 SQL 語句集，其實質上就是一段存儲在資料庫中的代碼，它可以由聲明式的 SQL 語句（如 CREATE、UPDATE 和SELECT 等語句）和過程式 SQL 語句（如 IF…THEN…ELSE 控制結構語句）組成。創建的存儲過程保存在資料庫的數據字典中。　　使用存儲過程通常具有以下優勢：　　1）可增強 SQL 語言的功能和靈活性；　　2）良好的封裝性；　　3）高性能；　　4）可減少網路流量；　　5）存儲過程可作為一種安全機制來確保資料庫的安全性和數據的完整性。 2、創建存儲過程　 2.1）DELIMITER 命令　　 DELIMITER 命令將 MySQL 語句的結束標誌臨時修改為其他符號，從而使得 MySQL 伺服器可以完整地處理存儲過程體中所有的 SQL 語句，而後可通過 DELIMITER 命令再將 MySQL 語句的結束標誌改回為 MySQL 的預設結束標誌，即分號（；）。　　 DELIMITER 命令的語法格式： DELIMITER $$ 　 2.2）創建存儲過程　　在 MySQL 中，是使用 CREATE PROCEDURE 語句來創建存儲過程，其常用的語法格式是： CREATE PROCEDURE sp_name（[proc_parameter[，…]]） Routine_body
　　　 "sp_name" 用於指定存儲過程的名稱，且預設在當前資料庫中創建；　　　 "proc_parameter" 用於指定存儲過程的參數列表；　　　 "routine_body" 表示存儲過程的主體部分，也稱為存儲過程體。　其中，語法項“proc_parameter”的語法格式是： [IN|OUT|INOUT] param_name type

　　　　　　IN 輸入參數：表示調用者向過程傳入值（傳入值可以是字面量或變數）；
　　　　　　OUT 輸出參數：表示過程向調用者傳出值(可以返回多個值)（傳出值只能是變數）；
　　　　　　INOUT 輸入輸出參數：既表示調用者向過程傳入值，又表示過程向調用者傳出值（值只能是變數）；

　 2.3）存儲過程體　　　局部變數：在存儲過程體中可以聲明局部變數，用來存儲存儲過程體中的臨時結果。在 MySQL 中，可以使用 DECLARE 語句來聲明局部變數，並且同時還可以對該局部變數賦予一個初始值，其使用的語法格式是： DECLARE var_name[，…] type [DEFAULT value] 其中，　　 1）"var_name" 用於指定局部變數的名稱；　　 2）"type" 用於聲明局部變數的數據類型；　　 3） DEFAULT 子句用於為局部變數指定一個預設值，若沒有指定，則預設為 NULL。需要註意的事項如下：　　　　 1）局部變數只能在存儲過程體的 BEGIN…END 語句塊中聲明。　　　　 2）局部變數必須在存儲過程體的開頭處聲明。　　　　 3）局部變數的作用範圍僅限於聲明它的 BEGIN…END 語句塊，其他語句塊中的語句不可以使用它。　　　　 4）局部變數不同於用戶變數，兩者的區別是：　　　　局部變數聲明時，在其前面沒有使用@符號，只能被聲明它的 BEGIN…END 語句塊中的語句所使用；　　　　　　用戶變數在聲明時，會在其名稱前面使用@符號，同時已聲明的用戶變數存在於整個會話之中。　　 4) SET 語句在 MySQL 中，可以使用 SET 語句為局部變數賦值，語法格式是： SET var_name=expr[，var_name=expr] … 　　 5) SELECT…INTO 語句在 MySQL 中，可以使用 SELECT…INTO 語句把選定列的值直接存儲到局部變數中，語法格式是： SELECT col_name[,…] INTO var_name[,…] table_expr 其中：　　　　“col_name”用於指定列名；　　　　“var_name”用於指定要賦值的變數名；　　　　“table_expr”表示 SELECT 語句中的 FROM 子句及後面的語法部分。　　註意：存儲過程體中的 SELECT…INTO 語句返回的結果集只能有一行數據。　　 6）流程式控制制語句　　　　在 MySQL 中，可以在存儲過程體中，使用條件判斷語句和迴圈語句這樣兩類用於控制語句流程的過程式 SQL 語句。　　　　（1）條件判斷語句常用的條件判斷語句有 IF…THEN…ELSE 語句和 CASE 語句；　　　　（2）迴圈語句常用的迴圈語句有 WHILE 語句、REPEAR 語句和 LOOP 語句。此外，迴圈語句中還可以使用 ITERATE 語句，但它只能出現在迴圈語句的 LOOP、REPEAT 和 WHILE 子句中，用於表示退出當前迴圈，且重新開始一個迴圈。 eg：　　 select...into...

mysql> delimiter $$
mysql> CREATE PROCEDURE proc_add_stu(
　　 -> IN sNo INTEGER,
　　 -> OUT sid int
　　 -> )

mysql> BEGIN #存儲過程開始
　　 -> insert into student(s_no) values(sNo);
　　 -> SELECT LAST_INSERT_ID() into sid;  #將選定列的值直接存儲到局部變數中
　　 -> END $$ #存儲過程結束

mysql> delimiter;　　#將語句的結束符號恢復為分號

mysql> call pro_add_stu('0001');

in輸入參數（預設，可省略不寫）

mysql> delimiter $$
mysql> create procedure in_proce(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=0; #局部變數賦值（begin...和end之間）
    ->    select P_in;
    -> end$$
mysql> delimiter ;
 
mysql> set @p_in=1; #全局變數@p_in賦值
 
mysql> call in_param(@p_in); #將全局變數@p_in的值作為參數傳遞給局部變數p_in
+------+
| p_in |
+------+
|    1 |
+------+
 
+------+
| P_in |
+------+
|    0 |
+------+
 
mysql> select @p_in; #輸出全局變數@p_in的結果
+-------+
| @p_in |
+-------+
|     1 |
+-------+

以上可以看出，p_in 在存儲過程中被修改，但並不影響 @p_id 的值，因為前者為局部變數、後者為全局變數。

out輸出參數

mysql> delimiter //
mysql> create procedure out_proce(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_proce(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#因為out是向調用者輸出參數，不接收輸入的參數，所以存儲過程里的p_out為null

+-------+
| p_out |
+-------+
|     2 |
+-------+
 
mysql> select @p_out; #輸出全局變數（用戶變數）結果
+--------+
| @p_out |
+--------+
|      2 |
+--------+
#調用了out_proce存儲過程，輸出參數，改變了p_out變數的值

inout輸入參數(儘量少用)

mysql> delimiter $$
mysql> create procedure inout_proce(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_proce(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#調用了inout_param存儲過程，接受了輸入的參數，也輸出參數，改變了變數

變數作用域

　　內部的變數在其作用域範圍內享有更高的優先權，當執行到 end。變數時，內部變數消失，此時已經在其作用域外，變數不再可見了，應為在存儲過程外再也不能找到這個申明的變數，但是你可以通過 out 參數或者將其值指派給會話變數來保存其值。

mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default 'outer';  
     -> begin 
     -> declare x1 varchar(5) default 'inner';  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //
mysql > DELIMITER ;

條件語句

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

迴圈語句

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

　2.5）刪除存儲過程　　　　DROP PROCEDURE[IF EXISTS] sp_name 其中，“sp_name”用於指定要刪除的存儲過程的名稱。 2.4）游標　　游標是一個被 SELECT 語句檢索出來的結果集。即，游標可以遍歷返回的多行結果。　　在 MySQL 中，使用游標的具體步驟如下：　　（1）聲明游標 DECLARE cursor_name CURSOR FOR select_statement 其中，　　　　“cursor_name”用於指定要創建的游標的名稱，其命名規則與表名相同；　　　　“select_statement”用於指定一個 SELECT 語句，其會返回一行或多行的數據，且需註意此處的 SELECT 語句不能有 INTO 子句。　　（2）打開游標 OPEN cursor_name 其中，“cursor_name”用於指定要打開的游標。　　（3）讀取數據 FETCH cursor_name INTO var_name[,var_name]… 其中，“cursor_name”用於指定已打開的游標；語法項“var_name”用於指定存放數據的變數名。　　（4）關閉游標 CLOSE cursor_name 其中，語法項“cursor_name”用於要關閉的游標。（5）在使用游標過程中，需要註意以下幾點：　　　　1）游標只能用於存儲過程或存儲函數中，不能單獨在查詢操作中使用。　　　　2）在存儲過程或存儲函數中可以定義多個游標，但是在一個 BEGIN…END 語句塊中每一個游標的名字必須是唯一的。　　　　3）游標不是一條 SELECT 語句，是被 SELECT 語句檢索出來的結果集。模塊四調用存儲過程 CALL sp_name[parameter[,…]] CALL sp_name[()] 在此語法格式中：　　　　　　語法項“sp_name”用於指定被調用的存儲過程的名稱。如果要調用某個特定資料庫的存儲過程，則需要在前面加上該資料庫的名稱。　　　　　　語法項“parameter”用於指定調用存儲過程所要使用的參數。調用語句中參數的個數必須等於存儲過程的參數個數。　　　　當調用沒有參數的存儲過程時，使用 CALL sp_name()語句與使用 CALL sp_name 語句是相同的。 eg:

create procedure p1()
begin
    declare id int;
    declare name varchar(15);
    -- 聲明游標
    declare mc cursor for select * from class;
    -- 打開游標
    open mc;
    -- 獲取結果
    fetch mc into id,name;
    -- 這裡是為了顯示獲取結果
    select id,name;
    -- 關閉游標
    close mc;
    
end;

三、存儲函數 1、存儲函數和存儲過程的區別：　　（1）存儲函數不能擁有輸出參數，這是因為存儲函數自身就是輸出參數；而存儲過程可以擁有輸出參數。　　（2）可以直接對存儲函數進行調用，且不需要使用 CALL 語句；而對存儲過程的調用，需要使用 CALL 語句。　　（3）存儲函數中必須包含一條 RETURN 語句，而這條特殊的 SQL 語句不允許包含於存儲過程中。 2、創建存儲函數 CREATE FUNCTION sp_name([func_parameter[，…]]) RETURNS type routine_body 其中，語法項“func_parameter”的語法格式是： param_name type 　　　　在此語法格式中：　　　　（1）語法項“sp_name”用於指定存儲函數的名稱，需註意，存儲函數不能與存儲過程具有相同的名字。　　　　（2）語法項“func_parameter”用於指定存儲函數的參數，這裡的參數只有名稱和類型，不能指定關鍵字“IN”“OUT”和“INOUT”。　　　　（3） RETURNS 子句用於聲明存儲函數返回值的數據類型，其中 type 用於指定返回值的數據類型。　　　　（4）語法項“routine_body”用於指定存儲函數的主體部分，也稱為存儲函數體。所有在存儲過程中使用的 SQL 語句在存儲函數中同樣也適用，包括前面所介紹的局部變數、SET 語句，流程式控制制語句、游標等。但是，存儲函數體中還必須包含一個 RETURN value 語句，其中 value 用於指定存儲函數的返回值。 eg:

#刪除已經存在的存儲函數
DROP FUNCTION IF EXISTS func_stu;

#創建存儲函數(聲明返回類型為varChar(50))
CREATE FUNCTION func_stu(in_id INT) RETURNS VARCHAR(50)
BEGIN
    DECLARE o_name VARCHAR(50); #聲明局部變數
    SELECT name INTO o_name FROM tb_stu WHERE id = in_id; #tb_stu指事先創建好的資料庫
    RETURN o_name;
END;

3、調用存儲函數　　成功創建存儲函數後，就可以如同調用系統內置函數一樣，使用關鍵字 SELECT 對其進行調用，語法格式是： SELECT sp_name([func_parameter[，…]]) eg:

SELECT func_stu(1);

4、刪除存儲函數　　在 MySQL 中，可以使用 DROP FUNCTION 語句來實現，語法格式： DROP FUNCTION[IF EXISTS] sp_name 其中，語法項“sp_name”指定要刪除的存儲函數的名稱。 eg:

DROP FUNCTION IF EXISTS func_stu;