数据库设计-多表关系实现

#表关系(多对多)
-- 删除表
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);


#表关系(一对一）

drop table if exists tb_user;
drop table if exists tb_user_desc;
create table tb_user_desc
(
    id     int primary key auto_increment,
    city   varchar(20),
    edu    varchar(10),
    income int,
    status char(2),
    des    varchar(100)
);
create table tb_user
(
    id       int primary key auto_increment,
    photo    varchar(100),
    nickname varchar(50),
    age      int,
    gender   char(1),
    desc_id  int unique,
    constraint fk_tb_user_id foreign key (desc_id) references tb_user_desc(id)

);