【十二】【SQL】表的约束

NOT NULL非空约束

展示对NULL的查询

mysql> select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 1+NULL;
+--------+
| 1+NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> 

第一条查询：select NULL;

代码：select NULL;

解释：这条SQL语句查询了一个NULL值。SELECT语句被用来从数据库表中选择数据，但在这个例子中，它并没有从任何表中选择数据，而是直接返回了一个NULL值。NULL代表“没有值”或“未知值”。查询结果显示了一个包含单个NULL值的列。

第二条查询：select 1+NULL;

代码：select 1+NULL;

解释：这条SQL语句尝试将数字1与NULL值相加。在SQL中，任何数与NULL进行算术运算的结果都是NULL。这是因为NULL代表未知或缺失的数据，所以当它参与到算术运算中时，结果也是未知的。因此，查询结果是NULL。

对NULL的理解：

在数据库中，NULL用来表示缺失的或不适用的数据。NULL有其特殊的行为规则，尤其是在比较和计算中。

当NULL参与到比较运算中时，结果通常也是NULL，因为与未知值的比较结果自身也是未知的。

在算术运算中，任何与NULL的运算结果都是NULL，如上面的例子所示。

NOT NULL非空约束探究

mysql> create table myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(10) not null,
    -> other varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
| other      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table myclass\G
*************************** 1. row ***************************
       Table: myclass
Create Table: CREATE TABLE `myclass` (
  `class_name` varchar(20) NOT NULL,
  `class_room` varchar(10) NOT NULL,
  `other` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into myclass (class_name ,class_room ,other) values('高二2班','101教室','普通版');
Query OK, 1 row affected (0.00 sec)

mysql> select*from myclass;
+------------+------------+-----------+
| class_name | class_room | other     |
+------------+------------+-----------+
| 高二2班    | 101教室    | 普通版    |
+------------+------------+-----------+
1 row in set (0.00 sec)

mysql> insert into myclass(class_name,class_room )values ('高三3班','103教室');
Query OK, 1 row affected (0.00 sec)

mysql> select *from myclass;
+------------+------------+-----------+
| class_name | class_room | other     |
+------------+------------+-----------+
| 高二2班    | 101教室    | 普通版    |
| 高三3班    | 103教室    | NULL      |
+------------+------------+-----------+
2 rows in set (0.00 sec)

mysql> insert into myclass(class_name) values ('高三2班');
ERROR 1364 (HY000): Field 'class_room' doesn‘t have a default value
mysql> insert into myclass(class_name,class_room )values ('高三3班', NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null
mysql> insert into myclass(class_name,class_room )values (NULL, NULL);
ERROR 1048 (23000): Column 'class_name' cannot be null

创建表

create table myclass( class_name varchar(20) not null, class_room varchar(10) not null, other varchar(20));

这条命令创建了一个名为myclass的表，包含三个列：class_name、class_room和other。class_name和class_room列被设置为NOT NULL，表示这两个字段在插入数据时必须提供值，不能为NULL。other列没有设置NOT NULL约束，因此它可以接受NULL值。

描述表结构

desc myclass;

这条命令显示了myclass表的结构，包括字段名、数据类型、是否允许NULL值、键类型、默认值以及其他额外信息。从结果可以看出，class_name和class_room列不允许NULL值，而other列允许NULL值。

显示创建表的SQL语句

show create table myclass\G

这条命令展示了用于创建myclass表的完整SQL语句。它确认了class_name和class_room列为NOT NULL，而other列默认为NULL，即如果在插入数据时未指定other列的值，它将默认为NULL。

插入数据

insert into myclass (class_name ,class_room ,other) values('高二2班','101教室','普通版');

向myclass表中插入一行数据，指定了所有列的值。

insert into myclass(class_name,class_room )values ('高三3班','103教室');

向myclass表中插入一行数据，仅指定了class_name和class_room列的值，other列因为允许NULL，所以自动设为NULL。

显示表数据

select * from myclass;

查询并显示myclass表中的所有行。结果显示了之前插入的两行数据。

尝试插入不符合约束的数据

insert into myclass(class_name) values ('高三2班');

尝试仅提供class_name列的值插入数据，因为class_room列是NOT NULL，此操作失败，并显示错误，提示class_room列没有默认值。

insert into myclass(class_name,class_room )values ('高三3班', NULL);

尝试将class_room列的值设为NULL插入数据，操作失败，因为class_room列被定义为NOT NULL。

insert into myclass(class_name,class_room )values (NULL, NULL);

尝试将class_name和class_room列的值都设为NULL插入数据，操作失败，因为这两个列都被定义为NOT NULL。

NOT NULL（非空）和DEFAULT（缺省）的探究

在数据库设计和数据管理中，NOT NULL（非空）和DEFAULT（缺省）约束是两个非常重要的概念，它们对确保数据的完整性和一致性起着关键作用。下面我将分别探究这两个约束，并说明它们如何一起使用以及它们各自的重要性。

NOT NULL（非空）约束

定义：NOT NULL约束用于确保一个字段不能存储NULL值，即该字段在插入或更新记录时必须有一个明确的值。这个约束非常适用于那些在业务逻辑上必须有值的字段，比如用户的姓名、账号和密码等。

重要性：通过防止字段值为NULL，NOT NULL约束保证了数据的完整性和准确性。在没有这个约束的情况下，字段可能会存储NULL值，这可能会导致应用程序出错或业务逻辑失败，因为可能会预期到某些字段是有具体值的。

DEFAULT（缺省）约束

定义：DEFAULT约束为表中的字段定义一个默认值。如果在插入记录时没有为该字段提供值，数据库会自动填充这个默认值。DEFAULT值可以是一个常数（如数字、字符串）或系统函数（如CURRENT_TIMESTAMP）。

重要性：DEFAULT约束使得数据插入更加灵活和高效。它可以确保即使在用户未提供某些字段的值时，这些字段也能自动赋予一个预定义的值，从而保持数据的完整性和一致性。特别是对于那些有逻辑默认值的字段，如注册日期可以默认为当前日期，性别可以默认为'未知'等。

NOT NULL 和 DEFAULT 一起使用

将NOT NULL和DEFAULT约束一起使用可以确保字段既不会为NULL也会有一个合理的默认值。这对于那些既不能空也有一个合理默认值的字段特别有用。

示例：考虑一个员工表，其中的入职日期字段对于每个员工都是必需的，且如果未明确提供，应默认为记录插入的日期。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL DEFAULT CURRENT_DATE
);

在这个例子中，name和start_date字段都使用了NOT NULL约束，确保这些字段在插入新记录时必须有值。此外，start_date还定义了一个DEFAULT值CURRENT_DATE，这意味着如果在插入新员工记录时未指定入职日期，系统会自动将其设置为当前日期。

总结

NOT NULL和DEFAULT约束各自以及共同地为保持数据库的数据完整性和一致性提供了强大的支持。NOT NULL确保了关键字段在业务逻辑上不会缺失重要的值，而DEFAULT约束提供了灵活性，确保即使数据未被明确提供也能自动填充合理的默认值。在数据库设计时恰当地使用这些约束，可以显著提升数据管理的效率和准确性。

mysql> create table if not exists t13(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> gender char(1) default '男'
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t13;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t13 (name,age,gender)values('张三',19,'男');
Query OK, 1 row affected (0.00 sec)

mysql> select*from t13;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 男     |
+--------+------+--------+
1 row in set (0.00 sec)

mysql> insert into t13(name)values('李四');
Query OK, 1 row affected (0.00 sec)

mysql> select *from t13;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 男     |
| 李四   |   18 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

mysql> insert into t13 (name,age,gender)values('小美',19,'女');
Query OK, 1 row affected (0.00 sec)

mysql> select *from t13;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 男     |
| 李四   |   18 | 男     |
| 小美   |   19 | 女     |
+--------+------+--------+
3 rows in set (0.00 sec)

mysql> create table t14(
    -> name varchar(20) not null,
    -> age tinyint default 18,
    -> gender char(1) not null default'男'
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t14;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | NO   |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | 18      |       |
| gender | char(1)     | NO   |     | 男      |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t14\G
*************************** 1. row ***************************
       Table: t14
Create Table: CREATE TABLE `t14` (
  `name` varchar(20) NOT NULL,
  `age` tinyint(4) DEFAULT '18',
  `gender` char(1) NOT NULL DEFAULT '男'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t14 (name ,age,gender )values (NULL,20,'男');
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into t14 (age,gender )values (20,'男');
ERROR 1364 (HY000): Field 'name' doesn‘t have a default value
mysql> insert into t14 (name ,age,gender )values ('张三',20,'男');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t14 (name ,age,gender )values ('张三',20,NULL);
ERROR 1048 (23000): Column 'gender' cannot be null
mysql> insert into t14 (name ,age)values ('张三',20);
Query OK, 1 row affected (0.01 sec)

mysql> select *from t14;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   20 | 男     |
| 张三   |   20 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> show create table t14\G
*************************** 1. row ***************************
       Table: t14
Create Table: CREATE TABLE `t14` (
  `name` varchar(20) NOT NULL,
  `age` tinyint(4) DEFAULT '18',
  `gender` char(1) NOT NULL DEFAULT '男'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t14(name ,age,gender)values('张三',30,'女');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t14(name ,age,gender)values('张三',NULL,'女');
Query OK, 1 row affected (0.00 sec)

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

mysql> select *from t14;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   20 | 男     |
| 张三   |   20 | 男     |
| 张三   |   30 | 女     |
| 张三   | NULL | 女     |
| 张三   |   18 | 女     |
+--------+------+--------+
5 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> create table if not exists t15(
    -> name varchar(20),
    -> age tinyint unsigned
    -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> select*from t15;
+--------+------+
| name   | age  |
+--------+------+
| 张三   |   18 |
+--------+------+
1 row in set (0.00 sec)

mysql> insert into t15 (name ,age)values(NULL,18);
Query OK, 1 row affected (0.00 sec)

mysql> select *from t15;
+--------+------+
| name   | age  |
+--------+------+
| 张三   |   18 |
| NULL   |   18 |
+--------+------+
2 rows in set (0.00 sec)

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

mysql> show create table t15\G
*************************** 1. row ***************************
       Table: t15
Create Table: CREATE TABLE `t15` (
  `name` varchar(20) DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

COMMENT约束

COMMENT约束在数据库设计中不严格意义上是一个“约束”，而是一种在表或列级别添加注释的方式。这些注释不会影响数据的完整性或逻辑约束，但它们为数据库设计提供了额外的文档支持，有助于开发者和数据库管理员理解数据库结构的细节和业务逻辑。

COMMENT的作用

文档化：COMMENT允许开发者在表和列的定义中直接添加有关信息，如字段用途、业务规则说明、数据来源等。这有助于新团队成员快速理解数据库结构和特定字段的意图。

自文档化：随着数据库结构变得复杂，含有适当注释的表和字段可以自我说明，减少查阅外部文档的需要。

维护和更新的便利性：有了清晰的注释，未来对数据库结构进行维护或更新时，可以更容易地理解原有设计的考虑和约束，减少误解和错误。

如何使用 COMMENT

在MySQL中，可以在创建表或列时使用COMMENT关键字添加注释。注释文本需要被单引号包围。

列级别的COMMENT：

在创建或修改表结构时，可以为每个字段指定一个COMMENT。例如：

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '员工的全名',
    start_date DATE COMMENT '员工的入职日期'
);

这里，name列的注释是“员工的全名”，而start_date列的注释是“员工的入职日期”。

表级别的COMMENT：

也可以为整个表添加注释，说明表的用途或其他相关信息。例如：

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    start_date DATE
) COMMENT='存储员工基本信息及入职日期';

这个例子中，整个employee表有一个注释“存储员工基本信息及入职日期”。

查看 COMMENT

在MySQL中，可以通过SHOW CREATE TABLE命令查看表的创建语句，其中包含了字段和表的COMMENT信息。另外，查询INFORMATION_SCHEMA.COLUMNS表也可以查看列的注释。

总结

尽管COMMENT不直接影响数据库的逻辑功能或数据完整性，它在数据库设计和维护中扮演着重要的辅助角色。合理使用COMMENT可以极大地提高数据库的可读性和可维护性，是数据库文档化的一个重要工具。

mysql> create table if not exists t16(
    -> name varchar(20) not null comment '这个是用户的用户名',
    -> age tinyint unsigned default 18 comment '这个是用户的年龄',
    -> gender char(1)default'男' comment '这个是用户的性别'
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t16;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t16 values('张三',19,'女');
Query OK, 1 row affected (0.01 sec)

mysql> select *from t16;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
+--------+------+--------+
1 row in set (0.00 sec)

mysql> show create table t16 \G
*************************** 1. row ***************************
       Table: t16
Create Table: CREATE TABLE `t16` (
  `name` varchar(20) NOT NULL COMMENT '这个是用户的用户名',
  `age` tinyint(3) unsigned DEFAULT '18' COMMENT '这个是用户的年龄',
  `gender` char(1) DEFAULT '男' COMMENT '这个是用户的性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

ZEROFILL约束

ZEROFILL是MySQL中的一个特定属性，它不是标准SQL的一部分。当你在一个数值字段上应用ZEROFILL属性时，MySQL会自动为该字段添加UNSIGNED属性，并且在必要时用零填充该字段的前面，以确保它达到指定的宽度。这对于需要固定长度表示的数值，如产品代码、员工ID等场景特别有用。

ZEROFILL的作用

零填充：ZEROFILL使得数值字段的显示格式统一，对于不足指定长度的数值，会在前面补零。这对于报表生成和数据展示非常有帮助，因为它保证了数据的一致性和易读性。

自动添加UNSIGNED：应用ZEROFILL属性的字段自动变为无符号数（UNSIGNED），这意味着该字段只能存储正数。

如何使用 ZEROFILL

在定义或修改表结构时，可以为数值类型的列指定ZEROFILL属性。以下是一个简单的例子：

CREATE TABLE my_table (
    id INT(4) ZEROFILL AUTO_INCREMENT PRIMARY KEY,
    salary DECIMAL(8,2) ZEROFILL
);

在这个例子中，id列被定义为整型，长度为4，使用了ZEROFILL属性。如果id的值为1，则在查询时显示为0001。

salary列被定义为十进制类型，整数部分长度为8（包括前面的零），小数部分长度为2，同样使用了ZEROFILL属性。如果salary的值为123.45，则在查询时显示为0000123.45。

注意事项

存储和查询：ZEROFILL仅影响数据的显示方式，而不影响实际存储的数据。即，虽然在查询结果中数值前面会补零，但在数据库内部，这些数值是不包含前导零的。

可移植性：由于ZEROFILL不是所有数据库系统都支持的标准SQL特性，依赖于ZEROFILL的数据库设计可能在迁移到其他数据库系统时遇到问题。

使用场合：虽然ZEROFILL可以使输出更加整齐，但在某些情况下，使用应用程序层面的格式化可能更加灵活和可控。

总结

ZEROFILL属性在MySQL中用于数值字段的显示格式化，通过在数值前自动填充零来达到指定的宽度，同时确保字段值为正数。它在特定的用例中非常有用，尤其是当你希望数据在视觉上保持一致，或者需要固定长度的数值表示时。

mysql> create table if not exists t17(
    -> a int unsigned not null,
    -> b int unsigned not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t17;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| a     | int(10) unsigned | NO   |     | NULL    |       |
| b     | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table t17\G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t17 (a,b)values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select *from t17;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> alter table t17 modify b int unsigned zerofill;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t17;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| a     | int(10) unsigned          | NO   |     | NULL    |       |
| b     | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t17 modify b int unsigned zerofill not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t17;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| a     | int(10) unsigned          | NO   |     | NULL    |       |
| b     | int(10) unsigned zerofill | NO   |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select *from t17;
+---+------------+
| a | b          |
+---+------------+
| 1 | 0000000002 |
+---+------------+
1 row in set (0.00 sec)

mysql> insert into t17(a,b)values (100,200);
Query OK, 1 row affected (0.00 sec)

mysql> select *from t17 where b=200;
+-----+------------+
| a   | b          |
+-----+------------+
| 100 | 0000000200 |
+-----+------------+
1 row in set (0.00 sec)

mysql> select a,hex(b) from t17;
+-----+--------+
| a   | hex(b) |
+-----+--------+
|   1 | 2      |
| 100 | C8     |
+-----+--------+
2 rows in set (0.00 sec)

mysql> alter table t17 modify b int(1) unsigned zerofill not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from t17;
+-----+-----+
| a   | b   |
+-----+-----+
|   1 |   2 |
| 100 | 200 |
+-----+-----+
2 rows in set (0.00 sec)

mysql> alter table t17 modify b int(4) unsigned zerofill not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from t17;
+-----+------+
| a   | b    |
+-----+------+
|   1 | 0002 |
| 100 | 0200 |
+-----+------+
2 rows in set (0.00 sec)

mysql> insert into  t17 (a,b) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into  t17 (a,b) values (1,11);
Query OK, 1 row affected (0.01 sec)

mysql> insert into  t17 (a,b) values (1,111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into  t17 (a,b) values (1,1111);
Query OK, 1 row affected (0.01 sec)

mysql> insert into  t17 (a,b) values (1,11111);
Query OK, 1 row affected (0.01 sec)

mysql> insert into  t17 (a,b) values (1,111111);
Query OK, 1 row affected (0.00 sec)

mysql> select *from t17;
+-----+--------+
| a   | b      |
+-----+--------+
|   1 |   0002 |
| 100 |   0200 |
|   1 |   0001 |
|   1 |   0011 |
|   1 |   0111 |
|   1 |   1111 |
|   1 |  11111 |
|   1 | 111111 |
+-----+--------+
8 rows in set (0.00 sec)

mysql> show create table t17\G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `a` int(10) unsigned NOT NULL,
  `b` int(4) unsigned zerofill NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table t17 modify a int not null;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table t17 modify b int unsigned not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t17\G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `a` int(11) NOT NULL,
  `b` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table t17 modify b int not null;
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show create table t17\G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

PRIMARY KEY约束

PRIMARY KEY约束在数据库设计中非常重要，它用于唯一标识数据库表中的每一行记录。一个表只能有一个PRIMARY KEY，该键由一个或多个字段组成。当你在表中定义了PRIMARY KEY约束时，数据库系统会自动确保该键的值是唯一的，并且不允许NULL值。

PRIMARY KEY的作用

唯一性保证：PRIMARY KEY约束确保了表中每一行都可以被唯一标识，没有重复的行。这对于数据的完整性和一致性至关重要。

数据完整性：由于主键值不允许NULL，这保证了表中每一行的主键字段都必须有一个明确的值。

索引创建：在大多数数据库系统中，定义PRIMARY KEY会自动为该键创建一个唯一索引。这不仅强制了唯一性约束，还提高了基于主键的查询和数据操作的性能。

如何使用 PRIMARY KEY

单列主键：如果表的主键只需要一个字段，可以直接在字段定义后添加PRIMARY KEY约束。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

在这个例子中，student_id是主键，用于唯一标识students表中的每一条记录。

复合主键：如果主键由多个字段组成，可以在表定义的末尾指定这些字段。

CREATE TABLE course_registrations (
    student_id INT,
    course_id INT,
    registration_date DATE,
    PRIMARY KEY (student_id, course_id)
);

这个例子中，student_id和course_id共同构成了复合主键，用于唯一标识course_registrations表中的每一条记录。

总结

PRIMARY KEY约束是数据库设计的基石，它确保了数据的唯一性和完整性。合理地使用PRIMARY KEY可以提高数据库操作的效率和准确性，但设计主键时需要考虑到性能影响和未来的扩展性。

mysql> create table if not exists test_key(
    -> id int unsigned primary key comment '这个是学生的学号',
    -> name varchar(20) not null);
Query OK, 0 rows affected (0.03 sec)

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

mysql> show create table test_key\G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '这个是学生的学号',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

mysql> select*from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
+----+--------+
1 row in set (0.00 sec)

mysql> insert into test_key values(1,'刘备');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test_key values(2,'刘备');
Query OK, 1 row affected (0.01 sec)

mysql> select *from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
+----+--------+
2 rows in set (0.00 sec)

mysql> update test_key set name'曹老板' where id=2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''曹老板' where id=2' at line 1
mysql> update test_key set name='曹老板' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from test_key;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张飞      |
|  2 | 曹老板    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select *from test_key where id=1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
+----+--------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> alter table test_key drop primary key;
Query OK, 2 rows affected (0.22 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> select *from test_key;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张飞      |
|  2 | 曹老板    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> insert into test_key values (2,'孙权');
Query OK, 1 row affected (0.00 sec)

mysql> select *from test_key;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张飞      |
|  2 | 曹老板    |
|  2 | 孙权      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> show create table test_key \G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '这个是学生的学号',
  `name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table test_key add primary key(id);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select *from test_key;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张飞      |
|  2 | 曹老板    |
|  2 | 孙权      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> delete from test_key where name='孙权';
Query OK, 1 row affected (0.00 sec)

mysql> select *from test_key;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张飞      |
|  2 | 曹老板    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> alter table test_key add primary key(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> insert into test_key values (2,'孙权');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> show create table test_key\G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '这个是学生的学号',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

复用主键

mysql> create table if not exists pick_course(
    -> id int unsigned ,
    -> course_id int unsigned comment '课程编号',
    -> score tinyint unsigned comment '这个学生的课程考的分数',
    -> primary key (id,course_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into pick_course values (1234,40,90);
Query OK, 1 row affected (0.01 sec)

mysql> insert into pick_course values (1235,40,85);
Query OK, 1 row affected (0.00 sec)

mysql> insert into pick_course values (1234,41,95);
Query OK, 1 row affected (0.00 sec)

mysql> select *from pick_course;
+------+-----------+-------+
| id   | course_id | score |
+------+-----------+-------+
| 1234 |        40 |    90 |
| 1234 |        41 |    95 |
| 1235 |        40 |    85 |
+------+-----------+-------+
3 rows in set (0.00 sec)

mysql> insert into pick_course values (1234,40,95);
ERROR 1062 (23000): Duplicate entry '1234-40' for key 'PRIMARY'
mysql> insert into pick_course values (1234,41,90);
ERROR 1062 (23000): Duplicate entry '1234-41' for key 'PRIMARY'
mysql> insert into pick_course values (1234,42,90);
Query OK, 1 row affected (0.00 sec)

mysql> select *from pick_course;
+------+-----------+-------+
| id   | course_id | score |
+------+-----------+-------+
| 1234 |        40 |    90 |
| 1234 |        41 |    95 |
| 1234 |        42 |    90 |
| 1235 |        40 |    85 |
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> 

AUTO_INCREMENT自增约束

AUTO_INCREMENT是MySQL中的一个特性，用于在新记录插入表时自动生成一个唯一的数字。这个特性通常用于主键字段，确保每次插入新记录时都能自动分配一个唯一标识符。AUTO_INCREMENT属性对于管理具有大量插入操作的数据库非常有用，因为它简化了唯一标识符的生成过程。

AUTO_INCREMENT的作用

自动编号：每当向表中插入新记录时，AUTO_INCREMENT字段会自动设置为比当前最高值大1的值。如果表是空的，那么AUTO_INCREMENT字段的值开始于1（或者可以设置一个不同的起始值）。

唯一性保证：AUTO_INCREMENT保证了字段值的唯一性，这对于作为主键的字段尤为重要，因为主键需要唯一标识表中的每一行。

简化数据插入：使用AUTO_INCREMENT可以在插入数据时不必手动指定主键的值，这样可以避免插入过程中的错误并提高效率。

如何使用 AUTO_INCREMENT

AUTO_INCREMENT属性只能用于整数字段。在创建表时，可以将某个字段指定为AUTO_INCREMENT。以下是一个示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

在这个例子中，id字段被设置为AUTO_INCREMENT，并且是表的主键。每次向users表插入新用户时，id字段会自动增加。

注意事项

只能有一个：每个表中只能有一个AUTO_INCREMENT字段，并且该字段必须是索引的一部分。

删除记录：如果删除表中的某些记录，AUTO_INCREMENT的值不会自动减少，也不会重新使用被删除记录的AUTO_INCREMENT值。但是，可以手动重置AUTO_INCREMENT的值。

手动指定值：尽管AUTO_INCREMENT字段通常用于自动生成值，但也可以在插入记录时手动指定一个值。如果指定的值高于当前的自增值，MySQL会更新自增值为该值。

起始值：默认情况下，AUTO_INCREMENT的起始值为1，但可以通过ALTER TABLE命令修改起始值。

总结

AUTO_INCREMENT属性是数据库设计中一种非常实用的工具，它自动为表中的记录生成唯一标识符。这个属性简化了数据插入过程，保证了主键的唯一性，是处理自动编号需求的理想选择。正确使用AUTO_INCREMENT可以提高数据库操作的效率和准确性。

mysql> create table if not exists tt21(
    -> id int unsigned primary key auto_increment ,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tt21\G
*************************** 1. row ***************************
       Table: tt21
Create Table: CREATE TABLE `tt21` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

mysql> insert into tt21 (name) values ('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt21 (name) values ('b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt21 (name) values ('c');
Query OK, 1 row affected (0.01 sec)

mysql> select *from tt21;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into tt21 (name) values ('d');
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt21;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.01 sec)

mysql> insert into tt21 (id,name) values (1000,'e');
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt21;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
| 1000 | e    |
+------+------+
5 rows in set (0.00 sec)

mysql> insert into tt21 (id,name) values (1000,'e');
ERROR 1062 (23000): Duplicate entry '1000' for key 'PRIMARY'
mysql> insert into tt21 (name) values ('e');
Query OK, 1 row affected (0.01 sec)

mysql> select *from tt21;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
| 1000 | e    |
| 1001 | e    |
+------+------+
6 rows in set (0.00 sec)

mysql> show create table tt21 \G
*************************** 1. row ***************************
       Table: tt21
Create Table: CREATE TABLE `tt21` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> create table tt22 (
    -> id int unsigned primary key auto_increment,
    -> name varchar(20)not null
    -> )auto_increment=500;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table tt22\G
*************************** 1. row ***************************
       Table: tt22
Create Table: CREATE TABLE `tt22` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into tt22(name )values ('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt22(name )values ('b');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt22(name )values ('c');
Query OK, 1 row affected (0.01 sec)

mysql> select *from tt22;
+-----+------+
| id  | name |
+-----+------+
| 500 | a    |
| 501 | b    |
| 502 | c    |
+-----+------+
3 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              502 |
+------------------+
1 row in set (0.00 sec)

mysql> select *from tt22;
+-----+------+
| id  | name |
+-----+------+
| 500 | a    |
| 501 | b    |
| 502 | c    |
+-----+------+
3 rows in set (0.00 sec)

mysql> insert into tt22(name )values ('d');
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt22;
+-----+------+
| id  | name |
+-----+------+
| 500 | a    |
| 501 | b    |
| 502 | c    |
| 503 | d    |
+-----+------+
4 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              503 |
+------------------+
1 row in set (0.00 sec)

mysql> show create table tt22\G
*************************** 1. row ***************************
       Table: tt22
Create Table: CREATE TABLE `tt22` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=504 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

UNIQUE KEY唯一键

UNIQUE KEY（唯一键）是数据库中用于确保一列或列组合中的所有值都是唯一的约束。它是维护数据完整性的重要工具，防止在指定列中插入重复的值。与PRIMARY KEY（主键）约束类似，UNIQUE KEY也保证了数据的唯一性，但它们之间存在一些区别。

UNIQUE KEY的特点

唯一性：UNIQUE KEY约束确保一列或多列组合的值在整个表中是唯一的。

允许NULL值：与PRIMARY KEY不同，UNIQUE KEY约束允许有NULL值，而且如果是多列的唯一约束，每列都可以含有NULL值。但是，大多数数据库系统在实施UNIQUE KEY约束时只允许列中有一个NULL值。

非主键：一个表可以有多个UNIQUE KEY，但只能有一个PRIMARY KEY。UNIQUE KEY可以是表的非主键列。

索引创建：在大多数数据库系统中，对于设定了UNIQUE KEY约束的列，会自动创建唯一索引，这有助于提高查询性能。

如何使用 UNIQUE KEY

在创建表或之后，可以通过ALTER TABLE语句添加UNIQUE KEY约束。以下是一些示例：

创建表时添加UNIQUE KEY：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,  -- 设置email列为唯一
    username VARCHAR(50)
);

为已存在的表添加UNIQUE KEY：

ALTER TABLE users ADD UNIQUE (username);

这将为users表的username列添加一个唯一约束。

UNIQUE KEY与PRIMARY KEY的区别

唯一性：两者都保证了唯一性，但PRIMARY KEY自动排除了NULL值，而UNIQUE KEY允许一个NULL值（在某些数据库中可能允许多个）。

数量限制：一个表只能有一个PRIMARY KEY，但可以有多个UNIQUE KEY。

角色：PRIMARY KEY是用来唯一标识表中的每一行记录的，而UNIQUE KEY则用来保证某些列的数据不重复，即使它们不是主键。

使用场景

UNIQUE KEY常用于需要保证数据唯一但又不适合作为主键的场景。例如，用户的邮箱地址和用户名可能需要保持唯一性，以防止重复注册，但它们并不作为记录的主要标识符。

总结

UNIQUE KEY是数据库设计中用于确保数据唯一性的强大工具。通过在表的列上实施唯一约束，开发者可以避免数据重复，提高数据质量。了解和正确应用UNIQUE KEY约束对于维护数据库的完整性和实现高效的数据管理至关重要。

mysql> create table stu(
    -> id char(20) unique comment '这个是学生的唯一键',
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | char(20)    | YES  | UNI | NULL    |       |
| name  | varchar(32) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql> select *from stu;
+-------+--------+
| id    | name   |
+-------+--------+
| 12345 | 张三   |
+-------+--------+
1 row in set (0.00 sec)

mysql> insert into stu(id,name) values ('12345','李四');
ERROR 1062 (23000): Duplicate entry '12345' for key 'id'
mysql> insert into stu(id,name) values (NULL,'李四');
Query OK, 1 row affected (0.00 sec)


mysql> select *from stu;
+-------+--------+
| id    | name   |
+-------+--------+
| 12345 | 张三   |
| NULL  | 李四   |
+-------+--------+
2 rows in set (0.00 sec)

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

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

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

mysql> select *from stu;
+-------+--------+
| id    | name   |
+-------+--------+
| 12345 | 张三   |
| NULL  | 李四   |
| NULL  | 李四   |
| NULL  | 李四   |
| NULL  | 李四   |
+-------+--------+
5 rows in set (0.00 sec)

mysql> select NULL+1;
+--------+
| NULL+1 |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> create table student(
    -> id char(20) primary key,
    -> name varchar(32) not null,
    -> telphone char(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(20)    | NO   | PRI | NULL    |       |
| name     | varchar(32) | NO   |     | NULL    |       |
| telphone | char(20)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into student (id,name,telphone) values ('123','张三','1345678910');
Query OK, 1 row affected (0.00 sec)

mysql> select *from student;
+-----+--------+------------+
| id  | name   | telphone   |
+-----+--------+------------+
| 123 | 张三   | 1345678910 |
+-----+--------+------------+
1 row in set (0.00 sec)

mysql> insert into student (id,name,telphone) values ('123','张三','1345678910');
ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'
mysql> insert into student (id,name,telphone) values ('124','刘备','1345678911');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (id,name,telphone) values ('125','孙权','1345678911');
Query OK, 1 row affected (0.01 sec)

mysql> select *from student where telphone='1345678911';
+-----+--------+------------+
| id  | name   | telphone   |
+-----+--------+------------+
| 124 | 刘备   | 1345678911 |
| 125 | 孙权   | 1345678911 |
+-----+--------+------------+
2 rows in set (0.00 sec)

mysql> drop table student ;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student (
    -> id char (20) primary key,
    -> name varchar(32) not null,
    -> telphone char(20) unique key,
    -> qq varchar (64) unique key
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(20)    | NO   | PRI | NULL    |       |
| name     | varchar(32) | NO   |     | NULL    |       |
| telphone | char(20)    | YES  | UNI | NULL    |       |
| qq       | varchar(64) | YES  | UNI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into student values ('123','张飞','1341234','23456');
Query OK, 1 row affected (0.00 sec)

mysql> select *from student;
+-----+--------+----------+-------+
| id  | name   | telphone | qq    |
+-----+--------+----------+-------+
| 123 | 张飞   | 1341234  | 23456 |
+-----+--------+----------+-------+
1 row in set (0.00 sec)

mysql> insert into student values ('1234','李四','1341234','23456');
ERROR 1062 (23000): Duplicate entry '1341234' for key 'telphone'
mysql> insert into student values ('1234','李四','13412345','23456');
ERROR 1062 (23000): Duplicate entry '23456' for key 'qq'
mysql> insert into student values ('1234','李四','13412345','234567');
Query OK, 1 row affected (0.00 sec)

mysql> select *from student;
+------+--------+----------+--------+
| id   | name   | telphone | qq     |
+------+--------+----------+--------+
| 123  | 张飞   | 1341234  | 23456  |
| 1234 | 李四   | 13412345 | 234567 |
+------+--------+----------+--------+
2 rows in set (0.00 sec)

mysql> insert into student values ('1234','李四','13412345',NULL);
ERROR 1062 (23000): Duplicate entry '1234' for key 'PRIMARY'
mysql> insert into student values ('12345','李四','13412345',NULL);
ERROR 1062 (23000): Duplicate entry '13412345' for key 'telphone'
mysql> insert into student values ('12345','李四','134123456',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values (NULL,'李四','1341234567',NULL);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> select *from student;
+-------+--------+-----------+--------+
| id    | name   | telphone  | qq     |
+-------+--------+-----------+--------+
| 123   | 张飞   | 1341234   | 23456  |
| 1234  | 李四   | 13412345  | 234567 |
| 12345 | 李四   | 134123456 | NULL   |
+-------+--------+-----------+--------+
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> alter table student modify telphone char(20) unique not null;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> alter table student modify qq char(20) unique not null;
ERROR 1265 (01000): Data truncated for column 'qq' at row 3
mysql> delete from student where qq is NULL;
Query OK, 1 row affected (0.00 sec)

mysql> alter table student modify qq char(20) unique not null;
Query OK, 2 rows affected, 1 warning (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> desc student ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(20)    | NO   | PRI | NULL    |       |
| name     | varchar(32) | NO   |     | NULL    |       |
| telphone | char(20)    | NO   | UNI | NULL    |       |
| qq       | char(20)    | NO   | UNI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into student values ('4321','王五','1331234',NULL);
ERROR 1048 (23000): Column 'qq' cannot be null
mysql> insert into student values ('4321','王五',NULL,NULL);
ERROR 1048 (23000): Column 'telphone' cannot be null
mysql> 

FOREIGN KEY外键

FOREIGN KEY（外键）是数据库中的一个重要概念，用于在两个表之间建立关联关系，确保数据的一致性和完整性。外键约束是关系型数据库中实现引用完整性的一种机制，它指向另一个表的主键或唯一键字段。

外键的作用

数据一致性：通过在表之间建立外键关系，可以确保引用的数据存在，避免出现悬挂引用（即引用不存在的记录）。

数据完整性：外键约束保护关系，确保在更新或删除关联表中的数据时，不会破坏表间的链接。

实现关系：外键是实现数据库中的实体关系（如一对多、多对一）的关键，有助于维护数据的组织结构。

如何使用 FOREIGN KEY

在创建表或之后添加外键约束，可以通过CREATE TABLE或ALTER TABLE语句实现。以下是一些示例：

创建表时添加外键约束：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
    -- product_id 是 orders 表的外键，它引用了 products 表的主键 product_id
);

为已存在的表添加外键约束：

ALTER TABLE orders ADD CONSTRAINT fk_product
FOREIGN KEY (product_id) REFERENCES products(product_id);

这将为orders表的product_id列添加一个名为fk_product的外键约束，指向products表的product_id列。

外键引用其他表的列时，这个列不必须是主键，但必须是唯一的。

外键引用其他表的列时，这个列不必须是主键，但必须是唯一的。这意味着外键可以引用目标表中的任何具有唯一约束的列，无论是主键约束还是唯一约束（UNIQUE）。主键自然具有唯一性，因此它是外键引用的常见选择。然而，任何通过UNIQUE约束确保唯一性的列同样可以作为外键的引用目标。

为什么需要唯一性？

外键约束的目的是保证数据的引用完整性，确保引用的数据在被引用的表中存在。如果外键引用的列不是唯一的，那么就会存在多个相同的值，这样就无法保证一致的引用关系。例如，如果一个订单表中的customer_id字段作为外键引用顾客表中的id字段，那么每个订单都可以准确地指向一个特定的顾客。如果customer_id引用的不是唯一的，那么就无法确定引用的是哪一个顾客，从而破坏了数据的一致性和完整性。

没有外键时

mysql> create table if not exists stu_foreign(
    -> id int unsigned primary key auto_increment ,
    -> name varchar(20) not null,
    -> telphone varchar(32) not null,
    -> class_id int
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table if not exists class(
    -> id int primary key,
    -> name varchar(32)not null
    -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into class values (1,'通信101');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values (2,'通信102');
Query OK, 1 row affected (0.01 sec)

mysql> select *from class;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 通信101   |
|  2 | 通信102   |
+----+-----------+
2 rows in set (0.00 sec)

mysql> insert into stu_foreign(name ,telphone ,class_id) values ('李四','123456789',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu_foreign(name ,telphone ,class_id) values ('王五','12345678910',1);
Query OK, 1 row affected (0.00 sec)

mysql> select *from class;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 通信101   |
|  2 | 通信102   |
+----+-----------+
2 rows in set (0.00 sec)

mysql> select *from stu_foreign;
+----+--------+-------------+----------+
| id | name   | telphone    | class_id |
+----+--------+-------------+----------+
|  1 | 李四   | 123456789   |        1 |
|  2 | 王五   | 12345678910 |        1 |
+----+--------+-------------+----------+
2 rows in set (0.00 sec)

mysql> insert into stu_foreign(name ,telphone ,class_id) values ('赵六','12345678911',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu_foreign(name ,telphone ,class_id) values ('张三','12345678912',2);
Query OK, 1 row affected (0.00 sec)

mysql> select *from stu_foreign;
+----+--------+-------------+----------+
| id | name   | telphone    | class_id |
+----+--------+-------------+----------+
|  1 | 李四   | 123456789   |        1 |
|  2 | 王五   | 12345678910 |        1 |
|  3 | 赵六   | 12345678911 |        2 |
|  4 | 张三   | 12345678912 |        2 |
+----+--------+-------------+----------+
4 rows in set (0.00 sec)

mysql> insert into stu_foreign(name ,telphone ,class_id) values ('田七','12345678913',3);
Query OK, 1 row affected (0.01 sec)

mysql> delete from class where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> desc stu_foreign;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)      | NO   |     | NULL    |                |
| telphone | varchar(32)      | NO   |     | NULL    |                |
| class_id | int(11)          | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

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

mysql> 

初见外键

mysql> drop table stu_foreign;
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists stu_foreign (
    -> id int unsigned primary key,
    -> name varchar(20) not null,
    -> telphone varchar(32) unique key,
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(20)    | NO   | PRI | NULL    |       |
| name     | varchar(32) | NO   |     | NULL    |       |
| telphone | char(20)    | NO   | UNI | NULL    |       |
| qq       | char(20)    | NO   | UNI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc stu_foreign;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   | PRI | NULL    |       |
| name     | varchar(20)      | NO   |     | NULL    |       |
| telphone | varchar(32)      | YES  | UNI | NULL    |       |
| class_id | int(11)          | YES  | MUL | NULL    |       |
+----------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select *from class;
+----+-----------+
| id | name      |
+----+-----------+
|  2 | 通信102   |
+----+-----------+
1 row in set (0.00 sec)

mysql> insert into class values(1,'通信101');
Query OK, 1 row affected (0.00 sec)

mysql> select *from class;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 通信101   |
|  2 | 通信102   |
+----+-----------+
2 rows in set (0.01 sec)

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

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

mysql> insert into stu_foreign values (100,'张三','12345',2);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
mysql> insert into stu_foreign values (101,'张三','12345',2);
ERROR 1062 (23000): Duplicate entry '12345' for key 'telphone'
mysql> insert into stu_foreign values (101,'张三','123456',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu_foreign values (102,'李四','1234567',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`d1`.`stu_foreign`, CONSTRAINT `stu_foreign_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
mysql> delete from class where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`d1`.`stu_foreign`, CONSTRAINT `stu_foreign_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
mysql> select *from stu_foreign;
+-----+--------+----------+----------+
| id  | name   | telphone | class_id |
+-----+--------+----------+----------+
| 100 | 张三   | 12345    |        1 |
| 101 | 张三   | 123456   |        2 |
+-----+--------+----------+----------+
2 rows in set (0.01 sec)

mysql> delete from stu_foreign where id=100;
Query OK, 1 row affected (0.01 sec)

mysql> delete from class where id=1;
Query OK, 1 row affected (0.01 sec)

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

mysql> desc stu_foreign;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   | PRI | NULL    |       |
| name     | varchar(20)      | NO   |     | NULL    |       |
| telphone | varchar(32)      | YES  | UNI | NULL    |       |
| class_id | int(11)          | YES  | MUL | NULL    |       |
+----------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

结尾

最后，感谢您阅读我的文章，希望这些内容能够对您有所启发和帮助。如果您有任何问题或想要分享您的观点，请随时在评论区留言。

同时，不要忘记订阅我的博客以获取更多有趣的内容。在未来的文章中，我将继续探讨这个话题的不同方面，为您呈现更多深度和见解。

谢谢您的支持，期待与您在下一篇文章中再次相遇！