SQLServer數(shù)據(jù)庫基本知識點(diǎn)

1、SQL Server數(shù)據(jù)庫基本知識點(diǎn)、數(shù)據(jù)類型數(shù)據(jù)類型類型描述是否常用bit整型bit數(shù)據(jù)類型是整型，其值只能是0、1或空值。這種數(shù)據(jù)類型用 于存儲只有兩種可能值的數(shù)據(jù)，如 Yes 或 No True 或 Fa lse 、 On 或 Offint整型int數(shù)據(jù)類型可以存儲從-31312 (-48)到 2(2147483 647)之間的整數(shù)。存儲到數(shù)據(jù)庫的幾乎 所有數(shù)值型的數(shù)據(jù)都可以用這種 數(shù)據(jù)類型。這種數(shù)據(jù)類型在數(shù)據(jù)庫里占用4個字節(jié)是smalli nt整型smalli nt數(shù)據(jù)類型可以存儲從-15152 (-32768)到 2 (32767)之間的整數(shù)。這種數(shù)據(jù)類型對存儲一些 常限定在特定范圍

2、內(nèi)的數(shù)值型數(shù) 據(jù)非常有用。這種數(shù)據(jù)類型在數(shù) 據(jù)庫里占用2字節(jié)空間tin yi nt整型tinyint數(shù)據(jù)類型能存儲從 0到255之間的整數(shù)。它在你只打算 存儲有限數(shù)目的數(shù)值時很有用。這種數(shù)據(jù)類型在數(shù)據(jù)庫中占用1個字節(jié)nu meric精確數(shù)值型numeric數(shù)據(jù)類型與decimal型相同decimal精確數(shù)值型decimal數(shù)據(jù)類型能用來存儲從 -10 38-1到1038-1的固定精度和范 圍的數(shù)值型數(shù)據(jù)。使用這種數(shù)據(jù) 類型時，必須指定范圍和精度。 范圍是小數(shù)點(diǎn)左右所能存儲的數(shù) 字的總位數(shù)。精度是小數(shù)點(diǎn)右邊 存儲的數(shù)字的位數(shù)money貨幣型money數(shù)據(jù)類型用來表示錢和貨 幣值。這種數(shù)據(jù)類型能存

3、儲從-9220億到9220億之間的數(shù)據(jù)，精確到貨幣單位的萬分之一smallm oney貨幣型smallmoney數(shù)據(jù)類型用來表示錢和貨幣值。這種數(shù)據(jù)類型能存 儲從 到 之間的數(shù)據(jù)，精確到貨幣單位的萬分之一float近似數(shù)值型float 數(shù)據(jù)類型是一種近似數(shù)值 類型，供浮點(diǎn)數(shù)使用。說浮點(diǎn)數(shù) 是近似的，是因?yàn)樵谄浞秶鷥?nèi)不 是所有的數(shù)都能精確表示。浮點(diǎn) 數(shù)可以是從+308到+308之間的 任意數(shù)是real近似數(shù)值型real數(shù)據(jù)類型像浮點(diǎn)數(shù)一樣，是近似數(shù)值類型。它可以表示數(shù)值在+38到+38之間的浮點(diǎn)數(shù)datetime日期時間型datetime數(shù)據(jù)類型用來表示日期 和時間。這種數(shù)據(jù)類型存儲從1753年

4、1月1日到9999年12月3 1日間所有的日期和時間數(shù)據(jù)， 精確到二白分之一秒或毫秒是Smalldatetime日期時間型smalldatetime數(shù)據(jù)類型用來表示從1900年1月1日到2079年6月6日間的日期和時間，精確到一分鐘cursor特殊數(shù)據(jù)型cursor數(shù)據(jù)類型是一種特殊的 數(shù)據(jù)類型，它包含一個對游標(biāo)的 引用。這種數(shù)據(jù)類型用在存儲過 程中，而且創(chuàng)建表時不能用是timestamp特殊數(shù)據(jù)型timestamp數(shù)據(jù)類型是一種特殊的數(shù)據(jù)類型，用來創(chuàng)建一個數(shù)據(jù) 庫范圍內(nèi)的唯一數(shù)碼。一個表中只能有一個 timestamp列。每次 插入或修改一行時，timestamp列 的值都會改變。盡管它的名

5、字中 有“ time ”，但 timestamp 歹U不是人們可識別的日期。在一個數(shù) 據(jù)庫里，timestamp值是唯一的Uniq ueide ntifier特殊數(shù)據(jù)型Uni queide ntifier數(shù)據(jù)類型用來存儲一個全局唯一標(biāo)識符，即GUIB GUID確實(shí)是全局唯一的。這個數(shù)幾乎沒有機(jī)會在另一個系 統(tǒng)中被重建?？梢允褂?NEWID函 數(shù)或轉(zhuǎn)換一個字符串為唯一標(biāo)識 符來初始化具有唯一標(biāo)識符的列char字符型char數(shù)據(jù)類型用來存儲指定長度 的定長非統(tǒng)一編碼型的數(shù)據(jù)。當(dāng) 定義一列為此類型時，你必須指 定列長。當(dāng)你總能知道要存儲的 數(shù)據(jù)的長度時，此數(shù)據(jù)類型很有 用。例如，當(dāng)你按郵政編碼加4個

6、字符格式來存儲數(shù)據(jù)時，你知 道總要用到10個字符。此數(shù)據(jù)類 型的列寬最大為 8000個字符是varchar字符型varchar數(shù)據(jù)類型，同 char類型一樣，用來存儲非統(tǒng)一編碼型字 符數(shù)據(jù)。與char型不一樣，此數(shù) 據(jù)類型為變長。當(dāng)定義一列為該 數(shù)據(jù)類型時，你要指定該列的最 大長度。它與char數(shù)據(jù)類型最 大的區(qū)別是，存儲的長度不是列 長，而是數(shù)據(jù)的長度是text字符型text數(shù)據(jù)類型用來存儲大量的 非統(tǒng)一編碼型字符數(shù)據(jù)。這種數(shù) 據(jù)類型最多可以有 231-1或20億 個字符n char統(tǒng)一編碼字符型nchar數(shù)據(jù)類型用來存儲定長統(tǒng)一編碼字符型數(shù)據(jù)。統(tǒng)一編碼用 雙字節(jié)結(jié)構(gòu)來存儲每個字符，而 不是

7、用單字節(jié)（普通文本中的情 況）。它允許大量的擴(kuò)展字符。此 數(shù)據(jù)類型能存儲4000種字符，使 用的字節(jié)空間上增加了一倍n varchar統(tǒng)一編碼字符型n varchar數(shù)據(jù)類型用作變長的 統(tǒng)一編碼字符型數(shù)據(jù)。此數(shù)據(jù)類 型能存儲4000種字符，使用的字 節(jié)空間增加了一倍n text統(tǒng)一編碼字符型ntext數(shù)據(jù)類型用來存儲大量的 統(tǒng)一編碼字符型數(shù)據(jù)。這種數(shù)據(jù) 類型能存儲230-1或?qū)⒔?0億個字符，且 使用的字節(jié)空間增加 了一倍binary二進(jìn)制數(shù)據(jù)類型binary數(shù)據(jù)類型用來存儲可達(dá) 8000字節(jié)長的定長的二進(jìn)制數(shù) 據(jù)。當(dāng)輸入表的內(nèi)容接近相同的 長度時，你應(yīng)該使用這種數(shù)據(jù)類 型varb inary

8、二進(jìn)制數(shù)據(jù)類型varb inary數(shù)據(jù)類型用來存儲可達(dá)8000字節(jié)長的變長的二進(jìn)制 數(shù)據(jù)。當(dāng)輸入表的內(nèi)容大小可變 時，你應(yīng)該使用這種數(shù)據(jù)類型image二進(jìn)制數(shù)據(jù)類型image數(shù)據(jù)類型用來存儲變長的 二進(jìn)制數(shù)據(jù)，最大可達(dá)231-1或大約20億字節(jié)常用語句（用到的數(shù)據(jù)庫Northwind）查詢語句簡單的Transact-SQL查詢只包括選擇列表、FROM子句和 WHER子句。它們分別說明所查詢列、查詢的表或視圖、以及搜索條件等。例如，下面的語句查詢Customers表中公司名稱為"AlfredsFutterkiste ”的 ContactName 字段和 Address 字段。SELEC

9、T Con tactName, AddressFROM CustomersWHERE Compa nyName='Alfreds Futterkiste'（一）選擇列表選擇列表（select_list）指出所查詢列，它可以是一組列名列表、星號、 表達(dá)式、變量（包括局部變量和全局變量）等構(gòu)成。1選擇所有列例如，下面語句顯示 Customers表中所有列的數(shù)據(jù)：SELECT *FROM Customers2、選擇部分列并指定它們的顯示次序查詢結(jié)果集合中數(shù)據(jù)的排列順序與選擇列表中所指定 的列名排列順序相同。例如：SELECT Co ntactName, AddressFROM Cus

10、tomers3、更改列標(biāo)題在選擇列表中，可重新指定列標(biāo)題。定義格式為：列標(biāo)題 as 列名列名 列標(biāo)題如果指定的列標(biāo)題不是標(biāo)準(zhǔn)的標(biāo)識符格式時，應(yīng)使用引號定界符，例如，下列 語句使用漢字顯示列標(biāo)題：SELECT ContactName as 聯(lián)系人名稱 , Address as 地址FROM Customers4、刪除重復(fù)行SELECT語句中使用ALL或DISTINCT選項(xiàng)來顯示表中符合條件的所有行或刪除其中重復(fù)的數(shù)據(jù)行，默認(rèn)為ALL。使用DISTINCT選項(xiàng)時，對于所有重復(fù)的數(shù)據(jù)行在SELECT返回的結(jié)果集合中只保留一行。SELECT DISTINCT(Country)FROM Customer

11、s5、限制返回的行數(shù)使用TOP n PERCENT選項(xiàng)限制返回的數(shù)據(jù)行數(shù)，TOP n說明返回n行，而TOP n PERCENT時，說明 n 是表示一百分?jǐn)?shù)，指定返回的行數(shù)等于總行數(shù)的百分之幾。例如：SELECT TOP 2 *FROM CustomersSELECT TOP 20 PERCENT *FROM Customers(二) FROM子 句FROM子句指定SELECT語句查詢及與查詢相關(guān)的表或視圖。在FROM子句中最多可指定 256個表或視圖，它們之間用逗號分隔。在FROM子句同時指定多個表或視圖時，如果選擇列表中存在同名列，這時應(yīng)使用對象名限定這些列所屬的表或視圖。 例如在 Orde

12、rs 和 Customers 表中同時存在 CustomerID 列，在查詢兩個表 中的 CustomerID 時應(yīng)使用下面語句格式加以限定：select * from Orders,Customerswhere =在FROM子句中可用以下兩種格式為表或視圖指定別名：表名 as 別名表名 別名select * from Orders as a,Customers as bwhere =SELECT不僅能從表或視圖中檢索數(shù)據(jù)，它還能夠從其它查詢語句所返回的結(jié)果集合中查詢數(shù)據(jù)。例如：select * from Customers where CustomerID in （select Custom

13、erID from Orders where EmployeeID=4）此例中，將SELECT返回的結(jié)果集合給予一別名CustomerlD，然后再從中檢索數(shù)據(jù)。（三）使用WHER子句設(shè)置查詢條件WHER子句設(shè)置查詢條件，過濾掉不需要的數(shù)據(jù)行。例如下面語句查詢年齡大于20的數(shù)據(jù)：select CustomerlD from Orders where EmployeelD=4WHER子句可包括各種條件運(yùn)算符：比較運(yùn)算符 （ 大小比較 ） ： 、=、=、=、! 、! 范圍運(yùn)算符（表達(dá)式值是否在指定的范圍 ）：BETWEENAND，NOT BETWEEhAND列表運(yùn)算符（判斷表達(dá)式是否為列表中的指定項(xiàng)

14、）：IN （項(xiàng)1,項(xiàng)2）NOT IN （項(xiàng)1,項(xiàng)2）模式匹配符 （判斷值是否與指定的字符通配格式相符 ）:LIKE 、NOT LIKE空值判斷符 （判斷表達(dá)式是否為空 ）： IS NULL、NOT IS NULL邏輯運(yùn)算符 （用于多條件的邏輯連接 ）： NOT、AND、OR1、 范圍運(yùn)算符例：age BETWEEN 10 AND 30相當(dāng)于 age>=10 AND age<=302、 列表運(yùn)算符例：country IN （'Germany','China'）3、模式匹配符例：常用于模糊查找，它判斷列值是否與指定的字符串格式相匹配?？捎糜?char 、

15、varchar 、 text 、 ntext 、 datetime 和 smalldatetime 等類型查詢。 可使用以下通配字符：百分號 %：可匹配任意類型和長度的字符插入語句語法：Insert into 表（字段1,字段2,.字段n） values（ 值1,值2,.值n）例如： Insert into Region（RegionID, RegionDescription）values（5,'Southern'）更新語句語法：update 表set 字段1 = 值1,字段2=值2,字段門=值n where 條件如：update Customers set ContactNa

16、me='John',Address ='Avda. de la' whereCustomerID='ALFKI'where 條件同查詢語句刪除語句 語法： delete from 表 where 條件 如：delete from Customers where CustomerID='ALFKI'where 條件同查詢語句清空表：truncate table 表 清空表中所有數(shù)據(jù)，表中如果有自增長字段，該字段在插入時會重新開 始、I ' d;注意：由于更新語句與刪除語句會更改表數(shù)據(jù)對公司業(yè)務(wù)主要表進(jìn)行更新與刪除操作時一定要

17、先 進(jìn)行備份，然后進(jìn)行操作備份語句select * into Customers from Customers目標(biāo)表( Customers )不存在時會自動創(chuàng)建三、 存儲過程優(yōu)點(diǎn)：1. 存儲過程只在創(chuàng)造時進(jìn)行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次 , 所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。2. 當(dāng)對數(shù)據(jù)庫進(jìn)行復(fù)雜操作時 ( 如對多個表進(jìn)行 Update,Insert,Query,Delete 時)，可將此復(fù)雜操作用存儲 過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。3. 存儲過程可以重復(fù)使用 , 可減少數(shù)據(jù)庫開發(fā)人員的工作量4. 安全性高 , 可設(shè)定

18、只有某此用戶才具有對指定存儲過程的使用權(quán)語法CREATE PROC EDURE owner. procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 參數(shù) owner擁有存儲過程的用戶ID的名稱。owner必須是當(dāng)前用戶的名稱或當(dāng)前用戶所屬的角色 的名稱。procedure_name 新存儲過程的名稱。過程名必須符合標(biāo)識符規(guī)則，且

19、對于數(shù)據(jù)庫及其所有者必須唯一。;number是可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDUR語句即可將同組的過程一起除去。例如，名為 orders 的應(yīng)用程序使用的過程可以命名為 orderproc;1 、 orderproc;2 等。DROP PROCEDURE orderproc語句將除去整個組。如果名稱中包含定界標(biāo) 識符，則數(shù)字不應(yīng)包含在標(biāo)識符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?parameter過程中的參數(shù)。在 CREAT田ROCEDUR語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值 （除非定義了該參數(shù)的默認(rèn)

20、值， 或者該值設(shè)置為等于另一個參數(shù)）。存儲過程最多可以有 個參數(shù)。使用 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個過程 的參數(shù)僅用于該過程本身； 相同的參數(shù)名稱可以用在其它過程中。 默認(rèn)情況下， 參數(shù)只能代 替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。data_type參數(shù)的數(shù)據(jù)類型。除 table 之外的其他所有數(shù)據(jù)類型均可以用作存儲過程的參數(shù)。但是，cursor數(shù)據(jù)類型只能用于 OUTPUT參數(shù)。如果指定cursor 數(shù)據(jù)類型，則還必須指定 VARYING和OUTPUT關(guān)鍵字。對于可以是cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限 制。VARYI

21、NG指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。default參數(shù)的默認(rèn)值。 如果定義了默認(rèn)值， 不必指定該參數(shù)的值即可執(zhí)行過程。 默認(rèn)值必須是 常量或NULL。如果過程將對該參數(shù)使用 LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符（ % _、和A）。OUTPUT表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給 EXECUTE。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。 Text、ntext和image參數(shù)可用作 OUTPUT參數(shù)。使用 OUTPUT關(guān)鍵字 的輸出參數(shù)可以是游標(biāo)占位符。如：CREATE PROCEDURE CustOrdersDetail Orde

22、rID intASSELECT ProductName,UnitPrice=ROUND, 2),Quantity,Discount=CONVERT(int, Discount * 100),ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * , 2)FROM Products P, Order Details OdWHERE = and = OrderIDGO四：游標(biāo)：游標(biāo)( Cursor )是處理數(shù)據(jù)的一種方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo) 提供了在結(jié)果集中一次以行或者多行前進(jìn)或向后瀏覽數(shù)據(jù)的能力。我們可以把游

23、標(biāo) 當(dāng)作一個指針，它可以指定結(jié)果中的任何位置，然后允許用戶對指定位置的數(shù)據(jù)進(jìn) 行處理。1. 聲明游標(biāo)2. 打開游標(biāo)3. 讀取游標(biāo)數(shù)據(jù)4. 關(guān)閉游標(biāo)5. 釋放游標(biāo)常用于需要把多行的數(shù)據(jù) 進(jìn)行拼接處理如：declare ContactName varchar(50)declare AllContactName varchar(5000)set AllContactName = ''DECLARE GetContactName_Cursor CURSORFOR select ContactName from Customers where CustomerId = 'ALFK

24、I'OPEN GetContactName_CursorFETCH NEXT FROM GetContactName_Cursor into ContactNameWHILE FETCH_STATUS = 0BEGINset AllContactName= AllContactName+ContactName print AllContactNameFETCH NEXT FROM GetContactName_Cursor into ContactName ENDCLOSE GetContactName_Cursor DEALLOCATE GetContactName_Cursor 五

25、：常用系統(tǒng)函數(shù)和變量：系統(tǒng)變量：select ERROR- 返回最后執(zhí)行的 Transact-SQL 語句的錯誤代碼(integer)select IDENTITY- 返回最后插入的標(biāo)識值Select USER_NAME()- 返回用戶數(shù)據(jù)庫用戶名select ERROR- 返回最后執(zhí)行的 Transact-SQL 語句的錯誤代碼select CONNECTIONS -返回自上次 SQL啟動以來連接或試圖連接的次數(shù)。select GETDATE() -當(dāng)前時間select CPU_BUSY/100 - 返回自上次啟動 SQL 以來 CPU 的工作時間，單位為毫 秒U(xiǎn)SE tempdb Sele

26、ct DBTS - 為當(dāng)前數(shù)據(jù)庫返回當(dāng)前 timestamp 數(shù)據(jù)類型的值。 這一 timestamp 值保證在數(shù)據(jù)庫中是唯一的。select IDENTITY -返回最后插入的標(biāo)識值Select IDLE-返回SQL自上次啟動后閑置的時間，單位為毫秒Select IO_BUSY-返回SQL自上次啟動后用于執(zhí)行輸入和輸出操作的時間，單位為毫秒Select LANGID- 返回當(dāng)前所使用語言的本地語言標(biāo)識符 (ID) 。Select LANGUAGE- 返回當(dāng)前使用的語言名Select LOCK_TIMEOUT - 當(dāng)前會話的當(dāng)前鎖超時設(shè)置，單位為毫秒。Select MAX_CONNECTION

27、S -返回SQL上允許的同時用戶連接的最大數(shù)。返回的 數(shù)不必為當(dāng)前配置的數(shù)值EXEC sp_configure- 顯示當(dāng)前服務(wù)器的全局配置設(shè)置Select MAX_PRECISION -返回 decimal 和 numeric 數(shù)據(jù)類型所用的精度級別，即 該服務(wù)器中當(dāng)前設(shè)置的精度。默認(rèn)最大精度38。select OPTIONS - 返回當(dāng)前 SET 選項(xiàng)的信息。Select PACK_RECEIVED -返回SQL自啟動后從網(wǎng)絡(luò)上讀取的輸入數(shù)據(jù)包數(shù)目。Select PACK_SENT -返回SQ自上次啟動后寫到網(wǎng)絡(luò)上的輸出數(shù)據(jù)包數(shù)目。Select PACKET_ERRORS -返回自SQL啟動

28、后，在 SQL連接上發(fā)生的網(wǎng)絡(luò)數(shù)據(jù)包 錯誤數(shù)。Select SERVERNAME返回運(yùn)行 SQL服務(wù)器名稱。Select SERVICENAME -返回SQL正在其下運(yùn)行的注冊表鍵名Select TIMETICKS -返回SQL服務(wù)器一刻度的微秒數(shù)Select TOTAL_ERRORS -返回SQL服務(wù)器自啟動后，所遇到的磁盤讀/寫錯誤數(shù)。Select TOTAL_READ -返回SQL服務(wù)器自啟動后讀取磁盤的次數(shù)。Select TOTAL_WRITE -返回SQL服務(wù)器自啟動后寫入磁盤的次數(shù)。Select TRANCOUNT - 返回當(dāng)前連接的活動事務(wù)數(shù)。Select VERSION -返回

29、SQL服務(wù)器安裝的日期、版本和處理器類型。系統(tǒng)函數(shù)：1. 字符轉(zhuǎn)換函數(shù)LOWER和 UPPER()LOWER(將字符串全部轉(zhuǎn)為小寫；UPPER(將字符串全部轉(zhuǎn)為大寫STR() 把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)。2. 去空格函數(shù)3. LTRIM() 把字符串頭部的空格去掉。4. RTRIM() 把字符串尾部的空格去掉。5. 取子串函數(shù)left()LEFT (<character_expression> ， <integer_expression>)返回 character_expression 左起 integer_expression 個字符。RIGHT()RIGHT (&

30、lt;character_expression> ， <integer_expression>) 返回 character_expression 右起 integer_expression 個字符。SUBSTRING()SUBSTRING (<expression> ， <starting_ position>， length)返回從字符串左邊第 starting_ position 個字符起 length 個字符的部分6. 字符串比較函數(shù)CHARINDEX()返回字符串中某個指定的子串出現(xiàn)的開始位置。PATINDEX() 返回字符串中某個指定的子串出現(xiàn)的開始位置。7. 字符串操作函數(shù)REPLACE() 返回被替換了指定子串的字符串。SPACE() 返回一個有指定長度的空白字符串。8. 數(shù)據(jù)類型轉(zhuǎn)換函數(shù)CAST()CAST (<expression> AS <data_ type> length )CONVERT()CONVERT (<data_