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;
下面是查询结果: