來源：數(shù)據(jù)STUDIO

作者：云朵君

查詢"01"課程比"02"課程成績高的學(xué)生信息及課程分?jǐn)?shù)

分析

第一步：根據(jù)需要查詢的最終結(jié)果確認(rèn)所需用到的表："學(xué)生信息及課程分?jǐn)?shù)"，需要用到學(xué)生信息表與成績表；

第二步：確認(rèn)條件："01"課程比"02"課程成績高的學(xué)生，需要先分別查出"01"課程的成績與"02"課程，再根據(jù)條件"成績更高"篩選出學(xué)生；

 #查詢01課程的成績  SELECT * FROM sc WHERE c_id='01';
  
  #查詢02課程的成績  SELECT * FROM sc WHERE c_id='02';

第三步：查詢：根據(jù)第二步篩選出的學(xué)生，查詢出對應(yīng)學(xué)生的"學(xué)生信息及課程分?jǐn)?shù)"。

語句

SELECT stu.*,sc.c_id,sc.score FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id WHERE t1.score>t2.score;

結(jié)果

s_ids_names_births_sexc_idscore02錢電1990-12-21男017002錢電1990-12-21男026002錢電1990-12-21男038004李云1990-08-06男015004李云1990-08-06男023004李云1990-08-06男0320

如果要求課程分?jǐn)?shù)需要"課程名稱"與"對應(yīng)分?jǐn)?shù)"，則還需要跟課程表連接以顯示課程名及分?jǐn)?shù)。

語句

SELECT stu.*,co.c_name,sc.score FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id JOIN co ON sc.c_id=co.c_id WHERE t1.score>t2.score;

結(jié)果

s_ids_names_births_sexc_namescore02錢電1990-12-21男語文7004李云1990-08-06男語文5002錢電1990-12-21男數(shù)學(xué)6004李云1990-08-06男數(shù)學(xué)3002錢電1990-12-21男英語8004李云1990-08-06男英語20

一、利用子查詢進(jìn)行連接表

以上例子中先分別查出"01"課程的成績與"02"課程，再根據(jù)查詢出的結(jié)果去查詢對應(yīng)學(xué)生信息及課程成績，即用到本節(jié)將要介紹的子查詢。

在SELECT語句中，子查詢總是從內(nèi)向外處理。在處理上面的SELECT語句時，MySQL實(shí)際上執(zhí)行了兩個操作。

首先，它執(zhí)行下面的查詢：

SELECT t1.* FROM (SELECT * FROM sc WHERE c_id='01') t1 JOIN (SELECT * FROM sc WHERE c_id='02') t2 ON t1.s_id=t2.s_id WHERE t1.score>t2.score;

此時得到兩個學(xué)生編號s_id等于02和04表格：

s_idc_idscore020170040150

然后，將得到的表格t與另外兩個表格連接后再查詢。

SELECT stu.*,sc.c_id,sc.score FROM t JOIN stu ON t1.s_id=stu.s_id JOIN sc ON stu.s_id=sc.s_id

其實(shí)這里也分了兩步：

SELECT stu.*,sc.c_id,sc.score FROM t JOIN stu ON t1.s_id=stu.s_id

結(jié)果：

s_ids_names_births_sexc_idscore02錢電1990-12-21男017004李云1990-08-06男0150

這里只得到了這兩個學(xué)生的課程1的成績，結(jié)果需要查詢到這兩個學(xué)生所有課程的成績，因此需要將上述得到的表格tt再與成績表連接。

SELECT stu.*,sc.c_id,sc.score FROM tt JOIN sc ON stu.s_id=sc.s_id

格式化SQL 包含子查詢的SELECT 語句難以閱讀和調(diào)試，特別是它們較為復(fù)雜時更是如此。如上所示把子查詢分解為多行并且適當(dāng)?shù)剡M(jìn)行縮進(jìn)，能極大地簡化子查詢的使用。

二、利用子查詢進(jìn)行過濾

查詢沒學(xué)過"張三"老師授課的同學(xué)的信息

首先需要查詢出"張三"老師授課信息：

SELECT stu.s_id FROM te LEFT JOIN co ON te.t_id=co.t_id LEFT JOIN sc ON co.c_id=sc.c_id LEFT JOIN stu ON sc.s_id=stu.s_id WHERE t_name='張三';

得到"張三"老師授課信息s_id為01，02，03，04，05，07。然后，這兩個值以 NOT IN 操作符要求的逗號分隔的格式傳遞給外部查詢的 WHERE 子句。

外部查詢變成：

SELECT * FROM stu WHERE s_id NOT IN (01,02,03,04,05,07);

結(jié)果：

s_ids_names_births_sex06吳蘭1992-03-01女08王菊1990-01-20女

可見，在 WHERE子句中使用子查詢能夠編寫出功能很強(qiáng)并且很靈活的SQL語句。對于能嵌套的子查詢的數(shù)目沒有限制，不過在實(shí)際使用時由于性能的限制，不能嵌套太多的子查詢。

列必須匹配 在WHERE子句中使用子查詢（如這里所示），應(yīng)該保證SELECT語句具有與WHERE 子句中相同數(shù)目的列。通常，子查詢將返回單個列并且與單個列匹配，但如果需要也可以使用多個列。

三、作為計算字段使用子查詢

使用子查詢的另一方法是創(chuàng)建計算字段。

查詢每位學(xué)生選修的課程數(shù)

首先可使用 SELECT COUNT(*)對表中的行進(jìn)行計數(shù)，并且通過提供一條WHERE子句來過濾某個特定的學(xué)生，可僅對該學(xué)生的課程進(jìn)行計數(shù)。

SELECT COUNT(c_id) FROM sc WHERE s_id = '01';

為了對每個學(xué)生執(zhí)行COUNT(*)計算，應(yīng)該將COUNT(*)作為一個子查詢。

SELECT *, (SELECT COUNT(c_id)
		   FROM sc
           WHERE sc.s_id = stu.s_id) AS cos FROM stu;

結(jié)果：

s_ids_names_births_sexcos01趙雷1990-01-01男302錢電1990-12-21男303孫風(fēng)1990-05-20男304李云1990-08-06男305周梅1991-12-01女206吳蘭1992-03-01女207鄭竹1992-04-21女208王菊1990-01-20女0

這條SELECT語句對customers表中每個學(xué)生返回5列：s_id，s_name，s_birth，s_sex和cos。cos是一個計算字段，它是由圓括號中的子查詢建立的。該子查詢對檢索出的每個學(xué)生執(zhí)行一次。在此例子中，該子查詢執(zhí)行了8次，因為檢索出了8個學(xué)生。

子查詢中的 WHERE子句使用了完全限定列名，任何時候只要列名可能有多義性，就必須使用這種語法（表名和列名由一個句點(diǎn)分隔）。如果不使用完全限定的列名會與本身匹配。

逐漸增加子查詢來建立查詢 用子查詢測試和調(diào)試查詢很有技巧性，特別是在這些語句的復(fù)雜性不斷增加的情況下更是如此。用子查詢建立（和測試）查詢的最可靠的方法是逐漸進(jìn)行，這與MySQL處理它們的方法非常相同。首先，建立和測試最內(nèi)層的查詢。然后，用硬編碼數(shù)據(jù)建立和測試外層查詢，并且僅在確認(rèn)它正常后才嵌入子查詢。這時，再次測試它。對于要增加的每個查詢，重復(fù)這些步驟。這樣做僅給構(gòu)造查詢增加了一點(diǎn)點(diǎn)時間，但節(jié)省了以后（找出查詢?yōu)槭裁床徽＃┑拇罅繒r間，并且極大地提高了查詢一開始就正常工作的可能性。

四、附錄

數(shù)據(jù)庫建立即數(shù)據(jù)導(dǎo)入準(zhǔn)備

-- 創(chuàng)建數(shù)據(jù)庫school CREATE DATABASE school; -- 選擇進(jìn)入school數(shù)據(jù)庫 USE school; -- ------------建表導(dǎo)數(shù)------------- -- 創(chuàng)建stu CREATE TABLE stu(
s_id VARCHAR(10) PRIMARY KEY,
s_name VARCHAR(10) NOT NULL,
s_birth DATE,
s_sex VARCHAR(10)) -- 導(dǎo)入數(shù)據(jù) INSERT INTO stu VALUES ('01' , '趙雷' , '1990-01-01' , '男'),
('02' , '錢電' , '1990-12-21' , '男'),
('03' , '孫風(fēng)' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吳蘭' , '1992-03-01' , '女'),
('07' , '鄭竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女'); SELECT * FROM stu; -- 檢查數(shù)據(jù) SELECT COUNT(*) FROM stu; -- 檢查總行數(shù) -- 創(chuàng)建co CREATE TABLE co(
c_id VARCHAR(10) PRIMARY KEY,
c_name VARCHAR(10),
t_id VARCHAR(10)); -- 導(dǎo)入數(shù)據(jù) INSERT INTO co VALUES ('01' , '語文' , '02'),
('02' , '數(shù)學(xué)' , '01'),
('03' , '英語' , '03'); SELECT * FROM co; -- 檢查數(shù)據(jù) SELECT COUNT(*) FROM co; -- 檢查總行數(shù) -- 創(chuàng)建te CREATE TABLE te(
t_id VARCHAR(10) PRIMARY KEY,
t_name VARCHAR(10)); -- 導(dǎo)入數(shù)據(jù) INSERT INTO te VALUES ('01' , '張三'),
('02' , '李四'),
('03' , '王五'); SELECT * FROM te; -- 檢查數(shù)據(jù) SELECT COUNT(*) FROM te; -- 檢查總行數(shù) -- 創(chuàng)建sc CREATE TABLE sc(
s_id VARCHAR(10),
c_id VARCHAR(10),
score int); -- 導(dǎo)入數(shù)據(jù) INSERT INTO sc VALUES ('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98); SELECT * FROM sc; -- 檢查數(shù)據(jù) SELECT COUNT(*) FROM sc; -- 檢查總行數(shù)