- select * from student inner join sc on student.sno = sc.sno
- set statistics io on
- select student.sno,student.sname,sc.cno,sc.grade from student join sc on student.sno=sc.sno where student.sdept='计算机'
- --以下这两个sql实现的功能是一样的,只是写的方法不一样
- select sname,cno,grade from student,sc where sc.sno = student.sno and student.sdept='计算机'
- select student.sname,sc.cno,sc.grade from student join sc on student.sno = sc.sno where student.sdept='计算机'
- select cno,grade from sc where sc.sno<>990001
- --以下三条sql语句第一句开销最小,说明效率最高,后面两条开销一样
- select grade from sc where sno=(select sno from student where sname='张忠和') and cno = (select cno from course where cname='软件工程')
- --T-SQL写法
- select student.sname,student.sno,sc.grade,sc.cno,course.cname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and student.sname='张忠和' and course.cname='软件工程'
- --ANSI SQL92写法
- select student.sname,student.sno,sc.grade,sc.cno,course.cname from student
- join sc on student.sno=sc.sno --这里要注意了join与join之间连接的时候没有任何的连接符也没有and之类的
- join course on sc.cno=course.cno
- where student.sname='张忠和' and course.cname='软件工程'
- select * into student1 from student
- select * into student2 from student union select * from student1
- select * from student where sno in (select sno from sc where cno='001')
- select * from student where exists (select * from sc where sno=student.sno and cno='001')
- select * from student a where exists (select * from student b where a.sdept=b.sdept and b.sname='张三')
- select *from student where sno not in(select sno from sc where cno=001)
- select * from student where not exists (select sno from sc where student.sno=sc.sno and cno='001')
转载于:https://blog.51cto.com/sucre/420273