華聯(lián)學(xué)院《SQL數(shù)據(jù)庫系統(tǒng)》第2-5章例題及解答

1、 SQL數(shù)據(jù)庫系統(tǒng)第25章例題及解答第二章例題【例2-1】使用SQL語句，建立名為score的數(shù)據(jù)庫，包含一個(gè)主文件，一個(gè)次數(shù)據(jù)文件和一個(gè)事務(wù)日志文件。主文件的邏輯名稱為score_data，初始大小為3MB，最大為8MB，增長速度為10%。次文件的邏輯名稱為score_data1，初始大小為2MB，最大為5MB，增長速度為1MB。事務(wù)日志文件為score_log，初始大小為2MB，最大大小不受限制，增長速度為1MB。CREATE DATABASE scoreON PRIMARY(NAME=score_data,FILENAME=D:studentscore_data.mdf, -目錄要存在S

2、IZE=3MB,MAXSIZE=8MB,FILEGROWTH=10%),(NAME=score_data1,FILENAME=D:studentscore_data1.ndf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)LOG ON(NAME=score_log,FILENAME=D:studentscore_log.ldf,SIZE=2MB,FILEGROWTH=1MB)【例2-2】刪除score數(shù)據(jù)庫。DROP DATABASE student【例2-3】將student數(shù)據(jù)庫收縮到只保留60%的空間。DBCC SHRINKDATABASE(student,60

3、)【例2-4】在student數(shù)據(jù)庫中，創(chuàng)建學(xué)生表(student)。(1) 啟動(dòng)“SQL Server Management Studio”程序，在“對(duì)象資源管理器”中展開“數(shù)據(jù)庫”“student”數(shù)據(jù)庫節(jié)點(diǎn)。(2) 右擊“表”節(jié)點(diǎn)，在彈出的快捷菜單中選擇“新建表”命令，打開表設(shè)計(jì)窗口。根據(jù)表student的物理設(shè)計(jì)要求，輸入相應(yīng)的列名、選擇數(shù)據(jù)類型、是否為空及主鍵等情況。(3) 最后，點(diǎn)擊工具欄上的“保存”按鈕，或“文件”菜單中的“保存”命令，在彈出的“選擇名稱”對(duì)話框中，輸入“student”，點(diǎn)擊“確定”按鈕。【例2-5】使用對(duì)象資源管理器為student表建立s_id的主鍵約束、c

4、lass_id的外鍵約束、s_sex為“男”或“女”的檢查約束、nation默認(rèn)為“漢”和politic默認(rèn)為“團(tuán)員”的默認(rèn)約束。(1) 選擇student表，點(diǎn)右鍵，在彈出的快捷菜單中選擇“設(shè)計(jì)”，打開表設(shè)計(jì)窗口。(2) 選擇“s_id”字段，執(zhí)行“表設(shè)計(jì)器”“設(shè)置主鍵”菜單命令，或者點(diǎn)右鍵，選擇“設(shè)置主鍵”，此時(shí)，該字段前出現(xiàn)了主鍵標(biāo)志。(3) 單擊工具欄上的“關(guān)系”按鈕，或者“表設(shè)計(jì)器”菜單中的“關(guān)系”命令，彈出“外鍵關(guān)系”對(duì)話框，單擊“添加”按鈕在窗口左邊的子窗格中添加一個(gè)主外鍵關(guān)系并選中，再單擊展開“表和列規(guī)范”選項(xiàng)。(4) 單擊“表和列規(guī)范”選項(xiàng)后面的按鈕，彈出“表和列”對(duì)話框。在

5、“主鍵表”下拉選項(xiàng)列表中選擇表“class”，在其下面的下拉選項(xiàng)列表中選擇主鍵“class_id”；在“外鍵表”student下面對(duì)應(yīng)的下拉列表中選擇外鍵“class_id”。(5) 單擊“表和列”對(duì)話框的“確定”按鈕，回到“外鍵關(guān)系”對(duì)話框，修改名稱框內(nèi)容為“FK_student_class”，單擊“關(guān)閉”按鈕，即可完成“class”和“student”兩個(gè)表的主外鍵關(guān)系創(chuàng)建。(6) 單擊工具欄上的“管理CHECK約束”按鈕或“表設(shè)計(jì)器”菜單中的“CHECK約束”命令，彈出“CHECK約束”對(duì)話框。(7) 單擊“CHECK約束”對(duì)話框中的“添加”按鈕，左邊的窗格中添加一個(gè)CHECK約束，修改

6、名稱框中的內(nèi)容為“CK_student_sex”，單擊“常規(guī)”“表達(dá)式”欄目后面的按鈕，彈出“CHECK約束表達(dá)式”對(duì)話框，并編寫約束條件“s_sex=男 OR s_sex=女”。(8) 在表設(shè)計(jì)器窗口中，選擇所要修改的字段nation，在下面的常規(guī)屬性中，找到“默認(rèn)值或綁定”，在右側(cè)的文本框中輸入默認(rèn)值“漢”，如圖2-46所示。以同樣的方法，選擇“politic”字段，設(shè)置默認(rèn)值為“團(tuán)員”。(9) 按“Ctrl+S”組合鍵或單擊工具欄上的“保存”按鈕保存表設(shè)計(jì)?！纠?-6】使用對(duì)象資源管理器為class表建立class_name的唯一性約束。(1) 選擇class表，點(diǎn)右鍵，在彈出的快捷菜單

7、中選擇“設(shè)計(jì)”，打開表設(shè)計(jì)窗口。(2) 選擇字段class_name，點(diǎn)右鍵，選擇“索引/鍵(I)”。(3) 在彈出的“索引/鍵”對(duì)話框中，點(diǎn)擊左下角的“添加”按鈕，在“是唯一的”后面的下拉列表中，選擇“是”，將該“約束”的名稱改為“UK_class”，點(diǎn)擊常規(guī)中的列選項(xiàng)右側(cè)按鈕。(4) 在彈出的“索引列”對(duì)話框中的列名下拉列表中，選擇“class_name”字段，點(diǎn)擊“確定”按鈕。【例2-7】在對(duì)象資源管理器中，刪除student表的主外鍵約束。在對(duì)象資源管理器中，展開student表，再展開“鍵”，出現(xiàn)相應(yīng)的主外鍵約束名，選擇“FK_student_class”項(xiàng)，點(diǎn)右鍵，在快捷菜單中，選

8、擇“刪除”命令?！纠?-8】刪除student表中的class_id的外鍵約束和s_sex的檢查約束。ALTER TABLE studentDROP CONSTRAINT FK_student_classDROP CONSTRAINT CK_student_sex【例2-9】刪除學(xué)生表(student)和班級(jí)表(class)。USE STUDENTGODROP TABLE studentDROP TABLE class【例2-10】向student表中插入一條記錄，步驟如下：(1) 啟動(dòng)“Microsoft SQL Server Management Studio”，在“對(duì)象資源管理器”中依次

9、展開“服務(wù)器”“數(shù)據(jù)庫”“表”目錄，找到需要插入數(shù)據(jù)記錄的表。(2) 在student表上右擊，在彈出的快捷菜單中選擇“編輯前200行(E)”選項(xiàng)?！纠?-11】在教師表(teacher)中，刪除一條記錄。(1) 啟動(dòng)“Microsoft SQL Server Management Studio”，在“對(duì)象資源管理器”中依次展開“服務(wù)器”“數(shù)據(jù)庫”“表”目錄，找到需要?jiǎng)h除記錄的表。(2) 以teacher表為例，在teacher表上右擊，在彈出的快捷菜單中選擇“編輯前200行(E)”選項(xiàng)，打開teacher表。(3) 選擇要?jiǎng)h除的記錄行，點(diǎn)右鍵，在快捷菜單中選擇“刪除”選項(xiàng)。(4) 在彈出刪除

10、確認(rèn)對(duì)話框中單擊“是”按鈕，即可成功刪除記錄。第三章例題【例3-1】檢索學(xué)生表中的所有信息。SELECT * FROM student或者：SELECT s_id,s_name,s_sex,born_date,nation,place,politic,tel,address,class_id,resumeFROM student【例3-2】檢索學(xué)生表中學(xué)生的學(xué)號(hào)、姓名和班級(jí)號(hào)。SELECT s_id,s_name,class_id FROM student【例3-3】查詢學(xué)生的學(xué)號(hào)、姓名和籍貫。SELECT s_id AS 學(xué)號(hào),s_name AS 姓名,s_id AS 籍貫FROM stud

11、ent【例3-4】查詢成績表中沒有成績的學(xué)生。SELECT 學(xué)號(hào)=s_id, 姓名=s_name,江揚(yáng)學(xué)院 AS 學(xué)校名稱FROM student【例3-5】查詢所有男學(xué)生的學(xué)號(hào)、姓名、性別和出生日期。SELECT s_id, s_name, s_sex, born_dateFROM student WHERE s_sex=男【例3-6】查詢1989年以后出生的學(xué)生基本信息。SELECT * FROM student WHERE born_date1989-12-31【例3-7】查詢除江蘇南通以外所有學(xué)生的學(xué)號(hào)、姓名信息。SELECT s_id,s_name FROM student Wher

12、e place 江蘇南通【例3-8】查詢1989年以后出生的所有女生的基本信息。SELECT * FROM student WHERE born_date1989-12-31 AND s_sex=女【例3-9】查詢學(xué)生表中非團(tuán)員的學(xué)生信息。SELECT * FROM student WHERE NOT(politic=團(tuán)員)【例3-10】查詢學(xué)生表中班級(jí)號(hào)為09020111或09040911的學(xué)生的學(xué)號(hào)、姓名、班級(jí)編號(hào)、家庭住址和備注信息。SELECT s_id, s_name, class_id, address, resume FROM student WHERE class_id=090

13、20111or class_id=09040911【例3-11】查詢返回眾多學(xué)生記錄中前五位女生的姓名和地址信息。SELECT TOP 5 s_id,addressFROM student WHERE s_sex=女【例3-12】查詢返回眾多學(xué)生記錄中前20%的女生的姓名和地址信息。SELECT TOP 20 PERCENT s_id,addressFROM student WHERE s_sex=女【例3-13】按出生日期的降序顯示學(xué)生表中學(xué)生的姓名和出生日期。SELECT s_name,born_dateFROM studentORDER BY born_date DESC【例3-14】查

14、詢成績表中60分以上學(xué)生的學(xué)號(hào)、課程號(hào)和分?jǐn)?shù)，并且按學(xué)生成績和課程號(hào)依次升序排序。SELECT s_id,c_id,gradeFROM scoreWHERE grade60ORDER BY grade ,c_id【例3-15】查找成績表中090406號(hào)課程的最高分和最低分SELECT MAX(grade)最高分,MIN(grade)最低分FROM scoreWHERE c_id=090406【例3-16】計(jì)算成績表中0902011101號(hào)學(xué)生的總成績。SELECT MAX(grade)最高分,MIN(grade)最低分FROM scoreWHERE s_id=0902011101【例3-17】

15、計(jì)算成績表中學(xué)號(hào)為0902011101的學(xué)生的平均成績SELECT AVG(grade)平均分FROM scoreWHERE s_id=0902011101【例3-18】統(tǒng)計(jì)成績表中每個(gè)學(xué)生的總分和平均分，把查詢結(jié)果按總分的降序排列輸出。SELECT s_id,SUM(grade)總分,AVG(grade)平均分FROM scoreGROUP BY s_idORDER BY 總分 DESC【例3-19】統(tǒng)計(jì)學(xué)生表中學(xué)生的總數(shù)。SELECT COUNT(s_id)學(xué)生總數(shù)FROM student【例3-20】統(tǒng)計(jì)學(xué)生表中各個(gè)班學(xué)生的總?cè)藬?shù)。SELECT class_id,COUNT(s_id)

16、人數(shù)FROM studentGROUP BY class_id【例3-21】統(tǒng)計(jì)成績表中每個(gè)學(xué)生的總分和平均分。SELECT s_id 學(xué)號(hào),SUM(grade) 總分,AVG(grade) 平均分FROM scoreGROUP BY s_id【例3-22】統(tǒng)計(jì)學(xué)生表中每個(gè)班男生和女生各有多少人。SELECT class_id,s_sex,COUNT(s_sex)人數(shù)FROM studentGROUP BY class_id,s_sex【例3-23】計(jì)算在2010年10月1日的基礎(chǔ)上增加50天的日期；計(jì)算2012年國慶節(jié)距離現(xiàn)在還有多少天。SELECT DATEADD(DAY,50,2010-

17、10-1)SELECT DATEDIFF(DAY,GETDATE(),2012-10-1)【例3-24】查詢成績表中09040911班學(xué)生的學(xué)號(hào)和年齡的大小，并按照年齡進(jìn)行降序排列，年齡相同時(shí)按學(xué)號(hào)的降序排列。SELECT s_id,YEAR(GETDATE()-YEAR(born_date)年齡FROM studentWHERE class_id=09040911ORDER BY 年齡 DESC,s_id【例3-25】查詢課程表的課程類型。使用distinct之前：SELECT c_type FROM courseWHERE semester=2009-2010-1使用distinct之后：

18、SELECT DISTINCT c_type FROM courseWHERE semester=2009-2010-1【例3-26】查詢學(xué)生表中姓李的學(xué)生的基本情況。SELECT * FROM student WHERE s_name LIKE 李%【例3-27】查詢學(xué)生表中所有姓張和姓李學(xué)生的基本情況。SELECT * FROM student WHERE s_name LIKE 張,李%【例3-28】查詢學(xué)生表中1990年1月1日1991年12月31日出生的學(xué)生的學(xué)號(hào)、姓名、出生日期。SELECT s_id, s_name, born_date FROM student WHERE bo

19、rn_date BETWEEN 1990-1-1AND1991-12-31【例3-29】查詢表中備注內(nèi)容為空的學(xué)生的學(xué)號(hào)、姓名與備注。SELECT s_id, s_name, resume FROM student WHERE resume IS NULL【例3-30】查詢學(xué)生表中備注內(nèi)容不為空的學(xué)生的學(xué)號(hào)、姓名和備注。SELECT s_id, s_name, resume FROM student WHERE resume IS NOT NULL【例3-31】查詢學(xué)生表中來自南通市和徐州市的學(xué)生的姓名、班級(jí)編號(hào)和來自的城市。SELECT s_id, class_id,address FROM

20、 student WHERE RIGHT(address,3) IN (南通市,常州市)在這里，in可以用關(guān)系運(yùn)算符和邏輯運(yùn)算符的結(jié)合運(yùn)算來替代。例3-31題的查詢條件可以改為：WHERE RIGHT(address,3)=南通市 OR RIGHT(address,3)=常州市【例3-32】在學(xué)生表和成績表中查詢學(xué)生的基本信息和成績信息。SELECT student.*,score.*FROM student, scoreWHERE student.s_id=score.s_id【例3-33】查詢學(xué)生的基本信息和成績信息，在輸出結(jié)果中相同的字段只保留一個(gè)。SELECT student.s_id

21、,s_name,class_id,s_sex,born_date,address,tel,score.resume,c_id,gradeFROM student,scoreWHERE student.s_id=score.s_id【例3-34】查詢學(xué)生學(xué)號(hào)、姓名、所學(xué)課程的名稱和成績信息。SELECT student.s_id,s_name,c_name,gradeFROM student,score,courseWHERE student.s_id=score.s_idAND score.c_id=course.c_id【例3-35】查找同一課程成績相同的學(xué)生的學(xué)號(hào)、課程號(hào)和成績。SELEC

22、T a.s_id,b.s_id,a.c_id,a.gradeFROM score a,score bWHERE a.grade= b.grade AND a.s_id b.s_idAND a.c_id=b.c_id【例3-36】查詢學(xué)生基本信息和成績信息。SELECT student.*,score.*FROM student INNER JOIN scoreON student.s_id= score.s_id【例3-37】查詢學(xué)號(hào)為0904101108學(xué)生的基本信息和成績信息。SELECT student.s_id,s_name,class_id,s_sex,born_date,stude

23、nt.address,tel,student.resume,c_id,gradeFROM student INNER JOIN scoreON student.s_id=score.s_idWHERE student.s_id=0904101108【例3-38】用排名函數(shù)列出課程號(hào)為100401的學(xué)生的名次。(1) 使用ROW_NUMBER函數(shù)。SELECT ROW_NUMBER() OVER(ORDER BY score)as row_number,*FROM fun ORDER BY score(2) 使用RANK函數(shù)。SELECT RANK() OVER(ORDER BY score)a

24、s row_number,*FROM fun ORDER BY score(3) 使用DENSE_RANK 函數(shù)。SELECT DENSE_RANK() OVER(ORDER BY score)as row_number,*FROM fun ORDER BY score【例3-39】統(tǒng)計(jì)成績表中每個(gè)學(xué)生的總分和平均分，只輸出總分大于150的學(xué)生和學(xué)號(hào)，總分和平均分。SELECT s_id,SUM(grade)總分,AVG(grade)平均分FROM scoreGROUP BY s_idHAVING sum(grade)150【例3-40】查詢學(xué)生表中學(xué)生的學(xué)號(hào)、姓名和班級(jí)編號(hào)，并把查詢結(jié)果插入

25、到新的表student_class中。然后針對(duì)student_class表進(jìn)行查詢操作，驗(yàn)證新表student_class是否建立成功且被插入了記錄。SELECT s_id,s_name,class_idINTO student_classFROM student【例3-41】查詢與0904091203學(xué)號(hào)同學(xué)在同一班級(jí)的學(xué)生的學(xué)號(hào)與姓名信息。SELECT s_id ,s_nameFROM studentWHERE class_id= (SELECT class_id FROM student WHERE s_id =0904091203)【例3-42】查詢選修了090402號(hào)課程且成績比09

26、04091104號(hào)學(xué)生090402號(hào)課程成績高的學(xué)生的學(xué)號(hào)、課程編號(hào)和成績。SELECT s_id,c_id,gradeFROM scoreWHERE c_id=090402AND grade(SELECT gradeFROM scoreWHERE s_id=0904091104AND c_id=090402)【例3-43】查詢選修了090402號(hào)課程且成績比0904091104和0904091105號(hào)學(xué)生的090402號(hào)課程成績都高的學(xué)生的學(xué)號(hào)、課程編號(hào)和成績。SELECT s_id,c_id,gradeFROM scoreWHERE c_id=090402AND gradeALL(SELE

27、CT gradeFROM scoreWHERE (s_id=0904091104OR s_id=0904091105)AND c_id=090402)【例3-44】查詢與黃娟同學(xué)在同一班級(jí)的學(xué)生的學(xué)號(hào)與姓名信息。SELECT s_id ,s_nameFROM studentWHERE class_id= (SELECT class_id FROM student WHERE s_name =黃娟)【例3-45】查找未選修090401號(hào)課程的學(xué)生的學(xué)號(hào)、姓名和班級(jí)編號(hào)。SELECT s_id,s_name,class_idFROM studentWHERE s_id NOT IN(SELECT

28、s_idFROM scoreWHERE c_id=090401)【例3-46】查找選修090401號(hào)課程的學(xué)生的學(xué)號(hào)、姓名和班級(jí)編號(hào)。SELECT s_id,s_name,class_idFROM studentWHERE EXISTS ( SELECT * FROM SCORE WHEREc_id=090401AND s_id =student.s_id )本例可使用in謂詞實(shí)現(xiàn)SELECT s_id,s_name,class_idFROM studentWHERE s_id NOT IN(SELECT s_idFROM scoreWHERE c_id=090401)本例可使用連接查詢實(shí)現(xiàn)S

29、ELECT student.s_id,s_name,class_idFROM student ,scoreWHERE c_id=090401AND score.s_id =student.s_id【例3-47】查詢所有選修了090407號(hào)課程并獲得成績的學(xué)生姓名。SELECT s_nameFROM studentWHERE EXISTS (SELECT * FROM scoreWHERE c_id=090407)【例3-48】創(chuàng)建一個(gè)新的學(xué)生表st_info，要求包括學(xué)號(hào)，姓名和備注3個(gè)字段，然后將student表中相應(yīng)的字段值插入列表st_info中，最后顯示st_info表中的記錄。CRE

30、ATE TABLE st_info(學(xué)號(hào) char(10)PRIMARY KEY,姓名 char(8),備注 char(30)GoINSERT INTO st_info(學(xué)號(hào),姓名,備注)SELECT s_id,s_name,resumeFROM studentGoSELECT *FROM st_info【例3-49】將course表中學(xué)分(credit)字段為空值的記錄，用表中學(xué)分的平均值填充。UPDATE courseSET credit=(SELECT AVG(credit) FROM course)WHERE credit IS NULL【例3-50】刪除沒有選修090407號(hào)課程的學(xué)

31、生記錄。DELETE studentWHERE s_id not in(SELECT s_idFROM scoreWHERE c_id=090407)【例3-51】在student數(shù)據(jù)庫中建立一個(gè)名為 view_ place的視圖，通過視圖只能看到籍貫為“常州”的學(xué)生信息。(1) 啟動(dòng) SQL Server Management Studio窗口，在“對(duì)象資源管理器”中依次展開“數(shù)據(jù)庫”“student”數(shù)據(jù)庫節(jié)點(diǎn)。(2) 右擊“視圖”節(jié)點(diǎn)，在彈出的快捷菜單中選擇“新建視圖”選項(xiàng)，如圖3-28所示，彈出“添加表”對(duì)話框。(3) 在“添加表”對(duì)話框中選擇要用作建立視圖的基表，單擊“添加”按鈕，就

32、可以添加創(chuàng)建視圖的基表，重復(fù)該操作，可以添加多個(gè)基表。在這里選擇student表。單擊“關(guān)閉”按鈕退出。(4) 添加完基表后，在第一個(gè)窗格中就可以看到新添加的基表，在基表的每一列的左邊有一個(gè)復(fù)選框，選擇相應(yīng)的復(fù)選框，可以指定對(duì)應(yīng)的列在視圖中被引用。本例選擇student表中的所有列。(5) 在圖3-30的第2個(gè)窗格是條件窗格，用來設(shè)置查詢的條件、視圖中記錄排序類型和排序順序等。在本例中，篩選器一項(xiàng)設(shè)置為place =%常州，即在視圖中只包括籍貫為常州的學(xué)生信息。(6) 當(dāng)?shù)?和第2窗格中設(shè)置完成以后，在第3個(gè)窗格中自動(dòng)生成對(duì)應(yīng)的T-SQL語句。(7) 執(zhí)行文件菜單的“保存”命令，在彈出的“選擇

33、名稱”對(duì)話框中輸入視圖名view_place，單擊“確定”按鈕，完成視圖的建立。(8) 展開“視圖”節(jié)點(diǎn)，在視圖列表中右擊view_place視圖，在彈出的快捷菜單中選擇“選擇前1000行”選項(xiàng)。在窗口最下面的輸出窗格中會(huì)顯視圖中的T-SQL語句執(zhí)行結(jié)果?！纠?-52】在student數(shù)據(jù)庫中建立一個(gè)名為 view_student的視圖，通過視圖只能看到學(xué)生的學(xué)號(hào)、姓名、性別和班級(jí)信息。USE studentGOCREATE VIEW view_studentASSELECT s_id as 學(xué)號(hào), s_name as姓名, s_sex as性別 , class_id as班級(jí)FROM stu

34、dent【例3-53】在 student數(shù)據(jù)庫中建立一個(gè)名為view_teacher的視圖。通過該視圖，只能訪問到計(jì)算機(jī)系的教師信息，并且對(duì)視圖語句加密。USE studentGOCREATE VIEW view_teacherWITH ENCRYPTIONASSELECT dept_name, t_id,t_name,t_sex,titleFROM teacher,deptWHERE dept_name=計(jì)算機(jī)系A(chǔ)ND dept.dept_id=teacher.dept_id【例3-54】使用sp_help查看視圖view_student的特征。USE studentGOsp_help vi

35、ew_student【例3-55】使用sp_helptext查看視圖view_student的定義信息。USE studentGOsp_helptext view_student【例3-56】使用sp_helptext查看視圖view_teacher的定義信息。USE studentGOsp_helptext view_teacher【例3-57】修改student數(shù)據(jù)庫中的視圖“view_student”，使其只包含男學(xué)生的學(xué)號(hào)、姓名、班級(jí)和性別。并在對(duì)視圖進(jìn)行操作時(shí)滿足條件表達(dá)式。USE studentGOALTER VIEW view_studentASSELECT s_id as 學(xué)號(hào)

36、, s_name as 姓名, s_sex as 性別 , class_id as 班級(jí)FROM studentWHERE s_sex=男WITH CHECK OPTION【例3-58】在 student數(shù)據(jù)庫，使用SP_RENAME命令將視圖view_teacher重命名為v_teacher。USE studentGOSP_RENAME view_teacher,v_teacher【例3-59】在 student數(shù)據(jù)庫，使用DROP VIEW命令刪除視圖v_teacher。USE studentGODROP VIEW v_teacher【例3-60】在student數(shù)據(jù)庫，通過視圖view_

37、student 查詢09041011班級(jí)學(xué)生的學(xué)號(hào)、姓名、性別信息。USE studentGOSELECT 學(xué)號(hào),姓名,性別FROM view_studentWHERE 班級(jí)=09041011例3-61】在 student數(shù)據(jù)庫，通過視圖view_student向?qū)W生信息表中插入一條記錄。USE studentGOINSERT view_student(學(xué)號(hào),姓名,性別 ,班級(jí))VALUES(0904101120,謝霆峰,男,09041011)【例3-62】在 student數(shù)據(jù)庫，通過視圖view_student將學(xué)號(hào) 0904101120學(xué)生的姓名改為 “謝霆鋒”。USE studentG

38、OUPDATE view_studentSET 姓名=謝霆鋒WHERE 學(xué)號(hào)=0904101120【例3-63】在 student數(shù)據(jù)庫，通過視圖view_student刪除將學(xué)號(hào)為090401120學(xué)生的記錄。USE studentGODELETE view_studentWHERE 學(xué)號(hào)=0904101120【例3-64】在student數(shù)據(jù)庫中為course表創(chuàng)建了一個(gè)唯一非聚集索引，索引名稱為index_c_name，被索引的列為 c_name。(1) 啟動(dòng) SQL Server Management Studio窗口，在“對(duì)象資源管理器”中依次展開 “數(shù)據(jù)庫”“student”數(shù)據(jù)庫

39、“表”“course表”節(jié)點(diǎn)。(2) 右擊 “索引”節(jié)點(diǎn)，在彈出的快捷菜單中選擇“新建索引”選項(xiàng)，如圖3-37所示，彈出“新建索引”對(duì)話框。(3) 在“新建索引”對(duì)話框中選擇“常規(guī)”選項(xiàng)頁，在“索引名稱”文本框中輸入所要?jiǎng)?chuàng)建的索引名稱“index_c_name”，并選擇索引類型(聚集或非聚集)，以及是否設(shè)置唯一索引。(4) 單擊“添加”，系統(tǒng)打開如圖3-39所示的 “從dbo.course中選擇列”對(duì)話框，在該對(duì)話框上選擇需要?jiǎng)?chuàng)建索引的表列c_name，單擊“確定”按鈕完成被索引字段的設(shè)置，返回“新建索引”窗口。(5) 在“新建索引”對(duì)話框中，單擊“確定”按鈕，完成索引的創(chuàng)建?！纠?-65】在

40、student數(shù)據(jù)庫中刪除索引index_c_name(1) 啟動(dòng) SQL Server Management Studio窗口，在“對(duì)象資源管理器”中依次展開 “數(shù)據(jù)庫”“student”數(shù)據(jù)庫“表”“course表”“索引”節(jié)點(diǎn)。(2) 右擊表中index_c_name索引。在彈出快捷菜單中單擊“刪除”選項(xiàng)，彈出“刪除對(duì)象”對(duì)話框，在該對(duì)話框中，單擊“確定”按鈕即可刪除索引?！纠?-66】在student數(shù)據(jù)庫score表的c_id列和grade列上創(chuàng)建名為index_ course_grade的復(fù)合索引。USE studentGOCREATE INDEX index_course_gra

41、deON score (c_id, grade)【例3-67】刪除student數(shù)據(jù)庫中score表上名為index_ course_grade的復(fù)合索引。USE studentGODROP INDEX score.index_course_grade【例3-68】在student數(shù)據(jù)庫中定義一個(gè)名稱為classname的字符型局部變量。USE studentGODECLARE classname char【例3-69】在student數(shù)據(jù)庫中，查詢成績表中最高分和最低分的學(xué)生姓名和課程名稱及成績。方法一、查詢嵌套select s_name,c_name,gradefrom student ,

42、course,scorewhere student.s_id=score.s_id and course.c_id=score.c_idand (grade =(select min(grade) from score )or grade =(select max(grade) from score) )方法二、賦值語句，中間變量declare maxscore real,minscore realselect maxscore=max(grade),minscore =min(grade) from scoreselect student.s_name, course.c_name,grad

43、efrom student,course,scorewhere student.s_id= score.s_id and course.c_id = score.c_id and (grade=maxscore or grade=minscore)【例3-70】在student數(shù)據(jù)庫中，根據(jù)教師號(hào)查找馬麗麗老師的信息以及與她相鄰的教師信息。use studentgo/*- 查找馬麗麗教師的信息-*/DECLARE name char(10)set name=馬麗麗 -使用SET賦值SELECT t_id,t_name,t_sex,title,dept_id -查詢馬麗麗信息FROM teach

44、erWHERE t_name =name/*- 查找馬麗麗老師的教師號(hào)-*/DECLARE teacher_id char(4)SELECT teacher_id=t_id -使用select賦值FROM teacherWHERE t_name = name/*- 查找與馬麗麗教師相鄰的教師信息-*/SELECT t_id,t_name,t_sex,title,dept_idFROM teacherWHERE (t_id = teacher_id+1) or (t_id = teacher_id-1)【例3-71】輸出服務(wù)器名稱。PRINT 服務(wù)器名稱：+SERVERNAMESELECT SE

45、RVERNAME AS服務(wù)器名稱【例3-72】在teacher表中插入兩條記錄，并驗(yàn)證是否插入成功。USE studentGOINSERT teacher(t_id ,t_name ,t_sex ,title ,dept_id )VALUES (0404,張有偉,男,講師,04 )PRINT 當(dāng)前錯(cuò)誤號(hào) + CONVERT(varchar(5), ERROR)USE studentGOINSERT teacher(t_id ,t_name ,t_sex ,title ,dept_id )VALUES (0404,劉欣,女,講師,04 )PRINT 當(dāng)前錯(cuò)誤號(hào) + CONVERT(varchar

46、(5), ERROR)【例3-73】查看表student的所有信息EXEC sp_help student【例3-74】查看表student索引信息EXEC sp_helpindex student【例3-75】在student數(shù)據(jù)庫中，創(chuàng)建一個(gè)簡單的存儲(chǔ)過程pro_student_info，用于檢索學(xué)號(hào)為0904101104學(xué)生的信息，然后再將其刪除。1) 啟動(dòng) SQL Server Management Studio窗口，在“對(duì)象資源管理器”中依次展開“數(shù)據(jù)庫”“student”數(shù)據(jù)庫“可編程性”節(jié)點(diǎn)，右擊“存儲(chǔ)過程”節(jié)點(diǎn)。2) 在彈出的快捷菜單中選擇“新建存儲(chǔ)過程”選項(xiàng)，彈出的對(duì)話框，在

47、對(duì)話框中輸入建立存儲(chǔ)過程的語句，輸入完畢后執(zhí)行成功即可。3) 右擊 “存儲(chǔ)過程pro_student_info”，在彈出的快捷菜單中選擇“刪除”選項(xiàng)彈出“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕即可刪除選中存儲(chǔ)過程。例3-76】在student數(shù)據(jù)庫中，創(chuàng)建一個(gè)不帶參數(shù)的存儲(chǔ)過程pro_stu_info，用于檢索學(xué)生的姓名、班級(jí)和聯(lián)系方式。USE studentGO/*-判斷pro_stu_info存儲(chǔ)過程是否存在，若存在，則刪除-*/If EXISTS (SELECT name FROM sysobjectsWHERE name=pro_stu_info AND type=p)DROP PROCE

48、DURE pro_stu_infoGo-建立存儲(chǔ)過程CREATE PROC pro_stu_infoAsSELECT s_name,class_id,tel FROM student【例3-77】在student數(shù)據(jù)庫中，創(chuàng)建存儲(chǔ)過程pro_class_info。用于檢索09041011班級(jí)學(xué)生的基本信息。USE studentGOIf EXISTS (SELECT name FROM sysobjectsWHERE name= pro_class_info AND type=p)DROP PROCEDURE pro_class_infoGo-建立存儲(chǔ)過程CREATE PROC pro_cla

49、ss_infoAsSELECT * FROM studentWHERE class_id= 09041011【例3-78】在student數(shù)據(jù)庫中，創(chuàng)建一個(gè)帶輸入?yún)?shù)的存儲(chǔ)過程pro_class_info1。該存儲(chǔ)過程可以根據(jù)給定班級(jí)的編號(hào)，返回該班級(jí)學(xué)生的所有信息。USE studentGOIf EXISTS (SELECT name FROM sysobjectsWHERE name =pro_class_info1 AND type=p)DROP PROCEDURE pro_class_info1Go-建立存儲(chǔ)過程CREATE PROC pro_class_info1class_id c

50、har(8)AsSELECT * FROM studentWHERE class_id=class_id【例3-79】在student數(shù)據(jù)庫中，創(chuàng)建一個(gè)名為pro_student_grade的存儲(chǔ)過程, 該存儲(chǔ)過程可以檢索某個(gè)學(xué)生某門課程的成績。默認(rèn)課程為“大學(xué)英語”。USE studentGOIF EXISTS(SELECT name FROM sysobjectsWHERE name =pro_student_gradeAND type=P)DROP PROCEDURE pro_student_gradegoCREATE PROCEDURE pro_student_gradesname c

51、har(8) ,cname char(20)=大學(xué)英語ASSELECT student.s_id AS 學(xué)號(hào),s_name AS 姓名,c_name AS 課程名,grade AS 成績FROM student,course,scoreWHERE student.s_id=score.s_id and course.c_id=score.c_idAND student.s_name=sname AND course.c_name=cname【例3-80】使用sp_help查看存儲(chǔ)過程 pro_class_info的特征。USE studentGOEXEC sp_help pro_class_i

52、nfo【例3-81】使用sp_helptext查看存儲(chǔ)過程pro_class_info的定義信息。USE studentGOEXEC sp_helptext pro_class_info【例3-82】在student數(shù)據(jù)庫中，修改存儲(chǔ)過程pro_class_info。使其根據(jù)用戶提供的班級(jí)名稱檢索計(jì)算機(jī)應(yīng)用技術(shù)班級(jí)學(xué)生的基本信息，要求加密存儲(chǔ)過程。USE studentGO-修改存儲(chǔ)過程ALTER PROC pro_class_infoWITH ENCRYPTIONASSELECT * FROM student,classWHERE student.class_id=class.class_i

53、dAND class_name LIKE %計(jì)算機(jī)應(yīng)用技術(shù)%【例3-83】使用T-SQL 語句執(zhí)行 pro_stu_info和pro_class_info存儲(chǔ)過程。USE studentGOEXEC pro_stu_infoEXEC pro_class_info【例3-84】使用參數(shù)名傳遞參數(shù)的方法執(zhí)行存儲(chǔ)過程pro_class_info1，分別查找班級(jí)號(hào)為09040911和09040912學(xué)生信息。EXEC pro_class_info1 class_id=09040911GOEXEC pro_class_info1 class_id=09040912GO【例3-85】使用參數(shù)名傳遞參數(shù)的方

54、法執(zhí)行存儲(chǔ)過程pro_student_grade，查找孫楠同學(xué)網(wǎng)頁制作技術(shù)課程的成績。EXEC pro_student_grade cname =網(wǎng)頁制作技術(shù),sname=孫楠【例3-86】按位置傳遞參數(shù)值執(zhí)行存儲(chǔ)過程pro_class_info1，分別查找班級(jí)號(hào)為09040911和09040912學(xué)生信息。EXEC pro_class_info1 09040911GoEXEC pro_class_info1 09040912Go【例3-87】按位置傳遞傳遞參數(shù)值執(zhí)行存儲(chǔ)過程pro_student_grade，查找王芳c語言程序設(shè)計(jì)課程的成績。EXEC pro_student_grade 王芳

55、,c語言程序設(shè)計(jì)GOEXEC pro_student_grade 王芳,defaultGOEXEC pro_student_grade 王芳【例3-88】在student數(shù)據(jù)庫中，查詢學(xué)生成績表，如果其中存在學(xué)號(hào)為0904101101的學(xué)生，就輸出該學(xué)生的全部成績信息，否則顯示“沒有此學(xué)生的成績”。USE studentGOIF EXISTS(SELECT s_id FROM score WHERE s_id=0904101101)SELECT * FROM score WHERE s_id=0904101101ELSEPRINT沒有該生的成績【例3-89】統(tǒng)計(jì)面向?qū)ο蟪绦蛟O(shè)計(jì)課程的平均分。如

56、果平均分在70分以上，顯示“考試成績優(yōu)秀”，并顯示前三名學(xué)生的考試信息。如果在平均分70分以下，顯示“考試成績較差”，并顯示后三名學(xué)生的考試信息。USE studentGO/*-查詢面向?qū)ο蟪绦蛟O(shè)計(jì)課程的平均分-*/DECLARE objiectavg decimal(5,2)SELECT objiectavg = AVG (grade)FROM score ,courseWHERE c_name= 大學(xué)英語 AND course .c_id =score .c_idSELECT objiectavg as 平均分/*-根據(jù)平均分給出評(píng)語-*/IF (objiectavg =70)BEGINP

57、RINT考試成績優(yōu)秀，前三名的成績?yōu)椋篠ELECT TOP 3 score.s_id,s_name,c_name,gradeFROM student,score,courseWHERE c_name= 大學(xué)英語 AND course .c_id =score .c_idAND student.s_id =score.s_idORDER BY grade DESCENDELSEBEGINPRINT 考試成績較差，后三名的成績?yōu)椋篠ELECT TOP 3 score.s_id,s_name,c_name,gradeFROM student,score,courseWHERE c_name= 大學(xué)英

58、語 AND course .c_id =score .c_idAND student.s_id =score.s_idORDER BY gradeEND【例3-90】創(chuàng)建存儲(chǔ)過程pro_classnum,能根據(jù)用戶給定的班級(jí)編號(hào)統(tǒng)計(jì)該班學(xué)生人數(shù)，并將學(xué)生人數(shù)返回給用戶。USE studentGOIF EXISTS(SELECT name FROM sysobjectsWHERE name=pro_classnum AND type=P)DROP PROCEDURE pro_classnumGo-建立存儲(chǔ)過程CREATE PROC pro_classnumclass_id char(8),num

59、 int outputASSELECT num =COUNT(*)FROM studentWHERE class_id=class_id-執(zhí)行存儲(chǔ)過程DECLARE num intEXEC pro_classnum 09041011,num outputPRINT num【例3-91】使用case語句對(duì)學(xué)生性別顯示不同字樣。USE studentGOSELECT s_id 學(xué)號(hào), s_name 姓名,性別=CASE s_sexWHEN 男 THEN 男同學(xué)WHEN 女 THEN 女同學(xué)ENDFROM student【例3-92】開始一個(gè)事務(wù)對(duì)成績 score表中090101號(hào)課程的成績加10分

60、并查詢成績的結(jié)果，再用事務(wù)提交語句進(jìn)行提交。USE studentGO/*-事務(wù)執(zhí)行前-*/SELECT before as 事務(wù)執(zhí)行前 ,s_id ,c_id ,gradeFROM scoreWHERE c_id =090101/*- 開始事務(wù)-*/BEGIN tran gradeupdUPDATE scoreSET grade =grade+10WHERE c_id =090101/*- 事務(wù)執(zhí)行中-*/SELECT before as 事務(wù)執(zhí)行中 ,s_id ,c_id ,gradeFROM scoreWHERE c_id =090101/*- 事務(wù)提交-*/COMMIT tran/*-