JavaWeb_多级查询 案例_表数据及练习详解

此案例来自于B站黑马程序员JavaWeb教程，由于视频未给表数据，故特此手打于此，大家有需要可自取练习（也付有相应多级查询练习题和解答，可供参考）
黑马程序员JavaWeb教程地址： https://www.bilibili.com/video/BV1Qf4y1T7Hx?p=28&vd_source=da345f70943f8512a5a99a3a5285367d

表数据

员工表中员工数据原视频太多，练习用不需太多，故简化了

-- 部门表
CREATE TABLE dept ( id INT PRIMARY KEY,
 -- 部门id
dname VARCHAR (50),
 -- 部门名称
loc VARCHAR (50) -- 部门所在地
);

-- 职务表
CREATE TABLE job ( id INT PRIMARY KEY,
 -- 职务id
jname VARCHAR (20),
 -- 职务名称
description VARCHAR (50) -- 职务描述
);

-- 员工表
CREATE TABLE emp ( id INT PRIMARY KEY,
 -- 员工id
ename VARCHAR (50),
 -- 员工姓名
job_id INT,
 -- 员工工作id
mgr INT,
 -- 管理者id
joindate DATE,
 -- 入职日期
salary DECIMAL (7, 2),
 -- 薪资
bonus DECIMAL (7, 2),
 -- 奖金
dept_id INT -- 所属部门id
);

-- 薪资等级表
CREATE TABLE salarygrade ( grade INT PRIMARY KEY,
 -- 等级
losalary INT,
 -- 该等级最低薪资
hisalary INT
 -- 该等级最高薪资
);

-- 添加4个部门
INSERT INTO dept (id, dname, loc)
VALUES
	(10, '教研部', '北京'),
	(20, '学工部', '上海'),
	(30, '销售部', '广州'),
	(40, '财务部', '深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description)
VALUES
	(
		1,
		'董事长',
		'管理公司'
	),
	(
		2,
		'经理',
		'管理部门员工'
	),
	(
		3,
		'销售员',
		'推销产品'
	),
	(
		4,
		'文员',
		'使用办公软件'
	);

-- 添加员工
INSERT INTO emp (
	id,
	ename,
	job_id,
	mgr,
	joindate,
	salary,
	bonus,
	dept_id
)
VALUES
	(
		1001,
		'孙悟空',
		4,
		1004,
		'2000-12-17',
		'8000.00',
		NULL,
		20
	),
	(
		1002,
		'卢俊义',
		3,
		1006,
		'2001-02-20',
		'16000.00',
		'3000.00',
		30
	),
	(
		1003,
		'林冲',
		3,
		1006,
		'2001-02-22',
		'12500.00',
		'5000.00',
		30
	),
	(
		1004,
		'唐僧',
		2,
		1009,
		'2001-04-02',
		'29750.00',
		NULL,
		20
	),
	(
		1005,
		'李逵',
		4,
		1006,
		'2001-09-28',
		'12500.00',
		'14000.00',
		30
	),
	(
		1006,
		'宋江',
		2,
		1009,
		'2001-05-01',
		'28500.00',
		NULL,
		30
	),
	(
		1007,
		'刘备',
		2,
		1009,
		'2001-09-01',
		'24500.00',
		NULL,
		10
	);

-- 添加5个工资等级
INSERT INTO salarygrade (grade, losalary, hisalary)
VALUES
	(1, 3000, 10000),
	(2, 10010, 14000),
	(3, 14010, 20000),
	(4, 20010, 30000),
	(5, 30010, 99990);

员工表 emp

职务表 job

部门表 dept

薪资等级表 salarygrade

相应练习及详解

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp
LEFT JOIN job ON emp.job_id = job.id;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM
	emp,
	job,
	dept
WHERE
	emp.id = job.id,
	emp.id = dept.id;

-- 3.查询员工姓名，工资，工资等级
SELECT
	emp.ename,
	emp.salary,
	salarygrade.grade
FROM
	emp,
	salarygrade
WHERE
	emp.salary >= salarygrade.losalary
AND emp.salary <= salarygrade.hisalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	salarygrade.grade
FROM
	emp
INNER JOIN job ON job.id = emp.job_id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade ON emp.salary BETWEEN salarygrade.losalary
AND salarygrade.hisalary;

-- 5.查询部门编号，部门名称，部门位置，部门人数
SELECT
	dept.id,
	dept.dname,
	dept.loc,
	t1.count
FROM
	dept
LEFT JOIN (
	SELECT
		dept_id,
		count(*) AS count
	FROM
		emp
	GROUP BY
		dept_id
) t1 ON dept.id = t1.dept_id;