MySQL 多表查询

使用的代码

create database ckgl character set=utf8;

use ckgl;

create table warehouses(wnum char(3) primary key,area smallint null,city char(12));
insert into warehouses values('01',300,'北京');
insert into warehouses values('02',500,'天津');
insert into warehouses values('03',200,'上海');
insert into warehouses values('04',300,'北京');
insert into warehouses values('05',600,'上海');

create table employees(employee_id char(4) primary key,wnum char(2),salary smallint,name char(10));
insert into employees values('z1','01',3000,'King');
insert into employees values('z2','01',3500,'Abel');
insert into employees values('z3','01',5000,'Peter');
insert into employees values('z4','02',4000,'小宁');
insert into employees values('z5','02',2000,'Loction');
insert into employees values('z6','03',3000,'UUU');
insert into employees values('z7','03',6000,'小米');

create table departments(onum char(5) primary key, employee_id char(3), location_id char(3),amount int);
insert into departments values('d1','z1','s1',8000);
insert into departments values('d2','z2','s2',6500);
insert into departments values('d3','z2','s3',10000);
insert into departments values('d4','z3','s3',5000);
insert into departments values('d5','z4','s4',20000);
insert into departments values('d6','z5','s5',9000);
insert into departments values('d7','z5','s6',5500);
insert into departments values('d8','z6','s7',8800);

create table locations(location_id char(4) primary key,sname char(14),city char(12));
insert into locations values('s1','晨星公司','北京');
insert into locations values('s2','华大公司','天津');
insert into locations values('s3','芯片生产厂','北京');
insert into locations values('s4','全能公司','重庆');
insert into locations values('s7','万达公司','重庆');
insert into locations values('s5','索尼公司','北京');
insert into locations values('s6','字节公司','上沙');

# 查询员工为 ’UUU‘ 的人在哪里工作

select * from employees where name ='UUU';

select * from departments where employee_id = 'z6';

select * from locations where location_id = 's7';

# 多表查询实现
# 笛卡儿积（交叉连接）连接

select employees.employee_id,departments.location_id from employees,departments
# 缺少了多表连接的条件
where employees.employee_id = departments.employee_id;
# 关联查询

# 给表取别名  在 select 或 where 中使用表名的活,则必须使用表的表名  而不能在使用表的原名

select emp.employee_id,dept.location_id
from employees emp,departments dept where emp.employee_id = dept.employee_id;


# 查询员工的  employ_id,name, city

select e.employee_id,name,city from employees e,departments d,locations l
where e.employee_id = d.employee_id and d.employee_id = l.location_id

#  多表查询的分类 :
#1 (非)等值连接
#2 (非)自连接
#3 (非)内连接

#1 (非)等值连接
select e.employee_id,name,city from employees e,departments d,locations l
# where e.employee_id >= d.employee_id and d.employee_id >= l.location_id;
where e.employee_id = d.employee_id;
# and d.employee_id = l.location_id;

#2 (非)自连接

#3 (非)内连接

#     外连接的分类     左外连接   右外连接   满外连接(full join)

select employees.employee_id,departments.location_id from employees
left join departments on employees.employee_id = departments.employee_id
join locations l on departments.location_id = l.location_id;

select employees.employee_id,departments.location_id from employees
right join departments on employees.employee_id = departments.employee_id
join locations l on departments.location_id = l.location_id;

select employees.employee_id,departments.location_id from employees,departments
where employees.employee_id = departments.employee_id;

# 内连接  join...on....
select employees.employee_id,departments.location_id from employees join departments
on employees.employee_id = departments.employee_id
join locations l on departments.location_id = l.location_id;

UNION  操作符
# UNION ALL  操作符

# 7种 join 的实现

# select e.employee_id,d.employee_id from employees e join departments d on e.employee_id = d.employee_id;
#
# select e.employee_id,d.employee_id from employees e left join departments d on e.employee_id = d.employee_id;
#
# select e.employee_id,d.employee_id from employees e right join departments d on e.employee_id = d.employee_id;
#