PowerDesigner--快速创建表模型生成SQL语句

今天和大家分享一个我常用的设计表模型的工具PowserDesigner

- 选择物理模型

- 创建表、字段、索引等

- 梳理表之间的关系

- 一键生成SQL语句

目录

一、准备工作

二、选择物理模型

​三、创建表、字段等

3.1 创建表

3.2 创建字段

3.2.1 修改表名

3.2.2 设计表字段

四、梳理表之间的关系 

五、一键生成SQL语句

六、结束语

---

一、准备工作

下载并安装PowerDesigner工具， 链接将在文末给出。需要的自行下载。安装步骤不做详细介绍，没有什么需要注意的点。

二、选择物理模型

打开我们下载好的PD工具，选择文件--->新建模型---->Pyhsical Data---->ok创建我们的表模型。这里我选择是物理模型，可根据项目需求，选择不同的模型进行设计。如下图：

三、创建表、字段等

3.1 创建表

如下图：

3.2 创建字段

3.2.1 修改表名

3.2.2 设计表字段

Name没有什么条件限制，能明白自己这个字段是啥就行，Code用英文，数据类型根据字段设置即可，需要长度的设计值长度， 浮点类型的可以设置几位小数四舍五入。后面的P：主键，F：外键，M：强制。

创建好的效果如图：

四、梳理表之间的关系 

 表与表之间关系即为重要，下面就以我刚才创建的商品表、用户表、商家表这三者之间为例：

分析：

某用户注册进我们的平台，创建一个商家平台，在此商家平台中可以买很多商品。

某商家平台是被某个用户创建，且在此平台中存放着很多商品。

某商品被存放在某个商家平台，且这个商家平台是被某个用户创建。

分析完毕：

翻译成表逻辑为：

用户编号作为用户表中的主键，且作为商家平台的外键

商家编号作为商家表中的主键，且作为商品表中的外键

如下图：

场景：购买商品时

购买的是某用户创建的某商家下的商品，通过主外键关系找到对应数据。  

五、一键生成SQL语句

点击数据库--->生成数据库---->填写保存路径--->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