mysql更新语句sql_SQL更新语句说明：更新表的查询（包括MySQL示例）

mysql更新语句sql

更新查询可以做什么 (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 guide 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 | [email protected] |
| Teri Gutierrez         |                  5 | [email protected]    |
| Spencer Pautier        |                  8 | [email protected] |
| Louis Ramsey           |                  6 | [email protected]   |
| Alvin Greene           |                  6 | [email protected]   |
| Sophie Freeman         |                  7 | [email protected]  |
| Edgar Frank "Ted" Codd |                  6 | [email protected]   |
| Donald D. Chamberlin   |                  7 | [email protected]  |
| Raymond F. Boyce       |                  8 | [email protected] |
+------------------------+--------------------+------------------------+
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). 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”. In the real world this would HAVE TO be much more complex and you would need to ensure that the email address is unique.

关于concat()的注释：在MySQL中，此命令用于组合字符串，而在其他SQL版本中则不是这样(请查看手册)。 在这种用法中，它的工作方式如下：FullName字段的子字符串，直到但不包括第一个空格，都与“ @ someSchool.edu”组合在一起。 在现实世界中，这将变得更加复杂，并且您需要确保电子邮件地址是唯一的。

进行更新 (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       | [email protected] |
| Teri Gutierrez         |       800 | Programming      | [email protected]    |
| Spencer Pautier        |      1000 | Programming      | [email protected] |
| Louis Ramsey           |      1200 | Programming      | [email protected]   |
| Alvin Greene           |      1200 | Programming      | [email protected]   |
| Sophie Freeman         |      1200 | Programming      | [email protected]  |
| Edgar Frank "Ted" Codd |      2400 | Computer Science | [email protected]   |
| Donald D. Chamberlin   |      2400 | Computer Science | [email protected]  |
| Raymond F. Boyce       |      2400 | Computer Science | [email protected] |
+------------------------+-----------+------------------+------------------------+
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.

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

您如何使用Update语句？ (How do you use an Update statement?)

To update a record in a table you use the  UPDATE  statement.

要更新表中的记录，请使用UPDATE语句。

Be careful. You can update all records of the table or just a few. Use the  WHERE  condition to specify which records do you want to update. It is possible to update one or more columns at a time. The syntax is:

小心。 您可以更新表中的所有记录或仅更新其中的一些记录。 使用WHERE条件指定要更新的记录。 可以一次更新一个或多个列。 语法为：

UPDATE table_name
SET column1 = value1, 
    column2 = value2, ...
WHERE condition;

Here is an example updating the Name of the record with Id 4:

这是一个使用ID 4更新记录名称的示例：

UPDATE Person
SET Name = “Elton John”
WHERE Id = 4;

You can also update columns in a table by using values from other tables. Use  JOIN  clause to get data from multiple tables. The syntax is:

您还可以通过使用其他表中的值来更新表中的列。 使用JOIN子句从多个表中获取数据。 语法为：

UPDATE table_name1
SET table_name1.column1 = table_name2.columnA
    table_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key

Here is an example updating Manager of all records:

这是所有记录的更新管理器的示例：

UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID

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

mysql更新语句sql