MYSQL底层学习--自我任务学习会他们你基本上班够用了，好好看，写的很细

最新推荐文章于 2024-03-29 14:19:38 发布

Java开发追求者

最新推荐文章于 2024-03-29 14:19:38 发布

阅读量102

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_46528266/article/details/112766921

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

mysql底层学习-完整版

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 1 //正常查询，做个排序，限制显示条数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
法二：
select * from employees
where hire_date = (
select distinct hire_date from employees order by hire_date desc limit 2,1
)

3.查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no (注:输出结果以salaries.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));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘员工编号’
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

SELECT a.*, b.dept_no
FROM salaries AS a JOIN dept_manager AS b ON a.emp_no = b.emp_no
WHERE b.to_date = ‘9999-01-01’ AND a.to_date = ‘9999-01-01’
ORDER BY a.emp_no ASC

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)

select a.last_name,a.first_name,b.dept_no
from employees a
left join dept_emp b on a.emp_no = b.emp_no
where b.dept_no is not null

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

select a.last_name,a.first_name,b.dept_no
from employees a
left join dept_emp b on a.emp_no = b.emp_no

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));
法一：select e.emp_no,s.salary
from employees e join salaries s
on e.emp_no=s.emp_no and e.hire_date=s.from_date
order by e.emp_no desc
法二：select a.emp_no,b.salary
from employees a
left join salaries b on a.emp_no = b.emp_no and a.hire_date = b.from_date
order by a.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(salary)
from salaries
group by emp_no
having count(salary) > 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’
group by salary
order by salary desc

9.获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary，输出结果按照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 d.dept_no,d.emp_no,s.salary
from dept_manager d
left join salaries s on s.emp_no = d.emp_no
where d.to_date=‘9999-01-01’ and s.to_date=‘9999-01-01’
group by s.salary
order by d.dept_no

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

如插入为:
INSERT INTO dept_manager VALUES(‘d001’,10002,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d002’,10006,‘1990-08-05’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d003’,10005,‘1989-09-12’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d004’,10004,‘1986-12-01’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d005’,10010,‘1996-11-24’,‘2000-06-26’);
INSERT INTO dept_manager VALUES(‘d006’,10010,‘2000-06-26’,‘9999-01-01’);

INSERT INTO employees VALUES(10001,‘1953-09-02’,‘Georgi’,‘Facello’,‘M’,‘1986-06-26’);
INSERT INTO employees VALUES(10002,‘1964-06-02’,‘Bezalel’,‘Simmel’,‘F’,‘1985-11-21’);
INSERT INTO employees VALUES(10003,‘1959-12-03’,‘Parto’,‘Bamford’,‘M’,‘1986-08-28’);
INSERT INTO employees VALUES(10004,‘1954-05-01’,‘Chirstian’,‘Koblick’,‘M’,‘1986-12-01’);
INSERT INTO employees VALUES(10005,‘1955-01-21’,‘Kyoichi’,‘Maliniak’,‘M’,‘1989-09-12’);
INSERT INTO employees VALUES(10006,‘1953-04-20’,‘Anneke’,‘Preusig’,‘F’,‘1989-06-02’);
INSERT INTO employees VALUES(10007,‘1957-05-23’,‘Tzvetan’,‘Zielinski’,‘F’,‘1989-02-10’);
INSERT INTO employees VALUES(10008,‘1958-02-19’,‘Saniya’,‘Kalloufi’,‘M’,‘1994-09-15’);
INSERT INTO employees VALUES(10009,‘1952-04-19’,‘Sumant’,‘Peac’,‘F’,‘1985-02-18’);
INSERT INTO employees VALUES(10010,‘1963-06-01’,‘Duangkaew’,‘Piveteau’,‘F’,‘1989-08-24’);
INSERT INTO employees VALUES(10011,‘1953-11-07’,‘Mary’,‘Sluis’,‘F’,‘1990-01-22’);

select * from dept_manager

select * from employees

法一：
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

法二：
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL

11.获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的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 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));

如插入：
INSERT INTO dept_emp VALUES(10001,‘d001’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10002,‘d001’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10003,‘d004’,‘1995-12-03’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10004,‘d004’,‘1986-12-01’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10005,‘d003’,‘1989-09-12’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10006,‘d002’,‘1990-08-05’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10007,‘d005’,‘1989-02-10’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10008,‘d005’,‘1998-03-11’,‘2000-07-31’);
INSERT INTO dept_emp VALUES(10009,‘d006’,‘1985-02-18’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10010,‘d005’,‘1996-11-24’,‘2000-06-26’);
INSERT INTO dept_emp VALUES(10010,‘d006’,‘2000-06-26’,‘9999-01-01’);

INSERT INTO dept_manager VALUES(‘d001’,10002,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d002’,10006,‘1990-08-05’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d003’,10005,‘1989-09-12’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d004’,10004,‘1986-12-01’,‘9999-01-01’);
INSERT INTO dept_manager VALUES(‘d005’,10010,‘1996-11-24’,‘2000-06-26’);
INSERT INTO dept_manager VALUES(‘d006’,10010,‘2000-06-26’,‘9999-01-01’);

#此处最好的方法还是使用笛卡尔积，这样的话筛选出所有的可能情况，然后过滤
select dept_emp.emp_no as emp_no, dept_manager.emp_no as manager_no
from dept_emp, dept_manager

多表联查没用left、right、inner，查出来的结果是笛卡尔积

where dept_emp.dept_no=dept_manager.dept_no

员工的部门编号等于经理的部门编号

and dept_emp.emp_no!=dept_manager.emp_no

员工中去掉经理

and dept_manager.to_date=‘9999-01-01’;

12.获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出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));
如插入：
INSERT INTO dept_emp VALUES(10001,‘d001’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10002,‘d001’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10003,‘d001’,‘1996-08-03’,‘1997-08-03’);

INSERT INTO salaries VALUES(10001,90000,‘1986-06-26’,‘1987-06-26’);
INSERT INTO salaries VALUES(10001,88958,‘2002-06-22’,‘9999-01-01’);
INSERT INTO salaries VALUES(10002,72527,‘1996-08-03’,‘1997-08-03’);
INSERT INTO salaries VALUES(10002,72527,‘2000-08-02’,‘2001-08-02’);
INSERT INTO salaries VALUES(10002,72527,‘2001-08-02’,‘9999-01-01’);
INSERT INTO salaries VALUES(10003,90000,‘1996-08-03’,‘1997-08-03’);

select * from dept_emp

select * from salaries

select d.dept_no,s.emp_no,s.salary
from dept_emp d
left 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 s.salary
ORDER BY d.dept_no
limit 1

获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)

员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，

给出dept_no, emp_no以及其对应的salary，

按照部门编号升序排列。

select d.dept_no,s.emp_no,s.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 s.salary
ORDER BY d.dept_no asc

SELECT d.dept_no, MAX(s.salary)
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
AND d.to_date=‘9999-01-01’
AND s.to_date=‘9999-01-01’
GROUP BY d.dept_no;

select uni.dept_no, uni.emp_no, max_salary.salary
from
(select d.dept_no, s.emp_no, s.salary
from dept_emp d join salaries s
on d.emp_no = s.emp_no
and d.to_date = ‘9999-01-01’
and s.to_date = ‘9999-01-01’
) as uni, /* 部门编号,员工编号,当前薪水 /
(select d.dept_no, max(s.salary) as salary
from dept_emp d join salaries s
on d.emp_no = s.emp_no
and d.to_date = ‘9999-01-01’
and s.to_date = ‘9999-01-01’
group by d.dept_no
) as max_salary / 部门编号,当前最高薪水 */
where uni.salary = max_salary.salary
and uni.dept_no = max_salary.dept_no
order by uni.dept_no;

select d.dept_no,s.emp_no,MAX(s.salary)
from dept_emp d
INNER JOIN salaries s on d.emp_no = s.emp_no
and d.to_date = ‘9999-01-01’ and s.to_date=‘9999-01-01’
GROUP BY s.salary
ORDER BY d.dept_no asc
LIMIT 1

第一步：每个部门最高的薪水
select d.dept_no,max(s.salary) salary
from dept_emp d
INNER join salaries s on d.emp_no=s.emp_no
and d.to_date = ‘9999-01-01’ and s.to_date=‘9999-01-01’
GROUP BY d.dept_no

===>
d001 88958
第二步(将员工、部门、薪水整合在一张表内)：
SELECT d.emp_no,d.dept_no,s.salary
from dept_emp d
INNER JOIN salaries s on d.emp_no=s.emp_no
and d.to_date=‘9999-01-01’
and s.to_date=‘9999-01-01’;

第三步：(将t1和t2整合)
SELECT t1.dept_no,t2.emp_no,t2.salary
from (select d.dept_no,max(s.salary) salary
from dept_emp d
INNER join salaries s on d.emp_no=s.emp_no
and d.to_date = ‘9999-01-01’ and s.to_date=‘9999-01-01’
GROUP BY d.dept_no)t1
INNER JOIN
(SELECT d.emp_no,d.dept_no,s.salary
from dept_emp d
INNER JOIN salaries s on d.emp_no=s.emp_no
and d.to_date=‘9999-01-01’
and s.to_date=‘9999-01-01’)t2 on t1.dept_no = t2.dept_no
and t1.salary = t2.salary
order by t1.dept_no;

13.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

CREATE TABLE titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
如插入：
INSERT INTO titles VALUES(10001,‘Senior Engineer’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO titles VALUES(10002,‘Staff’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10003,‘Senior Engineer’,‘1995-12-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10004,‘Engineer’,‘1986-12-01’,‘1995-12-01’);
INSERT INTO titles VALUES(10004,‘Senior Engineer’,‘1995-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Senior Staff’,‘1996-09-12’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Staff’,‘1989-09-12’,‘1996-09-12’);
INSERT INTO titles VALUES(10006,‘Senior Engineer’,‘1990-08-05’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Senior Staff’,‘1996-02-11’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Staff’,‘1989-02-10’,‘1996-02-11’);
INSERT INTO titles VALUES(10008,‘Assistant Engineer’,‘1998-03-11’,‘2000-07-31’);
INSERT INTO titles VALUES(10009,‘Assistant Engineer’,‘1985-02-18’,‘1990-02-18’);
INSERT INTO titles VALUES(10009,‘Engineer’,‘1990-02-18’,‘1995-02-18’);
INSERT INTO titles VALUES(10009,‘Senior Engineer’,‘1995-02-18’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);

select *,count(title) t
from titles
GROUP BY title
HAVING t >= 2

14.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
如插入：
INSERT INTO titles VALUES(10001,‘Senior Engineer’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO titles VALUES(10002,‘Staff’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10003,‘Senior Engineer’,‘1995-12-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10004,‘Engineer’,‘1986-12-01’,‘1995-12-01’);
INSERT INTO titles VALUES(10004,‘Senior Engineer’,‘1995-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Senior Staff’,‘1996-09-12’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Staff’,‘1989-09-12’,‘1996-09-12’);
INSERT INTO titles VALUES(10006,‘Senior Engineer’,‘1990-08-05’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Senior Staff’,‘1996-02-11’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Staff’,‘1989-02-10’,‘1996-02-11’);
INSERT INTO titles VALUES(10008,‘Assistant Engineer’,‘1998-03-11’,‘2000-07-31’);
INSERT INTO titles VALUES(10009,‘Assistant Engineer’,‘1985-02-18’,‘1990-02-18’);
INSERT INTO titles VALUES(10009,‘Engineer’,‘1990-02-18’,‘1995-02-18’);
INSERT INTO titles VALUES(10009,‘Senior Engineer’,‘1995-02-18’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

select * from titles

select distinct emp_no,title,count(title) t
from titles
GROUP BY title
HAVING t >= 2

第一步找出所有的不重复的emp_no
select distinct emp_no from titles

第二步统计个数
SELECT Z.title, COUNT(title) t
FROM (SELECT DISTINCT emp_no,title FROM titles)Z
GROUP BY Z.title
HAVING t >= 2;

SELECT title, COUNT(DISTINCT emp_no) t
FROM titles
GROUP BY title
HAVING t >= 2;

SELECT title, COUNT(distinct emp_no) t
FROM titles
GROUP BY title
HAVING t >= 2;

14.查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列
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));

如插入：
INSERT INTO employees VALUES(10001,‘1953-09-02’,‘Georgi’,‘Facello’,‘M’,‘1986-06-26’);
INSERT INTO employees VALUES(10002,‘1964-06-02’,‘Bezalel’,‘Simmel’,‘F’,‘1985-11-21’);
INSERT INTO employees VALUES(10003,‘1959-12-03’,‘Parto’,‘Bamford’,‘M’,‘1986-08-28’);
INSERT INTO employees VALUES(10004,‘1954-05-01’,‘Chirstian’,‘Koblick’,‘M’,‘1986-12-01’);
INSERT INTO employees VALUES(10005,‘1955-01-21’,‘Kyoichi’,‘Maliniak’,‘M’,‘1989-09-12’);
INSERT INTO employees VALUES(10006,‘1953-04-20’,‘Anneke’,‘Preusig’,‘F’,‘1989-06-02’);
INSERT INTO employees VALUES(10007,‘1957-05-23’,‘Tzvetan’,‘Zielinski’,‘F’,‘1989-02-10’);
INSERT INTO employees VALUES(10008,‘1958-02-19’,‘Saniya’,‘Kalloufi’,‘M’,‘1994-09-15’);
INSERT INTO employees VALUES(10009,‘1952-04-19’,‘Sumant’,‘Peac’,‘F’,‘1985-02-18’);
INSERT INTO employees VALUES(10010,‘1963-06-01’,‘Duangkaew’,‘Piveteau’,‘F’,‘1989-08-24’);
INSERT INTO employees VALUES(10011,‘1953-11-07’,‘Mary’,‘Sluis’,‘F’,‘1990-01-22’);

查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列
select a.*
from (SELECT t1.* FROM employees t1 WHERE MOD ( emp_no, 2 ) =1) a
where ‘last_name’ != “Mary”
order by hire_date desc

from employees
where ‘last_name’ != “Mary”
order by hire_date desc

匹配偶数行：
SELECT t1.ID, t1.TagDesc, FROM temp1_status t1 WHERE MOD ( id, 2 ) =0

select * from (select @rownum := @rownum+1 as row_num, t.* from 表名 t,(select @rownum:=0) tmp_table order by CreateTime limit 10) t where t.row_num%2=1

15.统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

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));
CREATE TABLE IF NOT EXISTS “titles” (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

如插入：
INSERT INTO salaries VALUES(10001,88958,‘1986-06-26’,‘9999-01-01’);
INSERT INTO salaries VALUES(10003,43311,‘2001-12-01’,‘9999-01-01’);
INSERT INTO salaries VALUES(10004,70698,‘1986-12-01’,‘1995-12-01’);
INSERT INTO salaries VALUES(10004,74057,‘1995-12-01’,‘9999-01-01’);
INSERT INTO salaries VALUES(10006,43311,‘2001-08-02’,‘9999-01-01’);
INSERT INTO salaries VALUES(10007,88070,‘2002-02-07’,‘9999-01-01’);

INSERT INTO titles VALUES(10001,‘Senior Engineer’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO titles VALUES(10003,‘Senior Engineer’,‘2001-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10004,‘Engineer’,‘1986-12-01’,‘1995-12-01’);
INSERT INTO titles VALUES(10004,‘Senior Engineer’,‘1995-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10006,‘Senior Engineer’,‘2001-08-02’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Senior Staff’,‘1996-02-11’,‘9999-01-01’);

统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。
select t.title,AVG(s.salary)
from salaries s
left join titles t on s.emp_no = t.emp_no
where t.to_date=‘9999-01-01’ and s.to_date=‘9999-01-01’
group by t.title

16.获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水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 emp_no,salary
from salaries
where to_date=‘9999-01-01’
GROUP BY emp_no
ORDER BY salary
LIMIT 1,1

完美：
select emp_no,salary
from salaries
where to_date=‘9999-01-01’
GROUP BY emp_no
ORDER BY salary desc
LIMIT 1,1

17.查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

SELECT
e.emp_no,
s.salary,
e.last_name,
e.first_name
FROM
employees AS e
INNER JOIN salaries AS s ON e.emp_no = s.emp_no
WHERE
to_date = ‘9999-01-01’
AND salary = (
SELECT
s1.salary
FROM
salaries AS s1
INNER JOIN salaries AS s2 ON s1.to_date = ‘9999-01-01’
AND s2.to_date = ‘9999-01-01’
#此处查出4条数据两个两个相同
WHERE
s1.salary <= s2.salary
GROUP BY
s1.salary
HAVING
COUNT(DISTINCT s2.salary) = 2
);

18.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (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));

如插入：
INSERT INTO departments VALUES(‘d001’,‘Marketing’);
INSERT INTO departments VALUES(‘d002’,‘Finance’);
INSERT INTO departments VALUES(‘d003’,‘Human Resources’);
INSERT INTO departments VALUES(‘d004’,‘Production’);
INSERT INTO departments VALUES(‘d005’,‘Development’);
INSERT INTO departments VALUES(‘d006’,‘Quality Management’);

INSERT INTO dept_emp VALUES(10001,‘d001’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10002,‘d001’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10003,‘d004’,‘1995-12-03’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10004,‘d004’,‘1986-12-01’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10005,‘d003’,‘1989-09-12’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10006,‘d002’,‘1990-08-05’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10007,‘d005’,‘1989-02-10’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10008,‘d005’,‘1998-03-11’,‘2000-07-31’);
INSERT INTO dept_emp VALUES(10009,‘d006’,‘1985-02-18’,‘9999-01-01’);
INSERT INTO dept_emp VALUES(10010,‘d005’,‘1996-11-24’,‘2000-06-26’);
INSERT INTO dept_emp VALUES(10010,‘d006’,‘2000-06-26’,‘9999-01-01’);

这个是包括员工没有部门的：
select e.last_name,e.first_name,d.dept_name
from employees e
left JOIN dept_emp dt on e.emp_no = dt.emp_no
left JOIN departments d on d.dept_no = dt.dept_no

这个是不包括员工没有部门的：
select e.last_name,e.first_name,d.dept_name
from employees e
INNER JOIN dept_emp dt on e.emp_no = dt.emp_no
INNER JOIN departments d on d.dept_no = dt.dept_no

19.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth (可能有多次涨薪，没有降薪)

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 salary from salaries where emp_no = 10001
select sum(s2.salary-s1.salary)
from salaries s1 , salaries s2
where s1.emp_no = 10001
and s1.to_date = s2.from_date

SELECT
(MAX(salary) - MIN(salary)) AS growth
FROM
salaries
WHERE
emp_no = ‘10001’

select (
select max(s1.salary)
from salaries s1
where s1.emp_no=10001
)-
(
select min(s2.salary)
from salaries s2
where s2.emp_no=10001
) as growth

select (max(salary)-min(salary))growth from salaries
where emp_no = 10001

20.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

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

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (employees AS e
INNER JOIN salaries AS sCurrent
ON e.emp_no=sCurrent.emp_no
AND sCurrent.to_date=‘9999-01-01’
INNER JOIN salaries AS sStart
ON e.emp_no=sStart.emp_no
AND sStart.from_date=e.hire_date
)
ORDER BY growth ASC;

SELECT
s1.emp_no,
s2.salary - s1.salary AS growth
FROM
(SELECT
e.emp_no,
s.salary
FROM
employees e
LEFT JOIN salaries s
ON e.emp_no = s.emp_no
WHERE e.hire_date = s.from_date) AS s1 – 入职薪水
INNER JOIN
(SELECT
e.emp_no,
s.salary
FROM
employees e
LEFT JOIN salaries s
ON e.emp_no = s.emp_no
WHERE s.to_date = ‘9999-01-01’) AS s2 – 现在薪水
ON s1.emp_no = s2.emp_no
ORDER BY growth

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

21.对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，
相同salary并列且按照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));

这道题目考察的是SQL窗口函数（OLAP函数）中用于排序的专用窗口函数用法
但是由于关系数据库提供支持OLAP用途功能时间不长
还有一部分DBMS不支持这个新功能（比如MYSQL）
select emp_no, salary,
dense_rank() over (order by salary desc) as rank
from salaries
where to_date=‘9999-01-01’
order by rank asc,emp_no asc;

下面介绍三种用于进行排序的专用窗口函数：

1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······

2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······

3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> ）
*其中[ ]中的内容可以忽略

先构建不含salary的rank表，再将rank表和salaries表内接，然后排序得到结果

SELECT s1.emp_no,s1.salary,COUNT(DISTINCT s2.salary) 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
GROUP BY s1.emp_no
ORDER BY s1.salary DESC , s1.emp_no ASC
从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，
有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

22.获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示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 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,e.emp_no,s.salary FROM employees e
INNER JOIN salaries s ON s.emp_no = e.emp_no
INNER JOIN dept_manager d ON d.emp_no = e.emp_no
INNER JOIN dept_emp de.emp_no = d.emp_no and de.dept_no = d.dept_no
.

SELECT dept_emp.dept_no,employees.emp_no,salary
FROM employees
LEFT JOIN dept_emp ON employees.emp_no=dept_emp.emp_no
LEFT JOIN salaries ON employees.emp_no=salaries.emp_no

WHERE dept_emp.to_date=‘9999-01-01’
AND salaries.to_date=‘9999-01-01’
AND employees.emp_no NOT IN (
SELECT emp_no
FROM dept_manager
WHERE to_date=‘9999-01-01’)

23.获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 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 dept_emp.emp_no emp_no,dept_manager.emp_no manager_no
from dept_emp
LEFT JOIN dept_manager on dept_emp.emp_no = dept_manager.emp_no
WHERE dept_emp.emp_no NOT IN(SELECT dept_emp.emp_no from dept_emp
LEFT JOIN salaries on dept_emp.emp_no = salaries.emp_no
where dept_emp.to_date=‘9999-01-01’
) and dept_emp.to_date=‘9999-01-01’

经理当前的薪水：
SELECT dept_manager.emp_no,salaries.salary from dept_manager
LEFT JOIN salaries on dept_manager.emp_no = salaries.emp_no
where dept_manager.to_date=‘9999-01-01’

员工当前的薪水：
SELECT dept_emp.emp_no,salaries.salary from dept_emp
LEFT JOIN salaries on dept_emp.emp_no = salaries.emp_no
where dept_emp.to_date=‘9999-01-01’

查询员工当前工资表 emp_sal
select de.emp_no,de.dept_no,s1.salary as emp_salary
from dept_emp de,salaries s1
where de.emp_no=s1.emp_no
and s1.to_date=‘9999-01-01’
and de.to_date=‘9999-01-01’
查询经理当前工资表mag_sal
select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
from dept_manager dm,salaries s2
where dm.emp_no=s2.emp_no
and s2.to_date=‘9999-01-01’
and dm.to_date=‘9999-01-01’
联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
select de.emp_no,de.dept_no,s1.salary as emp_salary
from dept_emp de,salaries s1
where de.emp_no=s1.emp_no
and s1.to_date=‘9999-01-01’
and de.to_date=‘9999-01-01’
)as emp_sal
inner join(
select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
from dept_manager dm,salaries s2
where dm.emp_no=s2.emp_no
and s2.to_date=‘9999-01-01’
and dm.to_date=‘9999-01-01’
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;

24.汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、
其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count，结果按照dept_no升序排序
(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，
而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (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 IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

select departments.dept_no,departments.dept_name
from departments,dept_emp

25.汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)
员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count，结果按照dept_no升序排序
(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，
所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (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 IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

select dde.dept_no,dde.dept_name,t.title,count(t.title ) as count
from titles t left join
(select d.dept_no,d.dept_name,de.emp_no
from departments as d left join dept_emp de
on d.dept_no = de.dept_no
where de.to_date = ‘9999-01-01’)as dde
on t.emp_no = dde.emp_no
where t.to_date = ‘9999-01-01’
group by dde.dept_no,t.title
order by dde.dept_no;
法一：
select s1.emp_no,s2.from_date,(s2.salary-s1.salary) salary_growth
from salaries s1,salaries s2
where s2.salary-s1.salary > 5000 and s1.emp_no = s2.emp_no and s1.to_date = s2.from_date
order by salary_growth desc
法二：
select s1.emp_no,s2.from_date,(s2.salary-s1.salary) salary_growth
from salaries as s1 INNER JOIN salaries as s2
on s1.emp_no = s2.emp_no and s1.to_date = s2.from_date
where s2.salary-s1.salary > 5000
order by salary_growth desc

26.创建表
INSERT INTO film_category VALUES(1,6,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(2,11,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(3,6,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(4,11,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(5,6,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(6,6,‘2006-02-14 21:07:09’);

INSERT INTO film_category VALUES(8,6,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(9,11,‘2006-02-14 21:07:09’);
INSERT INTO film_category VALUES(10,15,‘2006-02-14 21:07:09’);

select c.name,
count(fc.film_id)
from film f,category c,film_category fc
where f.description like ‘%robot%’
and f.film_id=fc.film_id
and fc.category_id=c.category_id
and c.category_id in (select category_id
from film_category
group by category_id
having count(film_id)>=5)

Java开发追求者

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MYSQL底层学习--自我任务学习会他们你基本上班够用了，好好看，写的很细

mysql底层学习-完整版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.查找最晚入职员工的所有信息se
复制链接

扫一扫