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

9) Student Selection Course

10) Library Borrowing

Basic

--New table: CREATE TABLE table1 (ID varchar (primary) key, name varchar (+) not null);--insert data into   table1 (id,name ) VALUES (' AA ', ' BB '); --Updated Data update   table1 set id = ' BB ' where id= ' cc ';--delete data delete from   table1 where id = ' CC ';--delete table    drop tables T Able1; --Modify 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;--condition query: Select Id,name (Case gender when 0 then ' Male ' when 1 Then ' female ' end '  gender from  table1  

Mathematical functions

--Absolute Value: ABS () Select ABS ( -2) value from dual;       --(2)--rounding function (Large): Ceil () Select Ceil ( -2.001) value from dual;       --(-2)--Rounding function (small): Floor () Select Floor ( -2.001) value from dual;       --(-3)--Rounding function (intercept): Trunc () Select Trunc ( -2.001) value from dual;       --(-2)--Rounded: 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 the symbol: sign () Select signs ( -3) value from dual; --( -1) Select sign (3) value from dual;       --(1)--fetch the maximum value of the set: Greatest (Value) select Greatest ( -1,3,5,7,9) value from dual;       --(9)--Take the minimum value of the set: least (Value) select least ( -1,3,5,7,9) value from dual;       --(-1)--Handle null value: NVL (null value, instead of value) Select NVL (null,10) value from dual; --() Select NVL (score,10) score from student;

RowNum related

--rownum is less than a certain number can be directly as a query condition (note that Oracle does not support select top) SELECT * from student where rownum <3;--query rownum is greater than a certain value, you need to use a subquery, Also rownum needs to have an 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.* from Djdruver d order by  Drivernumber) t) p where p.rn< ; 10;--sort + interval query 1select * FROM (select RowNum rn, t.* from (select d.* from Djdriver d order by djdriver_drivertimes) T) p WH Ere p.rn<9 and p.rn>6;--sort + interval query 2select * FROM (select RowNum rn, t.* from (select d.* from Djdriver d order by DJDR Iver_drivertimes) t where rownum<9) p where p.rn>6; --efficiency is much higher than the way one

Paging Query

(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 Rownu M 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 where rownum<=20 ) p where p.rn>=10;

Time processing

1. Basic use of To_char and to_date

--date--year yyyy yyy yy year--month mm Mon month--Day + weekday  DD DDD (Day of the year) DY days--hours  hh hh24-min mi--sec ss

EG1:

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 ') Secondfrom dual;

EG2:

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 '--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 ') Secondfrom 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

eg

Select Cardid, borrowdate from borrow where To_date (Borrowdate, ' 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    

6) Add_months

Select Add_months (sysdate,12) newtime from dual;

7) Extract

Character functions

--Character function Select substr (' ABCDEFG ', 1,5) substr,--String intercept InStr (' ABCDEFG ', ' BC ') InStr, --Find substring ' Hello ' | | ' World ' concat,--connect trim (' wish ') trim,--go back and forth space RT              Rim (' 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                                   Suffix trim (' w ' from ' Wish ') TRIM1, ASCII (' a ') A1, ASCII (' a ') A2,              --ascii (converted to corresponding decimal number) Chr (C1), Chr (C2),--CHR (decimal to corresponding character)        Length (' ABCDEFG ') len,--length lower (' WISH ') lower, Upper (' WISH ') Upper, Initcap (' Wish ') Initcap,--Case Transform replace (' Wish1 ', ' 1 ', ' youhappy ') replAce,--replace translate (' wish1 ', ' 1 ', ' y ') translate,--convert, corresponding to a bit (the preceding number of digits is greater than or equal to the number of digits in the back) transl Ate (' wish1 ', ' sh1 ', ' hy ') translate1, concat (' One ', ' ') ' concat--connect

from dual;

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

Aggregation functions

The student table is as follows:

Count

--count (Distinct|all) Select COUNT (1) as Count from student;--efficiency up to select COUNT (*) as count from student;    Select COUNT (Distinct score) from student;     

Statement 1 Results: 11

Avg

--avg (Distinct|all) Select AVG (score) score from Student;select avg (distinct score) from Student;select Classno,avg ( Score) score from student group by Classno;

Statement 3 output Result:

Max

--max (distinct|all) select Max (score) from Student;select Classno, Max (score) score from student group by Classno;

Min

--min (distinct|all) Select min (score) from Student;select Classno, Min (score) score from student group by Classno;

StdDev (standard deviation) standards deviation

--stddevselect StdDev (Score) from Student;select Classno, StdDev (score) score from student group by Classno;

Sum

--sumselect sum (score) from Student;select Classno, sum (score) score from student group by Classno;

Median of median--

--medianselect median (score) from Student;select Classno, median (score) score from student group by Classno;

Case Study-Student selection course

1. Create a table Stu (student table), course (curriculum), choose a timetable (S_c)

--Create TABLE STU  (ID number not   null,    name VARCHAR2 (255)  );   CREATE TABLE COURSE  (    ID number not  null,    coursename VARCHAR2 (255)  );    CREATE TABLE S_c  (    sid number    ,    CID number    ,    score number  );

2. Inserting data

--Inserting data insert into STU (id,name) VALUES (1, ' wish '); INSERT into STU (Id,name) VALUES (2, ' rain '); insert into STU (id,name) VA Lues (3, ' July '); insert into STU (id,name) VALUES (4, ' Joey '); Insert to COURSE (id,coursename) VALUES (1, ' math '); insert in To COURSE (Id,coursename) VALUES (2, ' 中文版 '), insert into COURSE (Id,coursename) VALUES (3, ' Japanese '); INSERT INTO COURS E (Id,coursename) VALUES (4, ' Chinese '); insert into S_c (Sid,cid,score) values (1,1,80); insert into S_c (Sid,cid,score) Val UEs (1,2,90); insert into S_c (Sid,cid,score) VALUES (2,4,100), insert into S_c (Sid,cid,score) VALUES (4,4,90), insert INTO S_c (Sid,cid,score) VALUES (4,1,100), insert into S_c (Sid,cid,score) VALUES (4,3,80), insert into S_c (Sid,cid,score) value S (4,2,80), insert into S_c (Sid,cid,score) VALUES (2,1,90), insert into S_c (Sid,cid,score) VALUES (2,4,100), insert into S_ C (Sid,cid,score) values (3,1,60);

3. Check the students ' choice of course

With VT as (select S.id,s.name,c.coursename,sc.score from Stu S, Course c, S_c SC where s.id=sc.sid and c.id=sc.cid) Select * FROM VT ORDER by ID;

Results:

Case 2--Library Borrowing

1. Create a table: books, readers (reader), borrowing (borrow)

--Create a Table bookcreate table  Book (BookId  varchar2 (),   --Total number  of books SortID varchar2 (+),-    -classification number  BookName varchar2 (100),--title  author Varchar2 (+),    --author  publisher Varchar2 (+),--Publishing Unit price number  ( 6,2)       – Price  );  --Create Table Reader creation table  Reader (  cardId varchar2,--  library card number  org varchar2 (+),--    unit  name VARCHAR2 (+),   --Name  gender VARCHAR2 (2),--   gender  title VARCHAR2 (+),  --title Address VARCHAR2 (100) – addresses);--Create Tables Borrowcreate table Borrow (    cardId varchar2)  --Library card number  bookId  VARCHAR2 (+),   --book Number  borrowdate varchar2 (30)--borrowing time);

2. Inserting data

--Insert Data-bookinsert into book (Bookid,sortid,bookname,author,publisher,price) VALUES (' AAA ', ' A1 ', ' Gone and the Wind ', ' CA ', ' renmin ', ' 103 '); INSERT into book (Bookid,sortid,bookname,author,publisher,price) VALUES (' BBB ', ' A2 ', ' the Little Prince ', ' CB ', ' Jixie ', ' + '), insert into book (Bookid,sortid,bookname,author,publisher,price) VALUES (' CCC ', ' A3 ', ' the Ordinary world ', ' CC ', ' renmin ', ' the ' [] '], insert into book (Bookid,sortid,bookname,author,publisher,price) VALUES (' DDD ', ' A4 ', ' The Little Women ', ' CA ', ' Dianzi ', ' 110 ');--insert Data-readerinsert into reader (cardid, org, Name,gender, title, address) VALUES (' xxx ', ' A ', ' Wish ', ' 1 ', ' student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address) VALUES (' UUU ', ' A ', ' Luna ', ' 1 ', ' student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address) VALUES (' VVV ', ' B ', ' Harry ', ' 1 ', ' student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address) VALUES (' www ', ' C ', ' Chander ', ' 2 ', ' Professor ', ' Bupt '); insert into reader (cardid, org, name,gender, title, address) VALUES (' yyy ', ' A ', ' Joey ', ' 2 ', ' Student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, Address) VALUES (' zzz ', ' B ', ' Richard ', ' 2 ', ' Student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address VALUES (' OOO ', ' A ', ' micheal ', ' 2 ', ' Student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address) values (' PPP ', ' A ', ' Richal ', ' 2 ', ' Student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address) VALUES (' ABP ', ' A ', ' Michal ', ' 2 ', ' Student ', ' bupt '); insert into reader (cardid, org, Name,gender, title, address) VALUES (' CCP ', ' A ', ' Mike ', ' 2 ', ' Student ', ' bupt ');--insert data-borrowinsert into borrow (cardid,bookid,borrowdate) VALUES (' xxx ', ' aaa ', ' 2014-4-29 ') Insert into Borrow (cardid,bookid,borrowdate) VALUES (' xxx ', ' bbb ', ' 2014-4-29 '); insert into borrow (Cardid,bookid, Borrowdate) VALUES (' xxx ', ' CCC ', ' 2014-4-28 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' yyy ', ' CCC ', ' 2014-4-28 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' yyy ', ' DDD ', ' 2014-4-27 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' yyy ', ' aaa ', ' 2014-4-27 '); insert into borrow (Cardid, Bookid,borrowdate) VALUES (' zzz ', ' BBB ', ' 2014-4-28 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' zzz ', ' DDD ' , ' 2014-4-27 '), insert into borrow (cardid,bookid,borrowdate) VALUES (' zzz ', ' aaa ', ' 2014-4-27 '); insert into Borrow ( Cardid,bookid,borrowdate) VALUES (' UUU ', ' BBB ', ' 2014-4-28 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' UUU ', ' ddd ', ' 2014-4-27 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' UUU ', ' AAA ', ' 2014-4-27 '); Borrow (Cardid,bookid,borrowdate) VALUES (' UUU ', ' CCC ', ' 2014-4-26 '); insert into borrow (cardid,bookid,borrowdate) VALUES (' VVV ', ' BBB ', ' 2014-4-28 '), insert into borrow (cardid,bookid,borrowdate) VALUES (' VVV ', ' ddd ', ' 2014-4-27 '); Insert into Borrow (cardid,bookid,borrowdate) VALUES (' www ', ' aaa ', ' 2014-4-27 '); insert into borrow (Cardid,bookid, Borrowdate) VALUES (' www ', ' CCC ', ' 2014-4-26 ');

The table information is as follows:

Book------> Reader-------> Borrow

3. Query the number of readers and details of the people who borrow books in Unit a

Number:

With VT1 as (select Cardid from reader where reader.org= ' A ') select COUNT (1) from VT1 where exists (select Cardid from Borr ow where Borrow.cardid=vt1.cardid);

More information:

With VT1 as (select cardid,name,org from reader where reader.org= ' A ') select cardid,name,org from VT1 where exists (select Cardid from borrow where borrow.cardid=vt1.cardid);

4. Search for readers with the end of the library card number as ' P '

Select Cardid, name, org from reader where cardid like '%p ';

5. Query female readers whose names begin with M, ' 1 ' shown as female, ' 2 ' shown as male

Select Cardid, name, org, case if gender= ' 1 ' Then ' Woman ' when gender= ' 2 ' then ' man ' else ' other ' end Genderfrom reader where Nam E like ' m% ';

6.2014 2-April readers who borrowed books

1) Query the reader that satisfies the condition (contains only cardid)--No weight

Way One:

Select Cardid, borrowdate from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' all ' and To_char (to_ Date (borrowdate, ' yyyy-mm-dd '), ' mm ') >= ' "and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' mm ') <= ' 04 ';

Way two:

Select Cardid, borrowdate from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' all '  --Query and To_ char (to_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') >= ' 2014-02 ' and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') <= ' 2014-04 ';

Way three:

Select Cardid, borrowdate from borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ')  

2) query + go to weight

Select distinct Cardid from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' all '  --query + redo and to_ char (to_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') >= ' 2014-02 ' and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') <= ' 2014-04 ';

Select distinct Cardid from borrow where To_date (Borrowdate, ' yyyy-mm-dd hh24:mi:ss ')  

3) query + de-weight + reader name and other information

With VT1 as (select distinct cardid from borrow where To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy ') = ' all ' and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') >= ' 2014-02 ' and To_char (To_date (borrowdate, ' yyyy-mm-dd '), ' yyyy-mm ') <= ' 2014-04 ') Select Cardid, name,org from reader where exists (select Cardid from VT1 where Vt1.cardid=reader.cardid);

Common SQL statements and cases (Oracle)