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