1.
Oracle中运用ROWNUM实现SELECT TOP N
用法,见附件文档!
http://bfc99.blog.51cto.com/265386/803718
http://www.itpub.net/thread-415-1-1.html
2.删除重复记录,保留rowid最小的记录。即如果有10条记录重复,删除9条,保留一条。
not in实现为:
delete from tableName t where t.rowid not in
(select min(a.rowid) from tableName a group by a.field1,a.field2);
not exists实现为:
delete from tableName t where not exists
(select 1 from (select min(a.rowid) rid from tableName a group by a.field1,a.field2) b where b.rid=t.rowid);
或采用创建临时表的方式:
step1:创建临时表
create table tempTableName as
select min(a.rowid) rid from tableName a group by a.field1,a.field2;
step2:删除重复数据
delete from tableName t where t.rowid not in (select b.rid from tempTableName b);
或delete from tableName a where not exists (select 1 from tempTableName b where b.rid=a.rowid);
附:not in优化:http://blog.csdn.net/anerou/article/details/5185573
3.INSERT INTO SELECT语句 与 SELECT INTO语句
INSERT INTO SELECT语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1;
INSERT INTO SELECT也可以直接插入序列值、常量值,例如:
insert into Table2(ID, username, age, field) select table2_seq.nextval, 'gerry', 25, otherField from Table1;
SELECT INTO语句形式为:select * into Table_backup FROM Table;
SELECT INTO语句从一个表中选取数据,然后把数据插入另一个表中,SELECT INTO语句常用于创建表的备份复件或者用于对记录进行存档。需要注意的是:在Oracle中select into from不可以使用-----原因很简单:select into是PL/SQL language 的赋值语句!如果使用则Oracle会抛出0RA-00905:missing keyword的异常!但是可以用create table as select代替该功能:create table new_table as select * from old_table;
http://jingyan.baidu.com/article/d5c4b52b29c326da570dc548.html
http://stackoverflow.com/questions/2250196/select-into-using-oracle
4、序列
创建序列:
create sequence TABLE_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20;
插入记录时主键使用序列值:
insert into tableName(ID, ...) values(TABLE_SEQ.nextval, ...)
5、oracle中count(*) count(1) count(字段)
oracle中:
count(*):返回表中所有行数(包括值为null的行);
count(*)、count(1)、count(2)甚至count(23890), 执行计划一样,查询结果也一样。
count(字段):返回表中除去该列值为null以外的所有行数。
由于count(字段),查询的是该字段值不为空的记录数。所以select count(field) from table;与select count(*) from table t where t.field is not null;查询的结果是一样的。
select distinct filed from table; //剔除该列重复数据
select count(distinct filed) from table; //除去重复数据与值为null的结果
6、使用for update卡住锁表的解决办法
http://www.cnblogs.com/chenwenbiao/archive/2012/06/06/2537496.html
7、alter system flush shared_pool
alter system flush shared_pool的作用:
The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.
This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
http://wenku.baidu.com/link?url=bIEx6mMvgjW24kg04rg7DvP1SPGdbc0Wt6_TOg-PREkzHIOmKULYj6a9hljPzQtqSN9pSmRnL1TX_Kt6Gg6hlQlYoKO6GNmg30nYsFQ6RcC
8、sql中case when then条件语句
select CASE sex
WHEN 1 THEN '男'
WHEN 2 THEN '女'
ELSE '保密' END
from usertable;
--或
select CASE
WHEN sex = 1 THEN '男'
WHEN sex = 2 THEN '女'
ELSE '保密' END
from usertable;
其他复杂用法去google吧
分享到:
相关推荐
DB9 DB15 DB25 DB37 DB50 直型弯型针型孔型封装大全Altium库 PCB封装库90个合集(AD库): 封装型号列表: Component Count : 90 Component Name ----------------------------------------------- DB9 DB9 公 90° ...
DB9 DB15 DB25 DB37 D型公头母头连接器3D封装库(STEP后缀)文件: Connector - d-sub.STEP DB-Female.STEP DB-Male.STEP DB15RA_F.STEP DB15RA_M.STEP DB15_F.STEP DB15_M.STEP DB25 .STEP DB25-F.STEP DB25-M.STEP...
DB9/DB15/DB25/DB37/DB50/DB62/DB78封装PDF
DB9 DB15 DB25 DB37 DB50 ...包括90个DB9~50接插件全系列封装文件,孔型、针型、直型,90度弯型,DB9/M,DB9/F,DB15/M,DB15/F,DB25/M,DB25/F,DB37/M,DB37/F,DB50/M,DB50/F等,可以直接应用到你的项目开发。
DB9 DB15 DB25 等各种DB接口封装库 ,库都是经过项目验证的,能够节省不少开发时间,与大家分享。
db2db配置详细说明文档详细说明了如何使用db2db定时同步数据库中的表
python-oracledb的源码和使用示例代码, python-oracledb 1.0,适用于Python versions 3.6 through 3.10. Oracle Database; This directory contains samples for python-oracledb. 1. The schemas and SQL ...
用于连接db2数据库的驱动,
目前较为常用的串口有9针串口(DB9)和25针串口(DB25),通信距离较近时(),可以用电缆线直接连接标准RS232端口(RS422,RS485较远),若距离较远,需附加调制解调器(MODEM)。最为简单且常用的是三线制接法,即地、...
修改大话2单机版的那个。、DB工具谁有发我个454747236@qq.com
20Hz-0dB-30s.wav 30Hz-0dB-30s.wav 40Hz-0dB-30s.wav 50Hz-0dB-30s.wav 60Hz-0dB-30s.wav 70Hz-0dB-30s.wav 80Hz-0dB-30s.wav 90Hz-0dB-30s.wav 100Hz-0dB-30s.wav 200Hz-0dB-30s.wav 300Hz-0dB-30s.wav 400Hz-0dB-...
DB9M DB9FDB9弯型直型针型孔型RS232串口接口AD集成3D封装库(原理图库+PCB封装库) 3dpcb封装列表如下: PCB Library : 串口.PcbLib Date : 2021/5/11 Time : 17:19:08 Component Count : 5 Component Name ---...
DB添加工具DB添加工具DB添加工具DB添加工具DB添加工具
OLEDB驱动程序大全 PostgreSQL-OleDB-Provider
OLEDB驱动程序大全 MySQL-OleDB-Provider
db2注册码 有用的就下载回去用用 举手之劳
很多人在遇到mysql转sqlite,mysql转access这样的数据库转换就自己手动转换,这很麻烦,DB2DB可以自行进行各种数据库及表结构之间的转换
DB324066-2021
优化的 DB 块和标准 DB 块有哪些区别 What are the differences between optimized DB block and standard DB block