oracle 中的一些sql语句-优快云博客

本文介绍了Oracle数据库中一些实用的SQL技巧，包括查找表中的重复记录、清空表中的所有记录、建立自动编号、进行左连接查询等。还涵盖了常用的连接操作符如UNION、UNION ALL、INTERSECT及MINUS的使用方法。

Oracle常用的关键词

sysdate 系统的默认时间;

查找表中的重复记录

select * 
from hotye_user a 
where a.USER_ID < ( 
select max(b.user_id) 
from hotye_user b 
where a.USER_NAME=b.USER_NAME 
)order by a.USER_NAME;

清空表中的所有记录

truncate   table   table_name;

采用级联方式删除：

delete   from     out_record   where   inst_id=3     cascade   constraint

在Oracle数据库中建立自动编号(自动增量的方法)
1.先创建 Sequence

DROP SEQUENCE NET2_DEV.TERMINAL_ID;
CREATE SEQUENCE NET2_DEV.TERMINAL_ID
  START WITH 1
  MAXVALUE 999999999
  MINVALUE 1
  NOCYCLE
  CACHE 10
  NOORDER;

2.再创建 Trigger

DROP TRIGGER NET2_DEV.MERCHANT_ID_TRIGGER;

CREATE OR REPLACE TRIGGER NET2_DEV.merchant_id_trigger
  before insert ON NET2_DEV.MERCHANT   for each row
declare
  nextid number;
begin
    select merchant_id.nextval
    into nextid
    from sys.dual;
    :new.merchant_id:=nextid;
end merchant_id_trigger;
/

Oracle中过个表格的左连接查询

			SELECT  
					USER_ID,CAR_NO,CAR_NO_TYPE,OPERATOR_ID,REG_DATE, 
					ACCT_NO,BALANCE,
					HUID,USER_NAME,MOBILE,REAL_NAME,REG_FROM,
					UPID,POINT_BALANCE
				FROM  
					USER_CAR_NO UCN,   
                    (SELECT   ACCT_NO,BALANCE   FROM   EW_SUB_ACCOUNT   WHERE SUB_TYPE_CODE='11' AND BALANCE>0)   ESA,   
                    (SELECT   USER_ID HUID,USER_NAME,MOBILE,REAL_NAME,REG_FROM   FROM   HOTYE_USER   WHERE  USER_NAME IS NOT NULL)  HU,   
                    (SELECT   USER_ID UPID,POINT_BALANCE   FROM   USER_POINT  WHERE   POINT_TYPE_CODE='11' AND POINT_BALANCE>0)   UP   
 
               WHERE 
	              		UCN.USER_ID   =   ESA.ACCT_NO(+)   
	              AND   UCN.USER_ID   =   HU.HUID(+)   
	              AND   UCN.USER_ID   =   UP.UPID(+)

Oracle的一些连接操作的设置符号

UNION 用来返回前后查询的结果,如果有重复则返回其中一个

select myname,mysex
from employees01
union
select myname,mysex
from employees02
/;

UNION ALL 用来返回前后两个查询的结果,如果有重复也全部返回

select myname,mysex
from employees01
union all
select myname,mysex
from employees02
/;

INTERSECT 用来返回前后查询相同的部分

select myname,mysex
from employees01
intersect
select myname,mysex
from employees02
/;

MINUS 用来返回前面查询减去后面查询的部分

select myname,mysex
from employees01
minus
select myname,mysex
from employees02
/;