mysql联表关联时隐式转换 mysql关联表设计

文章目录

• 多表设计
• 数据库建立范式

• 第一范式(确保每列保持原子性)
• 第二范式(要有主键)
• 第三范式(外键)

• 外键(引用另外一个数据表的某条记录)
• 注意事项

• 关联查询

• 内连接

• 等值连接
• 非等值连接
• 自连接

• 外连接

• 左外连接

• 语法

• 右外连接

• 交叉连接

多表设计

Q : 为什么需要多表关联?
A : 数据冗余 —> 拆分

Eg:

数据库建立范式

• 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。
• 目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
• 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

第一范式(确保每列保持原子性)

• 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该 数据库表满足了第一范式

其中联系方式还可以拆分

学号	姓名	性别	联系方式
100	tom	男	邮箱/电话

拆分为不可分解的原子值后

学号	姓名	性别	手机号	邮箱
100	tom	男	邮箱/0912-0000/	[e-mail]

第二范式(要有主键)

• 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
• 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。

第三范式(外键)

• 要消除传递依赖，方便理解，可以看做是“消除冗余”

订单编号	数量	商品编号	商品名称	单价	金额
1000	2	2020	手机	2000	4000

拆分为下面俩张表

订单编号	数量	金额	商品编号
1000	2	4000	2020

商品编号	商品名称	单价
2020	手机	2000

外键(引用另外一个数据表的某条记录)

• 数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来的。

1 建表的时候之间加外键

create table score(
 cid int not null auto_increment primary key,
 score int,
 sid int,
 CONSTRAINT 约束名 foreign key(sid) references student(sid)
 );

2.建完表之后加

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(外键列) REFERENCES 关联表(主键);

3.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

注意事项

1. 当主表中没有对应的记录时，不能将记录添加到从表
2. 不能更改主表中的值而导致从表中的记录孤立
3. 从表存在与主表对应的记录，不能从主表中删除该行
4. 删除主表前，先删从表

关联查询

含义: 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

• 发生原因：没有有效的连接条件
• 如何避免：添加有效的连接条件
  按功能分类

内连接

求两张表的交集

等值连接

在连接中使用等号（=）操作就是等值连接。

SELECT t_student.t_student_id,t_student_name,t_student_sex FROM t_student INNER JOIN t_grade ON t_student_id = t_grade_id

非等值连接

主要用除了等号之外的操作符，比如：<>、>、<、>=、<=、LIKE、IN、BETWEEN…AND。

SELECT t_student.t_student_id,t_student_name,t_student_sex FROM t_student INNER JOIN t_grade ON t_grade_id>2

自连接

自己连接自己

SELECT t1.name,t2.name FROM t_area AS t1 INNER JOIN t_area AS t2 ON t1.pid = t2.id

外连接

左外连接

语法

select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2

查询的是左边+两表交集

右外连接

select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2

查询的是右表+两表交集

交叉连接

可以理解为笛卡尔积现象