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) |
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作为我们的存储引擎,它提供了事务、行级锁机制和外键约束的功能。大家如果对其他引擎感兴趣,可以自行阅读。
建立外键约束
在一节中我们知道,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