力扣数据库mysql 简单题

1.组合两张表

表: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

表: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
SQL语句:

select Person.firstName,Person.lastName,Address.city,Address.state from  Person LEFT JOIN Address on Person.personId = Address.personID;

上述语句是连续查询(交叉连接查询、内连接查询、外连接查询)  中的外连接:

举例:部门  员工表  黑马程序员例子

create table department(
did int(4) not null primary key,
danme varchar(36)
);
CREATE table employee(
id int(4) not null primary key,
name varchar(36),
age int(2),
did int(4) not null
)

insert into department values(1,'网络部'),(2,'媒体部'),(5,'人事部');
insert into employee values(1,'王红',20,1),(2,'李强',22,1),(3,'赵四',20,2),(4,'郝娟',20,4);

select * from  department   cross join employee；

表1的行数   *    表2的行数

内连接:

select employee.name,department.danme
from department join employee
on department.did = employee.did;

上述和where子句差不多

外连接:(左连接)

select department.did,department.danme,employee.name from department left JOIN
employee on department.did=employee.did;

 外连接:(右连接）

select department.did,department.danme,employee.name from department right JOIN
employee on department.did=employee.did;