mysql函数怎么执行 mysql函数nvl

1. 组函数（统计、聚合、多行函数）

组函数操作行集，给出每组的结果。组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。

组函数与单行函数区别：

单行函数对查询到每个结果集做处理，而组函数只对分组数据做处理。

单行函数对每个结果集返回一个结果，而组函数对每个分组返回一个结果。

------组函数的类型 （Oracle与MySQL下相同）：

• AVG 平均值

• COUNT 计数

• MAX 最大值

• MIN 最小值

• SUM 合计

------组函数的语法

使用组函数的原则

• 用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。

• 所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。

2. 组函数的使用

2.1使用 AVG 和 SUM 函数 ------------参数类型只能是数字类型

AVG(arg)函数：对分组数据做平均值运算。

arg:参数类型只能是数字类型。

SUM(arg)函数：对分组数据求和。

arg:参数类型只能是数字类型。

示例：

求雇员表中的的平均薪水与薪水总额

select avg(salary),sum(salary) from employees;

2.2 使用 MIN 和 MAX 函数

MIN(arg)函数：求分组中最小数据。

arg:参数类型可以是字符、数字、日期。

MAX(arg)函数：求分组中最大数据。

arg:参数类型可以是字符、数字、日期。

示例：

求雇员表中的最高薪水与最低薪水

select max(salary),min(salary) from employees;

字符类型：

select max(last_name),min(last_name) from employees;

日期类型：

select max(hire_date),min(hire_date) from employees;

2.3 使用 COUNT 函数

COUNT 函数：返回一个表中的行数。

COUNT 函数有三种格式：

• COUNT(*)

• COUNT(expr)

• COUNT(DISTINCT expr)

2.3.1 COUNT(*)

返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数。

示例：

返回查询结果的总条数

select count(*) from employees;

2.3.2 COUNT(expr)函数

非空值的数

示例：

显示部门 80 中有佣金的雇员人数

select count(commission_pct) from employees where department_id = 80;

2.3.3 COUNT(DISTINCT expr)：

禁止计算在一列中的重复值

示例：

显示 EMPLOYEES 表中不重复的部门数

select count(distinct department_id) from employees;

组函数和 Null 值

所有组函数忽略列中的空值。

在组函数中使用 NVL 函数来处理空值。

示例1：

计算有佣金的员工的佣金平均值

select avg(commission_pct) from employees;

示例2：

计算所有员工的佣金的平均值

select

3. 创建数据组(GROUP BY)

可以根据需要将查询到的结果集信息划分为较小的组，用 GROUP BY 句实现

3.1 GROUP BY 子句语法

GROUP BY 子句可以把表中的行划分为组。然后可以用组函数返回每一组的摘要信息

3.2 使用分组原则

• 如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。

• 使用 WHERE 子句，你可以在划分行成组以前过滤行。

• 在 GROUP BY 子句中必须包含列。

• 在 GROUP BY 子句中你不能用列别名，可以使用表别名进行指向

• 默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。可以用 ORDER BY子句覆盖这个默认值。

3.3 GROUP BY 子句的使用

我们可以根据自己的需要对数据进行分组，在分组时，只要将需要做分组的列的列名添加到 GROUP BY 子句后侧就可以。GROUP BY 列不必在 SELECT 选中的列的列表中。

示例：

select department_id , avg(salary) from employees e group by e.department_id;

3.4 多于一个列的分组

示例：

显示在每个部门中付给每个工作岗位的合计薪水的报告

select e.department_id,e.job_id,sum(salary)from employees  e group by e.department_id,e.job_id order by e.department_id;

3.5 GROUP BY 子句的执行顺序

先进行数据查询，在对数据进行分组，然后执行组函数

3.6 非法使用 Group 函数的查询

在 SELECT 列表中的任何列必须在 GROUP BY 子句中。

• 在 GROUP BY 子句中的列或表达式不必在 SELECT 列表中。

3.7 约束分组结果

HAVING 子句 ：

HAVING 语句通常与 GROUP BY 语句联合使用，用来过滤由 GROUP BY 语句返回的记录集。

HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。

HAVING 子句语法：

示例1：

显示那些最高薪水大于 $10,000 的部门的部门号和最高薪水。

select e.department_id,max(e.salary) from employees e group by e.department_id having max(e.salary) > 10000;

示例2：

查询那些最高薪水大于 $10,000 的部门的部门号和平均薪水

select e.department_id,avg(e.salary) from employees e group by e.department_id having max(e.salary) > 10000;

3.8 嵌套组函数

在使用组函数时我们也可以根据需要来做组函数的嵌套使用。

示例：

显示部门中的最大平均薪水

select max(avg(e.salary)) from employees e group by e.department_id;

4. 子查询

子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。

组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。内查询或子查询返回一个值给外查

询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值

4.1 子查询语法

注意：

• 子查询 (内查询) 在主查询之前执行一次

• 子查询的结果被用于主查询 (外查询)

可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句

• HAVING 子句

• FROM 子句

4.2 使用子查询的原则 ：

• 子查询放在圆括号中。

• 将子查询放在比较条件的右边。

• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

示例：

谁的薪水比 Abel 高

使用内连接实现：

select e2.last_name,e2.salary from employees e1,employees e2 where e1.last_name = 'Abel' and e2.salary > e1.salary;

使用子查询实现：

select last_name,salary from employees where salary > (select salary from employees where last_name = 'Abel');

4.3 子查询的类型

单行子查询：子查询语句只返回一行的查询

多行子查询：子查询语句返回多行的查询

单行子查询

• 仅返回一行

• 使用单行比较符

主查询对子查询结果的单行比较运算符：

示例1：

显示那些 job ID 与雇员 141 相同的雇员的名字与 job ID

select e.last_name,e.job_id from employees e 
      where e.job_id = (select em.job_id from employees em where em.employee_id = 141);

示例2：

显示 job ID 与雇员 141 相同，并且薪水 高于雇员 143 的那些雇员

select e.last_name,e.job_id,e.salary from employees e 
        where e.job_id = (select em.job_id from employees em where em.employee_id = 141)
        and e.salary > (select emp.salary from employees emp where emp.employee_id = 143);

A. 在子查询中使用组函数

在子查询中也可使用组函数

示例：

显示所有其薪水等于最低薪水的雇员的 last name、job ID 和 salary

select e.last_name,e.job_id,e.salary from employees e where e.salary = (select min(salary) from employees);

带子查询的 HAVING 子句

可以在 WHERE 子句中使用子查询，也可以在 HAVING 子句中使用子查询

示例：

显示所有其最低薪水小于 部门 50 的最低薪水的部门号和最低薪水

select e.department_id,min(salary) from employees e group by e.department_id 
      having  min(salary) <= (select min(salary) from employees em where department_id = 50);

子查询错误

使用子查询的一个常见的错误是单行子查询返回了多行

4.3.2 多行子查询

• 返回多于一行

• 使用多行比较符

主查询对子查询的多行比较运算符

在条件中也可使用 NOT 进行取反

A. 在多行子查询中使用 IN 运算符

示例：

查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID

select e.last_name,e.salary,e.department_id from employees e 
     where e.salary in (select min(salary) from employees em group by em.department_id);

B. 在多行子查询中使用 ANY 运算符

< ANY 意思是小于最大值。

>ANY 意思是大于最小值。

示例：

显示工作岗位不是 IT_PROG 的雇员，并且这些雇员的的薪水少于 IT_PROG 工作岗位 的雇员的 ID、名字、工作岗位和薪水

select e.employee_id,e.last_name,e.job_id,e.salary from employees e
   where e.salary < any (select em.salary from employees em where em.job_id = 'IT_PROG') and e.job_id <> 'IT_PROG';

C. 在多行子查询中使用 ALL 运算符

<ALL 意思是小于最小值。

>ALL 意思是大于最大值。

示例：

显示那些薪水低于工作岗位 IT_PROG 的最低薪水，并且工作岗位不是 IT_PROG 的所有雇员

select e.employee_id,e.last_name,e.job_id,e.salary from employees e
   where e.salary < all(select em.salary from employees em where em.job_id = 'IT_PROG') and e.job_id <> 'IT_PROG';

ANY 与 ALL 的区别：

ANY: >ANY 表示至少大于一个值，即大于最小值；<ANY 表示至少小于一个值，即小于最大值

ALL: >ALL 表示大于每一个值，既大于最大值； <ALL 表示小于每一个值，既小于最小值