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 )

Like

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

/*
Like '%_[][^]'
*/

--列出'A'開頭客戶
select *
from dbo.Customers
where CompanyName like 'A%'

--列出'S'結尾客戶
select *
from dbo.Customers
where CompanyName like '%s'

--列出客戶名稱有含'B'的客戶
select *
from dbo.Customers
where CompanyName like '%B%'

--列出電話倒數第二個字是'2'的客戶
select *
from dbo.Customers
where Phone like '%2_'

--列出電話第二個字是'2',倒數第二個字是'2'的客戶
select *
from dbo.Customers
where Phone like '_2%2_'

--列出'A' 或'B'或'C'開頭客戶
select *
from dbo.Customers
where CompanyName like 'A%' or CompanyName like 'B%' or CompanyName like 'c%'
-- or
select *
from dbo.Customers
where CompanyName like '[ABC]%'
-- or
select *
from dbo.Customers
where CompanyName like '[A-C]%'

--列出Not 'A' 且Not 'B'且Not 'C'開頭客戶
select *
from dbo.Customers
where CompanyName not like 'A%' and CompanyName not like 'B%' and CompanyName not like 'c%'
-- or
select *
from dbo.Customers
where CompanyName like '[^ABC]%'
-- or
select *
from dbo.Customers
where CompanyName like '[^A-C]%'

-------------------------test
update dbo.Products
set QuantityPerUnit='70%'
where ProductID=1

--Select *
from dbo.Products
where ProductID=1

--列出有%產品
Select *
from dbo.Products
where QuantityPerUnit like '%#%%' escape '#'

比較運算子

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

/*
>, < ,= ,>= ,<= ,<>
*/

--列出延遲出貨訂單
SELECT OrderID,OrderDate,ShippedDate,RequiredDate
From dbo.Orders
where ShippedDate>RequiredDate

--列出非美國地區客戶
Select CustomerID,CompanyName,City,Country
From dbo.Customers
where Country<>'USA'

--列出庫存量不足的產品
Select ProductID,ProductName,UnitPrice,UnitsOnOrder,UnitsInStock
from dbo.Products
where UnitsOnOrder>UnitsInStock

--列出安全存量不足的產品Select ProductID,ProductName,UnitPrice,UnitsOnOrder,ReorderLevel
from dbo.Products
where UnitsOnOrder>ReorderLevel

Select

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


/*
Select Column1,Column2,... (垂直篩選)
From Table
Where Row條件式 (水平篩選)
*/


--------All
select *
from dbo.Customers


--列出美國客戶
select CustomerID,CompanyName,City,Country --垂直篩選
from dbo.Customers
where Country='USA' --水平篩選


---------All
Select *
from dbo.Employees


--列出1960年後出生的員工
Select LastName,FirstName,BirthDate,HireDate
from dbo.Employees
Where BirthDate>='1960/1/1'


--------使用YEAR(日期格式)
Select LastName,FirstName,BirthDate,HireDate
from dbo.Employees
Where Year(BirthDate)>=1960

------All
Select *
from dbo.Products

--列出單位售價>=100元以上的產品
Select ProductID,ProductName,UnitPrice
from dbo.Products
where UnitPrice>=100