学习过程

查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是：select * from table limit 10,20; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是：select * from table limit 20,30; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

------------------题目---------------------
–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);
–排序
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;
–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));
–Answer:
select
salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from salaries inner join dept_manager
on dept_manager.emp_no = salaries.emp_no
where dept_manager.to_date = ‘9999-01-01’ --没想起来
and salaries.to_date = ‘9999-01-01’;

–4.查找所有已经分配部门的员工的last_name和first_name
– 内链接推荐
SELECT e.last_name,e.first_name,d.dept_no
FROM dept_emp d INNER JOIN employees e
ON d.emp_no = e.emp_no

–left join(左连接)：join左表中所有记录和右表中满足连接条件的记录信息
–right join(右连接)：join右表中所有记录和左表中满足连接条件的记录信息
–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 d.emp_no = e.emp_no;

–6.查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序
SELECT e.emp_no,s.salary
FROM employees e INNER JOIN salaries s
ON s.emp_no = e.emp_no AND e.hire_date = s.from_date --少考虑这一种情况
ORDER BY e.emp_no DESC;

–7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
– 本题主要考聚合查询
SELECT emp_no, COUNT(emp_no) AS t
FROM salaries
GROUP BY emp_no HAVING t>15;

–8.找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
SELECT DISTINCT(salary)
FROM salaries
WHERE to_date=‘9999-01-01’
ORDER BY salary DESC;

–9.获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=‘9999-01-01’
– PS: on已经是条件的判断了，不能再用where, INNER JOIN 左右两表交换 没有 错，oj有问题
SELECT d.dept_no,d.emp_no,s.salary
FROM dept_manager d INNER JOIN salaries s
ON s.emp_no = d.emp_no
AND d.to_date = ‘9999-01-01’
AND s.to_date = ‘9999-01-01’
—WHERE to_date=‘9999-01-01’;
—注：有人反映将连接语句改成FROM dept_manager AS d INNER JOIN salaries AS s后，结果通不过。
–INNER JOIN对于左右两表并无顺序要求，此为本题OJ系统Bug所致。

–10.获取所有非manager的员工emp_no !!!
– -PS:考察NOT IN
–方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

– 方法二：先使用LEFT JOIN连接两张表，再从此表中选出dept_no值为NULL对应的emp_no记录
SELECT emp_no?FROM?(SELECT * FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL

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

SELECT de.emp_no, dm.emp_no AS manager_no?
FROM dept_emp AS de INNER JOIN dept_manager AS dm
ON de.dept_no = dm.dept_no?
WHERE dm.to_date = ‘9999-01-01’ AND de.emp_no <> dm.emp_no

–12.获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
SELECT de.dept_no,de.emp_no,MAX(sa.salary) AS salary
FROM dept_emp AS de INNER JOIN salaries AS sa
ON de.emp_no = sa.emp_no
WHERE de.to_date = ‘9999-01-01’ AND sa.to_date = ‘9999-01-01’
GROUP BY de.dept_no

–13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
–PS:正确的过滤是在having以后过滤
SELECT title,COUNT(title) AS t
FROM titles
WHERE t>=2 --错误
GROUP BY title
– 正确的答案
SELECT title, COUNT(title) AS t FROM titles
GROUP BY title HAVING t >= 2; — 聚合就聚合到这里了

–14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略。
–PS:对COUNT中进行分解
SELECT title, COUNT(DISTINCT emp_no) AS t FROM titles
GROUP BY title HAVING t >= 2

–15.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
SELECT *
FROM employees
WHERE emp_no%2 = 1 AND last_name!=‘Mary’
ORDER BY hire_date DESC;

–16 .统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
–PS:不知道为什么不行
–SELECT t.title AVG(s.salary);
–FROM salaries s INNER JOIN titles t
–ON s.emp_no = t.emp_no AND t.to_date=‘9999-01-01’ AND s.to_date=‘9999-01-01’
–GROUP BY title

select t.title,avg(s.salary) from titles t, salaries s where
s.emp_no = t.emp_no
and s.to_date =?‘9999-01-01’
and t.to_date =?‘9999-01-01’
group by title

–17.获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary
SELECT emp_no,salary
FROM salaries
WHERE to_date=‘9999-01-01’
ORDER BY salary DESC --注意要降序
LIMIT 1,1

–18.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
AND s.to_date = ‘9999-01-01’
AND s.salary NOT IN (
SELECT MAX(salary) FROM salaries
WHERE salaries.to_date = ‘9999-01-01’);

–19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
–毁三观！！！！！！

–1、第一次LEFT
–JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工
–2、第二次LEFT
–JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select e.last_name,e.first_name,d.dept_name
from employees e
left join dept_emp de on e.emp_no =de.emp_no
left join departments d on d.dept_no=de.dept_no;

–20.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth
SELECT (MAX(salary)-MIN(salary)) AS growth
FROM salaries WHERE emp_no = ‘10001’
–16 .统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
–PS:不知道为什么不行
–SELECT t.title AVG(s.salary);
–FROM salaries s INNER JOIN titles t
–ON s.emp_no = t.emp_no AND t.to_date=‘9999-01-01’ AND s.to_date=‘9999-01-01’
–GROUP BY title

select t.title,avg(s.salary) from titles t, salaries s where
s.emp_no = t.emp_no
and s.to_date =?‘9999-01-01’
and t.to_date =?‘9999-01-01’
group by title

–17.获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary
SELECT emp_no,salary
FROM salaries
WHERE to_date=‘9999-01-01’
ORDER BY salary DESC --注意要降序
LIMIT 1,1

–18.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees AS e INNER JOIN salaries AS s
ON e.emp_no = s.emp_no
AND s.to_date = ‘9999-01-01’
AND s.salary NOT IN (
SELECT MAX(salary) FROM salaries
WHERE salaries.to_date = ‘9999-01-01’);

–19.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
–毁三观！！！！！！

–1、第一次LEFT
–JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工
–2、第二次LEFT
–JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
select e.last_name,e.first_name,d.dept_name
from employees e
left join dept_emp de on e.emp_no =de.emp_no
left join departments d on d.dept_no=de.dept_no;

–20.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth
SELECT (MAX(salary)-MIN(salary)) AS growth
FROM salaries WHERE emp_no = ‘10001’

–21.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅growth，并按照growth进行升序
select t1.emp_no, t1.salary - t2.salary as growth
from
(select e.emp_no,s.salary from salaries s,employees e where e.emp_no=s.emp_no and s.to_date=‘9999-01-01’ )as t1,
(select e.emp_no,s.salary from salaries s,employees e where e.emp_no=s.emp_no and s.from_date=e.hire_date)as t2
where t1.emp_no=t2.emp_no
order by growth;

–22.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum
– 多个表查询，可以多次链接
SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum?
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no)?
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no?
GROUP BY de.dept_no

–23.对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
SELECT emp_no,salary, AS rank
FROM salaries
WHERE to_date=‘9999-01-01’
ORDER BY emp_no

–24.对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
select s1.emp_no , s1.salary ,count(distinct s2.salary) as rank
from salaries s1, salaries s2
where s1.to_date = ‘9999-01-01’ and s2.to_date = ‘9999-01-01’ and
s1.salary<=s2.salary --最大的数只小于等于自己，第二大的数只小于等于两个数，以此类推。。。为他们的rank
group by s1.emp_no
order by rank ;

–25.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

SELECT de.dept_no, s.emp_no, s.salary?
FROM (employees AS e INNER JOIN salaries AS s ON s.emp_no = e.emp_no AND s.to_date = ‘9999-01-01’)
INNER JOIN dept_emp AS de ON e.emp_no = de.emp_no