三大重要操作
scanning
sorting
hashing
UNION，EXCEPT, INTERSECT相当于集合操作，返回结果也是集合，所以不会存在相同行
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是等价的)
Find sid of students who take all CS courses.
一个学生上了所有的CS的课，就说明不存在一门CS的课是这个学生没上过的。
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))
构造方法
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;
构造视图
视图就是一个虚拟的关系表
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解决现在的数据表不足以及数据表扩张
Collection & Aggregate function
SELECT COUNT(*) FROM R r;
SELECT E.sid, COUNT(*) FROM Enroll E GROUP BY (E.sid)
但是这个无法输出没有上过课的学生，S4,0
可以改成
SELECT distinct S.sid(SELECT COUNT(E.cno) FROM Enrool E where E.sid = S.sid) As NumberCourse
FROM Student S
计算两个筛子和的出现频率
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的强大之处
所有的集合问题都可以转化为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
运用集合思维就是 |Math-C(S)| = 0 然后转化为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
这些数字本身不是Data，这只是索引，最终index指向的才是Data
锁三条rule:
li(A) .... ui(A)有上锁必须有解锁
li(A) .... ui(A)之间不能有别的lj(A)就是不能有别的事务在A没被解放时候再上锁
li(A) .... ui(A)同一个事务i，所有的上锁都在所有的解锁前面，任何一个上锁活动前不存在解锁，任何一个解锁活动后不存在上锁
网友评论