藍色小惡魔 - 舒活札記

簡單的指令，卻常常有人需要找他 ^^...
 
DBCC CHECKIDENT('dbo.table_name', RESEED, 0)

其實有比較簡短的語法 就是 SQL 內定的 SP  寶寶知道 但是寶寶先不說



又臭又長的 只要把有中文說明的部份改成你要的對象



P.S. 完整內容及持續更新版本請連結至原文查看：http://www.imp.idv.tw/play/forum/viewthread?thread=990

以下是我整理過去工作經驗較常用的格式，簡化記憶和尋找，並非每個資料庫僅提供這些格式而已...就當作是工具收藏到書籤內，挺好用的！
MS SQL
小惡魔系統設計慣性用法
YYYY-MM-DD (2008-06-26)：CONVERT(CHAR(10), GETDATE(), 120)
HH:mm:SS (15:49:34)：CONVERT(CHAR(8), GETDATE(), 108)
MM DD YYYY (06 26 2008)：CONVERT(CHAR(10), GETDATE(), 100)
MM/DD/YYYY (06/26/2008)：CONVERT(CHAR(10), GETDATE(), 101)
MM.DD.YYYY (06.26.2008)：CONVERT(CHAR(10), GETDATE(), 102)
 
DD/MM/YYYY (14/03/2011)：CONVERT(CHAR(10), GETDATE(), 103)
DD.MM.YYYY (14.03.2011)：CONVERT(CHAR(10), GETDATE(), 104)
DD-MM-YYYY (14-03-2011)：CONVERT(CHAR(10), GETDATE(), 105)
DD MM YYYY (14 03 2011)：CONVERT(CHAR(10), GETDATE(), 106)
 
YYYYMMDD (20110314)：CONVERT(CHAR(8), GETDATE(), 112)
HH:MM:SS:sss (17:24:30:923)：CONVERT(CHAR(12), GETDATE(), 114)
HH:mm:SS (15:49:34)：CONVERT(CHAR(10), GETDATE(), 108)

---

Sybase
YYYYMMDD (20080626)：CONVERT(CHAR(10), GETDATE(), 112)
YYYY/MM/DD (2008/11/24)：CONVERT(CHAR(10), GETDATE(), 111)
MM-DD-YYYY (11-24-2008)：CONVERT(CHAR(10), GETDATE(), 110)
 
HH:mm:SS (15:49:34)：CONVERT(CHAR(10), GETDATE(), 108)
轉貼自:藍色小惡魔《SQL 日期時間轉換 (Convert Date Time to string)》
http://www.imp.idv.tw/play/forum/viewthread?thread=1372
MySQL
小惡魔系統設計慣性用法
select DATE_FORMAT(now(), '%Y-%m-%d');
select DATE_FORMAT(now(), '%T');
use DATE_FORMAT(date,format)
format 格式如下：
%Y 年 YYYY ex:2011
%y 年 yy ex:11
%a 星期 英文縮寫名稱 ex:Fri
%b 月 英文縮寫名稱 ex:Feb
%M 月 英文全名 ex:February
%c 月 數值 ex:2 (二月)
%m 月 數值(00-12)
%D 日 數值 加英文首碼 ex:3rd, 20th
%d 日 數值 補零 ex:(00-31)
%e 日 數值 補零 ex:(0-31)
%j 一年的第幾天 (001-366)
%r 時間 小時12（hh:mm:ss AM 或 PM）ex:10:05:54 PM
%T 時間 小時24 (hh:mm:ss)

---

%H 小時24 補零 (00-23)
%h 小時12 補零 (01-12)
%k 小時24 (0-23)
%l 小時12 (1-12)
%i 分鐘 補零 (00-59)
%S 秒(00-59)
%s 秒(00-59)
%f 微秒
%p AM 或 PM
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，與 %X 使用
%v 周 (01-53) 星期一是一周的第一天，與 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，與 %V 使用
%x 年，其中的星期一是周的第一天，4 位，與 %v 使用

這種 以己之矛攻己之盾的方法 真的是很高招，目前小惡魔網站應該還沒有被盯上的價值，不過先留下武功秘笈以備不時之需。
重點說明： 



 


DELETE FROM `nucleus_plugin_tb` WHERE block=1 and url IN ( SELECT url FROM `nucleus_plugin_tb` where block=1 group by url having count(*) > 10 )

drop table ICMS30Bak.dbo.JobBak_V2_繳款記錄
select * into ICMS30Bak.dbo.JobBak_V2_繳款記錄 from confer.dbo.V2_繳款記錄
-- 從債協專用更新最新繳款記錄 - vSQL1
insert into confer.dbo.V2_繳款記錄 (cust_idn)
select distinct cust_idn
from confer.dbo.V2_繳款記錄_債協專用 where cust_idn not in (select cust_idn from confer.dbo.V2_繳款記錄)
declare @vSQL1 nvarchar(1000), @vSQL2 nvarchar(1000)
declare @ColumnName varchar(50), @RunDate datetime, @MaxDate datetime, @DateStr char(8)
-- 取得 Table 中以年月設計的欄位最大值 (考慮未來維護的人只知道增加欄位而未修改程式)
select @MaxDate = convert(char(8), convert(int, max(substring(col.name, 8, 4)) + '25') + 19110000)
from sysobjects obj join syscolumns col on obj.id = col.id
where obj.name = 'v2_繳款記錄' and col.name like 'INCOME_%'
set @vSQL1 = 'update V2_繳款記錄 set FIRST_PAY_DATE = V1.FIRST_PAY_DATE'
set @vSQL2 = 'update V2_繳款記錄 set first_in_date = dateadd(day, 15, first_pay_date) where (0=1'
set @RunDate = @MaxDate
while @RunDate >= '2006-02-01' begin
set @ColumnName = 'INCOME_' + convert(char(4), (datepart(year, @RunDate) - 1911) * 100 + datepart(month, @RunDate))
set @vSQL1 = @vSQL1 + ', ' + @ColumnName + ' = V1.' + @ColumnName
set @vSQL2 = @vSQL2 + ' or upper(' + @ColumnName + ') = ''Y'''
set @RunDate = dateadd(month, -1, @RunDate)
continue end
set @vSQL1 = @vSQL1 + ' from V2_繳款記錄 V2 join V2_繳款記錄_債協專用 V1 on V1.CUST_IDN = V2.CUST_IDN'
print @vSQL1
execute sp_executesql @vSQL1
update V2_繳款記錄 set last_in_date = null, first_in_date = null, times = 0
set @vSQL2 = @vSQL2 + ')'
print @vSQL2
execute sp_executesql @vSQL2
set @RunDate = @MaxDate
while @RunDate > '2006-02-01' begin
set @DateStr = convert(char(8), @RunDate, 112)
set @vSQL1 = 'update V2_繳款記錄 set times = times + 1,'
+ ' last_in_date = case when last_in_date is null then ''' + @DateStr + ''' else last_in_date end'
+ ' from V2_繳款記錄 where income_' + convert(char(4), convert(int, substring(@DateStr, 1, 6)) - 191100)
+ ' = ''Y'''
print @vSQL1
execute sp_executesql @vSQL1
set @RunDate = dateadd(month, -1, @RunDate)
continue end

declare @vSQL1 nvarchar(1000), @vSQL2 nvarchar(500), @ColumnName varchar(50), @RunDate datetime
-- 取得 Table 中以年月設計的欄位最大值 (考慮未來維護的人只知道增加欄位而未修改程式)
select @RunDate = convert(char(8), convert(int, max(substring(col.name, 8, 4)) + '01') + 19110000)
from sysobjects obj join syscolumns col on obj.id = col.id
where obj.name = 'v2_繳款記錄' and col.name like 'INCOME_%'
set @vSQL1 = 'update V2_繳款記錄 set FIRST_PAY_DATE = V1.FIRST_PAY_DATE'
while @RunDate >= '2006-02-01' begin
set @ColumnName = 'INCOME_' + convert(char(4), (datepart(year, @RunDate) - 1911) * 100 + datepart(month, @RunDate))
set @vSQL1 = @vSQL1 + ', ' + @ColumnName + ' = V1.' + @ColumnName
set @RunDate = dateadd(month, -1, @RunDate)
continue end
set @vSQL1 = @vSQL1 + ' from V2_繳款記錄 V2'
+ ' join V2_繳款記錄_債協專用 V1 on V1.CUST_IDN = V2.CUST_IDN'
execute sp_executesql @vSQL1

 


declare @vSQL nvarchar(500), @ColumnName varchar(50), @RunDate datetime, @DateStr char(8)
set @RunDate = '2007-12-25'
while @RunDate > '2006-02-01' begin
set @DateStr = convert(char(8), @RunDate, 112)
print @DateStr + ' 處理中......'
set @vSQL = 'update V2_繳款記錄 set last_in_date = ''' + @DateStr + ''''
+ ' from V2_繳款記錄 where income_' + convert(char(4), convert(int, substring(@DateStr, 1, 6)) - 191100)
+ ' = ''Y'' and last_in_date is null'
print @vSQL
execute sp_executesql @vSQL
set @RunDate = dateadd(month, -1, @RunDate)
continue end

今天實在對本公司為了讓 User 直接強姦資料庫而用 Excel 白痴法開的 Table
造成本人懶得寫程式 而接手過來貼得拉拉長的 SQL 很不爽
直接上討論區只能用 "欄位" 兩個字當關鍵字 (不得以的 其他太直接的關鍵字找不到文章)
爬文爬了一個小時眼睛有點扭傷 終於找到答案
先提供一個簡單的範例讓大家吸收 - 動態 Table 名稱

剛剛有個  寫了個補 0 的 SQL 問我怎麼寫不出來
主要當然是他沒把數字轉字串所以 SQL 很雞婆會自動轉回數字
其次是他的技巧是 先判斷數字的長度 然後再不斷用 case-when 補字串 '000...'
換句話說 如果最大可能補 10 個 0 他的 SQL 就要寫 10 組 case-when
經過本大師的指導 只要以下這行就搞定了...

 


substring('0000000000', 1, 10 - len(a)) + a

MS SQL 教學 

MIS 或 DBA 經常會設計定時抓取資料的 Schedule SQL Job，但因為上游資料來源(或程式)延遲或失敗，過了 Job 執行時間也許才差幾秒幾分鐘或可容許的延遲時間內來源資料變已產生完畢了，可是定時抓取的 SQL Job 早已停工，此時 User 來上工了發現系統有問題通知 MIS 處理，這時也只能癡癡地等上游資料跑完後，MIS 再手動執行 SQL JOB... 或者更慘的是，假日被急摳來處理...!!
MIS 轄下受連累的系統因為資料不夠新鮮以致使用單位無法作業，只好吃下問題單 ="=... 接著安排人跟催上遊甚麼時候補好資料庫。
為了能安心休假只好下海找解套......
咱們工程師只會寫程式解決問題所以 SQL 很弱，常常去碰資料庫只為了重新啟動 JOB 當然覺得痛苦。
人在屋簷下不得不低頭，到了 user site 古人 MIS 已經搞了一堆 SQL 跑來跑去，只好配合在這個模式下找出不讓系統開發人員痛苦的解決方案！
其實用個簡單的 MS SQL 涵式，就可以解決這個惱人的問題了！
(轉貼時請加註來源)
引用自:藍色小惡魔《自動重新啟動資料庫工作排程》
http://www.imp.idv.tw/play/forum/viewthread?thread=2191
SQL 2000 : sp_update_jobschedule
SQL 2005, 2008 : sp_update_schedule (建議) or sp_update_jobschedule (為相容 2000 而殘留的) 
只要兩個簡單的要素即可高枕無憂
1. 要能判斷是否需重新執行的方法。
2. 要建立一個執行一次的 job schedule。
你的 JOB 應該有兩個 Schedule 如下，第一個是你正常應該要啟動的、一個是當有問題的時候需要被啟動的(disable)，這個 shcedule 在以下範例我命名為 reschedule。
接著你要依資料庫版本查出重新啟動 schedule 的 key，use msdb 後參考以下方法找出 schedule_id 或 job_id。 
SQL 2005, 2008 使用 scuedule_id 即可，可透過管理工具的作業屬性中的排程清單找到 schedule_id，或使用和 SQL 2000 相同指令也可，以上圖為例，ID 506 就是 @schedule_id。 
SQL 2000 只能用 job_id，則需呼叫 exec sp_help_job @job_name = '[JOB NAME]'，以上圖為例 @job_name = 'ADM_LETTERS_COUNT (2009-11-20)'。取得 @job_id = '499235D9-4EB1-45C5-A66D-1D4D53BA1C98'
然後我以如果資料更新失敗，則一個小時後重新執行一次為例，如下： 
-- 主要資料處理 SQL (建議規劃防重複執行判斷) 
INSERT ADM_LETTERS_COUNT SELECT ... 
-- 第一要素：設計判斷資料更新失敗的邏輯