第一篇：數(shù)據(jù)庫(kù)與軟件工程上機(jī)實(shí)驗(yàn)答案
《數(shù)據(jù)庫(kù)與軟件工程》上機(jī)實(shí)驗(yàn)答案
實(shí)驗(yàn)三 SQL語(yǔ)言的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 讀者編號(hào)
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)
實(shí)驗(yàn)四 SQL語(yǔ)言的DML初步
1． CREATE DEFAULT ZEROVALUE AS 0
sp_bindefault ZEROVALUE, '圖書(shū).借出否'
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語(yǔ)句對(duì)“圖書(shū)”表插入6條記錄
INSERT INTO 圖書(shū)(書(shū)號(hào),類(lèi)別,出版社,作者,書(shū)名,定價(jià))
VALUES('0001','計(jì)算機(jī)類(lèi)','清華出版社','嚴(yán)蔚敏','數(shù)據(jù)結(jié)構(gòu)', 20.11)INSERT INTO 圖書(shū)(書(shū)號(hào),類(lèi)別,出版社,作者,書(shū)名,定價(jià))
VALUES('0002','計(jì)算機(jī)類(lèi)','清華出版社','苗雪蘭','數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用',29.00)
INSERT INTO 圖書(shū)(書(shū)號(hào),類(lèi)別,出版社,作者,書(shū)名,定價(jià))
VALUES('0003','計(jì)算機(jī)類(lèi)','清華出版社','李建中','軟件工程',48.00)INSERT INTO 圖書(shū)(書(shū)號(hào),類(lèi)別,出版社,作者,書(shū)名,定價(jià))
VALUES('0004','計(jì)算機(jī)類(lèi)','電子工業(yè)出版社','湯惟','WEB技術(shù)',32.00)
INSERT INTO 圖書(shū)(書(shū)號(hào),類(lèi)別,出版社,作者,書(shū)名,定價(jià))
VALUES('0005','自動(dòng)化類(lèi)','機(jī)械工業(yè)出版社','胡壽松','自動(dòng)控制原理',52.00)
INSERT INTO 圖書(shū)(書(shū)號(hào),類(lèi)別,出版社,作者,書(shū)名,定價(jià))
VALUES('0006','自動(dòng)化類(lèi)','機(jī)械工業(yè)出版社','鄭大鐘','線(xiàn)性控制理論',32.00)用INSERT語(yǔ)句對(duì)“讀者”表插入4條記錄
INSERT INTO 讀者(讀者編號(hào),姓名,單位,性別,電話(huà))
VALUES('10001','張三','東華大學(xué)','男','67792312')
INSERT INTO 讀者(讀者編號(hào),姓名,單位,性別,電話(huà))
VALUES('10002','李四','東華大學(xué)','女','67792312')
INSERT INTO 讀者(讀者編號(hào),姓名,單位,性別,電話(huà))
VALUES('10003','王五','東華大學(xué)','男','67792312')
INSERT INTO 讀者(讀者編號(hào),姓名,單位,性別,電話(huà))
VALUES('10004','李明','東華大學(xué)','女','67792312')
用INSERT語(yǔ)句對(duì)“借閱”表插入7條記錄
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
VALUES('0001','10001','2006-04-19 09:58:03','2006-05-19 8:38:23')
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
VALUES('0006','10002','2006-07-01 15:28:12','2006-07-18 9:14:02')
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
VALUES('0002','10002','2006-07-01 15:28:30','2006-07-17 19:10:32')
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
VALUES('0002','10003','2006-07-03 15:28:30','2006-08-09 15:28:30')
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
VALUES('0004','10002','2007-05-01 11:28:24','2007-08-01 8:09:04')
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
VALUES('0005','10002','2007-11-01 10:43:12','2007-12-01 14:09:56')
INSERT INTO 借閱(書(shū)號(hào),讀者編號(hào),借閱日期,歸還日期)
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 讀者編號(hào)='10003' AND
借閱日期 BETWEEN '2006-07-03' AND '2006-07-04' AND 書(shū)號(hào)='0002'
5． DELETE FROM 借閱
WHERE 借閱日期<'2006-06-01'
1． SELECT 書(shū)號(hào),書(shū)名 as 名稱(chēng),定價(jià) as 價(jià)格 FROM 圖書(shū)
WHERE 類(lèi)別='自動(dòng)化類(lèi)'
2． 方法1：
SELECT * FROM 圖書(shū)
WHERE 類(lèi)別='計(jì)算機(jī)類(lèi)'
UNION
SELECT * FROM 圖書(shū)
WHERE 出版社='電子工業(yè)出版社'
方法2：
SELECT * FROM 圖書(shū)
WHERE 類(lèi)別='計(jì)算機(jī)類(lèi)' OR 出版社='電子工業(yè)出版社'
3． SELECT * INTO 計(jì)算機(jī)圖書(shū)表 FROM 圖書(shū)
WHERE 類(lèi)別='計(jì)算機(jī)類(lèi)'
4． SELECT DISTINCT 讀者.* FROM 讀者,借閱
WHERE 讀者.讀者編號(hào)=借閱.讀者編號(hào)
5． SELECT 讀者.讀者編號(hào),姓名,單位,圖書(shū).書(shū)號(hào),書(shū)名,借閱日期
FROM 讀者,借閱,圖書(shū)
WHERE 讀者.讀者編號(hào)=借閱.讀者編號(hào) AND 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào)
6． SELECT 圖書(shū).書(shū)號(hào),書(shū)名,類(lèi)別,借閱日期 FROM 讀者,借閱,圖書(shū)
WHERE 讀者.讀者編號(hào)=借閱.讀者編號(hào) AND 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào)
AND 姓名='張三'
ORDER BY 借閱日期 DESC
7． SELECT 讀者.讀者編號(hào),姓名,書(shū)名,DATEDIFF(day,借閱日期,歸還日期)as 借閱時(shí)間
FROM 讀者,借閱,圖書(shū)
WHERE 讀者.讀者編號(hào)=借閱.讀者編號(hào) AND 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào)
AND DATEDIFF(day,借閱日期,歸還日期)>20
order by 借閱時(shí)間
8． SELECT * FROM 讀者
WHERE NOT EXISTS（SELECT * FROM 圖書(shū)
WHERE 類(lèi)別='自動(dòng)化類(lèi)' and NOT EXISTS（SELECT * FROM 借閱
WHERE 讀者編號(hào)=讀者.讀者編號(hào) and 書(shū)號(hào)=圖書(shū).書(shū)號(hào)))
1． SELECT 類(lèi)別,COUNT(*)AS 數(shù)量, AVG(定價(jià))AS平均價(jià)格,SUM(定價(jià))AS 總價(jià)
FROM 圖書(shū)
GROUP BY 類(lèi)別
ORDER BY 類(lèi)別
2． SELECT YEAR(借閱日期)as 年份,書(shū)號(hào),COUNT(*)AS 借閱次數(shù)
FROM 借閱
GROUP BY YEAR(借閱日期),書(shū)號(hào)
ORDER BY 借閱次數(shù) DESC
3． SELECT 讀者.讀者編號(hào),COUNT(書(shū)號(hào))AS 借閱次數(shù) FROM 讀者
LEFT JOIN
(SELECT * FROM 借閱 WHERE 借閱日期 BETWEEN '2006-7-1' AND '2007-10-1')A
ON 讀者.讀者編號(hào)=A.讀者編號(hào)
GROUP BY 讀者.讀者編號(hào)
4． SELECT 讀者編號(hào),COUNT(*)AS 次數(shù) FROM 借閱,圖書(shū)
WHERE 借閱.書(shū)號(hào)=圖書(shū).書(shū)號(hào) AND 書(shū)名='數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用'
GROUP BY 讀者編號(hào)
ORDER BY 次數(shù) DESC
5． 方法1：
SELECT MAX(次數(shù))AS 最多,MIN(次數(shù))AS 最少,AVG(次數(shù)*1.0)AS平均
FROM（SELECT 圖書(shū).書(shū)號(hào),COUNT(*)AS 次數(shù) FROM 圖書(shū),借閱
WHERE 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào) AND 類(lèi)別='計(jì)算機(jī)類(lèi)'
GROUP BY 圖書(shū).書(shū)號(hào))A
方法2：
SELECT 圖書(shū).書(shū)號(hào),COUNT(*)*1.0 AS 次數(shù) FROM 圖書(shū),借閱
WHERE 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào) AND 類(lèi)別='計(jì)算機(jī)類(lèi)'
GROUP BY 圖書(shū).書(shū)號(hào)
COMPUTE MAX(COUNT(*)*1.0),MIN(COUNT(*)*1.0),AVG(COUNT(*)*1.0)
6． SELECT 類(lèi)別 FROM 圖書(shū)
GROUP BY 類(lèi)別
HAVING MAX(定價(jià))>=ALL(SELECT 2*AVG(定價(jià))FROM 圖書(shū) GROUP BY 類(lèi)別)
7． SELECT 書(shū)號(hào),書(shū)名,定價(jià),出版社 FROM 圖書(shū)
WHERE 類(lèi)別='計(jì)算機(jī)類(lèi)'
ORDER BY 出版社 DESC
COMPUTE COUNT(書(shū)號(hào))BY 出版社
COMPUTE COUNT(書(shū)號(hào))
實(shí)驗(yàn)七 存儲(chǔ)過(guò)程、觸發(fā)器和數(shù)據(jù)庫(kù)恢復(fù)
1．（1）
CREATE PROCEDURE 借閱情況
@Readerno VARCHAR(8)
AS
SELECT 讀者.讀者編號(hào),姓名,圖書(shū).書(shū)號(hào),書(shū)名,借閱日期, 借出否 AS 歸還否FROM 讀者,借閱,圖書(shū)
WHERE 讀者.讀者編號(hào)=借閱.讀者編號(hào) AND 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào)AND 讀者.讀者編號(hào)=@Readerno
EXEC 借閱情況 ‘10004’
（2）
CREATE PROCEDURE 借出情況
@Bookno VARCHAR(10)
AS
SELECT 書(shū)名,姓名,借閱日期,歸還日期
FROM 讀者,借閱,圖書(shū)
WHERE 讀者.讀者編號(hào)=借閱.讀者編號(hào) AND 圖書(shū).書(shū)號(hào)=借閱.書(shū)號(hào)AND 圖書(shū).書(shū)號(hào)=@Bookno
EXEC 借出情況 '0004'
2．CREATE TRIGGER lendbookinsert ON 借閱
FOR INSERT
AS
IF(SELECT 借出否 FROM 圖書(shū) WHERE 書(shū)號(hào) IN
(SELECT 書(shū)號(hào) FROM INSERTED))=1
BEGIN
PRINT '該書(shū)已經(jīng)借出'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
UPDATE 圖書(shū)
SET 借出否=1
WHERE 書(shū)號(hào) IN(SELECT 書(shū)號(hào) FROM INSERTED)
END
第二篇：數(shù)據(jù)庫(kù)上機(jī)實(shí)驗(yàn)
創(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語(yǔ)句創(chuàng)建數(shù)據(jù)表和輸入數(shù)據(jù) 1.使用SQL語(yǔ)句創(chuàng)建數(shù)據(jù)表 P72.例3-2
2．使用SQL語(yǔ)句輸入數(shù)據(jù) P82.例3.8
習(xí)題：P.105（1）（2）insert 數(shù)據(jù)操作Insert、Update、Delete
P105 3.上機(jī)練習(xí)題（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語(yǔ)句基礎(chǔ)1 1.將teaching數(shù)據(jù)庫(kù)中score表的studentno列設(shè)置為引用表student的外鍵。ALTER TABLE Score ADD CONSTRAINT FK_score_student FOREIGN KEY(studentno)REFERENCES student(studentno)2.將teaching數(shù)據(jù)庫(kù)中class表的classname創(chuàng)建UNIQUE約束。ALTER TABLE class ADD CONSTRAINT UQ_class UNIQUE(classname)執(zhí)行如下插入語(yǔ)句，查看提示信息
INSERT INTO class VALUES(‘090602’, ’計(jì)算機(jī)0902’, ’計(jì)算機(jī)學(xué)院’, ’馬文斐’)3.為teaching數(shù)據(jù)庫(kù)中student表的birthday列創(chuàng)建CHECK約束，規(guī)定學(xué)生的年齡在17-25歲之間。
ALTER TABLE student ADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE())-YEAR(birthday))BETWEEN 17 AND 25 執(zhí)行如下插入語(yǔ)句，查看提示信息
INSERT INTO student(studentno, sname, sex, birthday, classno)VALUES(‘0922221328’, ’張?jiān)础? ’男’, ’1983-04-05’, ’090501’)提示：表達(dá)式Y(jié)EAR(GETDATE())-YEAR(birthday)4.為teaching數(shù)據(jù)庫(kù)創(chuàng)建規(guī)則prof_rule，規(guī)定教師職稱(chēng)取值只能為’助教’,’講師’,’副教授’,’教授’，并將其綁定到teacher表的prof列上。
CREATE RULE prof_rule AS @prof IN(’助教’,’講師’,’副教授’,’教授’)EXEC sp_bindrule ‘prof_rule’, ‘teacher.prof’
執(zhí)行如下插入語(yǔ)句，查看提示信息
INSERT INTO teacher VALUES(‘t05002’, ’張?jiān)础? ’軟件工程’, ’工程師’, ’計(jì)算機(jī)學(xué)院’)提示：表達(dá)式IN(職稱(chēng)列表)5.編寫(xiě)程序，輸出在1-3000之間能被17整除的最大數(shù)值
提示：可使循環(huán)控制變量從最大值開(kāi)始，逐步減少，第一個(gè)滿(mǎn)足被17整除的數(shù)值即為所求解的結(jié)果，可通過(guò)BREAK語(yǔ)句跳出循環(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語(yǔ)句基礎(chǔ)2 1.利用Transact-SQL語(yǔ)句聲明一個(gè)長(zhǎng)度為16的nchar型變量bookname，并賦初值為“SQL Server數(shù)據(jù)庫(kù)編程”，打印該變量。
2.編程求50-100之間的所有能被3整除的奇數(shù)之和。
3.編寫(xiě)程序，聲明CHAR型變量@ch，并賦初值。判斷字符變量@ch中存放的是字母、數(shù)字字符還是其他字符，并輸出相關(guān)信息。（提示：UPPER函數(shù)可以將小寫(xiě)字母轉(zhuǎn)換為大寫(xiě)字母）
4.編寫(xiě)程序，求解如下分?jǐn)?shù)序列的前20項(xiàng)之和并打印輸出結(jié)果。
S=2/1+3/2+5/3+8/5+13/8+21/13+…
分析數(shù)列的規(guī)律：后一項(xiàng)的分子為前一項(xiàng)的分子和分母之和，后一項(xiàng)的分母則為前一項(xiàng)的分子，通過(guò)循環(huán)即可實(shí)現(xiàn)累加。注意：聲明分子和分母為浮點(diǎn)型數(shù)據(jù)NUMERIC(20,7).5.查詢(xún)編號(hào)為c06108, c08106, c05109課程的學(xué)生總評(píng)成績(jī)等級(jí)，平時(shí)成績(jī)usually*0.2+期末成績(jī)final*0.8>=90的為優(yōu)秀，80-90為良好，70-80為中等，60-70為及格，<60為不及格。（提示：使用CASE …END語(yǔ)句）數(shù)據(jù)檢索1 1.查詢(xún)course表中所有的記錄。2.查詢(xún)student表中女生的人數(shù)。
3.查詢(xún)teacher表中每一位教授的教師號(hào)、姓名和專(zhuān)業(yè)名稱(chēng)。
4.利用現(xiàn)有的表生成新表，新表中包括學(xué)號(hào)、學(xué)生姓名、課程號(hào)和總評(píng)成績(jī)。其中：總評(píng)成績(jī)=final*0.8+usually*0.2 5.查詢(xún)student表中所有年齡大于20歲的男生的姓名和年齡。6.查詢(xún)計(jì)算機(jī)學(xué)院教師的專(zhuān)業(yè)名稱(chēng)。
7.查詢(xún)Email使用126郵箱的所有學(xué)生的學(xué)號(hào)、姓名和電子郵箱地址。8.查詢(xún)score表中選修’c05109’或’c05103’課程，并且課程期末成績(jī)?cè)?0~100分之間的學(xué)生姓名和期末成績(jī)。
9.查詢(xún)student表中所有學(xué)生的基本信息，查詢(xún)結(jié)果按班級(jí)號(hào)classno升序排列，同一班級(jí)中的學(xué)生按入學(xué)成績(jī)point降序排列。10.查詢(xún)選修’c05109’課程，并且期末成績(jī)?cè)谇?名的學(xué)生學(xué)號(hào)、課程號(hào)和期末成績(jī)。（提示：TOP 5）數(shù)據(jù)檢索2
1.按性別分組，求出student表中每組學(xué)生的平均年齡。2.統(tǒng)計(jì)每個(gè)學(xué)生期末成績(jī)的平均分。
3.輸出student表中沒(méi)有職稱(chēng)的職工的教師號(hào)、姓名、專(zhuān)業(yè)和部門(mén)。4.查詢(xún)選修課程且期末成績(jī)不為空的學(xué)生人數(shù)。
5.查詢(xún)每名學(xué)生的學(xué)號(hào)、選修課程數(shù)目、總成績(jī)，并將查詢(xún)結(jié)果存放到生成的“學(xué)生選課統(tǒng)計(jì)表”中。6.查詢(xún)各班學(xué)生的人數(shù)。
7.查詢(xún)各課程期末成績(jī)的最高分和最低分。
8.查詢(xún)教兩門(mén)及以上課程的教師編號(hào)、任課班級(jí)數(shù)。
9.查詢(xún)課程編號(hào)以’c05’開(kāi)頭、被3名及以上學(xué)生選修且期末成績(jī)的平均分高于75分的課程號(hào)、選修人數(shù)和期末成績(jī)平均分，并按平均分降序排序。10.查詢(xún)所有08級(jí)學(xué)生的期末成績(jī)平均分，要求利用COMPUTE BY方法顯示每一名學(xué)生的學(xué)生編號(hào)、課程號(hào)、期末成績(jī)的明細(xì)表，以及期末成績(jī)平均分的匯總表。
11.查詢(xún)所有女生入學(xué)成績(jī)的最高分，要求利用COMPUTE BY方法既顯示明細(xì)又顯示匯總結(jié)果。SQL語(yǔ)句的高級(jí)應(yīng)用1
1.查詢(xún)每一位教授的教師號(hào)、姓名和講授的課程名稱(chēng)。表：teacher,course,teach_class 2.分別統(tǒng)計(jì)每個(gè)學(xué)生期末成績(jī)高于75分的課程門(mén)數(shù)。表：student,score 3.計(jì)算每個(gè)學(xué)生獲得的學(xué)分。表：student,score,course 4.獲取入學(xué)時(shí)間在2008年到2009年之間的所有學(xué)生中入學(xué)年齡小于19歲的學(xué)生的學(xué)號(hào)、姓名及所修課程的課程名稱(chēng)。表：student, stu_course 5.查詢(xún)09級(jí)學(xué)生的學(xué)號(hào)、姓名、課程及學(xué)分。表：student,stu_course 6.查詢(xún)所有班級(jí)的期末成績(jī)平均分，并按照平均分降序排列。表：score,student 7.查詢(xún)教師基本信息和教授課程信息，其中包括未分配課程的教師信息。表：teacher,teach_class 8.查詢(xún)’090501’班級(jí)中選修了’韓晉升’老師講授的課程的學(xué)生的學(xué)號(hào)、姓名、課程名和期末成績(jī)。表：score,student,course,teach_class,teacher 9.查詢(xún)每門(mén)課程的課程號(hào)、課程名和選修該課程的學(xué)生人數(shù)，并按所選人數(shù)升序排序。表：score,course 10.查詢(xún)兩門(mén)及以上課程的期末成績(jī)超過(guò)80分的學(xué)生的姓名及其平均成績(jī)。表：student,score SQL語(yǔ)句的高級(jí)應(yīng)用2 – 使用子查詢(xún)
1.輸出student表中年齡大于女生平均年齡的男生的所有信息。2.查詢(xún)?nèi)雽W(xué)考試成績(jī)最高的學(xué)生的學(xué)號(hào)、姓名和入學(xué)成績(jī)。3.查詢(xún)所有教授’c05127’號(hào)課程的教師信息。
4.查詢(xún)同時(shí)教授’c05127’和’c05109’號(hào)課程的教師信息。
5.查詢(xún)至少選修了姓名為’韓吟秋’的學(xué)生所選修課程中的一門(mén)課的學(xué)生的學(xué)號(hào)和姓名。
6.查詢(xún)沒(méi)有被任何學(xué)生選修的課程編號(hào)、課程名稱(chēng)和學(xué)分。
7.查詢(xún)’C語(yǔ)言’課程期末成績(jī)比’電子技術(shù)’課程期末成績(jī)高的所有學(xué)生的學(xué)號(hào)和姓名。
8.查詢(xún)所有班級(jí)期末平均成績(jī)的最高分，并將其賦值給變量，通過(guò)PRINT語(yǔ)句輸出。視圖與索引
使用SQL語(yǔ)言
1.創(chuàng)建一個(gè)視圖v_teacher，查詢(xún)所有“計(jì)算機(jī)學(xué)院”的教師信息。
CREATE VIEW v_teacher AS SELECT * FROM teacher WHERE department='計(jì)算機(jī)學(xué)院' GO SELECT * FROM v_teacher
2.創(chuàng)建一個(gè)視圖v_avgstu，查詢(xún)每個(gè)學(xué)生的學(xué)號(hào)、姓名及平均分，并且按照平均分降序排列。
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選項(xiàng)。
ALTER VIEW v_teacher AS SELECT * FROM teacher WHERE department='計(jì)算機(jī)學(xué)院' WITH CHECK OPTION
4.通過(guò)視圖v_teacher向基表teacher中分別插入數(shù)據(jù)(‘05039’, ‘張馨月’,’計(jì)算機(jī)應(yīng)用’,’講師’,’計(jì)算機(jī)學(xué)院’)和(‘06018’, ‘李誠(chéng)’,’機(jī)械制造’,’副教授’,’機(jī)械學(xué)院’)，并查看插入數(shù)據(jù)情況。
INSERT INTO v_teacher VALUES('05039', '張馨月','計(jì)算機(jī)應(yīng)用','講師','計(jì)算機(jī)學(xué)院')GO SELECT * FROM teacher
消息550，級(jí)別16，狀態(tài)1，第1 行
試圖進(jìn)行的插入或更新已失敗，原因是目標(biāo)視圖或者目標(biāo)視圖所跨越的某一視圖指定了WITH CHECK OPTION，而該操作的一個(gè)或多個(gè)結(jié)果行又不符合CHECK OPTION 約束。語(yǔ)句已終止。
5.通過(guò)視圖v_teacher將基表teacher中教師編號(hào)為05039的教師職稱(chēng)修改為”副教授”。
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)建視圖，按照樣圖進(jìn)行操作。
第三篇：數(shù)據(jù)庫(kù)上機(jī)實(shí)驗(yàn)(二)
數(shù)據(jù)庫(kù)上機(jī)實(shí)驗(yàn)內(nèi)容及要求（第二部分）
1． 建立工廠管理數(shù)據(jù)庫(kù)
工廠（包括廠名和廠長(zhǎng)名）需要建立一個(gè)管理數(shù)據(jù)庫(kù)存儲(chǔ)以下信息：
（1）一個(gè)廠內(nèi)有多個(gè)車(chē)間，每個(gè)車(chē)間有車(chē)間號(hào)、車(chē)間主任姓名、地址和聯(lián)系電話(huà)；
（2）一個(gè)車(chē)間有多個(gè)工人，每個(gè)工人有職工號(hào)、姓名、年齡、性別和工種；
（3）一個(gè)車(chē)間生產(chǎn)多種產(chǎn)品，產(chǎn)品有產(chǎn)品號(hào)和價(jià)格；每種產(chǎn)品只能由一個(gè)車(chē)間生產(chǎn)；
（4）一個(gè)車(chē)間制造多種零件，一種零件也可能為多個(gè)車(chē)間制造。零件有零件號(hào)、重
量和價(jià)格；
（5）一種產(chǎn)品可由多種零件組成，一種零件也可以裝配出多種產(chǎn)品；
（6）產(chǎn)品和零件均存入倉(cāng)庫(kù)；
（7）廠內(nèi)有多個(gè)倉(cāng)庫(kù)，倉(cāng)庫(kù)有倉(cāng)庫(kù)號(hào)、倉(cāng)庫(kù)主任姓名和電話(huà)。
根據(jù)以上需求分析結(jié)果，按照下述要求，設(shè)計(jì)并建立工廠管理數(shù)據(jù)庫(kù)。? 分析實(shí)體及聯(lián)系，設(shè)計(jì)E-R圖。
? 將E-R圖轉(zhuǎn)換成關(guān)系模式，并規(guī)范化到3NF。
? 在Microsoft SQL Server2000中基于“企業(yè)管理器”建立數(shù)據(jù)庫(kù)及相關(guān)對(duì)象（主
碼，外碼，索引，約束等）。
? 測(cè)試數(shù)據(jù)入庫(kù)
2． 基于“查詢(xún)分析器”，完成并保存下述題目的SQL腳本
（1）建立“工種”是“鉗工”的所有職工詳細(xì)信息的視圖；
（2）建立“車(chē)間號(hào)”是“CJ01”的鉗工詳細(xì)信息的視圖；
（3）建立使用了“零件號(hào)”是“LJ0002”的所有產(chǎn)品詳細(xì)信息的視圖；
（4）查詢(xún)使用了“零件號(hào)”是“LJ0002”的產(chǎn)品的生產(chǎn)車(chē)間號(hào)；
（5）對(duì)零件表按照“零件號(hào)”建立唯一索引；
（6）對(duì)職工表按照“性別”建立聚簇索引；
（7）查詢(xún)“車(chē)間主任姓名”是“趙平”的“車(chē)間地址”和“聯(lián)系電話(huà)”；
（8）查詢(xún)“職工號(hào)”是“ZG0001”的職工所在車(chē)間的“車(chē)間主任姓名”和“聯(lián)系電
話(huà)”；（連接查詢(xún)實(shí)現(xiàn)）
（9）查詢(xún)“產(chǎn)品號(hào)”是“CP0001”的產(chǎn)品的生產(chǎn)車(chē)間的“車(chē)間主任姓名”和“聯(lián)系
電話(huà)”；（嵌套查詢(xún)實(shí)現(xiàn)）
（10）查詢(xún)使用了“零件號(hào)”是“LJ0002”的所有產(chǎn)品的“產(chǎn)品號(hào)”，且查詢(xún)結(jié)果按照
“零件數(shù)量”降序排列；
（11）查詢(xún)使用了“零件號(hào)”是“LJ0002”的所有產(chǎn)品的“產(chǎn)品號(hào)”和“產(chǎn)品價(jià)格”；
（12）查詢(xún)使用了“零件號(hào)”是“LJ0002”的所有產(chǎn)品的生產(chǎn)車(chē)間的“車(chē)間主任姓名”
和“聯(lián)系電話(huà)”；
（13）查詢(xún)使用了“零件號(hào)”是“LJ0002”的產(chǎn)品數(shù)；
（14）查詢(xún)“LJ0002”號(hào)零件裝配產(chǎn)品的使用總量；
（15）查詢(xún)使用了3種以上零件的產(chǎn)品號(hào)；
【注意】：下機(jī)時(shí)保存數(shù)據(jù)庫(kù)文件（.mdf和.ldf）及SQL腳本文件到U盤(pán)。
第四篇：數(shù)據(jù)庫(kù)上機(jī)實(shí)驗(yàn)總結(jié)(含代碼)
實(shí)驗(yàn)一
（1）無(wú)條件單表查詢(xún)
select sname NAME,'year of birth:' BIRTH,2004-sage BIRTHDAY,LOWER(sdept)DEPARTMENT FROM student;（2）有條件單表查詢(xún)
SELECT sname,sdept,sage FROM student WHERE sage NOT BETWEEN 20 AND 23;（3）單表嵌套（一層）查詢(xún)
SELECT sno,sname,sdept FROM student WHERE sdept IN(SELECT sdept FROM student WHERE sname='劉晨');（4）復(fù)合條件多表查詢(xún)
SELECT student.sno,sname,cname,grade FROM student ,sc,course WHERE student.sno=sc.sno AND sc.cno=course.cno;（5）使用COUNT（）的單表查詢(xún) SELECT COUNT(*)FROM student;（6）使用AVG（）的單表查詢(xún)
SELECT AVG(grade)'平均成績(jī)' from SC where CNO='1';（7）查詢(xún)結(jié)果分組
SELECT cno,COUNT(sno)'人數(shù)' FROM sc GROUP BY cno;（8）查詢(xún)結(jié)果排序
SELECT * FROM student ORDER BY sdept,sage DESC;（9）使用通配符的查詢(xún)
SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'劉%';（10）使用換碼字符的單表查詢(xún)
SELECT cno,ccredit FROM course WHERE cname LIKE 'DB_Design'ESCAPE'';（11）插入單個(gè)元組 插入一個(gè)新學(xué)生元組
Insert into student(sno,sname,ssex,sdept,sage)values('200215128','陳冬','男','IS',18)（12）插入子查詢(xún)結(jié)果
對(duì)每一個(gè)系，求學(xué)生平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù) 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）修改某個(gè)元組的值
將學(xué)生200215121的年齡改為22歲
Update student set sage=’22’ where sno=’200215121’（14）修改多個(gè)元組的值 將所有學(xué)生的年齡增加一歲 Update student set sage=sage+1（15）刪除一個(gè)元組的值 刪除學(xué)號(hào)為200215128的學(xué)生記錄
delete from student where sno='200215128'（16）建立視圖 建立信息系學(xué)生的視圖
create view is_student as select sno,sname,sage from student where sdept='IS' ×（17）查詢(xún)視圖
查詢(xún)選修了1號(hào)課程的信息系學(xué)生信息
Select is_student.sno,sname from is_student,sc where is_student.sno=sc.sno and sc.cno=’1’ ×（18）更新視圖
將信息系學(xué)生視圖is_student中學(xué)號(hào)為95001的學(xué)生姓名改為李楠 update is_student set sname='李楠' where sno='95002' 將下列問(wèn)題用SQL命令表示：
1．查詢(xún)‘IS’系學(xué)生的學(xué)號(hào)、姓名、性別。
SELECT sno,sname,ssex FROM student WHERE sdept='IS';2．查詢(xún)‘IS’系年齡在20歲以下的學(xué)生。
SELECT * FROM student WHERE sdept='IS'AND sage<20;3．查詢(xún)所有不姓‘劉’的學(xué)生的學(xué)號(hào)、姓名、性別。
SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'劉%';4.查詢(xún)student表中學(xué)生的總?cè)藬?shù)。
SELECT COUNT(*)'總?cè)藬?shù)' FROM student;5.查詢(xún)和‘李勇’同性別的所有同學(xué)的姓名。
SELECT sname from student where ssex in(select ssex from student where sname='李勇');6.查詢(xún)和‘李勇’同性別并同系的所有同學(xué)的姓名。
Select sname from student where ssex in(select ssex from student where sname='李勇')and sdept in(select sdept from student where sname='李勇')7.查詢(xún)選修2號(hào)課程的學(xué)生的學(xué)號(hào)。Select sno from sc where cno='2' 8.求3號(hào)課程的平均成績(jī)。
Select avg(grade)from sc where cno=’3’ 9.查詢(xún)選修2號(hào)課程的學(xué)生的最高分。Select max(grade)from sc where cno=’2’
10.按成績(jī)降序排列，輸出‘IS’系學(xué)生選修了2號(hào)課程的學(xué)生的姓名和成績(jī)。
Select sname,grade from student,sc where sdept='IS' and cno='2' and student.sno=sc.sno order by grade desc SQL查詢(xún)分析器下建數(shù)據(jù)庫(kù)的命令代碼： 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%)在查詢(xún)分析器重建立各表的命令代碼： 建立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))實(shí)驗(yàn)二
T-SQL查詢(xún)、存儲(chǔ)過(guò)程、觸發(fā)器、完整性上機(jī)作業(yè)題 第一部分 ：T-SQL程序設(shè)計(jì)
（1）.如果3號(hào)課程的平均成績(jī)?cè)?0分以上，則輸出“3號(hào)課程成績(jī)良好”，否則輸出“3號(hào)成績(jī)一般” declare @avg float set @avg=(select avg(grade)from sc where cno='3')if @avg>80print'3號(hào)課程成績(jī)良好'else print'3號(hào)成績(jī)一般'（2）計(jì)算并輸出95003號(hào)學(xué)生的平均成績(jī)，若無(wú)該生信息，則顯示“該生未選課”,提示信息.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號(hào)學(xué)生平均成績(jī)' print @avg end（3）.如果有成績(jī)?cè)?0分以上的學(xué)生,則顯示他的學(xué)號(hào),課程和成績(jī),否則顯示“沒(méi)有學(xué)生的課程成績(jī)?cè)?0分以上”提示信息
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='沒(méi)有學(xué)生的課程成績(jī)?cè)?0分以上' print @text end ×(4).利用游標(biāo)逐行顯示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ù)計(jì)算全體男生的平均年齡
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個(gè)字符。select substring(Cname,1,2)from Course(7).在一列中顯示student中各元組的學(xué)號(hào)中的年級(jí)，列名顯示為“年級(jí)”；另一列中顯示學(xué)號(hào)中的學(xué)生序列號(hào)，列名顯示為“序號(hào)”。
select substring(Sno,1,2)年級(jí),substring(Sno,3,len(Sno)-1)序號(hào) from Student order by Sno(8).在選課表中顯示學(xué)號(hào)、課程號(hào)，并根據(jù)成績(jī)：0-59顯示“不合格”；60-79顯示“合格”；80-89顯示“良好”；90-100顯示“優(yōu)秀?！?/p>
select Sno as '學(xué)號(hào)',Cno as '課程號(hào)', 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 第二部分 ：存儲(chǔ)過(guò)程
（1）創(chuàng)建一個(gè)為worker表添加職工記錄的存儲(chǔ)過(guò)程Addworker go
if exists(select name from sysobjects where name='Addworker' and type='P')drop procedure Addworker go create proc Addworker @職工號(hào) char(4),@姓名 char(8),@性別 char(2),@出生日期 datetime,@黨員否 char(2),@參加工作 datetime,@部門(mén)號(hào) char(4)as insert into worker(職工號(hào),姓名,性別,出生日期,黨員否,參加工作,部門(mén)號(hào))values(@職工號(hào),@姓名,@性別,@出生日期,@黨員否,@參加工作,@部門(mén)號(hào))go exec Addworker '16','王璐','女','1988-11-20','否','2010-08-21','11'（2）創(chuàng)建一個(gè)存儲(chǔ)過(guò)程Delworker刪除worker表中指定職工號(hào)的記錄 go
if exists(select name from sysobjects where name='Delworker' and type='P')drop procedure Delworker go create procedure Delworker @職工號(hào) char(4)as delete from worker where 職工號(hào)=@職工號(hào)
go exec Delworker '16'（3）顯示存儲(chǔ)過(guò)程Delworker的定義信息。Sp_helptext Delworker（4）刪除存儲(chǔ)過(guò)程Addworker和Delworker。drop procedure Addworker, Delworker（5）創(chuàng)建并執(zhí)行以下存儲(chǔ)過(guò)程：
a.從數(shù)據(jù)庫(kù)表中查詢(xún)，返回學(xué)生學(xué)號(hào)、姓名、課程名、成績(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ù)庫(kù)表中查詢(xún)指定學(xué)號(hào)的學(xué)生學(xué)號(hào)，姓名，該存儲(chǔ)過(guò)程接受與傳遞參數(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)建一個(gè)觸發(fā)器 depart_update , 當(dāng)更改部門(mén)號(hào)時(shí)同步更改 worker表中對(duì)應(yīng)的部門(mén)號(hào)。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.部門(mén)號(hào)=（select 部門(mén)號(hào) from inserted）where worker.部門(mén)號(hào)=(select 部門(mén)號(hào)from deleted)(2)在表worker上創(chuàng)建一個(gè)觸發(fā)器worker_delete,當(dāng)刪除職工記錄時(shí)同步刪除salary表中對(duì)應(yīng)的職工記錄。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.職工號(hào)=(select 職工號(hào) from deleted)(3)刪除觸發(fā)器depart_update(4)刪除觸發(fā)器worker_delete(5)在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)觸發(fā)器，向選課表添加一條紀(jì)錄時(shí)，檢查該紀(jì)錄的學(xué)號(hào)在學(xué)生表中是否存在，檢查該紀(jì)錄的課程號(hào)在課程表中是否存在，若其中有一項(xiàng)為否，則拒絕添加操作，并顯示“違反數(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ù)庫(kù)完整性
1、實(shí)施worker表的“性別”字段默認(rèn)值為“男”的約束 create default default_sex as '男' go sp_bindefault'default_sex','worker.性別'
2、實(shí)施salary表的“工資”字段值在0~9999的約束、create rule salary_rule as @salary='[0~9999]' go sp_bindrule 'salary_rule','salary.工資'
3、實(shí)施depart表的“部門(mén)號(hào)”字段值唯一的非聚集索引的約束
4、為worker表建立外鍵“部門(mén)號(hào)”，參考表depart的“部門(mén)號(hào)”列。
5、建立一個(gè)規(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ù)庫(kù)上機(jī)實(shí)驗(yàn)8實(shí)驗(yàn)報(bào)告
上機(jī)實(shí)驗(yàn)八——完整性約束的實(shí)現(xiàn)
一、實(shí)習(xí)目的：
掌握SQL中實(shí)現(xiàn)數(shù)據(jù)完整性的方法，加深理解關(guān)系數(shù)據(jù)模型的三類(lèi)完整性約束。
二、實(shí)習(xí)準(zhǔn)備：
1.復(fù)習(xí)“完整性約束SQL定義”
2.完成習(xí)題四第10題中的各項(xiàng)操作的SQL語(yǔ)句。
3.了解SQL Server 中實(shí)體完整性、參照完整性和用戶(hù)自定義完整性的實(shí)現(xiàn)手段
三、實(shí)習(xí)內(nèi)容：
1.驗(yàn)證習(xí)題四第10題四個(gè)表結(jié)構(gòu)的SQL語(yǔ)句。
表一：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中提供了那些方法實(shí)現(xiàn)實(shí)體完整性、參照完整體和用戶(hù)自定義完整性
答：實(shí)體完整性：是通過(guò)主碼的定義（PRIMARY KEY）來(lái)實(shí)現(xiàn)的；
參照完整性：是利用外部碼（REFERENCES）的說(shuō)明，以限制相關(guān)表中某些屬性的取值，當(dāng)用戶(hù)違反規(guī)則時(shí)，提供三種：RESTRICT（限制策略）,CASCADE(級(jí)聯(lián)策略),SET NULL(置空策略)；
用戶(hù)自定義完整性：check約束，對(duì)元組的CHECK約束
點(diǎn)擊咨詢(xún) 