Delphi 与SQL编程

来源：互联网收集：自由互联发布时间：2021-06-23

Delphi 与SQL编程 SQL语言作为关系数据库管理系统中的一种通用的结构查询语言，已经被众多的数据库管理系统所采用，如Oracle、Sybase、Informix等数据库管理系统，它们都支持SQL 语言。

Delphi 与SQL编程

SQL语言作为关系数据库管理系统中的一种通用的结构查询语言，已经被众多的数据库管理系统所采用，如Oracle、Sybase、Informix等数据库管理系统，它们都支持SQL 语言。Delphi与使用SQL语言的数据库管理系统兼容，在使用Delphi开发数据库应用程序时，我们可以使用SQL语言编程，支持SQL编程是Delphi的一个重要特征，这也是体现Delphi作为一个强大的数据库应用开发工具的一个重要标志。　
17.1 SQL语言简介　
17.1.1 SQL的历史　
在70年代初，E.E.Codd首先提出了关系模型。70年代中期，IBM公司在研制 SYSTEM R关系数据库管理系统中研制了SQL语言，最早的SQL语言(叫SEQUEL2)是在1976 年 11 月的IBM Journal of R&D上公布的。1979年ORACLE公司首先提供商用的SQL，IBM公司在DB2 和SQL/DS数据库系统中也实现了SQL。
1986年10月，美国ANSI采用SQL作为关系数据库管理系统的标准语言(ANSI X3. 135-1986)，后为国际标准化组织(ISO)采纳为国际标准。1989年，美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言，称为ANSI SQL 89，该标准替代ANSI X3.135-1986版本。该标准为下列组织所采纳：
● 国际标准化组织(ISO)，为ISO 9075-1989报告“Database Language SQL With Integrity Enhancement”
● 美国联邦政府，发布在The Federal Information Processing Standard Publication(FIPS PUB)127
目前，所有主要的关系数据库管理系统支持某些形式的SQL语言，大部分数据库打算遵守ANSI SQL89标准。　
17.1.2 SQL的优点
SQL广泛地被采用正说明了它的优点。它使全部用户，包括应用程序员、DBA管理员和终端用户受益非浅。
(1) 非过程化语言
SQL是一个非过程化的语言，因为它一次处理一个记录，对数据提供自动导航。SQL允许用户在高层的数据结构上工作，而不对单个记录进行操作，可操作记录集。所有SQL 语句接受集合作为输入，返回集合作为输出。SQL的集合特性允许一条SQL语句的结果作为另一条SQL语句的输入。
SQL不要求用户指定对数据的存放方法。这种特性使用户更易集中精力于要得到的结果。所有SQL语句使用查询优化器，它是RDBMS的一部分，由它决定对指定数据存取的最快速度的手段。查询优化器知道存在什么索引，哪儿使用合适，而用户从不需要知道表是否有索引，表有什么类型的索引。
(2) 统一的语言
SQL可用于所有用户的DB活动模型，包括系统管理员、数据库管理员、应用程序员、决策支持系统人员及许多其它类型的终端用户。基本的SQL 命令只需很少时间就能学会，最高级的命令在几天内便可掌握。
SQL为许多任务提供了命令，包括：
● 查询数据
● 在表中插入、修改和删除记录
● 建立、修改和删除数据对象
● 控制对数据和数据对象的存取
● 保证数据库一致性和完整性
　
以前的数据库管理系统为上述各类操作提供单独的语言，而SQL 将全部任务统一在一种语言中。
(3) 是所有关系数据库的公共语言
由于所有主要的关系数据库管理系统都支持SQL语言，用户可将使用SQL的技能从一个RDBMS转到另一个。所有用SQL编写的程序都是可以移植的。
　
17.2 TQuery部件在SQL编程中的运用
　
在Delphi中是通过TQuery部件来实现对SQL语言支持的，也就是说用Delphi 开发数据库应用程序时，使用SQL语言操作数据库中的数据的唯一途径是经过TQuery部件。 TQuery部件在Delphi中使用SQL语言编程时占居着绝对重要的地位。在使用Delphi 开发的数据库应用中，可以使用SQL语言访问下列三个方面的数据库：
● Paradox或dBASE数据库中的表
在访问这些桌面数据库系统中的数据时，只能使用ANSI标准的SQL语言中的部分SQL 语句，它们主要包括：Select、Insert、Update和Delete语句；即本地SQL语句。有关详细情况请参见附录“局部SQL语句的使用”。
● 本地InterBase数据库服务器中的数据库
在InterBase数据库中支持的SQL语句，在Delphi中都可以使用。有关InterBase中SQL语句的语法和限制，请参看“InterBase的语言参考”。
● 远程数据库服务器中的数据库
当然这要求在Delphi中必须安装相应的SQL Link。只要是数据库服务器上的DBMS支持的SQL语句，在Delphi中都可以使用。有关语法及限制请参看相关的数据库管理系统的文档。
　
值得一提的是，Delphi还支持异构查询，即可以同时查询多个数据库服务器中相同的或不同类型的数据库表，例如查询的数据可以是来自ORACLE数据库中的表和Sybase数据库中的表或者其它多个数据库中的表。
　
17.2.1 TQuery部件的使用
　
TQuery部件是一个数据集部件，它在Delphi部件选择板上的数据访问页(Data Access)上，它与TTable部件具有很多共同的特性，我们在第十五章“数据访问部件的应用及编程”中较详细地进行了介绍。 TQuery 部件在 SQL 编程中占居了十分重要的地位。它实现了Delphi对SQL语言的支持，在Delphi开发的数据库应用中，SQL语句是通过TQuery部件传递到要访问的数据库系统的数据库引擎中，由数据库引擎具体执行SQL语句，以实现对数据的操作，而不是传递给Delphi中的BDE，由BDE实施具体的SQL动作。
　
我们已经知道了TTable部件在访问数据库时已经具备很强大的功能。TTable部件通过Delphi内置的BDE可以实现对各种数据库系统的访问，然而TQuery部件提供了一些 TTable部件不具备的功能，它们是：
●多表联接查询
●复杂的嵌套查询(Select中包含着Select子查询)
●明确需要按SQL语言进行的操作
　
因为TTabel部件不能使用SQL语言，而在TQuery部件可以使用SQL语言，因而TQuery部件也就具备了强大的关系查询能力。当然这也使数据库应用程序本身变得更复杂了。
在Delphi应用程序中编写和使用的SQL语句有两种：即静态SQL语句、动态SQL 语句。静态SQL语句是在程序设计阶段，将SQL命令文本作为TQuery部件的SQL属性值设置。而动态SQL语句编程是SQL语句中包含一系列的参数，在程序运行过程中各参数值是可变的，即可以动态地给SQL语句中的参数赋值。
静态方式是把SQL命令文作为TQuery部件的SQL属性值进行设置，这样，当执行应用程序时，Delphi便执行TQuery部件SQL属性中设置的SQL命令。如果是SQL中的查询命令，把TQuery部件通过TDataSource部件与数据控制部件相连，查询的结果将会显示在与 TQuery部件相连接的数据浏览部件中。动态SQL语句是指SQL语句中包含一些参数变量，在程序中可以为这些参数赋值，在程序运行过程中，各个参数值是变化的。TQuery部件的SQL 属性中的SQL语句的编写也有两种方法，一种方法是在程序设置阶段便将相应的SQL语句写入到TQuery的SQL属性中，另一种方法是在Delphi开发的应用程序将SQL语句，包含在Pascal代码单元中。
在SQL编程中使用TQuery部件的具体方法步骤如下：
①为TQuery部件设置DatabaseName属性，它可以是用BDE建立的数据库的别名，或桌面数据库系统中的目录名或数据库服务器中的文件名，如果在应用中使用了TDatabase 部件，那么TQuery部件的DatabaseName属性可以是TDatabase部件中定义的一个数据库别名。详细情况请参看“TDatabase部件的使用”；
②为TQuery部件设置SQL属性，TQuery部件的SQL属性值就是应用程序要执行的SQL 命令文本，设置SQL属性有两种方法：
● 在程序设计过程中，我们可以通过对象浏览器(Object Inspector)编辑SQL属性在Object Inspector中选择SQL属性，这样会打开String List Editor窗口，在其中我们便可以编写SQL命令，我还可以打开Visual Query Builder来编写SQL命令 (只有Delphi的客户/服务器版本才具有这一工具)。
● 将SQL命令包含在Pascal代码单元中
在程序运行过程中，首先调用TQuery部件的Close方法关闭当前的TQuery部件，然后调用Clear方法清除SQL属性，并说明新的SQL命令文本，然后调用Add方法，将新的SQL命令文本加入到SQL属性中。
　
③通过调用TQuery部件的Open方法或ExecSQl方法执行 SQL 命令。 Open 方法只执行Select命令，ExecSQL方法还可以执行其它的SQL命令。Open方法和ExecSQL 方法的区别我们在后面的章节里会进一步地加以讨论的。
如果使用动态SQL语句，首先调用prepare方法，给动态SQL语句中的参数赋值，然后再调用Open方法或ExecSQL方法。调用propare 方法并不是必须的，但是对于要多次执行TQuery部件中SQL属性中的动态SQL语句，调用Prepare可以大大提高TQuery部件执行SQL语句的性能。　
17.2.2 在TQuery部件中编写简单的SQL查询命令　
在这一节里我们将学习如何使用TQuery部件编写简单的SQL查询命令，并在Delphi 应用程序中实现SQL查询。
例如，如果我们想查询出表Customer.DB中客户的编号和公司名称，我们按下列步骤来实现：
①在应用窗体中放置一个TQuery部件、一个TDataSource部件一个TDataGrid部件，并将它们连接起来　
②设置窗体TQuery 部件Query1的DatabaseName属性值为DBDEMOS
③双击Object Inspector窗口中Query1的SQL 属性， Delphi 将显示 String List Editor窗口。
④在图17.3中的窗口中输入SQL语句：
Select CustNo,Company From Custormer;
⑤单击OK按钮，关闭String List Editor窗口。
⑥设置Query的Open属性为True。
17.3 SQL语言编程概述　
在Delphi应用程序中的SQL命令语句是包含在TQuery部件的SQL属性中，TQuery部件的SQL属性是TString类型的，也就是说SQL属性值是一个字符串列表，这个字符串列表非常类似于一个字符串类型的数组，有关TString类型的信息请参看联机帮助。在前一节里我们介绍了TQuery部件可以执行两种SQL语句：
● 静态SQL语句
● 动态SQL语句　
静态SQL语句在程序设计时便已固定下来，它不包含任何参数和变量，例如下面的语句便是一条静态SQL语句：　
Select * From Cusromer Where CustNo = 1234;　
而动态SQL语句，也被称作参数化的语句，在其中间包含着表示字段名或表名的参数，例如下面的语句是一条动态SQL语句：　
Select * From Customer Where CustNo =: Number;
　
其中的变量Number便是一个参数变量，它由一个冒号引导，在程序运行过程中，必须要为该参数赋值，该条SQL语句才能正确执行，每次运行应用程序时可以为该参数变量赋予不同的值。
　
17.3.1 SQL命令文本的编写
　
1. 使用String List Editor编写
我们要为TQuery部件的SQL属性设置SQL命令文本时，可以在应用窗体中选择TQuery部件且双击Object Inspector窗口中的SQL属性，这样便打开了String List Editor 窗口，在该窗口中我们便可以编写各种SQL命令，如图17.3所示。
在编写完适当的SQL语句之后，选择 OK 按钮便可以将编辑器中的 SQL 命令文装入到TQuery部件的SQL属性中，选择SAVE按钮可以将编写好的SQL命令保存到一个文件中供以后编程时使用。我们在编写SQL命令文本时还可以选择Load按钮从一个 SQL 命令文件中调入SQL命令。在程序运行过程中，要想设置TQuery部件的SQL属性，必须首先调用Close方法，关闭TQuery部件，然后再调用Clear方法清除SQL属性中现存的SQL命令语句，最后再调用Add方法为SQL属性设置新的SQL命令语句。例如：
　
Query1.Close {关闭Query1)
Query1.SQL.Clear {清除SQL属性中的SQL命令语句}
Query1.SQL.Add(‘Select * From Country‘);
Query1.SQL.Add(‘Where Name ="ARGENTINA" ‘);
　
在为TQuery部件设置SQL属性时调用Close方法总是很安全的，如果TQuery部件已经被关闭了，调用Close方法时不会产生任何影响。在应用程序中为SQL属性设置新的SQL 命令语句时，必须要调用Clear方法以清除SQL属性中现存的SQL命令语句，如果不调用Clear方法，便调用Add方法向SQL属性中设置SQL命令语句，那么新设置的SQL命令语句会追加在现存SQL命令语句后面，在程序运行时常常会出现出乎意料的查询结果甚至程序无法运行下去。
在这里要特别注意的，一般情况下TQuery部件的SQL属性只能包含一条完整的SQL语句，它不允许被设置成多条SQL语句。当然有些数据库服务器也支持在TQuery部件的SQL属性中设置多条SQL语句，只要数据库服务器允许这样，我们在编程时可以为 SQL 属性设置多条SQL语句。
2. 使用Visual Query Builder编写
客户/服务器版本的Delphi还包含一个可视化的查询构造器Visual Query Builder ，用这个可视化的工具我们只能编写Select语句。在应用程序窗体中选择TQuery部件后，单击鼠标右键，弹出一个弹出式菜单，从中选择Run Visual Query Builder后便会弹出一对话框提示你选择要访问的数据库，选择想要访问的数据库之后选择OK按钮，紧接着会出现一个弹出式对话框提示你选择要查询的数据库表，一次可以选择多个数据库表，若要选择多个数据库表，每选择一个表之后单击Add按钮，接着选择另一个表，选择完要查询的表之后单击Close按钮，这样，可视化的查询构造器中将会显示出用户选择的数据库表。　　
有关如何使用可视化的查询构造器Visual Query Builder 请参看联机帮助信息，在Visual Query Builder中构造完一个查询并退出Visual Query Builder时，其中的SQL 命令语句会自动地写入相应的TQuery部件的SQL属性。　
17.3.2 SQL程序的执行　
在为TQuery部件设置完SQL属性的属性值之后，也即编写好适当的SQL程序之后，可以有多种方式来执行SQL程序。
在设计过程中，设置完TQuery部件的SQL属性之后将其Active属性的值置为True，这样便可以执行SQL属性中的SQL程序，如果应用中有与TQuery部件相连的数据浏览部件( 如TDDGrid TDBEdit等)那么在这些数据浏览部件中会显示SQL程序的执行结果。
在应用程序运行过程中，通过程序调用TQuery部件的Open方法或ExecSQL 方法可以执行其SQL属性中的SQL程序。Open方法和ExecSQL方法是不一样的。大家在程序设计过程中一定要注意。Open方法只能用来执行SQL语言的查询语句(Select命令)，并返回一个查询结果集，而ExecSQL方法还可以用来执行其它常用的SQL语句(如Insert、UPDATE、 DELETE等命令)例如：
　
Query1.Open (这样会返回一个查询结果集)
　
如果调用Open方法，而没有查询结果时，会出错。此时应该调用ExecSQL 方法来代替Open方法。如：
　
Query1.ExecSQL (没有返回结果)
　
当然在设计应用程序时，程序设计人员是无法确定TQuery部件中的SQL 语句是否会返回一个查询结果的。对于这种情况应当用Try…Except模块来设计程序。在 Try 部分调用Open方法，而在Except部分调用ExceSQL方法，这样才能保证程序的正确运行。
例如：
　
Try
Query1.Open
Except
Query1.ExecSQL
End
　
在应用程序中使用TQuery部件时，还可以设置它的UniDirectional属性为True，这样会加快检索数据库表的速度，但是这样只能往一个方向移动记录指针，在缺省情况下，UniDirectional属性的值为False。
　
17.3.3 通过TQuery部件如何获得活动的数据
　
我们在前面的章节里介绍TTable部件时，我们知道通过TTable部件从数据库中获得的数据都是活动的，也就是说用户可以直接通过数据浏览部件对这些数据进行编辑修改。而通过TQuery部件可以获得两种类型的数据：
● “活动”的数据
这种数据就跟通过TTable部件获得的数据一样，用户可以通过数据浏览部件来编辑修改这些数据，并且当调用Post方法或当焦点离开当前的数据浏览部件时，用户对数据的修改自动地被写回到数据库中，详细情况请参看第四章“数据浏览部件的应用及编程”。
● 非活动的数据(只读数据)
用户通过数据浏览部件是不能修改其中的数据。在缺省情况下，通过TQuery部件获得的查询结果数据是只读数据，要想获得“活动”的数据，在应用程序中必须要设置TQuery部件的RequestLive属性值为True，然而并不是在任何情况下(通过设置RequestLive的属值True)都可以获得“活动”的数据的，要想获得“活动”的数据，除了将TQuery部件的RequestLive属性为True外，BDE要能够返回“活动”的数据，相应的SQL命令语句还要满足附录C中的语法规则和下列的约束条件：
TQuery部件获得“活动”的查询结果数据的约束条件：
　
当查询Paradox或dBASE数据库中的表：
● 查询只能涉及到一个单独的表
● SQL语句中不能包含ORDER BY命令
● SQL语句中不能含聚集运算符SUM或AVG
● 在Select后的字段列表中不能有计算字段
● 在Select语句WHERE部分只能包含字段值与常量的比较运算，这些比较运算符是： Like,>,<,>=,<=，各比较运算之间可以有并和交运算：AND和OR。
　
当通过SQL语句查询数据库服务器中的数据库表：
● 查询只能涉及到一个单独的表
● SQL语句中不能包含ORDER BY命令
● SQL语句中不能含聚集运算符SUM或AVG运算
　
另外，如果是查询Sybase数据库中的表，那么被查询的表中只能有一个索引。
如果在应用程序中要求TQuery部件返回一个“活动”的查询结果数据集，但是SQL 命令语句不满足上述约束条件时，对于本地数据库的SQL查询，BDE只能返回只读的数据集。对于数据库服务器中的SQL查询，只能返回错误的代码。当TQuery 部件返回一个“活动”的查询结果数据集时，它的CanModify属性的值会被设置成True。
　
表17.1 TQuery部件返回查询结果数据的类型
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RequestLive属性值 CanModify属性值查询结果的类型
────────────────────────────────
False False 只读数据
True(SQL语句满足约束条件) True “活动”数据
True(SQL语句不满足约束条件) False 只读数据
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
　
当TQuery部件返回只读的查询结果数据集，而用户又希望修改这只读的数据集时，一般这样来处理，在应用程序中另外增加一个 TQuery 部件 Query2( 假设获得只读结果的TQuery部件的名字是Query1)，在Query2中设置修改语句UpDATE对Query1 中的数据进行修改操作，这样会实现对只读数据的修改。
　
17.4 动态SQL语句的编程
　
在17.3节中，我们已经介绍了动态SQL语句（又被称为参数化的SQL语句），在其中包含在程序过程中可以变化的参数，在实际的程序设计中使用得更多的是动态SQL语句，因而在这一节里我们重点介绍如何给动态SQL语句的参数赋值，以在应用程序中灵活地使用SQL语句。动态SQL语句的编写、执行等等与17.3节中介绍的SQL语句的编写、执行是一样的。
动态SQL语句中的参数，我们可以通过两种途径来为它赋值：
1. 利用参数编辑器(Parameter Editor)来为参数赋值
具体方法是：选中TQuery部件，单击鼠标右键，然后从中选择Define Parameters 便可以打开参数编辑器。　
例如，在TQuery部件的SQL属性中我们设置如下的SQL语句：　
Setect * From Customer Where CustNO=:Number;
　
TQuery的DatabaseName属性为DBDEMOS，其中Number为参数变量。我们便可以为参数Number赋值，在Datetype组合框中选择该参数的数据类型为整数Integer，在Value编辑框中可以为参数Number赋一个值，也可以单击Null Value检查框为参数Number赋一个空值Null。给参数赋值之后，单击OK按钮，这样TQuery部件中的SQL 查询便准备好了，而且参数值也被赋给了动态SQL语句中相应的参数，此时当把TQuery 部件的Active属性设置成True时，在与TQuery部件相连的数据浏览部件中会显示出查询结果，通过参数编辑器为参数赋值，这种方式缺乏应有的灵活性，在实际应用中用得较少，在实际应用中程序设计人员希望用更灵活方便的方式为参数赋值，那就是我们接下来要介绍的另一种途径：
2. 在运行过程中，通过程序为参数赋值
用这种方式为参数赋值有三种方法：
①根据参数在SQL语句中出现的顺序，设置TQuery部件的Params属性值为参数赋值。
②直接根据SQL语句中各参数的名字，调用ParamByName方法来为各参数赋值。
③将TQuery部件的DataSource属性设置为另一个数据源，这样将另一个数据源中与当前TQuery部件的SQL语句中的参数名相匹配的字段值赋给其对应的参数。
这三种方法我们将在下面的三小节中具体地介绍
　
17.4.1 使用Params属性为参数赋值
　
TQuery部件具有一个Params属性，它们在设计时不可用，在程序运行过程中可用，并且是动态建立的，当为TQuery部件编写动态SQL 语句时， Delphi 会自动地建立一个数组Params，数组Params是以0下标开始的，依次对应动态SQL 语句中的参数，也就是说动态SQL语句中第一个参数对应Params[0],第二个参数对应params[1]，依此类推。
例如：一个TQuery部件Query1，我们为它编写的动态SQL语句是：
　
Insert Into Customer(CustNo,Name,Country)
Values(:CustNo,:Name, : Country)
　
对于上述这条动态SQL语句中的参数，我们可以利用TQuery部件的params 属性为参数赋值：
　
Query1.params[0].AsString := "1988";
Query1.params[1].AsString := "Lichtenstein";
Query1.params[2].AsString := "USA";
　
上述语句将把"1988"赋给参数:Cuse_No，"Lichtenstein"赋给参数:Name，"USA"赋给参数:Country。
　
17.4.2 使用ParamByName方法为参数赋值
　
ParamByName是一个函数，用动态SQL语句中的参数作为调用ParamByName函数的参数，这样便可以为它们赋值，使用这种赋值方法，必须要知道动态SQL语句参数的名字。
例如在17.4.1节中的例子中，也可以用下述方法给参数赋值：
　
Query1.ParamByName(‘CustNo‘).AsString := "1988";
Query1.ParamByName(‘Name‘).AsString := "Lichtenstein";
Query1.ParamByName(‘Country‘).AsString := "USA";
　
使用这种方法同样可以为各参数赋值，而且更加直观一些。
　
17.4.3 使用Datasource属性为参数赋值
　
上述两种方法的共同特点是：我们在为各参数赋值时，我们是知道各参数对应的具体参数值的。而在具体的应用程序中，有些参数值常常是无法确定的，例如参数值来自于另一个查询结果，对于这种情况，Delphi提供了使用Datasource属性为动态SQL 语句中尚存在没有赋值的参数时， Delphi 会自动检查 TQuery 部件的 Datasource 属性，如果为Datasource属性设置了属性值(该属性的值是另一个TDatasource部件的名字)，Delphi 会把没有赋值的参数与TDatasource部件中的各字段比较，Delphi 会将相应的字段值赋给与其相匹配的参数，利用这种方法也能实现所谓的连接查询，我们在学习使用TTable部件时，便会创建主要－－明细型数据库应用，用TQuery部件创建的连接查询与主要- －明细型应用是相似的。
例如：在如图17.7所示的应用中，设置了下列部件：
● 一个TTable部件
名字为Cust，它的DatabaseName属性为DEMOS，TableName属性为Customer。
● 一个TDatasource部件
名字为Custsource，其Dataset属性被设置为Cust。
● 一个TQuery部件
名字为ORDERS，其DatabaseName被设置为DEMOS，SQL属性值为：
　
Select Orders.CustNo,Orders.OrderNo,Orders.SaleDate FROM Orders
WHERE Orders.CustNo =: CustNo
　
ORDERS的DataSouce属性被设置为CustSource
● 一个TDatasource部件
名字为OrderSource，其DataSet属性被设置为Orders。
● 两个TDBGrid部件
它们分别连接CustSource和OrderSource。
TQuery部件Orders中的动态SQL语句中的参数:CustNo在程序设计过程中没有给它赋值，当该应用程序运行时Delphi会自动地到其Datasource属性中说明的数据源CustSource中查找与参数:CustNo匹配的字段，而CustSource中正好有一个名字为 CustNo 的字段与参数:CustNo匹配，这样Customer表中的CustNo字段值被赋给了参数 : CustNo ，而当每移动Customer表中的记录指针，参数:CustNo的值会随之改变，而参数:CustNo的值发生改变时，Orders中的动态SQL语句会根据新的参数值重新查询，从数据库表中获取相应的订单数据，这样也变实现了类似于主要－－明细型应用。即连接查询。　
17.4.4 Prepare方法的使用　
在使用动态SQL语句编程时，常常用到一个很重要的方法prepare，调用prepare 方法之后，Delphi会将带参数的SQL语句传送给与其对应的数据库引擎，对动态SQL语句进行语法分析和优化。虽然在用动态SQL语句编程时，调用prepare方法并不是必须的，但是这里我们要极力推荐调用prepare方法，因为调用prepare方法后，会大大提高动态SQL 语句的执行性能，特别是当要反复多次执行同一条动态SQL语句时，其优越性会更加明显。如果在应用程序中执行一条SQL语句之前并没有显式地调用prepare方法，每次在执行SQL 语句时，Delphi会隐含地调用propare方法以准备这个查询。
TQuery部件还有一个prepare属性，这是一个布尔型属性，当其属性值为True时，表明该查询已被准备好了( SQL 语句已被传送到数据库引擎中 ) ，当我们使用参数编辑器Parameters Editor来为动态SQL语句中的参数赋值时，当设置完相应的参数值并退出参数编辑器时，Delphi会隐含地调用prepare方法以准备好查询。
当SQL语句执行完之后，要想准备下一个查询，首先必须调用close方法，然后才能调用prepare方法准备下一个查询。一般来说，在一个应用程序中应该调用一次prepare方法，常常在窗体的OnCreate事件处理过程中调用prepare方法，然后用上述介绍的方法为参数赋值，最后调用Open方法或ExecSQL方法执行SQL语句，以完成查询。
当然在调用prepare方法准备好一个查询时，会消耗一些数据库资源，因而每当一个查询执行完毕之后，要养成调用Unprepare方法以撤消查询的好习惯。在运行程序过程中，通过程序改变TQuery部件的SQL属性值时，Delphi会自动地调用Close方法和Unprepare 方法，以撤消查询。

SQL JOIN

SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
Join 和 Key
有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。
请看 "Persons" 表：
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
请注意，"Id_P" 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同，Id_P 也可以区分他们。
接下来请看 "Orders" 表：
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
请注意，"Id_O" 列是 Orders 表中的的主键，同时，"Orders" 表中的 "Id_P" 列用于引用 "Persons" 表中的人，而无需使用他们的确切姓名。
请留意，"Id_P" 列把上面的两个表联系了起来。
引用两个表
我们可以通过引用两个表的方式，从两个表中获取数据：
谁订购了产品，并且他们订购了什么产品？
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
结果集：
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
SQL JOIN - 使用 Join
除了上面的方法，我们也可以使用关键词 JOIN 来从两个表中获取数据。
如果我们希望列出所有人的定购，可以使用下面的 SELECT 语句：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
结果集：
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。
函数的语法
内建 SQL 函数的语法是：
SELECT function(列) FROM 表
函数的类型
在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：
Aggregate 函数
Scalar 函数
合计函数（Aggregate functions）
Aggregate 函数的操作面向一系列的值，并返回一个单一的值。
注释：如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，则这个 SELECT 必须使用 GROUP BY 语句！
"Persons" table (在大部分的例子中使用过)
Name Age
Adams, John 38
Bush, George 33
Carter, Thomas 28
MS Access 中的合计函数
函数描述
AVG(column) 返回某列的平均值
COUNT(column) 返回某列的行数（不包括 NULL 值）
COUNT(*) 返回被选行数
FIRST(column) 返回在指定的域中第一个记录的值
LAST(column) 返回在指定的域中最后一个记录的值
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column) 返回某列的总和
VAR(column)
VARP(column)
在 SQL Server 中的合计函数
函数描述
AVG(column) 返回某列的平均值
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
COUNT(column) 返回某列的行数（不包括NULL值）
COUNT(*) 返回被选行数
COUNT(DISTINCT column) 返回相异结果的数目
FIRST(column) 返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column) 返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column) 返回某列的总和
VAR(column)
VARP(column)
Scalar 函数
Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。
MS Access 中的 Scalar 函数
函数描述
UCASE(c) 将某个域转换为大写
LCASE(c) 将某个域转换为小写
MID(c,start[,end]) 从某个文本域提取字符
LEN(c) 返回某个文本域的长度
INSTR(c,char) 返回在某个文本域中指定字符的数值位置
LEFT(c,number_of_char) 返回某个被请求的文本域的左侧部分
RIGHT(c,number_of_char) 返回某个被请求的文本域的右侧部分
ROUND(c,decimals) 对某个数值域进行指定小数位数的四舍五入
MOD(x,y) 返回除法操作的余数
NOW() 返回当前的系统日期
FORMAT(c,format) 改变某个域的显示方式
DATEDIFF(d,date1,date2) 用于执行日期计算
http://www.w3school.com.cn/sql/sql_functions.asp

SQL 语句获取表字段名称，属性

SQL 语句获取表字段名称，属性
SQL SERVER
查看所有表名：
select name from sysobjects where type=‘U‘
查询表的所有字段名：
Select name from syscolumns Where ID=OBJECT_ID(‘表名‘)
select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
ACCESS
查看所有表名：
select name from MSysObjects where type=1 and flags=0
MSysObjects是系统对象，默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。
--获取表字段名称，属性
SELECT
表名 = case when a.colorder=1 then d.name else ‘‘ end,
表说明 = case when a.colorder=1 then isnull(f.value,‘‘) else ‘‘ end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype=‘PK‘ and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then ‘√‘ else ‘‘ end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,‘PRECISION‘),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0),
允许空 = case when a.isnullable=1 then ‘√‘else ‘‘ end,
默认值 = isnull(e.text,‘‘),
字段说明 = isnull(g.[value],‘‘)
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘
left join
syscomments e
on
a.cdefault=e.id
left join
sysproperties g
on
a.id=g.id and a.colid=g.smallid
left join
sysproperties f
on
d.id=f.id and f.smallid=0
where
d.name=‘要查询的表‘ --如果只查询指定表,加上此条件
order by
a.id,a.colorder
--获取库中所有表字段的定义（包括名字，属性等）
--1. SqlServer数据库字典--表结构.sql
/**
表a为syscolumns
表b为systypes
表d为sysobjects
表e为syscomments
表g为sysproperties
*/

SELECT TOP 100 PERCENT --a.id,

CASE WHEN a.colorder = 1 THEN d.name ELSE ‘‘ END AS 表名,

CASE WHEN a.colorder = 1 THEN isnull(f.value, ‘‘) ELSE ‘‘ END AS 表说明,

a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,

a.name, ‘IsIdentity‘) = 1 THEN ‘√‘ ELSE ‘‘ END AS 标识,

CASE WHEN EXISTS

(SELECT 1

FROM dbo.sysindexes si INNER JOIN

dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN

dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN

dbo.sysobjects so ON so.name = so.name AND so.xtype = ‘PK‘

WHERE sc.id = a.id AND sc.colid = a.colid) THEN ‘√‘ ELSE ‘‘ END AS 主键,

b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, ‘PRECISION‘)

AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, ‘Scale‘), 0) AS 小数位数,

CASE WHEN a.isnullable = 1 THEN ‘√‘ ELSE ‘‘ END AS 允许空, ISNULL(e.text, ‘‘)

AS 默认值, ISNULL(g.[value], ‘‘) AS 字段说明, d.crdate AS 创建时间,

CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间

FROM dbo.syscolumns a LEFT OUTER JOIN

dbo.systypes b ON a.xtype = b.xusertype INNER JOIN

dbo.sysobjects d ON a.id = d.id AND d.xtype = ‘U‘ AND

d.status >= 0 LEFT OUTER JOIN

dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN

dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN

dbo.sysproperties f ON d.id = f.id AND f.smallid = 0

ORDER BY d.name, a.colorder

SQL：要求查询出用户信息，根据用户发表的信息个数对用户进行排序

要求：
查询出用户信息，根据用户发表的信息个数对用户进行排序
[sql] view plaincopy
/*用户表*/
CREATE TABLE `tb_user` (
`userId` int(11) NOT NULL auto_increment COMMENT ‘testtest‘,
`userName` varchar(64) default NULL,
`userNo` varchar(64) default NULL,
PRIMARY KEY (`userId`)
);
/*插入测试数据*/
insert into `tb_user`(`userId`,`userName`,`userNo`) values (1,‘张三‘,‘NO001‘),(2,‘李四‘,‘NO002‘),(3,‘王五‘,‘NO003‘);

/*信息表*/
CREATE TABLE `tb_article` (
`article_id` int(11) NOT NULL auto_increment,
`title` varchar(256) NOT NULL,
`content` varchar(1024) default NULL,
`userId` int(11) NOT NULL,
PRIMARY KEY (`article_id`)
);

/*插入测试数据*/
insert into `tb_article`(`article_id`,`title`,`content`,`userId`) values (1,‘a‘,‘a‘,1),(2,‘b‘,‘b‘,1),(3,‘c‘,‘c‘,2),(4,‘d‘,‘d‘,3),(5,‘e‘,‘e‘,2),(6,‘f‘,‘f‘,2);

根据信息表中信息个数对userId进行分组排序
[sql] view plaincopy
SELECT userId FROM tb_article WHERE 1=1 GROUP BY userId ORDER BY COUNT(userId) DESC

使用子查询和刚才的排序的结果，查询出用户信息
[sql] view plaincopy
SELECT t1.userId,(SELECT userName FROM tb_user t2 WHERE t2.userId=t1.userId) AS userName FROM tb_article AS t1 WHERE 1=1 GROUP BY t1.userId ORDER BY COUNT(t1.userId) DESC

最后的结果：
userId userName
2 李四
1 张三
3 王五

如果有更好的方法请不吝赐教！

SQL查询语句

用户购物价格（列表）
select wangwang,SUM(Price) from JF_Sale GROUP BY wangwang ORDER BY wangwang
用户购物总价、最后购物时间（个人）
select wangwang,SUM(Price),max(adddate) from JF_Sale where wangwang=‘chenzhenyu1982‘ GROUP BY wangwang
用户购物总价、最后购物时间、购物次数（个人）
select wangwang,SUM(Price),count(Price),max(adddate) from JF_Sale where wangwang=‘longzxmmsh‘ GROUP BY wangwang
用户购物总价、最后购物时间、购物次数(列表)
select wangwang,SUM(Price),max(adddate),count(Price) from JF_Sale GROUP BY wangwang ORDER BY wangwang
客单价的计算公式是：客单价=销售总额÷顾客总数，或者是客单价=销售总金额÷成交总笔数
select wangwang,SUM(Price),count(Price),max(adddate) from JF_Sale GROUP BY wangwang

select JF_Member.wangwang,JF_Member.tel,MAX(JF_Sale.adddate) from JF_Member,JF_Sale where JF_Member.wangwang = JF_Sale.wangwang GROUP BY wangwang

select wangwang,SUM(Price),count(Price),max(adddate) from JF_Sale where wangwang=‘longzxmmsh‘ GROUP BY wangwang union all select * from JF_Member

SQL多表联合查询（Access数据库表）

条件：Access数据库的三个表，a表、b表、c表，三个表结构不同，其中都有字段ID，a表为主表，其中的ID不一定b表、c表中都存在，建立此三个表的联合查询。

根据SQL语法，通过连接运算符可以实现多个表查询。连接可以在Select 语句的FROM子句或Where子句中建立，在FROM子句中指出连接时有助于将连接操作与Where子句中的搜索条件区分开来。　　
SQL-92标准所定义的FROM子句的连接语法格式为：
　　FROM join_table join_type join_table
　　[ON (join_condition)]
其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。
join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
前提：假设已存在外部数据库，并且已经打开包含a表、b表、c表三表的Mdb数据库。根据SQL语法规则，写出如下代码：
1. 内查询（查询三个表中均存在的ID记录，任何一个表中不存在的id将均被过滤掉）
.版本 2
记录集句柄＝外部数据库1.查询 (“select * from a表 inner join b表 on a表.id=b表.id inner join c表 on a表.id=c表.id ” ）‘＋ “ where ……”) ‘ where 条件暂时省略
2. 外查询（left join ,列出左表中的全部ID，不管b、c表中有无该ID）
.版本 2
记录集句柄＝外部数据库1.查询 (“select * from a表 left join b表 on a表.id=b表.id left join c表 on a表.id=c表.id ” ）‘＋ “ where ……”) ‘ where 条件暂时省略
可是运行结果均为查询失败，如果仅仅两个表，查询则成功，三个或三个以上的表就会失败。
经过一天的捉摸，终于弄明白了问题原因，问题不在SQl，也不在易语言，而是没弄明白Access对SQL的支持，将上述代码改为如下代码，问题解决！
.版本 2
记录集句柄＝外部数据库1.查询 (“select * from (a表 inner join b表 on a表.id=b表.id) inner join c表 on a表.id=c表.id ”) ‘ ＋ “ where ……”) ‘ where 条件暂时省略
.版本 2
记录集句柄＝外部数据库1.查询 (“select * from (a表 left join b表 on a表.id=b表.id) left join c表 on a表.id=c表.id ”) ‘ ＋ “ where ……”) ‘ where 条件暂时省略
如果有四张表，可以写为：
.版本 2
记录集句柄＝外部数据库1.查询 (“select * from ((a表 inner join b表 on a表.id=b表.id) inner join c表 on a表.id=c表.id) inner join d表 on a表.id=d表.id ”) ‘ ＋ “ where ……”) ‘ where 条件暂时省略
问题很简单，想不到位则很头疼，为了众弟兄少走弯路，故尔拿出分享！是踩还是顶，随大家心意！

SQL获取所有数据库名、表名、储存过程以及参数列表

1.获取所有用户名:
SELECT name FROM Sysusers where status=‘2‘ and islogin=‘1‘
islogin=‘1‘表示帐户
islogin=‘0‘表示角色
status=‘2‘表示用户帐户
status=‘0‘表示糸统帐户
2.获取所有数据库名:
SELECT Name FROM Master..SysDatabases ORDER BY Name
3.获取所有表名
SELECT Name FROM DatabaseName..SysObjects Where XType=‘U‘ ORDER BY Name
XType=‘U‘:表示所有用户表;
XType=‘S‘:表示所有系统表;
4.获取所有字段名:
SELECT Name FROM SysColumns WHERE id=Object_Id(‘TableName‘)
5.获取数据库所有类型
select name from systypes
6.获取主键字段
SELECT name FROM SysColumns WHERE id=Object_Id(‘表名‘) and colid=(select top 1 keyno from sysindexkeys where id=Object_Id(‘表名‘))

[综合网络资料整理]
1.获取所有数据库名:
(1)、Select Name FROM Master..SysDatabases order by Name
2.获取所有表名:
(1)、Select Name FROM SysObjects Where XType=‘U‘ orDER BY Name
XType=‘U‘:表示所有用户表;
XType=‘S‘:表示所有系统表;
(2)、SELECT name FROM sysobjects WHERE type = ‘U‘ AND sysstat = ‘83‘
注意：一般情况只需要type = ‘U‘，但有时候会有系统表混在其中（不知道什么原因），加上后面一句后就能删除这些系统表了

3.获取所有字段名:
(1)、Select Name FROM SysColumns Where id=Object_Id(‘TableName‘)
(2)、SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND "syscolumns.id = object_id(‘tableName‘)
注意点：
（a）这里为了重点突出某些重要内容，选取了其中几项信息输出。
（b）syscolumns表中只含有数据类型编号，要获取完整的名字需要从systypes表中找，一般用户使用的数据类型用xusertype对应比较好，不会出现一对多的情况。
（c）syscolumns.length得到的是物理内存的长度，所以nvarchar和varchar等类型在数据库中的显示是这个的一半。
4、得到表中主键所包含的列名:
SELECT syscolumns.name FROM syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id = object_id(‘tablename‘) AND sysobjects.xtype = ‘PK‘ AND sysobjects.parent_obj = syscolumns.id AND sysindexes.id = syscolumns.id AND sysobjects.name = sysindexes.name AND sysindexkeys.id = syscolumns.id AND sysindexkeys.indid = sysindexes.indid AND syscolumns.colid = sysindexkeys.colid
注意：这是在4张系统表中寻找的，关系比较复杂，大致可以表示为：
syscolumns中存有表中的列信息和表id，sysobjects表中存有主键名字（即PK_Table类似）和表id，sysindexes中存有主键名字和表id和index编号，sysindexkeys中存有表id和index编号和列编号，一项一项对应起来后就能找到列名了。

另外的SQL代码
select syscolumns.name, systypes.name, syscolumns.length from syscolumns

left join systypes on syscolumns. xusertype =systypes. xusertype

where id=(select id from sysobjects where name=‘订货主档‘);

go;
或者用这样的写法，执行结果一样：
select syscolumns.name, systypes.name, syscolumns.length from syscolumns,systypes

where (syscolumns.id=object_id(‘订货主档‘) and syscolumns.xusertype=systypes.xusertype)

order by syscolumns.colorder;
go
执行结果：（字段只出现一次，正常）
订单号码 int 4
客户编号 nvarchar 10
员工编号 int 4
订单日期 datetime 8
要货日期 datetime 8
送货日期 datetime 8
送货方式 int 4
运费 money 8
收货人 nvarchar 80
送货地址 nvarchar 120
送货城市 nvarchar 30
送货行政区 nvarchar 30
送货邮政编码 nvarchar 20
送货国家地区 nvarchar 30

select syscolumns.name, systypes.name, syscolumns.length from syscolumns

left join systypes on syscolumns.xtype=systypes.xtype

where id=(select id from sysobjects where name=‘订货主档‘);

go;
执行结果：（部分字段出现两次，数据类型不同）
订单号码 int 4
客户编号 nvarchar 10
客户编号 sysname 10
员工编号 int 4
订单日期 datetime 8
订单日期出生日期类型 8
要货日期 datetime 8
要货日期出生日期类型 8
送货日期 datetime 8
送货日期出生日期类型 8
送货方式 int 4
运费 money 8
运费薪水类型 8
收货人 nvarchar 80
收货人 sysname 80
送货地址 nvarchar 120
送货地址 sysname 120
送货城市 nvarchar 30
送货城市 sysname 30
送货行政区 nvarchar 30
送货行政区 sysname 30
送货邮政编码 nvarchar 20
送货邮政编码 sysname 20
送货国家地区 nvarchar 30
送货国家地区 sysname 30
查询存储过程DepartmentSalaryInfo所有的信息，信息包含在系统视图syscolumns，systypes中
select syscolumns.*, systypes.* from syscolumns

left join systypes on syscolumns.xusertype=systypes.xusertype

where id=(select id from sysobjects where name=‘DepartmentSalaryInfo‘);

go

exec sp_procedure_params_rowset @procedure_name = ‘DepartmentSalaryInfo‘;

go
执行结果：
北风贸易 dbo DepartmentSalaryInfo;1 @RETURN_VALUE 0 4 0 NULL 0 3 NULL NULL 10 NULL NULL int int
北风贸易 dbo DepartmentSalaryInfo;1 @department 1 1 0 NULL 1 129 10 10 NULL NULL NULL varchar varchar
北风贸易 dbo DepartmentSalaryInfo;1 @average 2 2 0 NULL 1 6 NULL NULL 19 NULL NULL money money
北风贸易 dbo DepartmentSalaryInfo;1 @maximum 3 2 0 NULL 1 6 NULL NULL 19 NULL NULL money money
北风贸易 dbo DepartmentSalaryInfo;1 @minimum 4 2 0 NULL 1 6 NULL NULL 19 NULL NULL money money

--存储过程中的参数名，参数类型，参数长度

select syscolumns.name, systypes.name, syscolumns.length from syscolumns

left join systypes on syscolumns.xusertype=systypes.xusertype

where id=(select id from sysobjects where name=‘DepartmentSalaryInfo‘);

1：获取当前数据库中的所有用户表
select Name from sysobjects where xtype=‘u‘ and status>=0
2：获取某一个表的所有字段
select name from syscolumns where id=object_id(‘表名‘)
3：查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from
master..syslogins where name=‘sa‘)
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01
4：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ‘表名‘
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
8.如何修改数据库的名称:
sp_renamedb ‘old_name‘, ‘new_name‘
9.只复制一个表结构，不复制数据
select top 0 * into [t1] from [t2]
10.连接远程数据库
select * from OPENDATASOURCE(‘SQLOLEDB‘,‘Data Source=远程ip;User
ID=sa;Password=密码‘).库名.dbo.表名
11.获取当前oracle数据库中的所有表
select table_name from user_tables
12 .获取当前oracle表中所有字段的类型
SELECT
COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE
FROM
USER_TAB_COLS where TABLE_NAME=‘teacher‘;
【Blog】http://virusswb.cnblogs.com/

【MSN】[email protected]

【说明】转载请标明出处，谢谢

SQL联合查询中的关键语法

联合查询效率较高.以下例子来说明联合查询的好处

t1表结构(用户名,密码) userid int username varchar(20) password varchar(20)

1 jack jackpwd

2 owen owenpwd

t3表结构(用户积分,等级) userid int jf int dj int

1 20 3

3 50 6

第一:内联(inner join)

如果想把用户信息,积分,等级都列出来.那么一般会这样写

select * from t1 ,t3 where t1.userid = t3.userid 其实这样的结果等同于select * from t1 inner join t3 on t1.userid=t3.userid

就是把两个表中都存在userid的行拼成一行.这是内联.但后者的效率会比前者高很多.建议用后者的写法.

运行结果:userid username password userid jf dj

1 jack jacjpwd 1 20 3

第二:左联（left outer join）显示左表中的所有行

select * from t1 left outer join t3 on t1.userid=t3.userid

运行结果：userid username password userid jf dj

　　　　　　1 jack jackpwd 1 20 3

2 owen owenpwd NULL NULL NULL

第三:右联（right outer join）显示右表中的所有行

select * from t1 right outer join t3 on t1.userid=t3.userid

运行结果：userid username password userid jf dj

　　　　　　1 jack jackpwd 1 20 3

Null Null Null 3 50 6

第四:全联（full outer join）显示两边表中所有行

select * from t1 full outer join t3 on t1.userid=t3.userid

运行结果：userid username password userid jf dj

　　　　　　1 jack jackpwd 1 20 3

2 owen owenpwd NULL NULL NULL

Null Null Null 3 50 6

总结，关于联合查询，本人已测试过．效率的确比较高，４种联合方式如果可以灵活使用，基本上复杂的语句结构也会简单起来．这４种方式是：

Inner join left outer join right outer join full outer join

Sql语句

经典SQL语句大全
一、基础
1、说明：创建数据库
CREATE DATABASE database-name
2
、说明：删除数据库
drop database dbname
3、说明：备份sql server
---
创建备份数据的
device
USE master
EXEC sp_addumpdevice ‘disk‘, ‘testBack‘, ‘c:\mssql7backup\MyNwind_1.dat‘
---
开始备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注
：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键：
Alter table tabname add primary key(col)
说明：删除主键：Alter table tabname drop primary key(col)
8、说明：创建索引：
create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10
、说明：几个简单的基本的sql语句
选择：
select * from table1 where 范围
插入：
insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：
select * from table1 order by field1,field2 [desc]
总数：
select count as totalcount from table1
求和：
select sum(field1) as sumvalue from table1
平均：
select avg(field1) as avgvalue from table1
最大：
select max(field1) as maxvalue from table1
最小：
select min(field1) as minvalue from table1
11
、说明：几个高级查询运算词
A：
UNION 运算符
UNION 运算符通过组合其他两个结果表（例如TABLE1 和TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL 随UNION 一起使用时（即UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1 就是来自TABLE2。
B：EXCEPT 运算符
EXCEPT
运算符通过包括所有在TABLE1 中但不在TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL 随EXCEPT 一起使用时
(EXCEPT ALL)，不消除重复行。
C：INTERSECT
运算符
INTERSECT
运算符通过只包括TABLE1 和TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL(INTERSECT ALL)，不消除重复行。随INTERSECT 一起使用时
注：使用运算词的几个查询结果行必须是一致的
。
12、说明：使用外连接
A、left （outer）join：
左
外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer）join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer）join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
12
、分组
:Group by:
一张表，一旦分组完成后，查询后只能得到组相关的信息。
组相关的信息：
（统计信息）count,sum,max,min,avg
分组的标准
)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；
13
、对数据库进行操作：
分离数据库
：sp_detach_db;附加数据库
：sp_attach_db 后接表明，附加需要完整的路径名
14.如何修改数据库的名称:
sp_renamedb ‘old_name‘, ‘new_name‘
二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：
select * into b from a where 1<>1（仅用于SQlServer）
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in ‘"&Server.MapPath(".")&"\data.mdb" &"‘ where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between
数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值
1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排where datediff(‘minute‘,f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段from 表名order by 排序字段desc) a,表名b where b.主键字段= a.主键字段order by a.排序字段
具体实现：
关于数据库分页：
declare @start int,@end int
@sqlnvarchar(600)
set @sql=’select top’+str(@[email protected]+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
selecta,b,cfrom tablename tawhere a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablenameorder by newid()
18、说明：随机选择记录
select newid()
19
、说明：删除重复记录
1),
delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
delete from tablename
insert into tablenameselect * from temp
评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作
3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段
alter table
tablename
--
添加一个自增列
addcolumn_b
int identity(1,1)
delete from
tablename
where column_b not in(
select
max(column_b)from tablenamegroup bycolumn1,column2,...
)
alter table
tablenamedrop column column_b
20、说明：列出数据库里所有的表名
select name from sysobjects where type=‘U‘ // U代表用户
21、说明：列出表里的所有的列名
select name from syscolumns where id=object_id(‘TableName‘)
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when ‘A‘ then pcs else 0 end),sum(case vender when ‘C‘ then pcs else 0 end),sum(case vender when ‘B‘ then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑
A 1
电脑
A 1
光盘
B 2
光盘
A 2
手机
B 3
手机C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名order by id desc
三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1”是表示选择全部“where 1=2”全部不选，
如：
if @strWhere !=‘‘
begin
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where ‘ + @strWhere
end
else
begin
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]‘
end
我们可以直接写成
错误！未找到目录项。
set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where 1=1 安定‘+ @strWhere 2、收缩数据库
--
重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--
收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘
go
5、检查备份集
RESTORE VERIFYONLY from disk=‘E:\dvbbs.bak‘
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(‘dvbbs‘,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename --
要操作的数据库名
SELECT @LogicalFileName = ‘tablename_log‘, -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 --
你想设定的日志文件的大小(M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ‘MB‘
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = ‘BACKUP LOG ‘ + db_name() + ‘ WITH TRUNCATE_ONLY‘
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES (‘Fill Log‘) DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +
CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB‘
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner ‘tablename‘,‘dbo‘
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select ‘Name‘ = name,
‘Owner‘ = user_name(uid)
from sysobjects
where user_name(uid)[email protected]
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @[email protected]
begin
set @OwnerName = @OldOwner + ‘.‘ + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @[email protected]+1
end
案例
：
有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:
Namescore
Zhangshan80
Lishi 59
Wangwu 50
Songquan69
while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if(select min(score) from tb_table)>60
break
else
continue
end
数据开发-经典
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多
2.数据库加密:
select encrypt(‘
原始密码
‘)
select pwdencrypt(‘
原始密码
‘)
select pwdcompare(‘
原始密码‘,‘加密后密码‘) = 1--相同；否则不相同encrypt(‘原始密码
‘)
select pwdencrypt(‘
原始密码
‘)
select pwdcompare(‘
原始密码‘,‘加密后密码‘) = 1--相同；否则不相同
3.取回表中字段:
declare @list varchar(1000),
@sql nvarchar(1000)
select @[email protected]+
‘,‘+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=‘
表A‘
set @sql=‘select ‘+right(@list,len(@list)-1)+‘ from 表A
‘
exec (@sql)
4.查看硬盘分区:
EXEC master..xp_fixeddrives
5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print ‘
相等
‘
else
print ‘
不相等‘
6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ‘kill ‘+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN(‘SQL profiler‘,N‘SQL
事件探查器
‘)
EXEC sp_msforeach_worker ‘?
‘
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N
到M条记录(要有主索引
ID)
Select Top M-N * From
表Where ID in (Select Top M ID From 表
) Order by ID Desc
----------------------------------
N
到结尾记录
Select Top N * From 表Order by ID Desc
案例
例如1：一张表有一万多条记录，表的第一个字段RecID 是自增长字段，写一个SQL语句，找出表的第31到第40个记录。
select top 10 recid from A where recid notin(select top 30 recid from A)
分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。
select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。
解决方案
1，用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题
2，在那个子查询中也加条件：select top 30 recid from A where recid>-1
例2：查询表中的最后以条记录，并不知道这个表共有多少数据,
以及表结构。
set @s= ‘select top 1 * from Twhere pid not in (select top ‘+str(@count-1) + ‘ pidfrom T)‘
print @[email protected]
9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype=‘u‘ and status>=0
10：获取某一个表的所有字段
select name from syscolumnswhere id=object_id(‘表名‘)
select name from syscolumnswhere id in (select id from sysobjectswhere type = ‘u‘ and name = ‘表名‘)
两种方式的效果相同
11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjectsa, syscommentsb where a.id = b.id and b.text like ‘%表名%‘
12：查看当前数据库中所有存储过程
select name as 存储过程名称from sysobjectswhere xtype=‘P‘
13：查询用户创建的所有数据库
select * from master..sysdatabasesD where sid not in(select sid from master..sysloginswhere name=‘sa‘)
或者
select dbid, name AS DB_NAME from master..s
ysdatabaseswhere sid <> 0x01
14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ‘
表名‘
15：不同服务器数据库之间的数据操作
--创建链接服务器
exec sp_addlinkedserver‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘远程服务器名或ip地址‘
exec sp_addlinkedsrvlogin‘ITSV ‘, ‘false ‘,null, ‘用户名‘, ‘密码‘
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_dropserver‘ITSV ‘, ‘droplogins ‘
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名‘; ‘用户名‘; ‘密码‘,数据库名.dbo.表名)
--生成本地表
select * into 表from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名‘; ‘用户名‘; ‘密码‘,数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( ‘SQLOLEDB ‘, ‘sql服务器名‘; ‘用户名‘; ‘密码‘,数据库名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名‘; ‘用户名‘; ‘密码‘,数据库名.dbo.表名)as a inner join 本地表b
on a.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
exec sp_addlinkedserver‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘远程服务器名或ip地址‘
--查询
select *
FROM openquery(ITSV,‘SELECT *FROM 数据库.dbo.表名‘)
--把本地表导入远程表
insert openquery(ITSV,‘SELECT *FROM 数据库.dbo.表名‘)
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV,‘SELECT * FROM 数据库.dbo.表名‘) as a
inner join 本地表b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT*
FROMopendatasource( ‘SQLOLEDB ‘,‘Data Source=ip/ServerName;User ID=登陆名;Password=密码‘ ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( ‘SQLOLEDB ‘,‘Data Source=ip/ServerName;User ID=登陆名;Password=密码‘).数据库.dbo.表名
select * from 本地表
SQL Server基本函数
SQL Server基本函数
1.字符串函数长度与分析用
1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度
3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反
4,isnull( check_expression ,replacement_value )字符操作类如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression
5,Sp_addtype自定義數據類型
例如：EXEC sp_addtype birthday, datetime, ‘NULL‘
6,set nocount {on|off}
使返回的结果中不包含有关受Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。
SET NOCOUNT 为ON 时，不返回计数（表示受Transact-SQL 语句影响的行数）。
SET NOCOUNT 为OFF 时，返回计数
常识
在SQL查询中：from后最多可以跟多少张表或视图：256
在SQL语句中出现 Order by,查询时，先排序，后取
在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。
SQLServer2000同步复制技术实现步骤
一、预备工作
1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户
--管理工具
--计算机管理
--用户和组
--右键用户
--新建用户
--建立一个隶属于administrator组的登陆windows的用户（SynUser）
2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
我的电脑--D:\ 新建一个目录,名为: PUB
--右键这个新建的目录
--属性--共享
--选择"共享该文件夹"
--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限
--确定
3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
开始--程序--管理工具--服务
--右键SQLSERVERAGENT
--属性--登陆--选择"此账户"
--输入或者选择第一步中创建的windows登录用户名（SynUser）
--"密码"中输入该用户的密码
4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
企业管理器
--右键SQL实例--属性
--安全性--身份验证
--选择"SQL Server 和Windows"
--确定
5.在发布服务器和订阅服务器上互相注册
企业管理器
--右键SQL Server组
--新建SQL Server注册...
--下一步--可用的服务器中,输入你要注册的远程服务器名--添加
--下一步--连接使用,选择第二个"SQL Server身份验证"
--下一步--输入用户名和密码（SynUser）
--下一步--选择SQL Server组,也可以创建一个新组
--下一步--完成
6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）
(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
开始--程序--Microsoft SQL Server--客户端网络实用工具
--别名--添加
--网络库选择"tcp/ip"--服务器别名输入SQL服务器名
--连接参数--服务器名称中输入SQL服务器ip地址
--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号
二、正式配置
1、配置发布服务器
打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:
(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导
(2) [下一步] 选择分发服务器可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）
(3) [下一步] 设置快照文件夹
采用默认\\servername\Pub
(4) [下一步] 自定义配置
可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置
否,使用下列默认设置（推荐）
(5) [下一步] 设置分发数据库名称和位置采用默认值
(6) [下一步] 启用发布服务器选择作为发布的服务器
(7) [下一步] 选择需要发布的数据库和发布类型
(8) [下一步] 选择注册订阅服务器
(9) [下一步] 完成配置
2、创建出版物
发布服务器B、C、D上
(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令
(2)选择要创建出版物的数据库，然后单击[创建发布]
(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)
(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,
SQLSERVER允许在不同的数据库如orACLE或ACCESS之间进行数据复制。
但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
注意: 如果前面选择了事务发布则再这一步中只能选择带有主键的表
(6)选择发布名称和描述
(7)自定义发布属性向导提供的选择:
是我将自定义数据筛选,启用匿名订阅和或其他自定义属性
否根据指定方式创建发布（建议采用自定义的方式）
(8)[下一步] 选择筛选发布的方式
(9)[下一步] 可以选择是否允许匿名订阅
1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器
方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加
否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅
如果仍然需要匿名订阅则用以下解决办法
[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅
2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示
(10)[下一步] 设置快照代理程序调度
(11)[下一步] 完成配置
当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库
有数据
srv1.库名..author有字段:id,name,phone,
srv2.库名..author有字段:id,name,telphone,adress
要求：
srv1.库名..author增加记录则srv1.库名..author记录增加
srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新
--*/
--大致的处理步骤
--1.在srv1 上创建连接服务器,以便在srv1 中操作srv2,实现同步
exec sp_addlinkedserver ‘srv2‘,‘‘,‘SQLOLEDB‘,‘srv2的sql实例名或ip‘
exec sp_addlinkedsrvlogin ‘srv2‘,‘false‘,null,‘用户名‘,‘密码‘
go
--2.在srv1 和srv2 这两台电脑中,启动msdtc(分布式事务处理服务),并且设置为自动启动
。我的电脑--控制面板--管理工具--服务--右键Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动
go
--然后创建一个作业定时调用上面的同步处理存储过程就行了
企业管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句: exec p_process
--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行
设置方法:
我的电脑--控制面板--管理工具--服务--右键SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.实现同步处理的方法2,定时同步
--在srv1中创建如下的同步处理存储过程
create proc p_process
as
--更新修改过的数据
update b set name=i.name,telphone=i.telphone
from srv2.库名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)
--插入新增的数据
insert srv2.库名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.库名.dbo.author where id=i.id)
--删除已经删除的数据(如果需要的话)
delete b
from srv2.库名.dbo.author b
where not exists(
select * from author where id=b.id)
go

sql过滤、删除、表中重复记录语句

文章介绍利用having group by 及各种select in 联合查询来实现的多种不同方法的过滤重复记录的sql语名。

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
代码如下复制代码
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
代码如下复制代码
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重复记录（多个字段）
代码如下复制代码
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
代码如下复制代码
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
代码如下复制代码
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
代码如下复制代码
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
代码如下复制代码
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
(三)
方法一
代码如下复制代码
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
方法二
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
代码如下复制代码
select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
代码如下复制代码
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
　　发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
代码如下复制代码
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
(四)查询重复
代码如下复制代码
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1
)

学习 SQL 语句 - Select(3) 条件查询与模糊查询

Where 用来指定查询条件;

Like 和 Not Like 来指定模糊条件;

模糊条件中:
_ 表示任一字符;
% 表示任一字符串;
[] 表示一个集合.

本例效果图:

代码文件:

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ExtCtrls, Grids, DBGrids, DB, ADODB;

type
TForm1 = class(TForm)
DBGrid1: TDBGrid;
DataSource1: TDataSource;
ADODataSet1: TADODataSet;
Panel1: TPanel;
Button1: TButton;
Button2: TButton;
Button3: TButton;
Button4: TButton;
Button5: TButton;
Button6: TButton;
Button7: TButton;
Button8: TButton;
Button9: TButton;
Button10: TButton;
Button11: TButton;
Button12: TButton;
Button13: TButton;
Button14: TButton;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
procedure Button7Click(Sender: TObject);
procedure Button8Click(Sender: TObject);
procedure Button9Click(Sender: TObject);
procedure Button10Click(Sender: TObject);
procedure Button11Click(Sender: TObject);
procedure Button12Click(Sender: TObject);
procedure Button13Click(Sender: TObject);
procedure Button14Click(Sender: TObject);
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

//country 表中 Area<200000 的记录
procedure TForm1.Button1Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Area<200000‘;
Open;
end;
end;

//country 表中 Continent="South America" 的记录; 字符串值应该在引号中(单引号、双引号均可).
procedure TForm1.Button2Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Continent="South America"‘;
Open;
end;
end;

//country 表中 Name="Cuba" 或者 Name="Peru" 的记录
procedure TForm1.Button3Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name="Cuba" or Name="Peru"‘;
Open;
end;
end;

//country 表中 Continent="South America" 并且 Area>1000000 的记录
procedure TForm1.Button4Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Continent="South America" and Area>1000000‘;
Open;
end;
end;

//country 表中 Name 是 c 开头的记录; 其中的 % 表示任意字符串
procedure TForm1.Button5Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "c%"‘;
Open;
end;
end;

//country 表中 Name 是 b 或 c 开头的记录; 可以用 "," 隔开更多条件
procedure TForm1.Button6Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "[b,c]%"‘;
Open;
end;
end;

//country 表中 Name 是 a 或 b 或 c 开头的记录
procedure TForm1.Button7Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "[a-c]%"‘;
Open;
end;
end;

//country 表中 Name 不是 a 或 b 或 c 或 m 开头的记录
procedure TForm1.Button8Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name NOT LIKE "[a-c,m]%"‘;
Open;
end;
end;

//country 表中 Name 不是 a 或 b 或 c 开头的记录
procedure TForm1.Button9Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name NOT LIKE "[a-c]%"‘;
Open;
end;
end;

//country 表中 Name 包含 er 的记录
procedure TForm1.Button10Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "%er%"‘;
Open;
end;
end;

//country 表中 Name 包含空格的记录
procedure TForm1.Button11Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "% %"‘;
Open;
end;
end;

//country 表中 Name 第二个字符任意, 但第一字符是 p、第三字符是 r 的记录; "_" 表示任意字符
procedure TForm1.Button12Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "p_r%"‘;
Open;
end;
end;

//country 表中 Name 是 4 个字符的记录
procedure TForm1.Button13Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "____"‘;
Open;
end;
end;

//country 表中 Name 是 4 个字符, 但最后是 a 结尾的记录
procedure TForm1.Button14Click(Sender: TObject);
begin
with ADODataSet1 do begin
Close;
CommandText := ‘SELECT * FROM country WHERE Name LIKE "___a"‘;
Open;
end;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
mdbFile: string;
begin
mdbFile := GetEnvironmentVariable(‘COMMONPROGRAMFILES‘);
mdbFile := mdbFile + ‘\CodeGear Shared\Data\dbdemos.mdb‘;

ADODataSet1.ConnectionString := ‘Provider=Microsoft.Jet.OLEDB.4.0;Data Source=‘ +
mdbFile + ‘;Persist Security Info=False‘;

DBGrid1.DataSource := DataSource1;
DataSource1.DataSet := ADODataSet1;
end;

end.

View Code

怎样用SQL语句查询一个数据库中的所有表

SQL  :  select * from information_schema.tables
ORACLE: select table_name from user_tables 
ACCESS: select    name    from    MSysObjects    where    type=1    and    flags=0

上一篇：Delphi ADOQuery
下一篇：delphi 文件分割与合并

网友评论

相关栏目

Delphi 与SQL编程

相关文章