面试部分知识点整理

来源：互联网发布：简述js闭包使用场景编辑：程序博客网时间：2024/05/16 15:21

1.将表A拷贝到表B，通过存储过程

CREATE OR REPLACE PROCEDURE copy_xxx
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE xxx AS SELECT * FROM xxx';
END;

2.Nls_Session_Parameters

3.临时表空间和永久表空间

临时表空间通常仅存在PGA中，DML操作不生成重做数据，通常是在会话生存期间的数据

4.索引

B-tree索引，位图索引

5.横表改纵表

SELECT t.id,SUM(DECODE(VALUE,'t1',0)) AS t1,SUM(DECODE(VALUE,'t2',0)) AS t2,SUM(DECODE(VALUE,'t3',0)) AS t3FROM test1 tGROUP BY t.id ORDER BY t.id

------------------------------------分割线-----------2014.06.16--------------------------------------------------------

6.给表增加一列，并创建索引到该列

alter table test1 add (id,varchar2(20));

create index test11 on test1(id);

7.删除表数据

drop truncate

8.将字符转成日期格式

9.内连接,外连接,全连接

10.删除表中重复行只留下一行

DELETE FROM TEST1 WHERE ROWID IN (SELECT ROWID                   FROM (SELECT T.ID,                                T.NAME,                                ROWID,                                ROW_NUMBER() OVER(PARTITION BY ID, NAME ORDER BY ID) ROW_WAR                           FROM TEST1 T)                  WHERE ROW_WAR > 1);DELETE FROM TEST1 A WHERE ROWID > (SELECT MIN(ROWID)                  FROM TEST1 B                 WHERE A.ID = B.ID                   AND A.NAME = B.NAME);

0 0