SQL学习笔记(六)
-------------------------------------------------分割线-----------------------------------------------------------
SQL 函数
内建 SQL 函数的语法是:
SELECT function(列) FROM 表
函数的类型
在 SQL 中,基本的函数类型和种类有若干种。函数的基本类型是:
Aggregate 函数
Scalar 函数
合计函数(Aggregate functions )
Aggregate 函数的操作面向一系列的值,并返回一个单一的值。
注释 :如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句,则这个 SELECT 必
须使用 GROUP BY 语句!
Scalar 函数
Scalar 函数的操作面向某个单一的值,并返回基于输入值的一个单一的值。
SQL G AVG 函数
定义和用法
AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
SQL AVG() 语法
SELECT AVG(column_name) FROM table_name
实例1:
我们希望计算 "OrderPrice" 字段的平均值。
我们使用如下 SQL 语句:
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
实例2:
现在,我们希望找到 OrderPrice 值高于 OrderPrice 平均值的客户。
我们使用如下 SQL 语句:
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
SQL COUNT() 函数
COUNT() 函数返回匹配指定条件的行数。
SQL COUNT() 语法
SQL COUNT(column_name) 语法
COUNT(column_name) 函数返回指定列的值的数目(NULL 不计入):
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) 语法
COUNT(*) 函数返回表中的记录数:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目:
SELECT COUNT(DISTINCT column_name) FROM table_name
注释:COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server,但是无法用于 Microsoft
Access
差不多的函数有:first(),lat(),sum(),max(),min(),等都有类似的作用,这里都不一一列出了。
SQL P GROUP Y BY 语句
数 合计函数 (如 比如 SUM) 加 常常需要添加 GROUP BY 语句。
GROUP BY 语句
GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。
SQL GROUP BY 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
现在,我们希望查找每个客户的总金额(总订单)。
我们想要使用 GROUP BY 语句对客户进行组合。
我们使用下列 SQL 语句:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
让我们看一下如果省略 GROUP BY 会出现什么情况:
SELECT Customer,SUM(OrderPrice) FROM Orders
上面的结果集不是我们需要的。
那么为什么不能使用上面这条 SELECT 语句呢?解释如下:上面的 SELECT 语句指定了两列(Customer 和 SUM(OrderPrice))。"SUM(OrderPrice)" 返回一个单独的值("OrderPrice" 列的总计),而 "Customer" 返回 6 个值(每个值对应 "Orders" 表中的每一行)。因此,我们得不到正确的结果。不过,您已经看到了,GROUP BY 语句解决了这个问题。
SQL G HAVING 子句
HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
语法如下:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
现在,我们希望查找订单总金额少于 2000 的客户。
我们使用如下 SQL 语句:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
现在我们希望查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。
我们在 SQL 语句中增加了一个普通的 WHERE 子句:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
1.SqlServer中聚合函数要求不能出现在where中
2.where 可以和 group by连用 但效果和having是不同的 where要求必须在group by 前面..意思是先过滤再分组 而 having是必须在group by后面连用 是分组后的过滤 所以过滤条件在什么位置是有很大区别的
3.sql中 要求前面有聚合函数和其他字段的,group by中必须把不是聚合函数的字段 加进去
SQL中,where,group by,having的区别?
where:数据库中常用的是where关键字,用于在初始表中筛选查询。它是一个约束声明,用于约束数据,在返回结果集之前起作用。如下面这个例子,从user表中查询出userDepartmentId等于2的数据
select * from dbo.user where userDepartmentId=2
group by:对select查询出来的结果集按照某个字段或者表达式进行分组,获得一组组的集合,然后从每组中取出一个指定字段或者表达式的值。
在说group by的时候,我们还需要了解聚合函数,聚合函数是SQL语言中一种特殊的函数。例如:
count(*):获取数量
sum():求和(这里要注意求和是忽略null值的,null与其他数值相加结果为null,所以可以通过ifnull(xxx,0)将null的值赋为0)
avg():求平均数
max():求最大值
min():求最小值
这些函数和其它函数的根本区别就是它们一般作用在多条记录上。
我们需要注意的是:在使用group by的SQL语句中,select中返回的字段,必须满足以下两个条件之一:
1.包含在group by语句的后面,作为分组的依据;
2.这些字段包含在聚合函数中。
从刚才的那个例子中,我们查询出每个城市,相同年龄的员工数量:
select city, count(*),age from dbo.user where departmentID=2 group by city,age
having:用于对where和group by查询出来的分组经行过滤,查出满足条件的分组结果。它是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作。
所以having的使用需要注意以下几点:
1.having只能用于group by(分组统计语句中)
2.where 是用于在初始表中筛选查询,having用于在where和group by 结果分组中查询
3.having 子句中的每一个元素也必须出现在select列表中
4.having语句可以使用聚合函数,而where不使用。
还是刚才的例子,我们进一步整理,查询员工数量大于20的城市和年龄段
select city, count(*),age from dbo.user where departmentID=2 group by city,age having age >40
回到开头的那个问题:当一个语句中同时含有where、group by 、having及聚集函数时,执行顺序如下:
执行where子句查找符合条件的数据;
使用group by 子句对数据进行分组;对group by 子句形成的组运行聚集函数计算每一组的值;
最后用having 子句去掉不符合条件的组。
需要注意的是,
having 子句中的每一个元素也必须出现在select列表中。有些数据库例外,如oracle.
having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
having子句限制的是组,而不是行。where子句中不能使用聚集函数,而having子句中可以。
当加上其他sql语句时,执行顺序如下:
S-F-W-G-H-O 组合
select –>where –> group by–> having–>order by
顺序是不能改变的