详解MySQLinsert和update语句

用来操作的SQL一般分为两种，一种是检索语法，也就是我们所说的SELECT语句，另外一种就是更新语句，也就叫数据操作语法。SQL语句中的更新语句update是最常见的句子之一，言外之意，就是对数据进行修订。在标准的SQL中有3个语句，这些是INSERT、UPDATE以及DELETE。

用来操作的SQL一般分为两种，一种是检索语法insert into 和update，也就是我们所说的SELECT语句，另外一种就是更新语句，也就叫数据操作语法。言外之意，就 是对数据进行修订。在标准的SQL中有3个语句，这些是INSERT、UPDATE以及DELETE。在MySQL中又多了一个REPLACE语句，因 此，本文以MySQL为背景来讨论怎样使有SQL中的更新语句。

一、INSERT和REPLACE

INSERT和REPLACE语句的系统都是向表中插入新的数据。这两条语句的句子类似。它们的主要差别是怎么处理重复的数据。

1. INSERT的一般用法

MySQL中的INSERT语句和标准的INSERT不太一样，在标准的SQL语句中，一次插入一条记录的INSERT语句只有一种形式。

INSERT INTO tablename(列名…) VALUES(列值);

而在MySQL中还有另外一种形式。

#a4dd029a3c1186b046ed206b137a201#

第一种方法将列名和列值分开了，在使用时，列名必须和列值的数一致。如上面的句子向users表中插入了一条记录：

INSERT INTO users(id, name, age) VALUES(123, '姚明', 25);

第二种方法禁止列名和列值成对出现和应用，如上面的句子将形成中样的作用。

INSERT INTO users SET id = 123, name = '姚明', age = 25;

因为使用了SET方式insert into 和update，必须至少为一列赋值。如果某一个字段使用了省缺值（如默认或自增值），这两种方式都可以省略那些函数。如id变量上使用了自增值，下面两条语句可以写出如下形式：

INSERT INTO users (name, age) VALUES('姚明',25);
INSERT INTO uses SET name = '姚明', age = 25;

MySQL在VALUES上也做了些改变。如果VALUES中怎么都不写，那MySQL将使用表中每一列的默认值来插入新记录。

INSERT INTO users () VALUES();

因为表名后怎么都不写，就认为向表中一切的函数赋值。使用这些方法，而且在VALUES中的值要和列数一致，但是顺序不能颠倒。

 INSERT INTO users VALUES(123, '姚明', 25);

因为将INSERT语句写出如下形式MySQL将会报错。

INSERT INTO users VALUES('姚明',25);

2. 使用INSERT插入多条记录

看到这个文章也许你们会问，这有多少好说的，读取多次INSERT语句不就可以插入多条记录了吗！但使用这些方式要提高服务器的负荷，因为，执行每一次 SQL服务器都要同时对SQL进行分析、优化等操作。幸好MySQL提供了另一种解决方案，就是使用一条INSERT语句来插入多条记录。这并不是标准的 SQL语法，所以没法在MySQL中使用。

INSERT INTO users(name, age)
VALUES('姚明', 25), ('比尔.盖茨', 50), ('火星人', 600);

里面的INSERT 语句向users表中连续插入了3条记录。值得注意的是，下面的INSERT语句中的VALUES后必须每一条记录的值放在一对(…)中，后面使用","分割。假设有一个表table1

CREATE TABLE table1(n INT)；

因为要向table1中插入5条记录，上面写法是错误的：

INSERT INTO table1 (i) VALUES(1,2,3,4,5);

MySQL将会抛出下面的错误

ERROR 1136: Column count doesn't match value count at row 1

而正确的句式应该是如此：

INSERT INTO t able1(i) VALUES(1),(2),(3),(4),(5);

其实，这样句式也可以省略列名，这种每一对括号里的值的数量必须一致，但是这个数量必须和列数一致。如：

INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);

3. REPLACE语句

我们在使用时可能会经常碰到这样现象。如果一个表在一个字段上构建了惟一索引，当我们再向这个表中使用已经存在的函数插入一条记录，那将要抛出一 个字段冲突的错误。当然，我们应该想用新记录的值来覆盖原来的记录值。如果使用传统的作法，必须先使用DELETE语句删除原先的记录，然后再使用 INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案，这就是REPLACE语句。使用REPLACE插入一条记录时，所以不重 复，REPLACE就和INSERT的系统一样，所以有重复记录，REPLACE就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，产生一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等繁杂操作了。

在使用REPLACE时，表中必须有惟一索引，但是这个函数所在的函数不能允许空值，如果REPLACE就和INSERT完全一样的。

在执行REPLACE后，功能回到了所影响的行数，所以离开1，表明在表中并没有重复的记录，所以离开2，表明有一条重复记录，功能自动先读取了 DELETE删除这条记录，然后再记录用INSERT来插入这条记录。如果离开的值大于2，那证明有多个惟一索引，有多条记录被删除和插入。

REPLACE的句子和INSERT非常的相同，如上面的REPLACE语句是插入或更新一条记录。

REPLACE INTO users (id,name,age) VALUES(123, '赵本山', 50);

插入多条记录：

REPLACE INTO users(id, name, age)
VALUES(123, '赵本山', 50), (134,'Mary',15);
REPLACE也可以使用SET语句
REPLACE INTO users SET id = 123, name = '赵本山', age = 50;

里面曾说过REPLACE可能影响3条以上的记录，这是因为在表中有达到一个的惟一索引。在这样现象下，REPLACE将考虑每一个唯一索引，并对每一 个函数对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都

有一个唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

假设table1中早已有了3条记录

a b c

1 1 1

2 2 2

3 3 3

上面我们使用REPLACE语句向table1中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

前往的结果如下

Query OK, 4 rows affected (0.00 sec)

在table1中的记录如下

a b c

1 2 3

我们可以看见，REPLACE将原先的3条记录都删除了，然后将（1, 2, 3）插入。

二、UPDATE

UPDATE的系统是更新表中的数据。这的句子和INSERT的第二种用法相同。必须提供表名还有SET表达式，在前面可以加

WHERE以受限更新的记录范围。

UPDATE table_anem SET column_name1 = value1, column_name2 = value2, ...
WHERE ... ;

如上面的句子将users表中id等于123的记录的age改为24

UPDATE users SET age = 24 WHERE id = 123;

其实，可以使用UPDATE更新多个函数的值 UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;

里面的UPDATE语句通过WHERE指定一个条件，如果，UPDATE将更新表中的一切记录的值。

在使用UPDATE更新记录时，所以被更新的函数的类型和所赋的值不匹配时，MySQL将这个值转换为对应类型的值。如果这个函数是差值类型，但是所赋 值达到了这个数据类型的最大范围，所以MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长，MySQL就将多余的字符串截去。如果增设非空 字段为空，所以将这个函数设置为这些的默认值，数字的默认值是，字符串的默认值是空串（不是null，是""）。

有两种情形UPDATE不会对影响表中的数据。

1. 当WHERE中的条件在表中没有记录和它匹配时。

2. 当我们将同样的值赋给某个函数时，如将函数abc赋为'123'，而abc的差额就是'123'。

和INSERT、REPLACE一样，UPDATE也离开所更新的记录数。但这种记录数并不包括满足WHERE条件的，但却未被更新的记录。如下同的UPDATE语句就未更新任何记录。

UPDATE users SET age = 30 WHERE id = 12;
Query OK, 0 rows affected (0.00 sec)

需要注意的时，所以一个字段的类型是TIMESTAMP，所以这个函数在其他函数更新时自动更新。

在有些时候我们需要得到UPDATE所选择的行数，而不是被更新的行数。我们可以通过一些API来接近这个动机。如MySQL提供的C API提供了一个选项可以得到你想要的记录数。而MySQL的JDBC驱动得到的默认记录数作为匹配的记录数。

UPDATE和REPLACE基本相似，所以这些之间有两点不同。

1. UPDATE在没有匹配记录时怎么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。

2. UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的函数都更新了。

三、DELETE和TRUNCATE TABLE

在MySQL中有两种方式可以删除数据，一种是DELETE语句，另一种是TRUNCATE TABLE语句。DELETE语句可以通过WHERE对要删除的记录进行选择。而使用TRUNCATE TABLE将删除表中的一切记录。因此，DELETE语句更灵活。

因为要清空表中的一切记录，可以使用上面的两种方式：

DELETE FROM table1
TRUNCATE TABLE table1

其中第二条记录中的TABLE是可选的。

因为要删除表中的部份记录，没法使用DELETE语句。

DELETE FROM table1 WHERE ...;

因为DELETE不加WHERE子句，所以它和TRUNCATE TABLE是一样的，但这些有一点不同，那就是DELETE可以离开被删除的记录数，而TRUNCATE TABLE返回的是。

因为一个表中有自增函数，使用TRUNCATE TABLE和没有WHERE子句的DELETE删除一切记录后，这个自增字段将起始值恢复成1.因为你不想这样做的话，可以在DELETE语句中加上永真的WHERE，如WHERE 1或WHERE true。

DELETE FROM table1 WHERE 1;

里面的句子在执行时将扫描每一条记录。但它并不比较，因为这个WHERE条件永远为true。这样做虽然可以维持自增的最大值，但由于它是扫描了所有的记录，所以，它的执行费用要比没有WHERE子句的DELETE大得多。

DELETE和TRUNCATE TABLE的最大区别是DELETE可以通过WHERE语句选择要删除的记录。但执行得速度不快。而且还可以离开被删除的记录数。而TRUNCATE TABLE无法删除指定的记录，但是不能返回被删除的记录。但它执行得十分快。

和标准的SQL语句不同，DELETE支持ORDER BY和LIMIT子句，通过这两个子句，我们可以更好地控制要删除的记录。如当我们只想删除WHERE子句过滤出来的记录的一部分，可以使用LIMIB， 如果要删除后几条记录，可以通过ORDER BY和LIMIT配合使用。假设我们要删除users表中name等于"Mike"的前6条记录。可以使用如下的DELETE语句：

DELETE FROM users WHERE name = 'Mike' LIMIT 6;

一般MySQL并不确定删除的这6条记录是哪6条，为了更保险，我们可以使用ORDER BY对记录进行排序。

DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6

以下所述是小编给你们介绍的MySQLinsert和update语句，能够对你们有所帮助！

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/ruanjian/article-118549-1.html