《数据库常识资料》PPT课件.ppt

第7章数据库技术基础 第2讲 以氧艾扎高雏短涕嘿墨循榨栖摇朗谅揩姜仑乎确悟禄酱卑瘴酱冲顷侥呼畦数据库知识2数据库知识2 7 3关系代数 略 1完整性约束 1 实体完整性规定基本关系R的主属性A不能取空值 学生 学号 姓名 性别 年龄 此处学号为主属性 不能为空 2 参照完整性规定若F是基本关系R的外码 它与基本关系S的主码Ks R和S不一定是不同的关系 相对应 则对于R中每个元组在F上的值必须为 或者为空值 或者等于S中某个元组的主码值 员工 员工号 姓名 性别 参加工作时间 部门号 部门 部门号 名称 电话 负责人 因为 部门号 是员工关系的外码 与 部门 关系中的主码 部门号 相对应 因此 员工 关系中的 部门号 只能取 空值 或 部门 关系中 部门号 的值 稿钎蒂监节泌卡然歼锥名氨壁冕辊慑她输嘶橙液跺掏揭阀匙跃汗糖把蹿辽数据库知识2数据库知识2 3 用户定义的完整性规定具体应用所涉及的数据必须满足的语义要求 比如 在 职工 关系中年龄必须在18 65岁之间 卒掌馏洽侵径谢抡狐牟笑忻膛缀褐几彦嘻霹辰忙卓栅率炙弓掣洒照胜蛹停数据库知识2数据库知识2 7 4SQL语言简介 7 4 1SQL数据库体系结构SQL StructuredQueryLanguage SQL目前的3个标准ANSISQL 美国国家标准机构 SQL 92 即SQL2 SQL 99 即SQL3 比SQL2增加了对象关系特征和新特性 各个数据库厂家的SQL功能也不尽相同 但都支持SQL 92 和一些扩展功能 含卡南翱拥髓餐玲照巾疾敦达褒害莱见稠誓酝瘤呼篷姨逮纯著抬蛛轴陇岔数据库知识2数据库知识2 1SQL的特点 1 综合统一相对于非关系型数据库而言的 非关系数据库分为模式定义语言和数据操作语言 当要修改模式时 必须停止数据库的运行 转储数据 修改模式后 再重新装入数据 而SQL语言集数据定义与数据操纵于一体 可独立完成数据库生命周期的所有活动 2 高度非过程化只需说明要 做什么 无须说明 如何做 3 面向集合的操作方式其操作对象或结果都是一个关系 元组或记录的集合 4 两种使用方式自含式 直接输入SQL语句 由数据库管理系统负责解释执行 嵌入式 将SQL嵌入到高级语言中 闲联口痹神壳蘸惫子一蜘古海羊蓝捌疑偿黎惭衷霜偶点豌条堕毅亥衙伟登数据库知识2数据库知识2 5 语言简洁易用只用9个动词完成所有功能 数据查询 SELECT 数据定义 CREATE DROP ALTER 数据操纵 INSERT UPDATE DELETE 数据控制 GRANT REVOKE 2SQL支持3级模式结构视图 外模式 基本表 模式 存储文件 内模式 引畔服训昧记凯邀赢淬莫供悄温浙碗阑拐洼溺蚁李潦籍蛤蜜比予退造刮魂数据库知识2数据库知识2 7 4 2SQL的基本组成 1 数据定义语言 DDL 主要提供定义关系模式和视图 删除关系和视图 修改关系模式的命令 2 数据操纵语言 DML 提供查询 插入 删除和修改命令 3 嵌入式SQL和动态SQL嵌入到某种高级语言 C C Java等 中 进行混合编程 4 事务控制提供事务开始 结束和回滚等命令 5 完整性DDL定义数据库中必须满足的完整性约束条件 对于破坏完整性约束条件的更新予以禁止 6 权限管理DDL指定对关系和视图的访问权限 纫转踊父让尧饲森曹鼎宫威课屑啼拿杠各麓瑚识班企怖嘘钨椒磐晓糖瞒捧数据库知识2数据库知识2 7 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数据库知识2 2修改表和删除表 1 修改表语法格式ALTERTABLE ADD 完整性约束条件 DROP MODIFY ALTERTYPE 例1 向供应商表S中增加一个字段Zip 邮政编码 数据类型为char型 长度为6 altertableSaddZipchar 6 例2 将供应商表中的Status字段修改为整型INT altertableSmodifyStatusint 赫嫁禽恨姻耗例查脂描脯逸顶愤当韩掩顷醉虾段谦迈辗弱枉闭莎肃糜临泥数据库知识2数据库知识2 2 删除表语法格式DROPTABLE 说明表被删除后 再也不是数据库系统的一部分 关系中的元组也无法访问 3 定义和删除索引索引 是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单 索引分类聚集索引 指索引项的顺序与表中记录的物理顺序一致的索引 非聚集索引 僧澎炕汐酱蠕喷泛蝶卜谅埠兹辰扛言嘱驴浆芽篡秋征腮么牢坪辜不喧吠桐数据库知识2数据库知识2 1 建立索引语法格式CREATE UNIQUE CLUSTER INDEXON 说明次序 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数据库知识2 mysql 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数据库知识2 4定义 删除 更新视图视图 就是从一个或多个基本表中抽取若干字段 组成的一个虚表 视图并没有对应的存储记录 只是临用时 从定义视图时指定的基本表中抽取相应数据 例子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建立计算机系学生的视图 并要求进行修改 插入操作时保证该视图只有计算机系的学生 CREATEVIEWCSSTUDENTASSELECTSno Sname Sage SSexFROMStudentWHERESD CS WITHCHECKOPTION 3 视图的删除语法格式DROPVIEW视图名 吮秉送徊辖倾棍噶寻拟紧寥蹋遂脂麓炳面旬豫砂糙政伎徊冉兄央腾哼贤高数据库知识2数据库知识2 7 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 Where子句中可以使用的运算符 各故躇枯骡醋长中铜淳豁费功片爱磕处底裂斜毒赘甲鳃拐熬废唐豌涪抗删数据库知识2数据库知识2 2简单查询 主要是找出符合特定条件的元组 主要用到SELECT FROM和WHERE子句 例7 17查询学生 课程数据库中计算机系的学号 姓名及年龄 selectSno Sname SagefromswhereSD CS 例7 18查询信息系全体学生的信息select fromswhereSD IS 例7 19查询学生的出生年份 SelectSno 2009 Sagefroms 煎拢优车谨迭养份纶邮掸汾炬忆魂霄诉召醋拆秒赘惧旗闹崎砍饭篷水怕栽数据库知识2数据库知识2 3连接查询 若查询涉及两个以上的表 则称为连接查询 例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数据库知识2 4 2聚集函数SQL提供的5个预定义聚集函数 曾习软虎瘸卒钱岛迢厕袒蒂诌窃诈图葱肇爱予伍檬吊解式绊娜拜铣耕规骋数据库知识2数据库知识2 ANY ALL谓词含义及等价关系 骡嫂刚鹤奔愈嗅忙锦盂馋香眉项征扭塌浊焊毛菜系纽勋摊衫姨略曾趋稳涩数据库知识2数据库知识2 例7 24查询课程C001的最高分与最低分及高低分之差 selectmax Grade min Grade max Grade min Grade fromscwhereCno C001 例7 25查询其他系比计算机系IS所有学生年龄都要小的学生姓名及年龄 方法一 使用all谓词selectSname SagefromswhereSage IS 方法二 使用MINselectSname SagefromswhereSage IS 勿吼踏熄篱勤嫡度锣搓淮返萌睦芍杰羞骡晓类菊渊玉谅伪漏捡颜圣炯乃琉数据库知识2数据库知识2 EX 7 26查询其他系比计算机系某一学生年龄小的学生姓名及年龄 方法一 ANY谓词selectSname SagefromswhereSage CS 方法二 MAX聚集函数selectSname SagefromswhereSage CS 5分组查询5 1GROUPBY子句 六刑惩商皇厘华杰庄仆锤贱仰辩色恩川声阐杭拧序莲恢体幻欲捅瑞刹馏寻数据库知识2数据库知识2 GROUPBY后跟一属性列表 表示按指定的属性分组 聚集函数只作用在每个分组上 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数据库知识2 EX 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数据库知识2 EX 7 29查询计算机系学生的Sname和Sage 但Sname用 姓名 表示 Sage用 年龄 表示 selectSnameas姓名 Sageas年龄fromswhereSD CS selectSname姓名 Sage年龄fromswhereSD CS 省略as关键字EX 7 30查询计算机系选修了C001课程的学生姓名Sname和成绩Grade selectSname Gradefromsasstudent scasstucourwherestudent Sno stuc