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\
)