MySQL高级查询
黑马蓝汐 人气:0一、排序
排序查询语法：
select * from 表名 order by 列1 asc/desc [,列2 asc/desc,...]
语法说明：
先按照列1进行排序，如果列1的值相同时，则按照列2排序asc：升序排序（从小到大）desc：降序排序（从大到小）默认是升序排序（asc）
查询未删除男生信息，按学号降序：
select * from students where is_del = 0 and sex = '男' order by id desc;
显示所有学生信息，先按年龄从大到小排序，年龄相同时按身高由高到低排序：
select * from students order by age desc,height desc;
二、分页查询
在网上购物时，浏览商品列表的时候，由于数据特别多，一页显示不完，一页一页的进行显示，这就是分页查询
select * from 表名 limit start,count
说明：
limit 是分页查询关键字start 表示开始行索引，默认是0count 表示查询条数
查询前三行男生的信息：
select * from students where sex='男' limit 0,3;
可以简写为
select * from students where sex='男' limit 3;
每页显示m条数据，求第n页显示的数据（关键是求每页的开始行索引）
select * from students limit (n-1)*m,m;
三、聚合函数
聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组（group by）来使用，用于统计和计算分组数据
常用的聚合函数：
count（col）：表示求指定列的总行数max（col）：表示求指定列的最大值min（col）：表示求指定列的最小值sum（col）：表示求指定列的和avg（col）：表示指定列的平均值
求总行数：
返回非null数据的总行数 select count(height) from students; 返回总行数，包含null值记录 select count(*) from students;
求最大值：
查询男生编号的最大值 select max(id) from students where sex='男'；
求最小值：
查询未删除的学生最小编号 select min(id) from students where is_del=0;
求和：
查询男生的总身高 select sum(height) from students where sex='男'; 查询男生的平均身高 select sum(height) / count(*) from students where sex='男';
求平均值：
求男生的平均身高，聚合函数不统计null值 select avg(height) from students where sex='男'; 求男生的平均身高，包含身高为null的值 select avg(ifnull(height,0)) from students where sex='男';
说明：
ifnull函数：表示判断指定字段的值是否为null，如果为空则使用自己提供的值
聚合函数特点：
聚合函数默认忽略字段为null的记录，要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换
四、分组查询
分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组
分组查询基本的语法格式：
group by 列名 [having 条件表达式] [with rollup]
说明：
列名：是指按照指定字段的值进行分组having 条件表达式：用来过滤分组后的数据with rollup：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果
group by 的使用：
group by可用于单个字段分组，也可用于多个字段分组
根据sex字段来分组 select gender from students group by sex; 根据name和sex字段来分组 select name,sex from students group by name,sex;
group by + group_concat()的使用：
group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间用逗号分割
根据sex字段进行分组，查询sex字段和分组的name字段信息 select sex,group_concat(name) from students group by sex;
group by + 聚合函数的使用：
统计不同性别的人的平均年龄 select sex,avg(age) from students group by sex; 统计不同性别的人的个数 select sex,count(*) from students group by sex;
group by + having的使用：
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
根据sex字段进行分组，统计分组条数大于2的 select sex,count(*) from students group by sex having count(*)>2;
group by + with rollup的使用：
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
根据sex字段进行分组，汇总总人数 select sex,count(*) from students group by sex with rollup; 根据sex字段进行分组，汇总所有人年龄 select sex,group_concat(age) from students group by sex with rollup;
小结：
group by 根据指定的一个或者多个字段对数据进行分组group_concat（字段名）函数是统计每个分组指定字段的信息集合聚合函数在和group by 结合使用时，聚合函数统计和计算的是每个分组的数据having 是对分组数据进行条件过滤with rollup 在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
五、连接查询
连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成
连接查询分为：
内连接查询左连接查询右连接查询自连接查询
1. 内连接查询
查询两个表中符合条件的共有记录（取交集）
内连接查询语法格式：
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明：
inner join 就是内连接查询关键字on 就是连接查询条件
使用内连接查询学生表与班级表：
select * from students s inner join classes c on s.c_id = c.id;
原本两个表的内容：
使用内连接：
2. 左连接查询
以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在则使用null值填充
左连接查询语法格式：
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明：
left join 就是左连接查询关键字on 就是连接查询条件表1 是左表表2 是右表
使用左连接查询学生表与班级表：
select * from students s left join classes c on s.c_id = c.id;
3. 右连接查询
以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在则使用null值填充
右连接查询语法格式：
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2；
说明：
right join 就是右连接查询关键字on 就是连接查询条件表1 是左表表2 是右表
使用右连接查询学生表与班级表：
select * from students s right join classes c on s.c_id = c.id;
4. 自连接查询
左表和右表是同一个表，根据连接查询条件查询两个表中的数据
创建areas表：
create table areas( id varchar(20) not null primary key, title varchar(30) not null, pid varchar(20) );
执行sql文件给areas表导入数据：
source areas.sql;
sql文件内容：
insert into areas values('11000', '北京市', null);
insert into areas values('11001', '北京市', '11000');
insert into areas values('11002', '东城区', '11001');
insert into areas values('11003', '西城区', '11001');
insert into areas values('11004', '朝阳区', '11001');
insert into areas values('11005', '丰台区', '11001');
insert into areas values('11006', '海淀区', '11001');
insert into areas values('12000', '河北省', null);
insert into areas values('12001', '石家庄市', '12000');
insert into areas values('12002', '长安区', '12001');
insert into areas values('12003', '桥东区', '12001');
insert into areas values('12004', '桥西区', '12001');
insert into areas values('12005', '新华区', '12001');说明：
source 表示执行的sql文件
自连接查询的用法：
select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id;
说明：
自连接查询必须对表起别名
六、子查询
在一个select语句中，嵌入了另外一个select语句，那么被嵌入的select语句称之为子查询语句，外部的那个select语句则称为主查询
主查询和子查询的关系：
子查询是嵌入到主查询中子查询是辅助主查询的，要么充当条件，要么充当数据源子查询是可以独立存在的语句，是一条完整的select语句
查询大于平均年龄的学生：
select * from students where age > (select avg(age) from students);
查询学生在班的所有班级名字：
select name from classes where id in (select c_id from students where c_id is not null);
查找年龄最大，身高最高的学生：
select * from students where age=(select max(age) from students) and height=(select max(height) from students); 可以简写为： select * from students where (age,height) = (select max(age), max(height) from students);
加载全部内容