Teradata 简明教程
Teradata - SELECT Statement
SELECT 语句用于从表中检索记录。
SELECT statement is used to retrieve records from a table.
Syntax
以下是 SELECT 语句的基本语法。
Following is the basic syntax of SELECT statement.
SELECT
column 1, column 2, .....
FROM
tablename;Example
考虑以下员工表。
Consider the following employee table.
EmployeeNo |
FirstName |
LastName |
JoinedDate |
DepartmentNo |
BirthDate |
101 |
Mike |
James |
3/27/2005 |
1 |
1/5/1980 |
102 |
Robert |
Williams |
4/25/2007 |
2 |
3/5/1983 |
103 |
Peter |
Paul |
3/21/2007 |
2 |
4/1/1983 |
104 |
Alex |
Stuart |
2/1/2008 |
2 |
11/6/1984 |
105 |
Robert |
James |
1/4/2008 |
3 |
12/1/1984 |
下面是 SELECT 语句的一个示例。
Following is an example of SELECT statement.
SELECT EmployeeNo,FirstName,LastName
FROM Employee;执行此查询时，它会从 employee 表中提取 EmployeeNo、FirstName 和 LastName 列。
When this query is executed, it fetches EmployeeNo, FirstName and LastName columns from the employee table.
EmployeeNo FirstName LastName
----------- ------------------------------ ---------------------------
101 Mike James
104 Alex Stuart
102 Robert Williams
105 Robert James
103 Peter Paul如果您想提取表中的所有列，您可以使用以下命令，而不必列出所有列。
If you want to fetch all the columns from a table, you can use the following command instead of listing down all columns.
SELECT * FROM Employee;上述查询将从 employee 表中提取所有记录。
The above query will fetch all records from the employee table.
WHERE Clause
WHERE 子句用于过滤 SELECT 语句返回的记录。一个条件与 WHERE 子句相关联。只有满足 WHERE 子句中条件的记录才会被返回。
WHERE clause is used to filter the records returned by the SELECT statement. A condition is associated with WHERE clause. Only, the records that satisfy the condition in the WHERE clause are returned.
Syntax
下面是带 WHERE 子句的 SELECT 语句的语法。
Following is the syntax of the SELECT statement with WHERE clause.
SELECT * FROM tablename
WHERE[condition];Example
以下查询提取 EmployeeNo 为 101 的记录。
The following query fetches records where EmployeeNo is 101.
SELECT * FROM Employee
WHERE EmployeeNo = 101;执行此查询时，它返回以下记录。
When this query is executed, it returns the following records.
EmployeeNo FirstName LastName
----------- ------------------------------ -----------------------------
101 Mike JamesORDER BY
执行 SELECT 语句时，返回的行没有任何特定顺序。ORDER BY 子句用于以升序/降序在任何列上排列记录。
When the SELECT statement is executed, the returned rows are not in any specific order. ORDER BY clause is used to arrange the records in ascending/descending order on any columns.
Syntax
以下是带 ORDER BY 子句的 SELECT 语句的语法。
Following is the syntax of the SELECT statement with ORDER BY clause.
SELECT * FROM tablename
ORDER BY column 1, column 2..;Example
以下查询从 employee 表中提取记录，并按 FirstName 对结果进行排序。
The following query fetches records from the employee table and orders the results by FirstName.
SELECT * FROM Employee
ORDER BY FirstName;执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
EmployeeNo FirstName LastName
----------- ------------------------------ -----------------------------
104 Alex Stuart
101 Mike James
103 Peter Paul
102 Robert Williams
105 Robert JamesGROUP BY
GROUP BY 子句与 SELECT 语句配合使用，并将类似的记录整理到组中。
GROUP BY clause is used with SELECT statement and arranges similar records into groups.
Syntax
以下是带有 GROUP BY 子句的 SELECT 语句的语法。
Following is the syntax of the SELECT statement with GROUP BY clause.
SELECT column 1, column2 …. FROM tablename
GROUP BY column 1, column 2..;Example
以下示例按 DepartmentNo 列对记录进行分组，并识别每个部门的总数。
The following example groups the records by DepartmentNo column and identifies the total count from each department.
SELECT DepartmentNo,Count(*) FROM
Employee
GROUP BY DepartmentNo;执行以上查询后，将产生以下输出。
When the above query is executed, it produces the following output.
DepartmentNo Count(*)
------------ -----------
3 1
1 1
2 3