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

--列出学生的姓名/科目名/成绩

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