子查询
students(学生表),scores(成绩表)、courses(课程表)
子查询:在select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句,子查询分(标量子查询、列子查询、行子查询、表子查询)
标量子查询: 子查询返回的结果是一个数据(一行一列)
查询班级学生的平均年龄
select avg(age) from students
查询大于平均年龄的学生 ,上一句结果8
select * from students where age>8
select * from students where age>(select avg(age) from students)(这就是标量子查询)
列子查询: 返回的结果是一列(一列多行)
下面方法是子查询
学生表中查询18岁的学生的学号
select studentNo from students where age=18
成绩表中根据学号查询成绩
select * from scores where studentNo in ('002','006')
select * from scores where studentNo in (select studentNo from students where age=18)
行子查询: 返回的结果是一行(一行多列)
查询男生中年龄最大的学生信息
1、select * from students where sex='男' and age=(select max(age) from students)
2、select * from students where (sex,age) = (select sex,age from students where sex='男' order by age desc limit 1)
3、select * from students
where age=(select max(age) from students where sex='男')
表级子查询: 返回的结果是多行多列(把某一个结果表当成一个原始表来应用,实际上缩小了比对时间,本来要比对8条数据现在只需要比对2条)
查询语文和数学的课程成绩
select * from scores s
inner join (select * from courses where name in ('语文','数学')) c
on s.courseNo = c.courseNo
内连接:select * from courses c
inner join scores s on c.courseNo=s.courseNo
where c.name='语文' or c.name='数学'
ps:有时子查询不一定比条件查询、内连接方便,实际上只是在某些情况下子查询比较便捷而已