本章，主要就数据库和表的基本操作作以总结。

需要事先说明的一点是，我写的所有MySQL的代码将采用大写形式。虽然说至少在MySQL环境下，SQL语句的大小写并不会影响代码运行结果，但是，有同行讲到，SQL语句大写比是一个更好地习惯。因为在执行程序的时候，如果你用的是小写，会先转化为大写字母然后执行，而如果大写，那就可以省一个转化的时间。在大型的ERP系统开发时，往往会这么要求。所以，我也就尽量规范一些了。算是帮自己养成好的习惯。

数据库的操作

MySQL中，数据库的操作基本有：创建，查看，选择，以及删除4种。

  • 创建数据库：CREATE DATABASE database_name;，后面的database_name是你要创建的数据库的名称；
  • 查看数据库：SHOW DATABASES;，查看已经创建的所有数据库
  • 选择数据库：USE database_name;，选择名为database_name的数据库，很多数据库的操作都需要先选中一个数据库，再执行进一步的操作；
  • 删除数据库：DROP DATABASE database_name;，删除名为database_name的数据库；
  • 查看数据库下的表名称: SHOW TABLES

创建表

建好了数据库，我们选择一个数据库，来对它进行表的相关操作。表是数据在数据库中一种逻辑上的存储形式，和常见的电子表格类似，其中每一行代表一个记录（可以理解成一个数据对象），而记录的每一列代表一个字段（其实你可以理解成数据对象的属性）。

表的基本操作有创建，查看，删除，修改4种基本操作。我先从创建表说起。

CREATE TABLE table_name(
    Att_name_1 Data_type_1,
    Att_name_2 Data_type_2,
    ...
    Att_name_n Data_type_n,
);

其中，table_name表示要创建的表的名称（注意：表明不能用关键字。比如：update,insert...等字段）；Att_name_i表示第i个字段（也就是属性）的名称，Data_type_i表示第i个属性所对应的值的数据类型。这里多说一句，Mysql中的数据类型大致上分为“整数”，“浮点数”，“定点数”，“位”，“日期”，“时间”，“字符串”这么几种。东西太杂，也没必要具体介绍，简单知道就行，具体用到时，可以再查。

举个例子，现在如下创建一个表"tab_student"（最好养成良好的编程习惯，这里为了表明在创建表，最好以 “tab_”+ 的形式设置表名）：

CREATE TABLE tab_student(
    ID VARCHAR(20),
    Score INT
);

表"tab_student"中有两个字段，学生的身份ID以及对应的成绩Score，其中，ID是一个VARCHAR(20)型的字符变量，里面的20是参数；Score则是一个整数类型的变量。

另外，如果想要查看当前数据库中有哪些表，可以先选择当前数据库，再通过语句SHOW TABLES查看。而具体查看已经创建的某个表的信息，则通过下面讲的方式了。

创建带JSON类型的表

CREATE TABLE t_json(
    id INT NOT NULL AUTO_INCREMENT,
    json_col JSON,
    PRIMARY KEY(id)
);

查看表

先说说如何查看数据库当中有哪些表。方法是选择数据库后，用语句SHOW TABLES：

USE test; # 选择数据库test
SHOW TABLES; # 查看test中的表tab_student

比如我们创建了数据库test，然后在test中创建了表tab_student，运行结果如下图所示：

mysql 数据对比 生成差异sql mysql数据库表对比_mysql

可以看到系统给出了一个列表，里面是现在数据库test中所有的表。

那么如何查看某个表的具体信息呢？还是先选择数据库，然后用DESCRIBE语句查看，可以使用简写DESC查看表接口。

USE test; # 选择数据库test
DESCRIBE tab_student; # 查看test中的表tab_student
DESC tab_student;# 查看test中的表tab_student

运行结果如下图所示：

mysql 数据对比 生成差异sql mysql数据库表对比_mysql 数据对比 生成差异sql_02

如果想要查看表结构更详细的定义，可以采用SHOW CREATE TABLE语句，可以使用\G或者\g更加美观，例如：

SHOW CREATE TABLE tab_student \G;

 

需要注意的是，在显示表的详细信息时，可以用;, \g或者\G来结束语句（我上面的代码用的是;）。其实，如果用\G可以让表的详细信息显示得更加美观。运行结果你自己跑一下就能发现区别，我这里不给图了。

删除表

DROP TABLE table_name语句用来删除表，其中"table_name"是表名。

修改表

修改表主要分为修改表名和修改表中的字段。

1. 修改表名

ALTER TABLE old_table_name RENAME new_table_name语句用来修改表名，其中"old_table_name"是旧表名，"new_table_name"是新表名，当然，这里所要操作的对象一定得是存在的，不然会报错。

例如，还是上面的例子：

ALTER TABLE tab_student RENAME tab_student1;

我分两行写，是为了看起来更清楚。现在将表格tab_student更名为tab_student1。然后我们在用上面说的查看表格的命令，可以看到以新名命名的表格，同时查看旧的名字时，报错。

2. 增加字段

  • ADD语句在表格末尾加字段：
ALTER TABLE table_name ADD Att_name_1 Data_type_1;

在表"table_name"末尾，增加属性名为Att_name_1，数据类型为Data_type_1的字段

  • ADD ... FIRST语句在表格开头加字段：
ALTER TABLE table_name ADD Att_name_1 Data_type_1 FIRST;

在表"table_name"开头，增加属性名为Att_name_1，数据类型为Data_type_1的字段

  • ADD ... AFTER ...语句在指定字段之后添加字段：
ALTER TABLE table_name ADD Att_name_1 Data_type_1 AFTER Att_name_2;

在属性名为Att_name_2的字段后，增加属性名为Att_name_1，数据类型为Data_type_1的字段

就上边那个"tab_student"表为例，我现在在字段ID的后面增加一个属性名为Gender，数据类型为CHAR的字段。

ALTER TABLE tab_student ADD Gender CHAR AFTER ID;

可以看到一开始如左图的表变成了右图的样子。其他添加字段的操作类似，这里不举例了。

 

mysql 数据对比 生成差异sql mysql数据库表对比_数据库_03

3. 删除字段

语法形式如下：

ALTER TABLE table_name DROP Att_name;

将属性名为"Att_name"的字段删除。

4. 修改字段

  • 修改字段的数据类型：
ALTER TABLE table_name MODIFY Att_name New_data_type;

将属性名为"Att_name"的字段的数据类型新设置为"New_data_type"。

  • 修改字段名：
ALTER TABLE table_name CHANGE Old_att_name New_att_name Old_data_type;

将属性名为"Old_att_name"，数据类型为"Old_data_type"的字段名修改为"New_att_name"。注意此处用的是CHANGE而非MODIFY（用混了会报错），再者，三个参数在代码中的顺序不要搞错。以表tab_student为例，我们现在想要将字段名"ID"改为"IDs"，代码如下：

ALTER TABLE tab_student CHANGE ID IDs CHAR;

这样，将表的形式由下面的左图变成了右图：

mysql 数据对比 生成差异sql mysql数据库表对比_主键_04

  • 同时修改字段名和数据类型：
ALTER TABLE table_name CHANGE Old_att_name New_att_name New_data_type;

将属性名为"Old_att_name"的字段名修改为"New_att_name"，数据类型为"New_data_type"的字段。基本语法结构与上面的修改字段名是一样的。

  • 修改字段顺序：
ALTER TABLE table_name MODIFY Att_name_1 Data_type_1 FIRST (AFTER Att_name_2);

我解释一下，括号里面是另一种形式，先不看，括号外头的意思是将属性名为"Att_name_1"，数据类型为"Data_type_1"的字段放置于表的开头。而括号里面的是另一种修改次序的方法，表示将属性名为"Att_name_1"，数据类型为"Data_type_1"的字段放置于属性名为"Att_name_2"的字段后面。

以表tab_student为例，现在将字段"Gender"放置到字段"Score"之后，代码如下：

ALTER TABLE tab_student MODIFY Gender CHAR AFTER Score;

tab_student表的形式由下面的左图变为右图：

mysql 数据对比 生成差异sql mysql数据库表对比_字段_05

表的约束

表的约束是用来检验表中数据的完整性的，说的直白一些，就是检验表中的数据是否满足了规定的条件。以tab_student表举例来说，如果我不愿意令字段ID为空，就可以在建表时定义相关的约束。如果用户在插入数据时，违反了这样的约束条件，就会报错。当然可以定义的约束条件分很多种，下面会一一讲解。

1. 设置非空约束

关键字NOT NULL用来约束某字段不能为空，语法形式如下：

CREATE TABLE table_name(
    Att_name Data_type NOT NULL,
    ...
);

 

上述代码将字段"Att_name"的默认值设置为"deVal"。

3. 设置唯一约束

关键字UNIQUE用来设置对某个字段的唯一约束，唯一约束表示，插入的数据在这个字段上的值得是唯一的，不能重复（你可以类比一般编程语言中“键值对”的“键”）。以学生的信息系统为例，每个人的学号信息应该是唯一的，那么在录入数据时，如果这一项出现了重复，那必然是出错了。所以我们最好设置唯一值约束，如果出现重复，则报错。其语法形式如下：

CREATE TABLE table_name(
    Att_name Data_type UNIQUE,
    ...
);

上述代码将字段"Att_name"设置为了唯一约束。

现在以上面的tab_student为例，我对tab_student中的属性设置上面说的“非空”，“默认值”，“唯一”的约束：

CREATE TABLE tab_student(
    ID VARCHAR(20) NULL UNIQUE, # 设置学生ID非空，且唯一
    Score INT DEFAULT 0, # 设置Score默认值为0
    Gender CHAR
);

 我们看到表会变成下面的样子：

mysql 数据对比 生成差异sql mysql数据库表对比_字段_06

 

4. 设置主键约束

主键的作用在于用某个字段唯一地标识所有记录，以便在数据库中快速地查找数据。比如我们一般用索引号来唯一标识每一条数据记录。由于主键可以是单一字段，也可以是多个字段，所以，下面分两种情况讨论：

  • 单字段主键：用关键字PRIMARY KEY设置主键。比如，还是tab_student的例子，现在将学号设置为主键。
CREATE TABLE tab_student(
    ID VARCHAR(20) PRIMARY KEY, # 设置学生ID为主键
    Score INT,
    Gender CHAR
);

 我们查看刚才建的表：

mysql 数据对比 生成差异sql mysql数据库表对比_mysql_07

发现ID字段也是非空的，其实，设置了主键，则主键字段必然是非空且唯一的。

  • 多字段主键：当主键由多个字段组合而成时，则需要关键字CONSTRAINT和PRIMARY KEY配合设置。其语法结构如下：
CREATE TABLE tab_student(
    ID VARCHAR(20),
    Name VARCHAR(20),
    Score INT,
    CONSTRAINT ID_Name PRIMARY KEY (ID, Name)
);

在上面代码展示的例子中，语句CONSTRAINT ID_Name PRIMARY KEY (ID, Name)，将学生的ID和Name组合起来设置为主键。最后括号内表示的是主键涵盖的字段，而CONSTRAINT后面的ID_Name则是为这个约束起的名字（就设置主键约束来说，这个名字不要也没什么影响）。

例子最后生成的表信息如下：

 

mysql 数据对比 生成差异sql mysql数据库表对比_字段_08

 

5. 设置字段值自动增加

往表中插入记录时，往往会遇到需要递增的字段。例如商品编号啊，等等。Mysql支持对这类字段设置字段值自动增加的约束。默认情况下，设置了自动增加的字段值为整数，从1开始，每次加1。又因为这种自动增加的字段肯定是唯一的，所以一般我们也把它设置为主键。

设置自动增加的关键字为AUTO_INCREMENT

CREATE TABLE tab_student(
    ID INT PRIMARY KEY AUTO_INCREMENT, # 将ID设置为整数类型，并且设置字段值自动增加
    Name VARCHAR(20),
    Score INT
);

现在这个表示这样的：

mysql 数据对比 生成差异sql mysql数据库表对比_数据库_09

注意，如果要将某个字段设置为自动增加，那么必须将这个字段设置“唯一”的属性，即设置为主键或者唯一约束，否则会报错。

6. 设置外键约束

上面的5种约束都是对于单表，而外键约束则是保证多个表之间的参照完整性。

设置外键约束的两个表之间具备父子关系，即子表中某个字段的取值依赖于父表。举例来说，现在有两个表：部门表和雇员表，部门表的主键为部门编号depNo，而雇员表中表示雇员所在部门编号的字段depno就成为了雇员表（子表）的外键。外键设置时有以下几点要求：

  • 外键所代表的字段必须依赖于已经存在的父表的主键；
  • 外键与相应的父表的主键可以不同名；
  • 外键可以为空（null）

设置外键的语法用FOREIGN KEY (attribute) + REFERENCES father_table_name (attribute)的形式：

CREATE TABLE child_table_name(
    Att_name_1, DataType_1,
    Att_name_2, DataType_2,
    CONSTRAINT FK_name FOREIGN KEY (Att_name_1) REFERENCES father_table_name (Att_name)
);