1.建表
create table person (
presonId int,
presonName varchar(20)
);
create table city (
cityId int,
cityName varchar(20)
);
2.插入数据
insert into person values(1,'April'),(3,'Harris'),(5,'Dave');
insert into city values(1,'BeiJing'),(2,'NewYork'),(3,'Hong kong'),(4,'ShaingHai');
3.查询数据
select * from person;
select * from city;
4.内连接
inner join(等值连接) 只返回两个表中联结字段相等的行 。
select * from city inner join person on city.cityId=person.presonId;
5.外连接
5.1 全外连接(full outer join)
全外连接(full outer join)返回参与连接的两个数据集合中的全部数据
select * from city full outer join person on city.cityId=person.presonId;
5.2 左外连接(left outer join)
左外连接(left outer join)返回左表中的所有记录和右表中连接字段相等的记录 。
select * from city left outer join person on city.cityId=person.presonId;
5.3 右外连接(right outer join)
右外连接(right outer join)返回右表中的所有记录和左表中连接字段相等的记录。
select * from city right outer join person on city.cityId=person.presonId;