多表查询语法

create table staff(
		id int primary key auto_increment,
		name varchar(32),
		sex varchar(32),
		dep_id int
);

create table dep(
		id int primary key auto_increment,
		dep_name varchar(32)
);

insert into staff(name,sex,dep_id) VALUES
('张三','男',1),
('李四','男',1),
('王五','女',1),
('赵六','男',2),
('孙七','女',2),
('周八','女',2),
('王炸','男',null);
insert into dep(dep_name) VALUES('研发部'),('销售部'),('摸鱼部');

-- 多表查询（由于没有条件约束 出现了12数据有六条重复数据 （笛卡尔积））
select * from staff,dep;

-- 加上where条件约束()
select * from staff,dep where staff.dep_id = dep.id;

-- 隐式内连接
select staff.id,staff.name,staff.sex,staff.dep_id,dep.dep_name from staff,dep where staff.dep_id = dep.id;

--  给表取别名
select t1.id,t1.name,t1.sex,t1.dep_id,t2.dep_name from staff t1,dep t2 where t1.dep_id = t2.id;


-- 显式内连接(这个也跟隐式内连接一样可以取别名，也可以选择查询不同的字段)
select * from staff INNER join dep ON staff.dep_id = dep.id;

-- 显式内连接的 INNER 可以省略
select * from staff  join dep ON staff.dep_id = dep.id;


-- 左外连接 
select * from staff LEFT outer join dep on staff.dep_id = dep.id; 

-- 右外连接
select * from staff RIGHT outer join dep on staff.dep_id = dep.id;-- 左右外连接的转换（只需要把两个表换个位置就可以）



-- 单行单列（查询部门为研发部的员工信息） 多表查询之子查询
select id from dep WHERE dep_name = '研发部'; -- 这个为子查询
SELECT * from staff WHERE dep_id = (select id from dep WHERE dep_name = '研发部');

-- 多行单列（查询部门为研发部和销售部的员工信息）
select id from dep WHERE dep_name = '研发部' or dep_name = '销售部';
SELECT * FROM staff WHERE dep_id in (select id from dep WHERE dep_name = '研发部' or dep_name = '销售部');

 -- 多行多列（查询id大于3的员工信息和部门信息）
select * from staff WHERE id > 3; -- id大于3的员工信息

SELECT * FROM staff,dep WHERE staff.dep_id = dep.id; -- 员工信息和部门信息

SELECT * from (select * from staff WHERE id > 3) t1, dep WHERE t1.dep_id = dep.id; -- 结合一下就是多行多列

多表查询案例

create table staff(
		id int primary key auto_increment,
		name varchar(32),
		job_id int,
		mgr int,
		joindate date,
		salary double(7,2),
		bonut DOUBLE(7,2),
		dep_id int
);

create table job(
	  id int primary key auto_increment,
		jname varchar(32),
		description varchar(50)
);

create table dep(
		id int primary key auto_increment,
		dname varchar(32),
		address varchar(50)
);

create table salarygrabe(
			grabe int,
			losalary int,
			hisalary int 
);

alter table staff add constraint fk_staff_job foreign key (job_id) references job(id);
alter table staff add constraint fk_staff_dep foreign key (dep_id) references dep(id);

INSERT into staff(name,job_id,mgr,joindate,salary,bonut,dep_id) VALUES
('孙悟空',4,1004,'2000-12-17',8000,NULL,2),
('卢俊义',3,1006,'2001-02-20',16000,3000,3),
('林冲',3,1006,'2001-02-22',12500,5000,3),
('唐僧',2,1009,'2001-04-02',29750,NULL,2),
('李逵',4,1006,'2001-09-28',12500,14000,3),
('宋江',2,1009,'2001-05-01',28500,NULL,3),
('刘备',2,1009,'2001-09-01',24500,NULL,1),
('猪八戒',4,1004,'2007-04-19',30000,NULL,2),
('罗贯中',1,NULL,'2001-11-17',50000,NULL,1);


insert into job(jname,description) VALUES
('董事长','管理整个公司，接单'),
('经理','管理部门员工'),
('销售员','向客人销售东西'),
('文员','使用办公软件');

insert into dep(dname,address) VALUES
('教研部','北京'),
('学工部','上海'),
('销售部','杭州');

insert into salarygrabe(grabe,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
		
-- 查询所有员工信息。员工id、员工姓名、工资、职位名称、职位描述 (使用多表查询 隐式内连接 或者 显式内连接)
select staff.id,staff.name,staff.salary,job.jname,job.description from staff,job WHERE staff.job_id = job.id;

select staff.id,staff.name,staff.salary,job.jname,job.description from staff INNER JOIN job on staff.job_id = job.id;

-- 查询所有员工信息。员工id、员工姓名、工资、职位名称、职位描述、部门名称、部门位置 (跟上面的一样，多了个表而已)
select staff.id,staff.name,staff.salary,job.jname,job.description,dep.dname,dep.address from staff,job,dep WHERE staff.job_id =job.id and staff.dep_id = dep.id;

select staff.id,staff.name,staff.salary,job.jname,job.description,dep.dname,dep.address from staff join job,dep WHERE staff.job_id =job.id and staff.dep_id = dep.id;

-- 查询员工姓名、工资、工资等级
SELECT staff.name,staff.salary,salarygrabe.grabe from staff,salarygrabe where staff.salary BETWEEN salarygrabe.losalary AND salarygrabe.hisalary;


-- 查询员工姓名、工资、职位名称、职位描述、部门名称、部门位置、工资等级
select staff.name,staff.salary,job.jname,job.description,dep.dname,dep.address,salarygrabe.grabe from staff,job,dep,salarygrabe 
WHERE staff.job_id = job.id and staff.dep_id = dep.id and staff.salary between salarygrabe.losalary and salarygrabe.hisalary;

-- 查询部门编号、部门名称、部门位置、部门人数 (子查询)

SELECT dep_id,COUNT(*) from staff GROUP BY dep_id;

SELECT * from dep,(SELECT dep_id,COUNT(*) from staff GROUP BY dep_id) t1 WHERE dep.id = t1.dep_id;