mysql七表查询实例（一）

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

ysql> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| s     | int(11)  | YES  |     | NULL    |       |
| sname | char(32) | YES  |     | NULL    |       |
| sage  | int(11)  | YES  |     | NULL    |       |
| ssex  | char(8)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.02 sec)

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

mysql> desc course;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| class | int(12)  | YES  |     | NULL    |       |
| cname | char(32) | YES  |     | NULL    |       |
| t     | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

mysql> desc teacher;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| t     | int(11)  | YES  |     | NULL    |       |
| tname | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 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> desc cadd;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| cadd  | char(22) | NO   | PRI | NULL    |       |
| s     | int(2)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql> desc tadd;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| tadd  | char(21) | YES  |     | NULL    |       |
| s     | int(12)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 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来看一看这个语句：

mysql> explain 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 ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                               |
|  1 | SIMPLE      | course  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | tadd    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | sc      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   21 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | cadd    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | cphone  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)