今天和大家分享一个我常用的设计表模型的工具PowserDesigner
- 选择物理模型
- 创建表、字段、索引等
- 梳理表之间的关系
- 一键生成SQL语句
目录
一、准备工作
二、选择物理模型
三、创建表、字段等
3.1 创建表
3.2 创建字段
3.2.1 修改表名
3.2.2 设计表字段
四、梳理表之间的关系
五、一键生成SQL语句
六、结束语
下载并安装PowerDesigner工具， 链接将在文末给出。需要的自行下载。安装步骤不做详细介绍，没有什么需要注意的点。
打开我们下载好的PD工具，选择文件--->新建模型---->Pyhsical Data---->ok创建我们的表模型。这里我选择是物理模型，可根据项目需求，选择不同的模型进行设计。如下图：
三、创建表、字段等 如下图：
Name没有什么条件限制，能明白自己这个字段是啥就行，Code用英文，数据类型根据字段设置即可，需要长度的设计值长度， 浮点类型的可以设置几位小数四舍五入。后面的P：主键，F：外键，M：强制。
创建好的效果如图：
表与表之间关系即为重要，下面就以我刚才创建的商品表、用户表、商家表这三者之间为例：
分析：
某用户注册进我们的平台，创建一个商家平台，在此商家平台中可以买很多商品。
某商家平台是被某个用户创建，且在此平台中存放着很多商品。
某商品被存放在某个商家平台，且这个商家平台是被某个用户创建。
分析完毕：
翻译成表逻辑为：
用户编号作为用户表中的主键，且作为商家平台的外键
商家编号作为商家表中的主键，且作为商品表中的外键
如下图：
场景：购买商品时
购买的是某用户创建的某商家下的商品，通过主外键关系找到对应数据。
点击数据库--->生成数据库---->填写保存路径--->ok
以我写过的项目模型为例：
生成的SQL语句如下：
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2022/6/19 14:47:48 */
/*==============================================================*/
drop table if exists department;
drop table if exists tb_activity;
drop table if exists tb_apply;
drop table if exists tb_cate;
drop table if exists tb_credit;
drop table if exists tb_evaluate;
drop table if exists tb_feedback;
drop table if exists tb_introduction;
drop table if exists tb_meeting;
drop table if exists tb_notice;
drop table if exists tb_noticecate;
drop table if exists tb_punishment;
drop table if exists tb_registration;
drop table if exists tb_reward;
drop table if exists tb_role;
drop table if exists tb_student;
drop table if exists tb_summary;
drop table if exists tb_system;
drop table if exists tb_university;
/*==============================================================*/
/* Table: department */
/*==============================================================*/
create table department
(
department_id integer not null auto_increment,
name varchar(20),
duty varchar(20),
primary key (department_id)
);
/*==============================================================*/
/* Table: tb_activity */
/*==============================================================*/
create table tb_activity
(
activity_id integer not null auto_increment,
cate_id integer,
name varchar(20),
content varchar(500),
plan text,
time varchar(20),
primary key (activity_id)
);
/*==============================================================*/
/* Table: tb_apply */
/*==============================================================*/
create table tb_apply
(
apply_id integer not null auto_increment,
department_id integer,
student_id integer,
name varchar(20),
major varchar(50),
grade varchar(20),
class varchar(20),
content varchar(50),
primary key (apply_id)
);
/*==============================================================*/
/* Table: tb_cate */
/*==============================================================*/
create table tb_cate
(
cate_id integer not null auto_increment,
name varchar(20),
primary key (cate_id)
);
/*==============================================================*/
/* Table: tb_credit */
/*==============================================================*/
create table tb_credit
(
credit_id integer not null auto_increment,
student_id integer,
reward_id integer,
punishment_id integer,
registration_id integer,
credit varchar(5),
primary key (credit_id)
);
/*==============================================================*/
/* Table: tb_evaluate */
/*==============================================================*/
create table tb_evaluate
(
evaluate_id integer not null auto_increment,
student_id integer,
tb__student_id integer,
content varchar(100),
primary key (evaluate_id)
);
/*==============================================================*/
/* Table: tb_feedback */
/*==============================================================*/
create table tb_feedback
(
feedback_id integer not null auto_increment,
student_id integer,
content varchar(100),
primary key (feedback_id)
);
/*==============================================================*/
/* Table: tb_introduction */
/*==============================================================*/
create table tb_introduction
(
introduction_id integer not null auto_increment,
university_id integer,
title varchar(20),
content text,
primary key (introduction_id)
);
/*==============================================================*/
/* Table: tb_meeting */
/*==============================================================*/
create table tb_meeting
(
meeting_id integer not null auto_increment,
student_id integer,
title varchar(20),
content varchar(200),
primary key (meeting_id)
);
/*==============================================================*/
/* Table: tb_notice */
/*==============================================================*/
create table tb_notice
(
notice_id integer not null auto_increment,
student_id integer,
notice_name varchar(20),
content varchar(500),
primary key (notice_id)
);
/*==============================================================*/
/* Table: tb_noticecate */
/*==============================================================*/
create table tb_noticecate
(
cate_id integer not null auto_increment,
notice_id integer,
cate_name varchar(20),
primary key (cate_id)
);
/*==============================================================*/
/* Table: tb_punishment */
/*==============================================================*/
create table tb_punishment
(
punishment_id integer not null auto_increment,
student_id integer,
content varchar(50),
primary key (punishment_id)
);
/*==============================================================*/
/* Table: tb_registration */
/*==============================================================*/
create table tb_registration
(
registration_id integer not null auto_increment,
activity_id integer,
student_id integer,
name varchar(10),
class varchar(20),
sn varchar(20),
primary key (registration_id)
);
/*==============================================================*/
/* Table: tb_reward */
/*==============================================================*/
create table tb_reward
(
reward_id integer not null auto_increment,
student_id integer,
activity_id integer,
time varchar(5),
prize varchar(20),
primary key (reward_id)
);
/*==============================================================*/
/* Table: tb_role */
/*==============================================================*/
create table tb_role
(
role_id integer not null auto_increment,
name varc(10),
primary key (role_id)
);
/*==============================================================*/
/* Table: tb_student */
/*==============================================================*/
create table tb_student
(
student_id integer not null auto_increment,
role_id integer,
department_id integer,
university_id integer,
student_sn varchar(20),
pwd varchar(20),
name varchar(10),
sex varchar(1),
major varchar(50),
grade varchar(20),
class varchar(50),
phone varchar(11),
primary key (student_id)
);
/*==============================================================*/
/* Table: tb_summary */
/*==============================================================*/
create table tb_summary
(
summary_id integer not null auto_increment,
student_id integer,
summary_content varchar(300),
summary_time datetime,
primary key (summary_id)
);
/*==============================================================*/
/* Table: tb_system */
/*==============================================================*/
create table tb_system
(
system_id integer not null auto_increment,
university_id integer,
name varchar(20),
content text,
create_time varchar(20),
update_time varchar(20),
primary key (system_id)
);
/*==============================================================*/
/* Table: tb_university */
/*==============================================================*/
create table tb_university
(
university_id integer not null,
role_id integer,
name varchar(30),
phone varchar(20),
pwd varchar(50),
primary key (university_id)
);
alter table tb_activity add constraint FK_Reference_11 foreign key (cate_id)
references tb_cate (cate_id) on delete restrict on update restrict;
alter table tb_apply add constraint FK_Reference_10 foreign key (department_id)
references department (department_id) on delete restrict on update restrict;
alter table tb_apply add constraint FK_Reference_29 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_credit add constraint FK_Reference_20 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_credit add constraint FK_Reference_23 foreign key (reward_id)
references tb_reward (reward_id) on delete restrict on update restrict;
alter table tb_credit add constraint FK_Reference_24 foreign key (punishment_id)
references tb_punishment (punishment_id) on delete restrict on update restrict;
alter table tb_credit add constraint FK_Reference_25 foreign key (registration_id)
references tb_registration (registration_id) on delete restrict on update restrict;
alter table tb_evaluate add constraint FK_Reference_12 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_evaluate add constraint FK_Reference_13 foreign key (tb__student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_feedback add constraint FK_Reference_18 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_introduction add constraint FK_Reference_28 foreign key (university_id)
references tb_university (university_id) on delete restrict on update restrict;
alter table tb_meeting add constraint FK_Reference_19 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_notice add constraint FK_Reference_8 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_noticecate add constraint FK_Reference_26 foreign key (notice_id)
references tb_notice (notice_id) on delete restrict on update restrict;
alter table tb_punishment add constraint FK_Reference_17 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_registration add constraint FK_Reference_21 foreign key (activity_id)
references tb_activity (activity_id) on delete restrict on update restrict;
alter table tb_registration add constraint FK_Reference_22 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_reward add constraint FK_Reference_15 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_reward add constraint FK_Reference_16 foreign key (activity_id)
references tb_activity (activity_id) on delete restrict on update restrict;
alter table tb_student add constraint FK_Reference_1 foreign key (role_id)
references tb_role (role_id) on delete restrict on update restrict;
alter table tb_student add constraint FK_Reference_2 foreign key (department_id)
references department (department_id) on delete restrict on update restrict;
alter table tb_student add constraint FK_Reference_3 foreign key (university_id)
references tb_university (university_id) on delete restrict on update restrict;
alter table tb_summary add constraint FK_Reference_14 foreign key (student_id)
references tb_student (student_id) on delete restrict on update restrict;
alter table tb_system add constraint FK_Reference_27 foreign key (university_id)
references tb_university (university_id) on delete restrict on update restrict;
alter table tb_university add constraint FK_Reference_4 foreign key (role_id)
references tb_role (role_id) on delete restrict on update restrict;
有了这些SQL语句之后，我们就可以在我们的编译器上面通过SQL语句，生成我们的表模型。更加方便快捷咯。
PD的基础使用方式就是这样额，感兴趣的朋友还可以去研究一下其他的模型用法，设计出不用的软件项目。喜欢的三连支持。
PD安装包：
链接：https://pan.baidu.com/s/1Kfm9BLzLAm_fmrAJVfzQ0A
提取码：17h8