MySQL数据库学习笔记之插入更新与删除数据（Linux系统）

目录

插入更新与删除数据

1、插入数据(INSERT)

1）为表的所有字段插入数据

2）为表的指定字段插入数据

3）同时插入多条记录

4）将查询结果插入表中

2、更新数据(UPDATE)

3、删除数据(DELETE)

4、为表增加计算列

5、注意事项

插入更新与删除数据

存储在系统中的数据是数据库管理系统（DBMS）的核心，数据库被设计用来管理数据的存储，访问和维护数据的完整性。MySQL中提供了功能丰富的数据库管理语句，包括有效地向数据库中插入数据的INSERT语句，更新数据的UPDATE语句及当数据不再使用时删除数据的DELETE语句。

1、插入数据(INSERT)

在使用数据库之前，数据库中必须要有数据，MySQL中使用INSERT语句向数据库表中的插入新的数据记录。

可以插入的方式有插入完整的记录，插入记录的一部分，插入多条记录，插入另一个查询的结果。

1）为表的所有字段插入数据

使用基本的INSERT语句插入数据要求指定表名称和插入到新纪录中的值。

基本语法格式为：

INSERT INTO table_name (column_list) VALUES (value_list)

#table_name指定要插入数据的表名
#column_list指定要插入数据的那些列
#value_list指定每个列应插入的数据
#注意，使用该语句时字段列和数据值的数量必须相同

本文使用样例表，创建语句如下：

向表中所有字段插入值的方法有两种：一种是指定所有字段名，另一种是完全不指定字段名。

例1：在person表中，插入一条新纪录，id值为1，name值为Green，age值为21，info值为Lawyer

INSERT语句后面的列名称顺序可以不是person表定义时的顺序，即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与字段的顺序相同就可以。

例2：在person表中，插入一条新记录，id值为2，name值为Suse，age值为22，info值为dancer（字段顺序倒一下，但是要求对应数据和字段一致）

使用INSERT插入数据时，允许列名称列表column_list为空，此时，值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

例3：在person表中，插入一条新记录，id值为3，name值为Mary，age值为24，info值为Musician。

注意：虽然使用INSERT插入数据时可以忽略插入数据的列名称，但是值如果不包含列名称，那么VALUES关键字后面的值不仅要求完整而且顺序必须和表定义时列的顺序相同。

如果表的结构被修改，对列进行增加，删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称，则不会收到表结构改变的影响。

2）为表的指定字段插入数据

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

例4：在person表中，插入一条新记录，name值为Willam，age值为20，info值为sport man

注意：这里，id字段为表的主键，不能为空，系统会自动为该字段插入自增的序列值。

在插入记录时，如果某些字段没有插入值，MySQL将插入该字段定义时的默认值。

例5：在person表中，插入一条新记录，name值为laura，age值为25

在本例中，没有只当info字段值，查询结果显示，info字段在定义时默认为NULL，因此系统自动为该字段插入空值。

注意：要保证每个插入值的类型和对应的数据类型匹配，如果类型不同，将无法插入，并且MySQL会产生错误。

3）同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号隔开。

基本语法格式如下：

INSERT INTO table_name (column_list)
VALUES (value_list),(value_list2),...,(value_listn);

例6：在person表中，在name，age和info字段中指定插入，同时插入3条新记录

由结果可以看到，INSERT语句执行后，person表中添加了3条记录，其name和age，info字段分别为指定的值，id字段为MySQL添加的默认子增值。

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
Records：表明插入的记录条数；

Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值；

Warnings：表明有问题的数据值，例如发生数据类型转换。

例7：在person表中，不指定插入列表，同时插入2条新记录

person表后面没有指定插入字段列表，因此VALUES关键字后面的多个值列表都要为每一条记录的每一个字段列表指定插入值，并且这些值的顺序必须和person表中字段定义的顺序相同。

带有AUTO_INCREMENT属性的id字段插入NULL值，系统会自动为该字段插入唯一的自增编号。

注意：一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高，因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

4）将查询结果插入表中

INSERT还可以将SELECT语句查询的结果插入表中；

如果想要从另一个表中合并个人信息到person表，不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

INSERT INTO table_name1 (column_list)
SELECT (column_list2) FROM table_name2 WHERE (condition);

#table_name1指定待插入数据的表；
#column_list1指定待插入表中要插入数据的哪些列；
#table_name2指定插入数据是从哪个表中查询出来的；

#注意，column_list2指定数据来源表的查询列，该列表必须和column_list1列表中的字段个数相同，数据类型相同。

#condition指定SELECT语句的查询条件。

例8：从person_old表中插入所有的记录，并将其插入到person表中

首先，创建一个名为person_old的数据表，其表结构与person结构相同，并向person_old表中添加两条记录。

将person_old表中所有的记录插入到person表中，SQL语句如下：

注意，这里的id字段为自增的主键，在插入的时候要保证该字段值的唯一性，如果不能确定，可以在插入的时候忽略该字段，只插入其他字段的指。

比如再次插入，就会报错，id重复：

但是，不指定id，插入就可以了，但是会插入很多重复值（慎重）

注意：这个例子中使用的person_old表和person表的定义相同。事实上，MySQL不关心SELECT返回的列名，它根据列的位置进行插入，SELECT的第一列对应待插入表的第一列，第二列对应插入表的第2列......即使不同结果的表之间也可以方便地转移数据。

2、更新数据(UPDATE)

MySQL中使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有的行。

基本语法结构如下：

UPDATE table_name
SET column_name1=value1,column_name2=value2,...,column_namen=valuen
WHERE (condition);

#column_name1,column_name2,...,column_namen为指定更新的字段的名称；
#value1,value2,...,valuen为相对应的指定字段的更新值；
#condition指定更新的记录需要满足的条件
#更新多列时，每个"列—值"对之间用逗号隔开，最后一列之后不需要逗号

例9：在person表中，更新id值为11的记录，将age字段值改为15，将name字段值改为LiMing

注意：保证UPDATAE以WHERE子句结束，通过WHERE子句指定被更新的记录所需要满足的条件，如果忽略WHERE子句，MySQL将更新表中所有的行

例10：在person表中，更新age值为19~22的记录，将info字段值都改为student

3、删除数据(DELETE)

从数据表中删除数据使用DELETE语句，DELETE语句允许WHERE子句指定删除条件。

DELETE语句基本语法如下：

DELETE FROM table_name [WHERE <condition>];

#table_name指定要执行删除的表；
#[WHERE <condition>]为可选参数，指定删除条件；
#如果没有WHERE子句，DELETE语句将删除表中所有的记录。

例11：在person表中，删除id等于11的记录

例12：在person表中，使用DELETE语句同时删除字段值在19~22之间的记录

例13：删除person表中所有记录

注意：如果想删除表中所有记录，还可以使用TRUNCATE TABLE语句，TRUNCATE将直接删除原来的表，并重新创建一个表，其语法结构为：

TRUNCATE TABLE table_name;

TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。

其实就是delete删除表中的数据，表和表的结构还在，仅仅是一张空表而已，而TRUNCATE则是将表都删除了，然后重新创建一个表，所有TRUNCATE比DELETE速度快。

总之，DELETE和TRUNCATE都是删除表里面的数据，仅仅留着一张空表（重建create的时候提示表格已经存在），DROP才是真正的删除表格（空表也没有了，需要重建create）

4、为表增加计算列

什么是计算列呢？

简单来说就是某一列的值是通过别的列计算得来的。

例如a列的值为1，b列的值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

增加计算列的语法格式如下：

col_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[NOT NULL | NULL] [[PRIMARY] KEY]

例14：定义数据表tb1，然后定义字段id，字段a，字段b和字段c，其中字段c为计算列，用于计算a+b的值

首先，创建测试表tb1

查询数据表tb1中的数据，结果可以看到c为300

更新数据库中的数据

再次查询数据表tb1中的数据，结果可以看到c为700

从结果可以看出，字段c中的数据始终是字段a和字段b的和，随着字段a和字段b中数据的变化，自动重新计算a+b的值。

5、注意事项

疑问1：插入记录时可以不指定字段名称吗？

不管使用哪种INSERT语法，都必须给粗VALUES的正确数目。如果不提供字段名，就必须给每个字段提供一个值，否则将产生一条错误消息。如果要在INSERT操作中省略某些字段，那么这些字段需要满足一定条件：该列定义为允许空值；或者表定义时给出默认值，如果不给出默认值，将使用默认值。

疑问2：更新或者删除表时必须指定WHERE子句吗？

在前面的内容中可以看到，所有的UPDATE和DELETE语句全都在WHERE子句中指定了条件。如果省略WHERE子句，那么UPDATE或DELETE将被应用到表中所有的行。因此，除非确实打算各更新或者删除所有记录，否则要注意使用不带WHERE子句的UPDATE或DELETE语句。

建议在对表进行更新和删除操作之前，使用SELECT语句确认需要删除的记录，以免造成无法挽回的结果。