用友SQL培训资料U8U9文档格式.docx

1、第三节 连接（Join）语句 16第四节 数据操作语句 20第五节 Select子查询语句 21第三章 SQL高级应用 24第一节 创建、删除数据库和表 24第二节 创建视图和索引 25第三节 创建和执行存储过程、触发器和游标 27第四节 系统存储过程、系统表 30第四章 事件探查器 33第一节 创建跟踪 33第二节 事件探查器的精确跟踪 37第三节 实际工作中事件探查器的应用 38附录： 40第一章 概述 SQL（Structured Query Language，结构化查询语言）是一种现今流行的数据库语言，目前主流的数据库产品都支持这种语言，如常见的Microsoft Access、Mic

2、rosoft SQLServer、Oracle、Sybase、MySQL等，并且这些数据库产品都不同程度上对标准的SQL进行了扩展，以使SQL应用更为高效。 用友产品以Microsoft Access（如8.12A版本）或MicrosoftSQLServer（如U8以上版本）作为后台数据库，目前（8.x、U8）不支持其他的数据库产品。第一节 U8数据库简述1.1 U8数据库 用友U8系列产品的后台数据库是采用Microsoft SQLServer。 U8系列产品的数据库按功能主要可分为三类：A.系统数据库UFSystem；B.账套数据库UFData_xxx_xxxx（如UFData_999_2

3、003，其中999表示账套号，2003表示会计年度）；C.模板数据库UFModel。 其中UFSystem库用于存储一些公共系统信息，如操作员信息、账套系统信息等，UFData_xxx_xxxx库用于存储账套数据，UFModel库在新建账套时作为模板数据库使用。 每个SQLServer数据库是由两类文件组成：一类是数据文件，包括主数据文件（扩展名为mdf）和次要数据文件（扩展名为ndf，该类文件不是必须的，U8数据库通常没有该文件）；一类是日志文件（扩展名为ldf）。1.2 8.X数据库 用友早期产品以Access为后台数据库，这些产品（如8.12A）目前仍有很多用户在使用。 大部分8.X产品

4、的系统库和账套数据库都是Access数据库类型。 每个Access数据库只对应一个文件，以mdb为扩展名。 以Access为后台数据库的产品，用Access软件可以直接打开对应的数据库文件，该数据库文件可以作为参考的数据字典使用（点击每个表的设计模式，内有详尽的字段含义解释）。另外，以Access为后台数据库的产品安装盘中有压缩的演示账套数据库文件，直接用Winzip解压缩后，手工将该文件扩展名更改为以.mdb为结尾，就可以作为参照的数据字典使用。1.3 SQLServer和MSDE的关系 MSDE是SQLServer的一个简版，该版本是免费使用的，功能和SQLServer基本相同，只是在多用

5、户条件下运行效率低于SQLServer产品，而且，MSDE本身没有一个可视化的操作界面，只是在后台运行，不过，SQLServer等数据库软件仍可以在远程访问控制该数据库。 虽然用友支持SQLServer的产品也支持MSDE，但用友产品需要的某些数据库高级功能则要求用户必须使用SQLServer作为后台数据库（如数据库的数据抽取功能）。而且在实际的维护实践中，也会发现，MSDE出现异常的情况远多于SQLServer。所以，在维护中如果遇到以MSDE作为后台数据库的情况应多加注意此方面的问题。1.4 Microsoft SQLServer、MSDE与Access数据库的比较 Microsoft S

6、QLServer和MSDE支持的功能远多于Access，Access通常不支持SQL的高级功能，如：存储过程、触发器等。 Microsoft SQLServer配有强大的辅助工具，特别是事件探查器工具，而Access产品没有实现此类功能的工具。另外，Access的产品的安全性也远低于SQLServer产品。 第二节 常用的SQL工具简介 SQL本身是一种语言，需要在特定的环境中运行。U8产品的Microsoft SQLServer、MSDE以及8.12A产品的Access都为SQL提供了运行环境。 为了使8.X、U8系列产品正常运行，必须要先安装好对应的后台数据库产品（关于Microsoft

7、Access和Microsoft SQLServer如何安装，请参阅安装盘帮助文件）。2.1 Microsoft SQLServerSQLServer中常用的工具包括：企业管理器、查询分析器、事件探查器等。如图1所示：图1企业管理器：可进行各种可视化的操作，功能强大，但由于其运行时消耗资源较大，所以当计算机硬件条件不好或者SQLServer负载太大时，会有操作反映迟钝等问题。查询分析器：是交互式图形工具，它使数据库管理员或开发人员能够编写查询、，同时执行多个查询、，查看结果、，分析查询计划和获得提高查询性能的帮助。事件探查器：是图形工具，使系统管理员得 以监视 Microsoft SQL Se

8、rver 实例中的事件。可以捕获有关每个事件的数据并将其保存到文件或 SQL Server 表中供以后分析。2.2 Microsoft Access 快捷方式如图1所示。第三节 数据库中的常用术语和概念 本节主要介绍一些常用的SQLServer术语和概念，更多术语请参考联机帮助中的“词汇表”内容，查询位置如图2所示。图2数据库 （database） ：为特定目的（如搜索、排序和重新组织数据）而组织和提供的信息、表和其它对象的集合。数据库存储在文件中。如：UFData_999_2003就是一个数据库。表 （table） ：一种二维数据对象，由行和列组成，用于存储关系数据库中的数据。每个表存储有关

9、由数据库建模的一类对象的信息。例如，某个教育数据库将具有一个教师表、一个学生表和一个课程表。字段 （field） ：存储单个数据值的窗口或记录中的某个区域。某些数据库（如 Microsoft Access）将字段用作列的同义词。如Accinformation表中的cSysID字段。记录 （record） ：视作一个单元信息的一组相关字段（列）。在 SQL 数据库中记录更经常被称作行。第四节 SQL语法格式说明 在SQLServer联机帮助中，可看到如下语句语法：SELECT ALL | DISTINCT TOP n PERCENT WITH TIES 1002.2.2 Order By子句Or

10、der By子句是用来指定在 SELECT 语句返回的列中所使用的排序次序。根据某一列排序（如OrderID列），SQL语句可写为：SELECT * FROM Order Details ORDER BY OrderID2.2.3 Group By子句Group By子句主要和函数连用（如sum函数），按指定列汇总。返回Northwind数据库中Order Details表中，productID字段相同的数量之和，SQL语句可写为：SELECT ProductID, sum（quantity） FROM Northwind.Order Details Group By ProductID2.2

11、.4 Having子句Having子句一般要和GROUP BY 子句连用。上句中要再加一个条件，数量之和大于1000的返回记录，SQL语句可写为：SELECT ProductID, sum（quantity） FROM Northwind.Order Details Group By ProductID Having sum（quantity）10002.2.5 Where子句和Having子句的区别Where子句和Having子句的主要区别：和Group By连用时，Where后面的条件是先排除再进行Group By汇总；而Having是在Group By汇总后，再用后面的条件进行筛选。另外

12、Having语句后的条件可以是聚合函数（如sum函数、Max函数），而where后不能接聚合函数。如下面的语句：如果用Where语句后加相同筛选条件则无法实现目标功能。第三节 连接（Join）语句Join子句的主要作用，就是将两个表横向连接起来。Join分为内连接和外连接。内连接为Inner Join（默认形式，Inner可以省略）；外连接分为三个Left Outer Join、Right Outer Join、Full Outer Join（其中的Outer可以省略）。 下面举一个例子来说明Join子句的用法。有两个表father和son结构如下（其中father表的Fid字段和Son表的F

13、id字段是对应的）：father表FidName1A2B3CSon表SidAson1Ason2Ason34Cson15Cson26Dson13.1 使用内连接语句Inner Join连接Inner Join返回两个表中匹配的行。两个表中不匹配的行不在结果中显示。如果未指定联接类型，则该连接形式为默认形式。在SQLServer查询分析器中执行如下语句：Select * From father Join son On father.fid = son.fid结果如下：3.2 使用左连接语句Left Outer Join连接Left Outer Join指定在结果集中除包含左右表中匹配的数据外，还返

14、回左表中所有不满足联接条件的行，且右表中相应字段值为 NULL。其中Outer可以省略，但Left和Join关键字不可省略。Select * From father Left Join son On father.fid = son.fid结果如下（注意下表中的NULL）：NULLSelect * from father left join son on father.fid = son.fid where son.name is null 实现的功能为：找出在father表中有，而在son表中没有对应的记录。3.3 使用右连接语句Right Outer Join连接 Right Outer

15、Join语句和Left Outer Join 语句的用法原理相同。Right Outer Join语句是在结果集中除包含左右表中匹配的数据外，还返回右表中所有不满足联接条件的行，且左表中相应字段值为 NULL。其中Outer可以省略，但Right和Join关键字不可省略。Select * From father Right Join son On father.fid = son.fidSelect * from father Right join son on father.fid = son.fid where father.name is null找出在Son表中有，而在father表中

16、没有对应的记录。3.4 使用全连接语句Full Outer Join连接Full Outer Join是指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。其中Outer可以省略，但Full和Join关键字不可省略。Select * From father Full Join son On father.fid = son.fid7Select * from father Full join son on father.fid = son.fid where father.name is null

17、Or son.name is null实现的功能为：找出在Son表中有，而在father表中没有对应的记录以及在Father表中有，而在son表中没有对应的记录。3.5 Join子句小结 在U8产品中，大量使用了Inner Join、Left Join、Right Join和Full Join子句。 Join语句以其灵活的应用方式，可以方便的实现各种的复杂查询功能。有些简单的功能也能用另外的一个子句：IN子句可以实现，但IN子句实现的功能要比Join语句简单很多。因此一定要重点学习Join语句的各种灵活使用的方法。 In子句会在Select语句子查询语句中讲解。3.6 Union子句的用法Jo

18、in子句是用来将两个表横向连接起来组成一个新表，而Union子句是将两个表纵向连接起来组成一个新表。当然，Union纵向连接两个表是有限制条件的：两个表的列数相等，且对应列的类型兼容（可以简单的理解为类型相同）。具体的Union语法请查询联机帮助。第四节 数据操作语句数据操作语句包括INSERT、 UPDATE 和DELETE 等4.1 插入Insert Into子句使用方法可以使用 INSERT INTO 在当前表中创建新行。INSERT INTO pubs.titles （title_id, title, type, pub_id, price）VALUES （BU9876, Creati

19、ng Web Pagesbusiness1389, 29.99）Pubs.titles是插入操作的目标数据表，title_id、 title、 type、 pub_id、 price是目标数据表的列名字，Values关键字后面是要插入对应列的具体数据。4.2 DELETE删除子句使用方法DELETE从表中删除行。为了尽可能的减少误操作最好在执行delete语句之前，先执行对应的select语句，以确定Where后面条件的准确性。在SQLServer查询分析器中执行如下语句（删除刚才插入的记录）：select * from pubs.titles where title_id =delete from pubs.titles where title_id = 4.3 UpDate子句使用方法UPDATE子句是用于更改表中的现有数据。为了尽可能的减少误操作最好在执行UPDATE语句之前，先执行对应的select语句，以确定Where后面条件的准确性。在SQLServer查询分析器中执行如下语句（更改刚才插入的记录，如果已经删除，请重新用Insert Into插入语句插入相应的数据）：sel