Oracle数据库表的连接、组函数

一、表连接

2019/11/7 19:10:47

1、 内连接

方式一:,笛卡尔乘积，在此基础上筛选

select 列名1，列名2
from 表1，表2
where 表1.外健列=表2.主键列;

select * from s_emp,s_dept
where s_emp.dept_id=s_dept.id and s_emp.LAST_NAME='Biri';

select 表别名1.列名1，表别名2.列名2
from 表1 表别名1，表2 表别名2
where 表别名1.外健列=表别名2.主键列

select e.LAST_NAME,d.NAME 
from s_emp e,s_dept d
where e.dept_id=d.id;

方式二:

select 表1.列名1，表2.列名2 
from 表1
inner join 表2
on 表1.外健列=表2.主键列

select * 
from s_emp
inner join s_dept
on s_emp.dept_id=s_dept.id
where s_emp.LAST_NAME='Biri'

**例：**查询员工表中last_name为’Biri’的员工的last_name与部门名称和地区名称

select *
from s_emp,s_dept, S_REGION
where s_emp.dept_id=s_dept.id 
and s_dept.REGION_ID=S_REGION.id
and s_emp.LAST_NAME='Biri'；

select * 
from s_emp
inner join s_dept
on s_emp.dept_id=s_dept.id
inner join S_REGION
on s_dept.REGION_ID=S_REGION.id
where s_emp.LAST_NAME='Biri'；

**例：**查询员工表中last_name为’Biri’的员工的last_name及其部门经理名称

SELECT 	worker.last_name||' works for '||manager.last_name
FROM s_emp worker, s_emp manager
WHERE worker.manager_id = manager.id;


SELECT 	worker.last_name||' works for '||manager.last_name
FROM s_emp worker
inner join s_emp manager
on worker.manager_id = manager.id;

2、左外连

例子： 查询出没有分部门的员工，员工的last_name,部门名左外连，表示 左表中的数据一定在会结果中出现

方式一：的左外连,左表中的数据一定在会结果中出现

select last_name,name from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+) and dept_id  is null  //表示，没有+方的那个表中的数据一定在会结果中出现

方式二：的左外连,左表中的数据一定在会结果中出现

select last_name,name from s_emp
left outer join s_dept
on  s_emp.dept_id=s_dept.id
and dept_id  is null  

3、右外连接

右外连，表示 右表中的数据一定在会结果中出现

**例子：**找出哪个部门没有员工
方式一：

select last_name,name from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id   //表示，没有+方的那个表中的数据一定在会结果中出现
and s_emp.id  is null  

方式二：

select last_name,name from s_emp
right outer join s_dept
on  s_emp.dept_id=s_dept.id
where s_emp.id  is null  

二、组函数

分组的语法格式

select 分组列名
分组列名,聚合函数（列名）
from 表明
group by 分组列
having 条件
order by 列名/分组函数 asc/desc

常用的分组函数

1.AVG (DISTINCT|ALL|n)平均值

1.查询s_emp表中所有员工的平均工资：
select avg(salary)
from s_emp;

2.查询s_emp表中各个部门员工的平均工资及部门名称：
select s_dept.name,avg(salary)
from s_emp,s_dept
where s_emp.dept_id=s_dept.id
group by s_dept.name;

2.COUNT (DISTINCT|ALL|expr|*)计数

1.查询s_emp表中31部门一共有多少员工
select count(*)
from s_emp
where s_emp.dept_id=31;

2.查询s_emp表中销售人员的数量(提成率不为空的记录个数)：
select count(COMMISSION_PCT)
from s_emp;

3.MAX (DISTINCT|ALL|expr)最大
4.MIN (DISTINCT|ALL|expr)最小

1.求s_emp表中员工的最高和最低工资
select max(salary),min(salary)
from s_emp;

5.SUM (DISTINCT|ALL|n) 总和

1.求s_emp表中所有员工的工资总和
select sum(salary)
from s_emp;

2.查询s_emp表中各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序
select avg(salary),sum(salary),s_dept.name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id
group by s_dept.name
order by sum(salary) asc;

3.查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：
select avg(salary),sum(salary),s_dept.name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id and s_emp.dept_id!=41
group by s_dept.name
order by sum(salary) asc;


4.求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：
select TITLE ,sum(salary)
from s_emp
where title not like 'VP%'//此处要注意大小写
group by title
having sum(salary)>5000
order by sum(salary) asc;

##三、子查询 ##

1.查询s_emp表中工资最低的员工的姓名
select * from s_emp where salary=(
  select min(salary) from s_emp
)


select * from s_emp where salary in(750,850)；--in表示两个值

2.查询s_emp表中平均工资低于32部门的部门ID,名称
select avg(salary),dept_id,name
from s_emp,s_dept 
group by dept_id,name
having avg(salary)<(
  select avg(salary) from s_emp where dept_id=32
)