SQL Server高级内容之子查询和表链接概述及使用

1．子查询概念
(1)就是在查询的where子句中的判断依据是另一个查询的结果，如此就构成了一个外部的查询和一个内部的查询，这个内部的查询就是自查询。
(2)自查询的分类
1)独立子查询
->独立单值(标量)子查询(=)

复制代码 代码如下:

Select
testID,stuID,testBase,testBeyond,testPro
from Score
where stuID=(
select stuID from Student where stuName='Kencery'
)

复制代码 代码如下:

Select
testID,stuID,testBase,testBeyond,testPro
from Score
where stuID in(
select stuID from Student where stuName='Kencery'
)

复制代码 代码如下:

use Test
go
create table testNum1
(
Num1 int
);
create table testNum2
(
Num2 int
);
insert into testNum1 values(1),(2),(3)
insert into testNum2 values(4),(5)

复制代码 代码如下:

insert into testNum1 values(4),(5),(6),(7),(8),(9),(0)

复制代码 代码如下:

select t1.num1,t2.num2 from testNum1 as t1 cross join testNum2 as t2

复制代码 代码如下:

Selects1.stuID,
s1.stuName,
s1.stuSex,
s2.testBase,
s2.testBeyond
from Student as s1
inner join Score as s2
on s1.stuID=s2.stuID
where s1.stuIsDel=0;

复制代码 代码如下:

create table tblMain
(
ID int,
name nvarchar(20),
fid int
);
create table tblOther
(
ID int,
name nvarchar(20)
)
insert into tblMain values(1,'张三',1),(2,'李四',2)
insert into tblOther values(1,'C++'),(2,'.net'),(3,'java')
select * from
tblMain as t1
inner join
tblOther as t2
on
t1.fid=t2.id

复制代码 代码如下:

select * from
tblMain as t1
right join tblOther as t2
on t1.fid=t2.id