MySQL学习记3（简单查询）

简单查询

---

该篇我们主要讲讲 SQL 里面的简单查询。查询的关键字是 SELECT ，它的用法非常丰富。

1. 计算表达式和函数的值

虽然 SELECT 语句通常用于从表中检索数据，但我们也可以用它计算表达式和函数的值。

select 3*2;
select now();
select concat('ab','cd');

2. 查询表中的字段

查询单个字段的值，比如：查询 heros 表中所有英雄的名字。

select name from heros;

查询多个字段的值，多个字段之间用 ','分隔。比如：查询 heros 表中所有英雄的名字，最大生命值，最大法力值以及主要角色定位。

select name, hp_max, mp_max role_main from heros;

还可以用 * 代指所有字段。比如：查询 heros 表中所有数据。

select * from heros;

注意：在生产环境中，尽量不要使用 * 通配符。因为查询不必要的数据会降低查询和应用程序的效率！

3. 使用 WHERE 子句过滤记录

WHERE 子句后面接逻辑表达式。如果逻辑表达式的结果为真，这条记录就会添加到结果集中，否则就

不会添加到结果集。

# 查询主要角色定位为‘法师’的英雄有哪些？
select name, role_main from heros where role_main <=> '法师';

# 查询最大生命值在[7000,8000]范围的英雄有哪些？
select name, hp_max from heros where hp_max between 7000 and 8000;

# 查询主要角色定位为'法师'或者'刺客'的英雄有哪些？
select name, role_main from heros where role_main in ('法师', '刺客');

# 查询姓'张'的英雄有哪些？
select name from heros where name like '张_%';

注意：LIKE 关键字一般是与通配符一起使用, 在 MySQL 中 ‘_’ 表示匹配单个字符, ‘%’ 表示匹配任意个字符。在不同的 DBMS 中，通配符的符号可能不一样。比如，在 ACCESS 中使用 ‘?’ 匹配单个字符，使用 ‘*’ 匹配任意个字符。

4. 给字段起别名

AS 可以给字段起别名。比如：

select name, hp_max as hp, mp_max as mp from heros;

注意：① AS 关键字可以省略，但是不推荐这样做。② AS 关键字不仅仅可以给字段起别名，还可以给表起别名。

5. 去除重复行

DISTINCT 可以对查询结果去重。

select distinct attack_range from heros;

注意：① DISTINCT 是对所有查询字段的组合进行去重，也就是说每个字段都相同，才认为两条

记录是相同的。② DISTINCT 关键字必须放在所有查询字段的前面。

6. 排序

ORDER BY 可以对结果集进行排序。 ASC 表示升序， DESC 表示降序，默认情况为升序。

select name, hp_max from heros order by hp_max;			# 升序
select name, hp_max from heros order by hp_max asc;		# 升序
select name, hp_max from heros order by hp_max desc;	# 降序

还可以对多个字段进行排序。即先按照第一个字段排序，当第一个字段相同时，再按照第二个字段排序，依此类推。

select name, hp_max, mp_max form heros order by hp_max asc, mp_max desc;

ORDER BY 可以对非选择字段进行排序，也就是说排序的字段不一定要在结果集中。

select name, hp_max from heros order by hp_max, mp_max desc;

甚至，我们还可以对计算字段进行排序。

select name, hp_max from heros order by (hp_max + mp_max) desc;

7. 限制结果集的数量

LIMIT 可以限制结果集的数量。它有两种使用方式： LIMIT offset, nums 和 LIMIT nums OFFSET 。(推荐使用第二种)

比如：我们想查询最大生命值最高的5名英雄。

select name, hp_max from heros order by hp_max desc limit 0, 5;		# 默认升序，降序要加desc

select name, hp_max from heros order by hp_max desc limit 5 offset 0;	

当 OFFSET 为 0 的时候，我们可以将其省略。

select name, hp_max from heros order by hp_max desc limit 5;

使用 LIMIT 可以很方便地实现分页查询。

select... from ... limit rows offset (page - 1) * row;

注意：不同的 DBMS 用来限制结果集的关键字是不一样的。比如，Microsoft SQL Server 和Access 使用的是 TOP 关键字。

8. 计算字段

计算字段并不实际存在于数据库表中，它是由表中的其它字段计算而来的。一般我们会给计算字段起一个的别名。

select name, hp_max + mp_max as total_max from heros;

9. 聚合函数

聚合函数是对某个字段的值进行统计的，而不是对某条记录进行统计。如果想计算某个学生各科成绩的总分，那么你应该使用计算字段。

聚合函数往往是搭配分组使用的。如果没有分组，那么聚合函数统计的是整个结果集的数据；如果分组

了，那么聚合函数统计的是结果集中每个组的数据。

SQL 中一共有 5 个聚合函数。分别为 COUNT() ， SUM() ， AVG() ， MAX() ， MIN() 。

a. COUNT()

• COUNT(*) 可以统计记录数。

select  count(*) from heros where hp_max > 6000;

• COUNT() 作用于某个具体的字段，可以统计这个字段的非 NULL 值的个数。

select count(role_assist) from heros;

b. SUM()

• SUM() 用于统计某个字段非 NULL 值的和。

select sum(hp_max) from heros;

c. AVG()

• AVG() 用于统计某个字段非 NULL 值的平均值。

select avg(hp_max) from heros;

d. MAX()

• MAX() 用于统计某个字段非 NULL 值的最大值。

select max(hp_max) from heros;

e. MIN()

• MIN() 用于统计某个字段非 NULL 值的最小值。

select min(hp_max) from heros;

f. DISTINCT

• 我们还可以对字段中不同的值进行统计。先用 DSITINCT 去重，再用聚合函数统计。

select count(distinct hp_max) from heros;

10. 分组

GROUP BY 可以对记录进行分组。

a. 搭配聚合函数使用

• 按照主要角色定位进行分组，并统计每一组的英雄数目。

select role_main, count(*) from heros group by role_main;

• 按照次要角色定位进行分组，并统计每一组的英雄数目。

select count(*), role_assist from heros group by role_assist;

你会发现 NULL 值也会被列为一个分组。在 heros 表中有 40 个英雄没有次要角色定位。

b. GROUP_CONCAT

• 如果我们想知道每种角色的英雄都有哪些，可以使用 GROUP_CONCAT() 函数。

select group_concat(name), role_main from heros group by role_main;

c. 多字段分组

我们可以对多个字段进行分组。也就是说，每个字段的值都相同的记录为一组。

select count(*) as num, role_main, role_assist from heros
group by role_main, role_assist
order by num desc;

d. HAVING 过滤分组

• HAVING 可以过滤分组。

比如：我们想要按照英雄的主要角色定位，次要角色定位进行分组，并且筛选分组中英雄数目大于 5 的组，最后根据每组的英雄数目从高到低进行排序。

select role_main, assist_main count(*) as num
from heros 
group by role_main, role_assist
having num > 5 
order by num desc;

WHERE 和 HAVING 的区别：WHERE 和 HAVING 都可以用来过滤数据，但是两者有着很明显的区别。WHERE 是分组前用来过滤记录的，HAVING 是分组后用来过滤分组的。

我们可以通过一个具体的例子来看一下它们之间的区别。

筛选最大生命值大于 6000 的英雄，按照主要角色定位，次要角色定位分组，并且筛选英雄数目大于 5 的分组，最后按照英雄数目从高到低进行排序。

select role_main, role_assist count(*) as num 
from heros
where hp_max > 6000
group by role_main, role_assist 
having num > 5 
order by num desc;

注意：虽然 DBMS 实现的时候，往往会对分组进行排序。但是如果没有明确的 ORDER BY 子句，

我们就不应该假定结果集是有序的。

11. SELECT 的顺序

SELECT 是 RDBMS 中执行最多的操作。我们不仅仅要理解 SELECT 的语法，还要理解它底层执行的原理。

有两个关于 SELECT 的顺序，我们需要记住。

a. 语法中关键字的顺序

select...  
from...  
where...
group by...
having...
order by...
limit...

b. 语句的执行顺序

不同的 RDBMS，它们 SELECT 语句的执行顺序基本是相同的。

from -> where -> group by -> having -> select -> distinct -> order by -> limit 

示例：

SELECT DISTINCT team_id, team_name, count(*) AS num # 顺序5 
FROM player JOIN team ON player.team_id = team.team_id # 顺序1 
WHERE height > 1.80 # 顺序2 
GROUP BY player.team_id # 顺序3 
HAVING num > 2 # 顺序4 
ORDER BY num DESC #顺序6 
LIMIT 2; #顺序7