如何在oracle和Mysql中限制返回结果集的大小
在Oracle中如下：
如何正确利用Rownum来限制查询所返回的行数？
软件环境：
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为：C:/ORANT
含义解释：
1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，
依此类推，这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。
使用方法：
现有一个商品销售表sale，表结构为：
month char(6) --月份
sell number(10,2) --月销售金额
create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
insert into sale values('200008',1000);
commit;
SQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到记录）
没有查到记录
SQL> select rownum,month,sell from sale where rownum>5;
（由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立，查不到记录）
没有查到记录
只返回前3条纪录
SQL> select rownum,month,sell from sale where rownum<4;
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
如何用rownum实现大于、小于逻辑？（返回rownum在4—10之间的数据）（minus操作，速度会受影响）
SQL> select rownum,month,sell from sale where rownum<10
2 minus
3 select rownum,month,sell from sale where rownum<5;
ROWNUM MONTH SELL
--------- ------ ---------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200
想按日期排序，并且用rownum标出正确序号（有小到大）
SQL> select rownum,month,sell from sale order by month;
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
4 200004 1300
5 200005 1400
6 200006 1500
7 200007 1600
11 200008 1000
8 200101 1100
9 200202 1200
10 200301 1300
查询到11记录.
可以发现，rownum并没有实现我们的意图，系统是按照记录入库时的顺序给记录排的号，rowid也是顺序分配的
SQL> select rowid,rownum,month,sell from sale order by rowid;
ROWID ROWNUM MONTH SELL
------------------ --------- ------ ---------
000000E4.0000.0002 1 200001 1000
000000E4.0001.0002 2 200002 1100
000000E4.0002.0002 3 200003 1200
000000E4.0003.0002 4 200004 1300
000000E4.0004.0002 5 200005 1400
000000E4.0005.0002 6 200006 1500
000000E4.0006.0002 7 200007 1600
000000E4.0007.0002 8 200101 1100
000000E4.0008.0002 9 200202 1200
000000E4.0009.0002 10 200301 1300
000000E4.000A.0002 11 200008 1000
查询到11记录.
正确用法，使用子查询
SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200002 1100
3 200003 1200
4 200004 1300
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
10 200202 1200
11 200301 1300
按销售金额排序，并且用rownum标出正确序号（有小到大）
SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;
ROWNUM MONTH SELL
--------- ------ ---------
1 200001 1000
2 200008 1000
3 200002 1100
4 200101 1100
5 200003 1200
6 200202 1200
7 200004 1300
8 200301 1300
9 200005 1400
10 200006 1500
11 200007 1600
查询到11记录.
利用以上方法，如在打印报表时，想在查出的数据中自动加上行号，就可以利用rownum。
返回第5—9条纪录，按月份排序
SQL> select * from (select rownum row_id ,month,sell
2 from (select month,sell from sale group by month,sell))
3 where row_id between 5 and 9;
ROW_ID MONTH SELL
---------- ------ ----------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
==================================================================================
在Mysql中：
下面是一些学习如何用MySQL解决一些常见问题的例子。
一些例子使用数据库表“shop”，包含某个商人的每篇文章(物品号)的价格。假定每个商人的每篇文章有一个单独的固定价格，那么(物品
，商人)是记录的主键。
你能这样创建例子数据库表：
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);
好了，例子数据是这样的：
SELECT * FROM shop
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
3.1 列的最大值
“最大的物品号是什么？”
SELECT MAX(article) AS article FROM shop
+---------+
| article |
+---------+
| 4 |
+---------+
3.2 拥有某个列的最大值的行
“找出最贵的文章的编号、商人和价格”
在ANSI-SQL中这很容易用一个子查询做到：
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop)
在MySQL中（还没有子查询)就用2步做到：
用一个SELECT语句从表中得到最大值。
使用该值编出实际的查询：
SELECT article, dealer, price
FROM shop
WHERE price=19.95
另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到的第一行：
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1
注意:如果有多个最贵的文章( 例如每个19.95)，LIMIT解决方案仅仅显示他们之一！
3.3 列的最大值：按组：只有值
“每篇文章的最高的价格是什么？”
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
3.4 拥有某个字段的组间最大值的行
“对每篇文章，找出有最贵的价格的交易者。”
在ANSI SQL中，我可以用这样一个子查询做到：
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
在MySQL中，最好是分几步做到：
==========================================================================
14. 如何在mysql中创建表 ?
答：试下这个 ..
CREATE TABLE pictures( picture_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id SMALLINT UNSIGNED NOT NULL,
location VARCHAR(40),
thumb VARCHAR(40),
title VARCHAR(80) NOT NULL,
description TINYTEXT,
last_modified DATE,
last_viwed DATE,
view_count INT UNSIGNED,
user_id VARCHAR(20) NOT NULL,
colour ENUM('true','false') NOT NULL DEFAULT 'true',
PRIMARY KEY (picture_id),
INDEX (title),
INDEX (user_id),
INDEX (category_id),
INDEX (colour) );
15. 如何在M个纪录中只列出N个，并用翻页的方法列出其它?
答：可以采用MYSQL的LIMIT函数.
注意:下面的代码用了cgi-lib.pl的函数来获取网页输入数据.
sub List_Result{
my ($user_action) = @_;
my %cgi_data;
&ReadParse(%cgi_data);
my $limit = 10 ;
my $offset = $cgi_data{'offset'};
my $printed = $cgi_data{'printed'};
my $prev_offset = $cgi_data{'prev_offset'};
my $next_action = $cgi_data{'next_action'};
my $print_cnt = 0;
$new_prev_offset = $offset;
#下面的代码取得用户的操作
if ($next_action eq "Next"){
$offset += $limit;
}
elsif($next_action eq "Previous"){
if ($printed < $limit){
$offset = $prev_offset;
}else{
$offset -= $printed;
}
}
else { $offset = 0 ; }
}
my $SELECT ;
my $LIMIT = " LIMIT $offset,$limit";
# 如果$KEEP_SQL 为空,则表示重新开始，用旧的sql语句
if ($KEEP_SQL eq ""){
if($user_action eq "list_all"){
$SELECT = "SELECT * FROM mytable ";
}
else{
$SELECT = "SELECT * FROM mytable WHERE rec_id = $rec_id ";
}
}else{
$SELECT = $KEEP_SQL;
}
my $SQL = $SELECT.$LIMIT;
# KEEP_SQL将被保存在一个隐含的表段输入中，这个变量保证每次都用一个sql语句.
my $KEEP_SQL = $SELECT;
my $sth = $dbh->prepare($SQL);
$sth->execute() or die "Can't execute:";
# 做你想做的事情.
print " [form method=post action=$this_cgi] ...
... 列出结果 ..
[input type=hidden name=offset value=$offset]
[input type=hidden name=printed value=$printed]
[input type=hidden name=prev_offset value=$new_prev_offset]
[input type=hidden name=user_action value=viewing_result]
[input type=hidden name=KEEP_SQL value=$KEEP_SQL] ";
if ($offset > 0 ) {print "[input type=submit name=next_action width=100 value="Previous"]n"; }
if ($printed == $limit){ print "[input type=submit name=next_action width=100 value="Previous"n"]; }
print "[/form]";
16. 如何获得表的字段信息?
答：
#!/usr/bin/perl
# connect to db
my $dbh = DBI->connect(bla..bla..bla);
my $sql_q = "SHOW COLUMNS FROM $table";
my $sth = $dbh->prepare($sql_q);
$sth->execute;
while (@row = $sth->fetchrow_array){
print"Field Type Null Key Default Extran";
print"---------------------------------------------------------------n";
print"$row[0] $row[1] $row[2] $row[3] $row[4] $row[5]n";
}
17. 如何添加一个超级用户 ?
答: 你可以用GRANT语句:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;
超级用户可以从任何地方连接服务器,但必须使用一个密码('something').
请注意我们同时对monty@localhost和monty@"%"用了GRANT语句.如果不加上localhost,当我们(超级用户)从本机上连接时,localhost上
mysql_install_db创建的匿名用户会取得更高的优先权,因为它有更特别的Host字段值,使得在用户列表中占据靠前的位置.
18.如何知道Mysql服务器中所有可供使用的数据库?
答: 用data_sources($driver_name)方法.
这个方法返回SQL服务器中数据库名字列表
例: $db_names = DBI->data_sources("mysql");
19. 如何连接SQL服务器?
答:
#!/usr/bin/perl
use DBI;
my $database_name = "db_name";
my $location = "localhost";
my $port_num = "3306"; # 这是mysql的缺省
值 # 定义SQL服务器的位置.
my $database = "DBI:mysql:$database_name:$location:$port_num";
my $db_user = "user_name";
my $db_password = "user_password";
# 连接.
my $dbh = DBI->connect($database,$db_user,$db_password);
# 做你要做的事情.. ... ...
$dbh->disconnect;
$exit;
20. 如何从SQL服务器上获取记录数据?
答:从SQL服务器上获取记录数据,必须先连接服务器,然后提交SQL查询语句,服务器则返回结果
#!/usr/bin/perl
# 连接服务器 (见22)
my $sql_statement = "SELECT first_name,last_name FROM $table ORDER BY first_name";
my $sth = $dbh->prepare($sql_statement);
my ($first, $last);
# 结果保存在$sth中 $sth->execute() or die "无法执行SQL语句:
$dbh->errstr"; $sth->bind_columns(undef, $first, $last);
my $row; while ($row = $sth->fetchrow_arrayref) {
print "$first $lastn";
# 或者
print "$row->[0] $row[1]n";
}
以上的程序将列出结果中的每一行,打印出first name和last name.这是最快的提取数据的方法之一.
21. 如何从服务器随机地提取记录?
答: 用Mysql的LIMIT函数.
$Query = "SELECT * FROM Table";
$sth = $dbh->prepare($Query);
$numrows = $sth->execute;
$randomrow = int(rand($numrows));
$sth = $dbh->prepare("$Query LIMIT $randomrow,1");
$sth->execute;
@arr = $sth->fetchrow;
22. 插入记录后,如何获得自动增加的主键值?
答: insertid方法是MySQL特有的,也许不能在其它SQL server上工作
#!/usr/bin/perl
# 连接数据库 ....
my $sql_statement = "INSERT INTO $table (field1,field2) VALUES($value1,$value2)";
my $sth = $dbh->prepare($sql_statement);
$sth->execute or die "无法添加数据 :
$dbh->errstr";
# 现在我们可以取回刚刚插入后生成的主键.
my $table_key = $sth->{insertid};
# 也可以用这种方法(标准的DBI方法)
my $table_key = $dbh->{'mysql_insertid'};
$sth->finish;
23. 执行SELECT查询以后,如何获得记录行数?
答:有好几种方法可以做到.这是其中的一种:
# 文档中说这种方法不行,但对我来说却可以,你或许也行.
my $mysql_q = "SELECT field1,field2 FROM $table WHERE field1=$value1";
my $sth = $dbh->prepare($mysql_q);
my $found = $sth->execute or die "无法执行 :
$dbh->errstr";
$sth->finish;
# 这是一种较慢的方法,而且做SELECT查询时还不太可靠.
my $sql = q(select * from $table where field = ? );
my $sth = $dbh->prepare($sql);
$sth->execute('$value');
my $rows = $sth->rows;
$sth->finish;
# 这是一种较快的方法.
my $sql = q(select count(*) from $table where field = ? );
my $sth = $dbh->prepare($sql); $sth->execute('$value');
my $rows = $sth->fetchrow_arrayref->[0];
$sth->finish;
24. 为什么SELECT LAST_INSERT_ID(USER_ID) FROM User返回的是所有的user id而不是最后一个?
答: 摘自手册:
"在服务器上最后创建的ID是根据每个连接来单独管理的.也就是说,它不能被另外一个客户端改变. 甚至你用一个非空和非零的值来更新另外一
个AUTO_INCREMENT字段,它也不会改变. 如果算式做为一个参量赋给UPDATE语句中的LAST_INSERT_ID(),则参量会返回LAST_INSERT_ID()的值."
你真正需要的是: SELECT USER_ID FROM User ORDER BY USER_ID DESC LIMIT 1
25. WHERE语句中可否使用两个条件?
答: 可以
my $sql_statment = "SELECT * FROM $table WHERE $field1='$value1' AND $field2='$value2'";
26. 如何在多个字段中查找一个关键字?
答: 试下这个:
SELECT concat(last,' ',first,' ',suffix,' ',year,' ',phone,' ',email) AS COMPLEAT, last, first, suffix, year, dorm, phone,
box, email
FROM Student HAVING COMPLEAT
LIKE '%value1%' AND COMPLEAT LIKE '%value2%' AND COMPLEAT LIKE '%value3%'
27.如何找到一个星期前创建的记录?
答: 我们需要用DATE函数来做sql查询:
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
例如 : # 这个查询语句返回所有"年龄"小于或等于7天的记录
my $sql_q = "SELECT * FROM $database WHERE DATE_ADD(create_date,INTERVAL 7 DAY) >= NOW() ORDER BY create_date DESC";
28.如何取回所有字段的数据并用"column_name" => value来放入一个相关的数组中?
答:用$sth->fetchrow_hashref 方法.
$SQL = "SELECT * FROM members";
my $sth = $dbh->prepare($SQL);
$sth->execute or die "sql语句错误 ".
$dbh->errstr;
my $record_hash;
while ($record_hash = $sth->fetchrow_hashref){
print "$record_hash->{first_name} $record_hash->{last_name}n";
}
$sth->finish;
29.如何保存一个图像文件(JPG和GIF)到数据库中?
答:
file: test_insert_jpg.pl
-------------------------
#! /usr/bin/perl
use DBI;
open(IN,"/imgdir/bird.jpg");
$gfx_file=join('',);
close(IN);
$database="speedy";
$table="archive";
$user="stephen";
$password="none";
$dsn="DBI:mysql:$database";
$dbh=DBI->connect($dsn, $user, $password);
$sql_statement=<<"__EOS__";
insert into $table (id, date, category, caption, content, picture1, picture2,
picture3, picture4, picture5, source, _show) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
__EOS__
# uncomment to debug sql statement
# --------------------------------
#open(SQLLOG,">>sql_log_file");
#print SQLLOG scalar(localtime)."t$sql_statementn";
#close(SQLLOG);
$sth=$dbh->prepare($sql_statement);
$sth->execute(NULL,NULL,"car|sports","Porsche Boxster S","German excellence",$gfx_file,NULL,NULL,NULL,NULL,"European
Car","Y");
$sth->finish(); $sth=$dbh->prepare("SELECT * FROM $table");
$sth->execute();
while($ref=$sth->fetchrow_hashref()){
print "id = $ref->{'id'}tcategory = $ref->{'category'}tcaption = $ref->{'caption'}n";
}
$numRows=$sth->rows;
$sth->finish();
$dbh->disconnect();
file: serve_gfx.cgi
-----------------------------------------------------
#!/usr/bin/perl
$|=1;
use DBI;
$database="speedy";
$table="archive";
$user="stephen";
$password="none";
$dsn="DBI:mysql:$database";
$dbh=DBI->connect($dsn, $user,$password);
$sth=$dbh->prepare("select * from $table where id=1");
$sth->execute();
$ref=$sth->fetchrow_hashref();
print "content-type: image/jpgnn";
print $ref->{'picture1'};
$numRows=$sth->rows;
$sth->finish();
$dbh->disconnect();
30. 如何插入N个记录?
答:
# 让我们插入10000个记录
my $rec_num = 10000;
my $PRODUCT_TB = "products";
my $dbh = DBI->connect($database,$db_user,$db_password) or die "无法连接数据库n";
my $sth = $dbh->prepare("INSERT INTO $PRODUCT_TB (name,price,description,pic_location) VALUES (?,?,?,?)");
for ($i = 1; $i <= $rec_num; $i++){
my $name = "Product $i";
my $price = rand 350;
my $desc = "Desccription of product $i";
my $pic = "images/product/product".$i.".jpg";
$sth->execute($name,$price,$desc,$pic);
}
$sth->finish();
print "完成插入$rec_num个记录到表$PRODUCT_TBn";
$dbh->disconnect;
exit;
31.如何创建一个date字段,使其缺省值是新记录创建时的日期?
答:有很多种方法可以做到:
(1) 用TIMESTAMP
Create Table mytable( table_id INT NOT NULL AUTO_INCREMENT,
value VARCHAR(25),
date TIMESTAMP(14),
PRIMARY KEY (table_id) );
当插入或更新记录时,TIMESTAMP字段将自动地设置成当前日期.如果你不想更新时改变日期,可在用UPDATE语句时,把日期字段设置成原来的(插
入日期).
(2) 用NOW()函数.
Create Table mytable( table_id INT NOT NULL AUTO_INCREMENT,
value VARCHAR(25),
date DATE,
PRIMARY KEY (table_id) );
在insert语句中设置date=NOW().