employees表
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
题目1:查找最晚入职员工的所有信息:
select * from employees
where hire_date = (select max(hire_date) from employees);
考点:子查询、函数。
题目2:查找入职员工时间排名倒数第三的员工所有信息
select * from employees
order by hire_date desc
limit 2,1;
考点:分页查询,limit offset,size;
offset要显示条目的起始索引(起始索引从0开始),size要显示的条目个数。故查询倒数第三员工信息即为先将入职时间进行降序desc
然后从第3个索引开始取一条数据。
题目3:查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no
//原始表
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select s.*,d.dept_no
from salaries as s
inner join dept_manager as d
on s.emp_no = d.emp_no
where s.to_date = '9999-01-01' and d.to_date = '9999-01-01';
考点:连接查询,sql99语法。题目要求是薪水情况以及部门编号,再结合输出情况dept_no 被放到了最后一列,可见是主表是“salaries”
题目4:查找所有已经分配部门的员工的last_name和first_name
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
select e.last_name,e.first_name,d.dept_no
from employees e
inner join dept_emp d
on e.emp_no = d.emp_no;
考点:连接查询
题目5:查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括展示没有分配具体部门的员工(表和上题一致)
select e.last_name, e.first_name, d.dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no
考点:主表员工
表,字段emp_no,birth_date,first_name,last_name,gender,hire_date
,从表部门
表,字段emp_no,dept_no,from_date,to_date
inner join
即内连接。两边表同时有对应的数据,即任何一边缺失数据就不显示。left join
左外连接。会读取左边数据表的全部数据,即便右边表无对应数据。right join
右外连接。会读取右边数据表的全部数据,即便左边表无对应数据。
题目6:查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
//方法1
select e.emp_no,s.salary
from employees e
inner join salaries s
on s.emp_no = e.emp_no and s.from_date = e.hire_date
order by e.emp_no desc;
//方法2
SELECT e.emp_no, s.salary FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC
//方法3
select emp_no,salary from salaries
group by emp_no having min(from_date)
order by emp_no DESC
解析:
- salaries.emp_no 不唯一(因为号码为 emp_no 的员工会有多次涨薪的可能,所以在 salaries 中对应的记录不止一条),employees.emp_no 唯一,即 salaries 的数据会多于 employees,因此需先找到 employees.emp_no 在 salaries 表中对应的记录salaries.emp_no,则有限制条件 e.emp_no = s.emp_no
- 根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等,因此有限制条件 e.hire_date = s.from_date
- 根据题意要按照 emp_no 值逆序排列,因此最后要加上 ORDER BY e.emp_no DESC
题目7:查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
//原始表
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select emp_no,count(emp_no) t
from salaries
group by emp_no
having t >15;
考点:
-
用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
-
根据题意,输出的涨幅次数为t,故用AS语句将COUNT(emp_no)的值转换为t
-
由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件。添加复杂的筛选条件(分组函数)一般使用having在group by后面,即分组后筛选。where放在group by前,即简单筛选,分组前筛选。
-
最后存在一个理解误区,涨幅超过15次,salaries中相应的记录数应该超过16(从第2条记录开始算作第1次涨幅),不过题目为了简单起见,将第1条记录当作第1次涨幅,所以令t>15即可
题目8:找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
//原始表
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select distinct(salary)
from salaries
where to_date = '9999-01-01'
order by salary desc;
考点:DISTINCT函数去重
题目9:获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=‘9999-01-01’
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select d.dept_no,d.emp_no,s.salary
from dept_manager d
inner join salaries s
on d.emp_no = s.emp_no and d.to_date = s.to_date
where d.to_date = '9999-01-01';
考点:因为同一emp_no在salaries表中对应多条涨薪记录,而当s.to_date = '9999-01-01’时是该员工当前的薪水记录
题目10:获取所有非manager的员工emp_no
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
//方法1:not in
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);
//方法2
select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where d.emp_no is null;
解析:
- 方法1是使用NOT IN选出在employees但不在dept_manager中的emp_no记录
- 方法2是先使用LEFT JOIN连接两张表,再从此表中选出dept_no值为NULL对应的emp_no记录
题目11:获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date=‘9999-01-01’。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
select e.emp_no,m.emp_no as manager_no
from dept_emp e
inner join dept_manager m
on e.dept_no = m.dept_no
where e.to_date='9999-01-01'
and m.to_date='9999-01-01'
and e.emp_no <> m.emp_no;
解析:
- 用 INNER JOIN 连接两张表,因为要输出自己的经理,得知自己与经理的部门要相同,故有限制条件 de.dept_no = dm.dept_no
- 再用 WHERE 限制当前员工与当前经理的条件,即 dm.to_date 等于 ‘9999-01-01’ 、de.to_date 等于 ‘9999-01-01’ 、 de.emp_no 不等于 dm.emp_no
题目12:获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select d.dept_no,d.emp_no,max(s.salary) salary
from dept_emp d
inner join salaries s
on d.emp_no = s.emp_no
where d.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
group by d.dept_no;
考点:
- 先用INNER JOIN连接两张表,限制条件是两张表的emp_no相同,即d.emp_no = s.emp_no;
- 选取每个员工当前的工资水平,用d.to_date = ‘9999-01-01’ AND s.to_date = '