一、表连接
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
)