Mysql关系、连接

Mysql关系、连接

文章目录

• 关系
• 连接查询
• • 查询格式
  • 建立逻辑查询分析过程

关系

表A 与 表B
1对1的关系，则将关系存储在A或B表中都可以
1对多的关系，则关系存在多的表中（表B是多的对象）
多对多，则关系存在一张新的表中

如果遇到如下这种关系闭合的情况，则必须让关系给断开：（任意断开一条，都能将三个表联系上，故需要根据工程需要即可）

创建score表，经过分析，是1对多的关系，且多的关系是在score中，故多的关系建立在score中

外键的级联操作

在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
推荐使用逻辑删除，还可以解决这个问题
可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作

级联操作的类型包括：
。restrict（限制）：默认值，抛异常
。cascade（级联）；如果主表的记录删掉，则从表中相关联的记录都将被删除
。set null：将外键设置为空
。no action；什么都做

注意：下面两种格式都可以，只要是在On后面已经出现了的表格都可以，但每一个On后面的关系必须与On前面的的那个表有关系

select students.name,subjects.title,scores.score
from scores
inner join students on scores.stuid=students.id
inner join students on scores.subid=subjects.id;

select students.name,subjects.title,scores.score
from students
inner join scores on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

连接查询

连接查询分类如下：

• 表A inner join表B：表A与表B匹配的行会出现在结果中
• 表A left join表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
• 表A right join表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
• 在查询或条件中推荐使用“表名.列名”的语法
• 如果多个表中列名不重复可以省略“表名.”部分
• 如果表的名称太长，可以在表名后面使用”as简写名“或”简写名”，为表起个临时的简写名称

inner 是仅在建立关系的表之间共有的数据才会显示，例：A表中有但是B表中没有的，用inner建立A与B之间的联系，则A相对于B多余的部分则不会显示出来

left 以left左边的表为基准，显示右边的表中的数据。如果左边表中数据比右边表中的数据更丰富，则多出的部分，右边的表会以null填充

同理，right 则以右边的表为基准

查询格式

select distinct 列*
from 表1 inner|left|right join 表2 on 表1与表2的关系

asc |desc 
limit sartt，count

建立逻辑查询分析过程

1、确定实体间是否有关系
2、确定是几对几的关系
3、确定在哪个实体中建立字段

• 查询男生的姓名、总分
  分析：
  1、查询结果的列名称：姓名（因为来自students表，故写成students.name）、总分（聚合并且分数来自啊scores表，故写成sum(scores.score)）
  2、条件：男生（性别来自students表，故写成der=1）
  3、建立连接：students表中的id与scores表中的studid（其中studid是上文外键关系时得到的）（students.id = scores.studid）
  4、因为是求每个男生的总分，所以需要姓名分组

select students.name sum(scotes.score)
from sccores
inner join students on students.id=scores.id
der=1
group by students.name;

注意，由于查询的结果关系多个表，所以所有列的参数前面都需要带上其所属表的名称，如students.name

• 查询科目的名称及其平均分

分析：
1、查询结果的列名称：科目名称（因为来自subjects表，故写成subjects.title）、平均分（聚合并且分数来自啊scores表，故写成avg(scores.score)）
2、建立连接：subjects表中的id与scores表中的subdid（其中subid是上文外键关系时得到的）（subjects.id = scores.studid）
3、因为是求每个科目的总分，所以需要科目分组

select subjects.title avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.title

• 查询未删除科目的名称、最高分、平均分

select subjects.title,max(scores.score),avg(scores.score)
from scores
inner join subjects subjects.id=scores.subid
where subject.isdelete=0
group by subjects.title;