目录
一、检索数据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 要点:
- GROUP BY可以包含任意数目列,可以对分组组合(为了更细致地数据分组)
- 组合后,数据会针对最后指定的分组汇总 相当于excel的分类汇总
- 建立分组后,指定分组的所有列都会一起计算(不能从个别列取回数据)
- GROUP BY后跟的必须是实际存在的列
- GROUP BY后面不能跟聚集函数,不能用别名
- 如果指定分组列存在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出需要的列 → 排序