Sql Certificate 简明教程

SQL - The SQL SELECT Statement

Retrieving data using the SQL Select Statement

SQL 是一种综合性的数据库语言。SQL，发音为 Sequel 或 S-Q-L，是一种用于非过程化查询关系数据库的计算机编程语言。使用 SQL 从数据库中提取信息时，这被称为查询数据库。

SQL is a comprehensive database language. SQL, pronounced Sequel or simply S-Q-L, is a computer programming language used for querying relational databases following a nonprocedural approach. When you extract information from a database using SQL, this is termed querying the database.

关系数据库是通过使用关系数据库管理系统 (RDBMS) 实现的。RDBMS 执行上述 DBMS 软件的所有基本功能，以及使关系模型更易于理解和实现的大量其他功能。RDBMS 用户通过使用特殊的数据操纵语言来操纵数据。数据库结构是通过使用数据定义语言来定义的。系统用户为了存储和检索数据而执行的命令可以通过键入命令通过一个具有 RDBMS 界面或使用某种图形化界面的方式来输入终端。然后 DBMS 处理这些命令。

A relational database is implemented through the use of a Relational Database Management System (RDBMS). An RDBMS performs all the basic functions of the DBMS software mentioned above along with a multitude of other functions that make the relational model easier to understand and to implement. RDBMS users manipulate data through the use of a special data manipulation language. Database structures are defined through the use of a data definition language. The commands that system users execute in order to store and retrieve data can be entered at a terminal with an RDBMS interface by typing the commands, or entered through use of some type of graphical interface. The DBMS then processes the commands.

Capabilities of the SELECT Statement

通过适当且有效地使用 SQL 从数据库中检索数据。关系理论中的三个概念涵盖了 SELECT 语句的功能：投影、选择和链接。

Data retrieval from data base is done through appropriate and efficient use of SQL. Three concepts from relational theory encompass the capability of the SELECT statement: projection, selection, and joining.

  1. Projection: A project operation selects only certain columns (fields) from a table. The result table has a subset of the available columns and can include anything from a single column to all available columns.

  2. Selection: A select operation selects a subset of rows (records) in a table (relation) that satisfy a selection condition. The ability to select rows from out of complete result set is called Selection. It involves conditional filtering and data staging. The subset can range from no rows, if none of the rows satisfy the selection condition, to all rows in a table.

  3. Joining: A join operation combines data from two or more tables based on one or more common column values. A join operation enables an information system user to process the relationships that exist between tables. The join operation is very powerful because it allows system users to investigate relationships among data elements that might not be anticipated at the time that a database is designed.

sql 1

考虑上述表结构。从 EMPLOYEES 表中提取第一姓名姓名、部门 ID 和单个员工的工资是投影。从 EMPLOYEES 表中提取工资低于 5000 的员工详细信息是选择。通过链接 EMPLOYEES 和 DEPARTMENTS 来提取员工的第一姓名、部门姓名是链接。

Consider the above table structures. Fetching first_name name, department_id and salary for a single employee from EMPLOYEES table is Projection. Fetching employee details whose salary is less than 5000, from EMPLOYEES table is Selection. Fetching employee’s first name, department name by joining EMPLOYEES and DEPARTMENTS is Joining.

Basic SELECT statement

选择语句的基本语法如下。

The basic syntax for a SELECT statement is presented below.

SELECT  [DISTINCT | ALL] {* | select_list}
FROM {table_name [alias] | view_name}
    [{table_name [alias]  | view_name}]...
[WHERE  condition]
[GROUP BY  condition_list]
[HAVING  condition]
[ORDER BY  {column_name | column_#  [ ASC | DESC ] } ...

SELECT 子句是必须的，并且执行关系投影操作。

The SELECT clause is mandatory and carries out the relational project operation.

FROM 子句也是必须的。它标识从中检索结果表中显示的列数据的表或视图中的一个或多个表。

The FROM clause is also mandatory. It identifies one or more tables and/or views from which to retrieve the column data displayed in a result table.

WHERE 子句是可选的，并且执行关系选择操作。它指定要选择哪些行。

The WHERE clause is optional and carries out the relational select operation. It specifies which rows are to be selected.

GROUP BY 子句是可选的。它按 SELECT 子句中列出的一个或多个列名将数据组织到组中。

The GROUP BY clause is optional. It organizes data into groups by one or more column names listed in the SELECT clause.

可选的 HAVING 子句设置有关在结果表中应包含哪些组的条件。这些组由 GROUP BY 子句指定。

The optional HAVING clause sets conditions regarding which groups to include in a result table. The groups are specified by the GROUP BY clause.

ORDER BY 子句是可选的。它按升序或降序对查询结果按一列或多列进行排序。

The ORDER BY clause is optional. It sorts query results by one or more columns in ascending or descending order.

Arithmetic expressions and NULL values in the SELECT statement

可以使用列名、操作符和常量值来创建算术表达式，以将表达式嵌入到 SELECT 语句中。适用于列的操作符取决于该列的数据类型。例如，算术运算符不适用于字符文本值。例如，

An arithmetic expression can be created using the column names, operators and constant values to embed an expression in a SELECT statement. The operator applicable to a column depends on column’s data type. For example, arithmetic operators will not fit for character literal values. For example,

SELECT employee_id, sal * 12 ANNUAL_SAL
FROM employees;

上面的查询包含算术表达式 (sal * 12) 以计算每位员工的年薪。

The above query contains the arithmetic expression (sal * 12) to calculate annual salary of each employee.

Arithmetic operators

运算符对列（称为运算数）进行操作，以产生不同的结果。如果表达式中有多个运算符，则运算顺序由运算符优先级决定。以下是优先级的基本规则 -

Operators act upon the columns (known as operands) to result into a different result. In case of multiple operators in an expression, the order of evaulation is decided by the operator precedence. Here are the elementary rules of precedence -

  1. Multiplication and division occur before Addition and Subtraction.

  2. Operators on the same priority are evaluated from left to right.

  3. Use paretheses to override the default behavior of the operators.

下表显示了在这种情况下运算符的优先级。优先级级别 运算符符号 操作

Below table shows the precedence of the operators, in such cases. Precedence Level Operator Symbol Operation

Description   Operator	Precedence
Addition	+	Lowest
Subtraction	-	Lowest
Multiplication	*	Medium
Division	/	Medium
Brackets	( )	Highest

检查下面的查询 (a)、(b) 和 (c)

Examine the below queries (a), (b), and (c)

  1. SQL> SELECT 2*35 FROM DUAL;

  2. SQL> SELECT salary + 1500 FROM employees;

  3. SQL> SELECT first_name, salary, salary + (commission_pct* salary) FROM employees;

查询 (a) 乘以两个数字，而 (b) 显示向所有员工的工资增加 1500 美元。查询 (c) 显示了向员工工资中添加佣金组件。根据优先级，将首先计算佣金，然后将其添加到工资中。

Query (a) multiplies two numbers, while (b) shows addition of $1500 to salaries of all employees. Query (c) shows the addition of commission component to employee’s salary. As per the precedence, first commission would be calculated on the salary, and then added to the salary.

Column Alias

别名用于在显示期间重命名列或表达式。列或表达式的别名显示为查询输出中的标题。为 SELECT 查询中的长表达式提供有意义的标头非常有用。默认情况下，别名以大写字母形式显示在查询输出中，不含空格。如果要覆盖此行为，必须用双引号将别名括起来，以保留别名名称中的大小写和空格。

An alias is used to rename a column or an expression during display. The alias to a column or an expression appears as the heading in the output of a query. It is useful in providing a meaningful heading to long expressions in the SELECT query. By default, the alias appears in uppercase in the query output without spaces. To override this behavior, alias must be enclosed within double quotes to preserve the case and spaces in the alias name.

SELECT price * 2 as DOUBLE_PRICE, price * 10 "Double Price"
FROM products;

DOUBLE_PRICE	Double Price
------------	------------
39.9			39.9
60			60
51.98			51.98

Concatenation operators

连接运算符可用于在 SELECT 查询中联接两个字符串值或表达式。双竖线符号用作字符串连接运算符。它仅适用于字符和字符串列值，生成一个新的字符表达式。示例

Concatenation operator can be used to join two string values or expressions in a SELECT query. The double vertical bar symbol is used as string concatenation operator. It is applicable only for character and string column values resulting into a new character expression. Example

SQL> SELECT 'ORACLE'||' CERTIFICATION' FROM dual;

上述查询显示了两个字符文本值连接。

The above query shows concatenation of two character literals values.

Literals

SELECT 语句中数据库中不存储的任何硬编码值称为文本值。它可以是数字、字符或日期值。字符值和日期值必须用引号括起来。考虑以下 SQL 查询。SQL 查询中使用不同数据类型的文本值的示例。

Any hard coded value, which is not stored in database, in the SELECT clause, is known s Literal. It can be number, character, or date value. Character and date values must be enclosed within quotes. Consider the below SQL queries.examples of using literals of different data types in SQL queries.

以下查询使用两个字符文本值将它们连接在一起。

The query below uses two character literals to join them together.

SQL> SELECT 'ORACLE'||' CERTIFICATION' FROM DUAL

以下查询使用字符文本值以美观方式打印员工的薪水。

The query below uses character literals to pretty print the employee’s salary.

SQL> SELECT first_name ||'earns'|| salary||' as of '|||sysdate
FROM employees

Quote Operator

引用运算符用于指定您自己的引号分隔符。您可以根据数据选择合适的分隔符。

The quote operator is used to specify the quotation mark delimiter of your own. You can chose a convenient delimiter, depedning on the data.

SELECT 	department_name|| ' Department' ||q'['s Manager Id: ]'|| manager_id
FROM departments;

NULL

如果某个列没有确定的值，则将其视为 NULL。NULL 值表示未知或不可用。对于数字值，它不是零；对于字符值，它不是空格。

If a column doesn’t has a definite value, it is considered as NULL. NULL value denotes unknown or unavailable. It is not zero for numeric values, not blank space for character values.

包含 NULL 值的列可在 SELECT 查询中选择，并且可以作为算术表达式的部分。使用 NULL 值的任何算术表达式都将生成 NULL。出于此原因，必须使用 Oracle 提供的 NVL 或 NULLIF 等函数指定其备用值来以不同的方式处理具有 NULL 值的列。

Columns with NULL value can be selected in a SELECT query and can be the part of an arithmetic expression. Any arithmetic expression using NULL values results into NULL. For this reason, columns with NULL value must be handled differently by specifying their alternate values using Oracle supplied functions like NVL or NULLIF.

SQL> SELECT NULL + 1000 NUM
FROM DUAL;

NUM
--------

DISTINCT Keyword

如果预计数据会出现重复的结果，请使用 DISTINCT 关键字消除重复项，并在查询输出中只显示唯一结果。仅针对所选列验证是否存在重复项，并且会在查询输出中从逻辑上消除行。请注意，DISTINCT 关键字必须出现在 SELECT 子句之后。

If the data is expected to have duplicate results, use DISTINCT keyword to eliminate duplicates and diplay only the unique results in the query output. Only the selected columns are validated for duplication and the rows will be logically eliminated from the query output. To be noted, the DISTINCT keyword must appear just after the SELECT clause.

以下简单查询演示了使用 DISTINCT 从 EMPLOYEES 表中显示唯一部门 ID。

The simple query below demonstrates the use of DISTINCT to display unique department ids from EMPLOYEES table.

SQL> SELECT DISTINCT DEPARTMENT_ID
FROM employees;

DEPARTMENT_ID
---------------
10
20
30
40

DESCRIBE command

可以使用 DESCRIBE 命令通过查询列的列表获取表的结构化元数据，这将组成该列表。它将列出所用的列名、它们的空属性和数据类型。

The structural metadata of a table may be obtained by querying the database for the list of columns that comprise it using the DESCRIBE command. It will list the used column names, their null property and data type.

Syntax:

Syntax:

DESC[RIBE] [SCHEMA].object name

例如，

For example,

DESC EMPLOYEE

将显示 EMPLOYEE 表结构，即列、它们的数据类型、长度和可空属性。

will display the EMPLOYEE table structure i.e. columns, their data types, precision and nullable property.