Mysql-多表查询入门经典案例

-- 部门表
CREATE TABLE dept ( 
	did INT PRIMARY KEY, -- 部门id
	dname VARCHAR ( 50 ), -- 部门名字
	loc VARCHAR ( 50 ) -- 部门地址
) 

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

-- 员工表
CREATE TABLE emp (
	eid INT PRIMARY KEY,-- 员工id
	ename VARCHAR ( 50 ),-- 员工姓名
	job_id INT,-- 职务id
	mgrid INT,-- 上级领导id
	joindate DATE,-- 入职日期
	salary DECIMAL ( 7, 2 ),-- 工资
	bonus DECIMAL ( 7, 2 ),-- 奖金
	dept_id INT,-- 所在部门编号
	CONSTRAINT emp_job_id_ref_job_jid FOREIGN KEY ( job_id ) REFERENCES job ( jid ),
	CONSTRAINT emp_dept_id_ref_dept_did FOREIGN KEY ( dept_id ) REFERENCES dept ( did ) 
);

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

-- 添加4个部门
INSERT INTO dept ( did, dname, loc )
VALUES
	( 10, '教研部', '北京' ),
	( 20, '学工部', '上海' ),
	( 30, '销售部', '广州' ),
	( 40, '财务部', '深圳' );
	
-- 添加4个职务
INSERT INTO job ( jid, jname, description )
VALUES
	( 1, '董事长', '管理整个公司，接单' ),
	( 2, '经理', '管理部门员工' ),
	( 3, '销售员', '向客人推销产品' ),
	( 4, '文员', '使用办公软件' );
	
-- 添加员工
INSERT INTO emp ( eid, ename, job_id, mgrid, 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 ),
	( 1008, '猪八戒', 4, 1004, '2007-04-19', '30000.00', NULL, 20 ),
	( 1009, '罗贯中', 1, NULL, '2001-11-17', '50000.00', NULL, 10 ),
	( 1010, '吴用', 3, 1006, '2001-09-08', '15000.00', '0.00', 30 ),
	( 1011, '沙僧', 4, 1004, '2007-05-23', '11000.00', NULL, 20 ),
	( 1012, '李逵', 4, 1006, '2001-12-03', '9500.00', NULL, 30 ),
	( 1013, '小白龙', 4, 1004, '2001-12-03', '30000.00', NULL, 20 ),
	( 1014, '关羽', 4, 1007, '2002-01-23', '13000.00', NULL, 10 );

-- 添加5个工资等级
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES
	( 1, 7000, 12000 ),
	( 2, 12010, 14000 ),
	( 3, 14010, 20000 ),
	( 4, 20010, 30000 ),
	( 5, 30010, 99990 );
	
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
-- 涉及表格：emp,job
SELECT e.eid,e.ename,e.salary,j.jname,j.description 
	FROM emp e,job j 
		WHERE e.job_id = j.jid;

SELECT e.eid,e.ename,e.salary,j.jname,j.description
	FROM emp e
		JOIN job j
			ON e.job_id = j.jid;
			
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 涉及表格：emp,job,dept
SELECT e.eid,e.ename,e.salary,j.jname,j.description,d.did,d.dname
	FROM emp e,job j,dept d
		WHERE e.job_id = j.jid and e.dept_id = d.did;

SELECT e.eid,e.ename,e.salary,j.jname,j.description,d.did,d.dname
	FROM emp e
		JOIN job j
			ON e.job_id = j.jid
		JOIN dept d
			ON e.dept_id = d.did;

-- 3.查询员工姓名，工资，工资等级
-- 涉及表格：emp,salarygrade
SELECT e.ename,e.salary,s.grade
	FROM emp e,salarygrade s
		WHERE salary BETWEEN s.losalary AND s.hisalary;
		
SELECT e.ename,e.salary,s.grade
	FROM emp e
		JOIN salarygrade s
			ON salary > s.losalary 
				AND salary < s.hisalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- 涉及表格：emp,job,dept,salarygrade
SELECT e.eid,e.ename,e.salary,j.jname,j.description,d.did,d.dname,s.grade
	FROM emp e,job j,dept d,salarygrade s
		WHERE e.job_id = j.jid 
			AND e.dept_id = d.did 
			AND salary BETWEEN s.losalary AND s.hisalary;

SELECT e.eid,e.ename,e.salary,j.jname,j.description,d.did,d.dname,s.grade
	FROM emp e
		JOIN job j
			ON e.job_id = j.jid
	  JOIN dept d
			ON e.dept_id = d.did
		JOIN salarygrade s
			ON salary BETWEEN s.losalary AND s.hisalary;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
-- 涉及表格：emp,dept
SELECT * 
	FROM dept;
	
SELECT dept_id, count(*) 
	FROM emp 
		GROUP BY dept_id;	
	
SELECT d.did,d.dname,d.loc,t1.count
	FROM dept d,(SELECT dept_id,count(*) count
						FROM emp
							GROUP BY dept_id
	) t1
		WHERE d.did = t1.dept_id;
	
SELECT d.did,d.dname,d.loc,t1.count
	FROM dept d
		LEFT JOIN (SELECT dept_id,count(*) count
						FROM emp
							GROUP BY dept_id
	  ) t1
			ON d.did = t1.dept_id;