How to limit the size of returned result sets in Oracle and MySQL

How to limit the size of returned result sets in Oracle and MySQL
In ORACLE:

How can I use rownum to limit the number of rows returned by a query?
Software environment:
1. Windows NT4.0 + Oracle 8.0.4
2. Oracle installation path: C:/orant

Description:
1. rownum is the number of the row returned from the query in the Oracle system. The first row is allocated 1 and the second row is 2,
This pseudo field can be used to limit the total number of rows returned by the query.
2. rownum cannot be prefixed with any base table name.
Usage:
There is a sales table sale with the following structure:
Month char (6) -- month
Billing number () -- monthly sales amount

Create Table sale (month char (6), Region number );
Insert into sale values ('20140901', 200001 );
Insert into sale values ('20140901', 200002 );
Insert into sale values ('20140901', 200003 );
Insert into sale values ('20140901', 200004 );
Insert into sale values ('20140901', 200005 );
Insert into sale values ('20140901', 200006 );
Insert into sale values ('20140901', 200007 );
Insert into sale values ('20140901', 200101 );
Insert into sale values ('20140901', 200202 );
Insert into sale values ('20140901', 200301 );
Insert into sale values ('20140901', 200008 );
Commit;

SQL> select rownum, month, distinct from sale where rownum = 1; (it can be used to limit the number of returned records to ensure no error, for example, implicit cursor)

Rownum month interval
------------------------
1 200001 1000

SQL> select rownum, month, region from sale where rownum = 2; (records cannot be found for more than 1)

No records found

SQL> select rownum, month, region from sale where rownum> 5;
(Since rownum is a pseudo column that always starts from 1, Oracle considers this condition invalid and cannot find records)

No records found

Only the first three records are returned.
SQL> select rownum, month, region from sale where rownum <4;

Rownum month interval
------------------------
1 200001 1000
2 200002 1100
3 200003 1200

How to Use rownum to implement greater than or less than the logic? (Returns the data of rownum between 4-10.) (the speed of the minus operation is affected)
SQL> select rownum, month, region from sale where rownum <10
2 minus
3 select rownum, month, region from sale where rownum <5;

Rownum month interval
------------------------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200

Sort by date and use rownum to mark the correct sequence number (from small to large)
SQL> select rownum, month, region from sale order by month;

Rownum month interval
------------------------
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 records found.

We can see that rownum does not implement our intention. The system sends the number to the record row according to the order in which the record is stored, and rowid is also allocated sequentially.

SQL> select rowid, rownum, month, region from sale order by rowid;

Rowid rownum month limit
------------------------------------------
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 records found.

Correct usage, use subquery
SQL> select rownum, month, distinct from (select month, distinct from sale group by month, distinct) Where rownum <13;

Rownum month interval
------------------------
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

Sort by sales amount, and use rownum to mark the correct sequence number (from small to large)
SQL> select rownum, month, distinct from (select distinct, month from sale group by region, month) Where rownum <13;

Rownum month interval
------------------------
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 records found.

Use the preceding method. For example, when printing a report, you can use rownum to automatically add a row number to the retrieved data.

Returns the 5-9 records, sorted by month.
SQL> select * from (select rownum row_id, month, week
2 from (select month, region from sale group by month, region ))
3 where row_id between 5 and 9;

Row_id month limit
--------------------------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
 
========================================================== ========================================================== =
In MYSQL:
The following are examples of how to use MySQL to solve some common problems.
　　
Some examples use the database table "Shop", which contains the price of each article (item number) of a merchant. Assume that each merchant's article has a separate fixed price

(Merchant) is the primary key of the record.
　　
You can create an example database table as follows:
Create Table shop (
Article int (4) unsigned zerofill default '100' 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 );
　　
Well, the example data is as follows:
　　
Select * from shop
　　
+ --------- + -------- + ------- +
| Article | dealer | price |
+ --------- + -------- + ------- +
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 1, 0003 | c | 1.69 |
| 1, 0003 | d | 1.25 |
| 1, 0004 | d | 19.95 |
+ --------- + -------- + ------- +
　　
Maximum Value of column 3.1
"What is the largest item number ?"
　　
Select max (Article) as article from shop
　　
+ --------- +
| Article |
+ --------- +
| 4 |
+ --------- +
　　
3.2 rows with the maximum value of a column
"Find the number, merchant, and price of the most expensive article"
　　
In the ANSI-SQL this is easily done with a subquery:
　　
Select article, dealer, price
From shop
Where price = (select max (price) from shop)
　　
In MySQL (no subquery is available), perform the following two steps:
　　
Use a SELECT statement to obtain the maximum value from the table.
Use this value to compile the actual query:
Select article, dealer, price
From shop
Where price = 19.95
　　
Another solution is to sort all rows by price in descending order and use the mysql-specific limit clause to obtain only the first row:
　　
Select article, dealer, price
From shop
Order by price DESC
Limit 1
　　
NOTE: If there are multiple most expensive articles (for example, each 19.95), the limit solution only shows one of them!
　　
Maximum Value of column 3.3: by group: only values
"What is the highest price for each article ?"
　　
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 rows with the maximum value of a field between groups
"Find the most expensive traders for each article ."
　　
In ansi SQL, I can use this subquery:
　　
Select article, dealer, price
From shop S1
Where price = (select max (s2.price)
From shop S2
Where s1.article = s2.article)
　　
In MySQL, it is best to do this in several steps:

========================================================== ========================================
14. How to Create a table in MySQL?
A: Try this ..

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,
Color Enum ('true', 'false') not null default 'true ',
Primary Key (picture_id ),
Index (title ),
Index (user_id ),
Index (category_id ),
Index (color ));
15. How to list only N records in M records and use the paging method to list others?
A: You can use the MySQL limit function.

Note: The following Code uses the cgi-lib.pl function to obtain the web page input data.

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;
# The following code retrieves user operations
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 ";

# If $ keep_ SQL is empty, it indicates a restart and the old SQL statement is used.
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 is saved in an implicit table segment input. This variable ensures that an SQL statement is used each time.
My $ keep_ SQL = $ select;
My $ something = $ DBH-> prepare ($ SQL );
$ Something-> execute () or die "can't execute :";

# Do what you want to do.
Print "[Form method = post action = $ this_cgi]...
... Listing results ..
[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. How to obtain the table field information?
A:

#! /Usr/bin/perl
# Connect to DB
My $ DBH = DBI-> connect (BLA... bla );
My $ SQL _q = "show columns from $ table ";
My $ something = $ DBH-> prepare ($ SQL _q );
$ Something-> execute;

While (@ ROW = $ th-> 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. How to add a Super User?
A: You can use the grant statement:
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;

Super Users can connect to the server from anywhere, but must use a password ('something ').

Please note that the grant statement is used for both Monty @ localhost and Monty @ "%". If localhost is not added, when we (Super User) connect from the local machine

The anonymous user created by mysql_install_db has a higher priority because it has a more special host field value, so that it occupies the top position in the user list.

18. How do I know all the databases available on the MySQL server?
A: Use the data_sources ($ driver_name) method.
This method returns the Database Name List on the SQL server.
Example: $ db_names = DBI-> data_sources ("MySQL ");

19. How to connect to the SQL server?
A:
#! /Usr/bin/perl
Use dBi;
My $ database_name = "db_name ";
My $ location = "localhost ";
My $ port_num = "3306"; # This is the default MySQL

Value # defines the location of the SQL Server.
My $ database = "DBI: mysql: $ database_name: $ location: $ port_num ";
My $ db_user = "user_name ";
My $ db_password = "user_password ";

# Connection.
My $ DBH = DBI-> connect ($ database, $ db_user, $ db_password );

# Do what you want to do ........
$ DBH-> disconnect;
$ Exit;

20. How can I obtain record data from the SQL server?
A: To obtain record data from the SQL server, you must first connect to the server and then submit the SQL query statement. Then, the server returns the result.

#! /Usr/bin/perl
# Connect to the server (see figure 22)
My $ SQL _statement = "select first_name, last_name from $ table order by first_name ";
My $ something = $ DBH-> prepare ($ SQL _statement );
My ($ first, $ last );

# The result is saved in $……-> execute () or die. The SQL statement cannot be executed:
$ DBH-> errstr "; $ something-> bind_columns (UNDEF, $ first, $ last );
My $ row; while ($ ROW = $ th-> fetchrow_arrayref ){
Print "$ first $ lastn ";
# Or
Print "$ row-> [0] $ row [1] n ";
}

The above program will list each row in the result and print the first name and last name. This is one of the fastest ways to extract data.

21. How to randomly extract records from the server?
A: Use the MySQL limit function.
$ Query = "select * from table ";
$ Something = $ DBH-> prepare ($ query );
$ Numrows = $ something-> execute;
$ Randomrow = int (rand ($ numrows ));
$ Something = $ DBH-> prepare ("$ query limit $ randomrow, 1 ");
$ Something-> execute;
@ Arr = $ something-> fetchrow;

22. After a record is inserted, how do I obtain the automatically added primary key value?
A: The insertid method is unique to MySQL and may not work on other SQL servers.

#! /Usr/bin/perl
# Connecting to the database ....
My $ SQL _statement = "insert into $ table (field1, field2) values ($ value1, $ value2 )";
My $ something = $ DBH-> prepare ($ SQL _statement );
$……> Execute or die: unable to add data:
$ DBH-> errstr ";

# Now we can retrieve the primary key generated after insertion.
My $ table_key = $ something-> {insertid };
# This method can also be used (Standard DBI method)
My $ table_key = $ DBH-> {'mysql _ insertid '};
$ Something-> finish;

23. After executing the SELECT query, how do I obtain the number of record rows?
A: There are several ways to do this:

# This method is not feasible in the document, but it is acceptable for me. You may also do it.
My $ mysql_q = "select field1, field2 from $ table where field1 = $ value1 ";
My $ something = $ DBH-> prepare ($ mysql_q );
My $ found = $ something-> execute or die "cannot be executed:
$ DBH-> errstr ";
$ Something-> finish;

# This is a slow method, and it is not reliable when performing select queries.
My $ SQL = Q (select * from $ table where field =? );
My $ something = $ DBH-> prepare ($ SQL );
$ Something-> execute ('$ value ');
My $ rows = $ th-> rows;
$ Something-> finish;

# This is a fast method.
My $ SQL = Q (select count (*) from $ table where field =? );
My $ something = $ DBH-> prepare ($ SQL); $ something-> execute ('$ value ');
My $ rows = $ th-> fetchrow_arrayref-> [0];
$ Something-> finish;

24. Why does the select last_insert_id (user_id) from user return all user IDs instead of the last one?
Answer: from the manual:

"The last Id created on the server is managed separately based on each connection. that is to say, it cannot be changed by another client. you even use a non-null or non-zero value to update another one.

Auto_increment fields will not be changed. If the formula is assigned to last_insert_id () as a parameter in the update statement, the parameter will return the value of last_insert_id ."

What you really need is: Select user_id from user order by user_id DESC limit 1

25. Can I use two conditions in the where statement?
A: Yes.

My $ SQL _statment = "select * from $ table where $ field1 = '$ value1' and $ field2 = '$ value2 '";

26. How can I find a keyword in multiple fields?
A: Try this:

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. How can I find the record created one week ago?
A: We need to use the date function for SQL query:

Date_add (date, interval expr type)
Date_sub (date, interval expr type)
Adddate (date, interval expr type)
Subdate (date, interval expr type)

Example: # This query statement returns all records whose ages are less than or equal to 7 days.

My $ SQL _q = "select * from $ database where date_add (create_date, interval 7 day)> = now () order by create_date DESC ";

28. How to retrieve the data of all fields and use "column_name" => value to put them into a related array?
A: use the $ something-> fetchrow_hashref method.

$ SQL = "select * from Members ";
My $ something = $ DBH-> prepare ($ SQL );
$ Something-> execute or die "SQL statement error ".
$ DBH-> errstr;
My $ record_hash;
While ($ record_hash = $ something-> fetchrow_hashref ){
Print "$ record_hash-> {first_name} $ record_hash-> {last_name} n ";
}
$ Something-> finish;

29. How to save an image file (JPG and GIF) to the database?

A:

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 );
$ Something = $ DBH-> prepare ($ SQL _statement );
$ Something-> execute (null, null, "Car | Sports", "Porsche Boxster S", "German excellence", $ gfx_file, null, "European

Car "," Y ");
$ Something-> finish (); $ something = $ DBH-> prepare ("select * from $ table ");
$ Something-> execute ();

While ($ ref = $ something-> fetchrow_hashref ()){
Print "id = $ ref-> {'id'} tcategory = $ ref-> {'category '} tcaption = $ ref-> {'caption'} n ";
}

$ Numrows = $ something-> rows;
$ Something-> 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 );
$ Something = $ DBH-> prepare ("select * from $ table where id = 1 ");
$ Something-> execute ();
$ Ref = $ something-> fetchrow_hashref ();
Print "Content-Type: image/jpgnn ";
Print $ ref-> {'picture1 '};
$ Numrows = $ something-> rows;
$ Something-> finish ();
$ DBH-> disconnect ();

30. How to insert N records?
A:

# Let's insert 10000 records
My $ rec_num = 10000;
My $ product_tb = "Products ";
My $ DBH = DBI-> connect ($ database, $ db_user, $ db_password) or die "cannot connect to database N ";
My $ something = $ 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 = revert 350;
My $ DESC = "desccription of product $ I ";
My $ PIC = "images/product". $ I. ". jpg ";
$ Something-> execute ($ name, $ price, $ DESC, $ pic );
}
$ Something-> finish ();
Print "insert $ rec_num records to the table $ product_tbn ";
$ DBH-> disconnect;
Exit;

31. How to Create a date field so that its default value is the date when the new record is created?
A: There are many ways to do this:

(1) Use Timestamp

Create Table mytable (table_id int not null auto_increment,
Value varchar (25 ),
Date timestamp (14 ),
Primary Key (table_id ));

When a record is inserted or updated, the timestamp field is automatically set to the current date. if you do not want to change the date when updating, you can use the update statement to set the date field to the original

Input date ).

(2) Use the now () function.

Create Table mytable (table_id int not null auto_increment,
Value varchar (25 ),
Date,
Primary Key (table_id ));

Set date = now () in the insert statement ().