SQL查询五之内连接 

  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

 

posted @ 2018-11-08 15:50  冬夜的火  阅读(344)  评论(0)    收藏  举报