MYSQL学习笔记03

聚合函数：

--总数：count(总数)

-----select count(*) from idols where gender = 1;

-----select count(*) from as 歌手人数 from singers where idol_id = 1;

-- max(最大值)

-----select max(height) from idols where gender = 1;

-- min(最小值 )

-----select min(height) from idols where gender = 1;

--avg(平均值)

-----select sum(age)/count(*) from idols;===平均年龄

--round(四舍五入)

-----select round(sum(age)/count(*),2) from idols;==保留两位小数

--分组：group by(按照唯一标识分组)

-----select gender from idols group by gender;

-----select gender,count(*) from idols group by gender;

-----select gender,group_concat(name) from idols group by gender;==显示具体内容

-----select gender,group_concat(name，_,age) from idols where gender=1 group by gender;====group_concat()里写什么就显示什么

--having

-----select gender,group_concat(name),avg(age) from idols group by gender having avg(age)>25;

分页：

--limit start,count

-----select * from idols where height > 180 limit 5;===从表中第1个数据开始显示5个

------select * from idols where height>175 limit 2,4;===从第2条数据开始显示4个

连接查询：多个表的关联查询

--inner join ... on(查询两个表都有的信息)

-----select * from idols inner join idols_id on idols.idol_id=idols_id.id;

-----select idols.name,idols_id.name from idols inner join idols_id on idols.idol_id=idols_id.id;

      等价于select i.name,id.name from idols as i inner join idols_id as id on i.idol_id=id.id;

-----select i.name,id.name from idols as i inner join idols_id as id on i.idol_id=id.id order by id.name;

--left  join ... on(left左边为基准，表里有就显示，没有显示为null)

-----select * from idols left join idols_id on idols.idol_id=idols_id.id;

自关联：一个表里的字段关联另一个字段