SQL2

SQL II 课程笔记

---

一、多表查询：JOIN

1. 交叉连接（CROSS JOIN）

SELECT * FROM table1, table2;

• 返回所有行的组合
• 通常需配合 WHERE 条件过滤
• ==这边所有行的组合也就是table1 直接添加table2的列然后进行排列组合==

2. 内连接（INNER JOIN）

SELECT * FROM table1 INNER JOIN table2 ON table1.col = table2.col;

/*  交叉连接 + WHERE条件等于内连接  */
/*  *用来表示所有列   */
SELECT S.sid, S.name, R.bid
FROM Sailors S, Reserves R
WHERE S.sid > R.sid

• 只返回匹配的行

3. 自然连接（NATURAL JOIN）

SELECT * FROM table1 NATURAL JOIN table2;

• 自动匹配同名列进行等值连接
• 不推荐使用，易出错

4. 外连接（OUTER JOIN）

• LEFT JOIN：保留左表所有行，右表无匹配则为 NULL
• RIGHT JOIN：保留右表所有行
• FULL JOIN：保留左右表所有行

---

二、表别名与列别名

SELECT s.sid AS sailor_id, s.name
FROM Sailors AS s
WHERE s.age > 20;

• 用于简化表名或重命名输出列
• 自连接时必须使用别名

---

三、表达式与字符串操作

1. 算术表达式

SELECT age, age - 5 AS age_minus_5 
FROM Sailors;

• 这边选择S1的rating的两倍等于S2 - 1

SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors AS S1, Sailors AS S2
WHERE 2*S1.rating = S2.rating - 1;

2. 字符串匹配

• 例子

WHERE S.sname LIKE 'B_%'  

• LIKE 'B_%'：==_ 只能匹配任意一个单字符，% 可以匹配任意字符==
• 正则表达式：~ 'B.*'（部分数据库支持）

---

四、布尔连接词与组合条件

WHERE (color = 'red' OR color = 'green') AND rating > 5;

• 可使用 AND, OR, NOT
• 注意优先级：NOT > AND > OR
  UNION的用法

---

五、NULL 值的处理

1. 比较与逻辑

• NULL = NULL → NULL
• NULL > 5 → NULL
• WHERE NULL → 不输出该行

2. 三值逻辑

A	B	A AND B	A OR B	NOT A
TRUE	NULL	NULL	TRUE	FALSE
FALSE	NULL	FALSE	NULL	TRUE
NULL	NULL	NULL	NULL	NULL

3. 聚合函数中的 NULL

• COUNT(column) 忽略 NULL
• SUM, AVG, MAX, MIN 也忽略 NULL

---

六、集合操作

1. 集合语义（去重）

• ==可以把一个关系看作一个集合，关系中的每个tuple就是一个元素==
• UNION：并集 （将行并起来）（==注意会去重==）
• INTERSECT：交集 （将相同的行过滤出来）相当于and
• EXCEPT：差集

-- 方法1：使用 UNION
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = 'red'
UNION
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = 'green';

-- 方法2：使用 OR（等价）
SELECT DISTINCT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND (B.color = 'red' OR B.color = 'green');

2. 多重集语义（保留重复）

• UNION ALL：合并所有行
• INTERSECT ALL：取最小重复数
• EXCEPT ALL：取差集重复数

要求：两个查询的输出列数与类型必须一致

---

七、嵌套查询（子查询）

1. 使用 IN / NOT IN

/*相当于给出预定的102船的水手的名字*/
SELECT name 
FROM Sailors 
WHERE sid IN 
(SELECT R.sid 
FROM Reserves R
WHERE R.bid = 102);

2. 使用 EXISTS / NOT EXISTS(返回==true==和==false==)

/*和上面的意思相同*/
SELECT name 
FROM Sailors S 
WHERE EXISTS 
(SELECT * 
FROM Reserves R 
WHERE R.sid = S.sid AND R.bid = 102);

理解：每个水手都检查一遍是否存在EXIST
如果R子查询中返回的结果不为空，那么就可以进行水手的SELECT

• 可关联外部查询（correlated subquery）

3. 使用 ANY / ALL

SELECT * 
FROM Sailors 
WHERE rating > ALL 
(SELECT rating 
FROM Sailors 
WHERE name = 'Popeye');

• ==例子:==
  找出预定了全部船的水手
  也就是找出不存在船只没有预定的水手

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS
    (SELECT B.bid
    FROM Boats B
    WHERE NOT EXISTS (SELECT R.bid
                      FROM Reserves R 
                      /*当前船只的记录*/
                      WHERE R.bid = B.bid
                      AND R.sid = S.sid))

分析：最内层是查找==当前==水手预定的==当前==船只的记录
返回的是水手和船只确定的时候的记录
第二层是查找当前水手没有预定的船只
也就是如果最内层没有返回那么该船只就确定没有被预定
第三层就是查找没有为预定船只的水手
如果第二层返回为空，那么就说明水手不存在没有预定的船只
bid是船只的编号，sid是水手的编号

---

八、视图与 CTE

1. 视图（VIEW）

CREATE VIEW RedCount AS
SELECT bid, COUNT(*) AS scount
FROM Boats JOIN Reserves USING(bid)
WHERE color = 'red'
GROUP BY bid;

2. 公共表表达式（CTE）

WITH RedCount(bid, scount) AS (
  SELECT bid, COUNT(*)
  FROM Boats JOIN Reserves USING(bid)
  WHERE color = 'red'
  GROUP BY bid
)
SELECT * FROM RedCount WHERE scount < 10;

九、测试查询的建议

• 构造包含边界值的数据
• 检查：
  • 输出列是否正确
  • 是否漏行（false negative）
  • 是否多行（false positive）
  • 重复行数是否正确
  • 排序是否正确

---

十、高级查询示例

1. 查询“所有船只都被预订的水手”

SELECT sname FROM Sailors S
WHERE NOT EXISTS (
  SELECT bid 
  FROM Boats
  WHERE NOT EXISTS (
    SELECT * 
    FROM Reserves R
    WHERE R.bid = Boats.bid AND R.sid = S.sid
  )
);

2. 查询每个年龄中评分最高的水手

WITH MaxRatings(age, maxrating) AS (
  SELECT age, MAX(rating) 
  FROM Sailors 
  GROUP BY age
)
SELECT S.* 
FROM Sailors S, MaxRatings M
WHERE S.age = M.age AND S.rating = M.maxrating;

3.WITH子句的优势

-- 没有CTE的等价写法（难以阅读）：
SELECT B.bname, red_counts.scount
FROM Boats2 B,
    (SELECT B.bid, COUNT(*) as scount
     FROM Boats2 B, Reserves2 R
     WHERE R.bid = B.bid AND B.color = 'red'
     GROUP BY B.bid) AS red_counts
WHERE red_counts.bid = B.bid AND red_counts.scount < 10;

-- 使用CTE的写法（清晰易读）：
WITH Reds AS (...), UnpopularReds AS (...)
SELECT * FROM UnpopularReds;