MySQL的Limit子句的使用，以及相关例题

        MySQL特有的子句。它是SELECT语句中的最后一个子句（在order by后面）。它用来表示从结果集中选取最前面或最后面的几行。偏移量offset的最小值为0。

        limit  <获取的行数> [OFFSET <跳过的行数>]或者  limit [<跳过的行数>,] <获取的行数> 

        有两个表如下：

root@TENNIS 17:32  mysql>select * from PENALTIES;
+-----------+----------+--------------+--------+
| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |
+-----------+----------+--------------+--------+
|         1 |        6 | 1980-12-08   | 100.00 |
|         2 |       44 | 1981-05-05   |  75.00 |
|         3 |       27 | 1983-09-10   | 100.00 |
|         4 |      104 | 1984-12-08   |  50.00 |
|         5 |       44 | 1980-12-08   |  25.00 |
|         6 |        8 | 1980-12-08   |  25.00 |
|         7 |       44 | 1982-12-30   |  30.00 |
|         8 |       27 | 1984-11-12   |  75.00 |
+-----------+----------+--------------+--------+
8 rows in set (0.00 sec)
root@TENNIS 17:32  mysql>select * from PLAYERS;
+----------+-----------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
| PLAYERNO | NAME      | INITIALS | BIRTH_DATE | SEX | JOINED | STREET         | HOUSENO | POSTCODE | TOWN      | PHONENO    | LEAGUENO |
+----------+-----------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
|        2 | Everett   | R        | 1948-09-01 | M   |   1975 | Stoney Road    | 43      | 3575NH   | Stratford | 070-237893 | 2411     |
|        6 | Parmenter | R        | 1964-06-25 | M   |   1977 | Haseltine Lane | 80      | 1234KK   | Stratford | 070-476537 | 8467     |
|        7 | Wise      | GWS      | 1963-05-11 | M   |   1981 | Edgecombe Way  | 39      | 9758VB   | Stratford | 070-347689 | NULL     |
|        8 | Newcastle | B        | 1962-07-08 | F   |   1980 | Station Road   | 4       | 6584WO   | Inglewood | 070-458458 | 2983     |
|       27 | Collins   | DD       | 1964-12-28 | F   |   1983 | Long Drive     | 804     | 8457DK   | Eltham    | 079-234857 | 2513     |
|       28 | Collins   | C        | 1963-06-22 | F   |   1983 | Old Main Road  | 10      | 1294QK   | Midhurst  | 010-659599 | NULL     |
|       39 | Bishop    | D        | 1956-10-29 | M   |   1980 | Eaton Square   | 78      | 9629CD   | Stratford | 070-393435 | NULL     |
|       44 | Baker     | E        | 1963-01-09 | M   |   1980 | Lewis Street   | 23      | 4444LJ   | Inglewood | 070-368753 | 1124     |
|       57 | Brown     | M        | 1971-08-17 | M   |   1985 | Edgecombe Way  | 16      | 4377CB   | Stratford | 070-473458 | 6409     |
|       83 | Hope      | PK       | 1956-11-11 | M   |   1982 | Magdalene Road | 16A     | 1812UP   | Stratford | 070-353548 | 1608     |
|       95 | Miller    | P        | 1963-05-14 | M   |   1972 | High Street    | 33A     | 5746OP   | Douglas   | 070-867564 | NULL     |
|      100 | Parmenter | P        | 1963-02-28 | M   |   1979 | Haseltine Lane | 80      | 6494SG   | Stratford | 070-494593 | 6524     |
|      104 | Moorman   | D        | 1970-05-10 | F   |   1984 | Stout Street   | 65      | 9437AO   | Eltham    | 079-987571 | 7060     |
|      112 | Bailey    | IP       | 1963-10-01 | F   |   1984 | Vixen Road     | 8       | 6392LK   | Plymouth  | 010-548745 | 1319     |
+----------+-----------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
14 rows in set (0.00 sec)

例题：

1、得到编号最大的前4个球员的编号和名字 

root@TENNIS 17:35  mysql>SELECT  playerno, name   FROM  PLAYERS   ORDER BY  playerno DESC   LIMIT  4;
+----------+-----------+
| playerno | name      |
+----------+-----------+
|      112 | Bailey    |
|      104 | Moorman   |
|      100 | Parmenter |
|       95 | Miller    |
+----------+-----------+
4 rows in set (0.00 sec)

        SELECT  playerno, name   FROM  PLAYERS   ORDER BY  playerno DESC   LIMIT  4;

         挑选字段，playerno和name，从PLAYERS这个表里面读取，ORDER BY 是一个用来排序的，desc是降序，asc是升序，默认也是升序，limit 4 在筛选出来的里面限制4个，也就是只取前面4个。

2、得到球员编号最低的5个球员的编号和名字，从第4个球员开始

 

root@TENNIS 17:40  mysql>SELECT playerno,  name   FROM PLAYERS   ORDER BY playerno ASC   LIMIT  3, 5;
+----------+-----------+
| playerno | name      |
+----------+-----------+
|        8 | Newcastle |
|       27 | Collins   |
|       28 | Collins   |
|       39 | Bishop    |
|       44 | Baker     |
+----------+-----------+
5 rows in set (0.00 sec)

        SELECT playerno,  name   FROM PLAYERS   ORDER BY playerno ASC   LIMIT  3, 5; 

        其他的跟上面差不多，limit 3，5  的意思是从第四个开始，往后面取5个，limit的偏移量是从0开始的，所以第4个开始是 limit 3，5 。

3、4个最低的罚款额的平均值是多少？

root@TENNIS 17:46  mysql>SELECT avg(amount)   FROM (SELECT amount           FROM PENALTIES       ORDER BY amount          LIMIT 4 ) a;
+-------------+
| avg(amount) |
+-------------+
|   32.500000 |
+-------------+
1 row in set (0.00 sec)