PostgreSQL 的自動備份策略

ChiaHu's Blog » PostgreSQL 的自動備份策略

如果 PostgreSQL 的連線設定需要使用密碼，那麼在利用 crontab 排程自動執行備份指令時，也會遇到需要輸入密碼而無法自動執行的情況。
如果你需要多台資料庫並行運作，共用負擔資料的操作動作，而且資料內容異動頻繁。那麼你需要採用 WAL 的方式。另一種採用 WAL 的場合在大型資料庫系統。當你的資料庫空間成長到數百MB或GB的單位時，你也需要考慮改用 WAL 。因為 WAL 採用 log 同步機制，每次備份時只需要傳輸上次備份時間到目前時間的異動資訊，而不會傳輸整個資料庫的內容。關於 WAL 的操作，不在本文的說明中。
PostgreSQL 在 8 版之後可以使用「.pgpass」來為使用者設定自動認證。
以下介紹詳細步驟
1. 假如我們要連線的資料庫位置為「localhost」(表示本機端)、連接埠為「5432」(PostgreSQL預設連接埠)、資料庫名稱為 「database」、資料庫帳號為「username」、密碼為「password」。注意，username 不一定要跟你執行 crontab 的帳號相同。
2. 切換到執行 crontab 的實體帳號，假如是「postgres」，則利用su指令切換：

[postgres@pgdb:/root/]# su postgres

3. 在該帳號的家目錄新增檔案「.pgpass」

[postgres@pgdb:/home/postgres/]# vi ~/.pgpass

4. 利用vi編輯器輸入以下資料：(格式為: hostname:port:database:username:password )

localhost:5432:database:username:password

完成之後儲存離開。
5. 修改 .pgpass 的權限為 600

[postgres@pgdb:/home/postgres/]# chmod 600 ~/.pgpass

6. 測試看看，利用 pg_dump 匯出資料庫到「/backups/dbbak.sql」

[postgres@pgdb:/]# pg_dump -U username database > /backups/dbbak.sql

7. 如果「/backups/dbbak.sql」有檔案，並且內文為正確的 SQL 檔，則表示認證成功。反之，如果系統要求你輸入密碼，那可能是哪個步驟做錯了，請回頭一一確認。
8.當確認可以自動通過認證，以指令執行 PostgreSQL 匯出的之後，就可以把這些指令撰寫於 backupdb.sh，將它放置在 /home/postgres 底下，backupdb.sh 參考內容如下:

#!/bin/sh

day=`date +%Y-%m-%d`
pg_dump -U username database > /backups/dbbak_$day.sql

說明：也就是將 database 資料庫備份到 /backups/ 底下，檔名則依 dbbak_年月日.sql 格式命名
9. 加入 crontab 排程內，每天凌晨3:30做備份。

30 3 * * * postgres /home/postgres/backupdb.sh

匯入CSV檔 到 Rails SQLite3 資料庫

require 'csv'

#每一行都是的一個按照順序儲存各個欄位的陣列，並且順序跟CSV檔案中的一樣
#匯入myfile.csv，分割符號'|'
CSV.open('myfile.csv', 'r','|') do |row|
# 從 CSV 檔匯入，假設欄位順序為 a, b, c, d
# 為每一欄位建立並儲存一個 Trunk model
Trunk.create!(:a => row[0], :b => row[1], :c => row[2], :d => row[3])
end

其他參考資料ruby-doc.org/CSV

SQLite 教學

* 1 建立資料庫檔案
* 2 在sqlite3提示列下操作
* 3 SQL的指令格式
* 4 建立資料表
* 5 建立索引
* 6 加入一筆資料
* 7 查詢資料
* 8 如何更改或刪除資料
* 9 其他sqlite的特別用法
* 10 小結

建立資料庫檔案

用sqlite3建立資料庫的方法很簡單，只要在shell下鍵入（以下$符號為shell提示號，請勿鍵入）：

$ sqlite3 foo.db3

如果目錄下沒有foo.db3，sqlite3就會建立這個資料庫。sqlite3並沒有強制資料庫檔名要怎麼取，因此如果你喜歡，也可以取個例如foo.icannameitwhateverilike的檔名。

在sqlite3提示列下操作

進入了sqlite3之後，會看到以下文字：

SQLite version 3.1.3
Enter ".help" for instructions
sqlite＞

這時如果使用.help可 以取得求助，.quit則是離開（請注意：不是quit）

SQL的指令格式

所有的SQL指令都是以分號（;）結尾的。如果遇到兩個減號（--）則代表註解，sqlite3會略過去。

建立資料表

假設我們要建一個名叫film的資料表，只要鍵入以下指令就可以了：

create table film(title, length, year, starring);

這樣我們就建立了一個名叫film的資料表，裡面有name、length、year、starring四個欄位。

這個create table指令的語法為：

create table table_name(field1, field2, field3, ...);

table_name是資料表的名稱，fieldx則是欄位的名字。sqlite3與許多SQL資料庫軟體不同的是，它不在乎欄位屬於哪一種資料型態：sqlite3的欄位可以儲存任何東西：文字、數字、大量文字（blob），它會在適時自動轉換。

建立索引

如果資料表有相當多的資料，我們便會建立索引來加快速度。好比說：

create index film_title_index on film(title);

意思是針對film資料表的title欄位，建立一個名叫film_title_index的索引。這個指令的語法為

create index index_name on table_name(field_to_be_indexed);

一旦建立了索引，sqlite3會在針對該欄位作查詢時，自動使用該索引。這一切的操作都是在幕後自動發生的，無須使用者特別指令。

加入一筆資料

接下來我們要加入資料了，加入的方法為使用insert into指令，語法為：

insert into table_name values(data1, data2, data3, ...);

例如我們可以加入

insert into film values ('Silence of the Lambs, The', 118, 1991, 'Jodie Foster');
insert into film values ('Contact', 153, 1997, 'Jodie Foster');
insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');
insert into film values ('Hours, The', 114, 2002, 'Nicole Kidman');

如果該欄位沒有資料，我們可以填NULL。



查詢資料

講到這裡，我們終於要開始介紹SQL最強大的select指令了。我們首先簡單介紹select的基本句型：

select columns from table_name where expression;

最常見的用法，當然是倒出所有資料庫的內容：

select * from film;

如果資料太多了，我們或許會想限制筆數：

select * from film limit 10;

或是年份比較早的電影先列出來（預設為 asc）：

select * from film order by year limit 10;

或是年份比較晚的電影先列出來：

select * from film order by year desc limit 10;

或是我們只想看電影名稱跟年份：

select title, year from film order by year desc limit 10;

查所有茱蒂佛斯特演過的電影：

select * from film where starring='Jodie Foster';

查所有演員名字開頭叫茱蒂的電影（'%'、'_' 符號便是 SQL 的萬用字元，前者代表任意長度字元，後者代表任意一個字元）：

select * from film where starring like 'Jodie%';

查所有演員名字以茱蒂開頭、年份晚於1985年、年份晚的優先列出、最多十筆，只列出電影名稱和年份：

select title, year from film where starring like 'Jodie%' and year ＞= 1985 order by year desc limit 10;

有時候我們只想知道資料庫一共有多少筆資料：

select count(*) from film;

有時候我們只想知道1985年以後的電影有幾部：

select count(*) from film where year >= 1985;

（進一步的各種組合，要去看SQL專書，不過你大概已經知道SQL為什麼這麼流行了：這種語言允許你將各種查詢條件組合在一起──而我們還沒提到「跨資料庫的聯合查詢」呢！）
[編輯]
如何更改或刪除資料

瞭解select的用法非常重要，因為要在sqlite更改或刪除一筆資料，也是靠同樣的語法。

例如有一筆資料的名字打錯了：

update film set starring='Jodie Foster' where starring='Jodee Foster';

就會把主角欄位裡，被打成'Jodee Foster'的那筆（或多筆）資料，改回成Jodie Foster。

delete from film where year ＞ 1970;

就會刪除所有年代早於1970年（不含）的電影了。



其他sqlite的特別用法

sqlite可以在shell底下直接執行命令：

sqlite3 film.db "select * from film;"

輸出 HTML 表格：

sqlite3 -html film.db "select * from film;"

將資料庫「倒出來」：

sqlite3 film.db ".dump" ＞ output.sql

利用輸出的資料，建立一個一模一樣的資料庫（加上以上指令，就是標準的SQL資料庫備份了）：

sqlite3 film.db ＜ output.sql

在大量插入資料時，你可能會需要先打這個指令：

begin;

插入完資料後要記得打這個指令，資料才會寫進資料庫中：

commit;

原始出處

excel csv資料檔案匯入MySQL phpmyadmin中文字看不到？

excel csv資料檔案匯入MySQL phpmyadmin中文字看不到問題？

如果MySQL資料庫伺服器安裝預設編碼為UTF 8（Unicode），那麼用Excel將資料轉存成CSV檔內容中文字會無法成功匯入，會出現空白…..



原因是因為Excel將資料轉存成CSV檔是以BIG5去儲存的,所以直接匯入MYSQL,當然會有問題,可以使用以下方式進行匯入：
1.將Excel資料轉存成CSV檔
2.使用notepad開啟剛剛轉存的CSV檔
3.再將這份文件另存檔案,注意編碼要選擇UTF-8
4.至phpmyadmin載入CSV檔即可

mysql 裡 utf8_general_ci 跟 utf8_unicode_ci 連線校對的差異

mysql 裡 utf8_general_ci 跟 utf8_unicode_ci 連線校對的差異

有用過 mysql 的 UTF-8 編碼的人可能都會對這件事感到疑惑：

連線校對(collation)裡面的

utf8_general_ci
跟
utf8_unicode_ci

到底有什麼差異呢？
在 phpMyAdmin 裡面的說明看起來通通一樣：

utf8_general_ci 統一碼 (Unicode) (多語言), 大小寫不相符
utf8_unicode_ci 統一碼 (Unicode) (多語言), 大小寫不相符

實在是看不出什麼刁來。

所以前一陣子在搞 mysql UTF-8 化的時候，谷歌了一番，
發現這篇文章裡有詳盡的說明：（其實就是把 mysql reference manual 翻譯而已）

utf8_general_ci 在轉換時速度比較快
utf8_unicode_ci 在轉換時比較精準

轉換？怎麼講呢？
簡單說就是當資料要從一個編碼換成另外一個編碼時，
mysql 要在兩個 codepage 裡面找出來相對應的字元位置在哪裡。
對 utf8_general_ci 來說，來源 codepage 裡面的一個字元只能對應到目標 codepage 裡面的一個字元，
而 utf8_unicode_ci 則可以把來源 codepage 裡的一個字元對應到目標 codepage 裡的多個字元（或反過來）。
例如德文裡的 ß 要轉換成英文的時候如果是用 utf8_unicode_ci 轉換會變成正確的 ss ，
但是如果用 utf8_general_ci 的話則會變成單一的 s 而已。

所以如果可以的話請盡量用 utf8_unicode_ci 而不要用 utf8_general_ci ，
雖然對 multibyte 字元來說這兩個都沒差，
但是 utf8 的網頁誰也不知道哪天會不會有這種字元出現在你的網頁上，
所以如果設成 utf8_unicode_ci 你就不需要擔心貼上去之後資料在轉換間遺失了。

MS SQL日期處理方法

--檢查是否為SQL SERVER合法日期格式

SELECT ISDATE('2004-03-01')

--1 (int)正確

SELECT ISDATE('2004-02-33')

--0 (int)錯誤

--取得現在時間GETDATE()

select GETDATE()

--2008-04-26 03:25:31.900

--取得現在UTC 時間 (國際標準時間或格林威治標準時間)GETUTCDATE()

select GETUTCDATE()

--2008-04-25 19:30:23.650

--取得年.月.日及其它時間單位值

select year(getdate())

select datepart(yyyy, getdate())

select datepart(yy, getdate())

--2008 yyyy(int)年

select month(getdate())

select datepart(mm, getdate())

select datepart(m, getdate())

--4 mm(int)月

select day(getdate())

select datepart(dd, getdate())

select datepart(d, getdate())

--26 dd(int)日

select datepart(qq, getdate())

--2 qq(int)第幾季

select datepart(dw, getdate())

--7 dw(int)星期幾

其它datepar的參數參考下表:

Datepart	Abbreviations
年 year	yy, yyyy
季 quarter	qq, q
月 month	mm, m
年的第幾天 dayofyear	dy, y
日 day	dd, d
年的第幾週 week	wk, ww
星期的第幾天 weekday 預設1=日 2=一....7=六	dw
時 hour	hh
分 minute	mi, n
秒 second	ss, s
毫秒 millisecond	ms

--取得指定日期部分的字串DATENAME()--使用參數同上表datepar參數

SELECT DATENAME(month, GETDATE())

--四月 今天是四月所以傳回=四月,系統地區若為英語系傳回April

SELECT DATENAME(dw, GETDATE())

--星期六

--取得星期幾的單一中文字(日,一,二,三,四,五,六)

SELECT Right(datename(weekday,getdate()),1)

--六 今天是星期六所以傳回=六,系統地區語言必須為中文地區

SELECT CASE datepart(dw, getdate())

WHEN 1 THEN '日'
WHEN 2 THEN '一'
WHEN 3 THEN '二'
WHEN 4 THEN '三'
WHEN 5 THEN '四'
WHEN 6 THEN '五'
WHEN 7 THEN '六' END

--六 今天是星期六所以傳回=六

--設定一星期的開始日為哪天--影響datepart,datename使用參數weekday,dw的回傳值

SET DATEFIRST 7 --設定星期天為一星期的第1天 (預設為7)

select @@DATEFIRST

--7 (int)

Value	First day of the week is
1	Monday
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7 (default, U.S. English)	Sunday

--取得10天前或10天後日期(參數請看上面datepar參數表)

SELECT DATEADD(day, -10, '04/30/2008')

--2008-04-20 00:00:00.000

SELECT DATEADD(day, 10, '04/30/2008')

--2008-05-10 00:00:00.000

--計算兩時間差(參數請看上面datepar參數表)

SELECT DATEDIFF(day,GETDATE(),'04/30/2008')

--4 (int)

--轉換時間格式

不帶世紀數位 (yy)	帶世紀數位 (yyyy)	標準	輸入/輸出**
-	0 或 100 (*)	預設值	mon dd yyyy hh:miAM（或 PM）
1	101	美國	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	英國/法國	dd/mm/yy
4	104	德國	dd.mm.yy
5	105	義大利	dd-mm-yy
6	106	-	dd mon yy
7	107	-	mon dd, yy
8	108	-	hh:mm:ss
-	9 或 109 (*)	預設值 + 毫秒	mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10	110	美國	mm-dd-yy
11	111	日本	yy/mm/dd
12	112	ISO	yymmdd
-	13 或 113 (*)	歐洲預設值 + 毫秒	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 或 120 (*)	ODBC 規範	yyyy-mm-dd hh:mm:ss[.fff]
-	21 或 121 (*)	ODBC 規範（帶毫秒）	yyyy-mm-dd hh:mm:ss[.fff]
-	126(***)	ISO8601	yyyy-mm-dd Thh:mm:ss:mmm（不含空格）
-	130*	科威特	dd mon yyyy hh:mi:ss:mmmAM
-	131*	科威特	dd/mm/yy hh:mi:ss:mmmAM

select convert(char, getdate(), 100)

--04 26 2008 2:04AM mon dd yyyy hh:mmAM(PM)

select convert(char, getdate(), 101)

--04/26/2008 mm/dd/yyyy
select convert(char, getdate(), 102)

--2008.04.26 yyyy.mm.dd

select convert(char, getdate(), 103)

--26/04/2008 dd/mm/yyyy
select convert(char, getdate(), 104)

--26.04.2008 dd.mm.yyyy
select convert(char, getdate(), 105)

--26-04-2008 dd-mm-yyyy
select convert(char, getdate(), 106)

--26 04 2008 dd mon yyyy
select convert(char, getdate(), 107)

--04 26, 2008 mon dd, yyyy
select convert(char, getdate(), 108)

--02:04:53 hh:mm:ss
select convert(char, getdate(), 109)

--04 26 2008 2:04:53:583AM mon dd yyyy hh:mm:ss:mmmAM(PM)
select convert(char, getdate(), 110)

--04-26-2008 mm-dd-yyyy
select convert(char, getdate(), 111)

--2008/04/26 yyyy/mm/dd
select convert(char, getdate(), 112)

--20080426 yyyymmdd

select convert(char(6), getdate(), 112)

--200804 yyyymm

select convert(char, getdate(), 113)

--26 04 2008 02:04:53:583 dd mon yyyy hh:mm:ss:mmm
select convert(char, getdate(), 114)

--02:04:53:583 hh:mm:ss:mmm(24h)
select convert(char, getdate(), 120)

--2008-04-26 02:04:53 yyyy-mm-dd hh:mm:ss(24h)

select convert(char(7), getdate(), 120)

--2008-04 yyyy-mm
select convert(char, getdate(), 121)

--2008-04-26 02:04:53.583 yyyy-mm-dd hh:mm:ss.mmm

資料類型	範圍	精確度
datetime	1753 年 1 月 1 日到 9999 年 12 月 31 日	3.33 毫秒
smalldatetime	1900 年 1 月 1 日到 2079 年 6 月 6 日	1 分鐘

--取得這個小時開始的時間
SELECT DATEADD(hh,DATEDIFF(hh,0,getdate()),0)
--2008-04-26 06:00:00.000 (datetime)

--取得下個小時開始的時間
SELECT DATEADD(hh,1,DATEADD(hh,DATEDIFF(hh,0,getdate()),0))
--2008-04-26 07:00:00.000 (datetime)

--取得今天開始的時間(凌晨12點)
SELECT DATEADD(dd,DATEDIFF(dd,0,getdate()),0)
--2008-04-26 00:00:00.000 (datetime)

--取得本週的星期日,星期一(本週開始日為星期日)
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),-1)
--2008-04-20 00:00:00.000 (datetime)

SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
--2008-04-21 00:00:00.000 (datetime)

--取得上週五,上週六
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),-3)
--2008-04-18 00:00:00.000 (datetime)

SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),-2)
--2008-04-19 00:00:00.000 (datetime)

--取得本月的第一天
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
--2008-04-01 00:00:00.000 (datetime)

--取得下個月的第一天
SELECT dateadd(mm,1,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
--2008-05-01 00:00:00.000 (datetime)

--取得本月的最後一天
SELECT dateadd(dd,-1,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
--2008-04-30 00:00:00.000 (datetime)
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
--2008-04-30 23:59:59.997 (datetime)

--取得上個月的最後一天
SELECT dateadd(dd,-1,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
--2008-03-31 00:00:00.000 (datetime)
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
--2008-03-31 23:59:59.997 (datetime)

--取得本月天數
SELECT Day(dateadd(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))
--30 (int)

--取得本季的第一天
SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0)
--2008-04-01 00:00:00.000 (datetime)

--取得下一季的第一天
SELECT DATEADD(qq,DATEDIFF(qq,0,dateadd(mm,3,getdate())),0)
--2008-07-01 00:00:00.000 (datetime)

--取得本季天數
SELECT DATEDIFF(dd,DATEADD(qq,DATEDIFF(qq,0,getdate()),0),DATEADD(qq,DATEDIFF(qq,0,dateadd(mm,3,getdate())),0))
--91 (int)

--取得今年的第一天
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
--2008-01-01 00:00:00.000 (datetime)

--取得明年的第一天
SELECT DATEADD(yy,1,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
--2009-01-01 00:00:00.000 (datetime)

--取得去年的最後一天
SELECT dateadd(dd,-1,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
--2007-12-31 00:00:00.000 (datetime)
SELECT dateadd(ms,-2,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
--2007-12-31 23:59:59.997 (datetime)

轉摘至：http://openmyhand.com/cms/index.php?option=com_content&task=view&id=62&Itemid=39

直接用DataRead讀取DataTable

ADO.NET 2.0 的 DataTable可以直接從DataRead讀取

ADO.NET 1.1以前，要從資料庫中把要的資料讀取到DataTable需要經過以下的過程

1. 建立Connection
2. 建立Command
3. 使用DataAdapter
4. 將資料Fill到DataSet
5. 從DataSet中讀取第0個DataTable

大致上的程式如下

Dim ConnStr As String = "Data Source=localhost;Initial Catalog=Northwind;User ID=sa"

Using Conn As New SqlConnection(ConnStr)
    Dim Cmmd As New SqlCommand("SELECT * FROM Customer", Conn)
    Dim Da As New SqlDataAdapter(Cmmd)
    Dim Ds As New DataSet
    Da.Fill(Ds)
    Dim Dt As New DataTable
    Dt = Ds.Tables(0)

End Using

然而我們經常需要的就只是一個DataTable而已，卻需要透過DataAdapter的Fill到DataSet中，然後再從DataSet中取出，這樣的過程感覺上繞了一大圈

所以2.0的時候，就讓DataTable可以單獨的去Load一個DataReader，就能夠將相關的資料讀入一個DataTable中，相關的程式碼如下：

Dim ConnStr As String = "Data Source=localhost;Initial Catalog=Northwind;User ID=sa"

Using Conn As New SqlConnection(ConnStr)
    Dim Cmmd As New SqlCommand("SELECT * FROM Customer", Conn)
    'Dim Da As New SqlDataAdapter(Cmmd)

    'Dim Ds As New DataSet
    'Da.Fill(Ds)
    Dim Dt As New DataTable
    Conn.Open()         '這邊要特別注意，使用DataRead前，要先Conn.Open

    Dt.Load(Cmmd.ExecuteReader)
    Conn.Close()        'Conn用完記得關閉
End Using

有個地方要特別提醒，使用DataAdapter時候，Conn會自動的開啟、自動關閉；但是使用DataReader的話，使用前要記得先Open，使用後也要記得Close

關聯式代數 - 非原始運算子與 SQL 的對應關係

關聯式代數 - 非原始運算子與 SQL 的對應關係

非原始運算子：以下的三個運算子可由上述五個運算子推演出來，因此稱為「非原始運算子」，但是由於這幾個運算子的存在增加了理論推演的便利性，因此也就存在下來了。

6. Join：將兩個 Table 合併後，篩選出符合條件的資料，並挑選所需要的欄位。

從原始運算子的推演：

1. 將 Table T1 與 Table T2 做 Cartesian Product 運算，結果為 Table R1。
   
2. 對 Table R1 作 Select 運算就可以得到結果了。
   

將 Table T1 與 Table T2 以 EMP.DEPID 與 DEP.DEPID 做 Join 運算的結果如下，Table 中的資料為假設值。

Table ( EMP )


EMPID	EMPNAME	DEPID
1	趙一	a
2	錢二	a
3	孫三	b
4	李四	b


Table ( DEP )


DEPID	DEPNAME
a	技術部
b	業務部


Table ( EMP ) 與 Table ( DEP ) 以 EMP.DEPID 與 DEP.DEPID 做 Join 運算的結果


EMP.EMPID	EMP.EMPNAME	EMP.DEPID	DEP.DEPID	DEP.DEPNAME
1	趙一	a	a	技術部
2	錢二	a	a	技術部
3	孫三	b	b	業務部
4	李四	b	b	業務部


對應的 SQL 語法：


代碼:

Select *     From EMP, DEP     Where EMP.DEPID = DEP.DEPID

7. Intersection：從兩個 Table 中取出交集的資料。

從原始運算子的推演：以下推演 Table T1 以 Table T2 取代結果是一樣的。
1. 將 Table T1 與 Table T2 做 Difference 運算，結果為 Table R1。
   
2. 將 Table T1 與 Table R1 做 Difference 運算。
   

將 Table T1 與 Table T2 做 Intersection 運算的結果如下，Table 中的資料為假設值。

Table ( T1 )


C1	C2
c1a	c2a
c1b	c2b


Table ( T2 )


C1	C2
c1a	c2a
c1c	c2c


Table ( T1 ) 與 Table ( T2 ) 做 Difference 運算的結果


C1	C2
c1a	c2a


對應的 SQL 語法一：適用於提供 Temporary Table 的資料庫，以兩句 Select 語法完成。


代碼:

Select *     Into #R1     From T1     Where not exists (         Select *         From T2         Where T1.C1 = T2.C1 and T1.C2 = T2.C2) Select *     From T1     Where not exists (         Select *             From #R1             Where T1.C1 = #R1.C1 and T1.C2 = #R1.C2)


對應 SQL 語法二：適用於提供將 Select 的結果可以直接再使用的資料庫，其實就是將上述兩句 Select 語法結合成一句 Select 語法。


代碼:

Select *     From T1     Where not exists (         Select *             From (                 Select *                     From T1 T3                     Where not exists (                         Select *                             From T2                             Where T3.C1 = T2.C1 and T3.C2 = T2.C2)                 ) R1     Where T1.C1 = R1.C1 and T1.C2 = R1.C2)


對應 SQL 語法三：上述兩種語法都是依據理論上的推演法直接取得答案，實務上的做法就簡單多了。


代碼:

Select T1.C1, T1.C2     From T1, T2     Where T1.C1 = T2.C1 and T1.C2 = T2.C2

8. Divide：兩個 Table 間做除法運算，假設 Table T1 為被除表，Table T2 為除表，有一個大前提是 T1 一定要比 T2 的欄位多出一個以上，同時兩個 Table 間有相同屬性的欄位，運算的結果是 T1 必須包含所有的 T2 值。看不懂在說什麼，這是正常的，參考以下範例就可以瞭解了。

從原始運算子的推演：步驟很繁複就不在此說明了。

將 Table EMPSKILL 與 Table SKILL 做 Divide 運算的結果如下，希望從員工資料中取得符合所有技能的員工資料，Table 中的資料為假設值。

Table ( EMPSKILL )


EMPNAME	SKILL
趙一	PowerBuilder
趙一	Sybase
趙一	Java
錢二	PowerBuilder
錢二	Oracle
錢二	ASP


Table ( SKILL )


SKILL

PowerBuilder

Sybase


Table ( EMPSKILL ) 與 Table ( SKILL ) 以 SKILL 為基準找出符合所有條件的 EMPNAME 做 Divide 運算的結果


EMPNAME

趙一


對應的 SQL 語法：


代碼:

Select Distinct EMPNAME     From EMPSKILL T1     Where not exists (         Select *             From SKILL T2             Where not exists (                 Select *                     From EMPSKILL T3                     Where T3.EMPNAME = T1.EMPNAME and T3.SKILL = T2.SKILL ) )

這就是關聯式代數八大運算子與 SQL 的對應方式，其中最特別是 Divide 運算子，要如何善加利用就看實際上的需要了。

本則專題由飛達客工作室提供

關聯式代數 - 原始運算子與 SQL 的對應關係

關聯式資料庫的 SQL 語法是以關聯式代數作為基礎的。

以下就針對 E. F. Codd 這位大師對於關聯式代數的八個最原始的運算子，與 SQL 語法的對應關係作一個說明。

原始運算子：在這八個運算子裡面有五個運算子可稱為「原始運算子」，因為這些運算子無法以其他的運算子來定義他們的。

1. Select：從一個 Table 中取出符合條件的資料，也就是取得 Row 的資料，請注意這裡的 Select 是運算子並不是指 SQL 語法中的 Select。
   
   取出的資料如下圖 Table 反白部分所示：
   
   Table ( T1 )
   

   C1	C2	C3	C4

   
   對應的 SQL 語法：
   

   代碼:

   Select *     From T1     Where

   
   
2. Project：從一個 Table 中取出所需要的欄位，也就是取得 Column 的資料。
   
   取出的資料如下圖 Table 反白部分所示：
   
   Table ( T1 )
   

   C1	C2	C3	C4

   
   對應的 SQL 語法：請注意沒有條件篩選的設定。
   

   代碼:

   Select C1, C2     From T1

   
   
3. Cartesian Product：將兩個 Table 組合成一個 Table，這個是理論基礎上的運算子，在實務上是避免這種使用方式的。
   
   將 Table T1 與 Table T2 做 Cartesian Product 運算的結果如下，Table 中的資料為假設值。
   
   Table ( T1 )
   

   C11	C12
   c11a	c12a
   c11b	c12b
   c11c	c12c

   
   
   Table ( T2 )
   

   C21	C22
   c21a	c22a
   c21b	c22b

   
   
   Table ( T1 ) 與 Table ( T2 ) 做 Cartesian Product 運算的結果
   

   C11	C12	C21	C22
   c11a	c12a	c21a	c22a
   c11a	c12a	c21b	c22b
   c11b	c12b	c21a	c22a
   c11b	c12b	c21b	c22b
   c11c	c12c	c21a	c22a
   c11c	c12c	c21b	c22b

   
   對應的 SQL 語法：請注意沒有條件篩選的設定。
   

   代碼:

   Select *     From T1, T2

   
   
4. Union：從兩個 Table 中取出聯集的資料，重複的資料只取出一筆，請注意這裡的 Union 是運算子並不是指 SQL 語法中的 Union。
   
   將 Table T1 與 Table T2 做 Union 運算的結果如下，Table 中的資料為假設值。
   
   Table ( T1 )
   

   C1	C2
   c1a	c2a
   c1b	c2b

   
   
   Table ( T2 )
   

   C1	C2
   c1a	c2a
   c1c	c2c

   
   
   Table ( T1 ) 與 Table ( T2 ) 做 Union 運算的結果
   

   C1	C2
   c1a	c2a
   c1b	c2b
   c1c	c2c

   
   對應的 SQL 語法：要注意的是兩個 Table 間的對應欄位資料型態必須是相同或是可以轉換的。
   

   代碼:

   Select *     From T1 Union Select *     From T2

   
   
5. Difference：從一個 Table 中刪除另一個 Table 中有的資料，還是看圖示比較清楚。
   
   將 Table T1 與 Table T2 做 Difference 運算的結果如下，Table 中的資料為假設值。
   
   Table ( T1 )
   

   C1	C2
   c1a	c2a
   c1b	c2b

   
   
   Table ( T2 )
   

   C1	C2
   c1a	c2a
   c1c	c2c

   
   
   Table ( T1 ) 與 Table ( T2 ) 做 Difference 運算的結果
   

   C1	C2
   c1b	c2b

   
   對應的 SQL 語法：要注意的是兩個 Table 間的對應欄位資料型態必須是相同或是可以轉換的。
   

   代碼:

   Select *     From T1     Where not exists (         Select *             From T2             Where T1.C1 = T2.C1 and T1.C2 = T2.C2 )

這就是關聯式代數八大運算子中，五個原始運算子的部分，下次再為大家介紹其他三個非原始運算子的內容。

本則專題由飛達客工作室提供

簡介 Isolation Level

上次介紹到 Lock 的特性，主要是要了解資料庫對於資料保護的基本方法，在實務上的運用是要利用 Isolation Level 來做資料存取權限的設定。一般而言，Isolation Level 可以分成四個階層，層級高的功能會涵蓋層級低的功能。

層級	名稱	說明
0	read uncommitted	允許讀取尚在作用中的 transaction異動過的資料，也就是當資料正在被其他 transaction異動時，也可以讀取該資料。
1	read committed	只能讀取不在 transaction中異動的資料，也就是當資料正在被其他 transaction異動時，就不可以讀取該資料。
2	repeatable read	在同一個 transaction 中，同樣的 SQL語法都會得到同樣的結果，也就是當資料被讀取後，其他的transaction ，不可以 update 或 delete 該資料。
3	serialized read	在同一個 transaction 中，同樣的 SQL語法都會得到同樣的結果，也就是當資料被讀取後，其他的transaction ，不可以 insert、update 或 delete 該資料。

Isolation Level 0 ( read uncommitted )

T1	事件說明	T2
begin transaction	T1、T2 兩個 transaction 同時啟動	begin transaction
Update employee Set salary = salary * 1.05	T1 正在做調薪的動作
	T2 讀取 empid 為 A01 的薪資資料	Select salary From employee Where empid = 'A01'
	T2 處理結束	commit transaction
commit/ rollback transaction	T1 處理過程正常 ( commit )，處理過程中有問題，回復到未處理的狀態( rollback )

Isolation Level 1 ( read committed )

T3	事件說明	T4
begin transaction	T3、T4 兩個 transaction 同時啟動	begin transaction
Update employee Set salary = salary * 1.05	T3 正在做調薪的動作
	T4 讀取 empid 為 A01 的薪資資料	Select salary From employee Where empid = 'A01'
commit/ rollback transaction	T4 處理過程正常 ( commit )，處理過程中有問題，回復到未處理的狀態( rollback )
	T4 處理結束	commit transaction


當 T3 以 read committed 的方式讀取資料時，T4 會等到 T3 執行完成 ( commit/ rollback )，再讀取該筆資料，萬一 T4 等太久就會發生 Time Out 的現象。

Isolation Level 2 ( repeatable read )

T5	事件說明	T6
begin transaction	T5、T6 兩個 transaction 同時啟動	begin transaction
Select sum ( salary ) From emp	T5 計算所有的薪資總和
	T6 更新 empid 為 A01 的薪資	Update emp Set salary = salary * 1.10 Where empid = 'A01'
	T6 處理結束	commit transaction
Select sum ( salary ) From emp
commit transaction	T5 處理結束


這裡要特別注意的是，T5 重複讀取相同的資料，但是在處理的過程中，T6 更新了部分的資料，將導致 T5 無法取得相同的資料。

為避免這個問題， T5 在讀取資料時應該採用 Isolation Level 2 的方式，禁止使用 update、delete 的指令，才可以達到所需要的效果。

Isolation Level 3 ( serialized read )

T7	事件說明	T8
begin transaction	T7、T8 兩個 transaction 同時啟動	begin transaction
Select sum ( salary ) From emp	T7 計算所有的薪資總和
	T8 新增一筆 emp 資料	Insert Into emp ( ... ) Values ( ... )
	T8 處理結束	commit transaction
Select sum ( salary ) From emp
commit transaction	T7 處理結束


T7 延續 Isolation Level 2 所產生的問題，當 T8 insert 新的資料進來時，將導致 T7 無法取得相同的資料。

為避免這個問題， T7 在讀取資料時應該採用 Isolation Level 3 的方式，除了禁止使用 update、delete 的指令，同時也禁止使用 insert 的指令，才可以達到所需要的效果。

至於要使用哪一種 Isolation Level，必須視實際上的需要做設定，沒有絕對的處理方法。

本則專題由飛達客工作室提供

簡介 SQL 語法的三大類別

為了統一資料庫的基本語法，才訂定出標準的 SQL 語法，提供使用者一致的操作指令。一般而言，資料庫的語法 ( SQL ) 分為三大類別：

1. DDL ( Data Definition Language )：定義資料庫物件使用的語法，常看到的關鍵字有：
   
   
   • Create：建立資料庫的物件。
     
   • Alter：變更資料庫的物件。
     
   • Drop：刪除資料庫的物件。
     
   
   
2. DCL ( Data Control Language )：控制資料庫物件使用狀況的語法，常看到的關鍵字有：
   
   
   • Grant：賦予使用者使用物件的權限。
     
   • Revoke：取消使用者使用物件的權限。
     
   • Commit：Transaction 正常作業完成。
     
   • Rollback：Transaction 作業異常，異動的資料回復到 Transaction 開始的狀態。
     
   
   
3. DML ( Data Manipulation Language )：維護資料庫資料內容的語法，常看到的關鍵字有：
   
   
   • Insert：新增資料到 Table 中。
     
   • Update：更改 Table 中的資料。
     
   • Delete：刪除 Table 中的資料。
     
   • Select：選取資料庫中的資料。
     

當然各家資料庫廠商，除了標準語法之外還會增加一些獨特的功能，關於這個部份以後再陸續為大家介紹。

至於是否要使用資料庫獨特的功能，這純粹是見仁見智的問題。使用標準的語法，可以不受限於資料庫種類，隨時可以更換品牌；但是使用獨特的功能，通常可以有更好的執行效能。取捨之間就看要如何抉擇了。

本則專題由飛達客工作室提供