共计 7844 个字符,预计需要花费 20 分钟才能阅读完成。
范式(NF):设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余,反而会降低数据读写的效率,这个时候就要反范式,利用空间来换时间。可以把它粗略地理解为 一张数据表的表结构所符合的某种设计标准的级别。
即表的列的具有原子性, 不可再分解,即列的信息,不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server),就自动的满足 1NF。数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项。如果实体中的某个属性有多个值时,必须拆分为不同的属性。通俗理解即一个字段只存储一项信息。
以上,就不符合第一范式,因为进货、销售还可以再分为,进货数量、进货单位、销售单位、销售数量等,以下则满足了第一范式。
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。满足 1NF 后,要求表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系,也就是说一个表只描述一件事情;
例如:订单表只描述订单相关的信息,所以所有字段都必须与订单 id 相关;产品表只描述产品相关的信息,所以所有字段都必须与产品 id 相 关;因此不能在一张表中同时出现订单信息与产品信息;如下图所示:
必须先满足第二范式(2NF),要求:表中的每一列只与主键直接相关而不是间接相关,表中的每一列只能依赖于主键。
例如:订单表中需要有客户相关信息,在分离出客户表之后,订单表中只需要有一个用户 id 即可,而不能有其他的客户信息。因为其他的客户信息直接关联于用户 id,而不是直接与订单 id 直接相关。
约束是用来限定表中数据准确性、完整性、一致性、联动性的一套规则。在 Mysql 中,约束保存在 information_schema 数据库的 table_constraints 中,可以通过该表查询约束信息。如下图:
非空约束,是否允许该列的值为 NULL,这里有一点很重要,很多字段(除了时间?)默认值如果不指定的话都是 NULL,所以除了 NULL=NULL,其他值并不等于 NULL,比如“”、0 等。
修改一个字段为 NOT NULL:
MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| regtime | timestamp | NO | | CURRENT_TIMESTAMP | |
| logtime | timestamp | NO | | 0000-00-00 00:00:00 | |
| logip | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)
MariaDB [mydb]> ALTER TABLE user MODIFY logip varchar(20) NOT NULL;
Query OK, 5 rows affected, 5 warnings (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 5
MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| regtime | timestamp | NO | | CURRENT_TIMESTAMP | |
| logtime | timestamp | NO | | 0000-00-00 00:00:00 | |
| logip | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)
这里还有一个问题,对于默认值为 NULL 但是又没有指定插入这个字段:
MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| regtime | timestamp | NO | | CURRENT_TIMESTAMP | |
| logtime | timestamp | NO | | 0000-00-00 00:00:00 | |
| logip | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------------------+----------------+
// 看 username 这个字段,默认值为 NULL,不允许 NULL
MariaDB [mydb]> INSERT INTO user(password) VALUES('test7');
Query OK, 1 row affected, 1 warning (0.00 sec)
// 这里看到我们插入成功了。MariaDB [mydb]> SELECT * FROM user WHERE password='test7';
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime | logtime | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 12 | | test7 | 2018-02-25 15:25:14 | 0000-00-00 00:00:00 | NULL |
+----+----------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)
可以看到 username 这一列的值为空字符,而它的默认值为 NULL 啊,
而 logip 默认值为 NULL,但是允许插入 NULL 值,所以这里显示了 NULL 值。
查了一下~ 因为 NULL 为默认值,但是又不允许 NULL 值,所以,也就是说现在 username 这个字段没有值,因为 SQL_MODE 的原因,只会警告一下并不会直接报错,当我们指定 SQL_MODE 为 ’STRICT_ALL_TABLES’ 时,此时插入就会报如下错误:
MariaDB [mydb]> INSERT INTO user(password) VALUES('test88');
ERROR 1364 (HY000): Field 'username' doesn't have a default value
unique 代表唯一约束:唯一约束是指定 table 的列或列组合不能重复,保证数据的唯一性,虽然唯一约束不允许出现重复的值,但是 可以为多个 null,同一个表可以有多个唯一约束,多个列组合的约束。在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同,MySQL 会给唯一约束的列上默认创建一个唯一索引。
添加唯一约束:
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username); //uq_username 为约束名称,UNIQUE(可多个字段)// 当插入用户名相同的数据事则会直接报错
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
ERROR 1062 (23000): Duplicate entry 'test4' for key 'uq_username'
// 删除此约束
MariaDB [mydb]> ALTER TABLE user DROP KEY uq_username;
// 添加两个字段的约束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);
// 测试添加数据
MariaDB [mydb]> SELECT * FROM user;
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime | logtime | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 7 | test2 | test3 | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 | |
| 8 | test3 | test3 | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 | |
| 9 | test4 | test5 | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 | |
+----+----------+----------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
Query OK, 1 row affected (0.01 sec)
// 仅当两个字段的数据都相同时才违反唯一约束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'
主键约束相当于 唯一约束 + 非空约束 的组合,主键约束列不允许重复,也不允许出现空值。如果是多列组合的主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。每个表 最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建,MySQL 的主键名总是 PRIMARY,当创建主键约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。
操作如下:
// 因为现在的表中已经有主键了,先把主键删掉
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
// 告诉我们一张表里只允许有一个字段为自动增长,且这个字段必须是主键,所以,我们要先取消它的自动增长。MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id int(11) NOT NULL;
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id | int(11) | NO | PRI | NULL | |
// 再次删除主键
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
// 好了,再让我们把主键加上吧~~~ 以下两种方式都可以哦~
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT PRIMARY KEY(id);
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;
外键约束是 保证一个或两个表之间的参照完整性 ,外键是构建于 一个表的两个字段或是两个表的两个字段之间的参照关系 。也就是说从表的 外键值必须在主表中能找到或者为空,当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据,还有一种就是级联删除子表数据。
注意:外键约束的参照列,在主表中引用的只能是主键或唯一键约束的列,假定引用的主表列不是唯一的记录,那么从表引用的数据就不确定记录的位置,同一个表可以有多个外键约束。
现在,我们创建一个 GROUP 表吧,用于记录用户的分组信息,
CREATE TABLE `usergroup` (`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 |
然后呢~ 为 user 表添加一个记录,记录用户属于那个组
MariaDB [mydb]> ALTER TABLE user ADD COLUMN groupid INT(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
// 添加一个外键
ALTER TABLE user ADD CONSTRAINT fk_groupid FOREIGN KEY (groupid) REFERENCES usergroup(id);
// 验证外键约束
MariaDB [mydb]> INSERT INTO user(username,password,groupid) VALUES('test99','test00',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user`, CONSTRAINT `fk_groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`id`))
// 可以为空,但是不可以为参照表中没有的值
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test99','test00');
Query OK, 1 row affected (0.01 sec)
外键定义:
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
其中一下级联操作需要注意:
ON DELETE CASCADE:当删除父(参照)表中的行时,如果子表中有依赖于被删除父行的子行存在,那么连同子行一起删除,不推荐使用。
ON DELETE SET NULL:当删除父(参照)表中的行时,如果子表中有依赖于被删除父行的子行存在,那么不删除,而是将子行的外键列设置为 NULL
CHECK 约束就是当向表中插入一行或更新一行数据时进行 CHECK 约束检查,CHECK 接受一个表达式,如果这个表达式为 TRUE 则允许插入,如果这个表达式为 FALSE 则拒绝插入,在 MariaDB10.2 版本才开始支持 CHECK。
常见的 CHECK 约束有:
CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0)
CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date
例子:检查用户名长度是否大于 0
ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK(CHAR_LENGTH(username)>0);
INSERT INTO user(id,username) VALUES(1,'');
/* SQL 错误(4025):CONSTRAINT `non_empty_name` failed for `test`.`user` */
这个东西看起来很鸡肋的样子,好像一般都是在业务层进行数据判断了,而且数据库嘛~ 就存数据就好了。
![post-qrcode](https://www.xgss.net/wp-content/uploads/2023/07/二维码-微信公众号256x256.jpg)