㈠ 如何在db2命令行運行sql必知必會例子
db2 => connect to dbName user xxx using password
db2 => sql語句
如果要執行一個sql腳本文件：
db2 => quit
c:\> db2 -tvf sql文件名
db2 -td@ -f filename
@是語句結束符。
E:\>db2 ? options
db2 [option ...] [db2-command | sql-statement |
[? [phrase | message | sqlstate | class-code]]]
option：-a、-c、-e{c|s}、-finfile、-lhistfile、-n、-o、-p、-rreport、-s、-t、
-td;、-v、-w、-x 和 -zoutputfile。
選項 描述 預設設置
------ ---------------------------------------- ---------------
-a 顯示 SQLCA OFF
-c 自動落實 ON
-e 顯示 SQLCODE/SQLSTATE OFF
-f 讀取輸入文件 OFF
-l 將命令記錄到歷史文件中 OFF
-n 除去換行字元 OFF
-o 顯示輸出 ON
-p 顯示 db2 互動式提示符 ON
-r 將輸出報告保存到文件 OFF
-s 在命令出錯時停止執行 OFF
-t 設置語句終止字元 OFF
-v 回送當前命令 OFF
-w 顯示 FETCH/SELECT 警告消息 ON
-x 不列印列標題 OFF
-z 將所有輸出保存到輸出文件 OFF
注意：
使用 DB2OPTIONS 環境變數定製選項預設值。
緊跟選項字母後的減號（-）使該選項關閉。
使用 UPDATE COMMAND OPTIONS 更改選項設置（以互動式或
文件輸入方式）。
只能提供nt環境下編寫腳本的例子給你以供參考:
腳本樣例:
db2 connect to yourdb user yourname using yourpassword
db2 insert into newuser(username,password,email) values('Amy','1234','[email protected]')
db2 insert into newuser(username,password,email) values('Judy','1234','[email protected]')
db2 commit
db2 disconnect yourdb
運行腳本: 運行db2cmd X:\XXX.bat
以下摘自本論壇的FAQ可參考:
"
在命令窗口中運行DB2腳本，可用 db2 -svtf 腳本文件名 來實現。
例如，腳本文件名為sample.sql，運行：db2 -svtf sample.sql
參數中：
s 代表遇到錯誤時中止運行腳本
v 代表輸出結果到屏幕
t 指以；號作為每行的分隔符
f 指後面需跟腳本文件名 "---此摘錄版權歸斑竹非本人所有
具體在AS400如何編寫腳本非常遺憾.
db2 -x select SERIALNO from tabname where clause
C:>db2 attach to db2164 user ccp
輸入 ccp 的當前密碼：
實例連接信息
實例伺服器 = DB2/NT 8.2.0
授權標識 = CCP
本地實例別名 = DB2164
C:>db2 connect to dw164 user ccp
輸入 ccp 的當前密碼：
資料庫連接信息
資料庫伺服器 = DB2/NT 8.2.0
SQL 授權標識 = CCP
本地資料庫別名 = DW164
C:>db2 select * from CCP_STS1 fetch first 2 rows only with ur
CUST_ID NOW_PRED_S LOAD_TIME
-------------------- -------------------- --------------------------
3094736. ZFS 2008-05-07-10.02.00.453000
3145886. ZFS 2008-05-07-10.02.00.453000
2 條記錄已選擇。
C:>db2 list command options
命令行處理器選項設置
後端進程等待時間（秒） (DB2BQTIME) = 1
連接至後端的重試次數 (DB2BQTRY) = 60
請求隊列等待時間（秒） (DB2RQTIME) = 5
輸入隊列等待時間（秒） (DB2IQTIME) = 5
命令選項 (DB2OPTIONS) = +m
選項 描述 當前設置
------ ---------------------------------------- ---------------
-a 顯示 SQLCA OFF
-c 自動落實 ON
-d 檢索並顯示 XML 聲明 OFF
-e 顯示 SQLCODE/SQLSTATE OFF
-f 讀取輸入文件 OFF
-i 顯示 XML 數據並帶有縮進 OFF
-l 將命令記錄到歷史記錄文件中 OFF
-m 顯示受影響的行數 OFF
-n 除去換行字元 OFF
-o 顯示輸出 ON
-p 顯示互動式輸入提示符 ON
-q 保留空格和換行符 OFF
-r 將輸出保存到報告文件 OFF
-s 在命令出錯時停止執行 OFF
-t 設置語句終止字元 OFF
-v 回傳當前命令 OFF
-w 顯示 FETCH/SELECT 警告消息 ON
-x 不列印列標題 OFF
-z 將所有輸出保存到輸出文件 OFF
C:>db2set DB2OPTIONS=-x
C:>db2 select * from CCP_STS1 fetch first 2 rows only with ur
4654908. ZFS 2008-05-07-10.02.00.453000
3716687. ZFS 2008-05-07-10.02.00.453000
㈡ SQL必知必會(索引的使用原則)
你能看到查詢效率還是比較低的。當我們對 user_id 欄位創建索引之後
兩個單索引 comment_time , user_id
聯合索引（ user_id , comment_time ）
聯合索引（ comment_time , user_id ）
這里我們對 comment_text 創建索引，再執行上面的 SQL 語句
㈢ SQL書籍推薦
1、《SQL必知必會》
福達著, 鍾鳴、劉曉霞譯
這本書由淺入深地講解了SQL的基本概念和語法，涉及數據的排序、過濾和分組，以及表、視圖、聯結、子查詢、游標、存儲過程和觸發器等內容，實例豐富，便於查閱。新版增加了針對ApacheOpenOfficeBase、MariaDB、SQlite等DBMS的描述，並根據新版本的Oracle、SQLServer、MySQL和PostgreSQL更新了相關示例，非常適合初學者。
2、《SQL基礎教程》
MICK 著, 孫淼、羅勇 譯
這本書豆瓣評分9.0，重印13次，第2版基於新版本RDBMS全面升級，並新增一章介紹從應用程序執行SQL語句的方法。本書從資料庫、表的結構到查詢、更新表的語法，常用的函數，表的聯結等，內容逐步深入。對於初學者常見的疑難點，均通過專欄進行講解。各章節後精心設計了練習題，幫助讀者鞏固理解
3、《SQL Cookbook》
Anthony Molinaro 著
本書是一本指南，其中包含了一系列SQL 的常用問題以及它們的解決方案，希望能對讀者的日常工作有所幫助。有150 多個小節，這還僅僅是SQL 所能做的事情的一鱗半爪，而這本書更主要的是讓讀者看到，SQL 能夠做多少一般認為是SQL 問題范圍之外的事情。
4、《深入淺出SQL（中文版）》
貝里 著，O『Reilly Taiwan 譯
這本對所有沒SQL基礎的人來說都是本好書，幽默的語言，緊湊的內容，而且還有生動的圖畫，這無非給了初學者一個很好的開始，不僅會信心大增，也會提高學習興趣，同時對後續的提高也打下了基礎。著重基本語法的理解和基本概念的闡述，穿插在各個章節的練習題恰好提供了鞏固作用，如果你正苦於尋找一本SQL入門書籍，那麼它一定是不二之選
5、《SQL 反模式》
Bill Karwin 著，譚振林 / Push Chen 譯
《SQL反模式》是一本廣受好評的SQL圖書。它介紹了如何避免在SQL的使用和開發中陷入一些常見卻經常被忽略的誤區。它通過講述各種具體的案例，以及開發人員和使用人員在面對這些案例時經常採用的錯誤解決方案，來介紹如何識別、利用這些陷阱，以及面對問題時正確的解決手段。另外，《SQL反模式》還涉及了SQL的各級範式和針對它們的正確理解。
㈣ SQL必知必會(視圖)
這樣的話，下次再對視圖進行查詢的時候，視圖結果就進行了更新。
我在講解 SQL99 標准連接操作的時候，舉了一個 NBA 球員和身高等級連接的例子，有兩張表，分別為 player 和 height_grades。其中 height_grades 記錄了不同身高對應的身高等級。這里我們可以通過創建視圖，來完成球員以及對應身高等級的查詢。
這樣的話，我們直接查詢視圖，就可以得到格式化後的結果：
㈤ SQL必知必會(SQL 是如何執行的)
SQL 在 Oracle 中的執行過程
首先我們需要看下 profiling 是否開啟，開啟它可以讓 MySQL 收集在 SQL 執行時所使用的資源情況，命令如下：
然後我們執行一個 SQL 查詢
㈥ SQL必知必會(第五版)
去重 ：使用DISTINCT關鍵字，它必須直接放在列名的前面。
檢索前5行數據：
LIMIT 5 OFFSET 5指示MySQL等DBMS返回從第5行起的5行數據。第一個數字是檢索的行數，第二個數字是指從哪兒開始。
2.1 排序數據
注意： ORDER BY 子句的位置在指定一條ORDER BY子句時，應該保證它是 SELECT語句中最後一條子句 。如果它不是最後的子句，將會出錯。
ORDER BY 2, 3表示先按prod_price，再按prod_name進行排序。
好處在於不用重新輸入列名。
缺點。首先，不明確給出列名有可能造成錯用列名排序。其次，在對SELECT清單進行更改時容易錯誤地對數據進行排序（忘記對ORDER BY子句做相應的改動）。最後，如果進行排序的列不在SELECT清單中，顯然不能使用這項技術。
DESC 是DESCENDING的縮寫，這兩個關鍵字都可以使用。與DESC相對的是 ASC （或ASCENDING），在升序(A→Z)排序時可以指定它。但實際上，ASC沒有多大用處，因為升序是默認的（如果既不指定ASC也不指定DESC，則假定為ASC）。
DESC關鍵字只應用到直接位於其前面的列名。如果想在多個列上進行降序排序，必須對每一列指定DESC關鍵字。
在SELECT語句中，數據根據WHERE子句中指定的搜索條件進行過濾。WHERE子句在表名（FROM子句）之後給出。
在同時使用ORDER BY和WHERE子句時，應該讓ORDER BY位於WHERE之後，否則將會產生錯誤。
提示： 單引號用來限定字元串。如果將值與字元串類型的列進行比較，就需要限定引號。用來與數值列進行比較的值不用引號。
操作符（operator） 用來聯結或改變WHERE子句中的子句的關鍵字，也稱為邏輯操作符（logicaloperator）。
AND 用在WHERE子句中的關鍵字，用來指示檢索滿足所有給定條件的行。
一個AND子句，只有兩個過濾條件。可以增加多個過濾條件，每個條件間都要使用AND關鍵字。
ORDER BY子句，放在WHERE子句之後。
OR： WHERE子句中使用的關鍵字，用來表示檢索匹配任一給定條件的行。
SQL（像多數語言一樣）在處理OR操作符前，優先處理AND操作符。
圓括弧具有比AND或OR操作符更高的優先順序。
任何時候使用具有AND和OR操作符的WHERE子句，都應該使用圓括弧明確地分組操作符。
IN：WHERE子句中用來指定要匹配值的清單的關鍵字，功能與OR相當。
IN操作符一般比一組OR操作符執行得更快。
IN的最大優點是可以包含其他SELECT語句，能夠更動態地建立WHERE子句。
NOT： WHERE子句中用來否定其後條件的關鍵字。
通配符（wildcard） 用來匹配值的一部分的特殊字元。
搜索模式（search pattern） 由字面值、通配符或兩者組合構成的搜索條件。
說明：區分大小寫根據DBMS的不同及其配置，搜索可以是區分大小寫的。如果區分大小寫，則』fish%』與Fish bean bag toy就不匹配。
除了能匹配一個或多個字元外，%還能匹配0個字元。%代表搜索模式中給定位置的0個、1個或多個字元。
'%』不會匹配名稱為NULL的行。
下劃線的用途與%一樣，但它只匹配單個字元，而不是多個字元。
與%能匹配多個字元不同，_總是剛好匹配一個字元，不能多也不能少。
此語句的WHERE子句中的模式為』[JM]%'。這一搜索模式使用了兩個不同的通配符。[JM]匹配方括弧中任意一個字元，它也只能匹配單個字元。因此，任何多於一個字元的名字都不匹配。[JM]之後的%通配符匹配第一個字元之後的任意數目的字元，返回所需結果。
也可以使用NOT操作符得出類似的結果。^的唯一優點是在使用多個WHERE子句時可以簡化語法：
例如：
拼接（concatenate）
將值聯結到一起（將一個值附加到另一個值）構成單個值。
操作符可用加號（+）或兩個豎杠（||）表示。
說明：是+還是||？SQL Server使用+號。DB2、Oracle、PostgreSQL和SQLite使用||。在MySQL和MariaDB中，必須使用特殊的函數。
說明： TRIM函數 大多數DBMS都支持 RTRIM() （去掉字元串右邊的空格）、 LTRIM() （去掉字元串左邊的空格）以及 TRIM() （去掉字元串左右兩邊的空格）。
SQL支持列別名。 別名（alias） 是一個欄位或值的替換名。別名用 AS 關鍵字賦予。
AS關鍵字可選，最好用它。
提示：如何測試計算
SELECT語句為測試、檢驗函數和計算提供了很好的方法。雖然SELECT通常用於從表中檢索數據，但是省略了FROM子句後就是簡單地訪問和處理表達式，例如SELECT 3＊2;將返回6，SELECT Trim(' abc ');將返回abc,SELECT Curdate()；使用Curdate()函數返回當前日期和時間。
UPPER()函數： 將文本轉換為大寫。
SQL函數不區分大小寫，因此upper(), UPPER(), Upper()都可以，substr(), SUBSTR(), SubStr()也都行。
㈦ SQL必知必會(SQL99連接（JOIN）)
SQL92
SQL99
SQL92
SQL99
SQL99
SQL92
SQL99
至此我們講解完了 SQL92 和 SQL99 標准下的連接查詢，連接操作基本上可以分成三種情況：
SQL 連接具有通用性，但是不同的 DBMS 在使用規范上會存在差異，在標准支持上也存在不同。在實際工作中，你需要參考你正在使用的 DBMS 文檔，這里我整理了一些需要注意的常見的問題。
1. 不是所有的 DBMS 都支持全外連接
2.Oracle 沒有表別名 AS
3.SQLite 的外連接只有左連接
1. 控制連接表的數量
2. 在連接時不要忘記 WHERE 語句
3. 使用自連接而不是子查詢
㈧ SQL必知必會（二）函數、子查詢
#文本處理函數
UPPER() --大寫
LOWER() --小寫
SOUNDEX() --讀音類似
LENGTH() --字元串長度
#日期和時間處理函數
SELECT order_code
FROM order_table
WHERE DATEPART(yy,order_date)=2012
#to_char()函數提取日期成分，MySQL可用year()函數提取年份
SELECT order_num
FROM order_table
WHERE to_number(to_char(order_date,'YYYY'))=2012; --to_char提取日期成分to_number轉化為數值
SELECT order_num
FROM order_table
WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31--2012'); --字元串轉日期
#數值處理函數
ABS() --絕對值
COS() --餘弦
EXP() --指數值
PI() --圓周率
SIN() --正弦
SQRT() --平方根
TAN() --正切
#平均數AVG()
SELECT AVG(a) AS avg_a
FROM tableA
WHERE a='DLL01';
#計數
SELECT COUNT(*) AS num_cust --對所有行計數，不管是否NULL值
FROM tableA;
SELECT COUNT(a) AS num_a --指定了列名，會忽略NULL值
FROM tableA;
#最值
MAX()和MIN()，忽略NULL值
#總值SUM()，忽略NULL值
SELECT SUM(price*quantity) AS total_price
FROM tableA
WHERE order_code=20008
#以上聚集函數只包含不同值
SELECT AVG(DISTINCT order_price) AS avg_price
FROM tableA
WHERE id='DLL01';
兩個子句：GROUP BY 和 HAVING
#GROUP BY創建分組HAVING過濾分組
SELECT vend_id， COUNT(*) AS num_prods
FROM tableA
WHERE price>=4
GROUP BY vend_id
HAVING COUNT(*)>=2;
ORDER BY price --GROUP BY在WHERE之後在ORDER BY之前
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
#子查詢順序為從內而外
SELECT order_num
FROM tableA
WHERE id='DLL01'；
(輸出20007和20008)
SELECT id
FROM tableB
WHERE order_num IN（20007，20008）；
#合並為子查詢。只能查詢單個列
SELECT id
FROM tableB
WHERE order_num IN (SELECT order_num FROM tableA WHERE id='DLL01')
SELECT cust_name,
cust_state
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id=Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
#Orders.cust_id=Customers.cust_id完全限定列名，在兩張表中名字相同列拿出來比較，防止歧義
㈨ SQL必知必會(游標)
第一步，定義游標。
第二步，打開游標。
第三步，從游標中取得數據。
第四步，關閉游標。
最後一步，釋放游標。
你會發現執行 call calc_hp_max() 這一句的時候系統會提示 1329 錯誤，也就是在 LOOP 中當游標沒有取到數據時會報的錯誤。
使用游標來解決一些常見的問題
㈩ SQL必知必會(查詢優化器)
了解查詢優化器的作用之前，我們先來看看一條 SQL 語句的執行都需要經歷哪些環節，如下圖所示：
你能看到一條 SQL 查詢語句首先會經過分析器，進行語法分析和語義檢查。我們之前講過語法分析是檢查 SQL 拼寫和語法是否正確，語義檢查是檢查 SQL 中的訪問對象是否存在。比如我們在寫 SELECT 語句的時候，列名寫錯了，系統就會提示錯誤。語法檢查和語義檢查可以保證 SQL 語句沒有錯誤，最終得到一棵語法分析樹，然後經過查詢優化器得到查詢計劃，最後交給執行器進行執行。
查詢優化器的目標是找到執行 SQL 查詢的最佳執行計劃，執行計劃就是查詢樹，它由一系列物理操作符組成，這些操作符按照一定的運算關液嫌系組成查詢的執行計劃。在查詢優化器中，可以分為邏輯查詢優化階段和物理查詢優化階段。
邏輯查詢優化就是通過改變 SQL 語句的內容來使得 SQL 查詢更高效，同時為物理查詢優化提供更多的候選執行計劃。通常採用的方式是對 SQL 語句進行等價變換，對查詢進行重寫，而查詢重寫的數學基礎就是關系代數。對條件表達式進行等價謂詞重寫、條件簡化，對視圖進行重寫，對子查詢進行優化檔埋鬧，對連接語義進行了外連接消除、嵌套連接消除等。
邏輯查詢優化是基於關系代數進行的查詢重寫，而關系代數的每一步都對應著物理計算，這些物理計算往往存在多種演算法，因此需要計算各種物理路徑的代價，從中選擇代價最小的作為執行計劃。在這個階段里，對於單表和多表連接的操作，需要高效地使用索引，提升查詢效率。
在這兩個階段中，查詢重寫屬於行罩代數級、語法級的優化，也就是屬於邏輯范圍內的優化，而基於代價的估算模型是從連接路徑中選擇代價最小的路徑，屬於物理層面的優化。
查詢優化器的目的就是生成最佳的執行計劃，而生成最佳執行計劃的策略通常有以下兩種方式。
但我們需要記住，SQL 是面向集合的語言，並沒有指定執行的方式，因此在優化器中會存在各種組合的可能。我們需要通過優化器來制定數據表的掃描方式、連接方式以及連接順序，從而得到最佳的 SQL 執行計劃。
你能看出來，RBO 的方式更像是一個計程車老司機，憑借自己的經驗來選擇從 A 到 B 的路徑。而 CBO 更像是手機導航，通過數據驅動，來選擇最佳的執行路徑。
大部分 RDBMS 都支持基於代價的優化器（CBO），CBO 隨著版本的迭代也越來越成熟，但是 CBO 依然存在缺陷。通過對 CBO 工作原理的了解，我們可以知道 CBO 可能存在的不足有哪些，有助於讓我們知道優化器是如何確定執行計劃的。
首先，我們先來了解下 MySQL 中的 COST Model ， COST Model 就是優化器用來統計各種步驟的代價模型，在 5.7.10 版本之後，MySQL 會引入兩張數據表，裡面規定了各種步驟預估的代價（Cost Value） ，我們可以從 mysql.server_cost 和 mysql.engine_cost 這兩張表中獲得這些步驟的代價：
server_cost 數據表是在 server 層統計的代價，具體的參數含義如下：
由這張表中可以看到，如果想要創建臨時表，尤其是在磁碟中創建相應的文件，代價還是很高的。
然後我們看下在存儲引擎層都包括了哪些代價：
engine_cost 主要統計了頁載入的代價，我們之前了解到，一個頁的載入根據頁所在位置的不同，讀取的位置也不同，可以從磁碟 I/O 中獲取，也可以從內存中讀取。因此在 engine_cost 數據表中對這兩個讀取的代價進行了定義：
既然 MySQL 將這些代價參數以數據表的形式呈現給了我們，我們就可以根據實際情況去修改這些參數。因為隨著硬體的提升，各種硬體的性能對比也可能發生變化，比如針對普通硬碟的情況，可以考慮適當增加 io_block_read_cost 的數值，這樣就代表從磁碟上讀取一頁數據的成本變高了。當我們執行全表掃描的時候，相比於范圍查詢，成本也會增加很多。
比如我想將 io_block_read_cost 參數設置為 2.0，那麼使用下面這條命令就可以：
我們對 mysql.engine_cost 中的 io_block_read_cost 參數進行了修改，然後使用 FLUSH OPTIMIZER_COSTS 更新內存，然後再查看 engine_cost 數據表，發現 io_block_read_cost 參數中的 cost_value 已經調整為 2.0。
如果我們想要專門針對某個存儲引擎，比如 InnoDB 存儲引擎設置 io_block_read_cost ，比如設置為 2，可以這樣使用：
然後我們再查看一下 mysql.engine_cost 數據表：
從圖中你能看到針對 InnoDB 存儲引擎可以設置專門的 io_block_read_cost 參數值。
總代價的計算是一個比較復雜的過程，上面只是列出了一些常用的重要參數，我們可以根據情況對它們進行調整，也可以使用默認的系統參數值。
那麼總的代價是如何進行計算的呢？
在論文 《Access Path Selection-in a Relational Database Management System》 中給出了計算模型，如下圖所示：
你可以簡單地認為，總的執行代價等於 I/O 代價 +CPU 代價。在這里 PAGE FETCH 就是 I/O 代價，也就是頁面載入的代價，包括數據頁和索引頁載入的代價。W*(RSI CALLS) 就是 CPU 代價。W 在這里是個權重因子，表示了 CPU 到 I/O 之間轉化的相關系數，RSI CALLS 代表了 CPU 的代價估算，包括了鍵比較（compare key）以及行估算（row evaluating）的代價。
為了讓你更好地理解，我說下關於 W 和 RSI CALLS 的英文解釋：W is an adjustable weight between I/O and CPU utilization. The number of RSI calls is used to approximate CPU utilization。
這樣你應該能明白為了讓 CPU 代價和 I/O 代價放到一起來統計，我們使用了轉化的系數 W，
另外需要說明的是，在 MySQL5.7 版本之後，代價模型又進行了完善，不僅考慮到了 I/O 和 CPU 開銷，還對內存計算和遠程操作的代價進行了統計，也就是說總代價的計算公式演變成下面這樣：
總代價 = I/O 代價 + CPU 代價 + 內存代價 + 遠程代價
這里對內存代價和遠程代價不進行講解，我們只需要關注 I/O 代價和 CPU 代價即可。