数据库查询习题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`));

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

-- 先按时间倒排序，再查出第一个
select * from employees order by hire_date DESC limit 0,1;

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

-- limit a,b 
-- a 代表从a开始查询（a，从零开始计数），b 代表查询几个数
select * from employees order by hire_date DESC limit 2,1;

---

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`));

3.查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

SELECT s.*,d.dept_no FROM salaries s,dept_manager d WHERE s.emp_no=d.emp_no AND s.to_date='9999-01-01' AND d.to_date='9999-01-01';

---

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`));

4.查找所有已经分配部门的员工的last_name和first_name
select last_name,first_name,dept_no from employees e,dept_emp d where e.emp_no=d.emp_no and e.emp_no in(select emp_no from dept_emp);

---

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`));

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

-- left join 左外连接，根据左边表匹配，右边表没有的属性直接为null
select last_name,first_name,dept_no from employees e left join dept_emp d 
on e.emp_no=d.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`));

6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
select e.emp_no,salary from employees e,salaries s where e.emp_no=s.emp_no and e.hire_date=s.from_date order by e.emp_no DESC;

---

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`));

7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

-- 注意：group by 一般和having（加条件）连用，如果要和where，order by连用，where要在group 
-- by 前面，order by要在最后面
select emp_no,count(salary) t from salaries group by emp_no having count(salary)>15;

---

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`));

8.找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

-- distinct 去重
select distinct salary from salaries where to_date='9999-01-01' order by salary DESC;

---

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`));

9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=’9999-01-01’

-- 和上面的第三题差不多，只是输出顺序不一样
mp_no,salary from dept_manager d,salaries s where d.emp_no=s.emp_no and
 s.to_date='9999-01-01' and d.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 `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`));

10.获取所有非manager的员工emp_no

select e.emp_no from employees e where not exists (select * from dept_manager d
 where e.emp_no=d.emp_no)
-- not exists 表面意思就是不存在，按照括号内语句查询的是manager，不存在就是不要括号内的
-- 内容，当然还有exists与此相反，和这种功能类似的如：in、not in ，不过两者是有区别的，显
-- 而易见的就是，in括号内的返回的只能是单个字段，而exists可以返回多个字段 

---

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`));

11.获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=’9999-01-01’。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

-- inner join(内连接)，仔细看这两张表，其实字段，主键都一样，只不过含义不一样，所以使用
-- 内连接查询，也叫自连接查询，物理上是2张表，逻辑上是一张表。它只输出符合条件的查询结果，
-- 不像外连接，不符合where条件的会输出null
select e.emp_no,m.emp_no 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

---

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`));

12.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select de.dept_no,s.emp_no,MAX(s.salary) salary from dept_emp de inner join salaries s 
on de.emp_no=s.emp_no
where de.to_date='9999-01-01' and s.to_date='9999-01-01'  group by de.dept_no