Oracle database 数据转换 从现有表中派生数据 理论实践

使用 hr schema

1、复制整张表：

create table new_countries as select * from countries;

2、带有条件的选择：

create table new_countries1as select*from countrieswhere REGION_ID=1;

3、选取表中的某些列，以及带有条件的选择：

create table new_countries3 as select REGION_ID, COUNTRY_ID, COUNTRY_NAME from countries where REGION_ID=1;

SQL> select * from new_countries3;


 REGION_ID CO COUNTRY_NAME
---------- -- ----------------------------------------
1 BE Belgium
1 CH Switzerland
1 DE Germany
1 DK Denmark
1 FR France
1 IT Italy
1 NL Netherlands
1 UK United Kingdom


8 rows selected.


SQL> 

4、带数据转换

create table new_employees2 
as 
select employee_id, salary*1.1 as salary_new ,
department_id
from new_employees
where department_id=100;

SQL> select * from new_employees2;


EMPLOYEE_ID SALARY_NEW DEPARTMENT_ID
----------- ---------- -------------
108    13208.8100
109  9900100
110  9020100
111  8470100
112  8580100
113  7590100


6 rows selected.

如果红色的 as 及内容不输入，则会报错：

00998. 00000 -  "must name this expression with a column alias"

5、insert ，append， 

insert 

into new_employees2

select employee_id, salary, department_id    *这里的列要和new——employees2中的列对等，否则会报 00926. 00000 -  "missing VALUES keyword"的错误 或 

00913. 00000 -  "too many values" 的错误

from new_employees 

where department_id=50;

select * from new_employees2;