一、理解需求
我公司目前有一套汽车资产管理处置数据库，该系统包括用户基本资料、资方基本资料、处置车辆基本信息管理、匹配订单管理、处置订单管理，匹配记录管理、及相关数据统计管理。该系统实现了车辆信息录入、修改、查询、管理、运营等功能。要进行后台数据库的设计，以“处置订单管理”为例，创建数据库。此模块具有车辆录入、查询、回款情况查询，某一段时间内的订单量、营业额、接单用户统计等功能。
现要求对‘处置订单管理‘数据库进行日常工作的处理，对数据库进行设计并实现。
二、创建数据库
关于设计表的约束
三、插入数据
（数据插入、修改、删除、查询等操作，保存SQL脚本）
车辆基本信息的测试数据如下：
说明：
Ø id不需要插入数据，从1 开始自动递增
Ø 姓名为空值的，自动填充为‘未知’
四、数据查询
1、查询表中所有记录，检查数据是否正确；
2、查询所有‘已完成‘订单的营业额，包括车辆基本信息、资方、所在城市；
3、查询在2019年9月期间的订单量，并且按照省份汇总；
4、查询在‘已完成‘订单中，广东省的订单量和营业额；
5、排名次：在所有订单中，订单量占前三位的省份；
6、排名次：按照客户经理的订单量与营业额，显示平台佣金和毛利润及利润率并进行排名；
7、多条件筛选：想知道客户经理 苏**，在湖北区域>5000的订单；
8、使用case,when,then 对订单种类进行分级。5000-15000 为一级；15001-22000为二级；22001-90000为三级；
9、多表连接查询：对车辆匹配订单中的资方，匹配资方信息表。
五、数据修改
1、由于资方提供停车费的支出，现在需要对 “* * * *” 公司的平台佣金加上停车费；
2、因工作日人员失误，没有为车辆所有人（Owner）字段设置默认值为“未知“，导致有信息的车辆顺利录入，缺失信息的车辆为空值，现在需要对为空的进行信息补充。
六、执行数据库的分离和附加操作
【以下为SQL脚本】
#二、创建数据库
CREATE TABLE `pipei` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_number` bigint(20) DEFAULT NULL,
`finish_time` datetime DEFAULT NULL COMMENT '完成时间',
`auto_number` varchar(7) DEFAULT NULL,
`auto_type` text, `owner` varchar(4) DEFAULT NULL,
`sales` varchar(5) DEFAULT NULL,
`quoted_price` int(7) DEFAULT NULL,
`towing_fee` int(6) DEFAULT NULL,
`profit` int(5) DEFAULT NULL,
`whether_remittance` varchar(1) DEFAULT NULL,
`reality` int(10) unsigned DEFAULT NULL COMMENT '实际回款',
`client` text,
`provience` varchar(20) DEFAULT NULL,
`city` varchar(20) DEFAULT NULL,
`status` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=499 DEFAULTCHARSET=utf8;
INSERT INTO `pipei` VALUES ('1', '2018********','2018-**-** 00:00:00', '鲁000000', '起亚', '姜**', '**', '12345', '12345', '12345', '是', '12345', '******公司', '山东省', '青岛市', '已完成');
INSERT INTO `pipei` VALUES ('2', '2018******','2018-**-** 00:00:00', '鲁000000', '奔腾B50', '王**', '**', '12345', '12345', '12345','是', '12345', '******公司', '山东省', '青岛市', '已完成');
INSERT INTO `pipei` VALUES ('3', '2018*******','2018-**-** 00:00:00', '鲁000000', '奔驰', '董**', '***', '12345', '12345', '12345','是', '12345', '******公司', '河南省', '郑州市', '已完成');
……………………（此处隐藏相关信息）#四、数据查询
#1、查询表中所有记录，检查数据是否正确
select * from pipei；
#2、查询所有‘已完成‘订单的营业额，包括车辆基本信息、资方、所在城市
select auto_number,auto_type,owner,client,city from pipei;
#3、查询在2019年9月期间的订单量，并且按照省份汇总
select provience ,count(provience) frompipei
where year(finish_time)=2019 and month(finish_time)=9 group BY provience ；
#4、查询在‘已完成‘订单中，广东省的订单量和营业额
select count(auto_number),quoted_price from pipei where provience like '广东省'；
#5、排名次：在所有订单中，订单量占前三位的省份
select provience,count(*)as dingdan from pipei group by provience order by dingdan desc；
#6、排名次：按照客户经理的订单量与营业额，显示平台佣金和毛利润及利润率并进行排名
select sales,count(*)as dingdan,quoted_price from pipei group by sales order by dingdan desc；
#7、多条件筛选：想知道客户经理 苏**，在山东区域>5000的订单
select provience,count(*) as m from
(select * from pipei where sales in('苏***' ) and provience like '山东省') as a
where quoted_price >= 5000
#8、使用case,when,then对订单种类进行分级。5000-15000 为一级；15001-22000为二级；22001-90000为三级。
select
case when quoted_price > 5000 and quoted_price<=15000 then '1'
when quoted_price > 15001 and quoted_price<=22000 then '2'
when quoted_price > 22001 and quoted_price<=90000 then '3'
else null END quoted_price, count(*) from pipei
GROUP BY
case when quoted_price > 5000 andquoted_price<=15000 then '1'
when quoted_price > 15001 and quoted_price<=22000 then '2'
when quoted_price > 22001 and quoted_price<=90000 then '3'
else null END;
#9、多表连接查询：对车辆匹配订单中的资方，匹配资方信息表及匹配车辆信息
三表：select p.client, c.info,a.num from relation as r
left join pipei as p on p.clientNum = r.pno
left join company as c on c.companyNum = r.cno
left join auto_info as a on a.autoid = r.ano ;
两表：select p.client, c.info from pipei
left join company as c on c.companyNum = p.clientNum;
#五、数据修改
#1、由于资方提供停车费的支出，现在需要对‘****‘公司的平台佣金加上停车费
UPDATE pipei set quoted_price = '12345' where auto_number = '鲁K00000';
#2、因工作日人员失误，没有为车辆所有人（Owner）字段设置默认值为“未知“，导致有信息的车辆顺利录入，缺失信息的车辆为空值，现在需要对为空的进行信息补充。
select (CASE when (provience is NULL OR provience ='') then '暂无'
else provience end) as pr from pipei
六、保存,执行数据库的分离和附加操作
