1.
select* from employees order by hire_date desc limit 1; 笔记：
limit 0,1;
使用limit关键字 从第0条记录 向后读取一个，也就是第一条记录
2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量
使用子查询
select* from employees where hire_date=(select max(hire_date) from employees);
查找入职员工时间排名倒数第三的员工所有信息
select * from employees where hire_date=(
select distinct hire_date
from employees
order by hire_date desc
limit 1 offset 2
);
可能有相同时间入职的员工，所以下面这种只考虑到了一个员工
select * from employees order by hire_date desc limit 1,2;
不行
SQL语句：
select x.emp_no,x.salary,x.from_date,x.to_date,y.dept_no from salaries as x,dept_manager as y where x.emp_no=y.emp_no order by x.emp_no asc; 注意：属性名那里要用表明区分一下
SQL语句：
select x.last_name,x.first_name,y.dept_no from employees x,dept_emp y where x.emp_no=y.emp_no and dept_no is not null;
左外连接：
select x.last_name, x.first_name, y.dept_no from employees x left outer join dept_emp y on x.emp_no=y.emp_no;
SQL: select emp_no,count(emp_no) as t from salaries group by emp_no having t>15;
SQL:
select x.emp_no from employees x where x.emp_no not in ( select emp_no from dept_manager )
SQL:
select x.emp_no,y.emp_no as manager from dept_emp x,dept_manager y where x.dept_no=y.dept_no and x.emp_no !=y.emp_no;
把两个表连接起来，把各个部门的最高薪资查询出来，再把符合条件这个最高薪资的员工编号求出来
select x.dept_no,x.emp_no,y.salary as maxSalary from dept_emp x,salaries y where x.emp_no=y.emp_no and (x.dept_no,y.salary) in ( select h.dept_no, max(z.salary) from dept_emp h, salaries z where h.emp_no = z.emp_no group by h.dept_no ) order by x.dept_no asc
判断数字是否为奇数，用mod(字段，2)=1,为奇数，=0为偶数
select emp_no,birth_date,first_name,last_name,gender,hire_date from employees where mod(emp_no,2)=1 and last_name not like "Mary" order by hire_date desc;
select title,avg(s.salary) from titles x,salaries s where x.emp_no=s.emp_no group by x.title order by avg(s.salary) asc;
SQL: select emp_no,salary from salaries where salary=(select salary from salaries order by salary desc limit 1 offset 1 ) order by emp_no
先把第二大求出来
select max(salary) from salaries where salary !=(select max(salary) from salaries)再求该第二多薪资的全部信息查询出来
SQL: select x.emp_no,y.salary,x.last_name,x.first_name from employees x,salaries y where x.emp_no=y.emp_no and y.salary=( select max(salary) from salaries where salary !=(select max(salary) from salaries) )
先左连接员工和雇佣关系模式，再左连接部门关系模式
SQL: select last_name,first_name,dept_name from ( select last_name,first_name,dept_no from employees left join dept_emp on employees.emp_no=dept_emp.emp_no ) as a left join departments on a.dept_no=departments.dept_no
SQL: select y.dept_no ,y.dept_name,count(x.salary) as sum from dept_emp z,salaries x,departments y where x.emp_no=z.emp_no and y.dept_no=z.dept_no group by z.dept_no order by z.dept_no
SQL: select concat(last_name," ",first_name) Name from employees 批量插入数据： insert into actor(actor_id,first_name,last_name,last_update) values(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"), (2,"NICK","WAHLBERG","2006-02-15 12:34:33"); 更新列表两列内容： update titles_test set to_date=null ,from_date="2001-01-01" where to_date="9999-01-01" 利用replace更新数据 10005替换10001 update titles_test set id=replace(id,5,5),emp_no=replace(emp_no,10001,10005) where id=5 and emp_no=10001 标签：salary,name,记录,no,dept,emp,MySQL,select,刷题 From： https://www.cnblogs.com/moxiaozhi/p/17436005.html