mysql查询语句学习记录

查询日期最大的数据
select max(字段名) from 表

查询单个人每月
select * FROM 表名 where xh=‘12215445242’ AND
rzsj between ‘2020-10-1 00:00:00’ and ‘2020-10-31 00:00:00’ ;

查询总数
select count（*） as 男性人数 from students where gender=1

查询求和
select sum(age) from students ;

求平均值
select avg(age) from students;
select sum(age)/count(*) from students;

四舍五入
select round(sum(age)/count(*),2) from students;
存储金额的时候会扩大多少倍，不会存储小数
select round(avg(height),3) from students;

分组
select后面放唯一能够标识的字段
select gender from students group by gender;

计算每组性别的人数
select gender,count() from students group by gender;
分组和聚合相结合才有用
select gender,count() from students where gender=1 group by gender;
select gender,group_concat(name) from students where gender=1 group by gender;
select gender,group_concat(name,age,id) from students where gender=1 group by gender;
select gender,group_concat(name," “,age,” ",id) from students where gender=1 group by gender;

不显示某些分组
select gender,group_concat(name) from students group by gender having avg(age)>30;

where对原始表进行判断，having是对计算的结果进行判断

查询每种性别中大于2的分组
select gender,group_concat(name) from students group by gender having count(*)>2;

排序
select * from students where (age between 18 and 35) and gender=1;
select * from students where (age between 18 and 34) and gender=1 order by age;
select * from students where (age between 18 and 35) and gender=1 order by age asc;

查询年龄在18到30岁之间的女性，按身高倒序排
select * from students where (age between 18 and 30) and gender=2 order by height desc,id desc;
select * from students where (age between 16 and 30) and gender=2 order by height desc,age asc,id desc;
select * from students order by age asc,height desc;

分页
limit限制查询出来的个数
select * from students where gender=1 limit 2;

查询前5个
select * from students limit 0,5;
select * from students limit 5,5;

每页显示2个，第1页
select * from students limit 0，2；
每页显示2个，第2页
select * from students limit 2，2；
每页显示2个，第3页
select * from students limit 4，2；

limit （第N页-1）*每页的个数，每页的个数
不能写成式子
limit在最后
select * from students where gender=2 order by height desc limit 0,2;

连接查询
内连接
select * from students inner join classes；此语句只是将两个表合起来，查询结果不正确

查询能对的上的学生信息
select * from students inner join classes on students.cls_id=classes.id;

按要求显示姓名‘班级
select students.*,classes.name from students inner join classes on students.cls_id=classes.id;
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;

给表取名字
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

在以上的查询中将班级的名字放在第一列
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;

按班级信息排序,按id排
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;

左连接
select * from students as s left join classes as c on s.cls_id=c.id;

查询没有对应班级的学生
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
从结果集中找东西，用having

右连接
一般用的很少

自关联
查询山东的id
select * from area where atitle=‘山东省’；

查询山东的地级市
select * from areas where pid=370000;

将一张表当作两张表来查询
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle=‘山东省’;
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle=‘山东省’;

子查询
在select中嵌套select，将一个select的查询结果当作另一个查询条件
查询最高的男性的信息
select * from students where height=(select max(height) from students);

select * from areas where pid=(select aid from areas where atitle=‘河北省’);

sql语句没有区别，执行时间有区别