/*
子查询
*/
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`=`课程编号`);