MySQL基础——多表查询练习题
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
文章目录
1.查询员工的姓名、年龄、部门信息隐式内连接
2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息显示内连接
3.查询拥有员工的部门id、部门名称
4.查询所有年龄大于40岁的员工及其归属的部门名称如果没有分配部门也需要展示出来
5.查询所有员工的工资等级
6.查询‘研发部’所有员工的信息及工资等级(两种写法)
7.查询‘研发部’员工的平均薪资
8.查询工资比‘灭绝’高的员工信息
9.查询比平均薪资高的员工信息
10.查询低于本部门平均工资的员工信息
11.查询所有的部门信息,并统计部门的员工人数
所要涉及到的表结构
CREATE TABLE emp(
id INT PRIMARY KEY,
NAME VARCHAR(10),
age INT,
job VARCHAR(10),
salary INT,
entrydate DATE,
managerid_id INT,
dept_id INT
);
INSERT INTO emp(id,NAME,age,job,salary,entrydate,managerid_id,dept_id)
VALUES(1,'金庸',66,'总裁',20000,'2000-01-01',NULL,5),
(2,'张无忌',20,'项目经理',12000,'2005-01-11',1,1),
(3,'杨晓',33,'开发',8400,'2000-01-01',2,1),
(4,'韦一笑',48,'开发',11000,'2002-01-01',2,1),
(5,'常遇春',43,'开发',10500,'2004-01-01',3,1),
(6,'小昭',19,'程序员鼓励师',6600,'2004-01-01',2,1),
(7,'灭绝',60,'财务总监',8500,'2002-01-01',1,3),
(8,'周芷若',19,'会计',48000,'2006-01-01',7,3),
(9,'丁敏君',23,'出纳',5350,'2009-01-01',7,3),
(10,'赵敏',20,'市场部总监',12500,'2004-01-01',1,2),
(11,'鹿杖客',56,'职员',3750,'2006-01-01',10,2),
(12,'鹤笔翁',19,'职员',3750,'2007-01-01',10,2),
(13,'房东白',19,'职员',5000,'2009-01-01',10,2),
(14,'张三丰',88,'销售总监',14000,'2004-01-01',1,4),
(15,'玉莲舟',38,'销售',4600,'2004-01-01',14,4),
(16,'宋巧缘',40 ,'销售',4600,'2004-01-01',14,4),
(17,'陈友谅',42,NULL,20000,'2001-01-01',1,NULL);
SELECT*FROM emp;CREATE TABLE salgrade(
grade INT,
losal INT,
hisal INT
);
INSERT INTO salgrade(grade,losal,hisal)
VALUES(1,0,3000),
(2,3001,5000),
(3,5001,8000),
(4,8001,10000),
(5,10001,15000),
(6,15001,20000),
(7,20001,25000),
(8,25001,30000);
SELECT*FROM salgrade;CREATE TABLE dept(
id INT,
NAME VARCHAR(10)
);
INSERT INTO dept(id,NAME) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');
SELECT*FROM dept;题目如下
1.查询员工的姓名、年龄、部门信息隐式内连接
-- 1.查询员工的姓名、年龄、部门信息隐式内连接
-- 表emp,dept
-- 连接条件:emp.dept_id=dept.id
SELECT e.`NAME`,e.`age`,d.name FROM emp e,dept d WHERE e.`dept_id`=d.`id`;
2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息显示内连接
-- 2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息显示内连接
-- 表emp,dept
-- 连接条件:emp.dept_id=dept.id
SELECT e.`NAME`,e.`age`,e.`job`,d.`name` FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id` WHERE e.`age`<30;3.查询拥有员工的部门id、部门名称
-- 3.查询拥有员工的部门id、部门名称
-- 表emp,dept
-- 连接条件:emp.dept_id=dept.id
SELECT DISTINCT d.`id`,d.`name` FROM emp e,dept d WHERE e.`dept_id`=d.`id`;4.查询所有年龄大于40岁的员工及其归属的部门名称如果没有分配部门也需要展示出来
-- 4.查询所有年龄大于40岁的员工及其归属的部门名称如果没有分配部门也需要展示出来
-- 表emp,dept
-- 连接条件:emp.dept_id=dept.id
-- 外连接
SELECT e.*,d.`name` FROM emp e LEFT JOIN dept d ON e.`dept_id`=d.`id` WHERE e.`age`>40;5.查询所有员工的工资等级
-- 5.查询所有员工的工资等级
-- 表emp,salgrade
-- 连接条件:e.`salary`>=s.`losal` AND e.`salary`<=s.`hisal`
SELECT e.*,s.`grade`'工资等级'FROM emp e,salgrade s WHERE e.`salary`>=s.`losal` AND e.`salary`<=s.`hisal`;6.查询‘研发部’所有员工的信息及工资等级(两种写法)
-- 6.查询‘研发部’所有员工的信息及工资等级(两种写法)
-- 表emp,dept,salgrade
-- 连接条件:emp.dept_id=dept.id 和 e.`salary`>=s.`losal` AND e.`salary`<=s.`hisal`
SELECT id FROM dept WHERE NAME='研发部';
SELECT*FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='研发部');
SELECT e.*,s.`grade` FROM (SELECT*FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='研发部')) e,salgrade s WHERE e.`salary`>=s.`losal` AND e.`salary`<=s.`hisal`;
SELECT e.*,s.`grade`
FROM emp e,dept d,salgrade s
WHERE (e.`dept_id`=d.`id`) AND (e.`salary`>=s.`losal` AND e.`salary`<=s.`hisal`) AND d.`name`='研发部'; 7.查询‘研发部’员工的平均薪资
-- 7.查询‘研发部’员工的平均薪资
-- 表emp,dept
-- 连接条件:emp.dept_id=dept.id
SELECT id FROM dept WHERE NAME='研发部';
SELECT AVG(salary) FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='研发部');8.查询工资比‘灭绝’高的员工信息
-- 8.查询工资比‘灭绝’高的员工信息
SELECT salary FROM emp WHERE NAME='灭绝';
SELECT*FROM emp WHERE salary>(SELECT salary FROM emp WHERE NAME='灭绝');9.查询比平均薪资高的员工信息
-- 9.查询比平均薪资高的员工信息
SELECT AVG(salary) FROM emp;
SELECT*FROM emp WHERE salary>(SELECT AVG(salary) FROM emp);10.查询低于本部门平均工资的员工信息
SELECT AVG(salary)FROM emp WHERE dept_id=1;
SELECT*FROM emp e2 WHERE e2.`salary`<(SELECT AVG(e1.`salary`)FROM emp e1 WHERE e1.`dept_id`=e2.`dept_id`);11.查询所有的部门信息,并统计部门的员工人数
-- 11.查询所有的部门信息,并统计部门的员工人数
SELECT e.`dept_id`,COUNT(*)FROM emp e,dept d WHERE e.`dept_id`=d.`id` GROUP BY e.`dept_id`;