文章目录
数据库约束
数据库中主要有六种约束
- NOT NULL(非空约束) - 指示某列不能存储 NULL 值。
- UNIQUE(唯一约束) - 保证某列的每行必须有唯一的值。
- DEFAULT(缺省约束) - 规定没有给列赋值时的默认值。
- PRIMARY KEY(主键约束) - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY(外键约束) - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK(检查约束) - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句。
NOT NULL(非空约束)
指示某列不能存储 NULL 值。
例如在这里对birth添加非空约束
MariaDB [test]> create table student(
-> id int,
-> name varchar(8),
-> age int,
-> birth datetime not null,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
接着向其中插入数据,如果不给birth一个确定的值,就会导致插入失败
MariaDB [test]> insert into student values(3, "孙悟空", 22, null, 90, 91.5, 96.03);
ERROR 1048 (23000): Column 'birth' cannot be null
MariaDB [test]> insert into student(id, name, age) values(1, "猪八戒", 20);
ERROR 1364 (HY000): Field 'birth' doesn't have a default value
添加了非空约束后该列就不能为空,必须要给一个值
UNIQUE(唯一约束)
保证某列的每行必须有唯一的值,即对于添加了唯一约束的数据项不能有重复
// 对id添加unique约束
MariaDB [test]> create table student(
-> id int unique,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
// 插入第一条数据
MariaDB [test]> insert into student values(2, "白骨精", 99, "1800-1-1 1:1:1", 0,0,0);
Query OK, 1 row affected (0.00 sec)
// 插入第二条数据,除了id与第一条相同,其它全都不同
MariaDB [test]> insert into student values(2, "孙悟空", 22, "1998-2-28 17:10:01", 90, 90, 90);
// 报错,id必须唯一,不能重复
ERROR 1062 (23000): Duplicate entry '2' for key 'id'
DEFAULT(缺省约束)
规定没有给列赋值时的默认值。
// 给chinese、math、english添加缺省约束,缺省值为60
MariaDB [test]> create table student(
-> id int,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2) default 60,
-> math decimal(4,2) default 60,
-> english decimal(4,2) default 60
-> );
Query OK, 0 rows affected (0.00 sec)
// 插入数据,没有指定chinese、math、english的值
MariaDB [test]> insert into student(id, name, age, birth) values(6, "白龙马", 17, "2003-3-26 16:45:21");
Query OK, 1 row affected (0.00 sec)
// 没有指定时会被修改为默认值
MariaDB [test]> select * from student;
+------+-----------+------+---------------------+---------+-------+---------+
| id | name | age | birth | chinese | math | english |
+------+-----------+------+---------------------+---------+-------+---------+
| 6 | 白龙马 | 17 | 2003-03-26 16:45:21 | 60.00 | 60.00 | 60.00 |
+------+-----------+------+---------------------+---------+-------+---------+
1 row in set (0.00 sec)
PRIMARY KEY(主键约束)
NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
主键的特性即非空且唯一,如果在没有指定主键的时候,如果某一列具有非空且唯一的特性,他就会被暂定为主键,但是主键只能有一个。
// id非空且唯一
MariaDB [test]> create table student(
-> id int not null unique,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
// 可以看到id成为了主键
MariaDB [test]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(8) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| chinese | decimal(4,2) | YES | | NULL | |
| math | decimal(4,2) | YES | | NULL | |
| english | decimal(4,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
// 使用primary key将id设置为主键,同样是非空且唯一
MariaDB [test]> create table student(
-> id int primary key,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(8) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| chinese | decimal(4,2) | YES | | NULL | |
| math | decimal(4,2) | YES | | NULL | |
| english | decimal(4,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
但是非空且唯一并不代表主键,主键只能有一个,而非空不唯一可以有多个,如果有多个非空不唯一,则只会有第一个是主键
// 将id和name都设置为非空且唯一
MariaDB [test]> create table student(
-> id int not null unique,
-> name varchar(8) not null unique,
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
// id和name都是非空且唯一,但是只有id是主键
MariaDB [test]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(8) | NO | UNI | NULL | |
| age | int(11) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| chinese | decimal(4,2) | YES | | NULL | |
| math | decimal(4,2) | YES | | NULL | |
| english | decimal(4,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
如果想要使用多个列共同作为主键,就得使用下面这种语法
// 在末尾声明组合主键的列都有哪些
MariaDB [test]> create table student(
-> id int,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2),
-> primary key(id, name)
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(8) | NO | PRI | | |
| age | int(11) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| chinese | decimal(4,2) | YES | | NULL | |
| math | decimal(4,2) | YES | | NULL | |
| english | decimal(4,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
// 不能直接在多个列后面设置primary key,这样的意思是设置多个主键,但是主键是唯一的,所有会报错
// 错误写法
MariaDB [test]> create table student(
-> id int primary key,
-> name varchar(8) primary key,
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
ERROR 1068 (42000): Multiple primary key defined // 报错,定义了多个主键
AUTO_INCREMENT 自增
添加自增属性的项必须为数字,并且必须为主键,并且只有缺省的时候才会使用自增。
- 表中数据从1开始自增,每次为上一条记录的+1
- 如果删除了表中数据,序号并不会重置,而是继续从删除的位置自增
MariaDB [test]> create table student(
-> id int primary key auto_increment,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
insert into student(name, age) values("牛魔王", 90);
insert into student(name, age) values("白骨精", 99);
MariaDB [test]> select * from student;
+----+-----------+------+-------+---------+------+---------+
| id | name | age | birth | chinese | math | english |
+----+-----------+------+-------+---------+------+---------+
| 1 | 牛魔王 | 90 | NULL | NULL | NULL | NULL |
| 2 | 白骨精 | 99 | NULL | NULL | NULL | NULL |
+----+-----------+------+-------+---------+------+---------+
2 rows in set (0.00 sec)
// 删除刚刚插入的两条数据
delete from student where id < 3;
insert into student(name, age) values("孙悟空", 26);
insert into student(name, age) values("猪八戒", 24);
insert into student(name, age) values("沙悟净", 25);
insert into student(name, age) values("白龙马", 17);
MariaDB [test]> select * from student;
+----+-----------+------+-------+---------+------+---------+
| id | name | age | birth | chinese | math | english |
+----+-----------+------+-------+---------+------+---------+
| 3 | 孙悟空 | 26 | NULL | NULL | NULL | NULL |
| 4 | 猪八戒 | 24 | NULL | NULL | NULL | NULL |
| 5 | 沙悟净 | 25 | NULL | NULL | NULL | NULL |
| 6 | 白龙马 | 17 | NULL | NULL | NULL | NULL |
+----+-----------+------+-------+---------+------+---------+
4 rows in set (0.00 sec)
FOREIGN KEY(外键约束)
保证一个表中的数据匹配另一个表中的值的参照完整性。
当我们的表中有数据与另一个表有关联的时候,就需要用到外键约束。例如学生表中存储了班级的信息,但是在班级表中并没有这个班级存在,就会导致数据出现冲突,所以必须将两个表关联起来。
语法
foreign key (外键项) reference 关联表名(关联表中的对应项)
示例
MariaDB [test]> create table classes(
-> id int primary key auto_increment
-> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> create table student(
-> id int primary key auto_increment,
-> age int,
-> name varchar(8),
->
-> classid int,
-> foreign key(classid) references classes(id)
-> );
Query OK, 0 rows affected (0.01 sec)
// 添加班级
insert into classes values(1);
insert into classes values(2);
// 添加学生信息,插入成功
MariaDB [test]> insert into student(name, classid) VALUES("孙悟空", 1);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into student(name, classid) VALUES("猪八戒", 2);
Query OK, 1 row affected (0.00 sec)
// 插入失败,3号班级不存在,如果没有外键约束,此时就会插入成功,对数据造成影响
MariaDB [test]> insert into student(name, classid) VALUES("沙悟净", 3);
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 `classes` (`id`))
CHECK(检查约束)
保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句。
MariaDB [test]> create table student(
-> id int primary key auto_increment,
-> name varchar(8),
-> age int,
-> birth datetime,
-> chinese decimal(4,2),
-> math decimal(4,2),
-> english decimal(4,2),
-> check(age < 20)
-> );
Query OK, 0 rows affected (0.00 sec)
// 可以看到插入年龄11和20都会成功,所以MySQL忽略了check子句
MariaDB [test]> insert into student values(2, "玉皇大帝", 11, "2009-10-1 1:1:1", 99, 99, 99);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into student values(1, "玉皇大帝", 22, "2009-10-1 1:1:1", 99, 99, 99);
Query OK, 1 row affected (0.00 sec)
在MySQL中,CHECK子句的功能并没有被实现,所以它虽然会对语句进行分析,但是并不会去真正使用这个功能。
表的关系
如果要设计一个表,首先就要考虑多个表之间的关系
一对一
例如人和身份证的关系,每个人都对应有着只属于自己的身份证
一对多
例如学生和班级的关系,一个班级拥有多个学生,但是一个学生只能属于一个班级
多对多
例如学生、课程、选课表的关系。
一个学生可以选择多门课程,一个课程也可以被多个学生选择
三大范式
表的关系只是设计的最基础的一项,考虑好关系,确认好数据项,将数据填进去即可。
但是那样的设计并不合理,可能会存在数据冗余、传输性能、查询性能等问题,所以需要用到三大范式来规范数据库表的设计,减少数据库的冗余性。
范式是针对数据库表设计的几种方案,目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
通常我们使用的都是第一范式(1NF)、第二范式(2NF)、第三范式(3NF),所以又将他们称为三大范式。
第一范式
第一范式:要求数据库表的每一列都是不可分割的原子数据项。
例如
在这个表中,家庭信息和学校信息并不是原子的,例如家庭信息中包含了家庭组成和所在地,学校信息包含了年级和学位。
对于第一范式,需要确保每一项数据都是不可分割的原子性数据,不能是一个集合
调整后
第二范式
第二范式:在第一范式的基础上,非主键数据必须完全依赖主键,不能部分依赖(针对组合主键)
第二范式的目的是确保一个表只说明一个事物
例如
在这个图中订单号和产品号作为组合主键,但是后面的订单金额、订单时间只与订单号相关,而与产品号无关,部分依赖于主键,违反了第二范式。可以看到,后面的数据中存在着大量的重复,造成了数据冗余
所以需要将其分割出去单独建立一个表
第三范式
第三范式:在第二范式的基础上,非主键数据之间不能相互依赖,依赖关系不能传递,每一个非主键数据都必须要和主键直接依赖而非间接依赖
第三范式主要解决依赖关系传递带来的数据冗余问题
例如
该图中,班主任性别与班主任年龄直接依赖于班主任姓名,而与主键学号并没有直接的依赖关系,而是间接,存在着依赖传递的问题。
所以需要将这两项分割出去单独建表