当前位置：首页 > news >正文

数据库约束和查询

news 2025/7/19 4:35:02

一约束意义

这个后面的字段是什么意思呢? 先前说数据类型是一种约束，约束我们只能放该类型的数据，还有其它的约束来保证数据的合法性，下面的字段就和约束有关。

编译器的编译就是一个约束，保证我们的代码一定是语法合格的。我们有可能在表将qq号写到学号上，我们应该避免这种问题，这些都是需要我们去增加约束来保证的。

二约束介绍

1 空属性约束

添加空属性约束

我们发现NULL字段有些填YES，有些填NO，NO就是表示该字段不可以为NULL，

用户必须插入数据，不能让表格显示为NULL，注意，有时候是显示为空，而不是为NULL，后面提，接下来演示一下。

下面两个insert的sql语句报错是不同的情况，我们已经知道insert into 表名(列名称) values(列名称)这句sql语句，值得一提的是，values中没写的列，它们的值是用默认的值。而由于如果我们的列有了not null这个约束，此时不会给我们加上default null，所以values()插入时会报错说没有默认值。

后面那个报错就是我们真的插入了null，这个违背了空约束。

2 默认值约束

接下来要讲的约束，就和上表的Default字段有关。

此时我们values没有值，但是也没有报错，就是因为有了默认值。

之前创建表未指明default值，缺省值为NULL。

3 列描述

没有实际意义，也没有什么约束功能，就是一个对列成员的描述，让后来者可以快速知道这个表的成员意义，就像注释一样。

comment字段呢不显示在该表中，要用下面方式才可以查看。这个注释可以让程序员知道这个字段的意义，可以一定程度上约束我们输入的数据，例如我们知道这个列成员是学号，就不会数据一个班级上去。

4 显示约束

zerofill

type是成员类型可以理解，那为什么会有数字11。这个其实是显示的最大数据位数。

zerofille表示将数据增加零来扩充宽度。若数据位数大于10，则无需补零。上面不是11位吗，为什么才十个数字，因为int默认有符号，还要一个字符给符号位。

有意思的是zerofill和default是有顺序要求的，我反过来书写会报错。

5 主键约束

设定主键，主键列元素不能重复，而且该列自动设为非空约束。主键只能有一列，唯一键可以有多列。

插入几行数据进来。

主键标识一行的唯一性，我们可以来搜索某一行出来修改。

去除主键，直接alter drop，不用指定列名，因为主键只有一列，这个不是删除列，而是去除主键约束。

表已经建立好了，我们也可以增加主键，但是会检测表内是否有冲突行。

复合主键，我们可以让id和name合起来为key，也就是只有当id和name合起来都重复了才算冲突。

再来看看发现有两列都是主键了。

我们插入几条数据，发现id可以重复，名字可以重复。

此时id和name都重复就不行了。

6 自增长约束

自增长约束，首先必须是主键列，而且改列的类型是整数，显然自增长列也只有一列。而且我们还可以指定自增长的起始值。

id列无需传值，自动插入，自动增长。

我们也可以自己指定id。

随后的id从11开始自增。原因有一个auto_increment在维护。

7 唯一键约束

id必须是唯一的，感觉和主键差不多? 唯一的区别就是id可以为空，而主键不可以。

我们甚至可以让唯一键不为空，这样就和主键几乎完全一样了。可能还有其它列也是要有唯一性的，就有了唯一键，那还要主键干嘛呢，全是唯一键不就行了? 可以认为保存每个学生的数据时，每个学生都必须要有学号，必须唯一，不能有人无学号，这是主键约束，然后就是每个学生的电话号码不能重复，但是你可以为空，由此我们发现唯一键约束是约束性小的主键约束。

问1 为什么主键只有一个，这个其实和索引有关系，学了索引我们就知道，主键索引是最特殊的索引，其它的索引都是普通的，两者要维护的数据结构不同，当然要相互区分。

8 外键约束

外键是和下表的最后一个字段有关。

如果我们把班级表上的列属性都放到学生表中，此时每行都要保存id和班级名，比较冗余，

如果我们将班级表抽离出来，然后学生表每行就只保存class_id了，不用保存name了，字节数来看是节省了不少的。

接下来创建学生表和教室表。

注意，class_id在教室表中是主键，学生表中的class_id则是普通列成员，外键约束是在从表中建立的，作用后提，什么是主从表也后提。

我们给两张表插入一些数据。

学生表插入时应该能够判断班级id是否存在? 如果不存在就会报错。

还有教室表如何判断能否删除？就是要判断学生表是否无该班级学生，无就可以删除了。

所以我们本来是想要节省空间，但是却衍生了一些问题，两个表在插入删除的时候不能无脑用mysql语句了，解决: 就是我们给这两个表建立某种约束。

重新创建表，添加外键约束，在从表定义，这个就是语法而已，在从表定义，会在两个表都产生约束。

如何区分主从表? 从表的列成员是主表主键，从表列成员和主表主键建立了外键约束。主表的主键列可以是唯一键或者主键。主表，从表只是一种分别称呼两个有关联表的称呼而已，因为这两张表在遇到插入删除时处理动作不同。

这个约束建立后，从表在插入时会拿着学生的class id去关联的表中查找，不存在报错。

在主表教室表删除时，也会拿着class id去学生表查找，存在就不能删。

总而言之，学生表为了节省存储的字节，将一些重复的并且较长的教室名字段拆出，学生表称为从表，教室表称为主表，从表保存class_id，通过class_id映射教室名，查询class_id必须能对应一个教室名，也就是有唯一性，所以是主键或者唯一键，然后就是两张表要维护一个约束，使得表的增删改查要多考虑一些东西，也就是外键约束。

三 curd

先前我们大致了解了表的操作，接下来再回忆一下curd操作，方便后续查询的时候使用。

Create(创建)，Retrieve(读取)，Update(更新)，Delete(删除)。

创建表

多行插入

插入更新

qq和name都必须是唯一的。

之后我们再插入可能会发生冲突，我们想插入失败转为更新语句，就用如下sql。

我解释一下我对下面这句sql语句的理解，冲突是从左往右比对的，所以如果name和qq都冲突，会说name冲突了，冲突就执行后面的update，但是在更新的时候更新值不能和旧的其它行冲突，可以和原先行相等。

下面这个提示都是用insert去演示一下。

replace一句就可以代替上面的两句，这个代替是先删除再插入。

读取Retrieve

主要是用select语句。

全列查询

我们把*换成特定列名称，此时我们就可以对特定列查询了，指定列。

多列

注意，这个不是不是查询多列，而是对name列重命名。

多列重命名

去重，在列前面加个distinct即可。

select后面还可以跟着表达式，显示的列名就是表达式，也可以重命名。

where字句

我们可以对一些行做筛选，这个指令在下面的查询中经常用到。

注意，在where字句中NULL用<=>运算符比较，或者用is NULL或者is not null，一个=只能用来数字和字符的比较。

还有很多其它的运算符后面在实际例子中学习。

between a0 and a1可以用来代替 >= a0 and <= a1，判断是否为null可以用如下方式。

四查询

1 需求实战

建立表。

需求1

找英语成绩小于60的。

需求2

找语文成绩在80-90的，并且显示出来语文成绩。

这里我们就用到了and这个并且的逻辑运算符，我还可以用下面这个。

需求3

找数学成绩是58，59，98，99都找出来。

此时就用到了or这个运算符，我们也可以用下面这个代替。

需求4

在表中找一个姓孙的一行，此时就涉及模糊匹配，也是可以用一个运算符like来处理。=是精确匹配，不能用下面这两个通配符。

注意%和_的意义。

_只能匹配一个字符，*可以匹配任意个字符。

需求5

语文成绩好于英语成绩的。

需求6

求总分小于两百的。

为什么下面报错说total找不到了，因为select执行顺序先通过from定位表，然后识别筛选条件，最后才去select筛选，将筛选结果显示的时候才对列重命名total，所以在条件识别时sql就不认识total，所以就报错了。

只支持对列名称重命名，不支持在筛选条件重命名。

需求7

语文成绩大于80，且不姓孙的同学。

需求8

孙某同学，否则总成绩>200，并且语文<数学成绩并且英语>80。

可用括号将多个条件合并为一个单元。

NULL查询

所以找NULL不能name=NULL。

2 对结果排序

order by字句排序，用数学成绩来排序。

我们可以指定升序降序，默认是按照升序的。

3 排序练习

1 依次按照数学降序，英语降序，语文升序来排序显示，也就是说如果数学成绩相等就按照英语来排，也相等就按照语文来排序。

如果数学成绩不相等就直接按照数学来排了。都是降序可写如下简略写法。

2 查询同学总分排名。

order为什么能使用别名，还是和执行顺序有关。先select筛选再order排序，先排序再select效率太低，因为不是所有的数据都要排序的，先select筛选是指执行where，然后执行select .. from。

3 查询姓孙的同学或者姓曹的同学的数学成绩，结果按照高到低显示。

先筛选出数据，再排序。

limit可以对结果进行截取。只有一个数字表示从开始位置截取，截取行数为1。

offset可以指定起始位置。

limit 1，4是从第二行开始往后显示4行，数据排序好要显示了才到limit来限制显示，所以Limit还在select，order之后。

update

update本质也是查找，因为需要先找到需要更新的行，才能更新。

尝试1，将孙悟空的数学成绩更为80。

多列更新如下。

尝试2，将后三名同学的数学+30分。

此时我们就拿出了后三名同学成绩。

显然我们是先排序，再获取前三行，再更新，所以更新顺序排在limit后面，加了三十分后对总分排名会有影响，此时再获取前三行不一定符合原意。

4 delete删除

这里delete是删除一行，alter drop是删除表的一列，是对表结构做修改，我们学的curd都是对内容做修改。

删除总分倒数第一。由于delete和select语法格式不太一样，所以我们的总分无法重命名。

如果没有limit那就是删除整张表了。

5 截断表

清空表不会对 auto increament清零。

插入多个数据。

截断和delete表都会清空表，但是截断时会对auto increament清零。还有个区别是truncate比delete快，因为不会变成事务。

事务: 我们的sql操作痕迹一般会变成事务被记录下来，方便溯源恢复，而截断不保存，所以比delete少事，也就更快了。值得一提的是，数据库有一种内存文件来保存数据，断开时会紧急保存到磁盘上，所以如果恢复数据库，我们可以重新执行sql语句，也可以读取磁盘文件来恢复数据。

6 去重

insert和select组合，案例如下

插入数据。

下面这个可以去重，但是我们要构建一个无重复的，现在用去重的结果创建出一个表。

创建被去重表，还可以用like运算符来模仿现有表结构。

选择唯一的插入表中，此时insert可以没有value?

再修改表名，原先的duplicate_table改成其它的表名，这个最好要在一句sql语句中完成，保证原子性。

实际上不能把文件直接上传到某个目录下，因为上传会很慢，不是原子的，一般是上传到中间目录下，然后再move转到特定目录下，move和rename是原子的，只需要更改目录的保存的文件名和innode映射即可。所以当我们在创建新的duplicate_table时，也要保证原子性，以免修改时影响上层使用。