数据库笔记（六）

Posted on | Edited on

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

在开始今天的摸鱼大业之前，让我们继续延用之前用的表(为了演示方便，我们在EMP表中多插入了一条数据，这个数据的部门号是空值)，用于演示下面的例子(´`)

BONUS.png

DEPT.png

EMP.png

SALGRADE.png

FROM 中的子查询

由于FROM中的子查询涉及到多表的操作，所以准备放在后面讲，先在此处做个小提示，等学完多表操作后，会回来提这个，到时候我会在这边贴一个链接


多表查询中的笛卡尔积（全匹配问题）

  • 如果在进行多表查询操作时没有连接条件 ，则会进行全匹配，结果集相当于两个表进行笛卡尔积

    • 举个栗子
      1
      2
      3
      4
      -- 下面的操作本来是想输出所有的员工的姓名以及其所在的部门名
      -- 但是由于没有指定连接条件，所以结果将会是每一个员工与每一个部门组合的结果
      SELECT ENAME, DNAME
      FROM EMP, DEPT;

    结果如下(结果太多了，总共有52条，下面的图片中只截取了前面的一部分)：

    1.png

  • 在实际开发过程中笛卡尔积得到的结果一般是没有太大意义的，所以应当尽量避免==>添加连接条件(WHERE emp.deptno = dept.deptno)

    • 举个栗子：
      1
      2
      3
      4
      -- 这个栗子就基本完成了上面的需求
      SELECT ENAME, DNAME
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO;

    得到如下结果：

    2.png

    • 但是仔细观察会发现，上面的数据只有13条，而员工表里面的数据有14条，其中Sunny所在部门号为空值，在部门表中匹配不到结果，故没有出现在结果集当中。（这是因为采用内连接的缘故，下面将会对内外连接分别做分析）

在WHERE子句中书写连接条件的内连接

这种在where子句中写连接条件的实现方式，不是SQL标准中的标准用法，但是大多数数据库都支持这种用法，所以这种用法已经成了一种事实标准。在内连接的范畴中，这种用法与SQL标准的内连接用法是等价的

  • 等值内连

    • 上述那个例子就是最常见的等值内连

      1
      2
      3
      SELECT ENAME, DNAME
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO;

    • 等值内连就是在where中书写多表的连接条件的时候比较两个表中某一个或多个字段的值，值相等的则匹配（常见的就是用一个表的外键与另一个表中对应的外键的参照键进行比较）

    • 必须是两张表中能够满足连接条件的数据才会出现在结果集当中（不单是等值连接，下面讲的非等值连接也是，所有内连接都应该瞒住这个）

      • 再来举个上面举过的栗子
        1
        2
        3
        4
        -- 下面的操作得到了所有员工的名字以及其所在部门的名字
        SELECT ENAME, DNAME
        FROM EMP, DEPT
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

      得到如下结果：

      2.png

      • 我们发现员工名为Sunny的员工数据和部门号为40的部门数据都没有出现在结果集当中。因为Sunny的部门号为空值，不等于部门表中任意一行数据的部门号，故找不到匹配；因为在员工表中没有员工的部门号为40，所以部门号为40的部门也没有出现在结果集当中。（只有满足连接条件，并且成功找到匹配的数据才会出现杂结果集当中）

    • 这种用where书写的多表连接语句等价于SQL标准中的内连接（inner join）

      • 上面的语句也可以写成下面这种形式
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        -- 下面的语句和上面例子中的语句是等价的
        SELECT ENAME, DNAME
        FROM EMP INNER JOIN DEPT
            ON EMP.DEPTNO = DEPT.DEPTNO

        -- SQL标准中还有下面这种用法（课本上有提到，但是显然SQL Server不支持这种用法）
        -- 表示的是利用DEPNOT这个字段来进行两表的连接（前提是这两个表中要有同名字段）
        -- 如果支持这种用法的数据库执行下面操作的话得到的结果和上面的是一样的（经验证，MySQL数据库是支持这种用法的）
        SELECT ENAME, DNAME
        FROM EMP INNER JOIN DEPT
            USING(DEPNOT)

    • 对于两表中的同名字段，在使用的时候必须用表名或者表别名加以限定，不然SQL语句会有歧义，导致无法正确被解析

      • 举个栗子
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        -- 我们想在上面例子的基础上，多显示一个部门号
        -- 如果写成下面这样，就会报错，因为SQL解析器不知道DEPTNO指的是EMP表的还是DEPT表的
        SELECT ENAME, DNAME, DEPNOT
        FROM EMP, DEPT
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

        -- 正确的写法应该是这样的
        SELECT ENAME, DNAME, EMP.DEPTNO
        FROM EMP, DEPT
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

        --当然也可以用DEPT来限定
        SELECT ENAME, DNAME, DEPT.DEPTNO
        FROM EMP, DEPT
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

        -- 不过最好用表别名来限定，比较简洁一点
        SELECT ENAME, DNAME, e.DEPTNO
        FROM EMP e, DEPT d
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

    • 如果对一个表起了别名之后就不能再使用原表名了，而要换成其别名

      • 举个栗子
        1
        2
        3
        4
        -- 下面这个语句执行的话是要报大错滴，因为已经给EMP表起了别名e，就不能再用原表名EMP了
        SELECT ENAME, DNAME, EMP.DEPTNO
        FROM EMP e, DEPT d
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

    • 如果再考虑效率问题，在进行多表操作时，最好所有字段都用表名或者表别名限定，这样可以免去SQL解析器帮你分析某个字段属于哪个表的开销，可以在一定程度上提高执行效率

      • 举个栗子
        1
        2
        3
        SELECT e.ENAME, d.DNAME, e.DEPTNO
        FROM EMP e, DEPT d
        WHERE EMP.DEPTNO = DEPT.DEPTNO;

  • 不等值连接

    • 举个栗子
      1
      2
      3
      4
      -- 下面的语句就查处了所有员工的姓名、工资、以及其工资等级
      SELECT e.ENAME, e.SAL, g.GRADE
      FROM EMP e, SALGRADE g
      WHERE e.SAL >= g.LOSAL AND  e.SAL <= g.HISAL;

    得到如下结果：

    3.png

    • 不等值连接就是内连接中除了通过比较值相同来进行连接以外的其他内连接操作

  • n个表相连，至少需要n-1个连接条件，要不然就会在连接过程中出现笛卡尔积

  • 多表的连接条件一般都是建立在外键和外键的参照键之间，采用等值连接

SQL内连接的标准写法

  • JOIN … ON …
  • 举个栗子
    1
    2
    3
    4
    5
    6
    -- 下面的语句就是SQL标准中多表内连接的写法
    SELECT *
    FROM TABLE1 t1
      JOIN TABLE2 t2 ON ...
      JOIN TABLE3 t3 ON ...
      JOIN TABLE4 t4 ON ...

外连接

内连接的结果是外连接结果的一个子集，外连接的结果中还可以包括只在一张表中出现，并且在另一张表种找不到匹配的结果

  • 左外连接(LEFT OUTER JOIN)

    • 包含JOIN关键字左表中的所有数据（即便某个数据在右表中找不到匹配）
    • 举个栗子
      1
      2
      3
      4
      5
      6
      -- 下面的语句与上面的例子类似
      -- 同样是得到所有员工的名字以及其所在部门名
      -- 不同的是采用左外连接以后Suuny的数据会出现在结果集中了
      SELECT ENAME, DNAME
      FROM EMP LEFT OUTER JOIN DEPT
          ON EMP.DEPTNO = DEPT.DEPTNO;

    得到如下结果：

    4.png

  • 右外连接(RIGHT OUTER JOIN)

    • 包含JOIN关键字右表中的所有数据(即便某个数据在左表中找不到匹配
    • 举个栗子
      1
      2
      3
      4
      5
      -- 还是这个栗子，但不同的是我们把LEFT改成了RIGHT
      -- 会发现，部门号为40的部门信息显示出来了
      SELECT ENAME, DNAME
      FROM EMP RIGHT OUTER JOIN DEPT
          ON EMP.DEPTNO = DEPT.DEPTNO;

    得到如下结果：

    5.png

  • 对比可以发现左外连接和右外连接的效用其实是一样的，只要吧JOIN两边表的位置对调一下，两者就可相互转换。（使用时随意，习惯怎么用就怎么用就好）

  • 全外连接

    • JOIN关键字两边的表的所有数据都会出现在结果集当中，得到的结果其实就是左外连接和右外连接结果集的并集
    • 举个栗子：
      1
      2
      3
      SELECT ENAME, DNAME
      FROM EMP FULL OUTER JOIN DEPT
          ON EMP.DEPTNO = DEPT.DEPTNO;

    得到如下结果：

    6.png

  • 在进行多表连接的时候采用WHERE和ON的区别

    • 其一，在进行外连接的时候，必须用ON

    • 举个栗子

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      -- 下面的语句做了简单的外连接操作
      SELECT *
      FROM EMP
        LEFT OUTER JOIN DEPT  ON EMP.DEPTNO = DEPT.DEPTNO;

      -- 下面的语句执行是会报错的，因为没有加on
      SELECT *
      FROM EMP
        LEFT OUTER JOIN DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO;

    • 其二，如果在on子句中指定连接条件，并在where子句中出现其余条件，这样的SQL插叙通常更容易让人读懂

    • 所以在执行内连接的时候，on和where的使用是没有多大区别的，但是在执行外连接的时候就必须用on了。所以建议就是在on子句中指定连接条件，并在where子句中出现其余条件
坚持原创技术分享，您的支持将鼓励我继续创作！