3、MySQL数据库DQL操作

DQL 操作

/*
 * SELECT 
 * 			字段列表
 * FROM
 * 			表明列表
 * WHERE 
 * 			条件列表
 * GROUP BY
 * 			分组字段列表
 * HAVING
 * 			分组后条件列表
 * ORDER BY
 * 			排序字段列表
 * LIMIT
 * 			分页参数
 * */
-- 查询当前数据库
select database()

比较运算符

逻辑运算符

位运算符

运算符优先级

DQL基本查询(如果字段列表为*则查询所有字段数据)

select name,id,sex from tb_employee;
select * from tb_employee;
-- 别名设置(as 可省略)
select name as 姓名,id 工号,sex 年龄 from tb_employee;
-- 去除重复数据(distinct)
select distinct sex,age from tb_employee;

DQL条件查询

-- 1、查询年龄大于19的员工信息
select * from tb_employee where age >19;
select name from tb_employee where age>19;
-- 2、查询年龄在19到20之前 包含19，20
select * from tb_employee where age>=19 && age<=20;
select * from tb_employee where age>=19 and age<=20;
select * from tb_employee where age between 19 and 20 and sex = '女';
-- 2、查询员工编号为3，5，8，11的员工信息
select * from  tb_employee where coller=3 or coller = 5 or coller = 8 or coller =11;
select * from  tb_employee where coller=3 || coller = 5 || coller = 8 || coller =11;
select * from  tb_employee where coller in(3,5,8,11);
-- 查询name为三个字符的员工信息
select * from tb_employee where name like "___"; 
-- 查询身份证包含1的数据
select * from tb_employee where card like "%1%"; 
-- 查询身份证最后一位包含1的数据
select * from tb_employee where card like "%1"; 
select * from tb_employee where card like "_________________1"; 

DQL 聚合函数

-- 1、统计员工数量(使用*，或者某个字段的数量，如果该字段值为null则不统计数量)
select count(id) from tb_employee; 
-- 2、计算平均年龄
select avg(age) from tb_employee; 
-- 3、寻找年龄最大值,最小值,之和(男员工)
select max(age) from tb_employee; 
select min(age) from tb_employee; 
select sum(age) from tb_employee where sex = '男';

DQL 分组查询（在where条件里不能使用聚合函数，having可以）

-- 1、根据性别统计男女员工数量
select count(sex),sex  from tb_employee group by sex; 
-- 2、根据性别分组统计男女员工的平均年龄
select avg(age),sex  from tb_employee group by sex; 
-- 3、年龄小于35的员工，并根据工作地址分组，获取员工数量大于2的地址；
-- 添加工作地址字段
alter table tb_employee add workaddress varchar(100) comment '工作地址';
-- 给工作地址添加数据
update tb_employee set workaddress = '安徽' where coller %2 =0;
update tb_employee set workaddress = '四川' where coller %3 =0;
update tb_employee set workaddress = '重庆' where workaddress is null ;
select workaddress '工作地址',count(workaddress) '数量'  from tb_employee  where age <35  group by workaddress having count(workaddress) >2 ;

DQL 排序查询（ASC升序，desc降序）

-- 1、根据年龄升序排序
select * from tb_employee order by age asc; 
select * from tb_employee order by age; 
-- 修改入职时间
update tb_employee set entry = '2024-3-9' where coller %2 =0;
update tb_employee set entry = '2024-3-2' where coller %3 =0;
update tb_employee set entry = '2024-3-7' where workaddress is null ;
-- 2、根据入职时间进行降序排序
select * from tb_employee order by entry desc; 
-- 3、根据年龄升序排序，年龄相同，在按照入职时间降序排序
select * from tb_employee order by age , entry desc; 
-- DQL 分页查询
-- 1、查询第一页员工数据，每页展示5条数据
select * from tb_employee limit 0,5;
select * from tb_employee limit 5;
-- 2、查询第2页员工数据，每页展示5条数据
select * from tb_employee limit 2,5;