- 查询只选修了‘C++编程’的学生
SELECT studentName
FROM stu_choose_lesson,student
WHERE stu_choose_lesson.studentNum = student.studentNum
AND stu_choose_lesson.studentNum IN
(SELECT stu_choose_lesson.studentNum
FROM stu_choose_lesson, lesson
WHERE lessonName = 'C++编程'
AND stu_choose_lesson.lessonNum = lesson.lessonNum
)
GROUP BY stu_choose_lesson.studentNum
HAVING COUNT(stu_choose_lesson.studentNum) = 1;
2.查询选修了所有课程的学生
(语义转换,即这个学生没有一门课是他没有选的)
SELECT studentName
FROM student
WHERE NOT EXISTS
(SELECT *
FROM lesson
WHERE NOT EXISTS
(SELECT *
FROM stu_choose_lesson
WHERE stu_choose_lesson.studentNum = student.studentNum
AND stu_choose_lesson.lessonNum = lesson.lessonNum
));
3.如何取所获得的表的首行或前x行
在代码段最后加入LIMIT x 即为限制表的行数为前x行,适用于取边缘