1.使用create table table_name_new as select * from table_name_old where 1=2;可以复制表结构
2.create table as 使用create table emp_copy as select * from scott.emp;可以复制表结构和数据
3.select (...) into (...) from table_name 复制数据时,在sqlserver中是可以的但是在oracle中提示缺失或错误,所以在复制数据的时候也可以用:insert into table1 (...) select (...) from table2 where (...)
4. 查询前n行
SELECT ROWNUM as RANK, ename, sal
FROM (SELECT ename,sal FROM emp_copy
ORDER BY sal DESC)
WHERE ROWNUM <= 10;
或者
SELECT e.empno,e.ename,e.sal FROM emp_copy e WHERE ROWNUM<=10
MINUS
SELECT e.empno,e.ename,e.sal FROM emp_copy e WHERE ROWNUM<=5;
或者
SELECT * FROM
(SELECT ROWNUM RN,e.empno,e.ename,e.sal FROM emp_copy e WHERE ROWNUM<=10) T
WHERE T.RN>5
/* 使用别名*/
select * from (
select rownum wn,e.ename "姓名",e.job "工作",e.sal "薪水",d.dname"部门名称" from emp_copy e,dept_copy d
where d.deptno=e.deptno
and d.deptno=20
and rownum<=4)t
where t.wn>=3
WN | 姓名 | 工作 | 薪水 | 部门名称 |
3 | SCOTT | ANALYST | 3000.00 | RESEARCH |
4 | ADAMS | CLERK | 1100.00 | RESEARCH |