目录
Sql3.查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no
Sql4.查找所有已经分配部门的员工的last_name和first_name
Sql5.查找所有员工的last_name和first_name以及对应部门编号dept_no
Sql7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
Sql1. 查找最晚入职员工的所有信息
查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
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`));
解法一:子查询,这种方法(先找出 hire_date 字段的最大值,再把该值当成 employees 表的 hire_date 查询条件。 )
SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);
解法二:排序,降序(这个方法比较局限,如果同一天有多个员工入职就有问题了)。
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;
Sql2.查找入职员工时间排名倒数第三的员工所有信息
查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
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`));
思路:按入职时间倒序排列,然后用limit选择第三个即可。
select distinct * 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);
Sql3.查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no
查找各个部门当前(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 s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no
from salaries s join dept_manager dm on s.emp_no=dm.emp_no
where s.to_date='9999-01-01' and dm.to_date='9999-01-01'
select s.emp_no,s.salary,s.from_date,s.to_date,dm.dept_no
from salaries s,dept_manager dm where s.emp_no=dm.emp_no
and s.to_date='9999-01-01' and dm.to_date='9999-01-01';
Sql4.查找所有已经分配部门的员工的last_name和first_name
查找所有已经分配部门的员工的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 e.last_name,e.first_name,de.dept_no
FROM employees e,dept_emp de where e.emp_no=de.emp_no;
SELECT e.last_name,e.first_name,de.dept_no
FROM employees e JOIN dept_emp de ON e.emp_no=de.emp_no;
Sql5.查找所有员工的last_name和first_name以及对应部门编号dept_no
查找所有员工的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`));
注意:
INNER JOIN 两边表同时有对应的数据,即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据,即便左边表无对应数据。
SELECT e.last_name,e.first_name,de.dept_no FROM
employees e LEFT JOIN dept_emp de ON e.emp_no=de.emp_no;
Sql6.查找所有员工入职时候的薪水情况
查找所有员工入职时候的薪水情况,给出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`));
解法一:只调用1表,先按照员工编号分组,再用having过滤出 最小开始日期的情况,获取salaries中from_data中最小的对应的salary和emp_no,因为salaries可能涨薪,所以这张表一个员工可能有多条记录。
SELECT emp_no,salary FROM salaries GROUP BY emp_no
HAVING min(from_date) ORDER BY emp_no DESC;
解法二:连接两表,因为薪水表的员工编号不唯一,所以限制条件:雇佣日期 = 薪水开始日期,即hire_data时间和from_data时间一致
SELECT e.emp_no,s.salary
FROM employees e,salaries s WHERE e.emp_no=s.emp_no AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC;
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 t.* FROM (SELECT e.emp_no,s.salary
FROM employees e,salaries s WHERE e.emp_no=s.emp_no AND e.hire_date = s.from_date) t ORDER BY t.emp_no DESC;
Sql7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
查找薪水变动超过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`));
此题应注意以下四点:
1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意,输出的涨幅次数为t,故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中,故使用HAVING语句来限定t>15的条件
4、最后存在一个理解误区,涨幅超过15次,salaries中相应的记录数应该超过16(从第2条记录开始算作第1次涨幅),不过题目为了简单起见,将第1条记录当作第1次涨幅,所以令t>15即可
/** 注意: 严格来说,下一条salary高于本条才算涨幅,但本题只要出现了一条记录就算一次涨幅,salary相同可以理解为涨幅为0,salary变少理解为涨幅为负 **/
select emp_no,count(*) t from salaries
group by emp_no having t>15
Sql8.找出所有员工当前具体的薪水salary情况
找出所有员工当前(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`));
去重时:distinct与group by的使用
1、当对系统的性能高并数据量大时使用group by
2、当对系统的性能不高时使用数据量少时两者皆可
3、尽量使用group by
解法一:根据group by 去重 (推荐)
select emp_no,count(*) t from salaries group by emp_no having t>15;
解法二:根据distinct去重
select distinct salary from salaries where to_date='9999-01-01' order by salary desc;
Sql10. 获取所有非manager的员工emp_no
获取所有非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');
解法一:使用not in选出在employees但不在dept_manager中的emp_no记录
SELECT emp_no from employees WHERE emp_no
NOT IN (SELECT emp_no from dept_manager);
解法二:使用not exists选出在employees但不在dept_manager中的emp_no记录
SELECT e.emp_no from employees e WHERE
NOT EXISTS
(SELECT d.emp_no from dept_manager d WHERE e.emp_no = d.emp_no);
解法三:使用left join,employees作为主表,dept_manager为从表,从表dept_manager的emp_no 为null
SELECT em.emp_no from employees em
LEFT JOIN dept_manager dm on em.emp_no=dm.emp_no
WHERE dm.emp_no is null;