實(shí)驗(yàn)二 數(shù)據(jù)的查詢、更新.doc

_實(shí)驗(yàn)二 數(shù)據(jù)的查詢、更新 一、實(shí)驗(yàn)?zāi)康?、掌握用戶自定義數(shù)據(jù)類型的方法2、掌握用T-SQL語句進(jìn)行數(shù)據(jù)的插入、修改、刪除的方法3、熟練掌握SELECT語句，能夠運(yùn)用該語句完成各種查詢二、實(shí)驗(yàn)要求1、實(shí)驗(yàn)前做好上機(jī)實(shí)驗(yàn)的準(zhǔn)備，針對實(shí)驗(yàn)內(nèi)容，認(rèn)真復(fù)習(xí)與本次實(shí)驗(yàn)有關(guān)的知識，完成實(shí)驗(yàn)內(nèi)容的預(yù)習(xí)準(zhǔn)備工作；2、能認(rèn)真獨(dú)立完成實(shí)訓(xùn)內(nèi)容；3、實(shí)驗(yàn)后做好實(shí)驗(yàn)總結(jié)，根據(jù)實(shí)驗(yàn)情況完成總結(jié)報(bào)告。三、實(shí)驗(yàn)內(nèi)容1、用T-SQL語句，創(chuàng)建一用戶自定義數(shù)據(jù)類型：名稱為“char20”，數(shù)據(jù)類型為varchar，長度為20，允許為空。sp_addtype char20, varchar(20),null提示：sp_addtype typename=用戶自定義類型的名字 , phystype=系統(tǒng)類型名 , nulltype= not null | null , owner= 擁有該類型的用戶名 例：自定義一個(gè)名為address的類型，其所屬系統(tǒng)類型為varchar，長度為80，不能為空。sp_addtype address, varchar(80), not null 2、用T-SQL語句，建立一個(gè)“學(xué)生課程數(shù)據(jù)庫”，在此基礎(chǔ)上建立該數(shù)據(jù)庫包含的學(xué)生表，課程表，學(xué)生選修表，并向各表插入如下相應(yīng)的數(shù)據(jù)。create database 學(xué)生課程數(shù)據(jù)庫學(xué)生表:Student(Sno,Sname,Ssex,Sage,Sdept) 其中Sno 為主鍵、Ssex取值為男或女、Sage在15到30之間:SnoSnameSsexSageSdept95001李敏勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男18IScreate table student(sno int primary key, sname char(10), ssex char(2) constraint ssex_ch check(ssex in(男,女), sage int constraint sage_ch check(sage=15 and sage=30), sdept char(10) ) insert into studentvalues(95001,李敏勇,男,20,CS)insert into studentvalues(95002,劉晨,女,19,IS)insert into studentvalues(95003,王敏,女,18,MA)insert into studentvalues(95004,張立,男,18,IS)課程表:Course(Cno,Cname,Cpno,Credeit,remarks) 其中Cno為主鍵、Teacher的類型為char20即為用戶定義的數(shù)據(jù)類型;CnoCnameCpnoCredit Teacher1數(shù)據(jù)庫54王芳2數(shù)學(xué)NULL2劉新3信息系統(tǒng)14劉新4操作系統(tǒng)63高升5數(shù)據(jù)結(jié)構(gòu)74宋明6數(shù)據(jù)處理NULL2張彬7Pascal語言64李磊create table course(cno int primary key, cname char(15), cpno int null, credit int,teacher char20 ) insert into coursevalues(1,數(shù)據(jù)庫,5,4,王芳)insert into coursevalues(2,數(shù)學(xué),NULL,2,劉新)insert into coursevalues(3,信息系統(tǒng),1,4,劉新)insert into coursevalues(4,操作系統(tǒng),6,3,高升)insert into coursevalues(5,數(shù)據(jù)結(jié)構(gòu),7,4,宋明)insert into coursevalues(6,數(shù)據(jù)處理,NULL,2,張彬)insert into coursevalues(7,PASCAL語言,6,4,李磊)學(xué)生選修表:SC(Sno,Cno,Grade) 其中Sno,Cno為主鍵同時(shí)又為外鍵、Grade值在0到100;SnoCnoGrade950011929500128595001388950022909500325595004270create table sc(sno int, cno int, grade int constraint grade_ch check(grade between 0 and 100),primary key(sno,cno),constraint fk_sno foreign key (sno) references student(sno),constraint fk_cno foreign key (cno) references course(cno), ) insert into scvalues(95001,1,92)insert into scvalues(95001,2,85)insert into scvalues(95001,3,88)insert into scvalues(95002,2,90)insert into scvalues(95003,2,55)insert into scvalues(95004,2,70)3、用T-SQL語句，修改上面所建學(xué)生課程數(shù)據(jù)庫中數(shù)據(jù):1) 向?qū)W生表:Student中加入一條記錄：(95030,謝非,男,22,CS)并保存insert into studentvalues(95030,謝非,男,22,CS)2) 將李敏勇的數(shù)據(jù)庫的成績改為98分update sc set grade=98where o=(select o from course where ame=數(shù)據(jù)庫 and sno=(select sno from student where sname=李敏勇 ) )3) 刪除學(xué)生表Student中謝非的記錄并保存delete from studentwhere sname=謝非4) 能不能從Student表中刪除李敏勇學(xué)生的記錄,為什么?能不能刪除王敏, 張立兩個(gè)學(xué)生的記錄?不能， sc表中列sno是外碼，參照student表的sno列。4、用T-SQL語句，完成下面簡單的查詢1）查詢?nèi)w學(xué)生的學(xué)號、姓名及年齡.use 學(xué)生課程數(shù)據(jù)庫select *from student2）查詢?nèi)w學(xué)生的姓名, 年齡及所在系（要用小寫字母表示系名，并用“系名”來表示列名）。select sname,sage, lower(sdept) 系名from student3）查詢選修了課程的學(xué)生學(xué)號select distinct sno from sc4）查詢信息系全體學(xué)生的姓名select snamefrom studentwhere sdept=IS5）查詢所有年齡在20歲以下的學(xué)生姓名及其年齡select sname,sagefrom studentwhere sage=70order by grade desc16)查詢每個(gè)學(xué)生的學(xué)號、課程號及分?jǐn)?shù)，同時(shí)統(tǒng)計(jì)每個(gè)學(xué)生的總分select sno,cno,gradefrom scorder by snocompute sum(grade) by sno17)查詢每個(gè)學(xué)生的各科分?jǐn)?shù)、最高分、最低分、總分、平均分select sno,cno,gradefrom scorder by snocompute max(grade),min(grade),sum(grade),avg(grade) by sno5、用T-SQL語句完成下面的查詢1)查詢學(xué)生的總?cè)藬?shù)select COUNT(sno) 人數(shù)from student2)查詢選修了課程的學(xué)生人數(shù)select COUNT(distinct sno) 選課人數(shù)from sc3)計(jì)算選2號課程的學(xué)生平均成績select AVG(grade)平均分from scwhere cno=24)查詢選修2號課程的學(xué)生最高分?jǐn)?shù)select MAX(grade) 最高分from scwhere cno=25)求各個(gè)課程號及相應(yīng)的選課人數(shù)select cno,COUNT(sno) 人數(shù)from scgroup by cno6)查詢選修了2門以上的課程的學(xué)生學(xué)號select snofrom scgroup by sno having (COUNT(cno)2)7)查詢每個(gè)學(xué)生及其選修課程的情況select student.sno,o,cnamefrom student,sc,coursewhere student.sno=sc.sno and o=o8)查詢每一門課的間接先修課(即先修課的先修課)select o,c2.cpnofrom course c1,course c2where o=o9)查詢選修2號課程且成績在90分以上（包括90分）的所有學(xué)生。select student.sno,snamefrom studentwhere student.sno in(select sc.sno from sc where cno=2 and grade=90)6. 用T-SQL語句完成下面的查詢 1)查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生select sno,snamefrom studentwhere sname劉晨 and sdept=(select sdept from student where sname=劉晨)2)查詢選修了課程名為“數(shù)學(xué)”的學(xué)生學(xué)號和姓名select sno,snamefrom studentwhere sno in(select sno from sc where cno =(select cno from course where cname=數(shù)學(xué) )3)查詢其它系中比信息系中某一學(xué)生年齡小的學(xué)生姓名和年齡select sname,sagefrom studentwhere sdeptIS and sage any(select sagefrom student where sdept=IS)4)查詢其它系中比計(jì)算機(jī)系所有學(xué)生年齡都小的學(xué)生姓名及年齡select sname,sagefrom studentwhere sdeptIS and sage(select sno from student where sname like劉%)and sage(select avg(sage)from studentwhere ssex=女)and ssex=男9)求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。select sname,sagefrom studentwhere ssex=男 and sageall(select sage from student where ssex=女)10)檢索每一門課程成績都大于等于80分的學(xué)生學(xué)號、姓名和性別，并把檢索到的值送往另一個(gè)已存在的基本表S（SNO,SNAME,SEX）。select sno,sname,ssexinto sfrom studentwhere sno in(select sno from sc where grade=80)11)把選課數(shù)學(xué)課不及格的成績?nèi)臑榭罩怠pdate scset grade=where sno in(select sno from sc where grade60) and cno=(select cno from course where cname=數(shù)學(xué))12)把王同學(xué)的選課信息全部刪去。delete from scwhere sno = (select sno from student where sname like 王%)13)把低于總平均成績的男同學(xué)成績提高5。update scset grade=grade*1.05where grade (select AVG(grade) from sc) and cno in(select o from student,sc where ssex = 男 and student.sno = sc.sno)14)檢索沒有選修1課程的學(xué)生學(xué)號和姓名select sno,snamefrom studentwhere sno not in(select sc.sno from sc,student where cno=1 and student.sno=sc.sno)15)檢索至少有一門課程超過學(xué)生95001一門成績的學(xué)生學(xué)號select distinct sc.snofrom sc,studentwhere sc.sno95001and sc.sno=student.snoand gradeany(select grade from sc where sno=95001)16)向?qū)W生選修課程表中插入元組“學(xué)生95003選修課程1”。insert into sc(sno,cno)values(95003,1)17)求出女同學(xué)的每一年齡組（超過10人）有多少人？要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同的按年齡降序排列。select sage,COUNT(sno) 人數(shù)from studentwhere sse