MySQL备忘之数据更新篇(INSERT, UPDATE, DELETE)

INSERT

插入完整行与NULL

• not null字段必须插入值
• auto_increment字段可以不填入，即使填入NULL值也是不必要的，由DBMS自动计算并填充。
• 如果表的定义允许，则可以在INSERT操作中省略某 些列。省略的列必须满足以下某个条件。
  
  • 该列定义为允许NULL值（无值或空值）。
  • 在表定义中给出默认值。这表示如果不给出值，将使用默 认值。
  • 如果对表中不允许NULL值且没有默认值的列不给出值，则 MySQL将产生一条错误消息，并且相应的行插入不成功。

插入多行

MySQL用单条INSERT语句处理多个插入比使用多条INSERT 语句快，

INSERT INTO customers（cust_name, cust_address） VALUES('Pep E. LaPew', '100 Main Street');
INSERT INTO customers（cust_name, cust_address） VALUES('Pep E. LaPew', '100 Main Street');

# 其中单条INSERT语句有多组值，每组值用一对圆括号括起来， 用逗号分隔。 
INSERT INTO customers（cust_name, cust_address） VALUES('Pep E. LaPew', '100 Main Street'), ('Pep E. LaPew', '100 Main Street');

# 后者的速率比前者快，尤其是需要插入大量的数据的时候

INSERT……SELECT……

典型应用场景

把一个名为custnew的表中的数据 导入customers表中。为了试验这个例子，应该首先创建和填 充custnew表。

mysql语句

INSERT INTO customers(cust_contact, cust_email,cust_name, cust_city) 
SELECT (cust_contact, cust_email,cust_name, cust_city) FROM custnew;

# cust_id是auto_increment，所以可以省略该字段
#倘若需要保留custnew表格中的cust_id的话，也可导入该字段，前提是保证cust_id的值不重复

tips

• MySQL不关心SELECT返回的列名，它使用的是列的位置
  为简单起见，这个例子在INSERT和 SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。 因此SELECT中的第一列（不管其列名）将用来填充 表列中指定的第一个列，第二列将用来填充表列中指定的第二 个列，如此等等。
• INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据

UPDATE

不加限定条件（where子句）会更新所有行中指定的列值。

IGNORE关键字

• 更新多行，并且在更新这些行中的一行或多行时出现一个错误，整个UPDATE操作将被取消。
• 即使是发生错误也要继续进行更新，可使用IGNORE关键字。

UPDATE IGNORE customers
SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

删除列值

假如某列允许NULL值，在删除该列值时可设置它为NULL.

UPDATE IGNORE customers
SET cust_name = NULL
WHERE cust_id = 10005;

DELETE

• DELETE删除整行而不是删除列。为了删除指定的列，使用UPDATE.
• 不加限定条件的DELETE，会删除表中所有的行，但是不会删除表。如果想要删除表中的所有的行，使用TRUNCATE效率更高。TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据，因而效率比DELETE删除表中的数据的效率要高。

TRUNCATE TABLE customer;
#删除表格customer

注意事项

MySQL没有undo按钮，所以在使用数据更新操作时要特别小心。

为什么insert操作也要小心呢？倘若Table使用auto_increment的数字id作为主键，并且还有创建了记录创建时间与更新时间的字段当然没有问题。但是，肘子最坑的情况是，4W+的数据，使用UUID作为主键，没有时间记录的字段，误操作插入了几百条冗余数据，难过T……T

措施：

• 在进行更新操作前，先使用select进行测试，保证它过滤的是正确的记录。
• 强制实施引用完整性的数据库（外键），这样MySQL将不允许删除具有与其他表相关联的数据的行。