数据库查询资料.docx
- 文档编号:10799094
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:23
- 大小:24.83KB
数据库查询资料.docx
《数据库查询资料.docx》由会员分享,可在线阅读,更多相关《数据库查询资料.docx(23页珍藏版)》请在冰豆网上搜索。
数据库查询资料
一、视图的创建:
1.创建计算机科学系学生信息的视图C_student
usexsgl
go
createviewc_student
as
selectsno,sname,ssex,birth,depart,dnamefromstudentsjoindepartmentdons.depart=d.deno
wheredname='计算机科学系'
2.创建计算机科学系选修了ZB171401号课程的学生的视图C_course:
usexsgl
go
createviewc_course
as
selects.sno,sname,ssex,birth,depart,dname,cnofromstudentsjoindepartmentdons.depart=d.deno
joinsconsc.sno=s.sno
wheredname='计算机科学系'andcno='ZB171401'
3.建立计算机科学系选修了ZB171401号课程且成绩在90分以上的学生的视图:
usexsgl
go
createviewc_course
as
selects.sno,sname,ssex,birth,depart,dname,cno,gradefromstudentsjoindepartmentdons.depart=d.deno
joinsconsc.sno=s.sno
wheredname='计算机科学系'andcno='ZB171401'andgrade>90
4.创建一个反映学生出生年份的视图:
usexsgl
go
createviews_year
as
selectsno,sname,datepart(yyyy,birth)出生年份fromstudent
5.将所有女生的记录定义为一个视图:
usexsgl
go
createviews_women
as
select*fromstudentwheressex='女'
6.将所有学生的学号和他的平均成绩定义为一个视图S_G:
usexsgl
go
createviewS_G
as
selectS.sno,avg(grade)平均分fromstudentSjoinsconsc.sno=s.sno
groupbyS.sno
二、视图结构的修改:
6.将视图C_student修改为计算机科学系的所有女生的视图
usexsgl
go
ALTERviewc_student
as
selectsno,sname,ssex,birth,depart,dnamefromstudentsjoindepartmentdons.depart=d.deno
wheredname='计算机科学系'ANDSSEX='女'
三、查询视图
7.在计算机科学系的学生视图c_student中查询年龄小于22岁的学生:
usexsgl
go
selectdatediff(yy,birth,getdate())as年龄
fromC_student
wheredatediff(yy,birth,getdate())<22
8.查询计算机科学系选修了ZB171401号课程的学生:
usexsgl
go
SELECT*fromc_course
9.在视图S_G中查询平均成绩在90分以上的学生的学号和平均成绩:
select*
fromS_G
where平均分>90
四、更新视图:
10.将计算机科学系学生视图C_student中学号为”061713101”的学生姓名改为”刘辰”:
usexsgl
go
updateC_student
setsname='刘辰'
wheresno='061713101'
比较:
updateC_Studentsetsname=’刘辰’wheresno=’091714150’此语句不能实现数据的更新.
(为什么呢?
因为在视图中并没有”091714150”这条记录)
11.向计算机科学系学生视图C_Student中插入一个新的学生记录,学号为091714145,姓名为”赵新”,出生日期为1991-1-2:
此操作能成功吗?
不能成功,格式不匹配,INSERT语句与FOREIGNKEY约束"FK_Student_Department"冲突。
该冲突发生于数据库"xsgl",表"dbo.Department",column'Deno'。
12.删除计算机科学系学生视图C_Student中学号为091714145的学生的记录:
usexsgl
go
deletefromc_student
wheresno='091714145'
五、删除视图:
13.删除视图S_G:
Usexsgl
go
dropviews_g
思考:
1.创建所有学生的基本信息和选课信息的视图
Selectviews_scasselectstudent.*cno,gradefromstudent,scwherestudent.sno=sc.sno
2.基于上述视图查询各系学生各门功课的平均成绩.
Selectsdept,cno,avg(grade)asavg_gradefroms_scgroupbycno,sdept.
1、使用XSGL数据库中适当的表,创建一个自定义函数—kccj,该函数可以根据输入的学生姓名返回该学生选修的课程名称和成绩。
并调用该函数。
createfunctionkcci(@snamevarchar(10))
returnstable
as
return(
selectsname,s.sno,cname,gradefromstudentsjoin
sconsc.sno=s.snojoin
coursecono=o
wheresname=@sname
)
go
select*fromkcci('张三')
go
2、使用XSGL数据库中适当的表,创建一个自定义函数—xbxs,该函数可以根据输入的系部名称返回该系学生的学号、姓名和出生日期。
并调用该函数。
createfunctionxbxs(@dnamevarchar(20))
returnstable
as
return(
selectsname,s.sno,birthfromstudentsjoin
departmentdond.deno=s.depart
wheredname=@dname
)
go
select*fromxbxs('计算机科学系')
go
3、使用系统存储过程sp_helptext查看kccj函数的文本信息。
sp_helptextkcci
4、修改kccj函数,使该函数根据输入的学生学号返回该学生的姓名、选修课程名称和成绩。
alterfunctionkcci(@snochar(9))
returnstable
as
return(
selectsname,cname,gradefromstudentsjoin
sconsc.sno=s.snojoin
coursecono=o
wheresno=@sno
)
Go
5、删除xbxs函数。
dropfunctionxbxs
6、自定义一个函数,其功能是将一个百分制的成绩按范围转换为“优秀”,“良好”,“通过”,“不及格”。
并调用该函数。
CREATEFUNCTIONsc_pass
(@grade1int)
RETURNSCHAR(8)
AS
BEGIN
DECLARE@infochar(8)
IF@grade1>=90SET@info='优秀'
ELSEIF@grade1>=75SET@info='良好'
ELSEIF@grade1>=60SET@info='通过'
ELSESET@info='不通过'
RETURN@info
END
selectdbo.sc_pass(95)
7、定义一个内嵌表值函数,通过课程名、系名称可以查询某系中选修了该课程的全部学生名单和成绩。
并调用该函数。
CREATEFUNCTIOND_courseG(@deptchar(20),@Cnavarchar(20))
RETURNSTABLE
AS
RETURN(SELECTSname,Cname,GradeFROMStudentS,SC,CourseC
WHERES.Sno=SC.SnoANDSC.Cno=C.CnoANDDepart=@deptAND@Cna=C.Cname)
GO
Select*fromd_courseg('17','操作系统')
8、创建自定义函数top_grade(),根据输入的系代码统计出该系平均成绩最高的前三名同学的信息。
并调用该函数。
CREATEFUNCTIONtop_grade(@deptchar(20))
RETURNSTABLE
AS
RETURN(SELECTtop3sc.sno,s.sname,avg(grade)as平均成绩FROMStudentS,SC
WHERES.Sno=SC.SnoANDDepart=@deptgroupbysc.sno,s.snameorderby平均成绩desc)
GO
select
*fromdbo.top_grade('17')
go
8、自定义一个名为Fun_add函数,实现两个数值的加法,并返回其和值。
并调用该函数。
CREATEFUNCTIONFun_add
(@num1int,@num2int)
RETURNSint
AS
BEGIN
declare@numint
set@num=@num1+@num2
return@num
END
select
dbo.fun_add(10,20)
3、利用日期函数,计算你来到这个世界已经有多少天?
setlanguageus_english
declare@firsttimedatetime,@secondtimedatetime
set@firsttime='1991-12-02'
set@secondtime=getdate()
selectdatediff(dy,@firsttime,@secondtime)
4、将32768.55转换为整数。
declare@floattestfloat
set@floattest='32768.55'
selectcast(@floattestasint)
5、利用字符串函数,截取“SQLServer2005”,从第5个字符开始的长度为10的字符串。
declare@stringtestchar(20)
set@stringtest='SQLServer2005'
selectsubstring(@stringtest,5,7)
6、利用字符串函数,将“helloworld”的前导空格去掉。
declare@stringtestchar(20)
set@stringtest='helloworld'
selectltrim(@stringtest)
7、延迟20秒执行查询select*fromstudent
usexsgl
go
waitfordelay'00:
00:
20'
select*fromstudent
8、给出课程号为“ZB171401”的学生成绩单,将百分制成绩换算成优、良、中等、及格、不及格。
selectsname,sc.sno,cno,grade,成绩评级=
case
whengrade>=85then'优'
whengrade>=80andgrade<85then'良'
whengrade>=70andgrade<80then'中等'
whengrade>=60andgrade<70then'及格'
whengrade<60then'不及格'
else
'没选修成绩'
end
fromsc,student
wheresc.sno=student.snoandcno='ZB171401'
1、查询学生总人数:
selectcount(*)学生总人数
fromstudent
go
2、查询选修了课程的学生总数:
selectcount(*)学生总数
fromsc
go
3、查询所有课程的总学分数和平均学分数,以及最高学分和最低学分:
selectsum(ccredit)as总学分,avg(ccredit)as平均成绩,max(ccredit)as最高学分,min(ccredit)as最低学分
fromcourse
go
4、计算’ZB171402’号课程的学生的平均成绩,最高分和最低分:
selectavg(grade)as平均成绩,max(grade)as最高成绩,min(grade)as最低成绩
fromsc
wherecno='ZB171402'
go
5、查询选修’ZB171401’的学生学号和成绩,并按成绩降序排列,成绩相同,则按学号升序排列:
selectsnoas学号,gradeas成绩
fromsc
WHERECno='ZB171401'
orderbygradedesc,snoasc
go
6、查询各系的学生的人数并按人数从多到少排序:
selectdepart,count(*)学生总数
fromstudent
groupbydepart
orderbycount(*)desc--count(*)是按人数进行排序的
go
7、查询各系的男女生学生总数,并按系别,升序排列:
selectdepartas系名,ssexas性别,count(*)学生总数--列名(depart)与函数(count)是不能同时出现在select中的,由于有groupbydepart
--(以系进行了分组,就是把每个系的人都统一了,所以depart放在这里是合法的。
不会出现学生与系出现混乱)
fromstudent
groupbydepart,ssex--用groupby以depart(按系别)进行分组的
orderbydepartasc--以系名排序(因为groupby已经分好组了,
--而orderby又是以系名升序排列的,又因为'departas系名'语句是以系名升序排列的,故系名与学生总数正好是下图显示的结果)
Go
8、查询每个学生所选课程的平均成绩,最高分,最低分,和选课门数:
selectcount(*)选课门数,avg(grade)as平均成绩,max(grade)as最高成绩,min(grade)as最低成绩
fromsc
go
9、查询至少选修了2门课程的学生的平均成绩:
(至少选两门课程,就是说学号出现两次)
selectsnoas学号,avg(grade)as平均成绩
fromsc
groupbysc.grade,sno
havingcount(sno)>=2
go
10、查询平均分超过80分的学生的学号和平均分:
selectsnoas学号,avg(grade)as平均成绩
fromsc
groupbysc.grade,sc.sno
havingavg(grade)>=80
go
11、统计有成绩同学的人数:
selectdistinctcount(*)总人数
fromsc
wheregradeisnotnull
go
12、求选课在2门以上且各门课程均及格的学生的学号及其总成绩,查询结果按总成绩降序列出:
Selectsum(grade)as总成绩,snoas学号
fromsc
groupbysc.sno
havingcount(sno)>2andavg(grade)>=60
orderbysum(grade)desc
go
二、T-SQL命令方式建立表:
1.建立Department表命令如下:
createdatabaseXSGL--创建数据库XSGL
go--执行条件,当扫描到go时就执行go上面的语句
useXSGL
CREATETABLEDepartment--创建表Department
(
Denochar(6)NOTNULLPRIMARYKEY,
Dnamevarchar(20)notnullunique,
Leadvarchar(20)unique,
Telchar(10)unique,
)
go
2.建立student表命令如下:
createtablestudent--创建一个表student
(
Snochar(8)primarykey,
Snamechar(16)notnull,
Ssexchar
(2)check(Ssex='男'orSsex='女'),--性别
Brithdatetimecheck(Brith>='1900-1-1'andBrith<='2011-12-31'),--出生日期
Departchar(6)foreignkeyreferencesDepartment(Deno)default'0000001',--创建的Depart的外码是Deno的主码,默认值是
Addrchar(50),
)
go
3.建立Course表命令如下:
CREATETABLECourse--创建表Course
(
Cnochar(10)notnullprimarykey,
Cnamevarchar(20)notnullunique,
Ccreditintcheck(Ccredit<=10andCcredit>=0),
Periodintcheck(Period<=200andPeriod>=0),
)
go
4.建立SC表命令如下:
createtablesc
(
Snochar(8)referencesstudent(Sno),
Cnochar(10)referencescourse(Cno),
Gradeintcheck(Grade<=100andGrade>=0),
primarykey(Sno,Cno),
)
go
三、使用T-SQL命令修改表
1、为表SC删除主码约束,然后添加主码约束
altertablesc
dropconstraintCK__SC__Grade__15502E78
go
altertableSC
addconstraintPKprimarykey(Sno,Cno)
go
2、为表“Course”添加Period添加学期大于等于1小于等于10的约束。
altertableCourse
addconstraintCKcheck(Period>=1andPeriod<=10)
go
3、在学生表中添加“身份证号”字段,char类型,长度为18
四、T-SQL命令方式输入如下表记录:
1、录入Department表命令如下:
insertintoDepartmentvalues('000001','中文','张三','2345011')
insertintoDepartmentvalues('000002','数学','李四','2345021')
insertintoDepartmentvalues('000003','信息','王五','2345041')
insertintoDepartmentvalues('000004','外语','王七','2345051')
insertintoDepartmentvalues('000005','教育','任杰','2345061')
insertintoDepartmentvalues('000006','物理','管斌','2345071')
insertintoDepartmentvalues('000007','政治','申奇','2345081')
go
2、录入Student表命令如下:
insertintoStudentvalues('20070001','李勇','男','1983-1-2','000001','孝感学院')
insertintoStudentvalues('20070002','刘晨','女','1983-2-2','000002','孝感学院')
insertintoStudentvalues('20070003','王敏','女','1983-3-2','000003','孝感学院')
insertintoStudentvalues('20070004','张立','男','1983-4-2','000004','孝感学院')
insertintoStudentvalues('20070005','刘云','女','1983-5-2','000005','孝感学院')
go
3、录入Course表命令如下:
useXSGL
insertintoCoursevalues('1','数据库',4,5)
insertintoCoursevalues('2','数学',4,5)
insertintoCoursevalues('3','信息系统',3,1)
insertintoCoursevalues('4','操作系统',4,6)
insertintoCoursevalues('5','数据结构',4,7)
insertintoCoursevalues('6','数据处理',1,null)
insertintoCoursevalues('7','C语言',4,6)
go
4、
5、录入SC表命令如下:
useXSGL
insertintoSCvalues('20070001','1',92)
insertintoSCvalues('20070001','2',85)
insertintoSCvalues('20070001','3',88)
insertintoS
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 查询 资料
![提示](https://static.bdocx.com/images/bang_tan.gif)