分類: Dababase

T-SQL 常用字串函數

老王

substring(欄位名稱,開始字元,取得字元字數)=>取欄位從第幾字元開始取幾位字元

left(欄位名稱,取得字元字數)=>取欄位左邊開始幾位字元

right(欄位名稱,取得字元字數)=>取欄位右邊開始幾位字元

ltrim(欄位名稱)=>去除欄位左邊空白

rtrim(欄位名稱)=>去除欄位右邊空白

replace(欄位名稱,要被變更的字,變更後的字)=>變更特定字元

T-SQL 日期轉換說明 , 相當實用

老王

來源網站

CONVERT
將某種資料類型的運算式顯式轉換為另一種資料類型。由於某些需求經常用到取日期格式的不同.現以下可在
SQL Server中　將日期格式化.

SQL Server 支援使用科威特演算法的阿拉伯樣式中的資料格式。

在表中，左側的兩列表示將 datetime 或 smalldatetime 轉換為字元資料的 style 值。給 style 值加 100，可獲得包括世紀數位的四位年份 (yyyy)。

不帶世紀數位 (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

*    預設值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始終返回世紀數位 (yyyy)。
** 當轉換為 datetime 時輸入；當轉換為字元資料時輸出。
*** 專門用於 XML。對於從 datetime 或 smalldatetime 到 character 資料的轉換，輸出格式如表中所示。對於從 float、money 或 smallmoney 到 character 資料的轉換，輸出等同於 style 2。對於從 real 到character 資料的轉換，輸出等同於 style 1。

clip_image001

重要 預設情況下，SQL Server 根據截止年份 2049 解釋兩位元數字的年份。即，兩位元數字的年份 49 被解釋為 2049，而兩位元數字的年份 50 被解釋為 1950。許多用戶端應用程式（例如那些基於 OLE 自動化物件的用戶端應用程式）都使用 2030 作為截止年份。SQL Server 提供一個配置選項（"兩位元數位的截止年份"），藉以更改 SQL Server 所使用的截止年份並對日期進行一致性處理。然而最安全的辦法是指定四位元數字年份。

當從 smalldatetime 轉換為字元資料時，包含秒或毫秒的樣式將在這些位置上顯示零。當從 datetime 或smalldatetime 值進行轉換時，可以通過使用適當的 char 或 varchar 資料類型長度來截斷不需要的日期部分。

下表顯示了從 float 或 real 轉換為字元資料時的 style 值。

值

輸出

0（預設值）

最大為 6 位數。根據需要使用科學記數法。

1

始終為 8 位值。始終使用科學記數法。

2

始終為 16 位值。始終使用科學記數法。

在下表中，左列表示從 money 或 smallmoney 轉換為字元資料時的 style 值。

值

輸出

0（預設值）

小數點左側每三位元數字之間不以逗號分隔，小數點右側取兩位數，例如 4235.98。

1

小數點左側每三位元數字之間以逗號分隔，小數點右側取兩位數，例如 3,510.92。

2

小數點左側每三位元數字之間不以逗號分隔，小數點右側取四位數，例如 4235.9819。

使用 CONVERT：

MSSQL CTE 遞迴查詢結構

老王

原文 BlueShop

遞迴 CTE 結構至少必須包含一個錨點成員與一個遞迴成員。下列虛擬程式碼顯示含有單一錨點成員與單一遞迴成員的簡單遞迴 CTE 所具備的元件。

遞迴執行的語意如下：
將 CTE 運算式分割為錨點成員與遞迴成員。
執行錨點成員以建立第一個引動過程或基底結果集 (T0)。
執行遞迴成員，以 Ti 做為輸入，而以 Ti+1 做為輸出。
重複步驟 3，直到傳回空的結果集為止。
傳回結果集。這是 T0 至 Tn 的 UNION ALL。

版上範例 :

所以錨點成員是
select up_dno,dno,1 as lvl from dbo_dsales where dno='A'
也就是　
　　　up_dno,dno,lvl
　　　　null　Ａ　　１
那遞迴成員是
select a.up_dno,a.dno,lvl+1 from dbo_dsales a,directsales b where a.up_dno=b.dno
也就是
在第一次跑時,b.dno = A
得到
　　　up_dno,dno,lvl
　　　　Ａ　　　Ｂ　　２
　　　　Ａ　　　Ｃ　　２
在第二次跑時,b.dno = B,C
得到
　　　up_dno,dno,lvl
　　　　Ｂ　　　Ｄ　　３
　　　　Ｂ　　　Ｅ　　３
　　　　Ｃ　　　Ｆ　　３
　　　　Ｃ　　　Ｇ　　３
　　　　Ｃ　　　Ｈ　　３
在第三次跑時,b.dno = D,E,F,G,H
得到
　　　up_dno,dno,lvl
　　　　Ｄ　　　Ｉ　　４
　　　　Ｄ　　　Ｊ　　４
　　　　Ｆ　　　Ｋ　　４
　　　　Ｇ　　　Ｌ　　４
在第四次跑時,b.dno = I,J,K,L
得到
　　　up_dno,dno,lvl
　　　　Ｊ　　　Ｍ　　５
　　　　Ｌ　　　Ｎ　　５
在第五次跑時,b.dno = M,N
得到 無符合資料(傳回空的結果集)
所以遞迴停止
所以最後得到結果為
　　　up_dno,dno,lvl
　　　　null　　Ａ　　１
　　　　Ａ　　　Ｂ　　２
　　　　Ａ　　　Ｃ　　２
　　　　Ｂ　　　Ｄ　　３
　　　　Ｂ　　　Ｅ　　３
　　　　Ｃ　　　Ｆ　　３
　　　　Ｃ　　　Ｇ　　３
　　　　Ｃ　　　Ｈ　　３
　　　　Ｄ　　　Ｉ　　４
　　　　Ｄ　　　Ｊ　　４
　　　　Ｆ　　　Ｋ　　４
　　　　Ｇ　　　Ｌ　　４
　　　　Ｊ　　　Ｍ　　５
　　　　Ｌ　　　Ｎ　　５

個人實作:

資料庫結構大概是:

sys_dep_id(部門ID) sys_dep_up(上層部門ID)
201 101
301 201
302 201
401 301
402 301
403 301
501 401

結果為: 401,402,403,501

mysql 基本指令收集

老王

每次都很久才會處理一次 資料庫的問題 , 每次就要找一次指令 !!
想想還是開個版把有用過的指令都記下來比下實際 !

===== 設定 mysql root密碼  =====
>mysqladmin -u root password '1234'
因為這樣會在伺服器上會留下history ,我個人會使用:
1.登入mysql:
>mysql -u root

2.選擇任一個資料庫:
mysql>use mysql;

3.設定密碼
mysql> update mysql.user set password=PASSWORD(”輸入你的密碼) where User=’root’;

4.存儲後離開
mysql> flush privileges;
mysql> quit

===== 建立資料庫與資料表=====

mysql>CREATE DATABASE 資料庫名稱;

===== 備份 及 復原 資料庫 =====

1.備分:
mysqldump -u root -p 資料庫名稱 --opt >file.sql

2.復原:
mysql  -u root -p 資料庫名稱 <file.sql

===== 新增使用者 =====

可使用GRANT指令，同時新增使用者與給予權限的工作
mysql> GRANT ALL PRIVILEGES ON * . * TO ‘帳號’@'%’ IDENTIFIED BY ‘密碼’;
mysql> FLUSH PRIVILEGES;
mysql> exit

=====  忘記mysql root 解決方法  ===== 

1. 關閉 MySQL 伺服器。
  >service mysqld stop

2. 用以下指令啟動 MySQL，以跳過檢查權限的資料表
>mysqld_safe --skip-grant-tables &

3. 現在己經可以用空密碼進入 MySQL
>mysql -u root

4. 進入 MySQL 後執行以下指令更改 mysql root 密碼：
> update mysql.user set password=PASSWORD(”new_password”) where User=’root’;
> flush privileges;
> quit

5. 最後只需重新啟動 MySQL，便可以用新設定的密碼進入了。

MySQL 的備份及還原

老王 1 Comment

要備份 MySQL 資料庫主要分為兩個方法，一是將資料庫目錄完整備份：二是使用 MySQL 內建的 mysqldump 程式。

備份資料庫目錄
MySQL 預設的儲存目錄在 /var/lib/mysql 內容，底下會有以資料庫名稱的目錄，例如 mydb 目錄便應該是 mydb 資料庫的資料。

如果 MySQL 正在運行，請先停止 MySQL，原因是可能會有資料未完全寫入，而 MySQL 會 lock 在使用中的 DB 檔案。

 

  1. /etc/rc.d/init.d/mysqld stop
  2. cd /var/lib/mysql/
  3. tar zxcf mydb_backup.tgz mydb
  4. /etc/rc.d/init.d/mysqld start

 

以上指令會先停止 MySQL，然後把 mydb 資料庫製作一個 taz 檔的備份，並儲存到 mydb_backup.tgz。
在使用以上指令時，請根據個別系統的設定作出修改。

好了，以上就麼 3 句指令就完成備份了，如果不幸的事情發生了，資料庫發生錯誤而要復原資料，可使用以下指令：

 

  1. /etc/rc.d/init.d/mysqld stop
  2. cd /var/lib/mysql/
  3. mv mydb mydb_error
  4. tar zxvf mydb_backup.tgz
  5. /etc/rc.d/init.d/mysqld start

 

以上指令是先把 /var/lib/mysql/mydb 移到 /var/lib/mysql/mydb_error，然後將原先製作的備份檔解壓到 /var/lib/mysql/mydb。

mysqldump
雖然以上方法十分簡單，但有一個問題存在，那就是在備份及復原時均需停止 MySQL 的運作，這樣對於實際應用十分不便。再者，這樣備份出來的檔案，如果在相同版本的 MySQL Server 應該沒有問題，但移到版本不同的 Server 則不一定可以成功復原。

因為有以上的問題，MySQL 已經內建了備份工具，它就是 mysqldump。
mysqldump 的備份方法是將資料庫內的每個資料表結構及每筆資料產生 SQL 語句，然後存到文字檔。而且它可以自訂每一個資料表一個檔案，以及將資料表結構及資料分開儲存，以下是使用例子：

 

  1. mysqldump --user=root -p mydb > /backup/mydb.sql

 

以上指令會使用 mysqldump 將 mydb 備份到 /backup/mydb.sql，在輸入指令後，需要輸入 MySQL 的 root 密碼。

至於復原資料同樣簡單，只要一句指令便完成：

 

  1. mysqldump --user=root -p mydb < /backup/mydb.sql

 

以上指令會將 /backup/mydb.sql 備份檔復原到 mydb 裡面。如果你的 MySQL Server 不止一個資料庫，希望可以一次過將所有資料庫備份起來，可以寫一個簡單的 shell script 完成，又或者使用以下指令：

 

  1. mysqldump --user=root -p --all-databases > /backup/mysql.sql

 

這個 --all-databases 代表所有資料庫，這樣 mysqldump 便會將所有資料庫備份到 /backup/mysql.sql。

以上文章轉貼自bake