第一题答案
/*
第一步:建立水果的数据库
*/
CREATE DATABASE if not exists `fruit`;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/bb65406c96ba40a5b18bfab82b37951e.png)
/*
第二步:水果零售价格表的建表语句,创建主键
*/
CREATE TABLE if not exists `fruit`.tb_fruit_list(
`fruit_id` INT(11) COMMENT '水果序号' PRIMARY KEY ,
`fruit_name` VARCHAR(50) COMMENT '水果名称' ,
`fruit_price` DECIMAL(3,2) COMMENT '水果零售价'
);
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/c1b266b2e3048f46a111619d8010787d.png)
/*
第三步:查看水果价格表的字段
*/
USE `fruit`;
SHOW CREATE TABLE tb_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/4424ebe95087a85cb100c3ca8367f7de.png)
/*
第四步:查看水果价格表的表结构
*/
DESC `fruit`.tb_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/79f78a2e22fe140f38d85f2a06acd39a.png)
/*
第五步:填写水果类目及单价
*/
INSERT INTO `fruit`.tb_fruit_list VALUES
(1,'苹果',5.00),
(2,'橘子',6.00),
(3,'香蕉',7.00
);
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/4fc80f79949617bc085c476788b19155.png)
/*
第六步:查看水果单价表中数据
*/
SELECT * FROM tb_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/e747e06385723154edc403e7871b0a17.png)
/*
第七步:创建水果销售表,建立外键
*/
CREATE TABLE if not exists tb_fruit_saleslist(
`fruitId` INT(11) COMMENT '水果序号',
`fruitAmout` INT(11) COMMENT '水果数量',
`salesAmount` DECIMAL(10,2) COMMENT '水果销售总额',
CONSTRAINT fk_fruit_list FOREIGN KEY (`fruitId`) REFERENCES tb_fruit_list (`fruit_id`)
);
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/80b79026cd762d56c29c60e57b3ac0a4.png)
/*
第八步查看水果销售表的字段
*/
SHOW CREATE TABLE tb_fruit_saleslist;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/65bc2b3bf15350a5aec02a87ab56e6ba.png)
/*
第九步:查看水果价格表的表结构
*/
DESC tb_fruit_saleslist;
*/
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/f266eb6bae4a481d33b021917572c051.png)
/*
第十步:填写售卖的数量和金额
*/
INSERT INTO tb_fruit_saleslist VALUES
(1,10,500.00),
(2,10,600.00),
(3,10,700.00
);
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/f7e62b54c45669df169da580a3f0404f.png)
/*
第十一步:查看水果售卖单价表中数据
*/
SELECT * FROM tb_fruit_saleslist;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/7b87e4f57f3fa2d35cb4ef48c9f86c74.png)
第二题答案
/*
第一步:删除子表(tb_fruit_saleslist)外键
*/
ALTER TABLE tb_fruit_saleslist DROP FOREIGN KEY fk_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/e8c4ec719357423a2b79f138e16a7cd5.png)
/*
第二步:删除父表(tb_fruit_list)中的`fruit_name`字段,查看父表(tb_fruit_list)结构
*/
ALTER TABLE tb_fruit_list DROP COLUMN `fruit_name`;
DESC tb_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/d381ff5272f1bc88f85352f9e2c723ab.png)
/*
第三步:查看父表字段结构
*/
SHOW CREATE TABLE tb_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/c687a8082b6055e5eca5c34bb8b90d46.png)
/*
第四步:查看父表(tb_fruit_list)数据
*/
SELECT * FROM tb_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/a96eafd02ba3575c701584bea382dff9.png)
/*
第五步:查看子表(tb_fruit_saleslist)数据———售卖记录
*/
SELECT * FROM tb_fruit_saleslist;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/fb7a2301a34ccf746946c2cd4efccff4.png)
第二题修改后答案
/*
第一步:删除子表(tb_fruit_saleslist)外键
*/
ALTER TABLE tb_fruit_saleslist DROP FOREIGN KEY fk_fruit_list;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/e8c4ec719357423a2b79f138e16a7cd5.png)
/*
第二步:删除父表中苹果数据
*/
DELETE FROM tb_fruit_list WHERE `fruit_name`= '苹果';
![第三课次作业 - 图19](/uploads/projects/yiyuwanghongchen@lxf4cv/f3c13a27f1aea073fc9c5254a9502931.png)
/*
第三步:查看子表(tb_fruit_saleslist)数据———售卖记录
*/
SELECT * FROM tb_fruit_saleslist;
![image.png](/uploads/projects/yiyuwanghongchen@lxf4cv/fb7a2301a34ccf746946c2cd4efccff4.png)