數(shù)據(jù)庫設(shè)計(jì)與應(yīng)用

1、數(shù)據(jù)Data是數(shù)據(jù)庫中存儲(chǔ)的根本對(duì)象,是描述事物的符號(hào)記錄.其種類包括：文字、圖形、圖像、聲音.其特點(diǎn)：數(shù)據(jù)與其語義是不可分的.數(shù)據(jù)結(jié)構(gòu)：邏輯結(jié)構(gòu)：級(jí)2數(shù)據(jù)之間存在的邏輯關(guān)系.如表、樹、圖、數(shù)組物理結(jié)構(gòu)：數(shù)據(jù)在計(jì)算機(jī)內(nèi)的存儲(chǔ)方式,如順序方式、鏈接方式數(shù)據(jù)庫：人們收集并抽取出一個(gè)應(yīng)用所需要的大量數(shù)據(jù)之后,應(yīng)將其保存起來以供進(jìn)一步加工處理,進(jìn)一步抽取有用信息.數(shù)據(jù)庫的特征：數(shù)據(jù)按一定的數(shù)據(jù)模型網(wǎng)狀,層次,關(guān)系型組織、描述和儲(chǔ)存；可為各種用戶共享；冗余度較小便于保持?jǐn)?shù)據(jù)的一致性；數(shù)據(jù)獨(dú)立性較高；易擴(kuò)展數(shù)據(jù)庫治理系統(tǒng)DatabaseManagementSystem,簡(jiǎn)稱DBMS由一個(gè)互相關(guān)聯(lián)的數(shù)據(jù)的集

2、合和一組用以訪問這些數(shù)據(jù)的程序組成.是位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)治理軟件.DBMS的用途：科學(xué)地組織和存儲(chǔ)數(shù)據(jù)、高效地獲取和維護(hù)數(shù)據(jù).主要功能：數(shù)據(jù)定義功能,即提供數(shù)據(jù)定義語言DDL,定義數(shù)據(jù)庫中的數(shù)據(jù)對(duì)象；數(shù)據(jù)操縱功能DML:如插入刪除等；數(shù)據(jù)庫的運(yùn)行治理功能；數(shù)據(jù)庫的建立維護(hù)功能.數(shù)據(jù)庫系統(tǒng)DatabaseSystem,簡(jiǎn)稱DBS是指在計(jì)算機(jī)系統(tǒng)中引入數(shù)據(jù)庫后的系統(tǒng)構(gòu)成在不引起混淆的情況下常常把數(shù)據(jù)庫系統(tǒng)簡(jiǎn)稱為數(shù)據(jù)庫.由數(shù)據(jù)庫、數(shù)據(jù)庫治理系統(tǒng)、應(yīng)用系統(tǒng)及其開發(fā)工具、數(shù)據(jù)庫治理員和用戶構(gòu)成.數(shù)據(jù)庫的特點(diǎn)：面向全組織的復(fù)雜的數(shù)據(jù)結(jié)構(gòu)；具有較高的數(shù)據(jù)和程序的獨(dú)立性；數(shù)據(jù)的冗余度小,易擴(kuò)充；

3、統(tǒng)一的數(shù)據(jù)限制功能,數(shù)據(jù)共享程度高型是對(duì)數(shù)據(jù)的結(jié)構(gòu)和屬性的說明-模式值是型的一個(gè)具體賦值-實(shí)例數(shù)據(jù)庫三級(jí)模式外模式Sub-Schema子模式：用戶的數(shù)據(jù)視圖,是數(shù)據(jù)的局部邏輯結(jié)構(gòu),模式的子集可以有多個(gè)模式Schema：所有用戶的公共數(shù)據(jù)視圖,是數(shù)據(jù)庫全體數(shù)據(jù)的全局邏輯結(jié)構(gòu)和特性的描述只有一個(gè)內(nèi)模式StorageSchema存儲(chǔ)模式：是數(shù)據(jù)的物理結(jié)構(gòu)及存儲(chǔ)方式,只有一個(gè)數(shù)據(jù)抽象：物理層：最低層次的抽象,描述數(shù)據(jù)如何存儲(chǔ)邏輯層：描述數(shù)據(jù)及數(shù)據(jù)間的關(guān)系視圖層：描述整個(gè)數(shù)據(jù)庫的某一局部,使用戶與系統(tǒng)交互更簡(jiǎn)單數(shù)據(jù)模型：描述數(shù)據(jù)、數(shù)據(jù)聯(lián)系、數(shù)據(jù)語義以及一致性約束的概念工具的集合三大數(shù)據(jù)模型：關(guān)系模型：用

4、二維表來表示實(shí)體及其相互聯(lián)系；層次模型：用樹結(jié)構(gòu)表示實(shí)體之間聯(lián)系的模型叫層次模型；網(wǎng)狀模型：是一個(gè)滿足以下條件的有向圖：可以有一個(gè)以上的節(jié)點(diǎn)無父節(jié)點(diǎn)；至少有一個(gè)節(jié)點(diǎn)有多于一個(gè)的父節(jié)點(diǎn)排除樹結(jié)構(gòu)事務(wù)是由一系列操作序列構(gòu)成的程序執(zhí)行單元,這些操作要么都做,要么都不做,是一個(gè)不可分割的工作單位.事務(wù)具有原子性事務(wù)包含的所有操作,要么做完,要么不做,一致性事務(wù)開始前和事務(wù)結(jié)束后,數(shù)據(jù)庫都保持一致性,隔離性對(duì)于兩個(gè)事,持久性即使發(fā)生故障,也無法改變其永久性超碼superkey：是一個(gè)或多個(gè)屬性的集合,這些屬性的集合可以使我們?cè)谝粋€(gè)關(guān)系中唯一地標(biāo)識(shí)一個(gè)元組候選碼CandidateKey:關(guān)系中的一個(gè)屬性組

5、,其值能唯一標(biāo)識(shí)一個(gè)元組.假設(shè)附屬性組中去掉任何一個(gè)屬性,它就不具有這一性質(zhì)了,這樣的屬性組稱作候選碼.主碼PrimaryKey:進(jìn)行數(shù)據(jù)庫設(shè)計(jì)時(shí),從一個(gè)關(guān)系的多個(gè)候選碼中選定一個(gè)作為主碼.主碼不可為空.實(shí)體完整性：關(guān)系的主碼中的屬性值不能為空值假設(shè)主碼為空,那么出現(xiàn)不可標(biāo)識(shí)的實(shí)體,這是不容許的空值：不知道或無意義.參照完整性：如果關(guān)系R2的外部碼Fk與關(guān)系R1的主碼Pk相對(duì)應(yīng),那么R2中的每一個(gè)元組的Fk值或者等于R1中某個(gè)元組的Pk值,或者為空值.常用完整性約束：主碼約束；唯一性約束；非空值約束：NOTNULL;參照完整性約束關(guān)系代數(shù)運(yùn)算：根本運(yùn)算：一元運(yùn)算：選擇、投影、更名；多元運(yùn)算：笛

6、卡兒積、并、集合差；其它運(yùn)算：集合交、.連接、自然連接、除、賦值空值：不知道或不存在.空值的表現(xiàn)：參與算術(shù)運(yùn)算：結(jié)果為Null;參與比擬運(yùn)算：結(jié)果為Null;參與邏輯運(yùn)算：1、Nullortrue=ture2、Nullandfalse=false3、其它情況結(jié)果為null空值是一種狀態(tài),不是一個(gè)明確的值空值測(cè)試：isnotnull例如:whereAGEisnull,不可寫為whereAGE=null易錯(cuò)點(diǎn)：除isnotnull之外,空值不滿足任何查找條件；對(duì)于isnull,為空那么true,否那么false;不會(huì)返回null;如果null參與聚集運(yùn)算,那么除count*之外其他聚集函數(shù)都忽略n

7、ull;對(duì)于聚集函數(shù),假設(shè)車入集合為空,count返回0,其他返回null.例：selectcount*fromSC結(jié)果為6例：selectcountscorefromSC結(jié)果為4不計(jì)入那兩個(gè)null表的刪除：DROPtable表名命令元組的刪除：deletefrom表名where條件表達(dá)式從表中刪除符合條件的元組,如果沒有where語句,那么刪除所有元組建立索引格式：createclusterindexs-indexonSSN可以動(dòng)態(tài)地定義索引,即可以隨時(shí)建立和刪除索引；一個(gè)表上可建多個(gè)索引.索引可以提升查詢效率,但索引過多耗費(fèi)空間,且降低了插入、刪除、更新的效率SQL根本語句格式SELEC

8、TALL|DISTINCT,目標(biāo)列表達(dá)式FROM襲名或視圖名,表名或視圖名WHERE條件表達(dá)式GROUPBYHAVINGORDERBYASC|DESCGROUPBY?句：對(duì)查詢結(jié)果按指定列的值分組,該屬性列值相等的元組為一個(gè)組.通常會(huì)在每組中使用聚集函數(shù)HAVING短語：篩選出只有滿足指定條件的組ORDERBYF句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序WHER由的運(yùn)算符算術(shù)比擬：,=,=,或！=邏輯：and,or,not不使用rAV,不支持集合成員資格：in,notin謂詞：exists,notexists,all,some,unique,between.and.,notbetween.an

9、d,like,notlike,isnull,isnotnull聚集函數(shù)：avg,min,max,sum,count.集合：union,intersect,except/minus常用SQL語句例如：例如1給出所有老師的信息select*fromPROF例如2Selectall:不去重復(fù)元組；Selectdistinct:去重復(fù)元組；r.*:表r的全部屬性例如3找出工資低于500的職工的姓名、工資、系別selectPNAME,SAL,DNAMEfromPROF,DEPTwhereSAL50例如8查詢有3門以上課程是90分以上的學(xué)生的學(xué)號(hào)及(90分以上的)課程數(shù)SELECTSno,COUNT(*)

10、FROMSCWHEREscore=90GROUPBYSnoHAVINGCOUNT(*)=3;集合操作：集合并：union集合交：intersect集合差：except(minus)提示集合操作自動(dòng)去除重復(fù)元組,如果要保存重復(fù)元組的話,必須用all關(guān)鍵詞指明測(cè)試集合成員資格in;測(cè)試集合是否為空exists;測(cè)試集合是否存在重復(fù)元組unique視圖的特點(diǎn)：虛表,是從一個(gè)或幾個(gè)根本表(或視圖)導(dǎo)出的關(guān)系；只存放視圖的定義,不會(huì)出現(xiàn)數(shù)據(jù)冗余；基表中的數(shù)據(jù)發(fā)生變化,從視圖中查詢出的數(shù)據(jù)也隨之改變；查詢時(shí),視圖名可以出現(xiàn)在任何關(guān)系名可以出現(xiàn)的地方；視圖的定義方式不支持遞歸定義.簡(jiǎn)單屬性：不可再分的屬性如

11、學(xué)號(hào)、年齡、性別復(fù)合(Composite)屬性：可以劃分為更小的屬性.如號(hào)=區(qū)號(hào)+本地號(hào)；出生日期=年+月+日單值屬性：每一個(gè)特定的實(shí)體在該屬性上的取值唯一.如學(xué)生的學(xué)號(hào),年齡、性別、系別等多值屬性：某個(gè)特定的實(shí)體在該屬性上有多于一個(gè)的取值.如學(xué)生(學(xué)號(hào),聯(lián)系)(多值用雙圈表示；派生屬性用虛圈表示)弱實(shí)體集的分辨符用下劃虛線標(biāo)明雙橫線表示全部參與,單橫線表示局部參與ER圖例如:例如1例如2范式：范式是對(duì)關(guān)系的不同數(shù)據(jù)依賴程度的要求第一范式：如果關(guān)系模式R所有屬性都源自原子域,稱模式屬于1NF第二范式：假設(shè)R71NF,且每個(gè)非主屬性完全依賴于碼,那么稱R?2NF數(shù)據(jù)庫作用？外鍵約束性？這是咱們做

12、過的實(shí)驗(yàn),參考下吧1找出沒有選修任何課程的學(xué)生的學(xué)號(hào)、姓名.createtabletest2_01asselectsid,namefrompub.studentwheresidnotin(selectsidfrompub.student_course)2找出至少選修了學(xué)號(hào)為“202100130417的學(xué)生所選修的一門課的學(xué)生的學(xué)號(hào)、姓名createtabletest2_02asselectsid,namefrompub.studentwheresidin(selectdistinctsidfrompub.student_coursewherecidin(selectdistinctcidfro

13、mpub.student_coursewheresid=202100130417)3找出至少選修了一門其先行課程號(hào)為“300002號(hào)課程的學(xué)生的學(xué)號(hào)、姓名.createtabletest2_03asselectsid,namefrompub.studentwheresidin(selectdistinctsidfrompub.student_coursewherecidin(selectdistinctcidfrompub.coursewherefcid=300002)4找出選修了“操作系統(tǒng)并且也選修了“數(shù)據(jù)結(jié)構(gòu)的學(xué)生的學(xué)號(hào)、姓名.createtabletest2_04asselectsid,

14、namefrompub.studentwheresidin(selectdistinctsidfrompub.student_coursewherecid=(selectcidfrompub.coursewherename=操作系統(tǒng))intersect(selectdistinctsidfrompub.student_coursewherecid=(selectcidfrompub.coursewherename=數(shù)據(jù)Z構(gòu))5查詢20歲的所有有選課的學(xué)生的學(xué)號(hào)、姓名、平均成績(jī)(avg_score,此為列名,下同)(平均成績(jī)四舍五入到個(gè)位)、總成績(jī)(sum_score)Test2_05有四個(gè)列,

15、并且列名必須是：sid、name、avg_score、sum_score.通過下面方式實(shí)現(xiàn)歹U名定義createtabletest2_05asselectsid,name,(表達(dá)式)avg_score,(表達(dá)式)sum_scorefrom答案1createtabletest2_05as(selecttone.sid,name,round(avg(score)asavg_score,sum(score)assum_scorefrompub.studenttone,pub.student_coursettwowhereage=20andtone.sid=ttwo.sidgroupbytone.si

16、d,name)答案2createtabletest2_05asselectpub.student.sid,name,round(avg(score)asavg_score,sum(score)assum_scorefrompub.student,pub.student_coursewhereage=20andpub.student.sid=pub.student_course.sidgroupbypub.student.sid,name6查詢所有課以及這門課的最高成績(jī),test2_06有兩個(gè)列：課程號(hào)cid、最高成績(jī)max_scorecreatetabletest2_06asselectcid

17、,max(score)asmax_scorefrompub.student_coursegroupbycid7查詢所有不姓張、不姓李、也不姓王的學(xué)生的學(xué)號(hào)sid、姓名namecreatetabletest2_07asselectsid,namefrompub.studentwherenamenotlike張andnamenotlike李andnamenotlike王8查詢學(xué)生表中每一個(gè)姓氏及其人數(shù)(不考慮復(fù)姓),test2_08有兩個(gè)列：second_name、p_countcreatetabletest2_08asselectsubstr(name,0,1)assecond_name,count(*)asp_countfrompub