- 課程目標(biāo)

- Relational Languages

- SQL 的歷史

? ? - 除了 IBM 推出的 SQL 之外，還有另一個(gè)主流是由伯克利推出的 Ingres

? ? - 而 PostgreSQL 是由開發(fā) Ingres 那幫人開發(fā)的，所以才被稱之為 "Post" "gre" "SQL"，與 "In" "gres" 對(duì)應(yīng)，因?yàn)槭窃?Ingres 之后才出現(xiàn)的

? ? - 開發(fā) Ingres 那幫人有自己的語言，叫 Quel

? ? - 英國有個(gè)人為他的編程語言也取名叫 SQL，IBM 因此與這個(gè)人產(chǎn)生版權(quán)以及商標(biāo)糾紛

? ? - 當(dāng) IBM 推出了 DB2 和 SQL 時(shí)，Oracle 也在同時(shí)支持 SQL

? ? - SQL 一直在發(fā)展，推出很多新特性?

? ? - 現(xiàn)在只有非常少的數(shù)據(jù)庫系統(tǒng)通過 SQL:2016 標(biāo)準(zhǔn)

- SQL 并不是一門單一的語言，它是一些東西的集合

? ??

? ? - DML: 類似 insert/update/delete/select 命令

? ? - DDL: 跟 schema 有關(guān)，通過定義 schema 創(chuàng)建表來存數(shù)據(jù)

? ? - DCL: 跟安全性授權(quán)相關(guān)

? ? - lists，也就是列表，可以有重復(fù)元素，是有序的

? ? - sets, 也就是集合，沒有重復(fù)元素，是無序的

? ? - bags, 允許元素重復(fù)，無序

- Example Database

? ??

- Aggregates 聚合函數(shù)

? ??

? ??

? ? 用 "*" 代替 login

? ? "*" 是 SQL 中的特殊關(guān)鍵字，代表該 tuple 中所有屬性

? ??

?

? ? 更進(jìn)一步，用 "1" 代替 "*"，每數(shù)一個(gè) tuple，tuple 的數(shù)量就 +1

? ??

? ??

? ? 一般來講建議使用 "COUNT(1)"

? ??

? ? - 可以在單個(gè)查詢中放入多個(gè)聚合函數(shù)

? ??

? ? - DISTINCT? ??

? ? ? ? 得到去重?cái)?shù)據(jù)時(shí)使用

? ? ? ??

? ? ? ??

? ? - 一個(gè)錯(cuò)誤的例子，在聚合函數(shù)之外輸出其他 columns 會(huì) undefined

? ??

? ? ? ??

? ? - 如何解決上面的錯(cuò)誤例子？使用 GROUP BY

? ??

? ??

? ? ? ? 基于某個(gè)屬性把想要的 tuple 放到一起

? ? ? ? 物以類聚的結(jié)果

? ? ? ??

? ? ? ??

? ? ? ? 新增一個(gè) s.name

? ? ? ??

? ? ? ??

? ? - 數(shù)據(jù)過濾，使用 HAVING

? ??

? ? ? ??

? ? - 所謂查詢優(yōu)化，可以用 GROUP BY 來做，統(tǒng)計(jì)小于某個(gè)值的一些集合的數(shù)量，或者通過 HAVING 做一些過濾操作，那么要查找的數(shù)據(jù)集就小了，自然也就快了

? ??

- 字符串操作

? ? 不同數(shù)據(jù)庫對(duì)字符串的處理

? ??

? ? - SQL 標(biāo)準(zhǔn)規(guī)范有說，所有的字符串，例如 varchar、char、和 text field，它們必須區(qū)分大小寫，并且使用 單引號(hào) 來進(jìn)行聲明

? ??

? ? - '%' 和 '_'

? ??

? ? ? ??

? ? ? ? - '%': 匹配一個(gè)或者多個(gè)字符

? ? ? ? - '_': 匹配一個(gè)字符

? ??

? ? - 對(duì)于字符串函數(shù)、數(shù)學(xué)函數(shù)和日期函數(shù)來講，它們可以出現(xiàn)在查詢中的任意位置

? ??

? ? ? ??

? ? ? ? 你可以把它放在 SELECT/HAVING/或者其他條件語句中等

? ??

? ? - '||'

? ? ? ? SQL 標(biāo)準(zhǔn)下可以使用 '||' 把字符串連起來

? ? ? ??

? ? ? ??

? ? - Postgres 和 Oracle 大概是所有數(shù)據(jù)庫系統(tǒng)中遵循 SQL 標(biāo)準(zhǔn)做的最好的

? ? ? ? - Postgres ~= Oracle > SQL server > DB2

? ? ? ? - SQLite 還行

? ? ? ? - MySQL 最差

- DATE/TIME 操作

? ??

? ? 注意這里記錄用的是 時(shí)間戳，但是當(dāng)你對(duì)時(shí)間進(jìn)行操作，提取其中的信息時(shí)，結(jié)果會(huì)變得不可靠

? ??

? ? 比如對(duì)日期進(jìn)行加減處理，三個(gè)數(shù)據(jù)庫 SQL 的不同寫法

? ? 1. PostgreSQL

? ? 2. MySQL

? ? 3. SQLite

? ??

? ??

? ? SQLite 居然是最流行的。。。最初由 Richard Hipp 和其他兩人開發(fā)，手機(jī)安卓應(yīng)用很多前端數(shù)據(jù)庫就用的是 SQLite，很多桌面應(yīng)用比如 Photoshop 和 ILLustrator，內(nèi)部也運(yùn)行 SQLite

? ??

? ? 順便互聯(lián)網(wǎng)在美國興起的時(shí)間居然是 2000 年代早期。。。

? ??

? ? MySQL 是 Oracle 的，所以 Oracle 有 MySQL 的版權(quán)，但 SQLite 沒有

- 輸出重定向

? ??

? ? 即你輸出的結(jié)果可以保存到另一張表中

? ??

? ? - 可以用 INTO?

? ? - 或者用 CREATE TABLE

? ? - 或者用 INSERT INTO

? ??

- 輸出控制

? ??

? ? 如果想要輸出結(jié)果變得有序，使用 ORDER BY，因?yàn)?SQL 是基于 bag

? ? 代數(shù)，它是無序的，所以需要使用這個(gè)

? ??

? ? 想要在輸出列表中加屬性，可以接 DESC/ASC

? ??

? ??

? ? 限制輸出可以用 LIMIT

? ??

? ??

? ? 可以在 LIMIT 后面加 OFFSET

? ??

? ??

? ? OFFSET 的意思是，在限制返回 tuple 的數(shù)量之前，需要先跳過多少個(gè) tuple，當(dāng)跳過這么多你設(shè)置的 tuple 之后，再開始返回?cái)?shù)據(jù)

- 嵌套查詢（難的優(yōu)化的東西來了?。?/p>

? ? 即在查詢的內(nèi)部再嵌套一個(gè)查詢

? ??

? ? 一個(gè)嵌套查詢例子，SELECT 嵌 SELECT（實(shí)際上這也可以用 JOIN 來實(shí)現(xiàn)）

? ??

? ??

? ? 為什么這種方式很慢，實(shí)際上這里可以理解為有倆 for 循環(huán)查詢，因?yàn)橐獙?duì)這個(gè)表里面每個(gè) tuple 遍歷一遍，然后對(duì)于每個(gè) tuple 也要遍歷一遍，用 JOIN 可以優(yōu)化

? ??

? ? 用 IN 來優(yōu)化

? ??

? ??

? ? 其他查詢優(yōu)化操作符

? ??

? ??

? ? 使用 ANY 來優(yōu)化

? ??

? ??

? ? SELECT 嵌套也可以不用寫在 WHERE 中

? ??

? ??

? ? 一個(gè)查詢沒有學(xué)生注冊(cè)的所有課程的例子，注意這個(gè)例子中使用了 NOT EXIST，邏輯相當(dāng)于取了個(gè)反

? ??

? ??

? ? 內(nèi)部查詢可以引用外部查詢的東西，但是反過來不行

? ??

- window 函數(shù)

? ? window 函數(shù)會(huì)對(duì)一個(gè) tuple 子集進(jìn)行函數(shù)計(jì)算，并將它們聚合成一個(gè)結(jié)果。輸出每一行數(shù)據(jù)，并在其后面追加一個(gè)聚合字段所表示的數(shù)據(jù)

? ??

? ? 這里的 OVER 有點(diǎn)像 GROUP BY 的意思

? ??

? ? 一個(gè)例子

? ??

? ? 輸出結(jié)果如下

? ??

? ??

? ? 可以看到后面增加了一個(gè) row_num 的字段

? ??

? ? - OVER 關(guān)鍵字的作用是當(dāng)計(jì)算 window 函數(shù)時(shí)，指定一種組合 tuple 的方式

? ? - 使用 PARTITION BY 指定 group

? ? ? ??

? ??

? ? ? ? 輸出如下

? ? ? ??

? ? ? ??

? ? ? ? 根據(jù) cid 對(duì)它們進(jìn)行分組，每一組內(nèi)按 row_num 的順序排列

? ? ? ??

? ? - 在 window grouping 中也可以使用 ORDER BY

? ??

? ??

? ? - 一個(gè)例子，查詢每門課中最高分的學(xué)生

? ??

? ??

? ? ? ? 在這個(gè)內(nèi)部查詢（紅色部分表示的）中，遍歷整個(gè) enrolled 表，對(duì)于每個(gè) tuple，基于 cid 來對(duì)它們進(jìn)行分組

? ? ? ??

? ? ? ? RANK() 這個(gè)聚合函數(shù)會(huì)對(duì)每一個(gè)組進(jìn)行 rank 排序

? ? ? ??

? ? ? ? 注意此處的 'ranking.rank' 實(shí)際上并不存在于數(shù)據(jù)庫中，它只存在于這個(gè)查詢之中（感覺像是一個(gè)臨時(shí)變量

? ? ? ??

? ? ? ? 最終的結(jié)果如下，PostgreSQL 支持這個(gè)語句

? ? ? ??

? ? ? ??

? ? ? ? RANK() 函數(shù)會(huì)根據(jù)排序后每個(gè) tuple 在輸出列表中出現(xiàn)的現(xiàn)后順序來進(jìn)行計(jì)算

? ? ? ??

? ? ? ? 如果是 SQL 為 `SELECT *, RANK() OVER() AS rank FROM enrolled; `，那么就是如下的結(jié)果

? ? ? ??

? ? ? ??

? ? ? ? rank 處的值都是 1，因?yàn)檫@里面并沒有排序，所以所有記錄中的 rank 都是 1

? ? ? ??

? ? ? ? 這里的例子中，如果沒有 ORDER BY，就沒有 rank

- CTE（Common Table Expressions）公用表表達(dá)式

? ? 這個(gè)感覺有點(diǎn)像是可以把一些公用的東西抽出來，然后給其他 SQL 引用的感覺

? ? ? ??

? ? 它和嵌套查詢非常類似，可以將一個(gè)查詢的輸出結(jié)果作為另一個(gè)查詢的輸入，但某些 功能 CTE 能做，嵌套查詢做不到

? ??

? ? - 一個(gè)更復(fù)雜的例子

? ??

? ? ? ??

? ? ? ? 將輸出的 col1 和 col2 與 cteName 中的查詢語句中的 name 進(jìn)行綁定，然后在下面的 SELECT 語句中就可以直接引用 col1 和 col2

? ? ? ??

? ? - 例子，在 enrolled 表中找到最大的學(xué)生 id

? ??

? ? ? ??

? ? 那么問題來了，這個(gè)跟嵌套查詢有啥區(qū)別呢？

? ? 在 CTE 中可以遞歸，但是嵌套查詢不能（所以感覺像是聲明了一個(gè)函數(shù)類似

? ? ? ? 東西？）

? ? 比如打印 1 到 10，注意: 不帶 ALL 的 UNION 操作會(huì)將重復(fù)結(jié)果移除

? ??

? ??

? ? 主要邏輯就是調(diào)用之前的 CTE 中的 tuple 作為輸出結(jié)果。

? ? 在這個(gè)例子中，會(huì)生成只有一個(gè)屬性的表（counter），調(diào)用一個(gè) SELECT 來獲取這個(gè) tuple，然后對(duì)它進(jìn)行 +1，這樣就生成了一個(gè)新的 tuple。接著繼續(xù)上述操作，直到 WHERE 條件無法匹配為止

? ? ? ? ? ??

- 總結(jié)

? ??

? ? 在理想情況下，可以無需將數(shù)據(jù)存放到本地，或者來回移動(dòng)，就能對(duì)整個(gè)查詢進(jìn)行計(jì)算