利用MySQL进行数据复杂查询（1）

1） 某电商网商品库存管理系统数据库及表的创建
某电商网商品库存管理系统是与某电商网后台无缝对接，属于某电商网整体框架中的子系统，完整系统涵盖了某电商网卖家订单的库存、采购、物流、财务、会员、数据分析一体化的业务管理。本案例抽取完整系统中核心数据库和业务需求，实现了商品管理，客户管理和订单管理的功能，让学员能够深刻体验企业中真实的业务场景以及针对业务需求而涉及的技术知识点。
本案例根据系统功能可分为如下表：
表2-1 某电商网商品库存管理系统数据库表
序号 表名称 描述
1 tbl_commoditytype 商品类型表
2 tbl_commodity 商品表
3 tbl_customer 客户表
4 tbl_order 订单
同时本案例对如上表结构及表字段进行约束设计，具体如下：
表2-2 tbl_commoditytype表结构
编号 列名称 说明 类型 长度 约束
1 id 商品类型编号 int 11 主键约束
2 name 商品类型名称 varchar 50 非空约束
表2-3 tbl_commodity表结构
编号 列名称 说明 类型 长度 约束
1 id 商品类型编号 int 11 主键约束
2 name 商品类型名称 varchar 50 非空约束
3 madein 商品产地 varchar 50 非空约束
4 type 商品类型 int 11 外键约束
5 inprice 商品进价 int 11 非空约束
6 outprice 商品售价 int 11 默认约束（默认10）
7 num 商品库存 int 11 默认约束（默认100）
表2-4 tbl_customer表结构
编号 列名称 说明 类型 长度 约束
1 id 用户编号 int 11 主键约束
2 name 用户名称 varchar 50 非空约束
3 phone 用户手机 varchar 50 非空约束、唯一约束
4 gender 用户性别 int 11 默认约束 （默认1）
5 address 用户地址 varchar 100 非空约束
表2-5 tbl_order表结构
编号 列名称 说明 类型 长度 约束
1 id 订单编号 int 11 主键约束、自动增长
2 cuid 用户编号 int 11 外键约束
3 cid 商品编号 int 11 外键约束
4 num 订单数量 int 11 检查约束（检查订单数量至少为1）
向表中插入数据如下：
tbl_commoditytype表
tbl_commodity表

tbl_customer表

tbl_order表

1）每件商品的单件利润是多少？

SELECT name,outprice-inprice FROM tbl_commodity;


2）查询进价大于等于10的商品信息

SELECT name,outprice-inprice FROM tbl_commodity;

3）查询进价小于10的商品信息

SELECT name,inprice FROM tbl_commodity WHERE inprice<=10;


4）查询进价小于10并且商品类型为1的商品信息

SELECT name,inprice,type FROM tbl_commodity WHERE inprice<=30 AND type = 1;


5）查询进价小于10或商品类型为1的商品信息

SELECT name,inprice,type FROM tbl_commodity WHERE inprice<10 OR type = 1;

6）查询进价在10-20之间

SELECT name,type FROM tbl_commodity WHERE inprice BETWEEN 10 AND 20;


7）查询商品售价为空的商品信息

SELECT name FROM tbl_commodity WHERE outprice IS NULL;


8）查询商品进价为10或20或30的商品信息

SELECT name,inprice FROM tbl_commodity WHERE inprice =10 OR inprice =20 or inprice =30;


9）商品名称包括'玩具'的所有商品

SELECT name FROM tbl_commodity WHERE name LIKE '%玩具%';


10）根据商品进价从低到高排序输出

SELECT name,inprice FROM tbl_commodity ORDER BY inprice;


11）根据商品售价从高到低排序输出

SELECT name,outprice FROM tbl_commodity ORDER BY outprice desc;


12）商品售价排行榜前5名

SELECT name,outprice FROM tbl_commodity ORDER BY outprice desc LIMIT 5;


13）商品售价排行榜第6-10名

SELECT name,outprice FROM tbl_commodity ORDER BY outprice desc LIMIT 6,5;


14）计算每种商品的平均进价

SELECT type,AVG(inprice) FROM tbl_commodity GROUP BY type;


15）根据客户姓名查询客户信息

SELECT * FROM tbl_customer WHERE name LIKE '刘德华';

16）根据客户手机号查询客户信息

SELECT * FROM tbl_customer WHERE phone = '123';

17）查询出一共有多少男性客户

SELECT count(*) as 男性人数 FROM tbl_customer GROUP BY gender HAVING gender = 1;

18）查询出一共有多少女性客户

SELECT count(*) as 女性人数 FROM tbl_customer GROUP BY gender HAVING gender = 0;

19）使用子查询来获取指定客户买了什么

SELECT name FROM tbl_commodity WHERE id IN(SELECT id FROM tbl_customer WHERE name = '刘德华');

20）使用连接查来获取指定客户买了什么

SELECT a.name FROM tbl_commodity a JOIN tbl_customer b on a.id = b.id WHERE b.name = '刘德华';