一．数据的维护：
1.插入：
sql="insert into 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"
sql="insert into 数据表 valuess (值1,值2,值3 …)"
不指定具体字段名表示将按照数据表中字段的顺序，依次添加
sql="insert into 目标数据表 select * from 源数据表"
把源数据表的记录添加到目标数据表
2.查询：
Sql = "Select Distinct 字段名 From 数据表"
Distinct函数，查询数据库存表内不重复的记录
sql="select * from 数据表 where 字段名 between 值1 and 值2"
Sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
Sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
查找数据库中前10记录
Sql="select top n * form 数据表 order by newid()"
随机取出数据库中的若干条记录的方法
top n，n就是要取出的记录数
Sql="select * from 数据表 where 字段名 in ('值1','值2','值3')"
3.更新：
Sql="update 数据表 set 字段名=字段值 where 条件表达式"
Sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"
Sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n "
没有条件则更新整个数据表中的指定字段值
4.删除：
Sql="delete from 数据表 where 条件表达式"
Sql="delete from 数据表"
没有条件将删除数据表中所有记录)
二．数据表的操作：
1.建表
create table 表名
(
字段1名 类型 约束1 约束2....,
字段2名 类型 约束1 约束2....,
字段3名 类型 约束1 约束2....,
字段4名 类型 约束1 约束2....,
........
)
注：约束有（主键约束：primary key；外键约束：foreign key；非空约束：not null；
默认约束：default；惟一约束：unique；检查约束：check；标识约束：identity）
类型有（）
2.查看表结构
desc 表名
3.修改表――增加一列
alter table表名
add 列名 类型 约束1 约束2….
4.修改表――删除一列
alter table表名
drop column列名
5.修改表――修改属性的类型
alter table表名
alter column 列名 新类型
6.修改表――增加约束
alter table表名
add constraint 约束名 约束类型
7.修改表――删除约束
alter table表名
drop constraint约束名
8.删除表
drop table 表名
1.说明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
2.说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
3.说明：外连接查询(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c
4.说明：两张关联表，删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
5.说明：四表联查问题：
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
三：表空间的操作：
数据库维护的sql：
Oracle数据库维护常用SQL语句集合
2008-08-06 18:02
性能相关内容
1、捕捉运行很久的SQL
column username format a12
column opname format a16
column progress format a8
SELECT Username, Sid, Opname,
Round(Sofar * 100 / Totalwork, 0) || '%' AS Progress, Time_Remaining,
Sql_Text
FROM V$session_Longops, V$sql
WHERE Time_Remaining <> 0
AND Sql_Address = Address
AND Sql_Hash_Value = Hash_Value;
2、求DISK READ较多的SQL
SELECT St.Sql_Text
FROM V$sql s, V$sqltext St
WHERE s.Address = St.Address
AND s.Hash_Value = St.Hash_Value
AND s.Disk_Reads > 300;
3、求DISK SORT严重的SQL
SELECT Sess.Username, SQL.Sql_Text, Sort1.Blocks
FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1
WHERE Sess.Serial# = Sort1.Session_Num
AND Sort1.Sqladdr = SQL.Address
AND Sort1.Sqlhash = SQL.Hash_Value
AND Sort1.Blocks > 200;
4、监控索引是否使用
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;
5、求数据文件的I/O分布
SELECT Df.NAME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim,
Writetim
FROM V$filestat Fs, V$dbfile Df
WHERE Fs.File# = Df.File#
ORDER BY Df.NAME;
6、查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
7、回滚段查看
SELECT Rownum, Sys.Dba_Rollback_Segs.Segment_Name NAME,
V$rollstat.Extents Extents, V$rollstat.Rssize Size_In_Bytes,
V$rollstat.Xacts Xacts, V$rollstat.Gets Gets, V$rollstat.Waits Waits,
V$rollstat.Writes Writes, Sys.Dba_Rollback_Segs.Status Status
FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname
WHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_Name
AND V$rollstat.Usn(+) = V$rollname.Usn
ORDER BY Rownum
8、查看系统请求情况
SELECT Decode(NAME, 'summed dirty write queue length', VALUE) /
Decode(NAME, 'write requests', VALUE) "Write Request Length"
FROM V$sysstat
WHERE NAME IN ('summed dirty queue length', 'write requests')
AND VALUE > 0;
9、计算data buffer 命中率
SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads",
Round(100 * ((a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE)) "BUFFER HIT RATIO"
FROM V$sysstat a, V$sysstat b, V$sysstat c
WHERE a.Statistic# = 40
AND b.Statistic# = 41
AND c.Statistic# = 42;
SELECT NAME,
(1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * 100 h_Ratio
FROM V$buffer_Pool_Statistics;
10、查看内存使用情况
SELECT Least(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Used,
MAX(b.VALUE) / (1024 * 1024) Shared_Pool_Size,
Greatest(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) -
(SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Avail,
((SUM(a.Bytes) / (1024 * 1024)) / (MAX(b.VALUE) / (1024 * 1024))) * 100 Avail_Pool_Pct
FROM V$sgastat a, V$parameter b
WHERE (a.Pool = 'shared pool' AND a.NAME NOT IN ('free memory'))
AND b.NAME = 'shared_pool_size';
11、查看用户使用内存情况
SELECT Username, SUM(Sharable_Mem), SUM(Persistent_Mem), SUM(Runtime_Mem)
FROM Sys.v_$sqlarea a, Dba_Users b
WHERE a.Parsing_User_Id = b.User_Id
GROUP BY Username;
12、查看对象的缓存情况
SELECT Owner, Namespace, TYPE, NAME, Sharable_Mem, Loads, Executions, Locks,
Pins, Kept
FROM V$db_Object_Cache
WHERE TYPE NOT IN
('NOT LOADED', 'NON-EXISTENT', 'VIEW', 'TABLE', 'SEQUENCE')
AND Executions > 0
AND Loads > 1
AND Kept = 'NO'
ORDER BY Owner, Namespace, TYPE, Executions DESC;
SELECT TYPE, COUNT(*)
FROM V$db_Object_Cache
GROUP BY TYPE;
13、查看库缓存命中率
SELECT Namespace, Gets, Gethitratio * 100 Gethitratio, Pins,
Pinhitratio * 100 Pinhitratio, Reloads, Invalidations
FROM V$librarycache
14、查看某些用户的hash
SELECT a.Username, COUNT(b.Hash_Value) Total_Hash,
COUNT(b.Hash_Value) - COUNT(UNIQUE(b.Hash_Value)) Same_Hash,
(COUNT(UNIQUE(b.Hash_Value)) / COUNT(b.Hash_Value)) * 100 u_Hash_Ratio
FROM Dba_Users a, V$sqlarea b
WHERE a.User_Id = b.Parsing_User_Id
GROUP BY a.Username;
15、查看字典命中率
SELECT (SUM(Getmisses) / SUM(Gets)) Ratio
FROM V$rowcache;
16、查看undo段的使用情况
SELECT d.Segment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive,
d.Status
FROM V$rollname n, V$rollstat s, Dba_Rollback_Segs d
WHERE d.Segment_Id = n.Usn(+)
AND d.Segment_Id = s.Usn(+);
17、求归档日志的切换频率(生产系统可能时间会很长)
SELECT Start_Recid, Start_Time, End_Recid, End_Time, Minutes
FROM (SELECT Test.*, Rownum AS Rn
FROM (SELECT b.Recid Start_Recid,
To_Char(b.First_Time, 'yyyy-mm-dd hh24:mi:ss') Start_Time,
a.Recid End_Recid,
To_Char(a.First_Time, 'yyyy-mm-dd hh24:mi:ss') End_Time,
Round(((a.First_Time - b.First_Time) * 24) * 60, 2) Minutes
FROM V$log_History a, V$log_History b
WHERE a.Recid = b.Recid + 1
AND b.First_Time > SYSDATE - 1
ORDER BY a.First_Time DESC) Test) y
WHERE y.Rn < 30
18、求回滚段正在处理的事务
SELECT a.NAME, b.Xacts, c.Sid, c.Serial#, d.Sql_Text
FROM V$rollname a, V$rollstat b, V$session c, V$sqltext d, V$transaction e
WHERE a.Usn = b.Usn
AND b.Usn = e.Xidusn
AND c.Taddr = e.Addr
AND c.Sql_Address = d.Address
AND c.Sql_Hash_Value = d.Hash_Value
ORDER BY a.NAME, c.Sid, d.Piece;
19、求某个事务的重做信息(bytes)
SELECT s.NAME, m.VALUE
FROM V$mystat m, V$statname s
WHERE m.Statistic# = s.Statistic#
AND s.NAME LIKE '%redo size%';
20、求cache中缓存超过其5%的对象
SELECT o.Owner, o.Object_Type, o.Object_Name, COUNT(b.Objd)
FROM V$bh b, Dba_Objects o
WHERE b.Objd = o.Object_Id
GROUP BY o.Owner, o.Object_Type, o.Object_Name
HAVING COUNT(b.Objd) > (SELECT To_Number(VALUE) * 0.05
FROM V$parameter
WHERE NAME = 'db_block_buffers');
21、求buffer cache中的块信息
SELECT o.Object_Type, Substr(o.Object_Name, 1, 10) Objname, b.Objd, b.Status,
COUNT(b.Objd)
FROM V$bh b, Dba_Objects o
WHERE b.Objd = o.Data_Object_Id
AND o.Owner = '&owner'
GROUP BY o.Object_Type, o.Object_Name, b.Objd, b.Status;
22、求日志文件的空间使用
SELECT Le.Leseq Current_Log_Sequence#,
100 * Cp.Cpodr_Bno / Le.Lesiz Percentage_Full
FROM X$kcccp Cp, X$kccle Le
WHERE Le.Leseq = Cp.Cpodr_Seq;
23、求等待中的对象
SELECT /*+rule */
s.Sid, s.Username, w.Event, o.Owner, o.Segment_Name, o.Segment_Type,
o.Partition_Name, w.Seconds_In_Wait Seconds, w.State
FROM V$session_Wait w, V$session s, Dba_Extents o
WHERE w.Event IN (SELECT NAME
FROM V$event_Name
WHERE Parameter1 = 'file#'
AND Parameter2 = 'block#'
AND NAME NOT LIKE 'control%')
AND o.Owner <> 'sys'
AND w.Sid = s.Sid
AND w.P1 = o.File_Id
AND w.P2 >= o.Block_Id
AND w.P2 < o.Block_Id + o.Blocks
24、求当前事务的重做尺寸
SELECT V$statname.NAME,VALUE
FROM V$mystat, V$statname
WHERE V$mystat.Statistic# = V$statname.Statistic#
AND V$statname.NAME = 'redo size';
25、唤醒smon去清除临时段
column pid new_value Smon
set termout off
SELECT p.Pid
FROM Sys.v_$bgprocess b, Sys.v_$process p
WHERE b.NAME = 'SMON'
AND p.Addr = b.Paddr;
/
SET Termout ON Oradebug Wakeup &Smon Undefine Smon
26、求回退率
SELECT b.VALUE / (a.VALUE + b.VALUE), a.VALUE, b.VALUE
FROM V$sysstat a, V$sysstat b
WHERE a.Statistic# = 4
AND b.Statistic# = 5;
27、求free memory
SELECT *
FROM V$sgastat
WHERE NAME = 'free memory';
SELECT a.NAME, SUM(b.VALUE)
FROM V$statname a, V$sesstat b
WHERE a.Statistic# = b.Statistic#
GROUP BY a.NAME;
查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，
找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行
就看看能否kill它，等等, 查看当前正在使用的回滚段的用户信息和回滚段信息:
set linesize 121
SELECT r.NAME "ROLLBACK SEGMENT NAME ", l.Sid "ORACLE PID",
p.Spid "SYSTEM PID ", s.Username "ORACLE USERNAME"
FROM V$lock l, V$process p, V$rollname r, V$session s
WHERE l.Sid = p.Pid(+)
AND s.Sid = l.Sid
AND Trunc(l.Id1(+) / 65536) = r.Usn
AND l.TYPE(+) = 'TX'
AND l.Lmode(+) = 6
ORDER BY r.NAME;
28、查看用户的回滚段的信息
SELECT s.Username, Rn.NAME
FROM V$session s, V$transaction t, V$rollstat r, V$rollname Rn
WHERE s.Saddr = t.Ses_Addr
AND t.Xidusn = r.Usn
AND r.Usn = Rn.Usn
29、查看内存中存的使用
SELECT Decode(Greatest(CLASS, 10),
10,
Decode(CLASS, 1, 'Data', 2, 'Sort', 4, 'Header', To_Char(CLASS)), 'Rollback') "Class",
SUM(Decode(Bitand(Flag, 1), 1, 0, 1)) "Not Dirty",
SUM(Decode(Bitand(Flag, 1), 1, 1, 0)) "Dirty",
SUM(Dirty_Queue) "On Dirty", COUNT(*) "Total"
FROM X$bh
GROUP BY Decode(Greatest(CLASS, 10),
10,
Decode(CLASS, 1, 'Data', 2, 'Sort', 4, 'Header', To_Char(CLASS)), 'Rollback');
sql语句一些实用技巧for oracle
2008-08-28 17:45
1）在select语句中使用条件逻辑
1select ename,sal,
2 case when sal <= 2000 then 'UNDERPAID'
3 when sal >= 4000 then 'OVERPAID'
4 else 'OK'
5 end as status
6from emp
ENAME SAL STATUS
---------- ---------- ---------
SMITH 800 UNDERPAID
ALLEN 1600 UNDERPAID
WARD 1250 UNDERPAID
JONES 2975 OK
MARTIN 1250 UNDERPAID
BLAKE 2850 OK
CLARK 2450 OK
SCOTT 3000 OK
KING 5000 OVERPAID
TURNER 1500 UNDERPAID
ADAMS 1100 UNDERPAID
JAMES 950 UNDERPAID
FORD 3000 OK
MILLER 1300 UNDERPAID
2）从表中随机返回n条记录
1select *
2 from (
3 select ename, job
4 from emp
5 order by dbms_random.value()
6 )
7 where rownum <= 5
3）按照子串排序
比如要从EMP表中返回员工名字和职位，并且按照职位字段最后2个字符排序
1select ename,job
2 from emp
3order by substr(job,length(job)-2)
ENAME JOB
---------- ---------
KING PRESIDENT
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
JONES MANAGER
CLARK MANAGER
BLAKE MANAGER
ALLEN SALESMAN
MARTIN SALESMAN
WARD SALESMAN
TURNER SALESMAN
SCOTT ANALYST
FORD ANALYST
4）处理空值排序
当被排序的列存在空值，如果希望空值不影响现有排序
1select ename,sal,comm
2 from emp
3order by comm nulls last
ENAME SAL COMM
------ ----- ---------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
1select ename,sal,comm
2 from emp
3order by comm desc nulls first
ENAME SAL COMM
------ ----- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
5）根据数据项的键排序
比如如果job是“SALESMAN”，根据COMM排序，否则根据SAL排序
1select ename,sal,job,comm
2 from emp
3 order by case when job = 'SALESMAN' then comm else sal end
ENAME SAL JOB COMM
---------- ---------- --------- ----------
TURNER 1500 SALESMAN 0
ALLEN 1600 SALESMAN 300
WARD 1250 SALESMAN 500
SMITH 800 CLERK
JAMES 950 CLERK
ADAMS 1100 CLERK
MARTIN 1250 SALESMAN 1300
MILLER 1300 CLERK
CLARK 2450 MANAGER
BLAKE 2850 MANAGER
JONES 2975 MANAGER
SCOTT 3000 ANALYST
FORD 3000 ANALYST
6）从一个表中查找另一个表中没有的值
比如要从DEPT中查找在表EMP中不存在数据的所有部门（数据中，DEPTNO值为40的记录在表EMP中不存在）
1select deptno from dept
2minus
3select deptno from emp
7）在运算和比较时使用null值
null不会等于和不等于任何值，null和自己都不等于。以下例子是当comm有null的情况下列出比“WARD”提成低的员工。 （coalesce函数将null转换为其他值）
1select ename,comm,coalesce(comm,0)
2 from emp
3where coalesce(comm,0) < ( select comm
4 from emp
5 where ename = 'WARD' )
ENAME COMM COALESCE(COMM,0)
---------- ---------- ----------------
SMITH 0
ALLEN 300 300
JONES 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0 0
ADAMS 0
JAMES 0
FORD 0
MILLER 0
8）删除重复记录
对于名字重复的记录，保留一个
1delete from dupes
2 where id not in ( select min(id)
3 from dupes
4 group by name )
9）合并记录
比如如下需求：
如果表EMP_COMMISSION中的某员工也存在于EMP表，那么更新comm为1000
如果以上员工已经更新到1000的员工，如果他们SAL少于2000，删除他们
否则，从表中提取该员工插入表EMP_COMMISSION
1merge into emp_commission ec
2using (select * from emp) emp
3 on (ec.empno=emp.empno)
4 when matched then
5 update set ec.comm = 1000
6 delete where (sal < 2000)
7 when not matched then
8 insert (ec.empno,ec.ename,ec.deptno,ec.comm)
9 values (emp.empno,emp.ename,emp.deptno,emp.comm)
10）用sql生成sql
1select 'select count(*) from '||table_name||';' cnts
2 from user_tables;
（user_tables是oracle的元数据表之一）
CNTS
----------------------------------------
select count(*) from ANT;
select count(*) from BONUS;
select count(*) from DEMO1;
select count(*) from DEMO2;
select count(*) from DEPT;
select count(*) from DUMMY;
select count(*) from EMP;
select count(*) from EMP_SALES;
select count(*) from EMP_SCORE;
select count(*) from PROFESSOR;
select count(*) from T;
select count(*) from T1;
select count(*) from T2;
select count(*) from T3;
select count(*) from TEACH;
select count(*) from TEST;
select count(*) from TRX_LOG;
select count(*) from X;
11）计算字符在字符串里的出现次数
判断字符串里有多少个‘ , ’
1select (length('10,CLARK,MANAGER')-
2 length(replace('10,CLARK,MANAGER',',','')))/length(',')
3 as cnt
4from t1
先计算原字符串长度，再减去去掉逗号的长度，这个差再除以‘，’的长度
12）将数字和字母分离
原数据是：
DATA
---------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
1select replace(
2 translate(data,'0123456789','0000000000'),'0') ename,
3 to_number(
4 replace(
5 translate(lower(data),
6 'abcdefghijklmnopqrstuvwxyz',
7 rpad('z',26,'z')),'z')) sal
8 from (
9 select ename||sal data from emp
10 )
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
思路是很复杂的，比如先去除数字，是先把所有数字翻译为0，然后用replace去掉0.
13）根据表中的行创建分割列表
表中数据：
DEPTNO EMPS
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD
1select deptno,
2 ltrim(sys_connect_by_path(ename,','),',') emps
3 from (
4 select deptno,
5 ename,
6 row_number() over
7 (partition by deptno order by empno) rn,
8 count(*) over
9 (partition by deptno) cnt
10 from emp
11 )
12 where level = cnt
13 start with rn = 1
14 connect by prior deptno = deptno and prior rn = rn-1
查询结果
DEPTNO EMPS
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
14）按字母顺序排序
1select old_name, new_name
2 from (select old_name, replace(sys_connect_by_path(c, ' '), ' ') new_name
3 from (select e.ename old_name,
4 row_number() over(partition by e.ename order by substr(e.ename, iter.pos, 1)) rn,
5 substr(e.ename, iter.pos, 1) c
6 from emp e, (select rownum pos from emp) iter
7 where iter.pos <= length(e.ename)
8 order by 1) x
9 start with rn = 1
10 connect by prior rn = rn - 1
11 and prior old_name = old_name)
12 where length(old_name) = length(new_name)
You would like the result to be:
OLD_NAME NEW_NAME
---------- --------
ADAMS AADMS
ALLEN AELLN
BLAKE ABEKL
CLARK ACKLR
FORD DFOR
JAMES AEJMS
JONES EJNOS
KING GIKN
MARTIN AIMNRT
MILLER EILLMR
SCOTT COSTT
SMITH HIMST
TURNER ENRRTU
WARD ADRW
注意事项：
1. 1. DB.TBUXLUNCHBOX这个表格中使用的字段DESC为SQL的关键词，会导致SQL用到该字段时会报错，遇到此种情况要将该字段使用双引号(“)括起，例如：uxjcLucnhBoxDAO，中对于该table进行数据新增第156行程序写法为
StringBuffer sqlStr = new StringBuffer();
sqlStr.append("INSERT INTO db.tbuxLunchBox");
sqlStr.append(" (lunchId,vendorId,vendor,lunchBox,price,desc,pic,updateUser,updateTime)");
上一行的desc应该改为”/DESC/”，并将uxjcLunchBoxDAO中所有使用到该字段的SQL语句进行如上修改。
