需要复习的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   CHARNUMBERVARCHAR2   or   CHARDATENUMBERVARCHAR2DATEVARCHAR2

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';