《LinuxC13.DMLSQL92子查询和复合函数》由会员分享，可在线阅读，更多相关《LinuxC13.DMLSQL92子查询和复合函数（12页珍藏版）》请在金锄头文库上搜索。
1、第十三章 子查询和复合查询,本章目标,掌握多表关联查询和子查询语句 掌握虚拟表的概念 了解集合查询操作union等 掌握复合函数以及复合查询,子查询,嵌套子查询 in子查询 exists子查询,子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。,Select From Table,WHERE ,父查询,Select From WHERE = ,操作符,子查询,嵌套子查询,查询有成绩的学生信息 select * from tab_student where stu_id in (select stu_id from tab_score) 查询1课程有成绩的学生信息 select
2、 * from t_student where sno in (t_select sno from t_score where cno=1),in子查询,判断是否有行存在 如果有行存在，语句返回true；否则false 查询有成绩的学生列表 select * from t_student a where exists (select sno from t_score b where a.sno=b.sno) 查询没有成绩的学生列表 select * from t_student a where not exists (select sno from t_score b where a.sno=
3、b.sno),Exists语句,SQL虚拟表,在SQL语句中，可以将任何查询的结果，认为是一张虚拟表，并给出表名；并可以参与关系运算 例子：查询年龄在25以下的男性学生 select * from (select * from tab_student where ssex=男) t1 where sage25 其中： t1为虚拟表,集合操作-并union,并的前提 表或虚拟表的列个数相同 例子 (select * from tab_student where ssex=女) t1 union (select * from tab_student where ssex=男) t2,SUM,AVG,COUNT,聚合函数一,MAX,MIN,聚合函数二,Select sno,ssubject, sscore From t_score,SELECT sno,SUM(sscore) AS total_score FROM T_score GROUP BY sno,GROUP BY 子句,SELECT sno, SUM(sscore) AS total_score FROM t_score GROUP BY sno HAVING SUM(sscore)=150,Select sno,ssubject, sscore From t_score,GROUP BY 子句,
