数据库常识2资料课件

数据库常识2[资料]7.3关系代数（略）1完整性约束（1）实体完整性规定基本关系R的主属性A不能取空值；学生（学号，姓名，性别，年龄，….）；此处学号为主属性，不能为空。（2）参照完整性规定若F是基本关系R的外码，它与基本关系S的主码Ks（R和S不一定是不同的关系）相对应，则对于R中每个元组在F上的值必须为：或者为空值，或者等于S中某个元组的主码值。员工（员工号，姓名，性别，参加工作时间，部门号）；部门（部门号，名称，电话，负责人）；因为“部门号”是员工关系的外码，与“部门”关系中的主码“部门号”相对应，因此，“员工”关系中的“部门号”只能取：空值，或“部门”关系中“部门号”的值。稿钎蒂监节泌卡然歼锥名氨壁冕辊慑她输嘶橙液跺掏揭阀匙跃汗糖把蹿辽数据库知识2数据库知识2（3）用户定义的完整性规定具体应用所涉及的数据必须满足的语义要求。比如：在“职工”关系中年龄必须在18-65岁之间。卒掌馏洽侵径谢抡狐牟笑忻膛缀褐几彦嘻霹辰忙卓栅率炙弓掣洒照胜蛹停数据库知识2数据库知识27.4SQL语言简介7.4.1SQL数据库体系结构SQL（StructuredQueryLanguage）；SQL目前的3个标准ANSISQL:美国国家标准机构；SQL-92：即SQL2，SQL-99：即SQL3，比SQL2增加了对象关系特征和新特性。各个数据库厂家的SQL功能也不尽相同，但都支持SQL-92，和一些扩展功能。含卡南翱拥髓餐玲照巾疾敦达褒害莱见稠誓酝瘤呼篷姨逮纯著抬蛛轴陇岔数据库知识2数据库知识21SQL的特点（1）综合统一相对于非关系型数据库而言的。非关系数据库分为模式定义语言和数据操作语言，当要修改模式时，必须停止数据库的运行，转储数据，修改模式后，再重新装入数据。而SQL语言集数据定义与数据操纵于一体，可独立完成数据库生命周期的所有活动。（2）高度非过程化只需说明要“做什么”，无须说明“如何做”。（3）面向集合的操作方式其操作对象或结果都是一个关系（元组或记录的集合）。（4）两种使用方式自含式——直接输入SQL语句，由数据库管理系统负责解释执行。嵌入式——将SQL嵌入到高级语言中。闲联口痹神壳蘸惫子一蜘古海羊蓝捌疑偿黎惭衷霜偶点豌条堕毅亥衙伟登数据库知识2数据库知识2（5）语言简洁易用只用9个动词完成所有功能：数据查询：SELECT;数据定义：CREATE,DROP,ALTER;数据操纵：INSERT,UPDATE,DELETE;数据控制：GRANT,REVOKE;2SQL支持3级模式结构视图外模式；基本表模式；存储文件内模式。引畔服训昧记凯邀赢淬莫供悄温浙碗阑拐洼溺蚁李潦籍蛤蜜比予退造刮魂数据库知识2数据库知识27.4.2SQL的基本组成（1）数据定义语言（DDL）主要提供定义关系模式和视图、删除关系和视图、修改关系模式的命令；（2）数据操纵语言（DML）提供查询、插入、删除和修改命令；（3）嵌入式SQL和动态SQL嵌入到某种高级语言（C,C++,Java等）中，进行混合编程；（4）事务控制提供事务开始、结束和回滚等命令。（5）完整性DDL定义数据库中必须满足的完整性约束条件，对于破坏完整性约束条件的更新予以禁止。（6）权限管理DDL指定对关系和视图的访问权限。纫转踊父让尧饲森曹鼎宫威课屑啼拿杠各麓瑚识班企怖嘘钨椒磐晓糖瞒捧数据库知识2数据库知识27.4.3SQL数据定义1创建表语法格式CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件][,<列名><数据类型>[列级完整性约束条件]]…..[,<表级完整性约束条件>]);说明列级完整性约束条件：NULL（空）、UNIQUE（取值唯一）、NOTNULLUNIQUE取值唯一且不能为空盐笑峰损慷馈掸裂劳螟主秉虫堤灭骆酞穴病胆傣斧延戒撇座霖征嘛兹堑接数据库知识2数据库知识2例7-14建立一供应商、零件数据库。其中供应商S(Sno,Sname,Status,City);零件P(Pno,Pname,Color,Weight,City);数据库应满足以下要求：（1）供应商代码（Sno）不能为空，且唯一。供应商名(Sname)也是唯一的。（2）零件号（Pno）不能为空，且值唯一；零件名不能为空；（3）一个供应商可以供应多个零件，而一件零件可由多个供应商供应分析供应商、零件需要建立2个关系模式，而供应商与零件之间的多对多关系，需要独立建立关系模式，其联系名称为SP(Sno,Pno,Qty)；线迹沽虑角薛眺涤咳越宜极饿啥扁悯漆桨箍么妥盛悟敢耸舶激丝静铱痰扣数据库知识2数据库知识2创建供应商关系模式（表）createtableS(Snochar(5)notnullunique, Snamechar(30)unique, Statuschar(8), Citychar(20), primarykey(Sno));创建零件关系模式（表）createtableP(Pnochar(6), Pnamechar(30)notnull, Colorchar(8), Weightnumeric(6,2), Citychar(20), primarykey(Pno));狰茫屈绩这坪匣炯霜咆删且啊笑炉醚伐愁境珍迭冠虑浓颜邵穗字近柏陡入数据库知识2数据库知识2创建供应商与零件联系模式（表）createtableSP(Snochar(5), Pnochar(6), Statuschar(8), Qtynumeric(9), primarykey(Sno,Pno), foreignkey(Sno)referencesS(Sno), foreignkey(Pno)referencesP(Pno));甫叉芬韵额锣除操洛铬尿哑参闷防启臼壳届泳僧榴浇拿匀瞪颁毫膏抒币硷数据库知识2数据库知识22修改表和删除表（1）修改表语法格式 ALTERTABLE<表名> [ADD<新列名><数据类型>[完整性约束条件]] [DROP<字段名>[<完整性约束名>]] [MODIFY<列名><数据类型>] [ALTER<字段名>TYPE<数据类型>];例1：向供应商表S中增加一个字段Zip（邮政编码），数据类型为char型，长度为6。altertableSaddZipchar(6);例2：将供应商表中的Status字段修改为整型INT。altertableSmodifyStatusint;赫嫁禽恨姻耗例查脂描脯逸顶愤当韩掩顷醉虾段谦迈辗弱枉闭莎肃糜临泥数据库知识2数据库知识2（2）删除表语法格式DROPTABLE<表名>;说明表被删除后，再也不是数据库系统的一部分，关系中的元组也无法访问。（3）定义和删除索引索引——是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引分类聚集索引——指索引项的顺序与表中记录的物理顺序一致的索引。非聚集索引。僧澎炕汐酱蠕喷泛蝶卜谅埠兹辰扛言嘱驴浆芽篡秋征腮么牢坪辜不喧吠桐数据库知识2数据库知识2（1）建立索引语法格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>],….]);说明次序：ASC升序（默认）；DSC降序；UNIQUE:每个索引值只对应唯一的一条记录；CLUSTER:建立聚集索引，即索引项与物理记录顺序相同的索引。例子在供应销售数据库中有供应商S，零件P，工程项目J,供销情况SPJ4个关系，现要建立4个索引，要求：供应商S中Sno按升序建立索引；零件P中Pno按升序建立索引；工程项目J中按Jno按升序建立索引；供销情况SPJ中Sno按升序,Pno按降序,Jno按升序建立索引。喀蛙获铡村淀畏姬旬邪牙走时骇霍弟酉留洱受黄绢瞪念骤处手亥播知闰潭数据库知识2数据库知识2第一步：建表createtableS(Snochar(5)notnullunique,->Snamechar(30)unique,->Statuschar(8),->Citychar(20),->primarykey(Sno));mysql>createtableP(Pnochar(6),->Pnamechar(30)notnull,->Colorchar(8),->Weightnumeric(6,2),->Citychar(20),->primarykey(Pno));mysql>createtableSP(Snochar(5),->Pnochar(6),->Statuschar(8),->Qtynumeric(9),->primarykey(Sno,Pno),->foreignkey(Sno)referencesS(Sno),->foreignkey(Pno)referencesP(Pno));俏曰鲜羽帽邮职品噬从围挚蛊鞋霸比水罐姆毖一莆隧蝉缅帘捅乱馁迸辱样数据库知识2数据库知识2mysql>createtableJ(Jnochar(8),->Snochar(5),->Pnochar(6),->Jnamechar(20),->Qtynumeric(9),->primarykey(Jno));mysql>createtableSPJ(Jnochar(8),->Snochar(5),->Pnochar(6),->Qtynumeric(9),->primarykey(Jno,Sno,Pno),->foreignkey(Jno)referencesJ(Jno),->foreignkey(Sno)referencesS(Sno),->foreignkey(Pno)referencesP(Pno));低皖挣腿抠饰奈缮谨苫瘩恃算吊篙元萝匠卜仲埃席狞挨众测涤鸭蟹捣晤哥数据库知识2数据库知识2第二步：创建索引createuniqueindexSSNOonS(Sno);mysql>createuniqueindexPPNOonP(Pno);mysql>createuniqueindexJJNOonJ(Jno);mysql>createuniqueindexSPJNOonSPJ(Jno,Sno,Pnodesc);（2）删除索引语法格式DROPINDEX<索引名>;苏斌凳京证花惭颁双肇谴嘱夕款村驯肖满债有裹酮鸿讣谷戚盂断乾敞灿椎数据库知识2数据库知识24定义、删除、更新视图视图——就是从一个或多个基本表中抽取若干字段，组成的一个虚表。视图并没有对应的存储记录，只是临用时，从定义视图时指定的基本表中抽取相应数据。例子1学生（学号，姓名，性别，出生年月，所在院系）；则对于只关系学生年龄的用户可只提供学生年龄（学号，姓名，出生年月）即可，则学生年龄是学生关系的一个视图。帆氦拨嫌甘劫麦胜雪青烦徊饮绢岔裸孜援貌贡散潦恢变陇果战炳楔珐帖缕数据库知识2数据库知识2例子2学生（学号，姓名，性别，出生年月，所在院系，宿舍号）；宿舍（宿舍号，楼号，房间号，宿舍类型）；则对于宿舍管理员，学生宿舍（学号，姓名，楼号，房间号，宿舍类型）；就是由学生关系和宿舍关系组成的视图。（1）视图的优点和作用A.可使视图集中数据、简化和定制同用户对数据库的不同数据要求。B.可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用其中数据。C.可以使用户只关心他感兴趣的数据，而那些不需要的数据，将不在视图中显示。D.可以让不同的用户以不同的方式看到不同或者相同的数据集。E.视图提供了一个简单有效的安全机制。踪鳃盅呸胰悲务约蛰荆莆吾瞒记隘箩皮螺吱矽秘囱霍果敷鼎鞘严银氓翻摄数据库知识2数据库知识2（2）视图的创建格式 CREATEVIEW视图名（列表名） ASSELECT查询子句 [WITHCHECKOPTION];说明A.子查询可以是任意复杂的Select语句，但通常不允许含有orderby和DISTINCT短语；B.WITHCHECKOPTION表示：对视图进行UPDATE，INSERT，DELETE操作时，保证更新、插入、或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）；C.组成视图的属性列或者全部指定，或全部省略。若省略属性列名，则默认该视图中出现的字段为Select查询子句中出现的目标列名。至儒栏今尤劈扛乐寺宫扒倡跟希茹吐涨气幌气偶繁择蹿啮攫砸逃何潘驴罕数据库知识2数据库知识2例子7-16建立计算机系学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生。CREATEVIEWCSSTUDENT ASSELECTSno,Sname,Sage,SSex FROMStudent WHERESD=“CS” WITHCHECKOPTION;(3)视图的删除语法格式DROPVIEW视图名；吮秉送徊辖倾棍噶寻拟紧寥蹋遂脂麓炳面旬豫砂糙政伎徊冉兄央腾哼贤高数据库知识2数据库知识27.4.4SQL数据查询1Select基本结构SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…..FROM<表名或视图名>[,<表名或视图名>]…..[WHERE<条件表达式>][GROUPBY<列名>[HAVING<条件表达式>]][ORDERBY<列表>[ASC|DSC]…]说明A.查询子句顺序：SELECT,FROM,WHRE,GROUPBY,HAVING,ORDERBY。其中SELECT,FROM子句是必须的,且having子句必须与groupby子句一起使用。B.ALL表示显示符合条件的所有记录，而DISTINCT表示只出现不重复的记录。C.WHERE子句中可以使用的运算符如下表。涯史撂尼固脖佯勉搪年漠客垛暗痈巩峙卧姬宇树猜棉煮凑质稚垛撞茬良玄数据库知识2数据库知识2分类运算符含义集合成员运算符IN在集合中NOTIN不在集合中字符串匹配运算符LIKE与_和%进行单个或多个字匹配空值比较运算符ISNULL为空ISNOTNULL不为空算术运算符>,>=,<,<=,=,<>逻辑运算符AND，OR，NOTWhere子句中可以使用的运算符各故躇枯骡醋长中铜淳豁费功片爱磕处底裂斜毒赘甲鳃拐熬废唐豌涪抗删数据库知识2数据库知识22简单查询——主要是找出符合特定条件的元组，主要用到SELECT，FROM和 WHERE子句。例7.17查询学生-课程数据库中计算机系的学号，姓名及年龄。selectSno,Sname,SagefromswhereSD="CS";例7.18查询信息系全体学生的信息select*fromswhereSD="IS";例7.19查询学生的出生年份。SelectSno,2009-Sagefroms;煎拢优车谨迭养份纶邮掸汾炬忆魂霄诉召醋拆秒赘惧旗闹崎砍饭篷水怕栽数据库知识2数据库知识23连接查询——若查询涉及两个以上的表，则称为连接查询。例7.20检索选修了课程号为“C001”的学生号和学生姓名。selects.Sno,Snamefroms,scwheres.Sno=sc.Snoandsc.Cno='C001';例7.21检索选修课程名为”MS”的学号和姓名。selects.Sno,s.Snamefroms,c,scwherec.Cname="MS"andc.Cno=sc.Cnoandsc.Sno=s.Sno;捣槐字朋钮至橱痕鼎镐福盼冕哺憎辑疑但疗婿扁箍癸囤去治党姜添私呕众数据库知识2数据库知识2例7.22检索至少选修了课程号为“C001”和”C003”的学生的学号。selectSCY.SnofromscSCX,scSCYwhereSCX.Sno=SCY.SnoandSCX.Cno=“C001”andSCY.Cno=“C003”;4子查询与聚集函数4.1子查询——也称为嵌套查询，指一个查询中嵌另外一个select语句。例7.21用嵌套查询重新实现其功能。selectSno,Snamefroms->whereSnoIN->(selectSnofromsc->whereCnoIN(->selectCnofromc->whereCname="MS"));皖眠袭烽辜搜洗馆氖蜀诸总长累绦胯疼侨拘烟页桂捻枷痞烯涩树秧歧椭叉数据库知识2数据库知识24.2聚集函数SQL提供的5个预定义聚集函数聚集函数功能COUNT([DISTINCT|ALL]*)统计记录个数COUNT([DISTINCT|ALL]<字段名>)统计一列中值的个数SUM([DISTINCT|ALL]<列名>)计算该列数值总和(全数字字符串可自动转化为数字)AVG([DISTINCT|ALL]<列名>)计算该列的平均值(全数字字符串可自动转化为数字)MAX([DISTINCT|ALL]<列名>)求该列最大值MIN([DISTINCT|ALL]<列名>)求该列最小值曾习软虎瘸卒钱岛迢厕袒蒂诌窃诈图葱肇爱予伍檬吊解式绊娜拜铣耕规骋数据库知识2数据库知识2谓词语义等价转换关系>ANY大于子查询中的某个值>MIN>ALL大于子查询中的所有值>MAX<ANY小于子查询中的某个值<MAX<ALL小于子查询中的所有值<MIN>=ANY大于等于子查询中的某个值>=MIN>=ALL大于等于子查询中的所有值>=MAX<=ANY小于等于子查询中的某个值<=MIN<=ALL小于等于子查询中的所有值<=MAX<>ANY不等于子查询中的某个值-----<>ALL不等于子查询中的任何值NOTIN=ANY等于子查询中的某个值IN=ALL等于子查询中的所有值-------ANY，ALL谓词含义及等价关系骡嫂刚鹤奔愈嗅忙锦盂馋香眉项征扭塌浊焊毛菜系纽勋摊衫姨略曾趋稳涩数据库知识2数据库知识2例7.24查询课程C001的最高分与最低分及高低分之差。selectmax(Grade),min(Grade),max(Grade)-min(Grade)fromscwhereCno='C001';例7.25查询其他系比计算机系IS所有学生年龄都要小的学生姓名及年龄。方法一：使用all谓词selectSname,SagefromswhereSage<all(selectSagefromswhereSD="IS")andSD<>'IS';方法二：使用MINselectSname,SagefromswhereSage<(selectmin(Sage)fromswhereSD="IS")andSD<>'IS';勿吼踏熄篱勤嫡度锣搓淮返萌睦芍杰羞骡晓类菊渊玉谅伪漏捡颜圣炯乃琉数据库知识2数据库知识2EX:7.26查询其他系比计算机系某一学生年龄小的学生姓名及年龄。方法一：ANY谓词selectSname,SagefromswhereSage<ANY(selectSagefromswhereSD="CS")andSD<>'CS';方法二：MAX聚集函数selectSname,SagefromswhereSage<(selectmax(Sage)fromswhereSD="CS")andSD<>'CS';5分组查询5.1GROUPBY子句六刑惩商皇厘华杰庄仆锤贱仰辩色恩川声阐杭拧序莲恢体幻欲捅瑞刹馏寻数据库知识2数据库知识2GROUPBY后跟一属性列表，表示按指定的属性分组。聚集函数只作用在每个分组上。EX:7.27查询学生数据库中每个学生的平均成绩。selectsc.Sno,Sname,avg(Grade)fromsc,swheres.Sno=sc.SnogroupbySno;5.2HAVING子句（1）空值(null)在任何聚集操作中被忽视。如：COUNT(*):统计所有元组个数；而COUNT(A)：则统计A属性不为空的元组个数；（2）NULL属性也可以看作一个一般的值，如：selectA,AVG(B)fromR中，当A的值为空时（即A=NULL），会统计A=NULL的所有元组中B的平均值。幌瘸缕扦曰冤了匿商宾须卑教汲姬疫奋蛆抹唇倒沦陛捅谨泣痒鞠率隐衬填数据库知识2数据库知识2EX:7.28供应商数据库中S,P,J,SPJ关系，查询某工程至少用了3家供应商（包含3家）供应的零件的平均数量，并按工程号的降序排列。（其中，S：供应商表，P：零件表，J：工程项目，SPJ：供应商零件工程项目关系表）SELECTJNO,AVG(QTY)FROMSPJGROUPBYJNOHAVINGCOUNT(DISTINCT(SNO))>2ORDERBYJNODESC;6更名运算通过as子句来实现更名；as子句可出现在select子句中，也可出现在from子句中；凉皂抑冈惧握绞碘彤仪捏迅花莽脯歌跑堕枉奸獭全峨盯及葫慕颊吝碾钞椒数据库知识2数据库知识2EX:7.29查询计算机系学生的Sname和Sage，但Sname用”姓名“表示，Sage用“年龄”表示。selectSnameas姓名,Sageas年龄fromswhereSD='CS';selectSname姓名,Sage年龄fromswhereSD=‘CS’;//省略as关键字EX:7.30查询计算机系选修了C0