sql子查询示例_SQL更新查询示例说明

sql子查询示例

In this article, we're going to learn how to use the SQL update statement - what it is, what it can do, and what you need to be aware of before using it.

在本文中，我们将学习如何使用SQL更新语句-它是什么，它可以做什么以及在使用它之前需要注意的事项。

SQL更新查询或语句 (SQL Update Query or Statement)

更新查询可以做什么 (What an Update query can do)

An update query gives the DBA or SQL-using programmer the ability to update many records with one command.

更新查询使DBA或使用SQL的程序员能够使用一个命令更新许多记录。

Important Safety Tip! Always have a backup copy of what you are about to change BEFORE you change it!

重要安全提示！ 更改之前，请始终保留要更改内容的备份副本！

This part of the article will:

本文的这一部分将：

• add a new field to the student table
  向学生表添加新字段
• test the logic to update that field with a school assigned email address
  测试用学校分配的电子邮件地址更新该字段的逻辑
• update the new field.
  更新新字段。

Here is the student table as we start this process

这是我们开始此过程时的学生表

SELECT * 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)

更改表并添加新字段 (Alter the table and add a new field)

ALTER TABLE `fcc_sql_guides_database`.`student` ADD COLUMN `schoolEmailAdr` VARCHAR(125) NULL AFTER `programOfStudy`;

The student table after the alter is executed.

执行更改后的学生表。

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student;
+------------------------+-----------+------------------+----------------+
| FullName               | sat_score | programOfStudy   | schoolEmailAdr |
+------------------------+-----------+------------------+----------------+
| Monique Davis          |       400 | Literature       | NULL           |
| Teri Gutierrez         |       800 | Programming      | NULL           |
| Spencer Pautier        |      1000 | Programming      | NULL           |
| Louis Ramsey           |      1200 | Programming      | NULL           |
| Alvin Greene           |      1200 | Programming      | NULL           |
| Sophie Freeman         |      1200 | Programming      | NULL           |
| Edgar Frank "Ted" Codd |      2400 | Computer Science | NULL           |
| Donald D. Chamberlin   |      2400 | Computer Science | NULL           |
| Raymond F. Boyce       |      2400 | Computer Science | NULL           |
+------------------------+-----------+------------------+----------------+
9 rows in set (0.00 sec)

测试逻辑(非常重要的一步！) (TESTING the logic (VERY important step!))

SELECT FullName, instr(FullName," ") AS firstSpacePosition, 
concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu") AS schoolEmail
FROM student;

+------------------------+--------------------+------------------------+
| FullName               | firstSpacePosition | schoolEmail            |
+------------------------+--------------------+------------------------+
| Monique Davis          |                  8 | Monique@someSchool.edu |
| Teri Gutierrez         |                  5 | Teri@someSchool.edu    |
| Spencer Pautier        |                  8 | Spencer@someSchool.edu |
| Louis Ramsey           |                  6 | Louis@someSchool.edu   |
| Alvin Greene           |                  6 | Alvin@someSchool.edu   |
| Sophie Freeman         |                  7 | Sophie@someSchool.edu  |
| Edgar Frank "Ted" Codd |                  6 | Edgar@someSchool.edu   |
| Donald D. Chamberlin   |                  7 | Donald@someSchool.edu  |
| Raymond F. Boyce       |                  8 | Raymond@someSchool.edu |
+------------------------+--------------------+------------------------+
9 rows in set (0.00 sec)

A note about concat(): in MySQL this command is used to combined strings, not so in other SQL versions (check your manual).

关于concat()的注释：在MySQL中，此命令用于组合字符串，而在其他SQL版本中则不是这样(请查看手册)。

In this usage it works like this: The substring of the FullName field up to but not including the first space is combined with “@someSchool.edu”.

在这种用法中，它的工作方式如下：FullName字段的子字符串，直到但不包括第一个空格，都与“ @ someSchool.edu”组合在一起。

In the real world this would HAVE TO be much more complex and you would need to ensure that the email address is unique.

在现实世界中，这将变得更加复杂，并且您需要确保电子邮件地址是唯一的。

进行更新 (Doing the update)

We’ll pretend that this is what we want and update the table with this information:

我们将假装这就是我们想要的，并使用以下信息更新表：

UPDATE student SET schoolEmailAdr = concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu")
WHERE schoolEmailAdr is NULL;

Success!

成功！

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student;
+------------------------+-----------+------------------+------------------------+
| FullName               | sat_score | programOfStudy   | schoolEmailAdr         |
+------------------------+-----------+------------------+------------------------+
| Monique Davis          |       400 | Literature       | Monique@someSchool.edu |
| Teri Gutierrez         |       800 | Programming      | Teri@someSchool.edu    |
| Spencer Pautier        |      1000 | Programming      | Spencer@someSchool.edu |
| Louis Ramsey           |      1200 | Programming      | Louis@someSchool.edu   |
| Alvin Greene           |      1200 | Programming      | Alvin@someSchool.edu   |
| Sophie Freeman         |      1200 | Programming      | Sophie@someSchool.edu  |
| Edgar Frank "Ted" Codd |      2400 | Computer Science | Edgar@someSchool.edu   |
| Donald D. Chamberlin   |      2400 | Computer Science | Donald@someSchool.edu  |
| Raymond F. Boyce       |      2400 | Computer Science | Raymond@someSchool.edu |
+------------------------+-----------+------------------+------------------------+
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.

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

翻译自: https://www.freecodecamp.org/news/sql-update-query-and-update-statement-explained/

sql子查询示例