MySQL -单表查询

from

from的两种常用方式：

SELECT col1,col2 FROM 表;  -- 查询指定列
SELECT * FROM 表；   -- 全表查询，不要在生产中使用，性能太差

一般在生产中，select语句不加where语句是不允许通过的。

在查询中也可以使用四则运算：

SELECT population*20,population FROM city;

where

where是过滤的意思，可以加各种过滤条件，基本语法：

SELECT col1,col2 FROM TABLE WHERE col 条件;

来看常用的用法。

where配合等值查询

等值查询也就是col=xx的操作：

-- 查询中国(CHN)所有城市信息
SELECT * FROM city WHERE countrycode='CHN';

-- 查询中国郑州市信息
SELECT * FROM city WHERE NAME='zhengzhou';

-- 查询中国河南省所有城市信息
SELECT * FROM city WHERE district='henan';

where配合比较操作符

比较运算符也就是>、>=、<、<=、!=(<>)这些操作了：

-- 查询世界上人口小于十万人的城市
SELECT * FROM city WHERE population<100000;

-- 查询世界上人口大于一千万人的城市
SELECT * FROM city WHERE population>10000000;

-- 不等于这里，用 <> 和 != 都可以
SELECT * FROM city WHERE countrycode='CHN' AND DISTRICT!='shanghai';
SELECT * FROM city WHERE countrycode='CHN' AND DISTRICT<>'shanghai';

where配合逻辑运算符

逻辑运算符这里常用的有and or，not用的少，这里就不说了：

-- 查询中国人口数大于800万的城市
SELECT * FROM city WHERE countrycode='CHN' AND population>8000000;

-- 查询中国或美国城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

where配合模糊查询

-- 查询中国以"hu"开头省的城市信息
SELECT * FROM city WHERE district LIKE 'hu%';

like查询中，支持下面两种符号：

%，任意长度字符，注意，条件左边不能使用%，因为这样查询时不走索引(索引遵循最左原则)。
_，任意一个字符。

where配合in语句

-- 查询中国或美国城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
SELECT * FROM city WHERE countrycode IN ('CHN', 'USA');

上面两条语句是等价的，性能也是一样的，虽然性能都不怎么样！

where配合between and语句

-- 查询世界上人口数量在100万-200万之间的城市
SELECT * FROM city WHERE population<=2000000 AND population>=1000000;
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;

where配合is语句

判断一个字段是否为空不用等号，而是使用is语句：

-- 返回city表中countrycode是CHN的，并且district字段不为空的前10条数据
SELECT * FROM city WHERE countrycode='CHN' AND district IS NOT NULL LIMIT 10;

group by+聚合函数

根据by后面的(一个或多个)条件进行分组，便于统计。

常用聚合(统计)函数

max()：最大值。
min()：最小值。
avg()：平均值。
sum()：总和。
count()：个数。

另外再加一个函数：

group_concat()：列转行。

示例：

-- 统计世界上每个国家的总人口数量
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;

-- 统计中国每个省的总人口数量
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;

-- 统计世界上每个国家的城市数量
SELECT countrycode,COUNT(NAME) FROM city GROUP BY countrycode;
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;

-- 统计中国每个省的城市列表
SELECT district,GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;

-- 统计中国每个省的平均人口
SELECT district,AVG(population) FROM city WHERE countrycode='CHN' GROUP BY district;

在MySQL的SQL执行逻辑中，where条件必须放在group by前面！也就是先通过where条件将结果查询出来，再交给group by去分组，完事之后进行统计。

除此之外，如果使用unique字段作为分组依据，这样的话每条记录都自成一组，这么做是没有意义的

having

-- 统计中国每个省的总人数，只打印人口数量小于50万的省份
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district 
HAVING SUM(population)<500000;

注意，having子句是后过滤语句，在group by后面再进行过滤，并且having子句不走索引，针对这个问题，我们一般采用临时表来解决。

order by + limit

order by通常和limit搭配使用。
order by在having语句后面执行。
学到这里，我们也能发现，MySQL中的SQL语句的执行顺序

SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

示例：

-- 查询中国的城市信息，并且按照人口数量降序排序
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

-- 查询美国的城市信息，并且按照人口数量升序排序
SELECT * FROM city WHERE countrycode='USA' ORDER BY population ASC;

-- 统计中国每个省的总人口，找出大于500万的，并按照人口数量降序排序
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district 
HAVING SUM(population)>5000000 
ORDER BY SUM(population) DESC;

-- 统计中国每个省的总人口，找出大于500万的，并按照人口数量降序排序，只打印前3条结果
SELECT district,SUM(population) 
FROM city 
WHERE countrycode='CHN' 
GROUP BY district 
HAVING SUM(population)>5000000 
ORDER BY SUM(population) DESC
LIMIT 3;

MySQL中默认排序是升序ASC；降序是DESC，还有一种情况是，比如说按照年龄排序，但是有的年龄是相同的，该怎么排序，这里我们可以这样处理：

-- 根据age升序排序，如果age相同，这按照id降序排序
SELECT * FROM employee ORDER BY age,id desc;

下来来解释LIMIT语句为什么通常和ORDER BY一起使用，因为有序的或经过筛选的数据更有意义，无序的数据使用LIMIT在实际生产中意义不大；
再来说说LIMIT的常用用法：

LIMIT N        -- 返回前N行
LIMIT N,M    -- 跳过前N行，返回M行,如LIMIT 5,5，意思是跳过前5行，从第6行开始显式5条
-- 上下两个用法相反，推荐使用上面的
LIMIT M OFFSET N    -- 显式M行，跳过N行，如LIMIT 5,3，意思是跳过前3行，从第4行开始，显式5行

正则表达式查询

-- 查询中国重庆
SELECT * FROM city WHERE countrycode='CHN' AND district REGEXP '^C.*g$';

distinct

distinct去重，来看示例：

-- 查询世界上所有国家码
SELECT countrycode FROM city; -- 返回结果有重复
SELECT DISTINCT(countrycode) FROM city;  -- DISTINCT 去重

联合查询-union

联合查询的意思就是将两个同类型的结果集合并成一个，比如：

-- 查询中国和美国的城市信息
SELECT countrycode,NAME FROM city WHERE countrycode IN ('CHN','USA');

示例的意思是将查询到的中国城市信息的结果加上美国的城市信息加一起再返回，除了上述使用 IN 语句来完成也可以使用联合查询来完成，这里也更推荐使用联合查询：

-- 查询中国和美国的城市信息
SELECT countrycode,NAME FROM city WHERE countrycode='CHN'
UNION ALL
SELECT countrycode,NAME FROM city WHERE    countrycode='USA';

上述UNION ALL就是拼接了两个同类型的结果，当然，你还可以继续拼接：

-- 查询中国(CHN)、美国(USA)、日本(JPN)、英国(GBR)的城市信息
SELECT countrycode,NAME FROM city WHERE countrycode='CHN'
UNION ALL
SELECT countrycode,NAME FROM city WHERE    countrycode='USA'
UNION ALL
SELECT countrycode,NAME FROM city WHERE    countrycode='JPN'
UNION ALL
SELECT countrycode,NAME FROM city WHERE    countrycode='GBR';

注意，UNION ALL的性能高于IN、OR语句；所以，如果可以的话，我们尽量使用UNION ALL语句来代替IN、OR语句。

另外，在MySQL中，不是SQL语句越短性能越高！

最后，再来补充一下，联合查询有两种写法：

UNION：在拼接结果集时，带有去重功能，当然，去重就意味着会有性能消耗。
UNION ALL：没有去重功能。

以上就是单表查询中常用的语句用法了。

posted @ 2020-12-08 16:22 Tracydzf 阅读(157) 评论(0) 编辑收藏举报

刷新页面返回顶部