MySQL高级查询

排序查询语法：

ｓｅｌｅｃｔ * from 表名 order by 列1 asc/desc [,列2 asc/desc,...]

语法说明：

查询未删除男生信息，按学号降序：

ｓｅｌｅｃｔ * from students where is_del = 0 and sex = ‘男‘ order by id desc;

显示所有学生信息，先按年龄从大到小排序，年龄相同时按身高由高到低排序：

ｓｅｌｅｃｔ * from students order by age desc,height desc;

二、分页查询

在网上购物时，浏览商品列表的时候，由于数据特别多，一页显示不完，一页一页的进行显示，这就是分页查询

ｓｅｌｅｃｔ * from 表名 limit start,count

说明：

查询前三行男生的信息：

ｓｅｌｅｃｔ * from students where sex=‘男‘ limit 0,3;

可以简写为

ｓｅｌｅｃｔ * from students where sex=‘男‘ limit 3;

每页显示m条数据，求第n页显示的数据（关键是求每页的开始行索引）

ｓｅｌｅｃｔ * from students limit (n-1)*m,m;

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组（group by）来使用，用于统计和计算分组数据

常用的聚合函数：

求总行数：

返回非null数据的总行数

ｓｅｌｅｃｔ count(height) from students;

返回总行数，包含null值记录

ｓｅｌｅｃｔ count(*) from students;

求最大值：

查询男生编号的最大值

ｓｅｌｅｃｔ max(id) from students where sex=‘男‘；

求最小值：

查询未删除的学生最小编号

ｓｅｌｅｃｔ min(id) from students where is_del=0;

求和：

查询男生的总身高

ｓｅｌｅｃｔ sum(height) from students where sex=‘男‘;

查询男生的平均身高

ｓｅｌｅｃｔ sum(height) / count(*) from students where sex=‘男‘;

求平均值：

求男生的平均身高，聚合函数不统计null值

ｓｅｌｅｃｔ avg(height) from students where sex=‘男‘;

求男生的平均身高，包含身高为null的值

ｓｅｌｅｃｔ avg(ifnull(height,0)) from students where sex=‘男‘;

说明：

聚合函数特点：

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组

分组查询基本的语法格式：

group by 列名 [having 条件表达式] [with rollup]

说明：

group by 的使用：

group by可用于单个字段分组，也可用于多个字段分组

根据sex字段来分组

ｓｅｌｅｃｔ gender from students group by sex;

根据name和sex字段来分组

ｓｅｌｅｃｔ name,sex from students group by name,sex;

group by + group_concat()的使用：

group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间用逗号分割

根据sex字段进行分组，查询sex字段和分组的name字段信息

ｓｅｌｅｃｔ sex,group_concat(name) from students group by sex;

group by + 聚合函数的使用：

统计不同性别的人的平均年龄

ｓｅｌｅｃｔ sex,avg(age) from students group by sex;

统计不同性别的人的个数

ｓｅｌｅｃｔ sex,count(*) from students group by sex;

group by + having的使用：

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

根据sex字段进行分组，统计分组条数大于2的

ｓｅｌｅｃｔ sex,count(*) from students group by sex having count(*)>2;

group by + with rollup的使用：

with rollup的作用是：在最后记录后面新增一行，显示ｓｅｌｅｃｔ查询时聚合函数的统计和计算结果

根据sex字段进行分组，汇总总人数

ｓｅｌｅｃｔ sex,count(*) from students group by sex with rollup;

根据sex字段进行分组，汇总所有人年龄

ｓｅｌｅｃｔ sex,group_concat(age) from students group by sex with rollup;

小结：

连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成

连接查询分为：

查询两个表中符合条件的共有记录（取交集）

内连接查询语法格式：

ｓｅｌｅｃｔ 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

说明：

使用内连接查询学生表与班级表：

ｓｅｌｅｃｔ * from students s inner join classes c on s.c_id = c.id;

原本两个表的内容：

使用内连接：

2. 左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在则使用null值填充

左连接查询语法格式：

ｓｅｌｅｃｔ 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

说明：

使用左连接查询学生表与班级表：

ｓｅｌｅｃｔ * from students s left join classes c on s.c_id = c.id;

3. 右连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在则使用null值填充

右连接查询语法格式：

ｓｅｌｅｃｔ 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2；

说明：

使用右连接查询学生表与班级表：

ｓｅｌｅｃｔ * 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文件内容：

ｉｎｓｅｒｔ into areas values(‘11000‘, ‘北京市‘, null);ｉｎｓｅｒｔ into areas values(‘11001‘, ‘北京市‘, ‘11000‘);ｉｎｓｅｒｔ into areas values(‘11002‘, ‘东城区‘, ‘11001‘);ｉｎｓｅｒｔ into areas values(‘11003‘, ‘西城区‘, ‘11001‘);ｉｎｓｅｒｔ into areas values(‘11004‘, ‘朝阳区‘, ‘11001‘);ｉｎｓｅｒｔ into areas values(‘11005‘, ‘丰台区‘, ‘11001‘);ｉｎｓｅｒｔ into areas values(‘11006‘, ‘海淀区‘, ‘11001‘);ｉｎｓｅｒｔ into areas values(‘12000‘, ‘河北省‘, null);ｉｎｓｅｒｔ into areas values(‘12001‘, ‘石家庄市‘, ‘12000‘);ｉｎｓｅｒｔ into areas values(‘12002‘, ‘长安区‘, ‘12001‘);ｉｎｓｅｒｔ into areas values(‘12003‘, ‘桥东区‘, ‘12001‘);ｉｎｓｅｒｔ into areas values(‘12004‘, ‘桥西区‘, ‘12001‘);ｉｎｓｅｒｔ into areas values(‘12005‘, ‘新华区‘, ‘12001‘);

说明：

自连接查询的用法：

ｓｅｌｅｃｔ c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id;

说明：

六、子查询

在一个ｓｅｌｅｃｔ语句中，嵌入了另外一个ｓｅｌｅｃｔ语句，那么被嵌入的ｓｅｌｅｃｔ语句称之为子查询语句，外部的那个ｓｅｌｅｃｔ语句则称为主查询

主查询和子查询的关系：

查询大于平均年龄的学生：

ｓｅｌｅｃｔ * from students where age > (ｓｅｌｅｃｔ avg(age) from students);

查询学生在班的所有班级名字：

ｓｅｌｅｃｔ name from classes where id in (ｓｅｌｅｃｔ c_id from students where c_id is not null);

查找年龄最大，身高最高的学生：

ｓｅｌｅｃｔ * from students where age=(ｓｅｌｅｃｔ max(age) from students) and height=(ｓｅｌｅｃｔ max(height) from students);

可以简写为：

ｓｅｌｅｃｔ * from students where (age,height) = (ｓｅｌｅｃｔ max(age), max(height) from students);