-- 查询木子同学的班级号(只有一个)
select classid from student where studentname='木子';
-- 查询班级号为‘2008002’的学生的信息
select * from student where classid='2008002';
-- 查询班级号为(查询木子同学的班级号)的学生的信息,即和木子同班的学生的信息
select * from student
where classid=(select classid from student where studentname='木子');
-- 查询选课表中所有学生的学号
select distinct studentid from grade;
-- 查询学号为(0711001,0711032,0711045,0711069,0712001,0714001,0714005)的学生的信息,注意多个值要用in,而不是等于。
select * from student
where studentid in (0711001,0711032,0711045,0711069,0712001,0714001,0714005);
-- 嵌套查询所有选课同学的学生的信息
select * from student
where studentid in (select distinct studentid from grade);
-- 查询所有选修了'100000'这门程的学生的其他科目的成绩
select * from grade where studentID in (select distinct studentid from grade where courseID='100000')
and courseID<>'100000';
-- 查询和张然同学一个民族的同学的信息
select * from student where nation=(select nation from student where studentName='张然');
-- 查询和张然同学来自同一个城市(address)的同学的信息
select * from student where address=(select address from student where studentName='张然');
-- 查询和张然同学来自同一个系部的同学的信息
select * from student inner join class on student.classID=class.classID
inner join speciality on class.specialityID=speciality.specialityID
where departmentID=(
select departmentID from student inner join class on student.classID=class.classID
inner join speciality on class.specialityID=speciality.specialityID
where studentName='张然'
)
-- 删除所有男生的成绩信息
delete grade where studentID in (select distinct studentID from student where sex='男');
-- 把所有女生的成绩加10分
update grade set score+=10
where studentID in (select distinct studentID from student where sex='女');
select * from grade
select * from student
inner join grade on student.studentid=grade.studentid
where courseid='100000'
select * from student
where studentid in (select studentid from grade where courseid='100000')
select * from student
where studentid in (select studentid from grade)
--等价于
select * from student
where exists (select * from grade where grade.studentid=student.studentid)
select * from student
where studentid in (select studentid from grade where courseid='100000')
--等价于
select * from student
where exists (select * from grade where grade.studentid=student.studentid and courseid='100000' )
select * from student
where studentid not in (select studentid from grade where courseid='100000')
select * from student
where not exists (select * from grade where grade.studentid=student.studentid and courseid='100000' )
-- 用两种方法查询被'0711032'同学选修的课程的课程信息
select * from course
where courseID in (select courseID from grade where studentid='0711032')
select * from course
where exists (select * from grade where studentid='0711032' and grade.courseID=course.courseID)
-- 用两种方法查询没有被'0711032'同学选修的课程的课程信息
where courseID not in (select courseID from grade where studentid='0711032')
select * from course
where not exists (select * from grade where studentid='0711032' and grade.courseID=course.courseID);
-- 用两种方法查询没有学生的班级信息
select * from class where class.classID not in (select classID from student)
select * from class
where not exists (select classID from student where class.classID=student.classID)