Oracle Function Script Recording

--built-in functions--aggregate function returns a single valueSelect Count(*) fromStudentwhereSclass='95031';--count () record number of barsSelect sum(degree) fromScore T;--Sum (degree) to find the sum of resultsSelect avg(degree) fromScore T;--avg (degree) average of resultsSelect Max(degree),min(degree) fromScore T;--Max (degree), min (degree) is the maximum score, the minimum valueSelect Max(degree),min(degree),avg(degree),sum(degree) fromScore T;--you can calculate all the aggregate functions in one line--Conversion FunctionsSelectSname,sno,sbirthday fromStudentwhereTo_char (Sbirthday,'yyyy')=(SelectTo_char (S.sbirthday,'yyyy') fromStudent SwhereS.sno='108');--To_char Convert Date to string, date format ' Yyyy-mm-dd hh24:mi:ss 'Select *  fromStudentwhereSbirthday>=To_date ('1976-1-1','YYYY-MM-DD')--To_date Convert a string to a date--Time FunctionSelect cast('123'  as  Number) fromdual;SelectSysdate fromDual--gets the current time of the databaseSelectSysdate+1  fromDual--date of the day to be added 1st;SelectAdd_months (Sysdate,+1) fromDual--Month plus January--Numeric Functions--Absolute ValueSelect ABS(-123) fromdual;--die-FindingSelectMoD A,5) fromdual;--take the whole--take the upper valueSelectCeil (123.35) fromdual;--Remove Limit valueSelect  Floor(123.35) fromdual;--RoundingSelect round(123.35) fromdual;Select round(123.4567,2) fromDual--keep two decimal places according to the number of reserved digits in roundingSelect round(123.4567,-1) fromDual--Single -digit rounding, with decimal point as the distance to add and subtract--Give away the decimal pointSelectTrunc123.35) fromdual; SelectTrunc123.35,1) fromdual;--String Functions--Calculate LengthSelectSname, Langth (sname) fromstudent;--Go to SpaceSelectTrim'Asas') fromdual;Select LTrim('Asas') fromDual--left to go spaceSelect RTrim('Asas') fromDual--right to go space--ReplaceSelect Replace('AB CD',' ',"') fromdual;Select Replace(Sname,'Wang','Liu') fromStudentwhereSname like 'Wang%';--Find StringSelectInStr'ABCD','a') fromDual--Query index value starting from 1, no return 0 found--Intercept StringSelectSubstr'ABCDE','1') fromDual--0 or 1 will be intercepted from 1 bits, to the lastSelectSubstr'ABCDE',-1) fromDual--intercept from the right and intercept the result as eSelectSubstr'ABCDE',2,3) fromDual--The intercept result is BCD, and the 3-bit length is calculated from the Intercept position.SelectSubstr'ABCDE',-2,3) fromDual--interception result is DC, starting from the far right to intercept to 3 lengthsSelectAname,substr (Sname,1,1)||'classmate'  fromstudent;--Other FunctionsSelect avg(NVL (degree,0)) fromScore;--assigns a null value to the default value of 0, in the average calculation;SelectT.*Decode (Ssex,'1','male'，'2','female','Don't know') fromSTUDENT T;--Change 1 to Male, 2 to female, if not, show ' Don't Know '

Oracle Function Script Recording