《SQL必知必会》笔记

数据查询

SELECT
作用：从一个或多个表（试图）中检索数据

子句顺序

检索不同的值 DISTINCT

注意：查询的结果列中不能部分使用distinct。distinct 关键字作用于所有的列，不仅仅是跟在其后的那一列。
示例：
数据表：

select distinct vend_id,prod_price from products;
返回：

限制查询数量

返回前5行数据

(SQL SERVER)select TOP 5 ... from ...
(DB2) select ... from ... FETCH FRIST 5 ROWS ONLY;
(Oracle）select ... from ... where ROWNUM <=5;
(MYSQL等）select ... from ... LIMIT 5;

返回后5行数据

-- 从第5行起的5行数据
select ... from ... limit 5 offset 5;

WHERE 子句操作符

创建计算字段

拼接字符

+, ||, concat。不同数据库不一样

(SQL SERVER)select vend_name + '(' + vend_country + ')';
(DB2、pg、oracle) select vend_name ｜｜ '(' ｜｜ vend_country ｜｜ ')';
(MYSQL）select concat(vend_name , '(' , vend_country , ')');

去掉字符空格

Rtrim(), Ltrim(),trim() 
--去掉右边空格
select Rtrim(vend_name)
--去掉左边空客
select Ltrim(vend_name)
-- 去掉两边的空格
select trim(vend_name)

使用别名AS

注意：别名的名字即可以是一个单词，也可以是一个字符串。如果是后者，字符串需要括在引号中。一般不建议使用字符串作为别名，会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

执行算数计算

select order_num,
	   sum( item_price * quantity ) as order_amount
from order_items

如果省略了from子句，select就是简单的访问和处理表达式，比如’select 3*2’会返回6

聚集函数

函数	含义	备注
AVG()	平均值	忽略NULL值
MAX() MIN()	最大值/最小值	忽略NULL值
COUNT	特定列中具有值的行进行计数	Count(column)忽略NULL值,Count(*)包含NULL值
SUM	求和	忽略NULL值

注意：
所有聚集函数都可以使用[distinct]，如COUNT(distinct product_name)
利用标准的算数操作符，所有聚集函数都可用来执行多个列上的计算，如avg(item_pricequantity)
所有聚集函数指定列名时都会忽略NULL值的行，count()则不会忽略

分组查询

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

创建分组

select vend_id ,count(*) as num_prods from products
GROUP BY vend_id
/** 执行说明
group by 子句指示DBMS按vend_id排序并分组数据，对每个vend_id而不是整个表计算num_prods一次
**/

使用注意

group by 子句可以包含任意数据的列，对分组的内部再进行分组；
如果使用了嵌套分组，数据将在最后指定的分组上进行汇总；
group by 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果select中使用表达式，则必须在group by子句中指定相同的表达式，不能使用别名。
大多数sql实现不允许group by 列带有长度可变的数据类型。
除聚集计算语句外，select语句中的每一列都必须在group by 子句中给出。
如果分组列中包含具有null值的行，则null将作为一个分组返回。

过滤分组

语句：HAVING
having是基于完整的分组而不是个别的行进行过滤的。是对select语句中的聚集函数进行过滤。

为什么select语句中的每一列都必须在group by子句中给出？

因为被分到一个组的数据的其他列的值不一定都相等，数据库不知道该返回什么。除非返回的列的值也是每个分组中的值都一样。
比如products表中的数据如下：

此时查询每个供应商提供的产品个数，3个供应商分为3组，应该为：
select vend_id ,count(*) as num_prods from products GROUP BY vend_id
如果返回的列增加一个：prod_price
select vend_id ,prod_pric,count(*) as num_prods from products GROUP BY vend_id
此时在每个分组里，prod_price的值是不一样的，数据库不知道该返回什么，就会报错。如果额外的列在同一个分组里值都一样，就不会报错。
mysql的ONLY_FULL_GROUP_BY错误
注意：如果select中返回的列不在group by中mysql会报ONLY_FULL_GROUP_BY错误，可以在列上使用any_value()，数据库会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义。在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list中的表达式的值
MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据

子查询

利用子查询过滤

-- 查询购买过单价>10的产品的顾客信息
select cust_id,customers.cust_name,cust_contact
from customers
where cust_id in
	(select