mySql 查询最大、最小值及子查询

准备数据：

创建表：

create table gpChatRecord(
id int primary key auto_increment,
content varchar(50),
date_time datetime);

插入单条数据：

insert into gpChatRecord(content, date_time) values(1, current_time());

插入多条数据：

insert into gpChatRecord(content, date_time) values(2, current_time()), (3, current_time()), (4, current_time()), (5, current_time()), (6, current_time()), (7, current_time()), (8, current_time()), (9, current_time()),(10, current_time());

查询全部数据

select * from gpChatRecord;

id	content	date_time
1	1	2019-02-18 15:09:53
2	2	2019-02-18 15:10:18
3	3	2019-02-18 15:10:22
4	4	2019-02-18 15:24:37
5	5	2019-02-18 15:24:37
6	6	2019-02-18 15:24:37
7	7	2019-02-18 15:24:37
8	8	2019-02-18 15:24:37
9	9	2019-02-18 15:24:37
10	10	2019-02-18 15:24:37

 

where 查询

select id, content from gpChatRecord where id < 8;

id	content
1	1
2	2
3	3
4	4
5	5
6	6
7	7

 

max函数查询最大值

select max(id) from gpChatRecord where id < 8;

# max(id)
'7'

min函数查询最小值

select min(id) from gpChatRecord where id > 8;

# min(id)
'9'

子查询：

select * from gpChatRecord where id in (select id from gpChatRecord where id < 8);

id	content	date_time
1	1	2019-02-18 15:09:53
2	2	2019-02-18 15:10:18
3	3	2019-02-18 15:10:22
4	4	2019-02-18 15:24:37
5	5	2019-02-18 15:24:37
6	6	2019-02-18 15:24:37
7	7	2019-02-18 15:24:37