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

（一）测试任务

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

 

图1 评价子系统E-R图

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

表1 用户表t_user

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

表2 鲜花表t_flower

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

表3 评论表t_comment

字段名	数据类型	是否允许空	是否为主键(默认为否)	其它约束	字段说明
id	int	否	是	标识列，从1开始自动增长	编号
comment	varchar(200)	否			内容
comment_date	datetime	否			发布日期
user_id	int	否		外键	用户ID
flower_id	int	否		外键	鲜花ID

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

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

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

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

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

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

CREATE DATABASE IF NOT EXISTS floweronlinestore_dsw;
USE FlowerOnlineStore_dsw;
 
CREATE TABLE IF NOT EXISTS t_user_dsw(
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 (password_dsw REGEXP '^[A-Za-z0-9]+$') 
)ENGINE=InnoDB;
SELECT*FROM t_user_dsw;
 
CREATE TABLE IF NOT EXISTS t_flower_dsw(
id_dsw int PRIMARY key auto_increment,
price_dsw decimal(10, 1) NOT NULL check(price_dsw between 1 and 100),
name_dsw varchar(20) NOT NULL
)ENGINE=InnoDB;
SELECT*FROM t_flower_dsw;
 
CREATE TABLE IF NOT EXISTS t_comment_dsw(
id_dsw int PRIMARY key auto_increment,
comment_dsw varchar(200)NOT NULL,
comment_date_dsw datetime not null,
user_id_dsw int not null,
flower_id_dsw int not null,
CONSTRAINT fk_t_comment_dsw_t_user_dsw FOREIGN KEY(user_id_dsw)REFERENCES t_user_dsw(id_dsw)on DELETE CASCADE on UPDATE CASCADE,
CONSTRAINT fk_t_comment_dsw_t_flower_dsw FOREIGN KEY(flower_id_dsw)REFERENCES t_flower_dsw(id_dsw)on DELETE CASCADE on UPDATE CASCADE
)ENGINE=InnoDB;
SELECT*FROM t_comment_dsw;

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

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

 
insert into t_user_dsw values(null,'王旭', '18576432283', '111wx');
insert into t_user_dsw values(null,'张世杰', '18264972020', '111zsj');
insert into t_user_dsw values(null,'陈哲', '18767801220', '111cz');
insert into t_user_dsw values(null,'闻康', '15869736103', '111wk');
insert into t_user_dsw values(null,'孙威', '13089765674', '111sw');
insert into t_user_dsw values(null,'吴伟', '16810103241', '111ww');
insert into t_user_dsw values(null,'张三', '12323212345', '111zs');
SELECT*FROM t_user_dsw;
 
 
insert into t_user_dsw values(null,'王旭', '18576432283', '111wx');
insert into t_user_dsw values(null,'张世杰', '18264972020', '111zsj');
insert into t_user_dsw values(null,'陈哲', '18767801220', '111cz');
insert into t_user_dsw values(null,'闻康', '15869736103', '111wk');
insert into t_user_dsw values(null,'孙威', '13089765674', '111sw');
insert into t_user_dsw values(null,'吴伟', '16810103241', '111ww');
insert into t_user_dsw values(null,'张三', '12323212345', '111zs');
SELECT*FROM t_user_dsw;
 
insert into t_comment_dsw values(null,'好看','2022-09-1','2','3');
insert into t_comment_dsw values(null,'一般般','2022-09-2','1','6');
insert into t_comment_dsw values(null,'太喜欢了，下次还会买','2022-09-2','3','1');
insert into t_comment_dsw values(null,'花花很好看，贺卡也是','2022-9-4','4','5');
insert into t_comment_dsw values(null,'第二次买了，女朋友很开心','2022-9-6','5','2');
insert into t_comment_dsw values(null,'五星好评，店主很热心','2022-9-10','2','1');
insert into t_comment_dsw values(null,'满意','2022-9-10','7','2');
insert into t_comment_dsw values(null,'喜欢','2022-9-11','7','1');
SELECT*FROM t_comment_dsw;

（2）查询评论表中，包含‘好’的评论。

SELECT*FROM  t_comment_dsw a 
where a.comment_dsw like'%好%';

（3）查询用户姓名含‘张’的所有评论。

SELECT comment_dsw,usename_dsw,user_id_dsw
FROM t_comment_dsw INNER JOIN t_user_dsw
ON t_comment_dsw.user_id_dsw=t_user_dsw.id_dsw
WHERE t_user_dsw.usename_dsw like'%张%';

（4）查询哪个花被评论次数最多。

SELECT flower_id_dsw,count(*)as number
FROM t_comment_dsw
group by flower_id_dsw;
ORDER BY number DESC;

（5）删除用户‘张三’的所有关于‘玫瑰’的评论。

DELETE b 
FROM t_user_dsw as a,t_comment_dsw b
WHERE a.id_dsw=b.id_dsw and a.usename_dsw='张三';

任务4：数据库备份（5分）

（1）通过数据库备份命令，将数据库FlowerOnlineStore的数据库结构以及数据导出到考生成果文件夹下的backup文件夹，文件名为FlowerOnlineStore_backup.sql。