MySQL简单SELECT查询语法

Recently I showed you how to create a basic connection to a MySQL database with PHP and extract information via a query. At the time, I mentioned that once you have the ability to get data from a database, you will likely spend most of your time working on the query, trying to determine what information to retrieve. The query that we started with does the opposite to that:

最近，我向您展示了如何使用PHP 创建与MySQL数据库的基本连接以及如何通过查询提取信息。 当时，我提到，一旦你必须从数据库中获取数据的能力，你可能会花大部分时间在查询工作，试图确定要检索的信息。 我们开始的查询与此相反：

SELECT * FROM products

Phrased that way, this query retrieves absolutely everything from the table named products: every piece of data, in every column. While your database table is small, containing only a few records, this isn’t a problem… but when it grows to hundreds, thousands, or even millions of rows, the work required to fetch and display all of that information can be significant.

这样，此查询绝对会从名为products的表中检索所有内容：每列中的每个数据。 尽管您的数据库表很小，只包含几条记录，但这不是问题……但是，当它增长到成百上千，甚至几百万行时，获取和显示所有这些信息所需的工作就很重要。

We can immediately start to filter the data we retrieve by adding a WHERE clause:

我们可以通过添加WHERE子句立即开始过滤检索到的数据：

SELECT * FROM products WHERE price < 50

This retrieves complete data for each record in the products table, but only for records in which the price column has a value of less than 50. (For the sake of this example I am assuming that price is a numerical field).

这将检索products表中每个记录的完整数据，但仅检索price列的值小于50 。 (为了这个例子，我假设price是一个数字字段)。

We can further limit the amount of data we receive by using that very word and a value:

通过使用该单词和一个值，我们可以进一步限制接收到的数据量：

SELECT * FROM products WHERE price < 50 LIMIT 10

This query retrieves the first 10 rows that match our criteria, and only 10; very useful when creating pagination (showing the user a limited number of results, so as not to overwhelm them (or our server)),

该查询检索符合我们条件的前10行，只有10行； 在创建分页时非常有用(向用户显示有限数量的结果，以免使结果(或我们的服务器)不知所措)，

We can limit our results to just one record by using the value of the record’s primary key:

我们可以使用记录的主键的值将结果限制为仅一条记录：

SELECT * FROM table WHERE id = 5 LIMIT 1;

(Including the LIMIT 1 is somewhat redundant here; we should only have one record with a value of 5 in its id field, but leaving it in doesn’t do any harm).

(在这里包括LIMIT 1有点多余；我们应该只在其id字段中保留一条值为5记录，但是将其保留不会有任何危害)。

You can also cut down on the information you receive by specifying that you only want the data from certain columns, and not others. For example, this statement might be useful to retrieve data for the purposes of creating some navigation:

您还可以通过指定只希望某些列而不是其他列中的数据来减少收到的信息。 例如，以下语句对于创建某些导航的目的可能是有用的：

SELECT id, name FROM entries

In this example, while we are back to gaining information from every row, the only data we will receive is the is id and name value in each row; other information that might be in the table, such as description, will not be retrieved.

在此示例中，当我们返回每行的信息时，我们将收到的唯一数据是每行的id和name值； 表中可能存在的其他信息(例如description )将不会被检索。

By default, data gained from a MySQL database is listed in the order it was entered, typically meaning by the ascending value of the primary key field for each record. We can order by any other field by specifying it:

默认情况下，从MySQL数据库获取的数据按输入顺序列出，通常意味着每条记录的主键字段的值递增。 我们可以通过指定其他任意字段来进行排序：

SELECT * FROM products WHERE price < 50 ORDER BY price

The order of your retrieved data will automatically be sorted by ascending (ASC) value: that is, the rows will be ordered by whatever first appears in the field: alphabetically (a-z) or numerically, (0,  1, 2…). Often, you will want to order products by highest to lowest price instead, in which case your query becomes:

检索到的数据的顺序将自动按升序(ASC)值进行排序：也就是说，行将按照字段中第一个出现的顺序进行排序：字母(az)或数字(0、1、2…)。 通常，您会想按最高到最低的价格订购产品，在这种情况下，查询变为：

SELECT * FROM products WHERE price < 50 ORDER BY price DESC

You can also chain conditions together by using AND: note that references to text values should be quoted:

您还可以使用AND条件链接在一起：请注意，对文本值的引用应加引号：

SELECT name,description FROM products WHERE price > 50 AND color=’black’ LIMIT 10 ORDER BY price DESC

As you can see, MySQL is a deceptively simple language: you can create very complex queries by chaining easy syntactical rules together. What you can’t do at the moment is to make the query itself dynamic; i.e. changing the data that is returned based on user input. Right now, all of the determinants in the query are hard-coded: in the statement above, you will get information back on all records in the products table that are listed as being black and having a price greater than 50, but the user has no ability to change the ‘50’ value to anything else. We’ll tackle that feature next in this series.

如您所见，MySQL是一种看似简单的语言：您可以通过将简单的语法规则链接在一起来创建非常复杂的查询。 您目前不能做的就是使查询本身动态化。 即更改基于用户输入返回的数据。 现在，查询中的所有行列式都是硬编码的：在上面的语句中，您将获得有关products表中所有列为black且价格大于50 ，但用户拥有无法将“ 50 ”值更改为其他任何值。 我们将在本系列的下一部分中解决该功能。

翻译自: https://thenewcode.com/434/MySQL-Simple-SELECT-Query-Syntax