当前位置：首页 > article >正文

[MySQL]数据类型以及表的属性与操作大全

article 2025/1/24 12:03:43

一、表的操作

1.表的增删查

表的创建

表的删除

表的查看

2.表的修改

3.使用案例

二、数据类型

1.数值类型

整数类型

bit类型

float类型

decimal类型

2.字符串类型

char类型

varchar类型

char和 varchar的比较

3.日期和时间类型

4.enum和set类型

三、表的约束

1.空属性

2.默认值

3.列描述

4.zerofill属性

5.主键

设置主键

删除主键

复合主键

6.自增长

7.唯一键

8.外键

一、表的操作

1.表的增删查

表的创建

CREATE TABLE table_name （

field1 datatype,

field2 datatype,

field3 datatype

) character set 字符集 collate 校验规则 engine 存储引擎;

field表示的是变量的名称（列名称），datatype表示的是数据的类型（列类型），在MySQL创建表定义变量的时候一般是变量名写前面，类型和一些其他字段写后面。
对于创建数据库的时候设置的字符集和校验规则，相当于设置的是该数据库默认的字符集和校验规则，如果在创建表的时候不显示的去定义这两个内容的话，就使用创建数据库的时候设置的，如果显示的定义了，那么就使用创建表的时候定义的规则。
创建表的时候也需要定义存储引擎，不同的存储引擎存储数据的时候是创建的表文件是不一样的。

表的删除

DROP TABLE [IF EXISTS] table_name1 [, table_name2]; //可以一次性删除多个表

表的查看

desc 表名称

2.表的修改

数据库在实际开发中基本上是不修改的，但是对于数据库中的表结构确实会经常进行修改，例如修改表的字段名称、字段大小、字段类型，根据实际的需求增加和删除表的字段等等操作。

//增加字段操作

ALTER TABLE table_name ADD column datatype [DEFAULT expr][, column datatype]...

案例：alter table user1 add (test_field int);

//修改字段操作

ALTER TABLE table_name Modify column datatype [DEFAULT expr][, column datatype]...

案例：alter table user1 modify (name varchar(30));

//删除字段操作

ALTER TABLE table_name DROP column;

案例：alter table user1 drop id, name;

上述就是添加一个表中的字段操作，添加多个字段的时候，需要使用逗号进行分隔。对于修改操作和添加操作是一样的，只不过column的字段名称必须是表中有的，然后将字段属性覆盖式的重新定义。而对于删除操作的话，就比较简单了只需要输入字段名称即可。

在增加字段的时候，对于原有的数据不会做任何的修改，而新增的字段的值会填充为默认值。在修改字段属性的时候，可能会收到新字段属性的影响。而删除字段属性之后，字段属性对应的值都会被删除的。

//修改表名称

alter table user1 rename to xxxxx; //这里的to可以省略

//修改表中字段名称

alter table user1 change oldxx newxx [字段属性]

在修改表内部字段的名称的时候，需要将字段的属性再次定义一遍才可以。

3.使用案例

//创建表--------------------------------------------------------
mysql> create table optable (
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

//添加表字段--------------------------------------------------------
mysql> alter table optable add password varchar(32);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc optable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

//修改表字段--------------------------------------------------------
mysql> alter table optable modify name varchar(15);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc optable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(15) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//删除表字段--------------------------------------------------------
mysql> alter table optable drop id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc optable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(15) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

//修改表名称--------------------------------------------------------
mysql> alter table optable rename optables;
Query OK, 0 rows affected (0.01 sec)


mysql> show tables;
+----------------+
| Tables_in_tset |
+----------------+
| optables       |
| tinyvalue      |
+----------------+
2 rows in set (0.00 sec)

//修改字段名称--------------------------------------------------------
mysql> alter table optables change name newname varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc optables;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| newname  | varchar(20) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

二、数据类型

1.数值类型

整数类型

MySQL中的整数有5个类型，tinyint类型占有1个字节，smallint类型占用2个字节，mediumint类型占用3个字节、int类型占用4个字节，bigint类型占用8个字节。不同的类型就对应了数据存储的不同范围，当存储的数值越过类型的范围不会截断等处理，而是有检查直接报错处理。

数值越界测试：

mysql> create table tinyvalue (
    -> value tinyint
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tinyvalue values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tinyvalue values(128);
ERROR 1264 (22003): Out of range value for column 'value' at row 1

而且这些类型都提供了unsigned无符号类型版本，默认是有符号的，可以在创建字段的时候在后面手动加上unsigned属性，但是一般不建议使用unsigned类型的版本，因为无符号类型也就比有符号类型的取值范围大了一倍而已，在庞大的数据量面前，可能大一倍也不能符合数据的要求，所以使用unsigned int 还不如使用bigint类型。

设置字段属性为无符号类型:

mysql> alter table tinyvalue modify value tinyint unsigned;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tinyvalue;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| value | tinyint unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

bit类型

语法：bit(n)

使用案例：create table bittable(id int, value bit(8));

该字段是位字段类型，是一个可以指定多少个比特位的字段。n默认取值为1，取值范围在1~64之间。如果说可以用一个比特位0/1表示一些内容的时候，可以考虑bit(1)，这样比较省空间，bool类型的字段还需要1个字节呢，而bit(1)只需要一个比特位。

float类型

语法：float[(m,d)] [unsigned]

使用案例：create table floattable(id int, value float(4,2));

该字段是一个小数字段，m是指定数值显示的长度，d表示的是小鼠的位数，占用空间大小为4个字节。上述的(4,2)表示的数值范围就是-99.99~99.99之间。当我们定义的float为无符号的时候，那么取值范围就变成了0~99.99之间了。

decimal类型

语法：decimal[(m,d)] [unsigned]

使用案例：create table decimaltable(id int, value decimal(20,10));

该类型也是表示一个小数，使用上和float是一样的。float和decimal的最大区别就是在于精度不同，decimal的精度更大一些，float表示的精度大约是7位，decimal整数最大的位数m是65，支持小数最大位数d是30，如果d被省略的话，默认是0，如果m也被省略的话，默认是10。

精度对比：

如图可以看出来，float类型的数据，第7位就开始不准了。因此如果要表示较高精度的小数，就要使用decimal类型的数据了。

mysql> create table decimaltable (
    -> salary_float float(10, 8),
    -> salary_decimal decimal(10, 8)
    -> );
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> insert into decimaltable values(12.12345612, 12.12345612);
Query OK, 1 row affected (0.01 sec)

mysql> select * from decimaltable;
+--------------+----------------+
| salary_float | salary_decimal |
+--------------+----------------+
|  12.12345600 |    12.12345612 |
+--------------+----------------+
1 row in set (0.00 sec)

2.字符串类型

char类型

语法：char(L)

使用案例：create table chartable (id int, value char(5));

该类型表示的是一个固定长度的字符串类型，L是可以存储的长度，单位是字符而不是字节，那么就是说char(2)的话，那么就可以存放两个字母或者两个汉字字符。最大的长度值可以设置为255。

案例：

mysql> create table chartable (
    -> id int,
    -> name char(2)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into chartable values(1, 'aa'), (2, '哈哈');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from chartable;
+------+--------+
| id   | name   |
+------+--------+
|    1 | aa     |
|    2 | 哈哈   |
+------+--------+
2 rows in set (0.00 sec)

varchar类型

语法：varchar(L)

使用案例：create table varchartable (id int, value varchar(5));

varchar和char的语法一样，对于char来说指定长度为5的话，就是直接开辟一个5个字符的空间，你使用多少他都是开辟那么大的空间，但是varchar的空间是动态开辟的，他设置的L长度是最大的长度，使用多少空间开辟多少空间，使用的空间不可以超过最大字符数就可以。

varchar的占用空间最大可以为65535个字节，但是L最大具体是多少取决于采用的编码集。如果说采用的是utf8编码集，那么uft8中一个字符占用的空间是3个字节，所以说L最大的取值就是65535/3 = 21845个，而varchar有1~3个字节是用于记录数据大小，所以说L的最大取值就是21844。而在gbk的编码规则下，gbk一个字符占用2个字节，所以说(65535 - 3)/2 = 32766。char类型的计算也是如此的。

验证uft8下的varchar最大长度：

mysql> create table varchartable2 (
    -> name varchar(21845)
    -> )charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table varchartable2 (
    -> name varchar(21845)
    -> )charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

char和 varchar的比较

如果说长度存储的字符串长度是固定的话，那么就使用char类型，因为动态开辟空间的时候，会有效率的损耗，但是如果存储的数据长度范围变化大，那么就可能会导致空间的大量浪费，就需要考虑使用varchar类型了。

3.日期和时间类型

MySQL对于时间和日期提供了三种类型，第一个只表示日期的类型date类型，在插入数据的时候需要特定格式为'yyyy-mm-dd'，该类型数据占用三个字节。第二个表示日期和时间的datetime类型，插入数据的时候也是需要特定的格式数据'yyyy-mm-dd HH:ii:ss'，该类型需要占用8个字节。第三个就是时间戳类型，格式和datetime一样，但是占用4个字节。当该字段设置为自动添加的时候，如果在插入数据的时候，对于时间戳字段的数据不插入时会自动填充当前时间戳。当更新数据中的其他内容字段的时候，时间戳也会跟着更新。

使用案例：

//创建数据库
mysql> create table t2 (
    -> value1 date,
    -> value2 datetime,
    -> value3 timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.03 sec)

//插入一条数据，会自动添加时间戳
mysql> insert into t2 (value1, value2) values('2024-01-01', '2025-12-12 12:34:56');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------------+---------------------+---------------------+
| value1     | value2              | value3              |
+------------+---------------------+---------------------+
| 2024-01-01 | 2025-12-12 12:34:56 | 2025-01-22 20:44:27 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

//修改数据会自动更新时间戳
mysql> update t2 set value1 = '2026-05-15';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from t2;
+------------+---------------------+---------------------+
| value1     | value2              | value3              |
+------------+---------------------+---------------------+
| 2026-05-15 | 2025-12-12 12:34:56 | 2025-01-22 20:44:27 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

4.enum和set类型

enum语法：enum('选项1', '选项2', '选项3', ...)

相当于是提供了一些选项，然后添加数据的时候该字段的值可以从这些选项中选择一个。但是处于空间和效率的考虑，实际上该字段中存储的值并非选项的内容，而是选项对应的序号数字，从1开始标序号，最多可以提供65535个选项值。当我们在插入数据时，也可以使用对应的数字编号。

set语法：set('选项1', '选项2', '选项3', ...)

set和enum的区别就在于，enum是单选，而set可以进行多选。存储的也是选项对应的序号，最多可以提供64个选项。

使用案例：

mysql> create table t3 (
    -> username varchar(10),
    -> hobby set('唱歌', '跳舞', '学习', '打游戏'),
    -> gender enum('男', '女')
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t3 values('张三', '唱歌,学习', '男');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values('李四', 1, '2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| 张三     | 唱歌,学习     | 男     |
| 李四     | 唱歌          | 女     |
+----------+---------------+--------+
2 rows in set (0.00 sec)

三、表的约束

表的约束主要是对于表内部字段的一些约束条件，其实真正约束字段的是数据类型，但是数据类型的约束很单一，在合理的范围内，无法去判断该数据是否是符合存储的数据应有的逻辑，所以需要配合一些额外的约束，更好的保证数据的合法性，从业务逻辑上保证数据的正确性。例如：数据的唯一、数据的默认值设置等约束操作。

1.空属性

语法：field typedata not null

使用案例：create table test_table ( id int not null, name varchar(20) not null );

一般数据库的默认字段基本都是可以为空的，但是实际开发当中，对于注册等操作需要必须填写一些重要的信息，那么可以设置数据库的某些字段不为空，来达到某些字段必须在插入数据的时候给值，如果不给值的话就会执行出错。

2.默认值

语法 field typedata default xx

使用案例 create table test_table (id int default 0, name varchar(20) not null);

当某个字段的值经常性的是给定某个具体值的时候，可以在一开始就指定好，在插入的时候，就可以不用插入该值，那么也会自动帮我们将该字段的值填写为默认值。一般not null 和该字段是不同时出现的，因为如果设置了default字段的话，那就不可能为空值了。

3.列描述

语法field typedata commit 'xxxxxx'

使用案例 create table test_table (id int default 0 comment '学号', name varchar(20) not null);

该字段就相当于是一个注释一样的作用，没有实际的含义，专门用来描述字段用的，会根随表创建语句保存下来，用来给程序员了解字段的含义。在使用show create table xx;的时候就可以查看到comment的描述信息了。

mysql> create table if not exists t1 (
    -> id int default 0 comment '学号',
    -> name varchar(20) not null comment '姓名'
    -> );
Query OK, 0 rows affected (0.04 sec)

//不指定id的值，默认写入0
mysql> insert into t1 (name) values ('张三');
Query OK, 1 row affected (0.01 sec)

//设置name 为 not null，那么如果不指定name的值，是不允许插入的
mysql> insert into t1 (id) values (1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> insert into t1 (id, name) values (1, '李四');
Query OK, 1 row affected (0.01 sec)

//查看数据库表
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    0 | 张三   |
|    1 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT '0' COMMENT '学号',
  `name` varchar(20) NOT NULL COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3     |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.zerofill属性

该属性用于在数字类型的列中，当存储的数字长度小于列定义的宽度的时候，在数字左侧使用零进行填充。

mysql> create table t2 (
    -> id int(5) zerofill,
    -> id2 float(6,2) zerofill
    -> );
Query OK, 0 rows affected, 4 warnings (0.06 sec)

mysql> insert into t2 values (10, 3.14);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (100000, 3.14);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+--------+--------+
| id     | id2    |
+--------+--------+
|  00010 | 003.14 |
| 100000 | 003.14 |
+--------+--------+
2 rows in set (0.00 sec)

如上图所示，我们定义了int类型显示的长度为5，这里指的是显示的长度最少为5，如果说插入了一个6位数字他会显示6位的。设置了zerofill属性之后，我们插入10和插入3.14都不够5位，所以他会在前面自动补0形成5位的显示形式。

相当于对select的查看操作进行了一个格式化的输出，但是内部存储的值还是10和3.14不会变的。还需要注意的是，如果我们设置了zerofill属性，就相当于设置的是unsigned zerofill属性，该字段就不能为负数了。

mysql> insert into t2 value (-1, 3.14);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

5.主键

语法：field datatype primary key;

使用案例：create table test_table ( id int primary key);

主键是用来唯一的约束一个字段里面的数据，不能重复、不能为空的一个属性，而且一张表中只能有一个主键。对于主键的设置通常来说是设置在整数类型的字段上。

设置主键

主键的设置可以再创建表的时候指定哪个字段为主键，也可以在创建表之后再去追加设置主键。就是使用修改表字段属性的SQL语句即可，因为是追加，所以使用的是add。

追加主键：alter table 表名称 add pirmary key(字段名称);

mysql> insert into t2 value (-1, 3.14);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> create table t3 (
    -> id int unsigned primary key comment '学号',
    -> name varchar(20) not null comment '姓名'
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table t3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int unsigned NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3     |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

设置完毕之后，使用desc查看表，在Key值处显示PRI的就代表该字段是该表的主键。而且在查看建表语句的时候，可以看到对于id我们没有设置not null，但是因为设置主键的原因自动帮我们加上了not null属性。

主键字段的值不能重复：

mysql> insert into t3 values(1, '张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(1, '李四');
ERROR 1062 (23000): Duplicate entry '1' for key 't3.PRIMARY'

删除主键

alter table 表名称 drop primary key;

复合主键

当我们想要将多个字段放在一起设置为主键的时候，那么就可以使用复合主键。当设置为复合主键之后，对于单个字段数值相同不会报错了，会将整个复合主键包含的字段作为一个整体进行判断不能出现重复，对于不能为空的约束则是复合主键中的每个字段都不能为空。

复合主键的创建可以在创建数据库最后指定：

mysql> create table t4 (
    -> id int,
    -> name varchar(20),
    -> primary key(id, name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t4 value (1, '张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 value (2, '张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 value (2, '张三');
ERROR 1062 (23000): Duplicate entry '2-张三' for key 't4.PRIMARY'
mysql> insert into t4 value (2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>

6.自增长

语法：auto_increment

自增长属性是：auto_increment，当对应的字段不给值的时候，会自动的被系统触发，系统会从当前字段中已经由的最大值进行加1，然后赋值给该字段。如果首次插入的时候，默认该字段的值为1。一般都是搭配着主键使用。自增长属性设置的字段必须是一个整数，而且该字段前提必须是一个索引（key栏中有值），而且一张表中最多只能有一个自增长字段。

mysql> create table t5 ( 
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t5 (name) values ('张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (id, name) values (4, '张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 (name) values ('张三');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  4 | 张三   |
|  5 | 张三   |
+----+--------+
3 rows in set (0.00 sec)

而且在建表的语句中还可以看到auto_increment现在的值，如果下一次再不指定的写入的话，那么就会将6作为auto_increment字段的值。

mysql> show create table t5;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

7.唯一键

语法：unique

一张表中往往有很多字段需要唯一性的约束，例如统计一个人的信息时候，身份证和员工的工号都是需要唯一性的，但是一个表中只能拥有一个主键，那么唯一键是没有数量约束的，可以解决表中多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，但是唯一键允许为空，空字段不做唯一性的比较。主键更多的作用是用来标识表中数据的唯一性的，相当于是表中数据的唯一标识符，而唯一键更多的保证再业务逻辑上数据的唯一性。

//创建表
mysql> create table t6 (
    -> id int unique,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t6 values (1, '哈哈');
Query OK, 1 row affected (0.00 sec)

//唯一性测试
mysql> insert into t6 values (1, '你好');
ERROR 1062 (23000): Duplicate entry '1' for key 't6.id'

//唯一键约束的字段可以为空
mysql> insert into t6 (name) values ('你好');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 哈哈   |
| NULL | 你好   |
+------+--------+
2 rows in set (0.00 sec)

8.外键

语法：foreign key (字段名) references 主表(列名称)

外键用于定义主表与从表之间的关系，外键约束主要定义在从表上，主表则必须是有主键约束或者唯一键约束，当定义外键后，要求外键列数据必须再主表的主键列存在或者为null。

下面的代码就是从表的class_id和主表的id列进行了关联，将class_id字段设置了外键属性，那么当从表中插入的class_id数值在主表的id字段没有的时候就会出问题。

//创建班级表--主表
mysql> create table class (
    -> id int primary key,
    -> name varchar(20) not null 
    -> );
Query OK, 0 rows affected (0.03 sec)

//创建学生表--从表
mysql> create table stu
    -> ( id int primary key,
    -> name varchar(20) not null,
    -> class_id int,
    -> foreign key (class_id) references class(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

//正常插入数据
mysql> insert into class values(1, 'c++学习班'), (2, 'mysql学习班');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu values(100, '张三', 1), (101, '李四', 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

//插入一个主表中没有的id数据
mysql> insert into stu values(102, '王五', 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

//插入的时候外键属性字段设置为空
mysql> insert into stu values(102, '王五', null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+-----+--------+----------+
| id  | name   | class_id |
+-----+--------+----------+
| 100 | 张三   |        1 |
| 101 | 李四   |        2 |
| 102 | 王五   |     NULL |
+-----+--------+----------+
3 rows in set (0.00 sec)

如果说把上面的表合并到一个表的话，就会出现大量的数据冗余，假设增加了100个c++学习班的学生，那么他们每一个学生都需要添加一个c++学习班的字段值，就会造成数据冗余，而上面的分表+外键的形式，可以让c++学习班的值只存储一遍，然后通过外键约束和该值产生关联关系。

设置外键属性之后，就相当于是给该字段的取值设置了一个取值范围，该范围就是关联的主表中的列的所有取值。所以说也保证了数据的合法性。例如，上述就有两个班级，但是没有建立约束关系的话，可以在学生表中插入班级序号为3的数据，那么操作上不会有问题，但是业务逻辑上就出问题了。所以如果两张表在业务上有强相关性的时候，一般都要建立约束关系。

查看全文

http://www.kler.cn/a/516270.html