oracle 学习笔记

SELECT distinct last_name FROM EMPLOYEES ;
SELECT distinct * FROM EMPLOYEES

包含空值的数学表达式值都为空值

别名，特殊字符双引号括起来
SELECT JOB_ID,SALARY*12 "dd* dd%" FROM EMPLOYEES

||,列和字符的连接
SELECT last_name||'_'||job_id name_id,salary FROM EMPLOYEES


sql/pl
describe 显示表结构
SQL> desc employees
Name Type Nullable Default Comments
---------- -------------- -------- ------- --------
LAST_NAME VARCHAR2(50) Y
JOB_ID VARCHAR2(5) Y
SALARY NUMBER Y
FIRST_NAME NVARCHAR2(205) Y
BIRTH_DATE TIMESTAMP(6) Y
CURR_YEARS LONG Y

between ... and ... 在两个值之间，包含边界
select * from employees where salary between 3 and 5
select * from employees where salary in (3,4,5)

% 匹配多个，_ 匹配一个 escape 定义转义符
select * from employees where last_name like '%name_'
select * from employees where last_name like '\%na\\\_%' escape '\' 匹配　%na\_ 开头的字符

逻辑运算符　and or not
... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary NOT BETWEEN 10000 AND 15000
... WHERE last_name NOT LIKE '%A%'
... WHERE commission_pct IS NOT NULL


优先级
算术运算符　连接符　比较符　is [not] null,like,[not] in [not] between not and or
select * from employees where last_name='name2' and last_name='name1' or job_id='00004'
先执行 and 即：select * from employees where (last_name='name2' and last_name='name1') or job_id='00004'


select * from employees where last_name='name2' or last_name='name1' and job_id='00004'
先执行 and 即：select * from employees where last_name='name2' or (last_name='name1' and job_id='00004')


ORDER BY specifies the order in which the retrieved rows are displayed
ASC orders the rows in ascending order (this is the default order)
DESC orders the rows in descending order

null 值 asc 时排在最后，desc　时排在最前
可按 别名或表达式 排序

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];


大小写控制函数
lower upper initcap

LOWER: Converts mixed case or uppercase character strings to lowercase
UPPER: Converts mixed case or lowercase character strings to uppercase
INITCAP: Converts the first letter of each word to uppercase and remaining letters to lowercase

字符控制函数
CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)
SUBSTR: Extracts a string of determined length
LENGTH: Shows the length of a string as a numeric value
INSTR: Finds numeric position of a named character
LPAD: Pads the character value right-justified
RPAD: Pads the character value left-justified
TRIM: Trims heading or trailing characters (or both) from a character string (If trim_character or trim_source is a character literal, you must enclose it in single quotes.)

CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD('24000',10,'*') *****24000 左填充
RPAD('24000', 10, '*') 24000***** 右填充
TRIM('H' FROM 'HelloWorld') elloWorld

ltrim rtrim 去掉左右空字符
select substr('中华人民共和国',2,1) from dual 第2个开始，长度为1 华
select substr('中华人民共和国',-3,2) from dual 倒数第3个开始，长度为2 共和

数字函数：
ROUND: 四舍五入
ROUND(45.926, 2)　　　45.93 , ROUND(45.926, 0)　　　46 , ROUND(45.926, -1)　　　50
TRUNC: 截断
TRUNC(45.926, 2)　　　45.92
MOD: 求余
MOD(1600, 300) 　　　100


日期
默认的日期格式是 DD-MON-RR
select sysdate from dual 返回当前日期时间

MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date.
ROUND(date[,'fmt']): Returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.


nvl 和 nvl2
NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

COALESCE (expr1, expr2, ... exprn)
In the syntax:
expr1 returns this expression if it is not null
expr2 returns this expression if the first expression is null and this expression is not null
exprn returns this expression if the preceding expressions are null

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END


DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])


非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

外连接
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;

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

自连接
select *
from t1 a1,t1 a2
where a1.c11(+)=a2.c14


使用SQL: 1999 语法连接
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];

CROSS JOIN Returns a Cartesian product from the two tables
NATURAL JOIN Joins two tables based on the same column name
JOIN table
USING column_name Performs an equijoin based on the column name
JOIN table ON
table1.column_name Performs an equijoin based on the condition in the ON clause
= table2.column_name
LEFT/RIGHT/FULL OUTER

left join 与right join 中 on条件中对主表　行数影响的无效性


组函数类型
AVG
COUNT
MAX
MIN
STDDEV 返回expr的样本标准偏差
SUM

COUNT(*)
COUNT(expr) 不包含null值行
COUNT(DISTINCT expr) 不包含null值行

组函数忽略空值
SELECT AVG(commission_pct)
FROM employees;
平均值时会对结果产生影响

SELECT AVG(NVL(commission_pct, 0))
FROM employees;

过滤分组： HAVING 子句
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

where 子句中不能包含组函数
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);


子查询

子查询要包含在括号内。
将子查询放在比较条件的右侧。
除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。
单行操作符对应单行子查询，多行操作符对应多行子查询。

in any all 多行操作符


在 INSERT 语句中加入子查询

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';


在DML语句中使用 WITH CHECK OPTION 关键字

实际上，WITH CHECK OPTION限定的是，数据的改变，不能超出WITH CHECK OPTION所约束的范围。
在DELETE时，不管有不有这个WITH CHECK OPTION，子查询中的记录是都可以删除的，子查询以外的记录是不会变化的。
而INSERT和UPDATE时，WITH CHECK OPTION作用就比较明显了。如果修改的数据，不符合WITH CHECK OPTION的限制，是会报错，不被修改的。
比如：
insert into (select name, age from b1 where age = 23 with check option) values ('a', 20); 是会报错的，
insert into (select name, age from b1 where age = 23 with check option) values ('a', 23); 是可以的。
update (select name, age from b1 where age = 23 with check option) set age = 20; 是会报错的。


使用 DEFAULT 关键字表示默认值
可以使用显示默认值控制默认值的使用
显示默认值可以在 INSERT 和 UPDATE 语句中使用
INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);

UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;


合并语句的语法

可以使用merge语句，根据指定的条件进行插入或更新操作
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);


merge into t1
using t2
on (t1.c11 = t2.c21)
when matched then
update set t1.c12 = t2.c22
when not matched then
insert values ('bb', 'bb', default, default);


UPDATE...
SAVEPOINT update_done;

INSERT...
ROLLBACK TO update_done;

COMMIT;


创建表
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
hire_date DATE DEFAULT SYSDATE //默认值
);

查询数据字典

查看用户定义的表
SELECT table_name
FROM user_tables ;

查看用户定义的各种数据库对象
SELECT DISTINCT object_type
FROM user_objects ;

查看用户定义的表, 视图, 同义词和序列
SELECT *
FROM user_catalog ;

数据类型 描述
VARCHAR2(size) 可变长字符数据
CHAR(size) 定长字符数据
NUMBER(p,s) 可变长数值数据
DATE 日期型数据
LONG 可变长字符数据，最大可达到2G

CLOB 字符数据，最大可达到4G

RAW and LONG RAW 裸二进制数据
BLOB 二进制数据，最大可达到4G
BFILE 存储外部文件的二进制数据，最大可达到4G
ROWID 行地址

SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;


使用子查询创建表举例
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;


ALTER TABLE 语句

ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);

ALTER TABLE table
DROP (column);


SET UNUSED 选项

ALTER TABLE table
SET UNUSED (column);

ALTER TABLE table
SET UNUSED COLUMN column;

ALTER TABLE table
DROP UNUSED COLUMNS;


删除表
DROP TABLE dept80;
数据和结构都被删除
所有正在运行的相关事物被提交
所有相关索引被删除
DROP TABLE 语句不能回滚

改变对象的名称
RENAME dept TO detail_dept;


清空表

TRUNCATE TABLE 语句:
删除表中所有的数据
释放表的存储空间
TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据


表的注释
COMMENT ON TABLE employees
IS 'Employee Information';

可以通过下列数据字典视图查看所添加的注释:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS


创建视图
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;


CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
WITH CHECK OPTION 子句


屏蔽 DML 操作
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;

删除视图
DROP VIEW view;

Top-N 分析
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;


定义序列:

CREATE SEQUENCE 语句
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];


CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;

查询数据字典视图 USER_SEQUENCES获取序列定义信息
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;

NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效


修改序列
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;

使用DROP SEQUENCE 语句删除序列
DROP SEQUENCE dept_deptid_seq;


创建索引
CREATE INDEX index
ON table (column[, column]...);

CREATE INDEX emp_last_name_idx
ON employees(last_name);

什么时候创建索引

以下情况可以创建索引:
列中数据值分布范围很广
列中包含大量空值
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%


什么时候不要创建索引

下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
查询的数据大于2%到4%
表经常更新
加索引的列包含在表达式中

查询索引
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';


基于函数的索引

CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));

SELECT *
FROM departments
WHERE UPPER(department_name) = 'SALES';

删除索引
DROP INDEX index;


同义词
CREATE [PUBLIC] SYNONYM synonym
FOR object;

CREATE SYNONYM d_sum
FOR dept_sum_vu;

DROP SYNONYM d_sum;


创建用户
CREATE USER user
IDENTIFIED BY password;

CREATE USER scott
IDENTIFIED BY tiger;

用户的系统权限

以应用程序开发者为例, 一般具有下列系统权限:
CREATE SESSION（创建会话）
CREATE TABLE（创建表）
CREATE SEQUENCE（创建序列）
CREATE VIEW（创建视图）
CREATE PROCEDURE（创建过程）

GRANT create session, create table,
create sequence, create view
TO scott;


创建角色并赋予权限

创建角色
CREATE ROLE manager;
为角色赋予权限
GRANT create table, create view
TO manager;

将角色赋予用户
GRANT manager TO DEHAAN, KOCHHAR;

ALTER USER scott
IDENTIFIED BY lion;

对象权限

不同的对象具有不同的对象权限
对象的拥有者拥有所有权限
对象的拥有者可以向外分配权限

GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];

分配对象权限

GRANT select
ON employees
TO sue, rich;

GRANT update (department_name, location_id)
ON departments
TO scott, manager


WITH GRANT OPTION 使用户同样具有分配权限的权利

GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;

向数据库中所有用户分配权限

GRANT select
ON alice.departments
TO PUBLIC;

查询权限分配情况

数据字典视图 描述
ROLE_SYS_PRIVS 角色拥有的系统权限
ROLE_TAB_PRIVS 角色拥有的对象权限
USER_ROLE_PRIVS 用户拥有的角色
USER_TAB_PRIVS_MADE 用户分配的关于表对象权限
USER_TAB_PRIVS_RECD 用户拥有的关于表对象权限
USER_COL_PRIVS_MADE 用户分配的关于列的对象权限
USER_COL_PRIVS_RECD 用户拥有的关于列的对象权限
USER_SYS_PRIVS 用户拥有的系统权限

收回对象权限

使用 REVOKE 语句收回权限
使用 WITH GRANT OPTION 子句所分配的权限同样被收回

REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];

REVOKE select, insert
ON departments
FROM scott;


数据库联接

CREATE PUBLIC DATABASE LINK hq.acme.com
USING 'sales';

SELECT *
FROM emp@HQ.ACME.COM;

SET 运算符

使用 SET 操作符注意事项
在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置

使用 UNION 操作符
使用 UNION ALL 操作符
使用 INTERSECT 操作符 交集
使用 MINUS操作符 补集 差
使用 ORDER BY 对结果集排序

SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

SELECT last_name, hire_date,
EXTRACT (MONTH FROM HIRE_DATE)
FROM employees
WHERE manager_id = 100;

Oracle ROLLUP和CUBE 用法
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。 grouping_id()可以美化效果：

SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);


GROUPING 函数

GROUPING 函数可以和 CUBE 或 ROLLUP 结合使用
使用 GROUPING 函数，可以找到哪些列在该行中参加了分组
使用 GROUPING 函数, 可以区分空值产生的原因
GROUPING 函数返回 0 或 1

SELECT department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);


GROUPING SETS

GROUPING SETS 是对GROUP BY 子句的进一步扩充
使用 GROUPING SETS 在同一个查询中定义多个分组集
Oracle 对 GROUPING SETS 子句指定的分组集进行分组后用 UNION ALL 操作将各分组结果结合起来
Grouping set 的优点:
只进行一次分组即可
不必书写复杂的 UNION 语句
GROUPING SETS 中包含的分组项越多,性能越好。


SELECT department_id, job_id,
manager_id,avg(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));


复合列

复合列是被作为整体处理的一组列的集合
ROLLUP (a, (b,c), d)
使用括号将若干列组成复合列在ROLLUP 或 CUBE 中作为整体进行操作
在ROLLUP 或 CUBE中, 复合列可以避免产生不必要的分组结果

SELECT department_id, job_id, manager_id,
SUM(salary)
FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id));

连接分组集

连接分组集可以产生有用的对分组项的结合
将各分组集, ROLLUP 和 CUBE 用逗号连接 Oracle 自动在 GROUP BY 子句中将各分组集进行连接
连接的结果是对各分组生成笛卡尔集

GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)

SELECT department_id, job_id, manager_id,
SUM(salary)
FROM employeesGROUP BY department_id,
ROLLUP(job_id),
CUBE(manager_id);


WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率

WITH
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;


Oracle用Start with...Connect By子句递归查询

创建示例表：

CREATE TABLE TBL_TEST

(

ID NUMBER,

NAME VARCHAR2(100 BYTE),

PID NUMBER DEFAULT 0

);

插入测试数据：

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归

select * from TBL_TEST

start with id=1

connect by prior id = pid

从末梢往树ROOT递归

select * from TBL_TEST

start with id=5

connect by prior pid = id


使用 LEVEL 和 LPAD格式化分层查询

SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')
AS org_chart
FROM employees
START WITH last_name='King'
CONNECT BY PRIOR employee_id=manager_id


修剪树枝
WHERE last_name != 'Higgins'

CONNECT BY PRIOR
employee_id = manager_id
AND last_name != 'Higgins'


多表 INSERT 语句的类型

无条件的 INSERT
有条件的 ALL INSERT
有条件的 FIRST INSERT
旋转 INSERT

INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]


INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;


INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;


INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;


INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;


外部表

外部表是只读的表，其数据存储在数据库外的平面文件中
外部表的各种参数在 CREATE TABLE 语句中指定
使用外部表, 数据可以存储到外部文件或从外部文件中上载数据到数据库
数据可以使用 SQL访问, 但不能使用 DML 后在外部表上创建索引


创建路径

创建外部表之前应先使用CREATE DIRECTORY语句创建路径
CREATE DIRECTORY emp_dir AS '/flat_files' ;

CREATE TABLE oldemp (
empno NUMBER, empname CHAR(20), birthdate DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad_emp'
LOGFILE 'log_emp'
FIELDS TERMINATED BY ','
(empno CHAR,
empname CHAR,
birthdate CHAR date_format date mask "dd-mon-yyyy"))
LOCATION ('emp1.txt'))
PARALLEL 5
REJECT LIMIT 200;


创建主键约束同时创建索引举例

CREATE TABLE NEW_EMP
(employee_id NUMBER(6)
PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(employee_id)),
first_name VARCHAR2(20),
last_name VARCHAR2(25));