Oracle 笔记 day03

Oracle day3
20120216
==========================
select distinct/列名/表达式/单行函数/组函数/
from 表名
where 条件(子查询)1 or 条件2
      and 条件3
group by 列名
having 组函数的条件
order by 列名/列别名/表达式/组函数

select deptno, count(*) c
from emp_ning
group by deptno
--order by c;
order by 2;

--每个班的学生人数?
1    4
2    2
3    73
4    65
5    48
--把班级人数>50人的班级查出来
select class_id, count(*)
from t_student_ning
group by class_id
having count(*) > 3
order by 2;

--每个班的成绩比例加起来是否=100?
--百分之百
1    100
2    100

select class_id, sum(scale)
from t_assess_rule_ning
group by class_id;

--每个学生的总分?按总分由高到低排序
3     173
2     171
1     157

1    1    90
1    2    87
1    3    75
1    4    67
1    5    92
1    6    95

select student_id, sum(test_score)
from t_performance_ning
group by student_id
order by 2 desc;

一.子查询
--谁的总成绩比1号学生的总成绩高?
select student_id, sum(test_score)
from t_performance_ning
group by student_id
having sum(test_score) > (
 select sum(test_score)
 from t_performance_ning
 where student_id = 1
 );

--谁的薪水比公司的平均薪水低?
select ename, salary
from emp_ning
where salary < (select avg(nvl(salary,0))
                from emp_ning);

--谁的薪水比本部门的平均薪水低?
--关联子查询
select ename, salary, deptno
from emp_ning x
where salary < (
         select avg(nvl(salary,0))
         from emp_ning
         where deptno = x.deptno         
         );

--谁的薪水比同经理的员工平均薪水低?
select ename, salary, manager
from emp_ning x
where salary < (
         select avg(nvl(salary,0))
         from emp_ning
         where manager = x.manager      
         );

张三  8000    10    1001        
李四  5000    20    1001
王五  15000   30    1001
赵六  10000   10    1002

5000

10 12000
20 5000
30 8000
.....

--哪些员工是别人的经理?
exists: 子查询是否有结果返回
        有  true
        没有 false

select empno, ename
from emp_ning x
where exists (select 1
              from emp_ning
              where manager = x.empno);

--哪些人不是别人的经理?
select empno, ename
from emp_ning x
where not exists (select 1
              from emp_ning
              where manager = x.empno);

--哪些部门没有员工?
--判断依据: 部门表的编码,没有出现在职员表的部门编码列中.
--使用 not exists
select deptno, dname, location
from dept_ning x
where not exists (
  select 1
                from emp_ning
                where deptno = x.deptno);

集合操作:
select deptno from dept_ning
minus
select distinct deptno from emp_ning;

集合的合集: union / union all
集合的交集: intersect
select ename, salary, deptno
from emp_ning
where deptno = 10
intersect
select ename, salary, deptno
from emp_ning
where salary > 8000;

二.多表联合查询

select emp_ning.*, dept_ning.*
from emp_ning join dept_ning
  on emp_ning.deptno = dept_ning.deptno;

select e.*, d.*
from emp_ning e join dept_ning d
  on e.deptno = d.deptno;

select e.ename, d.dname, d.location
from emp_ning e join dept_ning d
  on e.deptno = d.deptno;

--主键: Primary Key = PK
--列值是唯一的,不重复的
--主表 / 父表

--外键: Foreign Key = FK
--列值参照某个主键列值
--从表 / 子表

--查询学生的名字,科目,成绩
select stu.student_name,
       per.subject_id,
       per.test_score
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id;

select stu.*, per.*, sub.*
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id
     join
     t_subject_ning sub
  on per.subject_id = sub.subject_id
;
--列出学生的姓名/科目名/成绩
select stu.student_name,
       sub.subject_name,
       per.test_score
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id
     join
     t_subject_ning sub
  on per.subject_id = sub.subject_id
;

--1班的学生成绩
select stu.student_name,
       sub.subject_name,
       per.test_score
from t_student_ning stu
     join
     t_performance_ning per
  on stu.student_id = per.student_id
     join
     t_subject_ning sub
  on per.subject_id = sub.subject_id
where stu.class_id = 1;

--1班学生的成绩的总分,并排序
select stu.student_name,
       sum(per.test_score) total_score
from t_student_ning stu
     join
     t_performance_ning per
     on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
order by total_score desc;
 

张无忌   张三丰
郭芙蓉   张无忌

--列出员工的名字和他的经理名字
--自连接
select worker.ename, manager.ename
from emp_ning worker
join emp_ning manager
on worker.manager = manager.empno;

update emp_ning
set deptno = null
where ename = 'tom';

--部门是null的员工不会被查出来
--没有员工的部门,也不会被查出来.
--内连接
select e.ename, d.dname
from emp_ning e join dept_ning d
on e.deptno = d.deptno;

--外连接: 左外连接, 右外连接
select e.ename, d.dname
from emp_ning e
     left outer join dept_ning d
on e.deptno = d.deptno;

select e.ename, d.dname
from dept_ning d
     right outer join emp_ning e
on e.deptno = d.deptno;

-- t1-驱动表
t1 left outer join t2
t2 right outer join t1

外连接的结果集 = 内连接的结果集 +
  驱动表中在匹配表中没有对应记录的记录和  空值的组合

--部门表做驱动表
select e.ename, d.dname
from emp_ning e right outer join
     dept_ning d
on e.deptno = d.deptno;
--等价的语句,部门表做驱动表
select e.ename, d.dname
from dept_ning d left outer join
     emp_ning e
on e.deptno = d.deptno;

--left / right 外连接 都可以
--关键是谁做驱动表.

--全外连接: full outer join
select e.ename, d.dname
from emp_ning e full outer join
     dept_ning d
on e.deptno = d.deptno;

--哪些部门没有员工?
--1.关联子查询实现
select dname, location
from dept_ning x
where not exists (
             select 1 from emp_ning
             where deptno = x.deptno)

--2.集合
select deptno from dept_ning
minus
select distinct deptno from emp_ning;

--3.外连接
--where 匹配表的pk is null = 驱动表中匹配不上的记录.
--相当于过滤掉内连接的结果集.
select e.empno, e.ename, d.deptno, d.dname, d.location
from emp_ning e right outer join dept_ning d
on e.deptno = d.deptno
where e.empno is null;

Top-N分析
--薪水最高的三个人?
伪列: rownum

--测试rownum的作用
select rownum, empno, ename, salary
from emp_ning;
--希望:先排序,再取前三条.
--实际:先取前三条,再排序.
select empno, ename, salary
from emp_ning
where salary is not null
and rownum < 4
order by salary desc;

--先排序,再取前三条.
select * from
(
select empno, ename, salary
from emp_ning
where salary is not null
order by salary desc)
where rownum < 4;

--计算学生总分: 学生表,成绩表
select *
from (
    select stu.student_name,
         sum(per.test_score) total_score
    from t_student_ning stu
         join
         t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = 1
    group by stu.student_name
    order by total_score desc)
where rownum < 2;

--输入:班号, 输出:该班的最高分
create or replace function maxScore_ning(p_class_id number)
return number
is
  v_total_score number;
begin
  --v_total_score赋值为指定班的最高分
select total_score into v_total_score
from (
    select stu.student_name,
         sum(per.test_score) total_score
    from t_student_ning stu
         join
         t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = p_class_id
    group by stu.student_name
    order by total_score desc)
where rownum < 2;
  return v_total_score;
end;
/

select stu.student_name, sum(per.test_score)
from t_student_ning stu
join t_performance_ning per
on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
having sum(per.test_score) = maxscore_ning(1);

--返回最高成绩的学生学号
create or replace function maxScore_ning1(p_class_id number)
return number
is
  v_student_id number;
begin
select student_id into v_student_id
from (
    select stu.student_id,
         stu.student_name,
         sum(per.test_score) total_score
    from t_student_ning stu
         join
         t_performance_ning per
    on stu.student_id = per.student_id
    where stu.class_id = p_class_id
    group by stu.student_id, stu.student_name
    order by total_score desc)
where rownum < 2;
  return v_student_id;
end;
/