对于Linux中MySQL的多表练习

现有员工表（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);

mysql> desc emp;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid            | int(11)     | YES  |     | NULL    |       |
| name           | varchar(11) | YES  |     | NULL    |       |
| age            | int(11)     | YES  |     | NULL    |       |
| worktime_start | date        | YES  |     | NULL    |       |
| incoming       | int(11)     | YES  |     | NULL    |       |
| dept2          | int(11)     | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> desc dept;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept1     | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(11) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table emp;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `sid` int(11) DEFAULT NULL,
  `name` varchar(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `worktime_start` date DEFAULT NULL,
  `incoming` int(11) DEFAULT NULL,
  `dept2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table dept;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| dept  | CREATE TABLE `dept` (
  `dept1` int(11) DEFAULT NULL,
  `dept_name` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.找出销售部门中年纪最大的员工的姓名
-- 方法1：连接查询，这种方法缺陷是如果销售部门有工资相同的都是最高的
-- 只输出一个结果，不严谨。错误写法
select name from emp e ,dept d
where e.dept2=d.dept1
and dept_name='销售'
order by incoming desc
limit 1;

方法2：
--1>先找出销售部门的部门编号，进而查询销售部门最大年龄
select dept1 from dept where dept_name='销售';

select max(age) from emp
where dept2=(select dept1 from dept where dept_name='销售');

--2> 显示符合条件的员工姓名
select name from emp
where dept2=(select dept1 from dept where dept_name='销售')
and age=(
select max(age) from emp
where dept2=(select dept1 from dept where dept_name='销售')
);

方法3： 连接查询
select name from emp e ,dept d
where e.dept2=d.dept1
and dept_name='销售'
and age=(
select max(age) from emp
where dept2=(select dept1 from dept where dept_name='销售')
);

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

select name from emp e ,dept d
where e.dept2=d.dept1
and dept_name='财务'
order by incoming
limit 1

select name from emp
where dept2=(select dept1 from dept where dept_name='财务')
and incoming=(
select min(incoming) from emp
where dept2=(select dept1 from dept where dept_name='财务')
);

--方法3：连接查询
select name from emp e ,dept d
where e.dept2=d.dept1
and dept_name='财务'
and incoming=(
select min(incoming) from emp
where dept2=(select dept1 from dept where dept_name='财务')
);

--     3.列出每个部门收入总和高于9000的部门名称
--1> 先查询收入总和高于9000的部门编号
select dept2 from emp
group by dept2
having sum(incoming)> 9000;

--2> 接下来使用连接查询或子查询都可以
--子查询
select dept_name from dept
where dept1 in (
select dept2 from emp
group by dept2
having sum(incoming)> 9000
);

--连接查询
select dept_name from dept d,
(
select dept2 from emp
group by dept2
having sum(incoming)> 9000
) 
where d.dept1=e.dept2;

--     4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
-->1.计算7500到8500元之间年龄最大的
select age from emp
where incoming BETWEEN 7500 and 8500
order by age desc
limit 1

或者
select max(age) from emp
where incoming BETWEEN 7500 and 8500;

--> 2. 输出符合1 条件的 name 和 dept2
select name,dept2 from emp
where age =
(
select age from emp
where incoming BETWEEN 7500 and 8500
order by age desc
limit 1
)

--3> 然后连接查询

select name,dept_name from dept d,
(
select name,dept2 from emp
where age =
(
select age from emp
where incoming BETWEEN 7500 and 8500
order by age desc
limit 1
)
) e
where d.dept1=e.dept2

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

--> 计算出销售部门部门编号和最低工资
select dept1,incoming from emp e,dept d
where d.dept1=e.dept2
and dept_name='销售'
order by incoming
limit 1
或者计算销售部门最低工资
select min(incoming) from emp e,dept d
where d.dept1=e.dept2
and dept_name='销售'

select worktime_start from emp A,(
select dept1,incoming from emp e,dept d
where d.dept1=e.dept2
and dept_name='销售'
order by incoming
limit 1 ) B
where A.dept2=B.dept1 and A.incoming=B.incoming
--
--     6.财务部门收入超过2000元的员工姓名
select name from emp
where dept2=(select dept1 from dept where dept_name='财务')
and incoming > 2000

select name from dept d,emp e 
where d.dept1=e.dept2 
and d.dept_name='财务' and incoming>2000;

--     7.列出每个部门的平均收入及部门名称
select dept_name,avg(incoming)  from dept d,emp e 
where d.dept1=e.dept2 
group by dept_name;

--     8.IT技术部入职员工的员工号
select sid from dept d,emp e 
where d.dept1=e.dept2 
and d.dept_name='IT技术';

select sid from emp where 
dept2=(select dept1 from dept where dept_name='IT技术' );
--     9.财务部门的收入总和；
select sum(incoming) from dept d,emp e
where d.dept1=e.dept2 
and dept_name='财务';

select SUM(incoming) as 收入总和 from emp 
where dept2=(select dept1 from dept where dept_name='财务' );
-- 
--     10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
SELECT * from emp order by dept2 desc,worktime_start asc;

--     11.找出哪个部门还没有员工入职；
select * from dept where dept1 not in (select distinct dept2 from emp);

--     12.列出部门员工收入大于7000的部门编号，部门名称；
select DISTINCT dept2,dept_name from dept d,emp e 
where d.dept1=e.dept2 and incoming>7000;

-- 方法二：使用临时表查询

select * from dept where dept1 in (select dept2 from emp where incoming>7000);

--     13.列出每一个部门的员工总收入及部门名称；
select * from dept left JOIN 
(select dept2, SUM(incoming) from emp GROUP BY  dept2) new1
on dept.dept1=new1.dept2

-- 
--     14.列出每一个部门中年纪最大的员工姓名，部门名称；
select name,age M_age,dept_name from dept d,emp e 
where d.dept1=e.dept2 
and age in (
select max(age) from emp,dept where dept.dept1=emp.dept2 group by dept2);

select name,dept_name from dept inner JOIN
(select emp.name,emp.age,emp.dept2 from emp
inner JOIN (select dept2, max(age)as age from emp GROUP BY  dept2) new1
on emp.dept2=new1.dept2 AND emp.age=new1.age)new2
ON dept.dept1=new2.dept2

-- 
--     15.求李四的收入及部门名称
select incoming,dept_name from emp e INNER JOIN dept d
ON e.dept2=d.dept1 
where e.name='李四'

--     16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
-- 方法：注意需要进行多次联查（目的：解决同一个部门存在有多个最大值问题）

select * from dept INNER JOIN
(SELECT emp.name,emp.incoming,emp.dept2 from emp RIGHT JOIN
(select MAX(incoming) as incoming ,dept2 from emp group by dept2)new1  
 on emp.dept2=new1.dept2 and emp.incoming=new1.incoming) new2
On dept.dept1=new2.dept2

--     17.列出部门员工数大于1个的部门名称
select * from dept where dept1 in 
(select  dept2 from emp group by dept2 having COUNT(1) >1)

select dept_name from dept A,
(select  dept2 from emp group by dept2 having COUNT(1) >1) B
where A.dept1=B.dept2
--     19.查找张三所在的部门名称

方法一：嵌套子查询
select dept_name from dept 
where dept1 = (select dept2 from emp where name='张三')

方法二：基本查询

select dept_name from dept,emp 
where dept.dept1=emp.dept2 
and  emp.name='张三'

方法三：临时表

select dept_name from (select * from dept LEFT JOIN emp on dept.dept1=emp.dept2) new1 where new1.name="张三"

总结：针对什么什么最大或者最小，要注意可能有多个相同的值同时是最大的，这个时候可以用子查询先将最大的数值查出来，然后再找到他所对应的，因为最大的数值只有一个。where后面跟的如果用‘=’那么后面子查询不能用group不然会报错，如果子查询需要用到group那么就将'='换成in。