数据蛙第8期数据分析组团

9.7sql学习

2020-09-10  本文已影响0人  九儿9292

3.1排序(order by)

asc从小到大排列,即升序(默认)

desc从大到小排序,即降序

例1:查询未删除男生信息,按学号降序select * from students where gender=1 and is_delete=0 order by id desc;

年龄相同则按

select * from students order by age desc,height desc;

例1:查询学生总数

select count(*) from students;

例2:查询女生的编号最大值

select max(id) from students where gender=2;

例4:查询男生的总年龄  sum select sum(age) from students where gender=1;

例5:查询未删除女生的编号平均值avg

select avg(id) from students where is_delete=0 and gender=2;

如果要保留2位小数,则(round会四舍五入)

select round(avg(id),2) from

3.3分组查询 group by

select gender from students group by gender;

用性别给表分组(会显示所有组名)

select * from students where gender=1;

显示性别为男的表信息

select gender,count(1)  from students group by gender

计算每种性别中的人数

select gender,avg(age)  from students group by gender

计算每组平均年龄

当group by单独使用时,只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大group by + group_concat()group_concat(字段名)可以作为一个输出字段来使用,

select gender,group_concat(name) from students group by gender;

查询用性别分组的各组姓名集合

只输出指定结果:having

having作用和where一样,但having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;

只输出两条记录以上的性别分组

select gender,count(*) from students group by gender having avg(age)>30;

只输出平均年龄大于30岁的性别分组

group by + with rollup在最后新增一行,来记录当前列里所有记录的总和(with rollup)

select gender,count(*) from students group by gender with rollup;

4.1分页查询

获取部分行

limit(此语句应放在句子最后)

select * from students where gender=1 limit 0,3;

获取第1行(位置0,若是第二行开始则为1,3)开始的3行记录

4.2内连接( inner join 内连接 On表一.*=表二.*的条件)

关联查询(取两表交集)

select * from students inner join classes on students.cls_id = classes.id;

4.3左连接left join(以左表为基准,无交集也输出所有左表内容)

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

4.4子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select语句称之为子查询语句

上一篇 下一篇

猜你喜欢

热点阅读