跟我学SQL:(一)数据查询 且不说你是否正在从事编程方面的工作或者不打算学习SQL,可事实上几乎每一位开发者最终都会遭遇它。你多半还用不着负责创建和维持某个数据库,但你怎么着也该知道以下的一些有关的SQL知识。 SQL数据库是怎么回事? 假如到目前为止你头回遭遇SQL语言,那么你怎么也得先理解一些基本的SQL概念。我尽量把这些基本知识阐述得简明扼要,如果你对那些数据库术语还能忍受,你尽可跳到下一节,此外你还可以把自己的问题提交给以下的讨论区。 笼统地说,“SQL数据库”其实就是关系型数据库管理系统(RDMS)通俗的叫法。对某些系统来说,“数据库”也指一组数据表、数据以及相互区分但结构类似的配置信息。在这种情况下,每一SQL数据库的安装都可能由若干数据库组成。在有些系统上,这种数据库则指的是表空间。 数据表是一种包含多行数据的数据库构造,这种数据库构造由命名的列组成。通常数据表构造为包含关系信息,同一数据库或表空间以内可以创建若干数据表。 表内的列保存某一种类型的数据而且应根据其保存数据的内容得以命名。例如,被称为“LastName”的列就应该在每一行包含姓氏条目。正是这一前提的存在才能让关系数据库查询返回一致的结果。 字段(field)指的是某一行某一列对应的数据(或保存数据的地方)。另外,数据集合(data set)则指的是多行多列的数据,而且数据集合通常说明你的数据库或数据表内的全部数据。结果集合(result set)就是从数据库查询返回的数据;它能够描述从单一字段到数据库内全部数据这一范围内的全部信息。 数据库查询就是发送给数据库的SQL指令,这些指令向数据库请求某种施加在数据集合或数据库上的功能。 现在我们就来看看基本的数据库查询,这些查询主要涉及到对数据库内数据的操作。在本文中,所有的例子都采用了标准SQL语言,而且这些基本功能可以转换为应用在几乎各种环境下。 数据查询类型 SQL语言中的数据查询分为4种基本类型: SELECT:这条语句要求数据库返回指定结果的数据集合;你可以用这一语句检索数据库中保存的信息。 用SELECT语句检索保存的信息 SELECT column1, column2 FROM table_name; 另外,使用通配符“*”可以从表中选出所有的列: SELECT * FROM table_name; 你要愿意自己编码分析以上返回的结果当然也没问题,不过你完全可以采用方便的WHERE子句限制返回的结果集合,该子句可以让你为选择数据定义某些条件。以下查询就会返回“column1”数值等于3的所以数据行: 除了“=”(等于)条件之外你还可以用到下列条件运算符: 表A SQL 条件语句 另外,你还可以联合WHERE语句使用BETWEEN、LIKE等比较运算符以及AND和OR这类逻辑运算符。注意,OR语句是包含性的的。以下有一个例子组合了以上这些概念: SELECT * FROM table_name WHERE ((Age < 18) AND (LastName BETWEEN ‘Anderson’ AND ‘Miller’)) OR Company LIKE ‘ 用自然语言来说,这条选择语句的含义是这样的:从数据表中选出年龄小于18岁而且姓氏在“Anderson”和“MIller”之间的或者其公司名称类中有“School”字样的数据行。 用INSERT语句加入新数据 插入语句的语法如下: 如果你想按照表内现有列的同一顺序插入所有的值,那么你不必指定列名,当然,从可读性考虑最好不要这样做。另外,如果你列出列名则不必要按照它们在数据库中出现的顺序包括它们,只要你列出的值与它们一一对应即可。有些列你并没有为其输入新的信息所以你自然没有必要列出它们来。 一旦数据库中有了数据要修改起来也与此很相似。 UPDATE语句和WHERE子句 你可以采用WHERE子句随意匹配任何一列,正在修改的一列都可以。这样会有助于你把某一特定的值修改为另一个值: UPDATE table_name SET FirstName = ‘Shelley’ 一旦你数据库中删除某一行数据就不可再后悔了,因此一般来说,最好在数据表中包括一名为“IsActive”的列或类似的指示信息,这样你就可以把该列数据设置为零表示数据禁用。只有在你确信不再需要受到影响的信息之后你才可以用DELETE语句。 小结 跟我学SQL:(二) SELECT语句选项 跟我学SQL分为三部分,其中涵盖了有关SQL标准的基本知识。在上一篇文章里我们讨论了一些数据库术语和4种最基本的数据查询类型。此外,我们还解释了WHERE子句和条件语句的用法,同时我们提供了各类查询的具体示例。 SELECT选项精制结果 子句是一种修改结果的语句。子句不是必要的语句但它对数据的内容及其显示进行了提炼。WHERE子句就是这样的子句。 关键词触发数据库的内在功能。这些关键词在有时甚至是查询所必需的。例如“INSERT INTO table_name (column1) VALUES (‘data1’);”语句中的INTO和VALUE就是如此。我们将了解DISTINCT关键词,它能触发一些非常有用的可选功能。 下面总结了一些最常用的子句、关键词和函数。然后我会对每一部分举例说明。 ORDER BY – 按照指定列排序返回结果的子句 SELECT * FROM Contacts ORDER BY first_name; 你可以随意在任何选择语句中使用ORDER BY 子句返回多列结果。你还可以用它连接其他子句: 你可以对多列数据排序。优先顺序按从左到右依次降低,所以查询语句中各列的排列顺序很重要。 查询结果默认按数字或者字母的升序排序。你可以在ORDER BY 子句后面加上DESC关键词改成降序排列。在下面的例子中,最高的net_amount排在最先(降序)。假如两行或者两行以上数据都包含了同样的net_amount值,那么同行中last_name值在字母表中最先出现的排先,因为last_name一列还是按照升序排序的。 在按照定义的列名排序以后,大多数数据库随后将按照数据表内的第一列排序然后顺序向右再排序。具体的实现各有变化,因此,如果排序在应用中比较重要那么你应该明确地定义所要排序的列。 另外一值得注意的问题是,采用ORDER BY子句(以及WHERE子句),你正在用来排序结果的数据列并不一定得是返回结果集合的一部分。只要所有引用的列都在数据表内存在则下例完全有效: SELECT company, first_name, net_amount FROM Sales ORDER BY start_date, last_name; DISTINCT返回不重复结果 SELECT DISTINCT company FROM Sales; 在使用DISTINCT时,它适用于所有的请求列。如果你打算列出表内的所有销售人员和他们所代表的公司而非每一销售记录,那么你可以使用下列语句。注意,这样操作还可能返回同一公司的若干条目等等。 SELECT DISTINCT company, last_name, first_name FROM Sales; 你还可以在对结果缩小范围和进行排序时结合SELECT语句使用DISTINCT。为了确定显示的内容,数据库首先会证实精练的请求是否匹配数据行,然后应用DISTINCT功能。在全部结果集合都得以确定之后即处理ORDER BY子句。如下例所示,只有net_amount大于100的数据行才被返回。由于DISTINCT保留遇见的第1个匹配查询条件的数据行而丢弃其他匹配行,所以ORDER BY语句所引用的net_amount看起来就好象产生了随机的结果。 SELECT DISTINCT company, last_name, first_name FROM Sales WHERE net_amount > 100 ORDER BY company, net_amount; $keyname = “COUNT(*)”; COUNT 你也可以用它来计算任何结果集合中的行数。 SELECT COUNT(*) FROM Sales WHERE net_amount > 100; 如果你想看看某特定列有多少行包含非空值,那你不妨对该列使用COUNT函数。注意,除非数据库设置为字段为空时缺省填充NULL否则将返回表内数据行的总数。另外,列出的列在超出一个的情况下会引起错误。 SELECT COUNT(company) FROM Sales; COUNT语句通常用在程序中确定FOR循环的循环次数。 你可以结合子句限制该函数的应用范围。 SELECT AVG(net_amount) FROM Sales WHERE company LIKE ‘%ABCD Co%’; 就象所有聚集函数一样,ORDER BY语句将被忽略。 SUM SUM的工作方式和AVG差不多,只不过该函数返回结果集合中所有字段值的和。 AVG、SUM、MIN和MAX函数在没有指定列的情况下都会返回错误,所以你不能使用“*”通配符。 MIN MAX MAX的工作方式和MIN函数一样,只不过该函数返回最大的非空值。该函数也可以用于字符串或者数字列 MAX函数有时还用在包含自动递增键字段的列上确定下一条目的键ID。除非你正在运行一个非公开的数据库,否则在使用这一信息插入下一条目时务必谨慎,以防其他用户先你执行数据操作。 SELECT company, MAX(net_amount) FROM Sales GROUP BY company; 这样做可以获得每家公司net_amount的的最大值。在选择多列名的时候也可以采用该语句,你还可以用多列来对函数结果分组。 下面的例子演示了以上各种方式。首先,包括GROUP BY子句可以令你指定要显示的其他列。然而,你得知道这个例子将返回在组中遇到的第1个last_name值;Sum( net_amount )将显示全部公司的结果而不仅仅针对匹配姓氏的数据行。这是因为,我们只使用了Company字段来定义我们的组。 SELECT company, last_name, SUM(net_amount) FROM Sales GROUP BY company; 在上面的例子中,last_name列实际上并没有提供什么有用的信息,但这样做是为了在下一个例子中要用到的功能做准备。你可以创建多列定义的组。这样就可以在结果集合中产生针对特定行的函数结果,而结果集合则是由所有指定的GROUP BY列联合起来创建的: SELECT company, AVG(net_amount), last_name FROM Sales GROUP BY company, last_name; 上面的例子给每家公司中每一姓氏给出了平均的net_amount。你列出GROUP BY列的顺序控制着结果的排序,但是实际的函数值结果是一样的。 下面的例子表明如何组织结果而不显示分组的列。在有些场合这样做是很有用的,例如,如果要显示个人的销售量但却不显示姓名就能用上下面的例子了: SELECT company, COUNT(sale_id) FROM Sales GROUP BY company, last_name; 限制使用GROUP BY的查询 SELECT company, AVG(net_amount), FROM Sales WHERE net_amount > 100 GROUP BY company; 上面的例子只对那些满足WHERE限制条件的数据行适用AVG函数。注意,WHERE子句必须放在GROUP BY子句之前。你还可以用HAVING语句对分组计算之后限制返回的结果集合。 SELECT company, AVG(net_amount), FROM Sales WHERE last_name BETWEEN ‘a’ AND ‘m’ GROUP BY company HAVING AVG(net_amount) > 500; 上面的语句计算每家公司net_amount的平均值,而且只计算那些姓氏满足限制条件的销售人员的销售量,同时只显示大于500的结果。 跟我学SQL:(三)使用SQL子选择来合并查询 你是否曾经为了得到所需要的信息而反复查询?子选择,也被称为子查询,也许正是你在寻找的。SQL的这项功能使你可以在一组结果中查询,创造性地给结果组加以限定,或是在向数据库的单一调用中将结果与一个无关系的查询做相关。这篇文章中我将给出几个子选择的例子并就何时使用他们进行讨论。 在一个结果组中搜索 使用这个功能的一个途径是对两个表格中的可比数据专栏中的值进行定位。例如,我的一个数据库有两个表格,Album和Lyric。我可以很容易地通过下面的子查询声明来找到每一个Metallica的歌曲中包含“justice”的歌名: SELECT song_name FROM Album 这个例子是很简单的,我从Album表格中选择了所有Metallica的歌曲,接着,我在lyric表格中选择所有包含“justice”的歌曲,最后,我使用IN关键字来从Lyric表格结果组中显示的Album表格中返回歌曲名称。 我使用Lyric表格结果组来给Album表格中的结果做限定。WHERE子句中的子选择部分是完全自包含的,因此我不需要使用例如Album.song_name和Lyric.song_name等完整的专栏名称。我没有从最终结果组的Lyric表格中返回任何值,如果我需要歌曲的Lyric,我会使用一个JOIN声明。 使用NOT IN排除结果 SELECT song_name FROM Album 在前面的SQL代码中,我选择了Metallica的“And Justice for All,”专辑中的所有歌曲,接着是带有歌词中带有“justice”所有歌曲,最后从在Lyric结果组中没有出现的Album结果组返回了所有歌曲。较之于返回两个查询并使用代码来比较数组,你通过一个单独的声明就可以得到确切的结果。 使用EXISTS来相关结果 SELECT Album.song_name FROM Album 在SQL代码中,我使用完整的专栏名称,这是因为我直接对两个表格作比较,而不仅仅是将结果组作为一个被动资源来使用。我并不从Cover表格中返回结果。一些数据库支持NOT EXISTS关键字来确保你并没有匹配。 使用合计函数来比较 SELECT AlbumInfo.album_name FROM AlbumInfo 现在我已经成功地返回了所有Metallica的专辑中,应有的曲目数量与Album表格中实际的歌曲条目数量不符的专辑名称。 返回子选择结果 SELECT AlbumInfo.album_name, album_tracks, 另一个强有力的例子涉及了在AlbumInfo表格中将album_tracks值改变为在Album表格中实际的条目数量: UPDATE AlbumInfo SET album_tracks = 在上两个例子中的子选择声明被看作一个自包含单位来执行。 子选择比较关键字(ALL, SOME, ANY) SELECT * FROM AlbumSales 上面的例子将从AlbumSales表格返回在AlbumProduction表格里面付出总额大于成本而生产最昂贵的专辑的所有记录。如果用ANY替代ALL,声明将返回所有付出总额大于最低专辑成本的专辑记录。声明= ANY与IN关键字意义是相同的。声明<> ALL与NOT IN关键字是对等的。关键字ANY和SOME也是等同的。数据库生产商中对这些关键字的支持情况是不同的,因此在出现问题时要相信查阅生产商方面的资料。 谁对标准化数据结构有疑问? 跟我学SQL:(四)查询多个表格 在对跨多个表格的数据进行组合时,有时很难搞清楚要使用哪一个SQL句法。我将在这里对将多个表格中的查询合并至单一声明中的常用方式进行阐述。 在这篇文章中的样本查询符合SQL92 ISO标准。不是所有的数据库生产商都遵循这项标准,而且很多厂商采取的提升措施会带来一些意料不到的后果。如果你不确定你的数据库是不是支持这些标准,你可以参看生产厂商的有关资料。 SELECT 一个简单的SELECT声明就是查询多个表格的最基本的方式。你可以在FROM子句中调用多个表格来组合来自多个表格的结果。这里是一个它如何工作的实例: SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1; 这个实例中,我使用点号(table1.column1)来指定专栏来自哪一个表格。如果所涉及的专栏只在一个参考的表格中出现,你就不需要加入完整的名称,但是加入完整名称会对可读性起到帮助。 在FROM子句中表格之间由逗号来分隔,你可以加入所需的任意多的表格,尽管一些数据库有一个在引入正式的JOIN声明之前他们可以有效地处理的内容这方面的限制,这个将在下面谈到。 这个句法是一个简单的INNER JOIN。一些数据库将它看成与一个外部的JOIN是等同的。WHERE子句告知数据库哪一个区域要做关联,而且它返回结果时,就像列出的表格在给定的条件下组合成一个单独的表格一样。值得注意的是,你的比较条件并不需要与你作为结果组返回的专栏相同。在上面的例子中,table1.column1和table2.column1用来组合表格,但是返回的却是table2.column2。 你可以在WHERE子句中使用AND关键字来将这个功能扩展至多于两个的表格。你还可以使用这样的表格组合来限制你的结果而不用实际地从每个表格返回专栏。在下面的例子中,table3与table1匹配,但是我没有从table3返回任何东西来显示。我只是确保来自table1的有关专栏存在于table3之中。注意此例中table3需要在FROM子句中被引用。 SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1; 然而,要注意的是,这个查询多个表格的方式是一个暗指的JOIN。你的数据库可能对事物进行不同的处理,这取决于它所使用的优化引擎。而且,忽略对与WHERE子句的相关特性的定义将会给你带来不愿看到的结果,例如从余下的查询中返回与每一个可能的结果相关的专栏的rogue域,就像在CROSS JOIN之中一样。 如果你习惯于你的数据库处理这种类型的声明的方式,且你只对两个或是少数几个表格进行组合,一个简单的SELECT声明就可以达到目的。 JOIN的类型有几种:LEFT,RIGHT,FULL OUTER,INNER和CROSS。你所使用的类型是由你想要看到的结果所决定的。例如,使用LEFT OUTER JOIN将会从列出的第一个表格中返回所有有关的行,而同时如果没有信息与第一个表格相关的话将潜在地从所列出的第二个表格中加入行。 在这里INNER JOIN和暗含的JOIN是不同的,INNER JOIN将只返回那些在两个表格中都有数据的行。 对第一个SELECT查询使用如下JOIN声明: SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 子查询 下面的例子中使用了两个表格。一个表格中包含了我想要返回的数据,而另一个表格则给出一个比较点来确定什么数据是我确实感兴趣的。 SELECT column1 FROM table1 WHERE EXISTS ( SELECT column1 FROM table2 WHERE table1.column1 = table2.column1 ); 子查询很重要的一个方面就是性能表现。便利性是有代价的,它取决于你所使用的表格和声明的大小,数量和复杂性,还有你可能会允许你的应用软件做处理工作。每一个查询在被主查询作为资源使用之前,都将被完整地单独处理。如果可能的话,创造性地使用JOIN声明可以以较少的滞后时间提供出相同的信息。 JOIN声明和子查询 跟我学SQL:(五)创建和修改表格 再次欢迎您来到SQL(结构化查询语言)基础系列教程。本文将介绍数据库定义语言(DDL)用于创建数据库和表格以及修改表格结果的指令。 当你使用这些指令时一定要小心——它很容易删去你的数据库中的主要结构令您丢失数据。所以,在您开始修改数据库之前,您需要知道数据库是什么。 数据库之间的差异 创建数据库 你的数据库用户必须有建立数据库的适当权限。如果与你有关的用户不能发出用于创建新数据库的命令,要求数据库管理员为你建立数据库,你也作为管理员登录然后建立数据库并设置权限。 举个例子,用CREATE指令为一个应用程序建立一个数据库用于显示一个目录: 这给你一个用于在查询时与其它表格区分的表格名字。下一步是创建用于输入它的表格。 创建表格 你可以用下面这条指令来创建数据库,命令行的参数为表格名字、栏目名字,还有每一栏的数据类型。 不同的数据库提供商的标准差别很大。你的帮助文档中应该有一段详细说明如何使用每一种数据、接受何种参数。为了通用,我在表A中列出了一些常用的数据类型。 表A 在本例中,我们建立了一个存放库存商品信息的表格。所用到的栏目和数据类型如表B所示: 表B 发出如下指令来建立表格: 如果这些指令顺利完成,你就可以在表格中正常地插入信息。你可以参到文章SQL基础一:数据查询"得到详细说明。 除了数据类型,你还可以在创建表格时定义自动增量字段(auto-incremented field)、关键字、索引和特殊数值限制。在表格定义时,这些参数与数据类型一同传递。如果在创建表格Product时定义具有特殊数值限制的自动增量prod_id,命令如下: 如果把prod_id做为索引字段定义,可以用CREATE INDEX: 这里有必要重申:数据库提供商在关键字的处理上有所不同。所以,具体情况请参考你的数据库提供商的文档。 关于索引的更多内容: 索引是一个比较深的课题。除了介绍有关关键字和索引的理论,Builder.com的供稿人Eric Roland写了几篇很好的文章,你可以通过它们来学到更多的相关知识。 修改表格 好,现在让我们看看如何修改表格。首先,在表格Product中加入一栏。你可以指定该栏插入的相对其它栏的位置,也可以让它插到表格末端(默认): 用类似的语句删除一个栏目: 最后,更改一个栏目的数据类型: 现在,你的表格如表C所示: 表C 删除表格和数据库 在删除表格或整个栏目之前,你必须清楚数据库的结构。如果你进入一个已经存在的数据库并错误删除了某个元素,可能会影响到促发条件(?trigger)、存储过程和视图。有些数据库支持用关键字RESTRICT和CASCADE去预防由于删除表格带来的损失。RESTRICT一般按默认设置,预防丢失表格,而CASCADE用于删除与该表格有关的实体。 现在上面建立的表格Product是可以被删除的,我们开始删除它: 现在删除数据库: 部分数据库提供了FLUSH命令,该命令可以让你删除表格中的内容但又可以保持表格的结果,: 如你所见,删除数据库中的主要结构并丢失所存的所有数据的容易程度令人难以想象,所以,一定要小心使用这些命令,而当你不清楚数据库中的内容时,就不要使用这些命令。 跟我学SQL:(六)串行数据类型 SQL的数据类型决定了一个字段的内容在数据库中会被如何处理、存储和显示。SQL92定义了标准的数据类型,目的是给数据库制造商建立自己的数据类型提供蓝图。在前面的文章中,我们介绍了一些常用的数据类型,这些数据类型分为四大类: 使用数据类型 CREATE TABLE Products 在以上的查询中,定义行prod_color VARCHAR(20)发出指令要创建一个列,名字是prod_color,数据类型是VARCHAR,长度为20。 你的数据库使用和每个类型相关的描述符来区别数据类型。例如,VARCHAR数据类型的描述符所含的信息将它区别为串行数据型,它包含所有的串字符,其长度是可变的。数据库里列的定义还包含了其他信息,例如对应于数据类型的特定长度。 如前所述,每个数据库制造商都希望在SQL92定义的标准上建立自己的数据类型。这样每个数据库在定义数据类型时都能够设定自己所需要的最大容量限制和其他属性。许多数据库使用的数据类型名字和这里列出来的一样,尽管每种的实现方法都有微小的差别。要确定特定数据类型使用方法的细节最好的方法还是查阅数据库制造商的文档。 已经说过了,希望对标准字符串数据类型有更多的了解就往下看。 串 CHARACTER | CHAR CHARACTER和CHAR这两个关键字是相同的。 CHARACTER VARYING,CHAR VARYING,和VARCHAR这几个关键字是相同的。 使用方法:BIT(blength) 这种类型包含了带有长度的位字符(1和0),blength。例如,如果我们使用BIT(2),样本值将为“01”。 使用方法: BIT VARYING(maxlength) 这种类型包含了最大长度的位字符,maxlength。 请把你的评论、问题或者回应发到下面的讨论栏,或者如果你有关于SQL基础系列的论题,可以发到我们编辑的信箱。 串理论 数据从一个数据库迁移到另一个数据库时,这种设计上的弹性产生了一个必须克服的障碍。在一个数据库里,你可能会有一个叫做CHAR的类型,这个类型所允许的最大容量大于你要迁移到的数据库的最大容量。而且,(SQL92)标准中没有明确定义的类型可能会变化较大,这样的话只用遵从惯例来简化迁移。 在ZDNet China最近的文章《BLOB移植的替换方案》中讨论了存在数据类型移植问题时保护数据的一个可能的解决方案。SQL标准没要包括存储二进制数据的指标,这造成了不同数据库制造商产品间的不兼容。软件开发者必须找到提到方案列清除这些障碍。 在这个系列的下一篇文章中,我们会看看SQL92的数字数据类型,每个有什么特点,以及对要实现这些类型的数据库的要求 跟我学SQL:(七)从子表里删除数据 在这篇文章里我要描述一下如何从表格里删除列,要删除的这些列同时还要依赖于其他表格的标准。要解决这个问题就需要一个很聪明而且完全遵守SQL92子查询声明的应用程序。 需要更多的背景信息? 宠物店的例子 在这个例子里,我们先假设商店把整窝Shitzu小狗都卖完了。我可以使用breed表格里的breed_id字段来删除Shitzu清单里的所有项目,就像这样: DELETE FROM inventory WHERE breed_id IN 首先,我要指定需要删除记录的表格,在这里是清单表格。然后再将识别字段breed_id同子选项子句的结果反复比对。我知道要找的是Shitzus,所以就能直接删掉他们,而不用再在单独的请求里查询breed_id。 我必须要警告你的是,以这种方式使用DELETE声明是危险的,只有在你对数据库的结构很熟悉的情况下才能使用这些声明。DELETE查询会从受影响的表格里删除掉全部列,你应该知道这对你所管理着的数据意味着什么。有个好办法是使用SELETE *这个短语替代DELETE关键字来对DELETE声明的子查询结果进行测试,这样就能保证结果里含有你要删除的所有东西,就像这样: SELECT * FROM inventory WHERE breed_id IN DELETE和JOIN联用 从多个表格里一次删除 DELETE的声明不能把多个表格作为一个参数接受。作为一个具有破坏性的查询,这能保证在命令要被执行的地方不会出现歧义。此外,这个限制防止了在单个声明内将AND和多个子查询联用。如果测试SELECT声明的结果用以检查DELETE查询将要影响到的是哪些数据,你会发现SELECT会返回多个表格的清单,DELETE不会影响到的多个子查询不在其中。 有很多可能的方法能够满足你的需求,例如在表格里创建一个字段,用以指明该项目是否为活动的。或者,你可以使用一些数据库里的预存程序在每个所需的DELETE查询里迭代。 跟我学SQL:(八)数值数据类型 SQL92标准定义了若干种基本数据类型,它们是SQL数据库中各种数据类型的基础。在《字符串数据类型》一文中,我们已经详细讨论了SQL92标准所定义的字符串数据类型。现在,我们来进一步讨论数值数据类型。 在字符串、数值、datetime和interval这四种数据类型中,数值型的种类最多,约束也最多。在不同数据库实现方法之间交换数据时,数值型的精度也最容易降低。Oracle和SQL服务器之间的实现分歧(同样的数据类型长度不同)导致它们之间的数据传递过程会截短数字、改变它们的数值。因此,在移植程序前,你有必须很明确的了解两个平台间的数据定义差异,以及危及数据精度的风险。 谨记上述警告后,让我们看看SQL92标准的数值类型 基本数值类型 每一个数据库实现方法都有关于如何近似数值或者截短数值的规则。除了提供获取数值长度和其它数值处理所需的属性外,SQL92提供了内建函数,如加、减、乘、除等。所有的数值类型之间都可以互相比较、互相赋值。尽管实现方法不同,但是它们有一个的共同点,即它们的结果一般都保留最大精度。 NUMERIC 用法:NUMERIC(精度,标度值) 是一种精确数值类型,即它是数字的值的文字表示。(可以对该数字进行取舍或者截取以符合指定精度,标度值由预定义的规则确定。) 是一种精确数值类型。 INTEGER | INT 是一种精确数值类型。 是一种精确数值类型。 是一种近似数值类型,即对一个指定的数值用指数形式表示出来,如1.23e-45(等于),该数值类型的取舍和截短方法大多由数据库提供商定义。 是一种近似数值类型。 是一种近似数值类型。 在我们的下一篇文章,我们将讨论datetime和interval数据类型。 跟我学SQL:(九)datetime和interval数据类型 datetime和interval是两种与时间有关的数据类型。它们的作用体现在以下几个方面:创建或者更改记录库中的某条记录、当某个时间发生时运行记录、或者计算某个datetime变量建立后所经历过的时间。本文将介绍SQL92标准对上述两种数据类型的描述。 SQL数据类型 如果你想进一步了解SQL数据类型,请阅读SQL基础:字符串型数据类型,这篇文章的范围覆盖了SQL中的通用数据类型并说明了各种字符串数据类型的用法,SQL基础:数字数据类型详细介绍了数字数据类型。 Datetime 实际上,datetime的实现形式随着定义它的标准不同而拥有不同的长度和格式;然而,各个公司定义的类型都内在地符合下述规则。举例来说,时标(timestamp)的某个实现可能没有分隔符,随着细节的规范不同,长度和格式也发生变化,在某些场合以空格做为间隔符。Datetime数据类型包括:DATA、TIME和TIMESTAMP。让我们仔细研究这些分类,首先我们看看DATA。 DATA DATA类型允许没有参数,如精度。 该类型包含了小时、分和秒,格式为hh:mm:ss(h表示小时、m表示分、s表示秒) TIME WITH TIME ZONE 用法:TIME (精度) WITH TIME ZONE 这个值要符合TIME数据类型 用法:TIMESTAMP(精度) 该类型包含有年、月、日、时、分、秒,格式为:YYYY-MM-DD hh:mm:ss.。 用法:TIMESTAMP(精度)WITH TIME ZONE 时标部分符合上述TIMESTAWP的规则。 Interval用于表示时间尺度。例如,你可以用操作符(将在下面进行解释)去计算两个日期间天数并加以保存。 各个公司在处理interval上有很大的不同——有些公司提供不同的度量单位,如年或者分钟,而有些公司在根本就不支持interval。SQL92标准的interval类型只提供一种子类型:INTERVAL。 INTERVAL 用法: INTERVAL (限定语) 有两种类型的interval:一种为“年份-月份”,即保存年份和月份(YYYY-MM);一种为“天-时间”(DD HH:MM:SS),用来保存天数、小时、分钟和秒。 操作结果类型 当处理日期时间时,时区保持不变——尽管有些数据库为了比较而将其中的一个时区转换为另一个。存在一些操作关键字,如OVERLAPS和EXTRACT,它们用于操作和比较datetime类型数据。然而,不同的数据库在这些操作关键字用法和支持方式上有着很大的不同。 OVERLAPS用于计算时间交叠的跨度,其操作对象可以是两个datetime也可以是一个datetime和一个interval。EXTRACT用于提取datetime或者interval类型数据的某个部分,如在DATA类型数据中提取月份。 数据类型 全面接触SQL语法(1) SQL功能与特性 通过SQL命令,程序设计师或数据库管理员(DBA)可以: SQL语法的分类 其实SQL命令并不是非常多,可是要把SQL用到出神入化,却也只需要短短几个命令便够,因为SQL命令是针对关系型数据库所建立出来的语法叙述,所以SQL在这类数据库中所发挥的功能非常的强,以下将针对在VB中常用的SQL语法基本命令加以分类介绍。在说明SQL的命令以及使用语法之前,以下将SQL做了的分类,在致上SQL语法所使用到的类型,可以说都已包含在这些类别当中。 第一类、属性词(Predicates) 在SQL命令中用来指明所要选择的记录的方式。如ALL、TOP与DISTINCT等等。 第二类、声明(Declaration) 针对SQL Parameter或Parameter Query 的名称与数据类型做声明,如PARAMETERS的声明等等。 第三类、条件子句(Clause) 在SQL的查询中,利用一些表达式定义出查询的条件,以缩小寻找的范围,如WHERE。 第四类、运算符(Operator)与操作数(Operation) 在SQL的查询中,与Operation共同组成表达式(Expression),如BETWEEN....AND 运算符与INNER JOIN操作数。 第五类、函数(Function) 一些SQL常见的函数,像是AVG()是求算数平均数的函数。 第六类、SQL语句(Statement) SQL的语句,可以说是SQL语法的主体,用来对某一个特定的数据库发出指示,并返回相关的数据,而SQL的语法结构,基本上可以利用下面的式子来表示:命令+条件子句 例如: SELECT*FROM TAB WHERE TAB.NAME='A' 其中的“FROM....WHERE”便是一个条件子句,其实SQL的语法并不难,您只需记住这样的一个规则,相信可以很快的了解SQL用法。 SQL语法与命令 SELECT 语句 * 从指定表格中指定所有的字段。 table 针对被选择出的记录的字段,所指定表格的名称。 field1,field2 想要读取数据的字段名称,如果包含了一个以上的字段,会依照列出的顺序来读取数据。 alias1,alias2 用来替代在表格实际字段名称的化名。 tableexpression 表格名称或包含我们所想要的数据的表格。 externaldatabase 若使用到不是目前的数据库则将其名字定义在externaldatabase当中。 SELECT [ALL|DISTINCT|DISTINCTROW|[TOP n[PERCENT]]] FROM table ALL 若是您不指定任何的字段数据,则Microsoft Jet数据库引擎(database engine)将会选择所有的字段,并依据所定的条件查询出需求数据集。 例如下面这两个例子将会具有相同的效果,都会从职员表格中返回所有字段的数据。 例如: 若是我们要查询出职员表格中的所有记录,可以通过下面的语句来完成。 SELECT ALL* FROM 职员表格; DISTINCT 对某个表格所选择的字段数据,略过重复的情况,也就是说,针对某个字段查询出来的记录结果是唯一的。例如有许多存放在职员表格的职员数据,也许会具有相同的姓名,所以若是我们用SQL语句中的SELECT DISTINCT,则查询出来的结果将会针对不一样的姓名加以筛选。若是您把 DISTINCT 加以省略,则这样的查询会显示所有的记录。 DISTINCTROW 将整条记录重复的记录忽略掉,而不是只有针对某一个字段的数据。 table 指定查询记录所需要的表格。 例如: SELECT DISTINCTROW 公司名称 FROM 顾客表格 INNER JOIN 订单表格 ON 顾客表格.顾客ID=订单表格.顾客ID ORDER BY 公司名称; 如果您忽略 DISTINCTROW 则会对每个公司产生一行以下的订单数据。此外,若是DISTINCTROW只有用在一个表格当中,则会被省略掉。 TOP 从第一条或最后一条开始(利用ORDER BY条件子句),返回特定条数的数据。 例如: 当您想要知道在2000年,班上前25名的学生姓名数据时,您可以输入这样的语句: SELECT TOP 25 学生姓名 FORM 学生表格 WHERE 毕业年份=1994 ORDER BY 毕业成绩平均分数 DESC; 如果您没有加上ORDER BY 这行条件的话,您所得到的数据,将会随机的数据。此外,在TOP语句之后,除了可以加上数字以外,还可以利用保留字PERCENT来查询。 例如: SELECT TOP 10 PERCENT学生姓名 FROM学生表格 WHERE毕业年份=1994 ORDER BY毕业成绩平均DESC; 对于参数型的查询语法中,对参数的名称以及数据类型作 声明的操作。 PARAMETERS name datatype[,name datatype[,...]] name PARAMETERS的名称。您可以把参数名称当作字符串来使用,若是名称中包含了空字符串,可以利用中括号来处理,例如:“VBeden”。 datatype 输入参数的数据类型。 例如: 若是您在查询时,需要机动的输入姓名 ,可以利用下列的方式完成: PARAMETERS “输入姓名” Text; SELECT* FROM 职员表格 WHERE姓名=“输入姓名:” ORDER BY条件语句 此条件子句,通常与SELECT语句合并使用目的是将查询的结果,依照指定字段加以排序。 SELECT fieldlist FROM table WHERE selectcriteria ORDER BY field[ASC|DESC][,field2[ASC|DESC][,...]] fieldlist 欲查询的字段名称。其中可以与ALL,DISTINCT,DISINCTROW,或TOP一起来使用。 table 欲查询的表格名称。 selectcriteria 查询的标准设置。 field1 指定要依照那个字段作为排序的依据,若是你没有加上ORDER BY查询出的数据集将不会作排序的操作。 ASC 递增顺序类别。(默认值) DESC 递减顺序类别。 例如: 或是我们要将输出数据依据出生的先后次序排列,可以利用下面的命令。 SELECT 姓名,生日 FROM 职员表格 ORDER BY 生日 SELECT LastName,FirstName FROM Employees ORDER BY LastName ASC; IN 条件子句 指定要速胜哪一个外部数据库的表格。(必须是Microsoft Jet数据库引擎所可以连接的数据库,如dBase,Paradox等等) SELECT|INSERT]INTO destination IN {path|["path" "type"]|[""[type;DATABASE=path]]} tableexpression 表格名称或是被读取数据的表格名称。这个参数可以是一个单一的表格名称,或是一段已经被存储的SQL查询等。 path 包含该表格的完整路径名称。 type 数据库的类型名称, 通常是当数据库部属于Jet database时才会使用。(例如:dBASE III,dBASE IV,Paradox 3.x,Paradox 4.x,或 Btrieve) 例如:下面这两段的意义相同 PartA....FROM Table IN ""[dBASE IV;DATABASE=C:\DBASE\DATA\SALES;]; PartB....FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;" 例如:Microsoft Jet database SELECT 顾客编号 FROM 顾客表格 IN CUSTOMER.MDB WHERE 顾客编号 Like "A*"; 例如:dBASE III or IV HAVING 条件子句 指定一特定的分组记录,并满足HAVING 所指定的条件或状态,但条件是针对分组的条件设置。 SELECT fieldlist FROM table WHERE selectcriteria GROUP BY groupfieldlist HAVING groupcriteria GROUP BY 条件子句 依据指定的字段,将具有相同数值的记录合并成一条。 SELECT fieldlist FROM table WHERE criteria GROUP BY groupfieldlist FROM 条件子句 指定表格名称或是查询,其中包含列在SELECT语句的字段数据。 SELECT fieldlist FROM tableexpression[IN externaldatabase] WHERE 条件子句 指定查询的条件与限制。 SELECT fieldlist FROM tableexpression WHERE criteria BETWEEN...AND 运算符 决定某一人数值是否介于特定的范围之内,此运算符只可以用在SQL的语句中。 expr[Not]BETWEEN value1 AND value2 LIKE 操作数 用来将一字符串与另一特定字符串样式(pattern)比较,并将符合该字符串样式的记录过滤出来。 expression LIKE "pattern" 7、指定非数字: "“!0 SQL数字函数 1、AVG:算数平均数 AVG(expr) Expr 字段名称或表达式。 例如: 若要计算职员身高超过 SELECT Avg(身高) AS 平均身高 FROM 职员表格 WHERE 身高>165; 2、COUNT:计算记录条数 COUNT(expr) expr 字段名称或表达式。 例如: 若是要统计出业务部门的职员人数,并查询出职员的姓名,可以利用下面的程序。 SELECT Count(姓名) AS 职员姓名 FROM 职员表格 WHERE 部门名称='业务部'; 3、FIRST与LAST:返回某字段的第一条数据与最后一条数据。 FIRST(expr) LAST(expr) expr 字段名称或表达式。 例如: 若是要找出货品数量字段的第一条数据与货品价格字段的最后一条数据时,可以利用下面的查询方式。 SELECT FIRST(货品数量),LAST(货品价格) FROM 订单表格 4、MAX,与MIN:返回某字段的最大值与最小值。 用法同FIRST与LAST。 5、SUM:返回某特定字段或是运算的总和数值。 SUM(expr) expr 字段名称或表达式。 例如: 要计算出货品总价,可使用下面的程序。 SELECT Sum(单位价格*货品数量) AS 货品总价 FROM 订单表格 多层SQL查询 顾名思义,多层的SQL查询的便在于:“在一个SQL语句中可以包含另一个SQL查询语句,形成内部嵌套的查询类型。” comparison[ANY|ALL|SOME](sqlstatement) 例如: 我们先从订单表格当中,查询出所有的单位,再将产品表格中的单位与的一一对比,查询出所有高于订单表格的单位价格的记录。 SELECT * FROM 产品表格 WHERE 单位价格>ANY (SELECT 单位价格 FROM 订单表格 WHERE 折扣>=.25); SQL与数据库的维护 将SQL中的基本语法作了一番介绍以后,但大多是偏向于数据库数据的查询与过滤,但实际上,我们通过SQL命令所可以做的事还有很多,接下来要介绍的便是如何利用SQL的语法命令来建立一个数据库中的表格。 CREATE TABLE语句 我们可以利用这个命令,来建立一个全新的表格,但前提则是:数据库必须已经存在。 CREATE TABLE table(field1 type[(size)][index1][,field2 type[(size)][index2][,...]][,nultifieldindex[,...]]) 例如: 建立一个拥有职员姓名与部门字段的表格。 CREATE TABLE 职员表格 (姓名TEST,部门TEST,职员编号 INTEGER CONSTRAINT职员字段索引PRIMARY KEY) 表格索引的建立 CREATE INDEX语句 这个命令主要是对一个已存在的表格建立索引,其用法如下: CREATE[UNIQUE]INDEX index ON table(field[ASC|DESC][,field[ASC|DESC],...])[WITH {PRIMARY|DISALLOWNULL|IGNORENULL}] table 欲建立索引的表格名称。 field 欲被建立的索引的字段名称。并可通过DESC保留字,来决定索引的顺序。 例如:在职员表格中建立一个索引。 表的删除 DELETE语句 我们可以利用DELETE语句,将表格中的记录删除。(注意:记录被删除后,无法再复原,所以条件设置要正确) DELETE[table.*] FROM tableexpression WHERE criteria 例如: 若是我们要将职员表格中姓名姓名叫做'李名'的记录删除,我们可以利用下面的SQL语句来完成。 DELETE * FROM 职员表格 WHERE 姓名='李名'; SQL除了可以作为查询与数据库表格的建立的工具外,对于数据库与表格的新建、删修、与维护,与具有相当不错的功能,若是读者使用SQL命令得宜,对于整个效率的提高有着很大的帮助,所以对于SQL语句所带来的优势,常常会遇到一个情况,就是:“当我们对多个表格作复杂与多步骤的处理时,或许SQL只要一个语句就可以完成所有的需求与目标”,乍看一下,或许觉得有些玄妙,但是接下来的章节,会让你了解其中的妙处。 SELECT...INTO语句 我们可以通过这个命令,利用既存表格查询,来建立一个新表格的查询语句。 SELECT field1[,field2[,...]]INTO newtable[IN externaldatabase] FROM source INNER JOIN操作数 当某一个共同的字段数据相等时,将两个表格的记录加以组合。 SELECT fields FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2 UNION操作数 我们可以通过UNION操作数来建立连接的查询条件,UNION操作数可以将两个以上的表格或是查询的结果组合起来。 [TABLE]query1 UNION [ALL][TABLE]query2 [ 例如: 你可以利用下面的SQL语句,将订单数量超过1000的顾客表格记录,与新客户表格作UNION的操作。 TABLE 新客户表格 UNION ALL SELECT * FROM 顾客表格 WHERE 订单数量>1000; ALTER TABLE table DROP语句 针对所指定的表格或字段加以删除,或是把索引删除。 DROP {TABLE table|INDEX index ON table} INSERT INTO语句 新建一条数据到表格当中。 多条记录新建查询: INSERT INTO target [IN externaldatabase][(field1[,field2[,...]])] UPDATE语句 建立一个UPDATE的查询,通过条件的限制来修改特定的数据。 UPDATE table SET newvalue WHERE criteria; 五种提高 SQL 性能的方法 有时,为了让应用程序运行得更快,所做的全部工作就是在这里或那里做一些很小调整。啊,但关键在于确定如何进行调整!迟早您会遇到这种情况:应用程序中的 SQL 查询不能按照您想要的方式进行响应。它要么不返回数据,要么耗费的时间长得出奇。如果它降低了报告或您的企业应用程序的速度,用户必须等待的时间过长,他们就会很不满意。就像您的父母不想听您解释为什么在深更半夜才回来一样,用户也不会听你解释为什么查询耗费这么长时间。(“对不起,妈妈,我使用了太多的 LEFT JOIN。”)用户希望应用程序响应迅速,他们的报告能够在瞬间之内返回分析数据。就我自己而言,如果在 Web 上冲浪时某个页面要耗费十多秒才能加载(好吧,五秒更实际一些),我也会很不耐烦。 为了解决这些问题,重要的是找到问题的根源。那么,从哪里开始呢?根本原因通常在于数据库设计和访问它的查询。在本月的专栏中,我将讲述四项技术,这些技术可用于提高基于 SQL Server? 的应用程序的性能或改善其可伸缩性。我将仔细说明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的检索。请记住,根本没有神奇的解决方案。调整您的数据库及其查询需要占用时间、进行分析,还需要大量的测试。这些技术都已被证明行之有效,但对您的应用程序而言,可能其中一些技术比另一些技术更适用。 从 INSERT 返回 IDENTITY 我决定从遇到许多问题的内容入手:如何在执行 SQL INSERT 后检索 IDENTITY 值。通常,问题不在于如何编写检索值的查询,而在于在哪里以及何时进行检索。在 SQL Server 中,下面的语句可用于检索由最新在活动数据库连接上运行的 SQL 语句所创建的 IDENTITY 值: SELECT @@IDENTITY 这个 SQL 语句并不复杂,但需要记住的一点是:如果这个最新的 SQL 语句不是 INSERT,或者您针对非 INSERT SQL 的其他连接运行了此 SQL,则不会获得期望的值。您必须运行下列代码才能检索紧跟在 INSERT SQL 之后且位于同一连接上的 IDENTITY,如下所示: INSERT INTO Products (ProductName) VALUES ('Chalk') SELECT @@IDENTITY 在一个连接上针对 Northwind 数据库运行这些查询将返回一个名称为 Chalk 的新产品的 IDENTITY 值。所以,在使用 ADO 的 Visual Basic? 应用程序中,可以运行以下语句: Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _ (ProductName) VALUES ('Chalk');SELECT @@IDENTITY") lProductID = oRs(0) 此代码告诉 SQL Server 不要返回查询的行计数,然后执行 INSERT 语句,并返回刚刚为这个新行创建的 IDENTITY 值。SET NOCOUNT ON 语句表示返回的记录集有一行和一列,其中包含了这个新的 IDENTITY 值。如果没有此语句,则会首先返回一个空的记录集(因为 INSERT 语句不返回任何数据),然后会返回第二个记录集,第二个记录集中包含 IDENTITY 值。这可能有些令人困惑,尤其是因为您从来就没有希望过 INSERT 会返回记录集。之所以会发生此情况,是因为 SQL Server 看到了这个行计数(即一行受到影响)并将其解释为表示一个记录集。因此,真正的数据被推回到了第二个记录集。当然您可以使用 ADO 中的 NextRecordset 方法获取此第二个记录集,但如果总能够首先返回该记录集且只返回该记录集,则会更方便,也更有效率。 此方法虽然有效,但需要在 SQL 语句中额外添加一些代码。获得相同结果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 语句,并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中,如下面的代码片段所示。这样,任何进入该表的 INSERT 语句都将自动返回 IDENTITY 值。 CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS SELECT @@IDENTITY GO 触发器只在 Products 表上发生 INSERT 时启动,所以它总是会在成功 INSERT 之后返回一个 IDENTITY。使用此技术,您可以始终以相同的方式在应用程序中检索 IDENTITY 值。 内嵌视图与临时表 某些时候,查询需要将数据与其他一些可能只能通过执行 GROUP BY 然后执行标准查询才能收集的数据进行联接。例如,如果要查询最新五个定单的有关信息,您首先需要知道是哪些定单。这可以使用返回定单 ID 的 SQL 查询来检索。此数据就会存储在临时表(这是一个常用技术)中,然后与 Products 表进行联接,以返回这些定单售出的产品数量: CREATE TABLE #Temp1 (OrderID INT NOT NULL, _ OrderDate DATETIME NOT NULL) INSERT INTO #Temp1 (OrderID, OrderDate) SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM #Temp1 t INNER JOIN [Order Details] od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName DROP TABLE #Temp1 这些 SQL 语句会创建一个临时表,将数据插入该表中,将其他数据与该表进行联接,然后除去该临时表。这会导致此查询进行大量 I/O 操作,因此,可以重新编写查询,使用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。所以,您不用在 tempdb 中的临时表上耗费大量 I/O 和磁盘访问,而可以使用内嵌视图得到同样的结果: SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM ( SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC ) t INNER JOIN [Order Details] od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName 此查询不仅比前面的查询效率更高,而且长度更短。临时表会消耗大量资源。如果只需要将数据联接到其他查询,则可以试试使用内嵌视图,以节省资源。 避免 LEFT JOIN 和 NULL 当然,有很多时候您需要执行 LEFT JOIN 和使用 NULL 值。但是,它们并不适用于所有情况。改变 SQL 查询的构建方式可能会产生将一个花几分钟运行的报告缩短到只花几秒钟这样的天壤之别的效果。有时,必须在查询中调整数据的形态,使之适应应用程序所要求的显示方式。虽然 TABLE 数据类型会减少大量占用资源的情况,但在查询中还有许多区域可以进行优化。SQL 的一个有价值的常用功能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如,如果希望返回每个客户及其定单,使用 LEFT JOIN 则可以显示有定单和没有定单的客户。 此工具可能会被过度使用。LEFT JOIN 消耗的资源非常之多,因为它们包含与 NULL(不存在)数据匹配的数据。在某些情况下,这是不可避免的,但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多,所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN,则会得到非常可观的回报(请参阅图 1 中的图)。 图 1 查询 加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型,插入第一个表(LEFT JOIN 左侧的表)中的所有行,然后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程,但与标准的 LEFT JOIN 相比,可以节省大量时间。一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间,直到获得用于您的应用程序的执行性能最佳的查询。 测试查询的速度时,有必要多次运行此查询,然后取一个平均值。因为查询(或存储过程)可能会存储在 SQL Server 内存中的过程缓存中,因此第一次尝试耗费的时间好像稍长一些,而所有后续尝试耗费的时间都较短。另外,运行您的查询时,可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时,可能会导致您的查询要排队等待。例如,如果您进行查询时某人正在更新此表中的数据,则在更新提交时您的查询可能需要耗费更长时间来执行。 避免使用 LEFT JOIN 时速度降低的最简单方法是尽可能多地围绕它们设计数据库。例如,假设某一产品可能具有类别也可能没有类别。如果 Products 表存储了其类别的 ID,而没有用于某个特定产品的类别,则您可以在字段中存储 NULL 值。然后您必须执行 LEFT JOIN 来获取所有产品及其类别。您可以创建一个值为“No Category”的类别,从而指定外键关系不允许 NULL 值。通过执行上述操作,现在您就可以使用 INNER JOIN 检索所有产品及其类别了。虽然这看起来好像是一个带有多余数据的变通方法,但可能是一个很有价值的技术,因为它可以消除 SQL 批处理语句中消耗资源较多的 LEFT JOIN。在数据库中全部使用此概念可以为您节省大量的处理时间。请记住,对于您的用户而言,即使几秒钟的时间也非常重要,因为当您有许多用户正在访问同一个联机数据库应用程序时,这几秒钟实际上的意义会非常重大。 灵活使用笛卡尔乘积 对于此技巧,我将进行非常详细的介绍,并提倡在某些情况下使用笛卡尔乘积。出于某些原因,笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责,开发人员通常会被警告根本就不要使用它们。在许多情况下,它们消耗的资源太多,从而无法高效使用。但是像 SQL 中的任何工具一样,如果正确使用,它们也会很有价值。例如,如果您想运行一个返回每月数据(即使某一特定月份客户没有定单也要返回)的查询,您就可以很方便地使用笛卡尔乘积。 图 2 中的 SQL 就执行了上述操作。 虽然这看起来好像没什么神奇的,但是请考虑一下,如果您从客户到定单(这些定单按月份进行分组并对销售额进行小计)进行了标准的 INNER JOIN,则只会获得客户有定单的月份。因此,对于客户未订购任何产品的月份,您不会获得 0 值。如果您想为每个客户都绘制一个图,以显示每个月和该月销售额,则可能希望此图包括月销售额为 0 的月份,以便直观标识出这些月份。如果使用 图 2 中的 SQL,数据则会跳过销售额为 0 美元的月份,因为在定单表中对于零销售额不会包含任何行(假设您只存储发生的事件)。 图 3 中的代码虽然较长,但是可以达到获取所有销售数据(甚至包括没有销售额的月份)的目标。首先,它会提取去年所有月份的列表,然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步,此代码会获取在该时间段内有销售额的所有客户公司的名称列表,然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基本数据,但实际销售数量除外。第一个表中列出了所有月份(12 行),第二个表中列出了这个时间段内有销售额的所有客户(对于我是 81 个)。并非每个客户在过去 12 个月中的每个月都购买了产品,所以,执行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。 笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘,生成一个行集合,其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此,笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表,以及选择最终的行集。 如果由于笛卡尔乘积占用的资源可能会很多,而不需要真正的笛卡尔乘积,则可以谨慎地使用 CROSS JOIN。例如,如果对产品和类别执行了 CROSS JOIN,然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行,那么使用 INNER JOIN 会获得同样的结果,而且效率高得多。如果需要为所有的可能性都返回数据(例如在您希望使用每月销售日期填充一个图表时),则笛卡尔乘积可能会非常有帮助。但是,您不应该将它们用于其他用途,因为在大多数方案中 INNER JOIN 的效率要高得多。 拾遗补零 这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计,但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组,并使用 HAVING 子句消除那些未处于活动状态的销售人员,也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数,所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。 另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表,来代替使用 GROUP BY 子句。在这种情况下,使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外,如果您的查询总是自己返回一个唯一的行,则不要使用 DISTINCT 关键字。在这种情况下,DISTINCT 关键字只会增加系统开销。 您已经看到了,有大量技术都可用于优化查询和实现特定的业务规则,技巧就是进行一些尝试,然后比较它们的性能。最重要的是要测试、测试、再测试。在此专栏的将来各期内容中,我将继续深入讲述 SQL Server 概念,包括数据库设计、好的索引实践以及 SQL Server 安全范例。 如有向 Johnny 提出的问题和建议,请发送电子邮件到 mmdata@microsoft.com SQL高手篇:精妙SQL语句介绍 文章类别:SQL技巧 发表时间:2004-8-22 19:36:18
浏览:522 次 http://www./article/2004822193618.htm 说明:复制表(只复制结构,源表名:a 新表名:b) SQL: select * into b from a where 1<>1 说明:拷贝表(拷贝数据,源表名:a 目标表名:b) SQL: insert into b(a, b, c) select d,e,f from b; 说明:显示文章、提交人和最后回复时间 SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 说明:外连接查询(表名1:a 表名2:b) 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 说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明:两张关联表,删除主表中已经在副表中没有的信息 SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 说明:-- SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM (+)AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 说明:-- SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 SQL: SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 说明:四表联查问题: SQL: 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 ..... 说明:得到表中最小的未使用的ID号 SQL: SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a) |
|