oracle SQLserver 函数-白红宇

oracle SQLserver 函数

阅读量：794 次

发布时间：2023-02-24

本文共 4558 字，大约阅读时间需要 15 分钟。

SQL????????

????????????SQL???????????????????????????SQL?????????????????????

??????????

?????

SELECT abs(-1) value

SELECT abs(-1) value from dual

??????????????????????abs(-1) ??1?

?????

SELECT ceiling(-1.001) value

SELECT ceil(-1.001) value from dual

???????????????????????????ceil(-1.001) ??-1?

?????

SELECT floor(-1.001) value

SELECT floor(-1.001) value from dual

???????????????????????????floor(-1.001) ??-2?

??????

SELECT cast(-1.002 as int) value

SELECT trunc(-1.002) value from dual

??????????????????????????trunc(-1.002) ??-2?

??????

SELECT round(1.23456,4) value 1.23460

SELECT round(1.23456,4) value from dual 1.2346

?????????????????????????????round(1.23456,4) ??1.2346?

????

SELECT Exp(1) value 2.7182818284590451

SELECT Exp(1) value from dual 2.71828182

???????????e???Exp(1) ??e?????2.7182818284590451?

??????

SELECT log(2.7182818284590451) value 1

SELECT ln(2.7182818284590451) value from dual; 1

??????????????????ln(2.7182818284590451) ??1?

??????

SELECT log10(10) value 1

SELECT log(10,10) value from dual; 1

?????????????10???log10(10) ??1?

????

SELECT SQUARE(4) value 16

SELECT power(4,2) value from dual 16

???????????????power(4,2) ??16?

?????

SELECT SQRT(4) value 2

SELECT SQRT(4) value from dual 2

????????????????SQRT(4) ??2?

???

SELECT power(3,4) value 81

SELECT power(3,4) value from dual 81

????????????????power(3,4) ??81?

?????

SELECT rand() value

SELECT sys.dbms_random.value(0,1) value from dual;

???????????????????sys.dbms_random.value(0,1) ??0?1???????

????

SELECT sign(-8) value -1

SELECT sign(-8) value from dual -1

????????????????sign(-8) ??-1?

???????????

??????
SELECT PI() value 3.1415926535897931
???PI() ????????3.1415926535897931?

??????
SELECT sin(PI()/2) value 1
???sin(PI()/2) ??1?

?????
SELECT Asin, Acos, Atan, Atan2
???ASIN?ACOS?ATAN?ATAN2 ????????????????????????

??????
SELECT max(value) value from (select 1 value union select -2 value union select 4 value union select 3 value)a
SELECT greatest(1,-2,4,3) value from dual
???GREATEST ????????????????

?????
SELECT min(value) value from (select 1 value union select -2 value union select 4 value union select 3 value)a
SELECT least(1,-2,4,3) value from dual
???LEAST ????????????????

????
SELECT F1, IsNull(F2,10) value from Tbl
SELECT F1,nvl(F2,10) value from Tbl
???NVL ??????????????????

????
SELECT ascii("a") value
SELECT ascii("a") value from dual
???ASCII ?????????ASCII???

????
SELECT char(97) value
SELECT chr(97) value from dual
???CHR ????????ASCII???????

?????
SELECT "11" + "22" + "33" value
SELECT CONCAT("11","22")||33 value from dual
???CONCAT ??????????|| ??????????

?????
SELECT CHARINDEX("s","sdsq",2) value
SELECT INSTR("sdsq","s",2) value from dual
???INSTR ????????????????

??????
SELECT substring("abcd",2,2) value
SELECT substr("abcd",2,2) value from dual
???SUBSTR ?????????????

?????
SELECT STUFF("abcdef", 2, 3, "ijklmn") value
SELECT Replace("abcdef","bcd","ijklmn") value from dual
???STUFF ?????????????????Replace ??????????????

?????
SELECT Translate("fasdbfasegas","fa","????") value from dual
???Translate ????????????????????????

??????
SELECT len,datalength
???LEN ? DATALENGTH ?????????????

?????
SELECT lower,upper
???LOWER ? UPPER ?????????????????

???????????

?????
SELECT INITCAP("abcd dsaf df") value from dual
???INITCAP ????????????????????

?????
SELECT space(10) + "abcd" value
SELECT LPAD("abcd",14) value from dual
???LPAD ??????????????????

?????
SELECT "abcd" + space(10) value
SELECT RPAD("abcd",14) value from dual
???RPAD ??????????????????

???
SELECT ltrim,rtrim,trim
???LTRIM?RTRIM ? TRIM ??????????????????????

?????
SELECT REPLICATE("abcd",2) value
???REPLICATE ???????????????

???
SELECT SOUNDEX("Smith"), SOUNDEX("Smythe") from dual
???SOUNDEX ????????????????

????
SELECT getdate() value
SELECT sysdate value from dual
???GETDATE ? SYSDATE ???????????

??????
SELECT convert(char(10),getdate(),20) value
SELECT trunc(sysdate) value from dual
???CONVERT ? TO_CHAR ?????????????????

????????
SELECT convert(char(8),getdate(),108) value
???CHAR(8) ? TO_CHAR ???????????????????

??????
SELECT DATEPART "yyyy-mm-dd", DATENAME "year", "quarter", "month", "dayofyear", "day", "week", "weekday", "hour", "minute", "second", "millisecond"
???DATEPART ? DATENAME ????????????????????