当前位置: Oracle DBA培训网-优技培训 >> MySQL培训 > MySQL培训教程 >

MySQL培训课程:Select多个表联合查询

MySQL培训课程:Select多个表联合查询

MySQL培训课程:Select多个表联合查询,查询多个表,FROM子句列出表名,并用逗号分隔,因为查询需要从他们两个拉出信息。

当组合(联结-join)来自多个表的信息时,你需要指定在一个表中的记录怎样能匹配其它表的记录。这很简单,因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。

因为name列出现在两个表中,当引用列时,你一定要指定哪个表。这通过把表名附在列名前做到。

现在有一个event表:

mysql>select * from event;

+----------+------------+----------+-----------------------------+

| name | date | type | remark |

+----------+------------+----------+-----------------------------+

| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male |

| Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male |

| Buffy | 1994-06-19 | litter | 3 puppies, 3 female |

| Chirpy | 1999-03-21 | vet | needed beak straightened |

| Slim | 1997-08-03 | vet | broken rib |

| Bowser | 1991-10-12 | kennel | NULL |

| Fang | 1991-10-12 | kennel | NULL |

| Fang | 1998-08-28 | birthday | Gave him a new chew toy |

| Claws | 1998-03-17 | birthday | Gave him a new flea collar |

| Whistler | 1998-12-09 | birthday | First birthday |

+----------+------------+----------+-----------------------------+

当他们有了一窝小动物时,假定你想要找出每只宠物的年龄。 event表指出何时发生,但是为了计算母亲的年龄,你需要她的出生日期。既然它被存储在pet表中,为了查询你需要两张表:

mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark

-> FROM pet, event

-> WHERE pet.name = event.name AND type = "litter";

+--------+------+-----------------------------+

| name | age | remark |

+--------+------+-----------------------------+

| Fluffy | 2.27 | 4 kittens, 3 female, 1 male |

| Buffy | 4.12 | 5 puppies, 2 female, 3 male |

| Buffy | 5.10 | 3 puppies, 3 female |

+--------+------+-----------------------------+

同样方法也可用于同一张表中,你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较,联结一个表到自身有时是有用的。例如,为了在你的宠物之中繁殖配偶,你可以用pet联结自身来进行相似种类的雄雌配对:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

-> FROM pet AS p1, pet AS p2

-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

+--------+------+--------+------+---------+

| name | sex | name | sex | species |

+--------+------+--------+------+---------+

| Fluffy | f | Claws | m | cat |

| Buffy | f | Fang | m | dog |

| Buffy | f | Bowser | m | dog |

+--------+------+--------+------+---------+

在这个查询中,我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。

技术沙龙MORE+

Oracle技术沙龙:【数据保护顶尖技术-MAA】首次全国首播12c MAA技术 云数据库加上MAA技术组成最豪华的技...
Oracle技术沙龙第49期:【Oracle 数据库云技术分享】Oracle技术沙龙第49期:【Oracle 数据库云技术分享】本次技术沙...
Oracle技术沙龙第48期:【Oracle 12c RAC安装】小编:今天讲什么内容呢?以后有什么用呢? 大师:今天讲的是12c...
我要参加技术沙龙

关闭