MySQL练习题

-- 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);

-- 修改所有登录密码为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
查询所有客户*/
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;

-- 插入测试数据，账户表
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;

-- 添加一条商品信息：商品名称: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;