本文共 7180 字，大约阅读时间需要 23 分钟。
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更新语句-它是什么，它可以做什么以及在使用它之前需要注意的事项。
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:
本文的这一部分将：
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 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)
SELECT FullName, instr(FullName," ") AS firstSpacePosition, concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu") AS schoolEmailFROM 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.
在现实世界中，这将变得更加复杂，并且您需要确保电子邮件地址是唯一的。
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.
请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。
翻译自:
sql子查询示例
转载地址：http://tbuzd.baihongyu.com/