• 基础语法
  • 检索数据
  • 过滤数据
  • 聚集函数

基础语法

检索数据

记录生疏的知识点

1. 在生产环境下，应尽量避免使用select *
   原因：将所有的列都检索出来增加数据库的负担。增加数据库的网络传输量。在日常的工作中往往不需要全部的列，需要养成良好的习惯
2. 去重记录
   distinct 是对后面列名的组合进行去重

SELECT DISTINCT attack_range FROM heros # 只有近战和远程两个
SELECT DISTINCT attack_range FROM heros # 有69条记录了

3. 排序记录

• 可根据多个字段排序，从前到后，前一个相同了，根据后一个排序
• 可根据非选择字段排序，如

SELECT NAME FROM heros ORDER BY hp_5s_growth DESC

• 位置：放在sql语句最后 （sql语句整体每一个位置很重要，后面会讲到）

4. 约束返回的条件
   这个其实不生疏，但是在使用的时候要反复提醒自己去这样做。
   约束返回数量可以减少数据表的网络传输量，提高查询效率。
5. select 的执行顺序
   重要：图像化想象就是从中间回到头再转到尾巴。下面的这个例子可以很好理解

SELECT DISTINCT player_id, player_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

6. count的讨论
   COUNT() = COUNT(1) > COUNT(字段)
   如果要统计某一个非空字段，则使用COUNT(字段)
   如果要统计COUNT()，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升

过滤数据

1. 比较运算符
   除了常规的大于、等于以及小于这些，还有between and，以及 is null
2. 逻辑运算符
   当 WHERE 子句中同时存在 OR 和 AND 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符。
   除了常规的or，and等，还有in，not
3. 通配符过滤
   前面是条件已经的情况，当条件待定的需使用 like 结合 % 以及 _ 等通配符

SELECT NAME,birthdate FROM heros
WHERE NAME LIKE '%太%'
结果：东皇太一、太乙真人

在实际中，尽量少用通配符。使用like检索字段，即使该字段有索引，也有可能使得索引失效。比如 name like '%太%' 或这 '%颇' 都会进行全表扫描。当匹配条件第一个字符不为 % 时，则不会全表

tips：

1. 对日期类型数据进行检索，所以使用到了 DATE 函数，将字段 birthdate 转化为日期类型再进行比较。

比如：date(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'

2. 为了避免全表扫描，需要在where 以及 order by 涉及到字段添加索引

聚集函数

定义：对于一批次的数据进行处理，输出一个具体的值。（多个输入，单个输出）。比较复杂的情况下，会先进行筛选，再进行汇聚
分类：MAX、MIN、AVG、COUNT以及SUM

1. 位置：放在select语句中。
2. 会自动会null的字段过滤。并且MAX和MIN可以用在字符串，根据字符进行排序，以及在使用它们俩时，不需要再使用distinct
3. 重要：结合group by一起使用聚合函数

SELECT COUNT(*) AS num, role_main 
	FROM heros
	GROUP BY role_main
	HAVING num >= 10  # having 可以把select中的字段拿过来作为筛选的条件

3.1 字段为null也会被分成一个分组
3.2 where和having的区别
都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。 where是先对原始表中的数据进行过滤，having再在其基础之上对过滤后的数据进行分组。用 WHERE 进行数据量的过滤，用 GROUP BY 进行分组
例子：筛选最大生命值大于 6000 的英雄，按照主要定位、次要定位进行分组，并且显示分组中英雄数量大于 5 的分组，按照数量从高到低进行排序。

SELECT COUNT(*) AS num, role_main, role_assist
	FROM heros
	WHERE hp_max > 6000  # 先筛选数据表中的数据
	GROUP BY role_main, role_assist  # 根据上面筛选后的数据再进行分组
	HAVING num > 5
	ORDER BY num DESC

练习题
1.筛选最大生命值大于 6000 的英雄，按照主要定位进行分组，选择分组英雄数量大于 5 的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。

SELECT COUNT(*) AS num, role_main, AVG(hp_max)
	FROM heros
        WHERE hp_max > 6000
	GROUP BY role_main
	HAVING num > 5
	ORDER BY num DESC

2.筛选最大生命值与最大法力值之和大于 7000 的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。

SELECT COUNT(*) hero_nums, ROUND(AVG(hp_max + mp_max), 2) AS avg_hpmp, ROUND(MAX(hp_max + mp_max), 2) AS max_hpmp, ROUND(MIN(hp_max + mp_max), 2) AS min_hpmp
	FROM heros
	WHERE (hp_max + mp_max) > 7000
	GROUP BY attack_range
	ORDER BY hero_nums