orcl基础笔记--多表查询和组函数

• 组函数

概念：也叫聚合函数，多行输入一行输出。普通函数是一行输入一行输出
常见五种组函数：
MAX() 求最大值 求员工的最大工资 select max(sal) from emp;
MIN() 求最小值 求员工的最小工资 select min(sal) from emp;
AVG() 求平均值 求部门编号为10的平均工资 select avg(sal) from emp where deptno = 10;
SUM() 求和 求部门编号为10的总工资 select sum(sal) from emp where deptno = 10;
COUNT() 求总共多少条记录 求10号部门一共有多少个员工 select count(*) from emp where deptno=10

· group by分组规则 : 如果分组了,select后面的字段要么是分组的条件，要么是五个组函数之一

select  deptno,avg(sal) as avg_sal from emp group by deptno

需求2：每个部门同一个职位的最大工资

select deptno ,job,max(sal)  from emp group by deptno,job

· 使用 having 对分组以后的数据进行再过滤
where 语句是对单条记录进行过滤的，不能过滤分组之后的记录执行语句的时候首先执行where 语句 然后把查到的记录进行分组要想过滤分组后的数据，用having

需求1:按照部门编号进行分组，分组之后求每一个部门的平均薪水，要求显示平均薪水大于2000的部门的部门编号和平均薪水

	select deptno,avg(sal) from emp	group by deptno	having avg(sal)>2000;

需求2:薪水大于1200的雇员，按照部门编号进行分组，分组之后平均薪水必须大于1500,求分组内的平均工资，平均工资按倒序排列

select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc；

- 查询

查询的分类：
简单查询: 是查一次 一个select
复杂查询分为子查询和连接查询

1. 子查询(也叫嵌套查询)

查多次，多个select嵌套出现,第一次的查询结果可以作为第二次的查询条件 或 表名

需求1:求最大工资那个人的姓名和薪水

第一步:求最大工资 

select max(sal) from emp;

第二步:求最大工资那个人叫什么

select ename,sal from emp where sal=5000;

第三步 合并

select ename,sal from emp where sal=(select max(sal) from emp); 

需求2:哪些人得工资位于 所有人得平均工资之上

1.求平均工资

	select avg(sal) from emp;

2.工资大于平均工资的 人的姓名、工资

select ename,sal from emp where sal>2073.21428571429;

3.合并

select ename,sal from emp where sal>(select avg(sal) from emp);

2. 连接查询

查询多张表的数据,把多张表连接起来查询叫连接查询，当数据来自多张表的时候，考虑使用连接查询。

分类：内连接、外连接、交叉连接、自然连接

【内连接】： inner join on
需求:查询员工姓名、薪水、部门名称

	select e.ename,e.sal,d.dname from dept d inner join emp eon (d.deptno = e.deptno)；

【外连接】(外连接语句中的outer关键字可以省略)
[左外连接]：在内连接的基础上,保证左表的数据都有 (右表的字段用空补全) left [outer] join on

select * from dept left outer join empon (dept.deptno = emp.deptno)；

[右外连接]：在内连接的基础上,保证右表的数据都有 (左表的字段用空补全) right [outer] join on

select * from dept right outer join emp	on (dept.deptno = emp.deptno)；

[全外连接]：在内连接的基础上保证左右表的数据都有 (左连接和右连接的并集)full [outer ]join on

select * from dept full outer join emp	on (dept.deptno = emp.deptno)

【交叉连接(笛卡尔积)】左表的每条记录和右表的每条记录首尾相连

   select ename,dname from emp,dept//1992sql	 
   select ename,dname from dept cross join emp//1999sql ；       

左表4条*右表14=查询结果共56条

【自然连接】
和内连接类似，只是有以下区别，以左右两表相同名称的字段 作为连接条件 相当于加上 on (dept.deptno = emp.deptno)
去掉重复的字段(即名称相同、类型也相同的字段)。

select * from dept natural join emp

【自关联】

连接查询时,左右两张表是同一张表，为一张表取不同的别名，当成两张表来用。
需求1:SMITH这个人的名字 以及他的经理人的名字

	select employee.ename,manager.ename from emp manager inner join emp employee
	on(manager.empno=employee.mgr) where employee.ename='SMITH'

需求2:查询所有员工的名字和上司的名字

	select employee.ename,manager.ename from emp manager right join emp employee	
	on(manager.empno=employee.mgr)

练习三

/*使用scott/tiger用户下的emp表和dept表完成下列练习*/

--1．列出至少有一个员工的所有部门
select e.ename, d.dname  from emp e inner join dept d on e.deptno = d.deptno;
--能用子查询就不要用表连接
select *  from dept where dept.deptno in(select deptno from emp);--效率更高
select deptno from emp group by deptno having count(*)>0;

--2．列出薪金比“SMITH”多的所有员工。
select ename, sal from emp where sal >( select sal from emp where ename = 'SMITH');

--3．列出所有员工的姓名及其直接上级的姓名。(自连接)
select e1.ename, nvl(e2.ename, 'boss') as "领导" from emp e1 
     left  join emp e2 on (e1.mgr = e2.empno);
     
--4．列出受雇日期晚于其直接上级的所有员工。(自连接)
select e1.ename as "员工", e1.hiredate, e2.ename as "领导", e2.hiredate  
   from emp e1 inner join emp e2 on (e1.mgr = e2.empno and e1.hiredate > e2.hiredate);
  --子查询方案（子查询一般用于一个表，表连接一般用于多个表）：
select * from emp p where p.hiredate - (select hiredate from emp where empno = p.mgr ) > 0;
--5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)
select e.*, d.deptno, d.dname from emp e right outer join dept d on (e.deptno = d.deptno);

--6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select e.ename, e.job, d.dname from emp e left outer join dept d 
    on ( e.deptno = d.deptno ) where (e.job = 'CLERK');
    
--7．列出最低薪金大于1500的各种工作。 
select job， min(sal) from emp group by job having min(sal) > 1500;

--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

--9．列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal >(select min(sal) from emp); 

--10．列出与“SCOTT”从事相同工作的所有员工。
select ename from emp where job = (select job from emp where ename = 'SCOTT');

--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename, sal,deptno from emp where sal in (select sal from emp where deptno = 30);

--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal > all (select sal from emp where deptno = 30);

--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select count(*), avg(sal), avg(sysdate - hiredate)/365 from emp group by deptno;

--14．列出所有员工的姓名、部门名称和工资。 内连接
select e.ename, d.dname, e.sal from emp e inner join dept d on (d.deptno = e.deptno);

--15.列出所有部门的详细信息和部门人数。
select d.*, nvl(m."人数",0) as "人数" from dept d left join 
    (select deptno, count(ename) as "人数" from emp group by deptno) m 
        on (d.deptno = m.deptno);
        
select * from dept d left join /*更简单*/
    (select deptno, count(ename) as "人数" from emp group by deptno) m 
        on (d.deptno = m.deptno);        
        
--16．列出各种工作的最低工资。
select job, min(sal) from emp group by job;

--17．列出MANAGER（经理）的最低薪金。
select min(sal) from emp where job = 'MANAGER' ;

--18．列出所有员工的年工资，按年薪从低到高排序。
select ename, sal*12 from emp order by sal*12;

--19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 注：NULL参与运算，结果为NULL，所以有些情况需要处理NULL
select nvl(ename, '未知'), nvl(sal, 0), nvl(comm, 0) from emp;

--20.查询部门表和职员表，列出所有的部门编码和部门名字，
--以及所有的职员编码和职员名字，并以部门编码和职员编码作为升序排列的标准
select e.empno, e.ename, d.deptno, d.dname from emp e 
   full join dept d on (e.deptno = d.deptno) order by  d.deptno,e.empno;

--21.多列分组计算每个部门每个职位的平均薪水和最高薪水
select dname, job,avg(sal), max(sal) from 
(select d.deptno, d.dname, e.job,e.sal from emp e inner join dept d on (e.deptno = d.deptno))
     group by dname,job order by dname;
     
select deptno, job, avg(sal), max(sal) from emp group by deptno, job order by deptno;
     
--22.查询出薪水比本部门平均薪水高的员工信息
select ename, sal, n."平均", n.deptno from emp m inner join 
   (select deptno, avg(sal) as "平均" from emp group by deptno) n 
   on (m.deptno = n.deptno) where (m.sal > n."平均") order by n.deptno;
   
select e.* from emp e where e.sal>--子查询，超简单
(select avg(p.sal)from emp p group by deptno having p.deptno=e.deptno);

练习四

/*表架构

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
*/
–建表语句
CREATE TABLE student(s# number, sname varchar(32),sage number,ssex varchar(8));
CREATE TABLE course(c# number, cname varchar(32), t# number);
CREATE TABLE sc(s# number,c# number,score number);
CREATE TABLE teacher(t# number,tname varchar(16));

–插入测试数据语句
insert into Student select 1,N’刘一’,18,N’男’ from dual union
select 2,N’钱二’,19,N’女’ from dual union select 3,N’张三’,17,N’男’ from dual union
select 4,N’李四’,18,N’女’ from dual union select 5,N’王五’,17,N’男’ from dual union
select 6,N’赵六’,19,N’女’ from dual;

insert into Teacher select 1,N’叶平’ from dual union all select 2,N’贺高’ from dual union
all select 3,N’杨艳’ from dual union all select 4,N’周磊’ from dual ;

insert into Course select 1,N’语文’,1 from dual union all select 2,N’数学’,2 from dual union
all select 3,N’英语’,3 from dual union all select 4,N’物理’,4 from dual;

insert into SC select 1,1,56 from dual union all select 1,2,78 from dual union
all select 1,3,67 from dual union all select 1,4,58 from dual union
all select 2,1,79 from dual union all select 2,2,81 from dual union
all select 2,3,92 from dual union all select 2,4,68 from dual union
all select 3,1,91 from dual union all select 3,2,47 from dual union
all select 3,3,88 from dual union all select 3,4,56 from dual union
all select 4,2,88 from dual union all select 4,3,90 from dual union
all select 4,4,93 from dual union all select 5,1,46 from dual union
all select 5,3,78 from dual union all select 5,4,53 from dual union
all select 6,1,35 from dual union all select 6,2,68 from dual union
all select 6,4,71 from dual;

--1.查询“语文1”课程比“数学2”课程成绩高的所有学生的学号；
/*select distinct e.s# from sc e where s# = (
(select s#, score from sc where c# = (select c# from course where cname = '语文') and s# = e.s#) >
(select s#, score from sc where c# = (select c# from course where cname = '数学') and s# = e.s#) 
);*/


select c# from course where cname = '语文';
select c# from course where cname = '数学';
select sc1.s# from (select * from sc where c# = 
(select c# from course where cname = '语文') order by s#) sc1 inner join 
    (select * from sc where c# = (select c# from course where cname = '数学') order by s#) sc2 
      on (nvl(sc1.score, 0) >= nvl(sc2.score, 0) and (sc1.s# = sc2.s#)) order by sc1.s#;  
        
--2.查询平均分大于60的学生的学号和平均分
select s#,"avg" from (select s#, avg(score) as "avg" from sc  group by s# ) where "avg" > 60;

--3.查询所有学生的学号，姓名，选课数，总分
select s#, count(s#), sum(score) from sc group by s# order by s#;
select s1.s#, s1.sname, s2."选课"， s2."总分" from student s1 left
    join (select s#, count(s#) as "选课", sum(score) as "总分" from sc group by s# order by s#) s2
      on (s1.s# = s2.s#);
      
--4.查询姓叶的老师的个数
select count(tname) from teacher where substr(tname,1 ,1 ) = '叶';
select count(tname) from teacher where tname like  '叶%';

--5.查询没学过“叶平”老师课的同学的学号、姓名；
select t# from teacher where tname = '叶平';--查出老师编号
select * from sc where c# = (select t# from teacher where tname = '叶平');--查出老师课程编号
select distinct s# from sc order by s#;--获得全部学生编号

select s# from (select distinct s# from sc order by s#) s where s.s# not in --得出选课学生学号
(select s# from sc where c# = (select t# from teacher where tname = '叶平'));

select s#, sname from student where s# in (
select s# from (select distinct s# from sc order by s#) s where s.s# not in --得到答案
(select s# from sc where c# = (select t# from teacher where tname = '叶平')) );

--6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；

--法一：三表连接，吊的一批
select s#, sname from ( 
select s.s#, s.sname, c.cname as "cou" from student s join sc on sc.s# = s.s# 
join course c on sc.c# = c.c#
where c.cname = '语文' or c.cname = '数学') group by s#,sname having count("cou")=2 order by s#;

--法二：用intersect取交集
select student.s#, sname from student , ( 
(select s# from sc where c# in (select c# from course where cname = '语文')) intersect
(select s# from sc where c# in (select c# from course where cname = '数学')) ) a where student.s#=a.s# ;

--法三：
select s#, sname from student where s# in (
select s# from
(select s#,count(s#) as "和" from sc where c# in --得到答案
(select c# from course where cname in ('语文', '数学'))group by s# order by s#)
  where "和" = 2) order by s#;

--7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select s#, sname from student where s# in ( 
--得出学过叶平老师所有课的学生的学号
select s# from (
--查出叶平老师教过的所有课的总数
select count(c#) as teaCount from course where t# = (select t# from teacher where tname = '叶平')) a,
--查出所有学过叶平老师的课的学生的选课总数
(select s#, count(c#) as stuCount from sc where c#  in (select c# from course 
where t# = (select t# from teacher where tname = '叶平')) group by s#) b
where a.teaCount = b.stuCount);


--8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select * from sc where c# = '1';
select * from sc where c# = '2';

select s#, sname from student where s# = (
select sc1.s# from (select * from sc where c# = '1' order by s#) sc1 inner join 
    (select * from sc where c# = '2' order by s#) sc2 
      on (nvl(sc1.score, 0) >= nvl(sc2.score, 0) and (sc1.s# = sc2.s#))
);

--9.查询所有课程成绩小于60分的同学的学号、姓名； 
select s.s#, s.sname from student s inner
join (select s#, max(score) from sc group by s# having max(score) < 60) m
on s.s# = m.s# order by s.s#;

--10.查询没有学全所有课的同学的学号、姓名； where sc.c# in(select c# from course) 
select S#, sname from student where s# in 
(select s# from sc group by s# having count(score) = 4);

--11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名； 
select c# from sc where s# = '1';--获得学号为1的学生的选课
select s#, c# from sc where  s# != 1 ;--获得其他学生的选课

select distinct s# from --获得学号
(select s#, c# from sc where  s# != 1) sc1 
inner join  (select c# from sc where s# = '1') sc2 on (sc1.c# = sc2.c#) order by s#;

select S#, sname from student where S# in (
select distinct s# from --获得答案
(select s#, c# from sc where s# in (select s# from student) and s# != 1) sc1 
inner join  (select c# from sc where s# = '1') sc2 on (sc1.c# = sc2.c#)
)


--12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名； 
select count(c#) as "count" from sc where s# = '6';
select s#, count(c#) as "count" from sc where s# in (select s# from student) and s# != '6' group by s#;

select s# from (select count(c#) as "count" from sc where s# = '6') sc1 inner join
(select s#, count(c#) as "count" from sc where s# in (select s# from student) and s# != '6' group by s#) sc2
  on (sc1."count" = sc2."count");
  
select S#, sname from student where S# in (
select s# from (select count(c#) as "count" from sc where s# = '6') sc1 inner join
(select s#, count(c#) as "count" from sc where s# != '6' group by s#) sc2
  on (sc1."count" = sc2."count")
);
--13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名； 
select c# from sc where s# = '1';
select c# from sc where  s# != 1 ; 

select s# , count(s#) from --获得与“1”有重复课程的其他同学学号
(select s#, c# from sc where  s# != 1) sc1 
inner join  (select c# from sc where s# = '1') sc2 on (sc1.c# = sc2.c#) group by s# order by s#;

select count(s#) from sc where s# = '1';


select s# from 
(select s# , count(s#) as "count" from --获得符合要求学号
(select s#, c# from sc where  s# != 1) sc1 
inner join  (select c# from sc where s# = '1') sc2 on (sc1.c# = sc2.c#) group by s# order by s#
) where "count" = (select count(s#) as "count" from sc where s# = '1');

select S#, sname from student where S# in (
select s# from 
(select s# , count(s#) as "count" from --获得符合要求学号
(select s#, c# from sc where  s# != 1) sc1 
inner join  (select c# from sc where s# = '1') sc2 on (sc1.c# = sc2.c#) group by s# order by s#
) where "count" = (select count(s#) as "count" from sc where s# = '1')
)
--14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
select c# , max(score) as "最高分", max(score) as "最低分" from sc group by c#;

select cname as "课程ID", "最高分" , "最低分" from (course) c inner join 
(select c# , max(score) as "最高分", max(score) as "最低分" from sc group by c#) sc
 on (c.c# = sc.c#);

--15.按各科平均成绩从低到高和及格率的百分数从高到低顺序 
select * from course;
select * from TEACHER;
select * from student;
select * from sc order by c#;

select c#, avg(score), count(nvl(score,0)) as "总数" from sc group by c# order by c#;

select c#, count(score) as "及格数" from sc where(score > 60)  group by c# order by c#; 

select c1.c#, "平均分", trunc(("及格数"/"总数"),3) as "及格率" from (
(select c#, avg(score) as "平均分", count(nvl(score,0)) as "总数" from sc group by c# order by c#) c1
inner join 
(select c#, count(score) as "及格数" from sc where(score > 60)  group by c# order by c#) c2
on (c1.c# = c2.c#) )order by "平均分" desc , "及格率" desc;