十二、单行函数

十二、单行函数

虚无缥缈的dual：

在Mysql和SQL Server中可以直接select 1不需要加from 表名就可以执行 ,而在Oracle中要满足select from的结构：
select 1不能实现满足上述结构，于是Oracle引入dual虚拟表来满足结构。 Oracle这样表示：select 1 from dual

现在一般线上使用的MySQL都是5.5以上的，在mysql里也存在和oracle里类似的dual虚拟表：官方声明纯粹是为了满足select … from…这一习惯问题，mysql会忽略对该表的引用。

1、upper/lower（大小写）

1. upper(xxx)：大写

   1. SELECT UPPER('aaa') FROM dual;
      -- 输出：AAA
      

2. lower(xxx)：小写

   1. SELECT LOWER('AAA') FROM dual;
      -- 输出：aaa
      

---

2、initcap（首字母大写）※注意：MySql不支持initcap！※

-- 格式： initcap(xxx)
-- 栗子
SELECT initcap('aaa') FROM DUAL;
-- Oracle输出：Aaa

---

3、concat（拼接）

-- 格式： concat(‘xxx’, 'xxx');可嵌套使用
-- 栗子
SELECT CONCAT(CONCAT('我是 ', 'AA'), ' 先生') AS "自我介绍" FROM DUAL;
--  输出：我是 AA 先生

---

4、distinct（去重）

-- 格式： distinct(列名)
-- 栗子
SELECT DISTINCT(job) FROM emp;

---

5、substr（截取）

-- 格式： 
-- substr(xxx, 3)   从第3位开始到最后
-- substr(xxx, -3)   截取倒数3个字符
-- substr(xxx, 2, 3)   从第2位开始截取3个字符
-- substr(xxx, -4, 3)   从倒数第4位开始截取3个字符
-- 栗子
SELECT SUBSTR('woshie...', 2, 2) FROM DUAL;
--  输出：os

---

6、replace（替换）

-- 格式： replace(列名, 被替换的字符, 替换为的字符)
-- 栗子
SELECT REPLACE('ACTM', 'T', '9') FROM DUAL;
--  输出：AC9M

---

7、instr（查找首次出现位置）

返回字符首次出现的位置（0是没有 <>0就是位置）
SELECT * FROM emp WHERE ename LIKE ‘%T%’【与like功能类似】
instr(‘syranmo’,‘an’,-1,1) 后两位非必填，第三位<0反向查找>0正向查找，第四位是查找第几次出现的’an’首字母出现位置【空格也算字符】

-- 格式： instr(列名, 要查找的字符串)
-- 栗子
SELECT INSTR('ABTD', 'T') AS "T首次出现位置" FROM DUAL;
--  输出：3

---

8、length（长度）

-- 格式： length(列名)
-- 栗子
SELECT LENGTH('abc') 名字长度 FROM DUAL;
--  输出：3

---

9、trim（去除首尾字母）

-- 格式： trim(字母 from 列名)
-- 栗子
SELECT TRIM('A' FROM 'ABABA') 首尾去除A FROM DUAL;
--  输出：BAB

---

10、lpad/rpad（左右填充）

不足填充长度的才会自动填充，否则展示原数据

1. lpad：左填充

   1. -- 格式： rpad(列名, 填充长度, 填充的字符)
      -- 栗子
      SELECT LPAD('123', 6, '*') FROM DUAL;
      --  输出：***123
      

2. rpad：右填充

   1. -- 格式： lpad(列名, 填充长度, 填充的字符)
      -- 栗子
      SELECT RPAD('123', 6, '*') FROM DUAL;
      --  输出：123***
      

---

11、数字函数

1、round（四舍五入）

-- 格式：
-- round(num)  四舍五入
-- round(num, n)   四舍五入保留小数点后n位
-- round(num, -n)   四舍五入保留小数点前n位以前的数据，也就是/10^n

-- 栗子
SELECT ROUND(123.556) FROM dual; -- 输出：124
SELECT ROUND(123.556, 2) FROM dual; -- 输出：123.56
SELECT ROUND(123.556, -2) FROM dual; -- 输出：100

2、mod（取余）

-- 格式：mod(num1, num2)
-- 栗子
SELECT MOD(123, 10) FROM dual;
--  输出：3

3、~trunc（截取）※注意：MySql不支持trunc！※

只保留有效位数，用法和round一样，功能和round区别：直接截取不进行四舍五入

-- 格式：mod(num1, num2)
-- 栗子
SELECT TRUNC(123.456) FROM dual; -- 输出：123 -- Oracle用法
SELECT TRUNC(123.456, 2) FROM dual; -- 输出：123.45 -- Oracle用法

---

12、通用计算

1、null

任何数值和null计算结果都是null

2、nvl（判空）※注意：MySql不支持nvl！※

-- 格式：nvl(参数1, 参数2) 
-- 如果参数1为null，则取值参数2，否则取值参数1
-- 栗子
SELECT nvl(null, 2) FROM dual; -- Oracle用法
-- 输出：2

3、nvl2（判空2）※注意：MySql不支持nvl2！※

-- 格式：nvl2(参数1, 参数2, 参数3)
-- 参数1是null返回参数3，否则返回参数2
-- 栗子
SELECT nvl(null, 2, 3) FROM dual; -- Oracle用法
--  输出：3

4、nullif（返null判定）

-- 格式：nullif(num1, num2)
-- 如果两个参数相等则返回null
-- 栗子
SELECT nullif(2, 2) FROM dual;
--  输出：null

5、ifNull（判空）

-- 格式：IFNULL(num1, num2)
-- 如果参数1为null，则取值参数2，否则取值参数1
-- 栗子
SELECT IFNULL(null, 2) FROM dual;
-- 输出：2

---

13、等值判断

1、case 表达式

-- 格式
case 表达式 
 when 值1 then 返回值
 when 值1 then 返回值
  ...
 [ else 其他情况返回值 ]
end

-- 栗子
SELECT
CASE '555'
    WHEN 'SALESMAN' THEN 1000
        WHEN 'MANAGER' THEN 2000
            ELSE 3000
END
FROM DUAL;
-- 输出：3000

2、decode※注意：MySql的decode/encode用于解/加密！※

-- decode(表达式, 值1, 返回值1, 值2, 返回值2, ... ,默认值)
-- 栗子
SELECT
DECODE('aaa', 'a', 1000, 'aa', 2000, 3000) -- Oracle用法
FROM DUAL;
-- 输出：3000

---

14、非等值判断

同“13、等值判断”使用case 表达式 ，故略