子查询:
示例:查询学生“刘能”的同班同学
select id,studentName,classID from studentInfo where classID=(select classID from studentInfo where studentName='刘能');
注意:执行顺序,先执行子查询,再执行父查询;子查询和比较运算符联合使用,必须保证子查询返回的值不能多于一个
查询套查询的两种方法:
示例:查询《MySQL 数据库》考试成绩刚好不等于90分的学生名单
方法一:通过inner join实现
select S.studentName from studentInfo as S inner join exam as e on S.studentID =e.studentID inner join Subject as sub on sub.ID =e.subjectID where sub.subjectName ='MySQL数据库' and and e.exam=90;
方法二:通过子查询实现
select studentName form studentInfo where studentID =(select studentID from exam where exam=90 subjectID=(select subjectID from subject where subjectName ='MySQL数据库'));
注意:一般来说,表连接都可以用子查询替换;但是有的子查询不能用表连接实现。子连接更加灵活,适合作为查询的筛选条件,表连接更适合查看表数据。
在update、delete、insert 中使用子查询:
示例:把王小蒙的MySQL 成绩改为95分
update exam set exam =55 where studentID=(select studentID from studentInfo where studentName='王小蒙')and subjectID =(select subjectid from subject where subjectName='MySQL数据库');
把刘能的所有成绩删掉
delete from exam where studentID =(select studentID from studentInfo where studentName='刘能');