十二、单行函数
虚无缥缈的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(大小写)
-
upper(xxx):大写
-
SELECT UPPER('aaa') FROM dual; -- 输出:AAA
-
-
lower(xxx):小写
-
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(左右填充)
不足填充长度的才会自动填充,否则展示原数据
-
lpad:左填充
-
-- 格式: rpad(列名, 填充长度, 填充的字符) -- 栗子 SELECT LPAD('123', 6, '*') FROM DUAL; -- 输出:***123
-
-
rpad:右填充
-
-- 格式: 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 表达式 ,故略