更新sql语句 sql注入_SQL更新语句– SQL中的更新查询

更新sql语句 sql注入

SQL Update Statement or Update Query in SQL is used to modify the column data in tables. Earlier we looked into SQL Select and SQL Insert queries.

在SQL UPDATE语句或更新查询SQL来修改表列数据。 之前我们研究了SQL Select和SQL Insert查询。

SQL更新 (SQL Update)

In a database the storage and retrieval of data is the most important aspect. But, there are cases when we have inserted some incorrect data by mistake or the data that is inserted needs some modification.

在数据库中，数据的存储和检索是最重要的方面。 但是，在某些情况下，我们可能错误地插入了一些不正确的数据，或者所插入的数据需要进行一些修改。

Modification of data that is existing in the database is a performance oriented task. In case if the number of records that needs to be updated is huge, the performance impact will be huge. SQL UPDATE statement is used for modification of existing records.

修改数据库中现有的数据是面向性能的任务。 如果需要更新的记录数量巨大，则对性能的影响将会很大。 SQL UPDATE语句用于修改现有记录。

SQL更新查询语法 (SQL Update Query Syntax)

For Updating Selected Records:

更新所选记录 ：

UPDATE table_name SET column1 = value 1, column2 = value 2,.. column_n = value_n
WHERE condition;

In the syntax above the update happens based on the condition that is specified in the WHERE clause. Let’s try to understand the sql update query through some example.

在以上语法中，更新是根据WHERE子句中指定的条件进行的。 让我们尝试通过一些示例来了解sql更新查询。

Let’s say we have a Customer table with some data into it, below the SQL query to create table and populate with some data. I am using MySQL database, for other database vendors below queries will be slightly different.

假设我们有一个包含一些数据的Customer表，位于SQL查询下方，用于创建表并填充一些数据。 我使用的是MySQL数据库，对于下面的其他数据库供应商的查询将略有不同。

CREATE TABLE `Employee` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `age` int(2) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `Employee` (`id`, `name`, `age`, `gender`)
VALUES
	(1, 'John', 30, 'M'),
	(2, 'Smith', 25, 'F'),
	(3, 'Henry', 20, 'M'),
	(4, 'Lisa', 32, 'M');
commit;

Now notice that Lisa gender is wrongly inserted as ‘M’. So we can update it to ‘F’ using update query in sql statement like below.

现在请注意，Lisa性别错误地插入为“ M”。 因此，我们可以使用sql语句中的更新查询将其更新为“ F”，如下所示。

UPDATE employee SET gender='F' WHERE id=4;

For Updating All Records:

对于更新所有记录 ：

UPDATE table_name SET column1 = value 1, column2 = value 2,.. column_n = value_n

In the syntax above the update happens without any condition and will update all the records of the table. Let’s say we want to remove Gender column values from Employee table. We can do the bulk update of all the rows using update query as shown below.

在上面的语法中，更新没有任何条件发生，并且将更新表的所有记录。 假设我们要从Employee表中删除Gender列值。 我们可以使用update查询对所有行进行批量更新，如下所示。

UPDATE employee SET gender=null; --sets gender value to NULL

UPDATE employee SET gender=null; --clears gender value for all rows

The most important point to consider with update query is the where clause, as the update is dependent on the WHERE clause.

update查询要考虑的最重要一点是where子句，因为更新依赖于WHERE子句。

The records that will get updated will be selected based on the WHERE clause and in case if there is no WHERE clause all the records of the table will get updated.

将基于WHERE子句选择将要更新的记录，如果没有WHERE子句，则将更新表的所有记录。

SQL更新选择 (SQL Update Select)

Sometimes we have a situation where we want to update one table data by selecting value from another table. Let’s suppose we have two tables – POSTS and AUTHORS. Below SQL query shows their structure and some sample data generated for our SQL Update Select query example.

有时我们遇到一种情况，我们想通过从另一个表中选择值来更新一个表数据。 假设我们有两个表-POSTS和AUTHORS。 下面SQL查询显示了它们的结构以及为我们SQL Update Select查询示例生成的一些示例数据。

CREATE TABLE `AUTHORS` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `POSTS` (
  `id` int(11) NOT NULL,
  `title` varchar(20) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


insert into authors (id, name) values
(1, 'Pankaj'),
(2, 'Anupam'),
(3, 'Ram');


insert into posts (id, title, author_id) values 
(1, 'Java', 1),
(2, 'Python', 2),
(3, 'SQL', 3),
(4, 'Android', 1);

If you look at above setup, AUTHORS table seems useless because all it’s use is to map author name to the post. So we can add a column for author name in the POSTS table and populate it’s value from corresponding rows in AUTHORS table.

如果您看上面的设置，那么AUTHORS表似乎没有用，因为它的全部作用是将作者姓名映射到帖子。 因此，我们可以在POSTS表中添加一列作者姓名，并从AUTHORS表中的相应行中填充它的值。

We can alter POSTS table using below query.

我们可以使用以下查询更改POSTS表。

alter table POSTS add column author_name varchar(20);

Below is the sql update with select query to update one table columns data from another table.

下面是带有select查询的sql更新，用于更新另一个表中的一个表列数据。

UPDATE POSTS SET author_name = (select name from AUTHORS where POSTS.author_id = AUTHORS.id);

SQL更新联接示例 (SQL Update Join Example)

We can perform above update operation using joins too. Below SQL update with inner join query will have the same result as above sql update with select query.

我们也可以使用联接执行上述更新操作。 使用内部连接查询SQL更新以下将具有与使用选择查询SQL更新相同的结果。

UPDATE POSTS AS P
INNER JOIN AUTHORS AS A ON P.author_id = A.id
SET P.author_name = A.name;

Note that all the above queries are for MySQL database, for other databases there might be small change needed. But the underlying operation and output will remain same.

请注意，以上所有查询均针对MySQL数据库，对于其他数据库，可能需要进行少量更改。 但是底层的操作和输出将保持不变。

That’s all for SQL update statement, I hope above update queries will help you in writing proper sql queries for update.

SQL更新语句就这些了，我希望上面的更新查询能帮助您编写适当SQL查询来进行更新。

Reference: Oracle Documentation

参考： Oracle文档

翻译自: https://www.journaldev.com/18285/sql-update-statement-query

更新sql语句 sql注入