一、多表關聯(lián)查詢
多表關聯(lián)查詢是使用一條SQL語句，將關聯(lián)的多張表的數(shù)據(jù)查詢出來。
1.1 交叉查詢
交叉查詢就是將多張表的數(shù)據(jù)沒有條件地連接在一起進行展示。
1.1.1 語法
使用交叉查詢類別和商品
-- 目標：查詢所有分類，以及每個分類下的所有商品信息
SELECT tc.*,tp.* FROM t_category tc,t_product tp;
SELECT * FROM t_category,t_product;
-- 這個語句查詢出來是一個笛卡爾積，里面有很多錯誤的數(shù)據(jù)，所以不用，這種查詢叫做交叉查詢
通過查詢結果可以看到，交叉查詢其實是一種錯誤的做法，在查詢到的結果集中有大量的錯誤數(shù)據(jù)，稱交叉查詢到的結果集是笛卡爾積。
1.2 內(nèi)連接查詢
通常要查詢的多個表之間都存在關聯(lián)關系，通過關聯(lián)關系（主外鍵關系）去除笛卡爾積。這種通過條件過濾去除笛卡爾積的查詢，稱之為連接查詢。
連接查詢又分為內(nèi)連接查詢和外連接查詢。
1.2.1 隱式內(nèi)連接
-- 1. 內(nèi)連接查詢：滿足連接條件的數(shù)據(jù)才能查詢出來，不滿足連接條件的數(shù)據(jù)無法查詢出來
-- 1.1 隱式內(nèi)連接查詢：
-- select 要查詢的字段 from 主表，從表 where 從表的外鍵 = 主表的主鍵
SELECT tc.*,tp.* FROM t_category tc,t_product tp WHERE tp.cno = tc.cid;
1.2.2 顯式內(nèi)連接
-- 1.2 顯式內(nèi)連接查詢：
-- select 要查詢的字段 from 主表 [inner] join 從表 on 從表的外鍵 = 主表的主鍵
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid;
-- 查詢手機數(shù)碼分類下的所有商品的信息以及分類信息
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname='手機數(shù)碼';
1.2.3 內(nèi)連接查詢的特點
主表和從表的數(shù)據(jù)都是滿足連接條件則能夠查詢出來，不滿足連接條件則不會查詢出來。
1.3 外連接查詢
如果要保證查詢出某張表的全部數(shù)據(jù)情況下進行連接查詢，急需要使用外連接查詢。
外連接查詢分為左外連接和右外連接。
1.3.1 左外連接查詢
以join左邊的表為主表，展示主表的所有數(shù)據(jù)，根據(jù)條件查詢連接右邊表的數(shù)據(jù)，若滿足條件則展示，若不滿足則以null顯示。
可以理解為：在內(nèi)連接的基礎上保證左邊表的數(shù)據(jù)全部顯示。
-- 2. 外連接查詢:會查詢出主表的所有數(shù)據(jù)，從表的數(shù)據(jù)滿足條件則能查詢出來，不滿足則查詢不出來
-- 2.1 左外連接查詢：以join左邊的表作為主表，能查詢出左邊的表的所有數(shù)據(jù)
SELECT * FROM t_product tp LEFT OUTER JOIN t_category tc ON tp.cno = tc.cid;
1.3.2 右外連接查詢
以join右邊的表為主表，展示右邊表的所有數(shù)據(jù)，根據(jù)條件查詢join左邊表的數(shù)據(jù)，若滿足則展示，若不滿足則以null顯示。
可以理解為：在內(nèi)連接的基礎上保證右邊表的數(shù)據(jù)全部顯示。
-- 2.2 右外連接查詢：以join右邊的表作為主表，能查詢出右邊的表的所有數(shù)據(jù)
SELECT * FROM t_product tp RIGHT OUTER JOIN t_category tc ON tp.cno = tc.cid;
1.4 union聯(lián)合查詢
聯(lián)合查詢并不是多表連接查詢的一種方式。
聯(lián)合查詢是將多條查詢語句的查詢結果合并成一個結果并去掉重復數(shù)據(jù)。
全外連接查詢的意思就是將左表和右表的數(shù)據(jù)都查詢出來，然后按照連接條件連接。
-- 全外連接，使用union聯(lián)合查詢做全外連接
-- union是將多個select語句查詢到的結果進行合并，合并成一個結果，要求多個select語句查詢到的結果的格式是一樣的
SELECT * FROM t_product tp LEFT OUTER JOIN t_category tc ON tp.cno = tc.cid
UNION
SELECT * FROM t_product tp RIGHT OUTER JOIN t_category tc ON tp.cno = tc.cid
1.5 自連接查詢
自連接查詢是一種特殊的多表連接查詢，因為兩個關聯(lián)查詢的表是同一張表，通過取別名的方式來虛擬成兩張表，然后進行兩張表的連接查詢。
-- 員工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 員工id
ename VARCHAR(50), -- 員工姓名
mgr INT , -- 上級領導
joindate DATE, -- 入職日期
salary DECIMAL(7,2) -- 工資
);
-- 添加員工
INSERT INTO emp(id,ename,mgr,joindate,salary) VALUES
(1001,'孫悟空',1004,'2000-12-17','8000.00'),
(1002,'盧俊義',1006,'2001-02-20','16000.00'),
(1003,'林沖',1006,'2001-02-22','12500.00'),
(1004,'唐僧',1009,'2001-04-02','29750.00'),
(1005,'李逵',1006,'2001-09-28','12500.00'),
(1006,'宋江',1009,'2001-05-01','28500.00'),
(1007,'劉備',1009,'2001-09-01','24500.00'),
(1008,'豬八戒',1004,'2007-04-19','30000.00'),
(1009,'羅貫中',NULL,'2001-11-17','50000.00'),
(1010,'吳用',1006,'2001-09-08','15000.00'),
(1011,'沙僧',1004,'2007-05-23','11000.00'),
(1012,'李逵',1006,'2001-12-03','9500.00'),
(1013,'小白龍',1004,'2001-12-03','30000.00'),
(1014,'關羽',1007,'2002-01-23','13000.00');
-- 自連接查詢：表中的一個字段作為外鍵指向本表的主鍵
SELECT employee.*,manager.ename FROM emp employee,emp manager WHERE employee.mgr=manager.id AND employee.ename='孫悟空'
二、子查詢
如果一個查詢語句嵌套在另一個查詢語句里面，那么這個查詢語句就稱之為子查詢。
根據(jù)位置不同，可以分為where型、from型、exists型。
2.1 where型
- 子查詢是單值結果，那么可以對其使用（=，>等比較運算符）
- 子查詢是多值結果，那么可對其使用（[not] in （子查詢結果），或>all（子查詢結果），或>=all（子查詢結果），<all（子查詢結果），<=all（子查詢結果），或>any（子查詢結果），或>=any（子查詢結果），<any（子查詢結果），<=any（子查詢結果））
-- where型子查詢，是將子查詢語句放在where后面
-- 需求：查詢emp表中的薪資最高的那個員工的信息
-- where后面的條件中不能使用聚合函數(shù)
-- 第一步：查詢出最高的薪資
SELECT MAX(salary) FROM emp -- 50000
-- 第二步：根據(jù)上一步查詢出來的最高薪資，查詢具體的員工信息
SELECT * FROM emp WHERE salary = 50000
-- 兩步合成一步
SELECT * FROM emp WHERE salary = (SELECT MIN(salary) FROM emp)
-- 需求：查詢出手機數(shù)碼和食物分類下的所有商品信息
SELECT * FROM t_product WHERE cno IN(SELECT cid FROM t_category WHERE cname IN('手機數(shù)碼','食物'))
-- 需求：查詢出價格最高的商品信息
-- 思路一：匹配最高的價格
SELECT * FROM t_product WHERE price = (SELECT MAX(price) FROM t_product)
-- 思路二：按照價格的降序排列，取第一個
SELECT * FROM t_product ORDER BY price DESC LIMIT 0,1
-- 思路三：查詢價格大于所有商品的商品
SELECT * FROM t_product WHERE price>=ALL(SELECT price FROM t_product)
2.2 from型
子查詢的結果是多行多列的結果，類似于一張表格。
必須給子查詢?nèi)e名，即臨時表名，表的別名不要加""和空格。
-- from型的子查詢，這個針對的是子查詢的結果是多行、多列數(shù)據(jù)
-- 需求：查詢每個分類下的分類名、商品總數(shù)
-- 思路一：使用連接查詢
-- 使用外連接，查詢出分類表的所有數(shù)據(jù)
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT OUTER JOIN t_product tp ON tp.cno=tc.cid GROUP BY tc.cname
-- 思路二：使用子查詢
-- 第一步：對t_product根據(jù)cno進行分組查詢，統(tǒng)計每個分類的商品數(shù)量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步：用t_category表去連接第一步查詢出來的結果，進行連接查詢，此時要求查詢出所有分類
SELECT tc.cname '商品名稱',IFNULL(tn.total,0) '總數(shù)量' FROM t_category tc LEFT OUTER JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid
2.3 exists型
-- exists型子查詢
-- 需求：查詢那些有商品的分類
-- 連接分類表和商品表進行查詢
-- exists是如果子查詢語句能夠至少查詢到一條數(shù)據(jù)，就返回TRUE，否則就返回FALSE
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid)
