mysql笔记5(列属性完整性)

1. 列属性问题

        根据业务判断某个字段是否可以为空、是否设置默认值、是否设置为自动增长、是否设置为主键等

① not null

例如：查询成绩时准考证号这个字段就可以设置为非空字段，只有输入准考证号才可以查询相关成绩

② default

例如：成绩这个字段就可以设置一个默认值(“缺考”)，当有人没有参加考试时，ta的成绩显示的就是默认值"缺考"，而不是0

③ auto_increment

例如：学生id、餐厅订单号等都可以设置自动增长这个属性

① 具备auto_increment 属性的字段编号一定是从1开始的
② 自增一定是主键，但主键不一定自增

2. 主键(Primary Key)

PRI：主键

① 作用

① 确保该表内数据的存在性、唯一性、不可重复性
例如：收集信息时可以把身份证号设置为主键，用来标识唯一人口
② 用于实现表关联，表关联必须要有主键
例如：学校的学生信息表里的学号也可能会被餐厅消费记录表、图书借阅表等使用。通过学号就可以把这些表关联起来。
        因此主键不仅可以在当前表里唯一标识数据，也有可能作为其他表查询数据的依据
表关联的好处：
例如：饭卡丢了以后可以到调一下该卡在餐厅的消费记录，锁定时间段，然后查监控，从而找到卡

② 好处

① 保证数据的完整性
例如：给新生儿上户口，让它们拥有唯一的身份证号，从而保证后续发放福利时不会把ta们遗漏
② 加快数据的查询速度
例如：利用身份证号就可以快速检索到我们的所有信息

③ 特点

① 不可重复
② 不能为空(主键是不能为null的，除非设置为自增)

④ 设置主键

建表时

后期添加

alter table 表名 add primary key(字段名);

⑤ 查看主键

① desc 表名; PRI代表主键

② show create table 表名;

⑥ 删除主键

alter table 表名 drop primary key;

⑦ 注意

① 选择主键时一定要慎重(尽可能选择那些更新、改动比较少的字段作为主键(例如：id、身份证号),而且尽可能只设置一个主键就好了，两个及以上不好维护。除此之外，大多数人都选择用数字号码的方法作为主键，因为数字相对于字符串而言更方便查询)
例如：把姓名设置为主键显然就不是很合理
② 一张表里只能有一个主键，但是一个主键可以由多个字段共同组成(即 设置一个组合键)
例如：一张表里定义两个主键会报错

③ 如果没有找到某张表里的主键怎么办？---- 答：给该表设置一个自增的id

3. 组合键(复合键)

① 设置复合主键

建表时

后期添加

alter table 表名 add primary key (字段名,字段名);

② 删除复合主键

alter table 表名 drop primary key;

③ 复合主键的作用

        当我们注册一个网站的账号时，我们就会拥有一个关于该网站的一个唯一的编号(主键)。通过该编号，就可以查询到我们在该网站的所有浏览、点赞等记录。
        但是如果这个网站想确保每个用户也都拥有不同的昵称，该怎么办呢？
        此时就可以设置一个联合主键，这样一来每个用户都有唯一的用户编号和昵称。

④ 复合主键的弊端

扩展性不好
例如：假如现在有一个论坛，论坛里有很多文章，那究竟是用id还是用昵称来确认文章的归属呢？

4. 唯一键(Unique)

① 作用

保证该表内数据的唯一性、不可重复性

② 特点

① 可以为空
② 一张表里可以有多个唯一键

③ 设置唯一键

建表时

UNI：唯一键

后期添加

alter table 表名 add unique (字段名);

④ 创建组合唯一键(意义不大)

alter table 表名 add unique (字段名，字段名);

⑤ 查看唯一键

① desc 表名; UNI代表唯一键

② show create table 表名;

(多个唯一键)

(组合唯一键)

⑥ 删除唯一键(只能一个一个删)

alter table 表名 drop index 唯一键名;

⑦ 创建多个唯一键和创建一个组合唯一键的区别

情境：分别创建一张表(t_8：创建了组合唯一键；t_10：创建了多个唯一键)，均将id设置为主键，将name、phone设置为唯一键。

① 表建好之后插入数据：
t_8中插入的数据只需要保证该条数据的name和phone不同时和之前的数据重复即可；t_10中插入的数据name和phone中有一个和之前的数据重复就无法插入
② 删除唯一键：
t_8中任意删除name或者phone的唯一键属性，另一个的唯一键属性也随之消失，甚至再用命令删除另一个的唯一键属性会报错；t_10中删除任意一个的唯一键属性以后，另一个的唯一键属性依旧存在


(以上是t_8的)>


(以上是t_10的)

5. 唯一键和主键的区别

① 唯一键可以为空，主键不能为空
② 一张表里可以有多个唯一键，一张表里只能有一个主键(哪怕是复合主键也只是由多个字段创建的一个主键)
③ 唯一键只在当前表里折腾，主键可能被其它的表或者数据库引用(主键一定是和其他表、社会中的其它产品绑定的。但是唯一键不同，唯一键就和其它表里的没有关联，只在当前的表里是唯一的)

例如：上述教师信息表中，主键id后续可能会被餐厅消费表、图书借阅表等使用。但是phone这个唯一键可能对其他表的用处就不太大，因此它只需要保证当前表中phone这个字段的数据不会重复就好了。

6. sql语句注释和字段注释

① sql语句注释

单行注释：# 或 - -
多行注释：/* */

② 字段注释(常用)

comment

7. 数据库完整性

        通常情况下DBA在设计时会考虑4种完整性：实体完整性(对应下面的①)、域完整性(对应②)、引用完整性(对应③)、自定义完整性(对应④)
相关介绍

设计数据库时应该保证：
① 字段完整：
        一张表里至少要有一个字段具备唯一性约束，保证不会出现重复数据。哪怕不是唯一键约束，至少也应该有一个主键约束(尽可能要用自动增长或其它方式保证数据不会重复)去唯一标识数据。
② 数据类型正确：
        给字段选择合适的数据类型(例如：手机号用 int 显然不合理)、该字段是否可以为空、是否设置default(例如：缺考、地址不详、原因未知等)
③ 可能需要：
        该表内的字段是否有可能被其它表引用
④ 自定义约束：
        业务逻辑约束(例如：订单的结束日期不能早于开始日期)、数据一致性约束(例如：某个字段的值只能从给出的几个选项里选择一个填写，跟枚举有些类似)、复杂验证(例如：当前消费额是否满足折扣规则)、触发器和存储过程(例如：这些触发器和存储过程可以在插入、更新或删除数据时自动检查数据的有效性)、数据完整性检查(例如：邮箱格式是否正确、数据是否合法等)

8. 外键

① 引用数据表的完整性问题，抛出外键的概念

        学生表利用学号唯一标识某个学生的信息(例如：姓名、电话、住址、性别)，食堂交易表也要利用学生的学号统计订单信息(例如：谁打了什么饭、花了多少钱)。既然如此，该如何处理这个问题呢？
        我们不难发现这两张表是有关联的，它们存在公共的字段 – 学生的学号。食堂交易表里的学生学号应该跟学生表里的学号是对应的。因此我们可以使用外键把两张表关联在一起

        当我们想知道张三同学今天在餐厅的消费记录时，我们只需要检索 student_id=001的数据即可。
注：
① 从表(食堂交易表)里student_id的数据不能出现主表(学生表)里没有的stu_id数据
② 销毁这两张表时，应该先销毁从表，再销毁主表

② 外键的作用

实现表关联

③ 设置外键

建表时

写在从表里：foreign key (字段名) references 哪张表(那张表里哪个字段)


MUL：表示该字段的数据可以重复(例如：张三可以买很多份饭)

后期添加

alter table 从表表名 add foreign key (从表字段名) references 哪张表(那张表里哪个字段)

④ 查看外键

show create table 表名;

⑤ 删除外键

① 第一步：
查看外键在该表中的别名：show create table 表名;

② 第二步：
alter table 表名 drop foreign key 外键别名;

⑥ 外键操作

严格性操作(基础操作)

添加、删除、更新一个外键

置空操作

① 关键字：on delete set null
② 作用：一般留给外键进行删除操作
例如：学生表中001号学生被开除了，把食堂交易表中有关ta订单的student_id全部变成null

级联操作

① 关键字：on update cascade
② 作用：一般留给外键进行更新操作
例如：学生表中002号学生留级了，把食堂交易表中有关ta订单的student_id更新成102

置空、级联操作演示

一般在创建表时就会确认是否存在置空和级联操作

创建学生表和食堂交易表：
查看食堂交易表

往学生表里插入数据：

往食堂交易表里插入数据：

级联操作：把学生表里Tom的学号改成4，发现食堂交易表里有关Tom的交易记录的学号也由1改成了4


置空操作：把学生表里Jerry的信息删除(实际开除学生后并不会删除其信息)，发现食堂交易表里有关Jerry的交易记录的学号也由2改成了null

⑦ 什么时候设计外键

        我们都知道设置外键时可以在建表时设置或者建完表以后后期添加，那么最好什么时候设置？
        一般情况下，我们使用关系型数据库时，在设计表时就应该把表结构确定得大差不差，达到结构统一化。开发期间应该尽可能不去修改表结构。
        后期添加外键相当于后期维护，此时再添加外键会增加维护成本。而且如果项目已经上线了，此时添加外键，就会产生一个新的麻烦 – 之前的用户数据怎么办？
        不可否认有后期添加外键的情况，但最好在建表时就考虑好要不要设置外键。

⑧ 注意

        我们知道有外键这个东西就行，实际开发过程中，尤其是对并发项目的处理，是禁止使用外键的。目前做这种并发处理效果的项目(例如：淘宝)，凡是涉及到这种并发操作的，基本上不会采用外键，甚至ta们是杜绝外键的。
① 问：为什么？
答(gpt)：
考虑到性能瓶颈、锁竞争、灵活性
② 问：用什么替代外键？
答(gpt)：
应用层处理(业务逻辑和服务)、使用事务、使用业务服务(服务层验证)、数据校验和任务(定期检查和修复)

9. 内容出处

mysql