试题2-13：数据库设计与操作

（一）测试任务

任务描述：在线鲜花商城系统，有一个订单子系统。该子系统的数据库设计E-R图如图1所示。

 

 

图1 订单子系统E-R图

 

该子系统涉及的表中各字段定义见表1到表4。

表1 用户表t_user

字段名	数据类型	是否允许空	是否为主键(默认为否)	其它约束	字段说明
id	int	否	是	标识列，从1开始自动增长	编号
username	varchar(18)	否			用户名
phone	varchar(11)	否		要求11位数字	电话
password	varchar(25)	否		长度至少为8位	密码

表2 订单表t_order

字段名	数据类型	是否允许空	是否为主键(默认为否)	其它约束	字段说明
id	int	否	是	标识列，从1开始自动增长	编号
status	tinyint	否		级别限制在1-15	状态
amount	decimal(10, 1)	否			金额
pay_method	enum	否		值为“支付宝”、“微信”	支付方式
user_id	int	否		外键	用户ID

表3 鲜花表t_flower

字段名	数据类型	是否允许空	是否为主键(默认为否)	其它约束	字段说明
id	int	否	是	标识列，从1开始自动增长	编号
price	decimal(10, 1)	否			单价
name	varchar(50)	否			名称

 

 

 

 

 

表4 订单详情表t_order_detail

字段名	数据类型	是否允许空	是否为主键(默认为否)	其它约束	字段说明
id	int	否	是	标识列，从1开始自动增长	编号
flower_id	int	否		外键	鲜花ID
order_id	int	否		外键	订单ID
count	int	否			数量

 

 

请按以上设计完成数据库创建、数据表创建和数据操作任务：

任务1：创建数据库（5分）

创建数据库FlowerOnlineStore。如果原来有该数据库，请先删除。

任务2：创建数据表及约束（30分）

（1）按表1到表4所列出字段， 创建数据表t_ user、t_ order、t_ flower、t_order_detail，将四个表的主键列设为标识列，自动从1开始增长。（10分）

（2）根据表1到表4中的约束要求，建立好主建、外键、默认、检查、非空等约束。（20分）

CREATE TABLE IF NOT EXISTS t_user_dsw1(
id_dsw int PRIMARY key auto_increment,
usename_dsw varchar(18) NOT NULL,
phone_dsw varchar(11) not null check(phone_dsw REGEXP '\\d{11}'),
password_dsw varchar(25) not null check (LENGTH(password_dsw)>=8)
)ENGINE=InnoDB;
SELECT*FROM t_user_dsw1;
 
CREATE TABLE IF NOT EXISTS t_order_dsw(
id_dsw int PRIMARY key auto_increment,
status_dsw tinyint not null check(status_dsw between 1 and 15),
amount_dsw decimal(10, 1)not null,
pay_method_dsw enum('支付宝','微信')NOT NULL,
user_id_dsw int not null,
CONSTRAINT fk_t_order_dsw_t_user_dsw1 FOREIGN KEY(user_id_dsw)REFERENCES t_user_dsw1(id_dsw)
)ENGINE=InnoDB;
SELECT*FROM t_order_dsw;
 
CREATE TABLE IF NOT EXISTS t_flower_dsw1(
id_dsw int PRIMARY key auto_increment,
price_dsw decimal(10, 1) NOT NULL ,
name_dsw varchar(20) NOT NULL
)ENGINE=InnoDB;
SELECT*FROM t_flower_dsw1;
 
CREATE TABLE IF NOT EXISTS t_order_detail(
id_dsw int PRIMARY key auto_increment,
flower_id_dsw int not null,
order_id_dsw int not null,
count_dsw int not null,
CONSTRAINT fk_t_order_detail_t_flower_dsw1 FOREIGN KEY(flower_id_dsw)REFERENCES t_flower_dsw1(id_dsw),
CONSTRAINT fk_t_order_detail_t_order_dsw FOREIGN KEY(order_id_dsw)REFERENCES t_order_dsw(id_dsw)
)ENGINE=InnoDB;
SELECT*FROM t_order_detail;

任务3：数据库操作（50分）

（1）在四个表中分别插入5条测试数据（样本数据包含下面题目中使用的数据）。

insert into t_user_dsw1 values(null,'王旭', '18576432183', '11111111');
insert into t_user_dsw1 values(null,'张世杰', '18264902020', '22222222');
insert into t_user_dsw1 values(null,'陈哲', '18067801220', '33333333');
insert into t_user_dsw1 values(null,'闻康', '15889736103', '44444444');
insert into t_user_dsw1 values(null,'孙威', '13089465674', '55555555');
insert into t_user_dsw1 values(null,'吴伟', '16310103241', '66666666');
insert into t_user_dsw1 values(null,'胡鑫','13456766530','77777777');
insert into t_user_dsw1 values(null,'张三','18767544543','88888888');
SELECT*FROM t_user_dsw1;
 
//状态；1已付款 2已发货 3已签收 4未付款 5退货中 6已退货 7取消交易
insert into t_order_dsw values(null,'1','59','支付宝','2');
insert into t_order_dsw values(null,'1','99','微信','4');
insert into t_order_dsw values(null,'3','66','支付宝','1');
insert into t_order_dsw values(null,'4','33','支付宝','3');
insert into t_order_dsw values(null,'3','59','支付宝','6');
insert into t_order_dsw values(null,'6','59','微信','7');
insert into t_order_dsw values(null,'4','33','支付宝','2');
insert into t_order_dsw values(null,'7','99','微信','4');
insert into t_order_dsw values(null,'5','20','微信','5');
insert into t_order_dsw values(null,'3','79','微信','3');
SELECT*FROM t_order_dsw;
 
insert into t_flower_dsw1 values(null,'59','百合花');
insert into t_flower_dsw1 values(null,'99','玫瑰花');
insert into t_flower_dsw1 values(null,'33','满天星');
insert into t_flower_dsw1 values(null,'66','康乃馨');
insert into t_flower_dsw1 values(null,'20','向日葵');
insert into t_flower_dsw1 values(null,'79','郁金香');
insert into t_flower_dsw1 values(null,'199','永生花');
SELECT*FROM t_flower_dsw1;
 
 
insert into t_order_detail values(null,'1','1','1');
insert into t_order_detail values(null,'1','5','1');
insert into t_order_detail values(null,'1','6','1');
insert into t_order_detail values(null,'2','2','1');
insert into t_order_detail values(null,'2','8','1');
insert into t_order_detail values(null,'3','4','1');
insert into t_order_detail values(null,'3','7','1');
insert into t_order_detail values(null,'4','3','1');
insert into t_order_detail values(null,'5','9','1');
insert into t_order_detail values(null,'6','10','1');
SELECT*FROM t_order_detail;

（2）查询订单表中，金额最多的订单。

SELECT amount_dsw,count(*)as Number,sum(amount_dsw)as total
FROM t_order_dsw
GROUP BY amount_dsw
ORDER BY total DESC;

（3）查询用户姓名含‘张’的所有订单金额总和。

SELECT sum(amount_dsw)
FROM t_order_dsw INNER JOIN t_user_dsw1
ON t_order_dsw.user_id_dsw=t_user_dsw1.id_dsw
WHERE t_user_dsw1.usename_dsw like'%张%';

（4）查询哪个花被订的次数最多。

SELECT sum(amount_dsw)
FROM t_order_dsw INNER JOIN t_user_dsw1
ON t_order_dsw.user_id_dsw=t_user_dsw1.id_dsw
WHERE t_user_dsw1.usename_dsw like'%张%';

（5）删除用户‘张三’的所有订单信息。

SELECT sum(amount_dsw)
FROM t_order_dsw INNER JOIN t_user_dsw1
ON t_order_dsw.user_id_dsw=t_user_dsw1.id_dsw
WHERE t_user_dsw1.usename_dsw like'%张%';