【2017-3-13】SQL Server 表连接

select * from Student

select * from Course
select * from Score
select * from Teacher

 

基础的表连接    --把多个表的多列显示在一个结果集里面 where之后的内容是连接条件
select * from Student,Score where Student.Sno = Score.Sno

 

 

 

两个表连接    --注意：要查询的列如果有重复的，要标明是哪个表的这一列
select Student.Sno,Sname,Cno,Degree from Student,Score where Student.Sno = Score.Sno

另一种  jion on       --inner join / left join --以左边的表为主 / right join --以右边的表为主
select Student.Sno,Sname,Cno,Degree from Student join Score on Student.Sno = Score.Sno

另一种 子查询方法        --返回的Sname是调用的Student表中的Sname来映射到结果集，所以 无列名
select Sno,(select Sname from Student where Student.Sno = Score.Sno),Cno,Degree from Score

 

 

多表连接      --必须有一个表来进行中转，做中介
select Student.Sno,Sname,Score.Cno,Degree,Cname
from Student,Score,Course
where Student.Sno = Score.Sno
and Score.Cno = Course.Cno

 

多表纵连接      --注意：多个表的列必须对应，连接后的列名是第一个表的列名
select Sno,Sname,Ssex,Sbirthday from Student
union
select Tno,Tname,Tsex,Tbirthday from Teacher