sql数据库训练1.docx
- 文档编号:5043168
- 上传时间:2022-12-12
- 格式:DOCX
- 页数:12
- 大小:24.68KB
sql数据库训练1.docx
《sql数据库训练1.docx》由会员分享,可在线阅读,更多相关《sql数据库训练1.docx(12页珍藏版)》请在冰豆网上搜索。
sql数据库训练1
1.新建学生-课程数据库的三个表:
学生表:
Student(Sno,Sname,Ssex,Sage,Sdept)Sno为主码;
课程表:
Course(Cno,Cname,Cpno,Credeit)Cno为主码;
学生选修表:
SC(Sno,Cno,Grade)Sno,Cno,为主码;
Student
学号(Sno)
姓名Sname
性别Ssex
年龄Sage
所在系Sdept
95001
李勇
男
20
CS
95002
刘晨
女
19
IS
95003
王敏
女
18
MA
95004
张立
男
19
IS
Course:
课程号
Cno
课程名
Cname
先行课
Cpno
学分
Credit
1
数据库
5
4
2
数学
2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理
2
7
Pascal语言
6
4
SC:
学号
Sno
课程号
Cno
成绩
Grade
95001
1
92
95001
2
85
95001
3
88
95002
2
90
95002
3
80
数据库生成语句:
Sql代码
1.createdatabasestu_course
2.
3.usestu_course
4.
5.createtablestudent(
6.snovarchar(32),
7.snamevarchar(32),
8.ssexvarchar(32),
9.sageint,
10.sdeptvarchar(32)
11.)
12.
13.createtableCourse(
14.Cnovarchar(32),
15.Cnamevarchar(32),
16.Cpnovarchar(32),
17.creditint
18.)
19.
20.createtableSC(
21.Snovarchar(32),
22.Cnovarchar(32),
23.Gradeint
24.)
createdatabasestu_course
usestu_course
createtablestudent(
snovarchar(32),
snamevarchar(32),
ssexvarchar(32),
sageint,
sdeptvarchar(32)
)
createtableCourse(
Cnovarchar(32),
Cnamevarchar(32),
Cpnovarchar(32),
creditint
)
createtableSC(
Snovarchar(32),
Cnovarchar(32),
Gradeint
)
一:
查询表中的列和行
1:
查询全体学生的学号与姓名
selectsno,snamefromstudent
2:
查询全体学生的姓名、学号、所在系。
selectsno,sname,sdeptfromstudent
3:
查询全体学生的详细记录
select*fromstudent
4:
查询全体学生的姓名及出生年份
selectsname,DATEPART(yy,GETDATE())-sage+1fromstudent(SQLServer)
5:
查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名
selectsname,DATEPART(yy,GETDATE())-sage+1,lower(sdept)fromstudent
6:
查询选修了课程的学生学号
selectsno,cnofromsc
7:
查询选修了课程的学生姓名
selectdistinctsnamefromstudent,scwherestudent.sno=sc.sno
二:
条件查询:
常用的查询条件
查询条件谓词
比较=,<,>,>=,<=,!
=,<>,!
>,!
<;
not+上述比较运算符
确定范围Betweenand,NotbetweenAnd,
确定集合IN,notIN
字符匹配Like,NotLike
空值IsNull,ISNOTNULL
多重条件AND,OR
1:
查询计算机系全体学生的姓名
selectsnamefromstudentwheresdept=”CS”
2:
查询所有年龄在20岁以下的学生姓名及其年龄
selectsname,sagefromstudentwheresage<20
3:
查询考试成绩有不及格的学生的学号
selectsnofromscwheregrade<60
4:
查询年龄在20到23间的学生的姓名,系别及年龄
selectsname,sdept,sagefromstudentwheresagebetween20and23
5:
查询年龄不在20到23间的学生的姓名,系别及年龄
selectsname,sdept,sagefromstudentwheresagenotbetween20and23
6:
查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别
selectsname,ssexfromstudentwheresdeptin("IS","MA","CS")
7:
查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别
selectsname,ssexfromstudentwheresdeptnotin("IS","MA","CS")
8:
查询学号为”95001”的学生详细情况
select*fromstudentwheresno=95001
9:
查询所有姓刘的学生的姓名,学号和性别(wherenamelike‘刘%’)
selectsname,sno,ssexfromstudentwheresnamelike'刘%'
10:
查询姓”欧阳”且命名为三个汉字的学生的姓名
selectsnamefromstudentwheresnamelike'欧阳_'
11:
查询名字中第2个字为”阳”字的学生姓名和学号(wheresnamelike'_阳%')
selectsname,snofromstudentwheresnamelike'_阳%'
12:
查询所有不姓刘的学生姓名
selectsnamefromstudentwheresnamenotlike'刘%'
13:
查询DB_Design课程的课程号和学分(wherecnamelike'Db\_Design'Escape'\')
selectcno,greditfromcoursewherecnamelike'Db\_Design'Escape'\'
14:
查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况(wherecnamelike‘DB\_%i__’escape’\’)
‘DB\_%i__’escape’\’)
selectcno,greditfromcoursewherecnamelike‘Db\_%i__’escape’\’
15:
查询缺少成绩的学生的学号和相应的课程号(wheregradeisnotnull)
selectsno,cnofromscwheregradeisnull
16:
查询有成绩的学生学号和课程号
selectsno,cnofromscwheregradeisnotnull
17:
查询计算机系年龄在20岁以下的学生姓名
selectsnamefromstudentwheresdept=”CS”andsage<20
18:
查询选修了3号课程的学生的学号及其成绩,分数降序排列
selectstudent.sno,gradefromstudent,sc
wherestudent.sno=sc.snoando=3orderbygradedesc
19:
查询全体学生情况,结果按所在系的号升序排列,同一系中的学生按年龄降序
select*fromstudentorderbysdept,sagedesc
三:
使用集函数
count,sum,avg,max,min
1:
查询学生的总人数
selectcount(sno)fromstudent
2:
查询选修了课程的学生人数(selectcount(distinctsno))
selectcount(distinctsno)fromSC
3:
计算1号课程的学生平均成绩
selectavg(grade)fromSCwherecno='1'
4:
查询选修1号课程的学生最高分数
selectmax(grade)fromSCwherecno='1'
5:
求各个课程号及相应的选课人数
selectcno,count(sno)fromscgroupbycno
6:
查询选修了3门以上的课程的学生学号
selectsno
fromsc
groupbysno
havingcount(*)>3
四:
连接查询:
<1>等值与非等值的连接查询
在连接查询中用来连接两个有的条件称为连接条件或连接谓词,,当连接运算符号为”=”时,称为等值连接,使用如,=,<,>,<=,>=,!
=连接时称非等值连接
1:
查询每个学生及其选修课程的情况
selectstudent.*,sc.*
fromstudent,sc
wherestudent.sno=sc.sno
<2>自身连接
连接操作在同一个表中进行连接查询
2:
查询每一门课的间接先修课(即先修课的先修课)
selectfirst.cno,second.cpno
fromcoursefirst,coursesecond
wherefirst.cpno=o
五:
复合条件连接
1:
查询选修2号课程且成绩在90分以上的所有学生。
Selectstudent,sname
formstudent,sc
Wherestudent.sno=sc.snoAnd
So=’2’andsc.grade>90
六:
嵌套查询
1:
带有谓词in的子查询
<1>查询与“刘晨”在同一个系学习的学生
selectsno,sname,sdept
fromstudent
wheresdeptin(
selectsdept
fromstudent
wheresname='刘晨')
或:
selects1.sname,s1.sdept
fromstudents1,students2
wheres1.dept=s2.deptands2.name='刘晨'
<2>查询选修了课程名为“信息系统”的学生学号和姓名
selectsno,sname
fromstudent
wheresnoin
(selectsno
fromsc
wherecnoin
(selectcno
fromcourse
wherecname='信息系统')
或:
selectsno,sname
fromstudent,sc,course
wherestudent.sno=sc.snoand
o=oand
ame='信息系统')
2:
带有Any或all谓词的子查询
<1>查询其他系中比信息系中某一学生年龄小的学生姓名和年龄
selectsname,sage
fromstudent
wheresage fromstudent wheresdept=’is’) andsdept<>’is’ 或用集函数: selectsname,sage fromstudent wheresage< (selectmax(sage) fromstudent wheresdept=’is’) andsdept<>’is’ <2>查询其他系中比信息系所有学生年龄都小的学生姓名及年龄 selectsname,sage fromstudent wheresage (selectsage fromstudent wheresdept=’is’) andsdept<>’is’ 3带有Exitst谓词的子查询 <1>查询所有选修了1号课程的学生姓名 selectsname fromstudent whereexists (select* fromsc wheresno=student.snoandcno='1') <2>查询没有选修1号课程的学生姓名 selectsname formstudent wherenotexists (select* formsc wheresno=stuedent.snoandcno=’1’) <2>查询选修所有全部课程的学生姓名 selectsname fromstudent wherenotexists (select* fromcourse wherenotexists (select* fromsc wheresno=student.sno andcno=o) <3>查询只选修了学生95002选修的全部课程的一部分的学生号码 selectdistinctsno fromscscx wherenotexists (select* fromscscy wherescy.sno=’95002’and notexists (select* fromscscz wherescz.sno=scx.snoand o=o) ) 二: 题一: 表A数据如下: FYearFNum 20061 20062 20063 20074 20075 20076 按如下格式显示: 年度20062007 汇总 615 方案一: createtable表名(FIDvarchar(10),Field1varchar(100)) go insertinto表名select1,'A' insertinto表名select1,'B' insertinto表名select1,'C' insertinto表名select2,'D' insertinto表名select2,'E' insertinto表名select2,'F' go --创建一个合并的函数 createfunctionf_merge(@namevarchar(100)) returnsvarchar(8000) as begin declare@strvarchar(8000) set@str='' select@str=@str+','+cast(Field1asvarchar(100))from表名whereFID=@name set@str=stuff(@str,1,1,'') return(@str) End go --select*from表名 --调用自定义函数得到结果: selectFID,dbo.f_merge(FID)astelfrom表名groupbyFID droptable表名 dropfunctionf_merge 方案二: select'汇总'as年度 [2006],[2007] from (selectfyear,fnumfromT)assourceTable pivot ( sum(fnum) forfyearin([2006],[2007]) ) aspivotTable 回头发现可以用SQL2005pivot的方法很简单 题二: 表A数据如下: FIDField1 1A 1B 1C 2D 2E 2F 要求按如下格式显示: FIDField1 1A,B,C 2D,E,F 如何做到? createtable表名(FIDvarchar(10),Field1varchar(100)) go insertinto表名select1,'A' insertinto表名select1,'B' insertinto表名select1,'C' insertinto表名select2,'D' insertinto表名select2,'E' insertinto表名select2,'F' go --创建一个合并的函数 createfunctionf_merge(@namevarchar(100)) returnsvarchar(8000) as begin declare@strvarchar(8000) set@str='' select@str=@str+','+cast(Field1asvarchar(100))from表名whereFID=@name set@str=stuff(@str,1,1,'') return(@str) End go --select*from表名 --调用自定义函数得到结果: selectFID,dbo.f_merge(FID)astelfrom表名groupbyFID droptable表名 dropfunctionf_merge
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 数据库 训练