--------------------------------------------------------------------------------
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;
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;