sql语句分类
select查询语句
dml语句(数据操作语言)
----->insert ,update,delete,merge
ddl语句(数据定义语言)
----->create,alter,drop,truncate
dcl语句(数据控制语言)
grant,revoke
事务控制语句
--->commit,rollback,savepoint
2,
select empno || ' ' || ename || ' is a ' || job || ' his annaul sal is '|| sal*12
select查询语句
dml语句(数据操作语言)
----->insert ,update,delete,merge
ddl语句(数据定义语言)
----->create,alter,drop,truncate
dcl语句(数据控制语言)
grant,revoke
事务控制语句
--->commit,rollback,savepoint
2,
select empno || ' ' || ename || ' is a ' || job || ' his annaul sal is '|| sal*12
from emp;
select empno 编号,ename 姓名,job as 工作,sal*12 工资 from emp;insert into student
select empno 编号,ename 姓名,job as 工作,sal*12 工资 from emp;insert into student
(name) values('tom');
select distinct deptno from emp;
select * from emp where hiredate='02-4月-81';
select * from emp where ename in('SMITH','SCOTT');//大小写敏感
select * from emp where sal between 2450 and 3000 order by sal;
insert into student values('a_b',22);
insert into student values('a%b',22);
insert into student values('abc_ef',22);
select * from student where
name like '%/_%' escape '/'; //%代表多个字符,_代表一个 escape '/'说明/是转义字符,
/_代表_
select * from student where name like '%/%%' escape '/';
select * from student where name like '___%_%' escape '/';
insert into student values('tom''cd',22); //tom'cd 单引号用''表示
select * from student where name like '%''%';
select * from emp where job='salesman' or job='clerk' and sal>2000
select * from emp where (job='salesman' or job='clerk') and sal>2000
select * from v$nls_parameters;//显示配置信息
a,------------------------------------------------------------------------
字符函数
select initcap(ename) from emp;//首字母大写
select lower(ename) from emp;
select upper(ename) from emp;
select concat(concat(ename,' is a '),job) from emp;
select substr(ename,3,2) from emp;
select ename,length(ename) from emp;
select ename,instr(ename,'N') as idx from emp;//找ename中N出现的位置,'N'区分大小
写
select ename,lpad(ename,10,'-') as idx from emp;
select trim(' Mr Brown ') from dual;
select ename,replace(ename,'A','+') from emp;
select distinct deptno from emp;
select * from emp where hiredate='02-4月-81';
select * from emp where ename in('SMITH','SCOTT');//大小写敏感
select * from emp where sal between 2450 and 3000 order by sal;
insert into student values('a_b',22);
insert into student values('a%b',22);
insert into student values('abc_ef',22);
select * from student where
name like '%/_%' escape '/'; //%代表多个字符,_代表一个 escape '/'说明/是转义字符,
/_代表_
select * from student where name like '%/%%' escape '/';
select * from student where name like '___%_%' escape '/';
insert into student values('tom''cd',22); //tom'cd 单引号用''表示
select * from student where name like '%''%';
select * from emp where job='salesman' or job='clerk' and sal>2000
select * from emp where (job='salesman' or job='clerk') and sal>2000
select * from v$nls_parameters;//显示配置信息
a,------------------------------------------------------------------------
字符函数
select initcap(ename) from emp;//首字母大写
select lower(ename) from emp;
select upper(ename) from emp;
select concat(concat(ename,' is a '),job) from emp;
select substr(ename,3,2) from emp;
select ename,length(ename) from emp;
select ename,instr(ename,'N') as idx from emp;//找ename中N出现的位置,'N'区分大小
写
select ename,lpad(ename,10,'-') as idx from emp;
select trim(' Mr Brown ') from dual;
select ename,replace(ename,'A','+') from emp;
b,---------------------------------------------------------------------------
数值函数
select trunc(314.1592,3) from dual;//截断
select abs(-1) from dual; select round(3.1516,3);
select ceil(3.14) from dual;//向上取整
select floor(3.14) from dual;//向下取整
select sign(-3.14) from dual;//-1
select sin(3.14) from dual; select power(4.5,2) from dual;
select sqrt(9) from dual; select mod(10,3) from dual;
exp(),log(4,16),ln(7)
c,---------------------------------------------------------------------------
日期函数
select add_months(sysdate,2) from dual;//当前日期加两个月
select last_day(sysdate)//返回当月最后一天的日期
select months_between(sysdate,hiredate) from emp;
select round(to_date('16-3月-08'),'month') from dual; //返回4月1日
select round(to_date('16-7月-08'),'year') from dual; //09-1-1
select trunc(to_date('20-12月-08'),'year') from dual//08-1-1
select next_day(sysdate,'星期二') from dual;//返回sysdate之后的第一个星期二
insert into test1 values('tom',to_date('2007-02-25','yyyy-mm-dd'));
select empno,ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-ddd hh24:mi:ss') from dual;//ddd该年的第几天
select to_char(sysdate,'yyyy-mm-d day hh24:mi:ss') from dual;/d该星期的第几天,周日
数值函数
select trunc(314.1592,3) from dual;//截断
select abs(-1) from dual; select round(3.1516,3);
select ceil(3.14) from dual;//向上取整
select floor(3.14) from dual;//向下取整
select sign(-3.14) from dual;//-1
select sin(3.14) from dual; select power(4.5,2) from dual;
select sqrt(9) from dual; select mod(10,3) from dual;
exp(),log(4,16),ln(7)
c,---------------------------------------------------------------------------
日期函数
select add_months(sysdate,2) from dual;//当前日期加两个月
select last_day(sysdate)//返回当月最后一天的日期
select months_between(sysdate,hiredate) from emp;
select round(to_date('16-3月-08'),'month') from dual; //返回4月1日
select round(to_date('16-7月-08'),'year') from dual; //09-1-1
select trunc(to_date('20-12月-08'),'year') from dual//08-1-1
select next_day(sysdate,'星期二') from dual;//返回sysdate之后的第一个星期二
insert into test1 values('tom',to_date('2007-02-25','yyyy-mm-dd'));
select empno,ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-ddd hh24:mi:ss') from dual;//ddd该年的第几天
select to_char(sysdate,'yyyy-mm-d day hh24:mi:ss') from dual;/d该星期的第几天,周日
为1。day表示星期几
select to_char(sysdate,'yyyy-mm-dd day hh:mi:ss am') from dual;/am或pm
select to_char(sysdate,'fmyyyy"年"mm"月"dd"日" day hh:mi:ss am') from dual;//""原样
select to_char(sysdate,'yyyy-mm-dd day hh:mi:ss am') from dual;/am或pm
select to_char(sysdate,'fmyyyy"年"mm"月"dd"日" day hh:mi:ss am') from dual;//""原样
显示,fm是去掉0
select to_date('2008-02-28 3:44:23 下午','yyyy-mm-dd hh:mi:ss am') from dual;
select to_date('2008-02-28 3:44:23 下午','yyyy-mm-dd hh:mi:ss am') from dual;
select to_char(to_date('2008-02-28 3:44:23 下午','yyyy-mm-dd hh:mi:ss
am'),'dd/mm/yy day') from dual;
-----------------------------------------------------------------------------
数字转换为字符串
select ename,sal,to_char(sal,'L99,999.9999') from emp;//L99,999.9999 9代表一位数字
-----------------------------------------------------------------------------
数字转换为字符串
select ename,sal,to_char(sal,'L99,999.9999') from emp;//L99,999.9999 9代表一位数字
,0代表一位数字强制显示为0. $放置一个美元符号,L放置一个本地货币符号,
select to_number('121545487') from dual;
select to_number('$12,345.6789','$99,999.0000') from dual;
select to_number('121545487') from dual;
select to_number('$12,345.6789','$99,999.0000') from dual;
3,
sqlplus输出显示调整
sqlplus输出显示调整
Oracle 2009-10-29 10:51 阅读11 评论0 字号: 大大 中中 小小 sql>set
linesize 32767
将linesize设到最大就行,可以先试一下99999,会提示超出了范围
sql>set heading off
其实显示的效果还是不能让人满意,没有MySQL那样自动的表格化输出,有待改进。
将linesize设到最大就行,可以先试一下99999,会提示超出了范围
sql>set heading off
其实显示的效果还是不能让人满意,没有MySQL那样自动的表格化输出,有待改进。
set echo on/off 是否显示脚本中的需要执行的命令
set feedback on/off 是否显示 select 结果之后返回多少行的提示
set linesize n 设置一行最多显示多少字符
set termout on/off 在执行脚本时是否在屏幕上输出结果,如果 spool 到
set feedback on/off 是否显示 select 结果之后返回多少行的提示
set linesize n 设置一行最多显示多少字符
set termout on/off 在执行脚本时是否在屏幕上输出结果,如果 spool 到
文件可以将其关闭
set heading on/off 是否显示查询结果的列名,如果设置为 off,将用空白
set heading on/off 是否显示查询结果的列名,如果设置为 off,将用空白
行代替,如果要去除该空白行,可以用 set pagesize 0
set pagesize n 设置每页的行数,将 n 设为 0 可以不显示所有
set pagesize n 设置每页的行数,将 n 设为 0 可以不显示所有
headings, page breaks, titles, the initial blank line, and other formatting
information
set trimspool on/off 在 spool 到文件时是否去除输出结果中行末尾的空白
set trimspool on/off 在 spool 到文件时是否去除输出结果中行末尾的空白
字符,之前的隔行可以用该参数去掉,和该参数对应的是 trimout,后者用于屏幕输出
set trimout on/off 是否去掉屏幕上输出结果行末尾的空白字符
set trimout on/off 是否去掉屏幕上输出结果行末尾的空白字符
注意linesize别设置过大,设置过大就使desc不能正常显示。
sys口令 chang_on_install
system manager
system manager