navicat循环执行上下两行相减sql语句_SQL太难?你离完全理解SQL就差这10步!
-?點擊上方“中國統計網”設置?星標不迷路!-
很多程序員視 SQL 為洪水猛獸。SQL 是一種為數不多的聲明性語言,它的運行方式完全不同于我們所熟知的命令行語言、面向對象的程序語言、甚至是函數語言(盡管有些人認為 SQL 語言也是一種函數式語言)。
我們每天都在寫 SQL 并且應用在開源軟件 jOOQ 中。于是我想把 SQL 之美介紹給那些仍然對它頭疼不已的朋友,所以本文是為了以下讀者而特地編寫的:
在工作中會用到 SQL 但是對它并不完全了解的人
能夠熟練使用 SQL 但是并不了解其語法邏輯的人
想要教別人 SQL 的人
1
SQL 是一種聲明式語言
首先要把這個概念記在腦中:“聲明”。SQL 語言是為計算機聲明了一個你想從原始數據中獲得什么樣的結果的一個范例,而不是告訴計算機如何能夠得到結果。這是不是很棒?(譯者注:簡單地說,SQL 語言聲明的是結果集的屬性,計算機會根據 SQL 所聲明的內容來從數據庫中挑選出符合聲明的數據,而不是像傳統編程思維去指示計算機如何操作。)SELECT first_name, last_name FROM employees WHERE salary > 100000上面的例子很容易理解,我們不關心這些雇員記錄從哪里來,我們所需要的只是那些高薪者的數據(譯者注:salary>100000 )。?我們從哪兒學習到這些?如果 SQL 語言這么簡單,那么是什么讓人們“聞 SQL 色變”?主要的原因是:我們潛意識中的是按照命令式編程的思維方式思考問題的。就好像這樣:“電腦,先執行這一步,再執行那一步,但是在那之前先檢查一下是否滿足條件 A 和條件 B ”。例如,用變量傳參、使用循環語句、迭代、調用函數等等,都是這種命令式編程的思維慣式。2
SQL 的語法并不按照語法順序執行
SQL 語句有一個讓大部分人都感到困惑的特性,就是:SQL 語句的執行順序跟其語句的語法順序并不一致。SQL 語句的語法順序是:SELECT[DISTINCT]
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY
3
?SQL 語言的核心是對表的引用
由于 SQL 語句語法順序和執行順序的不同,很多同學會認為SELECT 中的字段信息是 SQL 語句的核心。其實真正的核心在于對表的引用。根據 SQL 標準,FROM 語句被定義為:<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ]FROM 語句的“輸出”是一張聯合表,來自于所有引用的表在某一維度上的聯合。我們們慢慢來分析:FROM a, b上面這句 FROM 語句的輸出是一張聯合表,聯合了表 a 和表 b 。如果 a 表有三個字段, b 表有 5 個字段,那么這個“輸出表”就有 8 ( =5+3)個字段。這個聯合表里的數據是 a*b,即 a 和 b 的笛卡爾積。換句話說,也就是 a 表中的每一條數據都要跟 b 表中的每一條數據配對。如果 a 表有3 條數據, b 表有 5 條數據,那么聯合表就會有 15 ( =5*3)條數據。FROM 輸出的結果被 WHERE 語句篩選后要經過 GROUP BY 語句處理,從而形成新的輸出結果。我們后面還會再討論這方面問題。如果我們從集合論(關系代數)的角度來看,一張數據庫的表就是一組數據元的關系,而每個 SQL 語句會改變一種或數種關系,從而產生出新的數據元的關系(即產生新的表)。?我們學到了什么?思考問題的時候從表的角度來思考問題提,這樣很容易理解數據如何在 SQL 語句的“流水線”上進行了什么樣的變動。4
靈活引用表能使 SQL 語句變得更強大
靈活引用表能使 SQL 語句變得更強大。一個簡單的例子就是 JOIN 的使用。嚴格的說 JOIN 語句并非是 SELECT 中的一部分,而是一種特殊的表引用語句。SQL 語言標準中表的連接定義如下:<table reference> ::= <table name> | <derived table> | <joined table>就拿之前的例子來說:FROM a, ba 可能輸入下表的連接:a1 JOIN a2 ON a1.id = a2.id將它放到之前的例子中就變成了:FROM a1 JOIN a2 ON a1.id = a2.id, b盡管將一個連接表用逗號跟另一張表聯合在一起并不是常用作法,但是你的確可以這么做。結果就是,最終輸出的表就有了 a1+a2+b 個字段了。(譯者注:原文這里用詞為 degree ,譯為維度。如果把一張表視圖化,我們可以想象每一張表都是由橫縱兩個維度組成的,橫向維度即我們所說的字段或者列,英文為columns;縱向維度即代表了每條數據,英文為 record ,根據上下文,作者這里所指的應該是字段數。)在SQL語句中派生表的引用甚至比表連接更加強大,下面我們就要講到表連接。?我們學到了什么?思考問題時,要從表引用的角度出發,這樣就很容易理解數據是怎樣被 SQL 語句處理的,并且能夠幫助你理解那些復雜的表引用是做什么的。更重要的是,要理解 JOIN 是構建連接表的關鍵詞,并不是 SELECT 語句的一部分。有一些數據庫允許在 INSERT 、 UPDATE 、 DELETE 中使用 JOIN 。5
SQL 語句中推薦使用表連接
我們先看看剛剛這句:FROM a, b高級 SQL 程序員也許學會給你忠告:盡量不要使用逗號來代替 JOIN 進行表的連接,這樣會提高你的 SQL 語句的可讀性,并且可以避免一些錯誤。利用逗號來簡化 SQL 語句有時候會造成思維上的混亂,想一下下面的語句:FROM a, b, c, d, e, f, g, hWHERE a.a1 = b.bxAND a.a2 = c.c1AND d.d1 = b.bc-- etc...我們不難看出使用 JOIN 語句的好處在于:安全。JOIN 和要連接的表離得非常近,這樣就能避免錯誤
更多連接的方式,JOIN 語句能去區分出來外連接和內連接等
6
簡約框線標題
SQL 語句中,表連接的方式從根本上分為五種:EQUI JOIN
SEMI JOIN
ANTI JOIN
CROSS JOIN
DIVISION
INNER JOIN(或者是 JOIN )
OUTER JOIN(包括:LEFT 、 RIGHT、 FULL OUTER JOIN)
IN比 EXISTS 的可讀性更好
EXISTS 比IN 的表達性更好(更適合復雜的語句)
二者之間性能沒有差異(但對于某些數據庫來說性能差異會非常大)
SQL 語句性能低下:因為去重操作( DISTINCT )需要數據庫重復從硬盤中讀取數據到內存中。(譯者注:DISTINCT 的確是一種很耗費資源的操作,但是每種數據庫對于 DISTINCT 的操作方式可能不同)。
這么寫并非完全正確:盡管也許現在這么寫不會出現問題,但是隨著 SQL 語句變得越來越復雜,你想要去重得到正確的結果就變得十分困難。
7
SQL 中如同變量的派生表
在這之前,我們學習到過 SQL 是一種聲明性的語言,并且 SQL 語句中不能包含變量。但是你能寫出類似于變量的語句,這些就叫做派生表:說白了,所謂的派生表就是在括號之中的子查詢:-- A derived tableFROM (SELECT * FROM author)需要注意的是有些時候我們可以給派生表定義一個相關名(即我們所說的別名)。-- A derived table with an aliasFROM (SELECT * FROM author) a派生表可以有效的避免由于 SQL 邏輯而產生的問題。舉例來說:如果你想重用一個用 SELECT 和 WHERE 語句查詢出的結果,這樣寫就可以(以 Oracle 為例):-- Get authors' first and last names, and their age in daysSELECT first_name, last_name, ageFROM ( SELECT first_name, last_name, current_date - date_of_birth age FROM author)-- If the age is greater than 10000 daysWHERE age > 10000需要我們注意的是:在有些數據庫,以及 SQL :1990 標準中,派生表被歸為下一級——通用表語句( common table experssion)。這就允許你在一個 SELECT 語句中對派生表多次重用。上面的例子就(幾乎)等價于下面的語句:WITH a AS ( SELECT first_name, last_name, current_date - date_of_birth age FROM author)SELECT *FROM aWHERE age > 10000?我們學到了什么?我們反復強調,大體上來說 SQL 語句就是對表的引用,而并非對字段的引用。要好好利用這一點,不要害怕使用派生表或者其他更復雜的語句。8
SQL 語句中GROUP BY是對表的引用進行的操作
讓我們再回想一下之前的 FROM 語句:FROM a, b現在,我們將 GROUP BY 應用到上面的語句中:GROUP BY A.x, A.y, B.z上面語句的結果就是產生出了一個包含三個字段的新的表的引用。我們來仔細理解一下這句話:當你應用 GROUP BY 的時候, SELECT 后沒有使用聚合函數的列,都要出現在 GROUP BY 后面。(譯者注:原文大意為“當你是用 GROUP BY 的時候,你能夠對其進行下一級邏輯操作的列會減少,包括在 SELECT 中的列”)。需要注意的是:其他字段能夠使用聚合函數:SELECT A.x, A.y, SUM(A.z)FROM AGROUP BY A.x, A.y還有一點值得留意的是:MySQL 并不堅持這個標準,這的確是令人很困惑的地方。譯者注:這并不是說 MySQL 沒有 GROUP BY 的功能)但是不要被 MySQL 所迷惑。GROUP BY 改變了對表引用的方式。你可以像這樣既在 SELECT 中引用某一字段,也在 GROUP BY 中對其進行分組。?我們學到了什么?GROUP BY,再次強調一次,是在表的引用上進行了操作,將其轉換為一種新的引用方式。9
SQL 語句中的 SELECT 實質上是對關系的映射
我個人比較喜歡“映射”這個詞,尤其是把它用在關系代數上。(譯者注:原文用詞為 projection ,該詞有兩層含義,第一種含義是預測、規劃、設計,第二種意思是投射、映射,經過反復推敲,我覺得這里用映射能夠更直觀的表達出 SELECT 的作用)。一旦你建立起來了表的引用,經過修改、變形,你能夠一步一步的將其映射到另一個模型中。SELECT 語句就像一個“投影儀”,我們可以將其理解成一個將源表中的數據按照一定的邏輯轉換成目標表數據的函數。通過 SELECT語句,你能對每一個字段進行操作,通過復雜的表達式生成所需要的數據。SELECT 語句有很多特殊的規則,至少你應該熟悉以下幾條:你僅能夠使用那些能通過表引用而得來的字段
如果你有 GROUP BY 語句,你只能夠使用 GROUP BY 語句后面的字段或者聚合函數
當你的語句中沒有 GROUP BY 的時候,可以使用開窗函數代替聚合函數
當你的語句中沒有 GROUP BY 的時候,你不能同時使用聚合函數和其它函數
有一些方法可以將普通函數封裝在聚合函數中
憑直覺,這種做法從邏輯上就講不通
如果直覺不能夠說服你,那么語法肯定能。SQL : 1999 標準引入了 GROUPING SETS,SQL:2003 標準引入了 group sets : GROUP BY()?
10
SQL 語句中的幾個簡單的關鍵詞:DISTINCT,UNION ,ORDER BY 和 OFFSET
在學習完復雜的 SELECT 豫劇之后,我們再來看點簡單的東西:集合運算( DISTINCT 和 UNION )
排序運算( ORDER BY,OFFSET…FETCH)
集合運算( set operation)
DISTINCT 在映射之后對數據進行去重
UNION 將兩個子查詢拼接起來并去重
UNION ALL 將兩個子查詢拼接起來但不去重
EXCEPT 將第二個字查詢中的結果從第一個子查詢中去掉
INTERSECT 保留兩個子查詢中都有的結果并去重
往期小編推薦??
點擊標題即可閱讀??
干貨技巧丨數據崗位常考!如何用Vlookup數組公式做逆向查找
理論知識丨10個最經典的數據分析模型,你集齊了幾個
學員月考丨4道SQL題 | 快速入門數據分析師面試
點擊“在看” 讓更多朋友看到好內容!總結
以上是生活随笔為你收集整理的navicat循环执行上下两行相减sql语句_SQL太难?你离完全理解SQL就差这10步!的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: js监听地址栏变化_vue中本地储存也可
- 下一篇: 基于junit4的关于个人所得税计算的等