第一篇：數(shù)據(jù)庫與軟件工程上機實驗答案

《數(shù)據(jù)庫與軟件工程》上機實驗答案

實驗三 SQL語言的DDL

3．CREATE TABLE aa（Bb1 VARCHAR(30),Bb2 INT,Bb3 DECIMAL(6,2))

4． ALTER TABLE aa ADD Bb4 varchar(20)

5.DROP TABLE AA

6． CREATE VIEWReaderView(借閱者)

ASSELECT DISTINCT 讀者編號

FROM 借閱

7． Drop ViewReaderView。

8.CREATE INDEX INDEX1 ON 讀者(姓名 ASC，單位 ASC)

CREATE INDEX INDEX3 ON 借閱(借閱日期 DESC)

9． DROP INDEX 讀者.INDEX1

CREATE INDEX INDEX1 ON 讀者(姓名 ASC)

實驗四 SQL語言的DML初步

1． CREATE DEFAULT ZEROVALUE AS 0

sp_bindefault ZEROVALUE, '圖書.借出否'

CREATE DEFAULT CURTIME AS getdate()

sp_bindefault CURTIME, '借閱.借閱日期'

2． CREATE RULE gender_rule

AS@value in('男','女')

EXEC sp_bindrule 'gender_rule', '讀者.性別'

CREATE RULE lenddate_rule

AS@value > '2004-1-1'

EXEC sp_bindrule 'lenddate_rule', '借閱.歸還日期'

3． 用INSERT語句對“圖書”表插入6條記錄

INSERT INTO 圖書(書號,類別,出版社,作者,書名,定價)

VALUES('0001','計算機類','清華出版社','嚴蔚敏','數(shù)據(jù)結(jié)構(gòu)', 20.11)INSERT INTO 圖書(書號,類別,出版社,作者,書名,定價)

VALUES('0002','計算機類','清華出版社','苗雪蘭','數(shù)據(jù)庫技術(shù)及應用',29.00)

INSERT INTO 圖書(書號,類別,出版社,作者,書名,定價)

VALUES('0003','計算機類','清華出版社','李建中','軟件工程',48.00)INSERT INTO 圖書(書號,類別,出版社,作者,書名,定價)

VALUES('0004','計算機類','電子工業(yè)出版社','湯惟','WEB技術(shù)',32.00)

INSERT INTO 圖書(書號,類別,出版社,作者,書名,定價)

VALUES('0005','自動化類','機械工業(yè)出版社','胡壽松','自動控制原理',52.00)

INSERT INTO 圖書(書號,類別,出版社,作者,書名,定價)

VALUES('0006','自動化類','機械工業(yè)出版社','鄭大鐘','線性控制理論',32.00)用INSERT語句對“讀者”表插入4條記錄

INSERT INTO 讀者(讀者編號,姓名,單位,性別,電話)

VALUES('10001','張三','東華大學','男','67792312')

INSERT INTO 讀者(讀者編號,姓名,單位,性別,電話)

VALUES('10002','李四','東華大學','女','67792312')

INSERT INTO 讀者(讀者編號,姓名,單位,性別,電話)

VALUES('10003','王五','東華大學','男','67792312')

INSERT INTO 讀者(讀者編號,姓名,單位,性別,電話)

VALUES('10004','李明','東華大學','女','67792312')

用INSERT語句對“借閱”表插入7條記錄

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0001','10001','2006-04-19 09:58:03','2006-05-19 8:38:23')

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0006','10002','2006-07-01 15:28:12','2006-07-18 9:14:02')

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0002','10002','2006-07-01 15:28:30','2006-07-17 19:10:32')

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0002','10003','2006-07-03 15:28:30','2006-08-09 15:28:30')

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0004','10002','2007-05-01 11:28:24','2007-08-01 8:09:04')

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0005','10002','2007-11-01 10:43:12','2007-12-01 14:09:56')

INSERT INTO 借閱(書號,讀者編號,借閱日期,歸還日期)

VALUES('0003','10004','2007-12-01 16:06:02','2007-12-29 13:17:09')

4． UPDATE 借閱

SET 借閱日期='2006-08-03 15:28:30'

WHERE 讀者編號='10003' AND

借閱日期 BETWEEN '2006-07-03' AND '2006-07-04' AND 書號='0002'

5． DELETE FROM 借閱

WHERE 借閱日期<'2006-06-01'

1． SELECT 書號,書名 as 名稱,定價 as 價格 FROM 圖書

WHERE 類別='自動化類'

2． 方法1：

SELECT * FROM 圖書

WHERE 類別='計算機類'

UNION

SELECT * FROM 圖書

WHERE 出版社='電子工業(yè)出版社'

方法2：

SELECT * FROM 圖書

WHERE 類別='計算機類' OR 出版社='電子工業(yè)出版社'

3． SELECT * INTO 計算機圖書表 FROM 圖書

WHERE 類別='計算機類'

4． SELECT DISTINCT 讀者.* FROM 讀者,借閱

WHERE 讀者.讀者編號=借閱.讀者編號

5． SELECT 讀者.讀者編號,姓名,單位,圖書.書號,書名,借閱日期

FROM 讀者,借閱,圖書

WHERE 讀者.讀者編號=借閱.讀者編號 AND 圖書.書號=借閱.書號

6． SELECT 圖書.書號,書名,類別,借閱日期 FROM 讀者,借閱,圖書

WHERE 讀者.讀者編號=借閱.讀者編號 AND 圖書.書號=借閱.書號

AND 姓名='張三'

ORDER BY 借閱日期 DESC

7． SELECT 讀者.讀者編號,姓名,書名,DATEDIFF(day,借閱日期,歸還日期)as 借閱時間

FROM 讀者,借閱,圖書

WHERE 讀者.讀者編號=借閱.讀者編號 AND 圖書.書號=借閱.書號

AND DATEDIFF(day,借閱日期,歸還日期)>20

order by 借閱時間

8． SELECT * FROM 讀者

WHERE NOT EXISTS（SELECT * FROM 圖書

WHERE 類別='自動化類' and NOT EXISTS（SELECT * FROM 借閱

WHERE 讀者編號=讀者.讀者編號 and 書號=圖書.書號))

1． SELECT 類別,COUNT(*)AS 數(shù)量, AVG(定價)AS平均價格,SUM(定價)AS 總價

FROM 圖書

GROUP BY 類別

ORDER BY 類別

2． SELECT YEAR(借閱日期)as 年份,書號,COUNT(*)AS 借閱次數(shù)

FROM 借閱

GROUP BY YEAR(借閱日期),書號

ORDER BY 借閱次數(shù) DESC

3． SELECT 讀者.讀者編號,COUNT(書號)AS 借閱次數(shù) FROM 讀者

LEFT JOIN

(SELECT * FROM 借閱 WHERE 借閱日期 BETWEEN '2006-7-1' AND '2007-10-1')A

ON 讀者.讀者編號=A.讀者編號

GROUP BY 讀者.讀者編號

4． SELECT 讀者編號,COUNT(*)AS 次數(shù) FROM 借閱,圖書

WHERE 借閱.書號=圖書.書號 AND 書名='數(shù)據(jù)庫技術(shù)及應用'

GROUP BY 讀者編號

ORDER BY 次數(shù) DESC

5． 方法1：

SELECT MAX(次數(shù))AS 最多,MIN(次數(shù))AS 最少,AVG(次數(shù)*1.0)AS平均

FROM（SELECT 圖書.書號,COUNT(*)AS 次數(shù) FROM 圖書,借閱

WHERE 圖書.書號=借閱.書號 AND 類別='計算機類'

GROUP BY 圖書.書號)A

方法2：

SELECT 圖書.書號,COUNT(*)*1.0 AS 次數(shù) FROM 圖書,借閱

WHERE 圖書.書號=借閱.書號 AND 類別='計算機類'

GROUP BY 圖書.書號

COMPUTE MAX(COUNT(*)*1.0),MIN(COUNT(*)*1.0),AVG(COUNT(*)*1.0)

6． SELECT 類別 FROM 圖書

GROUP BY 類別

HAVING MAX(定價)>=ALL(SELECT 2*AVG(定價)FROM 圖書 GROUP BY 類別)

7． SELECT 書號,書名,定價,出版社 FROM 圖書

WHERE 類別='計算機類'

ORDER BY 出版社 DESC

COMPUTE COUNT(書號)BY 出版社

COMPUTE COUNT(書號)

實驗七 存儲過程、觸發(fā)器和數(shù)據(jù)庫恢復

1．（1）

CREATE PROCEDURE 借閱情況

@Readerno VARCHAR(8)

AS

SELECT 讀者.讀者編號,姓名,圖書.書號,書名,借閱日期, 借出否 AS 歸還否FROM 讀者,借閱,圖書

WHERE 讀者.讀者編號=借閱.讀者編號 AND 圖書.書號=借閱.書號AND 讀者.讀者編號=@Readerno

EXEC 借閱情況 ‘10004’

（2）

CREATE PROCEDURE 借出情況

@Bookno VARCHAR(10)

AS

SELECT 書名,姓名,借閱日期,歸還日期

FROM 讀者,借閱,圖書

WHERE 讀者.讀者編號=借閱.讀者編號 AND 圖書.書號=借閱.書號AND 圖書.書號=@Bookno

EXEC 借出情況 '0004'

2．CREATE TRIGGER lendbookinsert ON 借閱

FOR INSERT

AS

IF(SELECT 借出否 FROM 圖書 WHERE 書號 IN

(SELECT 書號 FROM INSERTED))=1

BEGIN

PRINT '該書已經(jīng)借出'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

UPDATE 圖書

SET 借出否=1

WHERE 書號 IN(SELECT 書號 FROM INSERTED)

END

第二篇：數(shù)據(jù)庫上機實驗

創(chuàng)建數(shù)據(jù)表與數(shù)據(jù)輸入

Part I.使用SQL Server Management Studio創(chuàng)建數(shù)據(jù)表和輸入數(shù)據(jù) 1.在SQL Server Management Studio中創(chuàng)建數(shù)據(jù)表 P69 1.（1）-（6）

2.為數(shù)據(jù)表輸入數(shù)據(jù) P76 4.（1）-（4）

3.數(shù)據(jù)瀏覽

P77

1.（1）-（2）

2.（1）-（3）

Part II.使用SQL語句創(chuàng)建數(shù)據(jù)表和輸入數(shù)據(jù) 1.使用SQL語句創(chuàng)建數(shù)據(jù)表 P72.例3-2

2．使用SQL語句輸入數(shù)據(jù) P82.例3.8

習題：P.105（1）（2）insert 數(shù)據(jù)操作Insert、Update、Delete

P105 3.上機練習題（2）（4）

Insert(100001, 1000, 2002-12-18 0:00:00)(100002, 2000, 2010-3-20 0:00:00)

Update(100001, 1500, 2002-12-18 0:00:00)(100002, 2000, 2012-9-25 0:00:00)

Delete Transact-SQL語句基礎1 1.將teaching數(shù)據(jù)庫中score表的studentno列設置為引用表student的外鍵。ALTER TABLE Score ADD CONSTRAINT FK_score_student FOREIGN KEY(studentno)REFERENCES student(studentno)2.將teaching數(shù)據(jù)庫中class表的classname創(chuàng)建UNIQUE約束。ALTER TABLE class ADD CONSTRAINT UQ_class UNIQUE(classname)執(zhí)行如下插入語句，查看提示信息

INSERT INTO class VALUES(‘090602’, ’計算機0902’, ’計算機學院’, ’馬文斐’)3.為teaching數(shù)據(jù)庫中student表的birthday列創(chuàng)建CHECK約束，規(guī)定學生的年齡在17-25歲之間。

ALTER TABLE student ADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE())-YEAR(birthday))BETWEEN 17 AND 25 執(zhí)行如下插入語句，查看提示信息

INSERT INTO student(studentno, sname, sex, birthday, classno)VALUES(‘0922221328’, ’張源’, ’男’, ’1983-04-05’, ’090501’)提示：表達式Y(jié)EAR(GETDATE())-YEAR(birthday)4.為teaching數(shù)據(jù)庫創(chuàng)建規(guī)則prof_rule，規(guī)定教師職稱取值只能為’助教’,’講師’,’副教授’,’教授’，并將其綁定到teacher表的prof列上。

CREATE RULE prof_rule AS @prof IN(’助教’,’講師’,’副教授’,’教授’)EXEC sp_bindrule ‘prof_rule’, ‘teacher.prof’

執(zhí)行如下插入語句，查看提示信息

INSERT INTO teacher VALUES(‘t05002’, ’張源’, ’軟件工程’, ’工程師’, ’計算機學院’)提示：表達式IN(職稱列表)5.編寫程序，輸出在1-3000之間能被17整除的最大數(shù)值

提示：可使循環(huán)控制變量從最大值開始，逐步減少，第一個滿足被17整除的數(shù)值即為所求解的結(jié)果，可通過BREAK語句跳出循環(huán)。（如果使循環(huán)控制變量從小到大逐步增加，則循環(huán)次數(shù)將大大增加，程序執(zhí)行效率將下降。）

PRINT ‘1-3000之間能被17整除的最大數(shù)值為：’ +CAST(@i AS CHAR(4))DECLARE @s INT, @i INT SELECT @s=0, @i=3000 WHILE @i>=1 BEGIN

IF @i%17=0

BEGIN

PRINT ‘1-3000之間能被17整除的最大數(shù)值為：’ + CAST(@i AS CHAR(4))

BREAK

END @i = @i-1 END Transact-SQL語句基礎2 1.利用Transact-SQL語句聲明一個長度為16的nchar型變量bookname，并賦初值為“SQL Server數(shù)據(jù)庫編程”，打印該變量。

2.編程求50-100之間的所有能被3整除的奇數(shù)之和。

3.編寫程序，聲明CHAR型變量@ch，并賦初值。判斷字符變量@ch中存放的是字母、數(shù)字字符還是其他字符，并輸出相關信息。（提示：UPPER函數(shù)可以將小寫字母轉(zhuǎn)換為大寫字母）

4.編寫程序，求解如下分數(shù)序列的前20項之和并打印輸出結(jié)果。

S=2/1+3/2+5/3+8/5+13/8+21/13+…

分析數(shù)列的規(guī)律：后一項的分子為前一項的分子和分母之和，后一項的分母則為前一項的分子，通過循環(huán)即可實現(xiàn)累加。注意：聲明分子和分母為浮點型數(shù)據(jù)NUMERIC(20,7).5.查詢編號為c06108, c08106, c05109課程的學生總評成績等級，平時成績usually*0.2+期末成績final*0.8>=90的為優(yōu)秀，80-90為良好，70-80為中等，60-70為及格，<60為不及格。（提示：使用CASE …END語句）數(shù)據(jù)檢索1 1.查詢course表中所有的記錄。2.查詢student表中女生的人數(shù)。

3.查詢teacher表中每一位教授的教師號、姓名和專業(yè)名稱。

4.利用現(xiàn)有的表生成新表，新表中包括學號、學生姓名、課程號和總評成績。其中：總評成績=final*0.8+usually*0.2 5.查詢student表中所有年齡大于20歲的男生的姓名和年齡。6.查詢計算機學院教師的專業(yè)名稱。

7.查詢Email使用126郵箱的所有學生的學號、姓名和電子郵箱地址。8.查詢score表中選修’c05109’或’c05103’課程，并且課程期末成績在90~100分之間的學生姓名和期末成績。

9.查詢student表中所有學生的基本信息，查詢結(jié)果按班級號classno升序排列，同一班級中的學生按入學成績point降序排列。10.查詢選修’c05109’課程，并且期末成績在前5名的學生學號、課程號和期末成績。（提示：TOP 5）數(shù)據(jù)檢索2

1.按性別分組，求出student表中每組學生的平均年齡。2.統(tǒng)計每個學生期末成績的平均分。

3.輸出student表中沒有職稱的職工的教師號、姓名、專業(yè)和部門。4.查詢選修課程且期末成績不為空的學生人數(shù)。

5.查詢每名學生的學號、選修課程數(shù)目、總成績，并將查詢結(jié)果存放到生成的“學生選課統(tǒng)計表”中。6.查詢各班學生的人數(shù)。

7.查詢各課程期末成績的最高分和最低分。

8.查詢教兩門及以上課程的教師編號、任課班級數(shù)。

9.查詢課程編號以’c05’開頭、被3名及以上學生選修且期末成績的平均分高于75分的課程號、選修人數(shù)和期末成績平均分，并按平均分降序排序。10.查詢所有08級學生的期末成績平均分，要求利用COMPUTE BY方法顯示每一名學生的學生編號、課程號、期末成績的明細表，以及期末成績平均分的匯總表。

11.查詢所有女生入學成績的最高分，要求利用COMPUTE BY方法既顯示明細又顯示匯總結(jié)果。SQL語句的高級應用1

1.查詢每一位教授的教師號、姓名和講授的課程名稱。表：teacher,course,teach_class 2.分別統(tǒng)計每個學生期末成績高于75分的課程門數(shù)。表：student,score 3.計算每個學生獲得的學分。表：student,score,course 4.獲取入學時間在2008年到2009年之間的所有學生中入學年齡小于19歲的學生的學號、姓名及所修課程的課程名稱。表：student, stu_course 5.查詢09級學生的學號、姓名、課程及學分。表：student,stu_course 6.查詢所有班級的期末成績平均分，并按照平均分降序排列。表：score,student 7.查詢教師基本信息和教授課程信息，其中包括未分配課程的教師信息。表：teacher,teach_class 8.查詢’090501’班級中選修了’韓晉升’老師講授的課程的學生的學號、姓名、課程名和期末成績。表：score,student,course,teach_class,teacher 9.查詢每門課程的課程號、課程名和選修該課程的學生人數(shù)，并按所選人數(shù)升序排序。表：score,course 10.查詢兩門及以上課程的期末成績超過80分的學生的姓名及其平均成績。表：student,score SQL語句的高級應用2 – 使用子查詢

1.輸出student表中年齡大于女生平均年齡的男生的所有信息。2.查詢?nèi)雽W考試成績最高的學生的學號、姓名和入學成績。3.查詢所有教授’c05127’號課程的教師信息。

4.查詢同時教授’c05127’和’c05109’號課程的教師信息。

5.查詢至少選修了姓名為’韓吟秋’的學生所選修課程中的一門課的學生的學號和姓名。

6.查詢沒有被任何學生選修的課程編號、課程名稱和學分。

7.查詢’C語言’課程期末成績比’電子技術(shù)’課程期末成績高的所有學生的學號和姓名。

8.查詢所有班級期末平均成績的最高分，并將其賦值給變量，通過PRINT語句輸出。視圖與索引

使用SQL語言

1.創(chuàng)建一個視圖v_teacher，查詢所有“計算機學院”的教師信息。

CREATE VIEW v_teacher AS SELECT * FROM teacher WHERE department='計算機學院' GO SELECT * FROM v_teacher

2.創(chuàng)建一個視圖v_avgstu，查詢每個學生的學號、姓名及平均分，并且按照平均分降序排列。

CREATE VIEW v_avgstu AS SELECT TOP 100 student.studentno,sname,avg(final)AS '平均分' FROM student JOIN score ON student.studentno=score.studentno GROUP BY student.studentno,sname ORDER BY avg(final)GO SELECT * FROM v_avgstu

3.修改v_teacher的視圖定義，添加WITH CHECK OPTION選項。

ALTER VIEW v_teacher AS SELECT * FROM teacher WHERE department='計算機學院' WITH CHECK OPTION

4.通過視圖v_teacher向基表teacher中分別插入數(shù)據(jù)(‘05039’, ‘張馨月’,’計算機應用’,’講師’,’計算機學院’)和(‘06018’, ‘李誠’,’機械制造’,’副教授’,’機械學院’)，并查看插入數(shù)據(jù)情況。

INSERT INTO v_teacher VALUES('05039', '張馨月','計算機應用','講師','計算機學院')GO SELECT * FROM teacher

消息550，級別16，狀態(tài)1，第1 行

試圖進行的插入或更新已失敗，原因是目標視圖或者目標視圖所跨越的某一視圖指定了WITH CHECK OPTION，而該操作的一個或多個結(jié)果行又不符合CHECK OPTION 約束。語句已終止。

5.通過視圖v_teacher將基表teacher中教師編號為05039的教師職稱修改為”副教授”。

UPDATE v_teacher SET prof='副教授' WHERE teacherno='05039' GO SELECT * FROM teacher

6.在course表的cname列上創(chuàng)建非聚集索引IDX_name。

CREATE NONCLUSTERED INDEX IDX_name ON course(cname)

7.在student表的studentno和classno列上創(chuàng)建唯一索引UQ_stu，若該索引已經(jīng)存在，則刪除后重建，并輸出student表中的記錄，查看輸出結(jié)果的順序。CREATE UNIQUE INDEX UQ_stu ON student(studentno,classno)

8.教材P.200-204（節(jié)7.5.2）使用SQL Server Management Studio創(chuàng)建視圖，按照樣圖進行操作。

第三篇：數(shù)據(jù)庫上機實驗(二)

數(shù)據(jù)庫上機實驗內(nèi)容及要求（第二部分）

1． 建立工廠管理數(shù)據(jù)庫

工廠（包括廠名和廠長名）需要建立一個管理數(shù)據(jù)庫存儲以下信息：

（1）一個廠內(nèi)有多個車間，每個車間有車間號、車間主任姓名、地址和聯(lián)系電話；

（2）一個車間有多個工人，每個工人有職工號、姓名、年齡、性別和工種；

（3）一個車間生產(chǎn)多種產(chǎn)品，產(chǎn)品有產(chǎn)品號和價格；每種產(chǎn)品只能由一個車間生產(chǎn)；

（4）一個車間制造多種零件，一種零件也可能為多個車間制造。零件有零件號、重

量和價格；

（5）一種產(chǎn)品可由多種零件組成，一種零件也可以裝配出多種產(chǎn)品；

（6）產(chǎn)品和零件均存入倉庫；

（7）廠內(nèi)有多個倉庫，倉庫有倉庫號、倉庫主任姓名和電話。

根據(jù)以上需求分析結(jié)果，按照下述要求，設計并建立工廠管理數(shù)據(jù)庫。? 分析實體及聯(lián)系，設計E-R圖。

? 將E-R圖轉(zhuǎn)換成關系模式，并規(guī)范化到3NF。

? 在Microsoft SQL Server2000中基于“企業(yè)管理器”建立數(shù)據(jù)庫及相關對象（主

碼，外碼，索引，約束等）。

? 測試數(shù)據(jù)入庫

2． 基于“查詢分析器”，完成并保存下述題目的SQL腳本

（1）建立“工種”是“鉗工”的所有職工詳細信息的視圖；

（2）建立“車間號”是“CJ01”的鉗工詳細信息的視圖；

（3）建立使用了“零件號”是“LJ0002”的所有產(chǎn)品詳細信息的視圖；

（4）查詢使用了“零件號”是“LJ0002”的產(chǎn)品的生產(chǎn)車間號；

（5）對零件表按照“零件號”建立唯一索引；

（6）對職工表按照“性別”建立聚簇索引；

（7）查詢“車間主任姓名”是“趙平”的“車間地址”和“聯(lián)系電話”；

（8）查詢“職工號”是“ZG0001”的職工所在車間的“車間主任姓名”和“聯(lián)系電

話”；（連接查詢實現(xiàn)）

（9）查詢“產(chǎn)品號”是“CP0001”的產(chǎn)品的生產(chǎn)車間的“車間主任姓名”和“聯(lián)系

電話”；（嵌套查詢實現(xiàn)）

（10）查詢使用了“零件號”是“LJ0002”的所有產(chǎn)品的“產(chǎn)品號”，且查詢結(jié)果按照

“零件數(shù)量”降序排列；

（11）查詢使用了“零件號”是“LJ0002”的所有產(chǎn)品的“產(chǎn)品號”和“產(chǎn)品價格”；

（12）查詢使用了“零件號”是“LJ0002”的所有產(chǎn)品的生產(chǎn)車間的“車間主任姓名”

和“聯(lián)系電話”；

（13）查詢使用了“零件號”是“LJ0002”的產(chǎn)品數(shù)；

（14）查詢“LJ0002”號零件裝配產(chǎn)品的使用總量；

（15）查詢使用了3種以上零件的產(chǎn)品號；

【注意】：下機時保存數(shù)據(jù)庫文件（.mdf和.ldf）及SQL腳本文件到U盤。

第四篇：數(shù)據(jù)庫上機實驗總結(jié)(含代碼)

實驗一

（1）無條件單表查詢

select sname NAME,'year of birth:' BIRTH,2004-sage BIRTHDAY,LOWER(sdept)DEPARTMENT FROM student;（2）有條件單表查詢

SELECT sname,sdept,sage FROM student WHERE sage NOT BETWEEN 20 AND 23;（3）單表嵌套（一層）查詢

SELECT sno,sname,sdept FROM student WHERE sdept IN(SELECT sdept FROM student WHERE sname='劉晨');（4）復合條件多表查詢

SELECT student.sno,sname,cname,grade FROM student ,sc,course WHERE student.sno=sc.sno AND sc.cno=course.cno;（5）使用COUNT（）的單表查詢 SELECT COUNT(*)FROM student;（6）使用AVG（）的單表查詢

SELECT AVG(grade)'平均成績' from SC where CNO='1';（7）查詢結(jié)果分組

SELECT cno,COUNT(sno)'人數(shù)' FROM sc GROUP BY cno;（8）查詢結(jié)果排序

SELECT * FROM student ORDER BY sdept,sage DESC;（9）使用通配符的查詢

SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'劉%';（10）使用換碼字符的單表查詢

SELECT cno,ccredit FROM course WHERE cname LIKE 'DB_Design'ESCAPE'';（11）插入單個元組 插入一個新學生元組

Insert into student(sno,sname,ssex,sdept,sage)values('200215128','陳冬','男','IS',18)（12）插入子查詢結(jié)果

對每一個系，求學生平均年齡，并把結(jié)果存入數(shù)據(jù)庫 Create table dept_age(sdept char(15),avg_age int)Insert into dept_age(sdept,avg_age)select sdept,avg(sage)from student group by sdept（13）修改某個元組的值

將學生200215121的年齡改為22歲

Update student set sage=’22’ where sno=’200215121’（14）修改多個元組的值 將所有學生的年齡增加一歲 Update student set sage=sage+1（15）刪除一個元組的值 刪除學號為200215128的學生記錄

delete from student where sno='200215128'（16）建立視圖 建立信息系學生的視圖

create view is_student as select sno,sname,sage from student where sdept='IS' ×（17）查詢視圖

查詢選修了1號課程的信息系學生信息

Select is_student.sno,sname from is_student,sc where is_student.sno=sc.sno and sc.cno=’1’ ×（18）更新視圖

將信息系學生視圖is_student中學號為95001的學生姓名改為李楠 update is_student set sname='李楠' where sno='95002' 將下列問題用SQL命令表示：

1．查詢‘IS’系學生的學號、姓名、性別。

SELECT sno,sname,ssex FROM student WHERE sdept='IS';2．查詢‘IS’系年齡在20歲以下的學生。

SELECT * FROM student WHERE sdept='IS'AND sage<20;3．查詢所有不姓‘劉’的學生的學號、姓名、性別。

SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'劉%';4.查詢student表中學生的總?cè)藬?shù)。

SELECT COUNT(*)'總?cè)藬?shù)' FROM student;5.查詢和‘李勇’同性別的所有同學的姓名。

SELECT sname from student where ssex in(select ssex from student where sname='李勇');6.查詢和‘李勇’同性別并同系的所有同學的姓名。

Select sname from student where ssex in(select ssex from student where sname='李勇')and sdept in(select sdept from student where sname='李勇')7.查詢選修2號課程的學生的學號。Select sno from sc where cno='2' 8.求3號課程的平均成績。

Select avg(grade)from sc where cno=’3’ 9.查詢選修2號課程的學生的最高分。Select max(grade)from sc where cno=’2’

10.按成績降序排列，輸出‘IS’系學生選修了2號課程的學生的姓名和成績。

Select sname,grade from student,sc where sdept='IS' and cno='2' and student.sno=sc.sno order by grade desc SQL查詢分析器下建數(shù)據(jù)庫的命令代碼： create database 霍雙雙200826352 on(name='霍雙雙200826352_data',filename='E:

霍霍

雙雙

雙雙

***5

霍霍

雙雙

雙雙200826352_data.mdf',size=10mb,maxsize=50mb,filegrowth=10%)log on(name='霍雙雙200826352_log',filename='E:200826352_log.ldf',size=10mb,maxsize=50mb,filegrowth=10%)在查詢分析器重建立各表的命令代碼： 建立student表：

create table student(sno char(5)primary key,sname char(20),ssex char(2),sage int,sdept char(15))建立course表：

create table course(cno char(2)primary key,cname char(15),cpno char(2),ccredit int)建立cs表：

use 霍雙雙200826352 create table sc(sno char(5),cno char(2),grade smallint,primary key(sno,cno),foreign key(sno)references student(sno),foreign key(cno)references course(cno))實驗二

T-SQL查詢、存儲過程、觸發(fā)器、完整性上機作業(yè)題 第一部分 ：T-SQL程序設計

（1）.如果3號課程的平均成績在80分以上，則輸出“3號課程成績良好”，否則輸出“3號成績一般” declare @avg float set @avg=(select avg(grade)from sc where cno='3')if @avg>80print'3號課程成績良好'else print'3號成績一般'（2）計算并輸出95003號學生的平均成績，若無該生信息，則顯示“該生未選課”,提示信息.declare @avg float if(select count(*)from sc where sno='95003')=0 print '該生未選課' else begin select @avg=avg(grade)from sc where sno='95003' print'95003號學生平均成績' print @avg end（3）.如果有成績在90分以上的學生,則顯示他的學號,課程和成績,否則顯示“沒有學生的課程成績在90分以上”提示信息

declare @text char(10)if exists(select grade from SC where grade>90)select Sno,Cno,Grade from SC where Grade>90 else begin set @text='沒有學生的課程成績在90分以上' print @text end ×(4).利用游標逐行顯示student表中的記錄。

declare stu cursor for select *from student open stu fetch next from stu while @@fetch_status=0 fetch next from stu close stu deallocate stu(5).用自定義函數(shù)計算全體男生的平均年齡

create function avg_age(@sex char(2))returns int as begin declare @aver int select @aver=(select avg(Sage)from Student where Ssex=@sex)return @aver end go declare @aver1 int,@sex char(2)set @sex='男' select @aver1=dbo.avg_age(@sex)select @aver1 as '全體男生的平均年齡' go(6).顯示course表中課程名的前2個字符。select substring(Cname,1,2)from Course(7).在一列中顯示student中各元組的學號中的年級，列名顯示為“年級”；另一列中顯示學號中的學生序列號，列名顯示為“序號”。

select substring(Sno,1,2)年級,substring(Sno,3,len(Sno)-1)序號 from Student order by Sno(8).在選課表中顯示學號、課程號，并根據(jù)成績：0-59顯示“不合格”；60-79顯示“合格”；80-89顯示“良好”；90-100顯示“優(yōu)秀?！?/p>

select Sno as '學號',Cno as '課程號', grade =case when Grade<=59 then '不合格' when Grade>=60 and Grade<=79 then '合格' when Grade>=80 and Grade<=89 then '良好' else '優(yōu)秀' end from SC 第二部分 ：存儲過程

（1）創(chuàng)建一個為worker表添加職工記錄的存儲過程Addworker go

if exists(select name from sysobjects where name='Addworker' and type='P')drop procedure Addworker go create proc Addworker @職工號 char(4),@姓名 char(8),@性別 char(2),@出生日期 datetime,@黨員否 char(2),@參加工作 datetime,@部門號 char(4)as insert into worker(職工號,姓名,性別,出生日期,黨員否,參加工作,部門號)values(@職工號,@姓名,@性別,@出生日期,@黨員否,@參加工作,@部門號)go exec Addworker '16','王璐','女','1988-11-20','否','2010-08-21','11'（2）創(chuàng)建一個存儲過程Delworker刪除worker表中指定職工號的記錄 go

if exists(select name from sysobjects where name='Delworker' and type='P')drop procedure Delworker go create procedure Delworker @職工號 char(4)as delete from worker where 職工號=@職工號

go exec Delworker '16'（3）顯示存儲過程Delworker的定義信息。Sp_helptext Delworker（4）刪除存儲過程Addworker和Delworker。drop procedure Addworker, Delworker（5）創(chuàng)建并執(zhí)行以下存儲過程：

a.從數(shù)據(jù)庫表中查詢，返回學生學號、姓名、課程名、成績 use 霍雙雙200826351 go if exists(select name from sysobjects where name='select_stu' and type='P')drop procedure select_stu go create procedure select_stu as select SC.Sno,Sname,Cname,Grade from Student,SC,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno go exec select_stu b.從數(shù)據(jù)庫表中查詢指定學號的學生學號，姓名，該存儲過程接受與傳遞參數(shù)，精確匹配的值 use 霍雙雙200826351 go if exists(select name from sysobjects where name='select_sno' and type='P')drop procedure select_sno go create procedure select_sno @Sno char(5)as select Sno,Sname from Student where Sno=@Sno go exec select_sno '95002' ×第三部分：觸發(fā)器

(1)在表depart上創(chuàng)建一個觸發(fā)器 depart_update , 當更改部門號時同步更改 worker表中對應的部門號。Go If exists(select name from sysobjects where name='depart_update'and type='tr')drop trigger depart_update go Create trigger depart_update on depart for update as set worker.部門號=（select 部門號 from inserted）where worker.部門號=(select 部門號from deleted)(2)在表worker上創(chuàng)建一個觸發(fā)器worker_delete,當刪除職工記錄時同步刪除salary表中對應的職工記錄。Go If exists(select name from sysobjects where name='worker_delete'and type='tr')drop trigger worker_delete go create trigger worker_delete on worker for delete as delete salary where salary.職工號=(select 職工號 from deleted)(3)刪除觸發(fā)器depart_update(4)刪除觸發(fā)器worker_delete(5)在數(shù)據(jù)庫中創(chuàng)建一個觸發(fā)器，向選課表添加一條紀錄時，檢查該紀錄的學號在學生表中是否存在，檢查該紀錄的課程號在課程表中是否存在，若其中有一項為否，則拒絕添加操作，并顯示“違反數(shù)據(jù)一致性”提示信息。Go If exists(select name from sysobjects where name='add_student'and type='tr')drop trigger add_student go

create trigger add_student on sc for insert as go 第四部分：數(shù)據(jù)庫完整性

1、實施worker表的“性別”字段默認值為“男”的約束 create default default_sex as '男' go sp_bindefault'default_sex','worker.性別'

2、實施salary表的“工資”字段值在0~9999的約束、create rule salary_rule as @salary='[0~9999]' go sp_bindrule 'salary_rule','salary.工資'

3、實施depart表的“部門號”字段值唯一的非聚集索引的約束

4、為worker表建立外鍵“部門號”，參考表depart的“部門號”列。

5、建立一個規(guī)則 sex：@性別=’男’OR @性別=’女’,將其綁定到worker表的“性別”列上。Create rule sex as @性別='男'OR @性別='女' Go Sp_bindrule 'sex','worker.性別'

6、刪除1小題所建立的約束。

7、刪除2小題所建立的約束。

8、刪除3小題所建立的約束

9、刪除4小題所建立的約束

10.解除5小題所建立的綁定并刪除規(guī)則sex

第五篇：數(shù)據(jù)庫上機實驗8實驗報告

上機實驗八——完整性約束的實現(xiàn)

一、實習目的：

掌握SQL中實現(xiàn)數(shù)據(jù)完整性的方法，加深理解關系數(shù)據(jù)模型的三類完整性約束。

二、實習準備：

1.復習“完整性約束SQL定義”

2.完成習題四第10題中的各項操作的SQL語句。

3.了解SQL Server 中實體完整性、參照完整性和用戶自定義完整性的實現(xiàn)手段

三、實習內(nèi)容：

1.驗證習題四第10題四個表結(jié)構(gòu)的SQL語句。

表一：Sstudent CREATE TABLE Sstudent(Sno char(7)NOT NULL PRIMARY KEY, Sname VarChar(20)NOT NULL, Ssex Char(2)NOT NULL DEFAULT('男')check(Ssex IN('男','女')), Sage smallint check(Sage >14 AND Sage<65), Clno Char(5)NOT NULL REFERENCES Cclass(Clno)ON UPDATE CASCADE);

表二：Ccourse CREATE TABLE Ccourse(Cno Char(1)NOT NULL PRIMARY KEY, Cname VarChar(20)NOT NULL, Credit Smallint CHECK(Credit IN(1,2,3,4,5,6)));

表三：Cclass CREATE TABLE Cclass(Clno Char(5)NOT NULL PRIMARY KEY, Speciality VarChar(20)NOT NULL, Inyear Char(4)NOT NULL, Number Integer CHECK(Number>1 AND Number<100), Mointor Char(7)REFERENCES Student(Sno));

表四：Ggrade CREATE TABLE Ggrade(Sno Char(7)NOT NULL REFERENCES Student(Sno)ON DELETE CASCADE ON UPDATE CASCADE, Cno Char(1)NOT NULL REFERENCES Course(Cno)ON DELETE CASCADE ON UPDATE CASCADE, Gmark Numeric(4,1)CHECK(Gmark>0 AND Gmark<100), PRIMARY KEY(Sno,Cno));

2.SQL Server中提供了那些方法實現(xiàn)實體完整性、參照完整體和用戶自定義完整性

答：實體完整性：是通過主碼的定義（PRIMARY KEY）來實現(xiàn)的；

參照完整性：是利用外部碼（REFERENCES）的說明，以限制相關表中某些屬性的取值，當用戶違反規(guī)則時，提供三種：RESTRICT（限制策略）,CASCADE(級聯(lián)策略),SET NULL(置空策略)；

用戶自定義完整性：check約束，對元組的CHECK約束