主題:sql的集合運算
一、表與集合
表(查詢結果):記錄行集合
集合中的元素:行
集合中運算:
- 屬于:可以把行元素看成隻有要一行的集合
- 差:minus
- 并:union/union all
- 交:intersect,mysql要代碼實作
-
相等:要代碼實作
關系:包含:A<B:中的元素都在B中,用差運算來實作,A-B為空就說明A是B的子集
二、并
1、基本文法
select A
union
select B
2、注意問題
- AB兩個結果中列數順序和類型都要相同
- all要注意
- 滿足交換律
3、和or條件的比較
并運算效率高于or條件
---查找cjb中>90分的同學記錄和學号為010101的同學的成績記錄
---方法一:
select b.*
from cjb b
where b.score>90
union
select a.*
from cjb a
where a.st_id='010101'
--方法二:
select b.*
from cjb b
where b.score>90 or b.st_id='010101'
三、差:minus
1、基本文法
select A
minus
select B
得出結果:A-B:A中的記錄排除掉B中存在的
2、注意
- AB兩個結果中列數順序和類型都要相同
- 不滿足交換律
- A-B為空則說明A是B的子集
3、應用:判斷兩個集合的包含關系,子集關系
結論:A-B為空,則A為B的子集,A包含于B
4、舉例1
---查詢學習了001課程,但是沒有學習002課程的學生學号
select *
from cjb
where course_id='001' and course_id!='002'
---where條件是以行為機關進行判斷,而題目需求是比較行與行之間
---方法一:用關聯子查詢
select a.st_id
from cjb a
where a.course_id='001' and not exists(
select b.*
from cjb b
where b.st_id=a.st_id and course_id='002'
)
---方法二:用差集:
---學習了001課程的學生學号
select st_id
from cjb
where course_id='001'
minus
--學習了001課程的學生學号
select st_id
from cjb
where course_id='002'
分析:
5、舉例2
查找哪些學生學習了010106同學學過的所有課程
----先考慮查詢010106同學所有的課程,010101同學哪些沒有學,如果沒有學的結果為空,則010101同學都學了
---方法一:
--第一步:
---如果這個結果為空,則說明010106同學所學的所有課程,010101同學都學了
select a.*
from cjb a
where a.st_id='010106' and not exists (
---如果查詢結果為不空,則說明010106同學學習的目前課程,010101同學也學了
---如果查詢結果為空,則說明010106同學學習的目前課程,但是010101同學沒有學,則成立
select b.*
from cjb b
where b.course_id=a.course_id and b.st_id='010101'
)
第二步:在xsb表中,用每一個學号去代替上面的010101
select *
from xsb
where not exists(
---如果這個結果為空,則說明010106同學所學的所有課程,010101同學都學了
select a.*
from cjb a
where a.st_id='010106' and not exists (
---如果查詢結果為不空,則說明010106同學學習的目前課程,010101同學也學了
---如果查詢結果為空,則說明010106同學學習的目前課程,但是010101同學沒有學,則成立
select b.*
from cjb b
where b.course_id=a.course_id and b.st_id=xsb.st_id
)
)
---方法二:
---第一步:
--兩集合的差集:如果這個結果為空則06同學學習的所有課程,01同學都學了,那麼06的集合-01的集合為空集:06學過課程,01都學了
select a.course_id from cjb a where a.st_id='010106'
minus
select b.course_id from cjb b where b.st_id='010101'
---第二步:
select *
from xsb
where not exists(
---如果這個結果是為空,說明06中學過的課程,xsb.st_id都學了,這個學号就滿足要求
select a.course_id from cjb a where a.st_id='010106'
minus
select b.course_id from cjb b where b.st_id=xsb.st_id
)
分析:
四、交:intersect
1、基本文法
select A
intersect
select B
查詢結果就是A交B
2、注意問題
- AB兩個結果中列數順序和類型都要相同
- 滿足交換律
3、舉例
查找010101和010106兩個同學學過的相同課程
select a.course_id
from cjb a
where a.st_id='010101'
intersect
select b.course_id
from cjb b
where b.ST_ID='010106'
4、用差運算來實作交運算
A-(A-B)
5、課堂練習
---課堂練習:查找有哪些同學和010106同學學過的相同課程
select c.*
from cjb c
where c.st_id!='010106' and c.course_id in (
select e.course_id
from cjb e
where e.st_id='010106'
) and exists(
---如果結果不為空,則說明xsb.st_id這學生和010106同學學了相同課程
select a.course_id
from cjb a
where a.st_id=c.st_id
intersect
select b.course_id
from cjb b
where b.ST_ID='010106'
)
五、相等
1、注意問題
- sql沒有相等運算,要自己代碼實作
- A=B:A-B為空,同時B-A也為空
2、舉例
---查詢和010106同學選修完全一樣的同學
---第一步:先考慮兩個固定同學的課程是否完全一樣
---檢查010106同學和010101同學選修是否完全一樣:所謂完全一樣,是兩同學學的課程不多少完全一緻
--如果下面并運算的結果為空,則說明兩個同學學習課程完全一樣
(select a.course_id
from cjb a
where a.st_id='010106'
minus
select a.course_id
from cjb a
where a.st_id='010101'
)
union
(
select c.course_id
from cjb c
where c.st_id='010101'
minus
select d.course_id
from cjb d
where d.st_id='010106'
)
--第二步:用xsb中的學号代替010101
select *
from xsb
where not exists
---如果下面結果為空,則說明xsb.st_id這學生和010106同學學習的課程完全一樣
(
(
select a.course_id
from cjb a
where a.st_id='010106'
minus
select b.course_id
from cjb b
where b.st_id=xsb.st_id
)
union
(
select c.course_id
from cjb c
where c.st_id=xsb.st_id
minus
select d.course_id
from cjb d
where d.st_id='010106'
)
)
where b.st_id=xsb.st_id
)
union
(
select c.course_id
from cjb c
where c.st_id=xsb.st_id
minus
select d.course_id
from cjb d
where d.st_id='010106'
)
)