我们看一下数据处理相关的题目,
55. 更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job
1). 搭建骨架
update employees
set salary = (
), job_id = (
) where employee_id = 108;
2). 所在部门中的最高工资
select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)
3). 公司中平均工资最低的 job
select job_id
from employees
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id
)
4). 填充
update employees e set salary = (
select max(salary)
from employees
where department_id = e.department_id
), job_id = (
select job_id
from employees
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id
)
) where employee_id = 108;
更改108号员工的信息,使其工资变为所在部门的最高工资,job变成公司中平均工资中最低的job,
有这种好事,给他工资往上调,部门的往下调,这里面结合我们讲的子查询了,所以整合在一起了我们该怎么
来做这个题目,更改他的信息,肯定是update了,update employees这个表,使其工资,set salary,等于什么什么,
不光改这个salary,还有一个job_id等于什么什么,是这个意思,然后这里相当于是一个子查询,相当于我们这个题目的
框架应该是这么来写的,我先给他注掉,我们先看看括号里面的查询应该怎么去写
update employees
set salar = (),job_id = ()
工资变成什么啊,变成其所在部门的最高工资,他部门的最高工资,你得看看108号员工,在哪个部门,
然后这个部门的的最高工资,再说一遍,我得知道108号员工在哪个部门,然后这个部门的你再去找最高工资,
所以说你又得分两步了,我需要select一下这个salary,from employees,这个salary应该是max(salary),
是哪个部门的,这个部门又作为一个变量,我们得使用一个子查询,你得填入108号员工的部门,所以叫
department_id,from employees,where,employee_id等于108,相当于108号员工的部门,然后这个部门的最高的
工资,这个工资就是让这个人有,我们还少一个,where employee_id = 108,这个人的salary等于它,这是查询到了
salary,然后我们还得查job_id
select max(salary) from employees where department_id = (select department_id from employees where
employee_id = 108)
我们先在这里给他注释掉,然后给他合在一起,job_id变成公司中平均工资中最低的job,变成公司中平均工资最低的job,
这个我们是用job_id进行一个分组,然后看看谁的平均工资是最低的,是这个意思吧,然后把平均工资最低的job_id给找
出来,这里又涉及到了多层子查询了,一点点来,我们看看用job_id进行分组以后,各个公司的平均工资,select avg(salary)
from employees,group by,涉及到分组,我用job_id进行分,按照他分组以后,我看看各个公司的平均工资,平均工资里面
我找最低的,这里我找到平均工资最低的,然后我还要看谁的job_id的平均工资正好等于这个,那就select job_id from employees,
你用到avg了,那就你还得用到having,having一起拿你先group 一下,group by job_id,having avg(salary),平均工资等于我们
刚才算出来最低的,大家看一下是不是这个意思,我先找到按照job_id进行分组以后的,平均工资中的最低的,然后看看谁的
最低工资是这个最低的,找到这个job_id,然后这个就是我们要调整的这个值
select job_id from employees group by job_id having avg(salary) = (select min(avg(salary))
from employees group by job_id)
找到这个job_id,这个就是我们要调整的这个值,这个实际上我们已经搞定了,只需要把它打开,然后salary和job_id
分别给他填充上,大家看,这个题目是不是这样搞定了,108号员工
update employees set salary = (select max(salary) from employees where department_id =
(select department_id from employees where employee_id = 108)),
job_id = (select job_id from employees group by job_id having avg(salary) =
(select min(avg(salary)) from employees group by job_id)) where employee_id = 108
这个题目就这样来写,大家你务必别忘了最后的语句,删除也好,修改也好,你如果最后忘了这个
过滤条件了,那么进行修改的是所有人的,删除的话也是删除所有人的,大家要格外的注意,然后相应的
要用到avg了,就要用having,你看这个人挺幸福的,当你要去公司工作的时候,一般你工作的价值有多高,
公司给你开相应的工资,现在把工资变成最高的了,job变成平均工资最低的,相当于干的活特别轻,活不累,
工资还是最高的,这个题目完了以后我们看
56. 删除 108 号员工所在部门中工资最低的那个员工.
1). 查询 108 员工所在的部门 id
select department_id
from employees
where employee_id = 108;
2). 查询 1) 部门中的最低工资:
select min(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)
3). 删除 1) 部门中工资为 2) 的员工信息:
delete from employees e
where department_id = (
select department_id
from employees e
where employee_id = 108
) and salary = (
select min(salary)
from employees
where department_id = e.department_id
)
大家下来再把这个题目做一做,删除108号员工所在部门中工资最低的那个员工,主要是我要把这个
员工给找到,找到以后我要把它删了,删除的话我们就从这个大框上来看,delete from employees,
给他删,删除他然后呢,是不是得where,平均工资最低的那个员工,把那个员工给他找着,where怎么写,
平均工资最低的,那就是工资等于什么什么的,加一个括号,工资等于这个,但工资等于这个的可能是多个,
人家要求是删除这个部门的,salary等于他,and department_id等于这个,这儿应该是108号员工所在部门的,
而且是这个部门工资最低的,那你就看工资最低的是多少,那我们下边需要做的
delete from employees
where salary = () and department_id = ()
我要看一下108号员工所在部门的最低工资是多少,那我们就来写,从外层到内层,或者从内层到外层,
都可以,那假设从外到内的话,他的salary,他的salary是最低的,min(salary),from employees,得
这样写,where department_id等于什么什么,哪个部门的最低工资,这个部门就是一个子查询了,
employee_id等于108,这个人他的部门,然后这个人的最低工资,然后把这个最低工资放到这,让你这个
工资等于最低的,并且这个department_id等于这个,然后我们看department_id怎么写
select min(salary) from employees where department_id = (select department_id from employees
where employee_id = 108)
就是这个部门,就是他,这个相当于我们就能写了,这个部门的,然后等于这个工资的这个人,
delete from employees where salary = (select min(salary) from employees where department_id=
(select department_id from employees where employee_id = 108)) and department_id = (select
department_id from employee_id = 108)
这就删除了,然后我再commit一下,那就给提交了
commit;
相当于就给你写死了,大家可以看一下实际这个题我们还可以给他优化一下,这就涉及到SQL优化了,
这个题目当中我说啊,存在一些冗余,什么叫冗余了,你看salary和department_id,本来就是有关系的,
这个部门的这个工资,然后这个部门你又在这进行重新查询了一遍,这里就是有一些冗余,因为我们看到刚才
是复制过来的,那我怎么给他优化一下,你看salary找的时候就是找的这个部门,就是108号员工的这个部门,
所以说我们这个查询,有点多余,怎么给他优化,这个我们写个e,你不写也没事,这里where department_id,这个
id等于谁,就是你查询的这个效果,所以就等于e.department_id,就是他这个id
delete from employees where salary = (select min(salary) from employees where department_id =
e.department_id) and department_id = (select department_id from employees where employee_id =108)
这个现在还有数据啊,这个为什么能够执行成功,是不是因为你刚才删的人是最低工资,
现在又有一个次低的称为最低的了,那这个题目现在我们是一个优化的形式,这涉及到了一个内容叫,
sql优化,跟我们的JAVA程序一样,你要实现同样的功能,我们也会有一些算法,取最优的,时间复杂度和
空间复杂度,他们都不一样,我们找到一个最优的,没有最好,只有更好,SQL也是存在一定优化的
1. 运行以下脚本创建表my_employees
Create table my_employee ( id number(3),
first_name varchar2(10),
Last_name varchar2(10),
User_id varchar2(10),
Salary number(5));
第一个创建一下脚本,以下脚本创建表,这个我们已经写好了,如何去创建一个表,我们就直接在这里写吧,
创建那个表大家都会吧,创建好以后显示这个表的结构,这个不难
2. 显示表my_employees的结构
DESC my_employees;
INSERT INTO my_employee
VALUES(1,’patel’,’Palph’,’Rpatel’895);
向表中插入以下数据,大家回忆一下插入怎么写,insert into,如果你不是依托已有的表的话,
就是自己干插入,那你就一条一条的写了,这个一条一条的写,写5个,你需要注意的是,你看我们这个表
里的元素的顺序,id,first_name,last_name,user_id,salary,就是跟这个顺序是一样的,所以我顺序给他
insert就可以了,my_employee,然后values,一个小括号,first_name,后面几个都是varchar型的,所以你得给他
加一堆单引号,这个你得用英文格式下的,最后一个salary是一个number类型的,895,那么这样我就像这个表中
插入一条数据
insert into my_employees
values(1,'patel','Palph','Rpatel',895);
4. 提交
COMMIT;
5. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name = ‘drelxer’
WHERE id = 3;
修改操作我们使用的叫update,my_employees,然后where,不是where,是set,修改,last_name等于,
改成他,set last_name,然后呢,然后id等于3
update my_employees
set last_name = 'drelxer'
where id = 3;
6. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary = 1000
WHERE salary< 900
将所有工资小于900的工资改成1000,这是一个修改,这个修改的不止一条数据,update my_employees,
set,低于的修改成1000,set salary等于1000,然后呢,where,本身你这个salary小于900的,把小于900的
改成1000
update my_employees
set salary = 1000
where salary < 900;
7. 检查所作的修正
SELECT * FROM my_employees
WHERE salary < 900
select * from my_employees,看看有没有低于900的了,或者你再加上一个where条件也行,
where salary小于900,你看看这个输出结果是否为空,如果为空的话就修改成功,修改成功以后
我还得进行一个提交
8. 提交
COMMIT;
结束我当前这个事务,或者另一方面来讲,你不提交的话你这个事务随时都可能被回滚回去的,我一提交的
话就写死了
9. 删除所有数据
DELETE FROM my_employees;
删除delete,from my_employees,这就删除所有的了
10. 检查所作的修正
SELECT * FROM my_employees;
看看这个结果现在是否为空的,
11. 回滚
ROLLBACK;
回滚rollback,因为你提交了,那就回滚到这个位置,就是删除的数据又回滚回来了,回来以后这个叫清空表,
12. 清空表my_employees
TRUNCATE TABLE my_employees
叫truncate,truncate table,有table这个词,大家要注意,清空my_employees,把这个表清空,这个清空以后,
你要再给他rollback的话,不好使,因为这个操作是属于DDL,他们的这个操作是会自动提交的,所以你再回滚也没有用