`
- 浏览:
153154 次
- 性别:
- 来自:
球面世界
-
創建
sql 代码
- create table a_bak as select * from a where 1=2;
- create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;
-
-
- create view a as select b1,b2 from b group by b1,b2;
-
-
- create sequence seq1
- increment by 1
- start with 1
- maxvalue 999999999
- cycle;
-
- insert into values(seq1.nextval,1,2);
刪除
sql 代码
-
-
- select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
- delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
-
-
- delete from a where exists (select 'X' from b where a.bm=b.bm and a.mc=b.mc);
- delete from a where (bm,mc) in (select bm,mc from b);
-
-
- TRUNCATE TABLE OTS_ISSUE_CONFIG_TBL;
-
插入
sql 代码
-
- insert into a values('i''m good');
- insert into a values('i'||chr(39)||'m good');
- insert into a values('a'||'&'||'b');
-
選擇
sql 代码
-
- SELECT 'CI' || to_char(sysdate, 'yymmdd') || trim(to_char((to_number( substr( nvl(max(ID), 'CI991231000'), 9, 3)) + 1), '000')) as next_comment_id
- FROM NEWBUZ.CS_COMMENT
-
-
- select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
-
-
- select 1||2 from 1;
- select concat(1,2) from 1;
-
管理
sql 代码
-
- select * from tab;
-
-
- select b.file_id 文件id,
- b.tablespace_name空間名,
- b.file_name物理文件名,
- b.bytes字節,
- (b.bytes-sum(nvl(a.bytes,0)))已經使用,
- sum(nvl(a.bytes,0))剩餘,
- sum(nvl(a.bytes,0))/(b.bytes)*100剩下百分比
- from dba_free_space a,dba_data_files b
- where a.file_id=b.file_id
- group by b.tablespace_name,b.file_name,b.file_id,b.bytes
- order by b.tablespace_name
- /
- dba_free_space
- dba_data_files
-
-
- segment_name,owner,tablespace_name,segment_id,file_id,status from dba_rollback_segs;
-
-
- col file_name format a50
- select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
-
-
- show user
-
-
- v$datafile數據文件信息
- v$filestat訪問信息
-
- select
- df.name filename,
- fs.phyrds readdegree,
- fs.phywrts writedegree,
- (fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,
- (fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetime
- from
- v$datafile df,
- v$filestat fs
- where df.file#=fs.file#
- order by df.name
-
-
- select object_id from v$locked_object;
-
- select object_id,object_name from dba_objects where object_id=v$locked_object.object_id
-
- select sid ,serial# from v$session where sid=v$locked_object.object_id
-
- alter system kill session 'sid,serial#'
-
-
常用
sql 代码
- ASCII
-
- select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
-
- CHR
-
- select chr(54740) zhao,chr(65) chr65 from dual;
-
- CONCAT
-
- select concat('010-','88888888')||'转23' 高乾竞电话 from dual;
-
- INITCAP
-
- select initcap('smith') upp from dual;
-
- INSTR(C1,C2,I,J)
-
-
-
-
-
- select instr('oracle traning','ra',1,2) instring from dual;
-
- LENGTH
-
- select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
-
- LOWER
-
- select lower('AaBbCcDd')AaBbCcDd from dual;
-
- UPPER
-
- select upper('AaBbCcDd') upper from dual;
-
- RPAD LPAD (粘贴字符)
-
-
- select lpad(rpad('gao',10,'*'),17,'*')from dual;
-
- LTRIM RTRIM
-
-
- select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
-
- SUBSTR(string,start,count)
-
- select substr('13088888888',3,8) from dual;
-
- REPLACE('string','s1','s2')
-
-
-
- select replace('he love you','he','i') from dual;
-
- SOUNDEX
-
- select xm from table1 where soundex(xm)=soundex('weather');
-
- TRIM('s' from 'string')
-
-
-
-
- ABS
-
- select abs(100),abs(-100) from dual;
-
- ACOS
-
- select acos(-1) from dual;
-
- ASIN
-
- select asin(0.5) from dual;
-
- ATAN
-
- select atan(1) from dual;
-
- CEIL
-
- select ceil(3.1415927) from dual;
-
- COS
-
- select cos(-3.1415927) from dual;
-
- COSH
-
- select cosh(20) from dual;
-
- EXP
-
- select exp(2),exp(1) from dual;
-
- FLOOR
-
- select floor(2345.67) from dual;
-
- LN
-
- select ln(1),ln(2),ln(2.7182818) from dual;
-
- LOG(n1,n2)
-
- select log(2,1),log(2,4) from dual;
-
- MOD(n1,n2)
-
- select mod(10,3),mod(3,3),mod(2,3) from dual;
-
- POWER
-
- select power(2,10),power(3,3) from dual;
-
- ROUND和TRUNC
-
- select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
-
- SIGN
-
- select sign(123),sign(-100),sign(0) from dual;
-
- SIN
-
- select sin(1.57079) from dual;
-
- SIGH
-
- select sin(20),sinh(20) from dual;
-
- SQRT
-
- select sqrt(64),sqrt(10) from dual;
-
- TAN
-
- select tan(20),tan(10) from dual;
-
- TANH
-
- select tanh(20),tan(20) from dual;
-
- TRUNC
-
- select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
-
- ADD_MONTHS
-
- select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
-
- MONTHS_BETWEEN(date2,date1)
-
- select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
-
- NEW_TIME(date,'this','that')
-
- select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
-
- NEXT_DAY(date,'day')
-
- select next_day('18-5月-2001','星期五') next_day from dual;
-
- SYSDATE
-
- select to_char(sysdate,'dd-mm-yyyy day') from dual;
-
- CHARTOROWID
-
- select rowid,rowidtochar(rowid),ename from scott.emp;
-
- CONVERT(c,dset,sset)
-
- select convert('strutz','we8hp','f7dec') "conversion" from dual;
-
- HEXTORAW
-
-
- RAWTOHEXT
-
-
- ROWIDTOCHAR
-
-
- TO_CHAR(date,'format')
- select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
-
- TO_DATE(string,'format')
-
-
- TO_MULTI_BYTE
-
- select to_multi_byte('高') from dual;
-
- TO_NUMBER
-
- select to_number('1999') year from dual;
-
- BFILENAME(dir,file)
-
- insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
-
- DECODE
-
- select sid,serial#,username,decode(command,
- 0,'none',
- 2,'insert',
- 3,'select',
- 6,'update',
- 7,'delete',
- 8,'drop',
- 'other') cmd from v$session where type!='background';
-
-
- DUMP(s,fmt,start,length)
-
- col global_name for a30
- col dump_string for a50
- set lin 200
- select global_name,dump(global_name,1017,8,5) dump_string from global_name;
-
-
- EMPTY_BLOB()和EMPTY_CLOB()
-
-
- GREATEST
-
- select greatest('AA','AB','AC') from dual;
-
- LEAST
-
- select least('啊','安','天') from dual;
-
- UID
-
- show user
- select username,user_id from dba_users where user_id=uid;
-
- USER
-
- select user from dual;
-
- USEREVN
-
-
-
-
- select userenv('isdba') from dual;
-
- select userenv('sessionid') from dual;
-
- select userenv('entryid') from dual;
-
- select userenv('instance') from dual;
-
- select userenv('language') from dual;
-
- select userenv('lang') from dual;
-
- select userenv('terminal') from dual;
-
- VSIZE(X)
-
- select vsize(user),user from dual;
-
- AVG(DISTINCT|ALL)
-
- create table table3(xm varchar(8),sal number(7,2));
- insert into table3 values('gao',1111.11);
- insert into table3 values('gao',1111.11);
- insert into table3 values('zhu',5555.55);
- commit;
- select avg(distinct sal) from gao.table3;
- select avg(all sal) from gao.table3;
-
- MAX(DISTINCT|ALL)
-
- select max(distinct sal) from scott.emp;
-
- MIN(DISTINCT|ALL)
-
- select min(all sal) from gao.table3;
-
- STDDEV(distinct|all)
-
- select stddev(sal) from scott.emp;
-
- VARIANCE(DISTINCT|ALL)
-
- select variance(sal) from scott.emp;
-
- GROUP BY
-
- select deptno,count(*),sum(sal) from scott.emp group by deptno;
-
- HAVING
-
- select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
- select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
-
- ORDER BY
-
- select deptno,ename,sal from scott.emp order by deptno,sal desc;
-
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
oracle 常用SQL语法手册 oracle 常用SQL语法手册
oracle 常用SQL语法手册
oracle常用sql语法集合与sql语法大全
ORACLE和SQL语法区别归纳 对于新手有帮助
Oracle与SQLServer的SQL语法差异,用简单易懂的语言和实例对Oracle和Sql Server语法之间的差异进行了对比分析,更加适用于入门的人
Oracle的SQL语法大全Oracle的SQL语法大全
ORACLE常用SQL语句大全.pdf
Oracle学习,常用sql语句汇总,适合新手使用。
oracle+SQL语法大全oracle+SQL语法大全oracle+SQL语法大全oracle+SQL语法大全oracle+SQL语法大全oracle+SQL语法大全oracle+SQL语法大全
oracle sql 语法大全 oracle sql 语法大全
Oracle中常用SQL语法和数据对象 经典实用
Oracle和SqlServer的语法区别
oracle-sql语法大全(简单实用),语法的大体讲解教程
这一部分概述了 Transact-SQL 和 PL/SQL 语言语法之间的相同点和不同点,并给出转换策略。 要将 Oracle DML 语句和 PL/SQL 程序迁移到 SQL Server 时,请按下列步骤执行: 1. 验证所有 SELECT、INSERT、UPDATE 和 ...
ORACLE 常用的SQL语法和数据对象(CHM电子书) 常用SQL语句
oracle SQL语法大全是常用命令的学习总结
Oracle_Sql语句资料oracle+110个常用函数经典SQL语句大全,可供学习参考。