☺☺☺
六道题
考查知识点
题号 | 知识点 |
---|---|
1 | ...LEFT JOIN...ON... 会读取左边数据表的全部数据,即便右边表无对应数据 |
2 | MAX() 、MIN() |
3 | ...JOIN...ON... 用于把来自两个或多个表的行按ON后边的条件结合起来。INNER JOIN(JOIN)...ON... 两边表同时有对应的数据,即任何一边缺失数据就不显示。ASC 升序 |
4 | ...JOIN...ON... 根据ON 之后的字段将两个表链接起来;GROUP BY 分组;COUNT() 统计数据条数 |
5 | 表的重复使用;ORDER BY 函数可以有多个参数; DISTINCT 去重; DESC 降序; ASC 升序 |
6 | ...NOT IN... 不属于 |
补充知识:MySQL GROUP BY 语句
-
GROUP BY 语句根据一个或多个列对结果集进行分组。
-
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
一、查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
- 题目描述:
查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
CREATE TABLEdepartments
(
dept_no
char(4) NOT NULL,
dept_name
varchar(40) NOT NULL,
PRIMARY KEY (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...
,即使右边的表中没有响应的数据,也要输出NULL。 - 因为last_name、first_name字段和dept_name字段不在一个表中,且没有直接的联系,因此需要用表dept_emp 来进行间接的链接,本题用了两次LEFT JOIN ON:
(1)第一次用LEFT JOIN 链接employees 表和dept_emp ,得到最终需要的前两个字段last_name、first_name,与间接连接字段dept_no;
(2)第二次链接用LEFT JOIN 链接(1)中的表与departments 表,得到最终需要的三个字段。
- 代码:
SELECT x.last_name, x.first_name, a.dept_name
FROM (SELECT c.last_name, c.first_name, b.dept_no
FROM employees c LEFT JOIN dept_emp b ON c.emp_no = b.emp_no
) AS x
LEFT JOIN departments a ON x.dept_no = A.dept_no
二、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
- 题目描述:
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
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
));
- 分析:
- 题目说了没有降薪,所以我认为是可以直接用最大的薪水值减去最小的薪水值(入职时的薪水值)的;
- 但是还有一个严谨的普遍做法,就是找到入职时候的工资,与最后一次工资的记录,两值相减得到的就是这个员工的工资涨幅:
SELECT
(SELECT salary FROM salaries WHERE empo_no = 1001 ORDER BYto date DESC LIMIT 1)-
(SELECT salary FROM salaries WHERE empo_no = 1001 ORDER BY date DESC LIMIT 1)
AS growrh
- 代码:
SELECT MAX(salary)- MIN(salary) as growth
FROM salaries
WHERE emp_no=10001
- 注意:字段的拼写一定要正确!
三、查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
- 题目描述:
查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
(注:可能有employees表和salaries表里存在记录的员工,有对应的员工编号和涨薪记录,但是已经离职了,离职的员工salaries表的最新的to_date!=‘9999-01-01’,这样的数据不显示在查找结果里面)
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
));
- 分析:
- 找到除了离职员工之外的所有员工当前的薪水值
- 找到所有员工入职时候的薪水值
- 两表相减即得到除了离职员工之外的每个人薪水涨幅
Tips:目前的时间为to_date=‘9999-01-01’;入职时间=from_date=hire_date
;薪水涨幅=两个时期薪水值相减。
- 代码1:
SELECT x.emp_no, (x.salary-y.salary) AS growth
FROM
(SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
) x,
(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
) y
WHERE x.emp_no = y.emp_no
ORDER BY growth
ASC
- 代码2:
这个也好理解,但是还没有这么写过:先用JOIN
将所需要的l这两列数据即每个员工两个时期的薪水值连接到一张表里,然后将目前每个员工的薪水值-入职时候的薪水值即可。
select e.emp_no, (j.salary - s.salary) as growth
from employees e
join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date
join salaries j on e.emp_no = j.emp_no and j.to_date = '9999-01-01'
order by growth;
asc
四、统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum
- 题目描述:
统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum
CREATE TABLEdepartments
(
dept_no
char(4) NOT NULL,
dept_name
varchar(40) NOT NULL,
PRIMARY KEY (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 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
));
- 分析:
统计各部门的工资记录总数,直接GROUP BY
之后再COUNT()
统计记录数即可,但是现在是有三个表,需要现实的字段之间没有直接的联系,因此需要用...JOIN...ON...
链接起来,在进行计算统计。 - 代码:
SELECT a.dept_no, a.dept_name, COUNT(c.salary)
FROM departments a
JOIN dept_emp b ON a.dept_no=b.dept_no
JOIN salaries c ON b.emp_no=c.emp_no
GROUP BY a.dept_no
五、对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
- 题目描述:
对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照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
));
- 思路:表的重复使用
- 按salary排名,所以先
ORDER BY salary
并且是降序,即salary值越大,排名越靠前; - 若salary相同,则按emp_no升序排列,这是ORDER BY的第二个条件,ORDER BY排序函数可以有多个参数;
- 注意这里多了一列数据是原表中没有的,即排名
rank
。我们再复合一个新表,让新表里的salary值都 大于等于原表的salary,则原表里每个salary的排名rank
就等于DISTINCT(新表里salary的值)
,即使有并列排名的也可以显示出来。 - 本题的精髓在于 s1.salary <=s2.salary,意思是在输出s1.salary的情况下,有多少个s2.salary大于等于s1.salary,比如当s1.salary=94409时,有3个s2.salary(分别为94692,94409,94409)大于等于它,但由于94409重复,利用COUNT(DISTINCTs2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。(from nowcoder,作者:ciphersaw)
- 注意:这里用
GROUP BY
让s1.emp_no
聚合的目的是:每回取出一个s1.salary与s2中的所有salary作比较。因为使用了合计函数导致count只返回一个值,表s1选择返回的值却有好几个,所以必须进行分组查询。
- 代码:
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 s2.salary>=s1.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC
六、获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date=‘9999-01-01’
- 题目描述:
获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date=‘9999-01-01’
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 TABLEdept_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 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
));
- 分析:
- 一共给了四个表,其中表3employees不用也可也以;
- 先从表dept_manager中找出属于manager的员工编号,限制条件为时间to_date;
- 然后根据表dept_emp 与salaries 找出dept_no, emp_no, salary这三个信息,其中emp_no的限制为不在manager里。
- 代码:
SELECT a.dept_no, a.emp_no, b.salary
FROM dept_emp a, salaries b
WHERE a.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date='9999-01-01')
AND a.emp_no=b.emp_no
AND a.to_date='9999-01-01'
AND b.to_date='9999-01-01'
使用…INNER JOIN …ON…的效果一样:
SELECT de.dept_no, de.emp_no, s.salary
FROM dept_emp AS de INNER JOIN salaries AS s ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01')