sql数据库训练1.docx

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）

）

二：

题一：

表Ａ数据如下：

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