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
