15 单行处理函数

15 单行处理函数

    什么是单行处理函数？

        输入一行，输出一行。

        

    计算每个员工的年薪？

        select ename,(sal + comm) * 12 as yearsal from emp;

        重点：所有数据库都是这样规定的，只要有null参与的运算结果一定是null。

        使用ifnull函数

            select ename,(sal + ifnull(comm,2)) * 12 as yearsal from emp;

                +--------+----------+

                | ename  | yearsal  |

                +--------+----------+

                | SMITH  |  9624.00 |

                | ALLEN  | 22800.00 |

                | WARD   | 21000.00 |

                | JONES  | 35724.00 |

                | MARTIN | 31800.00 |

                | BLAKE  | 34224.00 |

                | CLARK  | 29424.00 |

                | SCOTT  | 36024.00 |

                | KING   | 60024.00 |

                | TURNER | 18000.00 |

                | ADAMS  | 13224.00 |

                | JAMES  | 11424.00 |

                | FORD   | 36024.00 |

                | MILLER | 15624.00 |

                +--------+----------+

        

        ifnull() 空处理函数？

            ifnull(可能为null的数据，被当做什么处理)：属于单行处理函数。

            

        select ename,ifnull(comm,0) as comm from emp;

            +--------+---------+

            | ename  | comm    |

            +--------+---------+

            | SMITH  |    0.00 |

            | ALLEN  |  300.00 |

            | WARD   |  500.00 |

            | JONES  |    0.00 |

            | MARTIN | 1400.00 |

            | BLAKE  |    0.00 |

            | CLARK  |    0.00 |

            | SCOTT  |    0.00 |

            | KING   |    0.00 |

            | TURNER |    0.00 |

            | ADAMS  |    0.00 |

            | JAMES  |    0.00 |

            | FORD   |    0.00 |

            | MILLER |    0.00 |

            +--------+---------+