ORACLE进阶（四）知识库——创建属于自己的“新华字典”

ORACLE进阶（四）知识库——创建属于自己的“新华字典”

一、数据库定义语言DDL(Data Definition Language)

（一）表操作

--删表
drop table test; 
--建表
create table test(
id number(10) primary key,/*primary key 表示主键*/
month number(10),
status number(1) default 0 not null /*default 定义默认值*/ ,
name varchar2(32) not null /*not null 表示不为空*/,
remark varchar2(300) null,
create_date date
);
--定义注释
comment on table test is '测试表';
comment on column test.id is '主键';
--修改主键--start
alter table test drop  primary key;
alter table test add constraint pk_test primary key (month);
--如果知道约束constraint,可以根据约束删除
alter table test drop constraint pk_test ;
--修改主键--end
--拓展。表分区
create table test(
id number(10) primary key,/*primary key 表示主键*/
month number(10),
status number(1) default 0 not null /*default 定义默认值*/ ,
name varchar2(32) not null /*not null 表示不为空*/,
remark varchar2(300) null,
create_date date
)partition by range(month)(
 partition month_1 values less than (7),
 partition month_2 values less than (13)
 /*后面这段不写，默认分配*/
  tablespace USERS /*指定表空间*/
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
--字段增删改
alter table test add  remark2 varchar2(80);
alter table test modify  remark2 varchar2(100);
alter table test drop column remark2;
--快速清空表数据
truncate table test;

根据另一张表创建：

create table test2 as select * from test;

禁用DDL操作

create or replace trigger close_ddl
before create or drop or alter or truncate on database
declare
 err_msg varchar(100):='not allow ';
 begin
   if ora_sysevent ='CREATE' then
     raise_application_error(-20001, err_msg||ora_dict_obj_owner||'.'||ora_dict_obj_name);
     elsif ora_sysevent ='DROP' then
     raise_application_error(-20001, err_msg||ora_dict_obj_owner||'.'||ora_dict_obj_name);
       elsif ora_sysevent ='ALTER' then
     raise_application_error(-20001, err_msg||ora_dict_obj_owner||'.'||ora_dict_obj_name);
       elsif ora_sysevent ='TRUNCATE' then
     raise_application_error(-20001, err_msg||ora_dict_obj_owner||'.'||ora_dict_obj_name);
     end if;
     exception when no_data_found then null;
   end;

 

 （二）同义词

--建同义词
create or replace synonym db1.test for db2.test;
--删除同义词
drop synonym test;

（三） 索引

索引可以加快查询速度

drop index ind_test;
--唯一索引
create unique index ind_test on test(id) ;
--普通索引
create index idx_test1 on test1(a_id)

唯一索引，允许为空（不管是几个索引字段）。
主键，不允许为空。

缺点：
   索引占物理空间
   创建索引耗费时间
   表数据增加或删除，索引需要更新

（四）视图

drop view v_test;
create or replace view v_test as select id,name from test; 

（五）数据库链

create public database link to_orcl
  connect to SYSTEM
  using 'xx.xx.xx.xx:1521/orcl'; 

数据控制语言（DCL ）

--赋权
grant select,update on test  to   orcl;
grant execute on  pro_test to orcl;

二、数据操纵语言（Data Manipulation Language, DML）

--增删改查
insert into test(id,name) values(1,'test');
update test set name='test1' where id=1;
select * from test;
delete from test where id=1;

三、函数

--取长度length(字段或字符)  
select length(name) from test;
--截取substr(src,begin,length)
select substr('abc',0,2) from dual;
substr(src,begin)
--截去最后一个字符
substr(src,0,length(src)-1) 
--to_char
select to_char(sysdate,'yyyymmdd') from dual;
--大小写
select upper(id) from test;
select lower(id) from test;

 

四、其他技巧

（1）常用查询

--查询所有字段/字段类型
  select * from user_tab_columns t where table_name='TEST';
--判断表是否存在
select count(*)  from tab where tname ='TEST';
--查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from dba_users; 
--查看你能管理的所有用户！
select * from all_users; 
--查看当前用户信息 ！
select * from user_users; 

--查询表的主键
select t.*from user_constraints  t where lower(table_name) = 'test';

--查看系统重启日志：
select * from v$version;
--查看数据库名sid
select * from  V$database;
--查看定时任务：
SELECT * FROM user_jobs;

（2）千万级别数据库查询优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描,可以用union all 代替 or。
4.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
5.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
6.使用select  *的缺点有：查询出了不必要的列；效率上不如直接指定列名。
7.很多时候用 exists 代替 in 是一个好的选择： 
select num from a where num in(select num from b) 
用下面的语句替换： 
select num from a where exists(select 1 from b where num=a.num)
8.下面的查询也将导致全表扫描： 
select id from t where name like ‘%abc%’

9.in 和 not in 也要慎用，否则会导致全表扫描，如： 
select id from t where num in(1,2,3) 
对于连续的数值，能用 between 就不要用 in 了： 
select id from t where num between 1 and 3
10.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
 select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

（3）锁表/解锁

--查询进程号
select c.sid,c.SERIAL#,b.owner,b.object_name,a.locked_mode from v$locked_object a,dba_objects b,v$session c 
where b.object_id = a.object_id 
and a.session_id=c.SID
and object_name='TEST';

--杀进程
alter system kill session '7919,11543';

（4）更新分区关键字报错的解决方法

alter table test enable row movement;

 （5）数据库用户被锁的解决方法

 --ORA-28000：the account is locked
--数据库用户被锁：（sqlplus "/as sysdba"）
--解锁orcl用户
alter user orcl account unlock; 

（6）数据备份方案

--情况一：备份原表数据到历史表，原表数据不保留。
alter table test rename to test_his;
/*
重建原表、索引等
优点：相比create table test_his as select * from test 更加快捷。
*/

--情况二：备份原表数据到历史表，同时保留原表数据。
create table test_his as select * from test;
--缺点：数据量大，数据库性能差的情况，备份进度慢。

 （7）更改sqlplus中查询的数据变成科学计数法形式

set numw 25

（8）分析表

analyze table  test compute statistics;

（9）sqlplus操作

--sqlplus登录
sqlplus scott/tiger@orcl;