04-数据库CRUD（进阶1）

Chase_Mos

于 2024-12-22 19:58:38 发布

阅读量901

点赞数 30

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/2301_80160897/article/details/144569784

版权

数据库约束

数据库约束是关系数据库的一个重要功能，主要作用是保证数据库的完整性,也可以理解数据的正确性(数据本身是否正确，关联关系是否正确)，人工检查数据完整性的工作量很大，在数据表中定义一些约束，那么数据库写入数据的时候，数据库会帮助我们做校验工作，约束一般是指定在列上的。

约束类型

not null -- 指示某列不能存储null值。
unique -- 保证某列的每行必须有唯一的值。
default -- 规定没有给列赋值时的默认值。
primary key -- not null和unique的结合，确保某列（或两个列多个的结合）有唯一标识，有助于更容易快速的找到地找到表中一个特定的记录。
foreign key -- 保证一个表中的数据匹配另一个表中的值的参照完整性。
check -- 保证列中的值符合指定条件。

举例

drop table if exists limit_type;
create table if not exists limit_type(
    id int primary key auto_increment,
    sn int unique,
    name varchar(20) default 'unknown',
    mail varchar(30) not null
)

auto_increment:对于整数类型的主键，常搭配自增长的auto_increment来使用,插入数据对应字段不给值的时候,使用最大值加一。

补充：一个表中不允许有两个主键，但是一个主键可以包含多个列（复合主键）

在唯一校验的时候，只有复合主键中所有的列都相同的时候才会被判定为相同。

外键约束

外键用于关联其他主键或者唯一键：
foreign key (字段名) reference 主表(列)

# 创建班级表class,id为主键
drop table if exists classes;
create table classes(
    id int primary key auto_increment,
    name varchar(20),
    `desc` varchar(100)
);
# 创建学生表student,一个学生对应一个班级，一个班级对应多个学生。使用id为主键
# class_id为外键，关联班级表id
drop table if exists student;
create table if not exists student(
    id int primary key auto_increment,
    sn int unique,
    name varchar(20) default 'unknown',
    mail varchar(30) not null ,
    class_id int,
    foreign key (class_id) references classes(id)
);

表中的某个列的值，必须是另一张表中的主键列，或者是唯一约束列的值，也就是当前表中的值在另一张表中必须存在，且满足主键或者是唯一约束

当我在student中插入一条在classes中存在的id记录时就可以正常插入成功：
但是当我插入一条在classes中不存在的id记录时就会提示插入失败：
由于在classes中不存在id为3的这条记录，而student中的class_id又是和classes中的id关联的所以就插入失败，可以形象的理解为：我现在要插入一个学生，但是我只能把他放在已经存在的班级中去，总不能把他放在一个不存在的班级吧。
当子表中存在对主表的依赖的时候，不能删除主表中相应的记录:
它依然会报一个主外键的错误，如果要删除主表中的记录，字表不能有对该条记录的依赖，意味着要先删除字表中的记录，再删除主表中的记录
由于我们先将student中刚刚插入的与classes有关的数据删除了，所以这时就可以删除classes中的相关数据列。

check约束

当我们有时候输入数据时，有时候只会有几种固定的选项去输入，因此在输入时，可以让MySQL与分析check子句，判断输入的数据是否满足要求。

drop table if exists test_check;
create table test_check(
    id int primary key auto_increment,
    name varchar(20) default 'unknown',
    gender varchar(10) not null ,
    check ( gender='male' or gender='female' or gender='unknown')
);
insert into test_check (name,gender) value ('赵qq','male');
select * from test_check;
insert into test_check (name,gender) value ('赵q','nan');

当我插入的数据符合要求时，就会插入成功：
当输入的数据不符合要求时，就会显示插入失败：

补充：对于MySQL数据库，它会对check子句进行分析，但是会忽略check子句，在MySQL中是有效的，但是在其他版本例如MySQL5.7中是不生效的，所以一般对于数据的有效性校验是在代码层面进行操作的。

表的设计

OOA面向对象分析-->OOD面向对象设计-->OOP面向对象编程

从需求中获得类，类对应到数据库中的实体，实体在数据库中就表现为一张一张的表，类中的属性就对应着表中的字段（列）。
确定类与类之间的关系。
使用SQL去创建具体的表。

设计表的时候需要遵守一些规则，一般这些规则就称之为三大范式。

范式

范式描述的是数据关系模型：一对一关系，一对多关系，多对多关系

分类：第一范式1NF，第二范式2NF，第三范式3NF，BC范式，BCNF

第一范式1NF

第一范式是关系型数据库的一个最基本的要求，不满足第一范式就不可以称为关系型数据库：

表里的字段不可以再进行拆分。

描述一个学生的信息或者正确设计一个学生表：

应该将一个学生表分为：学号，姓名，年龄，班级名，学校名，学校地址，学校电话。
举出一个反例：学号，姓名，年龄，学校。

在第一种正确的设计方式中，每一列都是不可再分的，最起码可以表明一个学生和班级，学校之间的关系；而在第二列中，学校这个字段还可以再细分为：学校名，学校地址，学校电话等字段，并没有用一个数据型来表示学校，如果一个字段或者数据型可以继续拆分在关系型数据库中是绝对不允许的。
在定义表的时候，对照数据中的数据型，每一个字段都可以用一个数据型来表示，那么这个表就天然满足第一范式。

第二范式2NF

在满足第一范式的基本上，不存在非关键字段（非主键字段）对任意候选键（主键，外键，没有主键时的唯一键）的部分函数依赖（存在于复合主键（一个表中不可以存在两个主键，但是一个主键中可以包含多个列）的情况下）

场景：学生可以选修课程，课程有对应的学分，学生考试之后会针对每一门选修的课程生成相应的成绩，用数据库中的表记录学生成绩：

正解：学生表：学号，姓名，年龄
课程表：课程编号，课程名，学分
学生选修成绩表：学号，课程编号，成绩
反例：学生选修课成绩表：学号，学生姓名，年龄，课程名，学分，成绩

在第一种正确的设计中，每张表都有非主键字段，都强依赖于主键，满足了第二范式。
在第二种错误的设计中，学生相关的信息可以通过学号来确定，学分可以通过课程来确定，成绩是通过学生和课程共同区分，一个学生的选修课程，经过一次考试之后才会生成成绩；也就是说这个表中可以用学生和课程做为复合主键来确定同学当前选修课的成绩（主要作用）；学生的姓名，年龄与课程没有关系，即学生的姓名，年龄只依赖于学号，不依赖于课程；学分与学生没有关系，即学分只依赖于课程，不依赖于学生；
对于由两个或者多个关键字决定一条记录的情况下，如果一行数据中有些字段只与关键字段中的一个有关系，那么这种就说明它只存在部分函数依赖，如果有这样的情况就说明它不满足第二范式。
也就是说一个表中没有复合主键（主键只有一列）那么这种的表天然满足第二范式

不满足第二范式时可能会出现的问题

以上面的数据为例：

数据冗余：学生姓名，年龄，学分重复出现，造成大量数据冗余。
更新异常：如果需要调整某一门课的学分，那么就需要更新所有记录中关于MySQL的记录，如果一旦某些记录更新成功，某些更新失败，就会造成数据表中，同一门课程出现不同学分的情况，表现为数据不一致。
插入异常：目前这样的设计，每一门课与同学的考试是对应关系，只有同学进行考试之后才会生成一条关于这门课的成绩记录，这条记录中保存了课程的学分，也就是说一门新课在学生考试之前在数据库中是没有相应记录的，因为学生成绩为空时记录是没有意义的。
删除异常：把毕业的同学考虑全部删除之后，删除记录的同时，也可能把课程对应的学分全部删除了，导致一段时间内，数据中没有课程和学分的相应信息。

第三范式3NF

在第二范式的基础上，不存在非关键字段，对任意候选键的传递依赖。

描述学生就读于哪一个学院：

正解：根据学生于学院的关系，拆分成两张表即可：
学院表：学院编号，学院名，学院电话，学院地址
学生表：学号，姓名，年龄，学院编号
反例：学生表：学号，姓名，年龄，所在学院，学院地址，学院电话

对于第一种：两张都依赖于自己表中的主键，学生表可以通过外键与学院之间建立关联关系
对于第二种：那一条记录本质上是存在两个实体：学生，学院；因为现在要描述学生，所以学号是这个表的主键；在这个表的设计中，姓名和年龄与学号是强相关的，学院的地址，学院电话与所有学院是强相关的（描述清楚学生所有的学院，只需要把学生和学院建立一个关联关系即可）；现在在记录中出现了两种强相关关系：学号-->所在学院-->学院的电话,学院的地址，学院电话，学院地址原本不是直接依赖于候选键（学号），而是通过所在学院依赖于学号才得到依赖于学号的，这样的传递关系称之为传递依赖，所以这种设计不满足第三范式，因为存在传递依赖。
第三范式可以解决：数据冗余，更新异常，插入异常，删除异常的问题。

表的设计方法

从需求中获得类，类对应到数据库中的实体，实体在数据库中就表现为一张一张的表，类中的属性就对应着表中的字段（列）
确定类与类之间的关系：一对一关系，一对多关系（多对一关系），多对多关系，没有关系

一对一关系

比如一个网站的登入界面：登入时的用户名和密码用于校验，登入成功时显示对应的用户名。
这样的场景，一般对应着两个实体，一个实体是用户（记录个人信息，姓名，班级，QQ号，手机号），还有一个实体是账号（登入名和密码）。一个用户只能有一个账号，一个账号只能给一个用户使用。在设计表之前先按照上面的句式把实体之间的关系列出来。

针对一对一关系，设计表时，有两种方式：

把两个实体所有信息全部放在一张表里： user(user_id,name,age,phone_num,mail,username,password);(前四个字段时关于用户的信息，后面两个时关于账号的信息);
创建两张表，分别记录用户信息与账号信息，并把这两张表做关联：user(user_id,name,age,phone_num,mail); account(account_id,username,password,user_id);可以通过这两个字段确定一条记录，可以找到user_id，通过user_id就可以在user表中找到用户记录。

一对多关系

学生和班级之间的关系：一个学生只能存在一个班级，一个班级中可以有多个学生

分为不同的实体创建表：

学生表，班级表，创建表之后建立表与表之间的关联关系：
class(class_id,name);
student(student_id,name,age,class_id);
通过学生记录中的class_id可以表示学生在哪一个班级

多对多关系

一个学生可以选修多门课程，一门课程也被多名学生选修；

分别创建实体表：
course(course_id,name);[1 MySQL],[2 JAVA]
student(student_id,name,age);[1 张三 20],[2 李四 19],[3 王五 21]
创建关系表，在关系表，在关系表中为实体之间创建关联关系:
student_course(id,student_id,course_id);
[1 1 1]:张三选修了MySQL课程;
[2 1 2]:张三选修了JAVA课程;
[3 2 1]:李四选修了MySQL课程;
[4 3 2]:王五选修了JAVA课程;