华南俳烁实业有限公司

select distinct/列名/表達(dá)式/單行函數(shù)/組函數(shù)/

from 表名

where 條件(子查詢)1 or 條件2 and 條件3

group by 列名

having 組函數(shù)的條件

order by 列名/列別名/表達(dá)式/組函數(shù)

select deptno, count(*) c

from emp_ning

group by deptno

--order by c;

order by 2;

--每個班的學(xué)生人數(shù)?

1    4

2    2

3    73

4    65

5    48

--把班級人數(shù)>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;

--每個學(xué)生的總分?按總分由高到低排序

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號學(xué)生的總成績高?

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

--誰的薪水比本部門的平均薪水低?

--關(guān)聯(lián)子查詢

select ename, salary, deptno

from emp_ning x

where salary < (

         select avg(nvl(salary,0))

         from emp_ning

         where deptno = x.deptno         

         );

--誰的薪水比同經(jīng)理的員工平均薪水低?

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

.....

--哪些員工是別人的經(jīng)理?

exists: 子查詢是否有結(jié)果返回

        有  true

        沒有 false

select empno, ename

from emp_ning x

where exists (select 1

              from emp_ning

              where manager = x.empno);

--哪些人不是別人的經(jīng)理?

select empno, ename

from emp_ning x

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

--哪些部門沒有員工?

--判斷依據(jù): 部門表的編碼,沒有出現(xiàn)在職員表的部門編碼列中.

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

二.多表聯(lián)合查詢

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

--列值是唯一的,不重復(fù)的

--主表 / 父表

--外鍵: Foreign Key = FK

--列值參照某個主鍵列值

--從表 / 子表

--查詢學(xué)生的名字,科目,成績

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 ;

--列出學(xué)生的姓名/科目名/成績

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班的學(xué)生成績

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班學(xué)生的成績的總分,并排序

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;

張無忌   張三豐

郭芙蓉   張無忌

--列出員工的名字和他的經(jīng)理名字

--自連接

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的員工不會被查出來

--沒有員工的部門,也不會被查出來.

--內(nèi)連接

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-驅(qū)動表

t1 left outer join t2

t2 right outer join t1

外連接的結(jié)果集 = 內(nèi)連接的結(jié)果集 +

  驅(qū)動表中在匹配表中沒有對應(yīng)記錄的記錄和  空值的組合

--部門表做驅(qū)動表

select e.ename, d.dname

from emp_ning e right outer join dept_ning d

on e.deptno = d.deptno;

--等價的語句,部門表做驅(qū)動表

select e.ename, d.dname

from dept_ning d left outer join emp_ning e

on e.deptno = d.deptno;

--left / right 外連接 都可以

--關(guān)鍵是誰做驅(qū)動表.

--全外連接: full outer join

select e.ename, d.dname

from emp_ning e full outer join dept_ning d

on e.deptno = d.deptno;

--哪些部門沒有員工?

--1.關(guān)聯(lián)子查詢實現(xiàn)

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 = 驅(qū)動表中匹配不上的記錄.

--相當(dāng)于過濾掉內(nèi)連接的結(jié)果集.

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;

--計算學(xué)生總分: 學(xué)生表,成績表

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

--返回最高成績的學(xué)生學(xué)號

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;