☺☺☺
数据库的知识一直想复习,既然如此就专门开一个模块来练习题叭~
打算半个月完成实战的练习,中间还会穿插一些理论和其他知识。
然后每个题的流程依然是:题目描述+分析+代码+笔记。
Day1六道题
考查知识点
题号 | 知识点 |
---|---|
1 | MAX() 求最大值; DESC 降序排列(默认为升序排列ASC ) |
2 | limit x, y 分句表示: 跳过 x 条数据,读取 y 条数据;或者limit y offset x ; DISTINCT 去重 |
3 | 考察表的链接,用INNER JOIN...ON ... JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段;或者WHERE 限制链接也可以 |
4 | 考察表的链接 |
5 | LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据 |
6 | 考察分析题的能力;ORDER BY 排序 |
一、查找最晚入职员工的所有信息
- 题目描述:
查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)
CREATE TABLEemployees
(
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
));
- 分析:
在选择数据时让入职时间一栏值为最大,即这个人入职时间最晚。用MAX()
函数。 - 代码:
SELECT a.emp_no, a.birth_date, a.first_name, a.last_name, a.gender, MAX(a.hire_date)
FROM employees a
或者:
SELECT * FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees)
二、查找入职员工时间排名倒数第三的员工所有信息
- 题目描述:
查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
CREATE TABLEemployees
(
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
));
- 分析:
- 用
ORDER BY
对入职时间进行排序; LIMIT y OFFSET x
对要输出的数据个数以及位置进行限制,表示跳过x条数据从第y个数据开始输出;- DISTINCT去重:去除重复的日期
注意:DESC
降序排列,因为这个多次没有通过用例
- 代码:
SELECT *
FROM employees a
WHERE a.hire_date =
(SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date
DESC
LIMIT 1 OFFSET 2
)
- 笔记:SQL查询语句中的
limit
与offset
limit y
分句表示: 读取 y 条数据limit x, y
分句表示: 跳过 x 条数据,读取 y 条数据limit y offset x
分句表示: 跳过 x 条数据,从第x+1条数据开始读取 y 条数据(OFFSET表偏移)
三、查找当前薪水详情以及部门编号dept_no
- 题目
查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询,输出结果以salaries.emp_no升序排序,并且请注意输出结果里面dept_no列是最后一列)
CREATE TABLEsalaries
(
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 TABLEdept_manager
(
dept_no
char(4) NOT NULL, – ‘部门编号’
emp_no
int(11) NOT NULL, – ‘员工编号’
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
- 思路
- 使用
INNER JOIN... ON
将表连接到一起,然后排序输出。INNER JOIN 与 JOIN 是相同的。 - 或者使用
WHERE
将表的连接到一起,排序输出 - 即以下两句代码的效果是一样的
SELECT * FROM a INNER JOIN b ON a.id = b.id
和
SELECT * FROM a,b WHERE a.id = b.id
- 代码:两种实现的效果是一样的
使用INNER JOIN
的代码:
SELECT a.*, b.dept_no
FROM salaries a INNER JOIN dept_manager b ON a.emp_no=b.emp_no
WHERE a.to_date='9999-01-01' AND b.to_date='9999-01-01'
ORDER BY a.emp_no
不使用INNER JOIN
的代码:
SELECT a.*, b.dept_no
FROM salaries a , dept_manager b
WHERE a.to_date='9999-01-01' AND b.to_date='9999-01-01' and a.emp_no=b.emp_no
ORDER BY a.emp_no
四、查找所有已经分配部门的员工的last_name和first_name以及dept_no
- 题目描述
查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
CREATE TABLEdept_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 TABLEemployees
(
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, dept_emp b
WHERE a.emp_no = b.emp_no
或者
SELECT a.last_name, a.first_name, b.dept_no
FROM employees a INNER JOIN dept_emp b ON a.emp_no = b.emp_no
五、查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工
- 题目描述:
查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)
CREATE TABLEdept_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 TABLEemployees
(
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
));
- 思路:
用左链接LEFT JOIN ON
将两个表链接起来,这样左边的表中数据无论存在与否都会输出。 - 代码:
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
- 笔记:
JOIN
用于把来自两个或多个表的行结合起来。
INNER JOIN(JOIN)
两边表同时有对应的数据,即任何一边缺失数据就不显示。
LEFT JOIN
会读取左边数据表的全部数据,即便右边表无对应数据。
RIGHT JOIN
会读取右边数据表的全部数据,即便左边表无对应数据。- on与where有什么区别,两个表连接时用on,在使用left jion时,on和where条件的区别如下:(from 牛客网,作者:Manu8)
(1)on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
(2)where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left
join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
六、查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
- 题目描述:
查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序(请注意,一个员工可能有多次涨薪的情况)
CREATE TABLEemployees
(
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 TABLEsalaries
(
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
));
- 思路:
- 查找入职时候的薪水情况,所以条件要使用salaries.from_date = employees.hire_date 来进行限制;
- 按照emp_no逆序,则最后需要排序,并且降序排列;
- 一个员工可能又多次涨薪的情况,因此salaries中的值不唯一,但employees中的值是唯一的,用a.emp_no = b.emp_no条件限制即可。
- 代码:
SELECT a.emp_no, b.salary
FROM employees a, salaries b
WHERE a.emp_no = b.emp_no
AND a.hire_date = b.from_date
ORDER BY a.emp_no
DESC