SQL-SERVER-上機(jī)實驗.doc

數(shù)據(jù)庫原理與應(yīng)用（SQL Server）練習(xí)題 - 14 - 實驗1 數(shù)據(jù)庫操作1創(chuàng)建數(shù)據(jù)庫：操作1.1：創(chuàng)建一個test數(shù)據(jù)庫，其主數(shù)據(jù)文件邏輯名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸為無限大，增長速度1MB；數(shù)據(jù)庫日志文件邏輯名稱為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 = 1MB)LOG ON(name = test_log,filename = d:testtest_log.ldf,size = 1MB,maxsize = 5MB,filegrowth = 10%)GO2查看數(shù)據(jù)庫屬性：操作1.2：使用T-SQL語句查看數(shù)據(jù)庫test屬性參考答案：EXEC sp_helpdb test3刪除數(shù)據(jù)庫：操作1.3：使用T-SQL語句刪除數(shù)據(jù)庫test參考答案：drop database Test實驗2 表操作1創(chuàng)建表：操作2.1：創(chuàng)建學(xué)生表：表名：student說明：學(xué)生基本信息表屬性列數(shù)據(jù)類型長度空值列約束說明st_idnVarChar9Not NullPK學(xué)生學(xué)號st_nmnVarChar8Not Null學(xué)生姓名st_sexnVarChar2Null學(xué)生性別st_birthdatetimeNull出生日期st_scoreintNull入學(xué)成績st_datedatetimeNull入學(xué)日期st_fromnChar20Null學(xué)生來源st_dpidnVarChar2Null所在系編號st_mnttinyintNull學(xué)生職務(wù)參考答案：USE testGOCREATE TABLE student(st_id nVarChar(9) primary key NOT NULL ,st_nm nVarChar(8) NOT NULL ,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ù)據(jù)類型長度空值列約束說明cs_idnVarChar4Not NullPK課程編號cs_nmnVarChar20Not Null課程名稱cs_tmintNull課程學(xué)時cs_scintNull課程學(xué)分參考答案：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ù)據(jù)類型長度空值列約束說明cs_idnVarChar4Not NullFK課程編號st_idnVarChar9Not NullFK學(xué)生編號scoreintNull課程成績sltdatedatetimeNull選課日期參考答案：USE testGOCREATE 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ù)據(jù)類型長度空值列約束說明dp_idnVarChar2Not Null系編號dp_nmnVarChar20Not Null院系名稱dp_drtnVarChar8Null院系主任dt_telnVarChar12Null聯(lián)系電話參考答案：USE testGOCREATE TABLE dept(dp_id nVarChar(2) NOT NULL ,dp_nm nVarChar(20)NOT NULL ,dp_drtnVarChar(8) NULL ,dp_telnVarChar(12)NULL)GO2修改表結(jié)構(gòu)：（1）向表中添加列：操作2.5：為“dept”表添加“dp_count”列（數(shù)據(jù)類型為nvarchar，長度為3，允許為空）參考答案：ALTER TABLE dept ADD dp_count nvarchar(3) NULL（2）修改列數(shù)據(jù)類型：操作2.6：修改“dept”表的“dp_count”列數(shù)據(jù)類型為int參考答案：ALTER TABLE dept ALTER COLUMN dp_count int NULL（3）刪除表中指定列：操作2.7：刪除“dept”表的“dp_count”列參考答案：ALTER TABLE dept DROP COLUMN dp_count3刪除表操作2.8：刪除“dept”表參考答案：DROP TABLE student4向表中輸入數(shù)據(jù)記錄操作2.9：分別向“student”表、“couse”表、“slt_couse”表、“dept”表中輸入數(shù)據(jù)記錄實驗3 數(shù)據(jù)完整性1空值約束( NULL )操作3.1：將student表中的st_sex列屬性更改為NOT NULL參考答案：ALTER TABLE student ALTER COLUME st_nm nVarChar(8) NOT NULL2默認(rèn)值約束( DEFAULT )操作3.2：將student表中的st_from列默認(rèn)值設(shè)置為“陜西省”參考答案：ALTER TABLE student ADD DEFAULT 陜西省 FOR st_from3默認(rèn)值對象操作3.3：創(chuàng)建默認(rèn)值對象df_today為當(dāng)前日期，并將其綁定到slt_couse表中的sltdate列，然后取消綁定，最后刪除默認(rèn)值對象df_today。參考答案：CREATE DEFAULT df_today AS Getdate( )GOEXEC sp_bindefault df_today, slt_couse.sltdateGOEXEC sp_unbindefault slt_couse.sltdateGODROP DEFAULT df_todayGO4檢查約束( CHECK )操作3.4：將slt_couse表中的score列的檢查約束設(shè)置為=0且=0 AND score= 2008操作6.4：在查詢student表080808班學(xué)生的學(xué)號、姓名、性別和入學(xué)成績SELECT st_id, st_nm, st_sex, st_score FROM studentWHERE Left(st_id,6)=0808082使用邏輯表達(dá)式表示查詢條件操作6.5：查詢student表中非11系的學(xué)生信息SELECT * FROM student WHERE NOT (st_dpid = 11)操作6.6：查詢選修了1002號課程且成績在60以下的學(xué)生學(xué)號SELECT st_id FROM slt_couseWHERE (cs_id=1002) AND (score75操作8.11：查詢選修了2門以上課程的學(xué)生學(xué)號SELECT st_id FROM slt_couseGROUP BY st_id HAVING COUNT(*)2操作8.12：明細(xì)匯總年齡20的學(xué)生，并匯總學(xué)生數(shù)量、平均年齡SELECT st_nm,DATEPART(yy,GETDATE( )-DATEPART(yy,st_birth) AS 年齡FROM studentWHERE DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)20COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)操作8.13：按班級明細(xì)匯總成績85分的學(xué)生，匯總學(xué)生數(shù)、均分SELECT st_nm, LEFT(st_id,6) AS 班級, st_scoreFROM studentWHERE st_score85ORDER BY 班級COMPUTE COUNT(st_nm), AVG(st_score) BY 班級實驗9 數(shù)據(jù)查詢（5）連接查詢操作9.1：用SQL Server形式連接查詢學(xué)生學(xué)號、姓名、性別及其所選課程編號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形式連接查詢學(xué)生學(xué)號、姓名、性別及其所選課程編號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形式連接查詢學(xué)生學(xué)號、姓名及其所選課程名稱及成績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形式連接查詢學(xué)生學(xué)號、姓名及其所選課程名稱及成績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 c ON a.cs_id = c.cs_idORDER BY b.st_id操作9.5：查詢選修了1002課程的學(xué)生學(xué)號、姓名及1001課程成績SELECT a.st_id, st_nm, scoreFROM student a，slt_couse bWHERE a.st_id = b.st_id AND b.cs_id = 1002ORDER BY b.st_id操作9.6：查詢選修了“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生學(xué)號、姓名及課程成績SELECT a.st_id, st_nm, scoreFROM student a, slt_couse b, couse cWHERE a.st_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm=數(shù)據(jù)結(jié)構(gòu)ORDER BY a.st_id操作9.7：用左外連接查詢沒有選修任何課程的學(xué)生學(xué)號、姓名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：用右外連接查詢選修各個課程的學(xué)生學(xué)號SELECT b.cs_id, a.st_idFROM slt_couse a Right OUTER JOIN couse b ON a.cs_id = b.cs_idORDER BY b.cs_id實驗10 數(shù)據(jù)查詢（6）子查詢操作10.1：用子查詢對各班人數(shù)進(jìn)行查詢（新增列）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ù)進(jìn)行查詢（新增列）SELECT DISTINCT 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課程成績不及格的學(xué)生的學(xué)號、姓名和性別，并按姓名升序排序通過子查詢實現(xiàn)：使用IN關(guān)鍵字SELECT st_id, st_nm, st_sex FROM studentWHERE st_id IN( SELECT st_id FROM slt_couse WHERE cs_id=1002 AND score 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 ) 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班任一學(xué)生的1002號課程成績高的學(xué)生信息（ANY/ALL）SELECT * FROM slt_couseWHERE score ALL(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.7：查詢大于等于60分且且比1003課程平均成績低的學(xué)生課程信息（BetweenAnd）SELECT * FROM slt_couse aWHERE a.score BETWEEN 60 AND ( SELECT AVG(b.score) FROM slt_couse bWHERE b.cs_id=1003 )操作10.8：查詢系主任為“趙虎”的系的所有學(xué)生信息通過子查詢實現(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ù)據(jù)查詢（7）數(shù)據(jù)更新與子查詢操作11.1：將070511班所有學(xué)生信息插入到表student01(st_id,st_nm,st_sex)INSERT INTO student01SELECT st_id,st_nm,st_sex FROM studentWHERE LEFT(st_id,6)=070511操作11.2：生成1002號課程的成績單student02(st_id,st_nm, score)INSERT INTO student02SELECT a.st_id,st_nm,score FROM student a, slt_couse bWHERE a.st_id=b.st_id AND cs_id=1002操作11.3：將有不及格成績的學(xué)生的st_mnt值更改為3UPDATE student SET st_mnt=3WHERE st_id IN( SELECT DISTINCT st_id FROM slt_couse WHERE score 60)操作11.4：將沒有被選修的課程的學(xué)分更改為0UPDATE couse SET cs_sc=0WHERE cs_id NOT IN( SELECT DISTINCT cs_id