数据库笔记（七）

写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第四章(第三章的多表部分会挪到这一部分讲)
笔者接下来的代码示例会主要在SQL Server数据库中测试(由于SQL Server 数据库不支持自然连接，所以自然连接部分的将会在MySQL数据库中进行测试)

在开始今天的摸鱼大业之前，让我们继续延用之前用的表

外键约束：即具有外键约束的两张表，在进行操作时会收到一定程度的限制，如果某个操作执行后会破坏这个完整性，就将被拒绝执行。这个在之前的博客中有介绍==> 传送门

自连接
一个表与自身相连接的操作，其实与多表连接是一样的，只是连接的两张表用的是同一张表而已

举个栗子

-- 下面的语句查询了所有员工的名字以及其经理的名字
SELECT w.ENAME as worker_name, m.ENAME as manager_name
FROM EMP w
  JOIN EMP m ON w.MGR = m.EMPNO;

得到如下结果：

上面得到的结果是采用内连接得到的结果，所以对于那些MGR为null（没有经理）的员工，其信息就得不到显示，可以采用左外连接（LEFT OUTER JOIN）

员工表中MGR 和 EMPNO 字段应该有一层限制，MGR的取值应该是某一个EMPNO的取值（话句话说，一个员工的经理号应该也是同一张表中这个经理的职工号）。在定义这张表的时候可以给这两个字段加上一个外键约束。

--下面的语句模拟了这一操作
--只关注了EMPNO和MGR这两个字段，其他字段的描述省略了
CREATE TABLE EMP(
  EMPNO INTEGER PRIMARY KEY ,
  MGR INTEGER,
  ....
  FOREIGN KEY(MGR) REFERENCES EMP(EMPNO)
)

还是那句话：绝大多数连接条件是建立在外键与外键的参照键之间的(自连接也不例外)

自然连接（NATURAL JOIN）
其实自然连接就是写法更简单的多表连接，无需书写连接条件，让数据库自己判断应该采用什么连接条件（自然连接的语法是SQL的标准语法，但是并不是所有的数据库都支持，SQL Server就不支持自然连接的语法，但是Oracle和MySQL数据库都是支持的。。所以下面的测试就在MySQL里面测试啦，上次用IDEA连的那个数据库里面我已经建好了和在上面测试用例一样的表和数据，可以直接用）

可采用自然连接的前提条件：两个表中要有同名字段，并且同名字段的数据类型要一致

举个栗子

-- 下面的语句就实现了查询所有的员工名字及其所在部门名
-- 相当于内连接
-- 因为两个表中有同名字段DEPTNO，并且数据类型相同，数据库就用这个作为连接条件
SELECT e.ENAME, d.DNAME
FROM EMP e
  NATURAL JOIN DEPT d;

-- 上面的语句等价于下面的内连接语句
SELECT e.ENAME, d.DNAME
FROM EMP e
  JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

得到如下结果：

如果两个表中有同名字段，但是数据类型不一致，执行自然连接是会失败的，因为此时数据库不知道应该怎么连接两张表

当然如果想要执行外连接（左外，右外，全外）也是一样的

举个栗子

-- 下面几个语句是等价的，会得到相同的结果，都采用了左外连接

SELECT e.ENAME, d.DNAME
FROM EMP e
  NATURAL LEFT OUTER JOIN DEPT d;

SELECT e.ENAME, d.DNAME
FROM EMP e
  NATURAL LEFT JOIN DEPT d;

SELECT e.ENAME, d.DNAME
FROM EMP e
  LEFT OUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

得到如下结果：

在使用自然连接时，同名字段（用于连接的字段）不能写限定名（虽然老师上课这么讲，但实际上呢？）

举个栗子

-- 我们在上面例子的基础上，多显示一个部门号，下面的两条语句如果能执行，执行完的结果都是一样的

-- 下面的语句在Oracle数据库执行会报错
-- 但是在MySQL中执行是能过的
SELECT e.ENAME, d.DNAME, e.DEPTNO
FROM EMP e
  NATURAL JOIN DEPT d;

-- 下面这种写法在Oracle和MySQL中都是能过的
SELECT e.ENAME, d.DNAME, DEPTNO
FROM EMP e
  NATURAL JOIN DEPT d;

让我们先分析一下，为什么在使用自然连接的时候同名字段可以不写限定名
- 先看看执行下面的语句有什么结果：
  1
  2
  3
  SELECT *
  FROM EMP e
  NATURAL JOIN DEPT d;
  得到下面结果：
- 可以看出来，采用自然连接以后，原本的同名字段DEPTNO只出现了一次,可以理解为采用自然连接以后FROM子句的结果就是上面那张表，同时原来的同名字段合成了一个。所以在SELECT子句中书写的时候可以不加表限定名（在Oracle数据库中不能加表限定名）
- 对比join操作，如果你直接用内连接或者外连接，而不是自然连接，会发现结果集里面用来连接的字段即便同名，也是会出现两次的（这也很好的解释了，为什么之前说在进行内外连操作的时候，用来连接的字段是需要用表明来限定的，不然SQL语句会有歧义，导致无法正确被解析————见数据库笔记（六））
  1
  2
  3
  4
  -- 你可以试试执行下面的SQL语句，会发现，结果集中名为DEPTNO的列有两列
  SELECT *
  FROM EMP e
  INNER JOIN DEPT d on e.DEPTNO = d.DEPTNO;
让我们对上面的结果小结一下
- 在采用自然连接的时候，对于同名字段，可以不写表限定名（但是在使用非自然连接的时候，同名字段一定要用表名来限定）
- 至于能不能写，得看具体是什么数据库，Oracle此种情况下就不允许在同名字段前写表限定名，而MySQL中则可以
- 所以推荐是在采用自然连接的时候，非同名字段都加上限定名，同名字段都不加

下面是在多表操作中的一点建议
- 在多表连接中，所有的字段前面都建议加上限定名
- 特殊的是，在使用自然连接的时候，同名字段不要写限定名

有一个问题就是，由于数据库只分析到数据字典层面，而无法分到数据层，所以就算两个表中的一组同名字段之间没有任何关系，但是数据类型一致，采用自然连接的话，这一组同名字段也会被作为连接条件。

举个栗子

--下面这个栗子没有实际执行，想象一下就好

-- 假设我们给部门表的部门名字段更名：DNAME-->ENAME
-- 然后我们仍然执行上面的例子
-- 结果应该是没有数据，除非有一个员工，他的名字和他所在部门的名字是一样的。
SELECT e.ENAME, d.ENMAE
FROM EMP e
  NATURAL JOIN DEPT d

导致上面结果的原因是采用了自然连接以后，数据库会把所有同名并且类型相同的字段作为连接条件
有时候我们并不希望所有的同名字段都作为条件，这个时候我们就不能用上面的自然连接操作，就应该使用介绍的有关JOIN … USING的用法

利用JOIN … USING 可以灵活的指定用什么字段做连接条件

JOIN…USING 同样是一种自然连接，且是一种特殊的自然连接，不需要显示写明连接条件，只需要指定用什么字段连接即可

举个栗子

-- 用下面的语句同样可以实现查询所有员工名字以及其所在部门名字的需求
SELECT e.ENAME, d.DNAME
FROM EMP e
  JOIN DEPT d USING (DEPTNO);

-- 同样的，如果我们给部门表的部门名字段更名：DNAME-->ENAME
-- 为了避免上面提到的问题，我们可以只指定DEPTNO作为连接条件，就轻松解决上面的问题
SELECT e.ENAME, d.ENAME
FROM EMP e
  JOIN DEPT d USING (DEPTNO);

得到如下结果：

其中JOIN 表名 USING (字段序列)，字段序列可以是一个或多个字段

如果两个表中存在同名字段，但是数据类型不一样，这样执行自然连接是会失败的
连接类型和连接条件(就是老师上课提到的课本上那个图)
- 连接类型
  - INNER JOIN
  - LEFT OUTER JOIN
  - RIGHT OUTER JOIN
  - FULL OUTER JOIN
- 连接条件
  - NATURAL
  - ON< predicate>
  - USING(A1, A2, …, An)
- 其中上面的连接类型和连接条件可以任意组合使用
- 但不是所有数据库都支持NATURAL 和 USING的用法，SQL Server不支持，Oracle和MySQL支持，即便支持，有一些细节还是不一样的（比如上面同名字段前面能不能加限定词的问题）
- ON的话是都支持的，建议用ON，有更好的可读性和灵活性

FROM子句中的子查询

如果子查询出现在FROM子句中，我们将子查询的结果当做一个临时的关系（表），奕称之为虚表

举个栗子

-- 下面的例子求出了工资比本部门平均工资高的员工的名字和其工资，以及其所在部门的平均工资
SELECT e.ENAME, e.SAL, E.avg_sal
FROM EMP e, (SELECT DEPTNO, AVG(SAL) as avg_sal
      FROM EMP
      GROUP BY DEPTNO) as E
WHERE e.DEPTNO = E.DEPTNO and e.SAL > avg_sal;

得到如下结果：

分析上面的执行过程的时候，可以先分析一下子查询执行的结果是什么，然后再把它当做一张表放在上面，继续分析整个语句

可以给子查询得到的临时表取一个名字，并给里面的每个字段指定名字
- 最常见的是下面这种命名方法
  1
  2
  3
  4
  5
  SELECT e.ENAME, e.SAL, E.avg_sal
  FROM EMP e, (SELECT DEPTNO, AVG(SAL)
  FROM EMP
  GROUP BY DEPTNO) as E(DEPTNO, avg_sal)
  WHERE e.DEPTNO = E.DEPTNO and e.SAL > avg_sal;
  - 直接在子查询后面用as关系指定表名以及子查询结果中每个字段的别名
- 但是也不是所有数据库都支持上面那种用法，Oracle数据库中就不支持用as关键字指定子查询的表名，也不支持在表名后面加上每个字段的名字，那这个时候怎么办呢？（可以在子查询里面给每个字段取别名，最后再给子查询得到的虚表用空格分隔取个表名就好了）
  1
  2
  3
  4
  5
  SELECT e.ENAME, e.SAL, E.avg_sal
  FROM EMP e, (SELECT DEPTNO, AVG(SAL) as avg_sal
  FROM EMP
  GROUP BY DEPTNO) E
  WHERE e.DEPTNO = E.DEPTNO and e.SAL > avg_sal;
- 下面提几点注意（对于FROM子句中的子查询）：
  - 如果子查询中含有聚集函数（组函数）,一定要给它起个别名
  - 对每个子查询得到的虚表都务必取个表名（虽然有些数据库可以不起表名，但是有些数据库不起表名是会出错的，所以建议还是都起一个表名）

With 子句
其实with子句的作用和在FROM子句中使用子查询时一样的，同样是得到了一张虚表，只是把FROM中冗长的子查询移到外面，可读性更好一点

with子句的作用是定义一张临时表
WITH子句相当于把FROM中的子查询提出来，如果FROM子句中的子查询比较复杂的时候，这样做是很有好处的，可读性会好很多

WITH 的基本使用方法

1	WITH < 临时表名 > （字段别名序列）AS (子查询)

举个栗子

-- 下面的例子和上面找大于本部门平均工资的例子是等效的
-- 执行的结果也是相同的
WITH avg_tab(DEPTNO, avg_sal)
AS (SELECT DEPTNO, AVG(SAL)
    FROM EMP
    GROUP BY DEPTNO)
SELECT e.ENAME, e.SAL, avg_tab.avg_sal
FROM EMP e, avg_tab
WHERE e.DEPTNO = avg_tab.DEPTNO AND e.SAL > avg_tab.avg_sal;

-- 下面的语句和上面的等效
SELECT e.ENAME, e.SAL, E.avg_sal
FROM EMP e, (SELECT DEPTNO, AVG(SAL)
      FROM EMP
      GROUP BY DEPTNO) as E(DEPTNO, avg_sal)
WHERE e.DEPTNO = E.DEPTNO and e.SAL > avg_sal;

标量子查询

把子查询的结果当做一个值，可以出现在任何可以放值的地方，比如SELECT,WHERE,HAVING
子查询的结果必须是单个元组的单个属性（即一行一列，只有单个值）

带子查询的DELETE语句

举个栗子：

-- 下面的语句可以用来删除所有销售部的员工的信息
DELETE
FROM EMP
WHERE DEPTNO = (
  SELECT DEPT.DEPTNO
  FROM DEPT
  WHERE DNAME = 'SALES'
)

带子查询的INSERT语句

基本格式

1 2	INSERT INTO < 表名 >（字段序列）子查询

举个栗子

-- 由于SQL Server中是没有dual这张系统表的，而Oracle和MySQL数据库中是有的，所以下面的测试是在MySQL中测试的

-- 下面的语句很巧妙的实现了：
--如果部门表中没有部门号为666的部门的话，就将摸鱼部门的信息插入到部门表中
--如果部门表中有部门号为666的部门，则不执行插入操作
INSERT INTO DEPT (DEPTNO, DNAME, LOC)
SELECT '666', 'MOYU', 'DUT'
FROM dual
WHERE NOT EXISTS(SELECT *
              FROM EMP
              WHERE DEPTNO = '666');

UPDATE语句的扩展

有下面几个扩展
- 可以嵌入子查询（这个不在举例，和上面的用法类似）
- case分支结构

case分支结构(可以有效的避免更新次序引发的问题)

第一种用法

CASE WHEN <条件>
        THEN ...
     WHEN <条件>
        THEN ...
        ...
    ELSE ...
END

第二种用法

CASE <条件>
WHEN <值> THEN ...
WHEN <值> THEN ...
...
END

对比两种方法，可以将第一种方法类比成其他编程语言的if-else分支语句，而将第二种用法类比成switch语句

举个栗子

-- 我们的需求是给员工涨工资，工资大于等于3000的涨2%
-- 工资大于等于两千的涨3%
-- 工资小于2000的涨5%

--第一种尝试
UPDATE EMP
SET SAL = SAL * 1.02
WHERE SAL >= 3000;
UPDATE EMP
SET SAL = SAL * 1.03
WHERE SAL >= 2000;
UPDATE EMP
SET SAL = SAL * 1.05
WHERE SAL < 2000;
--会发现上面执行的次序不一样会导致结果不一样，如果把第三个更新语句先执行，那么可能某个工资低于2000的员工涨了一次工资后工资大于两千了，于是执行第二个更新语句的时候又给他涨了一遍工资

-- 第二种尝试
--下面就完美的实现了上面的需求，而且没有执行次序导致的问题
UPDATE EMP
SET SAL = CASE
    WHEN SAL >= 3000 THEN SAL * 1.02
    WHEN SAL >= 2000 THEN SAL * 1.03
    ELSE SAL * 1.05
    END

case分支结构不止可以用在update子句中，在select子句中也是有的

视图
啊啊啊啊啊，这个内容还不是三言两语讲的玩的，下次吧下次吧。>.<

自连接

举个栗子

自然连接（NATURAL JOIN）

可采用自然连接的前提条件：两个表中要有同名字段，并且同名字段的数据类型要一致

当然如果想要执行外连接（左外，右外，全外）也是一样的

在使用自然连接时，同名字段（用于连接的字段）不能写限定名（虽然老师上课这么讲，但实际上呢？）

下面是在多表操作中的一点建议

有一个问题就是，由于数据库只分析到数据字典层面，而无法分到数据层，所以就算两个表中的一组同名字段之间没有任何关系，但是数据类型一致，采用自然连接的话，这一组同名字段也会被作为连接条件。

利用JOIN … USING 可以灵活的指定用什么字段做连接条件

如果两个表中存在同名字段，但是数据类型不一样，这样执行自然连接是会失败的

连接类型和连接条件(就是老师上课提到的课本上那个图)

连接类型

连接条件

FROM子句中的子查询

如果子查询出现在FROM子句中，我们将子查询的结果当做一个临时的关系（表），奕称之为虚表

可以给子查询得到的临时表取一个名字，并给里面的每个字段指定名字

With 子句

with子句的作用是定义一张临时表

WITH子句相当于把FROM中的子查询提出来，如果FROM子句中的子查询比较复杂的时候，这样做是很有好处的，可读性会好很多

WITH 的基本使用方法

举个栗子

标量子查询

把子查询的结果当做一个值，可以出现在任何可以放值的地方，比如SELECT,WHERE,HAVING

子查询的结果必须是单个元组的单个属性（即一行一列，只有单个值）

带子查询的DELETE语句

带子查询的INSERT语句

UPDATE语句的扩展

有下面几个扩展

case分支结构(可以有效的避免更新次序引发的问题)

case分支结构不止可以用在update子句中，在select子句中也是有的

视图