續 | 《SQL基礎教程》學習筆記

點擊上方SQL資料庫開發，關注獲取SQL視頻教程

上周給大家分享了一部分《SQL基礎教程》學習筆記，今天將剩下部分繼續分享給大家。
5 複雜查詢5.1 視圖從sql角度，視圖和表是相同，區別在表中保存的實際數據，而視圖保存是select語句(視圖本身不存儲數據)可以將select語句當成視圖來使用，創建視圖用create view,刪除視圖用drop 視圖包含"不能使用order by"和"對其進行有限制的更新"，實際上視圖保存的是select語句頻繁的使用select語句保存成視圖，不用每次都重新書寫了，將進程使用的select做成視圖

create view <視圖名稱>
as 
<select語句>;
在視圖的基礎上創建視圖，但儘量避免在視圖的基礎上創建視圖，多重視圖會降低SQL的性能在select語句中視圖可以和表一樣使用，但是對於insert、delete、update這類更新語句滿足了一定條件可以被更新由於使用視圖來保存原表的匯總數據時無法判斷將視圖更改反映到原表中的，例如將一行數據插入視圖中，但是原表中的編號等信息不清楚，無法保證數據的一致性視圖和表需要同時進行更新，因此通過匯總得到的視圖無法進行更新
select子句中未使用distinct
from 子句中只有一張表
未使用group by子句
未使用having子句
標量就是單一的意思，在資料庫之外的領域也經常使用，標量子查詢就是必須且只能返回一行一列的結果，由於只返回單一值，所以標量子查詢的返回值可以用在"="或"<>"這樣需要單一值比較的運算符之中，使用關聯資產時，通常會使用"限定"或"限制"這樣的語言

select product_type,product_name,sale_price
from Product as P1
where sale_price >
  (select avg(sale_price)
   from Product as P2
   where P1.product_type = P2.product_type
  
  group by product_type
  );

函數就是輸入某一值得相應輸出結果的功能，輸入值稱為參數，輸出值稱為返回值

select round(1123.26723,2);

select abs(-1)

select mod(7,3)
concat() 
length() 

lower() 
replace(對象字符串，替換前的字符串，替換後的字符串)
substring(對象字符串 from 截取的位置 for 截取的字符個數) 

current_date
current_time
current_timestamp
extract()函數
在sql中有兩層意思：一是數據類型的轉換，簡稱類型轉換，在英語中稱為cast；另一層的意思是值的轉換；
cast(轉換前的值 as 想要轉換的數據類型)
select cast("ooo1" as interger)

謂詞是需要滿足特定條件的函數，該條件就是返回值是真值。返回值可能是數字、字符串或者日期，但是謂詞的返回值就是真值(True/False/Unknown)。這就是謂詞和函數的最大區別。

select * from SampleLike where strcol like "ddd%"; 

select * from SampleLike where strcol like "%ddd%"; 

select * from SampleLike where strcol like "%ddd";

select * from SampleLike where strcol like "ddd_ _"; 
--為了選出某些值為null的列的數據，不能使用=，使用特定的謂詞is null，不是null的is not null--選取出在範圍的數據，還有not in不在範圍的數據，兩者都無法選取出null數據-- 謂詞的作用就是"判斷是否存在滿足某種條件的記錄"，存在這樣的記錄返回True，否則返回Falsecase表達式分為簡單case表達式和搜索case表達式，簡單表達式包含在搜索表達式內，只介紹搜索case表達式case表達式是在區分情況時使用的，在編程中稱之為(條件)分支，是相當於sql中的if語句

case when <求值表達式> then <表達式> -- <求值表達式> 類似於"列=值"
     when <求值表達式> then <表達式>
     when <求值表達式> then <表達式>
     .
     .
     else <表達式> --指定了不滿足when子句中的條件的記錄，null之外其他值或表達式都可寫在else中
end --不能省略

select product_name,
case when product_type = "衣服"
then "A":'|| product_type'
when product_type = "辦公用品"
then "B":'|| product_type'
when product_type = "廚房用品"
then "C":'|| product_type'
else null
end as abc_product_type
from Product;
select product_name,
case product_type 
when "衣服"  then  "A":'|| product_type'
when "辦公用品"  then  "B":'|| product_type'
when "廚房用品"  then  "C":'|| product_type'
end as abc_product_type
from Product;

select product_name,product_price
from Product
where purchase_price not in (500,2800,5000)；
--結果如下，沒有含有null的兩行，是因為通常的謂詞都無法與null比較product_name  purchase_price
select product_name,product_price
from Product
where purchase_price not in (500,2800,5000，null)；
--結果是什麼都沒有，使用子查詢作為not in 的參數時，子查詢的返回值也不能是null
集合在數學領域表示"（各種各樣）事物的總和"，在資料庫領域表示記錄(行)的集合，集合運算是對滿足同一規則的記錄的進行加減等四則運算。用來進行集合運算的運算符稱為集合運算符。
select product_id,sale_price from Product
union
select product_id,sale_price from Product2;
作為運算對象的記錄中列的類型必須一致，如果要使用不同的類型，可以使用類型轉換函數cast可以使用select語句，但是order by子句只能在最後使用一次
select product_id,sale_price from Product
union all
select product_id,sale_price from Product2;

--intersect應用於兩張表中，選取出公共記錄
select product_id,sale_price from Product
intersect
select product_id,sale_price from Product2
order by product_id;

select product_id,sale_price from Product
except
select product_id,sale_price from Product2
order by product_id;
--減法運算的減數和被減數位置的不同，得到的結果也不同聯結(join)就是將其他表中的列添加過來。聯結大致分為內連接和外聯結。之前的union和intersect都是以行方向為單位進行操作。union增加行，intersect減少行。之前from都是一個表，而這次同時使用兩張表，使用inner join將兩張表聯結過來注意on後邊的聯結條件，指定兩張表聯結使用的列（聯結鍵），使用內連接必須使用on子句，書寫在from和where之間外聯結包括做聯結和右聯結，外聯結使用left和right來指定主表，使用left時，from子句中left左邊的表是主表，使用right時，from子句中right右邊的表是主表。-- 聯結的from子句中再次使用使用inner join(outer join) 將其他表也添加進來-- 進行交叉聯結時無法使用內聯結和外聯結中所使用的on,交叉聯結是對兩張表中的全部記錄進行交叉組合，因此結果中的記錄通常是兩張表行數的集合。窗口函數可以進行排序、生成序列號等一系列的聚合函數無法實現的高級操作窗口函數也稱為OLAP函數(online analytical processing 實時分析處理)，例如進行市場分析、創建財務報表、創建計劃等日常商務工作。
<窗口函數> over ([partition by <列清單>]
order by <排序用列清單>）
--能夠作為窗口函數的函數
--聚合函數（sum,avg,count,max,min）
--rank,dense_rank,row_number等專用窗口函數

select product_name,product_type,sale_price,
rank() over (partition by product_type
             order by sale_price) as ranking
from Product;

-- partition by 能夠設定排序的對象範圍，本例中設定了商品的種類進行排序--order by指定了按照哪一列，何種順序進行排序，與select語句中末尾的order by一樣，可以通過關鍵字asc/desc來指定升序或者降序partition by在橫向上對表進行分組，而order by決定了縱向的排序規則。通過partition by分組後的記錄合稱為窗口。省略了partition by相當於一個大的窗口。窗口函數兼具group by子句分組和排序的功能，但是不具有group by子句匯總的功能計算排序時，如果存相同位次的記錄，則會跳過之後的位次同樣是計算排序，即使存在相同位次的記錄，也不會跳過之後的位次窗口函數只能在select中使用，能在order by中使用是因為order by在select之後執行的窗口函數就是以窗口為單位進行分割，並在其中進行排序的函數，其實還有包含在窗口中指定更加詳細的匯總範圍的備選功能，該備選功能中的匯總範圍稱為框架
select product_id,product_name,sale_price,
avg(sale_price) over (order by product_id
    rows 2 preceding) as moving_avg
from Product;
-- 這裡使用row("行")和preceding("之前")兩個關鍵字，將框架指定為"截止~行"，因此rows 2 preceding這就是截止2之前兩行，響應的「之後」是following-- 由於框架是根據當前記錄來確定，因此和固定的窗口不同，其範圍會隨著當前的記錄的變化而變化rows between 1 preceding and 1 following --前中後三行在窗口函數中也有一個order by，但是這個order by只是決定窗口函數按照什麼樣的順序進行計算的，對結果的排序沒有影響。要想最後按照順序排序，需要在select語句最後，使用order by子句進行指定；只使用group by子句和聚合函數是無法同時得到小計和合計的，可以用grouping實現理解grouping運算符中的cube關鍵在於形成「積木搭建出的立方體」的印象grouping 運算符包含以下是三種：rollup(捲起的意思)、cube、grouping sets
select product_type,sum(sale_price) as sum_price
from Product
group by rollup(product_type);

該函數在其參數列的值為超級分組記錄所產生的null時返回1，其他情況返回0
select grouping(product_type) as product_type,
grouping(regist_date) as regist_date,
sum(sale_price) as sum_price
from Product
group by rollup(product_type,regist_date);
product_type  regist_date  sum_price1                      1                   167800                      0                   111800                      0                   880cube語法和rollup相同，只需將rollup換成cube即可，rollup結果一定包含在cube中所謂cube就是將group by子句中的聚合鍵的「所有可能的組合」的匯總結果集中到一個結果中該運算符可以用於從rollup或者cube中取出部分記錄
關注SQL資料庫開發公眾號，在後臺回復關鍵字：資料領取，可以獲取一份精心整理的技術乾貨。
點擊「閱讀原文」了解SQL訓練營