02Mysql之多表查询--例题讲解

一、题目详情，以及表的建立

新增员工表emp和部门表dept
 create table dept (dept1 int ,dept_name varchar(11));
 create table emp (sid int ,name varchar(11),age int,worktime_start date,incoming int,dept2 int);

	 insert into dept values
	(101,'财务'),
	(102,'销售'),
	(103,'IT技术'),
	(104,'行政');

	 insert into emp values
	(1789,'张三',35,'1980/1/1',4000,101),
	(1674,'李四',32,'1983/4/1',3500,101),
	(1776,'王五',24,'1990/7/1',2000,101),
	(1568,'赵六',57,'1970/10/11',7500,102),
	(1564,'荣七',64,'1963/10/11',8500,102),
	(1879,'牛八',55,'1971/10/20',7300,103);

二、题目详情 

1.找出销售部门中年纪最大的员工的姓名

mysql> select name 
    -> from emp 
    -> where dept2=102
    -> order bt age desc
    -> limit;

 

 

• select e.name: 这是查询的主体部分，表示我们希望从表中选择特定的列（在这种情况下是 "name" 列）作为结果。

• from emp e: 这部分指定了我们要从 "emp" 表中检索数据，并使用 "e" 作为该表的别名。这使得在查询中可以使用 "e" 来引用 "emp" 表。

• join dept d on e.dept2 = dept1: 这是一个连接操作，它将 "emp" 表与 "dept" 表连接起来。我们使用了 "JOIN" 子句来连接这两个表，然后使用 "on" 子句指定连接的条件，即 "e.dept2"（员工所属部门）等于 "dept1"（部门表的部门编号）。

• where d.dept_name = '销售': 这是一个筛选条件，它限制了只选择 "dept_name" 列等于 '销售' 的部门。

• order by e.age desc: 这部分指定了对结果进行排序。"ORDER BY" 子句后面跟着要用于排序的列名，这里是 "e.age" 列（员工年龄）。"desc" 表示按降序排列，即从高到低排列。

• limit 1: 这部分限制了结果的数量，仅返回第一个满足条件的行。由于我们使用了降序排序，所以这将返回年龄最大的员工。

    2.求财务部门最低工资的员工姓名

SELECT name
FROM emp
WHERE dept2 = 101
ORDER BY incoming
LIMIT 1;

    3.列出每个部门收入总和高于9000的部门名称

SELECT dept_name
FROM dept
WHERE dept1 IN (
    SELECT dept2
    FROM emp
    GROUP BY dept2
    HAVING SUM(incoming) > 9000
);

 

    4.求工资在7500到8500元之间，年龄最大的人的姓名及部门

SELECT e.name, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
WHERE e.incoming BETWEEN 7500 AND 8500
ORDER BY e.age DESC
LIMIT 1;

 

    5.找出销售部门收入最低的员工入职时间

SELECT MIN(worktime_start)
FROM emp
WHERE dept2 = 102
ORDER BY incoming
LIMIT 1;

 

    6.财务部门收入超过2000元的员工姓名

SELECT name
FROM emp
WHERE dept2 = 101 AND incoming > 2000;

 

    7.列出每个部门的平均收入及部门名称

SELECT d.dept_name, AVG(e.incoming) AS avg_incoming
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
GROUP BY d.dept_name;

    8.IT技术部入职员工的员工号

SELECT sid
FROM emp
WHERE dept2 = 103;

 9.财务部门的收入总和；

SELECT SUM(incoming) AS total_incoming
FROM emp
WHERE dept2 = 101;

 

    10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表

SELECT e.sid, e.name, e.age, e.worktime_start, e.incoming, e.dept2, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
ORDER BY e.dept2, e.worktime_start;

 

    11.找出哪个部门还没有员工入职；

SELECT d.dept_name
FROM dept d
LEFT JOIN emp e ON d.dept1 = e.dept2
WHERE e.sid IS NULL;

 

    12.列出部门员工收入大于7000的部门编号，部门名称；

SELECT d.dept1, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
WHERE e.incoming > 7000
GROUP BY d.dept1, d.dept_name;

 

    13.列出每一个部门的员工总收入及部门名称；

SELECT d.dept_name, SUM(e.incoming) AS total_incoming
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
GROUP BY d.dept_name;

 

    14.列出每一个部门中年纪最大的员工姓名，部门名称；

SELECT d.dept_name, e.name
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
WHERE e.age = (
    SELECT MAX(age)
    FROM emp
    WHERE dept2 = d.dept1
);

 

    15.求李四的收入及部门名称

SELECT e.incoming, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
WHERE e.name = '李四';

 

    16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序

SELECT d.dept_name, e.name, e.incoming
FROM emp e
INNER JOIN dept d ON e.dept2 = d.dept1
WHERE e.incoming = (
    SELECT MAX(incoming)
    FROM emp
    WHERE dept2 = d.dept1
)
ORDER BY e.incoming DESC;

 

    17.列出部门员工数大于1个的部门名称

SELECT d.dept_name
FROM dept d
INNER JOIN emp e ON d.dept1 = e.dept2
GROUP BY d.dept_name
HAVING COUNT(*) > 1;

 

    19.查找张三所在的部门名称