000000E4.0000.0002 1 200001 1000 000000E4.0001.0002 2 200002 1100 000000E4.0002.0002 3 200003 1200 000000E4.0003.0002 4 200004 1300 000000E4.0004.0002 5 200005 1400 000000E4.0005.0002 6 200006 1500 000000E4.0006.0002 7 200007 1600 000000E4.0007.0002 8 200101 1100 000000E4.0008.0002 9 200202 1200 000000E4.0009.0002 10 200301 1300 000000E4.000A.0002 11 200008 1000
查询到11记录.
正确用法，使用子查询
SQL> select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;
ROWNUM MONTH SELL
--------- ------ --------- 1 200001 1000 2 200002 1100 3 200003 1200 4 200004 1300 5 200005 1400 6 200006 1500 7 200007 1600 8 200008 1000 9 200101 1100 10 200202 1200 11 200301 1300
按销售金额排序，并且用rownum标出正确序号（有小到大）
SQL> select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;
ROWNUM MONTH SELL
--------- ------ --------- 1 200001 1000 2 200008 1000 3 200002 1100 4 200101 1100 5 200003 1200 6 200202 1200
7 200004 1300 8 200301 1300 9 200005 1400 10 200006 1500 11 200007 1600
查询到11记录.
利用以上方法，如在打印报表时，想在查出的数据中自动加上行号，就可以利用rownum。
返回第5—9条纪录，按月份排序
SQL> select * from (select rownum row_id ,month,sell
2 from (select month,sell from sale group by month,sell)) 3 where row_id between 5 and 9;
ROW_ID MONTH SELL
---------- ------ ---------- 5 200005 1400 6 200006 1500 7 200007 1600 8 200008 1000 9 200101 1100
(1)
查所及杀锁
select l.session_id sid, l.locked_mode lockmode, l.oracle_username db_user, l.os_user_name os_user, s.machine, s.schemaname,
o.object_name tablename, q.sql_text
from v$locked_object l, v$session s, v$sql q, all_objects o where l.session_id=s.sid and s.type='USER' and
s.sql_address=q.address and l.object_id=o.object_id
alter system kill session 'sid,SERIAL#'
1.having 子句的用法
having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列.
2.外部联接\的用法
外部联接\按其在\的左边或右边分左联接和右联接.若不带\运算符的表中的一个行不直接匹配于带\预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接\可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢
select a.empno from emp a where a.empno not in
(select empno from emp1 where job=’SALE’);
倘若利用外部联接,改写命令如下:
select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job=’SALE’;
可以发现,运行速度明显提高.
3.删除表内重复记录的方法
可以利用这样的命令来删除表内重复记录:
delete from table_name a
where rowid< (select max(rowid) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);
问：用ORACLE的like（匹配操作命令）操作时，要查的条件含有特殊符号（_或%），该怎样写？
如我要找出以tt_开头的表，若安以下写法只能取出以tt开头的表，
因为_在like中用意是任意单一字符。 SELECT Tname FROM tab WHERE Tname like 'tt_％'
答：SELECT * FROM tab
WHERE tname LIKE 'TT/_%' ESCAPE '/'
自增字段：
ORACLE一般的做法是同时使用序列和触发器来生成一个自增字段. CREATE SEQUENCE SEQname INCREMENT BY 1 START WITH 1
MAXVALUE 99999999 /
CREATE TRIGGER TRGname
BEFORE INSERT ON table_name REFERENCING NEW AS :NEW FOR EACH ROW Begin
SELECT SEQname.NEXTVAL INTO :NEW.FIELDname FROM DUAL; End; /
动态sql:
在oracle8.1.5中：
用execute immediate来实现 declare
tsql varchar2(200); begin
tsql:='insert into '||tname||'values ('aaa','bbb')'; execute immediate tsql; end; /
说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 1<>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 说明：外连接查询(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息 SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 说明：-- SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 说明：-- SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='\and 专业名称='\性别,生源地,高考总成绩 说明：
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 说明：四表联查问题：
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 .....
说明：得到表中最小的未使用的ID号 SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)