mysql limit where_mysql select ，where，limit查询

1、使用查询where、limit

where：查询条件

limit：限制查询结果显示的数量

题目：查询students表中age<25的数据，并显示这些数据的id 、name、age

mysql>select id,name,age from students where  age <25  limit  2;

+----+------+-----+

| id | name | age |

+----+------+-----+

|  1 | lili |  12 |

|  3 | jujo |  21 |

+----+------+-----+

2 rows in set (0.00 sec)

2、使用where

题目：查找students表中name="lili"的所有数据

mysql> select * from students where name ="lili";

+----+------+-----+-----+-------------+

| id | name | sex | age | tel        |

+----+------+-----+-----+-------------+

|  1 | lili | f  |  12 | 13501665963 |

|  7 | lili | f  |  11 | 15110021003 |

+----+------+-----+-----+-------------+

2 rows in set (0.00 sec)

3、使用BINARY 区分大小写(只会查询出大写或者小写的数据)：

题目：查询出students表name=“lili”的所有数据

mysql> select * from students where  BINARY  name ="lili";

+----+------+-----+-----+-------------+

| id | name | sex | age | tel        |

+----+------+-----+-----+-------------+

|  1 | lili | f  |  12 | 13501665963 |

|  7 | lili | f  |  11 | 15110021003 |

+----+------+-----+-----+-------------+

2 rows in set (0.00 sec)

没有BINARY 查询出所有name为lili的数据(包括大小写在内)

题目：查询students表name="lili"的所有数据

mysql> select * from studentswherename ="lili";

+----+------+-----+-----+-------------+

| id | name | sex | age | tel        |

+----+------+-----+-----+-------------+

|  1 | lili | f  |  12 | 13501665963 |

|  7 | lili | f  |  11 | 15110021003 |

|  8 | LiLi | f  |  10 | 18710051006 |

+----+------+-----+-----+-------------+

3 rows in set (0.00 sec)

mysql> select*from students whereBINARYname ="LiLi";

+----+------+-----+-----+-------------+

| id | name | sex | age | tel        |

+----+------+-----+-----+-------------+

|  8 | LiLi | f  |  10 | 18710051006 |

+----+------+-----+-----+-------------+

1 row in set (0.00 sec)

4、limit和offset

①limit后面跟的是2条数据，offset后面是从第1条开始读取

mysql> select*from master where id >10  limit  2 offset 1;

②limit后面是从第2条开始读，读取1条信息

mysql> select*from master where id >10limit 2,1;