mysql中實作七表查詢實例（一）
- 黄舟轉載
- 2017-09-09 14:25:351237瀏覽
開始之前先說點有趣的事：常常聽說沒有主鍵就無法進行多表查詢，其實這太過於片面了。其實有些情況沒有主鍵也是可以進行多表查詢的（例一，所用到的表格也都是下面的表格，先來個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)
原文網址：https://blog.csdn.net/number1killer/article/details/77896758 作者： number1killer
以上是mysql中實作七表查詢實例（一）的詳細內容。更多資訊請關注PHP中文網其他相關文章！
陳述：
本文轉載於：number1killer。如有侵權，請聯絡admin@php.cn刪除
上一篇：MSSQL 預存程序加密詳解下一篇：MSSQL 預存程序加密詳解