數據庫基礎及測試應用

第四章數據庫基礎及測試應用支文瑜Winnie_zhi@基本理論數據模型Sql語言Oracle基礎數據庫應用測試本章目標數據庫技術的發展數據庫基本概念關系型數據庫非關系型數據庫一、基本概念數據管理技術的發展歷程，大體上經歷了幾個階段：人工管理階段文件系統階段數據庫階段分布式數據庫階段數據庫技術的發展早期的計算機沒有系統軟件的支持，程序員不但要負責處理數據還要負責組織數據。這使得程序員直接與物理設備打交道，從而使程序與物理設備高度相關，一旦物理存儲發生變化，程序必須全部修改，程序沒有任何獨立性。人工管理階段操作系統中的文件系統是專門的數據管理軟件，它的出現將程序員從直接與物理設備打交道的沉重負擔中解脫出來。文件系統實現了按名存取，程序員只要將需要管理的數據組織成文件并對文件命名，以后就可以按文件名邏輯地存取文件中的數據，不必考慮文件的物理存儲，這項工作由文件系統來實現。數據組織成文件后，程序有了較大程度的物理獨立性，即當數據的物理存儲發生某些變化時，不會引起整個程序的作廢。但是，文件系統管理數據仍有許多缺點，主要是數據冗余度大和數據與程序之間缺乏獨立性。文件系統階段針對文件系統的缺點，后來出現了數據庫技術。數據庫（Database，簡稱DB）是長期存儲在計算機內的、有組織的、可共享的數據集合。與文件系統相比，數據庫技術是面向系統的，而文件系統則是面向應用的。數據庫階段1951：Univac系統使用磁帶和穿孔卡片作為數據存儲1956：IBM公司在其Model305RAMAC中第一次引入了磁盤驅動器1961：通用電氣(GE)公司的CharlesBachman開發了第一個數據庫管理系統——IDS1969：E.F.Codd發明了關系數據庫。1973：由JohnJ.Cullinane領導Cullinane公司開發了

IDMS——一個針對IBM主機的基于網絡模型的數據庫。1976：

Honeywell公司推出了MulticsRelationalDataStore——第一個商用關系數據庫產品。1979：

Oracle公司引入了第一個商用SQL關系數據庫管理系統。1983：

IBM推出了DB2數據庫產品。1985：為Procter&Gamble系統設計的第一個商務智能系統產生。1991：

W.H.“Bill”Inmon發表了”構建數據倉庫”。數據庫發展大事記分布式數據庫系統的基礎是集中式數據系統技術和計算機網絡技術的結合，但并不是簡單地把集中式數據庫通過網絡連接構成分布式數據庫。分布式數據庫是對地理上分散的用戶對數據共享的數據庫技術發展的成果。分布式數據庫階段數據庫（Database，DB）數據庫系統（DatabaseSystem，DBS）數據庫管理系統（DatabaseManagementSystem，DBMS）數據庫管理員（DatabaseAdministrator,DBA）數據庫基本概念數據庫系統由四部分組成，它們是數據庫、硬件、軟件和人員；數據庫：數據庫系統的數據源。硬件：支持系統運行的計算機硬件設備。包括CPU、內存、外存及其它外部設備。軟件：包括操作系統、數據庫管理系統、應用開發工具和應用系統。人員：數據庫系統中的主要人員有：數據庫管理員、系統分析員和數據庫設計人員、應用程序開發人員和最終用戶。數據庫系統數據庫管理系統（DatabaseManagementSystem，簡稱DBMS）是提供建立、管理、維護和控制數據庫功能的一組計算機軟件。數據庫管理系統的目標是使用戶能夠科學地組織和存儲數據，能夠從數據庫中高效地獲得需要的數據，方便地處理數據。如：Oracle、MSSQL、DB2等...數據庫管理系統數據庫系統應用程序數據庫管理系統數據庫Oracle，DB2等數據1970年E.F.Codd在美國計算機學會會刊上發表了題為“ARelationalModelofDataforSharedDataBanks”的論文，開創了數據庫系統的新紀元。以后，他連續發表了多篇論文，奠定了關系數據庫的理論基礎。關系模型由關系數據結構、關系操作和關系完整性約束三部分組成。關系型數據庫概念：為了解決大規模數據集合多重數據種類帶來的挑戰，尤其是大數據應用難題。優勢：數據庫高并發讀寫對海量數據的高效存儲和訪問高可擴展性和高可用性的需求與關系型數據庫不同之處：數據庫事務一致性需求數據庫的寫、讀實時性需求對復雜的SQL查詢，多關聯表查詢非關系型數據庫NoSQL數據庫：Hbase、MongoDB…NoSQL存儲:鍵值存儲：Redis面向文檔的數據庫：MongoDB、CouchDB面向列的數據庫：Hbase、

Cassandra非關系型數據庫概念：restful、postman、json案例：驗證插入的數據是否正確步驟：插入數據：POSThttp://ip/record/tablename{"records":[{"test":"abc","id":"10001"}]}Hbase測試舉例獲取數據：GET

http://ip/record/tablename?key=10001&column=testHbase測試舉例數據模型實際上是現實世界到機器世界的一個中間層次。數據模型用于信息世界的建模，是現實世界到信息世界的第一層抽象，是數據庫設計人員進行數據庫設計的有力工具，也是數據庫設計人員和用戶之間進行交流的語言。在數據庫的發展史上，主要有三種模型：層次模型網狀模型關系模型關系模型是三種模型中最重要的一種數據模型，關系數據庫系統采用關系模型作為數據的組織方式，現在流行的數據庫管理系統大都是基于關系模型的。二、數據模型層次模型是一棵倒立的樹。在數據庫中，滿足以下條件的數據模型稱之為層次模型：有且僅有一個結點無父結點，這個結點稱之為根結點；其他結點有且僅有一個父結點。層次模型在現實世界中，事物之間的聯系更多的是非層次關系的，用層次模型表示非樹型結構是很不直接的，所以網狀模型則可以克服這一問題。在數據庫中，滿足以下兩個條件的數據模型稱為網狀模型。允許一個以上的結點無父結點；一個結點可以有多于一個的父結點。從以上定義看出，網狀模型構成了比層次結構復雜的網狀結構。網狀模型關系數據庫采用關系模型作為數據的組織方式。關系數據庫因其嚴格的數學理論、使用簡單靈活、數據獨立性強等特點，而被公認為最有前途的一種數據庫管理系統。它的發展十分迅速，目前已成為占據主導地位的數據庫管理系統。自20世紀80年代以來，作為商品推出的數據庫管理系統幾乎都是關系型的，例如，Oracle，Sybase，Informix，VisualFoxPro，mysql，sqlserver等。關系模型概念：符合某一種級別的關系模式的集合。1、第一范式（1NF）：每一個列（屬性）只有一個，沒有重復。

2、第二范式（2NF）：滿足第一范式，屬性完全依賴于主關鍵字。舉例：未到第一范式：create學生(姓名,性別年齡)----(因為性別年齡列包括了兩個屬性)未到第二范式：create學生(姓名,性別,年齡,語文老師名)

--(因為老師列不是完全函數依賴于主鍵碼)關系模式結構化查詢語言(StructuredQueryLanguage)簡稱SQL，結構化查詢語言是一種數據庫查詢和程序設計語言，用于存取數據以及查詢、更新和管理關系數據庫系統；同時也是數據庫腳本文件的擴展名。結構化查詢語言是高級的非過程化編程語言，允許用戶在高層數據結構上工作。它不要求用戶指定對數據的存放方法，也不需要用戶了解具體的數據存放方式，所以具有完全不同底層結構的不同數據庫系統,可以使用相同的結構化查詢語言作為數據輸入與管理的接口。結構化查詢語言語句可以嵌套，這使它具有極大的靈活性和強大的功能。三、Sql語言1．概念模式（又稱邏輯模式）2.外模式（也稱子模式或用戶模式）3.內模式（也稱存儲模式）SQL三級模式SQL是1986年10月由美國國家標準局（ANSI）通過的數據庫語言美國標準，接著，國際標準化組織（ISO）頒布了SQL正式國際標準。1989年4月，ISO提出了具有完整性特征的SQL89標準，1992年11月又公布了SQL92標準，在此標準中，把數據庫分為三個級別：基本集、標準集和完全集。SQL語言標準結構化查詢語言包含6個部分：數據定義語言（DDL：DataDefinitionLanguage）數據操作語言（DML：DataManipulationLanguage）數據查詢語言（DQL：DataQueryLanguage）事務處理語言（TPL：TransactionProcessingLanguage）數據控制語言（DCL：Data

Control

Language）指針控制語言（CCL：CursorControl

Language）SQL語句結構數據定義語言包括動詞CREATE和DROP。在數據庫中創建新表或刪除表（CREATTABLE或DROPTABLE）；為表加入索引等。DDL包括許多與人數據庫目錄中獲得數據有關的保留字。它也是動作查詢的一部分。數據定義語言CREATETABLEtable_name(column_name1type,column_name2type,column_name3type,...)CREATETABLE語句創建一個員工表（emp），表結構如下：練習員工編號員工姓名所屬部門崗位名稱入職日期月薪工資月度獎金備注EIDNameDeptJobHireDateSalaryCommRemarkNumber(4)Varchar2(10)Varchar2(10)Varchar2(10)DateNumber(11，2)Number(11，2)Varchar2(50)Createtableemp(EIDNumber(4),NameVarchar2(10),DeptVarchar2(10),JobVarchar2(10),HireDateDate,SalaryNumber(11,2),CommNumber(11,2),RemarkVarchar2(10))數據操作語言包括動詞INSERT，UPDATE和DELETE。它們分別用于添加，修改和刪除表中的行。也稱為動作查詢語言。數據操作語言INSERT

[INTO]table_name[colunm_list]VALUES(data_values)示例：Insertinto數據表名（字段1，字段2…）values（值1，值2…）Insertinto數據表名select*from源數據表（把源數據表的記錄添加到目標數據表）INSERT語句在員工表（emp）中插入記錄，表結構如下：員工編號員工姓名所屬部門崗位名稱入職日期月薪工資月度獎金EIDNameDeptJobHireDateSalaryCommNumber(4)Varchar2(10)Varchar2(10)Varchar2(10)DateNumber(11，2)Number(11，2)0101總裁辦CEO2015-01-01Null50,000,000.001234孔明總裁辦秘書長2015-04-011,000,000.00020關羽技術部CTO2015-01-01500,000.00030張飛技術部項目經理2015-01-01500,000.000練習Insertintoempvalues(01,’01’,’總裁辦’，’CEO’,to_date(‘2015-01-01’,’yyyy-mm-dd’),null,50,000,000.00,null)UPDATEtable_nameSETcolumn_name1=expression,[column_name2=expression,[...]][WHEREsearch_conditions]示例：Update數據表set字段名=字段值where條件表達式Update數據表set字段1=值1，字段2=值2…字段n=值nwhere條件表達式UPDATE語句在員工表（emp）中修改孔明的獎金為100.00，如下：員工編號員工姓名所屬部門崗位名稱入職日期月薪工資月度獎金EIDNameDeptJobHireDateSalaryCommNumber(4)Varchar2(10)Varchar2(10)Varchar2(10)DateNumber(11，2)Number(11，2)0101總裁辦CEO2015-01-01Null50,000,000.001234孔明總裁辦秘書長2015-04-011,000,000.00020關羽技術部CTO2015-01-01500,000.00030張飛技術部項目經理2015-01-01500,000.000練習UpdateempsetComm=100.00whereName=‘孔明’DELETE[FROM]table_name[WHEREsearch_conditions]示例：Deletefrom數據表where條件表達式Deletefrom數據表（刪除數據表所有記錄）DELETE語句在員工表（emp）中刪除張飛，如下：員工編號員工姓名所屬部門崗位名稱入職日期月薪工資月度獎金EIDNameDeptJobHireDateSalaryCommNumber(4)Varchar2(10)Varchar2(10)Varchar2(10)DateNumber(11，2)Number(11，2)0101總裁辦CEO2015-01-01Null50,000,000.001234孔明總裁辦秘書長2015-04-011,000,000.00020關羽技術部CTO2015-01-01500,000.00030張飛技術部項目經理2015-01-01500,000.000練習DeletefromempwhereName=‘張飛’數據查詢語言也稱為“數據檢索語句”，用以從表中獲得數據，確定數據怎樣在應用程序給出。保留字SELECT是DQL（也是所有SQL）用得最多的動詞，其他DQL常用的保留字有WHERE，ORDERBY，GROUPBY和HAVING。這些DQL保留字常與其他類型的SQL語句一起使用。數據查詢語言SELECT[DISTINCT]colunm_list[INTOnew_table]FROMtable_name[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]示例：Select

*from數據表where字段名=字段值orderby字段名descSelect

*from數據表where字段名like’%字段值%’orderby字段名ascSelect

top10*from數據表where字段名=字段值orderby字段名ascSelect

top10*from數據表orderby字段名descSelect

*from數據表where字段名in(‘值1’，‘值2’，‘值3’)Select

*from數據表where字段名between值1and值2SELECT語句Distinct這個關鍵字用來過濾重復記錄，只保留一條。事務處理語言能確保被DML語句影響的表的所有行及時得以更新。TPL語句包括：BEGINTRANSACTION

開始事務COMMIT

TRANSACTION

提交事務ROLLBACK

TRANSACTION

想取消，回滾事務事務處理語言數據控制語言通過GRANT或REVOKE獲得許可，確定單個用戶和用戶組對數據庫對象的訪問。某些RDBMS可用GRANT或REVOKE控制對表單個列的訪問。數據控制語言指針控制語言，如DECLARECURSOR，FETCHINTO和UPDATEWHERECURRENT用于對一個或多個表單獨行的操作。**Cursor（游標）是一個記錄表示，用于一行一行迭代的訪問查詢返回的結果示例：DECLAREcursor_nameCURSORFORselect_statement該語句聲明一個游標。可以在子程序中定義多個游標，但是一個塊中的每一個游標必須有唯一的名字。聲明游標后也是單條操作，但不能用select語句，不能有INTO子句。FETCHcursor_nameINTOvar_name[,var_name]…這個語句用指定的打開游標讀取下一行，并前進游標指針。指針控制語言CREATEVIEWview_nameASSELECTcolumn_name(s)FROMtable_nameWHEREconditionCREATEVIEW語句AVGCOUNTMAXMINSUMMIDLEN..SQL函數實例1：在“orders”表中，查找訂單總額少于2000的客戶SelectCustomer，sum（OrderPrice）FromordersGROUPBYCustomerHAVINGSUM(OrderPrice)<2000實例2：在“orders”表中，查找客戶“Bush”或“Adams”超過1500的訂單總金額SelectCustomer，SUM（OrderPrice）FromordersWhereCustomer=‘Bush’ORCustomer=‘Adams’GroupbyCustomerHAVINGSUM(OrderPrice)>1500練習JOIN：用于多表間關聯字段的數據查詢。舉例：表TV_SERIES（T）SQL高級TVnameNameTheBigBangTheoryJohnTheBigBangTheoryMaryTheITCrowdJohnSherlockJimNameDistrictJohnPudongJimHuangpu表District（D）列出看美劇的同學名字，來自于哪個地區？SELECTT.Tvname,T.Name,D.DistrictFROMTINNERJOINDONT.Name=D.NameSQL高級TVnameNameDistrictTheBigBangTheoryJohnPudongTheITCrowdJohnPudongSherlockJimHuangpuCARD聽課證:CNO課程號，SNO學號，NAME姓名，CLASS班級;INSERTINTOCARD(CNO,NAME,CLASS)VALUES('C0001',‘S1’，'Mary','B')，('C0001',‘S2’，'John','B')，('C0003',‘S3’，'Jim','A')COURSE課程:CNO課程號，CNAME課程名,TEACHER上課老師，DATE上課日期INSERTINTOCOURSE(CNO,CNAME,TEACHER,DATE)VALUES(‘C0001’，'MATHS','HUANG','20150701')，(‘C0002’，'ENG','LI','20150601')，(‘C0003’，'CHINA','ZHOU','20150801')練習題目：1.將Mary的CLASS改為‘C’2.查詢John聽課情況，輸出課程名，上課老師以及上課日期3.統計MATHS課程學生人數，輸出學生數量STUDENT_NUM練習答案：1.將Mary的CLASS改為‘C’UPDATECARDSETCLASS='C'WHEREName='Mary'2.查詢John聽課情況，輸出課程名，上課老師以及上課日期SELECTCNAME，TEACHER，DATEFROMCARD,COURSEWHERECARD.CNO=COURSE.CNO練習答案：3.統計MATHS課程學生人數，輸出學生數量STUDENT_NUMSELECTCOUNT(SNO)ASSTUDENT_NUMFROMCARDINNERJOINCOURSEONCARD.CNO=COURSE.CNOANDCNAME='MATHS‘結果顯示：練習STUDENT_NUM2char(n)n=[1to2000]字節；Varchar2(n)n=[1to4000]字節；Number（m,n）m=[1to38],n=[-84to127]如：number（5,2）,輸入123.128，則這個字段的最大值為99999，超出則以四舍五入方式截斷，得：123.13；Date缺省格式為DD-MON-YY,如：11-07-15表示2015年7月11日；Blob：三種大型對象（LOB）,保存較大圖形文件或帶格式的文本文件，如：Word，視頻等非文本文件Oracle基本數據類型概念：與oracle交互的客戶端工具，運行sql*plus命令和sql語句。作用：數據庫維護，啟動（startup）、關閉(shutdown);執行sql語句；執行sql腳本,如：SQL>startfile_name

SQL>@file_name用戶管理和權限維護Sqlplus基礎Sqlplus運行案例建表插入數據顯示表結構查詢數據修改字段值刪除行刪除表Sqlplus練習函數，在某變化過程中有兩個變量x，y，如果對于x在某一范圍內的每一個確定的值，y都有唯一確定的值與它對應，那么就稱y是x的函數，x叫自變量。函數根據傳遞給它的參數進行運算，并返回運算結果。函數一般形式：函數名（參數1，參數2，…）參數是傳遞給函數的數據。如：Power（3，2）是函數，用于計算3的2次方，3和2都是參數。Oracle基本函數操作對象：單行函數多行函數操作的數據類型（返回結果的類型）字符函數數字函數日期函數轉換函數通用函數Oracle函數類型SINGLEROWMULTIROWFUNCTION字符函數數字函數日期函數轉換函數單行函數LOWER 轉換為小寫形式UPPER 轉換為大寫形式INITCAP 轉換為首字母大寫形式CONCAT 連接函數SUBSTR 返回子串LENGTH 返回字符長度NVL 轉換一個空值字符函數轉換字符串的大小寫LOWER('SQLCourse') sqlcourseUPPER('SQLCourse') SQLCOURSEINITCAP('sql

course') SqlCourse轉換大小寫函數操縱字符串CONCAT(‘Good’,‘String’) GoodString字符連接函數，等同于||SUBSTR(‘String’,1,3) Str求母串中的某個子串LENGTH(‘String’) 6求字符串長度（字節數）字符操作函數ROUND 按照指定的精度進行舍入TRUNC 按照指定的精度進行截斷MOD 返回余數數字函數ROUND(45.923,3) 45.923四舍五入ROUND(45.923,2) 45.92ROUND(45.923,0) 46ROUND(45.923,-1) 50ROUND(45.923,-2) 0TRUNC(45.923,4) 45.923截斷TRUNC(45.923,3) 45.923TRUNC(45.923,2) 45.92TRUNC(45.923) 45TRUNC(45.923,-1) 40TRUNC(45.923,-2) 0ROUND和TRUNC函數使用MOD函數可以得出一個數除以另一個數所得的余數.MOD(1600,300) 100

1600/3=5余100MOD函數MONTHS_BETWEEN 兩個日期間的月ADD_MONTHS 加月份到一個日期上NEXT_DAY 指定日期的第二天LAST_DAY 某月的最后一天的日期ROUND 日期舍入到半夜TRUNC 從日期中去除部分時間日期函數MONTHS_BETWEEN(‘11-SEP-95','11-JAN-95') 8ADD_MONTHS('11-JAN-94',6) '11-JUL-94'NEXT_DAY(‘01-SEP-95’,‘FRIDAY’) '08-SEP-95'LAST_DAY(‘01-SEP-95’) '30-SEP-95'ROUND('25-MAY-95','MONTH') 01-JUN-95ROUND('25-MAY-95','YEAR') 01-JAN-95TRUNC('25-MAY-95','MONTH') 01-MAY-95TRUNC('25-MAY-95','YEAR') 01-JAN-95日期函數TO_CHAR轉換一個數字或日期串到一個字符串TO_NUMBER轉換一個字符串到一個數字TO_DATE轉換一個字符串到一個日期轉換函數語法格式一：TO_CHAR(date,'format')將日期date按指定格式轉換為VATCHAR2類型字符串。必須放置在單引號中且是區分大小寫的；可以包含任意可用的有效的日期格式元素；可用一個參數刪除數字前的空格或零值；日期之間用逗號隔開。TO_CHAR函數語法格式二：TO_CHAR(number,'format')將數字按指定格式轉換為VATCHAR2類型字符串。TO_CHAR函數YYYY 表示完整的年份的數字表示。YEAR 表示年份的英文拼寫。MM 表示兩位表示的月份。MONTH 表示月的全名。DY 表示星期的三個字母的縮寫。DAY 表示日期的全拼。日期格式元素時間元素格式化日期中的時間部分HH24:MI:SSAM 15:45:32PM通過使用雙引號添加字符串DD"of"MONTH 12ofOCTOBER日期格式元素把全為數字的字符串轉換為數字格式，使用TO_NUMBER函數；語法格式：TO_NUMBER(char)把日期格式的字符串轉換為日期格式，使用TO_DATE函數；語法格式：TO_DATE(char[,'fmt'])TO_NUMBER和TO_DATE函數nvl(exp1,exp2):當exp1為空時，返回exp2

nvl2(exp1,exp2,exp3):當exp1為空時，返回exp3；否則返回exp2NULLIF

(expr1,

expr2),如果expr1=expr2,返回null;否則,返回expr1

示例：NVL(1+1,2+2)2非null不替換NVL(NULL,2+2)4Null則替換

Nvl函數selectlower(‘HelloWorld’)fromdual;selectinitcap('helloworld')fromdual;selectconcat('Hello','World')fromdual;selectsubstr('HelloWorld',3)fromdual;selectsubstr('HelloWorld',3,4)fromdual;selectlength('China'),lengthb('China')

fromdual;selectinstr('HelloWorld',‘el')fromdual;selectlpad('abcd',10,'*'),rpad('abcd',10,'*')

fromdual;單行函數總結selecttrim('H'from'HelloWorldH')fromdual;fromdual;selectreplace('HelloWordl','l','*')fromdual;selectround(45.926,2),trunc(45.926,2)

,mod(1600,300)

fromdual;select(-1)sysdate(+1)fromdual;selectlast_day(sysdate)fromdual;selectnext_day(sysdate,'星期五')fromdual;selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss"今天是"day')fromdual;單行函數總結和單行函數相比，Oracle提供了豐富的基于組的，多行的函數。這些函數能在Select或Select的Having子句中使用，當用于Select子串時常常都和GROUPBY一起使用。多行函數的特征是接收多個輸入，返回一個輸出。多行函數AVG([DISTINCT|ALL]n)COUNT({*|[DISTINCT|ALL]expr})MAX([DISTINCT|ALL]expr)MIN([DISTINCT|ALL]expr)SUM([DISTINCT|ALL]n)聚合函數概念：將一個數據集合劃分成若干個“小區域”，然后針對若干個“小區域”進行數據處理。Groupbyselect類別,sum(數量)as數量之和fromAgroupby類別Groupby和聚合函數求各組平均值select類別,avg(數量)AS平均值fromAgroupby類別求各組記錄數目select類別,count(*)AS記錄數fromAgroupby類別where子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數據，where條件中不能包含聚組函數，使用where條件過濾出特定的行。having子句的作用是篩選滿足條件的組，即在分組之后過濾數據，條件中經常包含聚組函數，使用having條件過濾出特定的組，也可以使用多個分組標準進行分組。Having與WhereHaving求總數大于18的類別

select類別,sum(數量)as數量之和fromAgroupby類別havingsum(數量)>18三種備份方法：1.導入（IMP),導出（EXP）（邏輯）2.熱備份（物理）3.冷備份（物理）Oracle庫備份1.簡單導出備份：(1)、表方式(T方式)，將指定表的數據導出。(2)、用戶方式(U方式)，將指定用戶的所有對象及數據導出。(3)、全庫方式(Full方式)，將數據庫中的所有對象導出。