MySQL数据类型与完整性约束

在数据库开发中，数据类型的合理选择与正确使用是保障数据完整性、提升存储效率的基础。无论是基础的数值存储、字符匹配，还是特殊场景下的空值处理、二进制数据存储，都需要结合具体业务场景，熟练掌握数据类型的实操技巧。本文将结合实操案例，梳理数据库数据类型的核心操作，涵盖表的创建、数据插入、信息查看等关键步骤，方便大家自行测试验证。

前言

在数据库开发与日常运维中，数据类型的选择直接影响存储效率和数据准确性——比如用INT存储年龄（范围足够）比用BIGINT更节省空间，用DECIMAL存储金额比FLOAT更精准（避免精度丢失）。

而完整性约束则是“数据的守护者”，它能避免重复数据、非法数据插入，保证数据的一致性（比如用主键唯一标识每条记录，用外键关联两张表的关联数据）。

本文将从“数据类型分类→各类型实操测试→完整性约束测试”三个维度，一步步拆解实操细节。

核心数据类型分类及说明

数据类型大类	子类型	说明
数值类型	整数类型	INT（最常用，适合存储无小数的整数，如ID、年龄）
	浮点数类型	FLOAT、DOUBLE（用于存储小数，精度有限，适合非精准场景如身高、体重）
	定点数类型	DEC（精准小数，适合金融场景如金额、薪资）
	位类型	BIT（用于存储二进制数据，较少用）
字符串类型	字符系列	CHAR、VARCHAR（核心常用：char长度固定，varchar长度可变，如姓名、手机号）
	枚举类型	ENUM（65536个元素，单选，如性别、状态）
	集合类型	SET（64个元素，多选，如爱好、标签）
	TEXT系列	TEXT（ASCII编码，适合存储长文本，如文章内容、备注）
	BINARY系列	BINARY、VARBINARY（存储二进制字符串，与字符集无关）
	BLOB系列	BLOB（用于存储特殊符号、二进制文件如图片片段，不推荐存大文件）
	特殊符号存储	BLOB系列适用（避免特殊符号乱码）
时间和日期类型	年	YEAR（仅存储年份，如出生年份）
	日期	DATE（仅存储日期，如出生日期：YYYY-MM-DD）
	时间	TIME（仅存储时间，如打卡时间：HH:MM:SS）
	日期和时间	DATETIME（存储日期+时间，最常用，如注册时间、发布时间）

类型测试

整数类型测试：tinyint,int

核心用途：存储无小数的整数，如用户年龄、游戏等级、经验值、ID等，不同整数类型的范围不同，实操重点测试“范围限制”“无符号”“显示宽度”“零填充”四个核心特性。

LAB1:（int，tinyint的最大值）

前言

• TINYINT有符号型最大127
• INT有符号型最大2147483647

1.创建一个表
mysql> create table test1(      tinyint_test tinyint,    int_test int   );
Query OK, 0 rows affected (0.00 sec)

2.查询表结构
mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES  |     | NULL    |       |
| int_test     | int(11)    | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.插入数值
· 插入合法数值
mysql>  insert into test1 values (111,111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+--------------+----------+
| tinyint_test | int_test |
+--------------+----------+
|          111 |      111 |
+--------------+----------+
1 row in set (0.00 sec)

· 插入非法数值(TINYINT有符号型最大127)
mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

· 插入合法数值
mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.00 sec)

· 插入非法数值（INT有符号型最大2147483647）
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1

LAB2：（无符号unsigned）

前言：数值无符号（unsigned），即只能输入正值，不能输入负值，可扩大正数的存储范围（如TINYINT无符号最大255）。

1 创建一个表（约束条件unsigned限定只能存正值（无符号））
mysql> create table test2(
    ->  tinyint_test tinyint unsigned,      
    -> int_test int unsigned
    -> );
Query OK, 0 rows affected (0.01 sec)

2 查询表结构
mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES  |     | NULL    |       |
| int_test     | int(10) unsigned    | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3 插入数据
插入合法数据
mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (0.00 sec)

插入非法数据
mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

注意：mysql和mariadb不同。 mysql会提示输入错误 mariadb会输入0到表中。 但结果是肯定的，无符号只能输入正值

LAB3：（整数型，长度可变）

前言：重点区分“显示宽度”和“存储范围”

• 插入大于INT宽度限制的值，仍然可以存储。但不能超过INT的存储上限2147483647（有符号）
• INT整形的宽度仅为显示宽度，不是存储限制。因此建议整形无须指定宽度（如直接用INT，不用写INT(6)），字符型才需要指定宽度。

1 创建一个表
mysql> create table t1 (
 id1 int,
 id2 int(6)
 );
 
2 查询表结构
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | YES  |     | NULL    |       |
| id2   | int(6)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3 插入数据
mysql>  insert into t1 values(22,22222222);
Query OK, 1 row affected (0.00 sec)

4 查询数据
mysql> select * from t1;
+------+----------+
| id1  | id2      |
+------+----------+
|   22 | 22222222 |
+------+----------+
1 row in set (0.00 sec)

插入大于INT宽度限制的值，仍然可以存储。但不能超过上限2147483647

LAB4：（零填充zerofill）

前言： zerofill（零填充）特性，会自动用0填充字段，补全到指定的显示宽度，同时会自动将字段设为无符号。

1 创建一个表
mysql> create table t2 (
    ->  id1 int zerofill,
    -> id2 int(6) zerofill
    -> );
Query OK, 0 rows affected (0.00 sec)

2 查询表结构
mysql> desc t2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| id2   | int(6) unsigned zerofill  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3 插入数据
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.00 sec)

4 查询表内容
mysql> select * from t2;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)

浮点数类型测试：float

• 核心用途：用于存储带有小数的数值，如用户的身高、体重、薪水、商品价格等，重点区分“浮点数（float/double）”和“定点数（decimal）”的精度差异——浮点数精度有限，定点数精度精准。

  前言（补充注释：浮点数和定点数都可以用“类型名称后加(M,D)”的方式来表示，核心含义：）

  
  
  • (M,D)：M表示“总位数”（整数位+小数位），D表示“小数位数”
  
  
  • M和D又称为“精度”和“标度”，比如float(5,2)表示总位数5位，小数位2位，整数位最多3位
  
  

  三者区别介绍（float，double，decimal）（补充注释，便于对比选择）：

  • float: 浮点型，含字节数为4（32bit），数值范围为-3.4E38~3.4E38（7个有效位，小数点占一位），精度有限，适合非精准场景（如身高175.5cm）
  • double: 双精度实型，含字节数为8（64bit），数值范围-1.7E308~1.7E308（15个有效位），精度比float高，仍有微小误差
  • decimal: 数字型，128bit，常用于银行帐目、金额等精准计算（28个有效位），无精度丢失，是金融场景首选

LAB1：（浮点数float）

1 创建一个表（一共5位，小数占2位，整数3位）
mysql> create table test4(float_test float(5,2));
Query OK, 0 rows affected (0.01 sec)

2 查询表结构（float(5,2) 5是整数加小数的总长,2是小数长度。整数意味只有3位长度。）
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)


3 插入合法数据，非法数据
mysql>  insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


4 查询表内容
mysql> select * from test4;
+------------+
| float_test |
+------------+
|      10.20 |
|      70.24 |
|      70.25 |
+------------+
3 rows in set (0.00 sec)

5 插入非法数据
mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1

LAB2：（精准小数decimal）

前言：定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。decimal在不指定精度时，默认的整数位为10，默认的小数位为0

1 创建一个表（总长5位，小数占2位）
mysql> create table test5(decimal_test decimal(5,2));
Query OK, 0 rows affected (0.01 sec)

2 插入数据（注意有警告，超长部分不记录。会四舍五入）
mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test5;
+--------------+
| decimal_test |
+--------------+
|        70.25 |
+--------------+
1 row in set (0.00 sec)

时间和日期类型测试：year、date、time、datetime、timestamp

作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

LAB1：（日期，时间）

日期date和时间time类型测试

先了解一个函数now();
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2026-02-02 15:24:49 |
+---------------------+
1 row in set (0.00 sec)


1 创建一个表
mysql> create table test_time(
    ->  d date,
    ->  t time,
    ->  dt datetime);
Query OK, 0 rows affected (0.01 sec)

2 查看表结构
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

3 插入时间
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)


4.查看表内容（时间日期分门别类存储）
mysql> select * from   test_time;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2026-02-02 | 15:25:21 | 2026-02-02 15:25:21 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

LAB2：(当前时间)（了解）

当前时间timestamp类型测试

1.创建一个表
mysql> create table t(id timestamp);
Query OK, 0 rows affected (0.01 sec)

2.查询表结构
mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)


3.插入数据（插入控制，也可手动插入时间，以插入时间为准。年月日时分秒）
mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (201206150859);
Query OK, 1 row affected (0.00 sec)


4.查看数据
mysql> select * from t;
+---------------------+
| id                  |
+---------------------+
| 2026-02-02 15:31:31 |
| 2020-12-06 15:08:59 |
+---------------------+
2 rows in set (0.00 sec)

LAB3:(年)

年YEAR类型测试

注意（其它的时间，按要求插入）

• 插入年份时，尽量使用4位值
• 插入两位年份时，<=69，默认以20开头，比如65, 结果2065 , >=70，默认以19开头，比如82，结果1982

1.创建一个表
mysql> create table t5(born_year year);
Query OK, 0 rows affected (0.01 sec)

2.查询表结构
mysql> desc t5;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


3.插入数据
mysql> insert into t5 values (12),(80);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


4.查看数据(观察年份的边界)
mysql> select * from t5;
+-----------+
| born_year |
+-----------+
|      2012 |
|      1980 |
+-----------+
2 rows in set (0.00 sec)

字符串类型测试：CHAR、VARCHAR

LAB1:(字符、变长字符)

作用：用于存储用户的姓名、爱好、发布的文章等

注意二者区别：

• CHAR 列的长度固定为创建表时声明的长度： 0 ~ 255

  
  

• VARCHAR 列中的值为可变长字符串，长度： 0 ~ 65535

  
  

• 在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格

  
  

• CHAR的长度是固定的，VARCHAR长度是可以变化的

  
  

  固定与可变是针对存储介质（硬盘）来说的
  假如：
  CHAR和VARCHAR的默认长度都设为10，两个字段都分别写入“abc”
  CHAR 损耗了硬盘10字节 = “abc”长度 + 7个空字符
  VARCHAR损耗了硬盘 3字节 = “abc”长度
  设定默认值n（假如是 10 ） 则该字段内能写入的字符串长度最大只能为 10

  
  

1.创建一个表
mysql> create table vc (
    -> v varchar(4),
    ->  c char(4));
Query OK, 0 rows affected (0.01 sec)


2.查询表结构
mysql> desc vc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v     | varchar(4) | YES  |     | NULL    |       |
| c     | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


3.插入数据
mysql> insert into vc values('a','a');
Query OK, 1 row affected (0.00 sec)

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

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


4.查看数据（看着并无区别）
mysql> select * from vc;
+------+------+
| v    | c    |
+------+------+
| a    | a    |
| ac   | ac   |
| ab   | ab   |
+------+------+
3 rows in set (0.00 sec)


5.调用函数查看， length(v) 统计长度的函数
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         1 |         1 |
|         2 |         2 |
|         4 |         2 |
+-----------+-----------+
3 rows in set (0.00 sec)

6.调用函数查看，在后面加字符'='，看的更清楚（ concat(v,'=') 拼接函数）
mysql> select concat(v,'='), concat(c,'=') from vc;
+---------------+---------------+
| concat(v,'=') | concat(c,'=') |
+---------------+---------------+
| a=            | a=            |
| ac=           | ac=           |
| ab  =         | ab=           |
+---------------+---------------+
3 rows in set (0.00 sec)

LAB2：（了解）（二进制字符）

前言：二进制字符串 类型包括：binary、varbinary、blob，主要是处理图像、视频、音频等文件，与字符集无关。

这类文件一般是放在服务器硬盘里，而不是数据库里，如果要放在数据库，就一定不能指定字符集类型，否则会把二进制字段转换成相应的非二进制字符，图像和视频就不能正常显示了。

关于非二进制字符串：非二进制字符串类型：包括：char、varchar、text，主要用于处理文本格式的文件。

字符集就是一堆字符的集合，字符集的编码格式主要有以下几种：

1、utf-8：基于unicode编码（万国码），国际标准化组织制定的一套包含了世界上所有编码类型的字符，如果一个网页上想同时显示简体、繁体以及其他地区的一些字符，就可以使用utf8编码。每个字符的长度是1-3个字节，比如说【a】是一个字节，【盾】是3个字节。

2、gb2312：简体字，包含6700多个汉字，每个汉字相当于2个字节，每个英文单词相当于1个字节。

3、gbk：简体字和繁体字，包含21000多个汉字，由中国内地编写的编码库，每个汉字相当于2个字节，每个英文单词相当于1个字节。

4、big5：繁体字，包含13000多个汉字，也叫大五码，每个汉字相当于2个字节，每个英文单词相当于1个字节。

字符串类型测试：BINARY、VARBINARY BINARY 和 VARBINARY类似于CHAR 和 VARCHAR， 不同的是它们包含二进制字符，而不包含非二进制字符串

1.创建一个表
mysql> create table binary_t (c binary(3));
Query OK, 0 rows affected (0.01 sec)

2.查询表结构
mysql> desc      binary_t;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c     | binary(3) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.01 sec)


3.插入数据
mysql> insert into binary_t set c='aaa';
Query OK, 1 row affected (0.00 sec)

4.查看数据
mysql> select *,hex(c) from binary_t;
+------+--------+
| c    | hex(c) |
+------+--------+
| aaa  | 616161 |
+------+--------+
1 row in set (0.00 sec)

set解释：在标准的SQL语句中，一次插入一条记录的INSERT语句只有一种形式。INSERT INTO tablename(列名…) VALUES(列值);

而在MySQL中还有另外一种形式。INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;

1. 第一种方法将列名和列值分开了，在使用时，列名必须和列值的数一致。如下面的语句向users表中插入了一条记录：INSERT INTO users(id, name, age) VALUES(123, ‘姚明’, 25);

   
   

2. 第二种方法允许列名和列值成对出现和使用，如下面的语句将产生中样的效果。INSERT INTO users SET id = 123, name = ‘姚明’, age = 25;

   
   

616161解释：a是字符的话，对应ascII码是97，则二进制为01100001,转成16进制为61

枚举类型、集合类型：ENUM类型，SET测试

LAB1：（单选、多选）

作用

• 字段的值只能在给定范围中选择
• enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
• set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

目标：

1. 表school.student3
2. 姓名 name varchar(50)
3. 性别 sex enum(‘m’,’f’)
4. 爱好 hobby set(‘music’,’book’,’game’,’disc’)

1.创建一个表
mysql>  create table student3(
    ->  name varchar(50),
    ->  sex enum('m','f'),
    ->  hobby set('music','book','game','disc') );
Query OK, 0 rows affected (0.01 sec)

2.查询表结构
mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('m','f')                     | YES  |     | NULL    |       |
| hobby | set('music','book','game','disc') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


3.插入数据
mysql> insert into student3 values  ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)

插入非法数据（超出范围）
mysql> insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1


4.查看数据
mysql> select * from student3;
+------+------+-----------+
| name | sex  | hobby     |
+------+------+-----------+
| tom  | m    | book,game |
+------+------+-----------+
1 row in set (0.00 sec)

完整性约束

约束目的

由于生活中需要避免重名的情况,所以数据库中某列使用完整性约束来限定此类要求；核心目的是用于保证数据的完整性和一致性

约束类型及说明

约束条件	说明	示例关联
PRIMARY KEY (PK)	标识该字段为该表的主键，可以唯一的标识记录，不可以为空	员工信息表，UNIQUE+ NOT NULL
FOREIGN KEY (FK)	标识该段为该表的外健，实现表与表(父表主键/子表1外键/子表2外键)之间的关联	员工工资表
UNIQUE KEY (UK)	标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY	–
AUTO INCREMENT	标识该字段的值自动增长(整数类型，而且为主键)	–
DEFAULT	为该段设置默认值，示例：sex enum(‘male, female’) not null default ‘male’ //默认值：male	–
NOT NULL	是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值	–
UNSIGNED	无符号，正数	–
ZEROFILL	使用0填充，例如0000001	–

约束类型测试

默认值、空值：DEFAULT、NOT NULL

目的：

• 某列设置默认值，并不输入数值，该列是会显示什么。
• 2 某列设置为“NOT NULL”，请思考能不能为空呢。

1.创建一个表
mysql>  create table student.student4(
    ->  id int not null, 
    -> name varchar(50) not null, 
    -> sex enum ('m','f') default 'm' not null, 
    -> age int unsigned default 18 not null, 
    -> hobby set('music','disc','dance','book') default 'book,dance' );
Query OK, 0 rows affected (0.01 sec)

2.查询表结构
mysql> desc student4;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(50)                        | NO   |     | NULL       |       |
| sex   | enum('m','f')                      | NO   |     | m          |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| hobby | set('music','disc','dance','book') | YES  |     | dance,book |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.00 sec)


3.插入数据
mysql> insert into student4 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)


4.查看数据
mysql> select * from student4;
+----+-------+-----+-----+------------+
| id | name  | sex | age | hobby      |
+----+-------+-----+-----+------------+
|  1 | jack  | m   |  20 | book       |
|  2 | robin | m   |  18 | dance,book |
+----+-------+-----+-----+------------+
2 rows in set (0.00 sec)

5.插入非法数据（注意观察输出错误的结果，理解默认值和空值的含义。NULL函数是空值的意思。）
mysql> insert into student4 values(3,NULL,'m',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null

6.空格也不是空值
mysql> insert into student4 values (4,'','m',22,'music');
Query OK, 1 row affected (0.00 sec)

设置主键约束 PRIMARY KEY

目的：

• primary key 字段的值是不允许重复，且不允许NULL（UNIQUE + NOT NULL）
• 单列做主键
• 多列做主键（复合主键）

1.创建一个表
mysql> create table student6(
    ->  id int primary key not null auto_increment,
    ->  name varchar(50) not null,
    ->  sex enum('male','female') not null default 'male', 
    ->  age int not null default 18
    ->  );
Query OK, 0 rows affected (0.01 sec)


2.查询表结构
mysql> desc student6;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | NO   |     | NULL    |                |
| sex   | enum('male','female') | NO   |     | male    |                |
| age   | int(11)               | NO   |     | 18      |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


3.插入数据
mysql> insert into student6 values (1,'alice','female',22);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student6(name,sex,age) values
    ->  ('jack','male',19),
    ->  ('tom','male',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


4.查看数据（注意观察id列，并没有输入内容，自动增长）
mysql>  select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
|  2 | jack  | male   |  19 |
|  3 | tom   | male   |  23 |
+----+-------+--------+-----+
3 rows in set (0.00 sec)

5.插入非法数据
mysql>  insert into student6(name,sex,age) values  (3,'jack','male',19);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

6 主键设置了自动增长，再次尝试插入数据。成功（注意不要插入主键）
mysql>  insert into student6(name,sex,age) values  ('jack','male',19);
Query OK, 1 row affected (0.00 sec)

mysql>  select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
|  2 | jack  | male   |  19 |
|  3 | tom   | male   |  23 |
|  4 | jack  | male   |  19 |
+----+-------+--------+-----+
4 rows in set (0.00 sec)

设置唯一约束 UNIQUE

目的：

• unique唯一的特性。
• unique是可以为空的。
• 为部门创建一张员工信息表

1.创建一个表
方法一
mysql> CREATE TABLE company.department1 (
    -> dept_id INT,
    -> dept_name VARCHAR(30) UNIQUE,
    -> comment VARCHAR(50));
Query OK, 0 rows affected (0.00 sec)

2.查询表结构
mysql> desc company.department1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.插入数据
插入合法数据
mysql> insert into department1 values (1,'zhangsan','yyy');
Query OK, 1 row affected (0.00 sec)

插入空值数据
mysql>  insert into department1 values (1,NULL,'yyy');
Query OK, 1 row affected (0.01 sec)

插入空值数据（空值是允许重复的）
mysql>  insert into department1 values (1,NULL,'yyy');
Query OK, 1 row affected (0.01 sec)

插入非法数据
mysql> insert into department1 values (1,'zhangsan','yyy');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'dept_name'

4.查看数据
mysql> select * from department1;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
|       1 | zhangsan  | yyy     |
|       1 | NULL      | yyy     |
|       1 | NULL      | yyy     |
+---------+-----------+---------+
3 rows in set (0.00 sec)

设置外键约束 FOREIGN KEY

目的:

• 使两张表产生关联，同步更新内容。
• 创建员工信息表，创建员工薪资表。观察同步效应

1.创建父表company.employees
mysql> create table employees(
    ->  name varchar(50) not null, 
    ->  mail varchar(20),
    ->  primary key(name) 
    ->  )engine=innodb;
Query OK, 0 rows affected (0.01 sec)

2.创建子表company.payroll
mysql> create table payroll(
    -> id int not null auto_increment,
    -> name varchar(50) not null,   
    -> payroll float(10,2) not null,
    ->  primary key(id),
    -> foreign key(name)  references employees(name) on update cascade on delete cascade
    ->  )engine=innodb; 
Query OK, 0 rows affected (0.00 sec)


3.查询表结构
mysql> desc employees;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   | PRI | NULL    |       |
| mail  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc payroll;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | NO   | MUL | NULL    |                |
| payroll | float(10,2) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

3.插入数据
mysql> insert into employees values ('zhangsan','zhangsan@126.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into payroll values (1,'zhangsan',20000.23);
Query OK, 1 row affected (0.00 sec)


4.查看数据
mysql>  select * from employees;
+----------+------------------+
| name     | mail             |
+----------+------------------+
| zhangsan | zhangsan@126.com |
+----------+------------------+
1 row in set (0.00 sec)

mysql> select * from payroll;
+----+----------+----------+
| id | name     | payroll  |
+----+----------+----------+
|  1 | zhangsan | 20000.23 |
+----+----------+----------+
1 row in set (0.00 sec)


5.更新父表，观察子表的变化
mysql> update employees set name='zhangsansss' where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from payroll;
+----+-------------+----------+
| id | name        | payroll  |
+----+-------------+----------+
|  1 | zhangsansss | 20000.23 |
+----+-------------+----------+
1 row in set (0.00 sec)


5.删除父表，观察子表
mysql> delete from employees where name='zhangsansss';
Query OK, 1 row affected (0.00 sec)

mysql> select * from payroll;
Empty set (0.00 sec)

总结：

• 当父表中某个员工的记录修改时，子表也会同步修改
• 当父表中删除某个记录，子表也会同步删除该记录。

设置复合主键约束 PRIMARY KEY

目的：

• 解决单列主键无法保持唯一性的问题
• 例如：记录主机地址和服务的数据表是否运行的表。

目标：

表	school.service
host_ip	主机IP
service_name	服务名
por	服务对应的端口
allow(Y,N)	服务是否允许访问
主键	host_ip + port = primary key

1.创建一个表（关键部分，复合主键设置方法，primary key(host_ip,port)）
mysql>  create table service(
    -> host_ip varchar(15) not null,
    -> service_name varchar(10) not null,
    -> port varchar(5) not null,
    -> allow enum('Y','N') default 'N',
    -> primary key(host_ip,port)
    -> );
Query OK, 0 rows affected (0.01 sec)


2.查询表结构（主要看Key列的两个PRI值）
mysql>  desc service;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| host_ip      | varchar(15)   | NO   | PRI | NULL    |       |
| service_name | varchar(10)   | NO   |     | NULL    |       |
| port         | varchar(5)    | NO   | PRI | NULL    |       |
| allow        | enum('Y','N') | YES  |     | N       |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


3.插入数据
mysql> insert into service values ('192.168.2.168','ftp','21','Y');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into service values ('192.168.2.168','httpd','80','Y');
Query OK, 1 row affected (0.01 sec)

4.在mysql系统内部对于mysql账户的记录就是复合主键（用户名+主机地址）
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |

总结：复合主键是当多列值组合唯一。

设置字段值增 AUTO_INCREMENT

目的：配合整数型，主键使用自动增长约束。

1.创mysql> CREATE TABLE department3 (
    -> dept_id INT PRIMARY KEY AUTO_INCREMENT,
    -> dept_name VARCHAR(30),
    -> comment VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)
建一个表
mysql> CREATE TABLE department3 (
    -> dept_id INT PRIMARY KEY AUTO_INCREMENT,
    -> dept_name VARCHAR(30),
    -> comment VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)


2.查询表结构
mysql> desc department3;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| dept_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| dept_name | varchar(30) | YES  |     | NULL    |                |
| comment   | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


3.插入数据
插入合法数据
mysql>  insert into department3 values(1,'zs','manager');
Query OK, 1 row affected (0.00 sec)

插入非法数据
mysql> insert into department3 values(1,'zs','manager');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

插入合法数据
mysql> insert into department3 values(2,'zs','manager');
Query OK, 1 row affected (0.01 sec)

插入合法数据。未插入序号，但是在查询时会自动增加。
mysql> insert into department3 (dept_name,comment) values('zs','manager');
Query OK, 1 row affected (0.01 sec)


4.查看数据
mysql> select * from  department3;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
|       1 | zs        | manager |
|       2 | zs        | manager |
|       3 | zs        | manager |
+---------+-----------+---------+
3 rows in set (0.00 sec)