From变化集锦
1.从一个表中检索
select *
from dbo.teacher
2.从一个检索结果中再检索即从临时表中检索
select *
from (
    select teacher_id , teacher_name
    from dbo.teacher
) te
where te.teacher_id <4
3.从多表中检索，构成笛卡尔积(效率低)
select dbo.teacher.* , 系名
from dbo.teacher , dbo.系
where dbo.teacher.系_id = dbo.系.系_id
4.多表的链接查询（尊重左表）
select *
from dbo.系 left join dbo.teacher
on dbo.系.系_id = dbo.teacher.系id
(尊重右边)
select *
from dbo.系 right join dbo.teacher
on dbo.系.系_id = dbo.teacher.系_id
(内连接 都不尊重)
select *
    from dbo.系 inner join dbo.教师   
    on dbo.系.系_ID=dbo.教师.系_ID inner join dbo.教师认课
    on dbo.教师.教师_ID=dbo.教师认课.教师_ID
(全链接 都尊重)
select *
    from dbo.系 all join dbo.teacher
    on dbo.系.系_id = dbo.teacher.系_id
5.给被检索表起别名  注意：起了别名就一定要用别名来引用字段
错误的
select dbo.teacher.teacher_id
from dbo.teacher js
正确的
select js.teacher_id
from dbo.teacher js
    where 集锦

1.常规关系运算符 >、 >=、 <、 <=、 !=、 <>、 =
select 教师_ID
from dbo.教师
where 教师_ID<>1
2.常规逻辑运算符 not , and ,or
select teacher_id
from dbo.teacher
where not (teacher_id>=1 and teacher_id<=8)
3.区间
teacher_id >=1 并且 teacher_id<=8
select teacher_id
from dbo.teacher
where teacher_id between 1 and 8
4.检索null值
select teacher_id
from dbo.teacher
where teacher_date is null
5.关于字符的模糊查询
(1)姓张的所有老师
select teacher_id
from dbo.teacher
where teacher_name like '张%'
(2)姓张的但是名字只有两个字的老师
select teacher_id
from dbo.teacher
where teacher_name like '张-'
(3)名字含有国字的所有老师
select teacher_id
from dbo.teacher
where teacher_name like '%国%'
(4)老师的第一个名字在a-m之间的老师
select teacher_id
from dbo.teacher
where teacher_name like '[a-m]%'
(5)老师名字的第一个字母不在a-m之间的老师
select teacher_id
from dbo.teacher
where teacher_name like '[^a-m]%'
(6)老师的名字第一个字母是a或者b或者m的老师
select teacher_id
from dbo.teacher
where teacher_name like '[a,b,m]%'
(7)教师名字第一个字母在a到k之间或o到x之间的老师
select teacher_id
from dbo.teacher
where teacher_name like '[a-k,o-x]%'
6.关于in的用法
(1)枚举型
select *
from dbo.teacher
where teacher_id in (1,3,5)

select*
from dbo.teacher
where teacher_id not in(1,3,5)
(2)嵌套子查询
有课上的老师
select *
from dbo.teacher
where teahcer_id in (
    select teacherid
    from dbo.教师任课
)
7.exists判断子查询是否有结果（强调有无，不关心具体是什么）
select *
from dbo.教师
where exists   --存在
(
  select *
    from dbo.教师认课
    where dbo.教师认课.教师_ID=dbo.教师.教师_ID
)
8.any其中一个
教师ID大于所有教师ID中任意一个的教师（相当于大于最小ID的教师）
select *
from dbo.teahcer
where teahcer_id > any
(    select teacher_id
    from dbo.teacher

)
9.all全部
相当于大于最大max
select *
from dbo.teacher
where teacher_id > all(
    select teacher_id
    from dbo.teacher\
)