6-Mysql子查询，多表连接（内连接，外连接，交叉连接）

/*
    子查询
    
*/
select * from 学生表;
-- 查询高技2班的学生信息
select * from 班级表;
-- 1.子查询当做查询条件的一部分
select * from 学生表 where 班级ID in (select 班级ID from 班级表 where 班级名称='高技2班')
select * from 学生表 where 班级ID = (select 班级ID from 班级表 where 班级名称='高技2班')
-- 查询参加了HTML课程考试的学生信息
select * from 学生表 where 学号 in (select 学号 from 成绩表 where 课程编号=1)
-- 报错：子查询返回多个结果集，不能使用等于号
select * from 学生表 where 学号 = (select 学号 from 成绩表 where 课程编号=1)
/*
    使用in:如果子查询返回的结果包含多条记录
    使用=:子查询的返回结果必须是一条记录
*/
-- 2.子查询当做查询语句的一个字段
-- 显示所有学生的信息及考试成绩的总分

select s.*,(select sum(考试成绩) from 成绩表 where 学号 = s.学号 ) 总分 from 学生表 s;
-- 学生所有学生的信息及各科成绩
select * from 课程表;
select * from 成绩表;
-- ifnull函数，可以把空值改为你想显示的内容
select s.*,
ifnull((select 考试成绩 from 成绩表 where 课程编号=1 and 学号 = s.学号 ),0) HTML,
ifnull((select 考试成绩 from 成绩表 where 课程编号=2 and 学号 = s.学号 ),0) MySQL, 
ifnull((select 考试成绩 from 成绩表 where 课程编号=3 and 学号 = s.学号 ),0) JAVA,
ifnull((select 考试成绩 from 成绩表 where 课程编号=4 and 学号 = s.学号 ),0) JAVASCRIPT, 
ifnull((select 考试成绩 from 成绩表 where 课程编号=5 and 学号 = s.学号 ),0) C语言,
ifnull((select 考试成绩 from 成绩表 where 课程编号=7 and 学号 = s.学号 ),'未考试') JQuery 
from 学生表 s;

-- 删除所有未参加考试的学生信息
select * from 学生表;
delete from 学生表 where 学号 not in (select distinct 学号 from 成绩表)

select * from 课程表1;
-- 新增数据时，使用子查询
insert into 课程表1(课程ID,课程名称,学分) select * from 课程表;

-- 查询大于年龄最小的所有学生信息
select * from 学生表 where 年龄> any (
    select 年龄 from 学生表 

)
select * from 学生表 where 年龄> some (
    select 年龄 from 学生表 

)
-- 返回年龄大于所有女生最大年龄的学生信息
select * from 学生表 where 年龄> all (
    select 年龄 from 学生表  where 性别='女'

)
-- 查询所有参加了考试的学生信息
/*
    exists 表达式，返回的结果是真|假，返回真就显示数据，返回假就不显示数据
    exists 后面没有字段名称
*/
select * from 学生表 s where exists (select * from 成绩表 c where s.学号=c.学号);

insert into 学生表 values(5,'小小',23,1,'女','362135198808081184');
-- 查询所有未参加了考试的学生信息

select * from 学生表 s where not exists (select * from 成绩表 c where s.学号=c.学号);

/*
    多表连接：
        1.内连接（INNER JOIN        on）
        2.外连接
            left join  on 
            right join on 
            full join  on 
        3.交叉连接
            cross join    

*/
/* 内连接：能关联上的数据都要显示，关联不上的数据不显示
        如果用*号，则显示2个表所有字段,如果不需要学生所有字段，则所用字段列表,
        如果需要显示的字段存在同名的情况，则必须加上别名，否则可以省略
        特别说明：是否建立连接与外键无关
*/
-- 显示所有学生信息及班级名称
select * from 班级表;
select * from 学生表;

select * from 学生表 s INNER JOIN 班级表 c on s.班级ID = c.班级ID

select 学号,姓名,年龄,性别,身份证号,c.班级ID,班级名称 from 学生表 s INNER JOIN 班级表 c on s.班级ID = c.班级ID

/*
左外连接 left join 或者 left outer join (outer可以省略)      on 

    以左边的表作为基准表，基准表中符合条件的记录全部会显示，如果关联不上的数据，则显示为null
    
右外连接 right join 或者 right outer join (outer可以省略)      on 

    以右边的表作为基准表，基准表中符合条件的记录全部会显示，如果关联不上的数据，则显示为null

*/
select * from 学生表;
-- 新增一个学生到不存在的班级中
insert into 学生表 values(7,'王老五',34,11,'男','362135198808081186')
-- 左外连接
select 学号,姓名,年龄,性别,身份证号,c.班级ID,班级名称 from 学生表 s LEFT JOIN 班级表 c on s.班级ID = c.班级ID
-- 右外连接
select 学号,姓名,年龄,性别,身份证号,c.班级ID,班级名称 from 学生表 s right JOIN 班级表 c on s.班级ID = c.班级ID
-- 完全连接:
-- select 学号,姓名,年龄,性别,身份证号,c.班级ID,班级名称 from 学生表 s full JOIN 班级表 c on s.班级ID = c.班级ID;

-- cross join :交叉连接(笛卡尔积)：排列组合

select * from 学生表;
select * from 课程表;
select * from 成绩表;
-- 查询已参加考试的学生信息，科目及成绩
select s.*,c.`课程ID`,c.课程名称,a.`考试成绩` from 学生表 s cross join 课程表 c
INNER JOIN 成绩表 a on s.学号=a.学号 and c.课程ID=a.`课程编号`;

-- 缺考
select * from 成绩表;
select s.*,c.`课程ID`,c.课程名称 from 学生表 s cross join 课程表 c
where not exists (select * from 成绩表 where s.学号=学号 and  c.`课程ID`=`课程编号`);
-- 已考
select * from 成绩表;
select s.*,c.`课程ID`,c.课程名称 from 学生表 s cross join 课程表 c
where exists (select * from 成绩表 where s.学号=学号 and  c.`课程ID`=`课程编号`);

本人从事软件项目开发20多年，2005年开始从事Java工程师系列课程的教学工作，录制50多门精品视频课程，包含java基础，jspweb开发，SSH,SSM，SpringBoot，SpringCloud,人工智能，在线支付等众多商业项目，每门课程都包含有项目实战，上课PPT，及完整的源代码下载，有兴趣的朋友可以看看我的在线课堂

讲师课堂链接：https://edu.csdn.net/lecturer/893