sql指令

distinct
過濾重複資料，可過濾多欄交集重複

as
設定欄位別名，並以別名顯示於查詢結果。as可省略不輸入

新增運算欄位
直接下數學式然後用AS給予欄位名稱
亦可以使用"在編輯器中設計查詢"，在資料行下數學式，在別名給予欄位名稱

select dno, mno, sal, 12*sal as "年薪" from emp

between關鍵字
where dno between  1000 and 3000 --傳回dno介於1000~3000的資料
where dno not between 1000 and 3000 --傳回dno不在1000~30000範圍內的資料


isnull函數
若欄位值為NULL則傳回給定值，若不是NULL則傳回原值

sal
100
null
select isnull(sal,0) from emp
查詢結果
100
0

null查詢
where dno is null --傳回dno值為null的列
where dno is not null --傳回dno值不是null的列

like關鍵字與特殊符號

where ename like 'M%' --得到ename開頭為M的資料內容，%代表任意長度
where ename like '_M%' --得到第二個字母為M的資料內容，_代表該位置允許任意字元
where ename like '[MJ]%' --得到開頭為M或J的資料內容，中括號標示同一位置多重選項

排序關鍵字
asc --遞增(預設)
desc --遞減

排序可下多重條件:
order by ename desc, sal 先用ename遞減排序再用sal遞增排序
除了用欄位名稱，亦可用欄位序號
order by 2 用第二欄排序

where ename='Eric' or ename='Mary' or ename = 'Joy' 篩選符合其中一個條件的資料
簡化寫法如下
where ename in ('Eric', 'Mary', 'Joy')結果同上
反向過濾關鍵字'Not'
where ename Not in ('Eric', 'Mary', 'Joy')篩選ename不為Eric,Mary,Joy的資料

指令利用別名簡寫方法:
select emp.eno, emp.ename, dept.dno, dept.dname
from emp, dept
在from的地方用別名指令，則其他地方即可用別名，如下:
select e.eno, e.ename, d.dno, d.dname
from emp as e, dept as d (可省略as改為emp e, dept d)

inner join交集查詢，left outer join左聯集查詢，right outer join右聯集查詢

select e.ename, d.dno from emp as e inner join dept as d on e.dno = d.dno;

select e.ename, d.dno from emp as e left outer join dept as d on e.dno = d.dno;

select e.ename, d.dno from emp as e right outer join dept as d on e.dno = d.dno;

在mysql中 'on e.dno = d.dno 可以省略成 using (dno)

將查詢結果輸出到新資料表
select e.eno, e.ename into 新資料表名稱(例如emp1001)
from emp e
where e.dno = 1001

插入新資料行到指定資料表
方法1(此方法欄位數量只要<=目標資料表即可，格式要相同):
insert into dept(dno, dname, loc) values(1006,'RESEARCH','TAIWAN')
方法2(此方法select欄位數量和格式皆需和目標資料表相同):
insert into dept
select dno, dname, loc from emp

修改欄位資料:update (資料表名稱) set (欄位) = '修改結果' where (套用條件)
update emp set ename = 'haha'
where emp.eno = 2011

刪除欄位資料:delete from (資料表名稱) where (套用條件)
delete from emp
where emp.eno = 2011

group by將欄位資料區分成群組各別做查詢處理
select avg(sal) from emp => 計算所有人員平均薪資
若加入group by emp.dno =>將emp裡的資料以dno欄位內容相同者歸納為同一群組，依群組分別計算平均薪資

select dno, job, avg(sal) from emp group by dno, job
先用dno分組再在各組別裡用job再分組，然後做查詢處理

對group by結果做條件篩選:having關鍵字
select dno, avg(sal)
from emp
group by dno
having avg(sal) > 30000 =>平均薪資大於30000的群組才顯示

子查詢，可用在查詢中需要資料input的地方，以'( )'區隔:
查詢薪資最高者的資料
select *
from emp
where sal = (select max(sal) from emp)

修改符合條件為子查詢結果的欄位
update dept101 set dname = (select ename from emp where ename='TOM')
where dno = (select dno from emp where ename = 'MAY')

查詢各部門最高薪資人員
select *
from emp
where sal in (

--子查詢篩選各部門最高薪資
select max(sal)
from emp e inner join dept d on e.dno = d.dno
group by d.dname

)

符合子查詢中的任一結果關鍵字:any
查詢薪資大於子查詢結果中的任一筆數字的員工
select *
from emp e
where e.sal > any (
--以下子查詢會傳回高於20000的多組薪資數字
select sal from emp where sal > 20000
)

符合子查詢中的所有結果關鍵字:all
查詢薪資小於子查詢結果中的所有數字的員工
select *
from emp e
where e.sal < all (
select sal from emp where sal > 30000
)

not in () 清單中不可有null值，否則查詢會失敗，故需用isnull替代掉null值
select *
from emp e
where e.eno not in (
--以下子查詢的結果會含有null資料，需用isnull預處理
--select m.mgr from emp m
select isnull(m.mgr,0) from emp m
)

子查詢結果若有資料即成立關鍵字:exists
select *
from emp
where Exists (
--以下子查詢若有一筆以上的資料回傳，則where條件即成立，否則不成立
 select *
 from emp
 where eno = 2006
)

選取查詢結果的指定部份，關鍵字:top
查詢1，取得emp所有資料
select *
from emp
篩選查詢1結果的前兩筆資料
select top 2 *
from emp
篩選查詢1結果的前5%筆資料
select top 5 percent *
from emp
查詢2，取得emp中的所有資料，以sal由高至低排序
select *
from emp
order by sal desc
篩選查詢2結果的前兩筆資料
select top 2*
from emp
order by sal desc
篩選查詢2結果的前兩筆資料，當有排序時，將排序相同但不包含在結果的資料一併列出
例如以薪資排序，前3名為50K，40K，40K，top 2只取2筆，top 2 with ties取3筆
select top 2 with ties*
from emp
order by sal desc

mysql取查詢結果的指定筆數(ms sql不支援此語法)
select * from emp limit 2 取結果的前兩筆資料
select * from emp limit 5,10 取結果的第5~14共10筆資料

小計欄位關鍵字:with rollup，對group by的第一個參數群組做小計和總計
select dno, job, sex, sum(sal)
from emp
group by dno, job, sex
with rollup -- 對dno做小計和總計

小計欄位關鍵字:with cube，對group by的每個參數群組做小計和總計
select dno, job, sex, sum(sal)
from emp
group by dno, job, sex
with cube -- 對dno,job,sex做小計和總計

建立資料表:
create table test1(
a nvarchar(50),
b nvarchar(50) default 'abc', -- default 設定預設值
c int
)
刪除資料表:
drop table test1

加入排序欄:
ROW_NUMBER()，語法如下:
select ROW_NUMBER() over(order by dno, hiredate) as rowid, * from emp
以上語法是從emp選擇所有資料，然後依據order by內容排序後，在最前面加入一欄名稱為rowid的排序欄

建立檢視表:
將查詢結果儲存為類似新資料表的檢視表，後續可將此檢視表視為資料表做查詢的動作，便於二次查詢，如下建立一個名稱為男業務的檢視表
CREATE VIEW 男業務
as select *
from 員工
where 性別 = '男' and 職稱 like '%業務%'

以加密方式建立檢視表:with encryption關鍵字
create view 廠商商品
with encryption
as select s.供應商編號, s.供應商名稱, pt.類別名稱, pd.產品名稱
from 供應商 s, 產品類別 pt, 產品資料 pd
where s.供應商編號 = pd.供應商編號 and pd.類別編號 = pt.類別編號
order by s.供應商編號

修改既存檢視表:alter關鍵字
alter view 廠商商品
as select s.供應商編號, s.供應商名稱, pt.類別名稱, pd.產品名稱
from 供應商 s, 產品類別 pt, 產品資料 pd
where s.供應商編號 = pd.供應商編號 and pd.類別編號 = pt.類別編號
order by s.供應商編號
此例修改上例有加密的檢視表為不加密

合併查詢結果:union關鍵字，欄位數量與資料格式需相同
select 供應商編號, 供應商名稱, 聯絡人, 電話 from 供應商
union
select 客戶編號, 公司名稱, 聯絡人, 電話 from 客戶

限制透過檢視表修改資料時需符合特定條件: with check option關鍵字
加入with check option 關鍵字建立的檢視表，之後要透過該檢視表修改資料時，就必須滿足其where條件限制，以下例就是若要修改建議單價，就只能改 < 22的值，改平均成本就只能改< 10的值
create view 低單價或單成本之產品 (產品編號, 產品名稱, 建議單價, 平均成本)
as
select 產品編號, 產品名稱, 建議單價, 平均成本 from 產品資料
where 建議單價 <22 or 平均成本 < 10
with check option

分段執行查詢:GO關鍵字
select * from 員工
GO
select * from 客戶
GO
select * from 供應商
GO
以上程式碼會等同做三次查詢，以GO關鍵字分隔，當某段查詢有錯誤時，其他查詢依然會執行，不會中斷