oracle sql mod,oracle sql 学习笔记1

1.set verify on

打开绑定变量显示

SQL> select first_name,job_id,&&colnum1 from employees where &&colnum1='&num'order by &&colnum1;

old   1: select first_name,job_id,&&colnum1 from employees where &&colnum1='&num'order by &&colnum1

new   1: select first_name,job_id,DEPARTMENT_ID from employees where DEPARTMENT_ID='100'order by DEPARTMENT_ID

2.DEFINE employee_num = 200

使用DEFINE命令给变量创建和指派数值

使用UNDEFINE 命令删除变量

3.& 与&&的区别符号替代变量

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;

SELECT employee_id, last_name, job_id, &&column_name

FROM employees

ORDER BY &column_name ;

&每个变量都要输入，而&&只要输入一次就会记录，后面重复相同的变量自动赋值。

4.LOWER变小写函数

SQL> select LOWER('SQL Course') from dual;

LOWER('SQL

----------

sql course

5.UPPER变大写函数

SQL> select UPPER('SQL Course') from dual;

UPPER('SQL

----------

SQL COURSE

6.首字母大写函数

SQL> select INITCAP('SQL Course') from dual;

INITCAP('S

----------

Sql Course

7.CONCAT连接字符串

SQL> select CONCAT('Hello', 'World') from dual;

CONCAT('HE

----------

HelloWorld

8.substr截取字符串

SQL> select SUBSTR('HelloWorld',1,5) from dual;

SUBST

-----

Hello

9.字符串长度length

SQL> select LENGTH('HelloWorld') from dual;

LENGTH('HELLOWORLD')

--------------------

10

10.查看字符或字符串在什么位置instr

SQL> select INSTR('HelloWorld', 'Wo') from dual;

INSTR('HELLOWORLD','W')

-----------------------

6

11.左边填充LPAD

SQL> select LPAD(salary,10,'*') from employees;

12.右边填充RPAD

SQL> select RPAD(salary,10,'*') from employees;

13.REPLACE替换字符串

SQL> select REPLACE ('JACK and JUE','J','BL') from dual;

REPLACE('JACKA

--------------

BLACK and BLUE

14.去除字符TRIM

select TRIM('H' FROM 'HelloWorld') from dual;

15.ROUND: 对一个特定的十进制数进行四舍五入

SQL> select ROUND(45.926, 2) from dual;

ROUND(45.926,2)

---------------

45.93

16.TRUNC:对一个特定的十进制数进行截断

SQL> select trunc(45.926, 2) from dual;

TRUNC(45.926,2)

---------------

45.92

SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),

2  TRUNC(45.923,-1)

3  FROM DUAL;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)

--------------- ------------- ----------------

45.92            45               40

17. MOD:除法的返回余数

SQL> select MOD(1600, 300) from dual;

MOD(1600,300)

-------------

100

18.round的用法

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),

2  ROUND(45.923,-1)

3  FROM DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)

--------------- --------------- ----------------

45.92              46               50

19.MONTHS_BETWEEN 月份差函数

SQL> select MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') from dual;

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')

---------------------------------------

19.6774194

20.ADD_MONTHS N个月之后

SQL> select ADD_MONTHS ('31-JAN-96',1) from dual;

ADD_MONTHS('

------------

29-FEB-96

21.NEXT_DAY(下个星期五是哪天)

SQL> select NEXT_DAY('01-SEP-95','FRIDAY') from dual;

21.本月的最后一天LAST_DAY

select LAST_DAY(sysdate) from dual;

22.ROUND和TRUNC 对于日期的应用

QL> select ROUND(SYSDATE,'MONTH') from dual

2  ;

ROUND(SYSDAT

------------

01-MAR-11

SQL> select ROUND(SYSDATE,'DAY') from dual

2  ;

ROUND(SYSDAT

------------

27-FEB-11

SQL> select ROUND(SYSDATE,'YEAR') from dual

2  ;

ROUND(SYSDAT

------------

01-JAN-11

SQL> select TRUNC(SYSDATE,'YEAR') from dual

2  ;

TRUNC(SYSDAT

------------

01-JAN-11

SQL> select TRUNC(SYSDATE,'MONTH') from dual

2  ;

TRUNC(SYSDAT

------------

01-FEB-11

SQL> select TRUNC(SYSDATE,'DAY') from dual

2  ;

TRUNC(SYSDAT

------------

20-FEB-11

SQL> select sysdate from dual

2  ;

SYSDATE

------------

25-FEB-11

这里我们注意一下红色的实现，按照我们的想法应该返回是2月26日才对，但是却是27日，看一下SQL Reference就会知道是怎么回事了，可以看看