(整理)SQLServer數(shù)據(jù)庫基本知識點.

1、精品文檔SQL Server 數(shù)據(jù)庫基本知識點一、數(shù)據(jù)類型數(shù)據(jù)類型類型描述bit整型bit數(shù)據(jù)類型是整型， 其值只能是0 、 1 或空值。這種數(shù)據(jù)類型用于存儲只有兩種可能值的數(shù)據(jù)，如Yes 或 No 、 True或 Fa lse、On或 Offint整型int數(shù)據(jù)類型可以存儲從 -2 31 (-2147483648)到2 31(2147483 647)之間的整數(shù)。存儲到數(shù)據(jù)庫的幾乎所有數(shù)值型的數(shù)據(jù)都可以用這種數(shù)據(jù)類型。這種數(shù)據(jù)類型在數(shù)據(jù)庫里占用4個字節(jié)smallint整型smallint數(shù)據(jù)類型可以存儲從-2 15 (-32768) 到 2 15 (32767)之間的整數(shù)。這種數(shù)據(jù)類型對存儲一

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

3、20億到 9220億之間的數(shù)是否常用是精品文檔精品文檔smallmoneyfloatrealdatetimeSmalldatetimecursortimestampUniqueidentifier據(jù)，精確到貨幣單位的萬分之一貨幣型smallmoney 數(shù)據(jù)類型用來表示和貨幣值。這種數(shù)據(jù)類型能存儲從 -214748.3648到214748.3647之間的數(shù)據(jù)，精確到貨幣單位的萬分之一近似數(shù)值型float數(shù)據(jù)類型是一種近似數(shù)值是類型，供浮點數(shù)使用。說浮點數(shù)是近似的，是因為在其圍不是所有的數(shù)都能精確表示。浮點數(shù)可以是從 -1.79E+308到1.79E+308之間的任意數(shù)近似數(shù)值型real數(shù)據(jù)類型像

4、浮點數(shù)一樣，是近似數(shù)值類型。它可以表示數(shù)值在 -3.40E+38到 3.40E+38之間的浮點數(shù)日期時間型datetime 數(shù)據(jù)類型用來表示日是期和時間。這種數(shù)據(jù)類型存儲從1753年1月1日到 9999 年12月 3 1日間所有的日期和時間數(shù)據(jù)， 精確到三百分之一秒或 3.33毫秒日期時間型smalldatetime數(shù)據(jù)類型用來表示從 1900年1月1日到 2079年 6 月 6 日間的日期和時間，精確到一分鐘特殊數(shù)據(jù)型cursor數(shù)據(jù)類型是一種特殊的是數(shù)據(jù)類型，它包含一個對游標的引用。這種數(shù)據(jù)類型用在存儲過程中，而且創(chuàng)建表時不能用特殊數(shù)據(jù)型timestamp數(shù)據(jù)類型是一種特殊的數(shù)據(jù)類型，用來

5、創(chuàng)建一個數(shù)據(jù)庫圍的唯一數(shù)碼。一個表中只能有一個timestamp列。每次插入或修改一行時，timestamp列的值都會改變。盡管它的名字中有 “time ”， 但 timestamp列不是人們可識別的日期。在一個數(shù)據(jù)庫里， timestamp值是唯一的特殊數(shù)據(jù)型Uniqueidentifier數(shù)據(jù)類型用來存儲一個全局唯一標識符，即GUID 。 GUID確實是全局唯一的。這個數(shù)幾乎沒有機會在另一個系統(tǒng)中被重建?？梢允褂镁肺臋n精品文檔charvarchartextncharnvarcharntextbinaryNEWID函數(shù)或轉(zhuǎn)換一個字符串為唯一標識符來初始化具有唯一標識符的列字符型char 數(shù)

6、據(jù)類型用來存儲指定長度的定長非統(tǒng)一編碼型的數(shù)據(jù)。當定義一列為此類型時，你必須指定列長。當你總能知道要存儲的數(shù)據(jù)的長度時，此數(shù)據(jù)類型很有用。例如，當你按郵政編碼加4個字符格式來存儲數(shù)據(jù)時，你知道總要用到 10個字符。此數(shù)據(jù)類型的列寬最大為8000 個字符字符型varchar數(shù)據(jù)類型， 同 char 類型一樣，用來存儲非統(tǒng)一編碼型字符數(shù)據(jù)。 與 char型不一樣， 此數(shù)據(jù)類型為變長。當定義一列為該數(shù)據(jù)類型時，你要指定該列的最大長度。它與 char 數(shù)據(jù)類型最大的區(qū)別是，存儲的長度不是列長，而是數(shù)據(jù)的長度字符型text 數(shù)據(jù)類型用來存儲大量的非統(tǒng)一編碼型字符數(shù)據(jù)。這種數(shù)據(jù)類型最多可以有 2 31 -

7、1 或 20億個字符統(tǒng)一編碼字符型nchar數(shù)據(jù)類型用來存儲定長統(tǒng)一編碼字符型數(shù)據(jù)。統(tǒng)一編碼用雙字節(jié)結(jié)構(gòu)來存儲每個字符，而不是用單字節(jié) ( 普通文本中的情況 ) 。它允大量的擴展字符。此數(shù)據(jù)類型能存儲 4000 種字符， 使用的字節(jié)空間上增加了一倍統(tǒng)一編碼字符型nvarchar數(shù)據(jù)類型用作變長的統(tǒng)一編碼字符型數(shù)據(jù)。此數(shù)據(jù)類型能存儲 4000種字符， 使用的字節(jié)空間增加了一倍統(tǒng)一編碼字符型ntext數(shù)據(jù)類型用來存儲大量的統(tǒng)一編碼字符型數(shù)據(jù)。這種數(shù)據(jù)類型能存儲 2 30-1 或?qū)⒔?10 億個字符，且 使用的字節(jié)空間增加了一倍二進制數(shù)據(jù)類型binary數(shù)據(jù)類型用來存儲可達8000字節(jié)長的定長的二進

8、制數(shù)據(jù)。當輸入表的容接近相同的長度時，你應(yīng)該使用這種數(shù)據(jù)類型是是精品文檔精品文檔varbinary二進制數(shù)據(jù)類型varbinary數(shù)據(jù)類型用來存儲可達 8000字節(jié)長的變長的二進制數(shù)據(jù)。當輸入表的容大小可變時，你應(yīng)該使用這種數(shù)據(jù)類型image二進制數(shù)據(jù)類型image數(shù)據(jù)類型用來存儲變長的二進制數(shù)據(jù)，最大可達231 -1或大約 20 億字節(jié)二、常用語句(用到的數(shù)據(jù)庫 Northwind)查詢語句簡單的 Transact-SQL查詢只包括選擇列表、FROM子句和 WHERE 子句。它們分別說明所查詢列、查詢的表或視圖、以及搜索條件等。例如，下面的語句查詢Customers 表中公司名稱為 “Alf

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

10、指定的列名排列順序相同。例如：SELECT ContactName, AddressFROM Customers3、更改列標題在選擇列表中，可重新指定列標題。定義格式為：列標題as 列名列名 列標題如果指定的列標題不是標準的標識符格式時，應(yīng)使用引號定界符，例如，下列語句使用漢字顯示列標題：SELECT ContactNameas 聯(lián)系人名稱 , Address as地址FROM Customers4、刪除重復行SELECT語句中使用ALL 或 DISTINCT選項來顯示表中符合條件的所有行或刪除其中重復精品文檔精品文檔的數(shù)據(jù)行，默認為 ALL 。使用 DISTINCT選項時，對于所有重復的數(shù)據(jù)

11、行在SELECT返回的結(jié)果集合中只保留一行。SELECT DISTINCT(Country)FROM Customers5、限制返回的行數(shù)使用 TOP n PERCENT選項限制返回的數(shù)據(jù)行數(shù)，TOP n 說明返回n 行，而 TOP nPERCENT時，說明n 是表示一百分數(shù)，指定返回的行數(shù)等于總行數(shù)的百分之幾。例如：SELECT TOP 2 *FROM CustomersSELECT TOP 20 PERCENT *FROM Customers(二 )FROM 子句FROM 子句指定SELECT語句查詢及與查詢相關(guān)的表或視圖。在 FROM 子句中最多可指定256 個表或視圖，它們之間用逗號分

12、隔。在FROM 子句同時指定多個表或視圖時，如果選擇列表中存在同名列，這時應(yīng)使用對象名限定這些列所屬的表或視圖。例如在Orders 和 Customers表中同時存在CustomerID列，在查詢兩個表中的 CustomerID時應(yīng)使用下面語句格式加以限定：select * from Orders,Customerswhere Orders.CustomerID =Customers.CustomerID在 FROM 子句中可用以下兩種格式為表或視圖指定別名：表名 as 別名表名 別名select * fromOrders as a,Customers as bwhere a.Customer

13、ID =b.CustomerIDSELECT 不僅能從表或視圖中檢索數(shù)據(jù)，它還能夠從其它查詢語句所返回的結(jié)果集合中查詢數(shù)據(jù)。例如：select * from Customers where CustomerID in (select CustomerID from Orders whereEmployeeID=4)此例中，將SELECT返回的結(jié)果集合給予一別名CustomerID ，然后再從中檢索數(shù)據(jù)。(三 ) 使用 WHERE子句設(shè)置查詢條件WHERE 子句設(shè)置查詢條件，過濾掉不需要的數(shù)據(jù)行。例如下面語句查詢年齡大于20 的數(shù)據(jù)：select CustomerID from Orders w

14、here EmployeeID=4WHERE 子句可包括各種條件運算符：精品文檔精品文檔比較運算符 (大小比較 )： >、 >= 、 =、 <、 <= 、 <>、 !>、 !<圍運算符 (表達式值是否在指定的圍)：BETWEENANDNOT BETWEENAND列表運算符 (判斷表達式是否為列表中的指定項)： IN ( 項 1,項 2 )NOT IN ( 項 1,項 2 )模式匹配符 (判斷值是否與指定的字符通配格式相符):LIKE 、 NOT LIKE空值判斷符 (判斷表達式是否為空)： IS NULL 、 NOT IS NULL邏輯運算符 (

15、用于多條件的邏輯連接)：NOT 、 AND 、 OR1、圍運算符例：age BETWEEN 10 AND 30相當于 age>=10 AND age<=302、列表運算符例：country IN ('Germany','China')3、模式匹配符例：常用于模糊查找，它判斷列值是否與指定的字符串格式相匹配?？捎糜?char 、varchar 、 text 、 ntext 、 datetime和 smalldatetime等類型查詢?？墒褂靡韵峦ㄅ渥址喊俜痔?% ：可匹配任意類型和長度的字符插入語句語法： Insert into表 (字段 1,字段

16、2,.字段 n) values( 值 1,值 2,.值 n)例如： Insert into Region(RegionID, RegionDescription)values(5,'Southern')更新語句語法： update 表 set 字段 1 = 值 1,字段 2= 值 2,字段 n= 值 n where 條件如：update Customers set ContactName='John',Address ='Avda. de la'whereCustomerID='ALFKI'where條件同查詢語句刪除語句語法：

17、delete from表 where條件如：delete from Customers where CustomerID='ALFKI'where條件同查詢語句清空表：truncate table表 清空表中所有數(shù)據(jù)，表中如果有自增長字段，該字段在插入時會重新開始注意：由于更新語句與刪除語句會更改表數(shù)據(jù)對公司業(yè)務(wù)主要表進行更新與刪除操作時一定要先精品文檔精品文檔進行備份，然后進行操作備份語句select * into Customers20101019from Customers目標表（ Customers20101019）不存在時會自動創(chuàng)建三、存儲過程優(yōu)點：1.存儲過程只在創(chuàng)

18、造時進行編譯， 以后每次執(zhí)行存儲過程都不需再重新編譯，而一般 SQL語句每執(zhí)行一次就編譯一次 , 所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。2.當對數(shù)據(jù)庫進行復雜操作時 ( 如對多個表進行 Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。3. 存儲過程可以重復使用 , 可減少數(shù)據(jù)庫開發(fā)人員的工作量4. 安全性高 , 可設(shè)定只有某此用戶才具有對指定存儲過程的使用權(quán)語法CREATE PROC EDURE owner. procedure_name ; number parameter data_type VARYING =

19、default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 參數(shù)owner擁有存儲過程的用戶 ID 的名稱。 owner 必須是當前用戶的名稱或當前用戶所屬的角色的名稱。procedure_name新存儲過程的名稱。過程名必須符合標識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。;number是可選的整數(shù)，用來對同名的過程分組，以便用一條DROP PROCEDURE語句即可將同組的過程一起除去。例如，名為orders的應(yīng)用程序使用的過程可以命名為

20、精品文檔精品文檔orderproc;1 、 orderproc;2等。 DROP PROCEDURE orderproc語句將除去整個組。如果名稱中包含定界標識符，則數(shù)字不應(yīng)包含在標識符中，只應(yīng)在procedure_name前后使用適當?shù)亩ń绶?。parameter過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值，或者該值設(shè)置為等于另一個參數(shù)） 。存儲過程最多可以有2.100個參數(shù)。使用符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以

21、用在其它過程中。默認情況下， 參數(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ù)目的限制。VARYING指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，容可以變化）。僅適用于游標參數(shù)。default參數(shù)的默認值。 如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或NULL

22、。如果過程將對該參數(shù)使用LIKE關(guān)鍵字，那么默認值中可以包含通配符（ % 、_、 和 ）。OUTPUT表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXECUTE 。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。Text 、ntext和 image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標占位符。如：CREATE PROCEDURE CustOrdersDetail OrderID intASSELECT ProductName,UnitPrice=ROUND(Od.UnitPrice, 2),Quantity,Discount=CONVERT(int, Discount

23、* 100),ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice),2)FROM Products P, Order Details OdWHERE Od.ProductID = P.ProductID and Od.OrderID = OrderIDGO四：游標：游標（ Cursor）是處理數(shù)據(jù)的一種法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標提供了在結(jié)果集中一次以行或者多行前進或向后瀏覽數(shù)據(jù)的能力。我們可以把游標當精品文檔精品文檔作一個指針， 它可以指定結(jié)果中的任位置，然后允用戶對指定位置的數(shù)據(jù)

24、進行處理。1. 聲明游標2. 打開游標3. 讀取游標數(shù)據(jù)4. 關(guān)閉游標5. 釋放游標常用于需要把多行的數(shù)據(jù)進行拼接處理如：declare ContactName varchar(50)declare AllContactName varchar(5000)set AllContactName =DECLARE GetContactName_Cursor CURSORFOR select ContactName from Customers where CustomerId = 'ALFKI'OPEN GetContactName_CursorFETCH NEXT FROM Ge

25、tContactName_Cursor into ContactNameWHILE FETCH_STATUS = 0BEGINsetAllContactName= AllContactName+ContactNameprintAllContactNameFETCH NEXT FROM GetContactName_Cursor into ContactNameENDCLOSE GetContactName_CursorDEALLOCATE GetContactName_Cursor五：常用系統(tǒng)函數(shù)和變量：系統(tǒng)變量：select ERROR-返回最后執(zhí)行的 Transact-SQL語句的錯誤代碼

26、 (integer)select IDENTITY-返回最后插入的標識值Select USER_NAME()-返回用戶數(shù)據(jù)庫用戶名select ERROR-返回最后執(zhí)行的 Transact-SQL語句的錯誤代碼select CONNECTIONS-返回自上次 SQL 啟動以來連接或試圖連接的次數(shù)。select GETDATE() - 當前時間select CPU_BUSY/100- 返回自上次啟動 SQL 以來 CPU 的工作時間， 單位為毫秒USE tempdb Select DBTS- 為當前數(shù)據(jù)庫返回當前timestamp 數(shù)據(jù)類型的值。 這一timestamp值保證在數(shù)據(jù)庫中是唯一的。

27、select IDENTITY -返回最后插入的標識值Select IDLE-返回 SQL 自上次啟動后閑置的時間，單位為毫秒Select IO_BUSY-返回 SQL 自上次啟動后用于執(zhí)行輸入和輸出操作的時間，單位為毫秒Select LANGID-返回當前所使用語言的本地語言標識符(ID) 。Select LANGUAGE-返回當前使用的語言名Select LOCK_TIMEOUT-當前會話的當前鎖超時設(shè)置，單位為毫秒。Select MAX_CONNECTIONS- 返回 SQL 上允的同時用戶連接的最大數(shù)。返回的數(shù)精品文檔精品文檔不必為當前配置的數(shù)值EXEC sp_configure- 顯

28、示當前服務(wù)器的全局配置設(shè)置Select MAX_PRECISION -返回 decimal和 numeric 數(shù)據(jù)類型所用的精度級別，即該服務(wù)器中當前設(shè)置的精度。默認最大精度38。select OPTIONS- 返回當前 SET 選項的信息。Select PACK_RECEIVED-返回 SQL 自啟動后從網(wǎng)絡(luò)上讀取的輸入數(shù)據(jù)包數(shù)目。Select PACK_SENT-返回 SQ 自上次啟動后寫到網(wǎng)絡(luò)上的輸出數(shù)據(jù)包數(shù)目。Select PACKET_ERRORS-返回自 SQL 啟動后，在 SQL 連接上發(fā)生的網(wǎng)絡(luò)數(shù)據(jù)包錯誤數(shù)。Select SERVERNAME -返回運行 SQL 服務(wù)器名稱。S

29、elect SERVICENAME - 返回 SQL 正在其下運行的注冊表鍵名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- 返回當前連接的活動事務(wù)數(shù)。Select VERSION-返回 SQL 服務(wù)器安裝的日期、版本和處理器類型。系統(tǒng)函數(shù)：1. 字符轉(zhuǎn)換函數(shù)LOWER() 和

30、UPPER()LOWER() 將字符串全部轉(zhuǎn)為小寫； UPPER() 將字符串全部轉(zhuǎn)為大寫 STR() 把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)。2. 去空格函數(shù)LTRIM() 把字符串頭部的空格去掉。 RTRIM() 把字符串尾部的空格去掉。3. 取子串函數(shù) left()LEFT (<character_expression>， <integer_expression>)返回 character_expression左起integer_expression個字符。RIGHT()RIGHT (<character_expression>， <integer_exp

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