分享

转载 MySQL的Insert/replace/update/delete的几个使用方法

 mangtuzi 2012-09-20
1.Insert的几种区别标准写法的用法:
(1)Insert 同时插入多条记录
    Insert into tab(col1,col2,col3) values(a1,a2,a3),(b1,b2,b3),(c1,c2,c3);
    对比分多次单条插入记录来说,这种写法减少了SQL分析、优化的次数,因此能减轻服务器的负担,提高插入记录效率。
(2)Insert插入一条记录(如果使用了SET方式,必须至少为一列赋值。)
    Insert into tab set col1=a1,col2=a2,col3=a3;  <=>  Insert into tab(col1,col2,col3) values(a1,a2,a3);
我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用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和insert/replace一样返回所更新的记录数,都是通过row_count()函数返回。但是这里有个例外,在之前的我的一篇文章中(MySQL中计算sql语句影响行数的函数 )提到过的,返回的这些记录数都不包括满足WHERE条件的,但却未被更新的记录。
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就将多余的字符串截去。如果设置非空字段为空,那么将这个字段设置为它们的默认值,数字的默认值是0,字符串的默认值是空串(不是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在发现有重复记录时就将这条记录彻底删除,再插入新的记录。也就是说,将所有的字段都更新了。
4.delete(http://hi.baidu.com/neonway/blog/item/51c4c158e1b996dd9d820459.html) 
在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返回的是0。 
如果一个表中有自增字段,使用TRUNCATE TABLE。 
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;

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多