Single recording function 1.ascii in SQL returns a decimal number corresponding to the specified character; SQL> SELECT ASCII ('A') A, ASCII ('A') A, ASCII ('0') ZERO, ASCII ('' Space from dual;
A a a zero space ------------------------- --------- 65 97 48 32
2.chr gives an integer, returns the corresponding characters; SQL> SELECT CHR (54740) ZHAO, CHR (65) chr65 from dual;
ZH C - Zhao A
3. Concat connection two strings; SQL> SELECT Concat ('010 -', '88888888') || 'Turn 23' Gao Dynasty Board Phone from Dual;
Gao Dynasty boat phone ---------------- 010-88888888 turn 23
4. Initcap Returns the string and turn the first letter of the string to uppercase; SQL> SELECT INITCAP ('Smith') UPP from DUAL;
UPP ----- Smith
5.instr (C1, C2, I, J) Search for specified characters in a string, returns the position of the specified character; C1 Searched String C2 wants to search for the start position of the search, default 1J's position, default is 1SQL> SELECT INSTR ('Oracle TRANING', 'RA', 1, 2) Instring from Dual;
Instring --------- 9
6.Length Returns the length of the string; SQL> SELECT NAME, Length (Name), Addr, Length (Addr), Sal, Length (to_CHAR (SAL)) from GAO.NCHAR_TST;
Name Length (name) Addr Length (AddR) Sal length (to_char (sal)) ------------------------------ - - ------------ ---------------------------- High-drying 3 Beijing Sea Ingot 6 9999.99 7
7.Lower Returns the string and puts all the characters lowercase SQL> Select Lower ('aabbccdd') Aabbccdd from Dual;
Aabbccdd -------- Aabbccdd
8.upper returns a string and put all the characters to SQL> SELECT UPPER ('AABBCCDD') Upper from Dual;
Upper -------- Aabbccdd
9.RPAD and LPAD (Paste Character) RPAD Paste Character LPAD on the Right of the Column Paste Characters SQL> SELECT LPAD (RPAD ('GAO', 10, '*'), 17, '*') from Dual;
LPAD (RPAD ('Gao', 1 ---------------- ******* GAO ****** is not enough to fill it with *
10.Ltrim and Rtrimltrim Remove the string RTRIM on the left RTRIM Delete the string SQL> SQL> SELECT LTRIM (RTRIM ('GAO Qian Jing', ''), '') from DUAL;
LTRIM (RTRIM ('----------- Gao Qian Jing11.substr (String, Start, Count) Subsidence, starting from Start, take count SQL> SELECT SUBSTR (' 13088888888 " , 3, 8) from Dual;
SUBSTR ('-------- 08888888
12.Replace ('string', 's1', 's2') String Write the character string S2 to be replaced with the string S2 to be replaced SQL> SQL> SELECT Replace ('He Love You', 'He' , 'i') from dual;
Replace ('H ---------- i love you
13. Soundex returns the same string SQL> Create Table Table1 (XM VARCHAR (8)); SQL> Insert Into Table1 Values ('Wether); SQL> Insert Into Table1 Values '); SQL> INSERT INTO TABLE1 VALUES (' GAO ');
SQL> SELECT XM from Table1 Where Soundex (XM) = Soundex ('Weather');
XM -------- Weatherwether
14.Trim ('s' from 'string') Leading Cut the previous character trailing cut off the following characters If not specified, the default is space
15. Sps Returns the absolute value of the specified value SQL> SELECT ABS (100), ABS (-100) from dual;
ABS (100) ABS (-100) ----------------- 100 100
16. Acon gave a string value SQL> SELECT ACOS (-1) from Dual;
ACOS (-1) --------- 3.1415927
17.aSIN gives the value of an anti-sicrosive string SQL> SELECT Asin (0.5) from Dual;
Asin (0.5) ---------. 52359878
18.atan Returns an aromatic cutting value of a number SQL> SELECT Atan (1) from DUAL;
Atan (1) ---------. 78539816
19.ceil returns the minimum integer SQL> SELECT CEIL (3.1415927) from the given number.
CEIL (3.1415927) --------------- 4
20.COS Returns a given number cosine SQL> SELECT COS (-3.1415927) from dual;
COS (-3.1415927) --------------- -1
21.Cosh Returns a digital anti-hosh value SQL> SELECT COSH (20) from DUAL;
Cosh (20) --------- 242582598
22.EXP Returns a number E N - square root SQL> SELECT Exp (2), Exp (1) from dual;
Exp (2) exp (1) ----------------- 7.3890561 2.7182818
23.Floor Take an integer SQL> SELECT FLOOR (2345.67) from DUAL;
Floor (2345.67) -------------- 2345
24.Ln Returns a number of logs SQL> SELECT LN (1), LN (2), LN (2.7182818) from dual; ln (1) ln (2) ln (2.7182818) --------- --------------------- 0 .69314718 .99999999
25.log (N1, N2) Returns a log (2, 4) from DUAL in N1 N2 N2.
LOG (2, 1) log (2, 4) ----------------- 0 2
26.MOD (N1, N2) Returns a N1 divided by N2 SQL> SELECT MOD (10, 3), Mod (3, 3), MOD (2, 3) from DUAL;
MOD (10, 3) MOD (3, 3) MOD (2, 3) ------------------------- 1 0 2
27.power Returns N2 N2 Square SQL> SELECT POWER (2, 10), Power (3, 3) from DUAL;
Power (2, 10) Power (3, 3) -------------------- 1024 27
28.Round and Trunc Follow SQL> SELECT ROUND (55.5), Trunc (-55.5) from the specified precision.
Round (55.5) trunc (55.5) Trunc (-55.5) ----------------------------- - ------------ 56 -55 55-55
29.Sign Number N symbol, more than 0 returns 1, less than 0 Return -1, equal to 0 Returns 0SQL> SELECT SIGN (123), SIGN (-100), SIGN (0) from DUAL;
Sign (123) SIGN (-100) SIGN (0) ------------------ --------- 1 -1 0
30.Sin Returns a number of sinusoids SQL> SELECT SIN (1.57079) from DUAL;
Sin (1.57079) ------------ 1
31.Sigh Returns the value of a diverse sine SQL> SINE (20), SINH (20) from dual;
Sin (20) SINH (20) -----------------. 91294525 242582598
32.SQRT Returns the root SQL> SELECT SQRT (64), SQRT (10) from Dual;
SQRT (64) SQRT (10) ---------- --------- 8 3.1622777
33.Tan Returns the node of the number SQL> SELECT TAN (20), TAN (10) from Dual;
Tan (20) Tan (10) ---------- --------- 2.2371609.64836083
34.Tanh Returns the Digital N of the Double Corruption Value SQL> SELECT TANH (20), TAN (20) from DUAL;
Tanh (20) Tan (20) ---------- --------- 1 2.2371609
35.Trunc intercepted a number of SQL> SELECT TRUNC (124.1666, -2) Trunc1, Trunc (124.16666, 2) from DUAL in accordance with the specified accuracy.
Trunc1 Trunc (124.16666, 2) -------------------------- 100 124.1636.Add_months Add or minus the month SQL> SELECT TO_CHAR (Add_MontHS To_date ('199912', 'YYYMM'), 2), 'YYYYMM') from DUAL
TO_CHA ------ 200002sql> select to_char (add_months (to_date ('199912', 'YYYMM'), - 2), 'YYYYMM') from DUAL
TO_CHA ------ 199910
37.Last_day returns the last day of the date SQL> select to_char (sysdate, 'yyyy.mm.dd'), to_char (sysdate) 1, 'YYYY.MM.DD') from DUAL;
TO_CHAR (Sy to_Char ((S -------- ---------- 2004.05.09 2004.05.10sql> select last_day (sysdate) from DUAL
Last_day (s ---------- 31-50-04
38.Months_between (Date2, Date1) gives Date2-Date1 month SQL> SELECT MONTHS_BETWEEN ('19 - 12 month -1999', '19- March-1999') Mon_between from Dual;
MON_BETWEEN ----------- 9SQL> SELECTMONTHS_BETWEEN (TO_DATE ('20005.20'), to_date ('2005.05.20', 'YYYY.MM.DD')) Mon_betw from dual;
Mon_betw --------- -60
39.New_Time (Date, 'this', 'That') gives the date and time SQL> select to_char (sysdate, 'yyyy.mm.dd HH24: MI: SS') BJ_TIME, TO_CHAR (TO_CHAR) New_Time 2 (sysdate, 'pdt', 'gmt'), 'YYYY.MM.DD HH24: MI: SS') LOS_ANGLES from Dual
BJ_TIME LOS_ANGLES ------------------------------------- 2004.05.09 11:05:32 2004.05. 09 18:05:32
40.NEXT_DAY (Date, 'Day') gives the date Date and the week after calculation of the next week SQL> SELECT NEXT_DAY ('18 - May -2001 ',' Friday ') Next_day from Dual;
Next_day ---------- 25-May -01
41.sdate is used to get the current date SQL> select to_char (sysdate, 'dd-mm-yyyy day') from DUAL;
TO_CHAR (sysdate, '----------------- 09-05-2004 Sunday Trunc (Date, FMT) is truncated according to the requirements given, if FMT =' mi 'is represented Reserved, cut off second SQL> select to_char (trunc (sysdate, 'hh'), 'YYYY.MM.DD HH24: MI: SS') HH, 2 TO_CHAR (Trunc (Sysdate, 'Mi'), 'YYYY.MM .dd hh24: mi: ss') hhmm from dual; hh hhmm ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --- 2004.05.09 11:00:00 2004.05.09 11:17:00
42.ChartorowID converts the character data type to RowID type SQL> SELECT ROWID, ROWIDTOCHAR (ROWID), ename from scott.emp;
Rowid Rowidtochar (Rowid) ENAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --Aaaafkaacaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaad Jones
43.convert (c, dset, sset) converts the source string SSET from a language character set to another Dset character set SQL> SELECT Convert ('strutz', 'WE8HP', 'F7DEC') "Conversion" from DUAL ;
CONVER ------ Strutz
44.Hextoraw converts a hexadecimal string to binary
45.RawTohext converts a binary string to a hexadecimal
46.RowidTocha converts the ROWID data type to a character type
47.to_char (date, 'format') SQL> Select to_char (sysdate, 'yyyy / mm / dd hh24: mi: ss') from DUAL
TO_CHAR (sysdate, 'yy ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
48.to_date (string, 'format') Transforms a string into a date in Oracle
49.to_multi_byte transforms the single-byte character in the string into multi-byte character SQL> SELECT TO_MULTI_BYTE ('High') from DUAL;
TO - high
50.to_Number converts the characters given to Digital SQL> SELECT TO_NUMBER ('1999') Year from Dual;
Year --------- 1999
51.BFileName (Dir, file) Specifies an external binary file SQL> INSERT INTO File_TB1 VALUES (BFilename ('LOB_DIR1', 'Image1.gif'));
52.convert ('x', 'desc', 'Source') Convertion Source Source Source Source Source Source to Desql> Select Sid, Serial #, Username, Decode (Command, 2 0, 'None', 3 2, 'INSERT', 4 3, 5 'SELECT', 6 6, 'UPDATE', 7 7, 'DELETE', 8 8, 'Drop', 9 'Other') CMD from V $ Session Where Type! = 'Background' SID Serial # Username CMD -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ------ 1 1 None 2 1 None 3 1 None 4 1 None 5 1 None 6 1 None 7 1275 None 8 1275 None 9 20 Gao SELECT 10 40 GAO NONE
53.DUMP (s, fmt, start, length) DUMP function to specify the internal number format fmt returns a value of type VARCHAR2 SQL> col global_name for a30SQL> col dump_string for a50SQL> set lin 200SQL> select global_name, dump (global_name, 1017, 8, 5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------Oracle.World Typ = 1 LEN = 12 CHARACTERSET = zHS16GBK: W, O, R , L, D
54.Empty_blob () and EMPTY_CLOB () These two functions are functions used to initialize the big data type fields.
55.GreateSt Returns the maximum value in a set of expressions, namely the code size. SQL> SELECT GRETEST ('AA', 'AB', 'AC') from DUAL;
Gr - acsql> SELECT GREATEST ('ah', 'An', 'Day') from Dual
GR - day
56.Least returns the minimum of the minimum SQL> SELECT Least ('ah ",' 天 ') from Dual; LE - ah
57.uID Returns the only integer integer SQL> show useruser for the current user "Gao" SQL> SELECT UserName, user_id from dba_users where user_id = uid;
Username user_id --------------------------------------- GAO 25
58.User returns the current user's name SQL> SELECT User from Dual;
User ---------------------------- GAO
59.UseRevn Returns the information of the current user environment, Opt can be: entryid, sessionid, terminal, isdba, lable, language, client_info, lang, vsizeisdba View whether the current user is DBA if it returns Truesql> SELECT Userenv ('isdba') From Dual;
Useren ------ Falsesql> Select Useerenv ('Isdba') from DUAL;
Useren ------ TRUESSSION Returns Session Sign SQL> SELECT Userenv ('sessionID') from DUAL;
Userenv ('sessionid') -------------------- 152ENTRYID Return to the session population sign SQL> SELECT Userenv ('entryid') from DUAL;
Userenv ('entryid') ------------------ 0Instance Returns the logo of the current instance SQL> SELECT Userenv ('Instance') from Dual;
Userenv ('instance') ------------------ 1Language Returns the current environment variable SQL> SELECT Userenv ('Language') from Dual;
Userenv ('Language') ------------------------------------------------------------------------------------------------------ ----------- Simplified Chinese_China.zhs16gbklang Returns the language of the current environment SQL> SELECT Userenv ('lang') from DUAL;
Userenv ('lang') -------------------------------------------- -------- ENSTERMINAL Returns the user's terminal or the logo of the machine SQL> SELECT Userenv ('Terminal') from DUAL;
Userenv ('Termina -------------- GAOVSIZE (X) Returns the size of X (bytes) SQL> SELECT VSIZE (User), User from Dual;
VSIZE (User) User ---------------------------------------- 6 SYSTEM
60.avg (DistINCT | All) ALL indicates all values for all values, Distinct is only for different values for different values SQLWKS> Create Table Table3 (XM VARCHAR (8), Sal Number (7, 2)); deal with. SQLWKS> insert into table3 values ( 'gao', 1111.11); SQLWKS> insert into table3 values ( 'gao', 1111.11); SQLWKS> insert into table3 values ( 'zhu', 5555.55); SQLWKS> commit; SQL> select avg (Distinct sal) from gao.table3;
AVG (Distinctsal) ---------------- 3333.33
SQL> SELECT AVG (All sal) from gao.table3;
AVG (Allsal) ----------- 2592.59
61.max (DistINCT | ALL) Ask the maximum, all represents the maximum value of all values, Distinct indicates the maximum value of different values, the same, only one SQL> SELECT MAX (DistINCT SAL) from scott.emp;
Max (Distinctsal) ---------------- 5000
62.min (Distinct | All) Summary, all indicates the minimum value of all values, Distinct indicates the minimum value of different values, the same only one SQL> SELECT Min (all sal) from gao.table3;
Min (allsal) ----------- 1111.11
63.STDDEV (DistINCT | ALL) Seeking standard deviation, ALL indicates that all value standards are poor, and Distinct represents only the standard deviation of different values. SQL> SELECT STDDEV (SAL) from scott.emp;
STDDEV (SAL) ----------- 1182.5032
SQL> SELECT STDDEV (Distinct Sal) from scott.emp;
STDDEV (DistinctSal) ------------------- 1229.951
64.Variance (Distinct | All)
SQL> SELECT VARIANCE (SAL) from scott.emp;
Variance (SAL) ------------- 1398313.9
65.Group BY is mainly used to count the number of SQL> Select Deptno, count (*), SUM (SAL) from scott.emp group by deptno;
Deptno count (*) SUM (SAL) ------------------------- 10 3 8750 20 5 10875 30 6 9400
66.Having Packet Statistical Rehabilitation Conditions SQL> Select Deptno, Count (*), SUM (SAL) from Scott.emp Group by DePtno Having Count (*)> = 5;
Deptno count (*) Sum (SAL) ------------------------- 20 5 10875 30 6 9400SQL> Select Deptno, Count (*) , SUM (SAL) from scott.emp haVing count (*)> = 5 group by deptno; deptno count (*) sum (sal) ----------------- ------- 20 5 10875 30 6 9400
67.Order By is used to sort the results of the query to output SQL> Select Deptno, ENAME, SAL from Scott.emp Order by DePTNO, SAL DESC;
Deptno ENAME SAL -------------------------- 10 King 5000 10 Clark 2450 10 Miller 1300 20 Scott 3000 20 Ford 3000 20 Jones 2975 20 ADAMS 1100 20 Smith 800 30 Blake 2850 30 Allen 1600 30 Turner 1500 30 Ward 1250 30 Martin 1250 30 James 950
