Mysql 简明教程
MySQL - Select Query
在上一个教程中学到了如何创建 MySQL 表并向其中插入值，然后下一步就是检查表中是否记录了值。为此，必须使用 SELECT 语句来检索并查看具体表中的记录。
Now that we have learned how to create tables in MySQL and insert values into it in the previous tutorials, the next step is to check whether the values are recorded in this table or not. To do this, one must use the SELECT statement to retrieve and view the records in that specific table."
MySQL Select Statement
MySQL SELECT 命令用于从 MySQL 数据库中以结果表的形式获取数据。这些结果表称为结果集。
The MySQL SELECT command is used to fetch data from the MySQL database in the form of a result table. These result tables are called result-sets.
Note − 可以在 'mysql>' 提示符下以及在任何脚本（例如 PHP、Node.js、Java、python 等）中使用此命令。
Note − We can use this command at 'mysql>' prompt as well as in any script like PHP, Node.js, Java, python, etc.
Syntax
以下是用于从 MySQL 表格中获取数据的 SELECT 命令的通用 SQL 语法 -
Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −
SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]-
You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
-
We can fetch one or more fields in a single SELECT command.
-
We can specify star (*) in place of fields. In this case, SELECT will return all the fields.
-
We can specify any condition using the WHERE clause.
-
We can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
-
We can limit the number of returns using the LIMIT attribute.
Fetching Data Using SELECT from Command Prompt
这将使用 SQL SELECT 命令从 MySQL 表中获取数据。
This will use SQL SELECT command to fetch data from an MySQL table.
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：
First of all, let us create a table named CUSTOMERS using the following query −
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)
);以下查询在上述创建的表中插入 7 条记录 −
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );To Retrieve All Fields
To Retrieve All Fields
现在，要检索 CUSTOMERS 表中存在的所有数据，使用以下 SELECT 语句：
Now, to retrieve all the data present in a CUSTOMERS table, we use the following SELECT statement −
SELECT * from CUSTOMERS;CUSTOMERS 表中存在以下记录：
Following are the records present in the CUSTOMERS table −
To Retrieve Selective Fields
To Retrieve Selective Fields
在这里，仅检索 CUSTOMERS 表中存在的三个列/字段 ID 、 NAME 和 ADDRESS ，使用以下查询：
Here, we are retrieving only three columns/fields, ID, NAME, and ADDRESS present in the CUSTOMERS table, using the following query −
SELECT ID, NAME, ADDRESS FROM CUSTOMERS;执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
Computing using SELECT in Command Prompt
SELECT 语句不仅仅用于从表中获取数据，还可用于以表格形式获取数学计算的结果。在这些情况下，不必在 SELECT 语句中提到特定的数据库表。
The SELECT statement is not only used to fetch data from tables but can also be used to get the results of mathematical computations in a tabular format. In these cases, you don’t have to mention a specific database table in the SELECT statement.
以下是执行此操作的语法 −
Following is the syntax to do so −
SELECT [math_computation];Aliasing a Column in SELECT Statement
MySQL 数据库提供了一种方法，即在显示时将列名别名为更易于理解且相对的名称。这是使用 'AS' 关键字完成的。此关键字也在 SELECT 语句中使用。
MySQL database provides a method to alias column names into a more understandable and relative name when being displayed. This is done using the 'AS' keyword. This keyword is used in the SELECT statement as well.
以下是执行此操作的语法 −
Following is the syntax to do so −
SELECT column_name AS alias_name FROM table_name;您还可以使用别名，以相同的语法显示所选表达式；您应该在语法中使用选择表达式代替 column_name。
You can also use an alias to display select expressions with the same syntax; you should use a select expression instead of column_name in the syntax.
Example
在下面的示例中，我们正在从之前创建的 CUSTOMERS 表中检索 ID 列。我们将 ID 列别名为 "Identity_Document" ——
In the example below, we are retrieving the ID column from the previously created CUSTOMERS table. We aliased the ID column as "Identity_Document" −
SELECT ID AS Identity_Document FROM CUSTOMERS;正如我们看到的那样，已经使用别名“Identity_Document”而不是“ID”。
As we can see the output, the alias name 'Identity_Document' has been used instead of 'ID'.
Select Query into MySQL Database Using a Client Program
除了使用 SELECT 查询从表中获取数据之外，您还可以使用客户端程序对表执行 SELECT 操作。
Besides getting data from a table using the SELECT query, you can also use a client program to perform the SELECT operation on a table.