• Post author:
  • Post category:mysql
  • Post comments:0评论
Like
Like Love Haha Wow Sad Angry

一、简介

  在SQL中,可以使用两种语法形式连接表:一种是前面文章介绍的 FROM 子句,连接条件写在 WHERE 子句的逻辑表达式中,从而实现表的连接。另一种是 ANSI(美国国家标准学会) 连接语法形式,在 FROM 子句中使用 join….on 关键字,连接条件写在on之后,从而实现表的连接。
  语法格式:
    from { 表名 1 [连接类型] join 表名 2 on 连接条件} [,….n]
  from…join…on 可以实现表与表的两两连接,表1和表2连接后还可以继续与表3…..表n,最多可以连接64个表。连接条件放在on关键字后。下面分别介绍各种连接类型的应用。

二、内连接

  内连接即从两个或两个以上的表的笛卡尔积中,选出符合连接条件的数据行。如果数据行无法满足连接条件,则将其丢弃。
  语法格式:
    from 表名1 inner join 表名2 on 连接表达式

1、等值连接

  即从两个或两个以上的表的笛卡尔积中,选出符合连接条件的数据行。
  例如 ,从学生表 ‘student’ 和选课表 ‘sc’ 中查询出学生的详细信息。

mysql> select * from student,sc where student.sid=sc.sid;

mysql> select * from student inner join sc on student.sid=sc.sid;
+------------+-----------+-----+------------+-----------------------+---------+------------+----------+--------+
| Sid        | Sname     | Sex | Birthdate  | Specialty             | AScores | Sid        | Cid      | Scores |
+------------+-----------+-----+------------+-----------------------+---------+------------+----------+--------+
| 2011216001 | 赵成刚    | 男  | 1992-05-05 | 计算机应用技术        |   405.0 | 2011216001 | 16020010 |   96.0 |
| 2011216001 | 赵成刚    | 男  | 1992-05-05 | 计算机应用技术        |   405.0 | 2011216001 | 16020011 |   80.0 |
| 2011216002 | 李婧      | 女  | 1992-01-06 | 计算机应用技术        |   395.5 | 2011216002 | 16020010 |   67.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 2011216003 | 16020012 |   78.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 2011216003 | 16020013 |   87.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 2011216003 | 16020014 |   85.0 |
| 2011216111 | 吴秋娟    | 女  | 1992-08-05 | 软件技术              |   408.0 | 2011216111 | 16020014 |   89.0 |
| 2011216111 | 吴秋娟    | 女  | 1992-08-05 | 软件技术              |   408.0 | 2011216111 | 16020015 |   90.0 |
| 2012216089 | 姜丽丽    | 女  | 1994-10-18 | 计算机网络技术        |   368.0 | 2012216089 | 16020010 |   58.0 |
+------------+-----------+-----+------------+-----------------------+---------+------------+----------+--------+

  查询结果中’student’ 表中的 ‘sid’ 列和 ‘sc’ 表中的 ‘sid’ 列重复,我们可以通过投影操作消除。

mysql> select student.*,cid,scores from student inner join sc on student.sid=sc.sid;
+------------+-----------+-----+------------+-----------------------+---------+----------+--------+
| Sid        | Sname     | Sex | Birthdate  | Specialty             | AScores | cid      | scores |
+------------+-----------+-----+------------+-----------------------+---------+----------+--------+
| 2011216001 | 赵成刚    | 男  | 1992-05-05 | 计算机应用技术        |   405.0 | 16020010 |   96.0 |
| 2011216001 | 赵成刚    | 男  | 1992-05-05 | 计算机应用技术        |   405.0 | 16020011 |   80.0 |
| 2011216002 | 李婧      | 女  | 1992-01-06 | 计算机应用技术        |   395.5 | 16020010 |   67.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 16020012 |   78.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 16020013 |   87.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 16020014 |   85.0 |
| 2011216111 | 吴秋娟    | 女  | 1992-08-05 | 软件技术              |   408.0 | 16020014 |   89.0 |
| 2011216111 | 吴秋娟    | 女  | 1992-08-05 | 软件技术              |   408.0 | 16020015 |   90.0 |
| 2012216089 | 姜丽丽    | 女  | 1994-10-18 | 计算机网络技术        |   368.0 | 16020010 |   58.0 |
+------------+-----------+-----+------------+-----------------------+---------+----------+--------+

2、自然连接

  自然连接是一种特殊的等值连接,进行连接操作的表必须要有相同的字段,并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列,要求相等的列字段,也不一定是公共属性。也可以说自然连接是除去重复属性的等值连接。
  例如 ,从 Education 数据库中查询学生的详细信息、选择的课程及课程成绩。

mysql> select student.sid,sname,sex,specialty,cname,scores
    -> from student
    -> inner join sc on student.sid=sc.sid
    -> inner join course on sc.cid=course.cid;
+------------+-----------+-----+-----------------------+-----------------------------+--------+
| sid        | sname     | sex | specialty             | cname                       | scores |
+------------+-----------+-----+-----------------------+-----------------------------+--------+
| 2011216001 | 赵成刚    | 男  | 计算机应用技术        | 微机组装与维护              |   96.0 |
| 2011216001 | 赵成刚    | 男  | 计算机应用技术        | 操作系统安装与使用          |   80.0 |
| 2011216002 | 李婧      | 女  | 计算机应用技术        | 微机组装与维护              |   67.0 |
| 2011216003 | 郭洪亮    | 男  | 计算机应用技术        | 计算机基础                  |   78.0 |
| 2011216003 | 郭洪亮    | 男  | 计算机应用技术        | 面向过程程序设计            |   87.0 |
| 2011216003 | 郭洪亮    | 男  | 计算机应用技术        | 数据库开发与维护            |   85.0 |
| 2011216111 | 吴秋娟    | 女  | 软件技术              | 数据库开发与维护            |   89.0 |
| 2011216111 | 吴秋娟    | 女  | 软件技术              | 面向对象程序设计            |   90.0 |
| 2012216089 | 姜丽丽    | 女  | 计算机网络技术        | 微机组装与维护              |   58.0 |
+------------+-----------+-----+-----------------------+-----------------------------+--------+

三、外连接

  外连接返回FROM 子句指定至少一个表的所有行,只要这些行符合任何 WHERE 选择条件或 HAVING 限定条件。外连接分为左外连接、右外连接和全外连接。

1、左外连接

  左外连接即对连接左边的表不加限制。
  语法格式:
    from 表名1 left [outer] join 表名2 on 连接表达式
  例如,从学生表 ‘student’ 和选课表 ‘sc’ 中查询出学生的选课信息。

mysql> select * from student left join sc on student.sid=sc.sid;
+------------+-----------+-----+------------+-----------------------+---------+------------+----------+--------+
| Sid        | Sname     | Sex | Birthdate  | Specialty             | AScores | Sid        | Cid      | Scores |
+------------+-----------+-----+------------+-----------------------+---------+------------+----------+--------+
| 2011216001 | 赵成刚    | 男  | 1992-05-05 | 计算机应用技术        |   405.0 | 2011216001 | 16020010 |   96.0 |
| 2011216001 | 赵成刚    | 男  | 1992-05-05 | 计算机应用技术        |   405.0 | 2011216001 | 16020011 |   80.0 |
| 2011216002 | 李婧      | 女  | 1992-01-06 | 计算机应用技术        |   395.5 | 2011216002 | 16020010 |   67.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 2011216003 | 16020012 |   78.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 2011216003 | 16020013 |   87.0 |
| 2011216003 | 郭洪亮    | 男  | 1992-04-12 | 计算机应用技术        |   353.0 | 2011216003 | 16020014 |   85.0 |
| 2011216004 | 吕珊珊    | 女  | 1993-10-11 | 计算机信息管理        |   353.0 | NULL       | NULL     |   NULL |
| 2011216005 | 高全英    | 女  | 1993-07-05 | 计算机信息管理        |   387.5 | NULL       | NULL     |   NULL |
| 2011216006 | 赫莎      | 女  | 1991-08-03 | 计算机信息管理        |   372.0 | NULL       | NULL     |   NULL |
| 2011216007 | 张锋      | 男  | 1992-09-03 | 软件技术              |   389.0 | NULL       | NULL     |   NULL |
| 2011216111 | 吴秋娟    | 女  | 1992-08-05 | 软件技术              |   408.0 | 2011216111 | 16020014 |   89.0 |
.........

  从此例可以看出,连接结果保留了表 ‘student’ 中不满足等值条件的7-10行,表 ‘sc’ 相应的各列都为NULL值,说明7-10行对于的学生还没有选课。
  通过上面这个例子和等值连接的例子对比,可以明显发现内连接和外连接的区别。

2、右外连接

  右外连接即对连接右边的表不加限制。
  语法格式:
    from 表名1 right [outer] join 表名 2 on 连接表达式
  例如,从选课表 ‘sc’ 和课程表 ‘course’ 中查询出没有被选课程信息。

mysql> select * from sc right join course on sc.cid=course.cid;
+------------+----------+--------+----+----------+-----------------------------+--------+
| Sid        | Cid      | Scores | No | Cid      | Cname                       | Credit |
+------------+----------+--------+----+----------+-----------------------------+--------+
| 2011216001 | 16020010 |   96.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216001 | 16020011 |   80.0 |  2 | 16020011 | 操作系统安装与使用          |    2.0 |
| 2011216002 | 16020010 |   67.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216003 | 16020012 |   78.0 |  3 | 16020012 | 计算机基础                  |    3.5 |
| 2011216003 | 16020013 |   87.0 |  4 | 16020013 | 面向过程程序设计            |   10.0 |
| 2011216003 | 16020014 |   85.0 |  5 | 16020014 | 数据库开发与维护            |    6.5 |
| 2011216111 | 16020014 |   89.0 |  5 | 16020014 | 数据库开发与维护            |    6.5 |
| 2011216111 | 16020015 |   90.0 |  6 | 16020015 | 面向对象程序设计            |    7.5 |
| 2012216089 | 16020010 |   58.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| NULL       | NULL     |   NULL |  7 | 16020016 | 数字媒体采集与处理          |    4.0 |
| NULL       | NULL     |   NULL |  8 | 16020017 | 静态网页设计与制作          |    3.0 |
| NULL       | NULL     |   NULL |  9 | 16020018 | web标准设计                 |    4.0 |
| NULL       | NULL     |   NULL | 10 | 16020019 | web应用程序设计             |    7.0 |
| NULL       | NULL     |   NULL | 11 | 16020020 | 计算机组网与管理            |    3.5 |
| NULL       | NULL     |   NULL | 12 | 16020021 | 软件测试与实施              |    2.5 |
+------------+----------+--------+----+----------+-----------------------------+--------+

mysql> select course.cid,cname,credit 
       from sc right join course on sc.cid=course.cid 
       where sid is null ;
+----------+-----------------------------+--------+
| cid      | cname                       | credit |
+----------+-----------------------------+--------+
| 16020016 | 数字媒体采集与处理          |    4.0 |
| 16020017 | 静态网页设计与制作          |    3.0 |
| 16020018 | web标准设计                 |    4.0 |
| 16020019 | web应用程序设计             |    7.0 |
| 16020020 | 计算机组网与管理            |    3.5 |
| 16020021 | 软件测试与实施              |    2.5 |
+----------+-----------------------------+--------+

  从此例可以看出,连接结果保留保留了表 ‘course’ 中不满足等值条件的最后六行,表’sc’中对应的列为NULL。说明最后六个课程还没有被人选过。

3、全外连接

  mysql中并不支持全外连接,我们可以使用 UNION 联合查询实现,后面文章会介绍到 UNION 联合查询。

四、自连接

  表可以通过自连接实现自身的连接运算。自连接可以看作一张表的两个副本之间进行的连接,在自连接中,必须为表指定两个不同的别名,使之在逻辑上成为两张表。
  例如,从选课表 ‘sc’ 中查询出选了至少两门课程的学生。

mysql> select distinct x.sid as '学号' 
       from sc x join sc y on x.sid=y.sid 
       where x.cid<>y.cid;
+------------+
| 学号       |
+------------+
| 2011216001 |
| 2011216003 |
| 2011216111 |
+------------+

五、交叉连接

  交叉连接也叫非限制连接,它将两个表不加限制的组合起来。没有 WHERE 子句的交叉连接,将会产生连接所指定表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的行数。
  语法格式:
    from 表名1 cross join 表名2
  例如下例:

mysql> select count(*) from sc;
+----------+
| count(*) |
+----------+
|        9 |
+----------+

mysql> select count(*) from course;
+----------+
| count(*) |
+----------+
|       12 |
+----------+

mysql> select * from sc cross join course;
+------------+----------+--------+----+----------+-----------------------------+--------+
| Sid        | Cid      | Scores | No | Cid      | Cname                       | Credit |
+------------+----------+--------+----+----------+-----------------------------+--------+
| 2011216001 | 16020010 |   96.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216001 | 16020011 |   80.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216002 | 16020010 |   67.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216003 | 16020012 |   78.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216003 | 16020013 |   87.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216003 | 16020014 |   85.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216111 | 16020014 |   89.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2011216111 | 16020015 |   90.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
| 2012216089 | 16020010 |   58.0 |  1 | 16020010 | 微机组装与维护              |    2.0 |
......
108 rows in set (0.00 sec)

六、多表连接

  语法格式:
    from 表名1 join 表名2 on 连接表达式 join 表名3 on 连接表达式 [,…n]
  例如,从 Education 数据库中查询学生的课程成绩信息。

mysql> select sname,sex,specialty,cname,scores 
       from student,sc,course 
       where student.sid=sc.sid and sc.cid=course.cid;
+-----------+-----+-----------------------+-----------------------------+--------+
| sname     | sex | specialty             | cname                       | scores |
+-----------+-----+-----------------------+-----------------------------+--------+
| 赵成刚    | 男  | 计算机应用技术        | 微机组装与维护              |   96.0 |
| 赵成刚    | 男  | 计算机应用技术        | 操作系统安装与使用          |   80.0 |
| 李婧      | 女  | 计算机应用技术        | 微机组装与维护              |   67.0 |
| 郭洪亮    | 男  | 计算机应用技术        | 计算机基础                  |   78.0 |
| 郭洪亮    | 男  | 计算机应用技术        | 面向过程程序设计            |   87.0 |
| 郭洪亮    | 男  | 计算机应用技术        | 数据库开发与维护            |   85.0 |
| 吴秋娟    | 女  | 软件技术              | 数据库开发与维护            |   89.0 |
| 吴秋娟    | 女  | 软件技术              | 面向对象程序设计            |   90.0 |
| 姜丽丽    | 女  | 计算机网络技术        | 微机组装与维护              |   58.0 |
+-----------+-----+-----------------------+-----------------------------+--------+

mysql> select sname,sex,specialty,cname,scores 
       from student
       join sc on student.sid=sc.sid
       join course on sc.cid=course.cid;
+-----------+-----+-----------------------+-----------------------------+--------+
| sname     | sex | specialty             | cname                       | scores |
+-----------+-----+-----------------------+-----------------------------+--------+
| 赵成刚    | 男  | 计算机应用技术        | 微机组装与维护              |   96.0 |
| 赵成刚    | 男  | 计算机应用技术        | 操作系统安装与使用          |   80.0 |
| 李婧      | 女  | 计算机应用技术        | 微机组装与维护              |   67.0 |
| 郭洪亮    | 男  | 计算机应用技术        | 计算机基础                  |   78.0 |
| 郭洪亮    | 男  | 计算机应用技术        | 面向过程程序设计            |   87.0 |
| 郭洪亮    | 男  | 计算机应用技术        | 数据库开发与维护            |   85.0 |
| 吴秋娟    | 女  | 软件技术              | 数据库开发与维护            |   89.0 |
| 吴秋娟    | 女  | 软件技术              | 面向对象程序设计            |   90.0 |
| 姜丽丽    | 女  | 计算机网络技术        | 微机组装与维护              |   58.0 |
+-----------+-----+-----------------------+-----------------------------+--------+

  文章大部分内容来源:《 SQL Server 2008 R2 数据库技术及应用(第3版)》。以上内容基于数据库版本mysql 5.7.28。

Like
Like Love Haha Wow Sad Angry

发表评论