力扣数据库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;
