MySQL数据库聚集函数Null处理&&计算年龄（Navicat Preminum 12）

聚集函数Null处理

相信大家在用聚集函数 SUM() 、AVG() 、 COUNT() 等遇到这样的情况——返回NULL

ps1：SUM(x)=NULL   ?      
ps2: AVG(x+y)=NULL   ?

这是为什么呢？这就涉及到数据库里的运算问题了，在数据存储时如果把一个数据存为NULL，那么它就是【空】或者【未知值】，数据计算时 x + null！= x ，而是 x+ null = null（！！！！）

这里有一点大家不要习惯得把 NULL == 0 当做是真的，它俩不是一回事。

那么问题来，如何解决这个尴尬局面呢？

ifnull(x,0)

• ifnull()函数是判断一个值是否为 null 的函数
• 若 x 为 null，那么我们把它替换为 0

实例：

创建部门表：

create table dept(
 deptno int primary key, -- 部门编号
 dname varchar(14) ,   -- 部门名字
 loc varchar(13)   -- 地址
);

插入数据：

insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');

创建员工表 emp：

create table emp(
 empno int primary key,-- 员工编号
 ename varchar(10), -- 员工姓名
 job varchar(9), -- 岗位
 mgr int,  -- 直接领导编号
 hiredate date,/* --入职日期*/
 sal int, -- 薪水
 comm int,  -- 提成
 deptno int not null, -- 部门编号
 foreign key (deptno) references dept(deptno)
);

插入数据：

insert into emp values(7369,'刘一','职员',7902,'1990-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1991-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1991-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1991-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1991-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1991-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1991-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1997-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1982-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1991-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1997-06-13',1100,null,20);
insert into emp values(7900,'钱十二','职员',7698,'1991-12-03',950,null,30);
insert into emp values(7902,'张十三','分析师',7566,'1991-12-03',3000,null,20);
insert into emp values(7934,'李十四','职员',7782,'1993-01-23',1300,null,10);

任务：
统计每个部门员工的平均工资，并把结果存入到一个新表dept_avgsal（deptno，davgsal）中,其中deptno为部门编号，davgsal为部门平均工资。

-- 创建dept_avgsal表
CREATE TABLE dept_avgsal(
 deptno INT,
  davgsal INT
);

-- 将结果存入dept_avgsal表中
INSERT INTO dept_avgsal
SELECT deptno,AVG(sal+ifnull(comm,0))   -- sal是薪水这里不考虑null，comm是提成可能为null
FROM emp
GROUP BY deptno;

结果：

给定年月日，计算年龄、工龄等

TIMESTAMPDIFF(YEAR,@birthday,CURDATE())  -- @birthday 为年月日的属性列名

实例说明（沿用上述数据）：

任务：
定义一个反应员工工龄的视图emp_sen（empno,ename, seniority），其中seniority为工龄。

CREATE VIEW emp_sen(empno,ename,seniority)
AS
SELECT empno,ename,TIMESTAMPDIFF(YEAR,hiredate,CURDATE())
FROM emp
WITH CHECK OPTION;

结果：