1、實驗 2 SQL Server 數據庫的管理4. 啟動查詢分析器， 在查詢分析器中使用 Transact-SQL 語句 CREATEDATABASE 創建 studb 數據庫。然后通過系統存儲過程 sp_helpdb 查看系統中的數據庫 信息CREATE DATABASE studb sp_helpdb5.在查詢分析器中使用 Transact-SQL語句ALTERDATABAS修改studb數據庫的設置，指定數據文件大小為5MB最大文件大小為20MB自動遞增大小 文 1MB。ALTER DATABASE studbMODIFY FILE(NAME=studb,SIZE=5MB,MAXSIZE=

2、20MB,FILEGROWTH=1MB)7. 使用企業管理器將 studb 數據庫的名稱更改為 student_dbALTER DATABASE studbMODIFY NAME=student_db8. 使用 Transact-SQL 語句 DROP DATABASE除 student_db 數據庫。DROP DATABASE student_db實驗 3 SQL Server 數據表的管理5. 使用 Transact-SQL 語句 CREATTABLE在 studentsdb 數據庫中創建 gradeCREATE TABLE grade ( 學號 char(4), 課程編號 char(4)

3、, 分數 decimal(5) )8. 使用 Transact_SQL 語句 INSERT INTO.VALUES 向 studentsdb 數據庫的 grade 表插入以下數據：學號 課程編號 分數0004 0001 80USE studentsdbGOINSERT INTO grade VALUES(0004,0001,80)9. 使用Transact_SQL語句ALTER TABL修改curriculum 表的“課程編號” 列，使之為非空。ALTER TABLE curriculumALTER COLUM課程編號 char(4)NOT NULLGO10. 使用Transact_SQL語句

4、ALTER TABL修改grade表的“分數”列，使其數 據類型為 real 。ALTER TABLE gradeALTER COLU MN 分數 real11. 分別使用企業管理器和 Transact_SQL語句DELETE刪除studentsdb數據庫 的 grade 表中學號為 0004 的成績記錄。DELETE grade WHERE學號=000413. 使用 Transact_SQL 語句 UPDAT修改 studentsdb 數據庫的 grade 表 中學號為0003 、課程編號為 0005 、分數為 90的成績記錄。UPDATE grade SET分數 =90WHERE 學號=0

5、003 and 課程編號 =000514. 使用 Transact_SQL 語句 ALTER.ADD為 studentsdb 數據庫的 grade表添加一個名為“備注”的數據列，其數據類型為VARCHAR(20。)ALTER TABLE grade ADD 備注 VARCHAR(20) NULL15. 分 別 使 用 企 業 管 理 器 和 Transact_SQL 語 句 DROPTABLE 刪 除 studentsdb 數據庫中的 grade 表。DROP TABLE grade實驗4數據查詢1. 在studentsdb數據庫中，使用下列SQL語句將輸出什么？（1）15（2）劉衛張衛馬東錢

6、達東方郭文肖海張明（3）KELLY（4）kellykellykelly（5）9.48683298050513819.32737905308881579.27361849549570399.4339811320566032（6）238（7）2011 10102. 在studentsdb數據庫中使用SELECTS句進行基本查詢。（1）SELECT 姓名，學號，姓名，出生日期from student_info（2）SELECT 姓名，家庭住址 from student_infowhere 學號=0002（3）劉衛平0001張衛民0002馬東0003錢達理0004東方牧00053. 使用SELECT語

7、句進行條件查詢（1）SELECT學號，分數 from gradewhere 分數 80 SELECT av分數）from gradewhere 學號=0003SELECT課程編號,count（課程編號）from gradegroup by課程編號 SELECT姓名，出生日期 from student_infoorder by 出生日期 asc SELECT學號，姓名 FROM student_info WHERE 姓名 LIKE 張%4. 嵌套查詢（4）SELECT課程編號,分數 FROM gradewhere 學號=0001 and 分數 （SELECT max（分數）from gradew

8、here學號 =0002)5. 多表查詢(3) SELECT s.學號,s.姓名,c.課程名稱，g.分數FROM student_info s,grade g,curriculum cwhere s.學號=g.學號and s.性別=男and c. 課程編號=g.課程編號(4) select 學號 ,max( 分數 )from gradegroup by 學號(5) SELECT s.學號，姓名,sum(g.分數)FROM student_info s left outer joingrade g on s. 學號 =g. 學號 group by s. 學號 , 姓名(6) insert int

9、o grade( 學號,課程編號 ,分數)values(0004,0006,76)SELECT c. 課程編號 , 課程名稱 ,count(g. 學號 ) FROM curriculum c right outer joingrade g on g. 課程編號 =c. 課程編號 group by c. 課程編號 , 課程名稱6. 使用UNION運算符將student_info 表中姓張”的學生的學號、姓名與curriculum表的課程編號、課程名稱返回在一個表中，且列名為u_編號、u_名稱，如圖1-8所示。select 學號 u_ 編號 , 姓名 u_ 名稱 from student_info

10、 where 姓名 like 張 % unionselect 課程編號 , 課程名稱 from curriculum7. 數據更新(4) delete from totalgradewhere 總成績 =null實驗 5 索引和視圖1. 分別使用企業管理器和Transact-SQL語句為studentsdb數據庫的student_info表格和curriculum 表創建主鍵索引。ALTER table student_infoALTER column 學號 char(4) not nullgoALTER table student_info add constraint PK_student

11、_info primary key( 學號)ALTER table curriculumALTER column 課程編號 char(4) not nullgoALTER table curriculum add constraint PK_curriculum primary key( 課程編號 )5. .分別使用企業管理器和系統存儲過程sp_helpindex 查看 grade 表和 student_info 表上的索引信息。use studentsdbgoexec sp_helpindex gradegouse studentsdbgoexec sp_helpindex student_

12、infogo16. 在 studentsdb 數據庫中，使用 Transact-SQL 語句 CREATEVIEW 建立一個名 為 v_stu_c 的視圖，顯示學生的學號、姓名、所學課程的課程編號，并利用視圖 查詢學號為 0003 的學生情況create view v_stu_casselect student_info.學號,student_info.姓名 curriculum.課程名稱，grade.課程編號from stude nt_info inner join grade on stude nt_info. 學號=grade.學號inner join curriculum on gra

13、de. 課程編號 =curriculum. 課程編號select *from v_stu_cwhere 學號=000317. 基于 student_info 表、 curriculum 表和 grade 表，建立一個名為 v_stu_g 的視圖，視圖中具有所有學生的學號、 姓名、課程名稱、 分數。使用視圖 v_stu_g 查詢學號為 0001 的學生的所有課程和成績create view v_stu_gasselect student_info.學號,student_info.姓名 curriculum.課程名稱,grade.分數from stude nt_info inner join gr

14、ade on stude nt_info. 學號=grade.學號inner join curriculum on grade. 課程編號 =curriculum. 課程編號select *from v_stu_gwhere 學號=000118. 分別使用企業管理器和 Transact-SQL 語句修改視圖 v_stu_c, 使之顯示學號、 姓名、每個學生所學課程數目。alter view v_stu_c（ 學號,姓名 ,課程數目 ）as select student_info.學號,student_info.姓名，count（grade.課程編號）as 課程數目from student_in

15、fo,gradewhere student_info.學號=grade.學號group by student_info.學號,student_info.姓名21. 利用視圖 v_stu_i 為 student_info 表添加一行數據：學號為 0015、姓名為 陳婷、性別為女。insert into v_stu_infovalues（0015,陳婷,女）22. 利用視圖 v_stu_i 刪除學號為 0015 的學生記錄。delete from v_stu_infowhere 學號 =001523. 利用視圖 v_stu_g 修改姓名為劉衛平的學生的高等數學的分數為 84 update v_st

16、u_gset 分數 =84where 姓名=劉衛平 and 課程名稱 =高等數學 24. 使用 Transact-SQL 語句 DROP VIEW刪除視圖 v_stu_c 和 v_stu_g。 drop view v_stu_c,v_stu_g實驗 6 數據完整性1為 sutdentsdb 數據庫創建一個規則，限制所輸入的數據為 7 位 0-9 的數字。（1）SELECT*INTO stu_phone FROM student_infoALTER TABLE stu_ph one ADD 電話號碼 CHAR（7）NULL（2）CREATE RULE phone_ruleASphone LIKE

17、 0-9 0-9 0-9 0-9 0-9 0-9 0-9（3）sp_bindrule phone_rule,stu_phone. 電話號碼 （4）出錯原因：與該列所綁定的規則發生沖突；需要修改：改1234yyyy 為234567phone_rule規則不能對其他操作（如 DELETE進行規則檢查3創建一個規則 stusex_rule ，將其綁定到 stu_phone 表的“性別”列上，保證輸入的性別 值只能是“男”或“女” 。CREATE RULE stusex_ruleAS sex in（男,女）sp_bindrule stusex_rule,stu_phone. 性別 4使用系統存儲過程

18、sp_help 查詢 stusex_rule 規則列表，使用 sp_helptext 查詢 stusex_rule 規則的文本，使用 sp_rename 將 stusex_rule 規則更名為 stu_s_rule。sp_help stusex_rulesp_helptext stusex_rulesp_rename stusex_rule,stu_s_rule5刪除 stu_s_rule 規則。Sp_unbindrule stu_phone. 性別 DROP RULE stu_s_rule6在 stuedentdb 數據庫中，建立日期、貨幣和字符等數據類型的默認對象。（1） CREATE D

19、EFAULT df_dateAS 2006-4-12GOCREATE DEFAULT df_charAS unknownGOCREATE DEFAULT df_moneyAS $100GO(2) CREATE TABLE stu_fee(學號 char(10)NOT NULL, 姓名 char(8)NOT NULL, 學費 money, 交費日期 datetime, 電話號碼 char(7)(3) Sp_bindefault df_money,stu_fee. 學費 GOSp_bindefault df_date,stu_fee. 交費日期 GOSp_bindefault df_char,st

20、u_fee. 電話號碼 GO(4) INSERT INTO stu_fee學號,姓名)values(0001,劉衛平)INSERT INTO stu_fee學號,姓名，學費)values(0001,張衛民,$120)INSERT INTO stu_fee學號,姓名，學費，交費日期)VALUES(0001,馬東,$110,2006-5-12)( 5) sp_unbindefault stu_fee. 電話號碼 DROP DEFAULT df_charGOsp_unbindefault stu_fee. 交費日期 DROP DEFAULT df_dateGO sp_unbindefault stu

21、_fee. 學費 DROP DEFAULT df_moneyGO8為student_info表添加一列，命名為院系”，創建一個默認對象 stu_d_df,將其綁定到 student_info 表的“院系” 列上，時期默認值為“信息院” ，對 student_info 表進行插入操作， 操作完成后，刪除該默認對象。分別使用企業管理器和查詢分析器實現。ALTER TABLE student_info ADD 院系 CHAR(12)NULLCREATE DEFAULT stu_d_dfAS 信息院 sp_b in default stu_d_df,stude nt_info. 院系INSERT st

22、udent_info(學號,姓名，院系)values(0001,劉衛平,土木工程) sp_unbindefault student_info. 院系 DROP DEFAULT stu_d_df9.在studentsdb數據庫中用CREATE TABL語句創建表stu_con，并同時創建約束。(1) CREATE TABLE stu_con(學號 char(4)CONSTRAINT pk_sid PRIMARY KE學號),姓名 char(8)CONSTRAINT uk_name UNIQUE,性別 char（2）CONSTRAINT df_sex DEFAULT!,出生日期 datetimeC

23、ONSTRAINT ck_beday CHEC出生日期 1988-1-1）,家庭住址 varchar（50） INSERT stu_con學號，姓名,出生日期）VALUES（0009,張小東,1989-4-6）INSERT stu_con學號，姓名 性別，出生日期）VALUES（0010,李梅,女,1983-8-5）INSERT stu_con學號，姓名，出生日期）VALUES（OO11,王強,1988-9-10） INSERT stu_con學號，姓名，出生日期）VALUES（OO12,王強,1989-6-3） 結果分析：第一、三條命令順利執行，第二、四條命令不能執行。第二行語句 INSER

24、T 語句與 COLUMN CHECK 約束 ck_beday 沖突。該沖突發生于數據 庫 studentsdb ，表 stu_con, column 出生日期 。第四條語句違反了 UNIQUE KEY 約束 uk_name 。不能在對象 stu_con 中插入重復鍵。（3） ALTER TABLE stu_conDROP CONSTRAINT pk_sid,uk_name,df_sex,ck_beday11.在查詢分析器中，為 studentsdb數據庫的grade表添加外鍵約束（FOREIGN KEY，要 求將學號”設置為外鍵，參照表為student_info，外鍵名為ufk_sid。使用系

25、統存儲過程 sp_help 查看 grade 表的外鍵信息。ALTER TABLE student_infoADD PRIMARY KEY學號）GOALTER TABLE gradeADD CONSTRAINT ufk_sid FOREIGN KE學號 ） REFERENCES student_inf學號）GOsp_help grade在grade表中插入表1-2所示記錄，觀察 SQL Server會做何處理，為什么？如何解決所 產生的問題 ?答：學號 0100 顯示為 100；課程編號 0001 顯示為 1.ALTER TABLE gradeDROP CONSTRAINT ufk_sid實驗

26、 7 Transact-SQL 程序設計1. 結果顯示：張明華顯示的僅為第二個姓張的記錄2. DECLARE grademax int, grademin int, gradesum intSELECT grademax=max分數）,grademin=min（分數）,gradesum=sum（分數）FROM gradeSELECT grademax, grademin, gradesum3. DECLARE row intSET row=（SELECT COUNT（*）FROM grade）SELECT row4. DECLARE intCId int, intErrorCode intIN

27、SERT INTO curriculum課程編號，課程名稱，學分）VALUES（0006,VB程序設計,2）SELECT in tCld= ide ntity,i ntErrorCode= errorSELECT in tCId, in tErrorCode第一次顯示：NULL 0第二次顯示：NULL 0curriculum表中數據的變化：第一次：0006 VB程序設計 2第二次：0006 VB程序設計 25. 在studentsdb數據庫的student_info表中，以 性別為分組條件，分別統計男生和女生 人數。SELECT COUNT（性另U） FROM student_infoGROU

28、P BY生另【J6. 在grade表中，使用適當函數找出高等數學”課程的最高分、最低分和平均分。SELECT MAX分數）AS最高分,MIN（分數）AS最低分,AVG（分數）AS平均分FROM gradeWHERE課程編號=（SELECT 課程編號 FROM curriculumWHERE課程名稱=高等數學）7. 定義一個datetime型局部變量 student,以存儲當前日期。計算student_info表中的學生的年齡，并顯示學生的姓名、年齡。在以下代碼的劃線部分填入適當內容，以實現上述功能。DECLARE stude nt datetimeSET stude nt=getdate（）S

29、ELECT姓名,year（student）-year（出生日期）AS 年齡FROM stude nt_info8. 運行代碼，寫出運行結果。運行結果為：82332259. 在局部變量stu_id中存儲了學號值。編寫代碼查詢學號為0001的學生的各科平均成績，如果平均分=60則顯示 你的成績及格了，恭喜你！”否則顯示 你的成績不及格”。IF （SELECT AVG（分數）FROM grade where 學號=0001）60）PRINT 你的成績不及格ELSEPRINT 你的成績及格了 ，恭喜你！ ！10. 運行代碼段，寫出運行的結果。counter的值現在為：1counter的值現在為：2co

30、unter的值現在為：3counter的值現在為：4counter的值現在為：5counter的值現在為：6counter的值現在為：7counter的值現在為：8counter的值現在為：911. 查詢grade表。如果分數大于等于 90,顯示A;如果分數大于等于 80小于90，顯示B;如果分數大于等于 70小于80,顯示C;如果分數大于等于 60小于70,顯示D;其他 顯示E。在以下代碼的劃線部分填入適當內容完成上述功能。SELECT學號,分數,等級=CASEWHEN分數 =90 THEN AWHEN分數 =80 AND 分數 =70 AND 分數 =60 AND 分數 70 THEN D

31、ELSE EENDFROM grade12. 計算 grade 表的分數列的平均值。如果小于 80,則分數增加其值的 5%;如果分數 的最高值超過 95,則終止該操作。在以下代碼劃線處填入適當的內容以完成上述功能。WHILE (SELECT AVG分數)FROM grade)95BREAKELSEBREAKEND13. 編寫代碼計算并顯示 n = 1+2+3+.+20。DECLARE n int,a intSELECT n=1,a=1WHILE a=20BEGINSET n=n+aSET a=a+1ENDPRINT n = 1+2+3+.+20PRINT n14. 編寫代碼計算并顯示 1100

32、 之間的所有完全平方數。例如, 81 = 92,則稱 81 為完 全平方數。DECLARE a intSELECT a=1WHILE a=a*aPRINT CONVERT(char(3),a*a)+ 是完全平方數 SET a=a+1END15. 計算 1100 以內的所有的素數。DECLARE n in t,i intSET n=2PRINT素數是：WHILE n=100BEGINSET i=2WHILE iSQRT( n)PRINT nSET n= n+1END16. 在studentsdb數據庫中，使用游標查詢數據。(1) DECLARE stu_cursor CURSORGLOBAL S

33、CROLL DYNAMICFORSELECT * FROM stude nt_infoWHERE生另U =男(2) OPEN stu_cursor(3) FETCH NEXT FROM stu_cursorWHILE fetch_status=OBEGINFETCH NEXT FROM stu_cursorEND(4) CLOSE stu_cursor17. 使用游標修改數據。(1) OPEN stu_cursor(2) UPDATE stude nt_i nfoSET出生日期=Dateadd(Year,1,出生日期)WHERE生名Like 馬% AND性別=男(3) CLOSE stu_cu

34、rsor18. 聲明游標變量stu_c,使之關聯stu_cursor游標，利用stu _c查詢年齡在69 月份出生的學生信息。SELECT * FROM stude nt_infoWHERE DATEPART(MON出生日期)=619. 使用系統存儲過程 sp_cursor_list顯示在當前作用域內的游標及其屬性。OPEN stu_cursorDECLARE report CURSOREXEC sp_cursor_list cursor_return=report OUTPUT,cursor_scope=2CLOSE stu_cursorFETCH NEXT FROM report實驗 8 存儲過程和觸發器1 執行代碼之后， studentsdb 數據庫的存儲過程中出現 lletters_print ，表明命令已經成功 執行。EXECUTE letter_print2. EXECUTEstu_info 馬東 修改為CREATE PROCEDURE stu_info name varchar（40）= 劉衛平 ASSELECT a.學