如何在oracle和Mysql中限制返回結果集的大小

如何在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().