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

测试用例:
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');

查询最晚入职员工的信息

1、利用相关子查询，max函数
select * from employees where hire_date=(select max(hire_date) from employees);

2、排序，取第一个
select * from employees order by hire_date desc limit 1

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

select * from employees order by hire_date desc limit 2,1

若考虑同一时间有多个员工入职，则需要这样查询
select * from employees where hire_date=(select  hire_date from employees order by hire_date desc limit 2,1)

查找所有已经分配部门的员工的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`));

/*查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

*/

select employees.last_name,employees.first_name,dept_emp.dept_no 
from dept_emp inner join employees 
on dept_emp.emp_no=employees.emp_no
注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。
注意
on与where有什么区别，两个表连接时用on，在使用left  join时，on和where条件的区别如下：
1、  on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left  join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

查找薪水涨幅超过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`));

//group by ……having 的使用
select emp_no,count(emp_no) as t  from salaries group by emp_no having t>15

找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

//distinct 
select distinct salary from salaries where to_date='9999-01-01' order by salary desc

//group by
select salary from salaries where to_date='9999-01-01' group by salary order by salary DESC