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
/;