SQLite : Python開發環境下，使用SQLite的參數化查詢，可以避免因包含 ' 單引號(apostrophe)的字串，所引起的錯誤

1. 使用Python的 % 串接字串，很好用，很方便，但如果字串中包含單引號 (apostrophe, ASCII : 39)，又沒有預先處理好，就會造成SQL執行錯誤，例如：
   zSQL="""INSERT INTO logs
                   (logA,logB,logC,logD,logE,logF) values (
                   '%s','%s',%d,%d,%d,'%s');
           """
   SQL=zSQL%(strA,strB,strC,strD,strE,strF)
   
2. 如果直接改成參數化的語法，這樣就可以自動排除單引號 (apostrophe, ASCII : 39)所引起的錯誤。
oConn.execute("INSERT INTO logs (logA,logB,logC,logD,logE,logF) values (?,?,?,?,?,?), (strA,strB,strC,strD,strE,strF))"

1. 參考資料：https://zh.wikipedia.org/wiki/參數化查詢

SQLite3 : 欄位串接的運算子是 || (double pipe)

我一直有一個作法，如果資料內容在SQL查詢的過程中，能夠直接準備好所需要的資料項目內容，那我就會儘量在下SQL語法時，想辦法一併取得資料：
透過資料的串接，達到合併資料欄位的內容的需求，產生的新欄位，下接下來的程式取用資料，會相對較簡單。
SQLite : 欄位串接的運算子是 || (double pipe)
SQL語法範例：
SELECT (stockType||'_'||stockID||'.tw') as stockParm , *  FROM StdToNote

使用Python 處理 SQLite3 跨資料庫檔案間的資料表資料複製

1. 作法：
   建立兩個connection，分別對應來源及目標資料庫檔案
   一次讀取來源的所有資料，再逐筆寫入目標資料表內
2. 程式碼：
   import sqlite3
   def dict_factory(cursor, row):
       d = {}
       for idx, col in enumerate(cursor.description):
           d[col[0]] = row[idx]
       return d
   ##來源資料庫
   dbName="LineNotifierStock-20190731.db3"
   oConnSource=sqlite3.connect(dbName)
   ##目標資料庫
   dbName="StockNotifier.db3"
   oConnTarget=sqlite3.connect(dbName)
   ##作法：一次讀取來源的所有資料，再逐筆寫入目標資料表內
   zSQL="Select * from StdToNote
   oConnSource.row_factory = dict_factory
   cSource=oConnSource.cursor()
   cSource.execute(zSQL)
   oSource=cSource.fetchall()
   print(oSource)
   for aSource in oSource :
       zSQL="""INSERT INTO StdToNote
               (stockID,stockName,doNoteOpen,doNoteRate,
               doNotePrice,doNoteAccVol,doNoteUpDown,noteType,
               refPrice,rateHigh,rateLow,priceHigh,priceLow,memo)
               values('%s','%s','%s','%s','%s','%s','%s','%s',%f,%f,%f,%f,%f,'%s')
       """
       zSQL=zSQL%(aSource['stockID'],aSource['stockName'],aSource['isNoteOpen'], \
               aSource['isNoteRate'],aSource['isNotePrice'],aSource['isNoteAccVol'],
               aSource['isNoteUpDown'],aSource['noteType'],aSource['refPrice'], \
               aSource['rateHigh'],aSource['rateLow'],aSource['priceHigh'], \
               aSource['priceLow'],aSource['memo'])
       oConnTarget.execute(zSQL)
       oConnTarget.commit()
   oConnSource.close()
   oConnTarget.close()
   print("Data Copied Ready!!!")
   
3. 查看執行結果：
   

從政府資料開放平台(open data)，取得上市上櫃公司基本資料(CSV)，使用Python將資料寫入SQLite3資料庫

1. 政府資料開放平台 ( https://data.gov.tw/ )
• 上市公司基本資料(18419) -  https://data.gov.tw/dataset/18419  
• 上櫃股票基本資料(25036) -  https://data.gov.tw/dataset/25036  
• 實際資料提供者：證交所的公開資訊觀測站 https://mopsfin.twse.com.tw/  
• 資料格式只提供 CSV ，更新頻率→每日 
• 取得資料的連結點：
• http://mopsfin.twse.com.tw/opendata/t187ap03_L.csv  
• http://mopsfin.twse.com.tw/opendata/t187ap03_O.csv  
2. 程式碼：
   ## Table : Stocks (stockType,stockID,stockAbbr,industryType,releaseDate)
   ## stockType : tse->上市，otc->上櫃
   ## 上市公司基本資料(18419) - 政府資料開放平台 https://data.gov.tw/dataset/18419
   ## 上櫃股票基本資料(25036) - 政府資料開放平台 https://data.gov.tw/dataset/25036
   ## 主要欄位說明:
   ## 出表日期[0]、公司代號[1]、公司名稱[2]、公司簡稱[3]、外國企業註冊地國[4]、產業別[5]、住址、
   ## 營利事業統一編號、董事長、總經理、發言人、發言人職稱、代理發言人、總機電話、成立日期、上市日期、
   ## 普通股每股面額、實收資本額、私募股數、特別股、編制財務報表類型、股票過戶機構、過戶電話、過戶地址、
   ## 簽證會計師事務所、簽證會計師1、簽證會計師2、英文簡稱、英文通訊地址、傳真機號碼、電子郵件信箱、網址
   zSQL="""CREATE TABLE IF NOT EXISTS `Stocks` (
           `stockType`     VARCHAR(4)  NOT NULL,
           `stockID`       VARCHAR(8)  NOT NULL,
           `stockAbbr`     VARCHAR(30) NOT NULL,
           `industryType`  VARCHAR(30),
           `releaseDate`   VARCHAR(12),
           PRIMARY KEY (`stockID`)
   );
   """
   oConn.execute(zSQL)
   oConn.commit()
   zSQL="SELECT * FROM Stocks "
   cStocks=oConn.execute(zSQL)
   rStock=cStocks.fetchone()
   if rStock==None:
       ##寫入上市公司資料
       zCsvUrl="http://mopsfin.twse.com.tw/opendata/t187ap03_L.csv"
       oHTML=requests.get(zCsvUrl)
       oHTML.encoding='utf-8'
       oList=oHTML.text.split('\r\n')
       for i in range(1,len(oList)-1):
           aList=oList[i].replace('"','').split(',')
           zSQL="INSERT INTO Stocks (stockType,stockID,stockAbbr,industryType,releaseDate) values ('tse','%s','%s','%s','%s'); "
           zSQL=zSQL%(aList[1],aList[3],aList[5],aList[0])
           oConn.execute(zSQL)
           oConn.commit()
       ##寫入上櫃公司資料
       zCsvUrl="http://mopsfin.twse.com.tw/opendata/t187ap03_O.csv"
       oHTML=requests.get(zCsvUrl)
       oHTML.encoding='utf-8'
       oList=oHTML.text.split('\r\n')
       for i in range(1,len(oList)-1):
           aList=oList[i].replace('"','').split(',')
           zSQL="INSERT INTO Stocks (stockType,stockID,stockAbbr,industryType,releaseDate) values ('otc','%s','%s','%s','%s'); "
           zSQL=zSQL%(aList[1],aList[3],aList[5],aList[0])
           oConn.execute(zSQL)
           oConn.commit()
   
3. 透過Python寫入的資料：
   

SQL : INSERT INTO ... SELECT ... WHERE NOT EXISTS ( SELECT ...) 。如果資料不存在就插入一筆資料表。

目標：如果查詢的資料不存在，就插入一筆資料。
想法：如果按照CREATE TABLE IF NOT EXISTS的思考方式，會想要使用 IF NOT EXISTS的作法，以SELECT查詢資料作為條件判斷，可以用 WHERE NOT EXISTS的作法。當然還有其他的方法可以達到需求。

SQL參考語法：

INSERT INTO Config (cfgID, cfgDesc, srhTLD, srhNUM, srhSTOP, srhPAUSE, memo)
SELECT '0000','Inserted by program','com',5,5,120,''
WHERE NOT EXIST ( SELECT 1 FROM Config WHERE cfgID='0000' )

用SQLiteStudio查詢執行結果：

SQL / SQLite3 : CREATE TABLE IF NOT EXISTS 。如果資料表不存在就新增建立這個資料表。

目標：如果資料表不存在就新增建立這個資料表。
SQL參考語法：

在SQLite，也可以使用VARCHAR

CREATE TABLE IF NOT EXISTS `Config` (

    `cfgID`        VARCHAR(04) DEFAULT('0000') NOT NULL,

    `cfgDesc`    VARCHAR(50) NOT NULL,

    `...` ....,

    PRIMARY KEY(`cfgID`)

);

用SQLiteStudio查詢執行結果：

SQLite3的管理工具程式：sqlite_tools, SQLiteStudio, SQLiteBrowser

SQLite可以經由程式(Python, Java, C#, C, C++ ...)操作執行來產生檔案資料庫，操作使用資料庫內的資料表、資料...等，但一定會遇到，需要直接先建立、修改、刪除、修改資料庫、資料表資料欄位、資料內容...的狀況，這時候有個工具程式可以用的話，可以避免很多麻煩。

1. SQLite官網的command-line 管理工具，不需安裝
   https://www.sqlite.org/download.html
   sqlite-tools-win32-x86-3290000.zip (目前的版本 3.29.0)，包含sqlite3.exe, sqldiff.exe, sqlite3_analyzer.exe，其中sqlite3.exe，使用.help指令，可以查看相關操作指令
   
2. SQLiteStudio圖形介面、功能強大，portable免安裝
   網址：https://sqlitestudio.pl/index.rvt
   https://sqlitestudio.pl/index.rvt?act=download
   https://sqlitestudio.pl/files/sqlitestudio3/complete/win32/SQLiteStudio-3.2.1.zip
   
   可以透過圖形介面的程式管理(新增、修改、刪除、查詢)Structure / Data / Constraints / Indexes / Triggers / DDL
3. DB Browser for SQLite
   官網：https://sqlitebrowser.org/
   下載：https://sqlitebrowser.org/dl/
   

在樹莓派 RaspBerry Pi 下，用Python當開發工具，用SQLite3儲存資料，用SQLiteBrowser協助資料管理

工作環境：

• RaspBerry Pi 3 Mode B+
• 作業系統：版本代號為 Buster (Version:June 2019 / Release date:2019-06-20 / Kernel version:4.19)

在Python測試連接SQLite3的使用：

• Buster版本的Python環境，SQLite3已經是Ready的狀態
• 用以下的程式測試，在Python使用SQLite是OK的
  使用Python sqlite3模組建立資料庫連線，如果所指定的資料庫不存在，Python便會自動建立產生該資料庫檔案
  
  

在樹莓派安裝sqlitebrowser

• sudo apt-get update
• sudo apt-get upgrade
• sudo apt-get install sqlite3
• sudo apt-get install sqlitebrowser
  
  

參考資料：

• Powerful open source SQLite manager for Raspberry Pi  

T-SQL : 日期時間函數

(Transact-SQL)日期和時間資料類型與函數
資料來源：https://msdn.microsoft.com/zh-tw/library/ms186724(v=sql.120).aspx

1. @@DATEFIRST  傳回 SET DATEFIRST 之工作階段的目前值。SET DATEFIRST 會指定每週第一天。  U.S.  English 預設值是 7，也就是星期日。
2. CURRENT_TIMESTAMP  傳回目前資料庫的系統時間戳記，當做 datetime 值 (不含資料庫時區位移)。  這個值衍生自正在執行 SQL Server 執行個體之電腦的作業系統。
3. DATEADD  傳回指定的 date，並將指定的 number 間隔 (帶正負號的整數) 加入至該 date 的指定 datepart。
   語法：DATEADD (datepart , number , date )
   datepart 及 縮寫：
   • year  / yy ,yyyy  
   • quarter  / qq ,q  
   • month  /  mm ,m  
   • dayofyear / dy ,y  
   • day  /  dd ,d  
   • week  /  wk ,ww  
   • weekday  /  dw ,w  
   • hour  /  hh  
   • minute  /  mi ,n  
   • second  /  ss ,s  
   • millisecond  /  ms  
   • microsecond  /  mcs  
   • nanosecond  /  ns
   number  
   這是可解析成 int (要加入至 date 的 datepart) 的運算式。
   使用者自訂的變數有效。  如果您指定了含有十進位小數的值，該小數就會被截斷而且不會四捨五入。
   date
   這是可解析成 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的運算式。
   
   
4. DATEDIFF  傳回跨越指定 startdate 與 enddate 之指定 datepart 界限的計數 (帶正負號的整數)。
   語法：DATEDIFF ( datepart , startdate , enddate )
5. DATEFROMPARTS   傳回指定之年、月、日的 date 值。
   語法：DATEFROMPARTS ( year, month, day )
6. DATENAME   傳回代表指定 date 之指定 datepart 的字元字串。
   語法：DATENAME ( datepart , date )
   SELECT DATENAME(datepart,'2007-10-30 12:15:32.1234567 +05:10');
   datepart  /  傳回值
   year, yyyy, yy  /  2007
   quarter, qq, q  /  4
   month, mm, m  / 十月
   dayofyear, dy, y  / 303
   day, dd, d  /  30
   week, wk, ww  /  44
   weekday, dw  /  星期二
   hour, hh  /  12
   minute, n  /  15
   second, ss, s  /  32
   millisecond, ms  /  123
   microsecond, mcs  /  123456
   nanosecond, ns  /  123456700
   TZoffset, tz  / 310
   ISO_WEEK, ISOWK, ISOWW  /  44
7. DATEPART   傳回代表指定 date 之指定 datepart 的整數
   語法：DATEPART ( datepart , date )
8. DATETIME2FROMPARTS   以指定的精確度傳回指定日期與時間的 datetime2 值
   語法：DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
9. DATETIMEFROMPARTS   傳回指定日期和時間的 datetime 值。
   語法：DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, seconds, milliseconds )
10. DATETIMEOFFSETFROMPARTS   傳回包含指定時差和精確度之指定日期和時間的 datetimeoffset 值。
    語法：DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
11. DAY   傳回代表指定 date 之日期 (月份的日期) 的整數。
    語法：DAY ( date )
12. EOMONTH   以選擇性位移，傳回包含指定日期的當月最後一天。
    語法：EOMONTH ( start_date [, month_to_add ] )
13. GETDATE   傳回目前資料庫的系統時間戳記，當做 datetime 值 (不含資料庫時區位移)。  這個值衍生自正在執行 SQL Server 執行個體之電腦的作業系統。
    語法：GETDATE ( )
14. GETUTCDATE   傳回目前資料庫的系統時間戳記，當做 datetime 值。  不包含資料庫時區位移。 這個值代表目前 UTC 時間 (國際標準時間)。 這個值衍生自正在執行 SQL Server 執行個體之電腦的作業系統
    語法：GETUTCDATE()
15. ISDATE   如果 expression 是有效的 date、time 或 datetime 值，則傳回 1，否則傳回 0。  如果 expression 是 datetime2 值，ISDATE 會傳回 0。datetime 資料範圍為 1753-01-01 到 9999-12-31，而 date 資料範圍是 0001-01-01 到 9999-12-31。
    語法：ISDATE ( expression )
16. MONTH   傳回代表指定 date 之月份的整數
    語法：MONTH ( date )
17. SMALLDATETIMEFROMPARTS   傳回指定日期和時間的 smalldatetime 值
    語法：SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
18. SWITCHOFFSET   傳回 datetimeoffset 值，而且此值從已儲存的時區位移變更為指定的新時區位移
    語法：SWITCHOFFSET ( DATETIMEOFFSET, time_zone )
19. SYSDATETIME   傳回 datetime2(7) 值，此值包含 SQL Server 執行個體執行所在之電腦的日期和時間
    語法：SYSDATETIME ( )
20. SYSDATETIMEOFFSET   傳回 datetimeoffset(7) 值，此值包含 SQL Server 執行個體執行所在之電腦的日期和時間。 時區位移包括在內
    語法：SYSDATETIMEOFFSET ( )
21. SYSUTCDATETIME   傳回 datetime2 值，此值包含 SQL Server 執行個體執行所在之電腦的日期和時間。  日期和時間是以國際標準時間 (Coordinated Universal Time，UTC) 傳回。 毫秒的有效位數規格範圍介於 1 至 7 位數之間。 預設有效位數是 7 位數
    語法：SYSUTCDATETIME ( )
22. TIMEFROMPARTS   傳回包含指定精確度之指定時間的 time 值
    語法：TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
23. TODATETIMEOFFSET  傳回 datetimeoffset 值，此值是從 datetime2 運算式轉譯而來
    語法：TODATETIMEOFFSET ( expression , time_zone )
24. YEAR   傳回代表指定 date 之年份的整數
    語法：YEAR ( date )

T-SQL：如何系統日期時間

SELECT   GETDATE() AS GetDate,
                  CURRENT_TIMESTAMP AS CurrentTimeStamp,
                  { fn NOW() } AS FnNow,
                  SYSDATETIME() AS SysDateTime,
                  sysutcdatetime() AS SysUtcDateTime,
                  getutcdate() AS GetUtcDate,
                  SYSDATETIMEOFFSET() AS SysDateTimeOffSet

回傳值：
2016-03-05 17:19:26.610                  (GeDate)
2016-03-05 17:19:26.610                  (CurrentTimeStamp)(符合AnsiSQL標準的函數)
2016-03-05 17:19:26.610                  (FnNow)({fn NOW()}符合ODBC規範的函數)
2016-03-05 17:19:26.6060596          (SysDateTime)
2016-03-05 09:19:26.6060596          (SysUtcDateTime)
2016-03-05 09:19:26.600                  (GetUtcDate)
2016-03-05 17:19:26.6060596 +08:00    (SysDateTimeOffSet)

其他透過ODBC函數取得的日期時間：
SELECT {fn NOW()} AS FnNow,
               {fn CURRENT_TIMESTAMP()} AS CurrentTimeStamp,
               {fn CURRENT_DATE()}as CurrentDate,
       {fn CURRENT_TIME()} as CurrentTime



取得目前的系統日期：
SELECT Convert(date, GETDATE()) AS GetDate,
           Convert(date, CURRENT_TIMESTAMP) AS CurrentTimeStamp,
  Convert(date, { fn NOW() }) AS FnNow,
  Convert(date, SYSDATETIME()) AS SysDateTime,
  Convert(date, sysutcdatetime()) AS SysUtcDateTime,
  Convert(date, getutcdate()) AS GetUtcDate,
  Convert(date, SYSDATETIMEOFFSET()) AS SysDateTimeOffSet


取得目前的系統時間：
SELECT Convert(time, GETDATE()) AS GetDate,
           Convert(time, CURRENT_TIMESTAMP) AS CurrentTimeStamp,
  Convert(time, { fn NOW() }) AS FnNow,
  Convert(time, SYSDATETIME()) AS SysDateTime,
  Convert(time, sysutcdatetime()) AS SysUtcDateTime,
  Convert(time, getutcdate()) AS GetUtcDate,
  Convert(time, SYSDATETIMEOFFSET()) AS SysDateTimeOffSet

使用MS-SQL server management studio更新插入ASCII 13(Carriage Return) 及 10(Line Feed)

1. 要直接用編輯的方式將CR-LF(ASCII 13 / 10, Carriage Return / Line Feed)，插入或更新要資料表，是有一些難度的...
   參考：ASCII Codes Table → http://ascii.cl/ 
2. 最直接的想法是，用replace函數進行置換現有的資料，例：
   update TextContentTable
   set TextContent=REPLACE(TextContent,'<br>',char(13)+char(10))
   where No=1
3. 但replacec函數，不適用在text資料型態 ...
4. 調整一下作法 ...
   declare @strContent varchar(1024)
   set @strContent='001<br>abc<br>xyz'
   set @strContent=REPLACE(@strContent,'<br>',char(13)+char(10))
   update TextContentTable set TextContent=@strContent
   where No=1
5. 終於可以把<br> 置換為 CR-LF 了 ...

把字串型態的欄位，轉換成數字，再進行排序

很多情況下，都會將數字型態的資料欄位，建立成字串型態的資料欄位，但透過程式取用資料時，卻可能發生莫名其妙的錯誤...

上述的例子，使用SQL查詢：

SELECT          TestNo, TestMemo

FROM              TestTable

ORDER BY   TestNo DESC

本來預期TestNo出現的排列順序是：11,10,9,8,7,6,5,4,3,2,1

但實際出現的排列順序卻是：9,8,7,6,5,4,3,2,11,10,1

簡單的修正一下SQL，就可以得到預期想要的效果了...

SELECT          TestNo, TestMemo

FROM              TestTable

ORDER BY   CAST(TestNo AS integer) DESC

SQL語法：如何檢查資料表的多個資料欄位的組合，是否重複了？

如果遇到資料表的資料筆數已經成長到N萬筆了，才突然想到忘了設定資料表的主索引鍵(Primary Key)，除了資料存取的速度會明顯的變慢，系統也會變得怪怪的，最近接了一個這樣的系統，正展開救援...
我第一個想到的是問題：如何確定我想要補設的幾個欄位組合起來，在現有的資料中，是否可以成為一個Primary Key？
可以用以下的SQL指令測試一下：
假設col001, col002, col003是tblTEST資料表中，想組合起來當Primary Key的三個欄位，如果這三個欄位的值組合起來，出現的次數會超過一次，就要再想想辦法了...
select count(*) as nCount, col001, col002, col003 from tblTEST
group by  col001, col002, col003
having count(*) > 1

T-SQL函數：用CONVERT轉換日期時間的顯示格式

CONVERT的語法： CONVERT(資料型態[(長度)], 運算式[,格式])

YY西元年	YYYY西元年	依據的標準	顯示的格式
-	0 or 100	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 or 113	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130	Hijri	dd mon yyyy hh:mi:ss:mmmAM
-	131	Hijri	dd/mm/yy hh:mi:ss:mmmAM

怎麼沒有 yyyy-mm-dd ? 該如何轉換？可以用REPLACE函數幫忙。
select replace(convert(varchar(10), GETDATE(), 111), '/', '-') as 'yyyy-mm-dd'

那 yy-mm-dd  該如何轉換？可以用SUBSTRING函數幫忙。
select SUBSTRING(convert(varchar(10), GETDATE(), 120), 3, 8) as 'yy-mm-dd'

怎麼轉換出月份名稱的全名？例如：Month yyyy
select DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) as varchar(4)) as 'Month yyyy'

怎麼轉換成民國年制 yyymmdd ？
select cast((cast(CONVERT(varchar(8),getdate(),112) as int) - 19110000) as varchar(7)) as 'yyymmdd'

怎麼顯示成 yyyy/mm ?
select Left(CONVERT(VARCHAR(8), GETDATE(), 111), 7) as 'yyyy/mm'


參考資料來源：
CAST and CONVERT (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187928(v=sql.105).aspx

SQL語法：在Update命令裡面使用Select的結果，作為更新資料的條件

有一些情況要更新某一資料表的資料，選取所要更新資料的條件來源，並不是在同一資料表內，要如何讓這個更新的動作在單一SQL語法下完成呢？簡單的說，就是在Update命命中，使用次查詢(子查詢)，來進行資料的更新。舉例說明如下：

1. 資料表VendorProducts，是某家公司用來管理供貨廠商(VendorID)產品代號(ProductVen)，和自己公司對應的產品代號(ProductID)的對應表。
2. 資料表Products，是這家公司管理各項產品(ProductID)的資料表，由哪家供應商(VendorID)提供，進貨的價格(PriceV)多少？公告的售價(ListPrice)？最近是甚麼時候有更新過(LastUpdate)？
3. 日前這家公司接到來自供應商V2的通知(Vendor=V2)，該供應商所有X開頭的產品，將一律漲價20元，該公司的公告價格，將已漲價後的廠商供應價*1.5，作為新的公告售價，該如何完成這個些漲價產品的資料更新呢？
4. 可以使用以下SQL語法來快速達成這個任務：
   update Products
     set PriceV=PriceV+20,ListPrice=(PriceV+20)*1.5,LastUpdate=GETDATE()
     where ProductID in
     (select ProductID from VendorProducts where VendorID='V2' and ProductVen like 'X%')
5. 也可以把上述update條件的語法，改寫為Join的方式來下條件：
   update Products
     set PriceV=PriceV+20,ListPrice=(PriceV+20)*1.5, LastUpdate=GETDATE()
     from Products,VendorProducts
     where Products.VendorID=VendorProducts.VendorID and 
           Products.VendorID='V2' and VendorProducts.ProductVen like 'X%'

SQL語法：在Insert命令裡面使用Select的結果，作為要插入的資料來源

很多情況下，我們常需要根據一些已經存在的資料，來作為另一個資料表的資料來源。例如：
有一個資料表UserProgramRights，這個資料表示在管理某個User(UserID)？是否有某支程式(ProgramID)？的使用權限(YN)？
在新增一支程式(Prog001)後，要根據User帳號(UserAccount)的資料YN是否為Y？如果是Y就授予該User使用程式Prog001的權限。
SQL語法如下：

1. 查看一下資料來源的資料表
   select * from UserAccount (這個步驟不一定需要)
2. 查看一下要插入資料的資料表
   select * from UserProgramRights (這個步驟不一定需要)
3. 使用以下 Insert 的SQL語法，將資料來源插入對應的目標資料來源
   insert into UserProgramRights (UserID,ProgramID,YN)
    (select UserID,'Prog001','Y' from UserAccount where YN='Y' )
4. 查看一下插入資料的結果
   select * from UserProgramRights (這個步驟不一定需要)

善用SQL與法來管理資料，可以讓管理的工作更輕鬆愉快的進行 ... :)

使用SQL語法將資料轉換為cross table交叉資料表的形式

本範例要使用一個SQLite檔案，在SQLite Database Browser下，來進行資料的轉換示範。
SQLite Database Browser 1.3 Portable免安裝版下載的參考網址：http://files.bod.idv.tw/development/SQLiteDatabaseBrowser
在SQLite Database Browser下，可以建立SQLite資料庫檔檔、建立編輯刪除資料表、新增編輯刪除查詢資料、執行SQL指令...，一個學習SQL指令方常方便的免費小程式！
下載 MakingCrossTable.zip 範例檔案：MakingCrossTable.zip (解壓縮後檔案名稱為MakingCrossTable.db3，可以使用SQLite Database Browser直接開啟這個檔案)

在MakingCrossTable.db3中，包含一個資料表Sign，資料表中的資料欄位，請參閱圖例。
SheetNo：表單號碼，SlotNo：關卡，SignName：簽核人，ReceiveTime：收件時間，SignTime：簽核時間。

在Sign資料表中，包含下列資料：有兩個表單號碼102030301及102030302，每個表單都有三個關卡100/200/300，每筆資料中紀錄了，該表單在某一關卡的簽核人、收件時間、簽核時間。

接下來要透過SQL statements將Sign資料表中的這兩個表單單號的六個關卡簽核紀錄，彙整每個表單所有簽核關卡簽核紀錄，使成為一筆資料。

可以將以下的SQL指令，複製到Execute SQL分頁的SQL string方塊中，然後按下Execute query按鈕，即可檢視彙整後的結果。

select SheetNo,
max(case SlotNo when '100' then SignName else '' end) as U1,
max(case SlotNo when '100' then ReceiveTime else '' end) as R1,
max(case SlotNo when '100' then SignTime else '' end) as S1,
max(case SlotNo when '200' then SignName else '' end) as U2,
max(case SlotNo when '200' then ReceiveTime else '' end) as R2,
max(case SlotNo when '200' then SignTime else '' end) as S2,
max(case SlotNo when '300' then SignName else '' end) as U3,
max(case SlotNo when '300' then ReceiveTime else '' end) as R3,
max(case SlotNo when '300' then SignTime else '' end) as S3
from Sign
group by SheetNo
order by SheetNo


簡單幾行的SQL指令，就可以達到資料彙整的效果，太方便了！

使用VB.net將資料插入SQLite3資料表的範例(insert into)

************************************************

    Private Sub InsertData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertData.Click
        Dim aWord(,) = {{"boy", "男孩"}, {"girl", "女孩"}, {"man", "男人"}, {"woman", "女人"}, {"student", "學生"}}
        Try
            Dim zSQLFile As String = "Data Source=C:\SQLite檔案存放資料夾\NewDB3.db3"
            '連接資料庫
            Dim oConn As New SQLiteConnection(zSQLFile)
            oConn.Open()
            '執行SQL指令
            Dim zSQL As String = vbNullString
            Dim zWord As String = vbNullString
            Dim zDeno As String = vbNullString
            Dim oCmd As SQLiteCommand = Nothing
            For i = 0 To 4
                zWord = aWord(i, 0)
                zDeno = aWord(i, 1)
                zSQL = "INSERT INTO test (word,denotation) VALUES ('" & zWord & "','" & zDeno & "')"
                oCmd = New SQLiteCommand(zSQL, oConn)
                oCmd.ExecuteNonQuery()
            Next
            lblMsg.Text = "Data inserted ready!!!"
            oCmd.Dispose()
            oConn.Close()
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try
    End Sub

使用VB.net建立SQLite3資料表的範例(create table)

1. NewDB.db3是一個已存在的SQLite資料庫檔案
2. 要在NewDB.db3上新增一個test資料表，包含三個欄位oid, word, denotation，資料規格如程式內容。

************************************************

Imports System.Data.SQLite

Public Class Form2
    Private Sub CreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTable.Click
        Try
            Dim zSQLFile As String = "Data Source=C:\SQLite檔案存放資料夾\NewDB3.db3"
            '連接資料庫
            Dim oConn As New SQLiteConnection(zSQLFile)
            oConn.Open()
            '執行SQL指令
            Dim zSQL As String = "CREATE TABLE test(oid INTEGER PRIMARY KEY AUTOINCREMENT, word VARCHAR(50), denotation VARCHAR(255));"
            Dim oCmd As SQLiteCommand = New SQLiteCommand(zSQL, oConn)
            oCmd.ExecuteNonQuery()
            oCmd.Dispose()
            oConn.Close()
            lblMsg.Text = "Table created ready!!!"
        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try
    End Sub
End Class

在Visual Studio 2010 express中加入System.Data.SQLite

System.Data.SQLite : An open source ADO.NET provider for the SQLite database engine
參考網址：

• http://sqlite.phxsoftware.com/
• http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
• http://www.kirupa.com/net/sqllite_vb_pg1.htm
• http://www.kirupa.com/net/sqllite_vb_pg2.htm

安裝檔下載網址：http://sourceforge.net/project/showfiles.php?group_id=132486

安裝步驟：

在以上的安裝過程畫面中，可以清楚的看到，這個安裝適用於ADO.net 2.0/3.5，如果是使用.net framework 4.0的環境，會出現以下錯誤訊。可以在專案的web.confing或app.config檔案中，增加或修改<startup>區段，就可以避開這個問題決了！

以新增項目app.config為例：
點選專案名稱按一下滑鼠右鍵，選擇『加入(D)』→『新增項目(W)』，在『加入新項目』對話方塊中，選擇『應用程式組態檔』，預設的檔名稱為“app.config”，按一下“確定”。
開啟app.config在<configuration>節點下，加入以下內容：
<startup useLegacyV2RuntimeActivationPolicy="true">
  <supportedRuntime version="v4.0"/>
</startup>
以上修正的參考資料：

• [C#] 解決SQLite不能在.Net Framework 4下執行的問題
• [C#] 解決SQLite不能在.Net Framework 4下執行的問題(2)

建立專案時，必須加入參考：
C:\Program Files\SQLite.NET\bin\System.Data.SQLite.dll

程式碼中要加入： Imports System.Data.SQLite

加入您所需要的程式碼...