SQL学习笔记1（CRUD&联接）

先说inner join ：

 

给出两个表：

 

Employee：

 

 

 

 

Departmen：

 

 

现在从两个表来联合查询，先给出我想要的结果。。

 

 

也就是将部门载入……………………………………………………………………

 

select 学号=em.id,员工=em.employee,部门=dm.department,密码=em.pwd from Employee em inner join Department dm on em.department=dm.id 

 

下面试试 left join 和 right join

 

注意啊：我把Employee表中的”走要以草“删去了，只为了说明right join

 

 

看left join

 

select * from Employee em right join Department dm on em.department=dm.id 

 

截图：

 

 

 

看right join 

 

select * from Employee em right join Department dm on em.department=dm.id

 

截图： 

 

 

 

看完inner join 、 left join 、 right join 也就明白了full join了。。下面是full join，，左右开弓，，两边都select出来了啊。。

 

select * from Employee em full join Department dm on em.department=dm.id 

 

截图：

 

 

UNION:union与join的区别在于，union是列合并，join是行合并。

select em.id,em.employee from Employee em union select dm.id,dm.department from Department dm 

 

 

union all

 

 

select em.id,em.employee from Employee em union all select dm.id,dm.department from Department dm