select * from students where age > (select avg(age)  from students);

select name from classes where  id in (select cls_id from students);

select * from students where (height,age) = (select max(height), max(age) from students);

 

子查询                                                                                                                       

         在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句                                                                                                           

         主查询  主要查询的对象,第一条 select 语句                                             主查询和子查询的关系           1.子查询是嵌入到主查询中 2.子查询是辅助主查询的,要么充    当条件,要么充当数据源 3.子查询是可以独立存在的语句,是一条完整的 select 语句                         

         子查询分类       标量子查询        返回的结果是一个数据(一行一列)只能用比较用算符                                                                                

                     列子查询           返回的结果是一列(一列多行)                                                                            

                     行子查询           返回的结果是一行(一行多列)                                                                            

 select * from student where gender = '男' and height >(select avg(height) from student);                  列出身高大于平均身高的数据   标量子查询:返回定值,数字、字符串                               

select * from student where cls_id in (select id from classes where id in (1,2));                         列出学生报课程id为在(1,2)范围内的数据  列子查询(一列多行)用in范围查找