sql server 多表查询

创建数据库

if exists(select * from sysdatabases where name = 'test20141210')
 drop database test20141210
create database test20141210
on primary(
	name='test20141210',
	filename='G:\sql08\MSSQL10.MSSQLSERVER\MSSQL\DATA\test20141210.mdf',
	size=5
) 
log on(
	name='test20141210log',
	filename='G:\sql08\MSSQL10.MSSQLSERVER\MSSQL\DATA\test20141210log.ldf',
	size=5
)

创建表

use test20141210
create table student
(
	sname varchar(10) not null, --学生姓名
	scode int not null --学生编号
)
create table scode(
	sutdentId int not null, -- 学生编号
	courseId int not null, --课程编号
	score int not null --分数
)
create table stuinfo(
	stuId int not null,
	stuadd varchar(20) not null
)

插入数据

insert into student(sname,scode)
select 'czk1',1 union
select 'czk2',2 union
select 'czk3',3 union
select 'czk4',4 

insert into scode(sutdentId,courseId,score)
select 1,1,97 union
select 1,2,76 union
select 2,1,27 union
select 2,2,45 union
select 3,1,67 union
select 3,2,26 union
select 4,1,23 union
select 4,2,64 

insert into stuinfo(stuId,stuadd)
select 1,'a区' union
select 2,'b区' union
select 3,'c区' union
select 4,'d区' 

内联查询

<pre name="code" class="sql">/*如果只查询两个表可以不用inner join*/
select stu.sname,sc.courseid,sc.score from student stu,scode sc

/*用inner join 查询两张表*/
select stu.sname,sc.courseid,sc.score from student stu inner join scode sc on stu.scode=sc.sutdentid

/*查询三张表*/
select stu.sname,sc.score,si.stuadd from student stu 
inner join scode sc on stu.scode=sc.sutdentid
inner join stuinfo si on sc.sutdentid=si.stuid

/*也可以inner join 写一起，注意条件要从后往前写*/
select stu.sname,sc.score,si.stuadd from student stu inner join scode sc inner join stuinfo si
on si.stuid=sc.sutdentid
on sc.sutdentid=stu.scode

外联查询

/*左外连接*/
select stu.sname,sc.score,si.stuadd from student stu left outer join scode sc left join stuinfo si
on si.stu=sc.sutdentid 
on sc.sutdentid=stu.scode 

/*也可以这样查询*/
select student.*,scode.* from student left outer join scode
on student.scode=scode.sutdentid


/*右外联结*/
select student.*,scode.* from student right outer join scode
on student.scode=scode.sutdentid

/*全联结*/
select student.*,scode.* from student full outer join scode 
on student.scode=scode.sutdentid

/*交叉联结,基本不用*/
select student.*,scode.* from student cross join scode