Mysql练习

SQL1 查找最晚入职员工的所有信息

有一个员工employees表简况如下:

请你查找employees里最晚入职员工的所有信息，以上例子输出如下:

drop table if exists  `employees` ; 
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');

题解：

由大到小排列入职时间，结果限制一条。

SELECT * FROM employees
ORDER BY hire_date
DESC LIMIT 0,1;

SQL2 查找入职员工时间排名倒数第三的员工所有信息

有一个员工employees表简况如下:

请你查找employees里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:

题解：

和上题差不多

SELECT * FROM employees
ORDER BY hire_date
DESC LIMIT 2,1;

SQL3 查找当前薪水详情以及部门编号dept_no

有一个全部员工的薪水表salaries简况如下:

有一个各个部门的领导表dept_manager简况如下:

请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:

题解：

1.where + order

SELECT s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
FROM salaries as s ,dept_manager as d
where  s.emp_no = d.emp_no
ORDER BY s.emp_no

join+order

SELECT s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no
FROM salaries as s 
JOIN dept_manager as d
on s.emp_no = d.emp_no
ORDER BY s.emp_no

SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

有一个员工表，employees简况如下:

有一个部门表，dept_emp简况如下:

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示，以上例子如下:

输入：
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
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 dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','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');
复制
输出：
Facello|Georgi|d001
Simmel|Bezalel|d002

题解：

left join 需要把dept_emp表中为空的剔除

SELECT e.last_name,e.first_name,d.dept_no
FROM employees as e
left join dept_emp as d
ON e.emp_no = d.emp_no
where d.dept_no not NULL

right join 或内连接

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

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no

表和上题一模一样，right join 不加判断条件即可。

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

有一个薪水表，salaries简况如下:

请你查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t，以上例子输出如下:

输入：
drop table if exists  `salaries` ; 
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 salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
复制
输出：
10001|17

题解：

MySQL执行顺序：开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

where子句中不能使用聚合函数

SELECT emp_no, COUNT(emp_no)AS t 
FROM salaries
GROUP BY emp_no
HAVING t > 15;

MySQL练习