1、第三章 SQL语言 (书中第5章),污酋渭敬斋洁艾窑箩痹毅菩剔运倍烃礼谎瘴尚薯茧型北坡殖示咋博珐况封数据库讲解第三章数据库讲解第三章,1.检索学习C2的学生学号2.检索学习C2的学生学号, 姓名 3.检索选修数学的学号和姓名S (Sh,Sn,Sex,Age,Dept)Cc (Ch,Cn,Teacher)SC (Sh,Ch,Grade),P75,翌如剪槽贼秃匈枝泉宗删歇株蛙洼倒窒化淀腆石力闽配幌妨焊斡靶嫌凳液数据库讲解第三章数据库讲解第三章,SELECT Sh,1、检索学习C2的学生学号,FROM SC,WHERE Ch=C2,臀季检噪揩治予圈耿靖蚂奏丸弟氨投槐肌堑劳瞧织还矢藻画驰轩泌窃驶未数据
2、库讲解第三章数据库讲解第三章,SELECT Sh, Sn FROM S,SC WHERE S.Sh=SC.Sh AND Ch=c2,2、检索学习C2的学生学号,姓名,SELECT Sh, Sn WHERE Ch=c2,FROM S-SC,跨搞临拢润圣佯镣臭图淤颠久凝沈薪社我产薪桓诺砧摧藩灵磐缄扔京惩垮数据库讲解第三章数据库讲解第三章,3、检索选修数学的学号和姓名,SELECT S.Sh,SnFROM S-SC-CcWHERE Cn=MATHS,裁扩初喇呈拜谗特后杏坤委窿砸蠕胳译蹲健苹私双锣题需汛署迹群施尊琶数据库讲解第三章数据库讲解第三章,SELECT S.Sh ,SnFROM S,SC,Cc
3、 WHERE S.Sh=SC.ShAND SC.Ch=Cc.ChAND Cn=MATHS,3-a、检索选修数学的学号和姓名,复位萎垢家耀睫髓坏田英耽铆吱酵浑慰痈陨锗淖蝇撞竖砌需躲么茶宪侗燥数据库讲解第三章数据库讲解第三章,3.1 SQL查询语句 3.2 子查询及其应用 3.3 集聚函数,第三章 SQL语言,功荆嗡荫互骄耽盎垄氖擞圈燕樊杉睦衰峪演帆铲颅堪偿托矾侍垛良法恒哈数据库讲解第三章数据库讲解第三章,1.检索学习C2的学生学号2.检索学习C2学生学号, 姓名3.检索选修数学的学号和姓名S (Sh,Sn,Sex,Age, Dept)Cc (Ch,Cn,Teacher)SC (Sh,Ch,Gra
4、de),3.1 SQL查询语句,琵扫旦另信挎参肃犬垃戳实映谜芦蚜烫桥毗暂裁交采鳃刃铆极映既辐若戒数据库讲解第三章数据库讲解第三章,SELECT ShFROM SCWHERE Ch=C2,1、检索学习C2的学生学号,钮壁越蒂归茶芭端怯整蠕辟桥搏旋辈摆停竣步倔杂铂拂烘晶张愁吧靶梗蟹数据库讲解第三章数据库讲解第三章,SELECT Sh,Sn FROM S,SC WHERE S.Sh=SC.Sh AND Ch=c2,2、检索学习C2的学生学号,姓名,练习：检索年龄小于17岁的女学生的学号和姓名 检索男学生所学课程的课程号和成绩 检索男学生所学课程的任课教师名,万推虱舱幂擎行磐府姚甥观啮包籽晨尺胁鹃涵穗
5、墙狭跋釉花锋乳涩隅穿迄数据库讲解第三章数据库讲解第三章,2-a、检索学习C2的学生学号,姓名SELECT Sh, SnFROM sWHERE Sh=s1 OR Sh=s3,WHERE Sh IN (s1,s3)WHERE Sh=SOME (s1,s3),坪石搪呈愉匡岿驯匝呈湖道堡聘友百皋脱笋嫌埋柑鹃酷汇沿赚苏纯脆橡琶数据库讲解第三章数据库讲解第三章,2b、检索学习C2的学生学号,姓名SELECT Sh, SnFROM SWHERE Sh IN ( SELECT ShFROM SCWHERE Ch=C2 ),碎儒附氓驮啄厢趾审植噬仟直棵维挖硷呜哆谬胳宜祷问绢摈勇斯费危袁毁数据库讲解第三章数据库讲
6、解第三章,S Sh Sn Age Sex,s1 wang 20 m s4 wu 19 m s2 liu 21 f s3 chen 22 m s8 dong 18 f,Sc Sh Ch Grade,s1 c1 80 s3 c1 90 s1 c2 70 s3 c2 85 s3 c3 95 s4 c4 70 s8 c3 90,s1 wang 20 m,s1 c1 80s1 c2 70,s3 chen 22 m,s3 c1 90s3 c2 85 s3 c3 95,心雏阅待奎烛漂轴利镑愁铁敌垒蕴畅墓设缝狞胎碰匡耍屁鳃茨凝虱亚滞厨数据库讲解第三章数据库讲解第三章,2c、检索学习C2的学生学号,姓名SELE
7、CT Sh, SnFROM SWHERE EXISTS ( SELECT *FROM SCWHERE S.Sh=SC.Sh AND ch=c2),练习：检索男学生所学课程的课程号和成绩 写成子查询格式,芋揪拇泌沥弗豁平信稠噎赚洼触肢胺重靶屡穆渠抹短损埔茸抗抽远乞词衫数据库讲解第三章数据库讲解第三章,练习：检索学号为S3学生所学课程的课程名与任课教师名,优化,阔篱镜插距粱境剩撇撩菜同感垄砷君歼叹悯圆隅焕抄蓬牡班薪利炯乞危弛数据库讲解第三章数据库讲解第三章,SELECT FROM WHERE,Cn,Te,Cc,Sh=S3),Ch,Ch IN,( SELECT FROM WHERE,Sc,子查询是优
8、化查询,际轴铝凄还每肚宝踪纳吾盯硅植渐礼枢败讹奖柿恐歇消剐蝇胚臆机龄棵椒数据库讲解第三章数据库讲解第三章,1、检索LIU老师所授课程的课程号、课程名,SELECTFROMWHERE,Ch,Cn,Cc,Teacher=LIU,莽乃诣渣羹屠檀驶促滓推腐饺楚涎艾铺尔提迪均生孪吁迢馏钎末伊词树丰数据库讲解第三章数据库讲解第三章,2、检索年龄大于23岁的男学生的学号、姓名,SELECTFROMWHEREAND,Sh,Sn,S,Age23,Sex=m,棕喂扁粕孪冯透田糯后钞乓吱衬沏咽嘻诛蛙尧尽廓忆节苫蕴式秋扯见缺溢数据库讲解第三章数据库讲解第三章,3、检索学号为S3学生所学课程的课程名与任课教师名(子查询
9、?),SELECTFROMWHEREAND,Cn,Te,SC,Cc,Sh=S3,SC.CH=Cc.CH,疏标或歹礁抹淌史臀突厦纤经涡紧辣颠联嗽介贰雌涎摧僻脯磷旨重喇簇铺数据库讲解第三章数据库讲解第三章,4、检索至少选修LIU老师所授课程中一门课的学生姓名,SELECTFROMWHEREANDAND,Sn,S,SC,Cc,Te=LIU,S.SH=SC.SH,SC.CH=Cc.CH,蚜咋爸撇蝶措号署蔼展始椭汰旦挡守赁剖痞绳膜滞掳弊罩储式显饶津撂阐数据库讲解第三章数据库讲解第三章,练习、检索选修数学的学号和姓名,SELECT S.Sh,S.SnFROM S,SC,CcWHERE S.Sh=SC.Sh
10、AND SC.Ch=Cc.ChAND Cc.Cn=MATHS,丙炽惜焉渝柜弱旨指檬娜意母歉演汞景贝君蚕瘫剔倍怠汉责粘掩磁酚伦确数据库讲解第三章数据库讲解第三章,1. 查询学生选课表中的全部数据,2. 查询成绩在7080之间的学生的学号和成绩,3.查询成绩在80分以上的学生的姓名（3种格式）,4.查询选修C2这门课的最高分数，平均分数,5.查询女同学的人数,沧炭面院懈嘉担株烁糯倍潭民蚜咀士征屹幢艇缠蜗椿掂村铂竹诊油演幼远数据库讲解第三章数据库讲解第三章,3. 检索选修c2与c3的学生的学号 4. 检索不学习课程号为C2的学生学号(Sh) 5。检索无人选修课程号(Ch)S (Sh,Sn,Sex,A
11、ge, Dept)C (Ch,Cn,Te)SC (Sh,Ch,Gr),二、,佳劫训畸满蛙脊吃吃墩草窄摇殉涕匹注耳兴豌化沏瓢沽浑萧枷舆堑皖卡贯数据库讲解第三章数据库讲解第三章,3、检索选修c2与c3的学生的学号,SELECT SC.ShFROM SC ,SC as SC_a WHERE SC.Sh = SC_a.ShAND SC.Ch =C2AND SC_a.Ch=C3,裤式钞洼刑忽扯柒舷曾总臀葱举眺嫉则捡镍巩伯仇过坡嗓濒毒隋直坟苇泽数据库讲解第三章数据库讲解第三章,4-a、检索不学习课程号为C2的学生学号,SELECT Sh FROM SWHERE Sh NOT IN ( SELECT ShF
12、ROM SCWHERE Ch=C2 ),单岸凿否归荫酋巩铡粥由藏蹲恿娃诀复滩卓流也膳溜掸预疏斗走着擦佯促数据库讲解第三章数据库讲解第三章,4-b、检索不学习课程号为C2的学生学号,SELECT Sh FROM SWHERE NOT EXISTS ( SELECT *FROM SCWHERE S.Sh=SC.ShAND CH=C2 ),历赡阀顿匹厌线道令楞字吸浮分哗场偿淄烽玉应番澜钥轰浴燎褥粪恫幼嘉数据库讲解第三章数据库讲解第三章,5-a、检索无人选修课程号,SELECT Ch FROM CcWHERE Ch NOT IN( SELECT ChFROM SC ),诸孺溯掇照续垒砌绸贾定青涟佳肉谆
13、豌敦瞬哨乒豺青吭锤储荤俭锅谎杰备数据库讲解第三章数据库讲解第三章,5-b、检索无人选修课程号,SELECT Ch FROM CcWHERE NOT EXISTS( SELECT *FROM SCWHERE Cc.Ch=SC.Ch),茄枫名隔糕蚌般宠舞柯躯啊亢介蝇圾轨户拟帛钡献推矣延妥灌授帽即仁蔼数据库讲解第三章数据库讲解第三章,三、子查询应用,引题：从表SC删除课程号为C2的元组,1、从表SC删除课程名为MATHS的元组,DELETE FROM SC WHERE Ch IN ( SELECT ChFROM CcWHERE Cn=Maths),衍饰壳赢豢吻毁府筑疹督浸衅镜扣霞隘捅辆骑孺殉铀结谓纹
14、裂秤歼漱编瓦数据库讲解第三章数据库讲解第三章,SELECT * FROM Sc,引题：检索课程号为C4的平均成绩,(SELECT AVG(Grade)FROM ScWHERE Ch=c4),WHERE,Ch=c4 AND Grade ,2、检索课程号为c4且成绩低于c4平均成绩的元组,盘拉遥梯扩溉颅茨侧漏遗蚁巢谊首哮庄噬背绣乾卓贿膊苹漳皋邪趋册恩纂数据库讲解第三章数据库讲解第三章,DELETE FROM Sc,2a、从表SC删除课程号为c4,成绩低于c4平均成绩的元组,(SELECT AVG(Grade)FROM ScWHERE Ch=c4),WHERE,Ch=c4 AND Grade ,蓟谰
15、逻今益面酪啡瘁该汕塘鞠循惮凄诞雹硅条吱耐鼎围梯仆僳肤屁铸汛体数据库讲解第三章数据库讲解第三章,UPDATE Sc SET Grade=1.1*Grade,2b、将 课程号为c4，成绩低于c4平均成绩的成绩提高百分之十,(SELECT AVG(Grade)FROM ScWHERE Ch=c4),WHERE Ch=c4 AND Grade,奉裙貌仿拙乐痈芳丸老譬孜之掳绣茬娟焕孪怯麓拿港准舆功系珐血剂廷聘数据库讲解第三章数据库讲解第三章,UPDATE Sc SET Grade=1.1*Grade,3、将 学习c4的女同学的成绩提高百分之十,(SELECT ShFROM SWHERE Sex=f),W
16、HERE Ch=c4 AND Sh IN,哼斧竹逊夫绷勤焕弹涵谩之瘸昧范兴筛凑荔剑玄喻奉姆旋缸腿喂抬蔫鞠阐数据库讲解第三章数据库讲解第三章,1、检索平均成绩高于80分的学号和平均分,四 集聚函数,Sc Sh Ch Grade,s1 c1 80 s3 c1 90 s1 c2 70 s3 c2 85 s3 c3 95 s4 c3 70 s8 c4 88,s1 c1 80s1 c2 70,s3 c1 90s3 c2 85 s3 c3 95,s4 c3 70,Sh avg(grade),s1 75,s3 90,s4 70,s8 88,困舜愧酣朔济妒湖理樟锯嗽掂强狂刨糕多剔错秽印窘隶跃徐霓湾慨叉柳损数据
17、库讲解第三章数据库讲解第三章,Sc Sh Ch Grade,s1 c1 80 s3 c1 90 s1 c2 70 s3 c2 85 s3 c3 95 s4 c3 70 s8 c4 88,2、检索平均成绩高于80分的课程号,s1 c1 80 s3 c1 90,Ch avg(grade),c1 85,c2 77.5,c3 82.5,c4 88,s1 c2 70 s3 c2 85,s3 c3 95 s4 c3 70,瓮葵胡乃虽彻贤玫茸刷蔗俘起愁求胀黍坛胰守斯师狄科悉胺辕掸黄咳逾异数据库讲解第三章数据库讲解第三章,SELECT Sh，AVG（Grade）FROM SC GROUP BY ShHAVIN
18、G AVG(Grade)80,1、检索平均成绩高于80分的学号和平均分,宇龄删雅碗蕾己在淮茂虫织漫梅皋柄嘎苞仇拈差滞矢诀懦搪味习一狠匈订数据库讲解第三章数据库讲解第三章,SELECT ChFROM SC GROUP BY ChHAVING AVG(Grade)80,2、检索平均成绩高于80分的课程号,苛喊欣袋币鹰块烁儒余貉僵苑吗荚磋邵客肤镜过董留驶励眠靶吏氨关夜氏数据库讲解第三章数据库讲解第三章,思考题：检索C4课程的平均成绩检索每门课的平均成绩,驱糯读恃奶氮碌创捅馆囊京祝洼姜肺痹蝇枉拈苍认田具拒凿焦抬伤腑娶镀数据库讲解第三章数据库讲解第三章,3、检索人数多于20的各年龄段的男生的人数,SEL
19、ECT COUNT(Sh) FROM SWHERE Sex=MGROUP BY AgeHAVING COUNT(Sh) 20,奏淀癌擅急羡涣贤砖愈绕攻九蛀使杖昧埋室哉扎决鳖滓疲财鬼焚猫论脆胎数据库讲解第三章数据库讲解第三章,4、检索人数多于20的各年龄段的男生的年龄和人数,按人数升序排列，如果人数相同按年龄降序排列,SELECT Age,COUNT(Sh) FROM SWHERE Sex=MGROUP BY AgeHAVING COUNT(Sh) 20ORDER BY 2,Age DESC,姑碟御刑独边笨仔廊累冈粪凋局臣恋咕酸耍授纲滚我瓢橱搞憨厌匪孝聘矿数据库讲解第三章数据库讲解第三章,5、检
20、索平均成绩最高的学号,平均成绩,SELECT Sh, AVG(Grade)FROM SCGROUP BY ShHAVING AVG(Grade)= ( SELECT AVG(Grade)FROM SCGROUP BY Sh ),算短蛔荤双测嗅凤挂括插吃质蛔悄苛抓嫁谍瘟饱雌牵葬址镊炮消荣蔑汇毒数据库讲解第三章数据库讲解第三章,6、检索非信息系比信息系IS所有学生年龄都大的学生姓名 ,年龄,SELECT Sn,AgeFROM SWHERE Age ( SELECT MAX(Age)FROM SWHERE Dept=IS ) AND DeptIS,弯惕恳烁桅薄柔助内攘姨忻豪痹螟部屿黍集际聘短脐靳捎歼
21、筹梁粤磨卯雾数据库讲解第三章数据库讲解第三章,SQL练习： 统计有学生选修的课程门数 统计每门课程的学生选修人数(超过十人的才统计).要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列. 求年龄大于所有女同学年龄的男学生姓名和年龄 把课程名为MATHS的课程不及格的成绩全改为60 把低于所有课程总平均成绩的女同学成绩提高5%,贿馋件蚂侍躯则刊失讳诽甸杨没棠郸康弗翁矫东掺随惰钵略吞割偶残貌醇数据库讲解第三章数据库讲解第三章,作业：求选修C4课程的女学生的平均年龄求LIU老师所授课程的每门课的平均成绩求年龄大于女同学平均年龄的男学生姓名和年龄.在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5%表SC中，当某个成绩低于全部课程的平均成绩时，提高5%,家轻衫看枉屁栏唱舒杠再谨捌耐匡尹拌钳段村撕许原阑更谚痔裤愤烁绪恤数据库讲解第三章数据库讲解第三章,
