sql 练习(2)
1.用Decode函数实现横表和竖表的转换
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SELECT deptno DEPARTMENT_ID, 2 COUNT(*) "部门人数", 3 SUM(DECODE(SIGN(sal-9000),-1,0,1)) "超高收入人数(>9000)", 4 SUM(DECODE(SIGN(sal-9000),-1,0,( DECODE(SIGN(sal-12000),1,0,1) ) )) "极高收入人数([9000,12000])", 5 SUM(DECODE(SIGN(sal-6000),-1,0,( DECODE(SIGN(sal-9000),1,0,1) ) )) "高收入([6000,9000])", 6 SUM(DECODE(SIGN(sal-3000),-1,0,( DECODE(SIGN(sal-6000),1,0,1) ) )) "一般收入([3000,6000])", 7 SUM(DECODE(SIGN(sal-3000),-1,1,0)) "低收入([<3000])" 8 FROM emp 9 GROUP BY deptno;
2.sqlplus 环境变量设置:
spool a.sql 将回显内容保持到文本;
spool off 关闭回显保存设置;
col empno format a9 设置列宽度;
col empno heading '部门号' 更改列标题;
col empno justify right 设置列标题右对齐;
col sal format $99,999 设置数据显示格式
set echo on 设置执行行脚本时是否显示脚本内容;
set newpage n 设置每页间隔n行;
set wrap 关闭自动换行
set timing on 显示脚本执行时间;
l n 显示缓冲区第n行内容;
a order by sal desc 在上面命令显示第n行后再最加命令;
c/ename/job/ 在上面显示第n行后用job代替第n行的ename;
save a.sql 将缓冲区命令保存到文本
get a.sql 加载命令到当前缓冲区;
title '员工表' 为整张表添加信息;
set verify off 关闭输出绑定变量的新旧值
只用输入一次绑定变量:
select empno,ename,job,&&column_name
from emp
order by &column_name;
为绑定变量设置自定义的提示信息:
accept deptno prompt '请输入部门号:'
accept var prompt '请输入密码' hide
预定义绑定变量的值:
undefine name
sql 函数
1.MONTHS_BETWEEN(date1, date2):判断两个日期之间相差的月份
select months_between(to_date('2013.11.29','yyyy.mm.dd'),sysdate) from dual;
2.7268007019115890083632019115890083632
2.LAST_DAY(date):函数返回包含日期的月份的最后一天
select last_day(sysdate) from dual;
30-9月 -13
3.ROUND(date[,'fmt']):将日期d按照fmt指定的格式舍入,fmt为字符串
select round(sysdate,'mm') from dual;
01-9月 -13
4.TRUNC(date[, 'fmt']):返回由fmt指定的单位的日期
select trunc(sysdate,'mm') from dual;
01-9月 -13(由于截断到9月,前面的dd是自动补齐格式的)
5.NEXT_DAY(date, 'char'):找到下一个星期几
select next_day(sysdate,'星期一') from dual;
09-9月 -13
6.ADD_MONTHS(date, n):用于从一个日期值增加或减少一些月份
select add_months(sysdate,1) from dual;
06-10月-13
7.extract(date_field from [datetime_value]):找出日期或间隔值的字段值
select extract(month from sysdate) from dual;
9
8.Initcap(char)
Select initcap(‘hello’) from dual;
Hello
9.
Lower(char)
Select lower(‘FUN’) from dual;
fun
10.Ltrim(char,set)
Select ltrim( ‘xyzadams’,’xyz’) from dual;
adams
11.Translate(char, from, to)
Select translate(‘jack’,’j’ ,’bk’) from dual;
back (只会替换对应字符)
12.Replace(char, searchstring,[rep string])
Select replace(‘jack and jue’ ,’j’,’bl’) from dual;
black and blue (整体替换)
13.Instr (string,char, m, n)
Select instr (‘worldwide’,’d’) from dual;
5 (查找字符所在位置)
14.Substr (char, m, n)
Select substr(‘abcdefg’,3,2) from dual;
cd
15.Concat (expr1, expr2)
Select concat (‘Hello’,’ world’) from dual;
Hello world
16.Abs(n)
Select abs(-15) from dual;
15
17.Ceil(n)
Select ceil(44.778) from dual;
45(返回比他大的最小整数:即向上取整)
18.Cos(n)
Select cos(180) from dual;
-0.5984601
19.Floor(n)
Select floor(100.2) from dual;
100(向下取整)
20.Power(m,n)
Select power(4,2) from dual;
16
21.Mod(m,n)
Select mod(10,3) from dual;
1
22.Round(m,n)
Select round(100.256,2) from dual;
100.26 (这才是四舍五入,默认精确到整数)
23.Trunc(m,n)
Select trunc(100.256,2) from dual;
100.25
24.Sqrt(n)
Select sqrt(4) from dual;
2
25.Sign(n)
Select sign(-30) from dual;
-1
NVL(exp1,exp2) : 如果exp1为null,则NVL返回exp2;反之返回exp1
NVL2(exp1,exp2,exp3) :如果exp1不是null,则返回exp2,如果是,则返回exp3
NULLIF(exp1,exp2) :如果exp1,exp2相等则返回null,否则返回exp1