SQL複雜查詢

PS:本文中資料庫表請參考上一篇文章

I.連線查詢

定義
前一篇中提到的查詢都是針對單個表的。若一個查詢涉及到兩個以上的表，則稱之為連線查詢。連線查詢是關係型資料庫中的主要查詢，包括等值連線查詢、自然連線查詢、非等值連線查詢、自身連線查詢、外連線查詢和複合條件連線查詢等。

1、等值與非等值連線查詢

一般格式
[表名1.]<列名1> <比較運算子> [表名2.]<列名2>

其主要運算子有：=、>、<、>=、<=、!=(或<>)等；

當比較運算子為=時，稱為等值連線，其他的運算子稱為非等值連線。

-- 查詢每個學生及其選修課程的情況
select student.*, sc.*
from student, sc
where student.Sno=sc.Sno;

上述例子在屬性前面加上了表名，這個因為兩個表都存在Sno這一列，加上字首可以避免混淆，當參與連線的表的屬性列是唯一的時候，此字首可省略。

若把等值連線中目標列中重複的屬性列去掉則為自然連線，如：

-- 自然連線
select student.Sno, Sname,Ssex, Sage, Sdept, Cno, Grade
from student, sc
where student.Sno=sc.Sno;

2、自身連線

一個表與自己連線，則稱為表的自身連線。比如在Course表中，我們只能的到直接的先修課，如果要得到先修課的先修課，則必須與自身連線。

-- 查詢課程的先修課的先修課
select c1.Cno, c2.Cpno
from course c1, course c2
where c1.Cpno=c2.Cno;

3、外連線

在通常的連線中，只有滿足的條件的元組才能作為結果輸出。例如下面這個例子

-- 查詢每個學生及其選修課程的情況
select student.*, sc.*
from student, sc
where student.Sno=sc.Sno;

沒有選課的學生資訊被捨棄了。有時候想以Student表為主體列出每個學生的資訊和選課資訊，若某個學生沒有選課，則在SC表的屬性列填充null值，這時候就要使用到外連線。

-- 左外連線查詢
select student.Sno, Sname, Sage, Ssex, Sdept, Cno, Grade
from student
left join sc on (student.Sno=sc.Sno);

左外連線列出左邊關係的所有元組（例如本例中的Student），由外連線列出右邊關係的所有元組。

4、複合條件連線
前面所提到的連線查詢，where子句只有一個條件，當where子句中有多個連線條件時，稱為複合條件連線。

-- 查詢選修了2號可並且成績在90分以上的學生資訊
select student.Sno, Sname
from student,sc
where student.Sno=sc.Sno and sc.Cno=2 and Grade>=90;

-- 查詢選修了2號可並且成績在90分以上的學生資訊
select student.Sno, Sname, Grade
from student,sc
where student.Sno=sc.Sno and sc.Cno=2 and Grade>=90;

-- 查詢每個學生的學號、選修的課程名、成績
select student.Sno, Cname, Grade
from student,course,sc
where student.Sno=sc.Sno and sc.cno=course.cno;

II.巢狀查詢

定義
在SQL語言中，一個select-from-where語句稱為一個查詢塊。將一個查詢塊巢狀在另一個查詢塊的where子句或者having短語的條件中的查詢稱為巢狀查詢。
1、在in謂詞的子查詢

-- 使用巢狀查詢查出選修了2號課程的學生的學號
select Sno
from student
where Sno in(
    select Sno
    from sc
    where Cno='2'
);

這類查詢外層查詢（父查詢）和內層查詢（子查詢）的條件不相關，稱為不相關子查詢。

2、帶比較運算子的子查詢

-- 查詢每個學生成績超過他平均成績的課程號
select Sno, Cno
from sc x
where Grade >(
        select avg(Grade)
        from sc y
        where y.Sno=x.Sno);

子查詢依賴父查詢的Sno，這類查詢稱為相關子查詢。

3、帶有any(some)或all謂詞的子查詢

子查詢返回單值時可以用比較運算子，返回多值要用any(有的系統用some)或者all謂詞來修飾，而使用any或all謂詞修飾是必須同時使用比較運算子，其語義為：

比較運算	語義
> ANY	大於子查詢中的某個值
> ALL	大於子查詢中的所有值
< ANY	小於子查詢中的某個值
< ALL	小於子查詢中的所有值
>= ANY	大於等於子查詢中的某個值
>= ALL	大於等於子查詢中的所有值
<= ANY	小於等於子查詢中的某個值
<= ALL	小於等於子查詢中的所有值
= ANY	等於子查詢中的某個值
= ALL	等於子查詢中的所有值(通常沒有意義)
!=(或<>) ANY	不等於子查詢中的某個值
!=(或<>) ALL	不等於子查詢中的所有值

-- 查詢其他系中比計算機系某一個學生年齡小的學生姓名和年齡
select Sname, Sage
from student
where Sage < any(
        select Sage
        from student
        where Sdept='CS'
        )
and Sdept !='CS';

-- 用聚集函式查詢其他系中比計算機系某一個學生年齡小的學生姓名和年齡
select Sname, Sage
from student
where Sage <(
        select max(Sage)
        from student
        where Sdept='CS'
        )
and Sdept !='CS';

事實上，用聚集函式實現比用any或all謂詞效率要高。

4、帶有exists，not exists謂詞的子查詢

帶有exists的子查詢不返回人戶資料，只返回true 或者false。

-- 查詢選修了1號課的學生姓名
select Sname
from student
where exists(
        select * from sc
        where student.Sno=sc.Sno and Cno='1'
);

使用 not exists就是去取其非值。

-- 查詢沒有選修了1號課的學生姓名
select Sname
from student
where not exists(
        select * from sc
        where student.Sno=sc.Sno and Cno='1'
);

III. 集合查詢

select 語句一般是返回多個元組的集合，所以多個select語句的結果集合可以進行集合操作。集合操作包括並操作（union），交操作（intersect）和差操作(except)。但是需要注意的是參與集合運算的集合列數量必須相等，而且資料型別也要相同。

-- 查詢計算機系及年齡不大於19歲的學生 ，不保留重複的元組
select * from student
where Sdept='CS'
union
select * from student
where Sage<=19;

-- 查詢計算機系及年齡不大於19歲的學生，保留重複的元組
select * from student
where Sdept='CS'
union all
select * from student
where Sage<=19;