Oracle Database 12c: SQL Workshop I: 03 Restricting and Sorting Data_oracle database 12c sql tuning workshop-优快云博客

本文围绕SQL中数据的限制与排序展开。介绍了用WHERE子句限制行，包括各种比较条件、逻辑条件等；用ORDER BY子句排序，可指定升序、降序及NULL值位置。还提及SQL行限制子句，如OFFSET、FETCH等，以及如何使用替换变量补充各类SQL元素。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Restricting and Sorting Data

Limiting rows with:

——The WHERE clause

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE logical expression(s)];

——The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL conditions

SQL> select * from t2;

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2
ca                2      3

SQL> select * from t2 where t2name like 'c%';

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2
ca                2      3

SQL> select * from t2 where t2name like 'c_';

T2NAME               ID     NO
-------------------- ---------- ----------
ca                2      3

——The ESCAPE identifier to search for the actual % and _ symbols

SQL> select * from t2;

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2
co_a                  3      4
ca                2      3
co_at                 3      4

SQL> select * from t2 where t2name like 'co\_%' escape '\';

T2NAME               ID     NO
-------------------- ---------- ----------
co_a                  3      4
co_at                 3      4

SQL> select * from t2 where t2name like 'co\__' escape '\';

T2NAME               ID     NO
-------------------- ---------- ----------
co_a                  3      4

——Logical conditions using AND, OR, and NOT operators

Sorting rows using the ORDER BY clause
– ASC: Ascending order, default
– DESC: Descending order

NULL is ∞
Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

SQL> select * from t7;

    ID     NO
---------- ----------
     1      1
     1      2
     1      3
     2      1
     2      2
     2

6 rows selected.

SQL> select * from t7 order by no;

    ID     NO
---------- ----------
     1      1
     2      1
     1      2
     2      2
     1      3
     2

6 rows selected.

SQL> select * from t7 order by no asc;

    ID     NO
---------- ----------
     1      1
     2      1
     1      2
     2      2
     1      3
     2

6 rows selected.

SQL> select * from t7 order by no desc;

    ID     NO
---------- ----------
     2
     1      3
     2      2
     1      2
     1      1
     2      1

6 rows selected.

SQL> select * from t7 order by no desc nulls last;

    ID     NO
---------- ----------
     1      3
     1      2
     2      2
     2      1
     1      1
     2

6 rows selected.

SQL> select * from t7 order by no asc nulls first;

    ID     NO
---------- ----------
     2
     1      1
     2      1
     1      2
     2      2
     1      3

6 rows selected.

——by column alias
——by using the column’s numeric position
——by multiple columns

SQL> select id "place" from t7 order by place;
select id "place" from t7 order by place
                                   *
ERROR at line 1:
ORA-00904: "PLACE": invalid identifier


SQL> select id "place" from t7 order by "place";

     place
----------
     1
     1
     1
     2
     2
     2

6 rows selected.

SQL> select id place from t7 order by place;

     PLACE
----------
     1
     1
     1
     2
     2
     2

6 rows selected.

SQL> select id place from t7 order by "place";
select id place from t7 order by "place"
                                 *
ERROR at line 1:
ORA-00904: "place": invalid identifier


SQL> select * from t7 order by 1;

    ID     NO
---------- ----------
     1      1
     1      2
     1      3
     2      1
     2      2
     2

6 rows selected.

SQL> select * from t7 order by 3;
select * from t7 order by 3
                          *
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

SQL> select * from t7 order by id desc,no asc;

    ID     NO
---------- ----------
     2      1
     2      2
     2
     1      1
     1      2
     1      3

6 rows selected.

SQL> select * from t7 order by id desc,no asc nulls first;

    ID     NO
---------- ----------
     2
     2      1
     2      2
     1      1
     1      2
     1      3

6 rows selected.

The ORDER BY clause comes last in the SELECT statement:

SQL> select * from t7;

    ID     NO
---------- ----------
     1      1
     1      2
     1      3
     2      1
     2      2
     2

6 rows selected.

SQL> select * from t8;

       IDS    NOS
---------- ----------
     3      1
     3      2
     3

SQL> select * from t7 order by id
  2  intersect
  3  select * from t8;
intersect
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> select * from t7
  2  intersect
  3  select * from t8 order by id;
select * from t8 order by id
                          *
ERROR at line 3:
ORA-00904: "ID": invalid identifier


SQL> l
  1  select * from t7
  2  intersect
  3* select * from t8 order by id
SQL> c/id/ids
  3* select * from t8 order by ids
SQL> /
select * from t8 order by ids
                          *
ERROR at line 3:
ORA-00904: "IDS": invalid identifier

SQL> select id "place" from t7
  2  union
  3  select ids from t8 order by "place";

     place
----------
     1
     2
     3

SQL Row Limiting Clause

[ order_by_clause ]
[OFFSET offset { ROW | ROWS }]
[FETCH { FIRST | NEXT } [{ row_count | percent PERCENT
}] { ROW | ROWS }
{ ONLY | WITH TIES }]

Note that an ORDER BY clause is not required.
OFFSET: Use this clause to specify the number of rows to skip before row limiting begins. The value for offset must be a number. If you specify a negative number, offset is treated as 0. If you specify NULL or a number greater than or equal to the number of rows that are returned by the query, 0 rows are returned.
ROW | ROWS: Use these keywords interchangeably. They are provided for semantic clarity.
FETCH: Use this clause to specify the number of rows or percentage of rows to return.
FIRST | NEXT: Use these keywords interchangeably. They are provided for semantic clarity.
row_count | percent PERCENT: Use row_count to specify the number of rows to return. Use percent PERCENT to specify the percentage of the total number of selected rows to return.
The value for percent must be a number.
ONLY | WITH TIES: Specify ONLY to return exactly the specified number of rows or percentage of rows. Specify WITH TIES to return all rows that have the same sort keys as the last row of the row-limited result set (WITH TIES requires an ORDER BY clause)

Substitution Variables

Use substitution variables to supplement the following:
– WHERE conditions
– ORDER BY clauses
– Column expressions
– Table names
– Entire SELECT statements

SQL> select * from t2;

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2
co_a                  3      4
ca                2      3
co_at                 3      4

SQL> select &a from &b
  2  where &c
  3  order by &d;
Enter value for a: t2name
Enter value for b: t2
old   1: select &a from &b
new   1: select t2name from t2
Enter value for c: t2name='coat'
old   2: where &c
new   2: where t2name='coat'
Enter value for d: t2name
old   3: order by &d
new   3: order by t2name

T2NAME
--------------------
coat

SQL> select * from t2;

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2
co_a                  3      4
ca                2      3
co_at                 3      4

SQL> select &a;
Enter value for a: t2name from t2 where t2name like 'co%' order by t2name
old   1: select &a
new   1: select t2name from t2 where t2name like 'co%' order by t2name

T2NAME
--------------------
co_a
co_at
coat

SQL> select * from t2 where t2name=&a;
Enter value for a: coat
old   1: select * from t2 where t2name=&a
new   1: select * from t2 where t2name=coat
select * from t2 where t2name=coat
                              *
ERROR at line 1:
ORA-00904: "COAT": invalid identifier


SQL> select * from t2 where t2name='&a';
Enter value for a: coat
old   1: select * from t2 where t2name='&a'
new   1: select * from t2 where t2name='coat'

T2NAME               ID     NO
-------------------- ---------- ----------
coat                  1      2