（6）插入、更新与删除数据

(1)创建food表
CREATE TABLE food(id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20) NOT NULL,
 company VARCHAR(30) NOT NULL,
 price FLOAT,
 produce_time YEAR,
 validity_time INT(4),
 address VARCHAR(50)
 );
(2)插入数据
INSERT INTO food VALUES(1,'AA饼干','AA饼干厂',2.5,'2008',3,'北京');  


INSERT INTO food (id,NAME,company,price,produce_time,validity_time,address)
VALUES(2,'CC牛奶','CC牛奶厂',3.5,'2009',1,'河北');

INSERT INTO food VALUES
(NULL,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
(NULL,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
(NULL,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');


查看数据
SELECT * FROM food;


(3)将“CC牛奶厂”的地址改为“内蒙古”，并且价格改为3.2


查看“CC牛奶厂”的情况
SELECT * FROM food WHERE NAME='CC牛奶';


UPDATE food
SET address='内蒙古',price=3.2
WHERE NAME='CC牛奶';


再次查看“CC牛奶厂”的情况
SELECT * FROM food WHERE NAME='CC牛奶';


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


SELECT * FROM food WHERE address='北京';


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


(5)删除过期食品的记录。当前时间为2009年。用2009减去生产年份，若这个值大于保质期，则说明食品已经过期


SELECT * FROM food WHERE 2009-produce_time>validity_time;


DELETE FROM food 
WHERE 2009-produce_time>validity_time;

(6)删除厂址为北京的食品的记录


SELECT * FROM food WHERE address='北京';


DELETE FROM food
WHERE address='北京';