SQL SERVER 上機實驗

1、實驗1 數(shù)據庫操作1創(chuàng)建數(shù)據庫：操作1.1：創(chuàng)建一個test數(shù)據庫，其主數(shù)據文件邏輯名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸為無限大，增長速度1MB；數(shù)據庫日志文件邏輯名稱為test_log，物理文件名為test_log.ldf，初始大小為1MB，最大尺寸為5MB，增長速度為10%。參考答案：create database TestON primary(name = test_data,filename = 'd:testtest_data.mdf',size = 5MB,maxsize = unlimited,filegrowth

2、 = 1MB)LOG ON(name = test_log,filename = 'd:testtest_log.ldf',size = 1MB,maxsize = 5MB,filegrowth = 10%)GO2查看數(shù)據庫屬性：操作1.2：使用T-SQL語句查看數(shù)據庫test屬性參考答案：EXEC sp_helpdb test3刪除數(shù)據庫：操作1.3：使用T-SQL語句刪除數(shù)據庫test參考答案：drop database Test實驗2 表操作1創(chuàng)建表：操作2.1：創(chuàng)建學生表：表名：student說明：學生基本信息表屬性列數(shù)據類型長度空值列約束說明st_idnVarChar

3、9Not NullPK學生學號st_nmnVarChar8Not Null學生姓名st_sexnVarChar2Null學生性別st_birthdatetimeNull出生日期st_scoreintNull入學成績st_datedatetimeNull入學日期st_fromnChar20Null學生來源st_dpidnVarChar2Null所在系編號st_mnttinyintNull學生職務參考答案：USE testGOCREATE TABLE student(st_id nVarChar(9) primary key NOT NULL ,st_nm nVarChar(8) NOT NULL

4、 ,st_sex nVarChar(2) NULL ,st_birth datetime NULL ,st_score int NULL ,st_date datetime NULL ,st_ from nVarChar(20)NULL ,st_dpid nVarChar(2) NULL ,st_ mnt tinyint NULL)GO操作2.2：創(chuàng)建課程信息表：表名：couse說明：課程信息表屬性列數(shù)據類型長度空值列約束說明cs_idnVarChar4Not NullPK課程編號cs_nmnVarChar20Not Null課程名稱cs_tmintNull課程學時cs_scintNull課程

5、學分參考答案：USE testGOCREATE TABLE couse(cs_id nVarChar(4) primary key NOT NULL ,cs_nm nVarChar(20) NOT NULL ,cs_tm int NULL ,cs_sc int NULL)GO操作2.3：創(chuàng)建選課表：表名：slt_couse說明：選課表屬性列數(shù)據類型長度空值列約束說明cs_idnVarChar4Not NullFK課程編號st_idnVarChar9Not NullFK學生編號scoreintNull課程成績sltdatedatetimeNull選課日期參考答案：USE testGOCREATE

6、 TABLE couse(cs_id nVarChar(4) NOT NULL ,st_id nVarChar(9) NOT NULL ,score int NULL ,sltdate datetime NULL)GO操作2.4：創(chuàng)建院系信息表：表名：dept說明：院系信息表屬性列數(shù)據類型長度空值列約束說明dp_idnVarChar2Not Null系編號dp_nmnVarChar20Not Null院系名稱dp_drtnVarChar8Null院系主任dt_telnVarChar12Null聯(lián)系電話參考答案：USE testGOCREATE TABLE dept(dp_id nVarChar

7、(2) NOT NULL ,dp_nm nVarChar(20)NOT NULL ,dp_drtnVarChar(8) NULL ,dp_telnVarChar(12)NULL)GO2修改表結構：（1）向表中添加列：操作2.5：為“dept”表添加“dp_count”列（數(shù)據類型為nvarchar，長度為3，允許為空）參考答案：ALTER TABLE dept ADD dp_count nvarchar(3) NULL（2）修改列數(shù)據類型：操作2.6：修改“dept”表的“dp_count”列數(shù)據類型為int參考答案：ALTER TABLE dept ALTER COLUMN dp_count

8、 int NULL（3）刪除表中指定列：操作2.7：刪除“dept”表的“dp_count”列參考答案：ALTER TABLE dept DROP COLUMN dp_count3刪除表操作2.8：刪除“dept”表參考答案：DROP TABLE student4向表中輸入數(shù)據記錄操作2.9：分別向“student”表、“couse”表、“slt_couse”表、“dept”表中輸入數(shù)據記錄實驗3 數(shù)據完整性1空值約束( NULL )操作3.1：將student表中的st_sex列屬性更改為NOT NULL參考答案：ALTER TABLE student ALTER COLUME st_nm

9、nVarChar(8) NOT NULL2默認值約束( DEFAULT )操作3.2：將student表中的st_from列默認值設置為“陜西省”參考答案：ALTER TABLE student ADD DEFAULT '陜西省' FOR st_from3默認值對象操作3.3：創(chuàng)建默認值對象df_today為當前日期，并將其綁定到slt_couse表中的sltdate列，然后取消綁定，最后刪除默認值對象df_today。參考答案：CREATE DEFAULT df_today AS Getdate( )GOEXEC sp_bindefault df_today, 'sl

10、t_couse.sltdate'GOEXEC sp_unbindefault 'slt_couse.sltdate'GODROP DEFAULT df_todayGO4檢查約束( CHECK )操作3.4：將slt_couse表中的score列的檢查約束設置為>=0且<=100參考答案：ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100)5規(guī)則約束對象操作3.5：創(chuàng)建規(guī)則約束對象rl_sex，用于檢查性別的取值僅限于“男”和“女”，并將其綁定到student表中的st_sex列，然后取

11、消綁定，最后刪除規(guī)則約束對象rl_sex。參考答案：CREATE RULE rl_sex AS chksex 男 OR chksex=女或CREATE RULE rl_sex AS chksex IN (男, 女)GOEXEC sp_bindrule rl_sex, 'student.st_sex'GOEXEC sp_unbindrule 'student.st_sex'GODROP RULE rl_sexGO6主鍵操作3.6：將dept表中的dp_id列設置為主鍵參考答案：ALTER TABLE dept ADD PRIMARY KEY (dp_id)7唯一性

12、約束( UNIQUE )操作3.7：將dept表中的dp_nm列設置為唯一性約束參考答案：ALTER TABLE dept ADD UNIQUE (dp_nm)8標識列操作3.8：向slt_couse表中添加標識列id，第1行默認值為1，相鄰兩個標識列間的增量為1參考答案：ALTER TABLE slt_couse ADD id INT IDENTITY(1,1) NOT NULL9外鍵（ FOREIGN KEY ）操作3.9：被參照表為dept，參照表為student參考答案：ALTER TABLE studentADD FOREIGN KEY (st_dpid) REFERENCES de

13、pt(dp_id)實驗4 數(shù)據更新1表中插入數(shù)據操作4.1：向dept表插入一條記錄，系號11，系名自動控制系，系主任為李其余，電話81234567INSERT INTO dept VALUES('11', '自動控制系', '李其余', '81234567')操作4.2：向student表插入一條記錄，學號070201001，姓名為王小五，性別為男，出生日期為1990年9月9日，系號為11，其余字段為NULL或默認值INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dp

14、id)VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )操作4.3：向couse表插入一條記錄，課程號1234，課程名為操作系統(tǒng)，其余字段為NULL或默認值INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系統(tǒng)')操作4.4：向slt_couse表插入一條記錄，課程號1234，學名070201001，其余字段為NULL或默認值INSERT INTO slt_couse(cs_id,

15、 st_id) VALUES ('1234', '070201001')2修改表中數(shù)據操作4.5：修改student表記錄，將王小五的入學成績改為88UPDATE student SET st_score=88 WHERE st_nm='王小五'操作4.6：修改couse表記錄，將所有記錄的學分改為4，學時改為64UPDATE couse SET cs_tm=64, cs_sc=4操作4.7：修改slt_couse表記錄，將課程號為1234，學名為070201001的記錄的成績改為77UPDATE slt_couse SET score=77 W

16、HERE cs_id='1234' AND st_id='070201001'操作4.8:將“軟件學院”學生的成績加5分update slt_couse set score=score+5 where st_id in(select st_id from student where st_dpid in(select st_dpid from dept where dp_nm='軟件學院')3刪除表中數(shù)據操作4.9：刪除slt_couse表記錄，將課程號為1234，學名為070201001的記錄刪除DELETE FROM slt_couse WH

17、ERE cs_id='1234' AND st_id='070201001'操作4.10：刪除couse表記錄，將課程號為1234的記錄刪除DELETE FROM couse WHERE cs_id='1234'操作4.11：將“軟件學院”學生的選課記錄刪除delete slt_cousefrom slt_couse,dept,studentwhere slt_couse.st_id=student.st_id and student.st_dpid=dept.dp_id and dept.dp_nm='軟件學院'實驗5 數(shù)據查詢

18、（1）簡單查詢（1）查詢表中所有的列操作5.1：查詢所有系的信息SELECT * FROM dept（2）查詢表中指定列的信息操作5.2：查詢所有的課程號與課程名稱SELECT cs_id, cs_nm FROM couse（3）在查詢列表中使用列表達式操作5.3：在查詢student表時使用列表達式：入學成績+400SELECT st_id, st_nm, st_score, st_score+400 AS new_scoreFROM student（4）重新命名查詢結果操作5.4：使用AS關鍵字為dept表中屬性指定列名：系號、系名、系主任、聯(lián)系電話SELECT dp_id AS 系號,

19、dp_nm AS 系名, dp_drt AS 系主任, dp_tel AS聯(lián)系電話FROM dept操作5.5：使用"="號為couse表中屬性指定列名：課程號、課程名、學時(=cs_sc*16)、學分SELECT 課程號=cs_id, 課程名=cs_nm, 學分=cs_sc, 學時=cs_sc*16FROM couse（5）增加說明列操作5.6：查詢dept表的系號、系名和系主任，向查詢結果中插入說明列：系號、系名和系主任SELECT '系號:', st_id, '系名:', st_nm, '系主任:', st_drtFRO

20、M dept（6）查詢列表中使用系統(tǒng)函數(shù)操作5.7：顯示所有學生的學號、姓名、性別和入學年份SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入學年份FROM student操作5.8：顯示所有學生學號、姓名、性別和班級(學號前6位)SELECT st_id, st_nm, st_sex, LEFT(st_id, 6) AS 班級FROM student（7）消除查詢結果中的重復項操作5.9：顯示所有學生班級SELECT DISTINCT LEFT(st_id,6) AS 班級 FROM student（8）取得查詢結果的部分行集操作5

21、.10：顯示前5條學生記錄信息SELECT TOP 5 * FROM student操作5.11：顯示前25%條學生記錄信息SELECT TOP 25 PERCENT * FROM student操作5.12：顯示前n條學生記錄信息，n為局部變量DECLARE n INTSET n = 4SELECT TOP n * FROM student實驗6 數(shù)據查詢（2）條件查詢1使用關系表達式表示查詢條件操作6.1：查詢dept表中系號為11的院系信息SELECT * FROM dept WHERE dp_id = '11'操作6.2：查詢student表中11系的學生學號、姓名、性

22、別和所在系編號SELECT st_id, st_nm, st_sex, st_dpid FROM studentWHERE st_dpid = '11'操作6.3：查詢student表中2008年及以后入學的學生信息SELECT * FROM studentWHERE DATEPART( yy, st_date )>= 2008操作6.4：在查詢student表080808班學生的學號、姓名、性別和入學成績SELECT st_id, st_nm, st_sex, st_score FROM studentWHERE Left(st_id,6)='080808

23、9;2使用邏輯表達式表示查詢條件操作6.5：查詢student表中非11系的學生信息SELECT * FROM student WHERE NOT (st_dpid = '11')操作6.6：查詢選修了1002號課程且成績在60以下的學生學號SELECT st_id FROM slt_couseWHERE (cs_id='1002') AND (score<60)操作6.7：查詢2007年入學的11系所有男生信息SELECT * FROM studentWHERE DATEPART(yy,st_date)=2007 AND st_dpid='11&

24、#39; AND st_sex='男'操作6.8：查詢11系和12系的學生信息SELECT * FROM studentWHERE st_dpid='11' OR st_dpid='12'操作6.9：查詢11系和12系所有2007年入學的學生信息SELECT * FROM studentWHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=20073使用LIKE關鍵字進行模糊查詢操作6.10：查詢所有“計算機”開頭的課程信息SELECT * FR

25、OM couse WHERE cs_nm LIKE '計算機%'操作6.11：查詢所有由三個字組成的“王”姓學生信息SELECT * FROM student WHERE st_nm LIKE '王_'操作6.12：查詢所有課程名中包含“信息”的課程信息SELECT * FROM couse WHERE cs_nm LIKE '%信息%'操作6.13：查詢學生姓名介于王姓到張姓的信息SELECT * FROM studentWHERE st_nm LIKE '王-張%'4使用BetweenAnd關鍵字進行查詢操作6.14：查詢在1

26、989.7.1到1990.6.30之間出生的學生信息SELECT st_id, st_nm, st_sex, st_birth FROM studentWHERE st_birth BETWEEN '1981.7.1' AND '1999.6.30'操作6.15：查詢選修了1001號課程且成績在60到80之間的學生選課信息SELECT * FROM slt_couseWHERE cs_id='1001' AND (score BETWEEN 60 AND 80)5使用IN關鍵字進行查詢操作6.16：查詢11系、12系、13系的學生信息SELECT

27、 * FROM student WHERE st_dpid IN ('11', '12','13')操作6.17：查詢所有張，王，李，趙姓的學生的學號、姓名、性別SELECT st_id, st_nm, st_sex FROM studentWHERE Left(st_nm,1) IN ('張','王','李','趙')6使用NOT NULL關鍵字進行查詢操作6.18：查詢所有生源為非空的學生信息SELECT * FROM student WHERE st_from IS NOT N

28、ULL操作6.19：查詢選修了1001號課程且成績?yōu)榭盏膶W生選課信息SELECT * FROM slt_couse WHERE cs_id='1001' AND score IS NULL實驗7 數(shù)據查詢（3）查詢排序與查詢結果存儲操作7.1：查詢課程信息，按課程名稱降序排序SELECT * FROM couse ORDER BY cs_nm DESC操作7.2：查詢選修了1001號課程成績非空的學生學號和成績，并按成績降序排序SELECT st_id, score FROM slt_corseWHERE cs_id='1001' AND score IS NO

29、T NULLORDER BY score DESC操作7.3：查詢11系學生學號、姓名和年齡，按年齡升序排序SELECT st_id,st_nm,DATEPART(yy,GETDATE( )-DATEPART(yy,st_birth) AS ageFROM studentORDER BY age ASC操作7.4：查詢學生信息，按姓名升序排序，再按系號降序排序SELECT * FROM student ORDER BY st_nm, st_dpid DESC操作7.5：創(chuàng)建學生表副本student01，僅保留學生學號、姓名和性別SELECT st_id, st_nm, st_sex INTO

30、student01 FROM student操作7.6：查詢陜西籍學生，將結果保存在新表st_shanxiSELECT * INTO st_shanxiFROM studentWHERE st_from='陜西省'操作7.7：查詢選修了1001號課程學生的選課信息，按學號升序排序，將結果保存在新表slt1001SELECT * INTO slt1001 FROM slt_corseWHERE cs_id='1001' ORDER BY st_id操作7.8：用局部變量stage保存學生張三的年齡DECLARE stage intSELECT stage = DA

31、TEPART(yy,GETDATE()-DATEPART(yy,st_birth)FROM studentWHERE st_nm='張三'操作7.9：用局部變量name和stscore保存070101班按學號排序后最后一個學生的姓名和入學成績DECLARE name nVarChar(8), stscore intSELECT name = st_nm, stscore = st_scoreFROM studentWHERE LEFT(st_id,6)='070101'ORDER BY st_id實驗8 數(shù)據查詢（4）查詢統(tǒng)計與匯總操作8.1：查詢課程總數(shù)SEL

32、ECT COUNT( * ) FROM couse操作8.2：查詢選修1001號課程的學生人數(shù)SELECT COUNT(st_id)FROM slt_couseWhere cs_id = '1001'操作8.3：查詢被選修課程的數(shù)量SELECT COUNT( DISTINCT cs_id ) FROM slt_couse操作8.4：查詢選修070101班學生的平均入學成績SELECT AVG(st_score)FROM studentWHERE LEFT(st_id,6)='070101'操作8.5：查詢070101001號學生選修課程的數(shù)量、總分以及平均分SE

33、LECT COUNT(cs_id) AS 課程數(shù)量,SUM(score) AS 總分,AVG(score) AS 平均分FROM slt_couseWHERE st_id='070101001'操作8.6：查詢選修1001號課程的學生人數(shù)、最高分、最低分和平均分SELECT COUNT(*) AS 學生人數(shù), MAX(score) AS 最高分, MIN(score) AS 最低分, AVG (score) AS 平均分FROM slt_couseWHERE cs_id='1001'操作8.7：求各個課程號和相應的選課人數(shù)SELECT cs_id, COUNT(

34、st_id)FROM slt_couse GROUP BY cs_id操作8.8：統(tǒng)計各班人數(shù)SELECT LEFT(st_id,6) AS 班級, COUNT(st_id) AS 人數(shù)FROM studentGROUP BY LEFT(st_id,6)操作8.9：依次按班級、系號對學生進行分類統(tǒng)計人數(shù)、入學平均分SELECT st_dpid AS 系號, LEFT(st_id,6) AS 班級, COUNT(st_nm) AS 人數(shù), AVG(st_score) AS 均分FROM student GROUP BY LEFT(st_id,6), st_dpid操作8.10：查詢選修了均分在7

35、5以上的課程號及均分SELECT cs_id AS 課程編號, AVG(score) AS 均分FROM slt_couseGROUP BY cs_id HAVING AVG(score)>75操作8.11：查詢選修了2門以上課程的學生學號SELECT st_id FROM slt_couseGROUP BY st_id HAVING COUNT(*)>2操作8.12：明細匯總年齡<20的學生，并匯總學生數(shù)量、平均年齡SELECT st_nm,DATEPART(yy,GETDATE( )-DATEPART(yy,st_birth) AS 年齡FROM studentWHERE

36、 DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)<20COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)操作8.13：按班級明細匯總成績<85分的學生，匯總學生數(shù)、均分SELECT st_nm, LEFT(st_id,6) AS 班級, st_scoreFROM studentWHERE st_score<85ORDER BY 班級COMPUTE COUNT(st_nm), AVG(st_score) BY 班級實驗9 數(shù)據查詢（5）連接查詢操作9.1

37、：用SQL Server形式連接查詢學生學號、姓名、性別及其所選課程編號SELECT a.st_id, st_nm, st_sex, cs_idFROM student a, slt_couse bWHERE a.st_id = b.st_idORDER BY a.st_id操作9.2：用ANSI形式連接查詢學生學號、姓名、性別及其所選課程編號SELECT a.st_id, st_nm, st_sex, cs_idFROM student a INNER JOIN slt_couse bON a.st_id = b.st_idORDER BY a.st_id操作9.3：用SQL Server形

38、式連接查詢學生學號、姓名及其所選課程名稱及成績SELECT a.st_id, st_nm, cs_nm, scoreFROM student a, slt_couse b, couse cWHERE a.st_id = b.st_id AND b.cs_id = c.cs_idORDER BY a.st_id操作9.4：用ANSI形式連接查詢學生學號、姓名及其所選課程名稱及成績SELECT a.st_id, st_nm, cs_nm, scoreFROM slt_couse a INNER JOIN student b ON a.st_id = b.st_idINNER JOIN couse

39、c ON a.cs_id = c.cs_idORDER BY b.st_id操作9.5：查詢選修了1002課程的學生學號、姓名及1001課程成績SELECT a.st_id, st_nm, scoreFROM student a，slt_couse bWHERE a.st_id = b.st_id AND b.cs_id = '1002'ORDER BY b.st_id操作9.6：查詢選修了“數(shù)據結構”課程的學生學號、姓名及課程成績SELECT a.st_id, st_nm, scoreFROM student a, slt_couse b, couse cWHERE a.st

40、_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm='數(shù)據結構'ORDER BY a.st_id操作9.7：用左外連接查詢沒有選修任何課程的學生學號、姓名SELECT a.st_id, st_nm, scoreFROM student a LEFT OUTER JOIN slt_couse b ON a.st_id = b.st_idWHERE b.cs_id IS NULLORDER BY b.st_id操作9.8：用右外連接查詢選修各個課程的學生學號SELECT b.cs_id, a.st_idFROM slt_couse a Right

41、 OUTER JOIN couse b ON a.cs_id = b.cs_idORDER BY b.cs_id實驗10 數(shù)據查詢（6）子查詢操作10.1：用子查詢對各班人數(shù)進行查詢（新增列）SELECT DISTINCT LEFT(a.st_id,6) AS 班級, 人數(shù) = ( SELECT COUNT(st_id) FROM student b WHERE LEFT(a.st_id,6) = LEFT(b.st_id,6)FROM student a ORDER BY LEFT(a.st_id,6) ASC操作10.2：用子查詢對各課程的選課人數(shù)進行查詢（新增列）SELECT DISTI

42、NCT a.cs_id, 人數(shù) = ( SELECT COUNT(st_id) FROM slt_couse b WHERE a.cs_id = b.cs_id)FROM slt_couse a ORDER BY a.cs_id ASC操作10.3：查詢選修了1002課程成績不及格的學生的學號、姓名和性別，并按姓名升序排序通過子查詢實現(xiàn)：使用IN關鍵字SELECT st_id, st_nm, st_sex FROM studentWHERE st_id IN( SELECT st_id FROM slt_couse WHERE cs_id='1002' AND score &l

43、t; 60)ORDER BY st_nm通過子查詢實現(xiàn)：使用比較運算符SELECT st_id, st_nm, st_sexFROM student aWHERE ( SELECT score FROM slt_couse b WHERE a.st_id = b.st_id AND cs_id = '1002' ) < 60ORDER BY st_nm操作10.4：查詢“東方紅”同學所在班的學生信息，并按姓名降序排序通過子查詢實現(xiàn)：IN運算符SELECT st_id, st_nm, st_sex FROM student WHERE LEFT(st_dpid,6) IN(

44、SELECT Left(st_dpid,6) FROM student WHERE st_nm='東方紅')ORDER BY st_nm DESC通過自連接查詢實現(xiàn)：JOINSELECT s1.st_id, s1.st_nm, s1.st_sex FROM student s1 JOIN student s2 ON LEFT(s1.st_dpid,6) = LEFT(s2.st_dpid,6)WHERE s2.st_nm='東方紅'ORDER BY s2.st_nm操作10.5：查詢其它班比070511班某一學生的1002號課程成績高的學生信息（ANY/ALL）

45、SELECT * FROM slt_couseWHERE score > ANY(SELECT score FROM slt_couse WHERE cs_id = '1002' AND LEFT(st_id,6)='070511')AND LEFT(st_id,6) <> '070511' AND cs_id = '1002'操作10.6：查詢其它班比070511班任一學生的1002號課程成績高的學生信息（ANY/ALL）SELECT * FROM slt_couseWHERE score > ALL(S

46、ELECT score FROM slt_couse WHERE cs_id = '1002' AND LEFT(st_id,6)='070511')AND LEFT(st_id,6) <> '070511' AND cs_id = '1002'操作10.7：查詢大于等于60分且且比1003課程平均成績低的學生課程信息（BetweenAnd）SELECT * FROM slt_couse aWHERE a.score BETWEEN 60 AND ( SELECT AVG(b.score) FROM slt_couse

47、 bWHERE b.cs_id='1003' )操作10.8：查詢系主任為“趙虎”的系的所有學生信息通過子查詢實現(xiàn)：IN運算符SELECT * FROM student WHERE EXISTS( SELECT * FROM dept WHERE st_dpid = dp_id AND dp_drt='趙虎' )通過子查詢實現(xiàn)：=運算符SELECT * FROM student WHERE st_dpid =( SELECT dp_id FROM dept WHERE dp_drt='趙虎' )實驗11 數(shù)據查詢（7）數(shù)據更新與子查詢操作11.1：