pCloud Partner Program

SQL Fiddle好用線上資料庫語法測試工具

程式技術/Database 2016/12/29 05:13
views: 71602 times
線上的前端有好用的 jsfiddle (https://jsfiddle.net/), CodePen (http://codepen.io/), 今天發現了一個很棒的資料庫語法測試工具, SQL Fiddle, 網址如下:

http://sqlfiddle.com/

這個好用的工具分為左右兩側, 分別是 DDL,DML 與 SQL 兩塊, 左側的 DDL, DML 可以直接定義資料表與資料內容, 當然, 還是用標準的資料庫語法. 右邊則是於 DDL, DML 執行之後, 才能使用的 SQL 語法查詢與測試用.

這樣不僅能快速定義資料表供應用外, 最重要的是可以測試 SQL 的查詢語法. 從左上角 Menu 可以看到支援的資料庫有:

MySQL 5.6
MySQL 5.5
Oracle 11g R2
PostgreSQL 9.3
SQLite (WebSQL)
SQLite (SQL.js)
MS SQL Server 2014
MS SQL Server 2008

8種不同資料庫與版本, 十分方便. 另外於 DDL/DML的部分, 也可以直接用上方 Text To DDL 進行設定, 更加方便, 讓不會使用 DDL/DML 的人員, 也能無痛建立資料表, 例如:

用戶插入圖片

table name: t_staff,
data:
name, age, title
Mary, 25, manager
Charlie, 18, Clerk
Fiona, 19, Clerk

(第一列為欄位名, 之後為資料)

就會生出以下語法:

用戶插入圖片

接下來, 就可以在右側進行查詢, 如:

select * from t_staff where age > 20

就可以查出對應的資料了, 十分方便, 尤其對於要查找資料時的不同資料庫語法間的應用, 都有很大的幫助. 另外已完成的結果, 還能快速分享, 對於查找問題, 有更多可以協同作業的可能, 上面的範例如下:

http://sqlfiddle.com/#!9/18a360/1
top

MS SQL Server將可在Linux環境下執行了

程式技術/Database 2016/03/09 06:52
views: 32853 times
這篇文章: http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/ 提到了, 即將在 linux 上執行 SQL Server 了.

這個微軟的當家資料庫服務器, 終於跨出了微軟的作業系統了, 有用過的應該都說好, 但到了 linux 下又是另一番風貌, 姑且不論效能如何, 微軟能走出這一步, 真的很值得肯定. 當然相信也能大幅拓展這個在微軟作業系統以外的其他平台市場, 讓 SQL Server 的優點發揮出來.

還蠻期待的.

繼續閱讀:
http://www.bnext.com.tw/article/view/id/38871
top

Mysql dump檔案分割(大文字檔案分割指令-csplit)

程式技術/Database 2016/01/29 07:18
views: 36968 times
在要還原 mysql 備份檔時, 有時整個 database 的 backup file 檔案很大, 要找出其中某一個區塊很花時間, 也需要用一個能打開大檔的文字編輯器才能找到, 有沒有更方便的方法, 找出其中某個 table 呢? 可以使用好用的文字檔分割工具 csplit (linux 指令)

舉例來說, 我們要找一個 table t_table1 的 dump 檔案內容, 而備份的 mysql database 檔案為 mysqlbackup.sql 我們使用指令如下:

csplit -f output_file mysqlbackup.sql "/Table structure for table `t_table1`/" "/Table structure for table/"

這樣一共會輸出三個檔案: output_file00, output_file01, output_file02, 其中的 output_file01 就會是我們要的 t_table1 這個資料表的 dump 資料了. 當然, 前後的檔案就分別是切出 01 後的 00, 02 了.

所以工作原理就是將 Table structure for table `t_table1` 與 Table structure for table 兩個分割點來進行檔案切割. 所以若是希望把所有的 table 檔分出來, 則可以使用以下指令:

csplit -f output_file mysqlbackup.sql "/Table structure for table/" "{10}"

其中的 10 就是要重覆做幾次(包含前面做第一次, 後面再做10次共11次), 重點是若有 11 個 table, 應該就可以分出 11 個檔案, 其中的 00 沒有用, 而 01~11 就會是那 11個 table 的 dump file, 所以若是不知 table 數量時, 後面的那個參數, 就不能寫超過總數的次數, 否則會一個檔案也不輸出.

另外可以使用 {*} 的方式如下:

csplit -f output_file mysqlbackup.sql "/Table structure for table/" "{*}"

會直接盡可能地使用重覆到無法使用, 也就是 table 有多少就會做多少次,  下面 PS 有說明我在 FreeBSD 8.2 執行時有錯誤, 不過在 CentOS 是可以正常執行的.

使用 csplit 可以快速的切割超級大檔案, 把要找的資料整理出來, 很方便又實用.

繼續閱讀:
http://www.computerhope.com/unix/ucsplit.htm

PS. 雖然上面這篇介紹有使用 {*} 來重覆作業, 不過我實際執行時, 會發生:
csplit: *}: bad repetition count 的錯誤訊息, 環境是 FreeBSD 8.2, 不過在另一台 CentOS 7.1 是可以正常執行的, 所以若是不能使用 {*} 請參考上面說明.


top

MySQL資料表不存在的資料就新增-存在就忽略(或異動)的語法

程式技術/Database 2015/07/17 09:27
views: 55008 times
在新增資料於資料表時, 有個需求, 是不存在的資料就新增, 存在的資料就不動作(或是要處理一些異動), 這樣的需求如何操作呢?

可以使用 INSERT IGNORE INTO [table] ..... 的語法,

若是要異動則使用

INSERT INTO [table]..... ON DUPLICATE KEY UPDATE col=expr .....

可以參考以下語法:
http://cain19811028.blogspot.tw/2015/01/mysql-insert-ignore-replace-on.html
https://mariadb.com/kb/en/mariadb/insert-on-duplicate-key-update/
top

Mysql的字串轉型問題

程式技術/Database 2015/03/26 06:45
views: 89790 times
Mysql 在字串欄位比對上, 有個奇妙的狀況. 當然, 在正常一般狀況下不太容易發生.

某欄位 f1 為 varchar 或 char , 而在 where 條件下, 使用了

where f1='sometext'

這樣沒有問題.

但若是使用了

where f1=0

會發生什麼事呢? 結果是全部成立.

根據 f1=0 來看, mysql 會先將 f1 欄位轉為數字, 而將字串轉為數字, 在 mysql 中, 會是 0 的結果, 導致會全部成立. 檢查以下語法:

SELECT cast( 'test string' AS SIGNED )

結果會是 0 , 也就解開了這個問題.

另外可以參考一下 cast 的語法:
http://dev.mysql.com/doc/refman/5.6/en/cast-functions.html

其 case ( data as type ) 中的 type, 可以為以下幾種:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

供各位在不小心, 使用了等於零在判斷條件時, 發生問題的參考.

top
TAG cast, MySQL

SQL Server資料表使用GUID欄位考量

程式技術/Database 2014/03/14 08:34
views: 651745 times
在資料表中, 使用 GUID做為clustered index時, 很容易因為是隨機產生的值, 而導致資料存放的離散, 可以利用這個函數來改善:

NEWSEQUENTIALID()

該函數是循序地建立 GUID, 也是唯一值, 但可以降低離散的狀況, 進而改善未能有效填滿及離散的問題.

當然, 若是 GUID欄位沒有使用在 cludtered index時, 則比較不需要擔心這個問題.

參考資料:
http://technet.microsoft.com/zh-tw/library/ms189786.aspx
top

SQL Join語法圖示

程式技術/Database 2013/12/30 07:59
views: 207382 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 11:26
views: 130902 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 12:23
views: 82435 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 11:23
views: 94076 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




Nextbit Robin 5.2吋六核心智慧型手機 Microsoft Office 365 中文家用版PKC (無光碟)
ASUS華碩 AC1900 雙頻無線路由器 RT-AC68U 美國 VORNADO 533 渦流空氣循環機 (黑色)
御茶園 每朝健康綠茶(650mlx24入) 每朝健康 雙纖綠茶(650mlx24入)


 Waiting...