sql select 语句
The SELECT statement is probably the most important SQL command. It’s used to return results from our database(s) and no matter how easy that could sound, it could be really very complex. In this article, we’ll give an intro to the SELECT statement and we’ll cover more complex stuff in the upcoming articles.
SELECT语句可能是最重要SQL命令。 它用来从我们的数据库中返回结果，无论听起来多么容易，它实际上都非常复杂。 在本文中，我们将对SELECT语句进行介绍，并在后续文章中介绍更复杂的内容。
动机 (Motivation)
In this series, we had 4 articles so far and we’ve created a simple database, populated it with some data and explained what are primary and foreign keys. These are prerequisites needed to start “playing” with our data:
在本系列中，到目前为止，我们有4篇文章，并且我们已经创建了一个简单的数据库，并向其中填充了一些数据并解释了什么是主键和外键。 这些是开始“播放”我们的数据所需的先决条件：
In real-life situations, you’ll probably won’t insert all the data in the database. Data shall be either inserted manually by multiple users of your application/system or by some automated process(es). In these cases, you’ll either:
在现实生活中，您可能不会在数据库中插入所有数据。 数据应由您的应用程序/系统的多个用户手动插入，或由某些自动化过程插入。 在这些情况下，您将：
- Analyze data, most probably by using SELECT statements 分析数据，最有可能通过使用SELECT语句
- Track system performance 跟踪系统性能
- Make changes in the data model to support new features 更改数据模型以支持新功能
Well, I guess you get it, you’ll keep the system running and analyze what’s stored in there. The analytics part is probably cooler because you’ll be able to create reports and see what happened before, and if you have enough data and knowledge of statistics, you’ll be able to predict the future (as much as this is possible). I can hardly imagine doing any analytics when working with databases without writing a SELECT statement.
好吧，我想您明白了，您将保持系统运行并分析其中存储的内容。 分析部分可能更酷，因为您将能够创建报告并查看以前发生的事情，并且如果您有足够的数据和统计知识，则可以预测未来（尽可能多地预测）。 我很难想象在不编写SELECT语句的情况下使用数据库进行任何分析。
SELECT语句–语法 (SELECT statement – Syntax)
All SQL commands are important and needed, especially these 4 most commonly used – SELECT, INSERT, UPDATE, DELETE. Therefore, saying that the SELECT statement is the most important one is not true. It’s as important as others are but it’s definitely most commonly used. Being able to write a SELECT to get exactly what you wanted is a very desirable knowledge these days. Besides writing a statement that returns the correct result, you should almost (if you write a one-time query and it takes 2 seconds instead of 0.1 seconds, you can live with that) always make sure that the query is written in an optimal way.
所有SQL命令都是重要和需要的，尤其是这4个最常用的命令-SELECT，INSERT，UPDATE，DELETE。 因此，说SELECT语句最重要是不正确的。 它和其他人一样重要，但是绝对是最常用的。 如今，能够编写SELECT以获得所需的确切信息是非常可取的知识。 除了编写返回正确结果的语句外，您几乎还应该确保以最佳方式编写查询（如果您编写一次查询，它需要2秒而不是0.1秒，您可以接受）。 。
Let’s take a look at the Transact-SQL SELECT statement syntax:
让我们看一下Transact-SQL SELECT语句的语法 ：
-- Syntax for SQL Server and Azure SQL Database
<SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]
I’ll simplify this syntax to focus on the things I want to explain in this article:
我将简化此语法，以专注于我想在本文中解释的内容：
SELECT [TOP X] attributes & values
FROM first_table
INNER / LEFT / RIGHT JOIN second_table ON condition(s)
... other joins if needed
WHERE condition(s)
GROUP BY set of attributes
HAVING condition(s) for group by
ORDER BY list attributes and order;
SELECT语句–非常简单的示例 (SELECT statement – Very simple examples)
Only the line with the SELECT keyword is required in the select statement. After this reserved keyword, we’ll list everything we want to see in our result. These could be values, attributes from tables, results of (mathematical or logical) operations, etc. Lets’ take a look at a few simple examples:
select语句中仅需要带有SELECT关键字的行。 在此保留关键字之后，我们将列出我们想要在结果中看到的所有内容。 这些可能是值，表中的属性，（数学或逻辑）运算的结果等。让我们看几个简单的示例：
SELECT 1;
SELECT 1+2;
SELECT 1+2 AS result;
SELECT 1+2 AS first_result, 2*3 AS second_result;
SELECT (CASE WHEN 1+2 > 2*3 THEN 'greater' ELSE 'smaller' END) AS comparison;
The result displayed in the SQL Server Management Studio after executing them is the following:
执行它们后，在SQL Server Management Studio中显示的结果如下：
Let’s quickly explain what happened here.
让我们快速解释一下这里发生了什么。
First of all, in all these 5 queries, we haven’t used a single table from our database. In real-life situations, this could hardly prove to be usable in any case, but it will serve its’ purpose here.
首先，在所有这5个查询中，我们都没有使用数据库中的单个表。 在现实生活中，在任何情况下都很难证明这是可行的，但这将在这里达到目的。
The things we should remember from here are:
我们应该从这里记住的事情是：
- The SELECT keyword is the only one required in the SELECT statement SELECT关键字是SELECT语句中唯一需要的关键字
- “(No column name)” “（（无列名称）”）
- If you want to give a (different) name to the column in the result you can use AS and alias name (SELECT 1+2 AS result;) 如果要为结果中的列指定一个（不同的）名称，则可以使用AS和别名（SELECT 1 + 2 AS result;）。
- You can have more than 1 column in your result (SELECT 1+2 AS first_result, 2*3 AS second_result;), and usually that shall be the case 您的结果中可以包含多于1列（SELECT 1 + 2 AS first_result，2 * 3 AS second_result;），通常是这种情况
- You can even compare values or results of a calculation in the SELECT part of the statement (SELECT (CASE WHEN 1+2 > 2*3 THEN ‘greater’ ELSE ‘smaller’ END) AS comparison;) 您甚至可以在语句的SELECT部分中比较值或计算结果（SELECT（在1 + 2> 2 * 3 THEN'更大'ELSE'更小END'的情况下）AS比较；）
The chance that you’ll really need to write a query like any of these 5 examples while performing any serious tasks is next to nothing. Still, all of the previous bullets are correct for complex queries too, and I guess it was easier to explain them on really simple examples.
在执行任何重要任务时，您真正需要像这5个示例中的任何一个一样编写查询的机会几乎是零。 尽管如此，所有先前的项目符号也适用于复杂的查询，我想在非常简单的示例中解释它们会更容易。
SELECT语句–使用单个表 (SELECT statement – Using the single table)
Now we’re ready for the next step and that is to use data from our database. The data model is the same as the one used in the previous article. We’ll start with two simple SELECTs:
现在我们准备下一步，那就是使用数据库中的数据。 数据模型与上一篇文章中使用的模型相同。 我们将从两个简单的SELECT开始：
SELECT *
FROM country;
SELECT *
FROM city;
The result is given in the picture below:
结果如下图所示：
Both statements do the same thing, but for different tables. The * after SELECT means that we’ll select all columns from that table.
这两个语句执行相同的操作，但是用于不同的表。 SELECT之后的*表示我们将从该表中选择所有列。
Note: It’s nice (almost the rule) to put each keyword (SELECT, FROM, JOIN, WHERE…) in the new line.
注意：将每个关键字（SELECT，FROM，JOIN，WHERE…）放在新行中（很好）（几乎是规则）。
In case we need only some columns from the table, we should list all the columns we need after the SELECT keyword:
如果我们只需要表中的一些列，则应该在SELECT关键字之后列出所有需要的列：
SELECT id, country_name
FROM country;
As expected, we have only 2 columns in the result. While it’s common sense to return only the columns you’re interested in, later it’s even more than that. If your table has a large number of columns or you join a few tables, listing only the columns you need is much more than desirable.
不出所料，结果中只有2列。 虽然只返回您感兴趣的列是常识，但后来返回的甚至更多。 如果您的表中有大量的列或您联接了几个表，仅列出所需的列就远远超出了您的期望。
Note: You should always list only the columns you need in the result. A chance that you’ll use * and return all columns is very low. You’ll probably do that only when you want to take a look at the content of that table for the first time.
注意：您应该始终只列出结果中需要的列。 您将使用*并返回所有列的机会非常低。 仅当您想第一次查看该表的内容时，才可能这样做。
The next thing we want to do is to not only select columns, but also select rows we want in the result. We’ll do that by using the WHERE keyword. Let’s take a look at 3 examples:
我们要做的下一件事不仅是选择列，还要选择结果中想要的行。 我们将使用WHERE关键字来实现。 让我们看一下3个示例：
SELECT id, country_name_eng
FROM country
WHERE id = 2;
SELECT id, country_name_eng
FROM country
WHERE id > 2;
SELECT id, country_name_eng
FROM country
WHERE id = 6;
The result is given in the picture below:
结果如下图所示：
In all three SELECT statements, we’ve used the id column in the WHERE part of the statement.
在所有三个SELECT语句中，我们都在语句的WHERE部分中使用了id列。
This column is also the primary key of the table. Therefore, the first select can find only 1 record with id = 2. For the same reason, the third statement couldn’t find any record – there is no record with id = 6.
此列也是表的主键。 因此，第一个选择只能找到1条ID = 2的记录。出于同样的原因，第三条语句找不到任何记录-没有ID = 6的记录。
The second statement returned all records with id > 2, so our result had multiple rows.
第二条语句返回id> 2的所有记录，因此我们的结果有多行。
SELECT语句–使用多个表 (SELECT Statement – Using multiple tables)
The last thing we’ll do in this article is to select data from both tables in our model. Before we do that, let’s remind ourselves that these tables are related via foreign key (city.county_id = country.id). We’ll need to use this condition every time we use both these tables:
我们在本文中要做的最后一件事是从模型中的两个表中选择数据。 在此之前，让我们提醒自己，这些表是通过外键关联的（ city.county_id = country.id ）。 每次我们同时使用这两个表时，将需要使用此条件：
If we don’t use condition, the query will join all records from the first table with all records from the second table. This generates not only the large output but also relates values that actually are not related (only some of them truly match). For example, Berlin is obviously not in the USA, but a query like this would say it so. Therefore, we need to add join conditions to get the correct result.
如果不使用条件，查询将把第一个表中的所有记录与第二个表中的所有记录连接起来。 这不仅会产生较大的输出，还会产生实际上不相关的值（只有其中一些真正匹配）。 例如，柏林显然不在美国，但是像这样的查询会这样说。 因此，我们需要添加联接条件以获得正确的结果。
Note: If you join two (or more) tables without a join condition, you’ll get a combination of all rows with each other (Cartesian product).
注意：如果在没有连接条件的情况下连接两个（或多个）表，则将得到所有行的组合（笛卡尔积）。
Now, we’ll join tables using the join condition and also add one more condition in the WHERE part of the query:
现在，我们将使用联接条件联接表，并在查询的WHERE部分中添加另一个条件：
SELECT city.id AS city_id, city.city_name, country.id AS country_id, country.country_name, country.country_name_eng, country.country_code
FROM city
INNER JOIN country ON city.country_id = country.id
WHERE country.id IN (1,4,5);
The result is given in the picture below:
结果如下图所示：
I would like to point out a few things here:
我想在这里指出几点：
- We’ve joined our two tables using the INNER JOIN and foreign key as condition (INNER JOIN country ON city.country_id = country.id) 我们使用INNER JOIN和外键作为条件联接了两个表（INNER JOIN country ON city.country_id = country.id）
- id of the country is 1, 4 or 5. Please notice how the IN keyword is being used – similar to set theory ID为1、4或5的行。请注意如何使用IN关键字–与集合论类似
- We’ve listed only attributes we want to display in the result 我们仅列出了要在结果中显示的属性
- id, we should name them differently in the result returned. While we know which columns the query used, for someone who hasn’t written this query or can’t see its’ structure (for any reason), this is very important id ，因此我们在返回的结果中应使用不同的名称。 虽然我们知道查询使用了哪些列，但是对于尚未编写此查询或看不到其结构（出于任何原因）的用户，这非常重要
结论 (Conclusion)
In today’s article, we’ve explained basics related to the SELECT statement. In the next article, we’ll write some more complex statements and use other keywords like GROUP BY, HAVING and ORDER BY. So, stay tuned!
在今天的文章中，我们已经解释了与SELECT语句相关的基础知识。 在下一篇文章中，我们将编写一些更复杂的语句，并使用其他关键字，例如GROUP BY，HAVING和ORDER BY。 所以，请继续关注！
目录 (Table of contents)
sql select 语句
833
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
