RUI个人博客 首页>>Oracle Basic Knowledge>>[杂] sql相关笔记整理汇总,杂 2~

[杂] sql相关笔记整理汇总,杂 2~


紧接上一篇文档:

                sql相关笔记整理汇总,杂 1~

 http://www.5ibig.com/article/index/arcid/52.htm

本文档:sql相关笔记整理汇总,杂 2~

 http://www.5ibig.com/article/index/arcid/53.htm

SQL语句编译顺序:(from -->where -->group by -->having -->select -->order by)

组函数 函数说明
avg([distinct|all] expr) 表示计算 expr 的平均值    自动忽略 null值
sum([distinct|all] expr)
表示计算 expr 的总计值    自动忽略 null值
max([distinct|all] expr)
表示计算 expr 的最大值    自动忽略 null值
min([distinct|all] expr)
表示计算 expr 的最小值    自动忽略 null值
count(*|[distinct|all] expr)
表示计算表中数据的总数    计算 null值
group by 
创建组函数     组函数不能用于where子句,但可用于having子句
where子句
可以使用 where 子句对分组前的数据进行筛选
having子句
可以使用having子句对分组后的数据进行筛选

没有group by情况下,会将全部数据作为一组进行统计,然后通过having进行筛选
SCOTT@RACDB1>select sum(sal),count(*),avg(sal)
  2  from emp
  3  having avg(sal) <2500;

  SUM(SAL)   COUNT(*)   AVG(SAL)
---------- ---------- ----------
     29025         14 2073.21429
SCOTT@RACDB1>select sum(sal),count(*),avg(sal)
  2  from emp
  3  having avg(sal) <1500;

no rows selected
子查询 思路分析
1.select 子句进行逻辑嵌套   子查询可用于 from、where、having子句
单行子查询 -- 子查询的值返回一个
在  emp  表中哪个部门的平均工资最高?
step 1 :查询最高的平均工资是多少?
step 2 :查询哪个部门的平均工资等于最高的平均工资;
step 3 :查询部门 0 10  所对应的名字;

多行子查询 -- 子查询的值返回多个
对于多行子查询,需应用多行运算符(in、any、all)
in (多行子查询)    表示外层条件属于集合中的任意成员;
any (多行子查询) 表示外层条件只需满足子查询的任意一个值就行

all (多行子查询)   表示外层条件必须需满足子查询的全部的值才行

5. Merge操作 (相当于update 和 insert操作二合一)
SCOTT@RACDB1>merge into fd f
  2  using zd z
  3  on (f.fsp_id = z.zsp_id)
  4  when matched then
  5    update set f.fsp_price = z.zsp_price
  6  when not matched then
  7    insert values(z.zsp_id,z.zsp_name,z.zsp_price);
foreign key约束
外键是父表中的主键存在的,是逻辑的约束关系
不能创建不存在主键的外键

not null 约束只能在列级添加,并且只能通过modify命令进行滞后添加;(不能用add命令)
除 not null之外的约束,可以在列级或表级进行定义

视图只是观看基表数据的一个窗口,里面不包含真正的数据,真正的数据依然在基表中;
如果基表的数据发生变化,那么通过视图查看的时候也会看到修改后的结果

提示:如果视图中包含了以下 
 a) 、 b) 、 c) 时,不能添加、更新、删除基表的数据;
包含 d)时,不能添加、更新基表的数据;
包含 e)时,不能添加基表的数据;
a) 组函数   不能添加、更新、删除基表的数据;
注意:在组函数之后要定义列的别名,否则报错!
create view v032
    as select sum(sal),avg(sal) from emp; 
as select sum(sal),avg(sal) from emp
          *
ERROR at line 2:
ORA-00998: must name this expression with a column alias

create view v03 
    as select sum(sal) s,avg(sal) a from emp;
SCOTT@RACDB1>select * from v03;

         S          A
---------- ----------
     39024     2601.6

delete v03 where s<30000;
delete v03 where s<30000
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
b) group by 子句    不能添加、更新、删除基表的数据;
create view v04
    as select deptno,sum(sal) s,count(*) c,avg(sal) a
    from emp
    group by deptno
    order by deptno;

View created.

select * from v04;

    DEPTNO          S          C          A
---------- ---------- ---------- ----------
        10       8750          3 2916.66667
        20      10875          5       2175
        30       9400          6 1566.66667
        40       9999          1       9999

insert into v04 values(40,9999,4,44);
insert into v04 values(40,9999,4,44)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here

delete v04 where deptno=30;
delete v04 where deptno=30
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
c) distinct 关键字   不能添加、更新、删除基表的数据;
create view v05
    as select distinct deptno from emp;
View created.

set lines 100
desc v05
 Name                          Null?    Type
 --------------------------- -------- ------------------------------------
 DEPTNO                                 NUMBER(2)

select * from v05;

    DEPTNO
----------
        30
        20
        40
        10

SCOTT@RACDB1>insert into v05 values(30);
insert into v05 values(30)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SCOTT@RACDB1>delete v05 where deptno=20;
delete v05 where deptno=20
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
d) 视图中包含通过基表中的字段表达式来的信息,基表的数据不能更新或者添加,可以删除
create or replace view v06
    as select empno,ename,sal * 12 + nvl(comm,0) as newcomm from emp;

View created.


SCOTT@RACDB1>set lines 60
SCOTT@RACDB1>desc v06
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 EMPNO                         NOT NULL NUMBER(4)
 ENAME                                  VARCHAR2(10)
 NEWCOMM                                NUMBER

SCOTT@RACDB1>select * from v06;

     EMPNO ENAME         NEWCOMM
---------- ---------- ----------
      9999 TYGER          119988
      7369 SMITH            9600
      7499 ALLEN           19500
      7521 WARD            15500
      7566 JONES           35700
      7654 MARTIN          16400
      7698 BLAKE           34200
      7782 CLARK           29400
      7788 SCOTT           36000
      7839 KING            60000
      7844 TURNER          18000
      7876 ADAMS           13200
      7900 JAMES           11400
      7902 FORD            36000
      7934 MILLER          15600

15 rows selected.

SCOTT@RACDB1>insert into v06 values(8888,'WANG',33333);
insert into v06 values(8888,'WANG',33333)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


SCOTT@RACDB1>update v06 set NEWCOMM=999999 where empno=7369;
update v06 set NEWCOMM=999999 where empno=7369
               *
ERROR at line 1:
ORA-01733: virtual column not allowed here


SCOTT@RACDB1>delete v06 where empno=7369;

1 row deleted.

SCOTT@RACDB1>select * from emp;  --基表已经没有empno=7369的数据

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      9999 TYGER      WANG            9999 30-MAY-16          9999                    40
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.
e) 视图中不包含基表中被定义为not null的列     不能添加基表的数据;
SCOTT@RACDB1>create table tyger(id number(3),name varchar2(20) not null,sdate date);

Table created.

SCOTT@RACDB1>set lines 60
SCOTT@RACDB1>desc tyger
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 ID                                     NUMBER(3)
 NAME                          NOT NULL VARCHAR2(20)
 SDATE                                  DATE

SCOTT@RACDB1>create view v07
  2  as select id,sdate from tyger;

View created.

SCOTT@RACDB1>desc v07
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 ID                                     NUMBER(3)
 SDATE                                  DATE

SCOTT@RACDB1>insert into v07 values(3,sysdate);
insert into v07 values(3,sysdate)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TYGER"."NAME")


SCOTT@RACDB1>insert into tyger values(1,'hui',sysdate);

1 row created.

SCOTT@RACDB1>select * from v07;

        ID SDATE
---------- ------------
         1 30-MAY-16

SCOTT@RACDB1>update v07 set sdate=sysdate+1 where id=1;

1 row updated.

SCOTT@RACDB1>select * from v07;

        ID SDATE
---------- ------------
         1 31-MAY-16

SCOTT@RACDB1>select * from tyger;

        ID NAME                 SDATE
---------- -------------------- ------------
         1 hui                  31-MAY-16

SCOTT@RACDB1>delete v07;

1 row deleted.

SCOTT@RACDB1>select * from v07;

no rows selected

SCOTT@RACDB1>select * from tyger;

no rows selected
with check option 子句
    -- 满足条件的数据才能通过视图看到,其他的看不到
SCOTT@RACDB1>create or replace view v08
  2  as select * from emp where deptno=20 with check option;

View created.

SCOTT@RACDB1>desc v08;      
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                         NOT NULL NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

SCOTT@RACDB1>set lines 160
SCOTT@RACDB1>select * from v08;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20

SCOTT@RACDB1>update v08 set deptno=30;
update v08 set deptno=30
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


SCOTT@RACDB1>update v08 set ename='ttttt' where deptno=10;

0 rows updated.

SCOTT@RACDB1>insert into v08(empno,ename,hiredate,deptno) values(7777,'WANGTYGER',SYSDATE,10);
insert into v08(empno,ename,hiredate,deptno) values(7777,'WANGTYGER',SYSDATE,10)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


SCOTT@RACDB1>insert into v08(empno,ename,hiredate,deptno) values(7777,'WANGTYGER',SYSDATE,20);

1 row created.

SCOTT@RACDB1>select * from v08;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7777 WANGTYGER                       30-MAY-16                                  20
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20

SCOTT@RACDB1>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      9999 TYGER      WANG            9999 30-MAY-16          9999                    40
      7777 WANGTYGER                       30-MAY-16                                  20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

15 rows selected.
小结: row_number() 生成的行号与 rownum  之间的区别;
1.row_number() 可以按照指定的字段,指定顺序,生成相应的排序号;
2.而 rownum  是按照数据进入表中的时间顺序, 由 Oracle 自定义的顺序号, 可能会因为数据的更新、
删除而自动变化;
SCOTT@RACDB1>select empno,ename,sal,rownum,row_number() over(order by sal desc) new_rownum
  2  from emp;

     EMPNO ENAME             SAL     ROWNUM NEW_ROWNUM
---------- ---------- ---------- ---------- ----------
      7839 KING             5000          9          1
      7902 FORD             3000         13          2
      7788 SCOTT            3000          8          3
      7566 JONES            2975          4          4
      7698 BLAKE            2850          6          5
      7782 CLARK            2450          7          6
      7499 ALLEN            1600          2          7
      7844 TURNER           1500         10          8
      7934 MILLER           1300         14          9
      7521 WARD             1250          3         10
      7654 MARTIN           1250          5         11
      7876 ADAMS            1100         11         12
      7900 JAMES             950         12         13
      7369 SMITH             800          1         14

14 rows selected.
rollup: 对group by 的分组结果进行小计和总计;   
先通过第1个字段(deptno)进行小计,最后再形成总计;
SCOTT@RACDB1>select deptno,job,sum(sal),count(*),avg(sal)
  2  from emp
  3  group by rollup(deptno,job)
  4  order by deptno;

    DEPTNO JOB         SUM(SAL)   COUNT(*)   AVG(SAL)
---------- --------- ---------- ---------- ----------
        10 CLERK           1300          1       1300
        10 MANAGER         2450          1       2450
        10 PRESIDENT       5000          1       5000
        10                 8750          3 2916.66667
        20 ANALYST         6000          2       3000
        20 CLERK           1900          2        950
        20 MANAGER         2975          1       2975
        20                10875          5       2175
        30 CLERK            950          1        950
        30 MANAGER         2850          1       2850
        30 SALESMAN        5600          4       1400
        30                 9400          6 1566.66667
                          29025         14 2073.21429

13 rows selected.
cube: 对group by 的分组结果进行小计和总计
 按 cube 函数中的每个字段进行小计,最后再形成总计;
 -- 比rollup多出来的数据就是按照 job 列进行的小计。
SCOTT@RACDB1>select deptno,job,sum(sal),count(*),avg(sal)
  2  from emp
  3  group by cube(deptno,job)
  4  order by deptno;

    DEPTNO JOB         SUM(SAL)   COUNT(*)   AVG(SAL)
---------- --------- ---------- ---------- ----------
        10 CLERK           1300          1       1300
        10 MANAGER         2450          1       2450
        10 PRESIDENT       5000          1       5000
        10                 8750          3 2916.66667
        20 ANALYST         6000          2       3000
        20 CLERK           1900          2        950
        20 MANAGER         2975          1       2975
        20                10875          5       2175
        30 CLERK            950          1        950
        30 MANAGER         2850          1       2850
        30 SALESMAN        5600          4       1400
        30                 9400          6 1566.66667
           ANALYST         6000          2       3000
           CLERK           4150          4     1037.5
           MANAGER         8275          3 2758.33333
           PRESIDENT       5000          1       5000
           SALESMAN        5600          4       1400
                          29025         14 2073.21429

18 rows selected.
grouping(): 解释小计值出现对应结果的原因
 0:表示取得组函数结果的时候,该字段参与了条件运算
1: 表示取得组函数结果的时候,该字段未参与条件运算

SCOTT@RACDB1>select deptno,job,sum(sal),count(*),avg(sal),

         grouping(deptno) g1,grouping(job) g2

    from emp
    group by cube(deptno,job)
    order by deptno;

    DEPTNO JOB         SUM(SAL)   COUNT(*)   AVG(SAL)         G1         G2
---------- --------- ---------- ---------- ---------- ---------- ----------
        10 CLERK           1300          1       1300          0          0
        10 MANAGER         2450          1       2450          0          0
        10 PRESIDENT       5000          1       5000          0          0
        10                 8750          3 2916.66667          0          1
        20 ANALYST         6000          2       3000          0          0
        20 CLERK           1900          2        950          0          0
        20 MANAGER         2975          1       2975          0          0
        20                10875          5       2175          0          1
        30 CLERK            950          1        950          0          0
        30 MANAGER         2850          1       2850          0          0
        30 SALESMAN        5600          4       1400          0          0
        30                 9400          6 1566.66667          0          1
           ANALYST         6000          2       3000          1          0
           CLERK           4150          4     1037.5          1          0
           MANAGER         8275          3 2758.33333          1          0
           PRESIDENT       5000          1       5000          1          0
           SALESMAN        5600          4       1400          1          0
                          29025         14 2073.21429          1          1

18 rows selected.

   于 2016-05-25 整理于 北京邮储  tyger.wang
昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码