SQL Fundamentals：Restricting and Sorting Data限制和排序数据（FROM-WHERE-SELECT-ORDER BY）

 SQL Fundamentals || Oracle SQL语言

 

• 控制操作的显示列：基本的SELECT语句
• 控制行：限定查询和排序显示
• 分组统计查询

 

• 限定查询：WHERE字句
• 排序显示：ORDER BY 字句

 

 

一、限定查询语法：

SELECT [DISTINCT] * | 列名称 [AS] [列别名] , 列名称 [AS] [列别名] ,...

FROM 表名称 [表别名]

[WHERE 条件(s) ];

 

各个子句的执行顺序：

 

1、FROM-->

2、WHERE -->

3、GROUP BY（HAVING子句）-->

4、SELECT-->

5、ORDER BY-->

 

 

第一步：执行FROM字句，控制数据来源

第二步：执行WHERE字句，使用限定符进行数据行的过滤

第三步：执行SELECT字句，确定要显示的数据列

WHERE字句不能使用SELECT字句中的别名？why？就是因为WHERE字句先于SELECT执行。

 

• 限定排序主要使用WHERE子句，用于对选取的数据行进行控制。
• 限定查询主要的运算符：关系运算、BETWEEN…AND、IN、IS NULL、LIKE。

限定运算符

• 运算符

  符号

  描述

  关系运算符

  >、<、>=、<=、=、!=、<>

  进行大小或相等的比较，其中不等于有两种：!=和<>

  判断null

  IS NULL、IS NOT NULL

  判断某一列的内容是否是null

  语法：

  判断为NULL：字段 | 值 IS NULL；

  判断不为NULL：字段 | 值 IS NOT NULL（NOT 字段 | 值 IS NULL）；

  逻辑运算符

  AND、OR、NOT

  AND表示多个条件必须同时满足，OR表示只需要有一个条件满足即可，NOT表示条件去反，即：真变假、假变真

  在编写WHERE子句判断条件时，可以同时指定多个判断条件的连接，而连接主要通过逻辑运算符实现，逻辑运算符一共有以下三种：

  与（AND）	连接多个条件，多个条件同时满足时才返回TRUE，有一个条件不满足结果就是FALSE；
  或（OR）	连接多个条件，多个条件之中只要有一个返回TRUE，结果就是TRUE，如果多个条件返回的都是FALSE，则结果才是FALSE；
  非（NOT）	求反操作，可以将TRUE变FALSE、FALSE变TRUE。

  范围查询

  BETWEE 最小值 AND 最大值

  在一个指定范围中进行查找，查找结果为：“最小值<=内容<=最大值”

  范围查询

  IN

  通过IN可以指定一个查询的范围

  模糊查询

  LIKE

  可以对指定的字段进行模糊查询

  语法：

  满足模糊查询：字段 | 值 LIKE 匹配标记；

  不满足模糊查询：字段 | 值 NOT LIKE 匹配标记；

  如果现在想对查询某一列进行模糊查询，可以使用LIKE子句完成，通过LIKE可以进行关键字的模糊查询，在LIKE子句中有两个通配符：

  百分号（%）：可匹配任意类型和长度的字符，如果是中文则使用两个百分号（%%）；

  下划线（_）：匹配单个任意字符，它常用来限制表达式的字符长度

 

二、排序显示：ORDER BY 字句

排序显示语法：

SELECT [DISTINCT] * | 列名称 [AS] 列别名,列名称 [AS] 列别名

FROM 表名称 表别名

[WHERE 条件(s) ]

[ORDER BY 排序的字段 | 列索引序号 ASC|DESC ,排序的字段2 ASC | DESC ..] … ;

 

ORDER BY自居中指定要进行排序的字段，字段有2中排序模式：

升序：ASC,默认

降序：DESC，需要编写

执行顺序：

1、FROM

2、WHERE

3、SELECT

4、ORDER BY

因为ORDER BY是在SELECT执行后执行，那么ORDER BY就可以使用SELECT字句中设置的别名。

 

SQL语法

SELECT [DISTINCT] * | 列 [AS] [别名] , 列 [AS] [别名],...

FROM 表名称 [别名]

[WHERE 限定条件(s)]

[ORDER BY 排序字段 [ASC | DESC] [,排序字段 [ASC | DESC] ...]];

使用ORDER BY子句可以对查询结果进行排序，ORDER BY子句一定要写在所有查询语句的最后；

 

 

 

 详细介绍

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

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

 

Ampersand 英 [ˈæmpəsænd] 美 [ˈæmpərsænd]	n.&的记号名称
substitution 英 [ˌsʌbstɪ'tju:ʃn] 美 [ˌsʌbstɪˈtuʃən, -ˈtju-]	n.替换;代替;代用;<化>取代（作用）

 

 

• 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:

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

 

In the syntax,

WHERE	clause is the keyword Restricts the query to rows that meet a condition
[condition]	contains column names, expressions, constants, literals and a comparison operator. Is composed of column names, expressions, constants, and a comparison operator. A condition specifies a combination of one or more expressions and logical operators, and returns a value of TRUE, FALSE ,or UNKNOWN.

 

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.

SELECT Employee_ID, Last_Name, First_Name, Salary
FROM employees
WHERE Salary >= 25000; 

EMPLOYEE_ID LAST_NAME        FIRST_NAME       SALARY
----------   --------------- --------------- -----------
88303        Jones            Quincey          $30,550.00
88404        Barlow           William          $27,500.00
88505        Smith            Susan            $32,500.00

3 rows selected

 

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.

Operator	Meaning
=	equal to
<	less than
>	greater than
>=	greater than or equal to
<=	less than or equal to
!=	not equal to
<>	not equal to
!>	not greater than
!<	not less than··
BETWEEN…AND…	Between two values(inclusive)
IN(set)	Match any of a list of values
LIKE	Match a character pattern
IS NULL	Is a null value

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'.

SELECT first_name
FROM employees
WHERE last_name LIKE 'SA%';

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

Search conditions can contain either literal characters or numbers:

%	denotes zero or many characters 表示0个或多个字符 Represents any sequence of zero or more characters
_	denotes one character 表示一个字符 Represents any single character，代表任意单个字符

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:

SELECT emp_id

FROM emp

WHERE emp_id like '%SA\_%' ESCAPE '\';

 

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'.

 

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"
FROM employees
WHERE Gender = 'F' AND last_name > 'E'
ORDER BY last_name;

 

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

1	Arithmetic operators
2	concatenation operator
3	comparison conditions
4	IS [NOT] NULL,LIKE,[NOT] IN
5	[NOT] BETWEEN
6	Not equal to
7	NOT logical condition
8	AND logical condition
9	OR logical condition

 

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(默认的排序是升序)

ASC	Ascending order, default
DESC	Descending order

 

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

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

Sorting in descending order	SELECT ename FROM emp ORDER BY ename DESC;
sorting by column alias	SELECT ename sal,sal*12 annsal FROM emp ORDER BY annsal; annsal是别名
Sorting by the column's numeric(数字) position	SELECT ename, sal FROM emp ORDER BY 2; 这里的2是指按照第二个位置的列名进行排序，也就是sal.
Sorting by multiple columns	SELECT ename,sal FROM emp ORDER BY ename asc,sal desc; 同时指定多个列进行排序，用逗号隔开

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.

SQL> select ename,job,sal from emp order by job,sal desc nulls first;

 

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.

 

SELECT last_name, first_name
FROM employees
WHERE last_name >= 'J'
ORDER BY last_name;

last_name        first_name
--------------- ---------------
Jones            Quincey
Klepper          Robert
Quattromani      Toni
Schultheis       Robert

 

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.

SELECT first_name, salary
FROM employees
ORDER BY 2;