Sql 简明教程

SQL - SELECT Query

The SQL SELECT Statement

SQL SELECT 语句用于从数据库表中获取以表格形式返回此数据的数据。这些表格称为结果集。

The SQL SELECT Statement is used to fetch the data from a database table which returns this data in the form of a table. These tables are called result-sets.

Syntax

SELECT 查询的基本语法如下 -

The basic syntax of the SELECT Query is as follows −

SELECT column1, column2, columnN FROM table_name;

这里，column1, column2… 是你要获取其值的表的字段。如果您想要获取表中可用所有列，则可以使用以下语法 -

Here, column1, column2… are the fields of a table whose values you want to fetch. If you want to fetch all the columns available in a table, then you can use the following syntax −

SELECT * FROM table_name;

Example

假设已使用如下所示的 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表：

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，使用如下所示的 INSERT 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将被创建为：

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Retrieving Selected Fields/Columns

以下语句获取 CUSTOMERS 表中的可用记录的 ID、Name 和 Salary 字段。

The following statement fetches the ID, Name and Salary fields of the records available in CUSTOMERS table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

Output

以上查询将生成以下表格 -

The above query would produce the following table −

ID	NAME	Salary
1	Ramesh	2000.00
2	Khilan	1500.00
3	Kaushik	2000.00
4	Chaitali	6500.00
5	Hardik	8500.00
6	Komal	4500.00
7	Muffy	10000.00

Retrieving All Fields/Columns

如果您想要获取 CUSTOMERS 表的所有字段，则应在 SELECT 语句的查询中使用 Asterisk ( )* 而非列名称，如下所示 -

If you want to fetch all the fields of the CUSTOMERS table, then you should use the query of SELECT statement with an Asterisk ()* instead of the column names, as shown below −

SELECT * FROM CUSTOMERS;

Output

结果表格将为 -

The resultant table will be −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Computing Using SELECT

SQL SELECT 语句还可以用于以表格形式检索各种数学计算结果。在这些情况下，您无需在语句中指定任何数据库表。

The SQL SELECT statement can also be used to retrieve the results of various mathematical computations in the form of a table. In such cases, you do not need to specify any database table in the statement.

以下是执行此操作的语法 −

Following is the syntax to do so −

SELECT mathematical_expression;

Example

以下是一个使用 SQL 语句将给定的两个数字相乘的示例。

Following is an example which multiply two given numbers using SQL statement.

SELECT 56*65;

以上查询生成以下输出 -

The query above produces the following output −

56*65

3640

Aliasing a Column in SELECT Statement

每当表中的列名称太难阅读和理解时，SQL 提供了一种方法来别名此列名称为另一个可理解且相关的名称。这是使用 AS keyword 完成的。您可以在 SELECT 语句中使用 AS 关键字将表的列名称显示为别名名称。

Whenever a column name in a table is too difficult to read and understand, SQL provides a method to alias this column name into another understandable and relative name. This is done using the AS keyword. You can use the AS keyword in a SELECT statement to display the column names of a table as an alias name.

以下是执行此操作的语法 −

Following is the syntax to do so −

SELECT column_name
AS alias_name
FROM table_name;

您还可以使用别名以相同的语法显示 SELECT 表达式；您应使用数学语句而非 column_name。

You can also use an alias to display SELECT expressions with the same syntax; you should use a mathematical statement instead of column_name.

Example

在以下示例中，我们尝试使用 concat() 表达式和将列别名为 DETAILS 以及 CUSTOMERS 表中的客户地址，在一个结果表的一列中检索客户详细信息 NAME 和 AGE。这将在以下查询中使用 SELECT 语句完成 -

In the example below, we are trying to retrieve customer details NAME and AGE in a single column of the resultant table using the concat() expression and aliasing the column as DETAILS along with the customer addresses from the CUSTOMERS table. This will be done using SELECT statement in the following query −

SELECT CONCAT(NAME,' ',AGE)
AS DETAILS, ADDRESS
FROM CUSTOMERS ORDER BY NAME;

以上查询生成以下输出 -

The query above produces the following output −

DETAILS

ADDRESS

Chaitali 25

Mumbai

Hardik 27

Bhopal

Kaushik 23

Kota

Khilan 25

Delhi

Komal 22

Hyderabad

Muffy 24

Indore

Ramesh 32

Ahmedabad