Oracle 数据库萌新经验小结

刚出道的时候，学的就是oracle数据库，后期兴趣所向，转行java，
翻出以前的总结笔记，拿来分享给大家

有点小多，想直接看文档的小伙伴可以去网盘下载（文档里有格式和目录，好看点）：

链接：https://pan.baidu.com/s/1t_ybXIbRCCJFZAmqQ6lx-Q 
提取码：o18i 

 

常用查询及操作

复制表结构和数据

1. 复制表结构及数据到新表:CREATE TABLE 新表 SELECT * FROM 旧表
2. 只复制表结构到新表:CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

即:让WHERE条件不成立.

方法二:CREATE TABLE 新表 LIKE 旧表

3.复制旧表的数据到新表(假设两个表结构一样):

INSERT INTO 新表 SELECT * FROM 旧表

4.复制旧表的数据到新表(假设两个表结构不一样)

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

insert into b(a, b, c) select d,e,f from b;

 

说明：复制表(只复制结构,源表名：a 新表名：b) 

SQL: select * into b from a where 1<>1

 

说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

 

说明：显示文章、提交人和最后回复时间

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

 

说明：外连接查询(表名1：a 表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

 

说明：日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

 

说明：两张关联表，删除主表中已经在副表中没有的信息

SQL:delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

常用嵌套查询

1. 每个部门前几名提取出来

select e.department,e.salary from (select department, salary,row_number() over (partition by department order by salary desc) rn from employee) e where e.rn<=3;

1. 部门表和员工表，获取各个部门的最大工资？

Select deptno, max(sal) from 员工表 left outer join 部门表 on 员工表.deptno=部门表.deptno group by 部门表.deptno;

Select emp.deptno, Max(sal) From emp Left Join dept On dept.deptno=emp.deptno Group By emp.deptno

1. 如何查找和删除表中重复数据：

Select * from A not exists(select B.id from B where A.id=B.id);

 

1. 请写一段SQL，找出在2011年1月1日，客户地址发生变更的客户，并将这些客户2011.1.1的地址和电话显示出来，显示如下（8分）

客户编号	更新日期	家庭地址	家庭电话
3109026058996	XXXXXXXX	XXXXXX	XXXXX

 

Select  Party_Id 客户编号

       ,'2011-01-01' 更新日期

       , max(case when Addr_Type2_Cd='02' then Addr_Line1 end) 家庭地址//取到最后更新的那一条记录

       , max(case when Addr_Type_Cd='01' then Addr_Line1 end) 家庭电话

From    T06_ADDRESS

Where   St_Dt<=date'2011-01-01'

and     End_Dt>date'2011-01-01'//找出现在正常使用的客户，即2011.1.1号这个时间点上的信息

and     Party_Id in (select  Party_Id

                     From    T06_ADDRESS

                     Where   Addr_Type_Cd='02'

                     And     St_Dt=date'2011-01-01')

group by Party_Id;

（1）从T06_ADDRESS表中筛选出在2011年1月1日客户地址发生变更的客户编号

select  Party_Id

From    T06_ADDRESS

Where   Addr_Type_Cd='02'

And     St_Dt=date('2011-01-01')

（2）再从T06_ADDRESS表中查出客户编号为（1）的客户的地址和电话（当地址类型为‘02’时显示家庭地址，当地址类型为‘01’时显示家庭电话），并且结果中把客户编号和更新日期也显示出来

 

经典操作

• 用户管理

新建用户

Create User [usrename] Identyfied by [userpassword] [Default tablespace] [tempory tablespace]

授予权限

Grant connect to [username/rolename] [options]

更改用户

Alter [user]

 

• 向表中加入字段

　　通用式： alter table [表名] add [字段名] 字段属性 default 缺省值 default 是可选参数

 例如: ALTER TABLE employee  ADD  post varchar(20)

　　增加字段： alter table [表名] add 字段名 smallint default 0 增加数字字段，整型，缺省值为0

 

　　alter table [表名] add 字段名 int default 0 增加数字字段，长整型，缺省值为0

 

　　alter table [表名] add 字段名 single default 0 增加数字字段，单精度型，缺省值为0

 

　　alter table [表名] add 字段名 double default 0 增加数字字段，双精度型，缺省值为0

 

　　alter table [表名] add 字段名 Tinyint default 0 增加数字字段，字节型，缺省值为0

 

　　alter table [表名] add 字段名 text [null] 增加备注型字段，[null]可选参数

 

　　alter table [表名] add 字段名 memo [null] 增加备注型字段，[null]可选参数

 

　　alter table [表名] add 字段名 varchar（N） [null] 增加变长文本型字段大小为N（1～255）

 

　　alter table [表名] add 字段名 char [null] 增加定长文本型字段大小固定为255

 

　　alter table [表名] add 字段名 Datetime default 函数增加日期型字段，其中函数可以是 now（），date（）等，表示缺省值

 

　　（上面都是最常用的，还有其他的属性，可以参考下面的数据类型描述）

 

　　删除字段： alter table [表名] drop 字段名

 

　　修改变长文本型字段的大小：alter table [表名] alter 字段名 varchar（N）

 

　　删除表： drop table [表名]

 

　　创建表：

 

　　sql＝"CREATE TABLE [表名] （[字段1，并设置为主键] int IDENTITY （1， 1） NOT NULL CONSTRAINT PrimaryKey PRIMARY KEY，"&

 

　　"[字段2] varchar（50），"&

 

　　"[字段3] single default 0，"&

 

　　"[字段4] varchar（100） null，"&

 

　　"[字段5] smallint default 0，"&

 

　　"[字段6] int default 0，"&

 

　　"[字段7] date default date（），"&

 

　　"[字段8] int default 1）"

 

　　conn．execute sql

 

　　有null 的表示字段允许零长

 

• 表关联

if exists(select 1 from sys.sysforeignkey where role='FK_对公贷款账户信息_REFERENCE_对公账户') then

    alter table 对公贷款账户信息表

       delete foreign key FK_对公贷款账户信息_REFERENCE_对公账户

end if;

 

if exists(select 1 from sys.sysforeignkey where role='FK_对公贷款账户信息_REFERENCE_对公贷款账户余额') then

    alter table 对公贷款账户信息表

       delete foreign key FK_对公贷款账户信息_REFERENCE_对公贷款账户余额

end if;

 

drop table if exists 对公贷款账户信息表;

 

/*==============================================================*/

/* Table: 对公贷款账户信息表                                             */

/*==============================================================*/

create table 对公贷款账户信息表

(

   ACCT_NO              varchar2                       null,

   LOAN_NO              U_LOAN_NO                      not null,

   AREA_NO              U_AREA_NO                      not null,

   ITEM_NO              U_ITEM_NO                      null,

   ORG_NO               U_ORG_NO                       null,

   CURRENCY             U_CURRENCY                     null,

   TERM_NO              U_LOAN_TERM                    null,

   LOAN_DATE            DATE                           null,

   MATURE_DATE          DATE                           null,

   PRODUCT_NO           U_PRODUCT_NO                   null,

   INDUSTRY_NO          U_INDUSTRY                     null,

   ASSURE_MODE          U_ASSURE_MODE                  null,

   LEVEL_FOUR           U_LEVEL_FOUR                   null,

   LEVEL_RISK           U_LEVEL_RISK                   null,

   CARD_NO              U_CARD_NO                      null,

   INTEREST_RATE        U_HIGH_PRECIS_RATE             null,

   OVERDUE_DAYS         INTEGER                        null,

   NONPERFORMING_FLAG   U_NONPERFORM_FLAG              null,

   DEDUCT_ACCT_NO       U_ACCT_NO                      null,

   INTEREST_OWED        U_MONEY                        null,

   INTEREST_OWED_OUT    U_MONEY                        null,

   INTEREST_RATE_DATE   DATE                           null,

   LOAN_AMT             U_MONEY                        null,

   BAL                  U_MONEY                        null,

   MORTGAGE_FLAG        U_BOOLEAN                      null,

   CUST_LEVEL           U_CUST_LEVEL                   null,

   LOAN_VARIETY         U_LOAN_VARIETY                 null,

   LOAN_TYPE            U_LOAN_TYPE_U                  null,

   COLLATERAL_VALUE     U_MONEY                        null,

   PLEDGED_CURRENCY     U_CURRENCY                     null,

   PLEDGED_AMT          U_MONEY                        null,

   EAD_RATE             U_HIGH_PRECIS_RATE             null,

   FLOAT_TYPE           U_FLOAT_TYPE                   null,

   FLOAT_RATE           U_HIGH_PRECIS_RATE             null,

   DAY_REMAIN           INTEGER                        null

       constraint CKC_DAY_REMAIN_对公贷款账户信息 check (DAY_REMAIN is null or (DAY_REMAIN in (0,1))),

   STATUS               U_STATUS                       null,

   BAIL_ACCT            U_ACCT_NO                      null,

   BAIL                 U_MONEY                        null,

   BAIL_CURRENCY        U_CURRENCY                     null,

   FIRM_SIZE            U_FIRM_SIZE                    null,

   TRUST_PAY_AMT        U_MONEY                        null,

   RPDATE               DATE                           null

);

 

alter table 对公贷款账户信息表

   add constraint FK_对公贷款账户信息_REFERENCE_对公账户 foreign key (ACCT_NO)

      references 对公账户 (ACCT_NO)

      on update restrict

//外键约束，如果存在从数据就不能更新主数据

      on delete restrict;

//外键约束，如果存在从数据就不能删除主数据

alter table 对公贷款账户信息表

   add constraint FK_对公贷款账户信息_REFERENCE_对公贷款账户余额 foreign key ()

      references 对公贷款账户余额表

      on update restrict

      on delete restrict;

 

• 更改表中字段的长度

ALTER TABLE [table_name] MODIFY [column_name] [new type]

 

• 将一张表中的数据放到另一张表里

ALTER TABLE ODS_NBS_MCJNL

RENAME TO ODS_NBS_MCJNL_1

 

CREATE TABLE ODS_NBS_MCJNL

TABLESPACE ETL_SYS

AS

SELECT * FROM ODS_NBS_MCJNL_1 WHERE 1=0;

 

 

INSERT INTO ODS_NBS_MCJNL

SELECT  * FROM ODS_NBS_MCJNL_1

 

SELECT COUNT(*) FROM ODS_NBS_MCJNL_1

 

DROP TABLE ODS_NBS_MCJNL_1

 

 

• DBlink

不同数据库之间的连接

CREATE PUBLIC DATABASE LINK <DBLINK 名称> CONNECT TO <被连接的用户名> IDENTIFIED BY <被连接库的密码> USING '<ORACLE 客户端工具建立的指向被连接库服务名>'

 

在xxx.ORA里配置好    或直接配置好

'(DESCRIPTION=

    (ADDRESS= (PROTOCOL=TCP)(HOST=10.53.3.210)(PORT=1521))

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=GZPEMSDB)

    )

  )'

 

 

 

 

 

系统表

• 系统表列表

dba_开头..... 
dba_users 数据库用户信息dba_segments 表段信息dba_extents 数据区信息
dba_objects 数据库对象信息dba_tablespaces 数据库表空间信息
dba_data_files 数据文件设置信息dba_temp_files 临时数据文件信息
dba_rollback_segs 回滚段信息dba_ts_quotas 用户表空间配额信息
dba_free_space数据库空闲空间信息dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息dba_tab_privs用户具有的对象权限信息
dba_col_privs用户具有的列对象权限信息dba_role_privs用户具有的角色信息
dba_audit_trail审计跟踪记录信息dba_stmt_audit_opts审计设置信息
dba_audit_object 对象审计结果信息dba_audit_session会话审计结果信息
dba_indexes用户模式的索引信息

user_开头
user_objects 用户对象信息user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息user_tables 用户的表对象信息
user_tab_columns 用户的表列信息user_constraints 用户的对象约束信息
user_sys_privs 当前用户的系统权限信息user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息user_ind_columns用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息

user_cluster_hash_expressions 散列簇的信息
v$开头
v$database 数据库信息v$datafile 数据文件信息v$controlfile控制文件信息
v$logfile 重做日志信息v$instance 数据库实例信息v$log 日志组信息
v$loghist 日志历史信息v$sga 数据库SGA信息v$parameter 初始化参数信息
v$process 数据库服务器进程信息v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息v$thread 线程信息
v$datafile_header 数据文件头所记载的信息v$archived_log归档日志信息
v$archive_dest 归档日志的设置信息

v$logmnr_contents 归档日志分析的DML DDL结果信息
v$logmnr_dictionary 日志分析的字典文件信息v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息v$tempfile 临时文件信息v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息v$rollname 在线回滚段信息v$session 会话信息
v$transaction 事务信息v$rollstat 回滚段统计信息v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql 与v$sqlarea基本相同的相关信息v$sysstat 数据库系统状态信息
all_开头
all_users 数据库所有用户的信息all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息all_tables 所有的表对象信息
all_indexes所有的数据库对象索引的信息

session_开头
session_roles 会话的角色信息session_privs 会话的权限信息

index_开头
index_stats 索引的设置和存储信息
伪表
dual 系统伪列表信息

 

1.系统表 ORACLE数据库的系统参数都存储在数据库中，可以通过SQLPLUS，以用户SYSYTEM进行查询。几个重要的表或者视图如下：

v$controlfile：控制文件的信息；

v$datafile：数据文件的信息；

v$log：日志文件的信息；

v$process：处理器的信息；

v$session：会话信息；

v$transaction：事务信息；

v$resource：资源信息；

v$sga：系统全局区的信息。

上面的视图名中的‘v$’,只是视图名字中的字符。类似于上面的视图或表还有很多，位于： $ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL文件中。 这些视图或表可以在SQLPLUS中用SELECT语句进行查询。

2.数据字典视图

表和列 DBA_TABLES、ALL_TABLES和USER_TABLES显示了有关数据库表的一般信息。 DBA_TAB_COLUMNS、ALL_TAB_COLUMNS和USE..

 

 

• 查询数据库中的表

Select * from cat;

--某个用户的表有哪些

Select table_name from all_tables where owner=’scott’;

--查找该表所属用户有哪些

Select * from all_tab_comments where table_name in('[表名]', '[表名]', '[表名]'……);

--查找同义词下的用户有哪些

Select * from all_synoyms where table_name in ('[表名]', '[表名]', '[表名]'……);

--查看plsql或oracle编码

Select * from v$nls_paramenters t where t.parament='NLS_CHARACTERSET';

 

 

Sql优化

查询的逻辑执行顺序

　　(1) FROM left_table

(3) join_type JOIN right_table

(2) ON join_condition

　　(4) WHERE where_condition

　　(5) GROUP BY group_by_list

　　(6) WITH {cube | rollup}

　　(7) HAVING having_condition

(8) SELECT

(9) DISTINCT

(10) ORDER BY order_by_list

(11) top_specification select_list

　　标准的 SQL 的解析顺序为:

　　(1) FROM 子句 组装来自不同数据源的数据

　　(2) WHERE 子句 基于指定的条件对记录进行筛选

　　(3) GROUP BY 子句 将数据划分为多个分组

　　(4) 使用聚合函数进行计算

　　(5) 使用HAVING子句筛选分组

　　(6) 计算所有的表达式

　　(7) 使用ORDER BY对结果集进行排序

　　二、执行顺序

　　1. FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1

　　2. ON: 对vt1表应用ON筛选器只有满足 join_condition 为真的行才被插入vt2

　　3. OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。

　　4. WHERE：对vt3应用 WHERE 筛选器只有使 where_condition 为true的行才被插入vt4

　　5. GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5

　　6. CUBE|ROLLUP：把超组(supergroups)插入vt6，生成vt6

　　7. HAVING：对vt6应用HAVING筛选器只有使