mysql 10张表左关联查询_MySQL的多表设计和关联查询(数据库设计范式, 外键使用, 关联查询之内外交叉)...

多表设计-关联查询

为什么需要多表设计?

多表设计的目的就是为了消除冗余的数据,将一张表拆分成为多张表,或者将多张表中的共同数据提取到一张表中.

举个例子,有如下两张表

编号

姓名

民族

国籍

1

张三

汉族

中国

2

李四

汉族

中国

编号

省份

国籍

1

陕西

中国

2

广州

中国

这两张表中就存在着大量的冗余,如民族,国籍,若是我们对其进行多表设计就可以变为

民族表

民族编号

民族

1

汉族

2

回族

国家表

国家编号

国籍

1

中国

2

巴基斯坦

而最上面的两张表就可以变为

编号

姓名

民族编号

国家编号

1

张三

1

1

2

李四

1

1

编号

省份

国家编号

1

西安

1

1

广州

1

通过这样的方法我们可以将重复的冗余数据消除掉,并且可以做到对一个数据重复利用.

数据库设计范式

1.为了建立冗余较小, 结构合理的数据库,设计数据库时必须遵循一定的规则, 在关系型数据库中这种规则也被称作时范式. 范式是符合某一种设计要求的总结.

2.在目前关系型数据库中有6种范式:

(1)第一范式(1NF);

(2)第二范式(2NF);

(3)第三范式(3NF);

(4)巴斯-科德范式(BCNF);

(5)第四范式(4NF);

(6)第五范式(5NF), 又称完美范式.

3.满足最低要求的范式是第一范式(1NF), 在第一范式的基础上进一步满足更多的规范要求就被称为第二范式(2NF), 其余范式依次类推. 一般来说, 数据库只需要第三范式(3NF)就行了.

第一范式(1NF)

第一范式是最基本的范式, 要确保数据库表中的每一列都保持原子性, 如果数据库表中的所有字段值都是不可分解的原子值, 就说明该数据库表满足了第一范式.

举个例子

编号

姓名

联系方式

1

王麻子

邮箱/电话/qq

满足第一范式后的表结构

编号

姓名

邮箱

电话

QQ

1

王麻子

12345676@qq.com

1313131

2212321

第二范式(2NF)

1.第二范式就是要有主键, 要求其他字段都依赖于主键;

2.没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录, 所以要主键.

3.其他字段为什么要依赖于主键? 因为不依赖于主键, 就找不到他们. 更重要的是, 其他字段组成的这行记录和主键表达的是同一个东西, 而主键是唯一的, 它们只需要依赖于主键,也就成了唯一的.

第三范式(3NF)

第三范式就是要消除传递依赖, 方便理解, 可以看做是"消除冗余".

订单编号

数量

商品编号

商品名称

单价

订单金额

1000

2

2020

手机

2000

4000

商品编号

商品名称

单价

2020

手机

2000

订单编号

数量

订单金额

商品编号

1000

2

4000

2020

一般来说, 数据库只需要第三范式(3NF)就行了.

数据库实例

学生表:姓名,性别,手机号,年级编号,注册时间

年级表:年级编号,年级名称,年级介绍

课程表:课程编号,课程名称,课程介绍

表与表之间的几种关系:

1.一对一关联;

2.一对多关联;

3.多对一关联;

4.多对多关联.

-- 创建年级表

CREATE TABLE t_grade(

g_id INT PRIMARY KEY AUTO_INCREMENT COMMENT'年级编号',

g_name VARCHAR(10) COMMENT'年级名称',

g_desc VARCHAR(50) COMMENT'年级介绍'

)

-- 创建学生表

CREATE TABLE t_stu(

s_id INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号',

s_name VARCHAR(10) COMMENT'姓名',

s_sex CHAR(1) DEFAULT'男' COMMENT'性别',

s_phone INT(11) COMMENT'电话',

s_g_id INT COMMENT'年级编号',

reg_time DATETIME COMMENT'注册时间'

)

介绍一下弱关联关系: 表结构本质上没有联系,表与表之间的关系是人为定义的,删除关联表中的数据对另一个没影响, 如上图的年级编号和年级表是一种弱关联关系.

外键

再说一下强关联关系, 给外键添加约束,强制让外键与对应的主键产生关联关系(外键,对应的都是另一个表中的主键)

1.外键: 引用另外一个数据表的某条记录.

2.外键列的数据类型应于主键列保持一致.

3.数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键(foreign key)建立起来的.

建表时添加外键的语法:

create table 表名(

constraint 约束名 foreign key(外键列) references 主键表(主键列)

)

添加外键约束语法:

alter table 表名

add [constraint 约束名(自己起的)]

foreign key(外键列)

references 关联表(主键)

删除外键语法:

alter table 表名 drop foreign key 外键约束名;

数据库实例

为上表添加外键

ALTER TABLE t_stu

ADD CONSTRAINT s_foreign_key

FOREIGN KEY(s_g_id)

REFERENCES t_grade(g_id);

-- 创建课程表,多对多关系,一个学生对应多个课程,一个课程对应多个学生

CREATE TABLE t_course(

c_id INT PRIMARY KEY AUTO_INCREMENT COMMENT'课程号',

c_name VARCHAR(10) COMMENT'课程名',

c_desc VARCHAR(20) COMMENT'课程介绍'

)

-- 设计关系表来存储多个数据之间的关系

CREATE TABLE t_stu_course(

sc_id INT PRIMARY KEY AUTO_INCREMENT,

sc_stu_id INT,

sc_course_id INT,

CONSTRAINT stu_id_fk FOREIGN KEY(sc_stu_id) REFERENCES t_stu(s_id),

CONSTRAINT cou_id_fk FOREIGN KEY(sc_course_id) REFERENCES t_course(c_id)

)

注意

1.当主表中没有对应的记录时,不能将记录添加到从表

2.不能更改主表中的值而导致从表中的记录孤立

3.从表存在与主表对应的记录,不能从主表中删除该行

4.删除主表前,先删除从表

关联查询

概述

1.关联查询又称为多表查询, 当查询的字段来自于多个表时, 就会用到连接查询

2.笛卡尔乘积现象: 表1有m行, 表2有n行, 查询结果有m*n行

(1)发生原因: 没有有效的连接条件

(2)如何避免: 添加有效的连接条件

分类

内连接(inner join)

把满足条件的两张表中的交集数据查询出来

语法:

select 结果 from 表1,表2 where 表1.列1=表2.列2

SELECT s_id,s_name,s_sex,s_g_id,g_name,g_id

FROM t_stu,t_grade

WHERE s_g_id = g_id

1.等值连接

#等值连接

/*

语法

select 结果 from 表名 inner join 被连接的表名(外键表) on 外键=主键

*/

SELECT *

FROM t_stu

INNER JOIN t_grade ON s_g_id = g_id

2.非等值连接

#创建一个等级表

CREATE TABLE t_level(

l_name CHAR(1),

l_min_score INT,

l_max_score INT

)

/*

非等值连接

语法

select 结果

from 表1

inner join 表2

on 表1.列1 between 表2.列2 and 表2.列3

*/

SELECT

s_name,

l_name

FROM

t_stu

INNER JOIN t_level

ON s_score BETWEEN l_min_score

AND l_max_score

3.自连接: 在一张表中建立连接关系

CREATE TABLE t_area(

a_id INT,

a_name VARCHAR(10),

a_father INT

)

#在一张表中建立连接关系

SELECT

t1.a_name,

t2.a_name

FROM

t_area t1

INNER JOIN t_area t2

ON t1.a_father = t2.a_id;

外连接

1.左外连接(left join)

/*

语法

select 结果

from 表1

left join 表2

on 表1.字段=表2.字段l;

不管表1的字段与表2的字段是否有连接,都会显示表1的所有信息

*/

SELECT *

FROM t_stu s

LEFT JOIN t_grade g

ON s.s_g_id = g.g_id;

2.右外连接(right join)

/*

语法

select 结果

from 表1

right join 表2

on 表1.字段1=表2.字段2;

不管表1有没有和表2中的所有数据关联,都会显示出表2的所有

*/

SELECT *

FROM t_stu s

RIGHT JOIN t_grade g

ON s.s_g_id = g.g_id;

交叉连接

-- 交叉连接 类似于内连接

SELECT *

FROM t_stu s

CROSS JOIN t_grade g

ON s.s_g_id = g.g_id;