為什麼使用@tablename起别名産生的sql語句不能用_如何寫優雅的SQL原生語句？

前言：

上一篇講Mysql基本架構時，以“sql查詢語句在MySql架構中具體是怎麼執行的？”進行了全面的講解。知道了sql查詢語句在MySql架構中的具體執行流程，但是為了能夠更好更快的寫出sql語句，我覺得非常有必要知道sql語句中各子句的執行順序。看過上一篇文章的小夥伴應該都知道，sql語句最後各子句的執行應該是在執行器中完成的，存儲引擎對執行器提供的資料讀寫接口。現在開始我們的學習

作者簡介：koala，專注完整的 Node.js 技術棧分享，從 JavaScript 到 Node.js,再到後端資料庫，祝您成為優秀的進階 Node.js 工程師。【程式員成長指北】作者，Github 部落格開源項目 https://github.com/koala-coding/goodBlog

語句中各子句完整執行順序概括（按照順序号執行）

1. from (注:這裡也包括from中的子語句)
2. join
3. on
4. where
5. group by(開始使用select中的别名，後面的語句中都可以使用)
6. avg,sum… 等聚合函數
7. having
8. select
9. distinct
10. order by
11. limit

每個子句執行順序分析

所有的 查詢語句都是從from開始執行的，在執行過程中，每個步驟都會為下一個步驟生成一個虛拟表，這個虛拟表将作為下一個執行步驟的輸入。

1. from

form是一次查詢語句的開端。

• 如果是一張表，會直接操作這張表；
• 如果這個from後面是一個子查詢，會先執行子查詢中的内容，子查詢的結果也就是第一個虛拟表T1。（注意：子查詢中的執行流程也是按照本篇文章講的順序哦）。
• 如果需要關聯表，使用join，請看2，3

2. join

如果from後面是多張表，join關聯，會首先對前兩個表執行一個笛卡爾乘積，這時候就會生成第一個虛拟表T1（注意：這裡會選擇相對小的表作為基礎表）；

3. on

對虛表T1進行ON篩選，隻有那些符合的行才會被記錄在虛表T2中。（注意，這裡的這裡如果還有第三個表與之關聯，會用T2與第三個表進行笛卡爾乘積生産T3表，繼續重複3. on步驟生成T4表，不過下面的順序講解暫時不針對這裡的T3和T4，隻是從一個表關聯查詢T2繼續說）

4. where

對虛拟表T2進行WHERE條件過濾。隻有符合的記錄才會被插入到虛拟表T3中。

5.group by

group by 子句将中的唯一的值組合成為一組，得到虛拟表T4。如果應用了group by，那麼後面的所有步驟都隻能操作T4的列或者是執行6.聚合函數（count、sum、avg等）。（注意：原因在于分組後最終的結果集中隻包含每個組中的一行。謹記，不然這裡會出現很多問題，下面的代碼誤區會特别說。）

6. avg,sum… 等聚合函數

聚合函數隻是對分組的結果進行一些處理，拿到某些想要的聚合值，例如求和，統計數量等，并不生成虛拟表。

7. having

應用having篩選器，生成T5。HAVING子句主要和GROUP BY子句配合使用，having篩選器是第一個也是為唯一一個應用到已分組資料的篩選器。

8. select

執行select操作，選擇指定的列，插入到虛拟表T6中。

9. distinct

對T6中的記錄進行去重。移除相同的行，産生虛拟表T7.（注意：事實上如果應用了group by子句那麼distinct是多餘的，原因同樣在于，分組的時候是将列中唯一的值分成一組，同時隻為每一組傳回一行記錄，那麼是以的記錄都将是不相同的。 ）

10. order by

應用order by子句。按照order_by_condition排序T7，此時傳回的一個遊标，而不是虛拟表。sql是基于集合的理論的，集合不會預先對他的行排序，它隻是成員的邏輯集合，成員的順序是無關緊要的。對表進行排序的查詢可以傳回一個對象，這個對象包含特定的實體順序的邏輯組織。這個對象就叫遊标。

oder by的幾點說明

• 因為order by傳回值是遊标，那麼使用order by 子句查詢不能應用于表表達式。
• order by排序是很需要成本的，除非你必須要排序，否則最好不要指定order by，
• order by的兩個參數 asc（升序排列） desc（降序排列）

11. limit

取出指定行的記錄，産生虛拟表T9, 并将結果傳回。

limit後面的參數可以是 一個

limit m

，也可以是

limit m n

，表示從第m條到第n條資料。

（注意：很多開發人員喜歡使用該語句來解決分頁問題。對于小資料，使用LIMIT子句沒有任何問題，當資料量非常大的時候，使用LIMIT n, m是非常低效的。因為LIMIT的機制是每次都是從頭開始掃描，如果需要從第60萬行開始，讀取3條資料，就需要先掃描定位到60萬行，然後再進行讀取，而掃描的過程是一個非常低效的過程。是以，對于大資料處理時，是非常有必要在應用層建立一定的緩存機制）

開發某需求寫的一段sql

SELECT `userspk`.`avatar` AS `user_avatar`, 
`a`.`user_id`, 
`a`.`answer_record`, 
 MAX(`score`) AS `score`
FROM (select * from pkrecord  order by score desc) as a 
INNER JOIN `userspk` AS `userspk` 
ON `a`.`user_id` = `userspk`.`user_id`
WHERE `a`.`status` = 1 
AND `a`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' 
GROUP BY `user_id`
ORDER BY `a`.`score` DESC 
LIMIT 9;
           

查詢結果：

• 先簡要說一下我要查詢的内容：

想要查詢pk記錄表中分數最高的9個使用者記錄和他們的頭像。

• 通過這段sql實際想一遍sql各字句的執行順序

pk記錄表的資料結構設計，每個使用者每天每個館下可能會有多條記錄，是以需要進行分組，并且

查詢結果隻想拿到每個分組内最高的那條記錄

。

這段sql的一些說明：

1. 可能有些同學會認為子查詢沒有必要

   直接查詢pk記錄表就可以，但是并不能拿到預期的結果，因為

   分組後的每個組結果是不進行排序的 ，而且max拿到的最高分數肯定是對應的該分組下最高分數，但是其它記錄可能就不是最高分數對應的那條記錄。是以子查詢非常有必要， 它能夠對原始的資料首先進行排序 ，分數最高的那條就是第一條對應的第一條記錄。

看一下代碼和執行結果與帶有子查詢的進行比較，就能了解我上面說的一段話：

//不使用子查詢
SELECT `userspk`.`avatar` AS `user_avatar`, 
`pkrecord`.`user_id`, 
`pkrecord`.`answer_record`, 
`pkrecord`.`id`, 
 MAX(`score`) AS `score`
FROM pkrecord
INNER JOIN `userspk` AS `userspk` 
ON `pkrecord`.`user_id` = `userspk`.`user_id`
WHERE `pkrecord`.`status` = 1 
AND `pkrecord`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' 
GROUP BY `user_id`
ORDER BY `pkrecord`.`score` DESC 
LIMIT 9;
           

查詢結果

1. 在子查詢中對資料已經進行排序後，外層排序方式如果和子查詢排序分數相同，都是分數倒序，外層的排序可以去掉，沒有必要寫兩遍。

sql語句中的别名 别名在哪些情況使用

在 SQL 語句中，可以為表名稱及字段（列）名稱指定别名

• 表名稱指定别名

同時查詢兩張表的資料的時候：

未設定别名前：

SELECT article.title,article.content,user.username FROM article, user

WHERE article.aid=1 AND article.uid=user.uid
           

設定别名後：

SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid
           

好處：使用表别名查詢，可以使 SQL 變得簡潔而更易書寫和閱讀，尤其在 SQL 比較複雜的情況下

• 查詢字段指定别名

查詢一張表，直接對查詢字段設定别名

SELECT username AS name,email FROM user
           

查詢兩張表

好處：字段别名一個明顯的效果是可以自定義查詢資料傳回的字段名；當兩張表有相同的字段需要都被查詢出，使用别名可以完美的進行區分，避免沖突

SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid
           

• 關聯查詢時候，關聯表自身的時候，一些分類表，必須使用别名。
• 别名也可以在group by與having的時候都可使用

• 别名可以在order by排序的時候被使用

  檢視上面一段sql

• delete ， update MySQL都可以使用别名，别名在多表（級聯）删除尤為有用

delete t1,t2 from t_a t1 , t_b t2 where t1.id = t2.id
           

• 子查詢結果需要使用别名

  檢視上面一段sql

别名使用注意事項

• 雖然定義字段别名的 AS 關鍵字可以省略，但是在使用别名時候，建議不要省略 AS 關鍵字

書寫sql語句的注意事項 書寫規範上的注意

• 字元串類型的要加單引号
• select後面的每個字段要用逗号分隔，但是最後連着from的字段不要加逗号
• 使用子查詢建立臨時表的時候要使用别名，否則會報錯。

為了增強性能的注意

• 不要使

  select * from ……

  傳回所有列，隻檢索需要的列，可避免後續因表結構變化導緻的不必要的程式修改，還可降低額外消耗的資源
• 不要檢索已知的列

select user_id,name from User where user_id = ‘10000050’

• 使用可參數化的搜尋條件，如

  =

  ,

  >

  ,

  >=

  ,

  <

  ,

  <=

  ,

  between

  ,

  in

  ,

  is null

  以及

  like ‘<literal>%’

  ；盡量不要使用非參數化的負向查詢，這将導緻無法使用索引，如

  <>

  ,

  !=

  ,

  !>

  ,

  !<

  ,

  not in

  ,

  not like

  ,

  not exists

  ,

  not between

  ,

  is not null

  ,

  like ‘%<literal>’

• 當需要驗證是否有符合條件的記錄時，使用exists，不要使用

  count(*)

  ，前者在第一個比對記錄處傳回，後者需要周遊所有比對記錄
• Where子句中列的順序與需使用的索引順序保持一緻，不是所有資料庫的優化器都能對此順序進行優化，保持良好程式設計習慣（索引相關）
• 不要在where子句中對字段進行運算或函數（索引相關）

1. 如

   where amount / 2 > 100

   ，即使amount字段有索引，也無法使用，改成

   where amount > 100 * 2

   就可使用amount列上的索引
2. 如

   where substring( Lastname, 1, 1) = ‘F’

   就無法使用Lastname列上的索引，而

   where Lastname like ‘F%’

   或者

   where Lastname >= ‘F’ and Lastname < ‘G’

   就可以

• 在有min、max、distinct、order by、group by操作的列上建索引，避免額外的排序開銷（索引相關）
• 小心使用or操作，and操作中任何一個子句可使用索引都會提高查詢性能，但是or條件中任何一個不能使用索引，都将導緻查詢性能下降，如

  where member_no = 1 or provider_no = 1

  ，在member_no或provider_no任何一個字段上沒有索引，都将導緻表掃描或聚簇索引掃描（索引相關）
• Between一般比in/or高效得多，如果能在between和in/or條件中選擇，那麼始終選擇between條件，并用

  >=

  和

  <=

  條件組合替代between子句，因為不是所有資料庫的優化器都能把between子句改寫為

  >=

  和

  <=

  條件組合，如果不能改寫将導緻無法使用索引（索引相關）

• 調整join操作順序以使性能最優，join操作是自頂向下的，盡量把結果集小的兩個表關聯放在前面，可提高性能。（join相關）

  注意：索引和關聯我會單獨拿出來兩篇文章進行詳細講解，在這個注意事項中隻是簡單提一下。

看完三件事

如果你覺得這篇内容對你挺有啟發，我想邀請你幫我三個小忙：

點個贊或關注我，是對我的肯定，我會繼續努力寫好的文章

關注我的部落格 https://github.com/koala-coding/goodBlog，讓我們成為長期關系

關注「程式員成長指北」，持續為你推送優質精選好文，也可以加我為好友哦。

作者：koala_coding

連結：http://www.imooc.com/article/291452

來源：慕課網

本文原創釋出于慕課網 ，轉載請注明出處，謝謝合作