小汤学编程之MySQL经典例题——表的创建与查询

题目：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

15、查询所有商品的编号、名称、被订购的次数以及订购的总数量

解答：

-- 1.按照要求创建表和添加约束
create table UserTypeInfo(
	typeId int primary key auto_increment,
	typeName varchar(20) not NULL
)
DESC UserTypeInfo;-- 查询表结构
SELECT * FROM UserTypeInfo;
DELETE FROM UserTypeInfo WHERE typeName = "管理员";
INSERT into UserTypeInfo (typeName) VALUES ("普通会员")

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)
)
DESC UserInfo;-- 查询表结构
SELECT * FROM UserInfo;

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)
)
DESC ShopInfo;-- 查询表结构
SELECT * FROM ShopInfo;

create table proType(
	typeId int primary key auto_increment,
	typeName varchar(50) unique not NULL,
	typeDesc varchar(50)
)
DESC proType;-- 查询表结构
SELECT * FROM proType;

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)
)
DESC proInfo;-- 查询表结构
SELECT * FROM proInfo;

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)
)
DESC orderInfo;-- 查询表结构
SELECT * FROM orderInfo;

-- 2.为每张表添加测试数据
-- UserInfo
INSERT into UserInfo (userName,userPwd,typeId) VALUES ("张三","123456","1");
INSERT into UserInfo (userName,userPwd,typeId) VALUES ("李四","987654","2");
INSERT into UserInfo (userName,userPwd,typeId) VALUES ("王五","888888","3");
-- ShopInfo
INSERT into ShopInfo (shopName,userId,userPoint) VALUES ("小碗菜",3,50);
-- proType
INSERT into proType (typeName,typeDesc) VALUES ("餐馆","提供饭菜的地方");
INSERT into proType (typeName,typeDesc) VALUES ("毒药店","专门养蟑螂的地方");
-- proInfo
INSERT into proInfo (proName,proPrice,proCount,shopId,typeId) VALUES ("土豆丝",12.5,12,1,1);
INSERT into proInfo (proName,proPrice,proCount,shopId,typeId) VALUES ("烤蟑螂",10.0,1,2,2);
-- orderInfo
INSERT into orderInfo (proId,orderCount,userId) VALUES (2,5,3);

-- 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 and proInfo.typeId = proType.typeId;

-- 5.查询所有商品的编号、名称、单价、所属类型名、所属店铺名、以及店主名
SELECT proId,proName,proPrice,typeName,shopName,userName FROM proInfo,proType,ShopInfo,UserInfo
where proInfo.typeId = proType.typeId AND proInfo.shopId = ShopInfo.shopId 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 proInfo.shopId = shopInfo.shopId AND orderInfo.userId = UserInfo.userId;

-- 7.查询每个店铺的名称，以及该店铺下商品的种类，以及商品的总数量
SELECT shopName,typeName,proCount FROM ShopInfo,proType,proInfo 
WHERE proInfo.shopId = ShopInfo.shopId AND proInfo.typeId = proType.typeId;

-- 8.查询每个商品的名称、该商品所有订单的数量
SELECT proName,COUNT(orderId) FROM proInfo,orderInfo
WHERE proInfo.proId = orderInfo.proId;

-- 9.查询价格最贵的商品的名称、所属店铺的名称以及店主的名称
SELECT proName,shopName,userName,proPrice FROM UserInfo,ShopInfo,proInfo 
WHERE  proInfo.shopId = ShopInfo.shopId AND ShopInfo.userId = UserInfo.userId
GROUP BY proPrice DESC limit 1;

-- 10.查询单次订购数量最多的订单的编号、订购的商品名称、订购的日期以及订购该商品的会员名称
SELECT MAX(orderCount),orderId,proName,orderDate,userName FROM UserInfo,orderInfo,proInfo
WHERE UserInfo.userId = orderInfo.userId  AND orderInfo.proId = proInfo.proId 
GROUP BY orderCount DESC limit 1 ;

-- 11.查询出店铺名以'蟑'字开头的店铺信息以及店主的名称
SELECT shopId,shopName,userPoint,userName,ShopInfo.userId FROM ShopInfo,UserInfo 
WHERE ShopInfo.shopName LIKE "蟑%" AND ShopInfo.userId = UserInfo.userId;

-- 12.查询出没有发布商品的店铺信息
SELECT * FROM ShopInfo where shopId NOT in (
SELECT shopId FROM proInfo);

-- 13.将没有发布商品的店铺的积分减1
update ShopInfo SET userPoint = userPoint-1 WHERE shopId NOT in (
SELECT shopId FROM proInfo);

-- 14.查询出没有被订购的商品编号、商品名、所属的店铺名
SELECT proId,proName,shopName FROM proInfo,ShopInfo where proId NOT in (
SELECT proId FROM orderInfo) AND proInfo.shopId = ShopInfo.shopId;

-- 15.查询所有商品的编号、名称、被订购的次数以及订购的总数量
INSERT INTO orderinfo VALUES (null,1,2,null,null,1)
SELECT proInfo.proId,proName,COUNT(proInfo.proId)'被订购次数',SUM(orderCount)'订购总数量' FROM proInfo LEFT JOIN orderInfo ON 
proInfo.proId = orderInfo.proId GROUP BY proName;