单元4数据操纵

单元四数据操纵项目知识要点与目标项目知识要点知识能力目标学时4.1任务1插入表数据4.2任务2修改表数据4.3任务3删除表数据4.4综合实例PetStore数据录入4.4.1PetStore数据表及其内容4.4.2PetStore数据插入命令【情境】：Petstore数据管理【职业岗位】：操作员、程序员【典型工作任务】：数据表中数据的添加、修改、删除操作【技能要求】：1、创建与管理数据表2、数据的添加、修改、删除操作【知识要求】：1、数据类型2、数据表操作的SQL语句3、学会数据库表记录的操作4项目实训四“员工管理系统”数据表操作4任务一插入表数据

一旦创建了数据库和表，下一步就是向表里插入数据。通过INSERT或REPLACE语句可以向表中插入一行或多行数据。语法格式：

INSERT[IGNORE][INTO]表名[(列名,...)]VALUES({表达式|DEFAULT},..),(...),...|SET列名={表达式|DEFAULT},...说明：如果要给全部列插入数据，列名可以省略。如果只给表的部分列插入数据，需要指定这些列。对于没有指出的列，它们的值根据列默认值或有关属性来确定，MySQL处理的原则是：（1）具有IDENTITY属性的列，系统生成序号值来唯一标志列。（2）具有默认值的列，其值为默认值。（3）没有默认值的列，若允许为空值，则其值为空值；若不允许为空值，则出错。（4）类型为timestamp的列，系统自动赋值。●VALUES子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若tb1_name后不给出列名，则在VALUES子句中要给出每一列（除IDENTITY和timestamp类型的列）的值，如果列值为空，则值必须置为NULL，否则会出错。VALUES子句中的值：（1）表达式：可以是一个常量、变量或一个表达式，也可以是空值NULL，其值的数据类型要与列的数据类型一致。例如，列的数据类型为int，插入的数据是‘aaa’就会出错。当数据为字符型时要用单引号括起。（2）DEFAULT：指定为该列的默认值。前提是该列原先已经指定了默认值。如果列清单和VALUES清单都为空，则INSERT会创建一行，每个列都设置成默认值。插入语句最常用格式：

INSERTINTOtbl_name[(col_name,...)]VALUES(expr,…)INSERT命令

【例4.1】向Bookstore数据库中的表Book（表中列包括图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片）中插入如下的一行数据：

（'ISBN7-5006-6625-X/T','计算机','Dreamwearer8网站制作‘,'鲍嘉','高等教育出版社','2010-08-16',33.25,50,0.8,NULL）USEBookstore;INSERTINTObook

VALUES('ISBN7-5006-6625-X/T','计算机',

'Dreamwearer8网站制作‘,'鲍嘉','高等教育出版社',

'2010-08-16',33.25,50,0.8,NULL

);INSERT命令举例

【例4.2】若表Book中图书类别的默认值为“计算机”，封面图片默认值为NULL，插入上例那行数据。INSERTINTOBook(图书编号,书名,作者,出版社,出版时间,单价,数量,折扣)VALUES('ISBN7-5006-6625-X/T','Dreamwearer8网站制作','鲍嘉','高等教育出版社','2010-08-16',33.25,50,0.8);与下列命令效果相同：INSERTINTOBookSET图书编号='ISBN7-5006-6625-X/T',书名='Dreamwearer8网站制作',图书类别=DEFAULT,作者='鲍嘉',出版社='高等教育出版社',出版时间='2010-08-16',单价=33.25,数量=50,折扣=0.8;INSERT命令举例

如果例4.1正确执行，记录已经插入了，再执行例4.2的SQL代码，系统提示1062错误，这是因为两条记录的图书编号相同，而图书编号是Book表的主键，要求唯一。当插入第二条相同编号的记录时，系统提示错误，意为表中已有图书编号为'ISBN7-5006-6625-X/T'的记录，第二条记录不能插入。可以使用REPLACE语句，用第二条记录替换第一条记录：REPLACEINTOBookVALUES('ISBN7-5006-6625-X/T','计算机','PHP网站制作',

'林小红','高等教育出版社','2011-10-16',23.5,30,0.8,

NULL);INSERT命令举例

【例4.3】向Book表中插入一行数据：

（'ISBN7-5006-6625-T','计算机','计算机基础','林华忠','高等教育出版社','2011-10-16',45.5,45,0.8,picture.jpg）其中，封面图片的照片存储路径为D:\IMAGE\picture.jpg。方法一：以路径的形式来存储INSERTINTOBookVALUES('ISBN7-5006-6625-T','计算机','计算机基础','林华忠','高等教育出版社','2011-10-16',45.5,45,0.8,'D:/IMAGE/picture.jpg');方法二、使用LOAD_FILE()函数直接存储图片INSERTINTOBookVALUES('ISBN7-5006-6625-T','计算机','计算机基础','林华忠','高等教育出版社','2011-10-16',45.5,45,0.8,LOAD_FILE('D:/IMAGE/picture.jpg'));INSERT命令举例

在一个单独的INSERT语句中使用多个VALUES（）子句一次插入多条记录。【例4.4】向members表中插入两行数

4301031962010101388,李华,1234562013-8-23

4201031962010101399,张明,1234562012-9-23INSERTINTOmembersVALUES('4301031962010101388','李华',

'123456',,'2013-8-23'),('4201031962010101399','张明',

123456',,'2012-9-23');从上面的SQL代码可以看出，当一次插入多条记录时，每条记录的数据要用（）括起来，记录与记录之间用逗号分开。INSERT命令举例

课堂练习1、公司新成立了一个销售部，部门代码为6，请注明“筹建”，部门负责人暂时空缺。2、销售部新进两员工，信息如下表，另外张松还提供了照片，存放在D:\IMAGE\600001.jpg请将以上信息添加到yggl数据库。600001张松本科1988-01-30男2解放路23号83234567600002付晓大专1979-12-01女10前景路45号83455689任务二修改表数据

要修改表中的一行数据，可以使用UPDATE语句，UPDATE可以用来修改一个表，也可以修改多个表。修改单个表，语法格式：UPDATE[IGNORE]表名

SET列名1=表达式1[,列名2=表达式2...][WHERE条件]说明： ●SET子句：根据WHERE子句中指定的条件对符合条件的数据行进行修改。若语句中不设定WHERE子句，则更新所有行。●可以同时修改所在数据行的多个列值，中间用逗号隔开。修改表数据命令

【例4.5】将Bookstore数据库中Book表的所有书籍数量都增加10。将Members表中姓名为“张三”的员工的联系电话改为，密码改为“111111”。UPDATEBookSET数量=数量+10;UPDATEMembersSET联系电话=,密码='111111'

WHERE姓名='张三';任务二修改表数据

多表修改语法格式：UPDATE[IGNORE]

表名列表

SET

列名1=表达式1[,列名2=表达式2...][WHERE

条件]语法说明：表名列表：包含了多个表的联合，各表之间用逗号隔开。多表修改语法的其他部分与单表修改语法相同。【例4.6】表tb1和表tb2中都有两个字段idINT(4)，pwdCHAR(4)，其中id为主键。当表tb1中id值与tb2中id值相同时，将表tb1中对应的pwd值修改为“AAA”，将表tb2中对应的pwd值改为“BBB”。UPDATEtb1,tb2SETtb1.pwd='AAA',tb2.pwd='BBB'WHEREtb1.id=tb2.id;课堂练习

经过一段时间的工作，王向容调到销售部任负责人，工资收入相应的增加一千元。请将以上信息添加到yggl数据库。课堂练习参考答案经过一段时间的工作，王向容调到销售部任负责人，工资收入相应的增加一千元。请将以上信息添加到yggl数据库。UPDATEdepartments

SETmanager

=‘王向容'

WHEREdepartmentID=‘6‘;UPDATEsalarySETInCome=InCome+1000WHEREemployeeID='020010'UPDATEemployeesSETdepartmentID='6'WHEREemployeeID='020010'或：

UPDATEsalary,employeesSETsalary.InCome=InCome+1000,employees.departmentID='6'WHEREemployees.employeeID=salary.employeeIDandemployees.employeeID='020010'任务三删除表数据

1.使用DELETE语句删除数据从单个表中删除，语法格式：DELETE[IGNORE]FROM表名

[WHERE条件]说明：●如果省略WHERE子句则删除该表的所有行。【例4.7】将Bookstore数据库的Members表中姓名为“张三”的员工的记录删除。USEBookstore;DELETEFROMMembersWHERE姓名='张三';【例4.8】将Book表中数量小于5的所有行删除。USEBookstore;DELETEFROMBookWHERE数量<5;删除表数据命令从多个表中删除行，语法格式：DELETE[IGNORE]表名1[.*][,表名2[.*]...]FROM表名列表

[WHERE条件]或：DELETE[IGNORE]FROM表名1[.*][,表名2[.*]...] USING表名列表

[WHERE条件]【例4.9】假设有3个表t1、t2、t3，它们都含有id列。请删除t1中id值等于t2的id值的所有行和t2中id值等于t3的id值的所有行。DELETEt1,t2FROMt1,t2,t3WHEREt1.id=t2.idANDt2.id=t3.id;或：DELETEFROMt1,t2USINGt1,t2,t3WHEREt1.id=t2.idANDt2.id=t3.id;TRUNCATE命令2.使用TRUNCATETABLE语句删除表数据使用TRUNCATETABLE语句将删除指定表中的所有数据，因此也称其为清除表数据语句。语法格式：TRUNCATETABLEtable-name说明