Distinct

Use Northwind

/*
使用Distinct
*/

--列出客戶所在國家
select distinct Country
from dbo.Customers
--Distinct Country已內含ORDER BY Country asc動作

Order By

---使用北風資料庫
Use Northwind

/*
使用Order By
*/

--列出北美地區客戶
Select *
from dbo.Customers
where Country in ('Canada','USA','Mexico')
order by Country ---ASC--預設(升冪)

----列出北美地區客戶
Select *
from dbo.Customers
where Country in ('Canada','USA','Mexico')
Order by Country desc--(降冪)

---列出客戶指定排序(1.USA 2.UK 3.Germany 4.France 5.其他用升冪)
Select *
from dbo.Customers
order by case when Country='USA' then 1 when Country='UK' then 2 when Country='Germany' then 3 when Country='France' then 4 else 5 end,Country asc

AS

Use Northwind

/*
使用as
*/

select ProductID ,
ProductName as '產品名稱',
UnitsInStock '庫存量', --as 可省略
'訂購數量'=UnitsOnOrder,
'安全存量'=ReorderLevel,
'停產'=Discontinued
from dbo.Products;

NULL

Use Northwind

/*
Is Null /Is Not Null
*/

--列出尚未出貨訂單
select *
from dbo.Orders
where ShippedDate is null

--列出已出貨訂單
select *
from dbo.Orders
where ShippedDate is not null

In

---使用北風資料庫
Use Northwind

/*
In /Not In
*/

--列出北美地區客戶
Select *
from dbo.Customers
where Country='Canada' or Country='USA' or Country='Mexico'

--IN
Select *
from dbo.Customers
where Country in('Canada','USA','Mexico')

--列出非北美地區客戶
Select *
from dbo.Customers
where Country<>'Canada' and Country<>'USA' and Country<>'Mexico'

--Not IN
Select *
from dbo.Customers
where Country not in('Canada','USA','Mexico')

Between ... and ...

---使用北風資料庫
Use Northwind

/*
Between ... And ...
*/

--列出訂價10~30之間的產品
Select *
from dbo.Products
where UnitPrice<=30 and UnitPrice>=10
-- 或
Select *
from dbo.Products
where UnitPrice between 10 and 30

--列出訂價不在10~30之間的產品
Select *
from dbo.Products
where UnitPrice>30 or UnitPrice<10
-- 或
Select *
from dbo.Products
where UnitPrice not between 10 and 30

邏輯運算子

--- 使用北風資料庫
Use Northwind

/*
And /Or /Not
*/

--列出產品目錄'1'中庫存量不足或所有類別中安全存量不足的產品
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1 and UnitsInStock ＜UnitsOnOrder
or UnitsInStock＜ReorderLevel

--列出產品目錄'1'中-->庫存量不足或安全存量不足的產品
Select ProductID,ProductName,CategoryID,UnitsInStock,UnitsOnOrder,ReorderLevel
from dbo.Products
where CategoryID=1
and (UnitsInStock＜UnitsOnOrder or UnitsInStock＜ReorderLevel )