调侃数据库5.2：SQL：多表结构（简单不得了的教程）

昨天是介绍了select语句里面部分内容，今天做些补充，然后进入多表的组合等

---

• Any and All/Some
  这两个也是很常用的呀，直接上个例子就明白了
  Find staff whose salary is larger than salary of at least one member of staff at ranch B003.

SELECT staffNo, fName, lName, position, salary 
FROM Staff 
WHERE salary > SOME (SELECT salary FROM StaffWHERE branchNo = ‘B003’);

这是some的用法，意在说明如果薪水大于所选内容中任意一条的话，就满足条件，而All则要求薪水大于所有内存才满足条件

多表结构

很多时候，我们面对的表不止一张，查询数据时也需要我们把这些表连接起来，就像前面关系代数的join，如何实现join呢?
说出来您可能不信，我们并不用新命令，用已学的内容便可实现
List names of all clients who have viewed a property along with any comment supplied.

SELECT c.clientNo, fName,comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;

这样from后面的关系，代表的就是C与V内相同clientnumber的客户，可以从此看出，SQL还是很灵活的

• 左外连接
  List branches and properties that are in same city along with any unmatched branches.

SELECT b.*, p.*
FROM Branch1 b LEFT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;

• 右外连接同理

SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;

• 全外连接

SELECT b.*, p.*
FROM Branch1 b FULL JOIN
PropertyForRent1 p ON b.bCity = p.pCity;

存在和不存在

• exists：
  • True if and only if there exists at least one row in
    result table returned by subquery.
  • False if subquery returns an empty result table.
• not exists：NOT EXISTS is the opposite of EXISTS.

然后依旧给个例子
Find all staff who work in a London branch.

SELECT staffNo, fName, lName, position
FROM Staff s
WHERE EXISTS (SELECT *FROM Branch bWHERE s.branchNo = b.branchNoAND city = ‘London’);

其他：

还记得这些操作吗

没错，我们都能使用

(SELECT city FROM Branch WHERE city IS NOT
NULL)
UNION(SELECT cityFROM PropertyForRent WHERE city IS NOT NULL);

(SELECT city FROM Branch)
INTERSECT
(SELECT city FROM PropertyForRent);

(SELECT city FROM Branch)
EXCEPT
(SELECT city FROM PropertyForRent);