Oracle复杂查询

准备工作：启动Oracle的两个服务---数据库实例服务和监听服务

 

 

一、建基础表-部门表和员工表

1.部门表

drop table dep;
create table dep(
d_id varchar2(11) primary key not null,
d_name varchar2(50) not null,
d_remark varchar2(200) default null,
d_status varchar2(11) not null
)
insert into dep values ('1', 'kkk', '备注1', '1');
insert into dep values ('2', '部门2', '说明2', '1');
insert into dep values ('3', '部门3', '说明3', '1');
insert into dep values ('4', '部门4', '说明4', '1');
insert into dep values ('5', '部门5', '说明5', '1');
insert into dep values ('6', '部门6', '说明6', '1');
insert into dep values ('7', '部门7', '说明7', '1');
insert into dep values ('8', '部门8', '说明8', '1');
insert into dep values ('9', '部门9', '说明9', '1');
insert into dep values ('10', '部门10', '说明10', '1');
insert into dep values ('11', '部门11', '说明11', '1');
insert into dep values ('12', '部门12', '说明12', '1');
insert into dep values ('13', '部门13', '说明13', '1');
insert into dep values ('14', '部门14', '说明14', '1');
insert into dep values ('15', '部门15', '说明15', '1');
insert into dep values ('16', '部门16', '说明16', '1');
insert into dep values ('17', '部门17', '说明17', '1');
insert into dep values ('18', '部门18', '说明18', '1');
insert into dep values ('19', '部门19', '说明19', '1');
insert into dep values ('20', '部门20', '说明20', '1');
insert into dep values ('21', '部门21', '说明21', '1');
insert into dep values ('22', '部门22', '说明22', '1');
insert into dep values ('23', '部门23', '说明23', '1');
insert into dep values ('24', '部门24', '说明24', '1');
insert into dep values ('25', '部门25', '说明25', '1');
insert into dep values ('26', '部门26', '说明26', '1');
insert into dep values ('27', '部门27', '说明27', '1');
insert into dep values ('28', '部门28', '说明28', '1');
insert into dep values ('29', '部门29', '说明29', '1');
insert into dep values ('30', '部门30', '说明30', '1');
insert into dep values ('31', '部门31', '说明31', '1');
insert into dep values ('32', '部门32', '说明32', '1');
insert into dep values ('33', '部门33', '说明33', '1');
insert into dep values ('34', '部门34', '说明34', '1');
insert into dep values ('35', '部门35', '说明35', '1');
insert into dep values ('36', '部门36', '说明36', '1');
insert into dep values ('37', '部门37', '说明37', '1');
insert into dep values ('38', '部门38', '说明38', '1');
insert into dep values ('39', '部门39', '说明39', '1');
insert into dep values ('40', '部门40', '说明40', '1');
insert into dep values ('41', '部门41', '说明41', '1');
insert into dep values ('42', '部门42', '说明42', '1');
insert into dep values ('43', '部门43', '说明43', '1');
insert into dep values ('44', '部门44', '说明44', '1');
insert into dep values ('45', '部门45', '说明45', '1');
insert into dep values ('46', '部门46', '说明46', '1');
insert into dep values ('47', '部门47', '说明47', '1');
insert into dep values ('48', '部门48', '说明48', '1');
insert into dep values ('49', '部门49', '说明49', '1');
insert into dep values ('50', '部门50', '说明50', '1');
select * from dep;

 

 2.员工表

drop table emp;
create table emp (
  e_id varchar(10)PRIMARY KEY NOT NULL,
  d_id varchar2(11) DEFAULT NULL,
  e_name varchar(50) NOT NULL,
  e_sex varchar2(11) NOT NULL ,
  e_birth date DEFAULT NULL,
  e_date date DEFAULT NULL,
  e_leader varchar2(1) DEFAULT NULL,
  e_status varchar2(11) DEFAULT NULL ,
  foreign key (d_id) references dep (d_id)
)
insert into emp values ('1273', '5', '员工637', '2', null, null, null, '3');
insert into emp values ('1296', '21', '员工16', '2', null, null, null, '2');
insert into emp values ('1302', '33', '员工891', '1', null, null, null, '3');
insert into emp values ('1355', '44', '员工405', '2', null, null, null, '1');
insert into emp values ('1434', '13', '员工382', '1', null, null, null, '3');
insert into emp values ('1453', '23', '员工347', '1', null, null, null, '1');
insert into emp values ('1640', '31', '员工3', '2', null, null, null, '4');
insert into emp values ('1671', '46', '员工64', '2', null, null, null, '0');
insert into emp values ('175', '50', '员工920', '2', null, null, null, '1');
insert into emp values ('1962', '27', '员工294', '2', null, null, null, '4');
insert into emp values ('2024', '4', '员工278', '2', null, null, null, '4');
insert into emp values ('2031', '3', '员工823', '2', null, null, null, '5');
insert into emp values ('2092', '29', '员工78', '2', null, null, null, '2');
insert into emp values ('2273', '14', '员工258', '2', null, null, null, '0');
insert into emp values ('2317', null, '员工707', '2', null, null, null, '1');
insert into emp values ('2340', '16', '员工582', '1', null, null, null, '1');
insert into emp values ('2408', '37', '员工288', '2', null, null, null, '3');
insert into emp values ('2431', '41', '员工793', '1', null, null, null, '0');
insert into emp values ('2443', '20', '员工821', '1', null, null, null, '5');
insert into emp values ('2457', null, '员工782', '1', null, null, null, '0');
insert into emp values ('2538', null, '员工198', '1', null, null, null, '5');
insert into emp values ('2690', '1', '员工457', '1', null, null, null, '5');
insert into emp values ('2777', '22', '员工618', '1', null, null, null, '5');
insert into emp values ('2914', '32', '员工500', '2', null, null, null, '1');
insert into emp values ('3025', '17', '员工540', '2', null, null, null, '5');
insert into emp values ('3027', '12', '员工626', '1', null, null, null, '1');
insert into emp values ('3096', '18', '员工136', '2', null, null, null, '0');
insert into emp values ('3121', '15', '员工999', '1', null, null, null, '0');
insert into emp values ('3150', '6', '员工675', '1', null, null, null, '5');
insert into emp values ('3449', '33', '员工37', '1', null, null, null, '3');
insert into emp values ('3512', null, '员工776', '2', null, null, null, '1');
insert into emp values ('3559', '19', '员工305', '1', null, null, null, '1');
insert into emp values ('3605', '13', '员工757', '2', null, null, null, '4');
insert into emp values ('3671', '6', '员工438', '1', null, null, null, '1');
insert into emp values ('3745', '26', '员工52', '1', null, null, null, '2');
insert into emp values ('3814', '6', '员工857', '1', null, null, null, '2');
insert into emp values ('3902', '42', '员工667', '1', null, null, null, '0');
insert into emp values ('3944', '23', '员工17', '2', null, null, null, '5');
insert into emp values ('3996', '50', '员工272', '1', null, null, null, '1');
insert into emp values ('4020', '22', '员工21', '2', null, null, null, '2');
insert into emp values ('4023', '15', '员工287', '1', null, null, null, '0');
insert into emp values ('4052', '30', '员工621', '2', null, null, null, '3');
insert into emp values ('408', '13', '员工994', '2', null, null, null, '4');
insert into emp values ('4093', '16', '员工339', '1', null, null, null, '0');
insert into emp values ('4199', '6', '员工837', '2', null, null, null, '5');
insert into emp values ('4279', '33', '员工912', '1', null, null, null, '1');
insert into emp values ('4295', '50', '员工746', '1', null, null, null, '2');
insert into emp values ('4415', null, '员工978', '2', null, null, null, '3');
insert into emp values ('447', null, '员工481', '1', null, null, null, '2');
insert into emp values ('4501', '19', '员工278', '1', null, null, null, '1');
insert into emp values ('4590', '1', '员工950', '1', null, null, null, '4');
insert into emp values ('462', '37', '员工610', '2', null, null, null, '2');
insert into emp values ('4673', '28', '员工25', '2', null, null, null, '0');
insert into emp values ('4679', null, '员工634', '2', null, null, null, '4');
insert into emp values ('4878', '34', '员工454', '2', null, null, null, '4');
insert into emp values ('4948', '41', '员工283', '2', null, null, null, '2');
insert into emp values ('4961', '17', '员工172', '1', null, null, null, '3');
insert into emp values ('4963', '12', '员工349', '1', null, null, null, '5');
insert into emp values ('5021', '26', '员工502', '1', null, null, null, '5');
insert into emp values ('504', '29', '员工969', '2', null, null, null, '3');
insert into emp values ('5295', '39', '员工305', '2', null, null, null, '1');
insert into emp values ('5317', null, '员工131', '1', null, null, null, '4');
insert into emp values ('5331', '20', '员工518', '2', null, null, null, '4');
insert into emp values ('5353', null, '员工284', '2', null, null, null, '3');
insert into emp values ('5391', '22', '员工615', '1', null, null, null, '2');
insert into emp values ('5462', '38', '员工88', '2', null, null, null, '1');
insert into emp values ('5643', '22', '员工586', '1', null, null, null, '0');
insert into emp values ('5669', '22', '员工88', '2', null, null, null, '3');
insert into emp values ('5699', null, '员工149', '1', null, null, null, '2');
insert into emp values ('5702', '3', '员工300', '2', null, null, null, '4');
insert into emp values ('5747', '42', '员工929', '2', null, null, null, '1');
insert into emp values ('5853', '18', '员工552', '1', null, null, null, '1');
insert into emp values ('5886', '20', '员工283', '2', null, null, null, '1');
insert into emp values ('5920', '19', '员工102', '2', null, null, null, '5');
insert into emp values ('6032', null, '员工427', '1', null, null, null, '3');
insert into emp values ('6071', '47', '员工1', '1', null, null, null, '0');
insert into emp values ('6170', null, '员工286', '2', null, null, null, '5');
insert into emp values ('6256', '2', '员工655', '1', null, null, null, '0');
insert into emp values ('6259', '49', '员工379', '1', null, null, null, '3');
insert into emp values ('6496', '10', '员工134', '2', null, null, null, '4');
insert into emp values ('6568', null, '员工732', '2', null, null, null, '4');
insert into emp values ('6709', '46', '员工226', '1', null, null, null, '4');
insert into emp values ('671', '6', '员工6', '2', null, null, null, '2');
insert into emp values ('6749', null, '员工770', '2', null, null, null, '4');
insert into emp values ('6750', '20', '员工978', '2', null, null, null, '4');
insert into emp values ('6821', '11', '员工339', '2', null, null, null, '0');
insert into emp values ('7231', '22', '员工12', '2', null, null, null, '2');
insert into emp values ('7453', null, '员工610', '2', null, null, null, '5');
insert into emp values ('7472', '13', '员工612', '2', null, null, null, '3');
insert into emp values ('7566', '19', '员工578', '2', null, null, null, '5');
insert into emp values ('7728', '29', '员工962', '1', null, null, null, '0');
insert into emp values ('781', '37', '员工132', '1', null, null, null, '2');
insert into emp values ('7884', '15', '员工51', '2', null, null, null, '0');
insert into emp values ('789', '33', '员工174', '2', null, null, null, '4');
insert into emp values ('8038', '6', '员工281', '2', null, null, null, '4');
insert into emp values ('8113', '2', '员工237', '2', null, null, null, '3');
insert into emp values ('8163', '4', '员工878', '2', null, null, null, '2');
insert into emp values ('8166', null, '员工595', '2', null, null, null, '2');
insert into emp values ('8318', '32', '员工51', '2', null, null, null, '3');
insert into emp values ('8335', null, '员工680', '2', null, null, null, '0');
insert into emp values ('8369', '47', '员工204', '2', null, null, null, '3');
insert into emp values ('8498', '19', '员工869', '2', null, null, null, '4');
insert into emp values ('8526', '30', '员工654', '2', null, null, null, '5');
insert into emp values ('8576', '11', '员工367', '1', null, null, null, '2');
insert into emp values ('859', null, '员工904', '1', null, null, null, '0');
insert into emp values ('86', '50', '员工914', '2', null, null, null, '5');
insert into emp values ('8612', '19', '员工513', '2', null, null, null, '5');
insert into emp values ('8625', '20', '员工969', '1', null, null, null, '1');
insert into emp values ('8634', '21', '员工517', '2', null, null, null, '4');
insert into emp values ('8942', '10', '员工664', '2', null, null, null, '1');
insert into emp values ('8948', null, '员工777', '1', null, null, null, '4');
insert into emp values ('9000', null, '员工643', '2', null, null, null, '2');
insert into emp values ('9110', null, '员工372', '1', null, null, null, '2');
insert into emp values ('9159', '21', '员工461', '2', null, null, null, '4');
insert into emp values ('9183', '14', '员工366', '1', null, null, null, '4');
insert into emp values ('9225', null, '员工792', '1', null, null, null, '3');
insert into emp values ('931', '20', '员工672', '1', null, null, null, '4');
insert into emp values ('9325', null, '员工362', '1', null, null, null, '3');
insert into emp values ('9345', '33', '员工485', '2', null, null, null, '4');
insert into emp values ('9409', '46', '员工773', '1', null, null, null, '2');
insert into emp values ('9593', '49', '员工743', '2', null, null, null, '3');
insert into emp values ('9697', '10', '员工534', '2', null, null, null, '3');
insert into emp values ('9759', null, '员工842', '1', null, null, null, '2');
insert into emp values ('9765', '32', '员工649', '1', null, null, null, '2');
insert into emp values ('9782', '30', '员工830', '1', null, null, null, '5');
insert into emp values ('9848', '26', '员工877', '1', null, null, null, '5');
insert into emp values ('9997', '18', '员工880', '1', null, null, null, '1');
select * from emp;

 

 二、笛卡尔连接

--笛卡尔积
select * from emp,dep;--3.395s

 二、内连接（ select * from 表1  inner join 表2 on 相等条件 ）

--先笛卡尔积，后连接。
select * from dep d,emp e where d.d_id=e.d_id; 
--内连接(效率高，占内存少)
select * from dep d inner join  emp e on d.d_id=e.d_id; 

 

三、左外连接（select * from 表1  left outer  join  表2 on 相等条件） 

左表数据全部被筛选，右表符合连接条件的被筛选，不符合的以null补位。

--左外连接：左表数据全部被筛选，右表符合条件的被筛选，不符合的以null补位。
select * from dep d left outer join emp e on d.d_id=e.d_id; 

四、右外连接（select * from 表1  right outer  join  表2 on 相等条件） 

右表数据全部被筛选，左表符合连接条件的被筛选，不符合的以null补位。

--右外连接：右表数据全部被筛选，左表符合条件的被筛选，不符合的以null补位。
select * from dep d right outer join emp e on d.d_id=e.d_id; 

五、没有员工的部门。

--没有员工的部门。
--思路一：通过部门表和员工表左外查询，筛选员工编号为空的。
select * from dep d left outer join emp e on d.d_id=e.d_id where e_id is null; 

--思路二：部门表中的部门编号不存在和员工表中相等的部门编号
select * from dep d where not exists(select * from emp where d_id=d.d_id);

--思路三：再查部门表中部门编号不在其中的(先查员工表中部门编号非空的部门编号）。
--也就是部门表中除去员工表中部门编号非空的。 
select * from dep where d_id not in(select d_id from emp where d_id is not null);

六、有员工的部门

--有员工的部门。
--思路一：部门表和员工表左外查询，筛选条件为员工编号非空
select * from dep d left outer join emp e on d.d_id=e.d_id where e_id is not null;
--思路二：部门表中存在员工表中部门编号和部门表员工编号相等的。
select * from dep d where exists(select * from emp where d_id=d.d_id);
--思路三：部门表中的部门编号在员工表中的部门编号为空之内
select * from dep where d_id in(select d_id from emp where d_id is not null);

七、计算总数count的效率

--计算总数count的效率
select count(*) from emp;-- 效率低
select count(0) from emp; -- 较高
select count(e_id) from emp; -- 效率高

 八、查询员工人数大于5人的部门.并列出部门编号，部门名称和部门人数。

-- 查询员工人数大于5人的部门.并列出部门编号，部门名称和部门人数
--思路一：对员工表按照部门编号分组，筛选员工编号总数大于5
select d_id,(select d_name from dep where e.d_id=d_id) d_name ,count(e_id)
from emp e 
where d_id is not null
group by d_id
having count(e_id)>5;
--思路二：查询统计出员工表和部门表的部门编号相等的员工人数大于5的
select d_id,d_name,(select count(e_id) from emp where d_id = d.d_id ) cnt
from dep d
where (select count(e_id) from emp where d_id = d.d_id ) > 5;
--思路三:
select 
	e.d_id,
	d.d_name,
	count(e_id)
from emp e left join dep d on e.d_id = d.d_id
where e.d_id is not null
group by e.d_id,d.d_name
having count(e_id)>5;

 九、聚合函数： count 、avg 、sum 、max 、min。

十、由于失误，男女输反了，需要一条sql纠正。

update emp set e_sex = (case when e_sex = 1 then 2 else 1 end) ;
update emp set e_sex = mod(e_sex,2)+1;

十一、员工表的状态随机赋0~5。

update emp set e_status = floor(rand()*6);

 十二、查询员工表 按状态显示，0~实习 1-试用 2-基层 3~中层 4~专家 5-高层

select 
	e_id,e_name,
	case
		when e_status = 0 then '实习'
		when e_status = 1 then '试用'
		when e_status = 2 then '基层'
		when e_status = 3 then '中层'
		when e_status = 4 then '专家'
		else '高层'
	end e_type

from emp;

 

十三、按性别统计员工人数

--方法一：
select e_sex,count(e_id)
from emp
group by e_sex;
--方法二：
select sum(男) 男 , sum(女) 女
from
  (
  select count(e_id) 男, 0 女 from emp where e_sex = 1
  union all
  select 0,count(e_id) from emp where e_sex = 2
  ) t;
--方法三：
select distinct
  (select count(e_id)  from emp where e_sex = 1) 男,
  (select count(e_id)  from emp where e_sex = 2) 女
from emp;