数据库的设计与开发 Oracle Java EE实训教材系列教学PPT课件.ppt

单表查询的局限（二） 要想知道代码所对应的国家，还需从Country表中获得： mysql SELECT Code, Name, Continent, Population - FROM Country WHERE Code IN (GBR,CAN); +------+----------------+---------------+------------+ | Code | Name | Continent | Population | +------+----------------+---------------+------------+ | CAN | Canada | North America | | | GBR | United Kingdom | Europe | | +------+----------------+---------------+------------+ 虽然最终获得了城市和国家的数据，但这是在两次单独的查询中完成的。 如果希望通过一次性的查询来获取来自两个表中的组合数据，应使用联接查询。 * 创建演示用表（一） 为了便于讨论和理解，首先创建两个演示用的SimpleCountry和SimpleCity表。 创建SimpleCity表： mysql CREATE TABLE SimpleCity AS - SELECT Name AS CityName, CountryCode, Population AS CityPop - FROM City WHERE Name Like London; 其结果为： mysql SELECT * FROM SimpleCity; +----------+-------------+---------+ | CityName | CountryCode | CityPop | +----------+-------------+---------+ | London | GBR | 7285000 | | London | CAN | 339917 | +----------+-------------+---------+ * 创建演示用表（二） 创建SimpleCountry表： mysql CREATE TABLE SimpleCountry AS - SELECT Code, Name AS Country, Population AS CountryPop - FROM Country WHERE Code In (CAN,GBR); 其结果为： mysql SELECT * FROM SimpleCountry; +------+----------------+------------+ | Code | Country | CountryPop | +------+----------------+------------+ | CAN | Canada | | | GBR | United Kingdom | | +------+----------------+------------+ * 使用SQL语句联接表 使用SQL语句联接表的方法之一是“逗号联接”。 例如： mysql SELECT * FROM SimpleCity, SimpleCountry; +----------+-------------+---------+------+----------------+------------+ | CityName | CountryCode | CityPop | Code | Country | CountryPop | +----------+-------------+---------+------+----------------+------------+ | London | GBR | 7285000 | CAN | Canada | | | London | CAN | 339917 | CAN | Canada | | | London | GBR | 7285000 | GBR | United Kingdom | | | London