小饶学编程之JAVA SE第二部分——MySQL基础语法使用案例

一、创建表

1.1会员类型表UserTypeInfo

会员类型表UserTypeInfo [系统管理员-管理会员 店主-卖家 普通会员-买家]
类型编号 typeId int 主键 自增长
类型名 typeName varchar(20) 不能为空

下面展示 创建表的代码片。

CREATE table UserTypeInfo(
typeId  int primary key auto_increment,
typeName  varchar(20) not NULL
);

SELECT * from UserTypeInfo;

1.2会员信息表UserInfo

会员信息表UserInfo
会员编号 userId int 主键 自增长
会员名 userName varchar(20) 不能为空
密码 userPwd varchar(20)
类型编号 typeId int 外键(引用UserTypeInfo的typeId)
会员状态 userStatus varchar(20) 默认值为”正常”

下面展示 创建表的代码片。

CREATE table UserInfo(
userId    int primary key auto_increment,
userName    varchar(20) not NULL,
userPwd  varchar(20),
typeId  int,
userStatus  varchar(20)  DEFAULT '正常',
FOREIGN KEY(typeId)REFERENCES UserTypeInfo(typeId)
);

SELECT * from UserInfo;

1.3店铺信息表ShopInfo

店铺信息表ShopInfo
店铺编号 shopId int 主键 自增长
店铺名 shopName varchar(50) 不能为空
店主编号 userId int 外键(引用UserInfo的userId)
店铺积分 userPoint int 默认值为0

下面展示 创建表的代码片。

CREATE table ShopInfo(
shopId   int primary key auto_increment,
shopName    varchar(50) not NULL,
userId   int ,
userPoint  int  DEFAULT '0',

FOREIGN KEY(userId)REFERENCES UserInfo(userId)
);

SELECT * from ShopInfo;

1.4商品类型表 proType

商品类型表 proType
类型编号 typeId int 主键 自增长
类型名 typeName varchar(50) 唯一约束 不能为空
类型说明 typeDesc varchar(50)

下面展示 创建表的代码片。

CREATE table proType(
typeId  int primary key auto_increment,
typeName     varchar(50) UNIQUE not NULL,
typeDesc varchar(50)
);

SELECT * from proType;

1.5商品信息表 proInfo

商品信息表 proInfo
商品编号 proId int 主键 自增长
商品名称 proName varchar(50) 不能为空
商品单价 proPrice float
商品数量 proCount int
商品状态 proStates varchar(50) 默认值为”上架”
所属店铺编号 shopId int 外键(引用shopInfo的shopId)
所属类型编号 typeId int 外键（引用proType的typeId）

下面展示 创建表的代码片。

CREATE table proInfo(
proId    int primary key auto_increment,
proName varchar(50)  not NULL,
proPrice  float,
proCount  int,
proStates  varchar(50)  DEFAULT '上架',
shopId  int,
typeId  int,
FOREIGN KEY(shopId) REFERENCES ShopInfo(shopId),
FOREIGN KEY(typeId) REFERENCES proType(typeId)
);

SELECT * from proInfo;

1.6订单信息表 orderInfo

订单信息表 orderInfo
订单编号 orderId int 主键 自增长
订购的商品编号 proId int 外键
订购的数量 orderCount int
订单日期 orderDate date 默认为系统的当前时间
订单状态 orderStatus 值只能为"未处理" 和 “已发货”，默认为"未处理"
会员编号 userId int 外键
下面展示 创建表的代码片。

CREATE table orderInfo(
orderId     int primary key auto_increment,
proId   int,
orderCount   int ,
orderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
orderStatus enum('未处理','已发货') DEFAULT '未处理',
userId  int,
FOREIGN KEY(proId)REFERENCES proInfo(proId),
FOREIGN KEY(userId)REFERENCES UserInfo(userId)
);

SELECT * from orderInfo;

二、实现及结果

1、按照上面要求创建表和添加约束

2、为每张表添加测试数据

3、查询所有店铺的编号、名称、积分，以及店主名

下面展示 查询代码片。

select shopId,shopName,userPoint,userName 
from ShopInfo ,UserInfo 
where shopInfo.Userid = UserInfo.Userid;

下面是查询结果：

4、查询所有订单的编号、订购的商品名称、商品的类型名称 以及 订购的数量

下面展示 查询代码片。

select orderId,proName,typeName,orderCount 
from orderInfo,proInfo,proType
 where orderInfo.proId = proInfo.proId -- 如果2个字段名相同无法区分的话，就把表名带上
 and proInfo.Typeid =proType.Typeid;

下面是查询结果：

5、查询所有商品的编号、名称、单价、所属类型名、所属店铺名、以及店主名

下面展示 查询代码片。

select proId,proName,proPrice,typeName,shopName,userName 
from proInfo,proType,ShopInfo,UserInfo 
where proInfo.shopId = ShopInfo.Shopid and proInfo.typeId = proType.Typeid 
and ShopInfo.Userid = UserInfo.Userid;

下面是查询结果：

6、查询所有订单的编号、订购的商品名称、商品的类型名称，商品所属店铺名，店主名以及订购的数量

下面展示 查询代码片。

select orderId,proName,typeName,shopName ,userName,orderCount 
from orderInfo,proInfo,proType,ShopInfo,UserInfo 
where orderInfo.proId = proInfo.proid and proInfo.typeId = proType.typeId 
and ShopInfo.Shopid =proInfo.shopId and UserInfo.Userid = ShopInfo.Userid;

下面是查询结果：

7、查询每个店铺的名称，以及该店铺下商品的种类，以及商品的总数量

下面展示 查询代码片。

SELECT shopName,typeName,totalCount from 
(
select shopinfo.shopName,t.typeId, t.totalCount from (
	select proInfo.typeId,proInfo.shopId,sum(procount) totalCount from proInfo group by proInfo.typeId,proInfo.shopId
) t RIGHT  JOIN ShopInfo on  ShopInfo.shopId = t.shopId
) m  LEFT JOIN protype on m.typeId = protype.typeId

下面是查询结果：

8、查询每个商品的名称、该商品所有订单的数量

下面展示 查询代码片。

select sum(totalOrderCount),proName from (
	select proId,count(orderCount) totalOrderCount from orderinfo group by proId
) t , proInfo where t.proId = proinfo.proId group by proName ;

下面是查询结果：

9、查询价格最贵的商品的名称、所属店铺的名称以及店主的名称

下面展示 查询代码片。

select proName,shopName,userName  
from proInfo,ShopInfo,UserInfo 
where proPrice = (select max(proPrice) from proInfo)
and proInfo.shopId = ShopInfo.shopId
and UserInfo.Userid =ShopInfo.Userid ;

下面是查询结果：

10、查询单次订购数量最多的订单的编号、订购的商品名称、订购的日期以及订购该商品的会员名称

下面展示 查询代码片。

 select orderId,proName ,orderDate,userName
 from orderInfo,proInfo,UserInfo
 where UserInfo.Userid = orderInfo.Userid
 and orderInfo.proId = proInfo.proId
 and orderCount = (select max(orderCount) from orderInfo);

下面是查询结果：

11、查询出店铺名以’好’字开头的店铺信息以及店主的名称

下面展示 查询代码片。

select * 
from ShopInfo,UserInfo
where ShopInfo.userId = UserInfo.userId
and shopname like '好%';

下面是查询结果：

12、查询出没有发布商品的店铺信息

下面展示 查询代码片。

select * from ShopInfo where shopId not in (select distinct shopId from proInfo);

下面是查询结果：

13、将没有发布商品的店铺的积分减1

下面展示 代码片。

update ShopInfo set userPoint = userPoint-1 where shopId not in (select distinct shopId from proInfo);

14、查询出没有被订购的商品编号、商品名、所属的店铺名

下面展示 查询代码片。

select proId,proName ,shopName
from proInfo,ShopInfo
where proId not in (select distinct proId from orderInfo)
and proInfo.Shopid = ShopInfo.Shopid

下面是查询结果：

15、查询所有商品的编号、名称、被订购的次数以及订购的总数量
下面展示 查询代码片。

select proInfo.proid,proname,totalNum,totalOrderCount from proInfo left join 
 (select proId,count(orderId) totalNum, sum(orderCount) totalOrderCount from orderInfo group by proId) t 
 on proinfo.proid = t.proid;

下面是查询结果：