Oracle Single-line query

A single-line query is also called a single-line function that uses a function to manipulate data about a basic query:Oracle Basic Queries

1Sql> Select lower (' Hello world ') Turn lowercase,2Upper (' Hello world ') to capitalize,3Initcap (' Hello World ') Capitalize first letter42from dual;5  6  7Sql>--substr (A, b) takes a substring, starting at the end of a8Sql> Select substr (' Hello World ', 3) sub-string from dual;9Sql>--substr (A,b,c) from A, the B-bit begins to take, take C-bitTenSql> Select substr (' Hello world ', 3,4) sub-string from dual; One   A   -Sql>--length character number lengthb number of bytes -Sql> Select Length (' Hello World ') character, LENGTHB (' Hello World ') bytes from dual; the   -   -Sql>--Lpad left padding rpad right padding -Sql>--ABCD filled into 10 bits +Sql> Select Lpad (' ABCD ', 10, ' * ') left, Rpad (' ABCD ', 10, ' * ')) right from dual; - Around  +---------- ---------- AABCD abcd****** at   -Sql>--Trim removes the specified character before and after -Sql> Select Trim (' H ' from ' Hello WORLDH ') from dual; - If there is more than one character specified later in the preceding latter, it will be removed, for example: Hhhello, will remove H . -   -Sql>--Replace replacement insql> Select replace (' Hello world ', ' l ', ' * ') from dual; -REPLACE (' HE to----------- +He**o wor*D -   theSql>-Rounding, if the number of digits is negative, then it will push forward, -1 to see the bits saved to 10 bit,-2 See 10 bit saved to hundred *Sql> Select round (45.926,2) One, $Round (45.926,1) Two,Panax NotoginsengRound (45.926,0) Three, -Round (45.926,-1) Four, theRound (45.926,-2) Five + from dual; A   the 12345  +---------- ---------- ---------- ---------- ---------- -45.93 45.9 46) 50 0 $   $Sql>--truncate, truncated directly from the number of digits, 0 bits in digit -Sql> Select Trunc (45.926,2) One, -Trunc (45.926,1) Two, theTrunc (45.926,0) Three, -Trunc (45.926,-1) Four,WuyiTrunc (45.926,-2) Five the*From dual -Sql>/ Wu   - 12345  About---------- ---------- ---------- ---------- ---------- $45.92 45.9 45) 40 0 -   -Sql>--Current Time -Sql>select Sysdate from dual; ASql>--format Time +Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual; the time can be calculated and no addition is allowed -Sql> Select (sysdate-1) yesterday, Sysdate today, (sysdate+1) tomorrow from dual; $ Yesterday today tomorrow  the-------------- -------------- -------------- the30月-June-16 January-July-16 February-July-16 the   theSql>--months_between number of months, showing the number of months between two data intervals -Sql> Select Ename,hiredate, (sysdate-hiredate)/30One, in Months_between (sysdate,hiredate) Two the2from EMP; theThrough the above statement we find that the simple/30 is inaccurate. About   theSql>--add_months Month for calculation, plus how many months later theSql>--53 months later theSql> Select Add_months (sysdate,53) from dual; +   -Sql>--Last_day Get incoming time the last day of the month theSql>Select Last_day (sysdate) from dual;Bayi   theSql>--Next_day The next specified day of the week from the incoming date theSql>--Next Friday -Sql> Select Next_day (sysdate, ' Friday ')) from dual; - Application theSql>/* the sql> next_day application: Automatically backs up data from a table every week the sql> 1. Distributed Database the sql> 2. Trigger Snapshot - sql>*/ theRounding the date, the default current time is 2016-07-01 theSql> Select round (sysdate, ' month '), round (sysdate, ' year ')) from dual; the ROUND (Sysdate, ROUND (Sysdate, 94-------------- -------------- theJanuary-July-16 January-January-17 the   the convert date to character, To_char (data, format)98Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ' Today is ' Day ') from dual; AboutTo_char (sysdate, ' Yyyy-mm-ddhh24:mi -----------------------------------1012016-07-01 12:27: 22 Today is Friday102  103Sql>--Query Employee Salary: Two decimal places, thousand characters, local currency code L104Sql> Select To_char (Sal, ' l9,999.99 ')) from EMP; theTo_char (SAL, ' l9,999106-------------------107￥800.00108  109Sql>--General Functions the  111Sql>--nvl2 (a,b,c) when a=returns C when null, otherwise returns B theNVL (A, b) when a=returns B when null, otherwise returns a113Sql> Select SAL*12+NVL2 (comm,comm,0) from EMP; the   theSql>--nullif (A, b) when a=B, return null; theSql> Select Nullif (' abc ', ' ABC ') value from dual;117  118Sql>--COALESCE Find the first non-null value from left to right119Sql> Select Comm,sal,coalesce (comm,sal) "first non-null value"from EMP; -  121  CaseWhen to use122  123 For example, a rise in wages:124   the The syntax of sql99126Sql>Select Empno,ename,job,sal before the rise,1272 CaseJob when ' president ' then sal+1000 -3 when "MANAGER" then sal+8001294Elsesal+400 the5after the end Rose1316from EMP; the  133 sqlplus syntax, decode statement134Sql>Select Empno,ename,job,sal before the rise,1352 decode (Job, ' president ', sal+1000,1363 ' MANAGER ', sal+800,1374 sal+400) after the rise1385 from EMP;

Oracle Single-line query