常用的SQL语句

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 * from employees where hire_date = (select max(s.hire_date) from employees s);

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

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

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

查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

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

一张成绩表，查每个班成绩最好的那个：

 

select a.class_id,a.score,a.stu_id from exam a inner join (select max(score) as score, class_id as class_id from exam group by class_id) b
on a.class_id = b.class_id and a.score =b.score ;

 查询每个班级前两名：（就是外面先排序，对于每一个外面排序的数，再去查找原表查询，比它大的数的个数，如果小于2，表示小于它的就一个数或者0个数就是前2）

SELECT a.class_id,a.score FROM exam a WHERE (SELECT COUNT(*) FROM exam WHERE a.class_id=class_id AND a.score<score)<2
ORDER BY a.class_id, a.score DESC;

 https://blog.csdn.net/sandyagor/article/details/79664086

多行变一列的需求:

https://blog.csdn.net/rainyspring4540/article/details/50231435