MYSQL数据库主键和外键，内连接和外连接，关联子查询

友情提醒:
先看文章目录，大致了解知识点结构，直接点击文章目录可以跳转到文章指定位置。

第一章、MYSQL数据库多表主键和外键

1.1）外键介绍（FOREIGN KEY）

①外键是表格中的特殊字段但不是主键，外键用来建立主表与从表的关联关系，添加了外键的表我们称为从表，被引用主键的表我们称为主表。默认情况下主表和从表是相互约束的关系。
②外键约束的作用：避免多表中垃圾数据的产生，约束两个表中数据的一致性和完整性。
图片来自：http://cd.itheima.com/news/20220810/170702.html

③一个表可以有一个或多个外键，必须为主表定义主键，主表中的主键不能包含空值，但是外键可以是空值，如果不是空值，则每个外键的值必须等于主表中主键的某个值。
举例：每个外键的值必须等于主表中主键的某个值
例如：主键的id字段数据只有 1 ，这时候从表的外键字段也要是1，如果想添加tid=2会报错。

tid不能值=2，因为外键的值必须等于主表中主键的某个值：

④父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
⑤写SQL语句时，需要在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
⑥外键中列的数目和数据类型必须和主表的主键中的相同。也就是：如果父表的主键是个复合主键有三个列I’d name general三个列联合作为主键，当它被reference的时候，那这个外键也必须是三个列

1.2）外键约束作用

第一种：阻止执行
①从表插入新行，其外键值不是主表的主键值便阻止插入；
②从表修改外键值，新值不是主表的主键值便阻止修改；
③主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
④主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

第二种：级联操作
在外键约束中，主表和从表是相互约束，如果想通过主表去影响从表的数据，需要使用级联操作。

主表删除行，连带从表的相关行一起删除；

#级联删除：外键约束 on delete cascade;
alter table emp add foreign key(did) references dept(id) on update cascade ;

主表修改主键值，连带从表相关行的外键值一起修改。

#级联更新：外键约束 on update cascade;
alter table emp add foreign key(did) references dept(id) on delete cascade；

1.3）三种情况下添加外键约束

①一对一关系
在任何一方添加外键，引用到另外一方的主键。
一般一对一关系很少见，遇到一对一关系的表最好是合并表。

-- 新添加一列字段：
alter table 从表 add 新列 int;
-- 将新列设为外键援引主表的主键
alter table 从表 add foreign key(外键) references 主表(主键);

②一对多关系
在多的一方添加外键，引用到一的一方的主键。

-- 表已经创建完成：alter table 从表 add foreign key(外键) references 主表(主键);
alter table 从表 add foreign key(外键) references 主表(主键);

--创建表时：在从表中
 foreign key(did) references dept(id);

举例：

use money;
create table b_pro_evaluate(
    eid int(50) primary key auto_increment,# 主键约束 自增长
	pid int(30) ,# 产品id，外键约束
    star INT(10) not null default 5, # 点赞字段，最大指为5
    eval VARCHAR(100),# 评论字段
		foreign key (pid) references b_loan_info(id)#添加外键约束
);

③多对多关系
创建第三张表，在第三张表中添加两个外键，分别援引另外两个表的主键。
已有coder，project表，创建第三张表分别援引这两个表的主键。

-- 创建第三张表分别指令另外两个表的主键。
create table coder_project(
	cid int,
	pid int,
	foreign key(cid) references coder(id),
	foreign key(pid) references project(id)
);

1.4）删除和查看外键约束

①在navicat中查看外键约束：设计表–>外键

②查看外键名称

③查询外键约束名语句:

show create table 数据表名 ；

如图：

④删除外键约束使用下面的语句

-- 语法：alter table emp drop foreign key 外键约束名;
alter table b_pro_evaluate drop foreign key b_pro_evaluate_ibfk_1;

第二章、MYSQL数据库多表查询

2.1）内连接，外连接等

看这个大佬写的就好了，他写的太好了我就不献丑了

2.2）关联子查询

SQL关联子查询
子查询：把一个SQL语句的查询结果作为另外一个SQL语句查询的参数存在。

关联子查询—>查询价格最贵的水果名称 — >水果A表，价格B表。
分析：

-- 建立A表水果表
create table A(
    A_id int primary key auto_increment,
    A_name varchar(20) not null
);
insert into A values(1,'苹果');
insert into A values(2,'橘子');
insert into A values(3,'香蕉');
-- 建立B表水果价格表
create table B(
    B_id int primary key auto_increment,
    B_price double
);
insert into B values(1,2.3);
insert into B values(2,3.5);
insert into B values(4,null);
-------------------------------------------------------------------------
-- 1.B表保存的价格，在B表中查询出最高价格。b_price
select max(b_price) from b;
-- 2.通过最高的价格，找对应的id。 b_id
select b_id from b where b_price = (select max(b_price) from b);
--3.通过第二步查询到的最贵水果价格的id，到水果表中找对应的水果名称
select * from a where a_id = (select b_id from b where b_price = (select max(b_price) from b));

多表查询练习材料

-- 建表素材
create table teacher(
    id int not null primary key auto_increment,
    tname varchar(20) not null unique
);
​
create table student(
    id int not null primary key auto_increment,
    sname varchar(20) not null unique,
    addr varchar(40) not null,
    age int
);
​
create table course(
    id int not null primary key auto_increment,
    cname varchar(20) not null unique,
    t_id int not null,
    foreign key (t_id) references teacher(id)
);
​
create table student_course(
    s_id int not null,
    c_id int not null,
    score double,
    foreign key (s_id) references student(id),
    foreign key (c_id) references course(id)
);
​
insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');
​
insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小孙','上海',18);
insert into student values(null,'小吴','北京',20);
insert into student values(null,'小郑','广州',21);
insert into student values(null,'小冯','深圳',22);
insert into student values(null,'小楚','深圳',21);
​
insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);
​
insert into student_course values(1,1,80);
insert into student_course values(1,2,90);
insert into student_course values(1,3,85);
insert into student_course values(1,4,78);
insert into student_course values(2,2,53);
insert into student_course values(2,3,77);
insert into student_course values(2,4,80);
insert into student_course values(3,1,72);
insert into student_course values(3,2,70);
insert into student_course values(3,4,80);
insert into student_course values(3,5,65);
insert into student_course values(3,6,75);
insert into student_course values(4,2,80);
insert into student_course values(4,3,90);
insert into student_course values(4,4,70);
insert into student_course values(4,6,95);
insert into student_course values(5,1,60);
insert into student_course values(5,2,70);
insert into student_course values(5,5,80);
insert into student_course values(5,6,69);
insert into student_course values(6,1,76);
insert into student_course values(6,2,88);
insert into student_course values(6,3,87);
insert into student_course values(7,4,80);
insert into student_course values(8,2,71);
insert into student_course values(8,3,58);
insert into student_course values(8,5,68);
insert into student_course values(9,2,88);
insert into student_course values(10,1,77);
insert into student_course values(10,2,76);
insert into student_course values(10,3,80);
insert into student_course values(10,4,85);
insert into student_course values(10,5,83);
insert into student_course values(11,3,80);
insert into student_course values(12,4,99);
insert into student_course values(13,5,74);

练习以及答案

#1. 查询平均成绩大于70分的同学的学号和平均成绩
    # 成绩和学号在student_course表中 
    select * from student_course;
    # 查询平均成绩
    select avg(score) from student_course;
    # 学号分组查询学生的平均成绩
    select avg(score) from student_course group by s_id having avg(score) > 70;
    # 查询学号和平均成绩大于70分的学生信息
    select s_id , student.* , avg(score) 
    from student_course,student 
    where s_id = student.id group by s_id having avg(score) > 70 
    order by s_id;
    
#2. 查询所有学生的学号，姓名，选课数，总成绩
    # 学号，姓名在student表中  学号 选课数 总成绩 在student_course表中 
    
    # 查询 学号 选课数 总成绩 
    select s_id,count(c_id),sum(score) 
    from student_course group by s_id;
    # 将上述的查询结果 当做一个临时 和 student表联合查询
    select sname ,  temp.* 
    from student , (select s_id,count(c_id),sum(score) from 
    student_course group by s_id) as temp 
    where temp.s_id = student.id order by s_id;
        
    # 优化查询结果
    select temp.s_id , sname ,  temp.count , temp.sum  
    from student , (select s_id,count(c_id) as count,sum(score) as sum 
    from student_course group by s_id) as temp 
    where temp.s_id = student.id order by s_id;
​
#3.查询没有学过关羽老师课程的同学的学号和姓名
    # 通过关羽老师找到对应的id
    select id from teacher where tname = '关羽'; 
    # 通过关羽老师的id，找到关羽老师课程的id
    select id from course 
    where t_id = (select id from teacher where tname = '关羽');
    # 通过课程id找到学过对应课程学的id
    select s_id from student_course 
    where c_id in(select id from course 
    where t_id = (select id from teacher where tname = '关羽')); 
    # 找到所有学的id，从中把学习过的学生的id除去
    select s_id from student_course 
    where s_id not in(select s_id from student_course 
    where c_id in(select id from course 
    where t_id = (select id from teacher where tname = '关羽')));
    # 综合查出最后的结果
    select id,sname from student 
    where id in(select s_id from student_course 
    where s_id not in(select s_id from student_course 
    where c_id in(select id from course 
    where t_id = (select id from teacher 
    where tname = '关羽')))) order by id;
    
#4.查询学过赵云老师课程的同学的学号和姓名
#5.查询没有学习三门课程以上的同学学号和姓名
    # 学习的课程在student_course  姓名在student表中
    # 在student_course表中找到低于3门课程的学生的id
    select s_id from student_course group by s_id having count(c_id) < 3;
    # 通过上述查询的id结果，到student表中查询学生姓名
    select * from student 
    where id in(select s_id from student_course group by s_id 
    having count(c_id) < 3);
    
    # 两张表联合查询
    select s_id,sname from student_course,student 
    where student_course.s_id = student.id group by s_id 
    having count(c_id) < 3;
    
    
#6.查询各科成绩的最高分和最低分。  
    select c_id,max(score),min(score) 
    from student_course group by c_id;
    # 查询课程名称
    select c_id, cname, max(score),min(score) 
    from student_course,course 
    where student_course.c_id = course.id group by c_id 
    order by c_id;
    
#7. 查询学生信息和平均成绩 
    select sname,avg(score) from student,student_course 
    where student.id = student_course.s_id group by s_id;
​
#8. 查询上海和北京学生数量
    select addr,count(addr) from student 
    where addr in('北京','上海') group by addr;
​
#9. 查询不及格的学生信息和课程信息
    # 查询学生和课程的id
    select s_id,c_id from student_course where score < 60;
    # 通过学生的id和课程的id找到学生和课程的信息
    select * from student,course,(select s_id,c_id from student_course 
    where score < 60) as temp
    where student.id = temp.s_id and course.id = temp.c_id;
#10. 统计每门课程的学生选修人数(超过4人的进行统计)
    select c_id ,count(s_id) from student_course group by c_id 
    having count(s_id) > 4;