软件开发架构师

MySQL(九)插入、更新和删除

数据库 18 2019-06-12 15:09

常用的SQL语句,除了select用于查询,还有insert、update、delete等。

 

一、insert

insert:用来插入(或添加)行到数据库中,常见方式有以下几种:

插入完整的行;

插入行的一部分;

插入多行;

插入某些查询的结果;

1、插入完整的行

例如:insert into usertable

    values(null,

        'zhangsan',

        '25',

        '1992-05-20',

        null,

        null);

这条SQL语句插入一条新的个人信息到usertable表,存储在每个表列中的数据在values中给出,对每个列必须提供一个值,如果某个列没有值,应使用null(假定表允许对该项指定空值);

各个列必须以它们在表定义中出现的次序填充;缺点在于;高度依赖表中列的定义次序,如果表结构出现变动,难免出现问题。

更安全的方法如下(相对比较麻烦):

例如:insert into usertable(user_id,

     user_name,

     user_year,

     user_birthday,

     user_address,

     user_email)

values(null,

     'zhangsan',

     '25',

     '1992-05-20',

     null,

     null);

这条SQL语句与上面的SQL语句完成同样的工作,但给出了列名,valuse必须以指定的次序匹配指定的列名,不一定按照列出现在表中的实际次序;优点是:即使表结构改变,此insert语句仍然正确工作。

insert使用注意事项:

无论使用哪种insert语法,都必须给出values的正确数目,如果不提供列名,则必须给每个表列提供一个值;如果提供列名,则必须对每个列出的列给出一个值;

如果表定义允许,则可以在insert操作中省略某些列,列必须满足这两个条件(1.改列定义为允许null值;2.在表定义中给出默认值【如果不给出值,则使用默认值】);

数据库被多个用户访问,一般检索是最重要的,可以通过在insert和into中间添加关键字low priority,指示MySQL降低insert语句的优先级(同样适用于update和delete语句);

2、插入多个行

例如:insert into usertable(user_id,

     user_name,

     user_year,

     user_birthday,

     user_address,

     user_email)

values(null,

     'zhangsan',

     '25',

     '1992-05-20',

     null,

     null);

或者只要每条insert语句中的列名(和次序)相同,可以只在insert语句后面输入一次列名就可以(单个insert语句由多组值,每组值用一堆圆括号包含,用逗号隔开)

PS:MYSQL用单条insert语句处理多个插入比使用多个insert语句快。

3、插入检索出的数据

例如:insert into usertable(user_id,

     user_name,

     user_year,

     user_birthday,

     user_address,

     user_email)

select user_id,

     user_name,

     user_year,

     user_birthday,

     user_adress,

     user_email

from newtablebase;

这条SQL语句中,select从newtablebase表中检索出要插入的数据,select列出的每个列对应tablebase表名后所跟的列表中的每个列。

PS:insert select语句中,不一定要求列名匹配,实际上select中的第一列将用来填充表列中指定的第一列;insert select语句中select语句可包含where子句过滤插入的数据。

 

二、更新数据

如果要更新(修改)表中的数据,可以使用update语句,有以下两种方法:

更新表中特定行;

更新表中所有行;

update语句由三部分构成:

要更新的表;

列名和它们的新值;

确定要更新行的过滤条件;

例如:update usertable

     set user_email = '3033310371@qq.com'

     where user_id = '10086';

这条SQL语句中,要更新的表名为usertable,set命令用来将新值赋给被更新的列user_email;

PS:update语句总是以要更新的表的名字开始,以where子句结束,它告诉MySQL更新哪一行。

更新多个列:update usertable

          set user_name = 'zhangsan',

          user_email = '3033310371@qq.com'

          where user_id = '10086';

更新多个列时,只需要使用单个set命令,每个“列=值”对之间用逗号分隔(最后一列不用逗号)。

PS:如果用update更新多个行,且更新中一行或多行出现错误,则update操作将被取消(错误发生前执行的动作将被回滚到最初状态。即已更新的被恢复原来的值);

   如果希望出错误,更新继续执行,可以使用ignore关键字:update ignore tablebase......

   如果希望删除一个值,可以将它设置为null(如果表定义允许null值),例子如下:

   update usertable

   set user_email = null

   where user_id = '10086';

 

三、删除数据

从一个表汇总删除数据,使用delete语句;有以下两种方式:

从表中删除特定行;

从表中删除所有行;

例如:delete from usertable where user_id = '10086';

这条SQL语句中,delete from要求指定从中删除数据的表名,where子句过滤要删除的行。

PS:delete不需要列名或者通配符,delete删除整行而不是整列,为了删除指定的列,可使用update语句(delete从表中删除行甚至表中所有行,但不删除表本身);

   如果想删除表中所有行,可使用truncate table语句,它的执行效率比delete更快(truncate实际上是删除原来表并重新新建一个表,而不是逐行删除表中数据)。

更新和删除规则:

除非确实打算更新或删除每一行,否则决不能使用不带where子句的update或delete语句;

保证每个表都有主键,尽可能像where子句那样使用;

对update和delete语句使用where子句前,最好先select进行测试,保证过滤的数据是正确的;

使用强制实施引用完整性的数据库(这样MySQL将不允许删除具有与其他表相关联的数据的行)。

 

文章评论