mysql查询详解

xu_小白经验汇总

已于 2023-07-26 23:37:43 修改

阅读量38

点赞数

文章标签： sql

于 2023-07-26 23:36:58 首次发布

本文链接：https://blog.csdn.net/qq_42802941/article/details/131949878

版权

本文介绍了MySQL的各种查询操作，包括基础查询、清除重复行、条件查询、范围查询以及聚合和分组函数的使用。涉及的关键字有distinct用于去重，where用于设定查询条件，groupby用于数据分组，以及in,notin,between,isnull等比较操作。同时，文章还讲解了如何计算平均值、计数、最大值、最小值等统计信息。

摘要由CSDN通过智能技术生成

Mysql查询

普通查询

select name,age from students;
select * from students;
select students.name,students.age from students;
select s.name,s.age from students as s;

清除重复行

select distinct gender from students;

条件查询

select * from students where age>18;
select * from students where age>18 and /or age <28;
select * from students where not age<=18 and gender=2；年龄不是小于或者等于18 并且是女性
select name from students where name=”小%”；或者select name from students where name like “小%”姓名中以小开头的名字
select name from students where name like “%小%”；查询姓名中有小的所有名字
下滑线替换一个，%替换一个或者多个 select name from students where name like “__”;查询有两个字的名字
Select name from students where name rlike “^周.*伦$”; 周开头伦结尾的名字

范围查询

select name,age from students where age=18 or age=34;年龄为18或者34的名字
优化 select name,age from students where age in (18,34);
select name,age from students where age not in (18,34); 年龄不是18或者不是34的
select name,age from students where age between 18 and 34;年龄在18到34岁之间
select name,age from students where age not between 18 and 34;年龄不在18到34岁之间
select * from students where height is null/NULL;查询身高为空的
select * from students where height is not null/NULL;查询身高为非空的

聚合，分组

select count(*) as 男性人数 from students where gender=1;查询男性有多少人
select max(age)/min(age)/sum(age)/avg(age) from students;查询最大/最小/求和/求平均年龄
select sum(age)/count(*) from students;求平均
select round(sum(age)/count(*),2) from students;求平均保留两位小数
select gender,count(*) from students group by gender;计算每种性别中的人数
select gender,count(*) from students where gender=1 group by gender;计算男性人数
select gender,group_concat(name,”__”,age,” ”,id) from students where gender=1 group by gender;查同种性别中的姓名，年龄和id
where在group前，是对原数据表的条件 having在group后，是对分组后的数据表的条件 select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;查询平均年龄超过30 岁性别