MySQL/Language/Data manipulation

INSERT

编辑
 INSERT INTO TableName (Column1, Column2, Column3)
 VALUES (value1, value2, value3)

按照列在表中的顺序插入一条记录:

 INSERT INTO TableName
 VALUES (value1, value2, value3)

插入两条记录:

 INSERT INTO TableName
 VALUES (value1, value2, value3), (value4, value5, value6)

 INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);
 INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');

从其他表中抽取数据插入:

 INSERT INTO table1(field1, field2)
 SELECT field1, field2
 FROM table2

 INSERT INTO World_Events SELECT * FROM National_Events

性能提示:

  • 插入很多行,建议用LOAD DATA INFILE
  • 如果INSERT很慢且运行在索引非空表,增加变量bulk_insert_buffer_size的值
  • 在大量插入前,你可以禁止keys
  • LOCK表可以加快INSERT.

UPDATE

编辑
 UPDATE table SET field1 = newvalue1, field2 = newvalue2 WHERE criteria ORDER BY field LIMIT n

例子:

 UPDATE owner SET ownerfirstname = 'John'
   WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');
 
 UPDATE antiques SET price = 500.00 WHERE item = 'Chair';
 
 UPDATE order SET discount=discount * 1.05
 
 UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
   SET tbl1.col1 = tbl1.col1 + 1
   WHERE tbl2.status='Active'
 
 UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz')
 
 UPDATE products_categories AS pc
   INNER JOIN products AS p ON pc.prod_id = p.id
   SET pc.prod_sequential_id = p.sequential_id
 
 UPDATE table_name SET col_name =
   REPLACE(col_name, 'host.domain.com', 'host2.domain.com')

 UPDATE posts SET deleted=True
   ORDER BY date LIMIT 1

使用ORDER BY可以在修改前先排序,然后只修改更定数量的行(LIMIT).

目前不可能对正在修改的表执行子查询。例如:

mysql> UPDATE spip_auteurs SET pass =
 (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin';
ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause

性能提示

  • UPDATE的速度依赖于多少个索引被修改。
  • 锁表后UPDATE多行会更快。

REPLACE

编辑

REPLACE类似于INSERT,除了如果根据主键或者唯一索引,表中有老行与要新加入的行有同样的主键(或唯一索引), 则先删除这些老行。

IGNORE

编辑

从MySQL 5.5[1], "INSERT IGNORE" 或 "REPLACE IGNORE" 允许忽略掉主键重复错误。

DELETE 与 TRUNCATE

编辑
 DELETE [QUICK] FROM `table1`
 TRUNCATE [TABLE] `table1`
  • 如果不用WHERE子句,则DELETE删除所有行。
  • 大表可能很慢,特别是有很多index时。
  • 如果表有很多索引,增大cache会使DELETE更快(key_buffer_size变量)
  • TRUNCATE实际上做DROP然后CREATE table。
  • TRUNCATE不是事务安全,也不是锁安全。
  • DELETE会告诉你多少行删除;而TRUNCATE不会
  • DELET 30%以上的行之后, 应该紧接着OPTIMIZE TABLE
  • InnoDB表带FOREIGN KEY约束, TRUNCATE同义DELETE.
 DELETE FROM `antiques`
   WHERE item = 'Ottoman'
   ORDER BY `id`
   LIMIT 1

删除前先排序,可以只删除给定的行。

参考文献

编辑