-【优秀文档】Oracle_查询.docVIP

Oracle SQL语句查询 一、数学函数 SELECT ROUND(123.35) FROM DUAL;//123 SELECT TRUNC(123.45) FROM DUAL;//123 SELECT ROUND(123.45,-1) FROM DUAL;//120 SELECT TRUNC(123.45,-1) FROM DUAL;//120 二、字符串函数 SELECT LENGTH(HELLO) FROM DUAL;//5 SELECT LPAD(ABC,5,*) FROM DUAL;//**ABC SELECT REPLACE(ABCDEFG,DE,WW) S1,REPLACE(ABCDEFG,DE) S2 FROM DUAL; SELECT SUBSTR(ABCDEFG,1,3) S1,SUBSTR(ABCDEFG,5,5) S2 FROM DUAL; 三、日期函数 SELECT TO_CHAR(SYSDATE,DD-MON-YY HH:MI:SS),ADD_MONTHS(SYSDATE,3) FROM DUAL; SELECT SYSDATE,LAST_DAY(SYSDATE),LAST_DAY(SYSDATE) + 1 FROM DUAL; SELECT MONTHS_BETWEEN(SYSDATE,25-12月-06) FROM DUAL; SELECT NEXT_DAY(SYSDATE,星期一) FROM DUAL; 1.创建用户 connect system/manager@eis create user oracle_admin identified by oracle_admin; grant connect,dba to oracle_admin; alter user oracle_admin default tablespace users quota unlimited on users; alter user oracle_admin temporary tablespace temp; 2.修改密码 connect oracle_admin/oracle_admin@eis alter user oracle_admin identified by welcome; 3.解除账号锁定 connect oracle_admin/oracle_admin@eis alter user oe account unlock; connect oe/oe@eis; 4.修改表空间设置（将默认表空间设置为user，临时表空间为temp） select tablespace_name,contents from dba_tablespaces order by tablespace_name; alter user hr default tablespace users temporary tablespace temp; 5.修改表空间配额 connect oracle_admin/oracle_admin@eis alter user hr quota unlimited on users quota 10M on temp quota 0M on system; 6.建立和删除用户 connect oracle_admin/oracle_admin@eis create user dropme identified by doomed default tablespace users temporary tablespace temp quota unlimited on users; grant create table,create session to dropme; connect dropme/doomed@eis create table demo (name varchar2(20)) connect oracle_admin/oracle_admin@eis drop user dropme cascade; 7.处理日期数据 connect oracle_admin/oracle_admin create table test1 (name varchar2(20),birth date); insert into test1(name,birth) values(zhangliyong,to_da