产品——仓库表查询

数据库说明：本次查询采用的是MYSQL

一、建表

1.1 需建立的表及字段

员工表E（employeeID,name,department）:员工编号，姓名，部门；

产品表P（prodectID,name,model,size,color）：产品编号，产品名称，型号，尺寸，颜色；

仓库表W（warehouseID,name,address,employeeID）:仓库编号，仓库名称，地址，负责人编号；

库存表L（warehouseID,prodectID，quantity）:仓库编号，产品编号，产品数量。

1.2 表关系

仓库表W与员工表E呈一对多关联（one-many），建仓库表时employeeID为外键。

仓库表W与库存表L呈一对一关联（one-one），建仓库表时有些特殊，因为此表有两个外键，没有主键，如下图所示：

二、查询

1.查询每种产品的名称和该产品的总库存量：

SQL：

select name,sum(quantity)
from p,l
where p.productID=l.productID group by name

 

 

 

分析：该SQL要求对查询结果进行分组，即相同产品名称分为一组，然后计算每组的库存数量。

2.查询所有仓库中都存在的产品名称

SQL：

select name from p
where not exists
(select * from w
where not exists
(select * from l
where p.productID=l.productID and w.warehouseID=l.warehouseID))

 

 

 

 

 

分析：该查询较第一个难一些，主要考察对SQL中的not exists理解及运用，此条的SQL的查询逻辑是先在仓库表w中查询所有仓库没有的商品，基于此结果，再查一下商品表p中的商品名称——是该结果中不存在的，即所有仓库中都存在的商品名称。将此SQL进行拆分说明如下：

1.先在仓库表w中查询所有仓库没有的商品

SQL：

select * from w,p
where not exists
(select * from l
where p.productID=l.productID and w.warehouseID=l.warehouseID)

 

 

 

 

结果：

图1

分析：

先看一下库存表l中的数据信息：

从以上数据信息可看出仓库1只有商品1，缺少商品2,3，所以上面的查询结果应当有2条记录；仓库2，仓库3同理，在上面的查询结果应当各有1条记录，看下“图1”，果然如此。现在重点来了，既然查出了所有仓库中都不存在的商品，那么再次利用 not exists，查询一下商品表p中的商品名称——且在这个结果中（“图1”）不存在的，即为所有仓库中都存在的商品名称。

2.查询商品表p中的上面结果中不存在的商品名称

select name from p
where not exists
(select * from w
where not exists
(select * from l
where p.productID=l.productID and w.warehouseID=l.warehouseID))

 

 

 

 

 

结果：