数据库编程题

1.查找最晚入职员工的所有信息

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 * from employees 

where hire_date =

(select max(hire_date) from employees)

2.查找入职员工时间排名倒数第三的员工所有信息

select * from employees

order by hire_date desc

limit 2,1;

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
and s.to_date = '9999-01-01'
and d.to_date='9999-01-01';

要分清主表与次表，主表为 salaries 次表为 dept_manager，主表 inner join 次表。由于输出顺序是按照emp_no递增来的，join时salaries表要放在前面，否则输出顺序不对。由于两个表都有重复数据，所以两个表的to_date都要做限制。

4.查找所有已经分配部门的员工的last_name和first_name

select employees.last_name,employees.first_name,dept_emp.dept_no
from employees inner join dept_emp
on dept_emp.emp_no = employees.emp_no;

5.查找所有员工的last_name和first_name以及对应部门编号dept_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 `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`));

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

select de.dept_no,d.dept_name, count(s)
from departments as d inner join dept_emp as de
on d.dept_no=de.dept_no inner join salaries as s
on de.emp_no=s.emp_no
group by d.dept_no;

select d.dept_no ,dept.dept_name,count(salary) 
from salaries s ,dept_emp d,departments dept
where s.emp_no = d.emp_no 
and d.dept_no=dept.dept_no 
group by dept.dept_no

from salaries as s inner join dept_emp as de
on s.emp_no = de.emp_no inner join departments as d
on d.dept_no=de.dept_no

 

图解MySQL 内连接、外连接、左连接、右连接、全连接

https://blog.csdn.net/plg17/article/details/78758593