來(lái)源：數(shù)據(jù)STUDIO

作者：云朵君

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

分析

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

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

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

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

語(yǔ)句

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ù)需要"課程名稱"與"對(duì)應(yīng)分?jǐn)?shù)"，則還需要跟課程表連接以顯示課程名及分?jǐn)?shù)。

語(yǔ)句

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男語(yǔ)文7004李云1990-08-06男語(yǔ)文5002錢電1990-12-21男數(shù)學(xué)6004李云1990-08-06男數(shù)學(xué)3002錢電1990-12-21男英語(yǔ)8004李云1990-08-06男英語(yǔ)20

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

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

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

首先，它執(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;

此時(shí)得到兩個(gè)學(xué)生編號(hào)s_id等于02和04表格：

s_idc_idscore020170040150

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

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

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

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

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

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

查詢沒(méi)學(xué)過(guò)"張三"老師授課的同學(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。然后，這兩個(gè)值以 NOT IN 操作符要求的逗號(hào)分隔的格式傳遞給外部查詢的 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女

可見(jiàn)，在 WHERE子句中使用子查詢能夠編寫(xiě)出功能很強(qiáng)并且很靈活的SQL語(yǔ)句。對(duì)于能嵌套的子查詢的數(shù)目沒(méi)有限制，不過(guò)在實(shí)際使用時(shí)由于性能的限制，不能嵌套太多的子查詢。

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

三、作為計(jì)算字段使用子查詢

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

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

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

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

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

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語(yǔ)句對(duì)customers表中每個(gè)學(xué)生返回5列：s_id，s_name，s_birth，s_sex和cos。cos是一個(gè)計(jì)算字段，它是由圓括號(hào)中的子查詢建立的。該子查詢對(duì)檢索出的每個(gè)學(xué)生執(zhí)行一次。在此例子中，該子查詢執(zhí)行了8次，因?yàn)闄z索出了8個(gè)學(xué)生。

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

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

四、附錄

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

-- 創(chuàng)建數(shù)據(jù)庫(kù)school CREATE DATABASE school; -- 選擇進(jìn)入school數(shù)據(jù)庫(kù) 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' , '語(yǔ)文' , '02'),
('02' , '數(shù)學(xué)' , '01'),
('03' , '英語(yǔ)' , '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ù)