sql查詢學習和實踐點滴積累

世有因果知因求果發表於2017-01-31
SQL

https://blog.rjmetrics.com/2008/10/28/correlated-subqueries-in-mysql/

http://www.mysqltutorial.org/mysql-subquery/

SQL是關聯式資料庫中非常基礎同時也是非常重要的知識，雖然比如類似Laravel的後端開發類庫提供了ORM抽象資料類封裝掉了一部分簡單的sql查詢，因此很多時候我們無需關係sql的具體細節，便能非常快速地開發出自己的後端應用來，但是一旦涉及到相對比較複雜的關係時我們還是不得不再去求助於sql。本博作為雞年新年剛過，起個開端，不斷記錄積累本人在sql學習中覺得重要的點點滴滴，學習資源，備忘的同時，也希望給有緣人以幫助

SQL Subselect and correlated subquery

subquery就是被括號所包圍的一個被嵌入到另外一個SQL statement的statement。而包含這個subquery的statement我們通常稱為outer query， subquery本身被成為inner query.

A sub query is a nested query where the results of one query can be used in another query via a relational operator or aggregation function

和subquery有關的規則：

1. 一個subquery必須出現在()括號中；

2. 你可以在一個query中嵌入另外一個subquery,這個層級沒有限制；

3. 如果outer query對subquery期待（或者說引用）一個single value或者一系列value，那麼subquery只能使用one expression or column name in its select list; (a subquery can have only one column in the select clause if used in where clause)

4. orderby clause不允許在subquery中出現

5. subquery可以被用在where, having, from和select clause中

select t1.* from table1 t1 where t1.id not in (select t2.id from table2 t2): non-corelated subquery

 

http://www.geeksengine.com/database/subquery/return-single-value.php

.在大多數情況下，我們可以非常輕鬆地以JOIN語句來完全實現一個子select語句的功能（或者相反）。但是很多時候，和join方法相比,subquery來得更加通俗易懂，符合邏輯。比如像IN, ANY這些關鍵字的使用就往往使得語句更易理解和容易被分解。作為一個例子，我們來看下面問題的query語句:

" 列出所有在NJ州的客人名單"

select Name from Customers where CustomerID = ANY ( select CustomerID from AddressBook where state = 'NJ')

在這個例子中，括號中的黑體部分就是一個subquery.

這個subquery被我們稱做"Non-correlated" subquery,原因是你可以單獨執行這條select語句來得到一個合乎邏輯的正確的結果集.在這個例子中，隔離的subquery可以產生一個來自NJ州的客戶名單列表。

然而，相對於這種Non-correlated就有一種Correlated subquery,這種子查詢包含著outer query的value的引用，因此無法脫離outer query而單獨執行

我們舉一個典型的查詢例子：

select * from t1 where column1 = ANY ( SELECT column1 from t2 where t2.column2 = t1.column2)

注意在這個例子中，t1表雖然並未在subquery的from clause所指出但是卻被subquery中的where clause所引用，t1表只存在於outer query語句中，如果你直接執行這個隔離的subquery，你將由於無法找到t1表而出錯。

 和他們的non-correlated subquery例子，correlated subquery不允許在from clause中出現。這個關鍵規則,在mysql refman文件中甚至沒有提及，顯著地降低了correlated subquery可以應用的範圍。

那麼，如果一個correlated subquery不能在query的from clause中使用，那麼到底可以用在哪裡呢？

正如我們在例子中看到的一樣，correlated subquery可以在where clause中使用，同時就像non-correlated subquery一樣，它也可以在having clause或select clause中使用。

這些可以被允許出現的clause有什麼共同之處呢？答案可以說非常簡單：他們被用在資料已經被拉出來之後(after data is pulled)，要麼用於限制被選中的rows或者修改哪些columns被顯示出來!換句話說，如果你將subquery全部拉出去，你也可以獲得完全正確邏輯的結果（僅僅少掉subquery部分的邏輯）

 比如，我們看看下面兩個clause，一個correlated subquery可能會對他們的query結果有什麼影響：

首先，我們看看where clause的情況：

select Name from dogs where age>=5

在沒有where clause的情況下，query依然可以正確執行並且返回每一條狗的名字。然而，where clause的作用是僅僅剔除了那些年齡小於5的row

類似地，我們再看看select clause的情況下：

select Name, age*7 as humanage from dogs where age >=5

就算沒有age*7, 這個query也能夠獲取和上面的query相同結果的結果集。然而，在select clause中增加這個expressio則會在最後的結果集中的每一row中增加特定的資訊。

上面兩個例子有什麼共性呢？無論是在where clause的情況還是select expression的情況都是執行於一個除了where clause和select expression(age*7)外的其他query返回的結果上，他們只能limit rows(where)或者增加columns(select).無一例外，在where和select中出現的expression都是針對如果沒有他們時的query返回的結果集中的每一行來evaluated的.

所以，現在我們可以問這樣一個問題：

如果我們將where comparison或者select expression替換成一個correlated subquery結果會是怎樣？答案是相同的共性： subquery執行於將其剔除掉後的query返回的結果集中的每一行。這樣，對每一條row record,你可以在outer from clause中引用任何table的任何column的value. 看看下面的corelated subquery:

select Name from dogs d where (select max(HaircutDate) from haircuts h where h.Name = d.Name) < '2008-09-01'

這個query返回每一條自從2008年9月1日未理過發的狗的名字。當這個query被執行時，在where cluase中的subqury對於每一條剔除subquery外的其他query語句結果集中的每一條row都要被執行一遍。也就是說，如果在dogs表中有20個dogs記錄，mysql將執行這個subquery 20遍，針對每row每次執行時都將d.Name使用該row的dog name來替換後執行。

再看下面的查詢，我們將使用在select clause中的subquery來獲取每一條狗的最近harcut date:

select Name, (select max(HaircutDate) from haircuts h where h.Name = d.Name) as LastHaircut from dogs d

和普通的駐留於where和select clause中的subquery一樣，corelated subquery被設計為返回一系列標量值（而不是一表格的結果），或者有時為一行的值。 subquery-related keywords比如IN, ANY, SOME, EXISTS僅僅返回true或者false，而這非常適合使用where clause subquery

你也可以在UPDATE, DELETE STATEMENTS中的where clause中使用corelated subquery以便narrow down哪些row將可以被這條statement所影響。

 

下面的correlated query執行過程：

select distinct a.ProductID, 
       a.UnitPrice as Max_unit_price_sold
from order_details as a
where a.UnitPrice = 
(
    select max(UnitPrice)
    from order_details as b
    where a.ProductID = b.ProductID
)
order by a.ProductID;

    1. The outer query passes a value for ProductID to the subquery. It takes place in the WHERE clause in the subquery [ where a.ProductID = b.ProductID ]

    2. The subquery uses this passed-in ProductID value to look up the max unit price for this product
      [ select max(UnitPrice) from order_details ]

    3. When the max unit price for the product is found in the subquery, it's returned to the outer query.

      The outer query then uses this max unit price in its WHERE clause to match unit price in order_details table for this product [ where a.UnitPrice = ]

      When the row is found, query engine temporarily holds the row in memory. It's guaranteed that a row will be found because both outer query and subquery use the same table - order_details.

    4. The query engine then moves onto next row in the order_details table and repeat Step 1 to 3 again for the next product.

    5. When all products in order_details have been evaluated, it does a sorting and then returns the query result.

mysql查詢優化best practice

1. 只獲取app需要的row(使用where clause)

2. 只獲取app需要的column，避免使用select *

3. 避免多次獲取相同的資料，應該使用app的cache機制快取需要多次使用到的資料

4. 使用db的orderby in the select clause rather than app

5. 將大的delete, update, insert query分解成多個小的query

6. 所有的column使用適當的資料型別， smaller columns總是更快;

7. mysql query cache是case sensitive的

8. 將所有where clause中的column都增加index (具體要看explain命令檢視是否用到對應的index)

9. 將join中用到的column都做index

10. table order對於innder join clause是沒有關係的;

11. 使用limit clause來實現pagination 邏輯;

 

mysql查詢執行順序：

SQL JOIN:

SQL JOIN combines columns from two or more tables in a single result set.

inner join, outer join, cross join, self join, natural join

inner join返回rows when there is at least one match in both the tables.

應該避免ambiguity,通過alias table 的方法；

select t1.*, t2.* from table1 t1 inner join table2 t2 on t1.id = t2.id

left outer join returns all the rows from the left table with the matching rows from the right table, if there are no columns matching in the right table, it returns null values

right outer join returns all the rows from the right table with the matching rows from the left table, if there are no columns matching in the left table, it returns null values

full outer join: 由於mysql並不支援full outer join,因此我們必須使用left outer join和right outer join以及union來模擬full outer join

joins vs subquery

joins can include any columns from joining tables in the select clause

joins easy to read and more intuitive;

subquery can pass the aggregate values to the main query

select fm.title, cat.name,dt.countofcategory from film fm 

inner join film_category fc on fc.film_id = fm.film_id

inner join category cat on cat.category_id = fc.category_id

inner join(

select count(*) as countofcategory, fc.category_id from film_category fc

group by fc.category_id) dt on dt.category_id  = fc.category_id

 

相關文章