pCloud Partner Program

SQL Join語法圖示

程式技術/Database 2013/12/30 14:59
views: 272735 times
這篇主要目的是介紹集合, 使用 SQL Join 指令時的語法與集合的關係, 資料可以參考這篇: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins


(以上圖片引用自: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins )

在資料庫查詢時, 使用 Join 語法是用來查詢多個資料表間的相關資料狀況用的, 區分為 inner join 及 outer join 兩種, 不指定時預設為 inner join, 也就是在兩邊都存在的資料, 使用 inner join 查詢, 例如 select a.sid from table_a a inner join table_b b on a.sid=b.sid 時, 會出現同時在 a 與 b 的 sid 都存在的資料.

例如 table_a 存在 1, 2, 3, 5, 6, 7, 10, 11 共八筆資料, 而 table_b 存在 2, 3, 4, 5, 6, 7, 8 共七筆資料時, 使用 inner join 則取出 2, 3, 5, 6, 7 共五筆資料.

用戶插入圖片


而 left join (也就是 left outer join), 則是取出以左邊為主, 不管右邊是否存在的資料, (不存在的資料會用 null 值補齊欄位), 如:

select a.sid from table_a a left join table_b b on a.sid=b.sid

則會取出 1, 2, 3, 4, 5, 7, 10 , 11 共八筆資料,

用戶插入圖片


而 right join (也就是 right outer join), 則是取出以右邊為主, 不管左邊是否存在的資料, 如:

select a.sid from table_a a right join table_b b on a.sid=b.sid

則會取出 2, 3, 4, 5, 6, 7, 8 共七筆資料.
用戶插入圖片

另外還有 full join (也就是 full outer join)則是兩邊都取出來, 如:

select a.sid, b.sid from table_a a full join table_b b on a.sid=b.sid

則會取出兩個表全部的內容, 1, 2, 3, 5, 6, 7, 10, 11, 4, 8 共十筆資料,

用戶插入圖片


其他若是需要做其他的集合, 只需要再加上 where 中的 is null 就可以產生差集這樣的方式.

繼續閱讀:
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
http://blog.wu-boy.com/2009/01/mysqlleft-right-inner-outer-join-%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95/





top
TAG join, SQL

使用CTE進行遞迴(Recursive)查詢

程式技術/Database 2013/07/23 17:26
views: 174869 times
從 MS SQL 2005 起, 支援了 CTE (Common Table Expression) 語法, 可以參考之前的文章: http://diary.tw/tim/42 .

今天要來介紹的是有關應用在遞迴方面的查詢, 利用這種查詢, 可以很容易地將資料展開, 例如像是組織圖, 或是像分類項含有子分類這樣的樹狀資料, 當然, 簡單一點的像是累加也是一樣的, 先來介紹 CTE 用在累加的語法上.



看好, 在括號內的第一個 SELECT 1, 1 的部分, 就是原始值, 而後面的 SELECT .. FROM cte 則是開始自己對自己查詢, 最後的 num < 10 是他的 boundary condition, 也就是只做到 num < 10.

結果如下:
用戶插入圖片

是的, 結果就是那個 1 + 2 + 3 + ... + 10 = 55 .

再來看看其他的用法, 像是分類含有子分類的資料, 先來準備一些資料:


來組合吧, 使用CTE:


結果如下:
用戶插入圖片

是不是很清楚看出來各分類的位階(LEVEL)狀況? 十分方便好用的語法. 再來看看如何展開成一行資料, 也就是各自分類結構合成一筆結果, 語法如下:


結果如下:
用戶插入圖片

最後面的那個欄位就是想要呈現出來的結果, 說穿了, 就是一直累加上去, 只是是字串的累加(DATA), 不是數字的累加, 另外也多了一個欄位用來表現原本自己的CID(ORICID), 這樣看起來也就更清楚完整, 利用這樣的方式來表現分類結構, 非常好用呢!

另外記得字串累加的部分, 使用 NVARCHAR(MAX) 以避免發現 CTE 和累加的欄位不一致的問題, 這是要特別注意的地方.

無法對齊欄位的錯誤訊息為:
Types don't match between the anchor and the recursive part in column "xx" of recursive query "xx".
可以參考這篇解答: http://stackoverflow.com/questions/1838276/cte-error-types-dont-match-between-the-anchor-and-the-recursive-part

結論, CTE的遞迴語法, 其實也就是自己再對自己查詢, 只要把握好起始條件, 還有遞迴的終止條件, 就可以很順利的產出你要的結果. 大家可以多加利用這個好用的語法, Let's CTE!!
top

資料庫整筆ROW比對

程式技術/Database 2012/11/05 19:23
views: 121483 times
在資料庫操作時, 有時會需要比對資料表的內容是否一致, 或是某些資料的某些欄位是否一樣, 若是使用 SQL 指令來逐行比對, 會是個比較麻煩的操作方式, 使用 CHECKSUM 函數, 可以方便地將待比對的資料(多欄亦可), 計算出 hash index (int)後, 再進行比對, 舉例如下:

兩個 table 資料:

table TBL_TMP1
fno fdata fcreatetime
1 'data 1' '2012/1/1'
2 'data 2' '2012/2/3'
3 'data 3' '2012/2/4'
4 'data 4' '2012/4/1'

table TBL_TMP2
fno fdata fcreatetime
1 'data 001' '2012/1/1'
2 'data 2' '2012/2/12'
3 'data 3' '2012/2/4'
4 'data 4' '2012/4/1'

若要找出資料不同的資料列, 可以使用如下指令:
SELECT * FROM
(SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP1) a INNER JOIN
(SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP2) b
ON a.fno = b.fno AND a.CHK != b.CHK

其中用了 CHECKSUM(*) 是將 table 中的各欄位合併起來計算 hash index, 再將兩個 table 含有 hash index (欄位名 CHK)拿來比對, 列出不同的資料, 結果如下:
1    data 1    2012-01-01 00:00:00.000    803471792    1    data 001    2012-01-01 00:00:00.000    1877235137
2    data 2    2012-02-03 00:00:00.000    266601110    2    data 2    2012-02-12 00:00:00.000    266601103


是很方便的工具函數.

附上建立上面 sample code 的 create table 及 insert data 指令:
CREATE TABLE TBL_TMP2 (fno int, fdata varchar(20), fcreatetime datetime)

INSERT INTO TBL_TMP1 values (1, 'data 1', '2012/1/1')
INSERT INTO TBL_TMP1 values (2, 'data 2', '2012/2/3')
INSERT INTO TBL_TMP1 values (3, 'data 3', '2012/2/4')
INSERT INTO TBL_TMP1 values (4, 'data 4', '2012/4/1')

INSERT INTO TBL_TMP2 values (1, 'data 001', '2012/1/1')
INSERT INTO TBL_TMP2 values (2, 'data 2', '2012/2/12')
INSERT INTO TBL_TMP2 values (3, 'data 3', '2012/2/4')
INSERT INTO TBL_TMP2 values (4, 'data 4', '2012/4/1')

大家可以試看看, 若是只要比對前兩欄, 則指令如下:
SELECT * FROM
(SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP1) a INNER JOIN
(SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP2) b
ON a.fno = b.fno AND a.CHK != b.CHK
結果如下, 就是只有第一筆資料的前兩欄不同而已:
1    data 1    2012-01-01 00:00:00.000    -1291957785    1    data 001    2012-01-01 00:00:00.000    -1493283680
如同我們想要的結果一樣. 這樣一來可以方便活用這個 CHECKSUM 函數, 十分方便好用!

相關資料:
http://msdn.microsoft.com/en-us/library/ms189788.aspx

資料上寫到是 SQL Server 2005 以上版本可用, 不過其實在 SQL Server 2000 也有這個指令:
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx

top

[MSSQL]文字民國日期轉西元日期

程式技術/Database 2012/03/06 18:23
views: 138677 times
剛朋友問到有關存在 MSSQL 中的文字資料是民國日期, 例如 67/3/12 這樣的格式, 要轉出西元日期, 其實還蠻單純的, 只需要找出第一個 '/' 在什麼位置, 就很容易了, 查找 '/' 的函數使用 charindex, 如下:

declare @a varchar(20)
select @a = '67/6/7'
select charindex('/', @a)

這樣取出的值為 3, 也就是第 3個字元, 而下一步就是取出年的部份, 如下:

declare @a varchar(20)
select @a = '67/6/7'
select substring(@a, 1, charindex('/', @a)-1)

這樣取出的值為 67, 於是要轉出西元年其實就是先轉成 int 後, 加上 1911 再轉回字串後, 加回原來的月/日的部分, 如下:

declare @a varchar(20)
select @a = '67/6/7'
select convert(varchar, convert(int, substring(@a, 1, charindex('/', @a)-1))+1911)+ substring(@a, charindex('/', @a), 10)

其中最後一個 substring 中的取出長度 hardcode 寫 10 是一個小技巧, 不用真的算出取出長度, 而固定給一個大於此字串的長度, 就可以取出完整字串, 所以上面的結果就會是 1978/6/7, 而 substring 最後一個參數可以參考線上說明:

http://msdn.microsoft.com/en-us/library/ms187748.aspx

其中的 length_expression 參數的說明, 其中有提到
If the sum of start_expression and length_expression is greater than the number of characters in value_expression, the whole value expression beginning at start_expression is returned.
如此一來, 便可以一次在 sql command 中將原存在資料庫中的民國年日期轉出為西元年, 例如 table:

userid  birthday
---------------
1          67/3/12
2          79/2/11

語法如下:

select userid, birthday, convert(varchar, convert(int, substring(birthday, 1, charindex('/', birthday)-1))+1911)+ substring(birthday, charindex('/', birthday), 10) as birthday2 from table1


會取出:

userid  birthday  birthday2
------------------------
1          67/3/12  1978/3/12
2          79/2/11  1990/2/11

給大家參考!
top

找出SQL Server中的identity欄位值

程式技術/Database 2011/11/29 14:57
views: 166119 times
一般在 SQL Server 中, 若要找某 table 中的 identity 最後值(最大值或目前值), 可以使用以下指令:

dbcc checkident('table_name', NORESEED)

可以參考之前的這篇文章: SQL Server的Identity欄位使用/複製/重設 - http://diary.tw/tim/65

不過, 若是 table 很多, 又想一次性的將各 table 中的 identity 欄位最後值找出, 可以利用系統資料表: sys.identity_columns (2005, 2008, 2008R2都有) 來查找, 配合 sys.objects 表, 可以一次將 table, column, 最後值(last_value) 查找出來, 如下:

select b.name, a.name, a.last_value from sys.identity_columns a inner join sys.objects b on a.object_id=b.object_id

這樣可以利用一個指令就將該資料庫中的所有資料表含有 identity 欄位的最後值, 若是只需要使用者自行定義的 table (不要含系統表), 可以多加上 b.type='U' 來進行過濾.

參考資料:
http://technet.microsoft.com/zh-tw/library/ms176057.aspx
http://msdn.microsoft.com/en-us/library/ms187334.aspx
top

MySQL的on duplicate key update語法

程式技術/Database 2011/10/13 17:08
views: 120293 times
很有意思的一個語法, 不過可要小心使用, 在 mysql 5.0 版起, 可以在 insert 時, 指定若重覆 unique key 時, 則使用 update 語法, 這個有點像是在新增資料時, 若不存在則新增, 存在則 update 的方式.

來個例子:


這時候, 會有一筆, 1,1,1,1 和 2,3,2,2 的資料產生, 如此一來, 可以在重覆 unique key 時, 用來做為更新的條件, 由於 unique key 只會出現一次在對應 table 上, 所以可以用來做為更新的條件值, 而 primary key 也是 unique key, 所以發生在 primary key 時是一樣的狀況.

不過若是新增的資料, 發生多筆 unique key 重覆時(指不同欄位), 該 update 會只更新一筆, 這是在程式上要避免發生的, 以免有資料更新錯誤的問題. 詳情可以參考官方網站資料: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

這種有趣的語法, 其實用的機會不是太多, 不過多學習了解很有意思.
top

FreeBSD上mysql的效能追蹤

程式技術/Database 2011/08/08 16:02
views: 111951 times
最近因為有台主機的 mysql cpu 異常變高許多, 所以要來進行查詢問題所在.

因為 mysql 沒有像 mssql 的 sql profiler 那麼方便的工具, 所以利用 mysql 本身的 log 來進行, 也就是 slow query log 這個功能, 步驟如下:

1. 先將 /etc/my.cnf 中的 [mysqld] 內多加入下面資料:

log-slow-queries = /var/log/slow-query.log #slow query記錄檔的位置
long_query_time = 2 #query執行超過2秒時才記錄


2. 重啟 mysql 服務, 指令如下:

/usr/local/etc/rc.d/mysql-server restart


3. 執行一陣子後, 就可以看看該 log 檔內的 query , 接下來就是針對這些 query 來調整效能

以上是在 FreeBSD 環境下的作法. (其他環境其實也類似)

其實執行時間長不一定是效率不好, 不過若是常常發生的查詢是需要長時間的, 就有改善的必要, 簡單地說, 就是若一個查詢需要 5秒, 但一天跑不到 10次, 那根本不用管他, 不過若一個查詢需要 0.02 秒, 但一天要用到數萬次, 即使從 0.02 改善到 0.015 就會有很明顯的效能改善, 所以要看發生的頻率及所花費執行的時間, 平衡來看.

另外, 若是該 log 沒有產出, 記得權限要給對, 因為是 mysql service account 去執行寫入的動作, 就算沒有任何 log 也會有 mysql 啟動的資訊, 不會沒有任何產出的 log.

參考資料:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://blog.lansea-chu.com/index.php/archives/238
http://homeserver.com.tw/mysql/mysql%E7%9A%84%E6%9F%A5%E8%A9%A2%E6%99%82%E9%96%93log-slow-queries/
http://ezkuan.blogspot.com/2010/05/freebsd-mysql.html
top

MySQL的Log

程式技術/Database 2011/05/19 18:19
views: 134964 times
在 MySQL 運行時, 若想要了解執行時期, 對資料庫下達指令的狀況, 可以利用 my.ini 中的參數來達成, 在 [mysqld] 中多加一行 log=路徑/檔名, 再重起服務即可.

這個目的對於在追蹤應用程式執行指令很有幫助, 不過若是繁忙系統下, 很可能會有大量的資料, 而難以閱讀, 建議使用在開發環境, 再執行對應的 application 記錄下來的 log 就會單純許多, 以方便追蹤.

在 MSSQL 中可以利用 SQL Profiler 來達成, 不過 MySQL 無法過濾那麼多資訊, 只能整個記錄下來. 記得這些操作對於系統效能都會有一定的衝擊, 開起來之後, 記得要關掉, 否則將會影響效能.

另外還有一個好用的參數, 就是一樣在 [mysqld] 中, 加一行 log_slow_queries=路徑/檔名, 這個將會讓系統記錄執行時間較長的 query, 也方便做效能調校及追蹤使用, 預設是執行時間超過 10秒的會被記錄下來, 參數是 long_query_time 可以參考:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time


這兩種方式分別做完整記錄及長時查詢記錄, 對於想要了解資料庫執行時期的問題及效能調校, 都可以提供不少幫忙.

相關閱讀:
http://blog.wabow.com/archives/54
top

使用TABLESAMPLE方式取樣

程式技術/Database 2011/04/22 21:30
views: 102776 times
先弄清楚, 這個 TABLESAMPLE 是用來做以"頁(page)"為單位的取樣方式, 和所謂的隨機不同, 不過在超大型資料表上, 若要快速地取出一部分的資料, 可以應用這樣的方式來達成.

支援 MS SQL 2005 以上的版本, 使用方式很單純, 只需要在查詢指令的 table 之後加上 TABLESAMPLE 子句即可, 如下:

TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]

範例如下:
使用 percent百分比:
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;

或是指定筆數:
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;

要特別注意的是, 由於是以 page 為單位, 所以取出的資料只是約略的 page 內容物組合而成的, 不過若是對於超大內容的 table 來說, 比較如下兩個指令:

SELECT TOP 100 * FROM TABLE1 ORDER BY NEWID()

SELECT TOP 100 * FROM TABLE1 TABLESAMPLE(1 PERCENT) ORDER BY NEWID()

效率上會有很大的差異(當然是後者會快很多), 不過若是有 WHERE 條件下, 就不適合使用 TABLESAMPLE 了, 因為是無法以指定的條件來取出資料, 但仍可以下達 WHERE 子句, 是指取出的 TABLESAMPLE 內容, 再過濾的資料, 如下:

SELECT * FROM TABLE1 TABLESAMPLE(1 PERCENT) WHERE FIELD1 > 5000

記得是先取出 sample data 後再過濾的行為, 所以極有可能沒有資料發生, 使用上要特別留意.

官方資料: http://technet.microsoft.com/zh-tw/library/ms189108.aspx
繼續閱讀: http://sharedderrick.blogspot.com/2011/04/tablesample.html
top

MySQL的Text欄位長度

程式技術/Database 2011/01/13 15:35
views: 116269 times
MySQL 中, text 屬性的欄位, 用來存放文字的資料格式, 共區分為以下 4種, tinytext, text, mediumtext, longtext, 分別的長度大小為 2^8, 2^16, 2^24, 2^32 bytes 這麼大.

簡單地說, 分別就是 256bytes, 64kbytes, 16Mbytes, 4Gbytes, 在 MySQL 中的 text 有大小不同的限制呢, 所以一般若是只開 text 的話, 最大也只能存到 64kbytes 而已.

參考資料:
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
http://plog.longwin.com.tw/programming/2009/10/20/mysql-text-field-type-length-limit-2009

top




DJI OM 4 折疊式手機雲台 手持穩定器(先創公司貨)
【SONY 索尼】2.1 聲道單件式環繞音響(HT-X8500)
ASUS 華碩 RT-AC68U 雙頻AC1900 無線網路分享器
TEAM十銓 L5 Lite 3D 1TB 2.5吋 SSD固態硬碟
統一 番茄汁(6入/組)
UNITEK TYPE-C 轉VGA/HDMI/PD充電/HUB 鋁合金多功能轉接器
亞果元素 CASA Hub VH1 Type-C 轉 VGA - HDMI二合一顯示轉接器
realme XT (8G/128G) 6.4吋6400萬畫素 四鏡頭鷹眼猛獸
DJI Mavic Mini 暢飛套裝
全新原彩Apple MacBook Air 13吋/i5/8G/256G
DJI Mavic Air 2 全能套裝 [先創公司貨]預購-送記憶卡
Patriot美商博帝 BURST 960G 2.5吋 SSD固態硬碟


 Waiting...