【SQL】子查询

-- 查询木子同学的班级号（只有一个）

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)