一单行函数
--字符类型单行函数
SELECT LAST_NAME,LOWER(LAST_NAME)--变为小写
SELECT LAST_NAME,UPPER(LAST_NAME)--变为大写
--查询employees表中last_name列中包含a(不区分大小写)员工的last_name:
SELECT LAST_NAME FROM EMPLOYEES WHERE UPPER(LAST_NAME) LIKE '%A%';
SELECT SUBSTR(LAST_NAME,3) AS A --从指定的位置开始截取字符串直到字符的末尾
FROM EMPLOYEES;
SELECT SUBSTR(LAST_NAME,3,2)--从指定的位置开始截取指定长度的字符串
FROM EMPLOYEES;
SELECT SUBSTR(LAST_NAME,-3)--从倒数第N位开始截取字符串,直到字符串的末尾
FROM EMPLOYEES;
SELECT SUBSTR(LAST_NAME,-3,2)--从倒数第N位开始截取指定长度的字符串
FROM EMPLOYEES;
--INSTR():在第一个参数中查找第二个参数首次出现的位置,没找到返回0.
--查询employees表中last_name包含a员工的last_name(不允许使用LIKE)
SELECT LAST_NAME
FROM EMPLOYEES
WHERE INSTR(LAST_NAME,'a')>0;
SELECT LAST_NAME,LENGTH(LAST_NAME)
FROM EMPLOYEES;
SELECT LENGTH('中国')--获得字符数FROM DUAL;
SELECT LENGTHB('中国')--获得字节数
FROM DUAL;
SELECT TRIM(' ABC ABC ') AS A --去掉字符串两端的空格
FROM DUAL;
SELECT TRIM('A' FROM 'AAAABACAAAA')--去掉字符串两端指定的字符
FROM DUAL;
--LPAD()/RPAD():显示第一个参数的值,使用第二个参数设置第一个参数显示的长度,
如果第一个参数长度不足,使用第三个参数在第一个参数的左/右面补齐长度。
SELECT LPAD(EMPLOYEE_ID,6,0)FROM EMPLOYEES;
--数字类型单行函数
-- ROUND():四舍五入
SELECT ROUND(25698.9874),ROUND(25698.9874,2),ROUND(25698.9874,0),ROUND(25698.9874,-1)
FROM DUAL;
-- TRUNC():截取数字
SELECT TRUNC(25698.9874),TRUNC(25698.9874,2),TRUNC(25698.9874,0),TRUNC(25698.9874,-1)
FROM DUAL;
-- MOD():取余数
SELECT MOD(15,2)
FROM DUAL;
--日期类型单行函数
--SYSDATE:获得数据库服务器当前日期+时间
SELECT SYSDATE
FROM DUAL;
--日期类型单行函数
/*
Oracle中日期计算的方式
1.日期+天数=日期
2.日期-天数=日期
3.日期-日期=天数
4.日期不能加日期
*/
SELECT SYSDATE+1000
FROM DUAL;
SELECT SYSDATE-1000
FROM DUAL;
--查询employees表中所有员工的last_name,hire_date,天数
SELECT LAST_NAME,HIRE_DATE,TRUNC(SYSDATE-HIRE_DATE)
FROM EMPLOYEES;
--MONTHS_BETWEEN():获得两个日期相差的月数
--查询employees表中所有员工的last_name,hire_date,月数
SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE))
FROM EMPLOYEES;
--ADD_MONTHS():在指定的日期上加上N个月
SELECT ADD_MONTHS(SYSDATE,100)
FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,-100)
FROM DUAL;
--类型转换单行函数
--自动转换:效率低,不建议使用。
--手动转换:TO_CHAR(),TO_DATE(),TO_NUMBER()
/*
1.字符串可以与日期相互转换
2.字符串可以与数字相互转换
3.日期与数字不能相互转换
*/
--TO_CHAR(D,F):将日期类型D根据模板F转换成日期类型的字符串
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')
FROM EMPLOYEES;SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS DAY DY')
FROM DUAL;
SELECT LAST_NAME, TO_CHAR(HIRE_DATE,'FMYYYY"年"MM"月"DD"日"')
FROM EMPLOYEES;
SELECT TO_CHAR(SYSDATE,'D DD DDD')
FROM DUAL;
--查询所有星期二入职员工的last_name,hire_date(格式:YYYY-MM-DD DAY)
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD DAY')
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'DAY')='星期二';
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD DAY')
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'D')='3';
--TO_CHAR(N,F):将数字类型N根据模板F转换为字符类型的值
SELECT
TO_CHAR(256987.365,'FM$99,999,999.00'),
TO_CHAR(256987.365,'FML99,999,999.00')
FROM DUAL;
SELECT LAST_NAME,TO_CHAR(SALARY,'FM$999,999.00')
FROM EMPLOYEES;
--TO_DATE(C,F):根据模板F将字符类型C转换为日期类型的值
SELECT TO_DATE('2017-10-1','YYYY-MM-DD')-SYSDATE
FROM DUAL;
SELECT TO_DATE('2017-10-1','YYYY-MM-DD')
FROM DUAL;
--TO_NUMBER(C,F):根据模板F将字符类型C转换为数字类型的值
SELECT TO_NUMBER('¥10,000.00','L99,999.00')
FROM DUAL;
--通用单行函数
--NVL():当第一个参数不为NULL,返回第一个参数。当第一个参数为NULL,返回第二个参数。两个参数的类型必须保持一致。
--查询employees表中所有员工的last_name,commission_pct
SELECT LAST_NAME,NVL(TO_CHAR(COMMISSION_PCT,'FM0.00'),'没有佣金')
FROM EMPLOYEES;
--关系数据库中当算术运算中出现了NULL,结果一定为NULL。
--查询employees表中所有员工的last_name,salary,commission_pct,年薪,年收入(年薪+年薪*佣金)
SELECT LAST_NAME,SALARY,COMMISSION_PCT,SALARY*12 AS 年薪,
(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT,0)) AS 年收入
FROM EMPLOYEES;-- NVL2():当第一个参数不为NULL,返回第二个参数。当第一个参数为NULL,返回第三个参数。
SELECT LAST_NAME,NVL2(COMMISSION_PCT,'有佣金','没有佣金')
FROM EMPLOYEES;
--分支选择:CASE表达式,DECODE()函数
--CASE表达式:结果的类型必须保持一致。
--查询employees表中所有员工的last_name,job_id,salary,新工资。如果job_id为IT_PROG时,工资增加10%。如果job_id为ST_CLERK时,工资增加15%。如果job_id为SA_REP时,工资增加20%,其它职位工资不变。
SELECT LAST_NAME,JOB_ID,SALARY,
CASE
WHEN JOB_ID='IT_PROG' THEN SALARY*1.1
WHEN JOB_ID='ST_CLERK' THEN SALARY*1.15
WHEN JOB_ID='SA_REP' THEN SALARY*1.2
ELSE SALARY
END AS 新工资
FROM EMPLOYEES;
--只能判断是否相等
SELECT LAST_NAME,JOB_ID,SALARY,
CASE JOB_ID
WHEN 'IT_PROG' THEN SALARY*1.1
WHEN 'ST_CLERK' THEN SALARY*1.15
WHEN 'SA_REP' THEN SALARY*1.2
ELSE SALARY
END AS 新工资
FROM EMPLOYEES;
--DECODE():只能判断是否相等
SELECT LAST_NAME,JOB_ID,SALARY,
DECODE(
JOB_ID,
'IT_PROG',SALARY*1.1,
'ST_CLERK',SALARY*1.15,
'SA_REP',SALARY*1.2,
SALARY
)AS 新工资
FROM EMPLOYEES;
--查询employees表中所有员工的last_name,hire_date(格式如:1999-10-1),星期,查询的结果根据星期升序排序(要求:星期一作为一周的开始)
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'FMYYYY-MM-DD') AS A,TO_CHAR(HIRE_DATE,'DAY') AS B
FROM EMPLOYEES
ORDER BY TO_CHAR(HIRE_DATE-1,'D') ASC;
example:
--查询employees表中所有五月入职员工的last_name,hire_date(格式:YYYY-MM-DD)
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD') AS A
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'MM')='05';
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD') AS A
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'MON')='5月 ';
--查询employees表中所有员工的last_name,要求:last_name首字母小写,其它均为大写。
SELECT LOWER(SUBSTR(LAST_NAME,1,1))||UPPER(SUBSTR(LAST_NAME,2)) AS NAME
FROM EMPLOYEES;