MySQL多表关联查询

外键(foreign key)

外键的定义：
    [constraint 约束名称] foreign key(作为外键的字段名) references 父表名称(父表主键)
      [on delete cascade]  [on update cascade];
外键的作用：用来约束子表中的记录，使之与父表的记录对应

连接查询

等值连接：select 字段1,字段2,... from 表A,表B where 连接条件 [其他过滤条件];
内连接： select 字段1,字段2,... from 表A inner join 表B   on 连接条件[其他过滤条件];
      等值连接与内连接的效果是一样的，都是查询出符合连接条件的记录
外连接：
     左外连接: select 字段1,字段2,... from 表A  left join 表B   on 连接条件;
     注意：左外连接的查询结果是，除了返回表中符合条件的记录外还要加上左表中剩下的全部记录。
     右外连接: select 字段1,字段2,... from 表A right join 表B on 连接条件;
     注意：右外连接的查询结果是，除了返回表中符合条件的记录外还要加上右表中剩下的全部记录。

“一对多”关系表设计

“一对多”关系：第一个表中的单个行可以与第二个表中的一个或多个行关联，但第二个表中的一个行只可以与第一个表中的一个行关联。 例如：学校表(school)与学生表(student)。学校表(school)中的一条记录可以对应学生表(student)中的多条记录；学生表(student)中的一条记录只能对应学校表(school)的一条记录。
设计思路：通过在子表（“多”的一方）上设置外键的方式，关联父表（ “一”的一方）的主键。这样，父表的一条记录就可以关联子表的n条记录了。 

#一对多关系演示
先创建“父表”（“一”方）
create table school(
   schoolid    int  auto_increment primary key,
   name   varchar(20) not null,
   introduce   text
);

插入一些学校记录数据
insert into school(name,introduce)values('清华大学','清华大学非常好');
insert into school(name,introduce)values('北京大学','北京大学也不错');
insert into school(name,introduce)values('西安邮电大学',null);

再创建“子表”（“多”方）
create table student(
   stuid   int  auto_increment primary key,
   name   varchar(20) not null,
   score  double not null,
   school_id  int,
   foreign key(school_id) references school(schoolid) 
    on delete cascade  on update cascade
);

插入一些学生记录数据
insert into student(name,score,school_id)values('张三',90,8);
insert into student(name,score,school_id)values('李四',93,9);
insert into student(name,score,school_id)values('王五',96,8);
insert into student(name,score,school_id)values('赵六',96,9);



等值连接查询
查找每个学生的姓名、成绩、所在学校的名称
SELECT stu.`name`,stu.`score`,sc.`name`  FROM school sc,student stu WHERE stu.`school_id`=sc.`schoolid`;
查找王五的姓名、成绩、所在学校的名称
SELECT stu.`name`,stu.`score`,sc.`name`  FROM school sc,student stu WHERE stu.`school_id`=sc.`schoolid` AND stu.`name`='王五';

内连接查询
SELECT stu.`name`,stu.`score`,sc.`name`  FROM school sc INNER JOIN student stu ON stu.`school_id`=sc.`schoolid` AND stu.`name`='王五';

左外连接查询
SELECT stu.`name`,stu.`score`,sc.`name`  FROM school sc LEFT JOIN student stu ON stu.`school_id`=sc.`schoolid` ;

右外连接查询
SELECT stu.`name`,stu.`score`,sc.`name`  FROM school sc RIGHT JOIN student stu ON stu.`school_id`=sc.`schoolid` ;

“多对多”关系表设计

“多对多”关系：第一个表中的一个行可以与第二个表中的一个或多个行关联。第二个表中的一个行也可以与第一个表中的一个或多个行关联。
 设计思路：通过设置中间表的方式实现“多对多”关系表的设计。中间表至少有两个外键字段，分别关联于两张“多”表的主键,而且这两个外键字段的组合值不能重复(可以通过将这两个字段设置为联合主键，以确保它们的组合值不重复)。

多对多关系：成员表(members)与社团表(community)

# 创建members表
create table members(
   mid   int  auto_increment primary key,
   name   varchar(20) not null,
   sex    char(2)
);

# 插入成员信息
insert into members(name,sex)values('郭靖','男');
insert into members(name,sex)values('杨过','男');
insert into members(name,sex)values('黄蓉','女');
insert into members(name,sex)values('小龙女','女');

# 创建社团表
create table community(
   cid   int  auto_increment primary key,
   name   varchar(20) not null,
   introduce text
);

# 插入社团信息
insert into community(name,introduce)values('街舞社团','喜欢跳舞的可以加入');
insert into community(name,introduce)values('武术社团','爱好武术的团体社团');
insert into community(name,introduce)values('吉他社团','Guitar lover...');


# 设置中间关联表
create table members_community(
   members_id  int,
   community_id  int,
   joindate  date,
   foreign key(members_id) references members(mid),
   foreign key(community_id) references community(cid),
   primary key(members_id,community_id)
);

# 插入中间表信息（设置关联）
insert into members_community(members_id,community_id,joindate)
       values(1,2,'2024-01-01'),(2,2,'2024-01-02'),
       (2,3,'2024-01-03'),(3,1,'2024-02-02'),
       (3,2,'2024-02-08'),(3,3,'2024-02-22');

# 查询成员姓名与成员参加的社团名称、加入社团时间
select   m.name 成员姓名,c.name 参加的社团,mc.joindate 加入日期
                                from members m 
                                inner join
                                members_community mc
                                on m.mid=mc.members_id
                inner join
                community c
							 on mc.community_id=c.cid;

“一对一”关系表设计

“一对一”关系：第一个表中的单个行只可以与第二个表中的一个行关联，且第二个表中的一个行也只可以与第一个表中的一个行关联。
 设计思路：
基于“唯一外键”的设计：“一对一”关系表通过在某一方表中设置“唯一外键”的方式来实现。
基于“将主键同时作为外键的方式”设计： “一对一”关系表也可以通过将某张表的主键作为外键关联另一张表的方式来实现。 

一对一关系

创建person表（“一”方的父表）
create table person(
   pid  int auto_increment primary key,
   name   varchar(20),
   sex char(2)
);

insert into person(name,sex)values('张三','男'),('李四','男');


基于“唯一外键”的设计：创建card身份证表（“一”方的子表）
create table card(
   cid  int auto_increment primary key,
   cnum   varchar(20) not null unique,
   person_id  int unique,
   foreign key(person_id) references person(pid)
);

insert into card(cnum,person_id)values('zs123',1),('ls456',2);

# 查询'张三'的身份证号
select  person.name,card.cnum    from person 
           inner join card 
           on person.pid=card.person_id and person.name='张三';

# 基于“将主键同时作为外键的方式”设计
create table card(
   cid  int  primary key,
   cnum   varchar(20) not null unique,
   foreign key(cid) references person(pid)
);

insert into card(cid,cnum)values(1,'zs123'),(2,'ls456');

练习题：

科目表：
CREATE TABLE SUBJECT(
   subid   INT  AUTO_INCREMENT PRIMARY KEY,
   subname  VARCHAR(20)  NOT NULL  
);

中间表：
CREATE TABLE student_subject(
   student_id   INT,
   subject_id   INT,
   FOREIGN KEY(student_id) REFERENCES student(stuid),
   FOREIGN KEY(subject_id) REFERENCES SUBJECT(subid),
   PRIMARY KEY(student_id,subject_id)
);


INSERT INTO student(NAME,score)VALUES('郭靖',60),('黄蓉',65),('欧阳锋',92);
INSERT INTO SUBJECT(subname)VALUES('物理'),('化学'),('数学'),('英语');
INSERT INTO student_subject(student_id,subject_id)VALUES(1,1),(1,3),(2,3),
  (2,4);
  
  问题：
学号为2的学生选择了哪些课程？

SELECT stu.name 姓名,sub.subname 课程 FROM student stu 
INNER JOIN student_subject sc ON stu.stuid=sc.student_id 
INNER JOIN SUBJECT sub ON sc.subject_id=sub.subid;

以上内容如有错误请批评指正。