数据库应用系统开发

数据库应用系统开发了解数据库管理技术的发展、数据库系统组成、数据库系统结构、数据模型等，初步认识数据库及其应用系统50年代中期以前存储数据、组织方式由程序员自己决定、设计——数据与程序不具有独立性主要用于科学计算数据不保存应用程序管理数据数据不共享数据不具独立性人工手工管理50年代末至60年代中期，操作系统已有专门的管理数据软件——程序与数据之间由软件提供存取方法但冗余大，程序与数据存储的独立性不够，修改结构意味着得更改程序。数据以文件方式存放，按表单形式表示。数据可以长期保存数据可以共享但共享性差、冗余度高数据独立性差文件之间数据孤立安全性控制不灵活例：书籍（编号、标题、出版社编码、书籍类型、单价）、出版社（编码、出版社名称）问题：某标题的书籍是哪个出版社出版的？某个出版社出版了什么书籍？面向文件系统的管理从60年代后期开始。要完成的任务是：数据合并、数据共享、数据保护。数据库与文件系统的根本区别在于数据的结构化，由单个数据表组成的数据库文件是平面文件数据库，平面文件系统是数据库技术发展的开端。数据库系统数据冗余小，易扩充。具有较高的数据和程序独立性。统一的数据控制功能。数据库管理系统相关术语和基本概念1．数据（data）数据指描述事物的符号记录。文字、图形、图像、声音、学生的档案记录、货物的运输情况等都是数据。2．数据库（database，简称DB）数据库指长期存储在计算机内有组织的、可共享的数据集合。数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度，较高的数据独立性和易扩展性，并可为各种用户共享。3．数据库管理系统（databasemanagementsystem，简称DBMS）数据库管理系统指位于用户与操作系统之间的一层数据管理软件。数据库在建立、运用和维护时由数据库管理系统统一管理、统一控制。数据库管理系统使用户能方便地定义数据和操纵数据，并能够保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。4．数据库系统（databasesystem，简称DBS）数据库系统指在计算机系统中引入数据库后构成的系统，一般由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员和用户构成。DBA(DataBaseAdministrator)协调用户和开发人员的工作参与数据库设计创建用户和授权保证数据库的正常运行维护工作一个能够让用户定义、创建和维护数据库以及控制对数据库访问的软件系统。数据库管理系统是位于用户和操作系统之间的一层数据管理软件数据库管理系统（DBMS)DBMS功能DBMS提供统一的数据定义、插入、删除、检索及更新操作。功能：数据的安全性保护数据的完整性检查并发控制数据库恢复数据库是系统中各用户的共享资源（并发）数据的安全控制（允许谁操作，允许什么操作等）数据的完整性控制（实体完整性、参照完整性、属性完整性）并发控制（防止丢失更新等）统一的数据控制功能数据的存储结构与逻辑结构之间的映象或转换功能，当数据的存储结构（或物理结构）改变时，数据的逻辑结构可以不变——数据与程序的物理独立性，简称数据的物理独立性。数据的总体逻辑结构与某类应用所涉及的局部逻辑结构之间的映象或转换功能，当总体逻辑改变时，通过对映象的相应改变而保持局部逻辑结构不变。程序员是根据局部逻辑结构编写应用程序的，因而应用程序也就不必更改——数据和程序的逻辑独立性，简称数据的逻辑独立性。数据和程序独立性

DBMS环境有五个主要组成部分：硬件、软件、数据、工程和人硬件：DBMS和应用程序运行的计算机系统软件：DBMS软件和应用程序以及操作系统数据：运行数据和元数据过程：控制数据库设计和使用的指令和规则人：包括数据库设计者、数据库管理员、应用程序员和最终用户DBMS环境的组成一个通过向DBMS发出合适的请求（一般是一个SQL语句）与数据库交互的计算机应用程序数据库应用程序从相同数量的数据中可获得更多的信息数据共享平衡需求冲突控制或消除冗余一致性完整性安全性高效性数据无关性数据库处理的优点

规模较大相对成本较大复杂度较大故障的影响较大恢复较困难数据库处理的缺点

面向用户或应用程序员的用户级对应外模式（用户模式）面向建立和维护数据库人员的概念级对应模式（逻辑模式、概念模式）面向系统程员的物理级内模式（存储模式）数据库系统的三级模式结构二级映像为提高数据独立性，DBMS在三级模式之间提供两级映像外模式/模式间的映像：当数据逻辑结构变更时，通过修改和调整外模式/模式间的映像，使得用户对应的外模式不变，从而依据外模式编写的应用程序可以不变，保持数据的逻辑独立性。模式/内模式间的映像：当数据的存储结构发生改变时，通过修改和调整模式/内模式间的映像，是数据库的全局逻辑结构保持不变，保持数据的物理独立性数据模型由于计算机只能存储和处理数据而不能直接存储和处理现实世界中的客观事物，也就无法直接用计算机来管理一个企业的设备、产品和职员等，因此，我们必须先把企业的设备、产品和职员等客观事物的某些特征抽象成计算机能够存储和处理的数据，才能用计算机对其进行管理。那么，用什么数据格式来抽象表示客观事物的数据特征呢？那就是数据模型数据模型的构成数据模型的三要素数据结构

——数据的表示方法（数据的组织形式），是一种静态特征的描述数据操作

——数据操作是指对数据库的查询、修改、删除和插入等操作，是一种动态特征的描述完整性约束

——数据及数据间联系应具有的制约和依赖规则数据模型的分类概念数据模型（概念模型）按用户的观点对数据进行建模，强调语义表达功能独立于计算机系统和DBMS主要用于数据库的概念设计逻辑模型按计算机系统的观点对数据建模，主要用于DBMS的实现与具体计算机系统相关物理模型用于描述数据在计算机内部的表示与存储方式与具体计算机系统相关应用程序体系结构当前典型：客户-服务器（Client/Server,CS)浏览器-服务器（Browser/Server,BS)SQLServer2008版2008年上市，2010年R2版SQLServer2000是Microsoft公司于2000年12月推出的关系型数据库管理系统。SQLServerSQLServer2000企业管理器组实例数据库数据库对象数据库表记录查询分析器和Ｔ－ＳＱＬ语法检查执行选择数据库数据库对象T-SQL执行结果在线帮助系统按目录结构和主题进行文档查阅按词进行全文档搜索按关键词条进行文档查阅定义书签，定位常用帮助页面SQLServer2008企业管理器企业级的信息系统无一例外地是数据库应用系统从早些时候的C/S系统中变成当前的混合型系统今日的商业系统需要支持多种多样的客户端在这样的环境中，传统的“快速开发”应用通常变得不可能UML提供了语义定义来实现这些应用PowerDesigner提供了开发工具来实现这些应用现代信息系统的发展体系有职工、仓库、部门、客户、商品等基本信息，其中：部门、客户、商品要求多级定义职工与部门为m:1，仓库与部门为m:1，部门与客户为1:m有基本的物流功能，即进货、出货（批发、零售），要求：要同时改变与客户的资金流（应收应付）要记录所有的原始数据（包括业务双方、商品、商量、价格、时间、业务员、操作员等）有基本资金流动能，即支付、收款，要求：内部的资金周转与往来客户的资金流动要记录所有的原始数据（包括业务双方、金额、时间、业务员、操作员等）有基本的查询接口，基本信息查询、库存查询、与客户的应收应付查询、根据先进先出核算方法所得的利润查询等。可以为不同的操作员设置不同的权限课程示例——进销存SQL语言熟悉SQL语言SQL（结构化查询语言）是一个国际公认的定义和管理关系数据库的编程语言最初的开发是由IBM公司于70年代后期进行的SQL-86：1986年10月ANSI(AmericanNationalStandardInstitute,美国国家标准协会)发布ANSI文件X.135-1986《数据库语言SQL》，1987年国际标准化组织采用了该文件成为国际标准SQL-89：1989年4月ANSI发布X3.168-1989《数据库语言——嵌入式SQL》，1989年10月ANSI发布X3.135-1989《数据库语言——完整和增强的SQL》SQL-92（SQL-2）：1992年8月ISO和ANSI共同发布SQL-99(SQL-3)：1999年修订本，但已不再是强制标准。2003年再次修订SQLSQL语言采用集合操作方式，它对数据的处理是成组进行的，而不是一条一条处理的。当执行SQL语句时，每次只能发送并处理一条语句。对用户而言，当执行SQL语句时，只需要知道其逻辑含义，不需要关心SQL语句的具体执行步骤。使用SQL语句时，既可以采用交互方式执行，也可将SQL语句嵌入到高级语言中执行。SQL语言特点使用TransactSQL(T-SQL)可以在SQLServer2000提供的关系型数据库中实现编程功能T-SQL提供了几种运算符和条件表达式T-SQLTransact-SQL语句的种类数据定义语言（DDL）语句创建、更改或删除数据库对象数据控制语言（DCL）语句授权数据操作语言（DML）语句数据增、删、改、查SQL关键词不区分大小写对象名和列名不区分大小写字符值和日期值区分大小写可以分行SQL语句编写规则显示常量

Select'Thisisatest!'Select'hello!‘计算

select1+2+3 select‘aaa’+‘123’查看全局变量Select@@languageSelect@@version获得函数值SelectGetdate()SelectYear(Getdate())SELECT语句的基本格式SQL语句（SELECT）SELECT语句是SQL中最常用的语句，用于检索已经存储在数据库中数据。基本语法：SELECT

...

--<查询内容>FROM

...

--<表名>[WHERE

...]

--<条件>[GROUPBY

...]

--<分组内容>[HAVING

...]

--<组内条件>[ORDERBY

...]

--<排序内容>其中：From:用于指定被查询的表、视图或快照Where条件:用于限定处理的行数，选择满足条件的行Groupby:分组Having:分组后过滤数据，要求的列名是在聚合函数中如avg,或是在groupby子句中Orderby:行信息显示时的先后顺序SQL查询简单查询语句USENorthWindSELECT*FROMemployees查询全部的行和列SELECTfirstname,lastname,birthdateFROMEmployees查询全部行和特定列SELECT*FROMemployeesWHEREcity='LONDON'查询限定范围的行使用别名列别名 SELECTtitle_idas

'编号',priceas

'原价',price*0.7as

现价 FROMtitles表别名

SELECTtitle_id,price,price*0.7 FROMtitlest消除取值重复的行/限制返回行数消除取值重复的行——distinct限制返回的行数——topn排序Orderby功能：对查询结果进行排序例SELECT*FROMtitles ORDERBYtitle,pricedescWhere条件数字比较运算符(=、>、<、>=、<=、<>)字符串比较符(%、_、like)逻辑运算符(AND、OR、NOT)检索一定范围内的值(betweenand)使用值列表(In)检索未知值(Null)表达式是符号和运算符的组合，SQLServer可以对它进行运算以得到一个单值表达式可以包含下列一个或多个部分：常数列{一元运算符}{二元运算符}条件表达式查询语句中可以有函数、表达式查询列中的函数与表达式

selectproName,price*Numamount From…条件中的函数与表达式

select… From… Wheretdate>getdate()-7排序中的函数与表达式

select… From Orderbydatepart(hour,tDate)运算符

含义= 等于> 大于< 小于>= 大于等于<= 小于等于<> 不等于! 非比较运算符模糊查询SELECTcompanyname

FROMcustomersWHEREcompanynameLIKE'%Restaurant%‘companynameGROSELLA-RestauranteLonesomePineRestaurantTortugaRestaurante通配符'_'一个字符SelectBranch_CodeFromBranchWhereBranch_CodeLike'L__'%任意长度的字符串SelectRoute_CodeFromFlightScheduleWhereRoute_CodeLike'AMS-%'[]括号中所指定范围内的一个字符SelectAirbusnoFromFlightScheduleWhereAirbusnoLike'AB0[1-5]'[^]不在括号中所指定范围内的任意一个字符SelectAirbusnoFromFlightScheduleWhereAirbusnoLike'AB0[^1-5]'通配符解释示例T-SQL支持的逻辑运算符如下：逻辑运算符ANDORNOTAND和OR运算符用于在WHERE子句中连接搜索条件。NOT否定搜索条件。AND连接两个条件且仅当两个条件都为真时返回TRUE。OR也连接两个条件，但只要其中任一个为真就返回TRUE。子查询返回第一步USEnorthwind

SELECTorderid,customeridFROMordersASor1WHERE20<(SELECTquantity

FROM[orderdetails]ASodWHEREor1.orderid=od.orderidANDductid=23)GO父查询传送列值给子查询子查询获取父查询传送的列值子查询返回查询值给父查询父查询传送下一行的列值给子查询(重复1-3步)实例聚合函数AVG()——计算表达式中平均值COUNT()——表达式中值的数目MAX()——表达式中的最大值MIN()——表达式中最小值SUM()——计算表达式中所有值的和数据分组GROUPBY联合使用聚合函数和GROUPBY子句，能够把表中的记录分组，并对组中数据进行汇总使用GROUPBY子句时，应注意对于指定的一组，SQLServer只生成一条记录，不返回详细信息如果对可包含空值的字段使用GROUPBY子句，空值也将被当作一组在分组的同时，可用Having对字段或表达式指定搜索条件只在使用GROUPBY子句的同时，使用HAVING子句来限制分组可以引用任何出现在选择列表中的字段作为条件时聚合函数只能出现在Having子句中多表联接从多个表中选择指定的字段关键字JOIN

指定要联接的表，以及这些表联接的方式关键字

ON

指定联接条件查询两个或多个表并生成单个结果集所有联接的表必须共同拥有某些字段，这些字段必须有相同的或兼容的数据类型如果联接的表有相同字段，则引用这些字段时必须指定表名多表联接使用表的别名组合多个表中的数据合并多个结果集集合查询UnionInterSectexcept当服务器接收到一个SQL查询语句后，将按照如下过程进行处理分析过程：首先把这个批处理作为一个整体进行分析，在分析时，服务器检查命令动词和关键词的语法，验证表和字段的名字和合法性再优化过程：在优化阶段，服务器会确定完成一个查询的最有效的方法编译过程：在编译阶段，将生成这个批处理的可执行代码执行过程：最后，这个批处理中的语句将在运行阶段一句一句地执行

服务器处理SQL语句的过程创建、修改基表Createtable<tblName>( <col1><datatype>[列级完整性约束条件] [,<col2><datatype>[列级完整性约束条件]]… [,<coln><datatype>[列级完整性约束条件])Altertable…典型数据库表的设计基本范式表数据统计表分类表数据处理型表数据描述型表基本范式表员工表Employee (id,ename,deptid,

…)部门表Dept (dno,dname,…)特点： 基本满足３NF数据统计表每月利润统计monthProfits (year,month,subid,

profits,…)特点： 用于各类统计报表 统计表数据本可以从明细中获取，当数据量较大时通过将数据统计后保存可以有效地提高运行效率．分类表商品customer (cust_id,cust_name,parentid)特点 基于一个已经定义好的范围和背景。在内容管理过程中，可采用结构分类的标准术语来统一分类及标记信息资

来源于菜单式的数据结构数据处理型表商品whproduct (wh_id,pro_id,pos1,pos2,num1,money1,…,numn,moneyn,…)特点 直接将计算数据存放在表中数据描述型表工资Salary (Emp_id,Field1,Field2,…,Fieldn,…)商品SalaryField (FieldName,DisplayName,Flag,…)特点 类似于数据字典特殊字段设计（Ⅰ）主键使用标识字段通过存储过程产生排序设计Orderno字段父子关系增加字段parid自数目字段Sonsum,冗余设计,可以方便查询其他如可以在字段对应表中加一个字段SignFlag。其中应发工资项SignFlag为1，扣发工资项SignFlag为–1。而应发工资、实发工资为计算项让SignFlag为0。可以在字段对应表中加上每一字段在屏幕上的显示位置(PosX,PosY)，用一个界面设置工资字段。特殊字段设计（Ⅱ）使用位运算1，2，4，8，…特殊字段取值一般无法直接设计出完全符合用户要求的数据库通过单独考虑某个方面，这些单独的需求称为用户视图视图是一个命名的Select语句，将动态地产生一个结果集视图是一个虚拟表视图完成数据库到程序的字段名称的映射使复杂查询语句的使用简单化能适当提高程序的执行效率视图设计视图提供了数据独立性因为每个用户都有他自己的视图，相同的数据可以被不同的用户从不同的角度定义不同的视图一个视图只需包括用户所需求的那些列，这样既可简化用户对数据库的理解，又提供了对安全性的检测视图的优点只能在当前数据库中创建视图视图名称应与表名近似视图名称应遵循标识符的命名规则可以基于其他视图建立视图不能将默认值、规则和触发器与视图相关联不能为视图建立索引创建视图时不能使用临时表即使表被删除，视图定义仍将保留定义视图的查询不能包含ORDERBY、INTO创建视图的指导原则一个互动交流平台,要求:求助者可以发贴寻求帮助求助者所发之贴须管理员审核后方可公示普通网友可跟贴管理人员一旦回帖,该回帖在所有回帖的最上方视图例(需求)视图例(表设计)下面的视图可以显示主帖Id,管理人员回帖数及最近回帖ID SELECTgID,COUNT(*)ASreCount,max(id)asrID FROMdbo.reGBook whereisUsed=1andrtype<>0 GROUPBYgID视图例(视图设计Ⅰ)下面的视图可以显示所有主帖的相关信息,管理人员回帖数、点击数，最后是哪个管理员回帖等 SELECTg.id,g.topicastitle,g.content,g.ipAdd,ISNULL(g.pubtime,GETDATE())ASpubtime, ISNULL(g.userName,g.id)ASAuthor, isNull(rc.reCount,0)asreCount,chits,isNull(g.uId,0)asuId, r.pubtimeasrTime,r.UserNameasrAuthor,g.isdisplay FROMdbo.gbookgleftjoinregbookcountrc onrc.gid=g.id leftjoinregbookronrc.rId=r.ID WHERE(g.isused=1)视图例(视图设计Ⅱ)视图 SELECT*, CASEcTypeid WHEN0THEN'default'WHEN1THEN'red'ELSE'default'ENDASdispcolor FROMdbo.content视图例—不同主题以不同颜色呈现存储过程是是存储在服务器上执行指定操作的子程序，是有SQL语句和控制流语句构成的集合可以给存储过程传递数值，存储过程一可以返回数值存储过程存储过程与原始SQL语句的最大区别在于它是预编译的。当第一次运行一个过程时，数据库服务器的查询处理器对它进行分析并准备好一个执行计划并存储在系统表中，以后过程的执行都依据这个存储的计划如果是在客户/服务器环境下，存储过程经过编译和优化后存储在服务器端，在网络上传输的只是一个远程调用和最终的执行结果，从而可以减少网络流量存储过程简化了安全机制，它可以简化对某些操作的授权。例如，假设一个用户不希望别人在他的表上使用SELECT语句，他可以写一个仅查询这个表某些行或列的存储过程，然后把调用该存储过程的权限授权给特定的用户存储过程把用户和应用程序与数据源、网络和存取路径细节隔离开，使得非专业人员对数据库服务器上的数据访问变得更方便存储过程的优点游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制游标提供了一种对从表中检索出的数据进行操作的灵活手段使用游标，可以实现以下目标：允许定位到结果集中的特定行从结果集的当前位置检索一行或多行数据支持对结果集中当前位置的行进行修改对于其他用户对结果集包含的数据库数据所做的修改，支持不同的可见性级别使用游标把游标与Transact-SQL语句的结果集相关联，并且定义游标的特征，如是否能够更新游标中的行执行Transact-SQL语句以填充游标检索想要查看的游标中的行根据需要，对游标中当前位置的行执行修改操作（更新或删除）关闭游标游标工作进程DECLARE语句用于创建游标。它包含SELECT语句，用来引入表中的记录语法：

DECLARE<Cursor_Name>CURSOR

FOR<SelectStatements>

在T-SQL中创建游标打开游标 OPEN<Cursor_name>检索记录 FETCH<Cursor_name>关闭游标 CLOSE<Cursor_name>删除游标引用 DEALLOCATE<Cursor_name>在T-SQL中使用游标@@FETCH_STATUS：为执行的上一个游标FETCH语句返回一个整数值@@CURSOR_ROWS：返回当前打开的游标中符合条件的行的数目。FETCH语句全局变量使用@符号作为第一个字符来指定参数名称可以使用参数向存储过程中输入值，或从存储过程中输出值参数默认为Input输入参数可以使用默认值 @iValint=0输出参数需Output存储过程的参数存储过程的返回包括输出参数，可以返回数据（整型值或字符值等)返回代码，始终是整型值。SELECT语句的结果集，这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内。存储过程返回一个结果集合存储过程例(Ⅰ)declare @path varchar(300)execap_GetLoc20,'--',@pathoutputprint@path存储过程例(Ⅱ)可以有返回值在存储过程内部执行SQL语句使用

exec／execute项目需求：商品出库时需要一个出库价格，以计算商品成本与利润。有三种计算出库价格的方法：先进先出价—商品先入库的先出库，出库价为入库是的入库价。加权平均价—商品入库时将库存商品的价格与入库商品的价格进行加权平均，下次出库时按该加权平均价作为出库价格。先进后出价—商品后入库的先出库，出库价为入库是的入库价。成本核算—栈、队列的使用(Ⅰ)常规设计先进先出—队列；先进后出—栈实际程序设计时可以考虑将队列、栈合二为一，加权平均作为一个特例处理，即始终在栈顶处理。一个可能的处理方案将商品表结构设计为：

Id、Cname、……、Pos1、Pos2、Num1、Price1、……、Numn、Pricen通过Pos1与Pos2具体处理下一个入库存放在哪一个字段、下一个出库从哪一个字段处取得数据。成本核算—栈、队列的使用(Ⅱ)先进先出

读取Pos1、Pos2

入库出库

Num1Num1Price1Price1

……NumiPos2

NumiPriceiPricei

……Pos1

NumjNumjPricejPricej

……NumnNumnPricenPricen每一次入库，Pos1加1，每一次出库从Pos2开始加。成本核算—栈、队列的使用(Ⅲ)加权平均

读取Pos1、Pos2

入库出库

Num1Num1Price1Price1

……Pos1

NumiPos1

NumiPriceiPricei……NumnNumnPricenPricenPos1与Pos2相等，入库时先取得库存数量与价格，与入库数量与价格加权平均后存回。出库时直接取库存数量与价格，减出库数量后存回。成本核算—栈、队列的使用(Ⅳ)先进后出

读取Pos1、Pos2

入库出库

Num1Num1Price1Price1

……Pos2

NumiPos2

NumiPriceiPricei……NumnNumnPricenPricen入库时Pos2加1，出库是从Pos2开始减.成本核算—栈、队列的使用(Ⅴ)存储过程例(Ⅲ)动态Execute语句sp_executesql比EXECUTE更通用输出参数使用Output,如：

set@sqlstr=N'select@orgnum='+@numFldO +‘fromwhproduct

wherewh_id=‘ +

convert(nvarchar(6),@whid) +‘andpro_id=’+

convert(nvarchar(10),@proid)execsp_executesql@sqlstr, N'@Orgnumnumeric(9,2)output',@Orgnumoutput

函数是由一个或多个Transact-SQL语句组成的子程序，可用于封装代码以便重新使用标量函数：标量函数返回在RETURNS子句中定义的类型的单个数据值内嵌表值函数：内嵌表值函数没有函数主体，表是单个SELECT语句的结果集多语句表值函数：多语句表值函数在BEGIN...END块中定义的函数主体包含TRANSACT-SQL语句，这些语句可生成行并将行插入将返回的表中用户定义函数函数定义 CREATEFUNCTIONfnDocCount(@itemNoint) RETURNSint AS BEGIN return(selectcount(*)fromcontentwhereitemid=@itemNo) END函数使用

selectdbo.fnDocCount(2