2023年SQL語言實驗報告

實驗五SQL語言

—*、目的與規(guī)定

1.掌握SQL語言的查詢功能；

2.掌握SQL語言的數(shù)據(jù)操作功能；

3.掌握對象資源管理器建立查詢、索引和視圖的方法;

二、實驗準備

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

2.了解查詢、索引和視圖的概念；

3.了解各類常用函數(shù)的含義。

三、實驗內(nèi)容

(-)SQL查詢功能

使用提供的studentdb數(shù)據(jù)庫文獻，先附加到目錄樹中，再完畢下列題

目,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%▼?

口結(jié)果二|消息

St__NameSt_SexSt_ID

1j主，j'雍|里2602060106

圖5-1

(2)查詢?nèi)w學生的情況，查詢結(jié)構(gòu)按班級降序排列，同一班級再按學號升序,

并將結(jié)果存入新表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關(guān)鍵字，所以選擇用其他方式。

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%

二］結(jié)果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%一

口結(jié)果消息

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)

□結(jié)果,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

□結(jié)果消息

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

國結(jié)果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)查詢每個學生所選課程的最高成績，規(guī)定列出學號，姓名，課程編號和分數(shù)。

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%，

口結(jié)果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

⑵查詢所有學生的總成績，規(guī)定列出學號、姓名、總成績，沒有選修課程的

學生總成績?yōu)榭铡?/p>

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%▼?

□結(jié)果二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)查詢“大學計算機基礎(chǔ)”課程考試成績前三名的學生姓名和成績。

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大學計算機基礎(chǔ)'

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

⑷將s_c_info中的score列的值轉(zhuǎn)為等級制輸出，即60分以下顯示為

“不及格及60?69分顯示“及格”，70?79分顯示“中檔”，80~81顯

示“良好”，90?100顯示“優(yōu)秀二規(guī)定輸出學號、姓名、課程名、成績

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

selectst_info.st_id,st_name,c_name,成績等級二

case

whenscore〉二90then'優(yōu)秀'

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'優(yōu)秀’

whenscore>=80then‘良好’

whenscore>=70then‘中等’

whenscore>=60then'及格’

whenscore<60then'不及格’

end

froms_c_info,st_info,c_info

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

100%一

□結(jié)果消息

st_dst_namec_name成績等級

12603項108.徐文文C語言程序設(shè)計基甜及格

20603060109黃正剛C語言程序設(shè)計基酬中等

30603060110張紅飛C語言程序設(shè)計基礎(chǔ)不及格

40603060111曾莉娟C語言程序設(shè)計基礎(chǔ)優(yōu)秀

52001050105鄧紅艷大學計算機基礎(chǔ)良好

62001050105鄧紅艷大學計算機基礎(chǔ)實蛉優(yōu)秀

72001050106金第大學計算機基礎(chǔ)良好

82001050106金萍大學計算機基礎(chǔ)實驗優(yōu)秀

92001050107吳中華大學計算機基礎(chǔ)中等

102001050107吳中華大學計算機基礎(chǔ)實蛉中等

112602060105楊平娟體育中等

122602060106王小維體育優(yōu)秀

132602060107劉小玲體育優(yōu)秀

142602060108何邵陽體育良好

圖5-11

（二）SQL的增刪改功能

在實驗四建立的studb數(shù)據(jù)庫中，寫SQL語句實現(xiàn)增刪改功能。

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_數(shù)學','530.0'

浙江杭州:NULL)

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

1HinsertS

2values('s3'張明華里1995/08/2100:00:00.000F/MA一數(shù)學‘，'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表中將課程名為“數(shù)據(jù)庫”的學分更改為3。

updateCsetceredit=73'

wherecname='數(shù)據(jù)庫，

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

1EupdateCsetccredit=,3>

2wherecname二‘數(shù)據(jù)庫’

100%一

由肖息

(1行受影響)

圖5-15

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

enocnameccreditepno

?Cl高等數(shù)學7.2NULL

C2數(shù)據(jù)庫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,狀態(tài)。，第1行

DELETE語句與REFERENCE約束"FK_SCS"沖突。該沖突發(fā)生于數(shù)據(jù)庫“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數(shù)據(jù)庫中，分別用對象資源管理器和SQL語言定義索引

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

看聚集的效果。

-；新建案引

i＞就緒

區(qū)鹿本,的幫助

衰名CD：

、案引名稱⑻：

^x_tname

索引姆兇:

雌

睚一?

素弓i鍵列|

酬除(R)|

上移兇

W(D)

瑕取消幫助

圖5-21

(&1^3-J

圖5-22

1.使用SQL語言定義TC表的(tno,eno)列上的復合索引ix_tc,tno列設(shè)

為升序，eno列設(shè)為降序

先增長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數(shù)據(jù)庫中操作。

1.在對象資源管理中建立視圖v_s_c,列出所有學生所選課程的成績:學號,

姓名，班級名，課程號，課程名，成績。

8(SQLServer12.0.5000.0-PC-20160924WFD

2□數(shù)據(jù)庫

a□系統(tǒng)卦據(jù)庫

@□州庫快照

國［JReportServer

田|JReportServerTempDB

BUstudb

田□數(shù)據(jù)庫關(guān)系圖

國立表

一」寇r新建視圖（N）“.

（+）

圖5-25

添加表

圖5-26

PC-20160924WFDLstudb-dbo.View_l*

怖

脩

□為

□F*育

□*

回50g□

二

回El□sal

05口s

口

IEe

口ccrsexcor

CPInborn色

列別名表城出排序類型排序,頓序或.

snoExpr2S團

snameExpr3s

cnamec