MySQL查询子句(group by,limit,union,order by等)

时间:2022-01-14 09:49:51

Order by

按照字段值进行排序,默认升序(asc)。校对规则决定排序关系。
order by 字段 升序|降序(asc|desc),

Order by允许多字段排序。
指的是,先按第一个字段排序,如果不能区分,才使用第二个字段,以此类推。

create table test(
id int primary key not null auto_increment,
name varchar(10),
group_id int
);

insert into test values(null,'jason',5);
insert into test values(null,'mark',3);
insert into test values(null,'jason',4);
insert into test values(null,'ivy',3);
insert into test values(null,'jason',3);
insert into test values(null,'mark',5);
insert into test values(null,'mark',5);
insert into test values(null,'jason',4);

select * from test order by group_id asc,id desc;

注意:如果是分组,则应该使用对分组字段进行排序的。

select * from test group by group_id order by group_id;

MySQL查询子句(group by,limit,union,order by等)

limit

限制获得的记录数量。
limit的语法:
limit offset, row count
offset 偏移量,可以省略,默认从0开始。
row count 总记录数,如果数量大于余下的记录数,则获取剩余的记录数即可。

select * from test limit 2;
select * from test limit 2,3;
select * from test limit 100;

distinct

去除重复数据,重复的记录指的是字段值都相同的记录,而不是部分字段相同的记录。

select distinct group_id from test;

两个字段不重复

select distinct name,group_id from test;

MySQL查询子句(group by,limit,union,order by等)

与distinct相对的是all,表示所有,默认就是all行为。

union

将多条select语句的结果,合并到一起,称之为联合查询。
使用union关键字联合两个select语句。

select * from test where name='jason' order by id desc limit 2;
select * from test where name='jason' order by group_id desc limit 1;

(select * from test order by id desc limit 2)union(
select * from test order by group_id desc limit 1);

MySQL查询子句(group by,limit,union,order by等)

应用场景:
获得数据的条件出现逻辑冲突或者很难在一个逻辑内表示,就可以拆分成多个逻辑,分别实现最后将结果合并到一起。

如果union的结果有重复的记录,那么会消除重复,类似distinct。

(select id,name,group_id from test where name='mark' order by group_id desc limit 5)union(
select id,name,group_id from test where name='jason' order by group_id asc limit 5);

union 对两个结果集进行并集操作,重复数据只显示一次。
union all对两个结果集进行并集操作,重复数据全部显示。

(select id,name,group_id from test where name='mark' order by group_id desc limit 5)
union all
(select id,name,group_id from test where name='jason' order by group_id asc limit 5);

union的子语句获得的结果的排序
1、将子语句包裹在子括号内。
2、子语句order by,只有在order by 配合limit时,才生效。
原因是:union在做子语句时,会对没有limit子句的order by优化(忽略);

union的所有结果进行统一排序

(select id,name,group_id from test where name='mark')union all(
select id,name,group_id from test where name='jason')order by group_id;

MySQL查询子句(group by,limit,union,order by等)
子语句的括号不是必须的,但是语法看上去不清晰,容易产生歧义。

注意:
规定多个select语句检索到的字段数必须一致。
数据类型应该要求一致,如果mysql字段属性不一致,mysql内部会做类型转换处理,但是要求是能够转换成功。
检索结果中的列名称由第一条select语句的列名称而定。

子查询

定义:语句内部的查询语句就是子查询。
要求:子查询需要括号括起来。

查询id最大的那条记录的信息

select id,name,group_id from test order by id desc limit 1;

以上逻辑没有问题,但是出现了多个相同最大的id如何处理?
先获得最大的id,再判断是否有其相同的id。
select id from test order by id desc limit 1;
mysql允许将上面的查询结果,作为一个值来使用。

select * from test where id = (select id from test order by id desc limit 1);

子查询分类:

不同的分类,会有不同的使用方式。
分类标准:子查询出现的位置子查询的返回值形式。

返回值分类:
单一值,
一列,
多列,
表(多行,多列)

出现位置分类:
where型
from型
exists型

如何使用子查询?

单一值(标量):

获得一个值之后,使用关系运算符,进行判断。> < = >= <= !=

select * from test where id < (select max(id) from test);

列子查询:

获得一列,通常是多个行的一列值。注意一定是一列。

select name from test where group_id=4;

select id,name,group_id from test where
name in (select name from test where group_id=5);

一定是同集合类的操作符来完成(in| not in)。

其他的集合操作符:
any (集合) –>集合中的任何一个
=any (集合)–>等于集合中的任何一个即可,等同于in。

all (集合)–>集合中的所有元素
!=all(集合) –>不等于集合中的所有元素,等用于not in。

some(集合–>集合中的一些,语法上与any相同。
MySQL查询子句(group by,limit,union,order by等)

select id,name,group_id from test where 
name =any (select name from test where group_id=5);


select id,name,group_id from test where
name !=all (select name from test where group_id=5);

MySQL查询子句(group by,limit,union,order by等)
MySQL查询子句(group by,limit,union,order by等)

总结:
=any<==> in
!=all <==>not in
some和any是同义词
all,any和some可以使用除了=,!之外的运算符。

返回一行

select distinct id,group_id from test where name='mark' and 
group_id = 3 limit 1;

MySQL查询子句(group by,limit,union,order by等)
参与比较时,使用括号可以构建一行。
注意:通过limit 1表示确保一行

select id,name,group_id from test where (id,group_id) = 
(select distinct id,group_id from test where name='mark' and group_id = 3);


select id,name,group_id from test where (id,group_id) in
(select distinct id,group_id from test where name='mark' and group_id = 3);

MySQL查询子句(group by,limit,union,order by等)

返回一个表

select id,name from 
(select id,name,group_id from test where group_id>=4)
as t where group_id=5 and name like 'ma%';

MySQL查询子句(group by,limit,union,order by等)

如果用于from字句内,from字句内,要求使用一个表,而能是一个结果,应该给这个结果取一个别名。

select newId,name from 
(select id as newId,name,group_id from test where group_id>=4)
as t where group_id=5 and name like 'ma%';

MySQL查询子句(group by,limit,union,order by等)

外部查询所使用的别名,是由子查询指定。

exists

exists(subquery)
判断依据:如果子查询的可以返回数据,则认为exists表达式返回真,否则返回假。

select * from test 
where exists(select * from class where test.id=class_id);


select * from test
where id in(select class_id from class);

MySQL查询子句(group by,limit,union,order by等)
上面的两个语句完成的是同样的事情,但是解决思路是不一样的。

exists: 先获得test表的数据,然后获得id字段,然后去class表内查找对应值,找到,说明符合条件。

in: 先获得class表中所有的class_id的可能性,再在检索test表数据时,判断当前的class_id是否在id集合中。