Oracle 数据库

Oracle是一个强大的关系型数据库产品，功能丰富，产品安全性有保障，是Oracle公司的主打产品

本系列笔记可能会遇到的专业词汇有：

DB, DataBase, 数据库
DBMS,DataBase Management System, 数据库管理系统
RDBMS,Relationship DataBase Management System, 关系型数据库管理系统
SQL, Standard Query Language, 标准化查询语言

本系列学习笔记包含如下的课程内容：

数据库的基本概念
DDL 命令
- Create
- ALTER
- DROP
DML 命令
- insert
- update
- delete
DQL 命令
- select 命令
- 关联查询
- 分组查询
- 子查询
DCL 命令
DTL 命令
- commit
- rollback
- savepoint
数据字典
数据库对象
- 序列
- 索引
- 视图
- 同义词

##关联查询

它是用来在多表查询的一种机制，Oracle支持内联、左外联、右外联、全联查询。默认是采用内联。
早期语法：

select [distinct] [alias.]列名[,列名]|*
from 表名1 alias[,表名2 alias][,表名3 alias]…
[where 子句]
[order by 子句]

可以看出来，它是采用
‘,’号隔开多表，利用where 指定关联条件。如：
1
2
3
-- 查询员工名、工资以及所在的部门名称。
select e.first_name,e.salary, d.name
from s_emp e, s_dept d
这条查询语句由于没有指定“关联条件”，所以产生了迪卡尔积,
迪卡尔积会造成资源的浪费，我们在查询中是要避免的。改为如下：
1
2
3
4
-- 表之间的关联关系，绝大多数都是主外键关系
select e.first_name,e.salary, d.name
from s_emp e, s_dept d
WHERE e.dept_id = d.id
新语法：

select [distinct] [alias.]列名[,列名]|*
from 表名1 alias [inner|[left|right|full outer]] join 表名2 alias
on 关联条件
[where 子句]
[order by 子句]

上面示例利用新语法改写后：
1
2
3
select e.first_name,e.salary, d.name
from s_emp e join s_dept d
on e.dept_id = d.id;

练习

-- 找出Sales 部门的所有员工
select e.* from s_emp e join s_dept d 
on e.dept_id = d.id where d.name = 'Sales';
-- 找出在 Asia 地区工作的员工
select e.* from s_emp e join s_dept d on e.dept_id = d.id 
join s_region r on d.region_id = r.id 
where r.name = 'Asia';
-- 找出客户'Hamada Sport' 的所有订单号、费用、下订日期
select o.id,o.total,o.date_ordered from s_customer c join s_ord o 
on c.id = o.customer_id 
where c.name = 'Hamada Sport';
-- 找出所有在'Asia'客户的信息
select c.* from s_customer c join s_region r 
on c.region_id = r.id 
where r.name = 'Asia';
--- 查询出客户名及它的订单号，总费用
select c.name, o.id, o.total 
from s_customer c 
left join 
s_ord o on c.id = o.customer_id 
order by c.name;
　
select c.name, o.id, o.total 
from s_ord o 
right join 
s_customer c 
on c.id = o.customer_id 
order by c.name;
--- 查询订单号，订单费用以及订单所对应的客户名
select o.id, o.total, c.name 
from s_ord o 
left join 
s_customer c 
on o.customer_id = c.id;

我们的关联查询分为2大类：

1.内联查询

[inner] join

2.外联查询

a.左外联以关联的左边为准，即使右边没有与之匹配的记录，
则左边的记录也要出现在结果集中，右边全部以NULL值显示。
left [outer] join

b.右外联
以关联的右边为准，即使左边没有与之匹配的记录，
则右边的记录也要出现在结果集中，左边全部以NULL值显示。
right [outer] join

c.全联
full [outer] join

d.交叉联返回连接两张的笛卡尔积
cross [outer] join

自关联查询：同一张表自己关联自己，如：

-- 查询出所有的员工名以及员工的上司名
select e.first_name "员工名",m.first_name "上司名"
from s_emp e join s_emp m 
-- 关联条件
on e.manager_id = m.id
　
这个语句只查询出了24行结果，少了 Carmen，因为Carmen员工的manager_id
是空值。改为：
select e.first_name "员工名",m.first_name "上司名"
from s_emp e left join s_emp m 
-- 关联条件
on e.manager_id = m.id
注意：关联的条件不一定总是做等值比较的。
　
-- 找出Womansport所购买的订单信息（订单号，费用，支付方式）
select o.id, o.total, o.payment_type, c.name from s_ord o join s_customer c on o.customer_id = c.id 
where c.name = 'Womansport';
-- 找出Operations部门工作的员工名，工资，并且按照工资降序排列
select e.first_name, e.salary from s_emp e join 
s_dept d on e.dept_id = d.id where d.name = 'Operations' order by 2 desc;

##分组查询

所谓分组，就是看待数据的”角度”不同。也就是把某类值相同的看做一组。
语法：

SELECT [DISTINCT] [alias.][列名列别名[,列名列别名]|*]
FROM 表名 [alias] [inner|outer join]
[表名[alias] ON 关联条件]
[WHERE 子句]
[GROUP BY 子句]
[HAVING 子句]
[Order by 子句]
常用的分组函数

函数名称|作用
-|-
SUM([distinct] 列|表达式|值)|求和，会自动忽略null值
AVG([distinct] 列|表达式|值)|求平均值，自动忽略null值
MAX(列|表达式|值)|求最大值
MIN(列|表达式|值)|求最小值
COUNT([distinct] 列|*)|求记录数,包含重复值和null值

练习

-- 找出员工的最高、最低、平均、以及工资总和
select max(salary) 最高工资, min(salary) 最低工资, 
avg(salary) 平均工资, sum(salary) 工资总和 from 
s_emp;
　
-- 找出各部门员工的最高、最低、平均、以及工资总和
select max(salary) 最高工资, min(salary) 最低工资, 
avg(salary) 平均工资, sum(salary) 工资总和 from 
s_emp group by dept_id;
　
--- 找出各41,42,50部门员工的最高、最低、平均、以及工资总和
select dept_id,max(salary) 最高工资, min(salary) 最低工资, avg(salary) 平均工资, sum(salary) 工资总和 from s_emp where dept_id in (41,42,50) group by dept_id;
　
--- 找出各41,42,50部门员工的最高、最低、平均、以及工资总和且该部门的最高工资大于2000
select dept_id,max(salary) 最高工资, 
min(salary) 最低工资, avg(salary) 平均工资, 
sum(salary) 工资总和 from s_emp 
where dept_id in (41,42,50) group by dept_id
having max(salary) > 2000;
　
--- 找出各41,42,50部门员工的最高、最低、平均、以及工资总和以及部门名称且该部门的最高工资大于2000
select e.dept_id,d.name,max(salary) 最高工资, 
min(salary) 最低工资, avg(salary) 平均工资, 
sum(salary) 工资总和 from s_emp e join s_dept d 
on e.dept_id = d.id 
where dept_id in (41,42,50) group by dept_id,d.name
having max(salary) > 2000;

注意：

只有出现在group by 后面的列（即用来做为分组条件的列），才有资格
写在SELECT的后面，除非使用组函数进行修饰。

WHERE 子句中不可以使用组函数，它是对数据分组之前进行过滤。

HAVING 子句中可以使用组函数，它是对数据分组之后进行过滤。

GROUP BY 子句后面的字段不能使用*, 别名, 列的序号

如：

统计工资超过1100元以上的员工，各个职称各有多少人。
select title,count(e.*)
from s_emp e where salary > 1100
group by title;
　
-- 统计共计多少个员工
select count(*) from s_emp;
　
-- 统计共计多少个职称[不能重复]
select count(distinct title) from s_emp;
　
-- 找出订单数量超过(含)2个的客户
select c.id,c.name,count(*) "订单数"
from s_customer c join s_ord o on c.id = o.customer_id
group by c.id,c.name
having count(*) >= 2;

练习：

-- 找出超过(含)4个员工的部门id及部门名称
select d.id,d.name, count(*) from s_dept d 
join s_emp e on e.dept_id = d.id group by 
d.name,d.id having count(*) >= 4;
　
-- 找出订单总费用超过10000元的客户
select c.name from s_customer c join s_ord o on c.id = o.customer_id group by c.id,c.name having sum(o.total) > 10000;
　
-- 统计各区域的客户数量，按它的降序排序
select r.name 区域, count(*) 客户数量 from s_customer c join s_region r on r.id = c.region_id 
group by r.id, r.name order by 2 desc;
　
select count(*) from s_customer group by region_id 
order by 1 desc;
-- 统计各经理的所直接管理的员工数
select e.manager_id, m.first_name, count(*) 
from s_emp e join s_emp m on e.manager_id = m.id 
group by e.manager_id, m.first_name;
　
select m.first_name 上司名字, count(*) 直接下属数量
from s_emp e join s_emp m on e.manager_id = m.id 
group by m.id, m.first_name;
　
-- 查询职位为Warehouse Manager直接管理的下属数量
select m.title, m.first_name 上司名字, count(*) 直接下属数量
-- 分组前筛查
from s_emp e join s_emp m 
on e.manager_id = m.id 
where m.title = 'Warehouse Manager' 
group by m.id, m.first_name, m.title;
　
-- 分组后筛查
select m.title, m.first_name 上司名字, count(*) 直接下属数量
from s_emp e join s_emp m 
on e.manager_id = m.id 
group by m.id, m.first_name, m.title
having m.title = 'Warehouse Manager';
　
-- 统计订单中各种支付类型的总费用
select payment_type, sum(total) from s_ord group by payment_type;

##子查询

定义:就是指查询中嵌套查询。
子查询必须用()括起来，子查询可以出现在很多位置，比如: 当列、当表、当条件等
子查询的本质：
a.内敛视图
b.把子查询的结果作为外部查询的条件
语法：

SELECT (子查询)
FROM (子查询)
WHERE (子查询)
GROUP BY 子句
HAVING (子查询)
ORDER BY 子句

举例：

-- 找出与Ben同一个部门的员工
select e1.first_name from s_emp e1 join 
s_emp e2 on e1.dept_id = e2.dept_id and 
e2.first_name = 'Ben' where e1.first_name <> 'Ben';
　
step1. 找出Ben所在的部门号
select dept_id from s_emp where first_name = 'Ben';
　
step2. 再根据上面的查询结果做为第二个查询的条件
select first_name from s_emp where dept_id = (
  -- 子查询
  select dept_id from s_emp where first_name = 'Ben'
)
and first_name <> 'Ben';
　
--- 查询出客户名，电话号码以及订单数
-- 使用关联查询
select c.name, c.phone, count(o.id) from s_customer c 
left join s_ord o 
on c.id = o.customer_id 
group by c.id, c.name, c.phone 
order by c.id;
　
-- 无关子查询
select c.name, c.phone, nvl(r.rtotal, 0) from 
s_customer c left join
(
select customer_id, count(*) rtotal from s_ord group by customer_id
) r on c.id = r.customer_id 
order by c.id;
　
-- 相关子查询
select c.name, c.phone, (
select count(*) from s_ord o where o.customer_id = c.id
) from s_customer_id c 
order by c.id;

子查询分类：

无关子查询
是指子查询中没有使用外部查询所定义的变量/别名，而且内查询是可以单独运行的。
相关子查询
是指子查询中要使用外部查询所定义的变量/别名，内查询不能单独运行。

子查询的特点：

子查询很灵活，可以解决很多其他查询方式不能解决的问题
子查询效率很低，其中相关子查询效率最低
子查询嵌套的层数越多，效率越低
查询效率：关联查询 > 无相关子查询 > 相关子查询

练习：

-- 查询出客户名及他拥有的订单数(使用子查询)
-- 无关子查询
select c.name, nvl(r.rtotal, 0) from 
s_customer c left join
(
select customer_id, count(*) rtotal from s_ord group by customer_id
) r on c.id = r.customer_id order by c.id;
　
-- 相关子查询
select c.name, (
select count(*) from s_ord o where o.customer_id = c.id
) from s_customer_id c order by c.id;
　
-- 查询出部门名及此部门的员工数。(使用子查询)
-- 无关子查询
select d.name, r.ecount from s_dept d left join 
(
-- 按部门分组,查询出每个部门的人数
select dept_id, count(*) ecount from s_emp 
group by dept_id
) r on d.id = r.dept_id;
　
-- 相关子查询
select d.name, 
(
select count(*) from s_emp e 
where e.dept_id = d.id
) from s_dept d order by d.id;
　
-- 查询出工资超过公司平均工资的员工.
第一步, 查询公司的平均工资
select avg(salary) from s_emp;
第二步, 查询工资大于第一步查询出的平均工资的员工信息
select * from s_emp where salary > 平均工资
第三步, 合并查询语句
select * from s_emp where salary > 
(select avg(salary) from s_emp);
　
-- 查询部门平均工资超过公司平均工资的部门
第一步,查询公司的平均工资
select avg(salary) from s_emp;
第二步,查询部门的平均工资
select avg(salary) from s_emp group by dept_id;
第三步,查询出部门平均工资大于公司平均工资的部门ID
select e1.dept_id, avg(salary) from s_emp e1 group by e1.dept_id having avg(salary) > 
(select avg(salary) from s_emp);
第四步,查询出相关部门
select distinct * from s_dept d where d.id in 
(
select e1.dept_id from s_emp e1 group by e1.dept_id having avg(salary) > 
(select avg(salary) from s_emp)
);
　
select d.name from s_dept d join 
(
select e1.dept_id did, avg(salary) from s_emp e1 group by e1.dept_id having avg(salary) > 
(select avg(salary) from s_emp)
) r on d.id = r.did;
　
--- 找出各个部门中大于他所在部门平均工资的员工名和工资
第一步, 查询部门的平均工资
select avg(salary) from s_emp group by 
dept_id;
第二步,
select e.first_name, e.salary from s_emp e join 
(
select dept_id, avg(salary) vsal from s_emp group by dept_id
) r on e.salary > r.vsal and e.dept_id = r.dept_id;
　
-- 相关子查询
select first_name, salary from s_emp e1 where salary > 
(
select avg(salary) from s_emp e2 where e2.dept_id = e1.dept_id
)
　
-- 找出职称相同员工
分析:首先查询出根据职称进行分组,数量大于2的职称.第二步查询出职称为第一步查询的结果的员工即为满足条件的员工
第一步,
select title from s_emp group by title having count(*) >= 2;
第二步,
select first_name, title from s_emp where title in 
(
select title from s_emp group by title having count(*) >= 2
);

把子查询当做“表”来看，那么这个”表”就是一个内联视图[inline view]。如：
1
2
3
select * from (
select id,first_name,start_date,salary from s_emp where dept_id=41
) v_emp;

要理解为什么要使用这种子查询，先来看一个伪例：rownum
它总是从1开始，依次递增，绝不会产生间隔。如：

1
2
3
4
select d.*,rownum from s_dept d where rownum = 1; // 正确
select d.*,rownum from s_dept d where rownum = 5; // 错误
select d.*,rownum from s_dept d where rownum > 5; // 错误
select d.*,rownum from s_dept d where rownum < 5; // 正确

rownum 的作用
1.TopN问题
解题思路：
a).利用子查询先按要求的列做排序.
b).再利用外部查询以及rownum 来过滤出 TopN记录

练习：

-- 查询出工资排名前三的员工
select * from
(select first_name, salary from s_emp order by salary desc) 
where rownum <= 3;
　
-- 查询出41部门工资最高的员工
select * from 
(
select first_name, salary from s_emp where 
dept_id = 41 order by salary desc
)
where rownum = 1;
　
-- 查询出拥有订单数排名前3的客户
select * from 
(
select c.name, count(*) from s_customer c 
join s_ord o on o.customer_id = c.id group 
by c.id, c.name order by 2 desc
)
where rownum <= 3;

2.分页查询问题

通项公式

select * from
(
select inner_.*, rownum rn_ from
(
-- 核心业务语句
) inner_
where rownum <= endValue
) outter_
where outter_.rn_ >= stratValue;

练习：

-- 查询出工资排名第二和第三的员工
select * from
(
select inner_.*, rownum rn_ from
(
select first_name, salary 
from s_emp order by salary desc
) inner_
where rownum <= 3
) 
outter_
where outter_.rn_ >= 2;
　
-- 查询出员工表中第6到第10的数据。
select * from 
(
select v1.*, rownum rm_ from 
(
select * from s_emp
) v1 
where rownum <= 10
) v2 
where v2.rm >= 6;
　
-- 查询出在'Asia'地区工作的员工的第二行至第四行记录。
select * from 
(
select v1.*, rownum rm_ from 
(
select * from s_emp e join s_dept d on 
e.dept_id = d.id join s_region r on 
d.region_id = r.id where r.name = 'Asia'
) v1 
where rownum <= 4
) v2 
where v2.rm >= 2;

关于子查询的集合操作

关键字|作用
-|-
UNION|返回两个子查询的并集，不含重复记录
UNION ALL|返回两个子查询的并集，包含复复记录
INTERSECT|返回交集
MINUS|两个子查询相减

exists运算符

exists用来判断子查询是否有结果,如果有,则返回true;否则,返回false.如：

-- 找出与Ben同部门的员工
select * from s_emp e1 where exists 
(
select 1 from s_emp e2 where 
e2.dept_id = e1.dept_id and 
e2.first_name = 'Ben'
) and e1.first_name <> 'Ben';
　
-- 找出各部门工资排名前2名的员工
分析: 存在这样的员工，与我同一部门并且比我工资高的员工不超过1个
select dept_id,first_name,salary from s_emp e1
where exists (
  select 1 from s_emp e2
  where e1.dept_id = e2.dept_id
  and e1.salary < e2.salary
  having count(*) <= 1
)
order by dept_id,salary desc;
　
select dept_id,first_name,salary from s_emp e1
where not exists (
  select 1 from s_emp e2
  where e1.dept_id = e2.dept_id
  and e1.salary < e2.salary
  having count(*) > 1
)
order by dept_id,salary desc;
　
-- 找出各个部门中工资最高的员工
select dept_id,first_name,salary from s_emp e1
where not exists (
  select 1 from s_emp e2
  where e1.dept_id = e2.dept_id
  and e1.salary < e2.salary
) order by e1.dept_id;
　
select dept_id,first_name,
salary from s_emp e1
where not exists (
  select 1 from s_emp e2
  where e1.dept_id = e2.dept_id
  and e1.salary < e2.salary 
  having count(*) >= 1
)
order by dept_id,salary desc; // 等同于上面, 多加了个条件
　
select dept_id,first_name,
salary from s_emp e1
where exists (
  select 1 from s_emp e2
  where e1.dept_id = e2.dept_id
  and e1.salary < e2.salary 
  having count(*) = 0
)
order by dept_id,salary desc;
　
select * from s_emp e1 join (
select dept_id, max(salary) maxsal from s_emp group by dept_id) r 
on r.dept_id = e1.dept_id and e1.salary = r.maxsal;

其他运算符

any运算符

-- 找出与41部门任意一个人的工资相同的其他部门的员工
select * from s_emp where salary = any (
select salary from s_emp where dept_id = 41
) and dept_id <> 41;
　
select * from s_emp where salary in (
select salary from s_emp where dept_id = 41
) and dept_id <> 41;

all运算符

-- 找出工资比41部门所有人工资都高的员工
select * from s_emp where salary > all (
select salary from s_emp where dept_id = 41
);
　
select * from s_emp where salary > (
select max(salary) from s_emp where dept_id  = 41);