插入、更新与删除数据1、插入数据1.1、为表的所有字段插入数据1.2、为表的指定字段插入数据1.3、同时插入多条记录1.4、将查询结果插入到表中2、更新数据3、删除数据4、为表增加计算列5、MySQL 8.0的新特性——DDL的原子化1、插入数据在使用数据库之前数据库中必须要有数据MySQL中使用INSERT语句向数据库表中插入新的数据记录。可以插入的方式有插入完整的记录、插入记录的一部分、插入多条记录、插入另一个查询的结果下面将分别介绍这些内容。1.1、为表的所有字段插入数据使用基本的INSERT语句插入数据要求指定表名称和插入到新记录中的值。基本语法格式为INSERTINTOtable_name(column_list)VALUES(value_list);table_name指定要插入数据的表名column_list指定要插入数据的那些列value_list指定每个列应对应插入的数据。注意使用该语句时字段列和数据值的数量必须相同。样例表person创建语句如下createtableperson(idintunsignednotnullauto_increment,namechar(40)notnulldefault,ageintnotnulldefault0,infochar(50)null,primarykey(id));向表中所有字段插入值的方法有两种一种是指定所有字段名另一种是完全不指定字段名在person表中插入一条新记录id值为1name值为Greenage值为21info值为LawyerSQL语句如下执行插入操作之前使用SELECT语句查看表中的数据select*fromperson;---------------------|id|name|age|info|------------------------------------------结果显示当前表为空没有数据接下来执行插入操作insertintoperson(id,name,age,info)values(1,Green,21,Lawyer);可以看到插入记录成功。在插入数据时指定了person表的所有字段因此将为每一个字段插入新的值。INSERT语句后面的列名称顺序可以不是person表定义时的顺序。即插入数据时不需要按照表定义的顺序插入只要保证值的顺序与列字段的顺序相同就可以。在person表中插入一条新记录id值为2name值为Suseage值为22info值为dancerSQL语句如下insertintoperson(age,name,id,info)values(22,Suse,2,dancer);语句执行完毕查看执行结果select*fromperson;------------------------|id|name|age|info|------------------------|1|Green|21|Lawyer||2|Suse|22|dancer|------------------------由结果可以看到INSERT语句成功插入了一条记录。使用INSERT插入数据时允许列名称列表column_list为空此时值列表中需要为表的每一个字段指定值并且值的顺序必须和数据表中字段定义时的顺序相同在person表中插入一条新记录id值为3name值为Maryage值为24info值为MusicianSQL语句如下INSERTINTOpersonVALUES(3,Mary,24,Musician);可以看到插入记录成功。数据库中增加了一条id为3的记录其他字段值为指定的插入值。本例的INSERT语句中没有指定插入列表只有一个值列表。在这种情况下值列表为每一个字段列指定插入值并且这些值的顺序必须和person表中字段定义的顺序相同。虽然使用INSERT插入数据时可以忽略插入数据的列名称但是值如果不包含列名称那么VALUES关键字后面的值不仅要求完整而且顺序必须和表定义时列的顺序相同。如果表的结构被修改对列进行增加、删除或者位置改变操作这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称则不会受到表结构改变的影响。1.2、为表的指定字段插入数据为表的指定字段插入数据就是在INSERT语句中只向部分字段中插入值而其他字段的值为表定义时的默认值。在person表中插入一条新记录name值为Willamage值为20info值为sportsmanSQL语句如下insertintoperson(name,age,info)values(Willam,20,sports man);提示信息表示插入一条记录成功。使用SELECT查询表中的记录查询结果如下select*fromperson;-----------------------------|id|name|age|info|-----------------------------|1|Green|21|Lawyer||2|Suse|22|dancer||3|Mary|24|Musician||4|Willam|20|sports man|-----------------------------可以看到插入记录成功。如查询结果显示该id字段自动添加了一个整数值4。在这里id字段为表的主键不能为空系统会自动为该字段插入自增的序列值。在插入记录时如果某些字段没有指定插入值MySQL将插入该字段定义时的默认值。下面的例子说明在没有指定列字段时插入默认值。在person表中插入一条新记录name值为lauraage值为25SQL语句如下insertintoperson(name,age)values(Laura,25);语句执行完毕查看执行结果select*fromperson;-----------------------------|id|name|age|info|-----------------------------|1|Green|21|Lawyer||2|Suse|22|dancer||3|Mary|24|Musician||4|Willam|20|sports man||5|Laura|25|null|-----------------------------可以看到在本例插入语句中没有指定info字段值查询结果显示info字段在定义时默认为NULL因此系统自动为该字段插入空值。要保证每个插入值的类型和对应列的数据类型匹配如果类型不同将无法插入并且MySQL会产生错误。1.3、同时插入多条记录INSERT语句可以同时向数据表中插入多条记录插入时指定多个值列表每个值列表之间用逗号分隔开基本语法格式如下insertintotable_name(column_list)values(value_list1),(values_list2),...(value_listn);“value_list1,value_list2,…,value_listn”表示第12…n个插入记录的字段的值列表。在person表中在name、age和info字段指定插入值同时插入3条新记录SQL语句如下insertintoperson(name,age,info)values(Evans,27,secretary),(Dale,22,cook),(Edison,28,singer);语句执行完毕查看执行结果select*fromperson;-----------------------------|id|name|age|info|-----------------------------|1|Green|21|Lawyer||2|Suse|22|dancer||3|Mary|24|Musician||4|Willam|20|sports man||5|Laura|25|null||6|Evans|27|secretary||7|Dale|22|cook||8|Edison|28|singer|-----------------------------由结果可以看到INSERT语句执行后person表中添加了3条记录其name和age字段分别为指定的值id字段为MySQL添加的默认的自增值。使用INSERT同时插入多条记录时MySQL会返回一些在执行单行插入时没有的额外信息这些信息的含义如下Records表明插入的记录条数。Duplicates表明插入时被忽略的记录原因可能是这些记录包含了重复的主键值。Warnings表明有问题的数据值例如发生数据类型转换。在person表中不指定插入列表同时插入2条新记录SQL语句如下insertintopersonvalues(9,Harry,21,magician),(null,Harriet,19,painist);语句执行结果如下Query OK,2rowsaffectedTime:0.073s语句执行完毕查看执行结果select*fromperson;------------------------------|id|name|age|info|------------------------------|1|Green|21|Lawyer||2|Suse|22|dancer||3|Mary|24|Musician||4|Willam|20|sports man||5|Laura|25|null||6|Evans|27|secretary||7|Dale|22|cook||8|Edison|28|singer||9|Harry|21|magician||10|Harriet|19|painist|------------------------------由结果可以看到INSERT语句执行后person表中添加了2条记录与前面介绍单个INSERT语法不同person表名后面没有指定插入字段列表因此VALUES关键字后面的多个值列表都要为每一条记录的每一个字段列指定插入值并且这些值的顺序必须和person表中字段定义的顺序相同带有AUTO_INCREMENT属性的id字段插入NULL值系统会自动为该字段插入唯一的自增编号。一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。1.4、将查询结果插入到表中INSERT语句用来给数据表插入记录时指定插入记录的列值。INSERT还可以将SELECT语句查询的结果插入到表中如果想要从另外一个表中合并个人信息到person表不需要把每一条记录的值一个一个输入只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。基本语法格式如下INSERTINTOtable_name1(column_list1)SELECT(column_list2)FROMtable_name2WHERE(condition)table_name1指定待插入数据的表column_list1指定待插入表中要插入数据的哪些列table_name2指定插入数据是从哪个表中查询出来的column_list2指定数据来源表的查询列该列表必须和column_list1列表中的字段个数相同数据类型相同condition指定SELECT语句的查询条件。从person_old表中查询所有的记录并将其插入到person表中。首先创建一个名为person_old的数据表其表结构与person结构相同SQL语句如下createtableperson_old(idintunsignednotnullauto_increment,namechar(40)notnulldefault,ageintnotnulldefault0,infochar(50)null,primarykey(id));可以看到插入记录成功peson_old表中现在有两条记录。接下来将person_old表中所有的记录插入person表中SQL语句如下INSERTINTOperson(id,name,age,info)SELECTid,name,age,infoFROMperson_old;语句执行完毕查看执行结果select*fromperson;------------------------------|id|name|age|info|------------------------------|1|Green|21|Lawyer||2|Suse|22|dancer||3|Mary|24|Musician||4|Willam|20|sports man||5|Laura|25|null||6|Evans|27|secretary||7|Dale|22|cook||8|Edison|28|singer||9|Harry|21|magician||10|Harriet|19|painist|------------------------------由结果可以看到INSERT语句执行后person表中多了两条记录这两条记录和person_old表中的记录完全相同数据转移成功。这里的id字段为自增的主键在插入的时候要保证该字段值的唯一性如果不能确定可以在插入的时候忽略该字段只插入其他字段的值。2、更新数据表中有数据之后接下来可以对数据进行更新操作MySQL中使用UPDATE语句更新表中的记录可以更新特定的行或者同时更新所有的行。基本语法结构如下updatetable_namesetcolumn_name1value1,column_name2value2,...,column_namenvaluenwhere)condition);column_name1,column_name2,……,column_namen为指定更新的字段的名称value1,value2,……,valuen为相对应的指定字段的更新值condition指定更新的记录需要满足的条件。更新多列时每个“列-值”对之间用逗号隔开最后一列之后不需要逗号。在person表中更新id值为10的记录将age字段值改为15将name字段值改为LiMingSQL语句如下UPDATEpersonSETage15,nameLiMingWHEREid10;语句执行完毕查看执行结果select*frompersonwhereid10;--------------------------|id|name|age|info|--------------------------|10|LiMing|15|painist|--------------------------由结果可以看到id等于10的记录中的name和age字段的值已经成功地被修改为指定值。保证UPDATE以WHERE子句结束通过WHERE子句指定被更新的记录所需要满足的条件如果忽略WHERE子句MySQL将更新表中所有的行。在person表中更新age值为19~22的记录将info字段值都改为studentSQL语句如下updatepersonsetinfostudentwhereagebetween19and22;语句执行完毕查看执行结果select*fromperson;----------------------------|id|name|age|info|----------------------------|1|Green|21|student||2|Suse|22|student||3|Mary|24|Musician||4|Willam|20|student||5|Laura|25|null||6|Evans|27|secretary||7|Dale|22|student||8|Edison|28|singer||9|Harry|21|student||10|LiMing|15|painist|----------------------------由结果可以看到UPDATE执行后成功将表中符合条件的6条记录的info字段值都改为student。3、删除数据从数据表中删除数据使用DELETE语句DELETE语句允许WHERE子句指定删除条件。DELETE语句基本语法格式如下DELETEFROMtable_name[WHEREcondition];table_name指定要执行删除操作的表​“​[WHERE ]​”为可选参数指定删除条件如果没有WHERE子句DELETE语句将删除表中的所有记录。在person表中删除id等于10的记录SQL语句如下执行删除操作前使用SELECT语句查看当前id10的记录select*frompersonwhereid10;--------------------------|id|name|age|info|--------------------------|10|LiMing|15|painist|--------------------------可以看到现在表中有id10的记录。下面使用DELETE语句删除该记录deletefrompersonwhereid10;语句执行完毕查看执行结果select*frompersonwhereid10;---------------------|id|name|age|info|------------------------------------------查询结果为空说明删除操作成功。在person表中使用DELETE语句同时删除多条记录。在前面UPDATE语句中将age字段值在19~22之间的记录的info字段值修改为student在这里删除这些记录。执行删除操作前使用SELECT语句查看当前的数据select*frompersonwhereagebetween19and22;--------------------------|id|name|age|info|--------------------------|1|Green|21|student||2|Suse|22|student||4|Willam|20|student||7|Dale|22|student||9|Harry|21|student|可以看到这些age字段值在19~22之间的记录存在表中。下面使用DELETE删除这些记录deletefrompersonwhereagebetween19and22;语句执行完毕查看执行结果select*frompersonwhereagebetween19and22;---------------------|id|name|age|info|------------------------------------------查询结果为空删除多条记录成功。删除person表中所有记录。执行删除操作前使用SELECT语句查看当前的数据select*fromperson;----------------------------|id|name|age|info|----------------------------|3|Mary|24|Musician||5|Laura|25|null||6|Evans|27|secretary||8|Edison|28|singer|----------------------------结果显示person表中还有4条记录执行DELETE语句删除这4条记录deletefromperson;语句执行完毕查看执行结果select*fromperson;---------------------|id|name|age|info|------------------------------------------查询结果为空说明删除表中所有记录成功现在person表中已经没有任何数据记录。如果想删除表中的所有记录还可以使用TRUNCATE TABLE语句。TRUNCATE将直接删除原来的表并重新创建一个表其语法结构为TRUNCATE TABLE table_name。TRUNCATE直接删除表而不是删除记录因此执行速度比DELETE快。4、为表增加计算列什么叫计算列呢简单来说就是某一列的值是通过别的列计算得来的。例如a列值为1、b列值为2c列不需要手动插入定义ab的结果为c的值那么c就是计算列是通过别的列计算得来的。增加计算列的语法格式如下col_name data_type[generated always]as(expression)[virtual|stored][unique[key][commentcomment][notnull|null|[[primary]key]在MySQL 8.0中CREAE TABLE和ALTERTABLE中都支持增加计算列。下面以CREAETABLE为例进行讲解。定义数据表tb1然后定义字段id、字段a、字段b和字段c其中字段c为计算列用于计算ab的值。首先创建测试表tb1语句如下createtabletb1(idint(9)notnullauto_increment,aint(9)defaultnull,bint(9)defaultnull,cint(9)generated alwaysas((ab))virtual,primarykey(id));插入演示数据语句如下insertintotb1(a,b)values(100,200);查询数据表tb1中的数据结果如下select*fromtb1;-------------------|id|a|b|c|-------------------|1|100|200|300|-------------------更新数据中的数据语句如下updatetb1seta500;再次查看数据表中的数据结果如下select*fromtb1;-------------------|id|a|b|c|-------------------|1|500|200|700|-------------------从结果可以看出字段c中的数据始终是字段a和字段b的和随着字段a和字段b中数据的变化自动重新计算ab的值。5、MySQL 8.0的新特性——DDL的原子化在MySQL 8.0版本中InnoDB表的DDL支持事务完整性即DDL操作要么成功要么回滚。DDL操作回滚日志写入到datadictionary数据字典表mysql.innodb_ddl_log该表是隐藏的表通过show tables无法看到中用于回滚操作。通过设置参数可将DDL操作日志打印输出到MySQL错误日志中。下面通过案例来对比不同的版本中DDL操作的区别。分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表结果如下createdatabasemy_test;usemy_test;createtablebk1(bookidintnotnull,booknamevarchar(255));showtables;-------------------|Tables_in_my_test|-------------------|bk1|-------------------在MySQL 5.7版本中测试步骤如下删除数据表bk1和数据表bk2结果如下mysqldroptablebk1,bk2;ERROR1051(42S02): Unknowntablemy_test.bk2再次查询数据库中的数据表名称结果如下mysqlshowtables;Emptyset(0.00sec)从结果可以看出虽然删除操作时报错了但是仍然删除了数据表bk1。在MySQL 8.0版本中测试步骤如下删除数据表bk1和数据表bk2结果如下mysqldroptablebk1,bk2;ERROR1051(42S02): Unknowntablemy_test.bk2再次查询数据库中的数据表名称结果如下showtables;-------------------|Tables_in_my_test|-------------------|bk1|-------------------从结果可以看出数据表bk1并没有被删除。