【JavaWeb】MySQL中篇

旧城等待， 2024-04-26 03:06 60阅读 0赞

> ? 本文由 [程序喵正在路上][Link 1] 原创，CSDN首发！  
> ? 系列专栏：[JavaWeb开发][JavaWeb]  
> ? 首发时间：2024年3月4日  
> ? 欢迎关注?点赞?收藏?留言?

#### 目录 ####

*  数据库操作-DQL
 *   *  基本查询
     *  条件查询
     *  聚合函数
     *  分组查询
     *  排序查询
     *  分页查询
     *  案例
 *  多表设计
 *   *  一对多
     *  一对多-外键
     *  一对一&多对多
     *  案例
     *   *  关系分析
         *  表结构

## 数据库操作-DQL ##

*  DQL英文全称是 Data Query Language (数据查询语言)，用来查询数据库表中的记录。
 *  关键字：`SELECT`  
    ![在这里插入图片描述][72708a6ab89a4161a2a7c14575fb785b.png]

首先，我们准备一下后面要进行查询的环境，新建数据库 db02，新建 console mysql\_02 并让其使用数据库 db02：

![在这里插入图片描述][90c79c5fa4cf41e6b95a225654bdafba.png]

复制下列语句到 console 中并选中执行：

-- 员工管理(带约束)
    create table tb_emp (
        id int unsigned primary key auto_increment comment 'ID',
        username varchar(20) not null unique comment '用户名',
        password varchar(32) default '123456' comment '密码',
        name varchar(10) not null comment '姓名',
        gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
        image varchar(300) comment '图像',
        job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
        entrydate date comment '入职时间',
        create_time datetime not null comment '创建时间',
        update_time datetime not null comment '修改时间'
    ) comment '员工表';
    
    -- 准备测试数据
    INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES
        (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
        (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
        (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
        (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
        (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
        (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
        (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
        (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
        (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
        (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
        (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
        (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
        (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
        (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
        (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
        (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
        (17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
        (18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
        (19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
        (20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
        (21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
        (22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
        (23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
        (24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
        (25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
        (26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
        (27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
        (28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
        (29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');

![在这里插入图片描述][eb1d6c5befe8402e94f952f30fe8e07c.png]

### 基本查询 ###

*  查询多个字段：`select 字段1, 字段2, 字段3 from 表名;`
 *  查询所有字段（通配符）：`select * from 表名;`
 *  设置别名：`select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;`
 *  去除重复记录：`select distinct 字段列表 from 表名;`

示例：

1.  查询指定字段 name,entrydate 并返回
    
        select name,entrydate from tb_emp;
2.  查询返回所有字段
    
        -- 推荐
        select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
        
        -- 不推荐（不直观，性能低）
        select * from tb_emp;
3.  查询所有员工的 name,entrydate 并起别名（姓名、入职日期）
    
        select name as 姓名,entrydate as 入职日期 from tb_emp;
        
        -- as 可省略
        select name 姓名,entrydate 入职日期 from tb_emp;
        
        -- 别名中有特殊符号中，需要给别名加上引号
        select name '姓 名',entrydate 入职日期 from tb_emp;
4.  查询已有的员工关联了哪几种职位（不要重复）
    
        select distinct job from tb_emp;

注意事项

*  `*` 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

### 条件查询 ###

*  条件查询：`select 字段列表 from 表名 where 条件列表;`
    
    ![在这里插入图片描述][7723ea15414b45e097fd0a2f63c0045a.png]

示例：

1.  查询姓名为杨逍的员工
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where name = '杨逍';
2.  查询 id 小于等于 5 的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where id <= 5;
3.  查询没有分配职位的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where job is null;
4.  查询有职位的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where job is not null;
5.  查询密码不等于 ‘123456’ 的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where password != '123456';
6.  查询入职日期在 ‘2000-01-01’ （包含）到 ‘2010-01-01’ （包含）之间的员工信息
    
        -- 方式一
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
        
        -- 方式二
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where entrydate between '2000-01-01' and '2010-01-01';
7.  查询入职日期在 ‘2000-01-01’ （包含）到 ‘2010-01-01’ （包含）之间且性别为女的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where entrydate between '2000-01-01' and '2010-01-01'
          and gender = 2;
8.  查询职位是 2（讲师），3（学工主管），4（教研主管）的员工信息
    
        -- 方式一
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where job = 2 or job = 3 or job = 4;
        
        -- 方式二
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where job in (2,3,4);
9.  查询姓名为两个字的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where name like '__';
10. 查询姓 ‘张’ 的员工信息
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        where name like '张%';

### 聚合函数 ###

*  介绍：将一列数据作为一个整体，进行纵向计算
 *  语法：`select 聚合函数(字段列表) from 表名;`
    
    ![在这里插入图片描述][adab1c02bd184f88a2a2f8cb0fc7f755.png]

示例

1.  统计该企业员工数据
    
        -- count有3种用法
        -- 1. count(非空字段)
        select count(id) from tb_emp;
        select count(gender) from tb_emp;
        
        -- 2. count(不为null的常量)
        select count(0) from tb_emp;
        select count('a') from tb_emp;
        
        -- 3. count(*) 推荐
        select count(*) from tb_emp;
2.  统计该企业最早入职的员工
    
        select min(entrydate) from tb_emp;
3.  统计该企业最迟入职的员工
    
        select max(entrydate) from tb_emp;
4.  统计该企业员工 ID 的平均值
    
        select avg(id) from tb_emp;
5.  统计该企业员工的 ID 之和
    
        select sum(id) from tb_emp;

注意事项

*  null 值不参与所有聚合函数运算
 *  统计数量可以使用：`count(*)` `count(字段)` `count(常量)`，推荐使用 `count(*)`

### 分组查询 ###

*  分组查询：`select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];`

**where与having区别**

*  执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
 *  判断条件不同：where不能对聚合函数进行判断，而having可以

示例：

1.  根据性别分组，统计男性和女性员工的数量
    
        select gender,count(*) from tb_emp group by gender;
2.  先查询入职时间在 ‘2015-01-01’ （包含）以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
    
        select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

注意事项

*  分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
 *  执行顺序: where > 聚合函数 > having

### 排序查询 ###

*  排序查询：`select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;`
 *  ASC：升序（默认值）
 *  DESC：降序

示例：

1.  根据入职时间，对员工进行排序
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        order by entrydate asc;
        
        -- asc可不写
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        order by entrydate;
2.  根据入职时间，对员工进行降序排序
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        order by entrydate desc;
3.  根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        order by entrydate, create_time desc;

注意事项

*  如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

### 分页查询 ###

*  分页查询：`select 字段列表 from 表名 limit 起始索引, 查询记录数;`

示例：

1.  从起始索引0开始查询员工数据，每页展示5条记录
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        limit 0,5;
2.  查询第1页员工数据，每页展示5条记录
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        limit 0,5;
3.  查询第2页员工数据，每页展示5条记录
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        limit 5,5;
4.  查询第3页员工数据，每页展示5条记录
    
        select id,
               username,
               password,
               name,
               gender,
               image,
               job,
               entrydate,
               create_time,
               update_time
        from tb_emp
        limit 10,5;

注意事项

*  起始索引从0开始，`起始索引 = （查询页码 - 1）* 每页显示记录数`
 *  分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
 *  如果查询的是第一页数据，起始索引可以省略，直接简写为 `limit 10`

### 案例 ###

案例1：按需求完成员工管理的条件分页查询 —— 根据输入条件，查询第一页数据，每页展示10条记录

![在这里插入图片描述][59da09f87aab45e1a8ebeaa265a67691.png]

输入条件：

*  姓名：张
 *  性别：男
 *  入职时间：2000-01-01 2015-12-31

select id,
           username,
           password,
           name,
           gender,
           image,
           job,
           entrydate,
           create_time,
           update_time
    from tb_emp
    where name like '%张%'
      and gender = 1
      and entrydate between '2000-01-01' and '2015-12-31'
    order by update_time desc
    limit 0,10;

提示：按住 Ctrl \+ Alt \+ L 可以对代码快速进行格式化

案例2-1：根据需求，完成员工性别信息的统计

select if(gender = 1, '男性员工', '女性员工') 性别, count(*) 数量
    from tb_emp
    group by gender;

![在这里插入图片描述][7c2c0e662c0149ec8d2faec2c6de2f10.png]

案例2-2：根据需求，完成员工职位信息的统计

select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位,
           count(*)                                                                                             数量
    from tb_emp
    group by job;

![在这里插入图片描述][5c358f4e45cd49a382787d8b30ef1a9a.png]

流程控制函数：

*  `if(表达式, tvalue, fvalue)`：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
 *  `case expr when value1 then result1 [when value2 then value2 ...] [else result] end`

## 多表设计 ##

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

*  一对多 (多对一)
 *  多对多
 *  一对一

### 一对多 ###

*  一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键。

案例：

根据页面原型及需求文档，完成部门及员工模块的表结构设计。

前面，我们设计了员工表，但是没有设计归属部分的部分，下面我们将设计部分表并完善员工表

在前面给出的页面原型中，我们点击部门管理栏目，可以看到对部分表的需求：

![在这里插入图片描述][bc3c51a3935f483792ee57038ba61c9a.png]

新建数据库 db03，新建 console mysql\_02\_多表设计，先在 db02 中找到员工表 tb\_emp 的创建语句，将其复制到 mysql\_02\_多表设计.sql 中，再进行部门表的设计：

右键员工表，选择 Edit Source 可以看到其创建语句：

![在这里插入图片描述][588f6df70fcb45a9b299be7f443e4617.png]

![在这里插入图片描述][5047240eb19041aeb69f38da0b10bb7a.png]

设计部门表，完善员工表：

-- 员工表
    create table tb_emp
    (
        id          int unsigned auto_increment comment 'ID'
            primary key,
        username    varchar(20)                  not null comment '用户名',
        password    varchar(32) default '123456' null comment '密码',
        name        varchar(10)                  not null comment '姓名',
        gender      tinyint unsigned             not null comment '性别, 说明: 1 男, 2 女',
        image       varchar(300)                 null comment '图像',
        job         tinyint unsigned             null comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
        entrydate   date                         null comment '入职时间',
        dept_id     int unsigned comment '部门ID',
        create_time datetime                     not null comment '创建时间',
        update_time datetime                     not null comment '修改时间',
        constraint username
            unique (username)
    )
        comment '员工表';
    
    -- 部门表
    create table tb_dept
    (
        id          int unsigned primary key auto_increment comment '主键ID',
        name        varchar(10) not null unique comment '部门名称',
        create_time datetime    not null comment '创建时间',
        update_time datetime    not null comment '修改时间'
    ) comment '部门表';

选中右键执行，即可创建两张表

### 一对多-外键 ###

在前面创建的员工表中，我们来插入一些数据，方便后面的测试：

insert into tb_dept (id, name, create_time, update_time) values
    (1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
    (4,'就业部',now(),now()),(5,'人事部',now(),now());
    
    insert into tb_emp
    (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
    (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
    (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
    (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
    (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
    (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
    (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
    (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
    (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
    (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
    (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
    (11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()),
    (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()),
    (13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()),
    (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
    (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
    (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
    (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

插入数据后，我们发现，当我们删除部门表中的一个部门后，员工中归属于该部门的员工依然存在，这肯定是不合理的，此时就出现了数据的不完整、不一致问题

分析：

*  目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的
 *  我们需要用外键来约束两张表

外键语法：

-- 方式一：创建表时指定
    create table 表名(
    	字段名    数据类型,
    	...
    	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (字段名)	
    );
    
    -- 方式二：建完表后，添加外键
    alter table  表名  add constraint  外键名称  foreign key (外键字段名) references  主表(字段名);

右键员工表，选择 Modify Table：

![在这里插入图片描述][22b9ba016ea647e58a8765bc614b32ab.png]

添加外键：

![在这里插入图片描述][f3305981f6cf4101ad1607b973c21ccd.png]

**外键约束**

![在这里插入图片描述][b377114516424d7fb75dea503c5963de.png]

### 一对一&多对多 ###

**一对一**

*  案例: 用户与身份证信息的关系
 *  关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
 *  实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的 (UNIQUE)

![在这里插入图片描述][71d3f2a97a3d4554981885e490c21a1f.png]

代码实现：

-- ===========================================一对一=====================================
    create table tb_user
    (
        id     int unsigned primary key auto_increment comment 'ID',
        name   varchar(10)      not null comment '姓名',
        gender tinyint unsigned not null comment '性别, 1 男  2 女',
        phone  char(11) comment '手机号',
        degree varchar(10) comment '学历'
    ) comment '用户信息表';
    
    insert into tb_user
    values (1, '白眉鹰王', 1, '18812340001', '初中'),
           (2, '青翼蝠王', 1, '18812340002', '大专'),
           (3, '金毛狮王', 1, '18812340003', '初中'),
           (4, '紫衫龙王', 2, '18812340004', '硕士');
    
    
    create table tb_user_card
    (
        id           int unsigned primary key auto_increment comment 'ID',
        nationality  varchar(10)  not null comment '民族',
        birthday     date         not null comment '生日',
        idcard       char(18)     not null comment '身份证号',
        issued       varchar(20)  not null comment '签发机关',
        expire_begin date         not null comment '有效期限-开始',
        expire_end   date comment '有效期限-结束',
        user_id      int unsigned not null unique comment '用户ID',
        constraint fk_user_id foreign key (user_id) references tb_user (id)
    ) comment '用户信息表';
    
    insert into tb_user_card
    values (1, '汉', '1960-11-06', '100000100000100001', '朝阳区公安局', '2000-06-10', null, 1),
           (2, '汉', '1971-11-06', '100000100000100002', '静安区公安局', '2005-06-10', '2025-06-10', 2),
           (3, '汉', '1963-11-06', '100000100000100003', '昌平区公安局', '2006-06-10', null, 3),
           (4, '回', '1980-11-06', '100000100000100004', '海淀区公安局', '2008-06-10', '2028-06-10', 4);

**多对多**

*  案例: 学生与课程的关系
 *  关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
 *  实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键  
    ![在这里插入图片描述][70c5da6388934296bfda54d7bdcf2a3b.png]

代码实现：

--  ======================================多对多=============================
    create table tb_student
    (
        id   int auto_increment primary key comment '主键ID',
        name varchar(10) comment '姓名',
        no   varchar(10) comment '学号'
    ) comment '学生表';
    insert into tb_student(name, no)
    values ('黛绮丝', '2000100101'),
           ('谢逊', '2000100102'),
           ('殷天正', '2000100103'),
           ('韦一笑', '2000100104');
    
    
    create table tb_course
    (
        id   int auto_increment primary key comment '主键ID',
        name varchar(10) comment '课程名称'
    ) comment '课程表';
    insert into tb_course (name)
    values ('Java'),
           ('PHP'),
           ('MySQL'),
           ('Hadoop');
    
    
    create table tb_student_course
    (
        id         int auto_increment comment '主键' primary key,
        student_id int not null comment '学生ID',
        course_id  int not null comment '课程ID',
        constraint fk_courseid foreign key (course_id) references tb_course (id),
        constraint fk_studentid foreign key (student_id) references tb_student (id)
    ) comment '学生课程中间表';
    
    insert into tb_student_course(student_id, course_id)
    values (1, 1),
           (1, 2),
           (1, 3),
           (2, 2),
           (2, 3),
           (3, 4);

### 案例 ###

根据页面原型，设计分类管理、菜品管理、套餐管理模块的表结构，给出的页面原型如下

分类管理：

![在这里插入图片描述][4c0918d6e29d41819bd331f55ce840f0.png]

菜品管理：

![在这里插入图片描述][4402d9e3815443319f1c1f26992df0a9.png]

套餐管理：

![在这里插入图片描述][b998d24149124269bc9929f62b92627f.png]

#### 关系分析 ####

*  阅读页面原型及需求文档，分析各个模块涉及到的表结构，及表结构之间的关系

从页面原型中，我们可以知道至少有分类表、菜品表和套餐表这3个表，然后我们来分析它们之间的关系。

一个分类下有多个菜系，每个菜系又包含若干个菜品，所以分类表和菜品表是一对多的关系；一个分类下有多个套餐，所以分类表和套餐表是一对多的关系；因此我们需要分别在菜品表和套餐表创建一个分类表的外键。

一个套餐是由若干个菜品组成，一个菜品又可以包含在不同的套餐当中，所以菜品表和套餐表是多对多的关系。因此我们需要创建一个套餐菜品关系表来将这两个表关联起来。

![在这里插入图片描述][31c1361040144e3a91d1e5d92076fe3a.png]

#### 表结构 ####

*  根据页面原型及需求文档，分析各个表结构中具体的字段及约束

创建表依旧还是使用图形化界面的方式，这里为了方便，我给出了创建的语句

分类表的需求：

![在这里插入图片描述][dcaeebd7d53846e99c74ec3b4b5d3042.png]

分类表的创建：

create table category
    (
        id          int unsigned auto_increment comment '主键ID'
            primary key,
        name        varchar(20)                  not null comment '分类名称',
        type        tinyint unsigned             not null comment '类型：1 菜品分类，2 套餐分类',
        sort        tinyint unsigned             not null comment '排序字段',
        status      tinyint unsigned default '0' not null comment '状态：0 停用，1 启用',
        create_time datetime                     not null comment '创建时间',
        update_time datetime                     not null comment '修改时间',
        constraint category_name_uindex
            unique (name)
    )
        comment '分类表';

菜品表的需求：

![在这里插入图片描述][52178da6437d42ce82ea4afbe9e2645a.png]

菜品表的创建：

create table dish
    (
        id          int unsigned auto_increment comment '主键ID'
            primary key,
        name        varchar(20)                  not null comment '菜品名称',
        category_id int unsigned                 not null comment '分类ID',
        price       decimal(8, 2)                not null comment '价格',
        image       varchar(300)                 not null comment '图片url',
        description varchar(200)                 null comment '菜品描述',
        status      tinyint unsigned default '0' not null comment '售卖状态：0 停售，1 启售',
        create_time datetime                     not null comment '创建时间',
        update_time datetime                     not null comment '修改时间',
        constraint dish_name_uindex
            unique (name)
    )
        comment '菜品表';

套餐表的需求：

![在这里插入图片描述][5936f61582134f8e96208a81bed617e2.png]

套餐表的创建：

create table setmeal
    (
        id          int unsigned auto_increment comment '主键ID'
            primary key,
        name        varchar(20)                  not null comment '套餐名称',
        category_id int unsigned                 not null comment '套餐分类ID',
        price       decimal(8, 2)                not null comment '价格',
        image       varchar(300)                 not null comment '图片url',
        description varchar(200)                 null comment '套餐描述',
        status      tinyint unsigned default '0' not null comment '售卖状态：0 停售，1 启售',
        create_time datetime                     not null comment '创建时间',
        update_time datetime                     not null comment '修改时间',
        constraint setmeal_name_uindex
            unique (name)
    )
        comment '套餐表';

套餐菜品关系表的创建：

create table setmeal_dish
    (
        id         int unsigned auto_increment comment '主键ID'
            primary key,
        setmeal_id int unsigned     not null comment '套餐ID',
        dish_id    int unsigned     not null comment '菜品ID',
        copies     tinyint unsigned not null comment '菜品的份数'
    )
        comment '套餐菜品关系表';

[Link 1]: https://blog.csdn.net/weixin_62511863?spm=1011.2421.3001.5343
[JavaWeb]: https://blog.csdn.net/weixin_62511863/category_12511257.html?spm=1001.2014.3001.5482
[72708a6ab89a4161a2a7c14575fb785b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/b90ff390e1e743c0baaa7c33cf0cac6c.png
[90c79c5fa4cf41e6b95a225654bdafba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/4e4f4edac69241b7bd8468a397e04ecb.png
[eb1d6c5befe8402e94f952f30fe8e07c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/1f66f469d13e4b58bcf0b58736c7e8a8.png
[7723ea15414b45e097fd0a2f63c0045a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/6686377ea3084b63a2bc1639226ca092.png
[adab1c02bd184f88a2a2f8cb0fc7f755.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/eaafad8846714d5b90730cd5ec7b5f4e.png
[59da09f87aab45e1a8ebeaa265a67691.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/88b14d0c28204d9aadbce4c571c6b82f.png
[7c2c0e662c0149ec8d2faec2c6de2f10.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/220292c0aef042a5b96f0540646782fd.png
[5c358f4e45cd49a382787d8b30ef1a9a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/779211925d8e422f90e441749e6f4845.png
[bc3c51a3935f483792ee57038ba61c9a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/a78d462e203c4cd9b22537e946bf94ac.png
[588f6df70fcb45a9b299be7f443e4617.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/5c7f477d1b104286b11d2d70c964115c.png
[5047240eb19041aeb69f38da0b10bb7a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/b66d7b987157437f9b5076474ac7204a.png
[22b9ba016ea647e58a8765bc614b32ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/9b8b2a9b677e46fdb8ec94bea6c132e9.png
[f3305981f6cf4101ad1607b973c21ccd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/6034ec93805743899185053a6bfb52a1.png
[b377114516424d7fb75dea503c5963de.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/41484d8dbbac4620b9edf26030d0a157.png
[71d3f2a97a3d4554981885e490c21a1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/d0d59ec640084ae998d066938da93b4a.png
[70c5da6388934296bfda54d7bdcf2a3b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/95cd185bf1274c6b8b5bc0c9b6ad3c62.png
[4c0918d6e29d41819bd331f55ce840f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/273813f48d6249389f087e3ea84c18c0.png
[4402d9e3815443319f1c1f26992df0a9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/9ba30a5c7f464edc9056f063ececbd00.png
[b998d24149124269bc9929f62b92627f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/efd0dc02fbea47bfb7c98925093f99a6.png
[31c1361040144e3a91d1e5d92076fe3a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/d9d90b3bcc9f4511a3f3b6e85a6d9ac7.png
[dcaeebd7d53846e99c74ec3b4b5d3042.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/ea798fdfc084474292e6ed08faad0fe2.png
[52178da6437d42ce82ea4afbe9e2645a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/629df594d65e4ef59d41ee72e7dad1a9.png
[5936f61582134f8e96208a81bed617e2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/26/4304065bf6464e428ee1c4689fcaae0d.png