MYSQL 多表查询练习

1. 建立表并插入数据建立外链

drop table if exists emp;
drop table if exists dept;
drop table if exists job;
drop table if exists salarygrade;

-- 部门表
create table dept (
	id int primary key auto_increment, -- 部门ID
	dname varchar(50), -- 部门名称
	loc varchar(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
create table job(
	id int primary key,
	jname varchar(20),
	description varchar(50)
);

-- 员工表
create table emp(
	id int primary key, -- 员工ID
	ename varchar(50), -- 员工姓名
	job_id int, -- 职务ID
	mgr int, -- 上级领导
	joindate DATE, -- 入职日期
	sclare decimal(7, 2), -- 工资
	bouns decimal(7, 2), -- 奖金
	dept_id int, -- 所站部门编号
	constraint emp_jobid_ref_jb_id_fk foreign key (job_id) references job (id),
	constraint emp_deptid_ref_dept_id_fk foreign key (dept_id) references dept (id)
);

-- 工资等级表
create table salarygrade (
	grade int primary key, -- 级别
	losalary int, -- 最低工资
	hisalary int -- 最高工资
);

-- 添加4个部门
insert into dept (id, dname, loc) values (10, "教研部", "北京"), (20, '学工部', '上海'), (30, '销售部', '广州'),  (40, '财务部', '深圳');
-- delete from dept where id > 40;

-- 添加4个职务
insert into job (id, jname, description) values (1, '董事长', '管理整个公司，接单'), (2, '经理', '管理部门员工'), (3, '销售员', '向客人推销产品'), (4, '文员', '使用办公软件');

-- 添加员工
insert into emp (id, ename, job_id, mgr, joindate, sclare, bouns, dept_id) values 
(1001, '预报', 4, 1004, '2000-12-17', '8000.00', null, 20),
(1002, '炉石', 3, 1006, '2000-12-17', '8000.00', null, 30),
(1003, '差异', 3, 1006, '2009-02-17', '1600.00', null, 30),
(1004, '陆运', 2, 1009, '2008-11-22', '12500.00', null, 20),
(1005, '路遥', 4, 1006, '2007-08-24', '15480.00', null, 40),
(1006, '胡说', 2, 1009, '2005-07-18', '35000.00', null, 10),
(1007, '欧布', 2, 1004, '2004-05-07', '7000.00', null, 30),
(1008, '没法', 4, null, '2003-04-05', '9000.00', null, 20),
(1009, '初步', 1, 1006, '2002-11-13', '5500.00', null, 10),
(1010, '专辑', 3, 1004, '2001-10-11', '34000.00', null, 40),
(1011, '傲视', 4, 1006, '2001-02-27', '14500.00', null, 20),
(1012, '欧珀', 4, 1004, '2001-10-23', '1800.00', null, 30),
(1013, '讴歌', 4, 1004, '2001-12-07', '19000.00', null, 10),
(1014, '聚就', 4, 1007, '2002-02-23', '77000.00', null, 40);

-- 添加5个工资等级
insert into salarygrade (grade, losalary, hisalary) values 
(1, 3000, 10000),
(2, 10010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);
select * from salarygrade;

 2. 功能实现：

-- 查询所有员工信息。员工编号，姓名，工资，职务，职务描述
    -- 隐式内连接
    select emp.id as '员工编号', emp.ename as '姓名', emp.sclare as '工资', job.jname as '职务', job.description as '职务描述' from emp, job where job.id = emp.job_id;
    -- 显式内连接
    select emp.id as '员工编号', emp.ename as '姓名', emp.sclare as '工资', job.jname as '职务', job.description as '职务描述' from emp inner join job on job.id = emp.job_id;

-- 查询员工编号，姓名，工资，职务，职务描述，部门
select * from emp, job, dept where job.id = emp.job_id and emp.dept_id = dept.id;
	-- 隐式
	SELECT
		emp.id AS '员工编号',
		emp.ename AS '姓名',
		emp.sclare AS '工资',
		job.jname AS '职务',
		job.description AS '职务描述',
		dept.dname as '部门名称',
		dept.loc as '部门位置'
	FROM
		emp,
		job,
		dept 
	WHERE
		job.id = emp.job_id 
		AND emp.dept_id = dept.id;
	-- 显式
	SELECT
		emp.id AS '员工编号',
		emp.ename AS '姓名',
		emp.sclare AS '工资',
		job.jname AS '职务',
		job.description AS '职务描述',
		dept.dname AS '部门名称',
		dept.loc AS '部门位置' 
	FROM
		emp
		INNER JOIN job ON job.id = emp.job_id INNER JOIN dept on dept.id = emp.dept_id;
	
-- 查询员工姓名，工资，工资等级
select * from emp, salarygrade;
	-- 隐式
	select t1.ename as '姓名', t1.sclare as '工资', t2.grade as '工资等级' from emp as t1, salarygrade as t2 where t1.sclare > t2.losalary and t1.sclare <= t2.hisalary ORDER BY t2.grade;
	-- 显示
	select t1.ename as '姓名', t1.sclare as '工资', t2.grade as '工资等级' from emp as t1 inner join salarygrade as t2 on t1.sclare > t2.losalary and t1.sclare <= t2.hisalary ORDER BY t2.grade;
	-- 简写
	select t1.ename as '姓名', t1.sclare as '工资', t2.grade as '工资等级' from emp as t1, salarygrade as t2 where t1.sclare between t2.losalary and t2.hisalary ORDER BY t2.grade;

-- 查询 工资，职务名称，职务描述，部门名称，部门位置，工资等级
select * from emp, salarygrade, job, dept;
SELECT
	t1.sclare AS '工资',
	t3.jname AS '职务名称',
	t3.description AS '职务描述',
	t4.dname AS '部门名称',
	t4.loc AS '部门位置',
	t2.grade AS '工资等级' 
FROM
	emp AS t1,
	salarygrade AS t2,
	job as t3,
	dept AS t4 
WHERE
	t1.sclare > t2.losalary 
	AND t1.sclare <= t2.hisalary
	and t1.job_id = t3.id
	and t4.id = t1.dept_id
ORDER BY
	t2.grade;
-- 查询 部门编号，部门名称，部门位置，部门人数
select dept_id, count(*) as 'count' from emp group by dept_id;
SELECT
	t2.id AS '部门编号',
	t2.dname AS '部门名称',
	t2.loc AS '部门位置',
	t1.count AS '部门人数' 
FROM
	( SELECT dept_id, count(*) AS 'count' FROM emp GROUP BY dept_id ) AS t1,
	dept AS t2 
WHERE
	t1.dept_id = t2.id;
-- 实现方法2
SELECT
	emp.dept_id AS '部门编号',
	dept.dname AS '部门名称',
	dept.loc AS '部门位置',
	count(*) AS '部门人数' 
FROM
	emp,
	dept 
WHERE
	emp.dept_id = dept.id 
GROUP BY
	emp.dept_id;