orader by:就是排序,默认升序,desc就是降序。
group by:分组,前面必须有聚合函数,分组后面跟的是非聚合列,sql语句循序优先于orader by。
having :用于聚合列的筛选。
举例说明:
SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments group by tablespace_name;
TABLESPACE_NAME SUM(EXTENTS) SUM(BLOCKS) TOTAL_MB
------------------------------------------------------------ ------------ ----------- ----------
SYSAUX 4859 92848 725.375
EAS_D_TEST0319_STANDARD 20677 2270920 17741.5625
UNDOTBS1 128 4864 38
EAS_D_ZYWYDB_STANDARD 23819 2674256 20892.625
EAS_D_TEST0319_TEMP2 776 6208 48.5
EAS_D_ZYTEST0228_STANDARD 25870 2276664 17786.4375
USERS 6 48 .375
SYSTEM 3461 164544 1285.5
EAS_D_ZYWYDB_TEMP2 7996 63968 499.75
EAS_D_ZYDB_STANDARD 20393 2332464 18222.375
EAS_D_ZYTEST0228_TEMP2 5445 43560 340.3125
EAS_D_TEST1201_TEMP2 5820 46560 363.75
EAS_D_ZYDB_TEMP2 7999 63992 499.9375
EAS_D_TEST1201_STANDARD 20705 2046832 15990.875
PERFSTAT 2308 63840 498.75
已选择15行。
SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments order by tablespace_name group by tablespace_name;
select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments order by tablespace_name group by tablespace_name
*
第 1 行出现错误:
ORA-00933: SQL command not properly ended
SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments group by tablespace_name order by tablespace_name;
TABLESPACE_NAME SUM(EXTENTS) SUM(BLOCKS) TOTAL_MB
------------------------------------------------------------ ------------ ----------- ----------
EAS_D_TEST0319_STANDARD 20677 2270920 17741.5625
EAS_D_TEST0319_TEMP2 776 6208 48.5
EAS_D_TEST1201_STANDARD 20705 2046832 15990.875
EAS_D_TEST1201_TEMP2 5820 46560 363.75
EAS_D_ZYDB_STANDARD 20393 2332464 18222.375
EAS_D_ZYDB_TEMP2 7999 63992 499.9375
EAS_D_ZYTEST0228_STANDARD 25870 2276664 17786.4375
EAS_D_ZYTEST0228_TEMP2 5445 43560 340.3125
EAS_D_ZYWYDB_STANDARD 23819 2674256 20892.625
EAS_D_ZYWYDB_TEMP2 7996 63968 499.75
PERFSTAT 2308 63840 498.75
SYSAUX 4859 92848 725.375
SYSTEM 3461 164544 1285.5
UNDOTBS1 128 4864 38
USERS 6 48 .375
已选择15行。
SQL> select tablespace_name,sum(extents),sum(blocks),sum(bytes)/1024/1024 total_MB from dba_segments group by tablespace_name having sum(blocks) > 200000
2 order by tablespace_name;
TABLESPACE_NAME SUM(EXTENTS) SUM(BLOCKS) TOTAL_MB
------------------------------------------------------------ ------------ ----------- ----------
EAS_D_TEST0319_STANDARD 20677 2270920 17741.5625
EAS_D_TEST1201_STANDARD 20705 2046832 15990.875
EAS_D_ZYDB_STANDARD 20393 2332464 18222.375
EAS_D_ZYTEST0228_STANDARD 25870 2276664 17786.4375
EAS_D_ZYWYDB_STANDARD 23819 2674256 20892.625