总起:常用高级sql回忆总结。(写了一年的前端,得练练手额)
join:
MySQL 数据库不支持 SELECT … INTO 语句,但支持 INSERT INTO … SELECT 。
1,查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL comment '员工编号',
`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`));
mysql:
指定按hire_date降序desc,并取第一行。 (默认升序)
select * from employees order by hire_date desc limit 0,1
考虑最晚入职可能有很多:
select * from employees where hire_date =(select max(hire_date) from employees)
2,查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
从第二个开始取1个:
select * from employees order by hire_date desc limit 2,1
3,查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no(请注意输出结果,dept_no列是最后一列)
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL comment '部门编号',
`emp_no` int(11) NOT NULL comment '员工编号',
`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 comment '员工编号',
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
条件关联:
select s.*,d.dept_no from salaries s,dept_manager d
where d.emp_no = s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
jion关联:
select s.*,d.dept_no from salaries s left join dept_manager d
where d.emp_no = s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
查找所有已经分配部门的员工的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,d.dept_no
from employees e,dept_emp d
where e.emp_no = d.emp_no
join关联:(两个要都有,不能用left right join等。)
select e.last_name,e.first_name,d.dept_no from
employees e inner join dept_emp d on e.emp_no=d.emp_no
查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序):
select e.last_name,e.first_name,d.dept_no from employees e left join dept_emp d
on e.emp_no = d.emp_no
查找所有员工入职时候的薪水情况,给出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,找到hire_date时给的工资;2,找到最小时间给的工资。
1,找到hire_date时给的工资:
select s.emp_no,s.salary from employees e left join salaries s
on e.emp_no = s.emp_no
where e.hire_date = s.from_date
order by s.emp_no desc
2,找到最小时间给的工资:(不需要用到employees表,典型一对多处理)
一对多的分组emp_no有多个salaries。根据emp_no分组,取最小值,再根据emp_no排序。
select emp_no,salary from salaries
group by emp_no having min(from_date)
order by emp_no desc
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t。
select distinct emp_no, count(emp_no) as t from salaries
group by emp_no having count(emp_no) > 15
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示.
select distinct salary from salaries
where to_date='9999-01-01'
order by salary desc
获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
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(<