-- 目標(biāo)：查詢所有分類，以及每個(gè)分類下的所有商品信息
SELECT tc.*,tp.* FROM t_category tc,t_product tp;

SELECT * FROM t_category,t_product;
-- 這個(gè)語(yǔ)句查詢出來(lái)是一個(gè)笛卡爾積，里面有很多錯(cuò)誤的數(shù)據(jù)，所以不用，這種查詢叫做交叉查詢

-- 1. 內(nèi)連接查詢：滿足連接條件的數(shù)據(jù)才能查詢出來(lái)，不滿足連接條件的數(shù)據(jù)無(wú)法查詢出來(lái)
-- 1.1 隱式內(nèi)連接查詢：
-- select 要查詢的字段 from 主表，從表 where 從表的外鍵 = 主表的主鍵
SELECT tc.*,tp.* FROM t_category tc,t_product tp WHERE tp.cno = tc.cid;

-- 1.2 顯式內(nèi)連接查詢：
-- select 要查詢的字段 from 主表 [inner] join 從表 on 從表的外鍵 = 主表的主鍵
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid;

-- 查詢手機(jī)數(shù)碼分類下的所有商品的信息以及分類信息
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname='手機(jī)數(shù)碼';

-- 2. 外連接查詢:會(huì)查詢出主表的所有數(shù)據(jù)，從表的數(shù)據(jù)滿足條件則能查詢出來(lái)，不滿足則查詢不出來(lái)
-- 2.1 左外連接查詢：以join左邊的表作為主表，能查詢出左邊的表的所有數(shù)據(jù)
SELECT * FROM t_product tp LEFT OUTER JOIN t_category tc ON tp.cno = tc.cid;

-- 2.2 右外連接查詢：以join右邊的表作為主表，能查詢出右邊的表的所有數(shù)據(jù)
SELECT * FROM t_product tp RIGHT OUTER JOIN t_category tc ON tp.cno = tc.cid;

-- 全外連接，使用union聯(lián)合查詢做全外連接
-- union是將多個(gè)select語(yǔ)句查詢到的結(jié)果進(jìn)行合并，合并成一個(gè)結(jié)果，要求多個(gè)select語(yǔ)句查詢到的結(jié)果的格式是一樣的
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

-- 員工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 員工id
  ename VARCHAR(50), -- 員工姓名
  mgr INT , -- 上級(jí)領(lǐng)導(dǎo)
  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,'關(guān)羽',1007,'2002-01-23','13000.00');


-- 自連接查詢：表中的一個(gè)字段作為外鍵指向本表的主鍵
SELECT employee.*,manager.ename FROM emp employee,emp manager WHERE employee.mgr=manager.id AND employee.ename='孫悟空'

-- where型子查詢，是將子查詢語(yǔ)句放在where后面
-- 需求：查詢emp表中的薪資最高的那個(gè)員工的信息
-- where后面的條件中不能使用聚合函數(shù)
-- 第一步：查詢出最高的薪資
SELECT MAX(salary) FROM emp   -- 50000

-- 第二步：根據(jù)上一步查詢出來(lái)的最高薪資，查詢具體的員工信息
SELECT * FROM emp WHERE salary = 50000

-- 兩步合成一步
SELECT * FROM emp WHERE salary = (SELECT MIN(salary) FROM emp)

-- 需求：查詢出手機(jī)數(shù)碼和食物分類下的所有商品信息
SELECT * FROM t_product WHERE cno IN(SELECT cid FROM t_category WHERE cname IN('手機(jī)數(shù)碼','食物'))

-- 需求：查詢出價(jià)格最高的商品信息
-- 思路一：匹配最高的價(jià)格
SELECT * FROM t_product WHERE price = (SELECT MAX(price) FROM t_product)

-- 思路二：按照價(jià)格的降序排列，取第一個(gè)
SELECT * FROM t_product ORDER BY price DESC LIMIT 0,1

-- 思路三：查詢價(jià)格大于所有商品的商品
SELECT * FROM t_product WHERE price>=ALL(SELECT price FROM t_product)

-- from型的子查詢，這個(gè)針對(duì)的是子查詢的結(jié)果是多行、多列數(shù)據(jù)
-- 需求：查詢每個(gè)分類下的分類名、商品總數(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

-- 思路二：使用子查詢
-- 第一步：對(duì)t_product根據(jù)cno進(jìn)行分組查詢，統(tǒng)計(jì)每個(gè)分類的商品數(shù)量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步：用t_category表去連接第一步查詢出來(lái)的結(jié)果，進(jìn)行連接查詢，此時(shí)要求查詢出所有分類
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

-- exists型子查詢
-- 需求：查詢那些有商品的分類
-- 連接分類表和商品表進(jìn)行查詢
-- exists是如果子查詢語(yǔ)句能夠至少查詢到一條數(shù)據(jù)，就返回TRUE，否則就返回FALSE
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid)