sql secret scripts

sql 語法順序、執(zhí)行順序、執(zhí)行過程、要點解析、優(yōu)化技巧。

1、語法順序

??如上圖所示，為 sql 語法順序與執(zhí)行順序對照圖。其具體含義如下：

• 0、select： 用于從數據庫中選取數據，即表示從數據庫中查詢到的數據的列。其后可跟列名、函數、子查詢等。
• 1、distinct： 用于對結果集進行去重，即若查詢的數據中存在重復項，則可用其進行去重。其需要放在 select 后第一順位，且其去重并不是對 select 后某個字段進行去重，而是對 select 后所有列進行去重。
• 2、from： 表示要查詢的數據庫表，即主表。其后跟表名、子查詢等。
• 3、join： 表示要連接的表，及關聯表。其后跟要連接表名、子查詢等。
• 4、on： 表示主表與關聯表的關聯條件。
• 5、where： 表示查詢條件。其后可跟普通條件、函數（普通函數）等。
• 6、group by： 表示分組，及將數據按照分組條件進行分組。其后跟要分組的列名。
• 7、having： 對分組結果進行篩選。其后跟普通條件、聚合函數等。
• 8、order by： 表示排序，及將結果集按照某種條件進行排序。其后跟要排序的列名及排序方式（升序、降序）。
• 9、limit： 表示最終結果集的大小，即查詢結果集的大小將 <= limit 的值。其后跟數據集大小。

??如上所述，則對于表 用戶表 web_user(id, username, age, gender, address)、系統(tǒng)日志表 sys_log(id, user_id, operate_name, request_time, request_params)，其查詢 sql 可為：

# 某些數據庫中 user 字符為關鍵字 故此 sql 中 user 別名呈關鍵字色
select distinct user.username, log.operate_name
from sys_log logleft join web_user useron log.user_id = user.id
where log.operate_name like '%列表%'
group by user.username, log.operate_name
having avg(log.request_time) > 100
order by user.username desc
limit 10

2、執(zhí)行順序

??如上圖所示，為 sql 實際執(zhí)行順序。在 sql 的實際執(zhí)行過程中，每個步驟都會產生一個虛擬表，這個虛擬表將作為下一步的基礎數據。其具體含義如下：

• 0、from： 選擇要查詢的基表（即主表），產生虛擬表 1。
• 1、on： 連接查詢（join）時主表與關聯表的關聯條件。將關聯條件匹配到的行記錄在虛擬表 2。
• 2、join： 選擇要關聯的表。若為 left/right join，則將主表中關聯條件為匹配到的行添加到虛擬表 2，產生虛擬表 3。若有多個關聯表，則重復執(zhí)行 0～2 步，直到所有關聯表都處理完。
• 3、where： 使用過濾條件對虛擬表 3 進行過濾，將符合條件的行插入到虛擬表 4。
• 4、group by： 根據 group by 指定的列，對虛擬表 4 進行分組，產生虛擬表 5。
• 5、having： 根據 having 指定的過濾條件對 虛擬表 5 中的每一組記錄進行過濾，將符合條件的的行插入到虛擬表 6。
• 6、select： 從虛擬表 6 中取出 select 指定的列的記錄插入到虛擬表 7。
• 7、distinct： 將虛擬表 7 中重復的行刪除（記錄的唯一性），產生虛擬表 8。
• 8、order by： 對虛擬表 8 中的記錄按照 order by 指定的列及指定的排序方式進行排序。
• 9、limit： 取出指定行數的記錄，返回結果集。

3、要點解析

• distinct： 其作用是對記錄進行去重，去重時不是根據 distinct 后的某個字段去重，而是根據其后的所有字段去重，即可以理解為其后所有字段構成的唯一主鍵。一般而言，distinct 是 group by 子句的特殊情況，若對 distinct 結果集進行排序則可得到 group by 同樣的結果。同時，distinct 會講 null 值當作一條記錄返回。

  ??當想要根據某個或某幾個字段去重，同時又要查出其它字段時，則可以結合 group by 來實現，如想根據 username 去重，同時查詢出 id，則其 sql 可為：

  # 此方案不適配 mysql
  select id, count(distinct username) from sys_log group by username;
  

• join： 關聯查詢，分為四種類型，分別是：

  • inner join：內連接，即左右兩個表中至少匹配到一條記錄則返回。
  • left join：左連接，即使右表（關聯表）中沒有匹配到行，也返回左表（主表）中的所有行。
  • right join：右連接，即使左表（關聯表）中沒有匹配到行，也返回右表（主表）中的所有行。
  • full join：全連接，只要有一個表中匹配到行，則返回。

  ??join 時要注意 on 條件，on 條件作為左右兩表的關聯條件，直接決定了后續(xù) where 時的數據量，所以盡可能的在 on 中篩選掉無用數據。若無 on 條件則會出現笛卡爾積現象。

• where： where 條件中只能使用普通條件（如 and、or、in 等）和普通函數（ucase()、lcase()、mid()、substring()、len()、round()、now()、format() 等），不能使用聚合函數（avg()、max()、min()、count()、first()、last()、sum() 等）。

• not、and、or： 邏輯運算符。

  • and：若 and 前后兩個條件都成立，則 and 運算符顯示一條記錄。
  • or：若 or 前后兩個條件只要一個成立，則 or 運算符顯示一條記錄。
  • not：表示非。

  ??邏輯運算符使用需要注意優(yōu)先級，其優(yōu)先級為 ( )、not、and、or。所以必要時需使用 ( ) 來確保 and 和 or 條件的先后順序。

• between： between 的使用需要注意上下限，而其上下限由不同數據庫的實現決定。如：

  • 在某些數據庫中，between 選取介于兩個值之間但不包括兩個值的數據。
  • 在某些數據庫中，between 選取介于兩個值之間且包括兩個值的數據。
  • 在某些數據庫中，between 選取介于兩個值之間但只包括第一個值不包括第二個值的數據。

• group by： group by 表示分組，其規(guī)定 group by 后跟的列需和 select distinct 后跟的列保持一致，若此時還需要查出其它字段，則可以使用 rank() over (parition by) 關鍵字實現。

  ???rank() over (partition by)：其中 rank() 是排序函數，其會對結果集排序并產生一個序號；partition by 為分組，若無指定則所有結果集默認一個組。如想按 operate_name 分組，同時又想查出 username、operate_name 列，則 sql 可為：

  # order by 是為了對 partition by 分組結果進行排序，所以 order by 列盡可能使用 id 這種差異性極強的列來排序（如唯一索引）
  # 只有排序后每條記錄的 rankNo 值都不同，才能根據 rankNo = 1 取到唯一的一條記錄
  select * from (select username, operate_name, rank() over (partition by operate_name order by id) rankNo 			from sys_log) as temp
  where temp.rankNo = 1
  

• having： 對分組后的結果進行篩選，其后只能跟普通條件和聚合函數（avg()、max()、min()、count()、first()、last()、sum() 等）。

• order by： order by 表示排序，需要注意多字段排序的情況。如 order by a, b，先根據字段 a 的值排序，然后對 a 列相同的行再根據字段 b 的值排序。

• union： union 用來合并兩個或多個 select 的結果集。需注意，使用 union 時，多個 select 語句必須擁有相同數量的列，且列的數據類型需保持一致，select 列的先后順序也要保持一致。

• limit： limit 語句用來截取指定條數的結果集，一般用在分頁中，如以下 sql：

  select * from sys_log where operate_name = '列表' limit 1000000, 10
  

  ??其含義為查詢出第 1000000 行及之后的 9 行，但在實際執(zhí)行中會發(fā)現耗時較長。這是因為數據庫也不知道第 1000000 行從什么地方開始，因此需要先找到第 1000000 行，然后再取出 10 條。此時則可以將上一頁的最大值作為查詢條件傳入，則 sql 如下：

  # 假設上一頁最后一條數據的 create_time 值為 2023-11-30 22:25:00
  select * from sys_log where operate_name = '列表' and create_time > '2023-11-30 22:25:00' limit 10
  

  ??此時會發(fā)現，耗時將大大減小。