6.mysql 基础之多表查询

数据准备创建表 emploeescreate table emploees(id int,name varchar(20),gender varchar(100),saraly double(10,2),department_id int,phone_number int,job_id varchar(20),manger_id varchar(10))数据准备创建表 departmentscreate table departments(department_id int,department_name varchar(20),manger_id varchar(10)location_id varchar(20))数据准备创建表 locationscreate table locations(location_id varchar(20),street_address varchar(20),city varchar(10)state_province varchar(20))数据准备 插入数据insert into emploees values(101,tom,m,5000,20,zhangsan);insert into emploees values(102,tom1,m,6000,30,zhangsan);insert into emploees values(103,tom2,m,7000,40,zhangsan);insert into emploees values(104,tom3,m,8000,50,zhangsan);insert into emploees values(105,tom4,m,9000,60,zhangsan);insert into emploees values(106,tom5,m,10000,70,zhangsan);insert into emploees values(107,kate1,f,5000,20,lisi);insert into emploees values(108,kate2,f,6000,30,lisi);insert into emploees values(109,kate3,f,7000,40,lisi);insert into emploees values(107,kate4,f,8000,50,lisi);insert into emploees values(107,kate5,f,9000,60,lisi);insert into emploees values(107,kate6,f,9000,60,null);#查询所有员工姓名部门编号部门名称 左连接一般带有‘所有’都为外连接select e.name,d.department_id,department_name from emploees eleft joindepartments done.department_idd.department_id#查询员工姓名部门编号部门名称,城市三表连查select e.name,d.department_id,department_namel.city from emploees eleft joindepartments d on e.department_idd.department_idleft join location lon d.location_id l.location_id#查询部门编号为20的员工姓名部门编号部门名称内连接关键字inner 可以省略select e.name,d.department_id,department_name from emploees ejoindepartments done.department_idd.department_idwhere d.department_id20#查询员工姓名和领导信息自连接select e.name,m.name from emploees e join emploees mon e.id m.manger_id#查询没有员工的部门信息select e.name,d.department_id,department_name from departments d leftjoinemploees eond.department_id e.department_idwhere e.department_id is null