第一篇：復習資料(SQL)

以下復習資料僅供考試前作為復習的參考提綱，考試期間不得攜帶任何資料進入考場，切記！

一、將以下概念理解并牢記。

1、簡述SQL Server查詢分析器作用？

用于執行SQL語句，查看結果和分析查詢計劃等。

2、在SQL Server2000中數據庫文件有哪幾類？各有什么作用？ a．主數據庫文件：用于存儲數據庫的啟動信息和部分或全部數據。b．輔助數據庫文件：用于存儲主數據文件沒有存儲的其他數據。c．事務日志文件:用于存儲數據庫的修改信息。

3、SQL Server平臺有幾個系統數據庫？功能分別是什么？ a．master數據庫；記錄SQL Server數據庫系統中的全部信息。b．model數據庫；是所有數據庫和tempdb數據庫的創建模板。c．tempdb數據庫；系統的臨時數據庫。

d．msdb數據庫；所有任務調度，報警，操作員都存儲在里面，以及存儲備份歷史。

e．Northwind數據庫；方便學習數據庫系統提供的樣本數據庫。f．pubs數據庫；方便學習數據庫系統提供的樣本數據庫。

4、SQL Server包括哪些數據庫對象？

表，索引，視圖，關系圖表，默認，規則，觸發器，存儲過程，用戶。

5、SQL Server中包括幾種約束？各自的功能是什么？ a.主鍵約束：可以唯一地標識表中的每一行。b．唯一約束：用于指定一個或多個列的組合值是唯一性，以防止在列中輸入重復的值。

c．檢查約束：保證數據庫數據的完整性。d．默認值約束：可以為指定列定義一個默認值。e．空值約束：意味用戶還未為該列輸入值。f．外鍵約束：用來維護兩個表之間數據的一致性。

6、在指定數據源部分，表的連接包括幾種？在查詢結果上有什么不同？

a．交叉連接：集的行是兩個表的行數的乘積，集的列是兩個表的列數之和。

b．內連接：記錄的是兩個表中記錄的交集。

c．外連接：左外連接：有t1（左），t2表（右），連接結果包含t1表中的全部記錄和t2表中的相關記錄。

右外連接：有t1，t2表，連接結果包含t2表中的全部記錄和t1表中的相關記錄。

全外連接：顯示內連接的記錄和兩個表中不符合條件的全部記錄，并在左表或右表相應位置設置null。

d．自連接：顯示同一個表中一個字段值相同，其他字段不同的信息。

7、根據個人理解給出，完成一個表的創建，需要經過哪幾個步驟？ A．定義表的結構； B．設置約束； C．添加表數據。

8、備份包括幾種？各有什么特點？

a．完整數據庫備份：包括完整的數據庫信息。

b．差異性數據庫備份：復制自上一次完整數據庫備份之后修改過的數據庫頁。

c．事務日志備份：數據庫恢復模型必須是完整恢復模型或是批量日志恢復模型。

d．文件或文件組備份：必須與事務日志備份結合才有意義。

9、簡述SQL Server企業管理器作用？ 功能強大的圖形化數據庫管理工具

二、在復習的過程中，自行上機運行以下程序題，以便掌握響應的知識點。

1、編寫一條SELECT語句，從Leixing表中查詢一次性交易金額在1000元以上的交易類型信息。

SELECT FROM Leixing WHERE ‘交易金額’ in(‘>1000’)

2、編寫一條UPDATE語句，將Leixing表中ShouzhiLX為“投資”的更改為“股票”。

UPDATE Leixing SET ShouzhiLX=’投資’ WHERE ShouzhiLX=’股票’

3、編寫一條DELETE語句，刪除Leixing表中ShouzhiLX為“旅游”的項目，對此不再進行消費。Select from Leixing DELETE from ShouzhiLX where ‘旅游’

4、使用CREATE DATABASE語句創建以你的姓名拼音命名的數據庫，如姓名為張三，創建的數據庫的名稱是zhangsan，包括mdf文件，1個ndf文件和ldf文件，各個文件的屬性可以自行設置。create database linzitai on primary(name=linzitai_Data,filename=’d:linzitai_Data.mdf’, size=10MB,maxsize=50MB,filegrowth=25%),(name=linzitai_Data1,filename=’d:linzitai_Data1.ndf’, size=20MB,maxsize=50MB,filegrowth=40%)log on(name=linzitai_log,filename=’d:linzitai_log.ldf’, size=10MB,maxsize=unlimited,filegrowth=2MB)go

5、使用CREATE TABLE語句分別創建gerenlicai數據庫中的4個表，包ShouzhiMX、Leixing、Zhanghu、YonghuXX，并且在4個表中各自設置主鍵，在Leixing和ShouzhiMX之間建立外鍵，在Zhanghu和ShouzhiMX之間建立外鍵。（以上操作都要求通過SQL語句實現）①.create table YonghuXX(YHBianhao int NOT NULL, DengLuM varchar(32)primary key, Mima varchar(32), QuanXian int)在Leixing和ShouzhiMX之間建立外鍵: ②．create table ShouzhiMX（SZBianhao int primary key, LXBianhao int, JiaoyiSJ datetime(8), JiaoyiJE money(8), ZHBianhao int）Go create table Leixing(LXBianhao int foreign key references LXBianhao(ShouzhiMX), ShouZhi varchar(50), ShouzhiLX varchar(50), ShouzhiXX varchar(50))Go

6、在Leixing表和ShouzhiMX表之間建立內連接，查詢所有滿足條件的記錄，使用SQL語句完成。

Select a.LXBianhao from Leixing as a join ShouzhiMX as b on a.LXBianhao=b.LXBianhao

7、向Zhanghu表添加一條新的賬戶信息，使用SQL語句完成。alter table Zhanghu add 賬戶信息 varchar(50)

8、編寫一條DELETE語句，刪除ShouzhiMX表中消費在50元以下的數據。delete ShouzhiMX where ‘<50’

第二篇：sql試題

INNER JOIN操作

INNER JOIN操作用于組合兩個表中的記錄，只要在公共字段之中有相符的值?？梢栽谌魏蔚?FROM 子句中使用 INNER JOIN 運算。這是最普通的聯接類型。只要在這兩個表的公共字段之中有相符值，內部聯接將組合兩個表中的記錄。語法：FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2 說明：table1, table2參數用于指定從其中組合記錄的表的名稱。

field1, field2參數指定被聯接字段的名稱。如果這些字段不是數值的，被聯接的字段必須是相同的數據類型，并且包含相同類型的數據，但是它們不必名稱相同。

compopr參數用于指定關系比較運算符，如：“=”，“<”，“>”，“<=”，“>=”，或 “<>”。

如果在INNER JOIN操作中要聯接包含Memo 數據類型或 OLE Object 數據類型數據的字段，將會發生錯誤。在一個 INNER JOIN 之中，可以嵌套 LEFT JOIN 或 RIGHT JOIN，但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。

LEFT JOIN操作

LEFT JOIN操作用于在任何的 FROM 子句中，組合來源表的記錄。使用 LEFT JOIN 運算來創建一個左邊外部聯接。左邊外部聯接將包含了從第一個（左邊）開始的兩個表中的全部記錄，即使在第二個（右邊）表中并沒有相符值的記錄。語法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 說明：table1, table2參數用于指定要將記錄組合的表的名稱。

field1, field2參數指定被聯接的字段的名稱。且這些字段必須有相同的數據類型及包含相同類型的數據，但它們不需要有相同的名稱。

compopr參數指定關系比較運算符：“=”，“<”，“>”，“<=”，“>=” 或 “<>”。如果在INNER JOIN操作中要聯接包含Memo 數據類型或 OLE Object 數據類型數據的字段，將會發生錯誤。

RIGHT JOIN操作

RIGHT JOIN操作用于在任何的 FROM 子句中，組合來源表的記錄。使用 RIGHT JOIN 運算創建一個右邊外部聯接。右邊外部聯接將包含了從第二個（右邊）開始的兩個表中的全部記錄，即使在第一個（左邊）表中并沒有匹配值的記錄。語法：FROM table1 RIGHT JOIN table2 ON table1.field1 compopr table2.field2 說明：table1, table2參數用于指定要將記錄組合的表的名稱。

field1, field2參數指定被聯接的字段的名稱。且這些字段必須有相同的數據類型及包含相同類型的數據，但它們不需要有相同的名稱。

compopr參數指定關系比較運算符：“=”，“<”，“>”，“<=”，“>=” 或 “<>”。如果在INNER JOIN操作中要聯接包含Memo 數據類型或 OLE Object 數據類型數據的字段，將會發生錯誤。

你可以把存儲過程當做：把一系列語句合并到一起的這么一個整體

我覺得舉例說明比較好，給你個例子：

先將【表1】中ID號為50—2000的記錄刪除、再將【表2】中的這些記錄的狀態(STATUS)改為“已解除”：

delete 表1 where ID > 50 and ID < 2000

update 表2 set STATUS = '已解除' where ID > 50 and ID < 2000

正常情況下，以上兩條語句分步執行就可以了，如果要用存儲過程呢？

先建立存儲過程（以下的語法為Sybase數據庫的，其他數據庫類同）：

create procedure PRC_TEST(@start_ID int, @end_ID int)as

begin

delete 表1 where ID > @start_ID and ID < @end_ID

update 表2 set STATUS = '已解除' where ID > @start_ID and ID < @end_ID end

好了，執行這個語句，就將存儲過程PRC_TEST提交到數據庫里了，它有兩個參數：start_ID int 和 end_ID，代表起始和終止ID號，類型為整數型int

怎么用這個存儲過程呢？ 這樣執行：exc PRC_TEST 50, 2000

執行時，它先得到了兩個參數，50、2000，分別賦值給start_ID int 和 end_ID，然后按照這兩個參數分步執行封裝在存儲過程里的那兩條語句了。

如果你這樣執行：exc PRC_TEST 220, 8660

就是處理兩個表中ID介于220—8660之間的記錄了。

--------------

你也許會問了，既然可以分步執行的幾句SQL，為什么要費力的寫成存儲過程啊？

主要是(我的經驗和認識)：

1、使數據處理參數化，對經常使用的一系列SQL進行封裝，使其成為一個存儲過程的整體，在每次執行時只要更換執行參數即可，不用去改里面每句SQL的where子句

2、★★這個很重要★★，假設你要循環處理某些數據，例如需要使用“游標”、“Do...while...語句”…………時，就要用到存儲過程（或觸發器）

=======================

最后給你轉一個短文吧，這是書面上的概念：

將常用的或很復雜的工作，預先用SQL語句寫好并用一個指定的名稱存儲起來, 那么以后要叫數據庫提供與已定義好的存儲過程的功能相同的服務時,只需調用execute,即可自動完成命令。

那么存儲過程與一般的SQL語句有什么區別呢? 存儲過程的優點：

1.存儲過程只在創造時進行編譯，以后每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高數據庫執行速度。

2.當對數據庫進行復雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。

3.存儲過程可以重復使用,可減少數據庫開發人員的工作量

4.安全性高,可設定只有某此用戶才具有對指定存儲過程的使用權

存儲過程的種類：

1.系統存儲過程：以prc_（或sp_）開頭,用來進行系統的各項設定.取得信息.相關管理工作, 如 sp_help就是取得指定對象的相關信息

2.擴展存儲過程 以XP_開頭,用來調用操作系統提供的功能

exec master..xp_cmdshell 'ping 10.8.16.1'

3.用戶自定義的存儲過程,這是我們所指的存儲過程

測試table create table table1(id int,name char)insert into table1 select 1,'q' union all select 2,'r' union all select 3,'3' union all select 4,'5'

要求按指定的id順序(比如2,1,4,3)排列獲取table1的數據

方法1:使用union all,但是有256條數據的限制 select id,name from table1 where id=2 union all select id,name from table1 where id=1 union all select id,name from table1 where id=4 union all select id,name from table1 where id=3

方法2:在order by中使用case when select id ,name from t where id in(2,1,4,3)order by(case id when 2 then 'A' when 1 then 'B' when 4 then 'C' when 3 then 'D' end)

*以上兩種方法適合在數據量非常小的情況下使用

方法3:使用游標和臨時表

先建一個輔助表,里面你需要的順序插入,比如2,1,4,3 create table t1(id int)insert into t1 select 2 union all select 1 union all select 4 union all select 3 declare @id int--定義游標 declare c_test cursor for select id from t1

select * into #tmp from table1 where 1=2--構造臨時表的結構 OPEN c_test FETCH NEXT FROM c_test INTO @id WHILE @@FETCH_STATUS = 0 BEGIN--按t1中的id順序插數據到臨時表

insert into #tmp select id,name from table1 where id=@id FETCH NEXT FROM c_test INTO @id End Close c_test deallocate c_test

*該方法適合需要按照輔助表的順序重排table的順序時使用 方法4:分割字符串參數

select * into #tmp from table1 where 1=2--構造臨時表的結構 declare @str varchar(300),@id varchar(300),@m int,@n int set @str='2,1,4,3,'---注意后面有個逗號 set @m=CHARINDEX(',',@str)set @n=1 WHILE @m>0 BEGIN set @id=substring(@str,@n,@m-@n)--print @id insert into #tmp select id,name from table1 where id=convert(int,@id)set @n=@m+1 set @m=CHARINDEX(',',@str,@n)END *該方法比較有通用性

測試結果

id name---------------2 r 1 q 4 5 3 3

本文較長，包含了如下幾部分

1.2.3.4.5.使用不帶參數的存儲過程 使用帶有輸入參數的存儲過程

使用帶有輸出參數的存儲過程

使用帶有返回狀態的存儲過程

使用帶有更新計數的存儲過程

1使用不帶參數的存儲過程

使用 JDBC 驅動程序調用不帶參數的存儲過程時，必須使用 call SQL 轉義序列。不帶參數的 call 轉義序列的語法如下所示： {call procedure-name} 作為實例，在 SQL Server 2005 AdventureWorks 示例數據庫中創建以下存儲過程：

CREATE PROCEDURE GetContactFormalNames AS BEGIN

SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName FROM Person.Contact END

此存儲過程返回單個結果集，其中包含一列數據（由 Person.Contact 表中前十個聯系人的稱呼、名稱和姓氏組成）。

在下面的實例中，將向此函數傳遞 AdventureWorks 示例數據庫的打開連接，然后使用 executeQuery 方法調用 GetContactFormalNames 存儲過程。

public static void executeSprocNoParams(Connection con){ try { Statement stmt = con.createStatement();ResultSet rs = stmt.executeQuery(“{call dbo.GetContactFormalNames}”);

while(rs.next()){ System.out.println(rs.getString(“FormalName”));} rs.close();stmt.close();} catch(Exception e){ e.printStackTrace();} } 2使用帶有輸入參數的存儲過程

使用 JDBC 驅動程序調用帶參數的存儲過程時，必須結合 SQLServerConnection 類的 prepareCall 方法使用 call SQL 轉義序列。帶有 IN 參數的 call 轉義序列的語法如下所示：

{call procedure-name[([parameter][,[parameter]]...)]}

構造 call 轉義序列時，請使用 ?（問號）字符來指定 IN 參數。此字符充當要傳遞給該存儲過程的參數值的占位符?？梢允褂?SQLServerPreparedStatement 類的 setter 方法之一為參數指定值。可使用的 setter 方法由 IN 參數的數據類型決定。向 setter 方法傳遞值時，不僅需要指定要在參數中使用的實際值，還必須指定參數在存儲過程中的序數位置。例如，如果存儲過程包含單個 IN 參數，則其序數值為 1。如果存儲過程包含兩個參數，則第一個序數值為 1，第二個序數值為 2。作為如何調用包含 IN 參數的存儲過程的實例，使用 SQL Server 2005 AdventureWorks 示例數據庫中的 uspGetEmployeeManagers 存儲過程。此存儲過程接受名為 EmployeeID 的單個輸入參數（它是一個整數值），然后基于指定的 EmployeeID 返回雇員及其經理的遞歸列表。下面是調用此存儲過程的 Java 代碼：

public static void executeSprocInParams(Connection con){ try { PreparedStatement pstmt = con.prepareStatement(“{call dbo.uspGetEmployeeManagers(?)}”);pstmt.setInt(1, 50);ResultSet rs = pstmt.executeQuery();while(rs.next()){ System.out.println(“EMPLOYEE:”);System.out.println(rs.getString(“LastName”)+ “, ” + rs.getString(“FirstName”));System.out.println(“MANAGER:”);System.out.println(rs.getString(“ManagerLastName”)+ “, ” + rs.getString(“ManagerFirstName”));System.out.println();} rs.close();pstmt.close();} catch(Exception e){ e.printStackTrace();} } 3使用帶有輸出參數的存儲過程

使用 JDBC 驅動程序調用此類存儲過程時，必須結合 SQLServerConnection 類的 prepareCall 方法使用 call SQL 轉義序列。帶有 OUT 參數的 call 轉義序列的語法如下所示：

{call procedure-name[([parameter][,[parameter]]...)]}

構造 call 轉義序列時，請使用 ?（問號）字符來指定 OUT 參數。此字符充當要從該存儲過程返回的參數值的占位符。要為 OUT 參數指定值，必須在運行存儲過程前使用 SQLServerCallableStatement 類的 registerOutParameter 方法指定各參數的數據類型。

使用 registerOutParameter 方法為 OUT 參數指定的值必須是 java.sql.Types 所包含的 JDBC 數據類型之一，而它又被映射成本地 SQL Server 數據類型之一。有關 JDBC 和 SQL Server 數據類型的詳細信息，請參閱了解 JDBC 驅動程序數據類型。

當您對于 OUT 參數向 registerOutParameter 方法傳遞一個值時，不僅必須指定要用于此參數的數據類型，而且必須在存儲過程中指定此參數的序號位置或此參數的名稱。例如，如果存儲過程包含單個 OUT 參數，則其序數值為 1；如果存儲過程包含兩個參數，則第一個序數值為 1，第二個序數值為 2。

作為實例，在 SQL Server 2005 AdventureWorks 示例數據庫中創建以下存儲過程： 根據指定的整數 IN 參數(employeeID)，該存儲過程也返回單個整數 OUT 參數(managerID)。根據 HumanResources.Employee 表中包含的 EmployeeID，OUT 參數中返回的值為 ManagerID。

在下面的實例中，將向此函數傳遞 AdventureWorks 示例數據庫的打開連接，然后使用 execute 方法調用 GetImmediateManager 存儲過程：

public static void executeStoredProcedure(Connection con){ try { CallableStatement cstmt = con.prepareCall(“{call dbo.GetImmediateManager(?, ?)}”);cstmt.setInt(1, 5);cstmt.registerOutParameter(2, java.sql.Types.INTEGER);cstmt.execute();System.out.println(“MANAGER ID: ” + cstmt.getInt(2));} catch(Exception e){ e.printStackTrace();} } 本示例使用序號位置來標識參數?；蛘?，也可以使用參數的名稱（而非其序號位置）來標識此參數。下面的代碼示例修改了上一個示例，以說明如何在 Java 應用程序中使用命名參數。請注意，這些參數名稱對應于存儲過程的定義中的參數名稱：

CREATE PROCEDURE GetImmediateManager @employeeID INT, @managerID INT OUTPUT AS BEGIN

SELECT @managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @employeeID END

存儲過程可能返回更新計數和多個結果集。Microsoft SQL Server 2005 JDBC Driver 遵循 JDBC 3.0 規范，此規范規定在檢索 OUT 參數之前應檢索多個結果集和更新計數。也就是說，應用程序應先檢索所有 ResultSet 對象和更新計數，然后使用 CallableStatement.getter 方法檢索 OUT 參數。否則，當檢索 OUT 參數時，尚未檢索的 ResultSet 對象和更新計數將丟失。使用帶有返回狀態的存儲過程

使用 JDBC 驅動程序調用這種存儲過程時，必須結合 SQLServerConnection 類的 prepareCall 方法使用 call SQL 轉義序列。返回狀態參數的 call 轉義序列的語法如下所示：

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

構造 call 轉義序列時，請使用 ?（問號）字符來指定返回狀態參數。此字符充當要從該存儲過程返回的參數值的占位符。要為返回狀態參數指定值，必須在執行存儲過程前使用 SQLServerCallableStatement 類的 registerOutParameter 方法指定參數的數據類型。

此外，向 registerOutParameter 方法傳遞返回狀態參數值時，不僅需要指定要使用的參數的數據類型，還必須指定參數在存儲過程中的序數位置。對于返回狀態參數，其序數位置始終為 1，這是因為它始終是調用存儲過程時的第一個參數。盡管 SQLServerCallableStatement 類支持使用參數的名稱來指示特定參數，但您只能對返回狀態參數使用參數的序號位置編號。

作為實例，在 SQL Server 2005 AdventureWorks 示例數據庫中創建以下存儲過程：

CREATE PROCEDURE CheckContactCity(@cityName CHAR(50))AS BEGIN

IF((SELECT COUNT(*)FROM Person.Address WHERE City = @cityName)> 1)RETURN 1 ELSE

RETURN 0 END

該存儲過程返回狀態值 1 或 0，這取決于是否能在表 Person.Address 中找到 cityName 參數指定的城市。

在下面的實例中，將向此函數傳遞 AdventureWorks 示例數據庫的打開連接，然后使用 execute 方法調用 CheckContactCity 存儲過程：

public static void executeStoredProcedure(Connection con){ try { CallableStatement cstmt = con.prepareCall(“{? = call dbo.CheckContactCity(?)}”);cstmt.registerOutParameter(1, java.sql.Types.INTEGER);cstmt.setString(2, “Atlanta”);cstmt.execute();System.out.println(“RETURN STATUS: ” + cstmt.getInt(1));} cstmt.close();catch(Exception e){ e.printStackTrace();} } 5 使用帶有更新計數的存儲過程 使用 SQLServerCallableStatement 類構建對存儲過程的調用之后，可以使用 execute 或 executeUpdate 方法中的任意一個來調用此存儲過程。executeUpdate 方法將返回一個 int 值，該值包含受此存儲過程影響的行數，但 execute 方法不返回此值。如果使用 execute 方法，并且希望獲得受影響的行數計數，則可以在運行存儲過程后調用 getUpdateCount 方法。

作為實例，在 SQL Server 2005 AdventureWorks 示例數據庫中創建以下表和存儲過程：

CREATE TABLE TestTable(Col1 int IDENTITY, Col2 varchar(50), Col3 int);

CREATE PROCEDURE UpdateTestTable @Col2 varchar(50), @Col3 int AS BEGIN

UPDATE TestTable SET Col2 = @Col2, Col3 = @Col3 END;在下面的實例中，將向此函數傳遞 AdventureWorks 示例數據庫的打開連接，并使用 execute 方法調用 UpdateTestTable 存儲過程，然后使用 getUpdateCount 方法返回受存儲過程影響的行計數。

public static void executeUpdateStoredProcedure(Connection con){ try { CallableStatement cstmt = con.prepareCall(“{call dbo.UpdateTestTable(?, ?)}”);cstmt.setString(1, “A”);cstmt.setInt(2, 100);cstmt.execute();int count = cstmt.getUpdateCount();cstmt.close();

System.out.println(“ROWS AFFECTED: ” + count);} catch(Exception e){ e.printStackTrace();} }

1.sql存儲過程概述

在大型數據庫系統中，存儲過程和觸發器具有很重要的作用。無論是存儲過程還是觸發器，都是SQL 語句和流程控制語句的集合。就本質而言，觸發器也是一種存儲過程。存儲過程在運算時生成執行方式，所以，以后對其再運行時其執行速度很快。SQL Server 2000 不僅提供了用戶自定義存儲過程的功能，而且也提供了許多可作為工具使用的系統存儲過程。

存儲過程的概念

存儲過程（Stored Procedure）是一組為了完成特定功能的SQL 語句集，經編譯后存儲在數據庫。中用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。

在SQL Server 的系列版本中存儲過程分為兩類：系統提供的存儲過程和用戶自定義存儲過程。系統過程主要存儲在master 數據庫中并以sp_為前綴，并且系統存儲過程主要是從系統表中獲取信息，從而為系統管理員管理SQL Server 提供支持。通過系統存儲過程，MS SQL Server 中的許多管理性或信息性的活動（如了解數據庫對象、數據庫信息）都可以被順利有效地完成。盡管這些系統存儲過程被放在master 數據庫中，但是仍可以在其它數據庫中對其進行調用，在調用時不必在存儲過程名前加上數據庫名。而且當創建一個新數據庫時，一些系統存儲過程會在新數據庫中被自動創建。用戶自定義存儲過程是由用戶創建并能完成某一特定功能（如查詢用戶所需數據信息）的存儲過程。在本章中所涉及到的存儲過程主要是指用戶自定義存儲過程。

存儲過程的優點

當利用MS SQL Server 創建一個應用程序時，Transaction-SQL 是一種主要的編程語言。若運用Transaction-SQL 來進行編程，有兩種方法。其一是，在本地存儲Transaction-SQL 程序，并創建應用程序向SQL Server 發送命令來對結果進行處理。其二是，可以把部分用Transaction-SQL 編寫的程序作為存儲過程存儲在SQL Server 中，并創建應用程序來調用存儲過程，對數據結果進行處理存儲過程能夠通過接收參數向調用者返回結果集，結果集的格式由調用者確定；返回狀態值給調用者，指明調用是成功或是失?。话ㄡ槍祿斓牟僮髡Z句，并且可以在一個存儲過程中調用另一存儲過程。

我們通常更偏愛于使用第二種方法，即在SQL Server 中使用存儲過程而不是在客戶計算機上調用Transaction-SQL 編寫的一段程序，原因在于存儲過程具有以下優點：

（1）存儲過程允許標準組件式編程

存儲過程在被創建以后可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL 語句。而且數據庫專業人員可隨時對存儲過程進行修改，但對應用程序源代碼毫無影響（因為應用程序源代碼只包含存儲過程的調用語句），從而極大地提高了程序的可移植性。

（2）存儲過程能夠實現較快的執行速度

如果某一操作包含大量的Transaction-SQL 代碼或分別被多次執行，那么存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的，在首次運行一個存儲過程時，查詢優化器對其進行分析、優化，并給出最終被存在系統表中的執行計劃。而批處理的Transaction-SQL 語句在每次運行時都要進行編譯和優化，因此速度相對要慢一些。

（3）存儲過程能夠減少網絡流量

對于同一個針對數據數據庫對象的操作（如查詢、修改），如果這一操作所涉及到的 Transaction-SQL 語句被組織成一存儲過程，那么當在客戶計算機上調用該存儲過程時，網絡中傳送的只是該調用語句，否則將是多條SQL 語句，從而大大增加了網絡流量，降低網絡負載。

（4）存儲過程可被作為一種安全機制來充分利用

系統管理員通過對執行某一存儲過程的權限進行限制，從而能夠實現對相應的數據訪問權限的限制，避免非授權用戶對數據的訪問，保證數據的安全。（我們將在14 章“SQLServer 的用戶和安全性管理”中對存儲過程的這一應用作更為清晰的介紹）

注意：存儲過程雖然既有參數又有返回值，但是它與函數不同。存儲過程的返回值只是指明執行是否成功，并且它不能像函數那樣被直接調用，也就是在調用存儲過程時，在存儲過程名字前一定要有EXEC保留字。

2.SQL存儲過程創建

創建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數的 Transact-SQL 語句的集合。

可以創建一個過程供永久使用，或在一個會話中臨時使用（局部臨時過程），或在所有會話中臨時使用（全局臨時過程）。

也可以創建在 Microsoft? SQL Server? 啟動時自動運行的存儲過程。

語法

CREATE PROC [ EDURE ] procedure_name [;number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [...n ]

參數

procedure_name

新存儲過程的名稱。過程名必須符合標識符規則，且對于數據庫及其所有者必須唯一。

要創建局部臨時過程，可以在 procedure_name 前面加一個編號符(#procedure_name)，要創建全局臨時過程，可以在 procedure_name 前面加兩個編號符

(##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字符。指定過程所有者的名稱是可選的。

;number

是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為

orderproc;

1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前后使用適當的定界符。

@parameter

過程中的參數。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有 2.100 個參數。

使用 @ 符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其它過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其它數據庫對象的名稱。

data_type

參數的數據類型。所有數據類型（包括 text、ntext 和 image）均可以用作存儲過程的參數。不過，cursor 數據類型只能用于 OUTPUT 參數。如果指定的數據類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。

說明 對于可以是 cursor 數據類型的輸出參數，沒有最大數目的限制。

VARYING

指定作為輸出參數支持的結果集（由存儲過程動態構造，內容可以變化）。僅適用于游標參數。

default

參數的默認值。如果定義了默認值，不必指定該參數的值即可執行過程。默認值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT

表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將信息返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是游標占位符。

n

表示最多可以指定 2.100 個參數的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執行計劃時，請使用 RECOMPILE 選項。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發布。

說明 在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創建加密過程。

FOR REPLICATION

指定不能在訂閱服務器上執行為復制創建的存儲過程。.使用 FOR REPLICATION 選項創建的存儲過程可用作存儲過程篩選，且只能在復制過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

指定過程要執行的操作。

sql_statement

過程中要包含的任意數目和類型的 Transact-SQL 語句。但有一些限制。

n

是表示此過程可以包含多條 Transact-SQL 語句的占位符。

注釋

存儲過程的最大大小為 128 MB。

用戶定義的存儲過程只能在當前數據庫中創建（臨時過程除外，臨時過程總是在 tempdb 中創建）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 語句組合使用。

默認情況下，參數可為空。如果傳遞 NULL 參數值并且該參數在 CREATE 或 ALTER TABLE 語句中使用，而該語句中引用的列又不允許使用 NULL，則 SQL Server 會產生一條錯誤信息。為了防止向不允許使用 NULL 的列傳遞 NULL 參數值，應向過程中添加編程邏輯或為該列使用默認值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關鍵字)。

建議在存儲過程的任何 CREATE TABLE 或 ALTER TABLE 語句中都為每列顯式指定 NULL 或 NOT NULL，例如在創建臨時表時。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語句中沒有指定的話）。如果某個連接執行的存儲過程對這些選項的設置與創建該過程的連接的設置不同，則為第二個連接創建的表列可能會有不同的為空性，并且表現出不同的行為方式。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執行該存儲過程的連接使用相同的為空性創建臨時表。

在創建或更改存儲過程時，SQL Server 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設置。執行存儲過程時，將使用這些原始設置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設置在執行存儲過程時都將被忽略。在存儲過程中出現的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語句不影響存儲過程的功能。

其它 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在創建或更改存儲過程時不保存。如果存儲過程的邏輯取決于特定的設置，應在過程開頭添加一條 SET 語句，以確保設置正確。從存儲過程中執行 SET 語句時，該設置只在存儲過程完成之前有效。之后，設置將恢復為調用存儲過程時的值。這使個別的客戶端可以設置所需的選項，而不會影響存儲過程的邏輯。

第三篇：SQL語言匯總

1.UPDATE MARK

SET 總分=語文+數學+英語

SELECT 準考證號,姓名,總分

FROM MARK

WHERE 語文>=75 AND數學>=75 AND 英語>=75 AND 語文+數學+英語>=240

ORDER BY 總分 DESC

UPDATE MARK

SET 修正分數=高考分數*1.3

WHERE 籍貫='云南' OR 籍貫='福建'

SELECT AVG(高考分數)as平均分

FROM MARK

GROUP BY 性別

SELECT *

FROM cj

WHERE 期中成績>=80 OR 期末成績>=80 ORDER BY 課程號 ASC

UPDATE cj

SET平均成績=期中成績*0.2+期末成績*0.8

SELECT 書號,書名,作者

FROM gm

WHERE 書號 LIKE '理*' AND 單價>20 ORDER BY 單價 ASC

UPDATE gm

SET 金額=單價*數量

UPDATE Sale

SET 銷售金額=銷售數量*單價

UPDATE Wage

SET 實發工資=工資+獎金

SELECT 姓名,實發工資

FROM Wage

WHERE 姓名 LIKE '*輝'OR 姓名 LIKE ' *輝*' ORDER BY 年齡 DESC

SELECT 部門號, COUNT(*)as人數

FROM zhigong

GROUP BY 部門號

UPDATE kaoshi

SET平均成績=筆試成績*0.6+上機成績*0.4

SELECT 考號,姓名,平均成績

FROM kaoshi

WHERE 姓名 LIKE '黃*'

DELETE *

FROM mark

WHERE 學號='1003'

SELECT 學號,課程號,期末成績

FROM mark

WHERE 學號 LIKE '*1'

ORDER BY 期末成績 DESC

UPDATE fk

SET 超期罰款=單價*超期天數*0.005

SELECT *

FROM fk

WHERE 單價>=50 OR 超期天數>30 ORDER BY 超期罰款 DESC

UPDATE gp

SET 浮動盈虧=(市場現價-買入加個)*買入數量

SELECT 股票代碼,買入時間,浮動盈虧 FROM gp

WHERE 股票代碼 LIKE '6*' AND 浮動盈虧>0 ORDER BY 買入時間 ASC

UPDATE ah

SET 年齡=年齡+1

SELECT 寢室,COUNT(*)as 愛好球類人數 FROM ah

WHERE 愛好 LIKE '*球'

GROUP BY 寢室

UPDATE JHKC

SET 總價=單價*庫存量

SELECT 設備編號,設備名稱,單價

FROM JHKC

WHERE 庫存量<300 AND 單價<500 ORDER BY 單價 DESC

UPDATE HCSK

SET 票價=600

WHERE 航班號=F807

SELECT 航班號,起飛日期,票價

FROM HCSK

WHERE 出發地='福州' AND 目的地='巴黎' ORDER BY 票價 ASC

第四篇：sql常用語句

//創建臨時表空間

create temporary tablespace test_temp

tempfile 'E:oracleproduct10.2.0oradatatestservertest_temp01.dbf'size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//創建數據表空間

create tablespace test_data

logging

datafile 'E:oracleproduct10.2.0oradatatestservertest_data01.dbf'size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//創建用戶并指定表空間

create user username identified by password

default tablespace test_data

temporary tablespace test_temp;

//給用戶授予權限

//一般用戶

grant connect,resource to username;

//系統權限

grant connect,dba,resource to username

//創建用戶

create user user01 identified by u01

//建表

create table test7272（id number(10),name varchar2(20),age number(4),joindate date default sysdate,primary key(id));

//存儲過程

//數據庫連接池

數據庫連接池負責分配、管理和釋放數據庫連接

//

//創建表空間

create tablespace thirdspace

datafile 'C:/Program Files/Oracle/thirdspace.dbf' size 10mautoextend on;

//創建用戶

create user binbin

identified by binbin

default tablespace firstspace

temporary tablespace temp;

//賦予權限

GRANT CONNECT, SYSDBA, RESOURCE to binbin

//null與""的區別

簡單點說null表示還沒new出對象，就是還沒開辟空間

個對象裝的是空字符串。

//建視圖

create view viewname

as

sql

//建索引

create index indexname on tablename(columnname)

//在表中增加一列

alter table tablename add columnname columntype

//刪除一列

alter table tablename drop columnname

//刪除表格內容，表格結構不變

truncate table tableneme

//新增數據

insert into tablename()values()

//直接新增多條數據

insert into tablename()

selecte a,b,c

from tableabc

//更新數據 new除了對象，但是這“”表示

update tablename set columnname=? where

//刪除數據

delete from tablename

where

//union語句

sql

union

sql

//case

case

when then

else

end

第五篇：SQL語言

1．輸出任意兩個字符串變量的值（參考代碼）

declare @v1 varchar(12),@V2 varchar(12)

set @v1='中國'

set @v2='你好！'

select @v1+@v2

2．為以下程序糾錯

declare @v1 varchar(12),@V2 varchar(10)

set @v1='中國'

set @v2='你好！'

set @s='嗎！'

select @v1+@v2+@s

3．運行以下代碼，觀察以下全局變量的功能

select 34@@connections 返回當前服務器的連接數目

select 1@@rowcount 返回上一條T-SQL語句影響的數據行數 select @@error返回上一條T-SQL語句執行后的錯誤號 select @@procid返回當前存儲過程的ID號

select @@remserver 返回登錄記錄中遠程服務器的名字 select @@spid返回當前服務器進程的ID標識

select @@version 返回當前SQL Server服務器的版本和處理器類型 select @@language 返回當前SQL Server服務器的語言

select @@connections

4．輸入以下代碼并理解其含意

bggin

if 3>5

select 'true'

else print 'false'

end

go

begin

if 'a'<'A'

select 'true'

else print 'false'

end

go

begin

if '張'>'李'

select 'true'

else print 'false'

end

go

begin

if getdate()>2008-10-10

print'true'

else print'false'

end

5．附加數據庫student,并進行以下操作

use student

select * from xsda

以XSDa表為例，如果平均成績高于75分，則顯示“不錯！”，否則，顯示“加油呀！”。

declare @av1 char(12), @av2 char(12)

set @av1='不錯！'

set @av2='加油！'

if(select avg(總學分)from xsda)>75

begin

select @av1

end

else

select @av2

編寫一個程序，顯示每一同學的成績等級

select xh,zf,dj=

case

when zf>70 then '優秀'

when zf>65 then '良好'

when zf>=60 then '合格'

else '不合格'

end

from xsdn

6．求1-100偶數和（參考代碼）

declare @i int,@s int/*定義變量*/

set @i=0/*變量賦初值*/

set @s=0

while @i<=100/*條件*/

begin

set @s=@s+@i/*累加求和*/

set @i=@i+2/*偶數*/

end

print @s

7．求10！

8．為以下程序糾錯（求1-100的積）

declare @i int,@s int

set @i=0

set @s=0

while @i<=100 begin

set @s=@s*@iset @i=@i+2 end

print @s