SQL知识之单表查询和分组聚合

1、简单查询

SELECT * FROM user_info;              # 查询所有字段
SELECT id,name FROM user_info;        # 查询指定字段

SELECT DISTINCT name FROM user_info;  # DISTINCT，去除重复数据

SELECT name, salary*2 FROM user_info;  # 包含数学运算的查询
SELECT name as '姓名', salary*2 as '年薪' FROM user_info;  # 使用as来定义别名

# 使用CONCAT来连接字符串进行格式化显示
SELECT CONCAT(name, '的年龄为：', age) as '年龄' FROM user_info;

2、条件查询

2.1 WHERE

SELECT id,name FROM user_info WHERE id = 1;      # 单条件查询
SELECT id,name FROM user_info WHERE id != 1 AND  name = 'mkie';  # 多条件查询

下面的运算符可以在 WHERE 子句中使用：

2.2 BETWEEN AND

SELECT id,name FROM user_info WHERE id BETWEEN 1 AND 5;   # 按照范围查询
SELECT id,name FROM user_info WHERE id NOT BETWEEN 1 AND 5;   # 按照范围反向查询

2.3 IS NULL

SELECT id,name FROM user_info WHERE name IS NULL;    # 查询空值，注意不是空字符串
SELECT id,name FROM user_info WHERE name IS NOT NULL;    # 查询非空值

2.4 IN

SELECT id,name FROM user_info WHERE id IN (1, 3, 5);  # 从指定集合中查询，注意是(),不是[]
SELECT id,name FROM user_info WHERE id NOT IN (1, 3, 5);   # # 从指定集合中反向查询

2.5 LIKE

SELECT id,name FROM user_info WHERE name LIKE 'm%'     # 模糊匹配，查询以m开头的姓名
SELECT id,name FROM user_info WHERE name LIKE '%m'     # 模糊匹配，查询以m结尾的姓名
SELECT id,name FROM user_info WHERE name LIKE '%m%'     # 模糊匹配，查询以包含m的姓名
SELECT id,name FROM user_info WHERE name NOT LIKE '%m%'     # 模糊匹配，反向查询

# '_'表示匹配单个字符，以下例子为查询以第二个字符为m的姓名
SELECT id,name FROM user_info WHERE name LIKE '_m%'

3、排序查询（ORDER BY）

SELECT id,name FROM user_info ORDER BY name;   # 按照name排序，默认升序排序
SELECT id,name FROM user_info ORDER BY name DESC;   # 按照name排序，降序排序
SELECT id,name FROM user_info ORDER BY name DESC, age; # 多字段排序

SELECT id,name FROM user_info ORDER BY name LIMIT 2; # 取前两条记录，MySQL写法
# OFFSET 表示起始偏移量，即过滤掉前几条记录，以下是去掉前2行，取第3、4、5这3行
SELECT id,name FROM user_info ORDER BY name LIMIT 3 OFFSET 2;  
# 如果不使用 OFFSET，可以用如下写法，注意 LIMIT 2, 3 和  LIMIT 3 OFFSET 2 数字顺序是反过来的
SELECT id,name FROM user_info ORDER BY name LIMIT 2, 3;   

# 取前两条记录，SQL Server写法，SQL Server不支持LIMIT
SELECT TOP 2 id,name FROM user_info ORDER BY name; 

4、分组查询（GROUP BY）

SELECT COUNT(id) FROM user_info GROUP BY age;   # 根据age进行分组，统计每个年龄的人数
SELECT COUNT(id) FROM user_info GROUP BY age, name;   # 根据多个字段进行分组，

需要注意的是，使用分组查询时，SELECT的字段必须包含在分组或者是聚合函数里，否则会报错。

如果想要显示未在分组和聚合函数里的字段，可以使用MySQL中的GROUP_CONCAT()函数（SQL Server暂不支持）。

该函数用法如下：

GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

SELECT COUNT(id),  GROUP_CONCAT(name)  FROM user_info GROUP BY age; 

常用分组查询结合聚合函数来进行数据的统计，常见的聚合函数有：

• COUNT(*)，统计所有的记录，包括NULL值
• COUNT(field)，统计指定字段的记录，不包括NULL值
• 其余几个函数，都是根据具体字段进行统计，都会忽略NULL值
• 如果没有记录，COUNT返回0，其余几个函数返回NULL
• 注意聚合函数不能互相嵌套使用，如 MAX(COUNT(name)) 是错误的

5、HAVING 子句

5.1 简单理解HAVING和WHERE

• 在SQL增加HAVING子句的原因是因为 WHERE 无法与聚合函数一起使用，HAVING 子句可以用来筛选分组后的各种数据。
• WHERE 子句在聚合前先筛选记录，作用在GROUP BY 和 HAVING 子句之前
• HAVING 子句在分组后对组记录进行筛选

SELECT order_id	FROM order_info	WHERE goods in ('A','B','C')	
GROUP BY order_id	
HAVING COUNT(order_id) >= 3

5.2 HAVING和WHERE的区别

• WHERE 是数据从磁盘读入内存时一条一条判断的，是对原始数据进行筛选；而 HAVING 是将所有数据读入内存，在分组聚合后，根据 HAVING 的条件将不符合的数据删除，是对处理之后的数据进行筛选。
• HAVING 子句可以使用字段别名（MySQL支持，SQL Server不支持），WHERE 不可以使用
• HAVING 子句可以使用聚合函数，WHERE 不可以

5.3 其它一些需要注意的地方

• HAVING 筛选的字段必须出现在GROUP BY 子句或者聚合函数或者是SELECT语句中

• HAVING 通常是跟在GROUP BY 后面进行使用，不过没有GROUP BY也可以使用，这时候作用和WHERE一样，只不过此时 HAVING 筛选的字段必须要出现在SELECT语句中。

SELECT *  FROM score  WHERE sno = '103';
SELECT *  FROM score  HAVING sno = '103';     # 效果和WHERE一样
SELECT cno  FROM score  HAVING sno = '103';   # 报错