一、
1、内连接:两个我表中相匹配的字段
2、外连接:(1)左外连接(left outer join)
select
sId,
sName,
sAge,
Score.*
from Student
left outer join Score
on Score.studentId=Student.sId
select * from
(select
sId,
sName,
sAge,
Score.*
from Student
left outer join Score
on Score.studentId=Student.sId) as Tb1
where ScoreId is null
(2)右外连接(right outer join)
select
sId,
sName,
sAge,
Score.*
from Student
right outer join Score
on Score.studentId=Student.sId
左外连接和右外连接都是分两步查询结果的,第一找到匹配数据;第二填充不匹配的数据为null(注意,是有先后顺序的)
--cross Join交叉连接
select
*
from Student,Score
--自连接
select
*
from Student
inner join
(select sName,sAge,sId from Student
where sAge>10
)as TS1
on Student.sId=TS1.sId
--1:查询所有英语及格的学生姓名、年龄及成绩
select * from Studnet
select * from Score
select
sName,
sAge,
english
from Student
inner join Score
on Score.studentId=Student.sId
where english>=60
--2.查询所有参加考试的(english分数不为null)学
select * from Studnet
select * from Score
select
sName,
sAge,
english,
math
from Student
inner join Score
on Student.sId=Score.studentId
where english is not null
--3.查询所有学生(参加考试和未参加考试)的学生姓名、年龄、成绩,如果报考了,但是没有参加考试显示缺考,如果小于english、math小于60分显示不及格,如果没有报考显示没有报考(添加两列‘是否报考’、‘是否合格’)
select * from Studnet
select * from Score
select
sName,
sAge,
case
when english is null then '缺考'
else convert(varchar(50),english)
end as english,
math=case
when math is null then '缺考'
else convert(varchar(50),math)
end ,
是否报考=case
when scoreId is null then '未报考'
else '以报考'
end ,
是否合格=case
when english>=60 or math>=60 then '合格'
else '不合格'
from Student
left join Score
on Score.studentId=Student.sId
--面试题,表A中有过百条记录,B中有过千条记录,如何查询优化
使用‘临时表’把AB中满足条件的记录取出来放到相应的临时表中,再在临时表中进行查询
1.临时表放在内存中,查询速度快
2.临时表中数据量小
局部临时表:create table #bName(列信息)
全局临时表:create table ##bName(列信息)
create table #MyStudents
(
sName nvarchar(50),
sAge int
)
inser into #MyStudents
select FName,FAge from MyStudents
select * from #MyStudents
delete from #MyStudents
where sName in ('康凯','李昂','李琛')
select * from ##MyStudents
---------------------------------------
表变量
declare @varTbl table(col1 int,col2 varchar(30))
insert into @varTbl valuse(100,'A')
insert into @varTbl valuse(101,'B')
select * from @varTbl
-------------------------------------------------
视图:我们可以把很长的查询语句,放在一个视图中,这样我们在要得到查询语句的结果时就可以不在执行这段语句,而是查询一下相应的视图即可
视图类似于一个表,这个表和临时表不同,他不会在会话结束后释放掉,每次都能用。
视图中不存放数据,视图中只存放查询,不存放数据(记录)
视图的每一列都要有列名
create view vw_StudentScore
as
select
sName,
sAge,
case
when english is null then '缺考'
else convert(varchar(50),english)
end as english,
math=case
when math is null then '缺考'
else convert(varchar(50),math)
end ,
是否报考=case
when scoreId is null then '未报考'
else '以报考'
end ,
是否合格=case
when english>=60 or math>=60 then '合格'
else '不合格'
from Student
left join Score
on Score.studentId=Student.sId
update Student set sName='山西关羽'where sName='关羽'
create view Student
create view vw_StudentScore
create view vw2
as
select
sName,
sAge,
case
when sAge>13 and sAge<16 then'豆蔻年华'
where sAge>=50 then 'oldman'
else '青壮年'
end as 生存状态