SQL Select语句：Select Distinct，Select Into，Insert into等示例

最新推荐文章于 2021-04-29 05:29:50 发布

cumian9828

最新推荐文章于 2021-04-29 05:29:50 发布

阅读量1.2k

点赞数

文章标签：数据库 mysql java sql python

原文链接：https://www.freecodecamp.org/news/sql-select-statements/

版权

Select和From子句 (Select and From clauses)

The SELECT part of a query is normally to determine which columns of the data to show in the results. There are also options you can apply to show data that is not a table column.

查询的SELECT部分通常是确定要在结果中显示数据的哪几列。您还可以应用其他选项来显示不是表列的数据。

This example shows three columns selected from the “student” table and one calculated column. The database stores the studentID, FirstName, and LastName of the student. We can combine the First and the Last name columns to create the FullName calculated column.

本示例显示了从“学生”表中选择的三列和一个计算出的列。该数据库存储该学生的studentID，FirstName和LastName。我们可以组合名字和姓氏列来创建FullName计算列。

select studentID, FirstName, LastName, FirstName + ' ' + LastName as FullName
from student;

+-----------+-------------------+------------+------------------------+
| studentID | FirstName         | LastName   | FullName               |
+-----------+-------------------+------------+------------------------+
|         1 | Monique           | Davis      | Monique Davis          |
|         2 | Teri              | Gutierrez  | Teri Gutierrez         |
|         3 | Spencer           | Pautier    | Spencer Pautier        |
|         4 | Louis             | Ramsey     | Louis Ramsey           |
|         5 | Alvin             | Greene     | Alvin Greene           |
|         6 | Sophie            | Freeman    | Sophie Freeman         |
|         7 | Edgar Frank "Ted" | Codd       | Edgar Frank "Ted" Codd |
|         8 | Donald D.         | Chamberlin | Donald D. Chamberlin   |
|         9 | Raymond F.        | Boyce      | Raymond F. Boyce       |
+-----------+-------------------+------------+------------------------+
9 rows in set (0.00 sec)

*As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

*与所有这些SQL事物一样，它们比本入门指南中的内容要多得多。

I hope this at least gives you enough to get started.

我希望这至少能给您足够的入门。

Please see the manual for your database manager and have fun trying different options yourself.

请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。

SQL选择不同的语句 (SQL Select Distinct Statement)

介绍 (Introduction)

This keyword allows us to get lists of unique values in a column. This guide will demonstrate that.

此关键字使我们能够获取列中唯一值的列表。本指南将证明这一点。

完整显示学生表中的数据 (Full display of the data in the student table)

USE fcc_sql_guides_database;
SELECT studentID, FullName, sat_score, programOfStudy, rcd_Created, rcd_Updated FROM student;

+-----------+------------------------+-----------+------------------+---------------------+---------------------+
| studentID | FullName               | sat_score | programOfStudy   | rcd_Created         | rcd_Updated         |
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
|         1 | Monique Davis          |       400 | Literature       | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
9 rows in set (0.00 sec)

获取研究领域清单 (Get list of fields of study)

SELECT DISTINCT programOfStudy FROM student;

+------------------+
| programOfStudy   |
+------------------+
| Literature       |
| Programming      |
| Computer Science |
+------------------+
3 rows in set (0.00 sec)

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

与所有这些SQL事物一样，它们比本入门指南中的内容要多得多。

I hope this at least gives you enough to get started.

我希望这至少能给您足够的入门。

Please see the manual for your database manager and have fun trying different options yourself.

请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。

SQL选择入语句 (SQL Select into Statement)

The SELECT INTO statement is a query that allows you to create a new table and populate it with the result set of a SELECT statement. To add data to an existing table, see the INSERT INTO statement instead.

SELECT INTO语句是一个查询，使您可以创建一个新表，并使用SELECT statement的结果集填充该SELECT statement 。要将数据添加到现有表中，请参阅INSERT INTO语句。

SELECT INTO can be used when you are combining data from several tables or views into a new table.¹ The original table is not affected.

将多个表或视图中的数据合并到一个新表中时，可以使用SELECT INTO 。 ¹原始表不受影响。

The general syntax is:

通用语法为：

SELECT column-names
  INTO new-table-name
  FROM table-name
 WHERE EXISTS 
      (SELECT column-name
         FROM table-name
        WHERE condition)

This example shows a set of a table that was “copied” from the “Supplier” table to a new one called SupplierUSA which holds the set related to the column country of value ‘USA’.

此示例显示了一个表集，该表集是从“供应商”表中“复制”到一个名为SupplierUSA的新表中的，该表中包含与值“ USA”的列国家有关的集合。

SELECT * INTO SupplierUSA
  FROM Supplier
 WHERE Country = 'USA';

Results: 4 rows affected ²

结果：4行受影响²

IDCompanyNameContactNameCityCountryPhone2New Orleans Cajun DelightsShelley BurkeNew OrleansUSA(100) 555-48223Grandma Kelly’s HomesteadRegina MurphyAnn ArborUSA(313) 555-573516Bigfoot BreweriesCheryl SaylorBendUSANULL19New England Seafood CanneryRobb MerchantBostonUSA(617) 555-3267

IDCompanyNameContactNameCityCountryPhone2New Orleans Cajun DelightsShelley BurkeNew OrleansUSA(100)555-48223Grandma Kelly's HomesteadRegina MurphyAnn ArborUSA(313)555-573516Bigfoot BrewerysCheryl SaylorBendUSANULL19New England Seafood CanneryRobb MerchantBostonUSA(617)

Please see the manual for your database manager and have fun trying different options yourself.

请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。

SQL插入语句 (SQL Insert into Statement)

To insert a record in a table you use the INSERT INTO statement.

要在表中插入记录，请使用INSERT INTO语句。

You can do it in two ways, if you want to insert values only in some columns, you have to list their names including all mandatory columns. The syntax is:

您可以通过两种方式进行操作，如果只想在某些列中插入值，则必须列出其名称，包括所有必填列。语法为：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The other way is inserting values to all columns in the table, it is not necessary to specify the columns names. The syntax is:

另一种方法是将值插入表中的所有列，而不必指定列名称。语法为：

INSERT INTO table_name 
VALUES (value1, value2, value3, ...);

Here’s an example inserting a record in the table Person in both ways:

这是一个以两种方式在表Person中插入记录的示例：

INSERT INTO Person
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);

And

和

INSERT INTO Person(Id, Name, DateOfBirth, Gender)
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);

Some SQL versions (for example, MySQL) support inserting multiple rows at once. For example:

某些SQL版本(例如MySQL)支持一次插入多行。例如：

INSERT INTO Person(Id, Name, DateOfBirth, Gender)
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’), (2, ‘Paul McCartney’, ‘1942-06-18’, ‘M’),
(3, ‘George Harrison’, ‘1943-02-25’, ‘M’), (4, ‘Ringo Starr’, ‘1940-07-07’, ‘M’)

Note that the entire original query remains intact - we simple add on data rows encloded by paranthesis and separated by commas.

请注意，整个原始查询保持不变-我们简单地添加了用括号括起来并用逗号分隔的数据行。

SQL插入Select语句 (SQL Insert into Select Statement)

You can insert records in a table using data that are already stored in the database. This is only a copy of data and it doesn’t affect the origin table.

您可以使用数据库中已经存储的数据在表中插入记录。这只是数据的副本，不会影响原始表。

The INSERT INTO SELECT statement combines INSERT INTO and SELECT statements and you can use any conditions you want. The syntax is:

INSERT INTO SELECT语句结合了INSERT INTO和SELECT语句，您可以使用所需的任何条件。语法为：

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Here is an example that inserts in the table Person all the male students from the table Students.

这是一个示例，在表Person中插入了Students表中的所有男学生。

INSERT INTO Person(Id, Name, DateOfBirth, Gender)
SELECT Id, Name, DateOfBirth, Gender
FROM Students
WHERE Gender = ‘M’

其他SQL资源： (Other SQL resources:)

翻译自: https://www.freecodecamp.org/news/sql-select-statements/

cumian9828

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL Select语句：Select Distinct，Select Into，Insert into等示例

Select和From子句 (Select and From clauses)The SELECT part of a query is normally to determine which columns of the data to show in the results. There are also options you can apply to show data that is...
复制链接

扫一扫