oracle从入门到精通复习笔记

为方便大家跟着我的笔记练习，为此提供数据库表文件给大家下载：点我下载

描述一个表用 desc employees
过滤重复的部门 select distinct department_id from employees
别名的三种方式： 1、空格 2、加as 3、" "（多个单词组成的别名必须加空格，要么用下划线分开）
条件匹配日期的： where to_char(date,'yyyy-mm-dd')='1997-06-07'
默认格式： where date = '7-6月-1997'
like: where name like '%\_%' escape '\' (%:0个或者多个字符，_表示任意一个字符，escape表示转义关键字)
order by salary asc(升序) desc（降序）
多层排序： order by salary asc,name asc (在工资相同的情况下按照名字升序排列)
删除表中字段重复的记录：
delete from job_grades j1
where rowid <> (select min(rowid) from job_grades j2 where j1.grade_level = j2.grade_level);
如果删除表中自然顺序的第15行，下面语句可实现。
（rowid是数据库的一个伪列，建立表的时候数据库会自动为每个表建立ROWID列
用来唯一标识一行记录。rowid是存储每条记录的实际物理地址，对记录的访问是基于ROWID。）
delete from tab where rowid=(
select ii from (select ROWNUM nn,ROWID ii from tab WHERE ROWNUM<=15) WHERE nn=15);

等值连接：
select employee_id,e.department_id,department_name,city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
或者
select employee_id,e.department_id,department_name,city
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
等值连接另外方式：
select last_name,department_id,department_name
from employees join departments
--using (department_id) （前提是两表的列名以及列的数据类型要一样）

非等值连接
select employee_id,last_name,salary,grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal
左外连接（哪边空就把“+”号放在哪个表上）
select e.employee_id,e.last_name,d.department_name
from employees e,departments d
where e.department_id = d.department_id(+)
左外（右外、满）连接
select employee_id,e.department_id,department_name
from employees e
left outer
--right outer
--full
join departments d on e.department_id = d.department_id
自连接
--查询公司中员工‘Chen’的manager的信息。
select e1.last_name,e2.last_name,e2.email,e2.salary
from employees e1,employees e2
where e1.manager_id = e2.employee_id and lower(e1.last_name) = 'chen'
组函数：（注意：只要不是组函数中的列都应该放在group by中，group by中的顺序不做限定，且其中有的列select中可以没有）
select department_id,job_id,avg(salary)
from employees
group by department_id,job_id
order by department_id asc
有组函数的过滤条件用having而不能用where:
select department_id,avg(salary)
from employees
having avg(salary)>6000
group by department_id
order by department_id asc
查询全公司奖金基数的平均值（注意：avg、count默认是不把值为null的计入，此时要想计入的话必须使用nvl（）函数）：
select avg(nvl(commission_pct,0))
from employees
查询公司在1995-1998年之间，每年雇佣的人数：
select count(*) "total",
count(decode(to_char(hire_date,'YYYY'),'1995',1,null)) "1995",
count(decode(to_char(hire_date,'YYYY'),'1996',1,null)) "1996",
count(decode(to_char(hire_date,'YYYY'),'1997',1,null)) "1997",
count(decode(to_char(hire_date,'YYYY'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'YYYY') in ('1995','1996','1997','1998')
单行子查询：
谁的工资比‘Abel’的高
select last_name,salary
from employees
where salary > (select salary from employees where last_name = 'Abel')
查询员工为Chen的manager的信息
方法一：（使用自连接）
select e2.last_name,e2.manager_id,e2.email,e2.salary
from employees e1,employees e2
where e1.manager_id = e2.employee_id and e1.last_name = 'Chen'
方法二：
select employee_id,last_name,email,salary
from employees
where employee_id = (
select manager_id
from employees
where last_name = 'Chen')
返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资
select employee_id,last_name,email,salary
from employees
where employee_id = (
select manager_id
from employees
where last_name = 'Chen')
返回公司工资最少的员工的last_name,job_id,salary
select last_name,job_id,salary
from employees
where salary = (select min(salary) from employees)
查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id = 50)
多行子查询（in,any,all）:
返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <>'IT_PROG' and salary < any (select salary from employees where job_id = 'IT_PROG')
order by last_name asc
返回其它部门中比job_id为‘IT_PROG’部门所有（任意）工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <>'IT_PROG' and salary < any (select salary from employees where job_id = 'IT_PROG')
order by last_name asc

查询平均工资最低的部门信息
select *
from departments
where department_id = (select department_id
from employees
having avg(salary) = (select min(avg(salary))from employees
group by department_id)
group by department_id)
查询平均工资最低的部门信息和该部门的平均工资
select d.* ,(select avg(salary) from employees where department_id = d.department_id)
from departments d
where department_id = (select department_id
from employees
having avg(salary) = (select min(avg(salary))from employees
group by department_id)
group by department_id)
查询平均工资最高的job信息
select *
from jobs
where job_id in (select job_id
from employees
having avg(salary) = (select max(avg(salary))from employees group by job_id)
group by job_id)
查询平均工资高于公司的部门有哪些
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>(select avg(salary) from employees)
查询公司中所有manager的详细信息
select *
from employees
where employee_id in (select manager_id from employees)
各个部门中，最高工资中最低的那个部门的 最低工资是多少
select min(salary)
from employees
where department_id = (select department_id
from employees
having max(salary)=(select min(max(salary))
from employees
group by department_id)
group by department_id
)
查询平均工资最高的的部门的manager的详细信息：last_name,department_id,email,salary
select last_name,department_id,email,salary
from employees
where employee_id in (select manager_id
from employees
where department_id = (select department_id
from employees
having avg(salary) = (select max(avg(salary))
from employees
group by department_id)
group by department_id))

查询1999年来公司的员工中的最高工资的那个员工的信息
select *
from employees
where salary = (select max(salary)
from employees
where employee_id in (select employee_id
from employees
where to_char(hire_date,'YYYY') = '1999'))
and to_char(hire_date,'YYYY') = '1999'

查看用户定义的表：
select table_name from user_tables;
查询数据库有哪些对象（表、视图、序列、同义词）
select * from user_catalog;
查看用户定义的各种数据库对象
select distinct object_type from user_objects;

创建表的第一种方式：（白手起家）
create table emp1(
id number(10),
name varchar2(20),
salary number(10,2),
hire_date date
)
第二种方式：借助现有的表来创建（数据也会在原有的表中搬过来）
create table emp2
as select employee_id id,last_name name,hire_date,salary
from employees
(where department_id = 80)
(where department_id = 800):不想要任何数据就给它写个没有的部门或者：where 1=2

修改表：
给表追加多一列：
alter table emp1
add (email varchar2(20))
修改表中的列属性：
1、alter table emp1
    modify (id number(15))
2、alter table emp1
　 modify (salary number(20,2) default 2000)
删除一个列：
alter table emp1
drop column email
重命名列：
alter table emp1
rename column salary to sal
删除表：
drop table emp5
清空表：
truncate table emp3;（不可以回滚）
delete from emp2;（可以回滚）
重命名表：
rename emp2 to employees2;

表数据的增、删、改：
向表中增加数据
1、insert into emp1 ‘sysdate’或者
    values (1002,'BB',to_date('1998-08-08','yyyy-mm-dd'),20000)
2、insert into emp1
    values (1002,'BB',to_date('1998-08-08','yyyy-mm-dd'),null)

3、insert into emp1(employee_id,last_name,hire_date) 注意：这里是只给部分列赋值，有非空约束的必须赋值
     values (1004,'DD',to_date('1990-08-08','yyyy-mm-dd'))
4、注意：只想赋值一部分列的话，其它必须是允许放空值的列，这里默认salary是NULL（即有非空约束的必须赋值）
insert into emp1(employee_id,last_name,hire_date)
values (1005,'EE',to_date('1996-08-08','yyyy-mm-dd'))
5、弹窗式的插入数据
insert into emp1(employee_id,last_name,hire_date,salary)
values (&id,'&last_name','&hire_date',&salary)
基于现有表的记录插入数据
insert into emp1(employee_id,last_name,hire_date,salary)
select employee_id,last_name,hire_date,salary
from employees
where department_id = 80

更新数据：
update emp1
set salary = 22000
where employee_id = 179
更新114员工的工作和工资使其与206号员工相同
1、select employee_id,job_id,salary
from employees1
where employee_id in (114,205)
2、update employees1
set job_id = (
select job_id from employees1 where employee_id = 205
),salary = (
select salary from employees1 where employee_id = 205
)
where employee_id = 114

调整与employee_id为200的员工的job_id相同的
员工的department_id为employee_id为100的员工的department_id
update employees1
set department_id = (select department_id from employees1 where employee_id = 100)
where job_id = (select job_id from employees1 where employee_id = 200)
容易出现的数据完整性的错误如：
update employees
set department_id = 55
where department_id = 100; 问题出现在表中55号部门本来就不存在
从employees表中删除departments部门名称中含有Public字符的部门id
delete from employees1
where department_id = (select department_id from departments where department_name like '%Public%')

增：
insert into ...
values(...)

insert into ...
select...from...where...
改：
update ...
set ...
where ...
删：
delete from ...
where ...

事务：
commit;
savepoint A;
rollback to savepoint A;
当用户操作表的时候，还没有commit之前，其它用户是不能够对当前的表进行操作的
更改108号员工的信息：使其工资变为所在部门中的最高工资，job变为公司中平均工资最低的 job
update employees
set salary = (select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108)
group by department_id),
job_id = (select job_id
from employees
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id )
group by job_id)
where employee_id = 108
删除108号员工所在部门中工资最低的那个员工
delete from employees
where employee_id = (
select employee_id
from employees
where department_id = (select department_id
from employees
where employee_id = 108)
and salary = (select min(salary)
from employees
where department_id =(select department_id
from employees
where employee_id = 108)
)
)
可以优化成：
delete from employees e
where department_id = (select department_id
from employees
where employee_id = 108)
and salary = (select min(salary)
from employees
where department_id = e.department_id
)
使用约束not null和unique创建表：其中在有unique约束中给它赋值多个null，null之间是不冲突的
create table emp3(
--列级约束：
id number(10) constraint emp3_id_uk unique,
name varchar2(20) constraint emp3_name_nn not null,
email varchar2(20),
salary number(10),
--表级约束：
constraint emp3_email_uk unique(email)
)
主键约束：能够唯一的确定一条记录，同样也分表级约束和列级约束，primary key不仅是not null而且unique
create table emp4(
id number(10) constraint emp4_id_pk primary key,
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10),
constraint emp4_email_uk unique(email)
)
或者
create table emp4(
id number(10),
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10),
constraint emp4_email_uk unique(email)，
constraint emp4_id_pk primary key（id）
)
外键约束：（注意：在emp6中插入数据的时候，不能够插入departments表中department_id没有的数据记录。另外，外键引用的列起码要有一个唯一的约束）
create table emp6(
id number(10),
name varchar2(20) constraint emp6_name_nn not null,
email varchar2(20),
salary number(10),
department_id number(10),
constraint emp6_email_uk unique (email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
)
向表中插入departments表中存在的department_id(主键)数据
insert into emp6
values(1002,'AA',null,10000,20)

on delete set null:(级联置空：子表中相应的列置空)
on delete cascade:(级联删除：当父表中的列被删除时，子表中相对应的列也被删除)
create table emp7(
id number(10),
name varchar2(20) constraint emp7_name_nn not null,
email varchar2(20),
salary number(10),
department_id number(10),
constraint emp7_email_uk unique (email),
constraint emp7_id_pk primary key(id),
constraint emp7_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)
check约束：比如约束工资的范围
create table emp8(
id number(10),
name varchar2(20) constraint emp8_name_nn not null,
email varchar2(20),
salary number(10) constraint emp8_salary check(salary>1500 and salary<30000),
department_id number(10),
constraint emp8_email_uk unique (email),
constraint emp8_id_pk primary key(id),
constraint emp8_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)

修改约束：
添加not null约束
alter table emp5
modify (salary number(10,2) not null)
删除约束：
alter table emp5
drop constraint emp5_name_nn
添加unique约束
alter table emp5
add constraint emp5_name_uk unique(name)
无效化约束：
alter table emp3
disable constraint emp3_email_uk
激活约束：
alter table emp3
enable constraint emp3_email_uk
查询约束：（注意：其中条件里的表名要大写）
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name = 'EMPLOYEES'
查询定义有约束的列有哪些：
select constraint_name,column_name
from user_cons_columns
where table_name = 'EMPLOYEES'

视图：
它实际上是一个虚表，它是依赖于基表的，当视图中的数据更改时，基表中的相应数据也被更改
为什么要使用视图？
答：1、可以控制数据访问 2、简化查询 3、避免重复访问相同的数据

创建视图：
create view empview
as
select employee_id,last_name,salary
from employees
where department_id = 80
基于多张表来创建视图：
create view empview3
as
select employee_id id,last_name name,salary,e.department_name
from employees e,departments d
where e.department_id = d.department_id
修改视图：create or replace
create or replace view empview2
as
select employee_id id,last_name name,department_name
from employees e,departments d
where e.department_id = d.department_id
屏蔽DML操作：with read only (其他用户只能查看，不能增、删、改)
create or replace view empview2
as
select employee_id id,last_name name,department_name
from employees e,departments d
where e.department_id = d.department_id
with read only

简单视图和复杂视图的区别：简单视图没有分组函数，在复杂视图中若使用了组函数创建的，则对它不能使用DML（增、删、改）的操作，因为有些列在基表中原本是不存在的。
创建一个复杂视图：（注意：基表中不存在的列，创建视图时要给它个别名，如下面的平均工资。）
create or replace view empview3
as
select department_name dept_name,avg(salary) avg_sal
from employees e ,departments d
where e.department_id = d.department_id
group by department_name

rownum是一个伪列，跟id有点相关，且有它自己的一个排序。
比如，你想找到表中工资最高的前10位的员工，以下用rownum作为条件是不行的，因为它有自己默认的排列顺序
select rownum,employee_id,last_name,salary
from employees
where rownum <= 10
order by salary desc
要真想用rownum达到查询最高工资的前10位则要如下这样：（注意：rownum只能使用<或<=，而用=,>,>=都不会返回任何数据）
select rownum,employee_id,last_name,salary
from (select employee_id,last_name,salary
from employees
order by salary desc)
where rownum <=10
那么要查询最高工资排列40-50名的数据要怎么办？如下：（此时最外层的rn已经不是伪列了）
select rn,employee_id,last_name,salary
from (select rownum rn,employee_id,last_name,salary
from (select employee_id,last_name,salary
from employees
order by salary desc))
where rn <40 and rn<=50

序列：主要用来提供主键值
创建序列：
create sequence empseq
increment by 10
start with 10
maxvalue 100
cycle
nocache
序列在数据插入表时在主键位置的作用：
先：
create table emp01
as
select employee_id,last_name,salary
from employees
where 1=2
然后：
insert into emp01
values(empseq.nextval,'BB',3300)
修改序列：（能修改增量、最大值、最小值、是否循环以及是否装入内存，如要更改初始值则要通过删除序列重新创建序列，因为改后可能会与之前的数据发生冲突，因为序列是唯一的）
alter sequence empseq
increment by 1
nocycle
序列一以下情况中会出现裂缝：
1、回滚
2、系统出现异常
3、多个表同时使用同一个序列
查询序列：（如果指定了nocache选项，则last_number返回的是序列中下一个有效值）
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences
删除序列
drop sequence empseq
索引的作用，能够加速 oracle服务器的查询速度，主键和唯一约束中系统默认为它所约束的列创建所引，也可以为非唯一的列手动创建所引，创建好之后系统会自动调用索引，不用你手动编码调用
创建索引：
create index emp01_id_ix
on emp01(employee_id)
删除索引：
drop index emp01_id_ix
什么时候创建索引？
1、列中数据值分布范围很广
2、列经常在where子句或者连接条件中出现
3、表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
什么时候不要创建索引？
1、表很小
2、表经常更新
3、查询的数据大于2%到4%
4、表不经常作为where子句或者连接条件中出现

同义词：
创建同义词
create synonym e for employees
删除同义词
drop synonym e

创建用户和密码：
create user atguigu01
identified atguigu01;
给用户登录数据库的权限，（create table,create sequence,create view,create procedure）
grant create session
to atguigu01 ;
创建用户表空间
alter user atguigu01 quota unlimited（或者5M）
on users
更改用户自己的密码：
alter user atguigu01
identified by atguigu;

角色：使用角色分配给用户权限会更快，角色有什么权限，用户就有什么权限
1、创建角色
create role my_role
2、为角色赋予权限
grant create session,create table,create view
to my_role
3、将角色赋予用户
grant my_role to atguigu02

对象：
1、给atguigu01分配表employees的查询、修改的权限
grant select,update
on scott.employees
to atguigu01
验证上一步的操作：
update scott.employees
set last_name = 'ABCD'
where employee_id = 206 （此时改的是scott用户中的表，atguigu只是调用它的表，并没有复制过来）

with grant option：使用户同样具有分配权限的权利
grant select
on scott.departments
to atguigu01
with grant option
public：向数据库中所有用户分配权限
grant select
on scott.locations
to public

收回对象权限：
数据字典视图 描述
ROLE_SYS_PRIVS 角色拥有的系统权限
ROLE_TAB_PRIVS 角色拥有的对象权限
USER_ROLE_PRIVS 用户拥有的角色
USER_TAB_PRIVS_MADE 用户分配的关于表对象权限
USER_TAB_PRIVS_RECD 用户拥有的关于表对象权限
USER_COL_PRIVS_MADE 用户分配的关于列的对象权限
USER_COL_PRIVS_RECD 用户拥有的关于列的对象权限
USER_SYS_PRIVS 用户拥有的系统权限

先查询本用户所拥有的关于表的权限有哪些：
select * from USER_TAB_PRIVS_RECD;
然后执行收回:
revoke select
on scott.employees
from atguigu01

总结：
语句 功能
CREATE USER 创建用户（通常由DBA完成）
GRANT 分配权限
CREATE ROLE 创建角色（通常由DBA完成）
ALTER USER 修改用户密码或者给表分配空间
REVOKE 收回权限

SET操作符（默认查询到的结果是按照第一列的顺序来排列的，要是给列起别名，默认显示的结果是按照第一张表的别名，还有查询的列的数据类型和数量要对应得上）
1、union(去重复部分)/union all:联合
2、intersect：取交集
3、minus：减去
取employees01和employees02的并集
select employee_id,department_id
from employees01
union/union all
select employee_id,department_id
from employees02
取employees01和employees02的交集
select employee_id,department_id
from employees01
intersect
select employee_id,department_id
from employees02
取employees01和employees02的差集
select employee_id,department_id
from employees01
minus
select employee_id,department_id
from employees02
若查询的列的数据类型和数量要对应不上，解决办法如下：（即给相应的列补上为空的数据类型和列的个数）
select employee_id emp_id,department_id dept_id,to_char(null)
from employees01
union
select to_number(null),department_id,department_name
from departments

练习使用union
select 'I study at' my_blogs
from dual
union
select 'www.cnblogs.com/shellway'
from dual
union
select 'happily'
from dual
得到结果：
MY_BLOGS
------------------------
I study at
happily
www.cnblogs.com/shellway

可以给它加多一列来排序：
select 'I study at' my_blogs,1
from dual
union
select 'www.cnblogs.com/shellway',2
from dual
union
select 'happily',3
from dual
order by 2 （这里的2表示的是第二列）
得到结果：
MY_BLOGS 1
------------------------ ----------
I study at 1
www.cnblogs.com/shellway 2
happily 3

如果不想打印第二列，则先：
column col_nop noprint;
然后：
select 'I study at' my_blogs,1 col_nop
from dual
union
select 'www.cnblogs.com/shellway',2
from dual
union
select 'happily',3
from dual
order by 2
得出结果：
MY_BLOGS
------------------------
I study at
www.cnblogs.com/shellway
happily

练习：
查询部门的部门号，其中不包括job_id是“ST_CLERK”的部门号
方式一（之前学的）：
select department_id
from departments
where department_id not in(
select department_id
from employees
where job_id = 'ST_CLECK'
)
方式二（利用SET的方式）：
select department_id
from departments
minus
select department_id
from employees
where job_id = 'ST_CLECK'

查询10、50、20号部门的job_id、department_id,并且department_id按10、50、20的顺序排列
column col_nop noprint;(用于隐藏列使用)
select job_id,department_id,1 col_nop
from employees
where department_id = 10
union
select job_id,department_id,2
from employees
where department_id = 50
union
select job_id,department_id,3
from employees
where department_id = 20
order by 3 （这里的3表示的是第三列）
查询所有员工的last_name,department_id,department_name
select last_name,department_id,to_char(null) department_name
from employees
union
select to_char(null),department_id,department_name
from departments

疑问：同样是查询所有员工的last_name,department_id,department_name，用full join和union有什么区别？

多列子查询：
查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,department_id
方法一（传统的方法）：
select employee_id,manager_id,department_id
from employees e
where manager_id in (
select manager_id
from employees
where employee_id in (141,174)
)
and department_id in (
select department_id
from employees
where employee_id in (141,174)
)
and employee_id not in (141,174)
方法二（多列子查询）：
select employee_id,manager_id,department_id
from employees e
where (manager_id,department_id) in (
select manager_id,department_id
from employees
where employee_id in (141,174)
)
and employee_id not in (141,174)

在from子句中使用子查询
返回比本部门平均工资高的员工的last_name,department_id,salary以及平均工资
方法一：
select last_name,department_id,salary,( select avg(salary)
from employees e3
where e1.department_id = e3.department_id
group by department_id)
from employees e1
where salary > (
select avg(salary)
from employees e2
where e1.department_id = e2.department_id （注意，这是体现“本部门”的条件，不能少了）
group by department_id
)
方法二：
select last_name,e1.department_id,salary,e2.avg_sal
from employees e1,(select department_id,avg(salary) avg_sal from employees group by department_id) e2
where e1.department_id = e2.department_id
显示员工的employee_id,last_name,location,其中，若员工department_id与location_id为1800的department_id相同，则location为'Canada',其余为‘USA’
select employee_id,last_name,(
case department_id when (select department_id from departments where location_id = 1800) then 'Canada'
else 'USA' end
)location
from employees
若employees表中employee_id与job_history表中employee_id相同的数目不小于2，则输出这些相同id的员工的
employee_id,last_name和其job_id
查询公司管理者的employee_id,last_name,job_id,department_id信息
方法一：
select employee_id,last_name,job_id,department_id
from employees e1
where employee_id in (
select manager_id
from employees e2
where e1.employee_id = e2.manager_id
)
方法二：
select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1,employees e2
where e1.employee_id = e2.manager_id
方法三：利用esists来查询符合条件的就返回true输出否则返回false不输出，即只要你是管理都就给我输出，我不管你是几号的
select employee_id,last_name,job_id,department_id
from employees e1
where exists (
select 'A'
from employees e2
where e1.employee_id = e2.manager_id
)
查询departments表中，不存在于employees表中的部门的department_id,department_name
select department_id,department_name
from departments d
where not exists (
select 'A'
from employees
where department_id = d.department_id
)
删除employees表中，其与emp_history表皆有的数据
delete from employees e
where employee_id = (
select employee_id
from emp_history
where employee_id = e.employee_id
)

with子句：
查询公司中工资比Abel高的员工的信息
方法一：
select employee_id,last_name,salary
from employees
where salary>(
select salary
from employees
where last_name = 'Abel'
)
方法二：（使用with子句）
with Abel_sal as (
select salary
from employees
where last_name = 'Abel'
)

select employee_id,last_name,salary
from employees
where salary > (
select salary
from Abel_sal
)

查询公司中各部门和总工资大于公司中各部门的平均总工资的部门信息
with dept_sumsal as (
select department_name,sum(salary) sumsal
from employees e,departments d
where e.department_id = d.department_id
group by department_name
),
dept_avg_sumsal1 as (
select sum(sumsal)/count(*) dept_avg_sumsal2
from dept_sumsal
)
select *
from dept_sumsal dd
where dd.sumsal > (
select dept_avg_sumsal2
from dept_avg_sumsal1
)
练习：
查询员工的last_name,department_id,salary，其中员工的salary,department_id
与有奖金的任何一个员工的salary,department_id相同即可。(考的是多列子查询)
select last_name,department_id,salary
from employees
where (salary,department_id) in (
select salary,department_id
from employees
where commission_pct is not null
)
选择工资大于所有job_id = 'SA_MAN'的员工的工资的员工的last_name,job_id,salary
select last_name,job_id,salary
from employees
where salary > all (
select salary
from employees
where job_id = 'SA_MAN'
)
或者
select last_name,job_id,salary
from employees
where salary > (
select max(salary)
from employees
where job_id = 'SA_MAN'
)
选择所有没有管理者的员工的last_name
select last_name
from employees
where manager_id is not null
或者
select last_name
from employees e1
where not exists (
select 'A'
from employees e2
where e1.manager_id = e2.employee_id
)

 

吾时而躬身自省，自省使知已之不足，知不足而奋起，未为晚也!