Common SQL statements and cases (Oracle)

Directory
1) Basic
2) Mathematical functions
3) rownum
4) Paging
5) Time Processing
6) Character function
7) To_number
8) Aggregation function

1) Basic
--New table:
CREATE TABLE table1 (ID varchar (primary) key, name varchar (+) not NULL);

--Inserting data
Insert INTO table1 (id,name) VALUES (' AA ', ' BB ');

--Update data
UPDATE table1 Set id = ' BB ' where id= ' cc ';

--Delete data
Delete from table1 where id = ' CC ';

--Delete Table structure
drop TABLE table1;

--Delete table data
TRUNCATE TABLE table1;

--Modify the table name:
ALTER TABLE table1 Rename to table2;

--Table data replication:
INSERT INTO table1 (SELECT * from table2);

--Copy table structure:
CREATE TABLE table1 SELECT * from table2 where 1>1;

--Copy table structure and data:
CREATE TABLE table1 select * from table2;

--Copy the specified field:
CREATE TABLE table1 as select ID, name from table2 where 1>1;

--Conditional query:
Select Id,name (case gender if 0 then ' Male ' when 1 Then ' female ' end) gender from table1

2) Mathematical functions
--Absolute Value: ABS ()
Select ABS ( -2) value from dual; --(2)

--Take integer function (Large): Ceil ()
Select Ceil ( -2.001) value from dual; --(-2)

--Rounding function (small): Floor ()
Select Floor ( -2.001) value from dual; --(-3)

--Take the whole function (intercept): Trunc ()
Select Trunc ( -2.001) value from dual; --(-2)

--Rounding: Round ()
Select round (1.234564,4) value from dual; --(1.2346)

--Take square: Power (m,n)
Select Power (4,2) value from dual; --(16)

--Take square root: SQRT ()
Select sqrt (+) value from dual; --(4)

--Take random number: Dbms_random (minvalue,maxvalue)
Select Dbms_random.value () from dual; (default is between 0 and 1)
Select Dbms_random.value (2,4) value from dual; (random number between 2-4)

--Take symbol: sign ()
Select sign ( -3) value from dual; --(-1)
Select sign (3) value from dual; --(1)

--Fetch the maximum value of the collection: Greatest (Value)
Select Greatest ( -1,3,5,7,9) value from dual; --(9)

--Take the minimum value of the collection: least (value)
Select Least ( -1,3,5,7,9) value from dual; --(-1)

--handling Null value: NVL (null value, instead of value)
Select NVL (null,10) value from dual; --(10)
Select NVL (score,10) score from student;

3) rownum
--rownum can be used directly as a query condition when it is less than a certain number (note that Oracle does not support select top)
SELECT * FROM student where rownum <3;

--Query rownum is larger than a certain value, need to use subquery, and rownum need to have alias
SELECT * FROM (select RowNum RN, id,name from student) where rn>2;
SELECT * FROM (select RowNum RN, student.* from student) where RN >3;

--Interval query
SELECT * FROM (select RowNum RN, student.* from student) where RN >3 and rn<6;

--sort + top N
SELECT * FROM (select RowNum rn, t.* from (select d.* to Djdruver D order by Drivernumber) T) p where p.rn<10;

--Sorting + interval query 1
SELECT * FROM (select RowNum rn, t.* from (select d.* to Djdriver D order by djdriver_drivertimes) T) p where p.rn<9 and p.rn>6;

--Sorting + interval query 2
SELECT * FROM (select RowNum rn, t.* from (select d.* to Djdriver D order by djdriver_drivertimes) T where rownum<9) p where p.rn>6;--efficiency is much higher than the way one

4) Paging
(assuming 10 per page)
Does not contain a sort:

-Low efficiency
SELECT * FROM (select RowNum rn, d.* from Djdriver D) p where p.rn<=20 and p.rn>=10;
SELECT * FROM (select RowNum rn, d.* from Djdriver D) p where p.rn between and 20;

-High efficiency
SELECT * FROM (select RowNum rn, d.* from Djdriver D where rownum<=20) p where p.rn>=10;

Include sort:
--Sorting + interval query 1 (Low efficiency)
SELECT * FROM (select RowNum rn, t.* from (select d.* to Djdriver D order by djdriver_drivertimes) T) p where p.rn<=2 0 and p.rn>=10;

SELECT * FROM (select RowNum rn, t.* from (select d.* to Djdriver D order by djdriver_drivertimes) T) p where P.rn Betwe En 20;

--Sorting + interval query 2 (High efficiency)
SELECT * FROM (select RowNum rn, t.* from (select d.* to Djdriver D order by djdriver_drivertimes) T where rownum<=20 ) p where p.rn>=10;

5) Time Processing
1.to_char and to_date Basic use
--Date
--yyyy yyy yy year
--month mm Mon month
--day + week DD DDD (day of the year) DY days
--hour HH hh24
--Min mi
--SEC SS

Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') currenttime,
To_char (sysdate, ' yyyy ') year,
To_char (sysdate, ' mm ') month,
To_char (sysdate, ' DD ') day,
To_char (sysdate, ' Day ') week,
To_char (sysdate, ' hh24 ') hour,
To_char (sysdate, ' mi ') minute,
To_char (sysdate, ' SS ') second
from dual;

Select To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss ') currenttime,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' yyyy ') year,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' mm ') month,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' DD ') day,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' Day ') week,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' Day ', ' Nls_date_language=american ') week,--set language
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' hh24 ') hour,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' mi ') minute,
To_char (To_date (' 2009-07-04 05:02:01 ', ' yyyy-mm-dd hh24:mi:ss '), ' SS ') second
from dual;

2.months_between
Select Months_between (to_date (' 03-31-2014 ', ' mm-dd-yyyy '), to_date (' 12-31-2013 ', ' mm-dd-yyyy ') "months"
From DUAL;

3.next_day
Select Sysdate Today, Next_day (sysdate,6) Nextweek from dual;

4. Time interval
Select Cardid, borrowdate from borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ') between
To_date (' 2014-02-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and To_date (' 2014-05-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

5.interval
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') currenttime,
To_char (sysdate-interval ' 7 ' year, ' Yyyy-mm-dd hh24:mi:ss ') intervalyear,
To_char (sysdate-interval ' 7 ' month, ' Yyyy-mm-dd hh24:mi:ss ') Intervalmonth,
To_char (sysdate-interval ' 7 ' Day, ' Yyyy-mm-dd hh24:mi:ss ') Intervalday,
To_char (sysdate-interval ' 7 ' hour, ' yyyy-mm-dd hh24:mi:ss ') Intervalhour,
To_char (sysdate-interval ' 7 ' minute, ' Yyyy-mm-dd hh24:mi:ss ') Intervalminute,
To_char (sysdate-interval ' 7 ' second, ' Yyyy-mm-dd hh24:mi:ss ') intervalsecond
from dual;

6.add_months
Select Add_months (sysdate,12) newtime from dual;

7.extract
Select Extract (month from sysdate) "This month",
Extract (Year from add_months (sysdate,36)) ' Years ' from dual;

6) Character function
--Character functions
Select substr (' ABCDEFG ', 1,5) substr,--string intercept
InStr (' ABCDEFG ', ' BC ') InStr,--Find substring

' Hello ' | | ' World ' concat,--Connect

Trim (' wish ') trim,--go to front and back spaces
RTrim (' Wish ') RTrim,--go to the back space
LTrim (' Wish ') LTrim,--go to the front space

Trim (Leading ' w ' from ' Wish ') Deleteprefix,--Go to prefix
Trim (Trailing ' h ' from ' Wish ') deletetrailing,--Go to suffix
Trim (' W ' from ' Wish ') trim1,

ASCII (' A ') A1,
ASCII (' a ') A2,--ascii (converted to the corresponding decimal number)
Chr (C1),
Chr (C2),--CHR (decimal to corresponding character)

Length (' ABCDEFG ') Len,--lengths

Lower (' WISH ') lower,
Upper (' Wish ') upper,
Initcap (' Wish ') Initcap,--uppercase and lowercase transformations

Replace (' Wish1 ', ' 1 ', ' youhappy ') Replace,--replace

Translate (' Wish1 ', ' 1 ', ' y ') translate,--conversion, corresponding to a bit (the number of digits preceding the number of digits is greater than or equal to the following)
Translate (' Wish1 ', ' sh1 ', ' hy ') translate1,

Concat (' One ', ' concat ')--connection
from dual;

7) To_number
--to_number (expr)
--to_number (Expr,format)
--to_number (Expr,format, ' Nls-param ')

Select To_number (' 0123 ') Number1,--converts a string to number
Trunc (To_number (' 0123.123 '), 2) Number2,
To_number (' 120.11 ', ' 999.99 ') Number3,
To_number (' 0a ', ' xx ') number4,--converts a hex number to decimal
To_number (100000, ' xxxxxx ') number5
from dual;

6) Add_months

Select add_months (sysdate, from dual;

7) Extract

Select Extract (monthfrommonth, extract Add_months (sysdate, from

Common SQL statements and cases (Oracle)