Common SQL statements and cases (Oracle)

Source: Internet
Author: User
Tags abs chr mathematical functions square root string to number

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.