Oracle和SQL对比语法总结

SQL数据库                                                                                                                                                                                     Oracle数据库

ISNULL(字段名,替换值)  -------------------------------------------------------------          NVL(字段名,替换值)

SUBSTRING(字段名,起始位置,截取长度) --------------------------------------        SUBSTR(字段名,起始位置,截取长度)

GETDATE() -----------------------------------------------------------------------------         SELECT SYSDATE FROM DUAL

YEAR(GETDATE())  -----------------------------------------------------------------           SELECT TO_CHAR(SYSDATE,'yyyy')  FROM DUAL

MONTH(GETDATE())  --------------------------------------------------------------           SELECT TO_CHAR(SYSDATE,'MM')  FROM DUAL

DAY(GETDATE())  ------------------------------------------------------------------            SELECT TO_CHAR(SYSDATE,'dd')  FROM DUAL

SELECT DATEPART(HOUR,GETDATE()) ----------------------------------          SELECT TO_CHAR(SYSDATE,'hh')  FROM DUAL

SELECT DATEPART(MINUTE,GETDATE())---------------------------------        SELECT TO_CHAR(SYSDATE,'mi')  FROM DUAL

SELECT DATEPART(SECOND,GETDATE()) ----------------------------          SELECT TO_CHAR(SYSDATE,'ss')  FROM DUAL

+ (连接符号) --------------------------------------------------------------------------          ||

'2013-03-29 11:40:28'-------------------------------------------------------------         TO_DATE('2013-03-29 11:40:28','yyyy/MM/dd hh:mi:ss')

UniqueIdentifier ----------------------------------------------------------------------------------------------   SYS_GUID()

select SYS_GUID() from dual;

create table TEST2
(
   UuID varchar2(200) default SYS_GUID(),
   UName varchar2(20)
);

insert into TEST2(UName) values('Hello');

insert into TEST2(UName) values('World');

select * from TEST2;