2023年SQL语言实验报告

实验五SQL语言

—*、目的与规定

1.掌握SQL语言的查询功能；

2.掌握SQL语言的数据操作功能；

3.掌握对象资源管理器建立查询、索引和视图的方法;

二、实验准备

1.了解SQL语言的查改增删四大操作的语法；

2.了解查询、索引和视图的概念；

3.了解各类常用函数的含义。

三、实验内容

(-)SQL查询功能

使用提供的studentdb数据库文献，先附加到目录树中，再完毕下列题

目,SQL命令请保存到脚本文献中。

1.基本查询

(1)查询所有姓王的学生的姓名、学号和性别

SelectSt_Name,St_Sex,St_ID

Fromst_info

WhereSt_NameIike'王%

SQLQueryl.sql-.studentdb(PC-20160924WFDL\Administrator(59))*

1臼SelectSt_Name,St_Sex,St_ID

2Fromst_info

3WhereSt_Namelike*王％'

100%▼・

口结果二|消息

St__NameSt_SexSt_ID

1j主，j'雍|里2602060106

图5-1

(2)查询全体学生的情况，查询结构按班级降序排列，同一班级再按学号升序,

并将结果存入新表new中

select*intonew

fromst_info

orderbyCl_Namedesc,st_IDasc

SQLQuery2.sql-.studentdb(PC-20160924WFDL\Administrator(53))*

1Bselect*intonew

2fromst_info

3orderbyClLNamedesc,st_IDasc

100%一

由消息_________________________________________________________________________________

(16行受影响)

图5-2

(3)对S_C_inf。表中选修了“体育”课的学生的平均成绩生成汇总行和明

细行。(提醒：用compute汇总计算)

因2023版本已不支持compute关键字，所以选择用其他方式。

Seiectc_no,score

Froms_c_info

Wherec_no=29000011

groupbyc_no,score

PC-20160924WFDL...db-dbo.stjnfo对象资源笞理器SQLQuery3.sql-l...min

1ESelectc_no_,score

2Froms_c_info

3Wherec_no=29000011

4groupbyc_no_,score)

100%

二］结果j消息

c_noscore

1i29000011j77

22900001183

32900001192

42900001197

图5-3

2.嵌套查询

⑴查询其他班级中比“材料科学0601班”的学生年龄都大的学生姓名和年

龄

seiectst_name,born_date

fromst_info

wherecl_name!=’材料科学0601班'andborn_date<(select

min(born_date)

fromst_infowherecl_name=7材料科学0601班')

SQLQuery4.sql-l...ministrator(59))*XPC-20160924WFDL...db-dbo.stjnfo对象资源S3器

1臼selectst_name.,born-date

2fromst_info

3wherecl_name।二’材料科学0601班’andborn_date<(selectmin(born_date)

J4fromst_infowherecl_name二’材料科学0601班［)

100%一

口结果消息

st__nameborn_date

:邓红艳|1986-07-0300:00:00.000

金萍1984-11-0600:00:00.000

吴中华1985-04-1000:00:00.000

郑远月1986-06-1800:00:00,000

图5-4

⑵用exists查询选修了“9710041"课程的学生姓名

selectstname

fromst_info

whereexists(se1ect*froms_c_infowherec_no=9710041

andst_id=st_info.st_id)

PC-20160924WFDL...db-dbo.stjnfo对象资源霞器

1Bselectst_name

2fromst_info

3whereexists(select*froms_c_infowherec_no=9710041andst_id=st_info.st_id)

□结果,J消息

st_name

1「福受受

2黄正刚

3张红飞

4曾莉娟

图5-5

⑶用in查询找出没有选修“9710041”课程的学生的姓名和所在班级。

selectst_name,c1_name

fromst_info

wherest_idnotin(selectst_idfroms_c_infowherec

no'97100417)

ISQLQueryG.sql-l...ministrator(52))*XPC-20160924WFDL...db-dbo.stjnfo对象资源告理器

1-selectst_name.,cl_name

2fromst_info

3wherest_idnotin(selectst_idfroms_c_infowherec_no=r971004T)

100%▼4

□结果消息

st_namecl_name

1邓红艳法学0501

2金萍法学0502

3吴中华法学0503

4杨柳法学0503

5郑远月法学0601

6张力明法学0602

7张好然法学0603

8李娜法学0604

9杨平娟口腔（七）0601班

10王小维口腔（七）0601班

11划小玲口腔（七）0601班

12何邵阳j口腔（七）06。1班

图5-6

⑷查询选修了学号为的学生所选所有课程的学生姓名。

selectst_name

fromst_infowherest_idin

(selectdistinctst_idfroms_c_infowherenotexists

(se1ect*froms_c_info

wherest_id=''andnotexists

(select*froms_c_infowherest_info.st_id=s_c_info.st_id

and

c_no=any(selectc_nofromscinfowherestid

''))))

76c(s_enole=catn*y(fsroemlecst_cc__innofofrowmherse_cs_ti_nifnofow.hset_reid=sts__icd_=_*in2f0o0.1s0t5_0i1d05a,n)d)))|

S10Q0L%Que▼ry7".sql-l...ministrator(52))*xPC-20160924WFDL...db-对"源频器

5wherest-id=>2001050105*andnotexists

国结果1口Sse消le息ctst_najne

2st_nafmreomst_infowherest_idin

43((sseelleecctt*dfirsotmincs_tc_stin_fiodfroms_c_infowherenotexists

1『福箱1

2'荃舞……

3吴中华

1图5・7

3.连接综合查询及其他

(1)查询每个学生所选课程的最高成绩，规定列出学号，姓名，课程编号和分数。

selectst_info.st_id,st_name,c—info.c_no,score

fromst_infoinnerjoins_c_infoonst_info.st_id=s

_c_info.st_idinnerjoin

c_infoons_c_info.c_no=c_info.c_no

wherescore:(selectmax(s_c_info.score)froms_c_info

wherest_info.st_id=s_c_info,st—id)

SQLQuery8.sql-l...ministrator(52))*XPC-20160924WFDL...db-dbo.stjnfo对象资源压器SQLQuery3.sql-

1Bselectst_info.st_id,st.name,c_info.c_no,score

2fromst_infoinnerjoins_c_infoonst_info.st_id=s_c_info.st_idinnerjoin

3c_infoons_c_info.c_no=c_info.c_no

4wherescore-selectmax(s_c_info.score)froms_c_info

5wherest_info.st_id=s_c_info.st_id)

100%，

口结果J消息

st_idst_namec_nosee

1j0603060108;徐文文971004167

20603060109黄正刚971004178

30603060110张红飞971004152

40603060111曾莉娟971004199

52001050105邓红艳972001390

62001050106金萍972001393

72001050107吴中华972001377

82602060105杨平娟2900001177

92602060106王小维2900001197

102602060107刘小玲2900001192

112602060108何邵阳2900001183

图5-8

⑵查询所有学生的总成绩，规定列出学号、姓名、总成绩，没有选修课程的

学生总成绩为空。

se1ectst_info.st_id,st_name,总成绩

fromst_info

1eftouterjoin(se1ectst_id,sum(score)as总成绩f

roms_c_infogroupby

stid)scinfoonstinfo.stidscinfo.stid

2LQuery9.sql-l...ministrator(52))*XPC-20160924WFDL....db-dbo.stjnfo对象资源管理器

1Eselectst_info.st_id,st_name,总成绩

2fromst_info

43slet_fitd)osu_tecr_injofionon(ssetl_eicntfos.t_sitd_i,ds=ums_(csc_oinref)o.ast_总id成绩froms_c_infogroupby

100%▼♦

□结果二I消息

st-idst_name总成绩

r1^0603060108I徐文文67

20603060109黄正刚78

30603060110张红飞52

40603060111曾莉娟99

52001050105邓红艳178

62001050106金萍182

72001050107吴中华153

82001050109杨柳NULL

92001060103郑远月NULL

102001060104张力明NULL

112001060105张好然NULL

122001060106李娜HULL

132602060105杨平娟77

142602060106王小维97

152602060107刘小玲92

162602060108何邵阳83

图5—9

(3)查询“大学计算机基础”课程考试成绩前三名的学生姓名和成绩。

selectst_info.st_id,st_name,score

fromst_info

irir)(?rJoins_c_infoonst_info.st_id=s_c_info.st_id

innerjoinc_infoons_c_info.c_noc_info.c_no

andc_name=7大学计算机基础'

SQLQuerylO.sql-...dministrator(52))*X■骐，OT1立庭睦迎”4

⑷将s_c_info中的score列的值转为等级制输出，即60分以下显示为

“不及格及60〜69分显示“及格”，70〜79分显示“中档”，80~81显

示“良好”，90〜100显示“优秀二规定输出学号、姓名、课程名、成绩

等级。(提醒:在seiect字句中使用case...when...end语句)

selectst_info.st_id,st_name,c_name,成绩等级二

case

whenscore〉二90then'优秀'

whenscore>=80then'良好，

whenscore>=70then'中档’

whenscore>^60then'及格’

whenscore<60then，不及格‘

end

froms_c_info,st_info,c_info

wherest_info.st_ids_c_info.st_idandc_info.c_nos_

cinfo.cno

ISQLQueryll.sql-...dministrator(52))*X

selectst_info.st_id,st_name.,c_name..成绩等级二

case

|3

whenscore>=90then'优秀’

whenscore>=80then‘良好’

whenscore>=70then‘中等’

whenscore>=60then'及格’

whenscore<60then'不及格’

end

froms_c_info,st_info,c_info

10wherestinfo.stid二scinfo.stidandcinfo,cno二scinfo,cnol

100%一

□结果消息

st_dst_namec_name成绩等级

12603项108.徐文文C语言程序设计基甜及格

20603060109黄正刚C语言程序设计基酬中等

30603060110张红飞C语言程序设计基础不及格

40603060111曾莉娟C语言程序设计基础优秀

52001050105邓红艳大学计算机基础良好

62001050105邓红艳大学计算机基础实蛉优秀

72001050106金第大学计算机基础良好

82001050106金萍大学计算机基础实验优秀

92001050107吴中华大学计算机基础中等

102001050107吴中华大学计算机基础实蛉中等

112602060105杨平娟体育中等

122602060106王小维体育优秀

132602060107刘小玲体育优秀

142602060108何邵阳体育良好

图5-11

（二）SQL的增删改功能

在实验四建立的studb数据库中，写SQL语句实现增删改功能。

1.在S表中增长如下记录：

____|_sno」sname|ssex|bomdate|dname|enscore|address

1|jS3j张明华男1995^）8-2100:00:00,000MA重学530.0浙江杭州

图5—12

insertS

values('s3?张明华，，'男',1995/08/2100:00：00.00O','MA_数学','530.0'

浙江杭州:NULL)

SQLQuery5.sql-127.0.0.l.studb(PC-20160924WFDL\Administrator(57))*

1HinsertS

2values('s3'张明华里1995/08/2100:00:00.000F/MA一数学‘，'530.O',‘浙江杭州‘，NULL)

图5—13

PC-20160924WFDLstudb-dbo.SXPC-20160924WFDLstudb-dbo.S

snosnamessexborndateclnameenscoreaddresssfzh

►SI恒男1995-03-200...CS_计算机460.0湖南长沙NULL

S10刘京女1996-07-100...EN五语472.0NULL

S2欧阳蒙女1994-10-090...MAR519.5NULLNULL

s3张明华男1995-08-210...MA530.0浙江杭州NULL

图5-14

2.在C表中将课程名为“数据库”的学分更改为3。

updateCsetceredit=73'

wherecname='数据库，

SQLQueryG.sql-l...ministrator(52))*XPC-20160924WFDLstudb-dbo.S

1EupdateCsetccredit=,3>

2wherecname二‘数据库’

100%一

由肖息

(1行受影响)

图5-15

PC-20160924WFDL.studb-dbo.C]SQLQuery6.sql-L..ministrator(52))*1

enocnameccreditepno

►Cl高等数学7.2NULL

C2数据库3.0C5

C54.0Cl

・NULLNULLNULLNULL

图5-16

3.删除S表中S2的学生记录，请问是否能删除，为什么,要如何操作。

能删除

de1etefromS

wheresno='S2

SQLQuery7.sql-127.0.0.l.studb(PC-20160924WFDL\Administrator(52))*

1t-deletefromS

2wheresno='S2'

100%

td消息

消息547,级别16,状态。，第1行

DELETE语句与REFERENCE约束"FK_SCS"冲突。该冲突发生于数据库“studb",表"dbo.SC",column〈no'。

语句已终止。

图5-17

PC-20160924WFDL.studb-Diagram」)

SC

8sno8eno

8enocname

scoreccredit

cono

8sno

sname

ssex

borndate

clname

enscore

图5-18

SQLQuery7.sql-.studb(PC-20160924WFDL\Administrator(52))*

1EdeletefromS|

2wheresno=，S2'

100%…

石肖息

（1行受影响）

图5-19

>C-20160924WFDLstudb-dbo.S

snosnamessexborndateclnameenscoreaddresssfzh

►SI恒aa1995-03-200...CSJ+算机460.0湖南长沙NULL

S10踞女1996-07-100...EN至语472.0NULL

张明华EB1995-08-210...MA费学530.0浙江杭州NULL

NULLNULLNULLNULLNULLNULLNULLNULL

图5-20

（三）索引

1.在studb数据库中，分别用对象资源管理器和SQL语言定义索引

在对象资源管理器中，在T表的tname列上中建立聚集索引ix_tname,降序。查

看聚集的效果。

-；新建案引

i＞就绪

区鹿本,的帮助

衰名CD：

、案引名称⑻：

^x_tname

索引姆凶:

雌

睚一©

素弓i键列|

酬除(R)|

上移凶

W(D)

瑕取消帮助

图5-21

(&1^3-J

图5-22

1.使用SQL语言定义TC表的(tno,eno)列上的复合索引ix_tc,tno列设

为升序，eno列设为降序

先增长cno列，再删除聚集索引ix_tnameo

createc1usteredindexix—tc

onT(tno,cno)

SQLQuery9.sql-.studb(PC-20160924WFDL\Administrator(52))*

1Ecreateclusteredindexix_tc

2onT(tno.,cng)|

100%一

信消息I_______________________

命令已成功完成。

图5-23

图5-24

（四）视图

在studb数据库中操作。

1.在对象资源管理中建立视图v_s_c,列出所有学生所选课程的成绩:学号,

姓名，班级名，课程号，课程名，成绩。

8(SQLServer12.0.5000.0-PC-20160924WFD

2□数据库

a□系统卦据库

@□州库快照

国［JReportServer

田|JReportServerTempDB

BUstudb

田□数据库关系图

国立表

一」寇r新建视图（N）“.

（+）

图5-25

添加表

图5-26

PC-20160924WFDLstudb-dbo.View_l*

怖

脩

□为

□F*育

□*

回50g□

二

回El□sal

05口s

口

IEe

口ccrsexcor

CPInborn色

列别名表城出排序类型排序,顿序或.

snoExpr2S团

snameExpr3s

cnamec