三大重要操作
scanning
sorting
hashing
UNION，EXCEPT, INTERSECT相當(dāng)于集合操作，返回結(jié)果也是集合，所以不會(huì)存在相同行
find sid of students who take some CS :
select E.sid from Enroll E where E.cno?IN (select C.cno From Course c where c.cname = 'CS')
find sid of students who take no CS :
Select S.sid from Student S EXCEPT(select C.cno From Course c where c.cname = 'CS')
find sid of students who take only CS :
select E.sid from Enroll E where E.cno?IN (select C.cno From Course c where c.cname = 'CS') EXCEPT NOT IN (select )
(SOME 與 IN是等價(jià)的)
Find sid of students?who take all CS courses.
一個(gè)學(xué)生上了所有的CS的課，就說明不存在一門CS的課是這個(gè)學(xué)生沒上過的。
Select S.sid from Student S where NOT EXISTS(Select C.cno From Course C where C.Dept = 'CS'
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AND C.cno NOT IN(Select E.cno from Enroll E where E.sid = S.sid))
構(gòu)造方法
CREATE FUNCTION sum_and_product(OUT sum int, OUT product int)
RETURNS SETOF RECORD
AS $$
SELECT P.x+P.y , P.x*P.y FROM Pair P;
$$ LANGUAGE SQL;
構(gòu)造視圖
視圖就是一個(gè)虛擬的關(guān)系表
CREATE View CS_COURSE AS
SELECT C.Cno, C.Cname
FROM COURSE C
WHERE C.DEPT = 'CS'
可以直接利用我們的view
SELECT C.Cname
FROM CS_COUSE C
可以用view解決現(xiàn)在的數(shù)據(jù)表不足以及數(shù)據(jù)表擴(kuò)張
Collection & Aggregate function
SELECT COUNT(*) FROM R r;
SELECT E.sid, COUNT(*) FROM Enroll E GROUP BY (E.sid)
但是這個(gè)無法輸出沒有上過課的學(xué)生，S4,0
可以改成
SELECT distinct S.sid(SELECT COUNT(E.cno) FROM Enrool?E where E.sid = S.sid) As NumberCourse
FROM Student S
計(jì)算兩個(gè)篩子和的出現(xiàn)頻率
SELECT SUM(Q.RV * Q.Trials) / SELECT COUNT(*) FROM Trials
FROM (SELECT t.Dice1 + t.Dice2) AS RV, COUNT(t.Tid) AS Ntrials?
?????????????????FROM Trials t
????????????????GROUP BY (?t.Dice1 + t.Dice2) AS Q
COUNT的強(qiáng)大之處
所有的集合問題都可以轉(zhuǎn)化為count是否==0 >0等等
A屬于B????? ?|A-B| == 0? ? ? ? ? ? ? ? ? ? ? ? A EXCEPT B
A==B? ?????????|A-B|==0 并且|B-A|==0? ?A EXCEPT B and B EXCEPT A
A和B無交集 |A并B| == 0? ? ? ? ? ? ? ? ? ? A INTERSECT B
find sid take all ‘MATH’ courses
運(yùn)用集合思維就是 |Math-C(S)| = 0 然后轉(zhuǎn)化為SQL
SELECT S.sid FROM Student S
WHERE (SELECT COUNT(1))?
????FROM ((SELECT C.Cno FROM Course C Where C.cname ='MATH')
?????????EXCEPT (Select E.cno From Enrolll?E Where E.sid = S.sid))q)==0
CROSS JOIN就等同于叉乘
B+ TREE
這些數(shù)字本身不是Data，這只是索引，最終index指向的才是Data
鎖三條rule:
li(A) .... ui(A)有上鎖必須有解鎖
li(A) .... ui(A)之間不能有別的lj(A)就是不能有別的事務(wù)在A沒被解放時(shí)候再上鎖
li(A) .... ui(A)同一個(gè)事務(wù)i，所有的上鎖都在所有的解鎖前面，任何一個(gè)上鎖活動(dòng)前不存在解鎖，任何一個(gè)解鎖活動(dòng)后不存在上鎖
