mysql七表查询实例（一）

╰+攻爆jí腚メ 2021-09-23 03:00 232阅读 0赞

开始之前先说点有趣的事：经常听说没有主键就无法进行多表查询，其实这太过于片面了。实际上有些情况没有主键也是可以进行多表查询的（例一，所用到的表格也都是下面的表格，先来个4表查询热热身）。

mysql> select \* from student,teacher,sc,course where (student.s=sc.s and teacher.t=course.t) and course.class=sc.class;  
\+------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+  
| s    | sname  | sage | ssex | t    | tname  | s    | class | score | class | cname  | t    |  
\+------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+  
|    1 | 刘一   |   18 | 男   |    1 | 叶平   |    1 |     1 |    56 |     1 | 语文   |    1 |  
|    1 | 刘一   |   18 | 男   |    2 | 贺高   |    1 |     2 |    78 |     2 | 数学   |    2 |  
|    1 | 刘一   |   18 | 男   |    3 | 杨艳   |    1 |     3 |    67 |     3 | 英语   |    3 |  
|    1 | 刘一   |   18 | 男   |    4 | 周磊   |    1 |     4 |    58 |     4 | 物理   |    4 |  
|    2 | 钱二   |   19 | 女   |    1 | 叶平   |    2 |     1 |    79 |     1 | 语文   |    1 |  
|    2 | 钱二   |   19 | 女   |    2 | 贺高   |    2 |     2 |    81 |     2 | 数学   |    2 |  
|    2 | 钱二   |   19 | 女   |    3 | 杨艳   |    2 |     3 |    92 |     3 | 英语   |    3 |  
|    2 | 钱二   |   19 | 女   |    4 | 周磊   |    2 |     4 |    68 |     4 | 物理   |    4 |  
|    3 | 张三   |   17 | 男   |    1 | 叶平   |    3 |     1 |    91 |     1 | 语文   |    1 |  
|    3 | 张三   |   17 | 男   |    2 | 贺高   |    3 |     2 |    47 |     2 | 数学   |    2 |  
|    3 | 张三   |   17 | 男   |    3 | 杨艳   |    3 |     3 |    88 |     3 | 英语   |    3 |  
|    3 | 张三   |   17 | 男   |    4 | 周磊   |    3 |     4 |    56 |     4 | 物理   |    4 |  
|    4 | 李四   |   18 | 女   |    2 | 贺高   |    4 |     2 |    88 |     2 | 数学   |    2 |  
|    4 | 李四   |   18 | 女   |    3 | 杨艳   |    4 |     3 |    90 |     3 | 英语   |    3 |  
|    4 | 李四   |   18 | 女   |    4 | 周磊   |    4 |     4 |    93 |     4 | 物理   |    4 |  
|    5 | 王五   |   17 | 男   |    1 | 叶平   |    5 |     1 |    46 |     1 | 语文   |    1 |  
|    5 | 王五   |   17 | 男   |    3 | 杨艳   |    5 |     3 |    78 |     3 | 英语   |    3 |  
|    5 | 王五   |   17 | 男   |    4 | 周磊   |    5 |     4 |    53 |     4 | 物理   |    4 |  
|    6 | 赵六   |   19 | 女   |    1 | 叶平   |    6 |     1 |    35 |     1 | 语文   |    1 |  
|    6 | 赵六   |   19 | 女   |    2 | 贺高   |    6 |     2 |    68 |     2 | 数学   |    2 |  
|    6 | 赵六   |   19 | 女   |    4 | 周磊   |    6 |     4 |    71 |     4 | 物理   |    4 |  
\+------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+  
21 rows in set (0.05 second)

话不多说，先让我们来看看，接下来要用到的七个表吧：

mysql> select \* from sc;  
\+------+-------+-------+  
| s    | class | score |  
\+------+-------+-------+  
|    1 |     1 |    56 |  
|    1 |     2 |    78 |  
|    1 |     3 |    67 |  
|    1 |     4 |    58 |  
|    2 |     1 |    79 |  
|    2 |     2 |    81 |  
|    2 |     3 |    92 |  
|    2 |     4 |    68 |  
|    3 |     1 |    91 |  
|    3 |     2 |    47 |  
|    3 |     3 |    88 |  
|    3 |     4 |    56 |  
|    4 |     2 |    88 |  
|    4 |     3 |    90 |  
|    4 |     4 |    93 |  
|    5 |     1 |    46 |  
|    5 |     3 |    78 |  
|    5 |     4 |    53 |  
|    6 |     1 |    35 |  
|    6 |     2 |    68 |  
|    6 |     4 |    71 |  
\+------+-------+-------+  
21 rows in set (0.00 sec)

mysql> desc sc;  
\+-------+---------+------+-----+---------+-------+  
| Field | Type    | Null | Key | Default | Extra |  
\+-------+---------+------+-----+---------+-------+  
| s     | int(11) | YES  |     | NULL    |       |  
| class | int(12) | YES  |     | NULL    |       |  
| score | int(11) | YES  |     | NULL    |       |  
\+-------+---------+------+-----+---------+-------+  
3 rows in set (0.00 sec)

mysql> select \* from student;  
\+------+--------+------+------+  
| s    | sname  | sage | ssex |  
\+------+--------+------+------+  
|    1 | 刘一   |   18 | 男   |  
|    2 | 钱二   |   19 | 女   |  
|    3 | 张三   |   17 | 男   |  
|    4 | 李四   |   18 | 女   |  
|    5 | 王五   |   17 | 男   |  
|    6 | 赵六   |   19 | 女   |  
\+------+--------+------+------+  
6 rows in set (0.00 sec)

mysql> select \* from course;  
\+-------+--------+------+  
| class | cname  | t    |  
\+-------+--------+------+  
|     1 | 语文   |    1 |  
|     2 | 数学   |    2 |  
|     3 | 英语   |    3 |  
|     4 | 物理   |    4 |  
\+-------+--------+------+  
4 rows in set (0.00 sec)

mysql> select \* from teacher;  
\+------+--------+  
| t    | tname  |  
\+------+--------+  
|    1 | 叶平   |  
|    2 | 贺高   |  
|    3 | 杨艳   |  
|    4 | 周磊   |  
\+------+--------+  
4 rows in set (0.00 sec)

mysql> select \* from cadd;  
\+--------+------+  
| cadd   | s    |  
\+--------+------+  
| 上海   |    3 |  
| 广西   |    6 |  
| 江西   |    5 |  
| 深圳   |    2 |  
| 湖南   |    4 |  
| 福建   |    1 |  
\+--------+------+  
6 rows in set (0.02 sec)

mysql> select \* from tadd;  
\+--------+------+  
| tadd   | s    |  
\+--------+------+  
| 福建   |    1 |  
| 深圳   |    2 |  
| 上海   |    3 |  
| 湖南   |    4 |  
| 江西   |    5 |  
| 广西   |    6 |  
\+--------+------+  
6 rows in set (0.00 sec)

mysql> select \* from cphone;  
\+--------+------+  
| cphone | s    |  
\+--------+------+  
|  12345 |    1 |  
|  12346 |    2 |  
|  12347 |    3 |  
|  12348 |    4 |  
|  12349 |    5 |  
|  13349 |    6 |  
\+--------+------+  
6 rows in set (0.02 sec)

mysql> desc cphone;  
\+--------+---------+------+-----+---------+-------+  
| Field  | Type    | Null | Key | Default | Extra |  
\+--------+---------+------+-----+---------+-------+  
| cphone | int(12) | YES  |     | NULL    |       |  
| s      | int(2)  | YES  |     | NULL    |       |  
\+--------+---------+------+-----+---------+-------+  
2 rows in set (0.01 sec)

如果直接select \* from这七个表会怎么样？

结果其实还是蛮好玩的：

|    1 |     1 |    56 |     3 | 英语   |    3 |    5 | 王五   |   17 | 男   |    3 | 杨艳   | ^西C -- query aborted  
西   |    6 | 江西   |    5 |  12347 |    3 |  
\+------+-------+-------+-------+--------+------+------+--------+------+------+------+--------+--------+------+--------+------+--------+------+  
435456 rows in set (2.72 sec)

这个语句执行以后MYSQL就像中毒一样不停地输出，而且都4W多行了，还没有输出完。

那么就让我们来看一看怎么把这七个表的数据整合到一起吧。

mysql> select \* from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.s=student.s) and cphone.s=student.s;  
\+------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+--------+------+--------+------+--------+------+  
| s    | sname  | sage | ssex | t    | tname  | s    | class | score | class | cname  | t    | cadd   | s    | tadd   | s    | cphone | s    |  
\+------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+--------+------+--------+------+--------+------+  
|    1 | 刘一   |   18 | 男   |    1 | 叶平   |    1 |     1 |    56 |     1 | 语文   |    1 | 福建   |    1 | 福建   |    1 |  12345 |    1 |  
|    1 | 刘一   |   18 | 男   |    2 | 贺高   |    1 |     2 |    78 |     2 | 数学   |    2 | 福建   |    1 | 福建   |    1 |  12345 |    1 |  
|    1 | 刘一   |   18 | 男   |    3 | 杨艳   |    1 |     3 |    67 |     3 | 英语   |    3 | 福建   |    1 | 福建   |    1 |  12345 |    1 |  
|    1 | 刘一   |   18 | 男   |    4 | 周磊   |    1 |     4 |    58 |     4 | 物理   |    4 | 福建   |    1 | 福建   |    1 |  12345 |    1 |  
|    2 | 钱二   |   19 | 女   |    1 | 叶平   |    2 |     1 |    79 |     1 | 语文   |    1 | 深圳   |    2 | 深圳   |    2 |  12346 |    2 |  
|    2 | 钱二   |   19 | 女   |    2 | 贺高   |    2 |     2 |    81 |     2 | 数学   |    2 | 深圳   |    2 | 深圳   |    2 |  12346 |    2 |  
|    2 | 钱二   |   19 | 女   |    3 | 杨艳   |    2 |     3 |    92 |     3 | 英语   |    3 | 深圳   |    2 | 深圳   |    2 |  12346 |    2 |  
|    2 | 钱二   |   19 | 女   |    4 | 周磊   |    2 |     4 |    68 |     4 | 物理   |    4 | 深圳   |    2 | 深圳   |    2 |  12346 |    2 |  
|    3 | 张三   |   17 | 男   |    1 | 叶平   |    3 |     1 |    91 |     1 | 语文   |    1 | 上海   |    3 | 上海   |    3 |  12347 |    3 |  
|    3 | 张三   |   17 | 男   |    2 | 贺高   |    3 |     2 |    47 |     2 | 数学   |    2 | 上海   |    3 | 上海   |    3 |  12347 |    3 |  
|    3 | 张三   |   17 | 男   |    3 | 杨艳   |    3 |     3 |    88 |     3 | 英语   |    3 | 上海   |    3 | 上海   |    3 |  12347 |    3 |  
|    3 | 张三   |   17 | 男   |    4 | 周磊   |    3 |     4 |    56 |     4 | 物理   |    4 | 上海   |    3 | 上海   |    3 |  12347 |    3 |  
|    4 | 李四   |   18 | 女   |    2 | 贺高   |    4 |     2 |    88 |     2 | 数学   |    2 | 湖南   |    4 | 湖南   |    4 |  12348 |    4 |  
|    4 | 李四   |   18 | 女   |    3 | 杨艳   |    4 |     3 |    90 |     3 | 英语   |    3 | 湖南   |    4 | 湖南   |    4 |  12348 |    4 |  
|    4 | 李四   |   18 | 女   |    4 | 周磊   |    4 |     4 |    93 |     4 | 物理   |    4 | 湖南   |    4 | 湖南   |    4 |  12348 |    4 |  
|    5 | 王五   |   17 | 男   |    1 | 叶平   |    5 |     1 |    46 |     1 | 语文   |    1 | 江西   |    5 | 江西   |    5 |  12349 |    5 |  
|    5 | 王五   |   17 | 男   |    3 | 杨艳   |    5 |     3 |    78 |     3 | 英语   |    3 | 江西   |    5 | 江西   |    5 |  12349 |    5 |  
|    5 | 王五   |   17 | 男   |    4 | 周磊   |    5 |     4 |    53 |     4 | 物理   |    4 | 江西   |    5 | 江西   |    5 |  12349 |    5 |  
|    6 | 赵六   |   19 | 女   |    1 | 叶平   |    6 |     1 |    35 |     1 | 语文   |    1 | 广西   |    6 | 广西   |    6 |  13349 |    6 |  
|    6 | 赵六   |   19 | 女   |    2 | 贺高   |    6 |     2 |    68 |     2 | 数学   |    2 | 广西   |    6 | 广西   |    6 |  13349 |    6 |  
|    6 | 赵六   |   19 | 女   |    4 | 周磊   |    6 |     4 |    71 |     4 | 物理   |    4 | 广西   |    6 | 广西   |    6 |  13349 |    6 |  
\+------+--------+------+------+------+--------+------+-------+-------+-------+--------+------+--------+------+--------+------+--------+------+  
21 rows in set (0.00 sec)

不管怎么样，没有出现刚才那种“中毒”的情况了，不过这个结果还是不对的。

那么这样呢？

mysql> select student.s,sname,sage,sc.class,score,teacher.t,tname,ssex,cadd,cphone,tadd from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.s=cadd.s) and cphone.s=student.s;  
\+------+--------+------+-------+-------+------+--------+------+--------+--------+--------+  
| s    | sname  | sage | class | score | t    | tname  | ssex | cadd   | cphone | tadd   |  
\+------+--------+------+-------+-------+------+--------+------+--------+--------+--------+  
|    1 | 刘一   |   18 |     1 |    56 |    1 | 叶平   | 男   | 福建   |  12345 | 福建   |  
|    1 | 刘一   |   18 |     2 |    78 |    2 | 贺高   | 男   | 福建   |  12345 | 福建   |  
|    1 | 刘一   |   18 |     3 |    67 |    3 | 杨艳   | 男   | 福建   |  12345 | 福建   |  
|    1 | 刘一   |   18 |     4 |    58 |    4 | 周磊   | 男   | 福建   |  12345 | 福建   |  
|    2 | 钱二   |   19 |     1 |    79 |    1 | 叶平   | 女   | 深圳   |  12346 | 深圳   |  
|    2 | 钱二   |   19 |     2 |    81 |    2 | 贺高   | 女   | 深圳   |  12346 | 深圳   |  
|    2 | 钱二   |   19 |     3 |    92 |    3 | 杨艳   | 女   | 深圳   |  12346 | 深圳   |  
|    2 | 钱二   |   19 |     4 |    68 |    4 | 周磊   | 女   | 深圳   |  12346 | 深圳   |  
|    3 | 张三   |   17 |     1 |    91 |    1 | 叶平   | 男   | 上海   |  12347 | 上海   |  
|    3 | 张三   |   17 |     2 |    47 |    2 | 贺高   | 男   | 上海   |  12347 | 上海   |  
|    3 | 张三   |   17 |     3 |    88 |    3 | 杨艳   | 男   | 上海   |  12347 | 上海   |  
|    3 | 张三   |   17 |     4 |    56 |    4 | 周磊   | 男   | 上海   |  12347 | 上海   |  
|    4 | 李四   |   18 |     2 |    88 |    2 | 贺高   | 女   | 湖南   |  12348 | 湖南   |  
|    4 | 李四   |   18 |     3 |    90 |    3 | 杨艳   | 女   | 湖南   |  12348 | 湖南   |  
|    4 | 李四   |   18 |     4 |    93 |    4 | 周磊   | 女   | 湖南   |  12348 | 湖南   |  
|    5 | 王五   |   17 |     1 |    46 |    1 | 叶平   | 男   | 江西   |  12349 | 江西   |  
|    5 | 王五   |   17 |     3 |    78 |    3 | 杨艳   | 男   | 江西   |  12349 | 江西   |  
|    5 | 王五   |   17 |     4 |    53 |    4 | 周磊   | 男   | 江西   |  12349 | 江西   |  
|    6 | 赵六   |   19 |     1 |    35 |    1 | 叶平   | 女   | 广西   |  13349 | 广西   |  
|    6 | 赵六   |   19 |     2 |    68 |    2 | 贺高   | 女   | 广西   |  13349 | 广西   |  
|    6 | 赵六   |   19 |     4 |    71 |    4 | 周磊   | 女   | 广西   |  13349 | 广西   |  
\+------+--------+------+-------+-------+------+--------+------+--------+--------+--------+  
21 rows in set (0.02 sec)

虽然不小心把CNAME字段漏了，但这不是最重要的错误--显然TADD字段的信息是错误的，而并不是SQL语句错了，而是早在建表的时候就把TADD表弄错了--把S（学生编号）当初T（老师编号来用了）。改正以后：

mysql> select \* from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 ;  
\+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+  
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone |  
\+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+  
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |  
|    1 | 刘一   | 福建   |   18 |     2 |    2 | 数学   |    78 | 贺高   | 深圳   | 男   |  12345 |  
|    1 | 刘一   | 福建   |   18 |     3 |    3 | 英语   |    67 | 杨艳   | 上海   | 男   |  12345 |  
|    1 | 刘一   | 福建   |   18 |     4 |    4 | 物理   |    58 | 周磊   | 湖南   | 男   |  12345 |  
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  
|    2 | 钱二   | 深圳   |   19 |     2 |    2 | 数学   |    81 | 贺高   | 深圳   | 女   |  12346 |  
|    2 | 钱二   | 深圳   |   19 |     3 |    3 | 英语   |    92 | 杨艳   | 上海   | 女   |  12346 |  
|    2 | 钱二   | 深圳   |   19 |     4 |    4 | 物理   |    68 | 周磊   | 湖南   | 女   |  12346 |  
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |  
|    3 | 张三   | 上海   |   17 |     2 |    2 | 数学   |    47 | 贺高   | 深圳   | 男   |  12347 |  
|    3 | 张三   | 上海   |   17 |     3 |    3 | 英语   |    88 | 杨艳   | 上海   | 男   |  12347 |  
|    3 | 张三   | 上海   |   17 |     4 |    4 | 物理   |    56 | 周磊   | 湖南   | 男   |  12347 |  
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  
|    4 | 李四   | 湖南   |   18 |     3 |    3 | 英语   |    90 | 杨艳   | 上海   | 女   |  12348 |  
|    4 | 李四   | 湖南   |   18 |     4 |    4 | 物理   |    93 | 周磊   | 湖南   | 女   |  12348 |  
|    5 | 王五   | 江西   |   17 |     1 |    1 | 语文   |    46 | 叶平   | 福建   | 男   |  12349 |  
|    5 | 王五   | 江西   |   17 |     3 |    3 | 英语   |    78 | 杨艳   | 上海   | 男   |  12349 |  
|    5 | 王五   | 江西   |   17 |     4 |    4 | 物理   |    53 | 周磊   | 湖南   | 男   |  12349 |  
|    6 | 赵六   | 广西   |   19 |     1 |    1 | 语文   |    35 | 叶平   | 福建   | 女   |  13349 |  
|    6 | 赵六   | 广西   |   19 |     2 |    2 | 数学   |    68 | 贺高   | 深圳   | 女   |  13349 |  
|    6 | 赵六   | 广西   |   19 |     4 |    4 | 物理   |    71 | 周磊   | 湖南   | 女   |  13349 |  
\+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+  
21 rows in set (0.00 sec)

让我们用EXPLAIN来看一看这个语句：