1.好久没有学习mysql了突然想来学习一下首先要了解mysql语法,这个语法我知道所以我就不过多的去说直接上习题:
1)请你查找employees里最晚入职员工的所有信息:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
10004 | 1954-05-01 | Christian | Koblick | M | 1986-12-01 |
答案:
select * from employees
order by hire_date desc
limit 1;
/* 使用limit 与 offset关键字 */
select * from employees
order by hire_date desc
limit 1 offset 0;
/* 使用limit关键字 从第0条记录 向后读取一个,也就是第一条记录 */
select * from employees
order by hire_date desc
limit 0,1;
/* 使用子查询,最后一天的时间有多个员工信息 */
select * from employees
where hire_date = (select max(hire_date) from employees);
2)请你查找employees里入职员工时间排名倒数第三的员工所有信息
SELECT *
FROM employees
WHERE hire_date = (
SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date DESC -- 倒序
LIMIT 1 OFFSET 2 -- 去掉排名倒数第一第二的时间,取倒数第三
);
3)请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no,输出结果以salaries.emp_no升序排序,并且请注意输出结果里面dept_no列是最后一列:
有一个全部员工的薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 88958 | 2002-06-22 | 9999-01-01 |
10002 | 72527 | 2001-08-02 | 9999-01-01 |
10003 | 43311 | 2001-12-01 | 9999-01-01 |
有一个各个部门的领导表dept_manager简况如下:
dept_no | emp_no | to_date |
d001 | 10001 | 9999-01-01 |
d002 | 10003 | 9999-01-01 |
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'
或者
select s.*,d.dept_no
from salaries as s , 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'
4)请你查找所有已经分配部门的员工的last_name和first_name以及dept_no,未分配的部门的员工不显示:
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e, dept_emp AS d
WHERE e.emp_no = d.emp_no;
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e JOIN dept_emp AS d
ON e.emp_no=d.emp_no;
SELECT e.last_name, e.first_name, d.dept_no
FROM employees AS e INNER JOIN dept_emp AS 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 AS e
LEFT OUTER JOIN dept_emp AS d
ON e.emp_no=d.emp_no;
6)请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t:
有一个薪水表,salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 60117 | 1986-06-26 | 1987-06-26 |
10001 | 62102 | 1987-06-26 | 1988-06-25 |
10001 | 66074 | 1988-06-25 | 1989-06-25 |
10001 | 66596 | 1989-06-25 | 1990-06-25 |
10001 | 66961 | 1990-06-25 | 1991-06-25 |
10001 | 71046 | 1991-06-25 | 1992-06-24 |
10001 | 74333 | 1992-06-24 | 1993-06-24 |
10001 | 75286 | 1993-06-24 | 1994-06-24 |
10001 | 75994 | 1994-06-24 | 1995-06-24 |
10001 | 76884 | 1995-06-24 | 1996-06-23 |
10001 | 80013 | 1996-06-23 | 1997-06-23 |
10001 | 81025 | 1997-06-23 | 1998-06-23 |
10001 | 81097 | 1998-06-23 | 1999-06-23 |
10001 | 84917 | 1999-06-23 | 2000-06-22 |
10001 | 85112 | 2000-06-22 | 2001-06-22 |
10001 | 85097 | 2001-06-22 | 2002-06-22 |
10002 | 72527 | 1996-08-03 | 1997-08-03 |
select emp_no,count(distinct salary) as t from salaries
group by emp_no having t>15
7)请你找出所有员工具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示:
有一个薪水表,salaries简况如下:
emp_no | salary | from_date | to_date |
10001 | 72527 | 2002-06-22 | 9999-01-01 |
10002 | 72527 | 2001-08-02 | 9999-01-01 |
10003 | 43311 | 2001-12-01 | 9999-01-01 |
select distinct salary from salaries order by salary desc
8)请你找出所有非部门领导的员工emp_no
有一个员工表employees简况如下:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
有一个部门领导表dept_manager简况如下:
dept_no | emp_no | from_date | to_date |
d001 | 10002 | 1996-08-03 | 9999-01-01 |
d002 | 10003 | 1990-08-05 | 9999-01-01 |
#用not in 在实际中实现可观
select emp_no from employees where emp_no not in(select emp_no from dept_manager)
9)获取所有的员工和员工对应的经理,如果员工本身是经理的话则不显示:
有一个员工表dept_emp简况如下:
emp_no | dept_no | from_date | to_date |
10001 | d001 | 1986-06-26 | 9999-01-01 |
10002 | d001 | 1996-08-03 | 9999-01-01 |
10003 | d002 | 1995-12-03 | 9999-01-01 |
第一行表示为员工编号为10001的部门是d001部门。
有一个部门经理表dept_manager简况如下:
dept_no | emp_no | from_date | to_date |
d001 | 10002 | 1996-08-03 | 9999-01-01 |
d002 | 10003 | 1990-08-05 | 9999-01-01 |
第一行表示为d001部门的经理是编号为10002的员工。
select
e.emp_no,
d.emp_no as manager
from
dept_emp e
inner join dept_manager d
on e.dept_no = d.dept_no
where
e.emp_no <>d.emp_no
10)获取每个部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号dept_no升序排列:
# select d.emp_no,d.dept_no,salary as maxSalary from dept_emp d
# left join salaries s
# on d.emp_no=s.emp_no
# group by d.dept_no,d.emp_no
# having s.salary=max(s.salary)
select dept_no, emp_no,salary as maxSalary from(
select d.dept_no as dept_no, d.emp_no as emp_no, s.salary as salary,
rank() over(partition by d.dept_no order by s.salary desc) as ranking
from dept_emp d join salaries s on s.emp_no = d.emp_no
) as t where t.ranking = 1;