菜鸟笔记
提升您的技术认知

mysql高级查询-ag真人游戏

目录

一、排序

二、分页查询

三、聚合函数

四、分组查询

五、连接查询

1. 内连接查询

2. 左连接查询

3. 右连接查询

4. 自连接查询

六、子查询

排序查询语法:

select * from 表名 order by 列1 asc/desc [,列2 asc/desc,...]

语法说明:

  1. 先按照列1进行排序,如果列1的值相同时,则按照列2排序
  2. asc:升序排序(从小到大)
  3. desc:降序排序(从大到小)
  4. 默认是升序排序(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

说明:

  1. limit 是分页查询关键字
  2. start 表示开始行索引,默认是0
  3. count 表示查询条数

查询前三行男生的信息:

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)来使用,用于统计和计算分组数据

常用的聚合函数:

  1. count(col):表示求指定列的总行数
  2. max(col):表示求指定列的最大值
  3. min(col):表示求指定列的最小值
  4. sum(col):表示求指定列的和
  5. 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. 内连接查询
  2. 左连接查询
  3. 右连接查询
  4. 自连接查询

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语句则称为主查询

主查询和子查询的关系:

  1. 子查询是嵌入到主查询中
  2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
  3. 子查询是可以独立存在的语句,是一条完整的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);

网站地图