資料庫系統（六）---MySQL陳述句及存盤程序

一、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';

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

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'); 

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 的值，因為前者為區域變數、后者為全域變數，

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變數的值

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 ;

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;

#洗掉已經存在的存盤函式
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;

SELECT func_stu(1);

DROP FUNCTION IF EXISTS func_stu;

    5、修改存盤函式

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

感謝閱讀，如需轉載，請注明出處，謝謝！https://www.cnblogs.com/huyangshu-fs/p/11669708.html 

標籤：MySQL

上一篇：資料庫系統（五）---MySQL基礎

下一篇：Mysql Join-連接查詢(上)