JavaWeb：数据库设计、案例

1. • 数据库设计简介

1.1软件的研发步骤

需求分析-》设计-》编码-》测试-》安装部署

1.2数据库设计概念

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。

建立数据库中的表结构以及表与表之间的关联关系的过程。

有哪些表？表里有哪些字段？表和表之间有什么关系？

1.3数据库设计的步骤

需求分析（数据是什么?数据具有哪些属性?数据与属性的特点是什么)

逻辑分析（通过ER图(实体关系图)对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统)

物理设计（根据数据库自身的特点把逻辑设计转换为物理设计)

维护设计（1.对新的需求进行建表;2.表优化)

2. • 表关系之一对多

如：部门 和 员工

一个部门对应多个员工，一个员工对应一个部门

实现方式：在多的一方建立外键，指向一的一方的主键

3. • 表关系之多对多

如：商品 和 订单

一个商品对应多个订单，一个订单包含多个商品

实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

/*
    多对多：
        * 如：订单 和 商品
        * 一个商品对应多个订单，一个订单包含多个商品

    实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键


    
*/
-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;


-- 订单表
CREATE TABLE tb_order(
    id int primary key auto_increment,
    payment double(10,2),
    payment_type TINYINT,
    status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
    id int primary key auto_increment,
    title varchar(100),
    price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
    id int primary key auto_increment,
    order_id int,
    goods_id int,
    count int
);

-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

4. • 表关系之一对一

如： 用户 和 用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

实现方式:在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

5. • 数据库设计案例

分析

音乐专辑表名：Music 

| 字段名       | 数据类型      | 说明                               |
| ------------ | ------------- | ---------------------------------- |
| title        | varchar(32)   | 专辑名                             |
| alias        | varchar(32)   | 专辑别名                           |
| image        | varchar(64)   | 封面图片                           |
| style        | varchar(8)    | 流派（如经典、流行、民谣、电子等） |
| type         | varchar(4)    | 类型（专辑、单曲等）               |
| medium       | varchar(4)    | 介质（CD、黑胶、数字等）           |
| publish_time | date          | 发行时间                           |
| publisher    | varchar(16)   | 出版者                             |
| number       | tinyint       | 唱片数                             |
| barcode      | bigint        | 条形码                             |
| summary      | varchar(1024) | 简介                               |
| artist       | varchar(16)   | 艺术家                             |
| id           | int           | 编号（唯一）                       |

曲目表名： Song

| 字段名        | 数据类型    | 说明                |
| ------------- | ----------- | ------------------|
| name          | varchar(32) | 歌曲名            |
| serial_number | tinyint     | 歌曲序号           |
| id            | int         | 编号（唯一）       |



评论表名：Review

| 字段名      | 数据类型     | 说明                 |
| ----------- | ------------ | ------------------- |
| content     | varchar(256) | 评论内容             |
| rating      | tinyint      | 评分（1~5）          |
| review_time | datetime     | 评论时间             |

用户表名：User

| 字段名    | 数据类型    | 说明                                    |
| --------- | ----------- | --------------------------------------- |
| username  | varchar(16) | 用户名（唯一）                          |
| image     | varchar(64) | 用户头像图片地址                        |
| signature | varchar(64) | 个人签名，例如（万般各所是 一切皆圆满） |
| nickname  | varchar(16) | 用户昵称                                |
| id        | int         | 用户编号(主键)                           |

-- 删除表
DROP TABLE IF EXISTS Music;
DROP TABLE IF EXISTS Song;
DROP TABLE IF EXISTS Review;
DROP TABLE IF EXISTS User;
DROP TABLE IF EXISTS tb_music_user;

-- 音乐专辑表
CREATE TABLE Music(
    title varchar(32),-- 专辑名 
    alias varchar(32),-- 专辑别名 
    image varchar(64),-- 封面图片 
    style varchar(8),-- 流派（如经典、流行、民谣、电子等）
    type varchar(4),-- 类型（专辑、单曲等）
    medium varchar(4),-- 介质（CD、黑胶、数字等）
    publish_time date,-- 发行时间 
    publisher varchar(16),--  出版者  
    number tinyint,-- 唱片数
    barcode bigint,-- 条形码  
    summary varchar(1024),-- 简介 
    artist varchar(16),-- 艺术家 
    music_id int UNIQUE primary key auto_increment-- 编号（唯一） 
);

-- 曲目表
CREATE TABLE Song(
    name varchar(32),-- 歌曲名  
    serial_number tinyint ,-- 歌曲序号
    song_id int UNIQUE primary key auto_increment,-- 编号（唯一）

    -- 添加外键 song_id,关联 Music 表的music_id主键
    CONSTRAINT fk_song_music FOREIGN KEY(song_id) REFERENCES Music(music_id)
);

-- 评论表
CREATE TABLE Review(
    content varchar(256),-- 评论内容 
    rating   tinyint ,-- 评分（1~5）
    review_time datetime,-- 评论时间 
    review_id int UNIQUE primary key auto_increment,-- 编号（唯一）

    -- 添加外键 review_id,关联 Music 表的music_id主键
    CONSTRAINT fk_review_music FOREIGN KEY(review_id) REFERENCES Music(music_id),

    -- 添加外键 review_id,关联 User 表的user_id主键
    CONSTRAINT fk_review_user FOREIGN KEY(review_id) REFERENCES User(user_id)
);

-- 用户表
CREATE TABLE User(
    username varchar(16) UNIQUE,-- 用户名（唯一） 
    image varchar(64) ,-- 用户头像图片地址  
    signature  varchar(64),-- 个人签名，例如（万般各所是 一切皆圆满）
    nickname varchar(16),-- 用户昵称 
    user_id int primary key auto_increment-- 用户编号(主键) 
);

-- 专辑用户中间表
CREATE TABLE tb_music_user(
    id int primary key auto_increment,
    tb_music_id int,
    tb_user_id int
);

-- 建完表后，添加外键
alter table tb_music_user add CONSTRAINT fk_music_id FOREIGN key(tb_music_id) REFERENCES Music(music_id);
alter table tb_music_user add CONSTRAINT fk_user_id FOREIGN key(tb_user_id) REFERENCES User(user_id);