這回介紹日期函數的使用。
在大多的資料庫都會有一些可以用的函式，讓我們做一些特殊操作。
在這回以SQL server資料庫為例子。

SQL server日期函式

GETDATE() 取得現在的日期時間
DATEADD() 增加或減少指定的時間間隔
DATEPART() 取出日期時間中特定的部分
CONVERT() 格式化日期時間顯示
DATEDIFF() 日期相減

可用的關鍵字

可用的關鍵字參數:

GETDATE

取得現在的日期時間

1
2

Select 
GETDATE() as 現在的日期時間

結果

DATEADD

增加或減少指定的時間間隔
可以輸入負值，代表回推
正值則是往後推

1
2

Select 
DATEADD(day, 2, GETDATE()) as 兩天後

結果

DATEPART

取出日期時間中特定的部分

1
2
3

SELECT DATEPART(yyyy, GETDATE())  as 年,
DATEPART(mm, GETDATE())  as 月,
DATEPART(dd, GETDATE())  as 日

結果

CONVERT

格式化日期時間顯示，可以用格式化代碼快速轉成指定規範格式。

格式代碼:

1

SELECT CONVERT(varchar, GETDATE(), 100) AS Converted

DATEDIFF

日期相減

1

SELECT DATEDIFF(day, '2008-06-10', '2008-08-10') AS 日期相差

結果

結論

在SQL語法上比較常用的是DATEDIFF、DATEADD、CONVERT。GETDATE。
如:前1個月、今天-7天、上禮拜資料這種需求還蠻常見的。

參考資料
https://www.fooish.com/sql/sql-server-getdate-function.html
https://www.w3school.com.cn/sql/func_convert.asp

當我們一開始安裝SQL server時，系統會幫我們預設一組帳號，即最高管理員帳號。
但當我們實際開發時，勢必不會使用這組帳號，因為太過危險了。
他能夠刪除資料庫、表，哪天被入侵、盜用時整個資料庫都會有危險。
所以，在此要介紹一下新增自訂權限的管理員。

新增自訂的資料庫管理員

1.新增登入

2.填入帳、密資料

3.可以在左方看到剛新增的管理員

4.屬性

5.使用混和模式登入

6.開啟SQL Server 2019 Configuration Manager，重啟SQL server。
注意，重新開機是沒效的，一定要用這種方法重新運作SQL server

7.登入帳號

8.成功登入

9.登入後選擇原本資料庫會發現沒有權限

10.回去編輯test01帳號，把Orders的db_owner勾起來即可。

結論

這樣一來就能限制某些帳號管理特定資料庫了。

參考資料

https://ithelp.ithome.com.tw/articles/10214386

因為SQL server會有匯入舊版問題，像是把2019版的匯入到2008版的，所以要在探討另一個做法，即向下兼容的備份與還原。

備份

1.選擇generate scripts

2.會跳出框框，選擇進階、儲存成script

3.可選擇想降級的SQL server版本

4.可選擇只要匯出schema還是data

5.點Next下一步

6.也是點Next下一步

7.等待生成完畢，點OK

8.到資料夾就可以找到.sql檔案了

還原

1.雙擊剛剛匯出的.sql

2.執行他Execute

3.這時會一直跑都跑不完，中止後發現錯誤，因為用.sql匯入要把原本資料庫給砍了。
不能有相同名稱的資料庫

4.把現有資料庫給刪除

5.勾選關閉資料庫連線後點擊OK

6.此時，重新再跑一次，可以發現成功執行
刷新左方的資料庫，可以發現成功還原了。

使用.sql的優缺點

優點:
1.可以選擇產出舊版 SQL Server 的 script
2.可以自行調整 script
可能會有問題的部分:
1.特殊字元或是 binary 無法匯入
參考:https://social.technet.microsoft.com/Forums/zh-TW/1fc3c435-5b24-45bf-8cb9-1427e3392de2/293052753023383208032338420837sqlserver2001335722251043439930340?forum=sqlservermanagementzhcht
(但我發現新版2019的都已經有自動幫你補上N了所以好像不太會有問題。)
補上:
在國際性資料庫中管理字元資料，最容易的方式是使用 Unicode nchar、nvarchar 和 nvarchar(max) 資料類型，而不是相對的非 Unicode char、varchar 和 text。

2.如果想要建立相同的資料庫需要調整的內容較多。
(所以我上面做法是直接砍了。)

總結

如果.bak不行才出此下策，通常是對方機器太舊才可能用這種方式。

參考資料
https://blog.yowko.com/sql-server-backup-restore/

在這回主要介紹資料庫的備份方法。
避免哪天真的遇到不可測的失誤、資料庫毀損、硬碟壞掉。
在這篇中先介紹SQL server .bak檔案的備份與還原

完整備份

1.對資料表右鍵選到Back Up

2.Backup Type 選擇Full表示完整備份

3.備份成功會跳出訊息

4.可至資料夾找到完整備份的.bak
注意:.bak可能沒辦法向下兼容舊版資料庫，如果要向下兼容要用另一種SQL備份的方法。

完整備份還原

1.先把資料庫設定成離線狀態，Tasks>Take Offline。

2.勾選Drop All Active Connection，之後按下OK，讓資料庫進入離線模式。

3.之後對資料庫按右鍵Tasks>Restore>Datebase。

4.接下來選擇Device，從本地檔案去還原。

5.在跳出的框框選擇Add

6.選擇剛備份出來的.bak

7.返回原本視窗後按下OK

8.然後點OK，開始進行還原

9.跳出了還原成功

10.檢查可以發現資料表都回來了，裡面的資料也是

還原失敗可能情況

1.沒有Offline資料庫，就會造成無法匯入。
錯誤訊息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'Orders' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46437.65+(SMO-master-A)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

圖:

Ans:只要在還原前先把資料庫設定成離線狀態，Tasks>Take Offline即可。

結論

有還原檔案比沒有好，記得備份，以備不時之需。
下篇預計是用SQL方式去產可向下兼容的備份檔案。

在這回簡易示範SQL server資料刪除。

DELETE

主要就是用delete搭配某表，以及條件。
此外，要注意條件要記得下，不然整張表資料被刪掉會很悲劇。

語法:

1

DELETE FROM table_name WHERE condition; 

測試資料

還是一樣，先放上資料表:

customers 顧客

products 產品

orders 出貨單

order_details 出貨單明細

實作:想要刪除產品PS4。

1

delete from products where name = 'PS4'

結果:

結論

成功的練習了DELETE，非常簡單的語法。
但這種反而要小心，DELETE跟UPDATE，因為如果where條件下錯，整張表就完了QQ。

(ps.目前我還沒想到如果沒備份要怎麼救，只能求助google了。)

下回預計是資料庫備份。

參考資料
https://www.w3schools.com/sql/sql_delete.asp
https://ithelp.ithome.com.tw/articles/10220412

在這回簡易示範SQL server資料更新。

UPDATE

主要就是用update搭配set去設定資料數值。
此外，如果要針對某一條件修改請用where加上條件，否則將會每筆都改會很悲劇。(如果你又沒備份的話，記得每日備份阿。)

語法:

1
2
3

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

測試資料

還是一樣，先放上資料表:

customers 顧客

products 產品

orders 出貨單

order_details 出貨單明細

實作:想要更新客戶number為U002資料，把tel更新成09-00000000。

1
2
3

update customers
set tel='09-00000000'
where customers.number = 'U002'

結果:

結論

目前還沒犯過不小心更新全部表的失誤。但不知道哪天會很雖遇到(?)
所以，建議操作SQL前或是每日操作前都先備份，並且留存備份檔案，至少哪天有問題時還有機會救。
因為可能會有備份需求，所以先記著，之後會打篇資料備份。

(ps.目前我還沒想到如果沒備份要怎麼救，只能求助google了。)

下一回預計就是練習刪除語法。

參考資料
https://www.w3schools.com/sql/sql_update.asp
https://ithelp.ithome.com.tw/articles/10220040

在前面已經把大量的常見查詢語法做過一次實踐了。
接下來就是比較簡單的語法了，這回講的是insert into用來在表中插入資料。

INSERT INTO

語法:

1
2

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...); 

測試資料

還是一樣，先放上資料表:

customers 顧客

products 產品

orders 出貨單

order_details 出貨單明細

實作:想要新增一筆客戶資料。

1

insert into customers (number,name,tel) values('U003','Tom','09-15965423')

結果:

透過子查詢新增資料

算是另一種少見的做法，就是數值的部分是使用查詢來的數值。
雖然很少用，但就稍微嘗試一下這種寫法:

實作:我想把products的number、name、price添加到customers將他們視為number、name、tel。

1
2

insert into customers (number,name,tel) 
select number,name,price from products 

結果:
就把products的資料加到customers了。(為了示範需求，因為真的超怪。

總結

簡易的操作了資料插入的語法。
下回就是更新update。

參考資料
https://ithelp.ithome.com.tw/articles/10220002
https://www.w3schools.com/sql/sql_insert.asp

在這回中要介紹我很少很少很少用到的any與all用法。
因為大部分搜尋條件都能靠前面介紹的那些組合度過了，並且更直覺。

ANY

用於子查詢
任意，一個條件符合就返回true

1
2
3
4
5
6

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition); 

ALL

用於子查詢
所有，全部條件滿足才返回true
在select、having、where都可能用到

1
2
3
4
5
6

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition); 

差異比較

使用 > 比較運算子來作例子，>ALL 代表大於每個數值；換句話說，就是大於最大值。例如， >ALL (1, 2, 3) 代表大於 3。>ANY 代表至少大於一個數值，也就是說大於最小值。因此 >ANY (1, 2, 3) 代表大於 1。

實際演練

還是一樣，先放上資料表:

*因為這次範例太難用原本訂單的表，於是建立一個測試的表

實作:在test表中比較類型為1數值大於所有類型為2的數值結果。

1

select * from test where type = '1' and number > all (select number from test where type = '2')

結果:
1的內容:[1,3,4,8]
2的內容:[2,5,4]
而以上方條件all等於找最大值，即number > 5 的結果。

實作:在test表中比較類型為1數值至少大於一個類型2數值的結果。

1

select * from test where type = '1' and number > any (select number from test where type = '2')

結果:
1的內容:[1,3,4,8]
2的內容:[2,5,4]
而以上方條件any，等於找最小值，即number > 2 的結果。

總結

用到機會連要擠出範例都很難@@…
之前的範例都能用原本建的資料表示範，而這回測試any、all時發現除非改動表的設計，否則要做出好的實驗數據太不容易了。
總之，all、any就只要求看懂就好。

參考資料
http://www.blueshop.com.tw/board/FUM20041006152735ZFS/BRD200309251806228LO.html
https://blog.csdn.net/xianglingchuan/article/details/51737330

在SQL查詢時對這個關鍵詞有印象，但非常陌生。
於是這次就來探討一下having。
他用來跟我們前面聊過的Group by搭配，因為where不能用在聚合函數

having

HAVING 子句是用來取代 WHERE 搭配聚合函數 (aggregate function) 進行條件查詢，因為 WHERE 不能與聚合函數一起使用。

聚合函數指的也就是 AVG()、COUNT()、MAX()、MIN()、SUM() 等這些內建函數。

語法:

1
2
3
4
5
6

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

實作

先列一下目前資料表狀態

customers 顧客

products 產品

orders 出貨單

order_details 出貨單明細

實作:統計訂單明細並得到總額大於50000的訂單。

1
2
3

Select order_number,sum(price*amount) as 訂單總額 
from order_details group by order_details.order_number
having sum(price*amount) > 50000

結果:
透過having來與聚合函數搭配。

總結

發現having用法也很直覺，沒想像中難。
最主要就是group by後想篩選資料時就使用它。

參考資料
https://www.fooish.com/sql/having.html
https://www.w3schools.com/sql/sql_having.asp

這回探討SQL server exists用法。
之前有在子查詢的範例中使用到，但覺得不夠清楚所以拉出來探討看看。

Exists

EXISTS 運算子可以連接子查詢，用來判斷子查詢是否有返回的結果，如果有結果返回則為真、否則為假。若 EXISTS 為真，就會繼續執行外查詢中的 SQL；若 EXISTS 為假，則整個 SQL 查詢就不會返回任何結果。

語法

1
2
3
4

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition); 

且exists跟in語法都可以達到相同效果。
如:

1
2
3

SELECT * FROM table_a
WHERE EXISTS
(SELECT * FROM table_b WHERE table_b.id=table_a.id);

等價於

1
2
3

SELECT * FROM table_a
WHERE id
in (SELECT id FROM table_b);

注意使用EXISTS條件,main query(外部查詢)和subquery(子查詢)一定要有連接,例如 table_b.id=table_a.id,如果沒有連結EXISTS條件使用是無效的

實作

customers 顧客

products 產品

orders 出貨單

order_details 出貨單明細

實作:尋找有下訂單的客戶

1
2

Select * from customers 
where exists (select * from orders where customers.number = orders.customer_number )

結果:
把結果符合customers.number = orders.customer_number的列都返回。
所以得到有下訂單的客戶。
此外發現exists子查詢的Select * from其實隨便指派一個欄位都可以，因為並不影響結果。

實作:尋找”沒”有下訂單的客戶

1
2

Select * from customers 
where not exists (select * from orders where customers.number = orders.customer_number )

結果:
在exists前加個not可以得到相反結果，即沒下訂單的客戶

總結

覺得還是用in會比較直覺，用exists時會突然覺得where竟然能直接接一個非欄位的用法。
但熟悉後可能又覺得還好了。

此外，似乎有in跟exists效能一說，目前就先記著，改日有空再找機會打一篇。

參考資料
https://www.w3schools.com/sql/sql_exists.asp
https://www.fooish.com/sql/exists.html
http://mylinoraclesql.blogspot.com/2013/07/exists.html
https://eeluck.pixnet.net/blog/post/27559378