SQL必知必会 - 检索/排序/分组数据

目录

 一、检索数据Select from

1.检索单个列

2.检索多个列  ,

3.检索所有列  *

4.检索列的不同值   DISTINCT

5.限制结果

①限制最多返回多少行   TOP / LIMIT

②限制返回第几行到第几行  LIMIT OFFSET

6.注释

二、排序检索数据Order by

1.排序数据

2.按多列排序

3.按列位置排序

4.指定排序方向

三、创建分组Group By 

四、过滤分组 Having

五、分组、排序和子句顺序

1 分组和排序

2 SELECT子句顺序

---

 一、检索数据Select from

1.检索单个列

• 利用SELECT语句,从products表中检索一个名为prod_name的列
• 语句之间用分号隔开
• 语句不区分大小写
• 空格在处理时,会被系统忽略,可以写成长长的一句,或者用回车键

SELECT prod_name FROM Products;

2.检索多个列  ,

• 多个列名之间用逗号隔开

  SELECT prod_name,prod_id,prod_price FROM Products；

3.检索所有列  *

• 通配符*表示表中的所有列
• 能检索出未知列名的列

SELECT * FROM products;

4.检索列的不同值   DISTINCT

• 只返回该列中不同的值,相当于excel的删除重复值功能
• 将 DISTINCT 放在列名的前面
• 如检索的是多列,则相当于删除多列的重复值，合并组合,与excel同理

SELECT DISTINCT prod_name FROM products;

5.限制结果

①限制最多返回多少行   TOP / LIMIT

• LIMIT 和 TOP 都能对返回的结果限制行数
• LIMIT放在大表名称的后面，作为最后的语句
• TOP放在选取的列名前面
• 两行语句,都是只返回该列的前5行 ：

  SELECT TOP 5 prod_name FROM products;
  
  SELECT prod_name FROM products LIMIT 5 ;

②限制返回第几行到第几行  LIMIT OFFSET

• OFFSET是开端的意思。返回该列的第8 到 12行。即从第8行起的5行数据
• 5：检索的总行数是5
• 8：开始的行序数是8
• 注：如设置的检索行数,超过了原列的行数,则返回该列指定起始行到全部值

  SELECT prod_name FROM products LIMIT 5 OFFESET 8 ;

6.注释

注释字符后面跟的文本无效,系统不运行,仅作为注释。

• 行内注释：在语句中使用“--”符号,就可以添加注释
• 整行注释：将“#”字符置于行首,则该行全部为注释
• 多行注释：把“/*”和“*/”置于注释的首尾,可实现多行注释,中间的部分都作为注释不生效

SELECT prod_name FROM products LIMIT 5 OFFSET 8; --从第8行开始的5行返回值

二、排序检索数据Order by

1.排序数据

SELECT prod_name FROM products ORDER BY prod_name;

• 让prod_name列以字母顺序排序
• 注：确保order by位于SELECT语句的最后一个子句,即它是最后运行的

2.按多列排序

• 需要指定所按照排序的列名,中间用逗号隔开,原理和SELECT语句的多列选择相同
• 如选了3列出来,按照其中两列对其排序

SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_name,prod_id;

3.按列位置排序

• 按照相对列的位置,即第几列,去排序
• 如按照筛选出来的第2第3行排序

SELECT prod_name,prod_id,prod_price FROM products ORDER BY 2,3;

4.指定排序方向

• 系统默认为升序（从小到大， A TO Z），也可以用 ASC 
• 使用DESC（descend的缩写）则降序（大到小，Z TO A ）

注：DESC只作用到它直接碰到的前面的列名,不会跳过去作用在其他列

• 如将筛选出的列,按照价格,从大到小排序

SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price DESC;

• 因为DESC只作用在前面的直接列,所以下面语句按照prod_price 降序,而prod_name依然是默认的升序

SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_price DESC,prod_name;

三、创建分组Group By 

将数据分为多个逻辑组,从而能够对每个组进行聚集计算

• 运用GROUP BY语句,建立分组
• GROUP BY语句放在WHERE句后面,ORDER BY前面！  where...group by...order by...

e.g.分别计数每个供应商id出现的次数

该语句用了GROUP BY之后,就不会对整表计数,而是按照vend_id排列后分组对每个组进行聚集

e.g.分别计数每个供应商id出现的次数

SELECT COUNT(*) AS num_items
FROM products
GROUP BY vend_id;

• GROUP BY 要点：

1. GROUP BY可以包含任意数目列,可以对分组组合（为了更细致地数据分组）
2. 组合后,数据会针对最后指定的分组汇总 相当于excel的分类汇总
3. 建立分组后,指定分组的所有列都会一起计算（不能从个别列取回数据）
4. GROUP BY后跟的必须是实际存在的列
5. GROUP BY后面不能跟聚集函数,不能用别名
6. 如果指定分组列存在Null值,则所有的null值会作为一组

四、过滤分组 Having

因为 where 的操作对象是一条记录，比如说当一条记录xxx时，在什么条件下可以用 where

但涉及到多条记录时，如对象是从一组组中再过滤到"平均问题小于 5" 的小组，where就不适用。即where 过滤出某条记录，having从一组组记录中过滤掉哪几组

• where在分组前过滤，having在分组后过滤（where排除的行，不包括在分组里）
• where语句过滤的是特定的行的值,而不是列,所以where没有分组的作用

• having必须和group by组合使用
• having支持所有的where操作符

e.g1. 从ORDERS表里，选定出表里订单次数大于2的id

SELECT cust_id, COUNT(*) AS num_items
FROM orders
GROUP BY cust_id
HAVING COUNT(*)>=2;

e.g2. 从ORDERS表里，选定出售价大于等于4，且产品有2个以上的供应商名单

• 先用 where 过滤出售价大于等于4的供应商
• 再用having 按照vend_id分组
• 最后用 having 过滤出有2个以上产品的分组

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price>=4
GROUP BY vend_id
HAVING COUNT(*)>=2;

五、分组、排序和子句顺序

1 分组和排序

ORDER BY&GROUP BY对比

区别点	ORDER BY	GROUP BY
1	对产生的输出排序	针对行进行分组（但输出的可能不是分组的顺序）
2	任意列都能使用（非选择的列也可以使用）	只可能使用列or表达式列，且必须使用每个选择列表达式
3	不一定需要	若与聚集函数一起使用列or表达式，则必须使用

以某种方式分组，并不意味着结果会以此排序。∴用了group by分组后，还需要明确提供ORDER BY语句

2 SELECT子句顺序

• SELECT子句书写顺序

子句 说明 需要使用的情境 SELECT 选择要返回的列or表达式 必须使用 FROM 将在其检索数据的表 需要从表里选择数据的时候用 WHERE 行过滤 只在需要条件过滤行的时候用 GROUP BY 分组 需要按照组的形式，使用聚集函数的时候用 HAVING 组过滤 不必须使用 ORDER BY 对最后输出的数据排序 不必须使用

•  执行顺序： from → where → group by → having → select → order by
• 选定数据源 → 对选定的数据源筛选 → 将结果分组 + 分组后再筛选组 → select出需要的列 → 排序