Restricting and Sorting Data

 

• Limit the rows that are retrieved by a query

限制从查询中返回的行

• Sort the rows that are retrieved by a query

对查询返回的行进行排序

• Use ampersand substitution（&替代变量） to restrict and sort output at runtime

使用替代变量来限制和排序输出

 

 

 

• Limiting rows with：

The WHERE clause

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

Logical conditions using AND,OR and NOT operators

• Rules of precedence for operators in an expression
• Sorting rows using the ORDER BY clause
• Substitution variables
• DEFINE and VERIFY commands

 

 

1、Restrict 限制（Where-condition）

The essential capabilities of SELECT statement are Selection, Projection and Joining. Displaying specific columns from a table is known as a project operation. We will now focus on displaying specific rows of output. This is known as a select operation. Specific rows can be selected by adding a WHERE clause to a SELECT query. As a matter of fact, the WHERE clause appears just after the FROM clause in SELECT query hierarchy. The sequence has to be maintained in all scenarios. If violated, Oracle raises an exception.

Syntax:

 

In the syntax,

 

The WHERE clause can compare values in columns, literals, arithmetic expressions, or functions. It consists of three elements:

• Column name
• Comparison condition
• Column name, constant, or list of values

 

Suppose that your manager is working on the quarterly budget for your organization. As part of this activity, it is necessary to produce a listing of each employee‘s essential details, but only for employees that are paid at least $25,000 annually. The SQL query below accomplishes this task. Note the use of the WHERE clause shown in bold text.

 

Points to be noted -

• You cannot use column alias in the WHERE clause(不能在WHERE 子句中使用列别名)
• A SELECT clause can contain only one WHERE clause. However, multiple filter conditions can be appended to WHERE clause using AND or OR operator.
• The columns, literals or expressions in a predicate clause must be of similar or interconvertible data types.
• Column alias cannot be used in the WHERE clause.
• Character literals must be enclosed within single quotation marks and are case sensitive.

字符串值，在单引号内，所有字符串搜索是大小写敏感的

• Date literals must be enclosed within single quotation marks and are format sensitive. Default format is DD-MON-RR.

日期值，在单引号内，格式敏感

数据库以一个内部的数字格式来存储日期值，表示世纪、年、月、日、时分、秒. 默认日期显示格式是DD-MON-RR

 

Comparison Operators比较操作符

Comparison operators are used in predicates to compare one term or operand with another term. SQL offers comprehensive set of equality, inequality and miscellaneous operators. They can be used depending on the data and filter condition logic in the SELECT query. When you use comparison operators in a WHERE clause, the arguments (objects or values you are comparing) on both sides of the operator must be either a column name, or a specific value. If a specific value is used, then the value must be either a numeric value or a literal string. If the value is a character string or date, you must enter the value within single quotation marks (‘ ‘).

Oracle has nine comparison operators to be used in equality or inequality conditions.

Other Oracle operators are BETWEEN..AND, IN, LIKE, and IS NULL.

 

The BETWEEN Operator

Use the BETWEEN operator to display rows based on a range of values.

(以范围查找)

The BETWEEN operator can be used to compare a column value within a definite range. The specified range must have a lower and upper limit where both are inclusive during comparison. Its use is similar to composite inequality operator (<= and >=). It can be used with numeric, character and date type values.

For example, the WHERE condition SALARY BETWEEN 1500 AND 2500 in a SELECT query will list those employees whose salary is between 1500 and 2500.

You can also use BETWEEN operator on character values.

BETWEEN也可以用于字符串值；这时候会按照英文字母排序来查找.

 

The IN Operator

Use the IN operator to test for values in a list

（测试一个值是否是一个特定值集合中,使用IN操作符）

The IN operator can be used with any data type.

IN操作符可以用于任何数据类型。

If characters or dates are used in the list, they must be enclosed with single quotation marks(‘‘)

如果是字符或日期格式，需要用单引号括起来.

The IN operator is used to test a column value in a given set of value. If the column can be equated to any of the values from the given set, the condition is validated. The condition defined using the IN operator is also known as the membership condition.

For example, the WHERE condition SALARY IN (1500, 3000, 2500) in a SELECT query will restrict the rows where salary is either of 1500, 3000 or 2500.

The IN operator is internally evaluated by the ORACLE server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. therefore, using the IN operator has no performance benefits and is used only for logical simplicity.

 

 

The LIKE Operator

The LIKE operator is used for pattern matching and wildcard searches in a SELECT query. If a portion of the column value is unknown, wildcard can be used to substitute the unknown part. It uses wildcard operators to build up the search string, thus search is known as Wildcard search. These two operators are Percentile (‘%‘) and Underscore (‘_‘). Underscore (‘_‘) substitutes a single character while percentile (‘%‘) replaces more than one characters. They can be used in combination as well.

For example, the below SELECT query lists the first names of those employees whose last name starts with ‘SA‘.

Use the LIKE operator to perform wildcard通配符 searches of valid search string values.

Search conditions can contain either literal characters or numbers:

You can combine the two wildcard characters(%,_) with literal characters for pattern matching.

 

ESCAPE identifier

You can use ESCAPE identifier(标识符) to search for the actual % and _ symbols.

When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier.

This option specifies what the escape(转义) character is.

If you want to search for stings that contain SA_, you can use the following SQL statement:

 

The ESCAPE identifier identifies the backslash(\) as the escape character.

In the SQL statement, the escape character precedes the underscore(_).

This causes the ORACLE server to interpret the underscore literally.

 

 

IS (NOT) NULL Conditions

The NULL conditions include the IS NULL condition and the IS NOT NULL condition.

The IS NULL condition tests for nulls.

The null value means that the value is unavailable, unassigned, unknown, or inapplicable.

Therefore, you cannot test with=, because a null cannot be equal or unequal to any value.

不能使用=号测试null，因为null不等于任何值.

To be noted, NULL values cannot be tested using equality operator. It is because NULL values are unknown and unassigned while equality operator tests for a definite value. The IS NULL operator serves as equality operator to check NULL values of a column.

For example, the WHERE condition COMMISSION_PCT IS NULL in a SELECT query will list employees who don‘t have commission percentage.

 

Defining conditions Using the Logical Operators

Multiple filter conditions can be added to the WHERE clause predicate. More than one condition can be combined together using logical operators AND, OR and NOT.

• AND:	Returns TRUE if both component conditions are true. joins two or more conditions, and returns results only when all of the conditions are true.
  OR:	Returns TURE if either component condition is true. joins two or more conditions, and it returns results when any of the conditions are true.
  NOT:	Returns TRUS if the condition is false. negates the expression that follows it.

 

The AND operator links two or more conditions in a WHERE clause and returns TRUE only if all the conditions are true. Suppose that a manager needs a list of female employees. Further, the list should only include employees with last names that begin with the letter "E" or that come later in the alphabet. Additionally, the result table should be sorted by employee last name. There are two simple conditions to be met. The WHERE clause may be written as: WHERE Gender = ‘F‘ AND last_name > ‘E‘.

 

 

The OR operator links more than one condition in a WHERE clause and returns TRUE if either of the condition returns true. Suppose that your organizational manager‘s requirements change a bit. Another employee listing is needed, but in this listing the employees should: (1) be female or, (2) have a last name that begins with the letter "T" or a letter that comes later in the alphabet. The result table should be sorted by employee last name. In this situation either of the two conditions can be met in order to satisfy the query. Female employees should be listed along with employees having a name that satisfies the second condition.

 

The NOT operator is used to negate an expression or conition.

 

2、Precedence优先级

Rules of precedence for operators in an expression

 

3、Sort 排序

Sorting rows using the ORDER BY clause

The ORDER BY Clause

Sort the retrieved rows with the ORDER BY clause:

The default sort order is ascending(默认的排序是升序)

 

You can specify an expression, an alias, or a column position as the sort condition

你可以指定一个表达式，别名，或列的位置作为排序条件.

If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice.

如果不使用ORDER BY 排序，对同一个查询执行两次，可能不返回一样的顺序.

 

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.

 

When you display only a few rows of data, it may be unnecessary to sort  the output; however, when you display numerous rows, managers may be aided in decision making by having the information sorted. Output from a SELECT statement can be sorted by using the optional ORDER BY clause. When you use the ORDER BY clause, the column name on which you are ordering must also be a column name that is specified in the SELECT clause.

 

The below SQL query uses an ORDER BY clause to sort the result table by the last_name column in ascending order. Ascending order is the default sort order.

 

 

Sorting can be based on numeric and date values also. Sorting can also be done based on multiple columns.

 

By default, the ORDER BY clause will sort output rows in the result table in ascending order. We can use the keyword DESC (short for descending) to enable descending sort. The alternative default is ASC which sorts in ascending order, but the ASC keyword is rarely used since it is the default. When the ASC or DESC optional keyword is used, it must follow the column name on which you are sorting in the WHERE clause.

 

Positional Sorting - Numeric position of the column in the selected column list can be given in ORDER BY clause, instead of column name. It is mainly used in UNION queries (discussed later). The Query orders the result set by salary since it appears 2nd in the column list.

 

4、Substitution Variables（替代变量）

在WHERE子句中，通过使用替代变量来代替精确的值，就可以用不同的值运行同一个查询.

• Use substitution variables to:
• Temporarily store values with single-ampersand(&) and double-ampersand(&&) substitution
• Use substitution variables to supplement the following:
• WHERE conditions
• ORDER BY clauses
• Column expressions
• Table names
• Entire SELECT statements

 

Using the single-Ampersand Substitution Variable（&）

When a SQL query has to be executed more than once for the different set of inputs, substitution variables can be used. Substitution variables can be used to prompt for user inputs before the query execution. They are widely used in query based report generation which takes data range from the users as input for the conditional filtering and data display. Substitution variables are prefixed by a single-ampersand (&) symbol to temporarily store values. For example,

 

 

When the above SELECT query is executed, oracle identifies the ‘&‘ as substitution variable. It prompts user to enter value for ‘last_name‘ and ‘EMPNO‘ as below.

 

 

Once the user provides inputs to both the variables, values are substituted, query is verified and executed.

 

Points to be noted -

• If the variable is meant to substitute a character or date value, the literal needs to be enclosed in single quotes. A useful technique is to enclose the ampersand substitution variable in single quotes when dealing with character and date values. 字符串值和日期值需要用单引号引起来，所以要将替代变量用单引号引起来.
• Both SQL Developer and SQL* Plus support the substitution variables and the DEFINE/UNDEFINE commands. Though SQL Developer or SQL* Plus does not support validation checks (except for data type) on user input.
• You can use the substitution variables not only in the WHERE clause of a SQL statement, but also as substitution for column names, expressions, or text.
• 也可以将替代变量用作列名、表达式、文本

如果不输入替代变量的值，将会得到一个错误

替代变量可用于SELECT语句的任何地方，除了在命令提示符后作为第一个单词输入.

 

Using the Double-Ampersand Substitution Variable（&&）

&&用户只需要输入一次就可以了，&用户需要每次输入

如果想重用变量值而不用每次提示用户进行输入，可以使用两个&&的替代变量.

When the same substitution variable is used at more than one place, then to avoid re-entering the same data again, we use double ampersand substitution. In such cases, value of the substitution variable, once entered, would be substituted at all instants of usage.

 

 

Note that the same value of &DT is substituted twice in the above query. So, its value once given by the user will be substituted at two places.

 

SQL Developer stores the value that is supplied by using the DEFINE command.

SQL Developer存储使用DEFINE命令所提供的值.

It uses it again whenever you reference the variable name. After a user variable is in place, you need to use the UNDEFINE command to delete it:

 

UNDEFINE colum_name

 

The DEFINE and VERIFY Commands

 

Use the DEFINE command to create and assign a value to a variable.

定义一个替代变量

Use the UNDEFINE command to remove a variable.

删除一个替代变量

Setting the definition of variables in a session is set by DEFINE feature of SQL* Plus. The variables can be defined in the session, so as to avoid halt during query execution. Oracle reads the same variable whenever encountered in an SQL query. It is in ON state by default. With the help of DEFINE clause, one can declare a variable in command line before query execution as DEFINE variable=value;.

 

Use the VERIFY command to toggle切换 the display of the substitution variable, both before and after SQL Developer replaces substitution variables with values.

要确认SQL语句的变化，使用VERIFY命令

SET VERIFY ON/OFF

 

Verify command verifies the above substitution showing as OLD and NEW statement. It is OFF by default and can be set to ON using SET command.