入职sql练习

1.准备数据

DROP TABLE IF EXISTS `t_person_base_info`;
CREATE TABLE `t_person_base_info` (
  `id_card` varchar(18) comment '身份证号'  DEFAULT NULL,
  `name` varchar(20) comment '名称'  DEFAULT NULL,
  `gender` varchar(2) comment '性别'  DEFAULT NULL,
  `job` varchar(50) comment '岗位'  DEFAULT NULL
) comment '人员基本信息表' ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_person_base_info` VALUES ('330726199609043116', '周子明', '男', '销售');
INSERT INTO `t_person_base_info` VALUES ('330722198311191419', '陈晓', '女', '销售');
INSERT INTO `t_person_base_info` VALUES ('330719199105264310', '王志龙', '男', '研发');
INSERT INTO `t_person_base_info` VALUES ('330727198404140715', '陈狄鑫', '男', '研发');
INSERT INTO `t_person_base_info` VALUES ('330726198106171719', '应勇', '男', '销售');
INSERT INTO `t_person_base_info` VALUES ('330702199308073224', '李霞', '女', '前端');
INSERT INTO `t_person_base_info` VALUES ('330722199003290811', '潘绍', '男', '研发');
INSERT INTO `t_person_base_info` VALUES ('33072219930829648X', '何娜', '女', '前端');

DROP TABLE IF EXISTS `t_shopping_records`;
CREATE TABLE `t_shopping_records` (
  `id_card` varchar(18) comment '身份证号' DEFAULT NULL,
  `goods_name` varchar(50) comment '商品名称' DEFAULT NULL,
  `price` decimal(10,2) comment '单价'  DEFAULT NULL,
  `num` int(11) comment '购买数量'  DEFAULT NULL,
  `order_time` datetime comment '下单时间'  DEFAULT NULL
) comment '购物信息记录' ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_shopping_records` VALUES ('330726199609043116', '面包', '5.00', '3', '2020-12-01 14:33:59');
INSERT INTO `t_shopping_records` VALUES ('330726199609043116', '矿泉水', '3.00', '5', '2020-12-02 14:34:04');
INSERT INTO `t_shopping_records` VALUES ('330726199609043116', '袜子', '8.00', '3', '2020-12-02 14:34:08');
INSERT INTO `t_shopping_records` VALUES ('330702199308073224', '矿泉水', '3.00', '8', '2020-12-03 14:34:12');
INSERT INTO `t_shopping_records` VALUES ('330702199308073224', '乳酸奶', '10.00', '2', '2020-12-05 14:34:16');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '篮球', '180.00', '1', '2020-12-05 14:34:21');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '袜子', '7.00', '6', '2020-12-07 14:34:27');
INSERT INTO `t_shopping_records` VALUES ('330722198311191419', '面包', '4.00', '5', '2020-12-08 14:34:31');
INSERT INTO `t_shopping_records` VALUES ('330722198311191419', '手机', '2000.00', '1', '2020-12-09 14:34:35');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '矿泉水', '4.00', '2', '2020-12-01 14:34:40');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '篮球', '190.00', '2', '2020-12-03 14:34:45');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '面包', '5.00', '7', '2020-12-04 14:34:49');
INSERT INTO `t_shopping_records` VALUES ('330719199105264310', '笔记本', '3000.00', '1', '2020-12-18 14:34:53');
INSERT INTO `t_shopping_records` VALUES ('330727198404140715', '袜子', '15.00', '6', '2020-12-09 14:34:57');
INSERT INTO `t_shopping_records` VALUES ('330727198404140715', '球鞋', '399.00', '1', '2020-12-06 14:35:01');
INSERT INTO `t_shopping_records` VALUES ('330727198404140715', '电池', '20.00', '2', '2020-12-07 14:35:06');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '矿泉水', '3.50', '4', '2020-12-09 14:35:10');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '手机', '1999.00', '2', '2020-12-04 14:35:13');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '面包', '7.90', '2', '2020-12-14 14:35:17');
INSERT INTO `t_shopping_records` VALUES ('330722199003290811', '乳酸奶', '9.90', '1', '2020-12-16 14:35:21');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '笔记本', '2999.00', '1', '2020-12-08 14:35:25');
INSERT INTO `t_shopping_records` VALUES ('33072219930829648X', '面包', '4.90', '5', '2020-12-02 14:35:31');

2.练习题目

2.1 查询表t_person_base_info中job为‘前端’或‘销售’的所有内容

SELECT
id_card,
name,
gender,
job
from t_person_base_info
where job='前端' or job='销售';

2.2 查询表t_person_base_info中gender为‘男’ 和 ‘女’各有多少人

SELECT
gender,
count(*)
from
t_person_base_info
group by gender;

2.3 查询表t_person_base_info中job为‘销售’ 的不同性别各有多少人

SELECT
job,
gender,
count(*)
from
t_person_base_info
where job="销售"
group by gender;

2.4 查询表t_person_base_info中年龄大于30岁的所有人

 SELECT
 id_card,
 name,
 gender,
 job
 from t_person_base_info
 where  year(now()) -year(substring(id_card,7,8) )>=30;

2.5 查询表t_person_base_info中gender标记错误的人

SELECT
 id_card,
 name,
 gender,
 job,
 case id_card when (substring(id_card,17,1)+0)%2=1 then "男" else "女"  end as cc
 from t_person_base_info
 where ((substring(id_card,17,1)+0)%2=1 and gender="女") or ((substring(id_card,17,1)+0)%2=0 and gender="男");

2.6 查询表t_shopping_records中每个人花费总额

SELECT
id_card,
sum(price*num) all_cost
from t_shopping_records
group by id_card;

2.7 查询表t_shopping_records中销售数量最多的商品

SELECT
goods_name,
sum(num)  all_num
from t_shopping_records
group by goods_name
order by all_num desc
LIMIT 1;

2.8 查询表t_shopping_records中每天销售总额，按日期排序递增

SELECT
order_time,
sum(price*num) all_cost
FROM
 t_shopping_records
group by substring(order_time,1,10)
order by order_time asc;

2.9 查询表t_shopping_records中goods_name为“矿泉水”的单价走势情况

SELECT
goods_name,
substring(order_time,1,10) order_time,
price
FROM
 t_shopping_records
where 
goods_name="矿泉水"
order by order_time;

2.10 根据表t_person_base_info和表t_shopping_records关联判断，谁没有购物

select 
p.id_card,p.name,p.gender,p.job
from t_person_base_info p left join t_shopping_records s
on p.id_card = s.id_card
where s.id_card is null;

2.11 查询表t_shopping_records统计每个人商品最多花费情况

SELECT
id_card,
goods_name,
max(pr) cost
from
(
select 
id_card,
goods_name,
sum(price*num) pr
from t_shopping_records
group by id_card,goods_name
) t
group by id_card;

2.12 根据表t_person_base_info和表t_shopping_records关联判断，每个人总消费情况

SELECT
p.id_card,
P.name,
P.gender,
P.job,
s.pr all_cost
FROM(
select 
id_card,
sum(price*num) pr
from t_shopping_records
group by id_card
) s inner join t_person_base_info p
on s.id_card = p.id_card;