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;