Example of implementing seven-table query in mysql (1)

Let me tell you something interesting before I start: I often hear that multi-table queries cannot be performed without a primary key. In fact, this is too one-sided. In fact, in some cases, multi-table queries can be performed without a primary key (in example 1, the tables used are also the following tables, let’s warm up with a 4-table query first).

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)

Without further ado, let’s take a look at the seven tables that will be used next:

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)

What happens if you directly select * from these seven tables?

The result is actually quite interesting:

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

After this statement is executed, MYSQL keeps outputting like it is poisoned, and there are more than 40,000 rows, and it has not been output yet.

So let’s take a look at how to integrate the data from these seven tables.

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)

No matter what, there is no such "poisoning" situation as before, but the result is still wrong.

So what about this?

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)

Although the CNAME field was accidentally omitted, this is not the most important error-obviously the information in the TADD field is wrong, not that the SQL statement is wrong, but that it was built long ago. When making the table, I made a mistake in the TADD table - I used S (student number) instead of T (teacher number). After correction:

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)

Let us use EXPLAIN to take a look at this statement:

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)

Original address: https://blog.csdn.net/number1killer/article/details/77896758 Author: number1killer

The above is the detailed content of Example of implementing seven-table query in mysql (1). For more information, please follow other related articles on the PHP Chinese website!