数据库——数据库与表

智能2112杨阳

一、目的与要求：

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

熟练掌握MySQL的数据类型、主键实体完整性的设置
参照完整性的定义及应用
插入数据
数据库的备份操作

二、内容：

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

注：

1.各表中的“字段说明”属性仅用于辅助说明该属性的含义，不用定义在表的结构中

2.表中没有明确指定外键，试分析找出各表中的外键并定义其外键约束、级联删除、级联更新操作；

表 1 fruits表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
f_id	水果编号	char(10)	Y	？	Y	Y	N
s_id	供应商编号	INT	N	？	Y	N	N
f_name	水果名	char(255)	N	？	Y	N	N
f_price	价格	decimal(8,2)	N	？	Y	N	N

表 2 customers表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
c_id	客户编号	int	Y	？	Y	Y	Y
c_name	客户名	char(50)	N	？	Y	N	N
c_address	客户地址	char(50)	N	？	N	N	N
c_city	城市	char(50)	N	？	N	N	N
c_zip	邮编	char(10)	N	？	N	N	N
c_contact	联系人	char(50)	N	？	N	N	N
c_email	电子邮箱	char(255)	N	？	N	N	N

表 3 orderitems表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
o_num	订单号	int	Y	？	Y	N	N
o_item	订单项	int	Y	？	Y	N	N
f_id	水果编号	char(10)	N	？	Y	N	N
quantity	数量	int	N	？	Y	N	N
item_price	单价	decimal(8,2)	N	？	Y	N	N

注：orderitems表中o_num和o_item的组合构成主键。

表 4 suppliers表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
s_id	供应商编号	int	Y	？	Y	Y	Y
s_name	供应商名	char(50)	N	？	Y	N	N
s_city	城市	char(50)	N	？	N	N	N
s_zip	邮编	char(10)	N	？	N	N	N
s_call	电话	char(50)	N	？	Y	N	N

表 5 orders表结构

字段名	字段说明	数据类型	主键	外键	非空	唯一	自增
o_num	订单号	int	Y	？	Y	Y	Y
o_date	订购日期	datetime	N	？	Y	N	N
c_id	客户编号	int	N	？	Y	N	N

源码：

1、create table fruits(

-> f_id char(10) primary key not null unique,

-> s_id int not null,

-> f_name char(255) not null,

-> f_price decimal(8,2) not null);

2、create table customers(

-> c_id int auto_increment primary key not null unique,

-> c_name char(50),

-> c_address char(50),

-> c_city char(50),

-> c_zip char(50),

-> c_contact char(50),

-> c_email char(50));

3、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、create table suppliers(

-> s_id int auto_increment primary key not null unique,

-> s_name char(50) not null,

-> s_city char(50),

-> s_zip char(50),

-> s_call char(50) not null);

5、create table orders(

-> o_num int auto_increment primary key not null unique,

-> o_date datetime not null,

-> c_id int not null);

外键：

1、alter table fruits add constraint fk_fruits_suppliers foreign key(s_id)

references suppliers(s_id);

2、alter table orders add constraint fk_orders_customers foreign key(c_id)

references customers(c_id);

3、alter table orderitems add constraint fk_orderitems_fruits foreign key(f_id)

references fruits(f_id)；

4、alter table orderitems add constraint fk_orderitems_orders foreign key(o_num)

references orders(o_num);

运行测试结果截图:

1、fruits

2、customers

3、orderitems

4、suppliers

5、orders

外键：

2、向数据表fruits、customers、orderitems和suppliers、orders中插入给定的如下数据

fruits表数据

customers表数据

orderitems表数据

suppliers表数据

orders表数据

源码：

1、 fruits

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 ('c0',101,'plum',3.2);

INSERT INTO fruits VALUES ('m1',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,'coconut',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 (10001,'RedHook','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,'Nethood','1 Sunny Place','Qingdao','266000','LuoCong',NULL);

INSERT INTO customers VALUES (10004,'JoTo','829 Riverside Driver','Haikou','570000','YangShan','sam@hotmail.com');

3、orderitems

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 (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','Taiyuan','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 (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);

3、向数据表fruits、customers、orderitems和suppliers、orders中插入给定的如下数据,要求如下：

向数据表customers和suppliers中分别插入一条记录，新记录customers的C_ID属性值统一为10000，在suppliers的S_ID属性值统一为100，S_NAME数据为JMU，其余属性值为本人的真实信息；[l1] 向orders表中插入编号为“50001”和“50002”，客户编号为“10000”（本人）的两条记录；向数据表fruits、orderitems表中插入分别插入五条与本人相关（即在对应表中的C_ID属性为10000，S_ID属性为100）的新记录[l2] ；

源码：

customers:

INSERT INTO customers VALUES (10000,'yy','5.4.519','Xianmen','555555','Yangyang','yy@163.com');

suppliers:

INSERT INTO suppliers VALUES (100,'JMU','Xiamen','555555','12345');

orders:

INSERT INTO orders VALUES (50001,'2023-03-15 00:00:00',10000);

INSERT INTO orders VALUES (50002,'2023-03-17 00:00:00',10000);

fruits:

INSERT INTO fruits VALUES ('y1',100,'avocate',6.7);

INSERT INTO fruits VALUES ('y2',100,'peach',4.5);

INSERT INTO fruits VALUES ('z1',100,'pineapple',5.7);