MySQL高級語言（二）-有解無憂

一、視圖

1、VIEW

1.1概念

可以被當作是虛擬表或存盤查詢
視圖跟表格的不同是，表格中有實際儲存資料，而視圖是建立在表格之上的一個架構，它本身并不實際儲存資料，
臨時表在用戶退出或同資料庫的連接斷開后就自動消失了，而視圖不會消失，
視圖不含有資料，只存盤它的定義，它的用途一般可以簡化復雜的查詢，比如你要對幾個表進行連接查詢，而且還要進行統計排序等操作，寫SQL陳述句會很麻煩的，用視圖將幾個表聯結起來，然后對這個視圖進行查詢操作，就和對一個表查詢一樣，很方便，

1.2創建、查看和洗掉視圖

CREATE VIEW "視圖表名" AS "SELECT 陳述句";                      #創建視圖表
SELECT * FROM `V_NAME_VALUE`;                               #查看視圖表
DROP VIEW V_NAME_VALUE;                         　　　　　　  #洗掉視圖表

實體操作：
mysql> create view v_test_01 as select A.name,A.address from test A where name in (select B.name from t1 B where score > 20);
mysql> select *from v_test_01;
drop view v_test_01;

2、聯集

將兩個SQL陳述句的結果合并起來，兩個SQL陳述句所產生的欄位需要是同樣的

2.1UNION

生成結果值將沒有重復，且按照欄位的順序進行排序
語法：[SELECT 陳述句 1] UNION [SELECT 陳述句 2];

實體操作：
select name from t1 union select name from test;

2.2UNION ALL

將生成結果的值都列出來，無論有無重復
語法：[SELECT 陳述句 1] UNION ALL [SELECT 陳述句 2];

實體操作：
select name from t1 union all select name from test;

3、交集值（取兩個SQL陳述句結果的交集）

3.1取交集值的方法1

注：2種簡單方法，內連接+on/using，去重則加上distinct

select A.name from t1 A inner join test B on A.name=B.name;
select A.name from t1 A inner join test B using(name);
select distinct A.name from t1 A inner join test B on A.name=B.name;

3.2取交集方法2

1種，union all結合group by：兩表其中的一個表沒有指定的行，而另一個表這個行有重復不可用，要求兩個表確實有交集的時候用，

select A.name from (select name from t1 union all select name from test) A group by A.name having count(*) > 1;
select A.name from (select name from t1 union all select name from test) A group by A.name having count(name) > 1; 
select name from t1 union all select name from test; #拆分上面的SQL陳述句
select A.name,count(name) from (select name from t1 union all select name from test) A group by A.name having count(name) > 1; #顯示count值，便于理解
select A.name,count(name) from (select distinct name from t1 union all select distinct name from test) A group by A.name having count(name) > 1; #去重顯示，在聯集兩個表之前先把表去重，以防一個表中本身就有重復值

3.3取交集

取兩個SQL陳述句結果的交集，且沒有重復

方法一：
mysql> select A.name from (select B.name from t1 B inner join test C on B.name=C.name) A group by A.name;
方法二：
select distinct A.name from t1 A inner join test B using(name);
方法三：
select distinct name from t1 where name in (select name from test);
方法四：
select distinct A.name from t1 A left join test B using(name) where B.name is NOT NULL;

（1）內連接取交集結合group by去重

（2）內連接取交集結合distinct去重

（3）where+in遍歷取交集并結合distinct去重

（4）使用左連接（也可用右連接）+where 判斷NOT NULL 取交集并結合distinct去重

4、無交集值

顯示第一個SQL陳述句的結果，且與第二個SQL陳述句沒有交集的結果，且沒有重復

方法一：
select A.name from (select distinct name from t1 union all select distinct name from test) A group by A.name having count(name)=1;
方法二：
select distinct name from test where name not in (select distinct name from t1);
select distinct name from t1 where name not in (select distinct name from test);
方法三：
select distinct A.name from t1 A left join test B using(name) where B.name is NULL;
select distinct B.name from t1 A right join test B using(name) where A.name is NULL;

（1）union all結合group by進行分組匯總并使用count=1取無交集值

（2）where+not in遍歷取無交集值并結合distinct去重

（3）使用左連接（或者右連接）+where 判斷NULL 取無交集并結合distinct去重

5、CASE的用法

是SQL用來作為IF-THEN-ELSE之類邏輯的關鍵字

5.1語法格式

SELECT CASE （欄位名）
    WHEN "條件1" THEN  "結果1"
    WHEN "條件2" THEN  "結果2"
    ……
    ELSE "結果N"
    END
FROM "表名"

條件可以是一個數值或是公式，ELSE子句不是必須的

5.2實體操作

6、空值（NULL）和無值（“”）的區別

區別：
無值的長度為0，不占用空間；而空值null 的長度是null，是占用空間的；
IS NULL或者IS NOT NULL，是用來判斷欄位是不是NULL或者不是NULL，是不能查出是不是無值的；
無值的判斷使用=’‘或者<>’'來處理，<>代表不等于；
在通過count()指定欄位統計有多少行數時，如果遇到NULL值會自動忽略掉，遇到空值會自動加入記錄中進行計算，

6.1判斷空值和無值的字符長度

select length(NULL),length(''),length('1');

6.2使用count統計行數（體現null與空值的區別）

count(*) 表示包括所有列的行數，不會忽略null值；空值正常統計
count(列名) 表示只包括這一列，統計時會忽略null值的行；空值正常統計

二、正則運算式（REGEXP）

1、正則運算式匹配符

2、語法

SELECT 選項 FROM 表名 WHERE 選項 REGEXP (模式)

select * from test where name regexp 'wu$';　　　　###以wu結尾的
select * from test where score regexp '50|80';　　　　
select * from test1 where name regexp '^F|^Z';

三、存盤程序

1、概述

存盤程序是一組為了完成特定功能的SQL陳述句集合
存盤程序在使用程序中是將常用或者復雜的作業預先使用 SQL 陳述句寫好并用一個指定的名稱存盤起來，這個程序經編譯和優化后存盤在資料庫服務器中，當需要使用時，只需要呼叫即可
存盤程序在執行上比傳統SQL速度更快、執行效率更高，

2、優點

執行一次后，會將生成的二進制代碼駐留緩沖區，提高執行效率
SQL陳述句加上控制陳述句的集合，靈活性高
在服務器端存盤，客戶端呼叫時，降低網路負載
可多次重復被呼叫，可隨時修改，不影響客戶端呼叫
可完成所有的資料庫操作，也可控制資料庫的資訊訪問權限

3、創建、呼叫、查看和洗掉存盤程序

（1）創建存盤程序

DELIMITER $$                                #將陳述句的結束符號從分號;臨時改為兩個$$ (可以是自定義)
CREATE PROCEDURE proc()                     #創建存盤程序，程序名為Proc， 不帶引數
-> BEGIN                                    #程序體以關鍵字BEGIN開始
-> SELECT * FROM test5;                     #程序體陳述句（自己根據需求進行撰寫）
-> END $$                                   #程序體以關鍵字END結束
DELIMITER ;                                 #將陳述句的結束符號恢復為分號

實操：

mysql> delimiter ##
mysql> create procedure proc()
    -> begin
    -> insert into test2 values (2,'lucy',23);
    -> insert into test2 values (2,'jack',21);
    -> insert into test2 values (4,'nancy',22);
    -> select * from test2;
    -> end ##

　　mysql> delimiter ;

（2）呼叫存盤程序

mysql> CALL proc;

（3）查看存盤程序

SHOW CREATE PROCEDURE [資料庫.] 存盤程序名;      #查看某個存盤程序的具體資訊（如果在指定庫中，庫名可以省略）
SHOW CREATE PROCEDURE fzr.proc;                 #未省略庫名
SHOW CREATE PROCEDURE proc;                     #省略庫名
 
SHOW PROCEDURE STATUS [LIKE '%proc%'] \G                #豎列查看

（4）存盤程序的引數

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

實體操作：
IN 傳入引數

mysql> delimiter ##
mysql> create procedure proc1(in iage int)
    -> begin
    -> select * from test2 where age > iage ;
    -> end ##
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
mysql> call proc1(21);

OUT輸出引數

因為out是向呼叫者輸出引數，不接收輸入的引數，所以存盤程序里的num為null

#呼叫了proc2存盤程序，輸出引數，改變了num變數的值

INOUT輸入輸出引數

呼叫了proc3存盤程序，接受了輸入的引數，也輸出引數，改變了變數

（5）洗掉存盤程序

存盤程序內容的修改方法是通過洗掉原有存盤程序之后以相同名稱創建新的存盤程序

DROP PROCEDURE IF EXISTS proc1;

4、存盤程序的控制陳述句

（1）條件控制陳述句 if-then-else … end if

mysql> delimiter ##                                #修改默認結束符為##
mysql> create procedure proc1(in iage int)         #創建存盤程序proc1，引數為iage,資料型別為int
    -> begin                                       #程序體以關鍵詞begin開始
    -> declare var int;                            #定義變數var為int型別
    -> set var=iage*2;                             #設定變數var等于傳入引數的2倍
    -> if var >=20 then                            #如果var大于等于20,則執行下面的程序體
    -> update test2 set age=age+1;                 #設定test2中的age+1
    -> else                                        #如果變數var不大于10,則執行下面程序體
    -> update test2 set age=age-1;                 #設定表test2中的age-1
    -> end if;                                     #結束if陳述句
    -> end ##                                      #結束創建存盤程序
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;                                 #重新修改默認結束符為原來的;
mysql> call proc1(10);                             #呼叫proc1的存盤程序，并傳入引數10
Query OK, 6 rows affected (0.00 sec)

（2）回圈陳述句while … end while

mysql> delimiter ##                       #修改默認結束符為##
mysql> create procedure proc4()           #創建存盤程序為proc4
    -> begin                              #程序體以關鍵字begin開始
    -> declare var int;                   #定義變數var為int型別
    -> set var=0;                         #設定var=0
    -> while var <10 do                   #使用while回圈，var要小于10
    -> insert into test2(id) values(var); #滿足條件則進行添加資料，內容為變數var
    -> set var=var+1;                     #變數var每次回圈后加1
    -> end while;                         #結束while回圈
    -> end ##                             #結束創建存盤程序
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;                        #重新修改默認結束符為原來的;
mysql> call proc4;                        #呼叫proc4存盤程序
Query OK, 1 row affected (0.01 sec)

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/539165.html

標籤：其他

上一篇：機器學習：監督學習

下一篇：世界杯競猜專案Dapp-第一章（合約開發）