《数据库系统概念》笔记、部分课后答案优质博客链接

【实例简介】
内含关于学习数据库的 优质博客链接 《数据库系统概念》笔记、部分课后答案
where not exists( select l from loan L where Lempno eempno and not exists( select 1 from books B Where B isbn= isbn and B publisher="McGraw-Hill")) (MYSQ不支持 except可用 left join代替; sql server支持 A是某员工借书集合 B是M出版社出的书的集合 符合条的员工,满集合B属于A 关系A包含关系B: not exists( B except A)(关系A包含关系B即从集合B中去掉集合A的部分则为空 not exists在作为参数的子查询为空时返可ue SELECT E name FROM employee E WHERE not exists((SELECT B. isbn FROM books B WHERE B publisher =McGraw-Hill) except (SELECT Isbn FROM loan L employee E2 WHERE Lempno=Eempno and E name E2 name) C、拉巧:返回咩单值的子查可用J週辑輯判;不同的书— -distinct isbn select name from employee E where exists( select 1 from loan L where Lempno =Eempno and( select count( distinct isbn )from books B Where B. isbn= Isbn and B publisher ="McGraw-Hill )>=5) select E name from employee e where exists( select 1 from loan L where Eempno= Lempno and( select B publisher, count(*)from books B where B. isbn= Isbn group by B. publisher)>=5) 自己写的不测不道行不行: SeLECT name FROM employee e books b, loan I WHERE e empno=lempno AND I. isbn=b isbr GROUP BY b. publisher HAVING COUNT(b. isbn)>=5 关于es和notexists的用去:htp//ww.cnblogs.com/mytechblog/articles,2105785html 分组取馀条记录:htp/ bloqcsdn. net/ acmain chm/ article/ details/4126306 持名函数ROWNUMBERRANK、DENSERANK及OVER子句):htt//new-fighter.iteye.com/blog/1627952 取每个班前五名:htp// obs. csdn. net/ topIcs,/39044540 比如有个学生总表 class id name score 分别是班级,学号,姓名,学分 如果条语句求出每个理级前5名的同学。 ORCAL中 select class id name score, rn from (select class, id, name score row_number( over(partition by class order by score desc)'rn from学生总表 t where tr<=5 或用 with as() with t as( select * row_number over(partition by classid order by id desc)'m from student select from t where n<6 第三章SQL 聚集数 having子句在形成分组后才起作用 where子句在 having子句之前,凝 Where的组过 group by形成分组, having子句作用于每个分组,不符合 having子句条件的分 组将被摭弃 事务( transaction) 回退(ro|back) START transaction delete from orderitems where order num =20323 DELETE FROM orders where order num=20323 COMMIT 提交( commit):在事务处理莫央,提交不会隐含的进行,为明騠交,使用 commit句 保留点〔 savepoint)指事务处理中设置的临时占位符,你可以对他发布回退,与回浪整个事务处理不同。例如 savepoint delete1 为了回退到呆留点: rollBaCK tO deletel 下的的查在n1和2有个空时候查询为空( The query selects those values of p. aI that are equal to some value of r1.al or r2.a1 if and only if both rI and r2 are non-empty. If one or both of rI and r2 are empty, the cartesian product of p, rl and r2 is empty, hence the result of the query is empty. Of course if p itself is empty, the result is as expected, i.e. empty select p al from p, r1, r2 where p al=r1. al or pal=r2.al 第四章高级SQL 1、SQ中的漖据类型 标SqL支持的基本数类型 char(n)固定长度字符串 varchar变长字符串 int整数类型 smallin小型,范围匕nt小 numerIc(p,d):定点数,有p位数字(包括个符号位),其中d位数字在小数点右边, numerIc(3,1)精峥存储4.5,但 是稀储44.50.32样的数字。 rea, double precision:浮点数与双精度孚点数 float:精度至少为η的孚点数 时数据类型 date‘201607-13′ time09:30:00′ timestamp:2016-07-1310:50:01.45 decimal:十进制小数,和浮点数相差很大。浮点数是直挖把/小数转化七为进制,二多数小数转化为二进制是有误差的,也就是说不 精表示。 decimal则同以没有任何误差的表示小数。注意的是 decimal(184)总长18位,包括1位小数禾4为数,也就是说18- 1-4=13整数位只有13位 12345678901231234 MYSQ中的数姐据类型: http://wenku.baidu.com/view/0152a23467ec102de2bd8915.html?re=view 2SQ中的函数 21cas函数 CAST (expression AS data_type SELECT CAST(12.5 AS decimal(9, 2) decimal数类型在结果网格中将显示有刘小数位12.50 精度利数伩默认償分别是18与0。如果在 decimal类型中不这两个值, SQL Server将舂数字的小鹚部分,而不会产生错 误 SELECT CAST(12.5' AS decimal) 结果是个整数值:12 (1)CAST函数的参数是—个表式,它包括用AS关键字分聞的源信和目标数捃类型。以下例子用于将文本字符丰'12转换为型 SELECT CAST(12 AS int) (2)返回值是整型值12。如果斌堅将个代表小数的字符串转换奂为整型值,又会出现什么情呢? SELECT CAST(12.5 AS int) (3)CAST0函数和 ONVERT0数都稀行四啥舍五入或淅作。由于12.5用in数据类型来表示,所以这个数调用将产生 一个错误 Server. Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value 12.5 to a column of data type int (4).要回个合的擻值,就须使甬理这个的数类型。对于这个例子,存在多个可用的数捱类型。如果画过AST0函数 将这个值为 decima型,需要首先定乂 decimal值的煵度与小数位数。在本例中,精度与小数位数分别为9与。精度是总的数 字位数,包括小数点左边和右边位始的总和。而小数位数是小数点右边的位数。这表示本例多支持的最大的輕数值是99999布 最小的小数是001。 SELECT CAST(12. 5 AS decimal(9, 2) decima数据类型在结果网格中将显示有刘小数位:12.50 (5)精度利数数默认别是18与0。如果在 decimal类型中不提供这两个值, SQL Server将字的」数吩分,而不会产生 错误。 SELECT CAST(12.5 AS decimal 结果是一个整数值:12 2.2 convert函 数 select convert(decimal(10, 1),10*1./4) 结果2.5 等价于 select cast(10*1/4 as decimal(18, 1)) 2.3、日期处理数 caste as t将字符串e换成间类型( date\time\timestamp) extract(field from d)提取日期或间中的某一域,year、 month、day、hour、 minute、 second mysq常用的日期理乎数:mysq使用的日期格式是 yyy-mm-dd Date()提取日期吩,如Date( order-date)=2016-07-13 Year()返回年份部分,如Year( order-date)='2015 Month()返回月份部分,如M。nth( order-date)=9 st 检索出2015年9月的数据 WHERE Date( order -date) between'2015090and'20150930 ElEWHERE Year(order-date)=2015 and Month(order-date='g 3、完整性约束 (1)not null; unique; check unique(A, B, CD)限表的ABCD开成候铞码(僩码:几个属性的合,唯碥了元组,子集不能分成洗砳的嘱属 性集合),没有元组可以在所有主码上属性相同。 check用如 check( degree_lev in(A"B"C))限 degree_lev的取值只能在ABC中取倌。 (2)参照完整性 foreign key; cascade; grant create table account foreign key branch name) references branch on delete cascade on update cascade, 如果除更新 branch中的元組,将导致参照完整性被破坏,如果用 cascade级联刪除,当删除 branch的元组时会相应的删除 account中参照了 branch中硼删除的些组 4、授权 gant<权表>on<关系名或名to<用户/角色列表> st. grant select on account to John 第二部分数据库设计 第六章数据库设计和E-R模型 第七章关系数据库设计 数据棋式:是对数据库中全体数捃的熤辑结构(数据项字、类型、取值沱等)和特征(数据之间的联系以及数据有关的安全 性、完整性妻求)的摧术。 2、关系模式:从用户观点看,关系模式是由组关系组成,每个关系的爕据结构是张规苑范化的二维表 3、在关系椟型中,我们将属性殳有任何子结构开化,如果某个坷的元素被以为是不可分的单元,那么这个域就是原子的。如果个关 系椟式R的属性堿褚隄是原子的,我i称关系摸氓R属于第范式(INF) 4、函赖:对关系模式R上皈两个元组t1t2,属性a和属性b漩,若t1(a)=t2(a)则有t1(b=t2(b)则函漖赖a->b是成的。如 学号->房份证号;(注意有时候a->b不定有b->a) 平凡的数赖:如果b集合属于a;则a->b的函数依赖是平凡的,如AB-→>A-定有t1[AB]=t2[AB败1[A]=t2[A] 6、满足BCN范的条件:关系R上的護数依赖a->b,要么是平凡的函数衣赖,要么是甍式R的超码 7、第三沌式:关系模式R属于第三沌r的条件:郾伈棘集中所有卅->b的数赖少有以下之-存在:(1)a->b是—个平凡 的依赖 (2)a是旳的个超码(超是个或多个属性的集合,觞唯标组;仙的任意子集都不能构成超码,这样的最小 超成为候选码) (3)b->a中的每个属性A都包含在R的个候先码中 第八章应用设计和开发 et和jsp 2、触发器 CREAT TRIGGER neworder After insert on order FOR EACH ROW SELECT NEWorde num 在向 order,插入新订单以后,创建触发器选新增订单的编号 有时可以用或者存儲过程来代替触发器,实现同的需求 3、SQL中的授权 (1) sq不包含 delete insert select update的基本权限集,还有 references权限眼, execute权限 usage权限 grant<权唛>on<关系名或图名>o<用户角色列表> 例受予 reference权跟: grant reference( branch name) on branch to u1 允许用U1仓建通过外键参照 branch的 branch name的关系 2) 角色:比如创建一个角色 manager: create role manager 授校给角色: grant select on account to manager 把用户分酷给角色: grant manager to Mary (3)收回权限: revoke select on branch from U1U2U3 第四部分数据存储、查询和事务管理 第十一章数据存储和数据存取 1、数据字典 个关系数居库需要护关于关系的数据,这类信息被称作数据字典。存储的笮息类型有 关系的名字(表名) 每个关系的属性名(每个表都有吲些段) 属性的鱼和伥度 图的名和定义 宄整性约束 索引的客字 被索引的关系的字 2、索引 首先了解一下物理行储介质及数据库存耿数据的过程及原理 主存储器 磁硿存儲器:通常整个欻据库都存储在磁盤上,为了能够数据,系鉨必将戣据从磁盘税到主存讳器,在υ操作后,被修 改的数据必须回碱盘。 磁盘是由大小相同且轴的氐开盘片组成,磁盘可以各^磁盘同时转动。破巒的侧有读写头支架,读写头支架定了—组 读写头,磁僻车动,读写头就可以从草些磁的某扇区读据。磁弩可以前动,用于读取不同磁首上的数据。磁道就是以盘片为中心 分出来的系列心环如柄圈。磁首又划分为个个小段,叫扇区,是磁盘的最小存储单元。(毎次叟索敩据樊輕网应的磁 道,这个时间叫做寻道时间,然后在该首上找到对应的扇区读数据,这个时间叫旋转时间,总的査询时间主要取决于寻道间) 磁嶶O请求定要访可的磁盘地址,这个坩址以块号开给出。一个块是个逻辑单元,包含固定数目的连续家区。每欠查询磁盘 J/O请求次数小,査迫姣率高 为了∫尽量减少o操作,磁盘读取毎次都会预读,大小通常为页(用于在块中织记的结构,就埤解为吧)的整麴倍。即使只需 要读取一个字节,破楹也会读取一页(块)的数据通常为4K放入內存,内存与磁熤以页(块)为单位交奂数琚。因为局部吽性原理以为, 通常—个数据被用到,其数据也会立弓被用到。 21数据库索引,是括库管理系统中个排序敏数据构,以协助快诔査旬更新数据库表中据。索引的实通常吏用B树及其变种 B+树。 在数据之外,数据库系统维护着諴后特定査找算法的数捃结构,这些数喆构以葉种式引用(指向)数据,这样就可以在这些数结 构上实现言级査找算法。这种数据结构,就是索引 索引:为了让系统直接定位組要査找的记录,设讣种构,并将其与文件关联来。 为表设置索引要付岀代价的:-是增吇数库的存储空间,二是在插λ和修改数时要花麦多的时(因为索引要随之变动。 2.2 (1)根据数据库的能,可以在数牧居库设讦器中创建三索引:唯索引主键索引聚集索引。 D唯索引 唯索引提不允许其中任吋两行具有相同索引值的索引。 当现有数中存在重复的键值时,大多数数据车不允许将新创殚的唯索引与表起保存。数库还可能防山忝卩在表中创建 重复键的数据。例如,如果在 employee表中职员的姓(name)上创腱建了唯索引,则壬何冋两个员工都不能同姓。 m主键索引 数据库表经常有列妫合,其值唯标识表中的每行。该列称为表的主键。在数库关系图中为表定乂主键将自动创健 主键索引,主键索引是唯索引的定类型。该索弜腰求主键中的每个值都唯。当在査询中使用主键索引时,它还允许刈数居 的快速访可 m聚集索引 在集刻引中,表行的物理顺序与键值的遇辑(索引)顺序相同。一个表只貧含个聚集刻引。如某引不是聚集索引, 贝表中彳的物顺序与键值的逻辑顺序伓四配。与集索引\楫比,聚集索引常提供更快的数据访冋柬度。 (2)两种基本索引型 a)顺序索引 每一个结构与个特定搜索码相关联(用亍在文件中査找记的属性或属性集称为搜索码) 序索弓按顺序存储搜索码的值,并将搜索码与包含该搜索码的渌关联己来。 顺序索引伩件的缺点在于,随着文的增大,索弜查找性哕顺序擻索性会下降,虽然可以通丈件进行亍组来弥 补,但不希望频的组(文件数捃腼入册除等新会增弜的疒销,即索杝需要相竝的新,文件被修改, 刻引必须更新) (i)稠密索引:每个搜索码值都有索引录 (ⅱ)稀索引:只为索的某些值建立索引 (i)多级索引(B+树索引) B+刻引:在插入利删除的情下仍能猱持索行效的引结构之- 是多级索引,但不同多索引顺序文件 ⅳv)辅助索引:搜索码不是主码(有指桶) 多种树的关系: 红黑树 干衡二叉树 )II http又梅索树csdn C1749910 二叉树 树 图1多种树的关系概路图 B树 首先介绍一下B树(有的哋方也叫B-树,是同种),B+树是由B树帏宀展得到 棵m阶的B树满足下列条件: 1,每个结点至多有m棵子树。 2,除根结点外,其它每个分支结点至少有「m/21 棵子树, 3,根结点至少有两棵子树(除非B树只包含一个结点) 4,所有叶结点在同一层上。B树的叶结点可以看成一种 外部结点,不包含任何信息。 5,有j个孩子的非叶结点恰好有j-1个关键码,关键码按 递增次序排列 T35 PI P F 8口12 26■30 PI P2 PI P2P 35‖9m10‖1315‖|893|fd 79 碰盘块压 益块6 盘块7 盘块 谢埃9 块10 盘块L 棵B树 其中17示个磁文件的文件名;小红方块勅个17文件内容在硬盘中的存储位置;p1表示指向17左子树的针。 6树查询的过程 假如毎个盘块可以正好存放一个树的结点〔正好存放2个文牛女)。那么一个MD结点就代表一个盘坫,而 子树指针就是存放另外—个盘块的地址 下面,咱们来模拟下查找文件29的过程 1根据根結卢指针文件目录的根磁盘块1,圬中的息入内存。【磁盘操作1】 2.此时內存中有两个文件名17、和三个行悀其而曲地的据。根据耳法抗们发现:17<295, 囚比们到指针p2 3恨据指针,我们定付到磁盘块3,)将其巾信录。【盘1屎作2】 4.此时內存中有两个文件名26,30和三个存偭其仳畝盘西面址扯的数据。根据算法我们发现:26<29<50, 因比我们找到指针p2 5.根据2指针,我们定位到磁盘块8,并将其中的信息寻入内。【磁盘0探作3次】 6.此时内存中有两个文件名28,29。根据法孩们文件吝29,并定位了该文件内存的磁盘地址 析上面的过程,发现需要3次磁盘I0操作和次内存查找桑怍。关于内存中的文件名查找,由于是一^有应 表黠构,可以利用折半查找提高效率。至于I操作是景整个B擞效率的决定因素。 当然,如果我们使用平衡二叉树的磁盘存储结构来进仁查抟,磁盘次,最姦5次,而且文件越怒,B树比平衡 二叉树所用的磁盘操作次数将越少,效率也越高。 B+树 基本定义B树相同,除了: 1.所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小白小而大的顺序链接。(而 B树的叶子节点并没有包括全部需要查找的信息) 2所有的非终端结点可以看成是素引部分,结点中仅含有其子树根点中最大(或最小)关链字。而B树的丰终节点也包含需要查找约有效 信息 DATA PIP2P3 1020 P2 P3 PI P2P3 PI P2 P3 28 35 90 3出 50 gg B+树 为什么选用B+/Tree 舣来说,霸引也大,不可能在內存中,郾性往以引件开行徹的盘上。这样的话,索引过程中就要 产生磁耗,棡内佩取,I°瞰的濮精要高几个数级,F以价个据结作为刻弓的优最重要的指就是在査拉过 程中磁嶶O操作数的詬排复杂度。换句话说,索引的结构組织要尽量减少挝过科中磁盘O的存取欠数。 B+树比B树率更高的原因 B-Tree:如果次检索需要访可4个节点,数据库系统设计者利用憾囧预卖原理,把节点的大小设计为-个页,那读取一个节点需 要浏/O操作,完成次检索操作,最多需要3/∝根节点常存。数据记录越小,毎个节点存放的数多,树的度也 越小,I/O操作就少了,检索效率也就上去了。 B+Tree:非叶了节点只key,大大威了非叶了节点的大小,那么每个节点就可以存放更多的录,树更矮了,O操作更少 了。所以B+Tree拥有更好的性能 B可以在o(logn)时间内,实现各种如插入( insert),删除( delete)等动态集合操作。 B+树查询 在付X2叫 302第四部分数据存储、查询和事务管理 A IAILHKI 引有良好的查找、插入和删除的性能。 11.8.2B树的查询 让我们考虑一下如何处理B树上的查询。假设要找出搜索码值为v的所有记录。图1120 表示执行这个操作的伪代码。直观地看,这个伪代码如下工作。首先,检查结点,找到大于V 的最小搜索码值,假设我们找到的这个搜索码值是K。然后,顺着指针P到达另一个结点。如 果找不到这样的值,则k≥Km1,其中m是该结点中的指针数。这种情况下,沿着Pm到达另一即 个结点。按上述方式到达的那个结点中,再次寻找大于V的最小搜索码值,并且再次像上面那 样沿相应结点而下。最终,我们将到达一个叶结点,如果在该叶结点中有某个搜索码值K等于 V,那么指针P2指向我们所需要的记录或指针桶。如果在该叶结点中找不到值V,则不存在码 值为V的记录。 的: procedure. find(value V) 设置C=根结点 while C不是叶结点 begin 令K1=大于V的最小搜索码值(如果有的话) 迁没有这样的值 then begin m1一根袋点中最一个指针何 倍点 else设置C=P指向的结点 end HC中有一个码值K,满足K=V then指针P指向我们需要的记录或指针桶 else不存在具有码值为k的记录 图1120B+树的查询 B+比叉树更适合索引 因此,在处理一个查询的过程中,需要遍历树中从根到某个叶结点的一条路径。如果文 刀个恒 之实际上只需访问几个结点,结点的大小般等于盘块大水,通常为4KB如果搜索码的 的大小达到32字节,7也大约为100n=10的情况下,如果文件中搜索码值共有1百万 个,一次查找也只需要访问「og0(100000=4个结点。因此,查找时最多只需要从磁盘读四 个块。通常树的根结点访问频繁,很可能在缓冲区中,因此一般只要从磁盘读取三个或更少的 磁盘块。 B树结构与内存中树结构(如二又树的一个重要区别在于结点的大小及其造成的树的高度 的不同。二叉树的结点很小,每个结点最多有两个指针。而B树的结点非常大(般是一个磁 盘块的大小),每个结点中可以有大量指针。因此,B树一般胖而矮,不像二叉树那样瘦而高。 在平衡二又树中,查找路径的长度可达w2(K),其中K为搜索码值的个数。当K如上例中那 样为100000时,平衡二又树大约需要访问20个结点。如果每个结点在不同的磁盘块中,处理 一个查找需要读20个块,而B+树只需读4个块。 十的百立 B+树的朋腼入: 插入 使用和査找样技术,扌洷叟码直将岀现的叶节点,如果搜索码值已经存在J叶节点中,那么在文件中新增条记录,并且, 如果需要在指桶中加入一个指针指向该记 【实例截图】
【核心代码】