資料庫系統(tǒng)DatabaseSystems培訓課件

資料庫系統(tǒng)

DatabaseSystemsIntroduction主講人:劉佳灝1資料庫系統(tǒng)相關(guān)名詞資料庫(database)集合的特性資料庫系統(tǒng)資料庫管理系統(tǒng)(databasemanagementsystems)2資料庫系統(tǒng)資料庫資料庫(DB)資料庫是相關(guān)資料的集合集合的特性集合中的元素沒有順序性集合中的元素不會重複集合可以是空的3資料庫系統(tǒng)資料庫管理系統(tǒng)資料庫管理系統(tǒng)(DBMS)是許多程式的集合，讓使用者得以定義、建構(gòu)，與處理資料庫定義:資料的型態(tài)、結(jié)構(gòu)與限制建構(gòu):在DBMS的控制下,將資料儲存到媒體處理:查詢、更新4資料庫系統(tǒng)資料庫系統(tǒng)

DatabaseSystemsDatabaseSystemConcepts6資料庫系統(tǒng)簡介資料模型(DataModel)是提供資料抽象化的主要工具DataModel是用來描述資料庫結(jié)構(gòu)的一種概念DataModel的內(nèi)容包含結(jié)構(gòu)的表示方式限制運算(操作)7資料庫系統(tǒng)資料模型DataModelDataModel使用的概念實體Entity屬性Attribute關(guān)係Relationship客戶購買商品商品編號商品名稱尺寸顏色價格客戶編號客戶姓名電話時間訂購量8資料庫系統(tǒng)關(guān)聯(lián)式資料模型概念

RelationalModelConcepts關(guān)聯(lián)式模型將資料庫表示成一個關(guān)聯(lián)的集合相關(guān)名詞關(guān)聯(lián)(Relation)值組(Tuple)屬性(Attribute)定義域(Domain)9資料庫系統(tǒng)RelationSchemas–名詞簡介PRODUCT值組屬性集合關(guān)聯(lián)表名稱顏色屬性尺寸屬性品名屬性價格屬性PidPnameSizeColorPrice11襯衫L藍10022帽子M藍20013夾克S紅30034牛仔褲L黑400品號屬性主鍵價格資料10資料庫系統(tǒng)RelationSchemas–特性與內(nèi)容關(guān)聯(lián)表的屬性數(shù)目稱為它的維度一個關(guān)聯(lián)表中任兩個屬性名稱不可以重覆「關(guān)聯(lián)表綱要」(RelationSchema)包含:關(guān)聯(lián)表名稱屬性集宣告所有屬性的相對資料型態(tài)宣告主鍵的宣告外來鍵(ForeignKey)之宣告11資料庫系統(tǒng)DBMS的架構(gòu)與資料獨立ThreeSchema(三綱要架構(gòu))外層(景觀層)ExternalLevel:外部綱要概念層ConceptualLevel:慨念綱要內(nèi)層InternalLevel:內(nèi)部綱要資料獨立邏輯資料獨立實體資料獨立12資料庫系統(tǒng)ThreeSchema外層(又稱為景觀層)ExternalLevel:外部綱要描述特定使用者所感興趣的資料庫部分隱藏儲存結(jié)構(gòu)等其他細節(jié)概念層ConceptualLevel:慨念綱要描述整個資料庫的結(jié)構(gòu)隱藏儲存等細節(jié)著重在實體、資料型態(tài)、關(guān)係、使用者的操作與限制內(nèi)層InternalLevel:內(nèi)部綱要用來描述資料庫的實際儲存結(jié)構(gòu)與路徑13資料庫系統(tǒng)DataIndependence資料獨立Logicaldataindependence(邏輯資料獨立)改變conceptualschema時,不需改變externalschema(orapplication)Physicaldataindependence(實體資料獨立)

改變internalschema時,不需改變conceptualschema

14資料庫系統(tǒng)SQLDDL資料定義語言用來定義conceptschema與資料庫綱要之定義有關(guān)的操作,如資料庫的建立、表格的建立等命令DML資料處理語言對資料庫資料進行擷取、新增、刪除與修改等處理之命令僅對資料有所影響,與資料庫綱要定義無關(guān)15資料庫系統(tǒng)資料庫系統(tǒng)

DatabaseSystemsModelingUsingtheEntity-RelationshipApproach16資料庫系統(tǒng)屬性型態(tài)簡單(Simple)複合(Composite)單值(Single-valued)多值(Multi-valued)儲存(Stored)導出(Derived)17資料庫系統(tǒng)屬性型態(tài)簡單(simple)與複合(composite)姓名生日地址員工編號電話薪資員工姓名18資料庫系統(tǒng)地址里別隣號郵遞區(qū)號路別縣市別里隣應(yīng)該是簡單還是複合屬性?19資料庫系統(tǒng)屬性型態(tài)單值(single-valued)與多值(multi-valued)Ex:一個部門只有一個部門編號與部門名稱,某些部門可能存同時在兩個地點(例如:一個部門分別在臺中與彰化各有據(jù)點)部門名稱部門編號地點20資料庫系統(tǒng)屬性型態(tài)儲存(stored)與導出(derived)Ex:年紀可以經(jīng)由生日換算得到姓名姓名生日地址員工編號電話薪資員工年紀21資料庫系統(tǒng)屬性型態(tài)(範例二)Ex:一個客戶的交易次數(shù)可以經(jīng)由訂購紀錄中計算出來客戶編號交易次數(shù)姓名電話商品編號名稱價格日期交易量訂購22資料庫系統(tǒng)鍵值屬性一個具有唯一性的屬性假設(shè):每一個客戶的編號會唯一每一個商品的編號會唯一,且每一商品的名稱也會唯一客戶編號交易次數(shù)姓名電話商品編號名稱價格日期交易量訂購23資料庫系統(tǒng)實體,關(guān)係,屬性實體型態(tài)具有相同屬性集合的元素所形成的集合關(guān)係型態(tài)用來連接某些相關(guān)的實體型態(tài)屬性可以用來描述實體或關(guān)係學生學號姓名性別課程編號名稱教師日期成績選修24資料庫系統(tǒng)關(guān)係employeeWorksforDepartment關(guān)係型態(tài)的Degree是參與此關(guān)係的實體型態(tài)數(shù)目Ex:Works_for的Degree為2,又稱二元關(guān)係Relationship25資料庫系統(tǒng)關(guān)係型態(tài)的限制ConstraintsonRelationshipTypes基數(shù)率與參與基數(shù)率一個實體所能參與的關(guān)係案例數(shù)Ex:一個部門僅會有一個主管參與Ex:任何一個部門一定有一個主管(去管理它)26資料庫系統(tǒng)基數(shù)率(cardinalityratio)1:1的範例員工部門管理e1e2e3e4d1

d2d3r1r2r327資料庫系統(tǒng)基數(shù)率1:N的範例員工部門屬於e1e2e3e4d1

d2d3r1r2r3r428資料庫系統(tǒng)基數(shù)率N:M的範例員工專案參與e1e2e3e4P1

p2p3r1r2r3r4r5r629資料庫系統(tǒng)參與限制實體的存在是否依靠關(guān)係型態(tài)與另一實體產(chǎn)生關(guān)聯(lián)來決定部份參與(以單線表示)全部參與(以雙線表示) 員工部門管理部份參與全部參與30資料庫系統(tǒng)參與限制(範例二)課程實體的存在是否依靠開設(shè)關(guān)係型態(tài)與另一教師實體產(chǎn)生關(guān)聯(lián)來決定課程實體要存在必須所對應(yīng)的教師實體與開設(shè)關(guān)係都存在,該課程才會存在任何一個課程一定要有教師去開設(shè)它 教師課程開設(shè)部份參與全部參與31資料庫系統(tǒng)弱實體型態(tài)(WeakEntityType)WeakEntityTypes本身可以沒有任何的鍵值屬性透過另一實體型態(tài)的一些屬性所組成的相關(guān)特定實體(IdentifyingOwner)來辨認辨認關(guān)係(IdentifyingRelationship)WeakEntity在沒有IdentifyingOwner的狀況下是無法辨認的,所以WeakEntity總是有一個完全參與的限制來與它的辨認關(guān)係產(chǎn)生關(guān)聯(lián)32資料庫系統(tǒng)姓名生日性別關(guān)係WeakentityIdentifyingrelationship員工眷屬擁有全部參與Identifyingowner員工編號弱實體型態(tài)(WeakEntityType)33資料庫系統(tǒng)適當名稱大寫字母表示實體與關(guān)係型態(tài)屬性名稱字首大寫實體多使用名詞關(guān)係則採用動詞方向：由左到右,由上到下34資料庫系統(tǒng)範例:讀者借閱書籍系統(tǒng)必須紀錄讀者的身份證號碼、姓名、生日,與聯(lián)絡(luò)方式,其中聯(lián)絡(luò)方式包含了電話與地址資料系統(tǒng)紀錄了館藏中每一本書籍的書名與作者,雖然有些書籍有兩個以上的作者,但一定都只有一個唯一書號.為了掌握每本書的熱門程度,系統(tǒng)必須提供每本書籍的曾經(jīng)被借閱的次數(shù),以提供讀者參考當一個讀者要借閱書籍時,系統(tǒng)必須紀錄借閱的日期,以便管理.而每一位讀者至少都有借閱過一次以上書籍的紀錄35資料庫系統(tǒng)範例:讀者借閱書籍讀者書籍借閱身份證號碼生日聯(lián)絡(luò)方式電話地址姓名書名書號作者借閱次數(shù)日期作者36資料庫系統(tǒng)資料庫系統(tǒng)

DatabaseSystemsRelationalDatabaseConstraints37資料庫系統(tǒng)關(guān)聯(lián)式資料模型的限制定義域限制鍵值限制實體完整性限制參考完整性限制38資料庫系統(tǒng)屬性與定義域(Domain)欄位中所允許的數(shù)值(Value)資料稱為定義域(Domain)定義域D是基元(Atomic)數(shù)值資料的集合Domain中的數(shù)值是不可分割個所以稱為基元Dom(Sex)={男生,女生}

則”男生”與”女生”皆為sex屬性的的合理基元值Ex:age:大於0的整數(shù)ID_numbers:一個英文字母加上九位數(shù)字的集合年級:介於1~4之間的數(shù)值39資料庫系統(tǒng)定義域限制定義域限制指定了一屬性A的值必須在該屬性值的定義域Dom(A)裡的一個基元值定義方式列舉範圍格式資料型態(tài)40資料庫系統(tǒng)鍵值限制關(guān)聯(lián)可視為一個集合,所含值組必須相異t1[SK]t2[SK]SK:超鍵(superkey)每個關(guān)聯(lián)至少存在一個SK≠41資料庫系統(tǒng)包含SK的屬性集合亦為SKEx:Sid是一SK,所以包含Sid的屬性集合皆為SK

{Name,Sid}、{Sid,Tel_num}、{Sid,Name,Age}鍵值屬性可以用來確認關(guān)聯(lián)中的唯一的值組鍵值是由關(guān)聯(lián)綱要中屬性的意義來決定NameSidTel_numSexAge張大森s0023047111111男生22陳小東s0052047112235女生25林中正s0043047223344男生2342資料庫系統(tǒng)主鍵(PrimaryKey)的產(chǎn)生鍵值是一個最小的SuperKey若有一個以上的鍵值,則每一個鍵值都稱為是一個候選鍵(CandidateKey)必須從CandidateKey中選擇一個作為主鍵(PrimaryKey)主鍵的特性唯一性最小性43資料庫系統(tǒng)關(guān)聯(lián)式資料模型的限制定義域限制鍵值限制實體完整性限制參考完整性限制與關(guān)聯(lián)中的主鍵(PK)有關(guān)44資料庫系統(tǒng)鍵值限制與實體完整性鍵值限制(KeyConstraint)主鍵值不能重複實體完整性(EntityIntegrityConstraint)主鍵值不能有空值45資料庫系統(tǒng)參考完整性若一個關(guān)聯(lián)滿足定義域限制、鍵值限制、與實體完整性限制,我們只也能相信個別的關(guān)聯(lián)本身是合理的,但並無法保證關(guān)聯(lián)與關(guān)聯(lián)之間的資料狀態(tài)是一致的參考完整性限制是指定在兩個關(guān)聯(lián)之間,用以維持兩個關(guān)聯(lián)值組的一致性46資料庫系統(tǒng)外鍵與參考R1的外鍵FK(ForeignKey)FK的屬性與另一關(guān)聯(lián)綱要R2的PrimaryKey有相同的定義域(FK參考R2)R1的值組t1中的FK可以和R2的某一值組t2的PK相符,或是空值t1[FK]=t2[PK]47資料庫系統(tǒng)參考完整性FK的屬性值必須在所參考之關(guān)聯(lián)的PK屬性中被找到,或者為Null關(guān)聯(lián)式資料庫之參考完整性限制,藉由FK與PK間的參考關(guān)係,以達到關(guān)聯(lián)表之間的一致性48資料庫系統(tǒng)EMPLOYEE.Dno與DEPARTMENT.Dnum之間的一致性EidNameAgeSexDnoe1張三28男生1e2李四33女生1e3王五24男生3DnumDnameLocation1行政臺北3研發(fā)新竹EMPLOYEEDEPARTMENT49資料庫系統(tǒng)若一員工不屬任何部門時,EMPLOYEE.Dno

可以是空值DnumDnameLocation1行政臺北3研發(fā)新竹EMPLOYEEDEPARTMENTEidNameAgeSexDnoe1張三28男生1e2李四33女生1e3王五24男生3e4陳七31女生50資料庫系統(tǒng)限制的實作使用DDL指定限制

createtableorders (nointnotnull, idintnotnull, quantityint, odatedatetimedefaultgetdate(),

/*以系統(tǒng)日期及時間為odate的初值*/

primarykey(no,id,odate),

/*宣告複合欄位(no,id,odate)為主鍵*/

foreignkey(no)referencesbookstores, foreignkey(id)referencesbooks, check(quantity>0andquantity<5001))

/*設(shè)定quantity值的限制條件*/51資料庫系統(tǒng)資料庫系統(tǒng)

DatabaseSystemsFunctionalDependenciesandNormalization52資料庫系統(tǒng)重要觀念良好的語意減少tuple的重複值減少tuple的空值不允許假值組(SpuriousTuples)的存在53資料庫系統(tǒng)語意設(shè)計上的優(yōu)劣設(shè)計易於解釋的Schema一個實體僅描述單一意義,勿結(jié)合多個於一關(guān)聯(lián)不良的設(shè)計ENAMEEIDSEXTELDNUMDNAMEDMGREMP_DEPT54資料庫系統(tǒng)tuple的重複值問題ENAMEEIDSEXTELDNUMDNAMEDMGR陳花依E1女生71111111行政E1林大城E2男生71111121行政E1陳富強E3男生71111132研發(fā)E3張珠珠E4女生71111142研發(fā)E3莊勝為E5男生71111153生產(chǎn)E5EMP_DEPT55資料庫系統(tǒng)減少tuple的空值EIDENAMEADDRESSTELPNOPNAMEE1陳花依TAIPEI1234561PROJ_1E2林大城2E3陳富強HOUSTON6543213PROJ_3E2林大城3PROJ_356資料庫系統(tǒng)不允許假值組的存在適當?shù)胤指铌P(guān)聯(lián)可以有效減少空間的浪費,並藉由正規(guī)化與關(guān)聯(lián)式運算(處理)來維持資料的完整性並滿足各項操作假值組的問題,係因不良的關(guān)聯(lián)表格切割所導致關(guān)聯(lián)表的分割必須考量FK與PK57資料庫系統(tǒng)我們可以由分割成,但無法由回覆成原來的EIDPNOHOURENAMEPNAMEPLOCE1132陳花依Proj1TPE127陳花依Proj2CHE2340林大城Proj3CHE3120陳富強Proj1TPE4220張珠珠Proj3KSEIDPNOHOURPNAMEPLOCE1132Proj1TPE127Proj2CHE2340Proj3CHE3120Proj1TPE4220Proj3KSENAMEPLOC陳花依TP陳花依CH林大城CH陳富強TP張珠珠KS58資料庫系統(tǒng)FunctionalDependencies功能相依是資料庫的兩個屬性集合的限制關(guān)聯(lián)表分割的重要參考依據(jù)表示法(XY)與意義RelationR中的任意兩個tuplest1與t2,若t1[X]=t2[X],則使得t1[Y]=t2[Y]Tuple中attributeY的值是依靠X來決定稱為X到Y(jié)的功能相依,或Y功能相依於XX可以決定Y59資料庫系統(tǒng)XYXYiffr(R)的X值相同,則Y值必定相同Keypoints若一關(guān)聯(lián)不存在超過一個以上的X值,則表示X是一個CandidateKey若XY並不表示YX成立功能相依是一屬性語意的特性功能相依無法從特定的關(guān)聯(lián)中推論得到60資料庫系統(tǒng)功能相依性與(非)主要屬性在RelationR中,若一Attribute(s)為任意R的成員(每一Tuple都有該屬性),此Attribute(s)稱為主要屬性,否則則稱為非主要屬性EID與PNO為EMP_PROJ的主要屬性,其餘屬非主要屬性EIDPNOHOURENAMEPNAMEPLOCfd1fd2fd3EMP_PROJ61資料庫系統(tǒng)正規(guī)化第一正規(guī)化型式(1NF)byE.F.Codd第二正規(guī)化型式(2NF)byE.F.Codd第三正規(guī)化型式(3NF)byE.F.Codd廣義3NF─BCNF(Boyce/CoddNormalForm)

第四正規(guī)化型式(4NF)byR.Fagin第五正規(guī)化型式(5NF)byR.Fagin

又稱為Projection/JoinNormalForm(PJ/NF)62資料庫系統(tǒng)1NF1NF：所有屬性定義域僅含基元值(Atomic) (不能有複合屬性/多值的存在)不符合1NFDLOCS的定義域包含基元值,但某些值組包含這些值組的集合DLOCS屬性並非基元值DNUMDNAMEDMGRDLOCS1行政E1{TP,CH,TC}2研發(fā)E3{TP}3生產(chǎn)E5{KS}DEPARTMENT63資料庫系統(tǒng)non-1NF分割(Case1)將違反1NF的屬性從原來的關(guān)聯(lián)中刪除,並與主鍵放到一個個別的關(guān)聯(lián)DEPARTMENTDEPT_LOCATIONSDNUMDNAMEDMGRDLOCSDNUMDLOCDNUMDLOC1TP1CH1TC2TP3KS64資料庫系統(tǒng)1NF1NF不允許有複合屬性的存在,這些屬性本身是屬於多值巢狀關(guān)聯(lián):一個值組有一個關(guān)聯(lián)存在PNO為巢狀關(guān)聯(lián)PROJS的部份主鍵EIDENAMEPROJSPNOHOURSEIDENAMEPNOHOURSE1陳花依13227E2林大城340E3陳富強120212E4張珠珠21431410142014E5莊勝為30312010EMP_PORJ65資料庫系統(tǒng)non-1NF分割EIDENAMEPNOHOURSEIDENAMEEIDPNOHOURS主鍵與部份主鍵形成新關(guān)聯(lián)的主鍵EMP_PORJ1NF(Case2)66資料庫系統(tǒng)2NF2NF是以完全功能相依為基礎(chǔ)完全功能相依若XY為完全功能相依,則從X中移除任一屬性A,將使得此功能相依性不再成立{EID,PNO}HOUR(完全相依)EIDENAME(去掉EID後不滿足相依性)PNO{PNAME,PLOC}(去掉PNO後不滿足相依性){EID,PNO}ENAME(不滿足完全相依性,部份相依)EIDPNOHOURENAMEPNAMEPLOCfd1fd2fd3EMP_PROJ67資料庫系統(tǒng)2NF2NF若關(guān)聯(lián)R中的每一個非主要屬性是完全功能相依於R的PrimaryKey則稱RelationR屬於2NFnon-2NF的處理Non-2NF的Relation可以進一部被分解成多個2NF的Relations所產(chǎn)生的新關(guān)聯(lián)將只有非主要屬性與在主鍵內(nèi)其完全功能相依的部份68資料庫系統(tǒng)2NFEIDENAMEEIDPNOHOURENAMEPNAMEPLOCPNOPNAMEPLOCfd1fd2fd3EMP_PROJEIDPNOHOURfd1fd2fd32NF69資料庫系統(tǒng)3NF概念基礎(chǔ)3NF是以遞移相依性為基礎(chǔ)遞移相依性Z不屬於任何鍵值的子集合,且XZ,ZY成立DNUM不屬於任何鍵值的子集合,且EID

DNUM,DNUM{DNAME,DMGR}成立存在遞移相依

…(不符合3NF)ENAMEEIDSEXTELDNUMDNAMEDMGREMP_DEPT70資料庫系統(tǒng)3NF定義RelationR是3NF:RelationR是2NF且沒有非主要屬性是遞移相依於主鍵Non-3NF的Relation可以進一部被分解成多個3NF的Relations所產(chǎn)生的新關(guān)聯(lián)將只有非主要屬性及與其完全功能相依的部分71資料庫系統(tǒng)3NFENAMEEIDSEXTELDNUMDNUMDNAMEDMGR員工部門ENAMEEIDSEXTELDNUMDNAMEDMGREMP_DEPT3NF72資料庫系統(tǒng)Boyce-Codd正規(guī)式(BCNF)BCNF比3NF更嚴格,BCNF成立3NF一定成立,但3NF不一定屬於BCNFBCNF的定義在RelationR中每一個功能相依XA都滿足X是R的SuperKeyExampleCNAME={M,L}ASIZE=0.5、0.6、0.7、0.8、0.9、1.0則CNAME=MASIZE=1.1、1.2、…、2.0則CNAME=L則存在一個FD:ASIZECNAME假設(shè)有數(shù)千筆Tuples73資料庫系統(tǒng)3NFBCNFLOTS依舊符合3NF,但其實CNAME只有M與L兩種值P_IDLOTASIZECNAME的值將從數(shù)千筆減少到十幾筆ASIZECNAMEP_IDCNAMELOTASIZELOTSBCNF74資料庫系統(tǒng)資料庫系統(tǒng)

DatabaseSystemsSQL語法75資料庫系統(tǒng)CIDintPIDintODATEdatetimeQUANint112005/07/0150122005/07/0230132005/07/0320142005/08/0430152005/08/0540262005/08/0150212005/09/0260322005/09/0120332005/11/0330442005/11/0420452005/12/1110562005/12/2140PIDintPNAMEcharCOLORcharPRICEint1襯衫紅色1002長褲藍色2003長褲紅色3004長褲紅色4005襯衫水藍色5006襯衫綠色6007長褲淺綠色7008長褲深藍色800CIDintNAMEcharRANKintAGEint1ALEX1152BOB2123CATHY3254DAVIE2335ELLA124CUSTOMERORDERSPRODUCT資料庫範例76資料庫系統(tǒng)資料庫的建立與刪除CreateDatabase語法:Createdatabase資料庫名稱範例:CreatedatabasesaledbDrop

Database語法:Drop

database資料庫名稱範例:Drop

databasesaledb77資料庫系統(tǒng)關(guān)聯(lián)表的建立與刪除建立客戶(CUSTOMER)關(guān)聯(lián)表Createtablecustomer(cidintnotnull,namevarchar(50),rankint,ageint,primarykey(cid))建立商品(PRODUCT)關(guān)聯(lián)表Createtableproduct(pidintnotnull,pnamevarchar(50),colorvarchar(10),priceint,primarykey(pid))78資料庫系統(tǒng)關(guān)聯(lián)表的建立與刪除建立交易(ORDERS)關(guān)聯(lián)表Createtableorders(cidintnotnull,pidintnotnull,odatedatetimedefaultgetdate(),quanint,primarykey(cid,pid,odate),foreignkey(cid)referencescustomer,foreignkey(pid)referencesproduct)刪除關(guān)聯(lián)表droptable關(guān)聯(lián)表名稱79資料庫系統(tǒng)SQL-DML資料的新增資料擷取(查詢)資料修改資料刪除80資料庫系統(tǒng)INSERT指令

INSERTINTO

關(guān)聯(lián)表(欄位1,欄位2,欄位3)VALUES(值1,值2,值3)Ex1:insertintocustomer(cid,name,rank,age)VALUES(1,'ALEX',1,15)Ex2:INSERTINTOproduct(pid,pname,price)VALUES(1,'襯衫',100)Ex3:INSERTINTOorders(cid,pid,quan)VALUES(1,1,50)81資料庫系統(tǒng)資料擷取(查詢)

select[all][distinct][top<n>|<npercent>]<欄位>|<運算式>[as<自訂欄位標題>]from<關(guān)聯(lián)表>[where<搜尋條件>|<合併條件>][groupby<用以分組的欄位>[having<分組條件>]][orderby<欄位>|<運算式>]

82資料庫系統(tǒng)SQLQUERY基本組成SELECT<attributelist>FROM<tablelist>WHERE<condition>Ex1:擷取年紀大於20歲的客戶姓名與年紀SELECTNAME,AGE FROMCUSTOMER WHEREAGE>20CIDintNAMEcharRANKintAGEint1ALEX1152BOB2123CATHY3254DAVIE2335ELLA124CUSTOMERNAMEAGECATHY25DAVIE33ELLA2483資料庫系統(tǒng)SQLQUERYEx2:擷取所有商品編號與品名屬性SELECTPID,PNAME FROMPRODUCT

Ex3:擷取所有價格低於500元且顏色屬於紅色的商品項目資訊SELECT* FROMPRODUCT WHEREPRICE<500

ANDCOLOR='紅色'PIDintPNAMEcharCOLORcharPRICEint1襯衫紅色1002長褲藍色2003長褲紅色3004長褲紅色4005襯衫水藍色5006襯衫綠色6007長褲淺綠色7008長褲深藍色800PRODUCTPIDPNAMECOLORPRICE1襯衫紅色1003長褲紅色3004長褲紅色40084資料庫系統(tǒng)SQLQUERYEx4:擷取所有交易量大於40的交易,列出這些交易的客戶姓名,商品編號與交易量SELECTNAME,PID,QUAN FROMCUSTOMER,ORDERS WHERECUSTOMER.CID=ORDERS.CIDANDQUAN>40

SELECTNAME,PID,QUAN FROMCUSTOMERC,ORDERSO WHEREC.CID=O.CIDANDQUAN>40 Ex5:擷取所有屬於CATHY客戶的交易,列出這些交易的商品名稱,價格,與交易量

SELECTPNAME,PRICE,QUAN FROMCUSTOMERC,ORDERSO,PRODUCTP WHEREC.CID=O.CIDANDO.PID=P.PIDANDNAME='CATHY'85資料庫系統(tǒng)DISTINCT與IN的用法Ex6:擷取客戶等級的類型SELECTDISTINCTRANK FROMCUSTOMEREx7:擷取曾經(jīng)銷售過的書籍編號SELECTDISTINCTPID FROMORDERSEx8:擷取屬於1,3,或5號客戶的交易紀錄,列出客戶編號與商品編號 SELECTCID,PID FROMORDERS WHERECID=1ORCID=3ORCID=5 SELECTCID,PID FROMORDERS WHERECIDIN(1,3,5)86資料庫系統(tǒng)IN與巢狀查詢的應(yīng)用Ex9:擷取所有交易量大於40的交易,列出這些交易的商品名稱,顏色與價格 SELECTPNAME,COLOR,PRICE

FROMPRODUCT WHEREPIDIN(SELECTDISTINCTPID FROMORDERS WHEREQUAN>40)

87資料庫系統(tǒng)聚合函數(shù)(Aggregates)聚合函數(shù):Count,Sum,Avg,Max,Min計算完後的結(jié)果只有一個答案Ex10:擷取所有商品中最大,最小,與平均之價格 SELECTMAX(PRICE),MIN(PRICE),AVG(PRICE) FROMPRODUCTEx11:擷取所有屬於ALEX客戶的交易之次數(shù)與總交易量 SELECTCOUNT(*),SUM(QUAN) FROMCUSTOMERC,ORDERSO WHEREC.CID=O.CIDANDNAME='ALEX'Ex12:屬於1號商品的交易共有幾筆,總交易量又是多少? SELECTCOUNT(*)ASOTIMES,SUM(QUAN)ASOTOTAL FROMORDERS WHEREPID=188資料庫系統(tǒng)GROUPBY與HAVINGGROUPBY具有群組化特性,而Having可用來篩選符合條件的Group,正如Where子句是用來篩選出符條件的值組Ex13:擷取各項商品的交易次數(shù)與總交易量 SELECTCOUNT(*),SUM(QUAN) FROMORDERS GROUPBYPIDEx14:擷取各項商品的交易次數(shù)與總交易量,列出總交易量高於55的商品交易次數(shù)與總交易量 SELECTCOUNT(*),SUM(QUAN) FROMORDERS GROUPBYPID

HAVINGSUM(QUAN)>5589資料庫系統(tǒng)範例Ex15:擷取年紀低於18歲客戶的交易紀錄,求取各項商品的的總交易量,列出其中總交易量高於35的商品編號與總交易量 SELECTPID,SUM(QUAN) FROMCUSTOMERC,ORDERSO WHEREC.CID=O.CIDANDAGE>18 GROUPBYPID HAVINGSUM(QUAN)>3590資料庫系統(tǒng)Like的敘述%:以百分比符號表示零個或多個字元

:以底線符號表示任一個字元僅適用於字元型態(tài)的屬性Ex16:擷取商品顏色屬性中,含有'藍'的商品名稱與價格 SELECTPNAME,PRICE FROMPRODUCT WHERECOLORLIKE'%藍%'Ex17:擷取姓名中第二個字母為'L'的客戶編號與姓名 SELECTCID,NAME FROMCUSTOMER WHERENAMELIKE'_L%'91資料庫系統(tǒng)排序ORDERBY一般語法組成SELECT*FROMTABLEWHERECONDITIONORDERBYATTRIBUTE<ASC|DESC>排列方式ASC(由小至大)DESC(由大至小)92資料庫系統(tǒng)排序ORDERBYEx18:擷取所有價格低於500的商品名稱與價格,並依價格由低至高排列輸出SELECTPNAME,PRICE FROMPRODUCT WHEREPRICE<500

ORDERBYPRICEASCEx19:擷取所有交易量低於55的交易紀錄,列出客戶編號,商品編號與交易量,並依交易量由高至低排列,若交易量相同時,則依客戶編號由小至大排列SELECTCID,PID,QUAN FROMORDERS WHEREQUAN<55

ORDERBYQUANDESC,CIDASC93資料庫系統(tǒng)UPDATE指令

UPDATE關(guān)聯(lián)表SET欄位1=值1,欄位2=值2WHERE條件Ex:將1號客戶的年紀修改為20歲UPDATECUST