MySQL查询数据---单表查询

单表查询指从一张表数据中查询所需的数据。
(1)查询所有字段
(2)查询指定字段
(3)查询指定记录
(4)带in关键字的查询
(5)带between and的范围的查询
(6)带like的字符匹配查询
(7)查询空值
(8)带and的多条件查询
(9)带or的多条件查询
(10)查询结果不重复
(11)对查询结果排序
(12)分组查询
(13)使用limit限制查询结果的数量

---

(1)查询所有字段

1.select * from fruits
2.select f_id,s_id,f_name,f_price from fruits;

上面两个语句执行后的结果都是查询所有字段:

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| 12   |  104 | lemon      |    6.40 |
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbabay     |    3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)

(2)查询指定字段

1.查询单个字段:select 列名 from 表名;

【例】查询fruits表中f_name列所有水果名称,SQL语句如下:

mysql> select f_name from fruits;
+------------+
| f_name     |
+------------+
| lemon      |
| apple      |
| apricot    |
| blackberry |
| berry      |
| xxxx       |
| orange     |
| melon      |
| cherry     |
| mango      |
| xbabay     |
| xxtt       |
| coconut    |
| banana     |
| grape      |
| xbabay     |
+------------+
16 rows in set (0.00 sec)

2.查询多个字段:select 字段名1,字段名2,...字段名n from 表名;
【例】从fruits表中获取名称为f_name和f_price两列,SQL语句如下:

mysql> select f_name,f_price from fruits;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| lemon      |    6.40 |
| apple      |    5.20 |
| apricot    |    2.20 |
| blackberry |   10.20 |
| berry      |    7.60 |
| xxxx       |    3.60 |
| orange     |   11.20 |
| melon      |    8.20 |
| cherry     |    3.20 |
| mango      |   15.70 |
| xbabay     |    2.60 |
| xxtt       |   11.60 |
| coconut    |    9.20 |
| banana     |   10.30 |
| grape      |    5.30 |
| xbabay     |    3.60 |
+------------+---------+
16 rows in set (0.00 sec)

(3)查询指定记录

select 字段名1，字段名2，。。。，字段名n
from 表名
where 查询条件

【例1】查询价格为10.2元的水果名称，SQL语句如下：

mysql> select f_name,f_price
    -> from fruits
    -> where f_price = 10.2;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| blackberry |   10.20 |
+------------+---------+
1 row in set (0.00 sec)

【例2】查找名称为"apple"的水果价格，SQL语句如下：

mysql> select f_name,f_price
    -> from fruits
    -> where f_name = 'apple';
+--------+---------+
| f_name | f_price |
+--------+---------+
| apple  |    5.20 |
+--------+---------+
1 row in set (0.00 sec)

【例3】查询价格小于10的水果名称，SQL语句如下：

mysql> select f_name,f_price
    -> from fruits
    -> where f_price<10.00;
+---------+---------+
| f_name  | f_price |
+---------+---------+
| lemon   |    6.40 |
| apple   |    5.20 |
| apricot |    2.20 |
| berry   |    7.60 |
| xxxx    |    3.60 |
| melon   |    8.20 |
| cherry  |    3.20 |
| xbabay  |    2.60 |
| coconut |    9.20 |
| grape   |    5.30 |
| xbabay  |    3.60 |
+---------+---------+
11 rows in set (0.00 sec)

(4)带in关键字的查询

• in操作符用来查询满足指定范围内的条件的记录，使用in操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

【例1】s_id为101和102的记录，SQL语句如下;

mysql> select s_id,f_name,f_price
    -> from fruits
    -> where s_id in(101,102)
    -> order by f_name;
+------+------------+---------+
| s_id | f_name     | f_price |
+------+------------+---------+
|  101 | apple      |    5.20 |
|  102 | banana     |   10.30 |
|  101 | blackberry |   10.20 |
|  101 | cherry     |    3.20 |
|  102 | grape      |    5.30 |
|  102 | orange     |   11.20 |
+------+------------+---------+
6 rows in set (0.00 sec)

【例2】查询所有s_id既不等于101也不等于102的记录，SQL语句如下：

mysql> select s_id,f_name,f_price
    -> from fruits
    -> where s_id not in (101,102)
    -> order by f_name;
+------+---------+---------+
| s_id | f_name  | f_price |
+------+---------+---------+
|  103 | apricot |    2.20 |
|  104 | berry   |    7.60 |
|  103 | coconut |    9.20 |
|  104 | lemon   |    6.40 |
|  106 | mango   |   15.70 |
|  105 | melon   |    8.20 |
|  105 | xbabay  |    2.60 |
|  107 | xbabay  |    3.60 |
|  105 | xxtt    |   11.60 |
|  107 | xxxx    |    3.60 |
+------+---------+---------+
10 rows in set (0.00 sec)

(5)带between and的范围的查询

• between and 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值。

【例1】查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下：

mysql> select f_name,f_price from fruits where f_price between 2.00 and 10.20;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| lemon      |    6.40 |
| apple      |    5.20 |
| apricot    |    2.20 |
| blackberry |   10.20 |
| berry      |    7.60 |
| xxxx       |    3.60 |
| melon      |    8.20 |
| cherry     |    3.20 |
| xbabay     |    2.60 |
| coconut    |    9.20 |
| grape      |    5.30 |
| xbabay     |    3.60 |
+------------+---------+
12 rows in set (0.00 sec)

【例2】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下;

mysql> select f_name,f_price
    -> from fruits
    -> where f_price not between 2.00 and 10.20;
+--------+---------+
| f_name | f_price |
+--------+---------+
| orange |   11.20 |
| mango  |   15.70 |
| xxtt   |   11.60 |
| banana |   10.30 |
+--------+---------+
4 rows in set (0.00 sec)

(6)带like的字符匹配查询

1.%通配符，匹配任意长度的字符，甚至包括零字符。
【例1】查找所有以"b"字母开头的水果，SQL语句如下：

mysql> select f_id,f_name
    -> from fruits
    -> where f_name like 'b%';
+------+------------+
| f_id | f_name     |
+------+------------+
| b1   | blackberry |
| b2   | berry      |
| t1   | banana     |
+------+------------+
3 rows in set (0.00 sec)

【例2】在fruits表中，查询f_name中包含字母"g"的记录，SQL语句如下：

mysql> select f_id,f_name
    -> from fruits
    -> where f_name like '%g%';
+------+--------+
| f_id | f_name |
+------+--------+
| bs1  | orange |
| m1   | mango  |
| t2   | grape  |
+------+--------+
3 rows in set (0.00 sec)

【例3】查询以"b"开头，并以‘y’结尾的水果的名称，SQL语句如下：

mysql> select f_id,f_name
    -> from fruits
    -> where f_name like 'b%y';
+------+------------+
| f_id | f_name     |
+------+------------+
| b1   | blackberry |
| b2   | berry      |
+------+------------+
2 rows in set (0.00 sec)

2._通配符，一次只能匹配任意一个字符。

【例】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录，SQL语句如下：

mysql> select f_id,f_name
    -> from fruits
    -> where f_name like '____y';
+------+--------+
| f_id | f_name |
+------+--------+
| b2   | berry  |
+------+--------+
1 row in set (0.00 sec)

(7)查询空值

• 数据表创建时，可以指定某列中是否可以包含空值null。空值不同于0，也不同于空字符串。空值一般表示数据位置、不使用或将在以后添加数据。在select语句中使用is null子句，可以查询某字段内容为空记录。

下面创建数据表customers来演示：

create table customers
(
	c_id int not null auto_increment,
	c_name char(50) not null,
	c_address char(50) null,
	c_city char(50) null,
	c_zip char(10) null,
	c_contact char(50) null,
	c_email char(255) null,
	primary key (c_id)
	);

插入下列语句:

mysql> insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)
    -> values
    -> (10001,'redhool','200 Street ','Tianjin','300000','LiMing','LMing@163.com'),
    -> (10002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotmail.com'),
    -> (10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',null),
    -> (10004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

【例1】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：

mysql> select c_id,c_name,c_email from customers where c_email is null;
+-------+----------+---------+
| c_id  | c_name   | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL    |
+-------+----------+---------+
1 row in set (0.00 sec)

【例2】查询customers表中c_email不为空的记录的c_id、c_name、和c_email字段值，SQL语句如下：

mysql> select c_id,c_name,c_email
    -> from customers
    -> where c_email is not null;
+-------+---------+-------------------+
| c_id  | c_name  | c_email           |
+-------+---------+-------------------+
| 10001 | redhool | LMing@163.com     |
| 10002 | Stars   | Jerry@hotmail.com |
| 10004 | JOTO    | sam@hotmail.com   |
+-------+---------+-------------------+
3 rows in set (0.00 sec)

(8)带and的多条件查询

【例1】在fruits表中查询s_id=101，且f_price大于5的水果的价格和名称，SQL语句如下：

mysql> select f_id,f_price,f_name
    -> from fruits
    -> where s_id = '101' and f_price>=5;
+------+---------+------------+
| f_id | f_price | f_name     |
+------+---------+------------+
| a1   |    5.20 | apple      |
| b1   |   10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.00 sec)

【例2】在fruits表中查询s_id=101或者102,且f_price大于5,且f_name='apple’的水果价格和名称，SQL语句如下：

mysql> select f_id ,f_price , f_name
    -> from fruits
    -> where s_id in ('101','102') and f_price >=5 and f_name = 'apple';
+------+---------+--------+
| f_id | f_price | f_name |
+------+---------+--------+
| a1   |    5.20 | apple  |
+------+---------+--------+
1 row in set (0.05 sec)

(9)带or的多条件查询

【例1】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：

mysql> select s_id,f_name,f_price
    -> from fruits
    -> where s_id=101 or s_id=102;
+------+------------+---------+
| s_id | f_name     | f_price |
+------+------------+---------+
|  101 | apple      |    5.20 |
|  101 | blackberry |   10.20 |
|  102 | orange     |   11.20 |
|  101 | cherry     |    3.20 |
|  102 | banana     |   10.30 |
|  102 | grape      |    5.30 |
+------+------------+---------+
6 rows in set (0.00 sec)

【例2】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：

mysql> select s_id,f_name,f_price
    -> from fruits
    -> where s_id in(101,102);
+------+------------+---------+
| s_id | f_name     | f_price |
+------+------------+---------+
|  101 | apple      |    5.20 |
|  101 | blackberry |   10.20 |
|  102 | orange     |   11.20 |
|  101 | cherry     |    3.20 |
|  102 | banana     |   10.30 |
|  102 | grape      |    5.30 |
+------+------------+---------+
6 rows in set (0.00 sec

• 注意
  or可以和and一起使用，但是要注意两者的优先级，由于and的优先级高于or，旖旎次先对and两边的操作数进行操作，再与or中的操作数结合。

(10)查询结果不重复

• 语法格式：select distinct 字段名 from 表名；

【例】查询fruits表中s_id字段的值，返回s_id字段值且不得重复,SQL语句如下;

mysql> select distinct s_id from fruits;
+------+
| s_id |
+------+
|  104 |
|  101 |
|  103 |
|  107 |
|  102 |
|  105 |
|  106 |
+------+
7 rows in set (0.05 sec)

(11)对查询结果排序

1.单列排序order by

【例】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：

mysql> select f_name from fruits order by f_name;
+------------+
| f_name     |
+------------+
| apple      |
| apricot    |
| banana     |
| berry      |
| blackberry |
| cherry     |
| coconut    |
| grape      |
| lemon      |
| mango      |
| melon      |
| orange     |
| xbabay     |
| xbabay     |
| xxtt       |
| xxxx       |
+------------+
16 rows in set (0.00 sec)

2.多列排序

• 多列排序首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

【例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：

mysql> select f_name,f_price
    -> from fruits
    -> order by f_name, f_price;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| apple      |    5.20 |
| apricot    |    2.20 |
| banana     |   10.30 |
| berry      |    7.60 |
| blackberry |   10.20 |
| cherry     |    3.20 |
| coconut    |    9.20 |
| grape      |    5.30 |
| lemon      |    6.40 |
| mango      |   15.70 |
| melon      |    8.20 |
| orange     |   11.20 |
| xbabay     |    2.60 |
| xbabay     |    3.60 |
| xxtt       |   11.60 |
| xxxx       |    3.60 |
+------------+---------+
16 rows in set (0.00 sec)

3.指定排序方向desc

• desc是降序排列，与之对应的是asc升序排列，但asc是默认的，可以不加。

【例1】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：

mysql> select f_name,f_price
    -> from fruits
    -> order by f_price desc;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| mango      |   15.70 |
| xxtt       |   11.60 |
| orange     |   11.20 |
| banana     |   10.30 |
| blackberry |   10.20 |
| coconut    |    9.20 |
| melon      |    8.20 |
| berry      |    7.60 |
| lemon      |    6.40 |
| grape      |    5.30 |
| apple      |    5.20 |
| xxxx       |    3.60 |
| xbabay     |    3.60 |
| cherry     |    3.20 |
| xbabay     |    2.60 |
| apricot    |    2.20 |
+------------+---------+
16 rows in set (0.00 sec)

【例2】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：

mysql> select f_price,f_name
    -> from fruits
    -> order by f_price desc,f_name;
+---------+------------+
| f_price | f_name     |
+---------+------------+
|   15.70 | mango      |
|   11.60 | xxtt       |
|   11.20 | orange     |
|   10.30 | banana     |
|   10.20 | blackberry |
|    9.20 | coconut    |
|    8.20 | melon      |
|    7.60 | berry      |
|    6.40 | lemon      |
|    5.30 | grape      |
|    5.20 | apple      |
|    3.60 | xbabay     |
|    3.60 | xxxx       |
|    3.20 | cherry     |
|    2.60 | xbabay     |
|    2.20 | apricot    |
+---------+------------+
16 rows in set (0.00 sec)

(12)分组查询

• 分组查询是对数据按照某个或多个字段进行分组，MySQL中使用group by 关键字对数据进行分组，基本语法形式为：[group by 字段][having<条件表达式>]
• 字段值为进行分组时所依据的列名称，"having<条件表达式>"指定满足表达式限定条件的结果将被显示。

1.创建分组

【例1】根据s_id对fruits表中的数据进行分组，SQL语句如下;

mysql> select s_id,count(*) as total
    -> from fruits
    -> group by s_id;
+------+-------+
| s_id | total |
+------+-------+
|  104 |     2 |
|  101 |     3 |
|  103 |     2 |
|  107 |     2 |
|  102 |     3 |
|  105 |     3 |
|  106 |     1 |
+------+-------+
7 rows in set (0.05 sec)

可以看到group by 子句按照s_id排序并对数据分组。

• 如果需要查看每个供应商提供的水果种类名称，可以在group by子句中使用group_concat()函数，将每个分组中各个字段的值显示出来。

【例2】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来，SQL语句如下：

mysql> select s_id,group_concat(f_name) as Names
    -> from fruits
    -> group by s_id;
+------+-------------------------+
| s_id | Names                   |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | lemon,berry             |
|  105 | melon,xbabay,xxtt       |
|  106 | mango                   |
|  107 | xxxx,xbabay             |
+------+-------------------------+
7 rows in set (0.05 sec)

2.使用having过滤分组

• groub by可以和having一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
• having和where都是用来过滤数据的，having在数据分组之后进行过滤来选择分组，而where在分组之前用来选择记录。where排除的记录不再包括在分组中。

【例】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：

mysql> select s_id,group_concat(f_name) as Names
    -> from fruits
    -> group by s_id having count(f_name) >1;
+------+-------------------------+
| s_id | Names                   |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | lemon,berry             |
|  105 | melon,xbabay,xxtt       |
|  107 | xxxx,xbabay             |
+------+-------------------------+
6 rows in set (0.00 sec)

3.在group by 子句中使用with rollup

• 使用with rolluo关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

【例】根据s_id对fruits表中的数据进行分组，并显示记录数量，SQL语句如下：

mysql> select s_id,count(*) as Total
    -> from fruits
    -> group by s_id with rollup;
+------+-------+
| s_id | Total |
+------+-------+
|  101 |     3 |
|  102 |     3 |
|  103 |     2 |
|  104 |     2 |
|  105 |     3 |
|  106 |     1 |
|  107 |     2 |
| NULL |    16 |
+------+-------+
8 rows in set (0.05 sec)

4.多字段分组

• 使用group by可以对多个字段进行分组，group by 关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，以此类推。

【例】根据s_id和f_name字段对fruits表中的数据进行分组，SQL语句如下：

mysql> select * from fruits group by s_id,f_name;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| 12   |  104 | lemon      |    6.40 |
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbabay     |    3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)

5.group by 和order by一起使用

• 某些情况下需要对分组进行排序，order by用来对查询的记录排序，如果和group by一起使用可以完成对分组的排序。

创建数据表演示：

mysql> create table orderitems
    -> (
    -> o_num int not null,
    -> o_item int not null,
    -> f_id char(10) not null,
    -> quantity int not null,
    -> item_price decimal(8,2) not null,
    -> primary key (o_num,o_item)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into orderitems(o_num,o_item,f_id,quantity,item_price)
    -> values(30001,1,'a1',10,5.2),
    -> (30001,2,'b2',3,7.6),
    -> (30001,3,'bs1',5,11.2),
    -> (30001,4,'bs2',15,9.2),
    -> (30002,1,'b3',2,20.0),
    -> (30003,1,'c0',100,10),
    -> (30004,1,'o2',50,2.50),
    -> (30005,1,'c0',5,10),
    -> (30005,2,'b1',10,8.99),
    -> (30005,3,'a2',10,2.2),
    -> (30005,4,'m1',5,14.99);
Query OK, 11 rows affected (0.06 sec)
Records: 11  Duplicates: 0  Warnings: 0

【例】查询订单价格大于100的订单号和总订单价格，SQL语句如下;

mysql> select o_num,sum(quantity * item_price) as orderTotal
    -> from orderitems
    -> group by o_num
    -> having sum(quantity * item_price) >=100;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30001 |     268.80 |
| 30003 |    1000.00 |
| 30004 |     125.00 |
| 30005 |     236.85 |
+-------+------------+
4 rows in set (0.00 sec)

可以看到orderTotal列的总订单价格并没有按照一定的顺序显示，接下来使用order by关键字按总订单价格排列显示结果，SQL语句如下：

mysql> select o_num,sum(quantity * item_price) as orderTotal
    -> from orderitems
    -> group by o_num
    -> having sum(quantity * item_price)>=100
    -> order by orderTotal;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30004 |     125.00 |
| 30005 |     236.85 |
| 30001 |     268.80 |
| 30003 |    1000.00 |
+-------+------------+
4 rows in set (0.00 sec)

可以看到，group by 子句按订单号对数据进行分组，sum()函数便可以返回总的订单价格，having子句对分组数据进行过滤，使得只返回总价格大于100的订单，最后使用order by子句排序输出。

(13)使用limit限制查询结果的数量

select返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用limit关键字，基本语法如下：

limit [位置偏移量，] 行数

• 第一个"位置偏移量"参数只是MySQL从哪一行开始显示，是一个可选参数，如果不指定"位置偏移量",将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1,以此类推);第二个参数"行数指示返回的记录条数"。

【例1】显示fruits表查询结果的前4行，SQL语句如下：

mysql> select * from fruits limit 4;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| 12   |  104 | lemon      |    6.40 |
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
+------+------+------------+---------+
4 rows in set (0.00 sec)

【例2】在fruits表中，使用limit子句，返回从第5个记录开始的、行数长度为3的记录，SQL语句如下：

mysql> select * from fruits limit 4,3;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2   |  104 | berry  |    7.60 |
| b5   |  107 | xxxx   |    3.60 |
| bs1  |  102 | orange |   11.20 |
+------+------+--------+---------+
3 rows in set (0.00 sec)

也可以使用"limit 4 offset 3"也是获取从第5条记录开始后面的3条记录。