實驗4 T-SQL語言.doc

實驗4 T-SQL語言實驗目的：1、 掌握Transact-SQL語言中的變量、運算符、函數(shù)；2、 掌握Transact-SQL語句編寫批處理和流程控制程序。3、 掌握游標的創(chuàng)建，打開，數(shù)據(jù)讀取，關閉，銷毀的基本方法實驗內(nèi)容和步驟：Transact-SQL語言是在SQL Server 2005 中使用的程序設計語言。不但包含了標準SQL語言部分，而且為了滿足實際應用中的編程需要，在Transact-SQL語言中還另外增加了一些語言要素，它們包括注釋、變量、運算符、函數(shù)和流程控制語句等。所有的語句都在查詢分析器中執(zhí)行。1. Transact-SQL語句實驗內(nèi)容1： 返回在程序執(zhí)行過程中上一條SQL語句影響的記錄數(shù)（全局變量ROWCOUNT），注意觀察結果窗口。use teach-打開teach 數(shù)據(jù)庫GO-查詢學生表中的記錄SELECT * FROM Student-查詢上一條SELECT語句影響的記錄數(shù)SELECT ROWCOUNT AS 第一次查詢返回的記錄數(shù)GO實驗內(nèi)容2： 查詢學生表的人數(shù)，將返回的記錄數(shù)賦給局部變量RowsUSE teachGODECLARE Rows int-聲明局部變量-給局部變量賦值SET Rows=(SELECT COUNT(*) FROM Student)-顯示局部變量的值SELECT Rows AS SELECT返回的記錄數(shù)GO實驗內(nèi)容3： 設置一個局部變量為表，進行插入和查詢操作- 定義變量為臨時表DECLARE Table_Example TABLE (Col_num int PRIMARY KEY, Col_text char (50)- 向該變量中添加兩行內(nèi)容INSERT INTO Table_Example VALUES (1, this is a)INSERT INTO Table_Example VALUES (2, table data type example)- 從臨時表中查詢SELECT * FROM Table_ExampleGO實驗內(nèi)容4：(練習，自己試著寫一下)設置一個局部變量其值為選修了課程的人數(shù)，并顯示提示： DECLARE Rows int-聲明局部變量-給局部變量賦值SET Rows=(SELECT COUNT(distinct sno) FROM SC )-顯示局部變量的值SELECT Rows AS 選課人數(shù)GO實驗內(nèi)容5：檢查sysdatabases中的每一個數(shù)據(jù)庫，使用數(shù)據(jù)庫標識號來確定數(shù)據(jù)庫名稱。USE master-打開master 數(shù)據(jù)庫GO-DB_NAME 是一個函數(shù)，功能是根據(jù)數(shù)據(jù)庫標識號，來返回數(shù)據(jù)庫的名稱SELECT dbid , DB_NAME(dbid) AS DB_NAMEFROM sysdatabases ORDER BY dbidGO實驗內(nèi)容6：從數(shù)據(jù)庫中返回學生表的第2列的名稱USE teachGO-定義局部變量，用于存放學生表的ID號DECLARE 學生表ID INT-OBJECT_ID是一個函數(shù)，功能是返回數(shù)據(jù)庫對象的編號SET 學生表ID= OBJECT_ID(Student)-COL_NAME是一個函數(shù)， 返回指定表中指定字段的名稱，即列名SELECT COL_NAME(學生表ID,2) AS 學生表中第2列的名稱GO實驗內(nèi)容7：簡單年、月、日函數(shù)SELECT MONTH(03/12/1998), DAY(03/12/1998),YEAR(03/12/1998)實驗內(nèi)容8：從系統(tǒng)當前日期中返回系統(tǒng)當前的年份數(shù)、月份數(shù)和天數(shù)。- GETDATE()函數(shù)返回系統(tǒng)當前日期SELECT YEAR(GETDATE() AS 當前年份, MONTH(GETDATE() AS 當前月份, DAY(GETDATE() AS 當前天數(shù)GO實驗內(nèi)容9：自己編寫函數(shù)。編寫一個函數(shù)，名稱為“函數(shù)_學生選課數(shù)”，要求根據(jù)輸入的學生學號，求此學生的選課數(shù)。USE teachGO-通過CREATE FUNCTION語句創(chuàng)建用戶的自定義函數(shù)“函數(shù)_學生選課數(shù)”CREATE FUNCTION 函數(shù)_學生選課數(shù)(xuehao AS char(9) -函數(shù)的輸入?yún)?shù))RETURNS int -返回結果類型BEGIN -函數(shù)體 DECLARE 選課數(shù) AS int SELECT 選課數(shù) =COUNT(cno) FROM SC WHERE Sno=xuehao RETURN 選課數(shù)ENDGO-使用自定義函數(shù)，求選課表（SC）中每位學生的選課數(shù)SELECT DISTINCT Student.Sno,Sname,選課數(shù)=dbo. 函數(shù)_學生選課數(shù)(SC.Sno)FROM Student,SCWHERE Student .Sno=SC.SnoGO實驗內(nèi)容10: (練習，自己試著寫一下)練習自定義函數(shù)，求每門課程的選課人數(shù)提示：USE teachGOCREATE FUNCTION 函數(shù)_選課人數(shù)(cno AS char(9) )RETURNS int BEGIN DECLARE 選課人數(shù) AS int SELECT 選課人數(shù) =COUNT(sno) FROM SC WHERE cno=cno RETURN 選課人數(shù)ENDGO-使用自定義函數(shù)，查詢每門課的選課人數(shù)（注：沒有人選的課程，不會出現(xiàn)）SELECT DISTINCT Course.Cno ,Cname ,選課人數(shù)=dbo. 函數(shù)_選課人數(shù)(SC.Cno)FROM Course,SCWHERE Co=SC.Cno GO-使用自定義函數(shù)，通過左外連接，保留了左邊被舍棄的元組SELECT DISTINCT Course.Cno ,Cname ,選課人數(shù)=dbo. 函數(shù)_選課人數(shù)(SC.Cno)FROM Course left join SC on Co=SC.Cno GO2. 游標的創(chuàng)建和使用游標（Cursor）是處理數(shù)據(jù)的一種方法，為了查看或者處理結果集中的數(shù)據(jù)，游標提供了在結果集中一次以行或者多行前進或向后瀏覽數(shù)據(jù)的能力。我們可以把游標當作一個指針，它可以指定結果中的任何位置，然后允許用戶對指定位置的數(shù)據(jù)進行處理。T-SQL游標：該游標是基于Declare Cursor 語法，主要用于Transaction-SQL腳本、存儲過程以及觸發(fā)器中。Transaction-SQL游標在服務器處理由客戶端發(fā)送到服務器的Transaction-SQL語句。使用游標要遵循以下順序：聲明游標打開游標讀取數(shù)據(jù)關閉游標刪除游標。1聲明游標實驗內(nèi)容1：以下是一個符合SQL-92標準的游標聲明，此游標是只讀的，只能從頭到尾順序提取數(shù)據(jù)。 DECLARE S_CUR1 CURSOR FOR SELECT * FROM STUDENT FOR READ ONLY實驗內(nèi)容2：以下是一個符合T-SQL擴展的游標聲明，此游標是動態(tài)游標，可前后滾動，可對姓名列進行修改。 DECLARE S_CUR2 CURSOR SCROLL DYNAMIC FOR SELECT * FROM STUDENTFOR UPDATE OF SName2. 打開游標聲明游標之后，要使用游標提取數(shù)據(jù)，必須先打開游標。使用OPEN語句可以打開游標。打開游標后，可以使用全局變量CURSOR_ROWS查看游標中數(shù)據(jù)行的數(shù)目。實驗內(nèi)容3：打開游標S_CUR1，輸出其行數(shù) OPEN S_CUR1 SELECT 游標s_cur1的數(shù)據(jù)行數(shù)=CURSOR_ROWS注意：通過聯(lián)機叢書來查詢CURSOR_ROWS，來理解其值的含義3讀取數(shù)據(jù)游標打開后，就可以使用FETCH命令從中讀取數(shù)據(jù)。具體語句格式見聯(lián)機叢書。實驗內(nèi)容4：從游標S_CUR1中提取數(shù)據(jù)，只能用NEXT提取數(shù)據(jù)。 FETCH NEXT FROM S_CUR1提示：選中該語句，點擊執(zhí)行，查看結果。再點擊執(zhí)行，查看結果。重復執(zhí)行。實驗內(nèi)容5：從游標S_CUR2中提取數(shù)據(jù)：OPEN S_CUR2-讀取游標第一行FETCH FIRST FROM S_CUR2-讀取下一行FETCH NEXT FROM S_CUR2-讀取上一行FETCH PRIOR FROM S_CUR2-讀取最后一行FETCH LAST FROM S_CUR2-讀取當前行的上二行FETCH RELATIVE-2 FROM S_CUR24關閉游標游標使用完后，要及時關閉游標，以釋放當前的結果集并解除定位在該游標記錄行上的游標鎖定。實驗內(nèi)容6：關閉游標S_CUR1CLOSE S_CUR15刪除游標關閉一個游標后，其數(shù)據(jù)結構仍存儲在系統(tǒng)中，需要的時候仍然可以再次使用OPEN語句打開和使用該游標。如果確定以后不再使用該游標，則可以刪除游標，將游標占用的系統(tǒng)空間釋放出來。實驗內(nèi)容7：刪除游標S_CUR1DEALLOCATE S_CUR13. 收獲體會此次實驗與之前的幾次試驗都大有所不同，此次實驗的內(nèi)容主要是關于Transact-SQL語言中的變量、運算符、函數(shù)以及游標的相關操作。對于