SQL轻松学：通过DDL定义数据库结构

DDL定义数据库结构 由David发表在天码营

DDL

SQL中的数据定义语言（Data Definition Language, DDL）用以定义数据库结构，包括新建、更改或者删除数据库表等操作，对应于CREATE,ALTER,DROP三种命令。

创建数据库

搭建好MySQL环境后，我们就可以正式使用SQL对数据库进行操作了。我们首先打开命令行，输入以下命令登录Mysql Server：

mysql -u root -p

然后，我们创建一个数据库作为我们的测试数据库：

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

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

创建数据库表

现在让我们在tianmayingblog数据库中创建User表，根据我们之前的过程，我们的User表应该包含以下信息：

字段名	Java数据类型	MySQL数据类型
id	long	数字类型，对于长整形一般我们使用int(11)作为数据类型
username	String	文本类型，title属于可变长的字符串，我们选择varchar(255)作为其数据类型
password	String	文本类型，varchar(255)
avatar	String	文本类型，varchar(255)
title	String	文本类型，varchar(255)
email	String	文本类型，varchar(255)
description	String	文本类型，也属于可边长字符串，但由于其数据量可能较大，我们选择LONGTEXT作为其数据类型

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

use `blog`; //进入blog数据库
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,
  `title` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

大家可以看到，我们将id作为User表的主键。这时候有一个小问题，我们每次插入数据库时，都要为User生成一个唯一的id，比较麻烦。MySQL当然为我们考虑好了这些问题，在指定id字段时，我们可以为其声明自增属性，这样，插入时若我们未指定id字段的值，MySQL会自动为我们生成id的值，其值为上一条记录的id + 1。

另外，在CREATE TABLE语句的最后一行，我们为MySQL设置了字符编码集。字符编码我们在上一节中已经接触过，再次强调，每次创建数据库表时，都需要指定其字符编码为UTF8。

另外，我们选择了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 AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

创建完成后，我们尝试往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, title, email, description) values("David", "pwd", "avatar", "David", "david@tianmaying.com", "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,
  `title` 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
  
  

更多文章请访问 天码营网站