实验二-数据的查询、更新

实验二数据的查询、更新

一、实验目的

1、掌握用户自定义数据类型的方法

2、掌握用T-SQL语句进行数据的插入、修改、删除的方法

3、熟练掌握SELECT语句，能够运用该语句完成各种查询

二、实验要求

1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成

实验内容的预习准备工作；

2、能认真独立完成实训内容；

3、实验后做好实验总结，根据实验情况完成总结报告。

三、实验内容

1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20"，数据类型为varchar,

长度为20,允许为空。

sp_addtypechar20r*varchar(20)*fnull

SQLQueryLsql-NW^ministrator(53))*

sp_addtypechar20r*varchar(20)*znull

11

由消息

命令已成功完成。

提示：sp_addtype[@typename刁用户自定义类型的名字

[,@phystype=]系统类型名

[,[@nulltype=]'notnull|null'][,[@owner=]'拥有该类型的用户名,]

例：自定义一个名为address的类型，其所属系统类型为varchar,长度为80,不能为空。

sp_addtypeaddress,*varchar(80)'»rnotnull

2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表,

课程表，学生选修表，并向各表插入如下相应的数据。

createdatabase学生课程数据库

SQLQueryLsql-NW...ministrator(53))*

spaddtypechar20r'varchar(20)\null

七edatabase学

Lj消息

命令已成功完成。

学生表:Student(Sno,Sname,Ssex,Sage,Sd叩t)其中Sno为主键、Ssex取值为男或女、Sage

在15到30之间：

SnoSnameSsexSageSdept

95001李敏勇男20CS

95002刘晨女19IS

95003王敏女18MA

95004张立男18IS

createtablestudent(snointprimarykey,

snamechar(10),

ssexchar(2)constraintssex_chcheck(ssexin('男

女D),

sageintconstraintsage_chcheck(sage>=15and

sage<=30),

sdeptchar(10)

)

insertintostudent

values(95001「李敏勇，，,男120,'CSl

insertintostudent

values(95002,,刘晨l，女1193IS，)

insertintostudent

values(95003,'王敏'J女'，18,'MA')

insertintostudent

values(95004J张立'J男'，18,'IS')

/NW0DX9GMY1PY6A一库-dbo.studentSQLQueryl.sql-

列名数据类型允许Null值

SBintS

snamechar(10)画

ssexchar(2)画

sageint

sdeptchar(10)画

NW0DX9GMY1PY6A...库-dbo.studentNW0DX9GMY1PY6A...库-dbo.studentSQLQu，

snosnamessexsagesdept

►李敏更里20CS

95002刘晨女19IS

95003王敏女18MA

95004张立男18IS

米AUZNULLAUZNULL

课程表:Course(Cno,Cname,Cpno,Credeit,remarks)其中Cno为主键、Teacher的类型为

char20即为用户定义的数据类型；

CnoCnameCpnoCreditTeacher

1数据库54王芳

2数学NULL2刘新

3信息系统14刘新

4操作系统63高升

5数据结构74宋明

6数据处理NULL2张彬

7Pascal语言64李磊

createtablecourse(cnointprimarykey,

cnamechar(15),

cpnointnull,

creditint,

teacherchar20

)

insertintocourse

values(1J数据库l5,4，，王芳，)

insertintocourse

values（2,,数学INULL,2,，刘新，）

insertintocourse

values（3,,信息系统11,4,,刘新,）

insertintocourse

values（4,'操作系统I6,33高升，）

insertintocourse

values（5,,数据结构I7,4J宋明，）

insertintocourse

values（6,,数据处理INULL,2「张彬，）

insertintocourse

values（7,'PASCAL语言I6,4J李磊，）

NW0DX9GMY1PY6AS...据库-dbo.courseSQLQueryl.se

列名额据类型允许Null值

int

□namechar（15）叨

epnoint回

creditint

teachercha「20rvarcharQO）叨

E

NW0DX9GMY1PY6As.据库-dbo.courseNW0DX9GMY1PY6AS...西-dbo.courseSQ

cnocnameepnocreditteacher

►n数据库54王芳

2数学MJLL2刘新

3信息系统14刘新

4操作系统63高升

5数据结构74宋明

6数据处理MJLL2张彬

7PASCAL语言64李磊

米NULLNLILAJUU.AJULL

学生选修表:SC(Sno,Cno,Grade)其中Sno,Cno为主键同时又为外键、Grade值在0到100;

SnoCnoGrade

95001192

95001285

95001388

95002290

95003255

95004270

createtablesc（snoint,

cnoint,

gradeintconstraintgrade_chcheck（gradebetween0and

100),

primarykey(sno,eno),

constraintfksnoforeignkey(sno)references

student(sno),

constraintfkenoforeignkey(eno)references

course(eno),

)

insertintosc

values(95001,1,92)

insertintosc

values(95001,2,85)

insertintosc

values(95001,3,88)

insertintosc

values(95002,2,90)

insertintosc

values(95003,2,55)

insertintosc

values(95004z2z70)

列名数据类型允许Null值

S3intH

?enointH

gradeint切

n

NW0DX9GMY1PY6A-课SSfJS库-dbo.seNWODX9GMY1PY(

snoenograde

►SEiCTI192

95001285

95001388

95002290

95003255

95004270

米NULLMJLLNULL

3、用T-SQL语句，修改上面所建学生课程数据库中数据：

1）向学生表:Student中加入一条记录：（95030,谢非，男,22,CS）并保存

insertintostudent

values（950303谢非,3男1223CS，）

2）将李敏勇的数据库的成绩改为98分

updatesc

setgrade=98

wheresc.eno-(selectcourse.eno

fromcourse

wherecourse.cname'数据库，

andsno=(selectsno

fromstudent

wheresname='李敏勇

)

i

I

.匚

怎结果

命令已版功完成「

3)删除学生表Student中谢非的记录并保存

delete

fromstudent

wheresname-谢非'

delete

fromstudent

[wheresname=*

二1消息

<1行受影响）

4）能不能从Student表中删除李敏勇学生的记录，为什么？能不能删除王敏，张立两个

学生的记录？

不能,sc表中列sno是外码,参照student表的sno列。

4、用T-SQL语句，完成下面简单的查询

1）查询全体学生的学号、姓名及年龄.

use学生课程数据库

select*

fromstudent

SQLQueryLsql-N.Mministrator（54））*I

use学生课程数据库

F-]select*

Lfromstudent

,结果上消息

snosnamessexsagesdept

1I95001i李敏勇男20CS

295002划晨女19IS

395003王敏女18MA

495004张立男18IS

2）查询全体学生的姓名,年龄及所在系（要用小写字母表示系名，并用“系名”来表示

列名）。

selectsname,sage,lower(sdept)'系名'

fromstudent

selectsname/sagezlower(sdept)

二二：「5•二一二三二二

□结果_J消息

snamesage系名

120

1j"¥O"CS

2刘晨19is

3王敏18ma

4张立18is

3）查询选修了课程的学生学号

selectdistinctsno

fromsc

selectdistinctsno

fromsc|

口结果应肖息

sno

1[,95661”…|

2‘…飘5””

395003

495004

4）查询信息系全体学生的姓名

selectsname

fromstudent

wheresdept=*IS*

selectsnarr.eM

frcir.srudenr窗

wheresdept=,IS

口结果J]消息

sname

1pijiI

2窿立

5）查询所有年龄在20岁以下的学生姓名及其年龄

selectsname,sage

fromstudent

wheresage<20

selectsnamersage

fumzim二二

"二三二三M三：E-；2二

3结果j消息

snamesage

：..：

1s刘晨iis

2is

3张立18

6）查询年龄在20到18间的学生的姓名，系别及年龄

selectsname,sdept,sage

fromstudent

wheresagebetween18and20

Gjselectsname,adept,sage

fromstudent

-wheresagebetween18and20

<L__________________________

J结果「I消息

snamesdeptsage

1谭敏勇JCS20

2刘晨IS19

3王航MA18

4张立IS18

7)查询年龄不在23到19间的学生的姓名，系别及年龄

selectsname,sdept,sage

fromstudent

wheresagenotbetween19and23

8)查询不是信息系(IS)和计算机系(CS)学生的姓名和性别

selectsname,ssex

fromstudent

wheresdeptnotin(*IS*,*CS*)

selectsnamessex

AzS3

wheresdeptnotin(•IS,,'CS•)

□结果J)消息

snamessex

1……I女

9）查询所有姓刘的学生的姓名，学号和性别

selectsname,sno,ssex

fromstudent

wheresnamelike'张％’

[Hselectsname,sno,ssex

fromstudent

-wheresnamelike'张电'

口结果.J消息

snamesnossex

1[.…j95004里

10）查询姓“张”且名为一个汉字的学生的姓名

selectsname,sno,ssex

fromstudent

wheresnamelike，张」

selectsnamersnorssex

frcrr.m二nd".二

wheresnamelike・张

二1结果直%息

snamesnossex

1沔箴!95004男

11）查询名字中第2个字为“敏”字的学生姓名和学号

selectsname,sno

fromstudent

wheresnamelike'—敏％，

selectsnamezsno

frcrr.

wheresnamelike

国结果岛消息

snamesno

1rwi..i95001

2'Bii......95003

12）查询所有不姓刘的学生姓名

selectsname

fromstudent

wheresnamenotlike'刘3'

二二一二一二二二:二

frzrc.三二二d三二二

wheresnamenotlike*xf

口结果上消息

sname

1

2'"主场

3张立

13）查询全体学生情况，结果按所在系升序排列，同一系中的学生按年龄降序

select*

fromstudent

orderbysdeptasc,sagedesc

二,三二七一二一

二二二1二二一二

orderbysdeptascrsagedes

口结果.J|消息

snosnamessexsagesdept

1j95001i李敏勇男20CS

295002刘晨女19IS

395004张立里18IS

495003王敏女18MA

14）查询学生表中所有学生信息，要求只显示前10%行数据

selecttop10percent*

fromstudent

selecttop10percent*

:二二T三二二二三二二■翼

□结型电柒息

snosnamessexsagesdept

1r'gisooi2|李敏勇勇20cs

15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2

行数据,若第3行后的数据也等于70分也要显示。

selecttop2withtiessno

fromsc

wheregrade>=70

orderbygradedesc

占selecttop2withtiessno

fromsc

wheregrade>=70

-orderbygradedesc

□结果,J消息

sno

1595661

295002

16）查询每个学生的学号、课程号及分数，同时统计每个学生的总分

selectsno,eno,grade

fromsc

orderbysno

computesum(grade)bysno

NWODX9GMY1PY6A...课®-dbo.se

□selectsno,cnorgrade

fromsc

orderbysno

-computesum(grade)bysno

□结果_J）消息

snoenograde

1j95001=1%

295001285

395001388

sum

1["27l''l

snoenograde

1[950021290

工..........;

sum

1|90|

snoenograde

1r95003!255

;..........;

17）查询每个学生的各科分数、最高分、最低分、总分、平均分

selectsno,eno,grade

fromsc

orderbysno

computemax(grade)rmin(grade),sum(grade),avg(grade)bysno

□select3no,eno,grade

fromsc

orderbysno

-computemax(grade)fmxn(grade)zsuir^(grade),avg(grade)bysno

5、用T-SQL语句完成下面的查询

1)查询学生的总人数

select;COUNT(sno),人数，

frI二三二一：二三二二■魏

□结果J消息

1：4

selectCOUNT(sno)，人数

fromstudent

2)查询选修了课程的学生人数

selectCOUNT(distinctsno)'选课人数

fromsc

selectCOUNT(distinctsno)

口结果.J)消息

选课人数

1\4；

3)计算选2号课程的学生平均成绩

selectAVG(grade)’平均分'

fromsc

wherecno=2

4)查询选修2号课程的学生最高分数

selectMAX(grade)'最高分'

fromsc

wherecno^2

□结果」消息

最高分

1F90...1

5)求各个课程号及相应的选课人数

selecteno,COUNT(sno),人数'

fromsc

groupbyeno

Aselecteno,COUNT(sno)

fromsc|

groupby

6)查询选修了2门以上的课程的学生学号

selectsno

fromsc

groupbysnohaving(COUNT(eno)>2)

fromsc

groupbysnohaving(COUNT(eno)>2)

日结果消息

sno

1rssooii

;....;

7)查询每个学生及其选修课程的情况

selectstudent.sno,sc.eno,cname

fromstudent,sczcourse

wherestudent.sno=sc.snoando=course.eno

selectstudent.sno,sc.enocname

占mrsa

fromstudent^scrcourse

wherestudent.sno=sc.snoando=course.end

nr

8）查询每一门课的间接先修课（即先修课的先修课）

selectcl.eno,c2.epno

fromcourseclrcoursec2

9）查询选修2号课程且成绩在90分以上（包括90分）的所有学生。

selectstudent.sno,sname

fromstudent

wherestudent.snoin(selectsc.sno

fromsc

wherecno：2andgrade>90)

4|JH

」结果由福

snosname

1|'95602"|划晨

6.用T-SQL语句完成下面的查询

1）查询与“刘晨”在同一个系学习的学生

selectsno,sname

fromstudent

wheresname<>'文U晨'andsdept(selectsdept

fromstudent

wheresname=，文U晨，)

r二二二一二二:er.T

wheresnair.ec>'文ij辰'andsdepr=(select;sdepr

fromstudent

wheresnanie=,刘晨.)

4I____________________________________________________m_

3结果」消息

snosname

1["95004|张立

2)查询选修了课程名为“数学”的学生学号和姓名

selectsno,sname

fromstudent

wheresnoin(selectsno

fromsc

whereeno=(selecteno

fromcourse

wherecname=，数学，))

Aselectsno,sname

fromstudent

wheresnoin(selectsno

fromsc

whereeno=(selecteno

fromcourse

-wherecname="数学，))

4________________________________________________________nr______

□结果J消息

snosname

1:95001i李敏勇

295002刘晨

395003王徽

495004张立

3）查询其它系中比信息系中某一学生年龄小的学生姓名和年龄

selectsname,sage

fromstudent

wheresdepto11S1andsage<any(selectsage

fromstudent

wheresdept二*IS

@selectsnamersage

fromstudent

wheresdepto,IS*andsage<any(selectsage

fromstudent

wheresdept="IS*

in

G3结果J消息—

snamesage

1rw“一18

4）查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄

selectsname,sage

fromstudent

wheresdepto1IS1andsage<all(selectsage

fromstudent

wheresdept=1IS'

Aselectsnamesage

fromstudent

wheresdepro*IS,andsage<all(selectsage

fromstudent

wheresdept=,IS,

in

二1结果「消息

SnameSage

5）查询所有选修了2号课程的学生姓名

selectsname

fromstudent,sc

wherestudent.snosc.snoandeno2

fu.m二一ci2二二，三二

wherestudent.sno=sc.snoandcno=2|

6）查询没有选修3号课程的学生姓名

selectdistinctsname

二I结果消息

sname

肯

勇

1®..一

晨

刘

2敏

王

3立

张

4

7、用T-SQL语句完成下面的复杂查询

1)至少选修刘老师所授课程中一门课程的女学生姓名

selectsname

fromstudent

whereexists(selectsno

fromsc

whereexists(selecteno

fromcourse

whereteacherlike'刘当'andssex=，女'))

Aselectsname

fromstudent

whereexists

from

fromcourse

whereteacherlike%*andssex=.女.))

sname

1次谨

2'ii

2)检索王同学不学的课程的课程号

selecteno

fromcourse

whereenonotin(selectsc.eno

fromsc,student

wheresnamelike'王％

andstudent.sno=sc.sno

ando=sc.eno

)

白selecteno|

fromcourse

whereenonotin(selectsc.eno

Imwhe-res嵋namelikLe,王%・

andstudent.sno=sc.sno

and

N结果」消息

3）检索全部学生都选修的课程的课程号与课程名。

selectcnozcname

fromcourse

wherenotexists(select*

fromstudent

wherenotexists(select*

fromsc

wherestudent.sno=sc.sno

ando=course.eno))

白selecteno,cname

fromcourse

wherenotexists(select

fromstudent

wherenotexists(select

fromsc

wherestudent.sno=sc.sne

ando=course.eno))

«m

盅结果围诡nB

enocname

1[Tj数学

4)检索选修课程包含刘老师所授课的学生学号。

selectdistinctsno

fromsc

whereexists(select*

fromcourse

whereo=course.eno

andteacherlike'文U%')

占selectQLSLLnctsno,

fromsc

whereexists(select*

fromcourse

whereo=course.eno

andteacherlike・文［|告f)|

4\in

□结果」消息

sno

1)-9506I”…j

2'…丽5”"

395003

495004

5)求选修课程号为2的学生的平均年龄。

selectAVG(sage)*1平均年龄，

fromstudent,sc

wherestudent.sno=sc.snoandsc.cno=2

me•二三:二t-saej，二士m至

fromstudent,sc

wherestudent.sno=sc.snoando=2

4Iin

国结果口消息

乎丽龄

1|,l8............1

6)求刘老师所授课程的每门课程的学生平均成绩。

selectteacher,cname,AVG(grade)'平均分

fromsc,course,student

wheresc.cno=course.eno

andstudent.sno=sc.sno

andcourse.teacherlike'文U%

groupbyteacher,course.eno,cname

白selectteacher,cname,AVG(grade)*12平均分，

fromscrcourse,student

wheresc.cno=course.eno

andstudent.sno=sc.sno

andcourse.teacherlike.文麟.

groupbyteacherfcourse.eno,enamel

4Iin

E结果厩消息

teachercname平均分

1Wi.i数学75

2“知新信息系统88

7)检索学号比刘同学大，而年龄比他小的学生姓名。

selectsname

fromstudent

wheresno>(selectsno

fromstudent

wheresnamelike1文ij%1)

andsage<(selectsage

fromstudent

wheresnamelik㊀'文lj%‘)

□selectsname

fromstudent

wheresno>(selectsno

fromstudent

w■hereSsnamaelike'^lj%')

anasage<(selectsage

from

wheresnamelike,文依・)

4IHI

日结果3消息

sname

1fl®一.I

2'砥笠…

8)求年龄大于女同学平均年龄的男同学姓名和年龄。

selectsname,sage

fromstudent

wheresage>(selectavg(sage)

fromstudent

wheressex='女')

andssex='男

白select三匚me三己[三.鬻.

fromstudent

wheresage>(selectavg(sage)

fromstudent|MK

wheressex='女')

andssex=*男v

但结果不消息

snamesage

1r?ii']20

9)求年龄大于所有女同学年龄的男学生姓名和年龄。

selectsname,sage

fromstudent

wheressex='男

andsage>all(selectsage

fromstudent

wheressex，女’)

selectsname

fromstudent_

wheressex='男

andsage>all(selectsage

fromstudent

wheressex='

口结果消息

snamesage

1rwi"']20

10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另

一个己存在的基本表S(SNO,SNAME,SEX)„

selectsnozsname,ssex

intos

fromstudent

wheresnoin(selectsno

fromsc

wheregrad㊀>=80)

占selectsno,snamefssex

intos

fromstudent

wheresnoin(selectsno

wheregrade>=80)

消息_

<2行受影响）

11)把选课数学课不及格的成绩全改为空值。

updatesc

setgrade=1*

wheresnoin(selectsno

fromsc

wheregrade<60)

andcno=(selecteno

fromcourse

wherecname-数学f)

.：.-,--■■■■■■■■■■■■■♦■■■■■♦

setgrade=

wheresnoin(selectsno||।

fromsc

wheregrade<60)MMHM

andcno=(selectcno^H

fromcourse

wherecname=.数学')

“「—川

(消息_

<1行受^响)

12)把王同学的选课信息全部删去。

delete

fromsc

wheresno=(selectsno

fromstudent