首页 > 编程笔记 > MySQL笔记 阅读：174

MySQL创建数据表（非常详细）

在创建完 MySQL 数据库之后，接下来的工作就是创建数据表。所谓创建数据表，就是在已经创建好的数据库中建立新表。

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。

创建表的语法形式

数据表属于数据库，因此在创建数据表之前，应该使用“USE <数据库名>”语句指定操作在哪个数据库中进行。如果没有选择数据库，就会抛出“No database selected”的错误。

创建数据表的语句为 CREATE TABLE，语法格式如下：
CREATE  TABLE <表名>
(
字段名1，数据类型 [列级别约束条件] [默认值],
字段名2，数据类型 [列级别约束条件] [默认值],
...
[表级别约束条件]
);
要创建的表的名称，不区分大小写，不能使用 SQL 语言中的关键字，如 DROP、ALTER、INSERT 等。数据表中每一列（字段）的名称和数据类型，如果创建多列，各列之间就要用英文逗号隔开。

【实例】创建员工表 tb_emp1，结构如下表所示。

表 1 tb_emp1表结构
字段名称 数据类型 备注
id INT 员工编号
name VARCHAR（25） 员工名称
deptId INT 所在部门编号
salary FLOAT 工资

1) 创建数据库，SQL 语句如下： 
CREATE  DATABASE test_db;

2) 选择创建表的数据库，SQL 语句如下： 
USE test_db;

3) 创建 tb_emp1 表，SQL 语句如下：
CREATE TABLE tb_emp1
(
id      INT,
name   VARCHAR(25),
deptId  INT,
salary  FLOAT
);
以上语句执行后，便创建了一张名称为“tb_emp1”的数据表。

4) 查看数据表是否创建成功，SQL 语句如下：
mysql> SHOW TABLES;
+----------------------+
| Tables_in_ test_db   |
+----------------------+
| tb_emp1              |
+----------------------+
可以看到，test_db 数据库中已经有了数据表 tb_emp1，说明数据表创建成功。

使用主键约束

主键又称主码，是表中一列或多列的组合。主键约束要求主键列的数据唯一，并且不为空。

主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证号和人之间的关系，它们是一一对应的。

主键分为两种类型：单字段主键和多字段联合主键。

1) 单字段主键

单字段主键由一个字段组成，SQL 语句格式分为以下两种。

① 在定义列的同时指定主键，语法格式如下： 
字段名 数据类型 PRIMARY KEY [默认值]
【实例】定义数据表 tb_emp 2，其主键为 id，SQL 语句如下：
CREATE TABLE tb_emp2
(
id          INT PRIMARY KEY,
name        VARCHAR(25),
deptId     INT,
salary  FLOAT
);

② 在定义完所有列之后指定主键，语法规则如下：
CREATE TABLE tb_emp3
(
id INT,
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY(id)
);
上述两个例子执行后的结果是一样的，都会在 id 字段上设置主键约束。

2) 多字段联合主键

多字段联合主键由多个字段联合组成，语法格式如下： 
PRIMARY KEY [字段1, 字段2,..., 字段n]
【实例】定义数据表 tb_emp4，假设表中间没有主键 id，为了唯一确定一个员工，可以把 name、deptId 联合起来作为主键，SQL 语句如下：
CREATE TABLE tb_emp4
(
name VARCHAR(25),
deptId INT,
salary FLOAT,
PRIMARY KEY(name,deptId)
);
以上语句执行后，便创建了一张名称为“tb_emp4”的数据表，name 字段和 deptId 字段组合在一起成为 tb_emp4 的多字段联合主键。

使用外键约束

外键用来在两张表的数据之间建立连接，可以是一列或者多列。一张表可以有一个或多个外键。

外键对应的是参照完整性，一张表的外键可以为空值，若不为空值，则每一个外键值必须等于另一张表中主键的某个值。

外键是表中的一个字段，虽然可以不是本表的主键，但必须对应另外一张表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除另一张表中具有关联关系的行。

例如，部门表 tb_dept 的主键是 id，员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。在这个例子中，部门表（tb_dept）是主表（父表），而员工表（tb_emp5）是从表（子表）。从表中的外键（deptId）与主表中的主键（id）建立了关联关系，确保了数据的完整性和一致性。

对于两个具有关联关系的表而言，相关联字段中主键所在的那张表是主表（父表）；对于两个具有关联关系的表而言，相关联字段中外键所在的那张表是从表（子表）。

创建外键的语法格式如下：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ,字段名2,...] REFERENCES <主表名> 主键列1 [ ,主键列2,...]

【实例】定义数据表 tb_emp5，并在 tb_emp5 表上创建外键约束。
1) 创建一张部门表 tb_dept1，表结构如下表所示：

表 2 tb_dept1表结构
字段名称 数据类型 备注
id INT 部门编号
name VARCHAR（22） 部门名称
location VARCHAR（50） 部门位置

SQL 语句如下： 
CREATE TABLE tb_dept1
(
id            INT PRIMARY KEY,
name          VARCHAR(22)  NOT NULL,
location      VARCHAR(50)
);

2) 定义数据表 tb_emp5，让它的键 deptId 作为外键关联到 tb_dept1 的主键 id，SQL 语句如下：
CREATE TABLE tb_emp5
(
id      INT PRIMARY KEY,
name   VARCHAR(25),
deptId  INT,
salary   FLOAT,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
以上语句执行成功之后，在表 tb_emp5 上添加了名称为“fk_emp_dept1”的外键约束，外键名称为 deptId，它依赖于表 tb_dept1 的主键 id。

关联指的是在关系数据库中相关表之间的联系。它是通过相容或相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时，就会出现“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”错误。

使用非空约束

非空约束指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，则数据库系统会报错。

非空约束的语法格式如下： 
字段名 数据类型 NOT NULL
【实例】定义数据表 tb_emp6，指定员工的名称不能为空，SQL 语句如下：
CREATE TABLE tb_emp6
(
id     INT PRIMARY KEY,
name   VARCHAR(25) NOT NULL,
deptId  INT,
salary  FLOAT
);
以上语句执行后，在 tb_emp6 中创建了一个 name 字段，其插入值不能为空。

使用唯一性约束

唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或者几列不出现重复值。

唯一性约束的语法格式有以下两种：

1) 在定义完列之后直接指定唯一约束

在定义完列之后直接指定唯一约束，语法格式如下： 
字段名 数据类型 UNIQUE
【实例】定义数据表 tb_dept2，指定部门的名称唯一，SQL 语句如下： 
CREATE TABLE tb_dept2
(
id            INT PRIMARY KEY,
name          VARCHAR(22) UNIQUE,
location      VARCHAR(50)
);

2) 在定义完所有列之后指定唯一约束

在定义完所有列之后指定唯一约束，语法格式如下： 
[CONSTRAINT <约束名>] UNIQUE(<字段名>)
【实例】定义数据表 tb_dept3，指定部门的名称唯一，SQL 语句如下： 
CREATE TABLE tb_dept3
(
id            INT PRIMARY KEY,
name          VARCHAR(22),
location      VARCHAR(50),
CONSTRAINT  STH UNIQUE(name)
);
UNIQUE 和 PRIMARY KEY 的区别：

使用默认约束

默认约束（Default Constraint）指定某列的默认值。例如，当男性同学较多时，性别就默认为“男”。如果插入一条新的记录时没有为性别字段赋值，系统就会自动为这个字段赋值为“男”。

默认约束的语法格式如下： 
字段名 数据类型 DEFAULT 默认值
【实例】定义数据表 tb_emp7，指定员工的部门编号默认为 1111，SQL 语句如下： 
CREATE TABLE tb_emp7
(
id       INT PRIMARY KEY,
name    VARCHAR(25) NOT NULL,
deptId   INT DEFAULT 1111,
salary   FLOAT
);
以上语句执行成功之后，表 tb_emp7 上的字段 deptId 拥有了一个默认值 1111，新插入的记录如果没有指定部门编号，则默认为 1111。请读者自行测试一下。

设置表的属性值自动增加

在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值，可以通过为主键添加 AUTO_INCREMENT 关键字来实现。

在 MySQL 中，AUTO_INCREMENT 的初始值默认是 1，每新增一条记录，字段值自动加 1。一张表只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须为主键的一部分。AUTO_INCREMENT 约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT 等）。

设置表的属性值自动增加的语法格式如下： 
字段名 数据类型 AUTO_INCREMENT
【实例】定义数据表 tb_emp8，指定员工的编号自动递增，SQL 语句如下：
CREATE TABLE tb_emp8
(
id       INT PRIMARY KEY AUTO_INCREMENT,
name    VARCHAR(25) NOT NULL,
deptId  INT,
salary  FLOAT
);
上述语句执行后，会创建名称为“tb_emp8”的数据表，表 tb_emp8 中 id 字段的值在添加记录时会自动增加。即在插入记录的时候，默认的自增字段 id 的值从 1 开始，每添加一条新记录，该值自动加 1。

例如，执行如下插入语句： 
mysql> INSERT INTO tb_emp8 (name,salary)
-> VALUES('Lucy',1000), ('Lura',1200),('Kevin',1500);
这里使用 INSERT 声明向表中插入记录的方法，并不是 SQL 的标准语法，这种语法不一定被其他厂家的数据库支持，只能在 MySQL 中使用。

语句执行完后，表 tb_emp8 中增加了 3 条记录，这里并没有输入 id 的值，但系统已经自动添加该值。使用 SELECT 命令查看记录，结果如下： 
mysql> SELECT * FROM tb_emp8;
+----+-------+--------+--------+
| id | name  | deptId | salary |
+----+-------+--------+--------+
|  1 | Lucy  |   NULL |  1000  |
|  2 | Lura  |   NULL |  1200  |
|  3 | Kevin |   NULL |  1500  |
+----+-------+--------+--------+

相关文章