《用友SQL培训资料(U8、U9).docx》由会员分享，可在线阅读，更多相关《用友SQL培训资料(U8、U9).docx（52页珍藏版）》请在三一办公上搜索。
1、SQLServer专刊引 言本专刊主要针对于刚刚加入用友维护队伍、希望快速提高个人面向U8软件维护工作的SQLServer应用能力的人员而编写，特别适用于SQLServer初学者。注意：本专刊是为SQLServer快速入门而编写，内容简单概括，读者要使自己的SQLServer水平有质的提高，在实际工作中要充分利用SQLServer联机帮助，它是最具权威的参考资料。本专刊主要内容：第一章概述U8数据库特点、常用的SQL工具、语法格式，以及SQLServer数据库中的一些常用的术语。第二章和第三章主要介绍SQL的基础、高级应用。第四章简要介绍事件探查器的使用。事件探查器是U8维护工作中最为常用的分
2、析工具。附录 内容包括组成数据库对象的概念、SQLSERVER的系统组成以及Transact_SQL小手册，可以作为查询SQLServer信息的快速参考。信息反馈：由于时间和水平有限，不当之处和错误在所难免，如果在阅读中发现本专刊有错误之处，恳请读者批评指正！ 如果读者对本刊的编制或SQLServer学习方面有好的意见或建议，敬请email至如下地址：陈 媛cheny陈湘林gccxl马大刚mdg致谢：本期SQLServer专刊是在两期新员工、部分分公司维护人员进行的SQLServer培训的基础上整理而成。参与培训人员对本专刊的编制提供了很多有益的意见和建议，在这里对他们积极支持和配合(特别是新
3、员工李丹，对最后定稿的排版、勘误做了大量工作)致以衷心的感谢！目 录第一章 概述7第一节 U8数据库简述7第二节 常用的SQL工具简介9第三节 数据库中的常用术语和概念10第四节 SQL语法格式说明11第二章 SQL基础应用13第一节 简单的SELECT查询14第二节 Select语句中的常用子句及函数14第三节 连接（Join）语句16第四节 数据操作语句20第五节 Select子查询语句21第三章 SQL高级应用24第一节 创建、删除数据库和表24第二节 创建视图和索引25第三节 创建和执行存储过程、触发器和游标27第四节 系统存储过程、系统表30第四章 事件探查器33第一节 创建跟踪33
4、第二节 事件探查器的精确跟踪37第三节 实际工作中事件探查器的应用38附录：40第一章 概述SQL（Structured Query Language，结构化查询语言）是一种现今流行的数据库语言，目前主流的数据库产品都支持这种语言，如常见的Microsoft Access、Microsoft SQLServer、Oracle、Sybase、MySQL等，并且这些数据库产品都不同程度上对标准的SQL进行了扩展，以使SQL应用更为高效。用友产品以Microsoft Access（如8.12A版本）或MicrosoftSQLServer（如U8以上版本）作为后台数据库，目前（8.x、U8）不支持其他
5、的数据库产品。第一节 U8数据库简述1.1U8数据库用友U8系列产品的后台数据库是采用Microsoft SQLServer。U8系列产品的数据库按功能主要可分为三类：A.系统数据库UFSystem；B.账套数据库UFData_xxx_xxxx（如UFData_999_2003，其中999表示账套号，2003表示会计年度）；C.模板数据库UFModel。其中UFSystem库用于存储一些公共系统信息，如操作员信息、账套系统信息等，UFData_xxx_xxxx库用于存储账套数据，UFModel库在新建账套时作为模板数据库使用。每个SQLServer数据库是由两类文件组成：一类是数据文件，包括主
6、数据文件（扩展名为mdf）和次要数据文件（扩展名为ndf，该类文件不是必须的，U8数据库通常没有该文件）；一类是日志文件（扩展名为ldf）。1.28.X数据库用友早期产品以Access为后台数据库，这些产品（如8.12A）目前仍有很多用户在使用。大部分8.X产品的系统库和账套数据库都是Access数据库类型。每个Access数据库只对应一个文件，以mdb为扩展名。以Access为后台数据库的产品，用Access软件可以直接打开对应的数据库文件，该数据库文件可以作为参考的数据字典使用（点击每个表的设计模式，内有详尽的字段含义解释）。另外，以Access为后台数据库的产品安装盘中有压缩的演示账套数
7、据库文件，直接用Winzip解压缩后，手工将该文件扩展名更改为以.mdb为结尾，就可以作为参照的数据字典使用。1.3SQLServer和MSDE的关系MSDE是SQLServer的一个简版，该版本是免费使用的，功能和SQLServer基本相同，只是在多用户条件下运行效率低于SQLServer产品，而且，MSDE本身没有一个可视化的操作界面，只是在后台运行，不过，SQLServer等数据库软件仍可以在远程访问控制该数据库。虽然用友支持SQLServer的产品也支持MSDE，但用友产品需要的某些数据库高级功能则要求用户必须使用SQLServer作为后台数据库（如数据库的数据抽取功能）。而且在实际的
8、维护实践中，也会发现，MSDE出现异常的情况远多于SQLServer。所以，在维护中如果遇到以MSDE作为后台数据库的情况应多加注意此方面的问题。1.4Microsoft SQLServer、MSDE与Access数据库的比较Microsoft SQLServer和MSDE支持的功能远多于Access，Access通常不支持SQL的高级功能，如：存储过程、触发器等。Microsoft SQLServer配有强大的辅助工具，特别是事件探查器工具，而Access产品没有实现此类功能的工具。另外，Access的产品的安全性也远低于SQLServer产品。 第二节 常用的SQL工具简介SQL本身是一种
9、语言，需要在特定的环境中运行。U8产品的Microsoft SQLServer、MSDE以及8.12A产品的Access都为SQL提供了运行环境。为了使8.X、U8系列产品正常运行，必须要先安装好对应的后台数据库产品（关于Microsoft Access和Microsoft SQLServer如何安装，请参阅安装盘帮助文件）。2.1Microsoft SQLServerSQLServer中常用的工具包括：企业管理器、查询分析器、事件探查器等。如图1所示：图1企业管理器：可进行各种可视化的操作，功能强大，但由于其运行时消耗资源较大，所以当计算机硬件条件不好或者SQLServer负载太大时，会有操
10、作反映迟钝等问题。查询分析器：是交互式图形工具，它使数据库管理员或开发人员能够编写查询、，同时执行多个查询、，查看结果、，分析查询计划和获得提高查询性能的帮助。事件探查器：是图形工具，使系统管理员得 以监视 Microsoft SQL Server 实例中的事件。可以捕获有关每个事件的数据并将其保存到文件或 SQL Server 表中供以后分析。2.2Microsoft Access快捷方式如图1所示。第三节 数据库中的常用术语和概念本节主要介绍一些常用的SQLServer术语和概念，更多术语请参考联机帮助中的“词汇表”内容，查询位置如图2所示。图2数据库 (database) ：为特定目的（
11、如搜索、排序和重新组织数据）而组织和提供的信息、表和其它对象的集合。数据库存储在文件中。如：UFData_999_2003就是一个数据库。表 (table) ：一种二维数据对象，由行和列组成，用于存储关系数据库中的数据。每个表存储有关由数据库建模的一类对象的信息。例如，某个教育数据库将具有一个教师表、一个学生表和一个课程表。字段 (field) ：存储单个数据值的窗口或记录中的某个区域。某些数据库（如 Microsoft Access）将字段用作列的同义词。如Accinformation表中的cSysID字段。记录 (record) ：视作一个单元信息的一组相关字段（列）。在 SQL 数据库中
12、记录更经常被称作行。第四节 SQL语法格式说明在SQLServer联机帮助中，可看到如下语句语法：SELECT ALL | DISTINCT TOP n PERCENT WITH TIES := * | table_name | view_name | table_alias .* | column_name | expression | IDENTITYCOL | ROWGUIDCOL AS column_alias | column_alias = expression ,.n 如何理解上句中的各种符号的涵义呢？在联机帮助中搜索“Transact-SQL 语法规则”，会找到如图3所示内容，
13、其中包含了详细的SQL语法符号的具体涵义。图3本章小结：本章只是简要介绍了一些比较基础的术语和概念，目的是使读者对SQLServer有个总体的印象，以便能有效的进行后续的学习。更多的内容还请参阅联机帮助。第二章 SQL基础应用让我们从Select语句开始对SQL语法的学习。本专刊中所使用的SQL语句均在SQLServer查询分析器中执行。对SQL语言的学习，关键是要边学习边实践，读者可以在SQLServer查询分析器中实际练习本专刊提供的语句。Select语句的功能为从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列，该语句包含众多的子句，功能强大，使用灵活。下面先简要说明在SQLS
14、erver查询分析器中执行SQL语句的步骤：第一步：运行查询分析器，如图3所示：图3第二步：执行选中待操作的数据库语句。如图3所示：选中use ufdata_887_2004后，点击绿色箭头（或直接按F5）执行，该语句中，use为连接目标数据库的SQL关键字，ufdata_887_2004是要进行相应操作的目标数据库。第三步：执行相应的SQL语句（如图3中的select * from accinformation语句）。第一节 简单的SELECT查询在SQLServer的查询分析器中执行如下两句SQL语句：USE NorthwindSELECT * FROM Order Details执行该语
15、句返回Order Details表中包含所有列的记录。这两句SQL语句也可以合并为一条语句：SELECT * FROM Northwind.Order DetailsNorthwind.Order Details表示 Order Details 表是Northwind数据库中的表，使用这种形式，就不用再提前连接数据库了（即不用使用use语句来连接数据库）。注意：由于上面语句中表名Order Details两个单词之间存在空格，所以使用中括号，如果表名中不存在空格，如表名为Orderdetails，则中括号可以省略。第二节 Select语句中的常用子句及函数2.1SQL中函数的使用在SQL 中的
16、函数可以执行一些储如对某一列进行汇总或将一个字符串中的字符转换为大写的操作。比较常用的函数有聚合函数、日期与时间函数、数学函数、转换函数和字符函数等，具体内容参见附录。在这里以一个最常用的聚合函数为例。sum()就如同它的本意一样，返回某一列的所有数值的和。可以到查询分析器中执行如下语句（求数量的总和）：SELECT sum(quantity) FROM Northwind.Order Details注意：此处必须指定要合计的具体列而不能使用*，并且该列必须是数值型。2.2Select语句的常用子句Select中的常用子句包括：Where，Order By，Group by，Having，Jo
17、in等。Join语句会在第三节重点讲解。本节主要讲解前四个子句的用法。2.2.1 Where子句Where子句的作用是指定查询返回行的条件。如：返回Northwind数据库中Order Details表中的数量（quantity）字段值大于100的记录，SQL语句可写为：SELECT * FROM Northwind.Order Details Where quantity1002.2.2 Order By子句Order By子句是用来指定在 SELECT 语句返回的列中所使用的排序次序。如：根据某一列排序（如OrderID列），SQL语句可写为：USE NorthwindSELECT * F
18、ROM 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.4 Having子句Having子句一般要和GROUP BY 子句连用。如：上句中要再加一个条件，数量之和大于1000的返回记录，SQL语句可写为
19、：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汇总后，再用后面的条件进行筛选。另外Having语句后的条件可以是聚合函数（如sum函数、Max函数），而where后不能接聚合函数。如下面的语句：SELECT ProductID
20、, sum(quantity) FROM Northwind.Order Details Group By ProductID Having sum(quantity)1000如果用Where语句后加相同筛选条件则无法实现目标功能。第三节 连接（Join）语句Join子句的主要作用，就是将两个表横向连接起来。Join分为内连接和外连接。内连接为Inner Join（默认形式，Inner可以省略）；外连接分为三个Left Outer Join、Right Outer Join、Full Outer Join（其中的Outer可以省略）。下面举一个例子来说明Join子句的用法。有两个表father
21、和son结构如下（其中father表的Fid字段和Son表的Fid字段是对应的）：father表FidName1A2B3CSon表SidNameFid1Ason112Ason213Ason314Cson135Cson236Dson143.1 使用内连接语句Inner Join连接Inner Join返回两个表中匹配的行。两个表中不匹配的行不在结果中显示。如果未指定联接类型，则该连接形式为默认形式。在SQLServer查询分析器中执行如下语句：Select * From father Join son On father.fid = son.fid结果如下：FidNameSidNameFid11
22、A1Ason1121A2Ason2131A3Ason3143C4Cson1353C5Cson233.2 使用左连接语句Left Outer Join连接Left Outer Join指定在结果集中除包含左右表中匹配的数据外，还返回左表中所有不满足联接条件的行，且右表中相应字段值为 NULL。其中Outer可以省略，但Left和Join关键字不可省略。在SQLServer查询分析器中执行如下语句：Select * From father Left Join son On father.fid = son.fid结果如下（注意下表中的NULL）：FidNameSidNameFid11A1Ason1
23、121A2Ason2131A3Ason3142BNULLNULLNULL53C4Cson1363C5Cson23在SQLServer查询分析器中执行如下语句：Select * from father left join son on father.fid = son.fid where son.name is null实现的功能为：找出在father表中有，而在son表中没有对应的记录。3.3 使用右连接语句Right Outer Join连接Right Outer Join语句和Left Outer Join 语句的用法原理相同。Right Outer Join语句是在结果集中除包含左右表中
24、匹配的数据外，还返回右表中所有不满足联接条件的行，且左表中相应字段值为 NULL。其中Outer可以省略，但Right和Join关键字不可省略。在SQLServer查询分析器中执行如下语句：Select * From father Right Join son On father.fid = son.fid结果如下（注意下表中的NULL）：FidNameSidNameFid11A1Ason1121A2Ason2131A3Ason3143C4Cson1353C5Cson236NULLNULL6Dson14在SQLServer查询分析器中执行如下语句：Select * from father Ri
25、ght join son on father.fid = son.fid where father.name is null实现的功能为：找出在Son表中有，而在father表中没有对应的记录。3.4 使用全连接语句Full Outer Join连接Full Outer Join是指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。其中Outer可以省略，但Full和Join关键字不可省略。在SQLServer查询分析器中执行如下语句：Select * From father Full Join s
26、on On father.fid = son.fid结果如下（注意下表中的NULL）：FidNameSidNameFid11A1Ason1121A2Ason2131A3Ason3143C4Cson1353C5Cson236NULLNULL6Dson1472BNULLNULLNULL在SQLServer查询分析器中执行如下语句：Select * from father Full join son on father.fid = son.fid where father.name is null Or son.name is null实现的功能为：找出在Son表中有，而在father表中没有对应的
27、记录以及在Father表中有，而在son表中没有对应的记录。3.5 Join子句小结在U8产品中，大量使用了Inner Join、Left Join、Right Join和Full Join子句。Join语句以其灵活的应用方式，可以方便的实现各种的复杂查询功能。有些简单的功能也能用另外的一个子句：IN子句可以实现，但IN子句实现的功能要比Join语句简单很多。因此一定要重点学习Join语句的各种灵活使用的方法。In子句会在Select语句子查询语句中讲解。3.6 Union子句的用法Join子句是用来将两个表横向连接起来组成一个新表，而Union子句是将两个表纵向连接起来组成一个新表。当然，U
28、nion纵向连接两个表是有限制条件的：两个表的列数相等，且对应列的类型兼容（可以简单的理解为类型相同）。具体的Union语法请查询联机帮助。第四节 数据操作语句数据操作语句包括INSERT、 UPDATE 和DELETE 等4.1 插入Insert Into子句使用方法可以使用 INSERT INTO 在当前表中创建新行。在SQLServer查询分析器中执行如下语句：INSERT INTO pubs.titles (title_id, title, type, pub_id, price)VALUES (BU9876, Creating Web Pages, business, 1389, 2
29、9.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 =BU9876delete from pubs.titles where
30、 title_id = BU98764.3 UpDate子句使用方法UPDATE子句是用于更改表中的现有数据。为了尽可能的减少误操作最好在执行UPDATE语句之前，先执行对应的select语句，以确定Where后面条件的准确性。在SQLServer查询分析器中执行如下语句（更改刚才插入的记录，如果已经删除，请重新用Insert Into插入语句插入相应的数据）：select * from pubs.titles where title_id =BU9876UpDate pubs.titles set price = 200 where title_id =BU98764.4 Select In
31、to子句使用方法SELECT INTO new_table From子句创建新表并将查询的结果行插入新表中。在SQLServer查询分析器中执行如下语句：Select * Into pubs.new_table1From pubs.titles用语句Select * from pubs.new_table1可以查询出刚才新生成的表及其中刚刚插入的数据。注意：Select Into子句并不能将原来表的一些关系复制到新表中（如外键等），它只能插入相应的数据。所以要想既复制表关系又复制其中的数据，请使用SQLServer企业管理器中提供的导表功能。第五节 Select子查询语句5.1 子查询使用方法
32、子查询是一个 SELECT 查询，任何允许使用表达式的地方都可以使用子查询。在SQLServer查询分析器中执行如下语句（一个子查询用作 SELECT 语句中名为 MaxUnitPrice 的列表达式）：SELECT Ord.OrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM Northwind.dbo.Order Details AS OrdDet WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPriceFROM Northwind.dbo.Orders AS Ord其中的As关键
33、字用于指定别名。5.2 In子句的使用方法IN子句是确定给定的值是否与子查询或列表中的值相匹配。下面的示例选择名称和州的列表，列表中列出所有居住在加利福尼亚、印地安纳或马里兰州的作者:SELECT au_lname, stateFROM pubs.authorsWHERE state IN (CA, IN, MD)请参看“3.2 左连接语句Left Outer Join”的例子（找出在father表中有，而在son表中没有对应的记录）： Select * from father left join son on father.fid = son.fid where son.name is nu
34、ll对等的In子句是（注意这个语句并没有将两个表横向连接起来，同时也用了子查询语句）：Select * from father where father.fid Not In (Select fid from son)本章小结：本章主要讲解了SQL语句的常用语法。Select语句及其子句灵活的语法是学习的难点，在U8维护工作中， Join子句是最为常用的语法，必须重点学习。本专刊附录部分，详尽讲解了本章所涉及SQL语句一些较为高级灵活的用法，另外学习这些语法时，一定要结合SQLServer的联机帮助。第三章SQL高级应用有了SQL基础，本章开始讲解一些SQL的高级应用。包括如何创建数据库、表、
35、索引，视图、存储过程、触发器、游标的简单应用等，同时本章也讲解一些能有效提高维护效率的SQL知识：系统存储过程、系统表的应用。第一节 创建、删除数据库和表1.1创建、删除数据库的SQL语法创建数据库的语法：create database 数据库名在查询分析器中写：Create Database testData，选中该语句，按F5执行，创建了一个名字为testData的数据库。删除数据库的语法：drop database 数据库名在查询分析器中写：Drop Database testData，选中该语句，按F5执行，删除名字为testData的数据库。1.2创建、修改、删除数据库中表的SQL语
36、法创建表的语法：create table 表名 ( 列名1 数据类型(长度) not NULL NULL, 列名2 数据类型(长度) not NULL NULL, . )在查询分析器中写：use testDataCreate table testTable1(id int,name varchar(20)选中上面的SQL语句，按F5执行，创建一个名字为testTable1的表。修改表的关键字是：Alter table（其后可能跟随ALTER COLUMN、ADD、DROP COLUMN等），具体语法可到SQLServer联机帮助中查询。删除表的语法：Drop table 表名（可以一次性删除多
37、个表,表名和表名之间用逗号隔开）。在查询分析器中写：Drop table testTable1，选中该语句，按F5执行，删除名字为testTable1的表。第二节 创建视图和索引2.1创建视图CREATE VIEW是创建一个虚拟表，该表以另一种方式表示一个或多个表中的数据。CREATE VIEW 必须是查询批处理中的第一条语句。创建视图的简单语法是：CREATE VIEW 视图名As下面的示例为pubs库创建的名字为TestView的视图：use pubsgoCreate View TestViewAsSelect * from authors视图的使用和表基本相同，如：select * fr
38、om pubs.testview2.2删除视图删除视图的语法为：drop View 视图名下面的示例为删除刚刚在pubs库的创建的视图：USE pubsdrop view testview2.3创建索引为给定表或视图创建索引。只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引，无论表中是否有数据。可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。创建索引的简单语法是：create uniqueclustered index 索引名 on 表名(列asc|desc)下面的示例为pubs库的 authors 表的 au_id 列创建索引：USE pubsCREA
39、TE INDEX au_id_ind ON authors (au_id)2.4删除索引删除索引的语法为：drop index 表名.索引名下面的示例为删除刚刚在pubs库的 authors 表的 au_id 列创建的索引：USE pubsdrop index authors.au_id_ind2.5视图和表的区别视图和表的用法基本相同。但他们之间在作用上有很大区别的：视图是用来动态的创建一个表，它占用的数据库空间比较小（基本上就是一些SQL语句），但使用时的执行速度一般比表要慢一些；表的缺点就是比视图占用的数据库空间大，但执行速度比较快。另外视图是以表为基础的。第三节 创建和执行存储过程、触
40、发器和游标3.1创建存储过程创建存储过程的关键字是Create Proc（也可以是create procedure）在SQLServer查询分析器中执行如下语句创建一个名字为testPro的存储过程：use pubsgocreate proc testProcas select * from authors带参数存储过程的创建：use pubsgocreate proc testProcX name varchar(20)as select * from authors where au_lname = name3.2执行存储过程执行无参数存储过程的语句是：exec pubs.testproc
41、执行带参数存储过程的语句是（注意参数的赋值）：exec pubs.testprocx name=white3.3删除存储过程删除无参数和带参数的存储过程的语句分别是：use pubsdrop proc testProcuse pubsdrop proc testprocX3.4创建触发器CREATE TRIGGER是创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft SQL Server 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。在pubs库中的titles表上创建一个名字为mySelect
42、触发器，当对改表做插入和更新操作时，该触发器自动执行，检索出修改后的titles表的记录：use pubsgoCREATE TRIGGER mySelectON titlesFOR INSERT, UPDATE asSelect * from titlesGO可执行如下语句看一下触发器的运行结果：update titles set price = 19 where title_id = bu10323.5删除触发器DROP TRIGGER是从当前数据库中删除一个或多个触发器。 其语法为：DROP TRIGGER 触发器名字 ,.n 删除触发器的语句是：use pubsDROP TRIGGER
43、mySelect3.6游标的使用DECLARE CURSOR定义 Transact-SQL 服务器游标的特性，例如游标的滚动行为和用于生成游标对其进行操作的结果集的查询。创建游标的简单语法是：DECLARE cursor_name CURSOR FOR select_statement 下面是一个具体创建使用游标的语句：-建立（定义）一个游标DECLARE authors_cursor CURSOR FOR SELECT au_lnameFROM authors-（打开游标）OPEN authors_cursor-取游标记录，插入到变量中FETCH NEXT FROM authors_cursor INTO au_lname-浏览游标记录WHILE FETCH_STATUS = 0 BEGIN print au_lname-取下一记录FETCH NEXT FROM authors_cursor INTO au_lnameEND-关闭游
