ORACLE－SELECT学习

(一)select格式：
SELECT [ ALL ｜ DISTINCT ] <字段表达式1[,<字段表达式2[,…]
FROM <表名1>,<表名2>[，…]
[WHERE <筛选择条件表达式>]
[GROUP BY <分组表达式> [HAVING<分组条件表达式>]]
[ORDER BY <字段>[ASC ｜ DESC]]

语句说明：
[]方括号为可选项
[GROUP BY <分组表达式> [HAVING<分组条件表达式>]]
指将结果按<分组表达式>的值进行分组，该值相等的记录为一组，带【HAVING】
短语则只有满足指定条件的组才会输出。
[ORDER BY <字段>[ASC ｜ DESC]]
显示结果要按<字段>值升序或降序进行排序

sql各子句的执行顺序：             

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY 

(二)在ORACLE中实现Select TOP N

由于ORACLE不支持Select TOP语句，所以在ORACLE中经常是用ORDER BY跟ROWNUM的组合来实现Select TOP N的查询。

   简单地说，实现方法如下所示：

   Select　列名１．．．列名ｎ　FROM

     (Select　列名１．．．列名ｎ　FROM 表名 orDER BY 列名１．．．列名ｎ)

    Where ROWNUM <= N（抽出记录数）

   orDER BY ROWNUM ASC

    下面举个例子简单说明一下。

   顾客表customer(id,name)有如下数据：

   ID NAME

    01 first

    02 Second

    03 third

    04 forth

    05 fifth

    06 sixth

    07 seventh

    08 eighth

    09 ninth

    10 tenth

    11 last

    则按NAME的字母顺抽出前三个顾客的SQL语句如下所示：

   Select * FROM

     (Select * FROM CUSTOMER orDER BY NAME)

    Where ROWNUM <= 3

    orDER BY ROWNUM ASC

    输出结果为：

   ID NAME

    08 eighth

    05 fifth

    01 first

2.在TOP N纪录中抽出第M（M <= N）条记录

   在得到了TOP N的数据之后，为了抽出这N条记录中的第M条记录，我们可以考虑从ROWNUM着手。我们知道，ROWNUM是记录表中数据编号的一个隐藏子段，所以可以 在得到TOP N条记录的时候同时抽出记录的ROWNUM，然后再从这N条记录中抽取记录编号为M的记录，即使我们希望得到的结果。

   从上面的分析可以很容易得到下面的SQL语句。

   Select 列名１．．．列名ｎ FROM

      (

      Select ROWNUM RECNO, 列名１．．．列名ｎFROM

        (Select 列名１．．．列名ｎ FROM 表名 orDER BY 列名１．．．列名ｎ)

      Where ROWNUM <= N（抽出记录数）

    orDER BY ROWNUM ASC

      )

    Where RECNO = M（M <= N）

   同样以上表的数据为基础，那么得到以NAME的字母顺排序的第二个顾客的信息的SQL语句应该这样写：

    Select ID, NAME FROM

      (

       Select ROWNUM RECNO, ID, NAME FROM

         (Select * FROM CUSTOMER orDER BY NAME)

          Where ROWNUM <= 3

          orDER BY ROWNUM ASC )

        Where RECNO = 2

      结果则为：

    ID NAME

     05 fifth

3.抽出按某种方式排序的记录集中的第N条记录

    在2的说明中，当M = N的时候，即为我们的标题讲的结果。实际上，2的做法在里面N>M的部分的数据是基本上不会用到的，我们仅仅是为了说明方便而采用。

    如上所述，则SQL语句应为：

    Select 列名１．．．列名ｎ FROM

      (

       Select ROWNUM RECNO, 列名１．．．列名ｎFROM

         (Select 列名１．．．列名ｎ FROM 表名 orDER BY 列名１．．．列名ｎ)

          Where ROWNUM <= N（抽出记录数）

       orDER BY ROWNUM ASC

      )

      Where RECNO = N

      那么，2中的例子的SQL语句则为：

     Select ID, NAME FROM

       (

        Select ROWNUM RECNO, ID, NAME FROM

          (Select * FROM CUSTOMER orDER BY NAME)

        Where ROWNUM <= 2

        orDER BY ROWNUM ASC

       )

       Where RECNO = 2

      结果为：

    ID NAME

     05 fifth

4.抽出按某种方式排序的记录集中的第M条记录开始的X条记录

   3里所讲得仅仅是抽取一条记录的情况，当我们需要抽取多条记录的时候，此时在2中的N的取值应该是在N >= (M + X - 1)这个范围内，当让最经济的取值就是取等好的时候了的时候了。当然最后的抽取条件也不是RECNO = N了，应该是RECNO BETWEEN M AND (M + X - 1)了，所以随之而来的SQL语句则为：

    Select 列名１．．．列名ｎ FROM

     (

      Select ROWNUM RECNO, 列名１．．．列名ｎFROM

       (

       Select 列名１．．．列名ｎ FROM 表名 orDER BY 列名１．．．列名ｎ)

       Where ROWNUM <= N （N >= (M + X - 1)）

     orDER BY ROWNUM ASC

       )

      Where RECNO BETWEEN M AND (M + X - 1)

     同样以上面的数据为例，则抽取NAME的字母顺的第2条记录开始的3条记录的SQL语句为：

    Select ID, NAME FROM

      (

       Select ROWNUM RECNO, ID, NAME FROM

         (Select * FROM CUSTOMER orDER BY NAME)

       Where ROWNUM <= (2 + 3 - 1)

       orDER BY ROWNUM ASC

      )

      Where RECNO BETWEEN 2 AND (2 + 3 - 1)

      结果如下：

    ID NAME

     05 fifth

     01 first

     04 forth

     以此为基础，再扩展的话，做成存储过程，将开始记录数以及抽取记录数为参数，就可以轻松实现分页抽取数据

(三)分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

下面是Copy的一个Oracle分页的存储过程：

create or replace procedure KnowledgePagerCount(p_PageSize number,          --每页记录数
                  p_PageNo number,            --当前页码,从 1 开始
                  p_SqlSelect varchar2,    --查询语句,含排序部分
                  p_OutRecordCount out number,--返回总记录数
                  cur_OUT out GM.PAGER.refCursorType)
as
    v_sql varchar2(3000);
    v_count number;
    v_heiRownum number;
    v_lowRownum number;
begin
----取记录总数
execute immediate p_SqlSelect into v_count;
p_OutRecordCount := v_count;
----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize + 1;

v_sql := 'SELECT *
            FROM (
                  SELECT A.*, rownum rn
                  FROM ('|| p_SqlSelect ||') A
                  WHERE rownum <= '|| to_char(v_heiRownum) || '
                 ) B
            WHERE rn >= ' || to_char(v_lowRownum) ;
            --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

OPEN cur_OUT FOR v_sql;

end KnowledgePagerCount;

(四)SELECT CREATE Views group by 的使用

select col_name as col_alias from table_name ;

select col_name from table_name where col1 like '_o%'; ----'_'匹配单个字符

/*使用字符函数(右边截取,字段中包含某个字符,左边填充某字符到固定位数,右边填充某字符到固定位数)*/

select substr(col1,-3,5),instr(col2,'g'),LPAD(col3,10,'$'),RPAD(col4,10,'%') from table_name;

/*使用数字函数(往右/左几位四舍五入,取整,取余)*/

select round(col1,-2),trunc(col2),mod(col3) from table_name ;

/*使用日期函数(计算两个日期间相差几个星期,两个日期间相隔几个月,在某个月份上加几个月,某个日期的下一个日期,

某日期所在月的最后的日期,对某个日期的月分四舍五入，对某个日期的月份进行取整)*/

select (sysdate-col1)/7 week,months_between(sysdate,col1),add_months(col1,2),next_day(sysdate,'FRIDAY'),last_day(sysdate),

round(sysdate,'MONTH'),trunc(sysdate,'MONTH') from table_name;

/*使用NULL函数(当expr1为空取expr2/当expr1为空取expr2,否则取expr3/当expr1=expr2返回空)*/

select nvl(expr1,expr2),nvl2(expr1,expr2,expr3),nullif(expr1,expr2) from table_name;

select column1,column2,column3, case column2 when '50' then column2*1.1

when '30' then column2*2.1

when '10' then column3/20

else column3

end as ttt

from table_name ; ------使用case函数

select table1.col1,table2.col2 from table1

[CROSS JOIN table2] | -----笛卡儿连接

[NATURAL JOIN table2] | -----用两个表中的同名列连接

[JOIN table2 USING (column_name)] | -----用两个表中的同名列中的某一列或几列连接

[JOIN table2

ON (table1.col1=table2.col2)] |

[LEFT|RIGHT|FULL OUTER JOIN table2 ------相当于(+)=,=(+)连接,全外连接

ON (table1.col1=table2.col2)]; ------SQL 1999中的JOIN语法;

##################### CREATE/ALTER TABLE #######################

alter table table_name drop column column_name ;---drop column

alter table table_name set unused (col1,col2,...);----设置列无效，这个比较快。

alter table table_name drop unused columns;---删除被设为无效的列

rename table_name1 to table_name2; ---重命名表

comment on table table_name is 'comment message';----给表放入注释信息

create table table_name

(col1 int not null,col2 varchar2(20),col3 varchar2(20),

constraint uk_test2_1 unique(col2,col3))); -----定义表中的约束条件

alter table table_name add constraint pk_test2 primary key(col1,col2,...); ----创建主键

/*建立外键*/

create table table_name (rid int,name varchar2(20),constraint fk_test3 foreign key(rid) references other_table_name(id));

alter table table_name add constraint ck_test3 check(name like 'K%');

alter table table_name drop constraint constraint_name;

alter table table_name drop primary key cascade;----级联删除主键

alter table table_name disable/enable constraint constraint_name;----使约束暂时无效

/*删除列，并级联删除此列下的约束条件*/

alter table table_name drop column column_name cascade constraint;

select * from user_constraints/user_cons_columns;---约束条件相关视图

############## Create Views #####################

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[,alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint_name]]

[WITH READ ONLY [CONSTRAINT constraint_name]]; ------创建视图的语法

example: Create or replace view testview as select col1,col2,col3 from table_name; ------创建视图

/*使用别名*/

Create or replace view testview as select col1,sum(col2) col2_alias from table_name;

/*创建复杂视图*/

Create view view_name (alias1,alias2,alias3,alias4) as select d.col1,min(e.col1),max(e.col1),avg(e.col1) from table_name1 e,table_name2 d where e.col2=d.col2 group by d.col1;

/*当用update修改数据时，必须满足视图的col1>10的条件，不满足则不能被改变.*/

Create or replace view view_name as select * from table_name where col1>10 with check option;

/*改变视图的值.对于简单视图可以用update语法修改表数据，但复杂视图则不一定能改。如使用了函数，group by ,distinct等的列*/

update view_name set col1=value1;

########################## 增强的 group by 子句 #########################

select [column,] group_function(column)...

from table

[WHERE condition]

[GROUP BY [ROLLUP] group_by_expression]

[HAVING having_expression];

[ORDER BY column]; -------ROLLUP操作字，对group by子句的各字段从右到左进行再聚合

example:

/*其结果看起来象对col1做小计后，再对col2做小计，最后算总计*/

select col1,col2,sum(col3) from table group by cube(col1,col2);

/*复合rollup表达式*/

select col1,col2,sum(col3) from table group by cube((col1,col2));

/*混合rollup,cube表达式*/

select col1,col2,col3,sum(col4) from table group by col1,rollup(col2),cube(col3);

/*GROUPING(expr)函数，查看select语句种以何字段聚合，其取值为0或1*/

select [column,] group_function(column)...,GROUPING(expr)

from table

[WHERE condition]

[GROUP BY [ROLLUP] group_by_expression]

[HAVING having_expression];

[ORDER BY column];

(五)oracle的select for update

　 在多数情况下，提取循环中所完成的处理都会修改由游标检查出的行，PL/SQL提供了进行这样处理的一种语法。

    这种语法包括两部分——在游标声明部分的FOR UPDATE子句和在UPDATE或DELETE语句中的WHERE CURRENT OF 子句。

    通常,SELECT操作将不会对正处理的行执行任何锁定设置，这使得连接到该数据库的其他会话可以改变正在选择的数据。

    但是，结果集仍然是一致性的。当确定了活动集以后，在执行OPEN的时刻，ORACLE会截取下该表的一个快照。在此时刻以前所提交的任何更改操作都会在活 动集中反映出来。在此时刻以后所进行的任何更改操作，即使已经提交了它们，都不会被反映出来，除非将该游标重新打开。但是使用FOR UPDATE子句， 在OPEN返回以前的活动集的相应行上会加上互斥锁，这些锁会避免其他的会话对活动集中的行进行更改。直到整个事务被提交为止。

示例：
DECLARE
CURSOR C_CUR IS SELECT * FROM STUDENDS FOR UPDATE OF XM;
BEGIN
OPEN C_CUR;
WHILE C_CUR%FOUND LOOP

UPDATE STUDENDS SET XM='AA'||XM WHERE CURRENT OF C_CUR;

END LOOP;
CLOSE C_CUR;
COMMIT;
END;


需要注意的是：

1、UPDATE语句仅更新在游标声明的FOR UPDATE子句处列出的列。如果没有列出任何列，那么所有的列都可以更新。
2、示例中的COMMIT是在提取循环完成以后才完成的，因为COMMIT将释放由该会话持有的所有锁。因为FOR UPDATE子句获得了锁，所以COMMIT将释放这些锁。当锁释放了，该游标就无效了。所以后继的提取操作都将返回ORACLE错误。