Sql在多張表中檢索數(shù)據(jù)的方法詳解
1.內(nèi)連接
各表分開(kāi)存放是為了減少重復(fù)信息和方便修改,需要時(shí)可以根據(jù)相互之間的關(guān)系連接成相應(yīng)的合并詳情表以滿(mǎn)足相應(yīng)的查詢(xún)。FROM JOIN ON 語(yǔ)句就是告訴sql: 將哪幾張表以什么基礎(chǔ)連接/合并起來(lái)。
select * from order inner join customers on order.id = customers.id; -- inner可省略 -- 可起別名 select order_id, customer_id, o.id, last_name from order o join customers c on o.id = c.id;
2.跨數(shù)據(jù)庫(kù)連接
有時(shí)需要選取不同庫(kù)的表的列,其他都一樣,就只是WHERE JOIN里對(duì)于非現(xiàn)在正在用的庫(kù)的表要加上庫(kù)名前綴而已。依然可用別名來(lái)簡(jiǎn)化
use sql_store; select * from order_items oi join sql_inventory.products p on oi.product_id = p.product_id -- 或 use sql_inventory; select * from sql_store.order_items oi join products p on oi.product_id = p.product_id -- 非當(dāng)前使用的庫(kù),才需要加庫(kù)前綴
3.自連接
一個(gè)表和它自己合并。如員工的上級(jí)也是員工,所以也在員工表里,所以想得到的有員工和他的上級(jí)信息的合并表,就要員工表自己和自己合并,用兩個(gè)不同的表別名即可實(shí)現(xiàn)。這個(gè)例子中只有兩級(jí),但也可用類(lèi)似的方法構(gòu)建多層級(jí)的組織結(jié)構(gòu)。
USE sql_hr select e.employee_id, e.first_name, m.first_name as manger from employees e join employee m on e.reports_to = m.employee_id
4.多表連接
FROM
一個(gè)核心表A,用多個(gè) JOIN …… ON ……
分別通過(guò)不同的鏈接關(guān)系鏈接不同的表B、C、D……,通常是讓表B、C、D……為表A提供更詳細(xì)的信息從而合并為一張?jiān)斍楹喜鍭表,即:
FROM A
JOIN B ON AB的關(guān)系
JOIN C ON AC的關(guān)系
JOIN D ON AD的關(guān)系
……
將得到一個(gè)合并了BCD……等表詳細(xì)信息的詳情合并版A表
真實(shí)工作場(chǎng)景中有時(shí)甚至要合并十多張表
-- 訂單表同時(shí)鏈接顧客表和訂單狀態(tài)表,合并為有顧客和狀態(tài)信息的詳細(xì)訂單表 USE sql_store; SELECT o.order_id, o.order_date, c.first_name, c.last_name, os.name AS status FROM order o JOIN customers c on o.customers_id = c.customers_id JOIN order_statuses os ON o.status = os.order_status_id;
5.復(fù)合連接條件
像訂單項(xiàng)目(order_items)這種表,訂單id和產(chǎn)品id合在一起才能唯一表示一條記錄,這叫復(fù)合主鍵,設(shè)計(jì)模式下也可以看到兩個(gè)字段都有PK標(biāo)識(shí),訂單項(xiàng)目備注表(order_item_notes)也是這兩個(gè)復(fù)合主鍵,因此他們兩合并時(shí)要用復(fù)合條件:FROM
表1 JOIN
表2 ON
條件1 【AND】
條件2
USE sql_store SELECT * FROM order_items oi JOIN order_item_notes oin ON oi.order_Id = oin.order_Id AND oi.product_id = oin.product_id
6.隱式連接語(yǔ)法
就是用FROM WHERE取代FROM JOIN ON
盡量別用,因?yàn)槿敉沇HERE條件篩選語(yǔ)句,不會(huì)報(bào)錯(cuò)但會(huì)得到交叉合并(cross join)結(jié)果:即10條order會(huì)分別與10個(gè)customer結(jié)合,得到100條記錄。最好使用顯性合并語(yǔ)法,因?yàn)闀?huì)強(qiáng)制要求你寫(xiě)合并條件ON語(yǔ)句,不至于漏掉。
USE sql_store; SELECT * FROM orders o JOIN customers c on o.id = c.id; -- 可轉(zhuǎn)換為下面這個(gè) SELECT * FROM orders
7.外連接
(INNER) JOIN只包含兩個(gè)表的交集
LEFT/RIGHT (OUTER) JOIN
結(jié)果里除了交集,還包含只出現(xiàn)在左/右表中的記錄
-- 合并顧客表和訂單表,用INNER JOIN USE sql_store; SELECT c.customer_id, c.first_name, o.order_id FROM customers c JOIN orders o ON o.customer_id = c.customer_id ORDER BY customer_id -- 這樣是INNER JOIN,只展示有訂單的顧客(及其訂單),也就是兩張表的交集,但注意這里因?yàn)橐粋€(gè)顧客可能有多個(gè)訂單,所以INNER JOIN以后顧客信息其實(shí)是是廣播了的,即一條顧客信息被多條訂單記錄共用,當(dāng)然 這叫廣播(broadcast)效應(yīng),是另一個(gè)問(wèn)題,這里關(guān)注的重點(diǎn)是 INNER JOIN 的結(jié)果確實(shí)是兩表的交集,是那些同時(shí)有顧客信息和訂單信息的記錄。 -- 若要展示全部顧客(及其訂單,如果有的話(huà)),要改用LEFT (OUTER) JOIN,結(jié)果相較于 INNER JOIN 多了沒(méi)有訂單的那些顧客,即只有顧客信息沒(méi)有訂單信息的記錄 -- 當(dāng)然,也可以調(diào)換左右表的順序(即調(diào)換FROM和JOIN的對(duì)象)再 RIGHT JOIN,即: FROM orders o RIGHT [OUTER] JOIN customers c -- 中括號(hào) [] 表示是可選項(xiàng)、可省略 ON o.customer_id = c.customer_id -- 若要展示全部訂單(及其顧客),就應(yīng)該是 orders RIGHT JOIN customers,結(jié)果相較于 INNER JOIN 多了沒(méi)有顧客的那些訂單,即只有訂單信息沒(méi)有顧客信息的記錄。(注:因?yàn)檫@里所有訂單都有顧客,所以這里 RIGHT JOIN 結(jié)果和 INNER JOIN 一樣)
8.多表外連接
與內(nèi)連接類(lèi)似,我們可以對(duì)多個(gè)表(3個(gè)及以上)進(jìn)行外連接,最好只用 JOIN 和 LEFT JOIN
SELECT c.customer_id, c.first_name, o.order_id, sh.name AS shipper FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id ORDER BY customer_id -- 雖然可以調(diào)換順序并用 RIGHT JOIN,但作為最佳實(shí)踐,最好調(diào)整順序并統(tǒng)一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN(總是左表全包含),這樣,當(dāng)要合并的表比較多時(shí)才方便書(shū)寫(xiě)和理解而不易混亂
9.自外部連接
就用前面那個(gè)員工表的例子來(lái)說(shuō),就是用LEFT JOIN讓得到的 員工-上級(jí) 合并表也包括老板本人(老板沒(méi)有上級(jí),即 reports_to 字段為空,如果用 JOIN 會(huì)被篩掉,用 LEFT JOIN 才能保留)
USE sql_hr; SELECT e.employee_id, e.first_name, m.first_name AS manager FROM employees e LEFT JOIN employees m -- 包含所有雇員(包括沒(méi)有report_to的老板本人) ON e.reports_to = m.employee_id
10.USING子句
當(dāng)作為合并條件(join condition)的列在兩個(gè)表中有相同的列名時(shí),可用 USING (……, ……)
取代 ON …… AND ……
予以簡(jiǎn)化,內(nèi)/外鏈接均可如此簡(jiǎn)化。
一定注意 USING 后接的是括號(hào),特容易搞忘
SELECT o.order_id, c.first_name, sh.name AS shipper FROM orders o JOIN customers c USING (customer_id) LEFT JOIN shippers sh USING (shipper_id) ORDER BY order_id -- 復(fù)合主鍵表間復(fù)合連接條件的合并也可用 USING,中間逗號(hào)隔開(kāi)就行: SELECT * FROM order_items oi JOIN order_item_notes oin ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id /USING (order_id, product_id) -- USING對(duì)復(fù)合主鍵的簡(jiǎn)化效果更加明顯 -- 列名不同就必須用 ON …… 了 -- 實(shí)際中同一個(gè)字段在不同表列名不同的情況也很常見(jiàn),不能想當(dāng)然的用USING
11.自然連接
NATURAL JOIN
就是讓MySQL自動(dòng)檢索同名列作為合并條件。
最好別用,因?yàn)椴淮_定合并條件是否找對(duì)了,有時(shí)會(huì)造成無(wú)法預(yù)料的問(wèn)題,編程時(shí)保持對(duì)結(jié)果的控制是非常重要的
但也要知道有這個(gè)東西,混個(gè)臉熟,不要?jiǎng)e人用了看不懂。
USE sql_store; SELECT o.order_id, c.first_name FROM orders o NATURAL JOIN customers c
12.交叉連接
得到名字和產(chǎn)品的所有組合,因此不需要合并條件。 實(shí)際運(yùn)用如:要得到尺寸和顏色的全部組合
-- 得到顧客和產(chǎn)品的全部組合(毫無(wú)意義,純粹為了展示交叉連接) USE sql_store; SELECT c.first_name AS customer, p.name AS product FROM customers c CROSS JOIN products p ORDER BY c.first_name -- 上面是顯性語(yǔ)法,還有隱式語(yǔ)法,之前講過(guò),其實(shí)就是隱式內(nèi)合并忽略WHERE子句(即合并條件)的情況,也就是把 CROSS JOIN 改為逗號(hào),即 FROM A CROSS JOIN B 等效于 FROM A, B,Mosh更推薦顯式語(yǔ)法,因?yàn)楦逦? USE sql_store; SELECT c.first_name, p.name FROM customers c, products p ORDER BY c.first_name
13.聯(lián)合
FROM …… JOIN ……
可對(duì)多張表進(jìn)行橫向列合并,而 …… UNION ……
可用來(lái)按行縱向合并多個(gè)查詢(xún)結(jié)果,這些查詢(xún)結(jié)果可能來(lái)自相同或不同的表
- 同一張表可通過(guò)UNION添加新的分類(lèi)字段,即先通過(guò)分類(lèi)查詢(xún)并添加新的分類(lèi)字段再UNION合并為帶分類(lèi)字段的新表。
- 不同表通過(guò)UNION合并的情況如:將一張18年的訂單表和19年的訂單表縱向合并起來(lái)在一張表里展示
注意
- 合并的查詢(xún)結(jié)果必須列數(shù)相等,否則會(huì)報(bào)錯(cuò)
- 合并表里的列名由排在 UNION 前面的決定
-- 給訂單表增加一個(gè)新字段——status,用以區(qū)分今年的訂單和今年以前的訂單 USE sql_store; SELECT order_id, order_date, 'Active' AS status FROM orders WHERE order_date >= '2019-01-01' UNION SELECT order_id, order_date, 'Archived' AS status -- Archived 歸檔 FROM orders WHERE order_date < '2019-01-01';
到此這篇關(guān)于Sql在多張表中檢索數(shù)據(jù)的方法詳解的文章就介紹到這了,更多相關(guān)Sql檢索數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
提高M(jìn)ySQL 查詢(xún)效率的三個(gè)技巧
MySQL由于它本身的小巧和操作的高效, 在數(shù)據(jù)庫(kù)應(yīng)用中越來(lái)越多的被采用.我在開(kāi)發(fā)一個(gè)P2P應(yīng)用的時(shí)候曾經(jīng)使用MySQL來(lái)保存P2P節(jié)點(diǎn),由于P2P的應(yīng)用中,結(jié)點(diǎn)數(shù)動(dòng)輒上萬(wàn)個(gè),而且節(jié)點(diǎn)變化頻繁,因此一定要保持查詢(xún)和插入的高效.以下是我在使用過(guò)程中做的提高效率的三個(gè)有效的嘗試. 1. 使用statement進(jìn)行綁定查詢(xún) 2. 隨機(jī)的獲取記錄 3. 使用連接池管理連接.2008-04-04Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式
這篇文章主要介紹了Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-06-06Access數(shù)據(jù)庫(kù)的存儲(chǔ)上限
Access數(shù)據(jù)庫(kù)的存儲(chǔ)上限...2006-09-09MySQL的安全問(wèn)題從安裝開(kāi)始說(shuō)起
本篇文章小編為大家介紹,關(guān)于MySQL的安全問(wèn)題從安裝開(kāi)始說(shuō)起,有需要的朋友可以參考一下2013-04-04關(guān)于case when語(yǔ)句的報(bào)錯(cuò)問(wèn)題詳解
SQL CASE 表達(dá)式是一種通用的條件表達(dá)式,類(lèi)似于其它語(yǔ)言中的 if/else 語(yǔ)句,下面這篇文章主要給大家介紹了關(guān)于case when語(yǔ)句的報(bào)錯(cuò)問(wèn)題的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2018-08-08MyBatis攔截器實(shí)現(xiàn)分頁(yè)功能的實(shí)現(xiàn)方法
這篇文章主要介紹了MyBatis攔截器實(shí)現(xiàn)分頁(yè)功能的實(shí)現(xiàn)方法的相關(guān)資料,希望通過(guò)本文大家能夠?qū)崿F(xiàn)這樣的方法,需要的朋友可以參考下2017-10-10MySQL 5.7之關(guān)于SQL_MODE的設(shè)置
這篇文章主要介紹了MySQL 5.7之關(guān)于SQL_MODE的設(shè)置方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-08-08