mysql ddl 转pgsql 建表 在线工具

了解SQL

MySQL Workbench中能够可视化地创建数据库和数据库表，这些可视化的操作实际上最终都转换成SQL(结构化查询语言，Structured Query Language)语句提交给MySQL数据库系统。

SQL是关系型数据库管理系统的标准语言，基于SQL可以创建表的内部结构与关联关系，并对表数据进行各种操作。

现在的Web开发一般都引入了各种ORM(Object Relation Mapping)框架。通过ORM框架，我们可以基本屏蔽SQL的相关信息，直接通过面向对象的方式对数据库进行操作，ORM框架会自动生成对应的SQL语句(这正是Mapping的意思)。但是熟练掌握SQL依然是每个Java Web程序员的必备技能，因为：

由框架生成的SQL语句往往不是最高效的，当我们遇到效率上的瓶颈时，自己重写SQL语句常常是有效的解决方案。

开发时可能会遇到一些SQL相关的问题，如果不了解SQL的话可能就会在调试中卡住。熟悉SQL后就能够更容易的发现数据库相关的错误。例如from是SQL的一个关键字，如果我们将为某个对象的属性名取名为from，此时直接运行ORM框架为我们生成的SQL语句就可能会产生错误。如果了解SQL的话看到错误就能很快的发现问题并解决。

总而言之，如果你想成为一个优秀的Java Web开发者，学习SQL是非常必要的。

SQL的核心内容

SQL主要包含两部分内容：

数据定义语言(Data Definition Language, DDL)

负责数据结构定义与数据库对象定义的语言。新建、更改或者删除数据库表等对数据库表的操作都属于数据定义语言的范围。

数据操纵语言 (Data Manipulation Language, DML)

负责对数据库对象运行数据访问工作，包括查询、插入、更新与删除数据，是进行数据操纵时与数据库系统交互的指令。

这一节我们先来了解数据定义语言DDL，主要涉及到CREATE,ALTER,DROP三种命令。

创建数据库

我们首先打开命令行，输入以下命令登录MySQL Server：

mysql -u root -p

然后创建一个数据库作为测试数据库：

CREATE DATABASE `tianmayingblog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

在上面的SQL语句中，我们创建了一个名为tianmayingblog的数据库。在创建数据库的过程中我们需要注意，字符编码的设置一直是容易忽略的地方，如果数据库的字符编码和程序的字符编码不一致，就会出现乱码问题。在涉及到字符编码的地方，都要使用统一的字符编码，这里使用UTF8。

创建数据库表

在tianmayingblog数据库中创建user表，user表应该包含以下信息：

字段名

Java数据类型

MySQL数据类型

id

long

数字类型，对于长整形一般我们使用int(11)作为数据类型

username

String

文本类型，username属于可变长的字符串，我们选择varchar(255)作为其数据类型

password

String

文本类型，varchar(255)

avatar

String

文本类型，varchar(255)

email

String

文本类型，varchar(255)

description

String

文本类型，也属于可边长字符串，但由于其数据量可能较大，我们选择LONGTEXT作为其数据类型

确定字段以及对应的数据类型后，我们就可以通过SQL语句来创建表了：

use `tianmayingblog`; //进入tianmayingblog数据库
CREATE TABLE `user` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`username`varchar(255) NOT NULL,
`password`varchar(255) NOT NULL,
`avatar`varchar(255) NOT NULL,
`email`varchar(255) NOT NULL,
`description`longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里将id作为user表的主键，为其声明自增属性。将来插入数据时若我们未指定id字段的值，MySQL会自动为我们生成id的值，其值为上一条记录的id + 1。

在CREATE TABLE语句的最后一行，为MySQL设置了字符编码集。再次强调，每次创建数据库表时，指定其字符编码为UTF8。

我们通过ENGINE=InnoDB选择了InnoDB作为我们的存储引擎。MySQL提供了10种存储引擎(MyISAM、InnoDB、MERGE、MEMORY、BDB、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE)，每种不同的存储引擎都有不一样的特性，我们一般选择InnoDB作为我们的存储引擎，它提供了事务、行级锁机制和外键约束的功能。大家如果对其他引擎感兴趣，可以了解MySQL存储引擎。

建立外键约束

在数据库表之间的关系一节中我们知道，post表通过外键建立了与user表之间的一对多关系，现在让我们在MySQL中建立post表，并与user表建立外键约束：

CREATE TABLE `post` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`title`varchar(255) NOT NULL,
`content`longtext NOT NULL,
`creator` int(11) DEFAULT NULL,
`createdTime`datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `creator`(`creator`),
CONSTRAINT `post_ibfk_1`FOREIGN KEY (`creator`) REFERENCES `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CONSTRAINTpost_ibfk_1FOREIGN KEY (creator) REFERENCESuser(id)就是用于创建外键的关键语句。你也可以通过MySQL Workbench可视化地为一个表的属性建立外键约束。

创建完成后，我们尝试往post表插入一条数据，MySQL会抛出1452错误：

mysql> insert into `post`(title, content, creator, createdTime) values ("test", "test", 1, "2016-01-01 00:00:00");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tianmayingblog`.`post`, CONSTRAINT `post_ibfk_1` FOREIGN KEY (`creator`) REFERENCES `user` (`id`))

只有当数据表user中存在id为1的数据才能插入成功：

mysql> insert into `user`(username, password, avatar, email, description) values("David", "pwd", "avatar", "[e-mail]", "David's blog");
Query OK, 1 row affected (0.01 sec)
mysql> insert into `post`(title, content, creator, createdTime) values ("test", "test", 1, "2016-01-01 00:00:00");
Query OK, 1 row affected (0.00 sec)

更改数据库表

创建完user表后，我们发现我们并没有为用户添加注册时间这一字段，而注册时间又是用户一项特别重要的信息。此时user表已经创建成功，我们可以通过ALTER TABLE语句修改user表添加createdTime字段：

ALTER TABLE `user` ADD COLUMN createdTime datetime DEFAULT NULL;

当然，我们也可以修改已有字段，让我们将字段password从最大长度255的不定长字符变为最大长度512的不定长字符。

ALTER TABLE `user` CHANGE COLUMN `password` `password` varchar(512) NOT NULL;

让我们检查一下我们的修改是否成功，通过SQL语句SHOW CREATE TABLE可以查看某个数据库表的创建语句：

mysql> SHOW CREATE TABLE `user`;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table|Create Table|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id`int(11) NOT NULL AUTO_INCREMENT,
`username`varchar(255) NOT NULL,
`password`varchar(512) NOT NULL,
`avatar`varchar(255) NOT NULL,
`email`varchar(255) NOT NULL,
`description`longtext NOT NULL,
`createdTime`datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库表

为了测试，我们先新建一个没用的数据库表：

CREATE TABLE `to_drop` (`id` int(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们通过SHOW CREATE TABLE to_drop命令可以看到to_drop的创建语句，然后，我们通过下列命令将其删除：

DROP TABLE `to_drop`;

运行完成后，我们再查看to_drop的创建语句，MySQL会抛出错误to_drop不存在：

mysql> SHOW CREATE TABLE `to_drop`;
ERROR 1146 (42S02): Table 'tianmayingblog.to_drop' doesn't exist

小节

初学者创建数据库一般通过可视化的客户端进行操作，然后导出所有的DDL作为SQL脚本，需要迁移时导入SQL脚本即可。当需对数据库表进行增删或者修改操作时，再通过客户端操作。这种方式对于开发小型的项目也是可行的。

大家在学习过程中，可能会发现开发中最经常使用的是下节课介绍的DML，DDL中修改、创建数据库表的语句并不常用。

事实上，如果一个长期维护的项目，数据库结构的每一次修改也需要完整地记录下来，也就意味着每一次结构变化都要写响应DDL语句记录。这对于应用数据库的升级、演化和部署是非常重要的，这时你会发现DDL是不可或缺的。所以我们需要熟练掌握DDL。