需要复习的Oracle知识点(一)

1	SELECT  job_id  FROM  jobs  WHERE  job_id  LIKE  'IT\_%'   escape  '\';

优先级

算数运算符
连接运算符
比较运算符
IS  [NOT]  NULL ,LIKE, [NOT] IN
[NOT] BETWEEN
NOT 
AND
OR

Oracle函数

LOWER('SQL Cource')  sql cource
UPPER('SQL Cource') SQL COURCE
INITCAP('SQL Cource') Sql Cource

字符控制函数

CONCAT('Hello','World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld','W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary,10.'*') 24000*****
TRIM('M' FROM  'HelloWorld') elloWorld
replace('abcd','b','m') amcd

数字函数

ROUND(45.926,2)     45.93
TRUNC(45.926,2)   45.92
MOD(1600, 300) 结果100

日期的数字运算

在日期上加上或减去一个数字结果仍然为日期
两个日期相减返回日期之间相差的天数

日期函数

MONTHS_BETWEEN 两个日期相差的月数 months_between('01-sep-95'.'11-jan-94')  19.6774194
ADD_MONTHS 向指定的日期加上若干月数 add_months('11-jan-94',6) '11-jul-94'
NEXT_DAY 指定日期的下一个日期 next_day('01-sep-95','friday')   '08-sep-95'
LAST_DAY 本月的最后一天   last_day('01-feb-95')   '28-feb-95'
ROUND日期四舍五入
TRUNC 日期截断
假设SYSDATE = '25-JUL-95'
  ROUND(SYSDATE,'MONTH') 01-AUG-95
  ROUND(SYSDATE,'YEAR') 01-JAN-96
  TRUNC(SYSDATE,'MONTH')   01-JUL-95
  TRUNC(SYSDATE,'YEAR') 01-JAN-95

源数据类型	目标数据类型
VARCHAR2   or   CHAR	NUMBER
VARCHAR2   or   CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

to_char(date,'format_model')
YYYY     2011
YEAR     twenty eleven
MM     04
MONTH     4月
DY    星期一
DAY    星期一
DD    02
使用双引号向日期中添加字符
DD "of" MONTH   12 of OCTOBER

TO_CHAR 函数对数字的转换

TO_CHAR(number,'format_model')

9 数字

0 零

$ 美元符

L 本地货币符号

. 小数点

，千位符

NVL(hire_date,'01-JAN-97')将空值转换为一个已知的值
NVL2(expr1,expr2,expr3) expr1不为NULL,返回expr2，为NULL，返回expr3.
NULLIF(expr1,expr2) 相等返回NULL，不等返回expr1
COALESCE(commission_pot, salart, 10)找第一个不为空的值 

TO_NUMBER 和TO_DATE函数

TO_NUMBER(char, 'format_model')  函数将字符转换成数字
TO_DATE 函数将字符转换为日期
TO_DATE(char, 'format_model') 使用TO_DATE函数将字符转换成日期

条件表达式

1 2 3 4

CASE   ....    WHEN   ...   THEN  ...               WHEN   ...   THEN  ...                ELSE  ... END

1 2 3 4 5

SELECT  CASE  job_id   WHEN  'IT_PROG'  THEN  1.10*salary                      WHEN  'ST_CLERK'  THEN  1.15*salary                      ELSE  salary END  "REVISED_SALARY" FROM  employees;

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

1 2 3 4

DECODE(col | expression, search1, result1,                           search2, result2,                           defaultresult                )

1 2 3 4 5

SELECT DECODE(job_id,  'IT_PROG' ,    1.10 *salary,                        'ST_CLERK' ,   1.15 *salary,                        'SA_REP' ,     1.20 *salary,                        salary) REVISED_SALARY FROM employees;

---

组函数使用

AVG 平均值
COUNT 数量
MAX最大值
MIN最小值
SUM 总和

分组数据

GROUP BY子句用法

1 2 3 4 5 6

SELECT  column ,  group_function( column ) FROM  table [ WHERE  condition] [ GROUP  BY  group_by_expression] [ HAVING  group_condition] [ ORDER  BY  column ]

可以在GROUP BY 字句中包含多个列，不能在WHERE 子句中使用组函数，可以在HAVING 子句中使用组函数。

使用HAVING 子句过滤分组

行已经被分组
使用了组函数
满足HAVING 子句中条件的分组将被显示

连接查询

等值连接 不等值连接  外连接 自连接

1 2 3 4

SELECT  e.* ,j.* FROM   employee e,job_grades j WHERE  e.salary BETWEEN  j.lowest_sal  AND  j.highest_sal;

右连接查询，以table2为依据

1 2 3

SELECT  table1.column1,table2.column1 FROM  table1, table2 WHERE  table1.column1(+)=table2.column1;

叉集

使用CROSS JOIN子句使连接的表产生叉集（叉集和笛卡尔集是相同的）。

1 2 3

SELECT  last_name, department_name FROM  employees CROSS  JOIN  departments;

自然连接
natual join 子句，会以两个表中具有相同名字的列为条件创建等值连接。

在表中查询满足等值条件的数据

如果只是列名相同而数据类型不同，则会产生错误。 

1 2 3

SELECT  department_id ,department_name,  location_id city  FROM  departments NATUAL  JOIN  locations;

使用USING 子句创建连接

在NATUAL JOIN 子句创建等值连接时，可以使用USING 子句指定等值连接中需要用到的列。

使用USING可以在有多个列满足条件时进行选择。

不要给选中的列加上表明前缀或别名。

NATUAL JOIN 和USING 子句经常同时使用。

1 2 3

SELECT  e.department_id, e.last_name, d.locaiton_id FORM employees e  JOIN  departments d USING (department_id);

使用ON子句创建连接

自然连接中是以具有相同名字的列为连接条件的。

可以使用ON子句指定额外的连接条件。

这个连接条件是与其它条件分开的。

使用ON子句创建多表（3张表）连接

1 2 3

SELECT  e.department_id,e.last_name, e.department_id, d.department_id, d.location_id FROM  employees e  JOIN  departments d ON  (e.department_id = d.department_id);

内连接和外连接

在SQL 1999中，内连接只返回满足条件的数据

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为 左（或右）外连接。

两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为 满外连接。

左外联接

1 2 3 4

SELECT  e.last_name, e.department_id, d.department_name FROM  employee e LEFT  OUTER  JOIN  departments d ON  (e.department_id = d.department_id);

右外联接

1 2 3 4

SELECT  e.last_name, e.department_id,d.department_name FROM  employees e RIGHT  OUTER  JOIN  departments d ON  (e.department_id = d.department_id);

满外连接

1 2 3 4

SELECT  e.last_name , e.department_id, d.department_name FROM  employees e FULL  OUTER  JOIN  departments d ON  (e.department_id = d.department_id);

 

---

子查询

单行子查询操作符

 =    >    >=       <     <=          <>

多行子查询

IN 等于列表中的任意一个

ANY和子查询返回的任意一个值比较

ALL和子查询返回的所有值比较

1 2 3 4 5 6 7 8

SELECT  employee_id,last_name,job_id,salary FROM  employees WHERE  salqry < ALL                  ( SELECT  salary                   FROM  employees                   WHERE  job_id = 'IT_PROG'                   ) AND  job_id <> 'IT_PROG' ;