本文已参与「新人创作礼」活动，一起开启掘金创作之路。

数据库与表

一、目的与要求：

１、掌握MySQL中如何创建数据库和表的方法

２、掌握navicat中创建数据库、表，导入及导出表结构及数据。

3、熟练掌握MySQL的数据类型、主键、实体完整性的设置。

二、操作内容：

１、创建名为fruitshop的数据库，并创建数据表fruits、customers（客户）、orderitems（订单详单）、suppliers（供货商）和orders（订单总表），表结构和约束条件如下：

源码1：

按题目一段一段复制

-- 表 1   fruits表结构

create table fruits(
	f_id char(10) primary key not null,
	s_id int not null,
	f_name char(255) not null,
	f_price decimal(8,2) not null
)

-- 表 2   customers表结构

create table customers(
	c_id int primary key auto_increment not null,
	c_name char(50) not null,
	c_address char(50),
	c_city char(50),
	c_zip char(10),
	c_contact char(50),
	c_email char(255)
)

-- 表 3   orderitems表结构

create table orderitems(
	o_num int not null,
	o_item int not null,
	f_id char(10) not null,
	quantity int not null,
	item_price decimal(8,2) not null,
	primary key(o_num,o_item)
)

-- 表 4   suppliers表结构

create table suppliers(
	s_id int primary key auto_increment not null,
	s_name char(50) not null,
	s_city char(50),
	s_zip char(10),
  s_call char(50) not null
)

-- 表 5   orders表结构

create table orders(
	o_num int primary key auto_increment not null,
	o_date datetime not null,
	c_id int not NULL
)

运行测试结果截图: 2、向数据表fruits、customers、orderitems和suppliers、orders中插入给定的如下数据

fruits表数据

customers表数据

orderitems表数据

suppliers表数据

orders表数据

3.向数据表customers和suppliers中分别插入两条记录，新记录customers的C_ID属性值统一为10000，在suppliers的S_ID属性值统一为100，S_NAME数据为JMU，其余属性值为本人的真实信息；向数据表fruits、orderitems、orders表中插入分别插入五条与本人相关的新记录

源码2：

按题目一段一段复制

-- 表 1   fruits表数据插入
insert into fruits values('a0',00,'sekai-ichi',52);
insert into fruits values('a1',101,'apple',5.2);
insert into fruits values('a2',103,'apricot',2.2);
insert into fruits values('b1',101,'blackberry',10.2);
insert into fruits values('b2',104,'berry',7.6);
insert into fruits values('b3',104,'lemon',6.4);
insert into fruits values('b5',107,'pear',3.6);
insert into fruits values('bs1',102,'orange',11.2);
insert into fruits values('bs2',105,'melon',8.2);
insert into fruits values('c0',101,'plum',3.2);
insert into fruits values('ml',106,'mango',15.6);
insert into fruits values('m2',105,'watermelon',2.6);
insert into fruits values('m3',105,'cherry',11.6);
insert into fruits values('o2',103,'cocount',9.2);
insert into fruits values('t1',102,'banana',10.3);
insert into fruits values('t2',102,'grape',5.3);
insert into fruits values('t4',107,'peanut',3.6);
-- 表 2   customers表数据插入
insert into customers values(10000,'LinChuanTao','YanPingQv','NanPing',353000,'LinChuanTao','1804211700@qq.com');
insert into customers values(10001,'RedHook','200 Street','Tianjin',300000,'LiMing','LiMing@163.com');
insert into customers values(10002,'Stars','333 Fromgae Lane','Dalian',116000,'Zhangbo','Jerry@hotmail.com');
insert into customers values(10003,'Netbhood','1 Sunny Place','Qingdao',266000,'LuoCong',null);
insert into customers values(10004,'JOTO','829 Riverside Drive','Haikou',570000,'YangShan','sam@hotmail.com');
-- 表 3   orderitems表数据插入
insert into orderitems values(30000,0,'a0',99999999,0.09);
insert into orderitems values(30001,1,'a1',10,5.2);
insert into orderitems values(30001,2,'b2',3,7.6);
insert into orderitems values(30001,3,'bs1',5,11.2);
insert into orderitems values(30001,4,'bs2',15,9.2);
insert into orderitems values(30002,1,'b3',2,20);
insert into orderitems values(30003,1,'c0',100,10);
insert into orderitems values(30004,1,'o2',50,2.5);
insert into orderitems values(30005,1,'c0',5,10);
insert into orderitems values(30005,2,'b1',10,8.99);
insert into orderitems values(30005,3,'a2',10,2.2);
insert into orderitems values(30005,4,'m1',5,14.99);
-- 表 4   suppliers表数据插入
insert into suppliers values(100,'JMU','XiaMen','361021','6180039');
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');
-- 表 5   orders表数据插入
insert into orders values(30000,'2020-04-15 09:59:59',10000);
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',10002);
insert into orders values(30005,'2018_10-08 00:00:00',10001);

运行测试结果截图: