Oracle 基础

一、数据控制语句（DML）
1.insert
往表里插入值
insert into tablename(fieldname1,fieldname2, ... ...) values(value1,value2, ... ...);
往表1里插入表2对应的字段值
insert into tablename1(fieldname1,fieldname2, ... ...) select (fieldname1,fieldname2, ... ...) from tablename2;
 说明：
 字符串类型的字段值必须用单引号括起来；
insert时最大可操作的字符串长度≤4000单字节；如果超过，使用CLOB类型，方法借用DBMS_LOB程序包；
insert时如果用到从1开始自增长的序列号，应先建立一个序列号
create sequence seriesnumber increment by 1 start with 1 maxvalue 99999 cycle nocache;
seriesnumber最好是表名+序列号标记 ;最大值按字段长度来定，如果定义的自动增长序列号number(6)，最大值为999999；insert语句插入这个字段值为：seriesnumber.nextval
2.delete
delete from tablename where ... ...;
说明：
删除记录并不能释放oracle里被占用的数据块表空间，只是把被删除的数据块标成unused，可以恢复delete的数据；如果要删除一个大表的全部记录，可以用truncate命令，它可以释放oracle里被占用的数据块表空间，此操作不可回退
truncate table tablename;  
3.update
update tablename set fieldname1=value1,fieldname2=value2, ... ... where conditions;
说明：value没有赋值或定义时将把原来的记录清空为null，最好在修改前进行非空校验；

DML说明：以上SQL语句对表都加上了行级锁，执行后需commit，如果要撤回操作用rollback；执行语句的范围最好限定在一万条记录内，否则oracle处理这些事务会用到很大回退段，程序响应慢甚至失去响应，可以分段次完成，中间加上commit

二、数据定义语句（DDL）
1.create（创建表、索引、视图、同义词、过程、函数、数据库链接等）
常用字段类型：CHAR 固定长度的字符串，VARCHAR2 可变长度的字符串，NUMBER(M,N) 数字型（M是位数总长度，N是小数的长度），DATE 日期类型
创建表时可以给字段加上默认值，如DEFAULT SYSDATE，每次插入和修改时都能得到动作的时间；
创建表时可以给字段加上约束条件，如不允许重复UNIQUE，关键字PRIMARY KEY；
2.alter
改变表名
alter table tablename1 to tablename2;
在表的后面增加一个字段
alter table tablename add fieldname description;
修改表里字段的定义描述
alter table tablename modify fieldname description;
给表里的字段加上约束条件
alter table tablename add constraint constraintname primary key (fieldname);
alter table tablename add constraint constraintname unique (fieldname);
把表放进或取出数据库的内存区
alter table tablename cache;
alter table tablename nocache;
3.drop（删除表和它所有的约束条件）
drop table tablename cascade constraints;
4.truncate（清空表里所有记录，保留表结构）
truncate tablename;

三、查询语句（select）
select fieldname1,fieldname2, ... ... from tablename1,[tablename2, ... ...] where conditions;
字段名可以带入函数，如：count(*),min(fieldname),max(fieldname),avg(fieldname),distinct(fieldname),to_char(DATEfieldname,'YYYY-MM-DD HH24:MI:SS'),
nvl(expr1,expr2)函数:if express=null,return expr2;else,return expr1 
decode(AA,V1,R1,V2,R2 ... ...)函数:if AA=V1 then return R1;if AA=V2 then return R2; ... ... ;else,return null 
lpad(char1,n,char2)函数:字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位
字段名之间可以进行算数运算，如:(fieldname1*fieldname1)/3
查询语句可以嵌套
select ... from (select ... from tablename1,[tablename2, ... ...] where conditions1) where conditions2;
两个查询语句的结果可以做集合操作，如:并集union(去掉重复记录),并集union all(不去掉重复记录),差集minus,交集intersect
分组查询
select fieldname1,fieldname2, ... from tablename1,[tablename2, ... ...] group by fieldname1[having condition];
两个以上表之间的连接查询
select fieldname1,fieldname2, ... from tablename1,[tablename2, ... ...]where tablename1.fieldname=tablename2.fieldname [and ... ...];
select fieldname1,fieldname2, ... from tablename1,[tablename2, ... ...]where tablename1.fieldname=tablename2.fieldname(+) [and ... ...];                    (+)字段位置自动补空值
查询结果集的排序，默认是升序asc，降序是desc
select fieldname1,fieldname2, ... from tablename1,[tablename2, ... ...] order by fieldname1,fieldname2 desc;
字符串模糊比较的方法:instr(fieldname,'string')>0；fieldname like'string%'['%string%']
每个表都有一个隐含的字段rowid，它标记着记录的唯一性

四、schema（常用数据对象）
1.index（索引）
create index indexname on tablename(fieldname1,[fieldname2, ... ...]);
alter index indexname rebuild;
一个表的索引最好不要超过三个（特殊大表除外），最好用单字段索引或多字段组合索引和基于函数的索引

四、schema（常用数据对象）
1.index（索引）
create index indexname on tablename(fieldname1,[fieldname2, ... ...]);
alter index indexname rebuild;
一个表的索引最好不要超过三个（特殊大表除外），最好用单字段索引或多字段组合索引和基于函数的索引
2.view（视图）
create view viewname as select ... from ... ;
alter view viewname compile;
视图仅仅是个sql查询语句，可以把表之间复杂的关系简洁化
3.synonmy（同义词）
create synonmy synonmyname connect to username identfied by password using 'database connection string';
数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义
数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样
数据库全局名称可以用以下命令查出
select * from global_name;
查询远端数据库里的表
select ... from tablename@DBLinkName;

五、权限管理语句（DCL）
1.grant
常用的系统权限集合：connect(基本连接),resource(程序开发),DBA(数据库管理)
常用的数据对象权限：all on dataObjectName，select on dataObjectName，update on dataObjectName，
delete on dataObjectName，insert on dataObjectName，alter on dataObjectName
grant connect,resource to username;
grant select on tablename from username;
grant select,insert,delete on tablename to username1,username2;
2.revoke
revoke connect,resource to username;
revoke select on tablename from username;
revoke select,insert,delete on tablename to username1,username2;

查询数据库中第63号错误：
select orgaddr,destaddr from sm_histable0116 where error_code='63';
查询数据库中开户用户最大提交和最大下发数：
select msisdn,tcos,ocos from ms_usertable;
查询数据库中各种错误代码的总和：
select error_code,count(*) from sm_histable0513 group by error_code order by error_code;
查询报表数据库中话单统计种类查询：
select sum(Successcount) from tbl_MiddieMt0411 where ServiceType2=111;
select sum(Successcount),servicetype from tbl_middiemt0411 group by servicetype;