-- DQL查询语言中的分页查询,关键词 limit;
-- 特别注意 limit查询,起始索引是从0 开始的,什么意思,就是查第一页的数据,是0;
-- 第二页是(2-1)*每页返回的记录数据;
-- 具体的语法句子 select 字段 from 表名 limit 起始索引(哪页),查询记录数(多少条数据);
create database info;
use info;
create table worker(
id int,
name varchar(10),
age int,
gender char(1),
workcity varchar(10),
entrytime date
);
insert into worker values (1,'张三',28,'F','上海','2020-08-20');
insert into worker values(2,'李四',24,'F','上海','2021-06-25');
insert into worker values(3,'王二麻',27,'M','北京','2020-07-22');
insert into worker values(4,'王五',29,'M','深圳','2022-01-10');
insert into worker values(5,'赵六',32,'F','深圳','2019-07-07');
insert into worker values(6,'钱九',31,'M','北京','2018-04-19');
insert into worker values(7,'孙八',30,'F','深圳','2021-09-17');
insert into worker values(8,'何十',34,'M','北京','2022-03-28');
insert into worker values(9,'九九',26,'M','深圳','2023-08-19');
insert into worker values(10,'零七',24,'F','上海','2021-06-24');
insert into worker values(11,'可可',27,'M',null,'2024-07-21');
-- 查询第一页数据,每页展示5条数据;
-- select * from worker limit 0,5;
-- select * from worker limit 5;
-- 查询第2页数据,每页展示3条数据;第2页的起始索引是(2-1)*3=3
-- select * from worker limit 3,3;
-- 查询第3页数据,每页展示3条数据;第2页的起始索引是(3-1)*3=6
-- select * from worker limit 6,3;
-- DQL 查询语句的案例练习
-- 查询年龄是24、27、32、34的女性员工信息;
-- select * from worker where age in (24,27,32,34) && gender = 'M';
-- 查询年龄在20-40之间且姓名为3个字的女性员工信息;注意like查询
-- select * from worker where gender = 'M'&& age Between 20 and 40 && name like '___';
-- select * from worker where gender = 'M'&& age >= 20 <= 40 && name like '___';
-- 统计年龄小于30的男性和女性员工数量;
-- select gender,count(*) from worker where age <30 group by gender;
-- 查询年龄小于30(含)的员工姓名和ID,并对查询结果年龄升序排序,如果年龄一样按入职时间降序排序;
-- select name,id from worker where age <=30 order by age ASC,entrytime DESC;
-- 注意:order by +字段+排序,+字段+排序;
-- 查询性别为女且年龄在20-35之间的前5个员工信息,对查询结果年龄按升序,入职时间按升序排序;
select * from worker where gender ='M' && age between 20 and 35 order by age ASC,entrytime DESC limit 0,5;