数据库开发（三）函数使用

函数：oracle内置了很多函数

单行函数：作用于一行，一行可以得到一个结果

多行函数（组函数）：多行（一组）可以得到一个结果

---

单行函数：

• 字符函数
• 数值函数
• 日期函数
• 转换函数

字符函数：

• LOWER：转换成小写
• UPPER：装换成大写
• INITCAP：每个单词首字母变大写，其余小写，单词用空格隔开
• CONCAT：字符串连接（拼接）
• SUBSTR(str,start,length)：字符串的截取    数据库中位置从1开始，而不是像编程语言中从1开始
• LENGTH：求字符串的长度
• NVL：空值置换

eg1：大小写转换

SELECT LOWER('Hello')
FROM dual;

SELECT UPPER('HelloWorld')
FROM dual;

eg2：查询包含字母b（不区分大小写）的姓

SELECT last_name
FROM s_emp
--WHERE last_name LIKE '%b%' OR last_name LIKE '%B%'; --此方法字符多时不好用
--WHERE LOWER(last_name) LIKE '%b%';
WHERE UPPER(last_name) LIKE '%B%';

eg3：INITCAP

SELECT INITCAP('hELlo wORld')
FROM dual;

eg4: CONCAT：字符串连接（拼接）

SELECT CONCAT('aa','bb')
FROM dual;

eg5:SUBSTR(str,start,length)：字符串的截取

SELECT SUBSTR('helloworld', 2, 6)
FROM dual;

---

数值函数：

• ROUND：四舍五入
• TRUNC：截取，不就行四舍五入
• MOD：取余

eg1：ROUND

--截取到小数点后两位  415.63
SELECT ROUND(415.627, 2)
FROM dual;

--截取到整数位  416
SELECT ROUND(415.627, 0)
FROM dual;

--截取十位  420
SELECT ROUND(415.627, -1)
FROM dual;

--默认为0，即整数位   416
SELECT ROUND(415.627)
FROM dual;

eg2：TRUNC

--与ROUND的唯一区别是不用进位  415.62
SELECT TRUNC(415.627, 2)
FROM dual;

--截取到整数位  415
SELECT TRUNC(415.627, 0)
FROM dual;

--截取十位  410
SELECT TRUNC(415.627, -1)
FROM dual;

--默认为0，即整数位   415
SELECT TRUNC(415.627)
FROM dual;

eg3：MOD

SELECT MOD(15, 8)
FROM dual;

---

日期函数：

• MONTHS_BETWEEN(date1,date2)：求两个时间月份差，有小数点
• ADD_MONTHS(date, n)：给指定时间加n个月
• NEXT_DAY(date,'星期几')： 中文直接写星期几，英文写对应的单词
  • date之后最近的一个星期几 （如：下一个星期六是那天）
• LAST_DAY(date)，date所在月份的最后一天
• ROUND(date, 精确度)：对指定日期进行四舍五入，逢16日往月份进一，逢7月往年份进一
  • 精确度：
    • year：年
    • yyyy：年
    • month：月
    • mm：月
    • dd：日
    • hh：小时
    • mi：分
    • ss：秒
• TRUNC(date, 精确度)：对指定日期进行截取
• SYSDATE函数：当前系统时间，默认显示格式   01-SEP-18
  • DATE类型：精确到秒
• SYSTIMESTAMP：可以看到更多信息
  • TIMESTAMP：精确到毫秒

eg1：MONTHS_BETWEEN

SELECT MONTHS_BETWEEN('01-NOV-18','30-AUG-18')
FROM dual;

eg2：ADD_MONTHS

SELECT ADD_MONTHS(sysdate,3) 
FROM dual;

eg3：

• 增加1年：

  SELECT ADD_MONTHS(sysdate,12) 
  FROM dual;

• 增加1天：

  SELECT sysdate + 1
  FROM dual;

• 增加1小时：

  SELECT sysdate + 1/24
  FROM dual;

• 增加1分：

  SELECT sysdate + 1/24/60
  FROM dual;

• 增加1秒：

  SELECT sysdate + 1/24/60/60
  FROM dual;

eg4：

• NEXT_DAY

  SELECT NEXT_DAY(sysdate, 'Thursday') 
  FROM dual;

• LAST_DAY

  SELECT LAST_DAY(sysdate) 
  FROM dual;

eg5：ROUND(date, 精确度)

--精确到年，看月，大于7月进位，6月以下舍去
SELECT ROUND(sysdate,'year') 
FROM dual;

--精确到月，看日，大于16日进位，15以下舍去
SELECT ROUND(sysdate,'month') 
FROM dual;

--精确到日，看小时，大于12小时进位

eg6：TRUNC()

--TRUNC 不进位，都舍掉
SELECT TRUNC(sysdate,'year') 
FROM dual;

SELECT TRUNC(sysdate,'month') 
FROM dual;

eg7：查询本月入职的所有员工

• 通用：

  WHERE start_date BETWEEN TRUNC(sysdate, 'month') AND sysdate;

• 本年：

  WHERE start_date BETWEEN TRUNC(sysdate, 'year') AND sysdate;

• 本周：

  --从本周一0时开始
  WHERE start_date BETWEEN TRUNC(NEXT_DAY(sysdate-7,'Monday'), 'dd') AND sysdate;

---

转换函数：

• 数值<==>文本
  • 数值->文本：to_char                   TO_CHAR(number, 'fmt')
    • 占位符：
      • 0：不满的位用0填充
      • 9：整数位不满用空格填充，小数位用0填充
    • 货币：$表示美元，L表示本地货币单位
  • 文本->数值：to_number              TO_NUMBER(char)
• eg1：

  --用9作占位符        123456.7890
  SELECT to_char(123456.789, '99999999.9999') 
  FROM dual;
  
  --用0作占位符      00123456.7890
  SELECT to_char(123456.789, '00000000.0000') 
  FROM dual;
  
  
  
  --货币表示           123,456.7890
  SELECT to_char(123456.789, '99,999,999.0000') 
  FROM dual;
  --美元     $123,456.7890
  SELECT to_char(123456.789, '$99,999,999.0000') 
  FROM dual;
  --当前环境货币表示    ￥123,456.7890
  SELECT to_char(123456.789, 'L99,999,999.0000') 
  FROM dual;
  
  
  --文本转数值       123
  SELECT to_number('123') 
  FROM dual;

 

• 时间<==>文本，（掌握）
  • 时间->文本：to_char                    TO_CHAR(number, 'fmt')
    • 日期格式模型元素：
      • 数字格式（一般使用这个）
        • d：星期几，1~7（星期日到星期六）
        • dd：一个月的第几天
        • ddd：一年中的第几天
        • yyyy：四位的年
        • mm：两位的月
        • hh24:24进制的小时
        • hh：12进制的小时
        • mi：分
        • ss：秒
        • rr：两位的年，50以上表示上个世纪，49以下表示本个世纪（eg：89年为上个世纪）
        • yy：两位的年，和当前时间一个世纪     注：一般推荐使用四位的年
      • 英文格式
        • year:英文年份
        • month：英文月份
        • mon：英文月份缩写
        • day：星期几第几天
        • DY：星期几的缩写
        • ddsp；英文拼写的天
        • ddspth：英文评选的第几天
  • 文本->时间：to_date
• eg2：查询当前系统时间，要求显示年月日时分秒

  --日期格式转换        2018-11-17 10:53:02
  SELECT to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
  FROM dual;

• eg3：'1997-01-01'：是一个字符串，转换成时间

  --字符串转换成时间        01-JAN-97
  SELECT to_date('1997-01-01', 'yyyy-mm-dd') 
  FROM dual;