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

墨蓝 2022-12-21 14:58 214阅读 0赞

### MySQL数据库案例 ###

*  一、创建表
 *   *  1.1会员类型表UserTypeInfo
     *  1.2会员信息表UserInfo
     *  1.3店铺信息表ShopInfo
     *  1.4商品类型表 proType
     *  1.5商品信息表 proInfo
     *  1.6订单信息表 orderInfo
 *  二、实现及结果

# 一、创建表 #

## 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;

下面是查询结果：  
![在这里插入图片描述][20201119172706181.png_pic_center]

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

下面展示 `查询代码片`。

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

下面是查询结果：  
![在这里插入图片描述][20201119182027915.png_pic_center]

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;

下面是查询结果：  
![在这里插入图片描述][20201119182111950.png_pic_center]

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;

下面是查询结果：  
![在这里插入图片描述][2020111918223248.png_pic_center]

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

下面是查询结果：  
![在这里插入图片描述][20201119182318326.png_pic_center]

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 ;

下面是查询结果：  
![在这里插入图片描述][20201119182351720.png_pic_center]

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 ;

下面是查询结果：  
![在这里插入图片描述][2020111918243253.png_pic_center]

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);

下面是查询结果：  
![在这里插入图片描述][20201119182516887.png_pic_center]

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

下面展示 `查询代码片`。

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

下面是查询结果：  
![在这里插入图片描述][20201119182544518.png_pic_center]

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

下面展示 `查询代码片`。

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

下面是查询结果：  
![在这里插入图片描述][20201119182620565.png_pic_center]

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

下面是查询结果：  
![在这里插入图片描述][20201119182732209.png_pic_center]

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;

下面是查询结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjQ2MDg0Mw_size_16_color_FFFFFF_t_70_pic_center]

[20201119172706181.png_pic_center]: /images/20221120/5299b5aa50634c0dae5e5ba4e7b5604b.png
[20201119182027915.png_pic_center]: /images/20221120/0454e4060aeb4fa8ab1cc7beb43f280d.png
[20201119182111950.png_pic_center]: /images/20221120/0fa8f0ef3eed41df910d0a7ec9eeb977.png
[2020111918223248.png_pic_center]: /images/20221120/601e75977fef4e0c80466c8253cadeec.png
[20201119182318326.png_pic_center]: /images/20221120/abe429e38370400d88106ac3c1c9a00f.png
[20201119182351720.png_pic_center]: /images/20221120/bda37220d1db40199322ef6907c3206c.png
[2020111918243253.png_pic_center]: /images/20221120/ba5354173d584c5880f8df9cdcf184be.png
[20201119182516887.png_pic_center]: /images/20221120/3e70bdabb2514a0799f5faf1523950b2.png
[20201119182544518.png_pic_center]: /images/20221120/fbd02e754f624410afeed714f2cce6d3.png
[20201119182620565.png_pic_center]: /images/20221120/7031d5855eaf4472bfd741c29b084f51.png
[20201119182732209.png_pic_center]: /images/20221120/908a552df23343e094b2a98ca8b42b74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjQ2MDg0Mw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/2fcb10d1fe5e447bba31f1658d3162a0.png