oracle培训资料.doc

Oracle基础知识 sql语句 Select [distinct]{*|column,[expression],….} ROM表名或视图名[,表名或视图名]…… 　　 [WHERE 条件表达式] 　　 [GROUP BY列名1[HAVING条件表达式]] 　　 [ORDER BY列名2[ASC|DESC]]； 内连接 例如：select a.a1,a.c1,b.b1 From table1 a join table2 b On a.c1=b.c1 外连接 例如：select a.a1,a.c1,b.b1 From table1 a [left outer join | right out join | full out jion] table2 b On a.c1=b.c1 select a.a1,a.c1,b.b1 From table1 a ,table2 b Where a.c1 (+)=b.c1 DDL Create、Alter、Drop、Rename、 Truncate create table PUB_USERS( USER_ID VARCHAR2(30) not null, EXTERNAL_USER VARCHAR2(30) not null, USER_NAME VARCHAR2(30) not null, PASSWORD VARCHAR2(128) not null, ACCOUNT_STATUS CHAR(2) default 11 not null, SECURITY_LEVEL INTEGER not null, MAX_SESSIONS INTEGER default -1 not null, IS_SYS CHAR(1) default 0 not null, IS_CPUBLIC CHAR(1) default 0 not null, mm_dd number(8,2) ) Create table tablename as Select * from tablename Alter table tablename add|delete | MODIFY () alter table aaa rename column A1 to B1 RENAME old_name TO new_name; Truncate tablename DML Insert、Update、Delete、Commit、Rollback、Savepoint Inser into tablename [(cloumn_list)] values(); Insert into tablenamea [(cloumn_list)] Select [(cloumn_list)] from tablenameb; Savepoint mark1； ROLLBACK TO SAVEPOINT mark1； Commit; DCL Grant、Revoke Grant select on tablename to username ; REVOKE SELECT,UPDATE ON tablename FROM username; Oracle函数 字符和数值函数 LOWER(c) 将指定字符串内字符变为小写 例如：SELECT LOWER(WhaT is tHis) FROM DUAL; UPPER(c) 将指定字符串内字符变为大写 例如：SELECT UPPER(WhaT is tHis) FROM DUAL; LPAD(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点： 如果nc1.length则从右到左截取指定长度返回； 如果nc1.length and c2 is null，以空格从左向右补充字符长度至n并返回； 如果nc1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回； 例如：SELECT LPAD(WhaT is tHis,5),LPAD(WhaT is tHis,25),LPAD(WhaT is tHis,25,-) FROM DUAL; RPAD(c1,n[,c2]) 基本与上同，不过补充字符是从右向左方向正好与上相反 TRIM([[LEADING||TRAILING||BOTH] c2 FROM]