1 use sqlschool 2 go 3 --内连接(普通连接查询) 4 5 --Sql92 6 7 drop table sc 8 go 9 create table sc 10 ( 11 stuId char(8), 12 cName varchar(20), 13 cGrade numeric(3,1) check(cGrade >=0 And cGrade <= 100) 14 ) 15 go 16 17 18 insert into sc values('20060201','英语',80.2) 19 insert into sc values('20060201','数据库原理',70.0) 20 insert into sc values('20060201','算法设计与分析',92.4) 21 insert into sc values('20060202','英语',81.9) 22 insert into sc values('20060202','算法设计与分析',85.2) 23 insert into sc values('20060203','多媒体技术',68.1) 24 insert into sc values(null,'哲学',null) 25 insert into sc values(null,'生命科学之胃疼',null) 26 go 27 28 select * from student 29 select * from sc 30 31 --简单的多表联合查询,选了课的学生和他选的课 32 select s.*, cName from student s, sc 33 where s.stuId = sc.stuId 34 35 --SQL92 36 --from 谁,谁就是左表 37 select s.*, cName,cGrade from sc 38 --inner join:内联接 39 inner join student s 40 on s.stuId = sc.stuId 41 42 43 --外连接查询 44 select * from sc 45 46 --查询所有学生的基本信息和选课情况 47 select s.*, cName,cGrade 48 from student s 49 left join sc on s.stuId = sc.stuId 50 51 --下面的写法和上面的是等价的 52 select s.*, cName,cGrade 53 from sc right join student s 54 on sc.stuId = s.stuId 55 56 --查询选了课的学生和没人选的课的信息 57 58 select s.*, cName from student s right join sc on sc.stuId = s.stuId 59 60 select s.*, cName from sc left join student s 61 on s.stuId = sc.stuId 62 63 64 --查询李好和赵志远所在的专业所有学生 65 66 67 68 select student.* from student 69 where stuSpeciality in 70 ( 71 select stuSpeciality from student 72 where stuName = '李好' or stuName = '赵志远' 73 ) 74 and stuName <> '李好' and stuName <> '赵志远' 75 76 --查询平均成绩比王丫低的学生的信息 77 78 select * from student 79 where stuAvgrade < 80 ( 81 select top 1 stuAvgrade from student where stuName = '王丫' 82 ) 83 84 --查询所有选了课的学生的信息(相关子查询) 85 select * from sc 86 select * from student s 87 where exists --存在 88 ( 89 select * from sc 90 where sc.stuId = s.stuId 91 ) 92 93 94 --对比内联接查询我们可以看到相关子查询的不同之处 95 select s.*, cName from student s 96 inner join sc on s.stuId = sc.stuId 97 98 select * from student s 99 100 101 --查询的集合运算(并,交,差运算) 102 103 --使用union查询专业为网络工程或者平均成绩在良好(>=80)以上的学生的信息 104 105 select * from student where stuAvgrade >= 80 or stuSpeciality = '网络工程' 106 107 108 select * from student where stuAvgrade >= 80 109 union --把两个结果集联合成一个结果集,要求两个结果集的列数相同 110 select * from student where stuSpeciality = '网络工程' 111 112 --胡乱一粘 113 select stuId, stuName from student 114 union 115 select stuId,cName from sc 116 117 118 --使用Except查询专业为网络工程而且平均成绩在良好(<=80)以下的学生的信息 119 120 select * from student where stuAvgrade <= 80 and stuSpeciality = '网络工程' 121 122 select * from student where stuSpeciality = '网络工程' 123 Except--排除 124 select * from student where stuAvgrade <= 80 125 126 select * from student