【mysql】多表查询、左外连接、内连接、练习题

多表查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FBdzXkoQ-1659581225088)(C:\Users\L00589~1\AppData\Local\Temp\1659337934641.png)]

左外连接&右外连接

-- 查询emp表所有数据和对应的部门信息
select * from emp left join dept on emp.dep_id = dept.did;


-- 右外连接
-- 查询 dept 表所有数据和对应的员工信息

select * from emp right join dept on emp.dep_id = dept.did;

内连接

select * from emp where salary > (select salary from emp where name = '猪八戒')


-- 多行单列子查询
-- 查询 '财务部' 和 '市场部' 所有员工的信息
select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
select * from (select * from emp where join_date > '2011-11-11') t1, dept where t1.dep_id = dept.did;

查询练习

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, -- 部门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,-- 入职日期
	salary DECIMAL ( 7, 2 ),-- 工资
	bonus DECIMAL ( 7, 2 ),-- 奖金
	dept_id INT,-- 所在部门编号
	CONSTRAINT emp_jobid_ref_job_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, '财务部', '深圳' );-- 添加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.0', 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', '5000.00', 30 ),
	( 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、查询所有员工信息,查询员工编号,员工姓名,工资,职务名称,职务描述
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
FROM
	emp,
	job 
WHERE
	emp.job_id = job.id
ORDER BY(emp.id);
	
	
-- 2、查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
FROM emp,job,dept
WHERE (emp.job_id = job.id AND emp.dept_id = dept.id)
ORDER BY (emp.id);



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




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




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