数据库试验四SQL数据查询

1、v1.0 可编辑可修改1数据库实验四：SQL数据查询一、实验目的与要求：1.实验目的熟练掌握 SQL SELECTS 句，能运用查询语句完成各种查询。实验内容：在实验三和实验四的基础上完成查询操作，将题目中的x, y 等具体化：1、 一般简单查询(1)不带条件的查询指定字段(考虑去掉和不去掉重复值两种情况)。(2)查询某个表中的所有记录。(3)使用单个条件的简单查询。(4)使用多个条件(AND 关系)的查询。(5)使用多个条件(OR 关系)的查询。(6)使用多个条件(混合 AND 和 OF 关系)的查询。(7)使用带 NOT 运算的查询。(8)使用 BETWEEN - AND 的查询。(9)使

2、用 NOT BETWEEN - AND 的查询。(10)使用 LIKE 运算符的字符串匹配查询。(11)使用 LIKE 运算符的模板匹配查询。(12)查询空值和非空值的查询(13)结果要求排序的查询(14)查询结果按多列排序，并分别要求升序和降序的查询。(15)使用 TOP 显示前若干记录的查询。(16)使用 TOP 显示前若干记录的查询，如果有满足条件的并列记录一并显示。2、连接查询v1.0 可编辑可修改2(17)两个关系的连接查询(18)带其他查询条件的两个关系的连接查询。(19)多个关系(三个以上)的连接查询。(20)两个关系的广义笛卡尔积运算结果。(21)根据两个关系的广义笛卡尔积运算

3、结果得到两个关系进行自然连接的结果。(22)查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和 课程性质等 4 个字段。(23)查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和 课程性质等 4 个字段，要求结果中列出所有教师信息(即包括不是任 何课程责任教师的教师信息)。(24)查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和 课程性质等 4 个字段，要求结果中能够反映目前没有确定责任教师的 课程信息。(25)查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和 课程性质等 4 个字段，要求结果中既能够反映目前不是责任教师的教 师信息，又能反映目前没有

4、确定责任教师的课程信息。提示：(22)(25)分别用内连接、左连接、右连接完成，分析它们的效果3、嵌套查询(注意使用 ANY ALL 和 EXISTS(26)使用 IN 运算的简单嵌套查询。(27)使用 NOT IN 运算的简单嵌套查询。(28)使用关系运算(如等于)的简单嵌套查询。(29)使用 ANY 或 SOM 的简单嵌套查询。(30)使用 ALL 的简单嵌套查询。(31)查询院系名称含“计算机”、职称为教授、所负责教程为必修课的教 师姓名、职称、课程名称和课程学时等信息(分别用嵌套查询和连接 查询完成，分析各自的效率)。v1.0 可编辑可修改3(32) 设计两个内外层互相关的嵌套查询(3

5、3)使用 EXISTS 的嵌套查询。(34)使用 NOT EXISTS 勺嵌套查询。4、汇总和分组查询(35)使用 COUN 统计数目的查询。(36)使用 SUM 计算合计的查询。(37)一次完成求和、计数、计算平均值的查询。(38)查询所有课程的成绩都大于 60 分的学生的平均分最高的学生信息。(39)查询数据库课程的成绩大于 70 分的、所有课程平均分最高的学生信 息。(40)查询每个学生的平均成绩。(41)查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考 课程的门数。(42)查询至少有 10 门必修课程考试成绩的每个学生的平均成绩。(43)设计 2 个使用 COMPUTE -

6、BY 和 COMPUT 的查询。(44)设计 1 个使用 COMPUT 的查询。(45)设计 1 个使用 COMPUTE - BY 的查询。、实验内容1、实验原理SQL SELECT 查询语句的一般格式是：SELECT ALL | DISTINCT , FROM 表名 ,WHERE 逻辑表达式GROUP BY , HAVINGORDER BY ASC | DESC, ASC | DESC 2、实验步骤与结果v1.0 可编辑可修改4(1)调出 SQL Server2005 软件的用户界面，进入 SQL Server ManagementStudio。(2) 输入自己编好的程序。(3) 检查已输入

7、的程序正确与否。(4) 运行程序，并分析运行结果是否合理和正确。在运行时要注意当输入 不同的数据时所得到的结果是否正确。(5) 输出程序清单和运行结果。实验过程及相关程序：1、一般简单查询(1)不带条件的查询指定字段(考虑去掉和不去掉重复值两种情况)。select distinet学号 from 选课aclcct di c xTICX;宇吕fTw选JiDOT0D60D7select 学号 from 选课v1.0 可编辑可修改5QLQiuryl.iql - (la.jdinmiitrator (S4)F Ml老 g 娄号3 巴谭|-I站采J点同1i 0&12JO占D045305S滿7(W

8、7oor1011DOS1200413005U価15D07163qa(2)查询某个表中的所有记录查询教师表中的所有记录珂bQu*ry-14常尊x 1丘fTW曲沖1ri*ru-d齡齡J沽电谢必 L1墀扯皂丄莫H口.j1顼9.HI2s1鼻券冋工33 WH聊帀网工测如447蹄FITADD 00吕s3aw貢R5S2D0303:瞬能EDO cm7?口丹皱曲9*.號宜M2 BOM(3)使用单个条件的简单查询查询成绩大于 50 的学生的学号select 学号 from 选课where 成绩 50L SQLQu#ryl.iql -( (lo.minhTrilr CS4)J| Xv1.0 可编辑可修改6学号rrw

9、n锻爛二1俺 E欣績;制200 and 工资 500SQLQutryltql (l4.dlmlnli-VAC$r* X目吕61氈匕工贾一fra離师-wbsre工生:NQ i-_二资优丸叫HVin*.現1 :2筒回J涮闻I 3MD0(5)使用多个条件(OR 关系)的查询 select 工资from 教师where 工资 500EQLjQuflryljiql (lk.jdminiElsrjtjDf pH) Xg工资Ina*软师*where Xv5it*丄一她I丽莎v1.0 可编辑可修改7(6)使用多个条件(混合AN和 OR 关系)的查询select * from 学生where (院系=2 and

10、 生源=淮南)or (院系=6 and 生源=淮北)QHFJ0MO71HANaT.inMbf - d bo单fc I QkU0L7iaANBT.mMtr - d boAMI IISQLQUMyL* - gdmln IftFMor | M)E| 萝l aeleet：雲 ，隔系-V*=i生捋-准南u =r院毎-fHU生綿 1 淮jt|-二易ft ISWE_宇=RS电丄争宙严也平却左1城冷2宁霹X: LMSB松自iW S弹立社再I1L工韋“（7）使用带 NOT!算的查询select * from 学生where not （ 生源=合肥）(8) 使用 BETWEEN? ? AND? ? ?的查询 se

11、lect * from 选课where 成绩 between 86 and 93Qi-Il-2014D7i5NflT.mifiTeT -QH -20140lMkNBT.Fr-aswreryi_jql admJnfiAntiQF (34)*亍XEidtitcc * rcjtIICMi生孫音削|*n昏#苗甜呷芋芋也诽电正正間-tt-ttfifi山上呵iftxiftx北務妄曲S.-S.-亠4 4六m卷耳亠/la/la贴左毋ft凰男男立舜咻為穿s sfefe宜傥趙冉疑禺MVSSMVSS手卢2 2 4 4 5 5 G G-J-J S S銘呼常XO1QXO1Q23423456?56?G4S4v1.0 可编

12、辑可修改8Lququyl-M|l In.-dmirtifbilfir XLvte:e咸ffi 3一.70.心90业1* j刚g|1：00/ 1 fin702施02wp co? n704ooa论论30(9) 使用 NOT? ? ? BETWEEN? ? AND? ? ?的查询93.ami n Iwnt)r *)* |n选课TiDt betrwwn 70 a-d专引(10)使用 LIKE 运算符的字符串匹配查询。select * from 课程where 课程性质 like 专业 select * from选课where 成绩not betwee n 76 and円火口叭屮UUTZST*III口皓

13、兰幣v1.0 可编辑可修改90 utlvct*託* 课垠1wei甲程性质Like专j_| I_1dIM|二绪罠L占韵:jii黃任師 訓英程性13寺业樹i盤自f2143刨砸客9卜耳惟3K1ISSJ(11)使用 LIKE 运算符的模板匹配查询。select * from 院系where 办公地点 like 4_1QH ?0；-07iriNQ ravtf dbn然.f怨bQutrryU玳(ta.driaHlilHtBr (MJ护* XL atltst freaLJL朴临:地点11*-TI1!扌r：/;妬+M(12)查询空值和非空值的 select * from 教师where 工资 is null

14、select * from 教师 where 工资 is not nullv1.0 可编辑可修改10QH-20140719ANBT.master dboJR师SQLQueryLsql 0Odminiftrator (54)* 0 select * from教师I- whe re工资is not null|d1J结果匡消息丨刼师塢号院系性昂取称专业工資1rr11李凡网工3OTM221张衰 男助載网工 50COC332楚锐讲师网工200 00A42网工 4M.D0553女网管30Q.0QG女网管 S0DW774何丹 女讲师网管 5W.OO8E4网管3OTKJ(13) 结果要求排序的查询select

15、 * from 选课-升序order by 成绩学号慄糧编言成绩1JIB .UIBIIL j MlI01102 Ml_01103皿022Q4M502205DQ3013DMJ201307WM 02408)04D2409005Q150005015011DOG02012HE02和13M7017014M7017015DOB02SO1DOB0280select * from 选课-降序v1.0 可编辑可修改11order by 成绩 desc二1结果学E邊腥漏E畴憲冒聽T” W1购I2902KB”1)2BO3007017000701705006C260DOG血呦70050150gDOSfll需SEXH4

16、Q10W4J24011DG33012D03013013D02fl22014DC2J22015DOT01ID16D01们10(14)查询结果按多列排序，并分别要求升序和降序的查询。select * from 选课order by 学号，成绩v1.0 可编辑可修改12曰select * from选课LOlder toy学号庁成绩|结果j消息学号谍程偏号咸绩1f 0C1J 01102DOI-0110300202204D020220500301300030130700402408D04024D90050150WW501501100602601200602GO12007017014DC7ai701500

17、802SO009020select * from 选课order by 学号, 成绩 descQH-2014D719ANBT.master -师SQLQuerylsql - (Icdminirtrator (54*IKv1.0 可编辑可修改13QH-20140719AN盯肋站- dboM；1SQLQueryLsql (lodministrator CMJ)*El select * f reir.先1早.- order by学号# 戚绩descr 1HI邑结果Lj学号盪腥编号咸第F II IF mi I Mitillh1i01102011DS00202204002022050030130g003

18、01宾70040240&(MM024D/005015010005D1SD1100602012嘛02601300701701400701701500802301680280(15)使用 TO 显示前若干记录的查询select top 3 * from学生select：toe 3* from宰生4 |TT|ME桃至址台性蓼生漁麻畢咖住屢!1!1如嗅厠S創巴正堂7*20022剌営垢女：屋南分绞64J刚3f诙:RM合吃冰学54(16)使用 TO 显示前若干记录的查询，如果有满足条件的并列记录一并显示select top 3 * from学生v1.0 可编辑可修改14where 生源=淮南v1.

19、0 可编辑可修改15QH-J2OlO7LAIJBT.rraster - dho富忙QLQueryLsql - (1o._.dminitratOr (S4)* aelect top 3* frcm学牢Lwhe生噱“合肥$* 1Ml口结果角消息学号姓名性别生沥状态平均成皤1j 001 j1刘洪初男含肥正幫7420033何苏丹女休学543OOfl8陶亚伟男合肥45(17)两个关系的连接查询select * from学生 cross join选课aelecL *TEQKI学生croaa Jain迭i杲(18)带其他查询条件的两个关系的连接查询select 学生.学号，院系，姓名，性别，生源,课程编号，

20、成绩from 学生 join 选课*歸岳tt咅fifll百灯合JE正常平阴直诵宇号roiQlCS101j roii1201合JE正毎74an02Z3101更=iJZ74an013D4oai調合JS74(KM0240&W1龈祖&JS74(KKi册帥SD01ML含JS1LW血B07mi1姫l和M0D7 01旳mi1直把直把正幫腕80牛0011ar710Q1011010W11HEZ#74WR0?曲11EU11Ah74(XQ01120011虱8F.74世13001I#?r台杷74OT5501401TSF正常74WE 02閒15Dl正常740d7017D161174曲谊BD170022y

21、Mi01101B0022肚皈也2T9備2空UM330嚣0021.K曲4021D021螂悼t40150- - -AJirv丄*r- - -r(IfiKali 11: 50 KTMJQH= 2CTI40719AN 8FAdni-ma-sterl&onrv1.0 可编辑可修改16on 选课.学号=学生.学号v1.0 可编辑可修改17结果学号姓茗性别生漁课程编号 成绩1i colI1會肥011020022謔if*淮南022030033女合肥C13D40044第024050055楚锁男01&006蒋文松淮北02馴7D077卑马鞍山017030Ma陶亚伟臭合肥02即0011S台肥011Q1

22、0002a0220110033女台肥013012(M440240130055C15014DM&蒋文松第淮北02丸150071马鞍山C17016008a會肥0280（19 ）多个关系（三个以上）的连接查询select * from 学生，课程，选课where 学生.学号=选课.学号and 课程.课程编号=选课.课程编号生ItaME齐11.麻苧耳他姜11z*7JQ11鸟国10(11D11Q2?啓聲a?zfi换惋D020?203g丽日ff?ai1t003D13Q44昭022s左皿世DW0256就1000113Ml32005&1150S6惑n7B祖2!去业酬OKoe册77霍瓯fll1t

23、007UMt6045舅2专止它閒唸801正冲71Ql1t专址豆和O01OniwW2&402?t0020220113&4r fjiiMJ祕l:1心复1自侧1孙沁说血1d沁1訓刼2言 爭畔2赠C1n溯23*3勺国工iLia直呂含E活宕出出.吕咅E宀曰宀咅呂:岸右帚-创TDsfllD紂Mdo和钿R创1D2C兀购乩*I ?12t_?rti2 T .? I H期血嘛QK血则伽应屈冲臨皿7aTITi7ii74&5&5&*&*_1：ofll_2aoiJDOIv1.0 可编辑可修改19(22 )查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质

24、等个字段select 姓名，职称，课程名称，课程性质v1.0 可编辑可修改20from 教师 inner join 课程on 教师.教师编号=课程.责任教师二I结杲-J消息姓容猱程宕称躁程性质1!李凡高数宵业基础2助教高数专业选修|3楚锐讲师高数任意迭修4助教高教专业基础5白丽高数处共基础$助教高数专业选修卩何月渊帀处共垦础8高数专业基础(23 )查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段, 要求结果中列岀所有教师信息(即包括不是任何课程责任教师的教师信息)select 姓名，职称，课程名称，课程性质 from 教师 left joi n 课程on 教师.教师

25、编号=课程.责任教师QX 2G14O7:站忡盯皿ms dbo.X;? iQLQwofyUql课程性 班知fftijr -ifc - oir. i甩程=n 鞍师战师编号-耳程壷任载涮 i&M | _j出主1.墅召4:出2畫lb塑34诚Jt专注则5-虫二抽G741帀g4 脚(24 )查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段, 要求结果中能够反映目前没有确定责任教师的课程信息。select 姓名，职称，课程名称，课程性质from 教师 right join 课程v1.0 可编辑可修改21on 教师 .教师编号=课程.责任教师QIMPD丄*0丿IS AMU

26、1jra He-dlLcsA# QLQurylqll - (lo.dmilnlftritCir (M)rt- SfrlifiCC理咅收酝课崔名称”裸程性M尹师XUjhlon轅师教师褊号-W斋曲躺|41 491轲厂3菲莘3歸任屯乜捲郴寺业典5白朗*#丁術iftSTlI fl ?.stifl*别片业回.(25 )查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4 个字段,要求结果中既能够反映目前不是责任教师的教师信息，又能反映目前没有确定责任教师的课程信select 姓名，职称，课程名称，课程性质 from 教师 full join 课程on 课程.责任教师=教师.教师编号Q

27、H汕芒牛fQLQuerylfrqli亠(to,.dini n i tritor (S4)*i仏dtx玉名:邸T J瞬壬祜朋性&Eror教甲tnu iou课程L軀课祚，盍吁報师=数弭.魏师编匸-H!S4n寿程n哽1W；专业县2羅”3讲1帀45白閘专业曲7何丹ifttt么共赵需&(26) 使用 IN 运算的简单嵌套查询。select 教师.姓名from 教师where 教师.教师编号 in (select 课程.责任教师v1.0 可编辑可修改22from 课程where 课程性质=专业基础)QH-20140719AN3T.master -討BQ.塗里SQLQucryl.sql -

28、(Io. dministrator (54)* B selectfrum教师wheie教师教师编号(select谏程.责往教勺帀from课程-皿皀工亡謀程性质专业基础*) |(27)使用 NOT IN 运算的简单嵌套查询select 教师.姓名from 教师where 教师.教师编号 not in (select 课程.责任教师from 课程where 课程性质=专业基础)v1.0 可编辑可修改23QH-20145719ANBT-mastr - db口豳 锐LQu君ryl啊I - (lo-dministralor (54)* Q selectlfram教师汕ME教帅教师絹号ttor n (七课

29、程责任教师Irani课袒-where i棄程性质=专业基础*)4IrfL3结果匕消息II!TIHB1i张亮2楚宦白丽4刘婷5何丹（28）使用关系运算（如等于）的简单嵌套查询select 教师.姓名from 教师where 教师.教师编号=（select 课程.责任教师from 课程where 课程编号=02）v1.0 可编辑可修改select 姓名24SQLQueryLsql - (lodirinistrato-r (54)* select戒师姓名牡口砂教忡where教怖教师编号 rselect棄*呈.责住教师fToni课程亠皿课程编号=曾 |_irr结果消息|IL Ull IIU4III|1

30、i空垂(29)使用 AN 威 SOM 的简单嵌套查询select 姓名from 教师where 工资 =any(select 工资from 教师where 教师编号=20118)select * from 教师v1.0 可编辑可修改(select *25from 教师where 工资 =all（select 工资from 教师where 教师编号=20115）（31）查询院系名称含“计科系”、职称为教授、所负责教程为专业基础的教师姓名、职称、课 程名称和课程学时等信息（分别用嵌套查询和连接查询完成，分析各自的效率）select 姓名，课程名称，学时from 教师 join 课程on 责任教师=

31、教师编号 join 院系SSS300 00卍50OTO4DCM)1300 DO卜600的500腑30000社几和世旺氏uffi越Ti-“阔EttM1I-F(30) 使用 ALL 的简单嵌套查询。v1.0 可编辑可修改select * from 教师26on 院系.编号=教师.院系where 名称=计科系and 职称=教授St屯県程名称学时 iramarMBiii1 L$K.J高議E（32）设计两个内外层互相关的嵌套查询v1.0 可编辑可修改(select *27咅址直11”fj rTJLFIT3MOO221助執啦5DCJJ0332曲币RTaw.oo442黒助執460 00s5J白丽3W.Q06

32、63创疟GOD DO774诽师500.00g34Xi.OOselect 教师编号，姓名，性别，职称，工资from 教师 outwhere 工资=(select max( 工资)from 教师 innerawhere out.教师编号=inn era.教师编号)m.-.i- . i；.SQLQuer) )il tql Ercm教|ff inneraKbZM OUG数师編g-爭J币编号：I- - - - -一一- - -結耒jLii沽良工駕1TJL300 D022糕900 &J32tuts.a3凶4肺抑*0015&曰丽30000I 6町-frWWn/?何丹9D0閲S&立教提

33、300 03(33)使用 EXISTS 的嵌套查询。select *from 教师where existsv1.0 可编辑可修改select * from 教师28from 课程where 课程.责任教师=教师.教师编号）烈币旳号G茶虧11 Ji 1李凡 畀300 002 2 1网工3K00332网工200 00442陈便MD003SRfl网宜300 DOS63网官SOO 00何丹连帰500 00.毎XKOO(34)使用 NOT EXISTS 勺嵌套查询。select * from 教师 where n ot exists (select * from 课程where 课程.责任教师=教师.教

34、师编号）QH-2Dl-G7L9ANBT,mastar - dbo课程QH-20140719AN盯m日求寸-dba載师SQI匚select *九 5 教师where net existsfioni课程L铀比更课程责任教师教师.教师编号）|*TMa结果消息勒师编号院系姓容性别职称寺业工资（35）使用 COUN 统计数目的查询。v1.0 可编辑可修改29select cou nt （ 工资）from 教师结杲J消息物梆号院系姓名性别孫称羞业工资1j 1i i李ft男埶授网工3M.W2一厂i頫男 助埶网工5M-M312楚顿貝讲师网工2WK442晾播鸟肋執网工4O0.M553白丽女軸授网管则他GG3潮女

35、助轨疑GD0-00774何丹女谢币网管则閒a a斗汤雷女瓠授网管曲自1亍_i1 VdlllIllfaU（36）使用 SUM 计算合计的查询。select sum （ 工资）from 教师QH-20140719ANBT.master - Tbm课程QH-20140719ANBT,master - dbo*裁师SQLQueryksql - 0i selectsum工竇j from載！Mrri3 4亘结果I由艇宛列锐4 uiwSa_|vamuraa1! !（37）一次完成求和、计数、计算平均值的查询select * from 教师compute sum（ 工资）,avg（ 工资）v1.0 可编辑可修

36、改(select 学号 from 选课 join 课程301御_j h*siirtxft拆黑ti毬回IM 1L工缺1i 1 i李禹且聖庚阪MO M21亜豆見肪 ft 网L32侏卑帥丹网L也0.M)42臨憎卑姗阪O.KJ53Bl西丈师碾300M百2却t玄眈對网营6QQ.K7d何丹女 沖幵冋管5Q0.WJ日4隔女娴网它3WM:310B0C i酬了知的0(38)查询所有课程的成绩都大于60 分的学生的平均分最高的学生信息select学号，avg(成绩)平均成绩from 选课where 成绩60group by 成绩,学号orderby 成绩descEQLQueryl.fl - (Ifr.dminit

37、r3tor (54) select学号杏.：成绩平岛血责from选课where成绩X0ffioup by成绩”学号crdar by成绩deauII结果UJ ttB字号平均成務1! (m200770(39)查询数据库课程的成绩大于70 分的、所有课程平均分最高的学生信息select top (1) * from 学生 where 学号 inv1.0 可编辑可修改31on 选课.课程编号=课程.课程编号where 成绩70 and 课程名称=数据库)SQLQueryl.iql - (la.dministrator C54)* t&p 1* frcitit 牛LTI(select学号from

38、选课join课程on选课课程编号-i果程课程编号1-油匚氏 成绩旳口课程名粽=*数据库 7III口结果出消息L学号院系姓名性别生源状态平均咸绩(40)查询每个学生的平均成绩。select 学号，avg(成绩)as 平均成绩from 选课group by 学号v1.0 可编辑可修改32（41 ）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。select 学号，count （*）所选课程数,max（成绩）最高成绩，avg（成绩）平均成绩，min （成绩）最低成绩from 选课group by 学号钾字气1i 301210ID102om2加範20323030304血25xs2M50W6DOS初so翎D072707070ewa1BQ（42）查询至少有 1 门必修课程考试成绩的每个学生的平均成绩。select avg （成绩）from 选课where 课程编号 in（