oracle数据库（三）

--------------------------------------------------------------------------------
lesson  4   Displaying Data from Multiple Tables
多表查询概念：
    所谓多表查询，又称表联合查询，即一条语句涉及到的表有多张，数据通过特定的连接进行联合显示。
查询s_emp员工的id，last_name，所属部门的名称
查找内容：id,last_name,name
 s_emp,s_dept


基本语法：
select table1_column_name,table2_column_name....
from table1,table2
条件。。。。




学前须知：
笛卡尔积
在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y.
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积
例如:
员工表，部门表
select *
from s_emp,s_dept;




连接查询分类：
为了在多表查询中避免笛卡尔积的产生,我们可以使用连接查询来解决这个问题.
连接查询分为:
1.等值连接
2.不等值连接
3.外连接
左外连接
右外连接
全连接
4.自连接






等值连接：利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来。连接条件使用=
语法：
select tb_name.col_name,tb_name.col_name,...
from tb_name[alias],tb_name[alias],...(多表以逗号分隔)
where tb_name.col_name = tb_name.col_name
and
     tb_name.col_name = tb_name.col_name
...
(1)查询所有员工的ID，last_name和所在部门的名称？
ID，last_name:s_emp
name:s_dept


select s_emps.id,last_name,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;
第一步：确定要查询的表
s_emp,s_dept
第二步：确定连接条件
where s_emp.dept_id=s_dept.id
第三步：拼写sql
select se.id,se.last_name,name
from s_emp se,s_dept sd
where dept_id = sd.id;
注:有时为了简化操作，或者表述更加清楚，可以给每张表起别名，空格分开，不可加as


(2)查询员工的last_name和部门名称,所在地区的名称？
last_name,name,name
第一步：确定要查询的表
s_emp,s_dept,s_region
第二步：确定连接条件
where s_emp.dept_id = s_dept.id and s_dept.region_id = s_region.id
第三步：拼写sql
select last_name,sd.name,sr.name
from s_emp se,s_dept sd,s_region sr
where se.dept_id=sd.id and sd.region_id=sr.id;


(3)查询部门名称包含sa的员工姓名、薪水、部门名字


select last_name,salary,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id and lower(name) like '%sa%';






(4)查询部门名称大于5位，该部门员工的薪水不等于1500，并按员工的薪水降序排序


select last_name,salary,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id and length(name) >5 and salary !=1500
order by salary desc;






注意,连接查询：先写连接条件，再写其他条件




不等连接: 使用的是除=以外的操作符号的多表查询
 如>=....<=  between.... and.......
select t1.col_name,t2.col_name
from t1,t2
where t1.col_name between t2.col_name
and t2.col_name;




create table s_rank(
id number(5) primary key,
minSal number(7),
maxSal number(7),
name varchar2(10));
insert into s_rank values(1,0,1000,'蓝领');
insert into s_rank values(2,1000,1500,'白领');
insert into s_rank values(3,1500,2500,'金领');
commit;


(5)查询所有员工的工资等级？


第一步：确定要查询的表
s_emp,s_rank


第二步：确定连接条件
where s_emp.salary>=s_rank.minsal and s_emp.salary<s_rank.maxsal


第三步：拼写sql
select last_name,salary,name
from s_emp se,s_rank sk
where salary >=minsal and salary<maxsal;


外连接:当一个表的记录在另外一张表中不存在的时候，我们依旧需要显示，使用外连接即可。
先分别在俩s_emp和s_dept表中插入新的数据
特点： s_emp表中的briup没有所属部门
s_dept表中的教学部没有一个员工
insert into s_emp(id,last_name) values(999,'_briup');//已执行
insert into s_dept(id,name) values(60,'教学部');
commit;


外连接分为:
左外连接(left join/left outer join)
右外连接(right join/right outer join)
全外连接(full join/ full outer join)
员工表（有一个员工没有部门）  部门表（有一个部门没有员工）
左外连接:左边表(=号左边的表)中的记录在 右边表 中不存在的时候,左边表的记录依旧显示
查询员工姓名，部门名称
s_emp有员工       在s_dept没有对应部门
语法：
select tb_name,col_name,...
from tb_name1 left outer join tb_name2
on
tb_name1.col_name = tb_name2.col_name;


select last_name,name
from s_emp left outer join s_dept on s_emp.dept_id = s_dept.id;


或者：
select col_name1,col_name2,...
from tb_name1,tb_name2
where
tb_name1.col_name = tb_name2.col_name(+);




查询所有员工的last_name以及对应的部门的名字,id,没有部门的员工也要显示出来


select last_name,name
from s_emp se,s_dept sd
where se.dept_id=sd.id(+);


注意:outer可以省去不写
仍可加where条件对结果集筛选


右外连接：右外连接表示右边表（=号右边的表）中的记录在 左边表 中不存在的时候，右边表的记录依旧显示。
s_dept中有部门  在emp表中没有对应员工
select tb_name,col_name,
from tb_name1 right outer join tb_name2
on .......


或者:
select tb_name,col_name,...
from tb_name1,tb_name2
where
tb_name1.col_name (+)= tb_name2.col_name;


查询所有员工 以及对应的部门的名字,id,没有任何员工的部门也要显示出来(right/+/left相对)
select last_name,name
from s_emp right join
s_dept on s_emp.dept_id = s_dept.id;


select last_name,name
from s_dept,s_emp
where s_emp.dept_id(+) = s_dept.id;




全连接：
查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来,没有部门的员工也要显示出来
tablea full outer join tableb on..


select last_name,name
from s_emp full join
s_dept on s_emp.dept_id = s_dept.id;




自连接
实质就是一张表当多张表用，即一张表中的某列的值取决于自己的某一列
语法：
select ...
from tb_name t1,tb_name t2
where t1.col_name = t2.ano_col_name


例：查看每一个员工的id,经理id，经理名字
e_emp
select s1.id,s2.id,s2.last_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id;




没有经理的员工也要显示出来
select s1.id,s2.id,s2.last_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id(+);


select s1.id,s2.id,s2.last_name
from s_emp s1 left join s_emp s2
on s2.id=s1.manager_id;


select s1.id,s2.id,s2.last_name
from s_emp s1,s_emp s2
where s2.id(+)=s1.manager_id;


select s1.id,s2.id,s2.last_name
from s_emp s2 right join s_emp s1
on s2.id=s1.manager_id;




集合连接:对查询结果集的操作。
union:将上下结果取并集，去除掉重复的记录（重复的只显示一次）
union all：将上下结果全部显示，不会去除重复的行
minus：第一个结果集去掉第二个结果集和它相同的部分(取差集 A-B) [1,2,3]-[2,3,4] =[1]
intersect：取交集
前提条件是：两个结果集中查询的列要完全一致。


union  //并集，重复的行只显示一次
查询员工姓名与对应的部门id，name，没有部门的员工与没有员工的部门都要显示出来


select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id = sd.id(+)
union
select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id(+) = sd.id;




union all //并集，不会消除重复的行
select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id = sd.id(+)
union all
select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id(+) = sd.id;








minus //第一个结果集去掉第二个结果集和它相同的部分(差集)


select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id = sd.id(+)
minus
select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id(+) = sd.id;


intersect  //求俩个结果集的交集


select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id = sd.id(+)
intersect
select last_name,sd.id,name
from s_emp se,s_dept sd
where se.dept_id(+) = sd.id;


伪列：oracle
oracle中的伪列 rownum
伪列rownum，就像表中的列一样，但是它并不实际保存在表中。伪列只能查询，不能进行增删改操作。它会根据返回的结果为每一条数据生成一个序列化的数字.rownum是oracle才有的伪列


rownum 所能作的操作:>0,=1,<任何正数(>1)


你的规则中必须能让rownum取到1


rownum 只能等于1  如果让其等于其他数 则查不到数据
例如:
select last_name
from s_emp
where rownum=1


rownum 大于0     >,>= 如果让其大于其他数 则查不到数据
例如:
select last_name
from s_emp
where rownum>0


rownum 可以小于任何数  <,<=
例如:
select last_name
from s_emp
where rownum<7


查询s_emp第一条信息，前10条信息，第5到第7条信息
select id,last_name
from s_emp
where rownum =1;


select id,last_name
from s_emp
where rownum <=10;


n:查的第n页，m每一页的量
分页：n*m minus (n-1)*m
1--5
select id,last_name
from s_emp
where rownum <=5
minus
select id,last_name
from s_emp
where rownum <=0;
6-10
select id,last_name
from s_emp
where rownum <=10
minus
select id,last_name
from s_emp
where rownum <=5;
11-15
select id,last_name
from s_emp
where rownum <=15
minus
select id,last_name
from s_emp
where rownum <=10;


分页






子查询后:
1.查找薪水降序前5名的员工salary，last_name


select id,last_name,salary
from s_emp
where rownum <=5
order by id desc;
salary... stunum,start_date,age,name
测试:说明先截取，再排序,
伪列与排序问题：排序在最后
但是如果排序的列是主键列，先排序再截取
select id,last_name,salary
from s_emp
order by salary desc;












select *
from (
select last_name,salary
from s_emp
order by salary desc
)ss
where rownum <6;




//只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM
//可以在结果集中将 rownum 生成一列,分页
test1:


----
test2:
select * from(select last_name,salary,rownum nm
from (
select last_name,salary
from s_emp
order by salary desc
))
where nm>3 and nm<6;
----------------------------------------------------------------------------------
lesson 5：Group Function
组函数：
所谓组查询即将数据按照某列或者某些列相同的值进行分组，然后对该组的数据进行组函数运用，针对每一组返回一个结果。
note:
1.组函数可以出现的位置：
select子句和having 子句
2.使用group by 将将行划分成若干小组。
3.having子句用来限制组结果的返回。
语法：
select ...
from ...
where ...
group by col_name,col_name    dept_id
having ...
order by...


group by col_name:即将数据按照col_name相同值进行分组


组函数常见有5个：
avg()：求平均值
count()：求总数
max()：最大值
min()：最小值
sum()：求和


avg([distinct] column )/sum([distinct] column) :可以作用在存储数字数据的列上。
max(),min():可以作用在任意类型的数据之上。
对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。
count([distinct] column | *) :
count(*) ： 统计表中所有的行数
count(column) : 返回行数


练习：查询所有员工的平均工资
select avg(salary)
from s_emp;
 查询所有员工的最高工资
 select max(salary)
 from s_emp;
 查询每个部门的最高工资
 select max(salary),dept_id
 from s_emp
 group by dept_id;
 查询所有员工的最低工资
 select min(salary)
 from s_emp;
 查看所有员工的工资总和
  select sum(salary)
 from s_emp;
 查看拥有员工的部门的总数量
 select count(last_name)
 from s_emp
 group by dept_id;


group by 子句：
1.用来将表中的行划分成若干更小的组
2.出现在select子句中，但是没有出现在组函数中的列必须出现在group by子句中
3.出现在group by中的列不一定出现在select子句中。
4.group by子句中的列出现在select结果中，表意性比较强。
5.当group by子句中出现多列的时候，表示按照从左至右的顺序进行分组，即先按照第一列分组，  然后再第一列分好的组里面 按照第二列进行分组，以此类推。
6.限制组结果的返回一定使用having不能使用where。不能在group by子句中使用列别名


having字句: 限制组结果的返回。
1.如果希望限制组结果的返回，那么直接使用having子句跟在group by 子句之后。


注意：如果select/having语句后面有 没有被组函数修饰的列，就必须出现在group by 后面
 组函数出现的位置 : select后面 having后面 order by后面


  例：
查看各个部门的最高工资
select max(salary),dept_id
from s_emp
group by dept_id;




查看各个部门的员工数
select count(salary),dept_id
from s_emp
group by dept_id;


查看各个部门的平均工资
select avg(salary),dept_id
from s_emp
group by dept_id;


查看各个部门的最低工资
select min(salary),dept_id
from s_emp
group by dept_id;
1.查询每个部门的平均工资并且显示出部门的名字和部门id？
select avg(salary),name,sd.id
from s_emp se,s_dept sd
where se.dept_id = sd.id
group by sd.id,name;


2.查看各部门，职称相同的人的平均工资,按照部门id排序。
select avg(salary),dept_id,title
from s_emp
group by dept_id,title
order by dept_id;


3.查找部门平均工资>1000的所有部门的id和平均工资
select avg(salary),dept_id
from s_emp
group by dept_id
having avg(salary)>1000;


4.查询平均工资大于1400的部门id,并且显示出部门的名字？
select avg(salary),dept_id,name
from s_emp se,s_dept sd
where se.dept_id=sd.id
group by dept_id,name
having avg(salary)>1400;


5.查询title中不包含vp字符串的每个职位的平均薪水，并对平均薪水进行降序排列，
 并且每个职位的总薪水大于5000？


select avg(salary),title
from s_emp
where lower(title) not like '%vp%'
group by title
having sum(salary) >5000
order by avg(salary) desc;






from--where-->group by分组-->执行组函数-->having筛选->order by




--------------------------------------------------------------------------------------
查询s_emp表中最大工资数,并且显示出这个人的名字？
select last_name,salary
from s_emp
where salary = ??? --2500
???:数字，s_emp表中的最大薪水
select max(salary)
from s_emp;
一个sql语句的执行，想要借助另一句sql语句的结果。
select last_name,salary
from s_emp
where salary =(
select max(salary)
from s_emp
);




select last_name,salary
from s_emp
where salary = ?;




select max(salary) from s_emp;


MAX(SALARY)
-----------
2500


select last_name,salary
from s_emp
where salary=2500;


sql使用另一个sql语句的结果




Lesson 6：subqueries 子查询（嵌套查询）
概念：
所谓子查询，即一个select语句中嵌套了另外的一个或者多个select语句






应用场景：
1.一条查询语句的查询条件依赖另外一条查询语句的查询结果。where
2.一条查询语句的查询结果是作为另外一条查询语句的查询表(查询依据)。
from 表名
3.在DML操作中使用子查询(后期介绍)


子查询的基本原则：
1.在查询中可以有单行子查询和多行子查询
2.子查询可以出现在操作符的左边或者右边
3.子查询在很多sql命令中都可以使用
4.嵌套查询先执行，然后将结果传递给主查询。




一、比较值不确定，需要另外一个select语句执行后才能得到，使用子查询
语法：
select ...
from ...
where col_name 比较操作符 (
select ...
from ...
where ...
group by ...
having...
)
group by ...
having...
order by ...


单值子查询：子查询的结果为1个
需求：
1.查询和Smith职称相同的所有员工的last_name和职称
select last_name,title
from s_emp
where title=(
select title
from s_emp
where last_name='Smith'
);


分析步骤：
1.确定最终查询结果(目标/主查询)：查询员工的last_name和title
from : s_emp
 条件 ： title = Smith的职称
select last_name,title
from s_emp
where title = ?
2.确定条件(子查询)：Smith的职称
from : s_emp
条件 ：last_name = 'Smith';
select title
from s_emp
where last_name = 'Smith';
3.组合
select last_name,title
from s_emp
where title = (select title
from s_emp
where last_name = 'Smith');
2:查找和Smith同一个部门的员工的id和last_name




1.最终查询目标 ：


2.子查询的目标：


3. 组合：




3.查看工资大于Chang员工工资的所有员工的id和名字。


4.查看部门平均工资大于32号部门平均工资的部门id,名称




5.查询工资比Ngao所在部门平均工资高的员工信息




多值子查询：子查询的结果为多个
需求：
1.查询所在区域为2号区域的所有部门的员工的id和last_name
select se.id,se.last_name
from s_emp se,s_dept sd,s_region sr
where se.dept_id=sd.id and sd.region_id = sr.id and sr.id = 2;


select id,last_name
from s_emp
where dept_id in(
select id
from s_dept
where region_id = 2
);


1.确定最终查询结果： 员工的id, last_name
  from : s_emp
  条件 ：s_emp.dept_id  in (?);
select id,last_name
from s_emp
where dept_id  in ?
2.确定条件：所在区域为2号部门
子查询：部门id
from : s_dept
条件: region_id = 2;
select id
from s_dept
where region_id = 2;
3.组合：
select id,last_name
from s_emp
where dept_id  in (
select id
from s_dept
where region_id = 2
)




子查询出现情况二：
查找的内容不确定，需要从构建出来一个查询的表
语法：
select ....
from (select .... from ....) b
where ......


练习：查询各部门的id，name 和部门员工的平均工资
select s_dept.id,name,avg(salary)
from s_emp,s_dept
where s_emp.dept_id=s_dept.id
group by s_dept.id,name;




select id,name,b.sal
from s_dept,(
select avg(salary) sal,dept_id
from s_emp
group by dept_id
) b
where s_dept.id=b.dept_id




1.查询目标：
需要部门的id，部门的name ------ 从 s_dept表中
部门员工的平均工资 avg(salary) --------- salary只有s_emp表中有
条件 ： 部门id，name和部门 员工，因此要求部门的id跟员工所在部门的id相等才连接
select id,name, 平均工资
from s_dept , ?
where s_dept.id = ?.dept_id;


2.查询条件
select(dept_id,avg(salary) sal)
from s_emp
group by dept_id;


3.组合：
select id,name,b.sal
from s_dept dept,(select dept_id,avg(salary) sal
from s_emp
group by dept_id
) b
where dept.id = b.dept_id;


查询员工信息的前5条，第6--10条，第11--15条//使用子查询将伪列转换为实列，分页




查询出工资最高的前5名员工的姓名、工资


排序最后执行：


//查询入职日期最早的3名员工的start_date,last_name
































select *
from (
select last_name,salary
from s_emp
order by salary desc
)ss
where rownum <6;


//只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM
//可以在结果集中将 rownum 生成一列,分页
select * from(select last_name,salary,rownum nm
from (
select last_name,salary
from s_emp
order by salary desc
))
where nm>3 and nm<6;