数据库
库名:meitao
表
area(区域表)
areaID | int not null | 区域编号(主键) |
---|---|---|
areaName | varchar(20) null | 区域名称 |
p_areaID | int null | 上级区域编号 |
shop(商店表)
shopID | int not null | 商店编号(主键) |
---|---|---|
shopName | varchar(50) not null | 商店名称 |
shopAddress | varchar(50) null | 商店地址 |
contact | varchar(50) null | 联系电话 |
customer(客户表)
customerID | int not null | 客户编号(主键) |
---|---|---|
login | varchar(20) not null | 登录名称 |
pwd | varchar(20) not null | 登录密码 |
customerName | varchar(20) not null | 客户姓名 |
tel | varchar(20) null | 客户电话 |
address | varchar(20) null | 客户地址 |
gender | varchar(20) null | 客户性别 |
birthday | date null | 客户生日 |
category(类别表)
categoryID | int not null | 类别编号(主键) |
---|---|---|
categoryName | varchar(20) not null | 类别名称 |
p_categoryID | int null | 上级类别编号 |
orders(订单表)
ordersID | int not null | 订单编号(主键) |
---|---|---|
customerID | int null | 客户编号(外键,参照客户表主键) |
ordersDate | date null | 订单日期 |
deliveryDate | date null | 交付日期 |
amount | decimal(8,2) null | 订单金额 |
product(产品表)
productID | int not null | 产品编号(主键) |
---|---|---|
areaID | int null | 区域编号(外键,参照区域表主键) |
categoryID | int null | 类别编号(外键,参照类别表主键) |
shopID | int null | 商店编号(外键,参照商店表主键) |
title | varchar(50) not null | 产品名称 |
productDesc | varchar(500) null | 产品描述 |
originalPrice | decimal(8,2) null | 原始价格 |
currentPrice | decimal(8,2) null | 当前价格 |
picture | varchar(50) null | 产品图片 |
isCommend | varchar(10) null | 是否推荐 |
salesCount | int null | 销售数量 |
ordersDetail(订单详情表)
ordersID | int not null | 订单编号(联合主键,外键,参照订单表主键) |
---|---|---|
productID | int not null | 产品编号(联合主键,外键,参照产品表主键) |
quantity | int null | 订单数量 |
area(区域表)
shop(商品表)
customer(客户表)
category(类别表)
orders(订单表)
product(产品表)
ordersDetail(订单详情表)
area(区域表)
shop(商店表)
customer(客户表)
category(类别表)
orders(订单表)
product(产品表)
ordersDetail(订单详情表)
-- area(区域表)
insert into area values(1,'江岸区',null);
insert into area values(2,'江汉区',null);
insert into area values(3,'武昌区',null);
insert into area values(4,'江汉路',1);
insert into area values(5,'永清街',1);
insert into area values(6,'武广',2);
insert into area values(7,'取水楼',2);
insert into area values(8,'中南路',3);
insert into area values(9,'亚贸',3);
-- shop(商店表)
insert into shop values(1,'陶慧化妆品','上海路23号','15923670909');
insert into shop values(2,'领先一步干果店','江汉路万达广场B座','13927098123');
insert into shop values(3,'阳春菌品店','江汉一路19号','18723456123');
insert into shop values(4,'谷一韩式自助餐厅武广店','解放大道武汉广场','15927089156');
insert into shop values(5,'烫锅鲜万松园店','万松园路78-2号','13978561256');
insert into shop values(6,'傣妹中山大道店','中山大道平安大厦','13689126532');
insert into shop values(7,'公馆KTV前进四路店','前进四路46路','18912564321');
insert into shop values(8,'米乐星世界江汉路店','江汉路125号','13565569876');
insert into shop values(9,'欢乐迪KTV','中山公园对面','13612985643');
-- customer(客户表)
insert into customer values(1,'hqq','123','郝琼琼','13912345678','武汉市江汉路23号','女','1986/09/10');
insert into customer values(2,'lyb','123','雷亚波','15812782334','武汉市洞庭街123号','男','1988/03/21');
insert into customer values(3,'lhj','123','李慧娟','13812567812','武汉市书城路号30号','女','1991/2/20');
insert into customer values(4,'lym','123','刘亚蒙','15834671823','武汉市鹦鹉大街号132号','男','1992/07/09');
insert into customer values(5,'wgl','123','魏国兰','13678127812','武汉市关山大道456号','女','1984/10/19');
-- category(类别表)
insert into category values(1,'美食',null);
insert into category values(2,'酒店',null);
insert into category values(3,'电影',null);
insert into category values(4,'购物',null);
insert into category values(5,'休闲娱乐',null);
insert into category values(6,'生活服务',null);
insert into category values(7,'火锅', 1);
insert into category values(8,'自助餐',1);
insert into category values(9,'经济型酒店',2);
insert into category values(10,'豪华酒店',2);
insert into category values(11,'服装',4);
insert into category values(12,'鞋类',4);
insert into category values(13,'食品',4);
insert into category values(14,'化妆',4);
insert into category values(15,'KTV',5);
insert into category values(16,'健身',5);
insert into category values(17,'摄影',6);
insert into category values(18,'美发',6);
-- orders(订单表)
insert into orders values(1,4,'2014/01/09','2014/02/12',null);
insert into orders values(2,4,'2014/03/21','2014/03/24',null);
insert into orders values(3,2,'2014/03/15','2014/03/16',null);
insert into orders values(4,2,'2014/03/28','2014/03/30',null);
insert into orders values(5,4,'2014/04/12','2014/04/15',null);
insert into orders values(6,4,'2014/04/21','2014/04/24',null);
insert into orders values(7,2,'2014/03/23','2014/03/24',null);
insert into orders values(8,2,'2014/04/06','2014/04/09',null);
insert into orders values(9,2,'2014/04/12','2014/04/15',null);
-- product(产品表)
insert into product values(1,4,13,3,'虎标苦荞茶','香浓',38,12.7,null,null,null);
insert into product values(2,4,13,3,'味客吉柿饼','解酒',12.5,4.2,null,null,null);
insert into product values(3,4,13,2,'怪爽金针菇山辣椒','辣味',3.6,0.64,null,null,null);
insert into product values(4,4,14,1,'曼秀雷敦水感防晒露','实惠',78,64,null,null,null);;
insert into product values(5,6,7,4,'谷一韩式自助餐','吃饱为止',78,62,null,null,2);
insert into product values(6,6,7,5,'烫锅鲜','鲜美可口',65,52,null,null,3);
insert into product values(7,6,7,5,'筒子骨','含钙高',35,25,null,null,3);
insert into product values(8,4,7,6,'傣妹火锅','云南傣家风味',58,46,null,null,3);
insert into product values(9,4,7,6,'菠萝爆肉片','松软脆香',46,35,null,null,2);
insert into product values(10,4,5,7,'公馆KTV','音响一流',98,72,null,null,null);
insert into product values(11,4,15,8,'米乐星KTV','米老鼠风格',86,68,null,null,null);
insert into product values(12,6,15,9,'欢乐迪KTV','嗨乐',120,98,null,null,null);
insert into product values(13,4,14,1,'资生堂菲婷','日本原装',369,289,null,null,null);
-- ordersdetail(订单详情列表)
insert into ordersdetail values(1,1,2);
insert into ordersdetail values(1,3,3);
insert into ordersdetail values(2,2,2);
insert into ordersdetail values(2,3,4);
insert into ordersdetail values(3,1,1);
insert into ordersdetail values(4,1,3);
insert into ordersdetail values(4,2,1);
insert into ordersdetail values(4,3,4);
insert into ordersdetail values(5,6,3);
insert into ordersdetail values(5,8,2);
insert into ordersdetail values(6,7,2);
insert into ordersdetail values(7,7,1);
insert into ordersdetail values(7,9,2);
insert into ordersdetail values(8,5,2);
insert into ordersdetail values(8,8,1);
insert into ordersdetail values(9,6,1);
insert into ordersdetail values(9,10,2);
操作数据
题目1
修改所有登录密码为888888
题目2
修改客户姓名魏国兰的密码为123456
题目3
修改火锅类商品的原始价格和当前价格,在原始价格和当前价格上加1
题目4
删除客户姓名为郝琼琼的记录
题目5
删除客户姓名为刘亚蒙的记录
-- 修改所有登录密码为888888
update customer set pwd=888888 where customerID>0;
-- 修改客户姓名魏国兰的密码为123456
update customer set pwd=123456 where customerID=5;
-- 修改火锅类商品的原始价格和当前价格,在原始价格和当前价格上加1
update product
set originalPrice = originalPrice + 1,currentPrice = currentPrice + 1
where categoryID=7;
-- 删除客户姓名为郝琼琼的记录
delete from customer where customerName='郝琼琼';
-- 删除客户姓名为刘亚蒙的记录
delete from ordersdetail where ordersID=1 or ordersID=2 or ordersID=5 or ordersID=6;
delete from orders where customerID=4;
delete from customer where customerName='刘亚蒙';
查询和函数
题目1
查询所有客户,如图
题目2
查询所有女客户,如图
题目3
查询所有满35岁的客户,如图
题目4
查询没有上级区域编号的区域,如图
题目5
查询当前价格最贵的3个商品,如图
题目6
查询当前价格第3便宜的商品,如图
题目7
查询所有商品,如图
/*查询和函数
题目1
查询所有客户*/
select * from customer;
/*题目2
查询所有女客户*/
select customerName 姓名,tel 电话,address 地址,birthday 生日
from customer where gender='女';
/*题目3
查询所有满35岁的客户*/
select customerName 姓名,
concat(cast(datediff(curdate(),birthday) / 365 as signed),'岁') 年龄
from customer where datediff(curdate(),birthday) / 365 >= 35;
/*题目4
查询没有上级区域编号的区域*/
select areaID,areaName from area where p_areaID is null;
/*题目5
查询当前价格最贵的3个商品*/
select productID,title,currentPrice from product order by currentPrice desc limit 3;
/*题目6
查询当前价格第3便宜的商品*/
select productID,title,currentPrice from product order by currentPrice limit 2,1;
/*题目7
查询所有商品*/
select categoryID,currentPrice from product order by categoryID,currentPrice desc;
一、语言和环境
1、实现语言:Sql语言。
2、环境要求:mysql数据库。
二、题目
某银行拟开发一套ATM系统软件对客户的账户和交易信息进行管理。该系统的后台数据库为
ATM,该数据库中拥有账户表Account和交易表TransInfo两张表。
1、创建数据库,数据库名称为ATMDB
2、创建数据表Account和TransInfo,数据表的基本信息如下:
账号表Account
字段名 | 数据类型 | 长度 | 约束 | 说明 |
---|---|---|---|---|
CardNo | int | 主键, | 卡号 | |
CustomerName | varchar | 20 | 唯一,非空 | 账户名称 |
Balance | decimal(8,2) | 非空 | 账户余额 |
交易表TransInfo
字段名 | 数据类型 | 长度 | 约束 | 说明 |
---|---|---|---|---|
TransId | int | 主键,自动增长 | 交易编号 | |
CardNo | int | 外键,非空,Account表CardNo | 卡号 | |
TransType | varchar | 20 | 非空,只能是”存入”或”支取”,默认”存入” | 交易类型 |
Amount | decimal(8,2) | 非空,默认为0 | 交易金额 | |
TransDate | datetime | 非空,默认当前日期 | 交易日期 |
3、插入测试数据
账号表Account测试数据如下:
交易表TransInfo测试数据如下
4、使用DML语句完成以下功能(必须使用SQL脚本,并保留脚本):
1) 用户朱逸群在2014-04-16号由于生活费不够,将去银行取钱(支出)800,此时在交易表中会产生一条交易信息,同时账户表中的余额将发生相应的改变。
提示:
a、先将用户朱逸群的交易信息向交易表中插入一条数据
b、修改用户朱逸群在账户表中的余额
2) 删除用户朱逸群2014年4月13日的所有交易记录
3) 查询2014年所有存入的交易记录,按存款金额降序排列
要求采用别名显示字段:卡号,交易类型,交易金额,交易时间
4) 按交易类型查询交易总金额和交易次数
要求采用别名显示字段:交易类型,总金额,交易次数
提示:使用分组查询、聚合函数
5) 查询账户表和交易表,显示交易类型为存入且按交易金额降序排的账户名、交易金额、交易类型。
提示:使用表连接、排序、别名
6) 查询账户表和交易表,显示账户名为”朱逸群”的全部交易信息且按交易金额降序排后 显示前两条数据。
要求采用别名显示字段:账户名,交易类型,交易金额,交易时间
提示:使用表连接、order by排序、limit
7) 使用别名统计交易表每个卡号对应的存入次数和最大存入金额且要求最大存入金额大于1000
提示:使用别名、where条件、group分组、having筛选
三、注意事项:
1、建库、建表、建约束,注意表中的约束设置不能遗漏。
2、DML操作(要求4)必须新建SQL脚本使用语句完成,并在每题前注释操作要求。
3、考试完毕后,保存sql脚本,放入姓名文件夹打包提交。
4、请仔细检查考生文件夹是否为空,必须提交sql脚本。
四、评分标准
该程序的评分标准如下: | ||
---|---|---|
创建数据库 | 5 | 正确创建数据库。 |
创建数据表 | 20 | 正确创建两张数据表 |
建立约束 | 20 | 正确建立表中的约束(主键、自增、默认、唯一、外键)每个4分 |
添加数据 | 10 | 正确添加初始数据 |
DML语句操作数据库 | 40 | 每题5分 |
注释 | 5 | 命名规范,有适当注释 |
总分 | 100分 |
-- 插入测试数据,账户表
insert into account values(600000001,'朱逸群',100000.00);
insert into account values(600000002,'杜琦燕',9000.00);
insert into account values(600000003,'史珍香',7000.00);
insert into account values(600000004,'范统',190000.00);
insert into account values(600000005,'杜梓腾',50000.00);
-- 插入测试数据,交易表
insert into transinfo values(1, 600000001, '存入', 1600.00, '2014-04-13 21:58:37');
insert into transinfo values(3, 600000001, '支出', 500.00, '2013-05-20 00:00:00');
insert into transinfo values(4, 600000003, '存入', 6000.00, '2014-04-10 00:00:00');
insert into transinfo values(5, 600000002, '存入', 1000.00, '2012-06-20 00:00:00');
insert into transinfo values(6, 600000005, '支出', 2000.00, '2014-04-16 14:07:37');
/*
1、用户朱逸群在2014-04-16号由于生活费不够,将去银行取钱(支出)800,此时在交易表
中会产生一条交易信息,同时账户表中的余额将发生相应的改变。
提示:
a、先将用户朱逸群的交易信息向交易表中插入一条数据
b、修改用户朱逸群在账户表中的余额
*/
insert into transinfo values(null,600000001,'支出',800,current_date);
update account set Balance=Balance-800 where CustomerName='朱逸群';
-- 2、删除用户朱逸群2014年4月13日的所有交易记录
delete from transinfo where CardNo=600000001 and TransDate like '2014-04-13%';
-- 查询2014年所有存入的交易记录,按存款金额降序排列
-- 要求采用别名显示字段:卡号,交易类型,交易金额,交易时间
select CardNo 卡号,TransType 交易类型,Amount 交易金额,TransDate 交易时间 from transinfo where TransType='存入' and TransDate like '2014%'
-- 按交易类型查询交易总金额和交易次数
-- 要求采用别名显示字段:交易类型,总金额,交易次数
-- 提示:使用分组查询、聚合函数
select TransType 交易类型,sum(Amount) 总金额,count(*) 交易次数 from transinfo group by TransType;
-- 查询账户表和交易表,显示交易类型为存入且按交易金额降序排的账户名、交易金额、交易类型。
-- 提示:使用表连接、排序、别名
select CustomerName 账户名,Amount 交易金额,TransType 交易类型 from account t1,transinfo t2 where t2.CardNo=t1.CardNo and TransType='存入' order by Amount desc;
/*
查询账户表和交易表,显示账户名为”朱逸群”的全部交易信息且按交易金额降序排后显示前两条数据。
要求采用别名显示字段:账户名,交易类型,交易金额,交易时间
提示:使用表连接、order by排序、limit
*/
select CustomerName 账户名,TransType 交易类型,Amount 交易金额,TransDate 交易时间 from account t1,transinfo t2 where t2.CardNo=t1.CardNo and CustomerName='朱逸群' order by Amount desc limit 2;
-- 使用别名统计交易表每个卡号对应的存入次数和最大存入金额且要求最大存入金额大于1000
-- 提示:使用别名、where条件、group分组、having筛选
select CardNo 卡号,count(*) 存入次数,max(Amount) 最大存入金额 from transinfo where TransType='存入' group by CardNo having max(Amount)>1000;
一、语言和环境
1、实现语言:Sql语言。
2、环境要求:mysql数据库。
二、功能需求
具体要求如下:
1、创建数据库:KFC_DB
2、创建数据表和约束:
表1:T_Goods 商品表
序号 | 字段名称 | 字段说明 | 字段类型 | 字段大小 | 是否主键 | 允许空 | 备注 |
---|---|---|---|---|---|---|---|
(1) | GoodId | 商品编号 | int | 主键,自增 | |||
(2) | GoodName | 商品名称 | varchar | 50 | 唯一 | ||
(3) | GoodPrice | 商品单价 | decimal | 5,2 | 是 | ||
(4) | Rebate | 折扣 | decimal | 10,2 | 默认1 |
表2::T_Orders 订单表
序号 | 字段名称 | 字段说明 | 字段类型 | 字段大小 | 是否主键 | 允许空 | 备注 |
---|---|---|---|---|---|---|---|
(1) | OrderId | 订单编号 | int | 主键,自增 | |||
(2) | OrderDate | 下单日期 | datetime | 是 | 默认当前日期 | ||
(3) | GoodId | 商品编号 | int | 外键,参照商品表的商品编号字段 | |||
(4) | Quantity | 购买数量 | int | 是 | 大于0 |
3、向表中添加测试数据(可视化编辑或使用SQL脚本皆可):
商品表数据
商品编号 | 商品名称 | 商品价格 | 折扣 |
---|---|---|---|
1 | 五味小吃桶升级版 | 54.00 | 默认为1.00 |
2 | 培根鸡腿燕麦堡套餐 | 35.50 | 默认为1.00 |
3 | 黄金咖喱猪扒饭套餐 | 37.50 | 默认为1.00 |
4 | 香烤照烧鸡腿饭套餐 | 37.50 | 默认为1.00 |
5 | 培根蘑菇鸡柳饭套餐 | 33.50 | 默认为1.00 |
6 | 骨肉相连 | 5.50 | 默认为1.00 |
订单表数据
订单编号 | 下单日期 | 商品编号 | 订购数量 |
---|---|---|---|
1 | 2014-03-31 | 1 | 1 |
2 | 2014-03-31 | 2 | 2 |
3 | 2014-04-01 | 1 | 2 |
4 | 2014-04-01 | 3 | 3 |
5 | 2014-04-01 | 4 | 4 |
4、使用DML实现以下功能(必须使用SQL脚本,并保留脚本):
1)–添加一条商品信息:商品名称:KFC全家桶,商品价格:82.50。
2)–添加一条订单信息:日期(当前日期),商品编号(id为1),数量(2)。
3)–删除商品编号为6的商品。
4)–将所有商品的折扣修改为九八折(0.98)商品编号为3的除外。
5)–修改商品编号为3的价格更改为29.80.
6)–查询价格大于50元的商品数量.
7)–查询价格在35元到50元之间的商品信息,用别名显示名称,价格,折扣(包含35、50元)
8) –统计每个商品的订单数量,并按订单数量降序排序,显示商品名,订单数量.
提示:使用表联接、分组查询、聚合函数
9)–统计每个下单日期的销售总额,按销售总额升序排列。
提示:使用表联接、分组查询、聚合函数
商品折扣后单价:goodprice*rebate/10
订单价:goodpricerebate/10quantity
10)–统计本月的销售情况,显示商品名,售出总数量,总金额
提示:使用表联接、分组查询、聚合函数
三、注意事项:
1、建库、建表、建约束(要求1、2、3)可以使用可视化操作,注意表中的约束设置不能遗漏。
2、DML操作(要求4)必须新建SQL脚本使用语句完成,并在每题前注释操作要求。
3、考试完毕后,保存sql脚本,放入姓名文件夹打包提交。
4、请仔细检查考生文件夹是否为空,必须提交sql脚本。
四、评分标准
该程序的评分标准如下: | ||
---|---|---|
创建数据库 | 5 | 正确创建数据库。 |
创建数据表 | 20 | 正确创建两张数据表 |
建立约束 | 20 | 正确建立表中的约束(主键、自增、默认、唯一、外键)每个4分 |
添加数据 | 10 | 正确添加初始数据 |
DML语句操作数据库 | 40 | 每题4分 |
注释 | 5 | 命名规范,有适当注释 |
总分 | 100分 |
-- 添加一条商品信息:商品名称:KFC全家桶,商品价格:82.50
insert into t_goods values(null,'KFC全家桶',82.50,default);
-- 添加一条订单信息:日期(当前日期),商品编号(id为1),数量(2)
insert into t_orders values(null,current_date,1,2);
-- 删除商品编号为6的商品。
delete from t_orders where GoodId=6;
-- 将所有商品的折扣修改为九八折(0.98)商品编号为3的除外。
update t_goods set Rebate=0.98 where GoodId<>3;
-- 修改商品编号为3的价格更改为29.80.
update t_goods set GoodPrice=29.80 where GoodId=3;
-- 查询价格大于50元的商品数量
select count(*) 大于50元的商品数量 from t_goods where GoodPrice>50;
-- 查询价格在35元到50元之间的商品信息,用别名显示名称,价格,折扣(包含35、50元)
select GoodName 名称,GoodPrice 价格,Rebate 折扣 from t_goods where GoodPrice between 35 and 50;
-- 统计每个商品的订单数量,并按订单数量降序排序,显示商品名,订单数量.
-- 提示:使用表联接、分组查询、聚合函数
select GoodName 商品名,count(*) 订单数量 from t_goods t1,t_orders t2 where t2.GoodId=t1.GoodId group by GoodName order by 订单数量 desc;
/*
统计每个下单日期的销售总额,按销售总额升序排列。
提示:使用表联接、分组查询、聚合函数
商品折扣后单价:goodprice*rebate/10
订单价:goodprice*rebate/10*quantity
*/
select OrderDate 下单日期,sum(goodprice*rebate/10*quantity) 销售总额 from t_goods t1,t_orders t2 where t2.GoodId=t1.GoodId group by OrderDate order by 销售总额;
/*
统计本月的销售情况,显示商品名,售出总数量,总金额
提示:使用表联接、分组查询、聚合函数
*/
select GoodName 商品名称,Quantity 售出总数量,sum(goodprice*rebate*quantity) 总金额 from t_goods t1,t_orders t2 where t2.GoodId=t1.GoodId group by GoodName,Quantity;