Please enable JavaScript.
Coggle requires JavaScript to display documents.
数据查询2 - Coggle Diagram
数据查询2
嵌套查询
-
-
分类
带有IN谓词的子查询
例：查询数据库课程成绩大于90分的学生姓名:
SELECT Sname FROM S WHERE Sno IN ( SELECT Sno FROM SC WHERE Cname = '数据库' AND Score > 90);
带有比较运算符的子查询
例:查询年龄大于计算机系平均年龄的学生姓名和年龄:SELECT Sname,Sage FROM S WHERE Sage > (SELECT AVG(Sage) FROM S WHERE Sdept = '计算机系');
带有ANY(SOME)或ALL谓词的子查询
-
-
例:查询年龄大于计算机系任意一个学生年龄的学生姓名、年龄: SELECT Sname,Sage FROM S WHERE Sage > ALL(SELECT Sage FROM S WHERE Sdept = '计算机系');
带有EXISTS谓词的子查询
-
例:查询没有选修数据库课程的学生的学号和姓名:SELECT Sno,Sname FROM S WHERE NOT EXISTS(SELECT * FROM SC WHERE SC.Sno = S.Sno AND Cname = '数据库');
-
基于派生表的查询
-
例:查询所有选修了数据库课程的学生姓名:SELECT Sname FROM S,(SELECT Sno FROM SC WHERE Cname = '数据库') AS SC1 WHERE S.Sno = SC1.Sno;
集合查询
集合操作
UNION:并
例:查询计算机系和历史系的学生的学号和姓名:SELECT Sno,Sname FROM S WHERE Sdept = '计算机系' UNION SELECT Sno,Sname FROM S WHERE Sdept = '历史系';
INTERSECT:交
例:查询选修了数据库和操作系统的学生的学号:SELECT Sno FROM SC WHERE Cname = '数据库' INTERSECT SELECT Sno FROM SC WHERE Cname = '操作系统';
EXCEPT:差
例:查询选修了数据库但没有选修操作系统的学生的学号:SELECT Sno FROM SC WHERE Cname = '数据库' EXCEPT SELECT Sno FROM SC WHERE Cname = '操作系统';