本人初学
oracle，，第一次发帖， 以前听老师说“学编程要经常做笔记， 而写博客是做笔记最好的方法” 听的还挺有道理的就写了个贴，以后继续发发发！！哈！！！
1.------------------查询数据库表信息：
select table_name,tablespace_name,temporary from user_tables where table_name='表名';
2.-----------------查询数据库表中列信息
select column_name,data_type ,data_length,data_precision,data_scale from user_tab_columns where table_name='表名';
3 .----------------查询某个字段在哪张表中，并且显示其主键信息
SELECT * FROM user_cons_columns ucc where ucc.constraint_name in(
SELECT uc.constraint_name FROM user_constraints uc where uc.table_name IN
(SELECT utc.TABLE_NAME FROM user_tab_columns utc where utc.COLUMN_NAME='PK_PSNDOC' ) AND uc.constraint_type='P'
) order by table_name
4.---------------- 如果sex字段为男则显示女
select (case when sex='男' then '女' else '男' end) from 表名
5.----------------取ID 相同，DATE数据偏大的
select * from (
select id,date,row_number()over(partition by id order by date desc) mm from 表名
)where mm=1 order by id
6.---------------取唯一
select distinct(列名) from 表名
7.--------------查看前10行数据
select * from xxx where rownum<10
8.--------------截取小数点后几位数
TRUNC（89.985，2）=89.98
Round (89.985, 2) =89.99 -- 四舍五入
9.-------------if(num==1)return a if(num==2) return b，逻辑控制
select decode(num,1,'a',2,'b') from dual
10.------------查询 虚拟表
select * from dual
11------------------pl/sql 特殊COPY
在SQL Window里写好的SQL语句通常需要放到Java或者别的语言内，就需要转成字符串并上加上相应的连字符，这一个事不需要再重复做了，在写好的SQL上点右键--> Special Copy
12----------------pl/sql 快捷键输入SQL，例如输入SF空格自动替换为 SELECT * FROM 非常方便，节省了大量的时间去编写重复的SQL语句
设置方法：菜单Tools --> Preferences --> Editor --> AutoReplace. --> Edit
下面定义了一些规则作为参考
s=SELECT
f=FROM
w=WHERE
o=ORDER BY
d=DELETE
sf=SELECT * FROM
df=DELETE FROM
sc=SELECT COUNT(*) FROM
13.------------pl/sql 关键字大写小写 Tools->Preferences->Editor，将Keyword case选择Uppercase，init Caps。
14-------------------oracle 建表语句，插入多行数据,xxx为表名
create table xxx(
id number(9) not null primary key,
name varchar2(40) not null,
code varchar2(40) not null
)
insert all
into xxx(id,name,code) values ( 001,'小1','1')
into xxx(id,name,code) values ( 002,'小2','2' )
into xxx(id,name,code) values ( 003,'小3','1.1')
into xxx(id,name,code) values ( 004,'小4','1.2')
15.--------------修改表字段名
alter table [表名] rename column 旧的字段名 to 新的字段名;
16.--------------表增加多个字段，DR设置默认值0，TS设置默认值指定格式当前时间
alter table aa add (DR NUMBER(10) default 0,TS CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
17.--------------连接两张表数据函数 union Minus
18.效率问题
select sum(a.amount) from account a,card b where a.card_no = b.card_no （20秒）
select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no （< 1秒）
19.----------- oracle 添加服务，D:\app\cl\product\11.1.0\db_1\NETWORK\ADMIN\tnsnames.ora 文件
20.----------- 查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的哪些个数据
select * from test start with day_number=1 connect by prior day_number=day_number-1 and prior msisdn= msisdn
维护树挺好的，省去了不少代码，根目录为水果类的，全查出来
SELECT * FROM bd_defdoc where 1=1
start with pk_defdoc1='水果类'
connect by prior pk_defdoc1=pk_defdoc -------- pk_defdoc1=pk_defdoc 这两个字段前后是有区别的
21-------------WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;
22--------------对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一列。
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
23.---------------正则表达式 regexp_like(value,'')
24.--------------SELECT * FROM xxx where ltrim(xxx.code,'0123456789.') is null 查询出code是数字的数据
25.-------------SELECT * FROM 表名 where length(name)<>length(ltrim(name,'&%^')) or length(name)<>length(rtrim(name,'&%^')) -- name中包含&%^的数据都查出来
26.------------- substr(起始位置,截取长度)函数
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', -3, 3) would return 'Net'
27.-------------Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小,怎么解决???????
查一下sga的大小 select * from v$parameters where name like 'db%';
更改db_cache_size大小 alter system set db_cache_size=25165824 scope=both;
28.------------oracle 批量执行SQL文件，pl/sql打开command window
@D:\workspace\sy\sphbd\doc\sqls\updateDB.sql;
updateDB.sql文件内容：
@./func.sql;
@./tables.sql; 类似，也可单个执行
1.------------------查询数据库表信息：
select table_name,tablespace_name,temporary from user_tables where table_name='表名';
2.-----------------查询数据库表中列信息
select column_name,data_type ,data_length,data_precision,data_scale from user_tab_columns where table_name='表名';
3 .----------------查询某个字段在哪张表中，并且显示其主键信息
SELECT * FROM user_cons_columns ucc where ucc.constraint_name in(
SELECT uc.constraint_name FROM user_constraints uc where uc.table_name IN
(SELECT utc.TABLE_NAME FROM user_tab_columns utc where utc.COLUMN_NAME='PK_PSNDOC' ) AND uc.constraint_type='P'
) order by table_name
4.---------------- 如果sex字段为男则显示女
select (case when sex='男' then '女' else '男' end) from 表名
5.----------------取ID 相同，DATE数据偏大的
select * from (
select id,date,row_number()over(partition by id order by date desc) mm from 表名
)where mm=1 order by id
6.---------------取唯一
select distinct(列名) from 表名
7.--------------查看前10行数据
select * from xxx where rownum<10
8.--------------截取小数点后几位数
TRUNC（89.985，2）=89.98
Round (89.985, 2) =89.99 -- 四舍五入
9.-------------if(num==1)return a if(num==2) return b，逻辑控制
select decode(num,1,'a',2,'b') from dual
10.------------查询 虚拟表
select * from dual
11------------------pl/sql 特殊COPY
在SQL Window里写好的SQL语句通常需要放到Java或者别的语言内，就需要转成字符串并上加上相应的连字符，这一个事不需要再重复做了，在写好的SQL上点右键--> Special Copy
12----------------pl/sql 快捷键输入SQL，例如输入SF空格自动替换为 SELECT * FROM 非常方便，节省了大量的时间去编写重复的SQL语句
设置方法：菜单Tools --> Preferences --> Editor --> AutoReplace. --> Edit
下面定义了一些规则作为参考
s=SELECT
f=FROM
w=WHERE
o=ORDER BY
d=DELETE
sf=SELECT * FROM
df=DELETE FROM
sc=SELECT COUNT(*) FROM
13.------------pl/sql 关键字大写小写 Tools->Preferences->Editor，将Keyword case选择Uppercase，init Caps。
14-------------------oracle 建表语句，插入多行数据,xxx为表名
create table xxx(
id number(9) not null primary key,
name varchar2(40) not null,
code varchar2(40) not null
)
insert all
into xxx(id,name,code) values ( 001,'小1','1')
into xxx(id,name,code) values ( 002,'小2','2' )
into xxx(id,name,code) values ( 003,'小3','1.1')
into xxx(id,name,code) values ( 004,'小4','1.2')
15.--------------修改表字段名
alter table [表名] rename column 旧的字段名 to 新的字段名;
16.--------------表增加多个字段，DR设置默认值0，TS设置默认值指定格式当前时间
alter table aa add (DR NUMBER(10) default 0,TS CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
17.--------------连接两张表数据函数 union Minus
18.效率问题
select sum(a.amount) from account a,card b where a.card_no = b.card_no （20秒）
select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no （< 1秒）
19.----------- oracle 添加服务，D:\app\cl\product\11.1.0\db_1\NETWORK\ADMIN\tnsnames.ora 文件
20.----------- 查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的哪些个数据
select * from test start with day_number=1 connect by prior day_number=day_number-1 and prior msisdn= msisdn
维护树挺好的，省去了不少代码，根目录为水果类的，全查出来
SELECT * FROM bd_defdoc where 1=1
start with pk_defdoc1='水果类'
connect by prior pk_defdoc1=pk_defdoc -------- pk_defdoc1=pk_defdoc 这两个字段前后是有区别的
21-------------WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;
22--------------对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一列。
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
23.---------------正则表达式 regexp_like(value,'')
24.--------------SELECT * FROM xxx where ltrim(xxx.code,'0123456789.') is null 查询出code是数字的数据
25.-------------SELECT * FROM 表名 where length(name)<>length(ltrim(name,'&%^')) or length(name)<>length(rtrim(name,'&%^')) -- name中包含&%^的数据都查出来
26.------------- substr(起始位置,截取长度)函数
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', -3, 3) would return 'Net'
27.-------------Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小,怎么解决???????
查一下sga的大小 select * from v$parameters where name like 'db%';
更改db_cache_size大小 alter system set db_cache_size=25165824 scope=both;
28.------------oracle 批量执行SQL文件，pl/sql打开command window
@D:\workspace\sy\sphbd\doc\sqls\updateDB.sql;
updateDB.sql文件内容：
@./func.sql;
@./tables.sql; 类似，也可单个执行
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26840734/viewspace-732532/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26840734/viewspace-732532/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
