水果商店系统数据库

一、实验目的与要求：

１、掌握MySQL中如何创建数据库和表的方法（要求在交互式命令提示符下完成，
截图）
２、熟练掌握MySQL的数据类型、主键实体完整性的设置
３、参照完整性的定义及应用（Navicat可视化操作，熟悉代码）
４、插入数据（Navicat完成）
５、数据库的备份操作

二、实验内容：

１、创建名为fruitshop的数据库，并创建数据表fruits、customers（客户）、orderitems（订单详单）、suppliers（供货商）和orders（订单总表），表结构和约束条件如下：
注：
1.各表中的“字段说明”属性仅用于辅助说明该属性的含义，不用定义在表的结构中
2.表中没有明确指定外键，试分析找出各表中的外键并定义其外键约束、级联删除、级联更新操作；

三、实验源码：

创建表源码：

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- 1.Table structure for customers
-- ----------------------------
DROP TABLE
IF
	EXISTS `customers`;
CREATE TABLE `customers` (
	`c_id` INT NOT NULL AUTO_INCREMENT COMMENT '客户编号',
	`c_name` CHAR ( 50 ) NOT NULL COMMENT '客户名',
	`c_address` CHAR ( 50 ) DEFAULT NULL COMMENT '客户地址',
	`c_city` CHAR ( 50 ) DEFAULT NULL COMMENT '地址',
	`c_zip` CHAR ( 10 ) DEFAULT NULL COMMENT '邮编',
	`c_contact` CHAR ( 50 ) DEFAULT NULL COMMENT '联系人',
	`c_email` CHAR ( 255 ) DEFAULT NULL COMMENT '电子邮箱',
	PRIMARY KEY ( c_id ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

-- ----------------------------
-- 2.Table structure for suppliers
-- ----------------------------
DROP TABLE
IF
	EXISTS `suppliers`;
CREATE TABLE `suppliers` (
	`s_id` INT NOT NULL AUTO_INCREMENT COMMENT '供应商编号',
	`s_name` CHAR ( 50 ) NOT NULL COMMENT '供应商名',
	`s_city` CHAR ( 50 ) DEFAULT NULL COMMENT '城市',
	`s_zip` CHAR ( 10 ) DEFAULT NULL COMMENT '邮编',
	`s_call` CHAR ( 50 ) NOT NULL COMMENT '电话',
	PRIMARY KEY ( s_id ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- ----------------------------
-- 3.Table structure for fruits
-- ----------------------------
DROP TABLE
IF
	EXISTS `fruits`;
CREATE TABLE fruits (
	f_id CHAR ( 10 ) NOT NULL COMMENT '水果编号',
	s_id INT NOT NULL COMMENT '供应商编号',
	f_name CHAR ( 255 ) NOT NULL COMMENT '水果名',
	f_price DECIMAL ( 8, 2 ) NOT NULL COMMENT '价格',
	PRIMARY KEY ( f_id ),
	CONSTRAINT fk_s_id FOREIGN KEY ( s_id ) REFERENCES suppliers ( s_id ) ON DELETE CASCADE ON UPDATE CASCADE 
);
-- ----------------------------
-- 4.Table structure for orders
-- ----------------------------
DROP TABLE
IF
	EXISTS `orders`;
CREATE TABLE `orders` (
	`o_num` INT NOT NULL AUTO_INCREMENT COMMENT '订单号',
	`o_date` datetime NOT NULL COMMENT '订购日期',
	`c_id` INT NOT NULL COMMENT '客户编号',
	PRIMARY KEY ( o_num ),
	CONSTRAINT fk_c_id FOREIGN KEY ( c_id ) REFERENCES customers ( c_id ) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- ----------------------------
-- 5.Table structure for orderitems
-- ----------------------------
DROP TABLE
IF
	EXISTS `orderitems`;
CREATE TABLE `orderitems` (
	`o_num` INT NOT NULL COMMENT '订单号',
	`o_item` INT NOT NULL COMMENT '订单项',
	`f_id` CHAR ( 10 ) NOT NULL COMMENT '水果编号',
	`quantity` INT NOT NULL COMMENT '数量',
	`item_price` DECIMAL ( 8, 2 ) NOT NULL COMMENT '单价',
	PRIMARY KEY ( o_num, o_item ),
	CONSTRAINT fk_o_num FOREIGN KEY ( o_num ) REFERENCES orders ( o_num ) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT fk_f_id FOREIGN KEY ( f_id ) REFERENCES fruits ( f_id ) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

数据输入源码：

--*****************数据输入************** 
INSERT INTO customers
VALUES
	( '10001', 'ReadHook', '200 Street', 'Tianjin', '300000', 'LiMing', 'LMing@163.com' );
INSERT INTO customers
VALUES
	( '10002', 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo', 'Jerry@hotmail.com' );
INSERT INTO customers
VALUES
	( '10003', 'Netbhood', '1 unny Place', 'Qingdao', '266000', 'LuoCong', '' );
INSERT INTO customers
VALUES
	( '10004', 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com' );

INSERT INTO suppliers
VALUES
	( '101', 'FastFruit Inc.', 'Tianjin', '300000', '48075' );
INSERT INTO suppliers
VALUES
	( '102', 'LT Supplies', 'Chongqing', '400000', '44333' );
INSERT INTO suppliers
VALUES
	( '103', 'ACME', 'Shanghai', '200000', '90046' );
INSERT INTO suppliers
VALUES
	( '104', 'FNK Inc.', 'Zhongshan', '528437', '11111' );
INSERT INTO suppliers
VALUES
	( '105', 'Good Set', 'Taiyuang', '030000', '22222' );
INSERT INTO suppliers
VALUES
	( '106', 'Just Eat Ours', 'Beijing', '010', '45678' );
INSERT INTO suppliers
VALUES
	( '107', 'DK Inc.', 'Zhengzhou', '450000', '33332' );
	
INSERT INTO fruits
VALUES
	( 'a1', '101', 'apple', '5.20' );
INSERT INTO fruits
VALUES
	( 'a2', '103', 'apricot', '2.20' );
INSERT INTO fruits
VALUES
	( 'b1', '101', 'blackberry', '10.20' );
INSERT INTO fruits
VALUES
	( 'b2', '104', 'berry', '7.60' );
INSERT INTO fruits
VALUES
	( 'b5', '107', 'pear', '3.60' );
INSERT INTO fruits
VALUES
	( 'bs1', '102', 'orange', '11.20' );
INSERT INTO fruits
VALUES
	( 'bs2', '105', 'melon', '8.20' );
INSERT INTO fruits
VALUES
	( 'c0', '101', 'plum', '3.20' );
INSERT INTO fruits
VALUES
	( 'l2', '104', 'lemon', '6.40' );
INSERT INTO fruits
VALUES
	( 'm1', '106', 'mango', '15.60' );
INSERT INTO fruits
VALUES
	( 'm2', '105', 'watermelon', '2.60' );
INSERT INTO fruits
VALUES
	( 'm3', '105', 'cherry', '11.60' );
INSERT INTO fruits
VALUES
	( 'o2', '103', 'coconut', '9.20' );
INSERT INTO fruits
VALUES
	( 't1', '102', 'banana', '10.30' );
INSERT INTO fruits
VALUES
	( 't2', '102', 'grape', '5.30' );
INSERT INTO fruits
VALUES
	( 't4', '107', 'peanut', '3.60' );

INSERT INTO orders
VALUES
	( '30001', '2018-09-01 00:00:00', '10001' );
INSERT INTO orders
VALUES
	( '30002', '2018-09-12 00:00:00', '10003' );
INSERT INTO orders
VALUES
	( '30003', '2018-09-30 00:00:00', '10004' );
INSERT INTO orders
VALUES
	( '30004', '2018-10-03 00:00:00', '10005' );
INSERT INTO orders
VALUES
	( '30005', '2018-10-08 00:00:00', '10005' );
	
INSERT INTO orderitems
VALUES
	( '30001', '1', 'a1', '10', '5.20' );
INSERT INTO orderitems
VALUES
	( '30001', '2', 'b2', '3', '7.60' );
INSERT INTO orderitems
VALUES
	( '30001', '3', 'bs1', '5', '11.20' );
INSERT INTO orderitems
VALUES
	( '30001', '4', 'bs2', '15', '9.20' );
INSERT INTO orderitems
VALUES
	( '30002', '1', 'b3', '2', '20.00' );
INSERT INTO orderitems
VALUES
	( '30003', '1', 'c0', '100', '10.00' );
INSERT INTO orderitems
VALUES
	( '30004', '1', 'o2', '50', '2.50' );
INSERT INTO orderitems
VALUES
	( '30005', '1', 'c0', '5', '10.00' );
INSERT INTO orderitems
VALUES
	( '30005', '2', 'b1', '10', '8.99' );
INSERT INTO orderitems
VALUES
	( '30005', '3', 'a2', '10', '2.20' );
INSERT INTO orderitems
VALUES
	( '30005', '4', 'm1', '5', '14.99' );

运行测试结果截图: