目录

上篇在这里喔~

GROUP BY分组子句与联合查询的使用详解

联合查询步骤

1.自连接

1.查询每位同学的计算机原理和Java的成绩

2.显示所有'计算机原理'成绩比'java'成绩高的成绩信息

 2.子查询

1.单行子查询

1.查询’许仙‘的同班同学

2.多行子查询

1.查询语文或英语课程的成绩信息

IN关键字

EXISTS关键字

1.查询所有比’中文系2019级3班‘平均分高得成绩信息

1.先查询’中文系2019级3班‘的平均分

2.查询所有同学的所有成绩大于1结果的平均分

3.合并查询

1.查询id< 3 或者名为语文的课程信息

SQL查询各个关键字的执行先后顺序

上篇在这里喔~

GROUP BY分组子句与联合查询的使用详解

联合查询步骤

1.自连接

1.查询每位同学的计算机原理和Java的成绩

select stu.*, s1.score java, s2.score '计算机原理' from score s1 
join score s2 on s1.student_id = s2.student_id 
join course c1 on c1.id = s1.course_id 
join student stu on stu.id = s1.student_id
and s1.course_id = 3 and s2.course_id = 1;

2.显示所有'计算机原理'成绩比'java'成绩高的成绩信息

select stu.*, s1.score java, s2.score '计算机原理' from score s1 
join score s2 on s1.student_id = s2.student_id 
join course c1 on c1.id = s1.course_id 
join student stu on stu.id = s1.student_id
and s1.score > s2.score
and s1.course_id = 3 and s2.course_id = 1;

select stu.*, s1.score java, s2.score '计算机原理' from score s1 
join score s2 on s1.student_id = s2.student_id 
join course c1 on c1.id = s1.course_id 
join course c2 on c2.id = s2.course_id 
join student stu on stu.id = s1.student_id
and s1.score < s2.score
and c1.name = 'Java' and c2.name = '计算机原理';

 2.子查询

1.单行子查询

1.查询’许仙‘的同班同学

select * from student s where s.classes_id = 
(select classes_id from student where name = '许仙');

2.多行子查询

1.查询语文或英语课程的成绩信息

IN关键字

select stu.id, stu.name,c.name,sc.score from score sc, student stu, course c 
where sc.course_id = c.id and sc.student_id = stu.id and sc.course_id in 
(select distinct id from course c where c.name = '语文' or c.name = '英文');

EXISTS关键字

select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);

1.查询所有比’中文系2019级3班‘平均分高得成绩信息

1.先查询’中文系2019级3班‘的平均分

select avg(score) from score sc, student stu, classes cl 
where sc.student_id = stu.id and stu.classes_id = cl.id and cl.name = '中文系2019级3班';

2.查询所有同学的所有成绩大于1结果的平均分

 select stu.sn, stu.name, sc.score '分数', c.name '科目' from student stu, score sc, course c  where sc.student_id = stu.id and c.id = sc.course_id and sc.score > (select avg(score) from score sc, student stu, classes cl 
where sc.student_id = stu.id and stu.classes_id = cl.id and cl.name = '中文系2019级3班');

3.合并查询

UNION 合并后对结果去重，UNION ALL 合并后不对结果进行去重

1.查询id< 3 或者名为语文的课程信息

select * from course c where id < 7
union all
select * from course where name = '英文';

SQL查询各个关键字的执行先后顺序