各种数据库查询前几条数据的方法

落日映苍穹つ 2022-05-14 08:08 504阅读 0赞

sql在不同数据库查询前几条数据  
关键字: sql 前几条结果   
sql在不同数据库查询前几条数据   
1. ORACLE   
  SELECT \* FROM TABLE1 WHERE ROWNUM<=N

**select \* from stu\_info where rownum<=10 (查询学生信息表的前10条数据)**  
  HQL: from table1 t order by t.createTime desc where rownum<=n

**HQL:from stu\_info s order by s.creatTime desc where rownum<=10**

2. INFORMIX   
   SELECT FIRST N \* FROM TABLE1   
3. DB2   
SELECT \* ROW\_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N   
或者   
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY   
4. SQL SERVER   
SELECT TOP N \* FROM TABLE1      ** select TOP 10 \* from stu\_info   (前10条数据)**  
5. SYBASE   
SET ROWCOUNT N   
GO   
SELECT \* FROM TABLE1   
6. MYSQL (mySql)  
SELECT \* FROM TABLE1 LIMIT N      ** select \* fron stu\_info limit 10**  
hibernate查询记录的前10条记录   
就像mysql的SQL语句的"select \* from table limit 10" hql 不支持limit   
query.setFirstResult(0);//从第0条开始取   
query.setMaxResults(10);//取十条记录   
7. FOXPRO   
SELECT \* TOP N FROM TABLE ORDER BY COLUMN   
8.postgres查询前几条记录SQL   
SELECT \* FROM TABLE LIMIT   
跟我学SQL分为三部分，其中涵盖了有关SQL标准的基本知识。在上一篇文章里我们讨论了一些数据库术语和4种最基本的数据查询类型。此外，我们还解释了WHERE子句和条件语句的用法，同时我们提供了各类查询的具体示例。  
在这篇文章里，我们将就其他一些SQL函数和子句进行阐述，供你用于基本的SELECT数据查询中。  
SELECT选项精制结果  
正如我们从上一篇文章中所读到的那样，SELECT语句具有种类繁多的各类选项，这些选项可以用来控制数据返回的方式。这些选项以子句、关键词和函数的形式存在。  
子句是一种修改结果的语句。子句不是必要的语句但它对数据的内容及其显示进行了提炼。WHERE子句就是这样的子句。  
关键词触发数据库的内在功能。这些关键词在有时甚至是查询所必需的。例如“INSERT INTO table\_name (column1) VALUES (‘data1’);”语句中的INTO和VALUE就是如此。我们将了解DISTINCT关键词，它能触发一些非常有用的可选功能。  
下面总结了一些最常用的子句、关键词和函数。然后我会对每一部分举例说明。  
·ORDER BY – 按照指定列排序返回结果的子句  
·DISTINCT – 只返回结果集合内唯一行的关键词   
·COUNT -- 返回匹配查询的数据行总数数值的函数   
·AVG – 该函数返回指定列的平均值  
·SUM –该函数把指定的列中的数字加起来  
·MIN – 该函数返回列中最小的非NULL值  
·MAX –该函数返回列中的最大值  
·GROUP BY – 按列汇集查询函数结果的子句  
用ORDER BY对查询结果排序  
ORDER BY子句让数据库对查询结果排序，这样你就无须自己编写应用程序进行“手工”排序了。ORDER BY子句必须放在查询语句的结尾。其基本用法如下：  
SELECT \* FROM Contacts ORDER BY first\_name;  
你可以随意在任何选择语句中使用ORDER BY 子句返回多列结果。你还可以用它连接其他子句：  
SELECT first\_name, last\_name FROM Contacts WHERE first\_name BETWEEN ‘a’ AND ‘k’ ORDER BY last\_name;  
你可以对多列数据排序。优先顺序按从左到右依次降低，所以查询语句中各列的排列顺序很重要。  
SELECT \* FROM Contacts ORDER BY company, last\_name, first\_name;  
查询结果默认按数字或者字母的升序排序。你可以在ORDER BY 子句后面加上DESC关键词改成降序排列。在下面的例子中，最高的net\_amount排在最先（降序）。假如两行或者两行以上数据都包含了同样的net\_amount值，那么同行中last\_name值在字母表中最先出现的排先，因为last\_name一列还是按照升序排序的。  
SELECT \* FROM Sales ORDER BY net\_amount DESC, last\_name, first\_name;  
在按照定义的列名排序以后，大多数数据库随后将按照数据表内的第一列排序然后顺序向右再排序。具体的实现各有变化，因此，如果排序在应用中比较重要那么你应该明确地定义所要排序的列。  
另外一值得注意的问题是，采用ORDER BY子句（以及WHERE子句），你正在用来排序结果的数据列并不一定得是返回结果集合的一部分。只要所有引用的列都在数据表内存在则下例完全有效：  
SELECT company, first\_name, net\_amount FROM Sales ORDER BY start\_date, last\_name;  
DISTINCT返回不重复结果  
DISTINCT关键词只返回结果集合内不重复的数据行。例如，有时你可能需要找出Sales表内的公司，但是你又不想看见每个条目。于是你可以用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;  
函数应用逻辑  
返回单一值的函数称做聚集函数（aggregate function）。通过应用程序访问下列聚集函数的结果时，包含结果的“字段名”就是你所使用的实际函数。例如，在分析你的数据库结果时，结果数组的键值可能如下所示：  
$keyname = “COUNT(\*)”;  
$resultkey = “AVG(net\_amount)”;  
COUNT  
COUNT函数计算出结果集合中的数据行数。和其他函数一样它接受一个参数。以下的基本示例能告诉你数据表内的行数：SELECT COUNT(\*) FROM Sales;  
你也可以用它来计算任何结果集合中的行数。  
SELECT COUNT(\*) FROM Sales WHERE net\_amount > 100;  
如果你想看看某特定列有多少行包含非空值，那你不妨对该列使用COUNT函数。注意，除非数据库设置为字段为空时缺省填充NULL否则将返回表内数据行的总数。另外，列出的列在超出一个的情况下会引起错误。  
SELECT COUNT(company) FROM Sales;  
 COUNT还可以用来计算DISTINCT结果集合中的行数。  
SELECT COUNT(DISTINCT company, last\_name) FROM Sales;  
COUNT语句通常用在程序中确定FOR循环的循环次数。  
AVG  
AVG返回某列所有字段的平均值，该列必须是数字数据类型。该函数用列的名字作为其参数，如果列字段数据类型是非数字类型的则函数返回“0”。SELECT AVG(net\_amount) FROM Sales;  
你可以结合子句限制该函数的应用范围。  
SELECT AVG(net\_amount) FROM Sales WHERE company LIKE ‘%ABCD Co%’;  
就象所有聚集函数一样，ORDER BY语句将被忽略。  
SUM  
SUM的工作方式和AVG差不多，只不过该函数返回结果集合中所有字段值的和。  
SELECT SUM(net\_amount) FROM Sales WHERE net\_amount > 100;  
AVG、SUM、MIN和MAX函数在没有指定列的情况下都会返回错误，所以你不能使用“\*”通配符。  
MIN  
MIN返回指定列中最小的非空值。如果指定列是数字数据类型则结果将是最小的数字。如果它是一种字符串数据类型则函数将返回按字母表顺序出现的第1个值。SELECT MIN(net\_amount) FROM Sales WHERE last\_name = “Smith”;  
SELECT MIN(last\_name) FROM Sales;  
 MAX  
MAX的工作方式和MIN函数一样，只不过该函数返回最大的非空值。该函数也可以用于字符串或者数字列  
SELECT MAX(net\_amount) FROM Sales;  
SELECT MAX(company) FROM Sales WHERE net\_amount > 100;  
MAX函数有时还用在包含自动递增键字段的列上确定下一条目的键ID。除非你正在运行一个非公开的数据库，否则在使用这一信息插入下一条目时务必谨慎，以防其他用户先你执行数据操作。  
GROUP BY 令函数更有用  
虽然以上提到的所有这些函数都能提供相当有用的信息，但是，如果有GROUP BY子句帮忙的话更能让你在列的字段子集中应用这些函数。不要对你的Sales表中每一家公司一次又一次地执行MAX函数查询——你完全可以带GROUP BY子句获得同样的结果：  
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的查询  
如你在以上示例中所看到的那样，你可以结合WHERE字句利用以上的概念限制查询的范围。WHERE子句会首先被计算，然后执行函数。在使用组的时候就是这样的。  
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的结果。  
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能， 我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用 VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用 MEDIUMINT而不是BIGIN来定义整型字段。   
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。   
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。   
2、使用连接（JOIN）来代替子查询(Sub-Queries)   
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询 中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查 询，如下所示：   
DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )   
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查 询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：   
SELECT \* FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )   
如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：   
SELECT \* FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL   
连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。   
3、使用联合(UNION)来代替手动创建的临时表   
MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。   
SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author   
UNION   
SELECT Name, Supplier FROM product   
4、事务   
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一 条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候， 整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意 外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句 块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。   
BEGIN;   
INSERT INTO salesinfo SET CustomerID=14;   
UPDATE inventory SET Quantity=11   
WHERE item='book';   
COMMIT;   
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。   
5、锁定表   
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户   
来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。   
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。   
LOCK TABLE inventory WRITE   
SELECT Quantity FROM inventory   
WHEREItem='book';   
...   
UPDATE inventory SET Quantity=11   
WHEREItem='book';   
UNLOCK TABLES   
这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。   
6、使用外键   
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的 客户。在这里，外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到 salesinfo中。   
CREATE TABLE customerinfo   
(   
CustomerID INT NOT NULL ,   
PRIMARY KEY ( CustomerID )   
) TYPE = INNODB;   
CREATE TABLE salesinfo   
(   
SalesID INT NOT NULL,   
CustomerID INT NOT NULL,   
PRIMARY KEY(CustomerID, SalesID),   
FOREIGN KEY (CustomerID) REFERENCES customerinfo   
(CustomerID) ON DELETECASCADE   
) TYPE = INNODB;   
注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如例中所示。  
Mysql Limit操作   
select \* from table LIMIT 5,10; \#返回第6-15行数据  
select \* from table LIMIT 5; \#返回前5行  
select \* from table LIMIT 0,5; \#返回前5行   
性能优化：   
基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识.  
1.  
Select \* From cyclopedia Where ID>=(  
Select Max(ID) From (  
 Select ID From cyclopedia Order By ID limit 90001  
) As tmp  
) limit 100;  
2.  
Select \* From cyclopedia Where ID>=(  
Select Max(ID) From (  
 Select ID From cyclopedia Order By ID limit 90000,1  
) As tmp  
) limit 100;  
同样是取90000条后100条记录,第1句快还是第2句快?  
第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录  
第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录  
第1句执行结果.100 rows in set (0.23) sec  
第2句执行结果.100 rows in set (0.19) sec  
很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.  
其实第2句完全可以简化成  
Select \* From cyclopedia Where ID>=(  
Select ID From cyclopedia limit 90000,1  
)limit 100;  
直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.  
可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select \* From cyclopedia limit 90000,1呢?岂不更简洁?  
这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的"高分".Select \* 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.  
第1种方案同样可用于MS-SQL,而且可能是最好的.因为靠主键ID来定位起始段总是最快的.  
Select Top 100\* From cyclopedia Where ID>=(  
Select Top 90001 Max(ID) From (  
 Select ID From cyclopedia Order By ID  
) As tmp  
)  
但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的limit就有优势的多,执行:  
Select ID From cyclopedia limit 90000  
Select ID From cyclopedia limit 90000,1  
的结果分别是:  
90000 rows in set (0.36) sec  
1 row in set (0.06) sec  
而MS-SQL只能用Select Top 90000 ID From cyclopedia 执行时间是390ms,执行同样的操作时间也不及MySQL的360ms.