上機(jī)實(shí)驗(yàn)指導(dǎo)

1、上機(jī)實(shí)驗(yàn)指導(dǎo)實(shí)驗(yàn)內(nèi)容和思考參考答案數(shù)據(jù)庫技術(shù)與應(yīng)用實(shí)踐教程SQL Server 2005上機(jī)實(shí)驗(yàn)指導(dǎo)（實(shí)驗(yàn)思考）一參考答案實(shí)驗(yàn)1:安裝及管理工具使用 略。 實(shí)驗(yàn)2: SQL Server數(shù)據(jù)庫的管理 二、實(shí)驗(yàn)內(nèi)容及步驟4.單擊 新建查詢”打開查詢?cè)O(shè)計(jì)器窗口， 在查詢?cè)O(shè)計(jì)器窗口 中使用 Transact-SQL 語句 CREATE DATABASE 創(chuàng)建 studb數(shù)據(jù)庫。然后通過系統(tǒng)存儲(chǔ)過程sp_helpdb查看系統(tǒng)中的數(shù)據(jù)庫信息。 CREATE DATABASE studb ONNAMEstudb_dat FILENAMEC:DataBasestudb.mdf EXEC sp_helpdb

2、5 . 在查詢?cè)O(shè)計(jì)器中使用 Transact-SQL 語句ALTERDATABASE 修改studb數(shù)據(jù)庫的設(shè)置，指定 數(shù)據(jù)文件大小為 5MB ,最大文件大小為 20MB ,自動(dòng)遞增大 小為 1MB。 ALTER DATABASE studb MODIFY FILENAMEstudb_data SIZE 5MBMAXSIZE 20MB FILEGROWTH1MB 6 .在查詢?cè)O(shè)計(jì)器中為 studb數(shù)據(jù)庫增加一個(gè)日志文 件，命名為 studb_Log2，大小為 5MB，最大文件大小為10MB。 ALTER DATABASE studb ADD LOG FILENAME studb_log2FIL

3、ENAMEstudb_log2.ldf SIZE 5MB MAXSIZE 10MB 8 . 使用 Transact -SQL 語句 DROP DATABASE 刪 除 student_db 數(shù)據(jù)庫。DROP DATABASE student_db 實(shí) 驗(yàn)3 SQL Server數(shù)據(jù)表的管理 二、實(shí)驗(yàn)內(nèi)容及步驟5.使用 Transact-SQL 語句 CREATE TABLE 在 studentsdb數(shù)據(jù) 庫中創(chuàng)建 grade 表。 CREATE TABLE grade 學(xué)號(hào) char 4NULL 課程編號(hào) char 4 NULL 分?jǐn)?shù) char 5 NULL ON PRIMARY 8 使用 T

4、ransact-SQL 語句 INSERT INTO.VALUES 向 studentsdb 數(shù)據(jù)庫的 grade 表插入數(shù) 據(jù)： 學(xué)號(hào)為 0004，課程編號(hào)為 0001，分?jǐn)?shù)為 80。 INSERT grade VALUES 00040001 80 9 使用 Transact-SQL 語句 ALTER TABLE 修改 curriculum 表的 “課程編號(hào) ”列，使之為 非空。 ALTER TABLE curriculum ALTER COLUMN 課程編 號(hào) NOT NULL 10 使用 Transact-SQL 語句 ALTER TABLE修改grade表的 分?jǐn)?shù)”列，使其數(shù)據(jù)類型為r

5、eal。ALTER TABLE grade ALTER COLUMN 分?jǐn)?shù) real 11 使 用 Transact-SQL 語 句 ALTER TABLE 修 改 student_info 表的 “姓名 ”列，使其列名為 “學(xué)生姓名 ”，數(shù)據(jù)類型為 vacharlO,非空。 alter table student_info add 學(xué)生姓名 varchar10 NOTNULL update student_info set 學(xué)生姓名姓名 alter table student_info drop column 姓名 1 2分別使用 SQL Server 管理平臺(tái)和 Transact-SQL

6、語句 DELETE 刪除 studentsdb 數(shù)據(jù)庫的 grade 表中學(xué)號(hào)為 OOO4 的成績記錄。 DELETE grade WHERE 學(xué)號(hào) OOO4 13使用 Transact-SQL 語句 UPDATE 修改 studentsdb 數(shù)據(jù)庫的 grade 表中學(xué)號(hào) 為0003、課程編號(hào)為 0005、分?jǐn)?shù)為90的成績記錄。UPDATE grade SET 分?jǐn)?shù) 9O WHERE 學(xué)號(hào) OOO3 and 課程 編號(hào) 0005 14使用 Transact-SQL 語句 ALTER.ADD 為studentsdb數(shù)據(jù)庫的grade表添加一個(gè)名為 備注”的數(shù)據(jù) 列，其數(shù)據(jù)類型為 VARCHAR

7、20 。 ALTER TABLE grade ADD 備注 VARCHAR20 NULL 15 分別使用 SQL Server 管理平臺(tái)和 Transact-SQL 語句 DROP TABLE 刪除 studentsdb 數(shù)據(jù)庫中 grade 表。 DROP TABLE studentsdb.dbo.grade 三、實(shí)驗(yàn)思考 1使用 Transact-SQL 語 句刪除在 studentsdb 數(shù)據(jù)庫的 grade 表添加的 “備注 ”數(shù)據(jù) 列。 ALTER TABLE grade DROP COLUMN 備注實(shí)驗(yàn) 4 數(shù) 據(jù)查詢 二、實(shí)驗(yàn)內(nèi)容及步驟 在 2 studentsdb 數(shù)據(jù)庫中使

8、用 SELECT 語句進(jìn)行基本查詢。 （1）在 student_info 表 中，查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、出生日期信息。 SELECT 學(xué)號(hào) 姓名 出生日期 FROM student_info （2）查詢學(xué)號(hào)為 0002 的學(xué)生的姓名和家庭住址。 SELECT 姓名 家庭住址 FROM student_info WHERE 學(xué)號(hào) 0002 （3）找出所有男同 學(xué)的學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào) 姓名 FROM student_info WHERE 性別 男 3使用 SELECT 語句進(jìn)行條件查詢 （ 1） 在 grade 表中查找分?jǐn)?shù)在 8090 范圍內(nèi)的學(xué)生的學(xué)號(hào)和分 數(shù)。 SELECT

9、 學(xué) 號(hào) 分 數(shù) FROM grade WHERE 分 數(shù) BETWEEN 80 AND 90（ 2）在 grade 表中查詢課程編號(hào)為0003 的學(xué)生的平均分。 SELECT avg 分?jǐn)?shù) AS 平均分 FROM grade WHERE 課程編號(hào) 0003 （ 3）在 grade 表中查 詢學(xué)習(xí)各門課程的人數(shù)。 SELECT 課程編號(hào) COUNT AS 人數(shù) FROM gradeGROUP BY 課程編號(hào) （ 4）將學(xué)生按出生 日期由大到小排序。 SELECT FROM student_info ORDER BY 出生日期 DESC （ 5）查詢所有姓 “張 ”的學(xué)生的學(xué)號(hào)和 姓名。 SEL

10、ECT FROM student_info WHERE 姓名 LIKE 張 4對(duì) student_info 表，按性別順序列出學(xué)生的學(xué)號(hào)、姓名、 性別、出生日期及家庭住址，性別相同的按學(xué)號(hào)由小到大排 序。 SELECT 學(xué)號(hào) 姓名 性別 出生日期 家庭住址 FROM student_info ORDER BY 性別 學(xué)號(hào) 5使用 GROUP BY 查 詢子句列出各個(gè)學(xué)生的平均成績。 SELECT 學(xué)號(hào) SUM 分 數(shù) / COUNT AS 平均成績 FROM grade GROUP BY 學(xué)號(hào) 6使用 UNION 運(yùn)算符將 student_info 表中姓 “張 ”的學(xué)生 的學(xué)號(hào)、 姓名與 c

11、urriculum 表的課程編號(hào)、 課程名稱返回在 一個(gè)表中，且列名為 u_編號(hào)、u_名稱，如圖1-8所示。圖 1-8聯(lián)合查詢結(jié)果集 SELECT學(xué)號(hào)AS u_編號(hào) 姓名AS u_ 名稱 FROM student_infoWHERE 姓名 LIKE 張 UNIONSELECT 課程編號(hào) AS u_編號(hào) 課程名稱 AS u_名稱 FROM curriculum 7 嵌套查詢 （1）在 student_info 表中查 找與“劉衛(wèi)平 ”性別相同的所有學(xué)生的姓名、出生日期。SELECT 姓名，出生日期 FROM student_info WHERE 性別 SELECT 性別 FROM student

12、_info WHERE 姓名劉衛(wèi)平 （2）使用 IN 子查詢查找所修課程編號(hào)為 0002、 0005 的 學(xué)生學(xué)號(hào)、 姓名、 性別。 SELECT 學(xué)號(hào)，姓名， 性別 FROMstudent_info WHERE student_info. 學(xué)號(hào) IN SELECT 學(xué)號(hào) FROM grade WHERE 課 程 編 號(hào) IN0002 ，0005 （3 ）列 出學(xué)號(hào)為 0001 的學(xué)生的分?jǐn)?shù)比 0002 號(hào)的學(xué)生的最低分 數(shù)高的課程編號(hào)和分?jǐn)?shù)。 SELECT 課程編號(hào)，分?jǐn)?shù) FROM grade WHERE 學(xué)號(hào) 0001 AND 分?jǐn)?shù) ANY SELECT 分?jǐn)?shù) FROM grade WHE

13、RE 學(xué)號(hào) 0002 列出學(xué)號(hào)為 0001 的學(xué)生 的分?jǐn)?shù)比 （ 4）學(xué)號(hào)為 0002 的學(xué)生的最高成績還要高的課 程編號(hào)和分?jǐn)?shù)。 SELECT 課程編號(hào)，分?jǐn)?shù) FROM grade WHERE 學(xué)號(hào) 0001 AND 分?jǐn)?shù) SELECT MAX 分?jǐn)?shù) FROMgrade WHERE 學(xué)號(hào) 0002 8連接查詢 （ 1）查詢分?jǐn)?shù) 在 8090 范圍內(nèi)的學(xué)生的學(xué)號(hào)、姓名、分?jǐn)?shù)。 SELECT student_info.學(xué)號(hào)，姓名，分?jǐn)?shù) FROM student_info , grade WHERE student_info. 學(xué) 號(hào) grade. 學(xué) 號(hào) AND 分 數(shù) BETWEEN 80 A

14、ND 90（2）查詢學(xué)習(xí)“ C語言程序設(shè)計(jì)”課程的學(xué)生的學(xué)號(hào)、姓名、分?jǐn)?shù)。 SELECT student_info. 學(xué)號(hào)， 姓名，分?jǐn)?shù) FROM student_infoINNER JOIN grade ON student_info.學(xué)號(hào) grade.學(xué)號(hào) INNER JOIN curriculum ON 課 程名稱 C 語言程序設(shè)計(jì)（ 3）查詢所有男同學(xué)的選課情況，要求列出學(xué)號(hào)、姓名、課程名稱、分?jǐn)?shù)。 SELECT a.學(xué)號(hào)a.姓名 c.課程名稱 b.分?jǐn)?shù) FROM student_info a INNERJOIN grade b ON a.學(xué)號(hào) b.學(xué)號(hào) INNER JOIN curr

15、iculum c ON b.課 程編號(hào)c.課程編號(hào) WHERE a.性別 男 （4）查詢每個(gè)學(xué)生的所選課程的最高成績，要求列出學(xué)號(hào)、姓名、課程編號(hào)、分?jǐn)?shù)。SELECT a.學(xué)號(hào)c.姓名a.課程編號(hào)a.分?jǐn)?shù)FROM grade a INNER JOIN student_info c ON a.學(xué)號(hào) c.學(xué)號(hào) WHERE a. 分?jǐn)?shù) SELECT MAXb. 分?jǐn)?shù) FROM grade b WHERE b.學(xué)號(hào)a學(xué)號(hào)（5）查詢所有學(xué)生的總成績，要求 列出學(xué)號(hào)、姓名、總成績，沒有選修課程的學(xué)生的總成績?yōu)?空。 提示：使用左外連接。SELECT a.學(xué)號(hào)a.姓名b.總成績 FROM student_i

16、nfo a LEFT OUTER JOIN SELECT 學(xué)號(hào) SUM 分?jǐn)?shù) AS 總成績 FROM grade GROUP BY 學(xué)號(hào) b ON a學(xué)號(hào)b.學(xué)號(hào) 為（6） grade表添加數(shù)據(jù)行：學(xué)號(hào)0004、課程編號(hào)為 0006、分?jǐn)?shù)為 76。查詢所有課程的選修情況， 要求列出課程編號(hào)、課程名稱、選修人數(shù)， curriculum 表中 沒有的課程列值為空。 提示： 使用右外連接。 INSERT INTO grade 學(xué)號(hào) 課程編號(hào) 分?jǐn)?shù) VALUES0004 0006 76 SELECT a.課程編號(hào)a.課程名稱b.選修人數(shù)FROM curriculum a RIGHT OUTER JOI

17、N SELECT 課程編號(hào) COUNT AS 選修人數(shù) FROM grade GROUP BY 課程編號(hào) b ON a.課程編號(hào)b.課程編號(hào) 三、實(shí)驗(yàn)思考 1.查詢所有沒 有選修課程的學(xué)生信息，返回結(jié)果包括學(xué)號(hào)、姓名、性別。SELECT 學(xué)號(hào) 姓名 性別 FROM student_info WHERE 學(xué) 號(hào) NOT IN SELECT 學(xué)號(hào) FROM grade 2在 student_info 表和grade表之間實(shí)現(xiàn)交叉連接。 SELECT a.學(xué)號(hào)a姓名 a.性別 b.課程編號(hào) b.分?jǐn)?shù) FROM student_info a CROSSJOIN grade b 3查詢每個(gè)學(xué)生的所選課程

18、的成績，并列出學(xué) 號(hào)生成分組匯總行(總成績)和明細(xì)行(各課成績)。提示： 使用 SELECT 語句的 COMPUTE 選項(xiàng)。 SELECT 學(xué)號(hào) 分 數(shù) FROM grade ORDER BY 學(xué)號(hào) COMPUTE SUM 分?jǐn)?shù) BY 學(xué)號(hào) 4在查詢語句中 SELECT 、FROM 和 WHERE 選 項(xiàng)分別實(shí)現(xiàn)什么運(yùn)算？ 投影、選擇、自然連接 5在查詢 的 FROM 子句中實(shí)現(xiàn)表與表之間的連接有哪幾種方式？對(duì) 應(yīng)的關(guān)鍵字分別是什么？有三種： 內(nèi)連接 (INNER JOIN )、外連接( LEFT OUTERJOIN 、 RIGHT OUTER JOIN 、FULL OUTER JOIN )和

19、交叉連接( CROSS JOIN )。實(shí)驗(yàn) 5：索引 與視圖 二、實(shí)驗(yàn)內(nèi)容及操作步驟1分別使用 SQL Server管理平臺(tái)和 Transact-SQL 語句為 studentsdb 數(shù)據(jù)庫的 student_info 表和 curriculum 表創(chuàng)建主鍵索引。 CREATE UNIQUE CLUSTERED INDEX PK_curriculum ON curriculum 課程編號(hào) ON PRIMARY 9 在 studentsdb 數(shù)據(jù) 庫中，以 student_info 表為基礎(chǔ)，使用 SQL Server 管理平 臺(tái)建立名為 v_stu_i 的視圖，使視圖顯示學(xué)生姓名、性別、 家庭

20、住址。 10在 studentsdb 數(shù)據(jù)庫中， 使用 Transact-SQL 語句 CREATE VIEW 建立一個(gè)名為 v_stu_c 的視圖，顯示 學(xué)生的學(xué)號(hào)、姓名、所學(xué)課程的課程編號(hào)，并利用視圖查詢 學(xué)號(hào)為 0003 的學(xué)生情況。 CREATE VIEW v_stu_c ASSELECT a.學(xué)號(hào) a姓名 b.課程編號(hào) FROM student_info a INNER JOIN grade b ON a.學(xué)號(hào) b.學(xué)號(hào) SELECT FROM v_stu_c WHERE 學(xué)號(hào) 0003 11基于 student_info 表、 curriculum 表和 grade 表，建立一個(gè)名

21、為 v_stu_g 的視圖， 視圖中具有所有學(xué)生的學(xué)號(hào)、姓名、課程名稱、分?jǐn)?shù)。使用 視圖 v_stu_g 查詢學(xué)號(hào)為 0001 的學(xué)生的所有課程與成績， 如圖 1-9 所示。 圖 1-9 學(xué)號(hào)為 0001 的學(xué)生的視圖信息 CREATE VIEW v_stu_gASSELECT a.學(xué)號(hào) a姓名 c.課程名 稱 b.分?jǐn)?shù) FROM student_info a INNER JOIN grade b ON a.學(xué) 號(hào)b.學(xué)號(hào)INNER JOIN curriculum c ON b.課程編號(hào) c課程 編號(hào) SELECT FROM v_stu_g WHERE 學(xué)號(hào)?0001? 12分別 使用 SQL

22、 Server 管理平臺(tái)和 Transact-SQL 語句修改視圖 v_stu_c，使之顯示學(xué)號(hào)、姓名、每個(gè)學(xué)生所學(xué)課程數(shù)目。ALTER VIEW v_stu_c AS SELECT a. 學(xué)號(hào) a.姓名 COUNT AS 課程數(shù)目 FROM student_info a INNER JOIN grade b ON a學(xué)號(hào) b.學(xué)號(hào) GROUP BY a.學(xué)號(hào)a姓名13.使用 Transact-SQL語句 ALTER VIEW 修改視圖v_stu，使其具 有列名學(xué)號(hào)、姓名、性別。 ALTER VIEW v_stu_i 學(xué)號(hào)，姓 名，性別 AS SELECT 學(xué)號(hào)，姓名，性別 FROM student_info 14使用系統(tǒng)存儲(chǔ)過程sp_rename 將視圖 v_stu_i 更名為v_stu_info 。 sp_rename v_stu_i， v_stu_info 21 利用視圖 v_stu_i 為 student_info 表添加一行數(shù)據(jù)： 學(xué)號(hào)為 0015、姓 名為陳婷、性別為女。 INSERT INTO v_stu_iVALUES,0015?陳婷？女? 22.利用視圖 v_stu刪除學(xué)號(hào)為 0015 的學(xué)生記錄。 DELETE FROM v_stu_i WHERE 學(xué)號(hào) 0015 23.利用視圖 v_stu_g 修改