数据库基础及测试应用

第四章数据库基础及测试应用支文瑜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方式)，将数据库中的所有对象导出。