How to Use oraclesqlselect statements

Format: SELECT [ALL | DISTINCT] & lt; field expression 1 [, & lt; field expression 2 [, & hellip;] FROM & lt; table name 1 & gt ;, & lt; table name 2 & gt; [, & hellip;] [WHERE & lt; screening selection condition Expression & gt;] [GROUPBY & lt; Grouping Expression & gt; [HAVING & lt; Grouping condition Expression & gt;] [ORDERB

Format:
SELECT [ALL | DISTINCT] <字段表达式1[,<字段表达式2[,…]
FROM <表名1> , <表名2> [,…]
[WHERE <筛选择条件表达式> ]
[GROUP <分组表达式> [HAVING <分组条件表达式> ]
[ORDER <字段> [ASC | DESC]

Statement description:
[] Square brackets are optional
[GROUP <分组表达式> [HAVING <分组条件表达式> ]
Indicates to press <分组表达式> Groups the values of HAVING]
The phrase is output only when the Group meets the specified conditions.
[ORDER <字段> [ASC | DESC]
To display the results, press <字段> Sort values in ascending or descending order

Exercise:
1: In the hkb_test_sore table, records of the top five sore scores are obtained,
2: records with 5th names

1. select a. sore_id, a. sore
From (select * from hkb_test_sore order by sore desc)
Where rownum <= 5

2. select a. sore_id, a. sore
From (select * from hkb_test_sore order by sore desc)
Where rownum <= 5
Minus
Select a. sore_id, a. sore
From (select * from hkb_test_sore order by sore desc)
Where rownum <= 4;
3: Query two records with the same score
Select *
From hkb_test_sore
Where a. sore = (select sore
From hkb_test_sore
Group by a. sore
Having count (a. sore) = 2 );

Difference between union, union all, intersect and minus:
SQL> select * from hkb_test2;
X Y
---------
A 1
B 2
C 3
G 4

SQL> select * from hkb_test3;
X Y
---------
A 1
B 2
E 3
F 4

SQL> select * from hkb_test2;
X Y
---------
A 1
B 2
C 3
G 4

SQL> select * from hkb_test3;
X Y
---------
A 1
B 2
E 3
F 4

SQL> select * from hkb_test2
2 union
3 select * from hkb_test3;
X Y
---------
A 1
B 2
C 3
E 3
F 4
G 4

6 rows selected

SQL> select * from hkb_test2
2 union all
3 select * from hkb_test3;
X Y
---------
A 1
B 2
C 3
G 4
A 1
B 2
E 3
F 4

8 rows selected

SQL> select * from hkb_test2
2 intersect
3 select * from hkb_test3;
X Y
---------
A 1
B 2

SQL> select * from hkb_test2
2 minus
3 select * from hkb_test3;
X Y
---------
C 3
G 4

View the complete instance based on the above instance

SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee (
2 ID VARCHAR2 (4 BYTE) not null primary key,
3 First_Name VARCHAR2 (10 BYTE ),
4 Last_Name VARCHAR2 (10 BYTE ),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number (8, 2 ),
8 City VARCHAR2 (10 BYTE ),
9 Description VARCHAR2 (15 bytes)
10)
11/

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01 ', 'jason', 'martin ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960725, 'toronto ', 'programmer ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('02 ', 'alison', 'mathews ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19760321, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('03', 'James ', 'Smith', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19781212, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('04 ', 'cela', 'Rice', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19821024, 'vancouver ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('05 ', 'Robert', 'black', to_date ('000000', 'yyyymmdd'), to_date ('20140901', 'yyyymmdd'), 19840115, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('06', 'linda ', 'green', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19870730, 'New York ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('07 ', 'David', 'Larry', to_date ('123', 'yyyymmdd'), to_date ('123456', 'yyyymmdd'), 19901231, 'New York ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('08', 'James ', 'cat', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960917, 'vancouver ', 'tester ')
3/

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2/

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------------------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

8 rows selected.

SQL>
SQL>
SQL> SELECT id, first_name, last_name FROM employee
2/

ID FIRST_NAME LAST_NAME
--------------------------------------------
01 Jason Martin
02 Alison Mathews
03 James Smith
04 Celia Rice
05 Robert Black
06 Linda Green
07 David Larry
08 James Cat

8 rows selected.

How to implement select top n in ORACLE
1. implement select top n in ORACLE

Since ORACLE does not support the select top statement, order by and ROWNUM are often used in ORACLE to query the select top n statement.

To put it simply, the implementation method is as follows:

SELECT column name 1... column name n FROM

(SELECT column name 1... column name n FROM table name order by column name 1... column name n)

Where rownum <= N (number of Records extracted)

ORDER BY ROWNUM ASC

The following is a simple example.

The customer (id, name) Table has the following data:

ID NAME

01 first

02 Second

03 third

04 forth

05 th

06 sixth

07 seventh

08 eighth

09 ninth

10 tenth

11 last

The SQL statements of the first three customers are extracted by NAME as follows:

SELECT * FROM

(SELECT * from customer order by name)

Where rownum <= 3

ORDER BY ROWNUM ASC

Output result:

ID NAME

08 eighth

05 th

01 first

2. Extract the nth M (M <= N) record from the top n record

After obtaining the top n data, we can start with ROWNUM to extract the M record from the N records. We know that ROWNUM is a hidden sub-segment of the Data number in the record table, so we can extract the row num of the record when we get the top n records, then, we extract records numbered M from the N records, even if we want to get the results.

From the above analysis, you can easily obtain the following SQL statement.

SELECT column name 1... column name n FROM

(

Select rownum recno, column name 1... column name nFROM

(SELECT column name 1... column name n FROM table name order by column name 1... column name n)

Where rownum <= N (number of Records extracted)

ORDER BY ROWNUM ASC

)

Where recno = M (M <= N)

Based on the data in the preceding table, the SQL statement for obtaining the information of the second customer in alphabetical order of NAME should be written as follows:

Select id, NAME FROM

(

Select rownum recno, ID, NAME FROM

(SELECT * from customer order by name)

Where rownum <= 3

Order by rownum asc)

Where recno = 2

The result is:

ID NAME

05 th

3. Extract the N records from the record set sorted in a certain way

In the description in 2, when M is N, it is the result of our title. In fact, the N> M data in the 2 approach is basically not used. We just use it to illustrate convenience.

As described above, the SQL statement should be:

SELECT column name 1... column name n FROM

(

Select rownum recno, column name 1... column name nFROM

(SELECT column name 1... column name n FROM table name order by column name 1... column name n)

Where rownum <= N (number of Records extracted)

ORDER BY ROWNUM ASC

)

Where recno = N

The SQL statement in example 2 is:

Select id, NAME FROM

(

Select rownum recno, ID, NAME FROM

(SELECT * from customer order by name)

Where rownum <= 2

ORDER BY ROWNUM ASC

)

Where recno = 2

Result:

ID NAME

05 th

4. Extract the X records starting from the M records in the record set sorted in a certain way

3 is just about extracting a record. When we need to extract multiple records, at this time, the value of N in 2 should be in the range of N> = (M + X-1). It is time to make the most economical value equal to the value. Of course, the final extraction condition is not RECNO = N, it should be recno between m and (M + X-1), so the following SQL statement is:

SELECT column name 1... column name n FROM

(

Select rownum recno, column name 1... column name nFROM

(

SELECT column name 1... column name n FROM table name order by column name 1... column name n)

Where rownum <= N (N> = (M + X-1 ))

ORDER BY ROWNUM ASC

)

Where recno between m and (M + X-1)

Take the preceding data as an example. The SQL statement for extracting the three records starting from the first 2nd records with the NAME letter is as follows:

Select id, NAME FROM

(

Select rownum recno, ID, NAME FROM

(SELECT * from customer order by name)

Where rownum <= (2 + 3-1)

ORDER BY ROWNUM ASC

)

Where recno between 2 AND (2 + 3-1)

Note:

The execution sequence of each SQL clause:

1. FROM
2. WHERE
3. GROUP
4. HAVING
5. SELECT
6. ORDER