#1、自查询返回值

#2、where 子句中：
#单值 返回低于平均成绩的信息：
select avg(score) from choose;
select student_no,course_no,score from choose
	where score < (select avg(score) from choose);

#多值 返回没有开设课程的老师信息；
insert into teacher values('005','林老师','1110000005');
select t.*
	from teacher t left join course c 
	on t.teacher_no = c.teacher_no
	where c.teacher_no is null;

select * from teacher
	where teacher_no not in (select teacher_no from course);

select * from classes
	where class_no not in (select  class_no from student where class_no is not null);
	
#2、from 子句中：
select 语句可以看成一个是虚拟的内存表；可以在此基础上进一步筛选；
select table_schema, count(*) cnt 
	from information_schema.tables
	group by table_schema
	having cnt > 50;

select * from
	(select table_schema,count(*) cnt from information_schema.tables
		group by table_schema) db
	where cnt > 50;
	
select student_no,avg(score) avg_score from choose
	group by student_no;

select c.student_no,c.course_no,c.score,a.avg_score 
	from (select student_no,avg(score) avg_score from choose
	group by student_no) a, choose c
	where c.student_no = a.student_no
	and c.score < a.avg_score;