SQL 日记

最新推荐文章于 2023-04-27 19:20:44 发布

AdaLeery

最新推荐文章于 2023-04-27 19:20:44 发布

阅读量173

点赞数

分类专栏： DataBase

本文链接：https://blog.csdn.net/qq_37312720/article/details/88430083

版权

DataBase 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

1查找最晚入职的同事

select emp_no,max(hire_date),....cols from emplyees

2查找倒数第三个入职的同事的所有信息

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

原理：

.需要从一个特定的偏移开始提取记录。下面是一个实例，从第三位开始提取 3 个记录：

SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

3求部门领导的薪水在时间to_date='9999-01-01'

SELECT s.*, d.dept_no FROM salaries s , dept_manager d WHERE s.to_date='9999-01-01' AND d.to_date='9999-01-01' AND s.emp_no = d.emp_no;

4查找所有已经分配部门的员工的last_name和first_name(这个题目真特么。。。居然还和顺序有关，顺序必须对)

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

select e.last_name,e.first_name,d.dept_no
from employees e, dept_emp d
where e.emp_no==d.emp_no(所以说一直不过是顺序的问题，OMG)；

5查找所有员工的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

6查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序

select s.emp_no,s.salary
from employees e,salaries s
where e.emp_no==s.emp_no and e.hire_date==s.from_date
order by s.emp_no desc

7查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

select emp_no,count(salary) as t from salaries group by emp_no having t>15

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

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

9获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

select d.dept_no,d.emp_no,s.salary from dept_manager d,salaries s
where d.emp_no==s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'

10获取所有非manager的员工emp_no

select emp_no from employees
where emp_no not in (select emp_no from dept_manager)

11获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。

select d.emp_no,m.emp_no as manager_no from dept_emp d,dept_manager m
where d.dept_no==m.dept_no
and d.emp_no!=m.emp_no and d.to_date='9999-01-01' and m.to_date='9999-01-01'

12获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select d.dept_no,d.emp_no,max(s.salary) as salary from dept_emp d,salaries s
where d.emp=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by d.dept_no //我也不知道为啥上面的不通过，我找了别人的改成我的就通过了，我写的放上去一样的也不通过。

13从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select title,count(title) as t from titles group by title having t>=2

14从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select title,count(title) as t from (select distinct(emp_no),title from titles) group by title having t>=2

15查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
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 emp_no%2==1 and last_name!='Mary' order by hire_date desc

16统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。
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`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

select ti.title,avg(sa.salary) as avg
from titles ti,salaries sa
where sa.emp_no=ti.emp_no and sa.to_date='9999-01-01' and ti.to_date='9999-01-01'
group by ti.title

17获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary
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`));

select emp_no,salary
from salaries order by salary desc limit 1 offset 1

limit取几个 offset偏移量

顺序 select from where group by having order by limit offset

18查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 m.last_name,m.first_name,de.dept_name
from (select e.last_name,e.first_name,d.dept_no
from employees e,dept_emp d
where e.emp_no=d.emp_no) as m left join departments de
on m.dept_no=de.dept_no //这个我不懂为啥不对。。。。。

select e.last_name,e.first_name,de.dept_name from

employees e left join dept_emp d on d.emp_no=e.emp_no

left join departments de on de.dept_no=d.dept_no//这个就是表连接，以左边为准

19查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
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`));

select max(salary)-min(salary) as growth from salaries where emp_no=10001

20查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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`));

select old.emp_no,newsalary -oldsalary as growth from

//你能相信我到底怎么没出来么，我一直少写了个from 因为是先写的子句，OMG
(select e.emp_no,s.salary as oldsalary
from salaries s,employees e
where e.emp_no=s.emp_no and e.hire_date=s.from_date) as old ,
(select e.emp_no,s.salary as newsalary
from salaries s,employees e
where e.emp_no=s.emp_no and s.to_date='9999-01-01') as new
where old.emp_no=new.emp_no order by growth asc

21统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum//有错误，没想清楚
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 `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`));

select de.dept_no,de.dept_name,count(me.salary) as sum
from (select * from salaries s
inner join dept_emp d on s.emp_no=d.emp_no ) as me,
departments de
where me.dept_no=de.dept_no group by de.dept_no

SELECT
   de.dept_no,
   dp.dept_name,
   COUNT( s.salary ) AS sum
FROM
   ( dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no )
   INNER JOIN departments AS dp ON de.dept_no = dp.dept_no
GROUP BY
   de.dept_no;

22对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照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`));

23获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01'
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 `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 `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`));

select d.dept_no,e.emp_no,s.salary from dept_emp d,

(select emp_no from employees where emp_no not in (select emp_no from dept_manager)) as e,

salaries s where d.emp_no=e.emp_no and d.emp_no=s.emp_no

and d.to_date='9999-01-01' and s.to_date='9999-01-01'

24获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

select emp.emp_no,manager.manager_no,emp.emp_salary,manager.manager_salary from
(select de.dept_no,de.emp_no,s.salary as emp_salary from dept_emp de,salaries s where
de.emp_no=s.emp_no and de.emp_no not in (select emp_no from dept_manager) and
s.to_date='9999-01-01' and de.to_date='9999-01-01' )as emp,
(select d.dept_no,d.emp_no as manager_no,s.salary as manager_salary
from dept_manager d,salaries s where s.emp_no=d.emp_no
and s.to_date='9999-01-01' and d.to_date='9999-01-01') as manager
where manager.dept_no=emp.dept_no and manager.manager_salary<emp.emp_salary

//我感觉自己的想法乱七八糟的。。。