MYSQL基础_聚合函数_分组和多表连接查询

作用：去掉结果集中的重复记录
说明：
出现在查询语句字段列表中
当结果集中所有字段的值相同，才是重复记录
as
作用：给表或字段起别名
例子：
select age,name,brief j from psd_student;
is
作用：用来和null值进行比对
聚合函数
count(字段名)
作用：
获取结果集某字段下记录的总条数
获取分组后，每组中记录的条数

分组查询（count()）
group by sex  --把男生放一组，女生放一组 
例子：计算所有学生中男生，女生个有多少人
select count(sex) as num,sex from psd_student group by sex
练习：计算每一个班的总人数
select count(classid) num,classid from psd_student group by classid;
having 条件
作用:对分组的统计结果再进行筛选
例子：统计学生人数大于等于5的班级都有哪个？？？？？
1、知道每个班多少人
select count(classid) as num,classid from psd_student group by classid;
2、根据班级人数，进行筛选
select count(classid) as num,classid from psd_student group by classid having num>=5;
说明：
group by 和having一般是成对出现
练习：统计哪个年龄的人数最多
1、每个年龄对应的人数
select count(age) as num,age from psd_student group by age;
2、倒序排序
select count(age) as num,age from psd_student group by age order by num desc;
多表联查(多张有关系的表进行联查)
select tableA.字段,tableB.字段  from tableA inner join tableB on tableA.字段=tabelB.字段 where 子句 group by 子句 having 子句 order by 子句 limit子句

需求：查询小王他的班级名称
sid name sex age classid brief
1   小王  男  21  1      null
2   小li  男  21  1      null
3   小zhang  男  21  2      null
4   小sun  男  21  1      null
5   小sun  男  21  3      null

classid cname 
1       psd1306
2       psd1307

1   小王  男  21  1      null     1   psd1306
2   小li  男  21  1      null     1   psd1306
3   小zhang  男  21  2      null  2   psd1307
4   小sun  男  21  1      null    1   psd1306
5   小sun  男  21  3      null    null null
例子：
两个表连接
select * from psd_student as s join psd_class c on s.classid=c.classid; 
查询小王他的班级名称
1、不用连接
查询小王的班级id
select classid from psd_student where name="小王"
查询班级id对应的班级名称
select cname from psd_class where classid=1
2、连接查询
select c.cname from psd_student as s join psd_class c on s.classid=c.classid where s.name="小王";
左连接（以左边的表为主表去连接右边的表，如果右边的表中没有记录用null补齐）
select c.cname from psd_student as s left join psd_class c on s.classid=c.classid
右连接（以右边的表为主表去连接左边的表,如果左边的表中没有记录用null补齐）
select c.cname from psd_student as s right join psd_class c on s.classid=c.classid
练习：统计每个班的人数，在结果集中出现班级名称
1、连接
select * from psd_student s join psd_class c on s.classid=c.classid
2、分组，求每组中人数
select c.cname,count(s.classid) num from psd_student s join psd_class c on s.classid=c.classid group by s.classid having num>=3;
子查询
1、select 字段列表 from (查询语句) as 别名;
例子：查询班级id为1的班，男生年龄大于25的都有谁
select * from (select * from psd_student where classid=1 and sex="男") as s where s.age>25
2、select 字段列表 from 表名 where 字段名 in(查询语句)
例子：查询班级psd1306及psd1308的学生
select * from psd_student where classid in(select classid from psd_class where cname="psd1306" or cname="psd1308");

union(结果集的连接)
要求：结果集中字段的个数必须相同
例子：
select name,sex from psd_student union select tname,1 from psd_class;
union all(结果集的连接,重复值保留)

聚合函数
数学函数
count(字段名)-----对结果集或组中记录条数求总条数
avg(字段名)----对每组中的某个字段求平均数
min(字段名)----找某个字段下的最小值
max(字段名)----找某个字段下的最大值
sum(字段名)
round(字段名)---对某个字段做四舍五入处理
ceil(字段名)---对字段的值向上取整
floor(字段名)----对字段的值向下取整
说明：一般和分组同时使用
例子：
1、在学生表中，找出男生年龄最大者，和女生年龄最大者。
select max(age),sex from psd_student where sex!="保密" group by sex
2、在学生表中，求出男生平均年龄，女生平均年龄。
select avg(age),sex from psd_student where sex!="保密" group by sex;
select sum(age),sex from psd_student where sex!="保密" group by sex;
字符函数
replace(字段名,"search","replace")
作用：字符串替换
用途：
    查询语句的字段列表
    update语句的 字段名=值，在值位置出现
例子：
查询学生信息，简介中包含“李洪志”的，过滤掉。
select sid,name,replace(brief,"李洪志","***") from psd_student;
把学生表中，简介字段中的李洪志换成“***”
update psd_student set brief=replace(brief,"李洪志","***");

concate(字段名,"string",.....)
作用：字符串连接
特点：遇到NULL返回null
用户：
    查询语句的字段列表
    update语句的 字段名=值，在值位置出现
例子：
    1、把name和sex连接
    2、把name sex brief 三个字段的内容连接，连接后写入到brief字段    
    update psd_student set brief=concat(name,"|",sex,"|",brief);
时间日期函数
curtime()
curdate()
now()
用途：
文章表（id,title,content,num,addtime datetime）
insert语句中使用
insert into news(title,content,addtime) values("tt","cc",now())
from_unixtime(字段|时间戳,"时间日期格式")
作用：对时间戳，转化成时间日期格式
时间日期格式：
"%Y-%m-%d %H:%i:%s"
"%Y-%m-%d"
"%H:%i:%s"
用途：
    文章表（id,title,content,num,addtime int(11)）
select id,title,from_unixtime(addtime,"%Y-%m-%d %H:%i:%s") from news where id=1;
create table news(
    id int(8) primary key auto_increment,
    title char(100) not null,
    content text,
    num int(6) default 0,
    addtime int(11)
);