detachedcriteria查询去重_MySQL系列DML语句之select单表查询

系列文章说明MySQL系列文章包含了软件安装、具体使用、备份恢复等内容，主要用于记录个人的学习笔记，主要使用的MySQL版本为5.7.28，服务器系统版本为CentOS 7.5。本章节为select单表查询内容，本章节使用到了world.sql中的city表和自建的student表，其中world.sql可以在mysql官网下载。注：world数据为19xx年的，数据与现在不匹配。 student表结构如下：

①sno：学号

②sname：学生姓名

③sage：学生年龄

④ssex：性别

⑤status：状态

city表结构如下：

①ID：序号

②Name：城市名称

③CountryCode：国家代码

④District：省/州

⑤Population：人口

关于SELECT

    按照传统的SQL分类，select属于DML，但是由于select作为最常用的语句，有很多人将它和show语句分为DQL。

    无论select是属于DML还是DQL，它都是SQL语言里最重要、最常见的语句，可以配合where、group by、order by等一起使用。

select使用

0 1

单独使用

SELECT

单独使用

①查询全表信息

select * from student;

②查询指定列信息

select sname from student;

③计算用途

0 2

where条件语句

where语句格式如下：

select 指定列1,指定列2 from 表名 where 条件;

where

比较判断条件查询

常用的比较判断符号包括：>、>=、

①查询sname为yunwei的行信息

select * from student where sname='yunwei';

②查询sno小于5的学生姓名

select sno,sname from student where sno<5;

③查询sno为5-10的行数据

select * from student where sno between 5 and 10;

④查询sno为5或者7的数据

select * from student where sno in(3,5);

总结

>、

not：非、不

in、not in ：在、不在

where

like模糊查询

①查询名字列包含zhang的学生姓名行

select * from student where sname like "%zhang%";

②查询sname列以a开头的行

select * from student where sname like "a%";

③查询sname列以i结尾的行

select * from student where sname like "%i";

④查询sname列第二个字符为h的列

select * from student where sname like "_h%";

⑤查询sname列第二个字符不为h的列

 select * from student where sname not like "_h%";

like通配符

%：匹配零个或者多个字符

_：匹配一个字符

where

逻辑连接符

逻辑连接符号主要包括：and、or、union

①查询中国人口超过100w人口的城市信息

select * from city where countrycode='CHN' and population>1000000;

②查询广东或者广西的城市信息

select * from city where district='guangdong' or district='guangxi';

select * from city where district='guangxi';

总结

and：与，两个条件同时满足的数据会被选中

or：或，满足一个或一个以上的数据会被选中

union：类似或，上下两个语句满足一个即选中

0 3

group by

    group by在绝大多数的情况下需要与where配合使用，group by主要适用于分组，group by一定需要配合聚合函数，常用的聚合函数有：max(),min(),avg(),count(),sum(),group_concat()、DISTINCT()

    group by的核心使用步骤：

    ①找出分组条件(哪个列作为分组列)

    ②使用适合的聚合函数(怎么计算)

group by

sum总和

①计算每个国家的人口数量

select CountryCode,sum(Population) from city group by countrycode;

②计算中国每个省的人口总数

# 由于省的人口总数=sum(城市人口) 有重复列的为省，所以分组列应该为省select District,sum(population) from city where countrycode='CHN' group by district;

group by

count计数

①统计每个国家的城市数量

select CountryCode,count(district) from city group by Countrycode;

②统计中国每个省的城市数量

select District,count(name) from city where countrycode='CHN' group by district;

0 4

having

    having与where类似，having属于后过滤，一般需要在group by + 聚合函数后，再做过滤时使用。

having

having使用

①统计中国每个省的总人口，只显示总人口大于500w的省

select District,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000;

0 5

order by排序

    order by排序，order by 排序的列 DESC 倒序 ，没有DESC为正序。

order by

排序

①统计中国每个省的总人口，只显示人口数大于500w信息，并且按照少到多排序

select District,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population);

0 6

limit分页

    limit分页显示结果集 一般配合order by使用，limit x -- 前x个，x，y表示开始为x，往再显示后面y个。

limit

limit分页

①统计中国每个省的总人口，只显示人口数大于500w信息，并且按照多到少排序，只显示前五名

select District,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) DESC limit 5;

0 7

别名和去重

    别名：给列取一个别名，有时候列名太长，可以取个简单易懂的别名做后续的处理和展示。

    去重：去掉重复的值，重复的只显示一个

select

别名

    给列取别名，可以在列后面直接添加一个别名，或者使用as 添加一个别名

select countrycode '国家代码',name as '城市名' from city where district='guangdong';

select

去重

    distinct是select中去重语句

①打印出所有的国家代码，且不重复

select distinct(countrycode) from city;

0 8

案例说明

   

    统计中国，每个省总人口，城市个数，城市名列表

SELECT District,SUM(Population),COUNT(id),NAME FROM world.`city` WHERE CountryCode='CHN' GROUP BY District;

    如果你也是这样做的，恭喜你，你会收到一个报错信息如下：

ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    这是什么原因呢？首先我们看看我们上面的指令想做出来的结果是怎么样的

省/州	总人口	城市个数	城市名称
广东省	10000000	10	广州
			深圳
			东莞
			...

    但是！mysql不支持一对多的结果显示，那我们怎么处理呢？这时候就可以使用到聚合函数group_concat，对多行数据转换为一行，达到一行对一行的效果。

省/州	总人口	城市个数	城市名称
广东省	10000000	10	广州,深圳,东莞...

 SELECT District,SUM(Population),COUNT(id),group_concat(NAME) FROM world.`city` WHERE CountryCode='CHN' GROUP BY District;

‍

附个人思维导图

end