PS:本文中數(shù)據(jù)庫表請參考上一篇文章
I.連接查詢
定義
前一篇中提到的查詢都是針對單個(gè)表的。若一個(gè)查詢涉及到兩個(gè)以上的表辩块,則稱之為連接查詢良蛮。連接查詢是關(guān)系型數(shù)據(jù)庫中的主要查詢运提,包括等值連接查詢莫换、自然連接查詢、非等值連接查詢嚣艇、自身連接查詢式廷、外連接查詢和復(fù)合條件連接查詢等咐扭。
1、等值與非等值連接查詢
一般格式
[表名1.]<列名1> <比較運(yùn)算符> [表名2.]<列名2>
其主要運(yùn)算符有:=滑废、>蝗肪、<、>=蠕趁、<=薛闪、!=(或<>)等;
當(dāng)比較運(yùn)算符為=時(shí)俺陋,稱為等值連接豁延,其他的運(yùn)算符稱為非等值連接。
-- 查詢每個(gè)學(xué)生及其選修課程的情況
select student.*, sc.*
from student, sc
where student.Sno=sc.Sno;
上述例子在屬性前面加上了表名腊状,這個(gè)因?yàn)閮蓚€(gè)表都存在Sno這一列诱咏,加上前綴可以避免混淆,當(dāng)參與連接的表的屬性列是唯一的時(shí)候缴挖,此前綴可省略袋狞。
若把等值連接中目標(biāo)列中重復(fù)的屬性列去掉則為自然連接,如:
-- 自然連接
select student.Sno, Sname,Ssex, Sage, Sdept, Cno, Grade
from student, sc
where student.Sno=sc.Sno;
2映屋、自身連接
一個(gè)表與自己連接苟鸯,則稱為表的自身連接。比如在Course表中棚点,我們只能的到直接的先修課早处,如果要得到先修課的先修課,則必須與自身連接瘫析。
-- 查詢課程的先修課的先修課
select c1.Cno, c2.Cpno
from course c1, course c2
where c1.Cpno=c2.Cno;
3砌梆、外連接
在通常的連接中默责,只有滿足的條件的元組才能作為結(jié)果輸出。例如下面這個(gè)例子
-- 查詢每個(gè)學(xué)生及其選修課程的情況
select student.*, sc.*
from student, sc
where student.Sno=sc.Sno;
沒有選課的學(xué)生信息被舍棄了么库。有時(shí)候想以Student表為主體列出每個(gè)學(xué)生的信息和選課信息傻丝,若某個(gè)學(xué)生沒有選課甘有,則在SC表的屬性列填充null值诉儒,這時(shí)候就要使用到外連接。
-- 左外連接查詢
select student.Sno, Sname, Sage, Ssex, Sdept, Cno, Grade
from student
left join sc on (student.Sno=sc.Sno);
左外連接列出左邊關(guān)系的所有元組(例如本例中的Student)亏掀,由外連接列出右邊關(guān)系的所有元組忱反。
4、復(fù)合條件連接
前面所提到的連接查詢滤愕,where子句只有一個(gè)條件温算,當(dāng)where子句中有多個(gè)連接條件時(shí),稱為復(fù)合條件連接间影。
-- 查詢選修了2號可并且成績在90分以上的學(xué)生信息
select student.Sno, Sname
from student,sc
where student.Sno=sc.Sno and sc.Cno=2 and Grade>=90;
-- 查詢選修了2號可并且成績在90分以上的學(xué)生信息
select student.Sno, Sname, Grade
from student,sc
where student.Sno=sc.Sno and sc.Cno=2 and Grade>=90;
-- 查詢每個(gè)學(xué)生的學(xué)號注竿、選修的課程名、成績
select student.Sno, Cname, Grade
from student,course,sc
where student.Sno=sc.Sno and sc.cno=course.cno;
II.嵌套查詢
定義
在SQL語言中魂贬,一個(gè)select-from-where語句稱為一個(gè)查詢塊巩割。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的where子句或者h(yuǎn)aving短語的條件中的查詢稱為嵌套查詢。
1付燥、在in謂詞的子查詢
-- 使用嵌套查詢查出選修了2號課程的學(xué)生的學(xué)號
select Sno
from student
where Sno in(
select Sno
from sc
where Cno='2'
);
這類查詢外層查詢(父查詢)和內(nèi)層查詢(子查詢)的條件不相關(guān)宣谈,稱為不相關(guān)子查詢。
2键科、帶比較運(yùn)算符的子查詢
-- 查詢每個(gè)學(xué)生成績超過他平均成績的課程號
select Sno, Cno
from sc x
where Grade >(
select avg(Grade)
from sc y
where y.Sno=x.Sno);
子查詢依賴父查詢的Sno闻丑,這類查詢稱為相關(guān)子查詢。
3勋颖、帶有any(some)或all謂詞的子查詢
子查詢返回單值時(shí)可以用比較運(yùn)算符嗦嗡,返回多值要用any(有的系統(tǒng)用some)或者all謂詞來修飾,而使用any或all謂詞修飾是必須同時(shí)使用比較運(yùn)算符饭玲,其語義為:
比較運(yùn)算 | 語義 |
---|---|
> ANY |
大于子查詢中的某個(gè)值 |
> ALL |
大于子查詢中的所有值 |
< ANY |
小于子查詢中的某個(gè)值 |
< ALL |
小于子查詢中的所有值 |
>= ANY |
大于等于子查詢中的某個(gè)值 |
>= ALL |
大于等于子查詢中的所有值 |
<= ANY |
小于等于子查詢中的某個(gè)值 |
<= ALL |
小于等于子查詢中的所有值 |
= ANY |
等于子查詢中的某個(gè)值 |
= ALL |
等于子查詢中的所有值(通常沒有意義) |
!=(或<>) ANY |
不等于子查詢中的某個(gè)值 |
!=(或<>) ALL |
不等于子查詢中的所有值 |
-- 查詢其他系中比計(jì)算機(jī)系某一個(gè)學(xué)生年齡小的學(xué)生姓名和年齡
select Sname, Sage
from student
where Sage < any(
select Sage
from student
where Sdept='CS'
)
and Sdept !='CS';
-- 用聚集函數(shù)查詢其他系中比計(jì)算機(jī)系某一個(gè)學(xué)生年齡小的學(xué)生姓名和年齡
select Sname, Sage
from student
where Sage <(
select max(Sage)
from student
where Sdept='CS'
)
and Sdept !='CS';
事實(shí)上侥祭,用聚集函數(shù)實(shí)現(xiàn)比用any或all謂詞效率要高。
4咱枉、帶有exists卑硫,not exists謂詞的子查詢
帶有exists的子查詢不返回人戶數(shù)據(jù),只返回true 或者false蚕断。
-- 查詢選修了1號課的學(xué)生姓名
select Sname
from student
where exists(
select * from sc
where student.Sno=sc.Sno and Cno='1'
);
使用 not exists就是去取其非值欢伏。
-- 查詢沒有選修了1號課的學(xué)生姓名
select Sname
from student
where not exists(
select * from sc
where student.Sno=sc.Sno and Cno='1'
);
III. 集合查詢
select 語句一般是返回多個(gè)元組的集合,所以多個(gè)select語句的結(jié)果集合可以進(jìn)行集合操作亿乳。集合操作包括并操作(union)硝拧,交操作(intersect)和差操作(except)径筏。但是需要注意的是參與集合運(yùn)算的集合列數(shù)量必須相等,而且數(shù)據(jù)類型也要相同障陶。
-- 查詢計(jì)算機(jī)系及年齡不大于19歲的學(xué)生 滋恬,不保留重復(fù)的元組
select * from student
where Sdept='CS'
union
select * from student
where Sage<=19;
-- 查詢計(jì)算機(jī)系及年齡不大于19歲的學(xué)生,保留重復(fù)的元組
select * from student
where Sdept='CS'
union all
select * from student
where Sage<=19;