[SQL]组合两张表

创建数据库及表

CREATE DATABASE combineTable;
USE combineTable;
SHOW TABLES;

CREATE TABLE Person
(
PersonId int primary key auto_increment NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL
);

CREATE TABLE Address
(
AddressId int primary key auto_increment NOT NULL,
PersonId int NOT NULL,
City VARCHAR(255) NOT NULL,
State VARCHAR(255) NOT NULL 
);

INSERT INTO Person (LastName, FirstName) VALUES ('Qiao', 'James');
INSERT INTO Person (LastName, FirstName) VALUES ('Wang', 'Tom');
INSERT INTO Person (LastName, FirstName) VALUES ('Zhang', 'Joey');

INSERT INTO Address (PersonId, City, State) VALUES (2, 'Beijing', 'China');
INSERT INTO Address (PersonId, City, State) VALUES (3, 'Shanghai', 'China');
INSERT INTO Address (PersonId, City, State) VALUES (4, 'NewYork', 'USA');

查询表

SELECT * FROM Person;
SELECT * FROM Address;

合并两张表

SELECT FirstName, LastName, City, State 
FROM Person left join Address
on Person.PersonId = Address.PersonId