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就会知道是怎么回事了，可以看看

http://www.itpub.net/thread-1400232-1-1.html