oracle数据库基本知识和操作

FROM EMPLOYEES;

–从倒数第N位截取指定长度的字符串

SELECT SUBSTR(LAST_NAME,-3,2)

FROM EMPLOYEES;

- -INSTR():在第一个参数中查找第二个参数首次出现的位置，没找到返回0.

SELECT LAST_NAME,INSTR(LAST_NAME,‘a’)

FROM EMPLOYEES;

SELECT LAST_NAME

FROM EMPLOYEES

WHERE INSTR(LOWER(LAST_NAME),‘a’)>0;

–LPAD()/RPAD():显示第一个参数的值，并使用第二个参数指定第一个参数显示的长度，如果第一个参数长度不够，则使用第三个参数在第一个参数的左/右面补齐长度。

SELECT LPAD(EMPLOYEE_ID,6,0),LAST_NAME

FROM EMPLOYEES;

SELECT TRIM( ’ ABC ABC ') AS A–去掉字符串两端的空格

FROM DUAL;

SELECT TRIM(‘A’ FROM ‘AAABACAAA’)–去掉字符串两端指定的字符

FROM DUAL;

–查询employees表中所有员工的last_name,要求显示的last_name首字母为小写，其它均为大写。

SELECT LOWER(SUBSTR(LAST_NAME,1,1))||UPPER(SUBSTR(LAST_NAME,2))

FROM EMPLOYEES;

–数字单行函数

– ROUND():四舍五入

SELECT ROUND(2563.987),ROUND(2563.987,2),ROUND(2563.987,0),ROUND(2563.987,-1)

FROM DUAL;

- -TRUNC():截断数字

SELECT TRUNC(2563.987),TRUNC(2563.987,2),TRUNC(2563.987,0),TRUNC(2563.987,-1)

FROM DUAL;

- -MOD():取余数

SELECT MOD(15,2)

FROM DUAL;

–ABS():绝对值

SELECT ABS(-100)

FROM DUAL;

–日期单行函数

- -SYSDATE:获得数据库当前日期+时间

SELECT SYSDATE-100

FROM DUAL;

/*

1.日期+天数=日期

2.日期-天数=日期

3.日期-日期=天数

4.日期不能加日期

*/

–查询employees表中所有员工的last_name,hire_date,月数

SELECT LAST_NAME,HIRE_DATE,TRUNC((SYSDATE-HIRE_DATE)/30) AS 月数

FROM EMPLOYEES;

– MONTHS_BETWEEN():获得两个日期相差的月数

SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS 月数

FROM EMPLOYEES;

- -ADD_MONTHS():在指定的日期上加上指定的月数

SELECT ADD_MONTHS(SYSDATE,100)

FROM DUAL;

–NEXT_DAY():获得下一个星期N的日期 。

SELECT NEXT_DAY(SYSDATE,4)

FROM DUAL;

SELECT NEXT_DAY(SYSDATE,‘星期三’)

FROM DUAL;

–类型转换单行函数

–自动转换

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY>‘10000’;

- -手动转换：TO_CHAR(),TO_DATE(),TO_NUMBER()

/*

1.字符串与日期可以相互转换

2.字符串与数字可以相互转换

3.日期与数字不能相互转换

*/

–TO_CHAR(N,F):将日期类型N根据模板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 TO_CHAR(SYSDATE,‘D DD DDD’)

FROM DUAL;

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY"年"MM"月"DD"日"’)

FROM EMPLOYEES;

–查询employees表中所有星期一入职员工的last_name,hire_date(格式如:1999-1-1 星期一)

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY-MM-DD DAY’)

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,‘DAY’)=‘星期一’;

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY-MM-DD DAY’)

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,‘D’)=‘2’;

–TO_CHAR(N,F):将数据类型N根据模板F转换为字符串类型的值。

SELECT

TO_CHAR(256987.9654,‘FM$99,999,999.00’) AS A,

TO_CHAR(256987.9654,‘FML99,999,999.00’) AS A

FROM DUAL;

– RR:可自动补齐年的前2位

–T O_DATE(C,F):根据模板F将字符类型C转换为日期类型的值

SELECT TO_DATE(‘2017-1-27’,‘YYYY-MM-DD’)-SYSDATE

FROM DUAL;

SELECT TO_DATE(‘2017-1-27’,‘YYYY-MM-DD’)

FROM DUAL;

- -TO_NUMBER(C,F):根据模板F将字符类型C转换为数字类型的值。

SELECT TO_NUMBER(‘￥50,000.00’,‘L99,999.00’)

FROM DUAL;

–通用单行函数

–NVL():当第一个参数不为NULL，返回第一个参数。当第一个参数为NULL，返回第二个参数。两个参数的类型必须一致 。

SELECT LAST_NAME,NVL(TO_CHAR(COMMISSION_PCT,‘FM0.999’),‘没有佣金’)

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;

–SQL Server中IFNULL()的功能与Oracle中的NVL()功能一致。

– 分支选择：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 TO_CHAR(SALARY*1.1,‘FM$999,999.00’)

WHEN JOB_ID=‘ST_CLERK’ THEN TO_CHAR(SALARY*1.15,‘FM$999,999.00’)

WHEN JOB_ID=‘SA_REP’ THEN TO_CHAR(SALARY*1.2,‘FM$999,999.00’)

ELSE ‘不在此次活动范围内’

END AS 新工资

FROM EMPLOYEES;

–只能判断是否相等

SELECT LAST_NAME,JOB_ID,SALARY,

CASE JOB_ID

WHEN ‘IT_PROG’ THEN TO_CHAR(SALARY*1.1,‘FM$999,999.00’)

WHEN ‘ST_CLERK’ THEN TO_CHAR(SALARY*1.15,‘FM$999,999.00’)

WHEN ‘SA_REP’ THEN TO_CHAR(SALARY*1.2,‘FM$999,999.00’)

ELSE ‘不在此次活动范围内’

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