实验  3   MySQL数据库表的数据插入、修改、删除操作

一、实验目的

1．掌握MySQL数据库表的数据插入、修改、删除操作SQL语法格式

2．掌握数据表的数据的录入、增加和删除的方法

二、实验内容

1. 验证性实验：在学校教师管理数据库中的teacherInfo表中完成插入数据、更新数据和删除数据操作。

2.设计性试验：在某超市的食品管理的数据库的Food表中完成插入数据、更新数据和删除数据操作。

三、实验步骤与实验结果

（一）验证性实验

学校教师管理数据库中的teacherInfo表，其表的定义如下表所示，请完成如下操作。

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
num	教工号	INT(10)	是	否	是	是	否
name	姓名	VARCHAR(20)	否	否	是	否	否
sex	性别	VARCHAR(4)	否	否	是	否	否
birthday	出生日期	DATETIME	否	否	否	否	否
address	家庭住址	VARCHAR(50)	否	否	否	否	否

1.创建teacherInfo表

CREATE TABLE  teacherInfo(
num INT(10) NOT NULL  UNIQUE PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
); 

 2. 向teacherInfo表中插入记录。

INSERT INTO teacherInfo VALUES(1001,'张龙','男','1984-11-08','北京市昌平区');
INSERT INTO teacherInfo VALUES(1002,'李梅','女','1970-01-21','北京市海淀区');
INSERT INTO teacherInfo VALUES(1003,'王一丰','男','1976-10-30','北京市昌平区');
INSERT INTO teacherInfo VALUES(1004,'赵六','男','1980-06-05','北京市顺义区');

3.操作后查看表

SELECT * FROM teacherinfo;

4.更新教工号为1003的记录，将生日（birthday）改为“1982-11-08”。

UPDATE teacherInfo SET birthday='1982-11-08' WHERE num=1003;

5.将性别（sex）为“男”的记录的家庭住址（address）都变为“北京市朝阳区”。

UPDATE teacherInfo SET address='北京市朝阳区' WHERE sex='男';

6.删除教工号（num）为1002的记录删除。

DELETE FROM teacherInfo WHERE num=1002;

7.操作后查看表

SELECT * FROM teacherinfo;

（二）设计性实验

某超市的食品管理的数据库的Food表，Food表的定义如表所示，请完成插入数据、更新数据和删除数据。

Food表的定义

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
foodid	食品编号	INT(4)	是	否	是	是	是
Name	食品名称	VARCHAR(20)	否	否	是	否	否
Company	生产厂商	VARCHAR(30)	否	否	是	否	否
Price	价格（单位：元）	FLOAT	否	否	是	否	否
Product_time	生产年份	YEAR	否	否	否	否	否
Validity_time	保质期（单位：年）	INT(4)	否	否	否	否	否
address	厂址	VARCHAR(50)	否	否	否	否	否

1.创建食品管理数据库。

CREATE DATABASE foodCompanies;

2.创建Food表。

CREATE TABLE food(
foodid INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(20) NOT NULL,
Company VARCHAR(30) NOT NULL,
Price FLOAT NOT NULL,
Product_time YEAR,
Validity_time INT(4),
address VARCHAR(50)
);

3.采用3种方式，将表的记录插入到Food表中。

（1）不指定具体的字段，插入数据： 'QQ饼干','QQ饼干厂',2.5,'2018',3,'北京'。

INSERT INTO food VALUES(foodid,'QQ饼干','QQ饼干厂',2.5,'2018',3,'北京');

（2）依次指定food表的字段，插入数据： 'MN牛奶','MN牛奶厂',3.5,'2019',1,'河北'

INSERT INTO food
(`Name`,Company,Price,Product_time,Validity_time,address)
VALUES
('MN牛奶','MN牛奶厂',3.5,'2019',1,'河北');

（3）同时插入多条记录，插入数据：

'EE果冻','EE果冻厂',1.5,'2017',2,'北京',

'FF咖啡','FF咖啡厂',20,'2012',5,'天津',

'GG奶糖','GG奶糖',14,'2013',3,'广东';

INSERT INTO food
(`Name`,Company,Price,Product_time,Validity_time,address)
VALUES
('EE果冻','EE果冻厂',1.5,'2017',2,'北京'),
('FF咖啡','FF咖啡厂',20,'2012',5,'天津'),
('GG奶糖','GG奶糖厂',14,'2013',3,'广东');

（4）操作后查看表

SELECT * FROM food;

4.将“MN牛奶厂”的厂址(address)改为“内蒙古”，并且将价格改为3.2。

UPDATE food SET address='内蒙古',Price=3.2
WHERE
Company='MN牛奶厂';

5.将厂址在北京的公司的保质期(validity_time)都改为5年。

UPDATE food SET Validity_time=5
WHERE
address='北京';

6.删除过期食品的记录。若当前时间-生产年份(producetime)>保质期（validity_time），则视为过期食品。

DELETE FROM food
WHERE
Validity_time<(2022-Product_time);

7.删除厂址为“北京”的食品的记录。

DELETE FROM food
WHERE
address='北京';

四、观察与思考

1.对于删除的数据，如何实现”逻辑删除“（即数据库中的数据不删除，给用户的感觉是删除了）？

答：通常使用一个is_deleted字段标示行记录是不是被删除（或者使用一个status字段代表所谓的“删除”状态），在逻辑上是数据是被删除的，但数据本身是依然存在的，逻辑删除恢复的话只要修改is_deleted等类似的状态标示字段就可以了，但是表的数据量肯定会比物理删除增加了，并且查询时经常要考虑到is_deleted字段，对索引都会有影响。

2.DROP命令和DELETE命令的本质区别是什么？

答：DELETE命令是数据库操作语言（DML)，DROP命令是数据库定义语言（DDL）DELETE命令只删除内容、释放空间但不删除定义，而DROP命令即可以对行数据进行删除，也可以对整表数据进行删除。

DROP命令是删除内容和定义，并释放空间。执行DROP命令，将使此表的结构一起删除。

3.利用INSERT、UPDATE和DELETE命令可以同时对多个表进行操作吗？

答：不可以。

五、实验要求

按要求完成，详细记录操作步骤，书写实验报告。所有实验环节均由每位学 生独立完成，认真记录操作过程，严禁抄袭他人实验结果。

六、实验总结

通过本次实验实现了对表的操作，对于插入（insert）修改（updata）删除（delete）有了更深刻的认识，在插入语句中有多种方式插入数据，可指定列，可多条数据同时插入。在修改语句和删除语句中不光要声明需要操作的表，还需要添加where条件。在实验中，无论是插入还是修改，涉及到切换中英文输入法后，容易把一些符号也输入成中文，造成语法错误，需要注意。