2.Retrieve
①SELECT列
1>全列查询
在日常开发中不建议使用全列查询,因为数据会很多
mysql> create table result(
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null comment '同学姓名',
-> chinese float default 0.0 comment '语文成绩',
-> math float default 0.0 comment '数学成绩',
-> english float default 0.0 comment '英语成绩'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc result;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| chinese | float | YES | | 0 | |
| math | float | YES | | 0 | |
| english | float | YES | | 0 | |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into result (name,chinese,math,english) values
-> ('唐三藏',67,98,56),
-> ('孙悟空',87,78,77),
-> ('猪悟能',88,98,90),
-> ('曹孟德',82,84,67),
-> ('刘玄德',55,85,45),
-> ('孙权',70,73,78),
-> ('宋公明',75,65,30);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
2>指定列查询
mysql> select id from result;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
mysql> select id,math from result;
+----+------+
| id | math |
+----+------+
| 1 | 98 |
| 2 | 78 |
| 3 | 98 |
| 4 | 84 |
| 5 | 85 |
| 6 | 73 |
| 7 | 65 |
+----+------+
7 rows in set (0.00 sec)
mysql> select id,math,name from result;
+----+------+-----------+
| id | math | name |
+----+------+-----------+
| 1 | 98 | 唐三藏 |
| 2 | 78 | 孙悟空 |
| 3 | 98 | 猪悟能 |
| 4 | 84 | 曹孟德 |
| 5 | 85 | 刘玄德 |
| 6 | 73 | 孙权 |
| 7 | 65 | 宋公明 |
+----+------+-----------+
7 rows in set (0.00 sec)
3>查询字段为表达式
mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
mysql> select 7*8;
+-----+
| 7*8 |
+-----+
| 56 |
+-----+
1 row in set (0.00 sec)
mysql> select id,math,10 from result;
+----+------+----+
| id | math | 10 |
+----+------+----+
| 1 | 98 | 10 |
| 2 | 78 | 10 |
| 3 | 98 | 10 |
| 4 | 84 | 10 |
| 5 | 85 | 10 |
| 6 | 73 | 10 |
| 7 | 65 | 10 |
+----+------+----+
7 rows in set (0.00 sec)
mysql> select id,math,1+1 from result;
+----+------+-----+
| id | math | 1+1 |
+----+------+-----+
| 1 | 98 | 2 |
| 2 | 78 | 2 |
| 3 | 98 | 2 |
| 4 | 84 | 2 |
| 5 | 85 | 2 |
| 6 | 73 | 2 |
| 7 | 65 | 2 |
+----+------+-----+
7 rows in set (0.00 sec)
mysql> select name,math+chinese+english from result;
+-----------+----------------------+
| name | math+chinese+english |
+-----------+----------------------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+----------------------+
7 rows in set (0.00 sec)
4>为查询结构指定别名
-- 列重命名
mysql> select name,math+chinese+english as total from result;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
-- as也可以省略
mysql> select name,math+chinese+english total from result;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
-- 多列重命名
mysql> select name 姓名,math+chinese+english 总分 from result;
+-----------+--------+
| 姓名 | 总分 |
+-----------+--------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+--------+
7 rows in set (0.00 sec)
5>结果去重
mysql> select math from result;
+------+
| math |
+------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
7 rows in set (0.00 sec)
mysql> select distinct math from result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
6 rows in set (0.00 sec)
②WHERE条件
比较运算符
运算符 | 说明 |
| 大于,大于等于,小于,小于等于 |
| 等于, |
| 等于, |
| 不等于 |
| 范围匹配, |
| 如果是option中的任意一个,返回 |
| 是 |
| 不是 |
| 模糊匹配, |
逻辑运算符
运算符 | 说明 |
| 多个条件必须都为 |
| 任意一个条件为 |
| 条件为 |
示例
英语不及格的同学及成绩(<60)
mysql> select name,english from result where english<60;
+-----------+---------+
| name | english |
+-----------+---------+
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
+-----------+---------+
3 rows in set (0.00 sec)
语文成绩在[80,90]分的同学及语文成绩
-- and
mysql> select name,chinese from result where chinese>=80 and chinese<=90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
-- between
mysql> select name,chinese from result where chinese between 80 and 90;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
数学成绩是58/59/98/99的同学及数学成绩
-- or
mysql> select name,math from result where math=58 or math=59 or math=98 or math=99;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
2 rows in set (0.00 sec)
-- in
mysql> select name,math from result where math in (58,59,98,99);
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
2 rows in set (0.00 sec)
姓孙的同学及孙某同学
-- 模糊匹配——like
-- 含有孙的,匹配任意多个(包括0个)任意字符
mysql> select name from result where name like '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
2 rows in set (0.00 sec)
-- 孙X,匹配严格的一个任意字符
mysql> select name from result where name like '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+
1 row in set (0.00 sec)
语文成绩好于英语成绩的同学
mysql> select name,chinese,english from result where chinese > english;
+-----------+---------+---------+
| name | chinese | english |
+-----------+---------+---------+
| 唐三藏 | 67 | 56 |
| 孙悟空 | 87 | 77 |
| 曹孟德 | 82 | 67 |
| 刘玄德 | 55 | 45 |
| 宋公明 | 75 | 30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)
总分在200以下的同学
mysql> select name,chinese+math+english from result;
+-----------+----------------------+
| name | chinese+math+english |
+-----------+----------------------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+----------------------+
7 rows in set (0.00 sec)
-- 筛选
mysql> select name,chinese+math+english from result where chinese+math+english < 200;
+-----------+----------------------+
| name | chinese+math+english |
+-----------+----------------------+
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+----------------------+
2 rows in set (0.00 sec)
-- 如果进行重命名然后用重命名后的名字代替会报错
-- 先执行的from result,然后执行where 条件,最后执行select,才会进行重命名
mysql> select name,chinese+math+english total from result where total < 200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
语文成绩>80并且不姓孙的同学
-- 语文大于80
mysql> select name,chinese from result where chinese>80;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
3 rows in set (0.00 sec)
-- 姓孙的
mysql> select name,chinese from result where name like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 孙悟空 | 87 |
| 孙权 | 70 |
+-----------+---------+
2 rows in set (0.00 sec)
-- 语文大于80且不姓孙
mysql> select name,chinese from result where chinese >80 and name not like '孙%';
+-----------+---------+
| name | chinese |
+-----------+---------+
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+-----------+---------+
2 rows in set (0.00 sec)
孙某同学,否则总成绩>200且语文<数学且英语>80
mysql> select name,chinese,math,english,chinese+math+english 总分 from result;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 唐三藏 | 67 | 98 | 56 | 221 |
| 孙悟空 | 87 | 78 | 77 | 242 |
| 猪悟能 | 88 | 98 | 90 | 276 |
| 曹孟德 | 82 | 84 | 67 | 233 |
| 刘玄德 | 55 | 85 | 45 | 185 |
| 孙权 | 70 | 73 | 78 | 221 |
| 宋公明 | 75 | 65 | 30 | 170 |
+-----------+---------+------+---------+--------+
7 rows in set (0.00 sec)
mysql> select name,chinese,math,english,chinese+math+english 总分 from result where chinese+math+english>200;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 唐三藏 | 67 | 98 | 56 | 221 |
| 孙悟空 | 87 | 78 | 77 | 242 |
| 猪悟能 | 88 | 98 | 90 | 276 |
| 曹孟德 | 82 | 84 | 67 | 233 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
5 rows in set (0.00 sec)
mysql> select name,chinese,math,english,chinese+math+english 总分 from result where chinese+math+english>200 and chinese<math;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 唐三藏 | 67 | 98 | 56 | 221 |
| 猪悟能 | 88 | 98 | 90 | 276 |
| 曹孟德 | 82 | 84 | 67 | 233 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
4 rows in set (0.00 sec)
mysql> select name,chinese,math,english,chinese+math+english 总分 from result where chinese+math+english>200 and chinese<math and english>80;
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
+-----------+---------+------+---------+--------+
1 row in set (0.00 sec)
mysql> select name,chinese,math,english,chinese+math+english 总分 from result where name like '孙_';
+--------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+--------+---------+------+---------+--------+
| 孙权 | 70 | 73 | 78 | 221 |
+--------+---------+------+---------+--------+
1 row in set (0.00 sec)
mysql> select name,chinese,math,english,chinese+math+english 总分 from result where name like '孙_' or (chinese+math+engglish>200 and chinese<math and english>80);
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)
NULL的查询
mysql> create table test(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test (id,name) values (1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,name) values (NULL,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,name) values (1,null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,name) values (null,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | NULL |
| NULL | NULL |
+------+--------+
4 rows in set (0.00 sec)
mysql> select * from test where name is null;
+------+------+
| id | name |
+------+------+
| 1 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
-- 插入空字符串
mysql> insert into test (id,name) values (1,'');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | NULL |
| NULL | NULL |
| 1 | |
+------+--------+
5 rows in set (0.00 sec)
-- null和空字符串不一样
mysql> select * from test where name is null;
+------+------+
| id | name |
+------+------+
| 1 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test where name='';
+------+------+
| id | name |
+------+------+
| 1 | |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test where name is not null;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | |
+------+--------+
3 rows in set (0.00 sec)
③结果排序
没有 ORDER BY
子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
ASC 升序(从小到大)
DESC 降序 (从大到小)
默认为ASC
语法:select ... from table_name [where ...] order by column [ASC|DESC] ...;
案例
姓名及数学成绩,按数学成绩升序/降序显示
mysql> select name,math from result;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
-- 升序
mysql> select name,math from result order by math asc;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
-- 降序
mysql> select name,math from result order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 猪悟能 | 98 |
| 刘玄德 | 85 |
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)
表中含有null值的进行排序
-- null视为比任何值都小,升序出现在最上面
mysql> select * from test order by id asc;
+------+--------+
| id | name |
+------+--------+
| NULL | 张三 |
| NULL | NULL |
| 1 | 张三 |
| 1 | NULL |
| 1 | |
+------+--------+
5 rows in set (0.00 sec)
-- null视为比任何值都小,降序出现在最下面
mysql> select * from test order by id desc;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 1 | NULL |
| 1 | |
| NULL | 张三 |
| NULL | NULL |
+------+--------+
5 rows in set (0.00 sec)
mysql> select * from test order by name desc;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| NULL | 张三 |
| 1 | |
| 1 | NULL |
| NULL | NULL |
+------+--------+
5 rows in set (0.00 sec)
多字段排序,排序优先级随书写顺序
-- 查询同学各门成绩,依次按照数学降序、英语升序、语文升序的方式显示
mysql> select name,math,english,chinese from result;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 孙悟空 | 78 | 77 | 87 |
| 猪悟能 | 98 | 90 | 88 |
| 曹孟德 | 84 | 67 | 82 |
| 刘玄德 | 85 | 45 | 55 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name,math,english,chinese from result order by math desc;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 猪悟能 | 98 | 90 | 88 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name,math,english,chinese from result order by math desc,english desc;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select name,math,english,chinese from result order by math desc,english desc,chinese asc;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
-- 默认是asc升序,也可以这样写
mysql> select name,math,english,chinese from result order by math desc,english desc,chinese;
+-----------+------+---------+---------+
| name | math | english | chinese |
+-----------+------+---------+---------+
| 猪悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 刘玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
查询学生总分,由高到低
mysql> select name,math+chinese+english from result;
+-----------+----------------------+
| name | math+chinese+english |
+-----------+----------------------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+----------------------+
7 rows in set (0.00 sec)
mysql> select name,math+chinese+english as total from result;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘玄德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
+-----------+-------+
7 rows in set (0.00 sec)
-- 这里可以用别名
-- 先执行from result,然后执行where,然后select,最后再order by,所以这时候可以用别名
mysql> select name,math+chinese+english as total from result order by total;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+-------+
7 rows in set (0.00 sec)
结合where和order by子句
-- 查询姓孙的同学或者姓曹的同学的数学成绩,结果按数学成绩由高到低显示
mysql> select name,math from result where name like '孙%' or name like '曹%' order by math desc;
+-----------+------+
| name | math |
+-----------+------+
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+------+
3 rows in set (0.00 sec)
④筛选分页结果
-- 从0开始,筛选n条结果
select * from result limit n;
-- 从s开始,筛选n条结果
select * from result limit s,n;
-- 从s开始,筛选n条结果
select * from result limit n offset s;
mysql> select * from result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select * from result limit 5;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
mysql> select * from result limit 1;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select * from result limit 2;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
+----+-----------+---------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from result limit 1,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from result limit 2,4;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
-- 取4行,从第二行开始
mysql> select * from result limit 4 offset 2;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
分页展示(分页功能)
mysql> select * from result limit 0,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from result limit 3,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from result limit 6,3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)