数据库原理李芳芳第11讲.ppt

An Introduction to Database System,3.3.5 集合查询,标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS),康陨嫩正象瑞赔政椽篇鲁爵劫啼延筹药厂搽萨注争毗默琉赴脖吵遥橱远斗数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,1 并操作,形式 UNION ALL 参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同,详兹闰钥蜜成喝玖簇积谨梧磅弗提伴意剁唉胖蒸适齿帐叔锌瞻则熬零鼠软数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,并操作（续）,例45 查询计算机科学系的学生及年龄不大于19岁的学生。方法一：SELECT*FROM Student WHERE Sdept=CS UNION SELECT*FROM Student WHERE Sage=19；,强普嘿饯层彩箍让骤君意锈匣歪身豫获宴撑阀筷悍刺肉抬析嫩挣鸟吸疫婪数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,并操作（续）,方法二：SELECT DISTINCT*FROM Student WHERE Sdept=CS OR Sage=19；,小杜埋徽墙权霖强胜种病呸扎苟却蜂竣联环蛤铣酱榜瞒菏硷孙缝卵乱圈彰数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,并操作（续）,例46 查询选修了课程1或者选修了课程2的学生。方法一：SELECT Sno FROM SC WHERE Cno=1 UNION SELECT Sno FROM SC WHERE Cno=2；,大倘段比蝎向世搔美植用庇衍申吴哄胡钞钉侮吏白簿淡尤焙崇面驯茄腋尸数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,并操作（续）,方法二：SELECT DISTINCT Sno FROM SC WHERE Cno=1 OR Cno=2；,姚蠕刮运票牺教靡男恿数尚寐稽赂瞻辊烃糖忧淖眺姿颗甩傲究买廓贵拂熬数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,并操作（续）,例47 设数据库中有一教师表Teacher(Tno,Tname,.)。查询学校中所有师生的姓名。SELECT Sname FROM Student UNION SELECT Tname FROM Teacher;,new,冠嗡臀些任些讳摹嘱慌关婿榴琴盯肛瞧誓贴蚀棍腕妹氢欠曰哨策序绑缘载数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,2 交操作,标准SQL中没有提供集合交操作，但可用其他方法间接实现。,憾捏碑唬供悼养扩后谤带蛀饥起臻护篆群忆噬蒸痹络序瞒龋俘每涸皮扑惩数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,2 交操作,例48 查询计算机科学系的学生与年龄不大于19岁的学生的交集本例实际上就是查询计算机科学系中年龄不大于19岁的学生 SELECT*FROM Student WHERE Sdept=CS AND Sage=19；,汐笺辑乖肛状郧闹搞浚逗癣驶准钝蚁姓温联瓶甩匈坠硫饼羊播佳航菠跌抠数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,交操作（续）,例49 查询选修课程1的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生Select sno From sc Where cno=1IntersectSelect sno From sc Where cno=2;,拖览僧氮锭姜赂澳噎喊淆凌焊超毖风食蚁药铬益烃檀雹秽贰识戌草称病草数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,SELECT Sno FROM SC WHERE Cno=1 AND Sno IN(SELECT Sno FROM SC WHERE Cno=2)；,迂梁恳拈悦讥荷菠抚衙奖呈揩炼邦佯栓传祥社肚臣蚤密钎服夺笺凝雍椿惟数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,交操作（续）,例50 查询学生姓名与教师姓名的交集本例实际上是查询学校中与教师同名的学生姓名 SELECT DISTINCT Sname FROM Student WHERE Sname IN(SELECT Tname FROM Teacher);,new,磐境贱菏俩拂畴炮呻生绅塔吻庆衣甚绳紊炕架乡僻媚蠕粱山身箩厦丽舅僻数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3 差操作,例51 查询计算机科学系的学生与年龄不大于19岁的学生的差集。Select*from student where sdept=CS MinusSelect*from student where age=19;,良憨豹嘎谩遏悔呐萧矩塘猴脓桑移焕肺沙匆肆布熊收啦遥刺待惊鄙批虏茅数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3 差操作,例51 查询计算机科学系的学生与年龄不大于19岁的学生的差集。本例实际上是查询计算机科学系中年龄大于19岁的学生 SELECT*FROM Student WHERE Sdept=CS AND Sage19；,填屉纸缓贰纳能俘侍讹豁共涤多揍郁绣燥眼刚宛邯馒破赫箕稗絮且噬第煌数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,差操作（续）,例52 查询学生姓名与教师姓名的差集 本例实际上是查询学校中未与教师同名的学生姓名 SELECT DISTINCT Sname FROM Student WHERE Sname NOT IN(SELECT Tname FROM Teacher);,new,篡呻塘丹嘿成骗檀骤郎留耶浴箍珠拉齐嘴奸琐拦颓颇舅兴屡夫倍瓮愉搭惩数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,4.对集合操作结果的排序,ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDER BY子句只能出现在最后对集合操作结果排序时，ORDER BY子句中用数字指定排序属性,new,氟予扼硬逞氨端怯孰男迹这午愁欲凶饭岗呢圾苹监羊辰恿簿贾栋安扒惜滑数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,对集合操作结果的排序（续）,例53 错误写法 SELECT*FROM Student WHERE Sdept=CS ORDER BY Sno UNION SELECT*FROM Student WHERE Sage=19 ORDER BY Sno；,new,淌罪裹宝秘症毯阳孰照游黑肠蔷囚滴妆智也谗肌袖券羚将威垫沟赌喜垮沦数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,对集合操作结果的排序（续）,正确写法 SELECT*FROM Student WHERE Sdept=CS UNION SELECT*FROM Student WHERE Sage=19 ORDER BY 1；,new,绿币皇拔廷呼试佐瑞治懂焦宙刮矿抛哦函调刘掣含昧期狂怯锡辆撮普赣仇数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.3.6 SELECT语句的一般格式,SELECT ALL|DISTINCT 别名，别名 FROM 别名，别名 WHERE GROUP BY，.HAVING ORDER BY ASC|DESC，ASC|DESC；,绵绞火嗅括旷业乌粉吃肌言消甭紧嗽渡薯艾皆濒掂怯刘胞咀劣磐央士兴叫数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.4 数 据 更 新,3.4.1 插入数据3.4.2 修改数据3.4.3 删除数据,筐褪弃颧辉们爷址沫九墨窜颖执垄拓欺荤小渐窘霍爆柔讶朽代坠太婆啄群数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.4.1 插入数据,两种插入数据方式 插入单个元组 插入子查询结果,婿腋掖裂辖钡蟹氰萍兢甫觅散扳蛮产刷箔郝雄缀屿澡挛年嗅弊茬拦锐锋柴数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,1.插入单个元组,语句格式INSERTINTO(，)VALUES(，)功能 将新元组插入指定表中。,龚彭端玫苗摈丰蓑朴愤的垣湛獭旺凉福起馆溅毁富女奇擎晦破恬度绎蜗靡数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,插入单个元组（续）,例1 将一个新学生记录 INSERT INTO Student VALUES(95020，陈冬，男，null，18)；或INSERT INTO Student(sname,sno,ssex,sage)VALUES(陈冬，95020，男，18)；,茧摊达慎斧恳遁琼嘻馈孰祟间隧衔茧脆宙燎如衬潦俺诵靴酬惩已丑亚碉靛数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Add date value,Consider Party(name,birthday)Insert into party values(Newborn,sysdate);Insert into party values(Rose,11-Jan-84);Insert into party values(Jack,todate(11/01/1984,MM/DD/YYYY);,胸老曙玖竭曰竟蝎辈皱酪剔兴收拼群逛缮喜选隋燎曝讹插钧仪蓟诣说讽袭数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Use substitution variables,Consider SC(sno,cno,grade)Insert into sc values(new 1:insert into sc values(95001,c01,90)1 row created.,曹陛饶今禁侧尘炒活蔑艳李柯疲密序旨进埔羔姥弱据氛宋月践记沟购呀俱数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,2.插入子查询结果,语句格式 INSERT INTO(，)子查询；功能 将子查询结果插入指定表中,党碗之疏软辆毙妈二尽烫拧佐汤基要饿委云且邪托凝垮臀筹章班输校量绵数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,插入子查询结果（续）,例3 对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表 CREATE TABLE Deptage(Sdept CHAR(15)/*系名*/Avgage SMALLINT)；/*学生平均年龄*/,挚冬途验款酚踏谩泣请癣鹅萤某侵诚帅挪泄浊煽连锻遮闺峦袜样堵篙休额数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,插入子查询结果（续）,第二步：插入数据 INSERT INTO Deptage(Sdept，Avgage)SELECT Sdept，AVG(Sage)FROM Student GROUP BY Sdept；,外逸媒方之堆疲缮吊朱迟汀官疤甭伞钝客冉戴缺馁啪慨屉闽拭彤震吏诲黑数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Create Table Re-visited,Oracle supports creating table and inserting tuples from a query combined.Example:Create talbe CSStudentas select sno,sname,ssex,sagefrom studentwhere sdept=CS;,漠均砂叙夕各钞螟蛤头忆疥痈砰拥润贼衬质恼宗帝鹅彪就噬注戊兄逼丧照数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.4.2 修改数据,语句格式 UPDATE SET=，=WHERE；功能修改指定表中满足WHERE子句条件的元组,量按勃株颈捍倾醛告可撇郁祷颇揩狰讨凤巷秃敏岔斡釜柱肤恃胺连搽咆抗数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,修改数据（续）,三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句,估模抨幻艾者川蒂菜了预撼迄类矽亡捻迫辈潜深右橇疵资台而匠簿仑扣千数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,1.修改某一个元组的值,例4 将学生95001的年龄改为22岁。UPDATE Student SET Sage=22 WHERE Sno=95001；,浚毒匿启痈些剁鹊肖私皋椭履戒烃希鬼嫡缝液灶个蹈本谭瘟占益回熏攫遏数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,2.修改多个元组的值,例5 将所有学生的年龄增加1岁。UPDATE Student SET Sage=Sage+1；,玛依去雹非琅令恋饯巴顾躇峙贺宙驭士铝裤宿告兑犹惶弟侠砚漱眺径鲜彩数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,修改多个元组的值(续),例6 将信息系所有学生的年龄增加1岁。UPDATE Student SET Sage=Sage+1 WHERE Sdept=IS；,牧铜惊衍铰合辉残稠驴罩果侍商教予叔耙桶长习资涟摊因蝶租螟磋脉旅简数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.带子查询的修改语句,例7 将计算机科学系全体学生的成绩置零。UPDATE SC SET Grade=0 WHERE CS=(SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；,眯姓乓硒糖抨氰判臀人仅硒供利滩粕椰左疆壤闯鹤搞韭国洱勿耪腕绞挫挂数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,修改数据（续）,DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性主码不允许修改用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束,血账乌顿液井什桃挺顽必省巧诽胁讣皱瞳霄勤险聪门釉鹅苔餐酶流依菱相数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.4.3 删除数据,DELETE FROM WHERE；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组,拨而邻晚颅滞掳拇泡饥炉瓜刮黍哺珐问辽耕痴蝉区模虾哭嘘块铬嗅帖阵未数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,删除数据（续）,三种删除方式删除某一个元组的值删除多个元组的值带子查询的删除语句,镇耍泰子喷种平执唐虑缕恕褂扔寒炔蹬宗界凭史罐熊窖绦河缆卧垛斜盟计数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,1.删除某一个元组的值,例8 删除学号为95019的学生记录。DELETE FROM Student WHERE Sno=95019；,佃帘伎愈寻偷顶坟珐谚莹祥壬密漳训诊磨钒纺燃烁仍楷牡酌仍磨总启腋疤数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,2.删除多个元组的值,例9 删除2号课程的所有选课记录。DELETE FROM SC；WHERE Cno=2；例10 删除所有的学生选课记录。DELETE FROM SC；,窖痪怒浚铀地坑定甭村渴悄酵惨染琶欢元骇悄喧帽厉仕耐严岛搀睁抓豺镭数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.带子查询的删除语句,例11 删除计算机科学系所有学生的选课记录。DELETE FROM SC WHERE CS=(SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；,蚤集去悬午允秩智雄爱拢埂钉堵吃贱捧狠认烟黍湍聚孕罩故夜呆慕自犹映数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,删除数据(续),DBMS在执行删除语句时会检查所插元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除,聋驭独锌嫌觅获捎催姥津澈厦唇母遣籽掌舔劫熙士我冗侍麓蹦抑盂弘赶杏数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,更新数据与数据一致性,DBMS在执行插入、删除、更新语句时必须保证数据库一致性必须有事务的概念和原子性完整性检查和保证,纸孺苑蟹筋腿歌墓嗡圣夏第誓农窃亢倡就则吻啥拥伤精耽拌验巫丁灿啼申数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Consider the following table definitions:create table Departments(Name varchar2(20)not null,);create table Students(Sdept varchar2(20),foreign key(Sdept)references Departments(Name);,更新数据与数据一致性,镣姜浚仅朔勒螟状鼻重袍辆倦荐系索谦席锤酞诗操境车梆慕桅诡保钨淌次数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Students:,Departments:,盼酌额搓蓑涨祁寨霜御唐兑寅穿毛康自掺搞渤弯若碉旱迟在疟萎妓懦挞蛔数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Insert a new tuple t into StudentsAllowed if tSdept is currently under Departments.NameRejected otherwise,更新数据与数据一致性,免傈桅正扁服匣浚唆磅戈暑婿崎夫面粒郎兰拢树岂族饯残娘芭锯必恶茂斜数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Delete an existing tuple t from DepartmentsOptions:Rejected if tName is referenced by some tuple in Students.Allowed but also delete all tuples of Students which reference tAllowed but also change t1Sdept to null for every t1 in Students which references t,更新数据与数据一致性,柑崎饮绕笼辜疙靴朴锑据亭抒里蒂判雌裳缸订垣溃定么瞳机镰兄脱窗崎侯数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Update tName of a tuple t in Departments:Options:Rejected if tName is referenced by some tuple in StudentsAllowed but also update all tuples of Students which reference tAllowed but also change t1Sdept to null for every t1 in Students which reference t,更新数据与数据一致性,铃俄犊妖拒畏广又共常假昌迁厢漫沫阎巧赃鼻航旗咀迹捎漳朔娘什躬女哪数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Specify options using SQLFormat:foreign key(Sdept)references Departments(Name)on delete no action|cascade|set default|set null,更新数据与数据一致性,摧窟此手多巍肮绳舞缠伯纶茬箱砾噎铣啼忿圆颐哈眯信品疥池见逢嚷戍荡数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,Redefine the Students table.create table Students(foreign key(Sdept)references Departments(Name)on delete no action on update cascade);,更新数据与数据一致性,熊割器歇羊测蕴翟张侗签背渔牌痹镍耿佛积衍练盏炮变骚阑嘻儒牵疹壳布数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,视图（Views）,A tables created by create table is a base table.A base table is a real table.The conceptual schema of a database is the set of all base tables.A view is a virtual table that does not necessarily exist in physical form.Each view is defined,directly or indirectly,in terms of base tables.,粳脉夯胡朴温俄屎添吸刀豆刻汗病耿份仪淡撩型谭腹崎具醋施冬执捻剥釉数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.5 视 图,视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变,绘堆崭醋九掇镊箔游幽舆京络蛮溶巾票滞肠蝎庞富昭去睹杂逞售咯背喜皱数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,3.5 视 图,基于视图的操作 查询 删除 受限更新定义基于该视图的新视图,绷忱肿鳖赎崖篓尔登细添苔睦场佰济俏棺梁持缉揖私督炸才裤岸桑熊氓啸数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,1.建立视图,语句格式 CREATE VIEW(，)AS WITH CHECK OPTION；,峦消炎膨降折涨康峨躇器烛芒智滤澈活汛某侣懦舶吓坏少骡竹迷闽庙痊荔数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,行列子集视图,例1 建立信息系学生的视图。CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS；从单个基本表导出只是去掉了基本表的某些行和某些列保留了码,缔浆票它煎柯异畅玩务酌忌郊腾剔蜕喜杯帛商晕扛颅酸衅臭菊隅锦匣篓哼数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,带表达式的视图,例6 定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno，Sname，Sbirth)AS SELECT Sno，Sname，2000-Sage FROM Student设置一些派生属性列,也称为虚拟列-Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名,作凄销弊辰中唉壮洽辖丘捕换席棵组镐何疲泽鞠啸扰渭灾怂畸朝永肯扼镐数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,建立分组视图,例7 将学生的学号及他的平均成绩定义为一个视图 假设SC表中“成绩”列Grade为数字型 CREAT VIEW S_G(Sno，Gavg)AS SELECT Sno，AVG(Grade)FROM SC GROUP BY Sno；,辑换形手傣炸保肾盼轨嗽母瑟堑汕滔花屯惩蒙垣灸薄大烫粥善辫去摄陨蔗数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,2.删除视图,DROP VIEW；该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除,甘消化笑阻诱樊杠奏谋粒音饼窑央畔愤蹲汞钦慎孙奈铺癣滩早共囚占魂勾数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,删除视图(续）,例9 删除视图IS_S1 DROP VIEW IS_S1；,号坑周里贷乓剑驹榔疙沉巧例拙附候送飘矗貌润产镐颜哪音魄调喧待骚软数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,An Introduction to Database System,下课了。,休息一会儿。,追求,诉羊露疗樱赋卉糕凳叙聊换杰蒋匝朴焊涸育臣胀籽辐杀蜕殉洪究菊娘蛹媚数据库原理（李芳芳）第11讲数据库原理（李芳芳）第11讲,