sql sever 查询语句
参考https://blog.csdn.net/dacainiao007/article/details/8129637
子查询:嵌套在其他查询中的查询称之。
子查询又称内部,而包含子查询的语句称之外部查询(又称主查询)。
所有的子查询可以分为两类,即相关子查询和非相关子查询
1. 非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
2. 相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。
故非相关子查询比相关子查询效率高
.子查询和连接查询的最重要区别是:
子查询不需要两个表有关联字段,而连接查询必须有字段关联(所谓的主外键关系)
查询被t1和t3老师教了的课程的课程名//相关子查询 select cn from c where cno in(select cno from tc where tno='t1' and cno in(select cno from tc where tno='t3')) 查询选了c1课程的学生姓名//相关子查询 select sn from s where sno in (select sno from sc where cno='c1' and sc.sno=s.sno) 查询不学数据库的学生学号//相关子查询+连接子查询 select sno from s where sno not in (select sno from sc,c where cn='数据库' and sc.cno=c.cno ) 查询赵亦选的课程名 select cn from c where cno in (select cno from sc where sno in (select sno from s where sn='赵亦'))//子查询 select cn from c where '赵亦' in (select sn from s,sc where s.sno=sc.sno and c.cno=cno) //连接子查询+相关子查询 select cn from c where '赵亦' in (select sn from s where sno in (select sno from sc where cno=c.cno)) //相关子查询 查询被所以学生选修了课程的课程号,课程名 使用exists select cno ,cn from c where not exists (select * from s where not exists (select * from sc where sno=s.sno and c.cno=cno)) 查询被t1,t3和t4老师教了的课程的课程名 使用exists 思路:该课程没有一个老师没教 select cn from c where not exists (select * from (select * from t where tno in ('t1','t3','t4')) x
where not exists (select * from tc where tno=x.tno and cno=c.cno))
如果你够坚强够勇敢,你就能驾驭他们