【MySQL】数据库约束与数据表的设计

♫数据库约束

关系型数据库一个重要内容是需要包装数据的‘完整性’和‘正确性’，而由我们自己来确保数据的正确性是不可靠的，因此MySQL中提供了一些约束来帮助我们保证数据的正确性。

♪NOT NULL约束

创建表时，可以通过NOT NULL关键字确保某列不能为空：

语法：create table 表名(列名 列类型 NOT NULL);

mysql> use test;
Database changed
mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)

-- id这一列不能为空
mysql> create table student(id int NOT NULL);
Query OK, 0 rows affected (0.04 sec)

此时查看student表：

mysql> desc student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.03 sec)

可以发现Null这行的值为No，如果还要插入NULL值就会报错：

mysql> insert into student values(NULL);
ERROR 1048 (23000): Column 'id' cannot be null

♪UNIQUE约束

创建表时，可以通过UNIQUE关键字确保某列的值不能重复：

语法：create table 表名(列名 列类型 UNIQUE);

mysql> drop table if exists student;
Query OK, 0 rows affected (0.00 sec)

-- id这一列的数据不能重复
mysql> create table student(id int UNIQUE);
Query OK, 0 rows affected (0.34 sec)

此时查看student表：

mysql> desc student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

可以发现Key这一列为UNI（nuique的缩写），若往表里插入重复数据则会报错：

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

mysql> insert into student values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

注：加上UNIQUE之后数据库会先查询再插入，执行效率会比没加约束低

♪PRIMARY KEY（主键约束）

创建表时，可以通过PRIMARY KEY关键字确保某列（或两个列多个列的结合）有唯一标

识，有助于更容易更快速地找到表中的一个特定的记录：

语法：create table 表名(列名 列类型 PRIMARY KEY);

mysql> drop table if exists student;
Query OK, 0 rows affected (0.11 sec)

-- 将id这一列设置为主键
mysql> create table student(id int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

此时查看student表：

mysql> desc student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

可以发现Key这一列为PRI（PRIMARY 的缩写），若往表里插入重复数据则会报错：

mysql> insert into student values(1);
Query OK, 1 row affected (0.02 sec)

-- 插入重复数据
mysql> insert into student values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

-- 插入NULL值
mysql> insert into student values(NULL);
ERROR 1048 (23000): Column 'id' cannot be null

我们可能会发现PRIMARY KEY的约束效果很像NOT NULL加UNIQUE的约束效果，其实当我们将NOT NULL和UNIQUE两个关键字一起使用时：

mysql> drop table if exists student;
Query OK, 0 rows affected (0.41 sec)

-- 将id这一列加上NOT NULL和UNIQUE两个约束
mysql> create table student(id int NOT NULL UNIQUE);
Query OK, 0 rows affected (0.54 sec)

mysql> desc student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

可以发现，Key的值和PRIMARY KEY约束时一样，都是PRI。

既然PRIMARY KEY每次都得是不重复的数据，那有没有办法让系统自动帮我们生成不重复的值呢？这就要用到AUTO_INCREMENT关键字：

语法：create table 表名(列名 类型 PRIMARY KEY AUTO_INCREMENT);

mysql> drop table if exists student;
Query OK, 0 rows affected (0.11 sec)

mysql> create table student(id int PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.07 sec)

mysql> desc student;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.01 sec)

添加自增主键后，Extra行的值为auto_increment，此后id这一列若是没有输入值，系统会自动设置id为：前面id的最大值+1。

注：

①.PRIMARY KEY和UNIQUE一样都是先查询再插入，故MySQL会默认给被PRIMARY KEY和UNIQUE约束的列添加索引，以提高查询速率

②.MySql中，一个表只能有一个主键

③.若是数据量太多，超出自增主键能表示的访问，则需要考虑分库分表

♪FOREIGN KEY约束（外键约束）

创建表时，可以通过外键约束保证一个表中的数据匹配另一个表中的值的参照完整性：

语法：create table 表名(列名 列类型,FOREIGN KEY(列名) REFERENCES 主表(列名));

mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table class(id int PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

mysql> create table student(
    ->     id int,
    ->     classId int,
    ->     FOREIGN KEY(classId) REFERENCES class(id)
    -> );
Query OK, 0 rows affected (0.04 sec)


-- 不能插入class表中没有的id
mysql> insert into student(classId) values(1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`id`))


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

-- 可以插入class表中已有的id
mysql> insert into student(classId) values(1);
Query OK, 1 row affected (0.00 sec)

通过外键约束可以让student表里的classId只能取class表里的id里已有的值，还可以让class表里的id不能修改已经被student表里的classId引用的值：

-- 不能删掉被引用的id
mysql> delete from class where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`id`))

-- 不能修改被引用的id
mysql> update class set id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`id`))

注：要想使用外键约束，必须保证父表的对应列有PRIMARY KEY或UNIQUE约束

♪DEFAULT约束

创建表时，可以通过DEFAULT关键字规定没有给列赋值时该列的默认值：

语法：create table 表名(列名 列类型 DEFAULT 默认值);

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

-- 将id这一列的默认值设置为1
mysql> create table student(id int DEFAULT 1);
Query OK, 0 rows affected (0.04 sec)

此时查看student表：

mysql> desc student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

可以发现Default这一列为1，若往表里插入的数据里没有id的值：

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

mysql> select * from student;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

此时依然成功执行，且id的默认值为1

♪CHECK约束

CHECK约束能保证列中的值符合指定的条件。但对于 MySQL 数据库， MySQL使用时不报错，但忽略该约束：

mysql> drop table if exists student;
Query OK, 0 rows affected (0.03 sec)

mysql> create table student (
    ->     id int,
    ->     check (id = 0 or sex = 1)
    -> );
Query OK, 0 rows affected (0.06 sec)

-- id仍然可以为3
mysql> insert into student values(2);
Query OK, 1 row affected (0.01 sec)

♫数据表的设计

数据库表的设计是指在创建数据库时，规划出所需的数据表结构，并进行合理的设计、建立和维护。

♪三大范式

♩一对一：

如：一个学生只能有一个学生号，一个学生号对应一个学生

创建方式：

create table student(id int,name varchar(20));

♩一对多：

如：一个学生只能在一个班级，一个班级可以有多个学生

创建方式：

create table class_id(id int);
create table student(id int,name varchar(20),class_id int,foreign key(class_id) references class_id(id));

♩多对多：

如：一个学生可以学习多门课程，一门课程可以被多个学生学习

创建方式：

create table student(id int primary key,name varchar(20));
create table crouse(id int primary key,name varchar(20));
create table student_crouse(
    student_id int,
    class_id int,
    foreign key(student_id) references student(id),
    foreign key(class_id) references class(id));
)

♪设计步骤

1.明确数据库表的目的和需求，了解数据的来源和用途。

2.定义表的字段，包括名称、数据类型、长度、精度等属性，确保信息的准确性和完整性。

3.选择合适的主键和索引，以便快速查询和更新数据。

4.确定表之间的关系，如一对多、多对多、一对一等。

5.根据需求确定表的范式，通常为第三范式，以避免数据的冗余和更新异常。

6.考虑安全性和数据完整性问题，包括访问权限、数据备份和恢复等。

7.进行数据库表的创建和初始化，确保数据的正确性和可靠性。

8.设计相应的数据访问接口，使得用户能够简单方便地访问和操作数据库中的数据。