Oracle相关技术

傷城~ 2022-08-06 10:25 172阅读 0赞

1.  Distinct消除重复行数据: SELECT DISTINCT DEPTNO FROM EMP
2.  NULL操作  
    如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。  
    任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。  
    在查询条件中NULL值用IS NULL作条件，非NULL值用IS NOT NULL做条件： SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000 AND COMM IS NULL;
3.  IN 操作在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行。对应IN操作的还有NOT IN，用法一样，结果相反。： SELECT ENAME,JOB,SAL FROM EMP   WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST');
4.  BETWEEN…AND
    
    在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。BWTWEEN操作所指定的范围也包括边界。SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000

[oracle 集合运算][oracle]

*  集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

INTERSECT(交集)，返回两个查询共有的记录。  
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。  
UNION(并集)，返回各个查询的所有记录，不包括重复记录。  
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

例子:查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可

SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP

*  把一个结果集插入到另一张结构相同的表中 ，使用union把若干条记录一次性插入到一张表中

**INSERT**   **INTO**  DEPT     **SELECT**  50, '公关部' , '台湾'   **FROM**  DUAL     **UNION**       **SELECT**  60, '研发部' , '西安'   **FROM**  DUAL     **UNION**      **SELECT**  70, '培训部' , '西安'   **FROM**  DUAL

*  like模糊查询

一般like模糊查询 如下：

select b.\* from b\_city\_info b where b.c\_name like '%西藏%';---用时:0.062 , 此查询将会进行全表扫描

修改为如下的语句:

select b.\* from b\_city\_info b where b.c\_name like '西藏%';--用时：0.009

查询执行计划将会变成 （INDEX RANGE SCAN），成功的利用了name字段的索引

使用rowId实现分页

\--分页第一步 获取数据物理地址

select t.rowid rid, t.c\_id from b\_city\_info t order by t.c\_id desc;

\--分页第二步 取得最大页数

select rownum rn, rid

from (select t.rowid rid, t.c\_id from b\_city\_info t order by t.c\_id desc)

where rownum <= 10;

--分页第三步 取得最小页数

select rn,rid

from (select rownum rn, rid

from (select t.rowid rid, t.c\_id from b\_city\_info t order by t.c\_id desc)

where rownum <= 10)

where rn > 5;

--分页第四步 再根据物理地址，查询出具体数据

select t1.\*,t1.rowid

from b\_city\_info t1

where t1.rowid in

(select dt.rid

from (select rownum rn, rid

from (select t.rowid rid, t.c\_id from b\_city\_info t order by t.c\_id desc)

where rownum <= 10000)dt

where rn > 5000);

Oracle日期和字符串、数字的格式化

1.  select SYSDATE from dual t;--系统日期
2.  select TO\_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') from dual t;--格式化日期
3.  select TO\_DATE('20140630 18:28:20','YYYY/MM/DD HH24:MI:SS') from dual t;--格式化日期
4.  select sysdate,sysdate - interval '7' MINUTE from dual;--当前时间减去7分钟的时间
5.  select sysdate - interval '7' hour from dual;--当前时间减去7小时的时间
6.  select sysdate - interval '7' day from dual;--当前时间减去7天的时间 
7.  select sysdate,sysdate - interval '7' month from dual;--当前时间减去7月的时间
8.  select sysdate,sysdate - interval '7' year from dual;--当前时间减去7年的时间
9.  select sysdate,sysdate - 8\*interval '7' hour from dual;--时间间隔乘以一个数字
10. select to\_number('222.2') from dual t;--输出222.2
11. select to\_char(10000000000,'$999,999,999,99') from dual;--按格式输出
12. select to\_char(1000000,'L99,999,99') from dual;--输出 RMB10,000,00
13. select trunc(to\_number('1000000.123'),3) from dual;--保留三位有效数字

*  Dual虚拟表,只有一列

当查询时间或者日期这些对象的时候就需要使用这个表

select sysdate from dual

查询当前连接用户

select user from dual;

*  [把字符串按照分割符分解成不同的部分][Link 1]

select regexp\_substr('A,B,C,D,E', '\[^,\]+', 1, level, 'i') as str

from dual

connect by level <=

length('A,B,C,D,E') - length(regexp\_replace('A,B,C,D,E', ',', '')) + 1;

*  将一张表的数据导入到另一张表中

前提:两表结构一样

insert into 表A select \* from 表B

如果两表的数据结构不一样

INSERT INTO 表1(字段1,字段2,字段n)  
select 表2.字段1,表2.字段2,表2.字段n from 表2

*  [oracle 表数据恢复][oracle 1]

第一中方式：创建一个备份表，存放三小时前的数据

create table tabletest\_2014bak nologging as

select \* from tabletest as of timestamp sysdate-21/24 where name != 'boat';

第二种方式：回复表tabletest中的数据到某一个时间点，这个时间点一定要前于当前时间

alter table tabletest enable row movement;

Flashback table tabletest to timestamp to\_timestamp('2014-12-30 17:00:00','yyyy-mm-dd hh24:mi:ss');

*  Oracle修改原始表中字段的数据类型

方法一：

// 创建一样的临时表

create table T\_BUSINESS\_MODEL\_TEMP  as select \* from  T\_BUSINESS\_MODEL where 1=2 ;

// 对临时表改数据类型

alter table T\_BUSINESS\_MODEL\_TEMP modify MODEL\_MONEY NUMBER(10,2);

// 将原表数据赋值到最新的表中

insert into T\_BUSINESS\_MODEL\_TEMP select \* from T\_BUSINESS\_MODEL;

// 删掉原来的表

drop table T\_BUSINESS\_MODEL;

// 将临时表重新命名为原表

alter table T\_BUSINESS\_MODEL\_TEMP rename to T\_BUSINESS\_MODEL;

// 添加原表的约束 （主键，外键，索引）

alter table T\_BUSINESS\_MODEL

add constraint PK\_T\_BUSINESS\_MODEL primary key (ID);

方法二：

\-- 字段重命名

alter table PROJECT rename column NAME to NAME1;

\-----  把字段名称改成原来的名称

alter table PROJECT add  NAME VARCHAR2(300);

\--- 赋值

update PROJECT np

set np.name=np.name1;

commit;

\--- 删掉原来重命名的字段

alter table PROJECT drop column  NAME1;

*  Oracle 递归查询  START WITH....CONNECT BY PRIOR......

SELECT \* FROM B\_CITY\_INFO   START WITH c\_id=540000

CONNECT BY PRIOR c\_id=p\_c\_id ;

*  Oracle调用存储过程和删除

调用：

** set**  serveroutput  **on** ;     
 ** exec**  存储过程名;

删除：

drop procedure 存储过程名

[oracle]: http://blog.csdn.net/helloboat/article/details/42672463
[Link 1]: http://blog.csdn.net/helloboat/article/details/36011251
[oracle 1]: http://blog.csdn.net/helloboat/article/details/42269413