select greatest(’AA’,’AB’,’AC’) from dual;
select greatest(1,2,5) from dual;
--LEAST
--返回一组表达式中的最小值
select least(’啊’,’安’,’天’) from dual;
select least(1,5,9) from dual;
--如果所求字段为null则反回指定的值
--nvl(ziduan,value);
--如果ziduan为null,则返回value;
--TRUNCTRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,
--只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。
--TRUNC(num,num)
--返回一个字符串的第一个字符的ASCII码,他的逆函数是CHR()
select ascii('罗') from dual;
select ascii('周润发')from dual;
--函数返回十进制表示的字符
select chr(49886) from dual;
-- 函数
--DECODE(input_value(字段数据),value(匹配值),resul(想要表现的值)t[,value,result…][,default_result(默认值)]);
select decode(user_sex,0,'女',1,'男')性别,decode(user_level,1,'初级',5,'中级',9,'高级','中级')from user_info;
--c1,c2均为字符串,函数将c2连接到c1的后面,如果c1为null,将返回c2.如果c2为null,则返回c1,
--如果c1、c2都为null,则返回null,他和操作符||返回的结果相同.
select concat('罗','家')from dual;
--c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格,控制字符,标点符号限制
select INITCAP('luo,jia,you')from dual;
select INITCAP('luo jia you')from dual;
--INSTR(c1,c2,i,j)
--c1,c2均为字符串,i,j为整数。函数返回c2在c1中第j次出现的位置,搜索从c1的第i个字符开始。当没有发现需要的
--
select INSTR('luojiayou','o',5,1)from dual;
--与INSTR()函数一样,只是他返回的是字节,对于单字节INSTRB()等于INSTR()
select INSTRB('luojiayou','o',5,1)from dual;
--返回指定字符串的长度
select LENGTH('luojiayou')from dual;
select LENGTH('周润发')from dual;
--LENGTH()一样,返回指定字符串的字节长度
select LENGTHB('周润发')from dual;
--返回指定字符串的小写
select lower('LUOjiayou') from dual;
select UPPER('luoJiayou')from dual;
--LPAD(c1,i,c2)
--c1,c2均为字符串,i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复,如果i小于c1的长度,那么只返回i那么长
--
select LPAD('123',8,'0') from dual;
select LPAD('123456789',8,'0') from dual;
--to_char(x,format)
--将x转换成format格式的字符串
select to_char(sysdate,'yyyymmdd') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(123456789,'999,999,999,999') from dual;
--to_date('x',format);
--将字符串转化成format格式的日期
select to_date('20070209','yyyy-mm-dd') from dual;
--to_number('x',formate);
--to_number('x');
--RPAD(c1,i,c2)
--c1,c2均为字符串,i为整数。在c1的右侧用c2字符串补足致长度i,可多次重复,如果i小于c1的长度,那么只返回i那么长
--
select LPAD('123',8,'0') from dual;
select LPAD('123456789',8,'0') from dual;
--LTRIM(c1,c2);
--把c1中最右边的字符去掉,使其第后一个字符不在c2中,如果没有c2,那么c1就不会改变
select LTRIM('Mississippi','Mips') from dual;
select LTRIM('Mississippi','Mis') from dual;
select LTRIM('Mississippi','Mi') from dual;
select LTRIM('Mississippi','is') from dual;
--REPLACE
select REPLACE('up tow n',' ','') from dual;
select REPLACE('uptown','up','down') from dual;
--SUBSTR(c1,i,j)
-- c1为一字符串,i,j为整数,从c1的第i位开始返回长度为j的子字符串,如果j为空,则直到串的尾部
select SUBSTR('Message',2,4) from dual;
select SUBSTR('Message',2) from dual;
--SUBSTRB(c1,i,j)
--与SUBSTR大致相同,只是I,J是以字节计算
select SUBSTRB('Message',2,5) from dual;
select SUBSTRB('周润发',3,5) from dual;
--SOUNDEX(c1)
--返回与c1发音相似的词
select SOUNDEX('jia') from dual;
--TRANSLATE(c1,c2,c3)
--将c1中与c2相同的字符以c3代替
select TRANSLATE('fumble','uf','ab') test from dual;
select TRANSLATE('fumble','fu','ab') test from dual;
--trim(c1)
--删除c1字符串前后的空格
select TRIM(' l uo
--Round(n1,n2)
--返回舍入小数点右边n2位的n1的值,n2的缺省值为0,这回将小数点最接近的整数,如果n2为负数就舍入
--
select ROUND(12365,-2),ROUND(12345.54321,2) FROM dual
--------------------------------sqlplus常用命令--------------------
--将屏幕所有的输出输出到指定文件
-- spool 文件路径名;
--结束输出
--执行一个SQL脚本文件
--我们可以将多条sql语句保存在一个文本文件中,这样当要执行这个文件中的所有的sql语句时,
--用上面的任一命令即可,这类似于dos中的批处理。
--start file_name
start g:\mysql.sql;
-- 对当前的输入进行编辑
edit
ed
--重新运行上一次运行的sql语句
/
--显示一个表的结构
--清屏
clear screen;
--退出
exit;
--置当前session是否对修改的数据进行自动提交
--SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
set autocommit on;
--在用start命令执行一个sql脚本时,是否显示脚本中正在执行的SQL语句
-- SET ECHO {ON|OFF};
set echo on;
--是否显示当前sql语句查询或修改的行数
--SET FEED[BACK] {6|n|ON|OFF}
-- 默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ,则不管查询到多少行都返回。当为off 时,一律不显示查询的行数
set feedback 1;
--是否显示列标题
--当set heading off 时,在每页的上面不显示列标题,而是以空白行代替
--SET HEA[DING] {ON|OFF}
set heading on;
--设置一行可以容纳的字符数
-- 如果一行的输出内容大于设置的一行可容纳的字符数,则折行显示
--SET LIN[ESIZE] {80|n}
set linesize 100;
--设置页与页之间的分隔
-- SET NEWP[AGE] {1|n|NONE}
--当set newpage 0 时,会在每页的开头有一个小的黑方框。
--当set newpage n 时,会在页和页之间隔着n个空行。
--当set newpage none 时,会在页和页之间没有任何间隔
set newpage 1;
--设置一页有多少行数
--如果设为0,则所有的输出内容为一页并且不显示列标题
--SET PAGES[IZE] {24|n}
set pagesize 20;
--是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
--SET SERVEROUT[PUT] {ON|OFF}
set serveroutput on;
--是否在屏幕上显示输出的内容,主要用与SPOOL结合使用。
--在用spool命令将一个大表中的内容输出到一个文件中时,将内容输出在屏幕上会耗费大量的时间,
--设置set termspool off后,则输出的内容只会保存在输出文件中,不会显示在屏幕上,极大的提高了spool的速度
--SET TERM[OUT] {ON|OFF}
set termout off;
--在dos里连接oracle数据库
CONNECT user_name/passwd@l_jiayou
--在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@数据库名称
--显示当前用户
show user;
--显示当前环境变量的值:
show all;
--显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
--显示数据库的版本:
--show REL[EASE]
show release
--显示SGA的大小
show SGA
--显示初始化参数的值:
--show PARAMETERS [parameter_name]
show parameters;
--查看当前用户的缺省表空间
select username,default_tablespace from user_users
--查看当前用户的角色
select * from user_role_privs
--查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
--查看用户下所有的表
select * from user_tables
-- 查看名称包含log字符的表
select object_name,object_id from user_objects where instr(object_name,'LOG')>0;
--查看某表的创建时间
select object_name,created from user_objects where object_name=upper('&table_name');
--查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
--查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,'Y')>0;
--查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name;
--查看索引被索引的字段
select * from user_ind_columns where index_name=upper('&index_name');
--查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
-- 查看序列号,last_number是当前值
select * from user_sequences;
--查看视图的名称
--select view_name from user_views;
--查看创建视图的select语句
select view_name,text_length from user_views;
set long 2000;
select text from user_views where view_name=upper('&view_name');
--查看同义词的名称
select * from user_synonyms
--查看某表的约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name
select c.constraint_name,c.constraint_type,cc.column_name
--查看函数和过程
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
--查看函数和过程的源代码
select text from all_source where owner=user and name=upper('&plsql_name');
--查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
--查看表空间物理文件的名称及大小
--查看回滚段名称及大小
--查看控制文件
--查看日志文件
--查看表空间的使用情况
--查看数据库库对象
--查看数据库的版本
--查看数据库的创建日期和归档方式
--用系统管理员,查看当前数据库有几个用户连接:
select username,sid,serial# from v$session;
--如果要停某个连接用
--如果这命令不行,找它UNIX的进程数
select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
--说明:21是某个连接的sid数,然后用 kill 命令杀此进程号。
例子:
表test122,有两个字段t_id varchar2(20),t_name varchar2(10);
要求t_id的值为当天日期加上0001,0002的形式递加作为序列,如20070209_0001,200709_0002;
思路:查讯当天的t_id的最大值加1,然后生成序列;
insert into test122 values
(to_char(sysdate,'yyyymmdd')||'_'||(select lpad(to_number(ltrim(substr(max(t_id),length(max(t_id))-3),'0'))+1,4,0)
from test122 where substr(t_id,0,length(t_id)-5)=to_char(sysdate,'yyyymmdd')),'ok');
树形递归查询:Start with...Connect By
准备:
create table mymenu(tree_id varchar(10),tree_pid varchar(10),tree_lable varchar(50),tree_link varchar(100))
insert into mymenu values('1','0','蔬菜','')
insert into mymenu values('2','0','水果','')
insert into mymenu values('3','0','谷物','')
insert into mymenu values('4','0','肉类','')
insert into mymenu values('5','1','白菜','')
insert into mymenu values('6','1','茄子','htt://www.baidu.com')
insert into mymenu values('7','5','四月白','http://www.google.cn')
insert into mymenu values('8','5','冬白菜','htt://www.baidu.com')
insert into mymenu values('9','2','西瓜','http://www.google.cn')
insert into mymenu values('10','2','桔子','htt://www.baidu.com')
insert into mymenu values('11','3','大米','http://www.google.cn')
insert into mymenu values('12','3','大豆','htt://www.baidu.com')
insert into mymenu values('13','4','猪肉','http://www.google.cn')
insert into mymenu values('14','4','鱼','')
insert into mymenu values('15','14','昌鱼','http://www.google.cn')
insert into mymenu values('16','14','王八','htt://www.baidu.com')
从根往树末梢查询:
select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid;//查询所有
select * from mymenu start with tree_id='1' connect by prior tree_id=tree_pid; //查询指定ID
从树末梢向根查询:
select * from mymenu start with tree_pid='0' connect by prior tree_pid=tree_id
select * from mymenu start with tree_id='8' connect by prior tree_pid=tree_id
如果还有其他条件用and 加在语句后面
select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is
select * from mymenu start with tree_pid='0' connect by prior tree_id=tree_pid and tree_link is not null
oracle客户端连接的文件配置:
oracle的目录/network/ADMIN/tnsnames.ora
内容:
MIMI(客户端连接的名称) =