oracle 下一些零散的命令和 sql 语句

以前学过一些数据库相关的知识,回想起来刚入门还是看了传智播客的视频,杨忠科老师的sql语句教程。现在已经好多年了,中间自己也不断的在用,但是一直没有提高,只是会简单增删改查,今天学的 cast..then..等等都没怎么用过,所以记录一下没有学到过的内容,以后备用。


 

// 查看所有表

select * from tab;
// 查看表字段

desc emp;
// 清屏

host cls
// 执行上一次执行过的语句

/
// 修改列宽度
col 列名 for a20(字符串)
col 列名 for 99999(数字)
// 修改错误的sql语句
跳转到指定错误行
1
修改指定数据
c /form/from
使用ed命令,弹出系统默认的编辑器
// 去重
select distinct job from emp;
// 表达式中使用可能为NULL值的处理办法
在表达式中如果使用了带有NULL的操作数,那么整个表达式的结果也是NULL,如果想避免这种情况,可以使用如下函数
nvl(comm, 0)
意思就是如果comm是NULL的情况下,那么给其返回0
// 连接两个字符串和伪表 dual
select ‘hello ‘ || ‘world’ as “hello” from dual;
select concat(‘hello ‘, ‘world’) as “hello” from dual;
// 查询1250到3000之间的数据,包含1250和3000
between 1250 and 3000
// 查询 10 或者 20 的数据
select * from emp
where deptno = 10 or deptno = 20;select * from emp
where deptno in (10, 20);select * from emp
where deptno not in (10, 20);select * from emp
where comm in (300, 500, NULL)

select * from emp
where comm not in (300, 500, NULL)     // 不允许的

// 查询带有下划线的字段
select * from emp
where ename like ‘%\_%’ escape ‘\’     // 重要的是escape告诉sql语句是用\转义

// sql语句解析方式,自右向左解析,一般把and右侧的写比较容易出错的条件,这样右侧出错后就不用再判断左侧了,or也是同样道理

// order by 可以根据字段序号排列
select ename, deptno, sal, sal * 12 from emp
order by 2 desc

// order by 可以同时作用多个列
select ename, deptno, sal, sal * 12 from emp
order by 2 desc, sal desc

// order by 对 null 的处理
select ename, deptno, sal, sal * 12, comm
from emp
order by comm desc nulls last

// 日期格式化
select to_char(sysdate + 1, ‘yyyy-mm-dd hh24:mi:ss’) from dual;
select round((sysdate – hiredate) / 365) from emp;
*不允许日期相加*

// 两个日期相差的月数
select ename, hiredate, months_between(sysdate, hiredate) / 12 from emp;

// 在某日期上加上多少个月
select to_char(add_months(sysdate, 95), ‘yyyy-mm-dd hh24:mi:ss’) from dual;

// 日期所在月的最后一天
select to_char(last_day(sysdate), ‘yyyy-mm-dd hh24:mi:ss’) from dual;

// 返回指定的下一个日期
select to_char(next_day(sysdate, ‘星期日’), ‘yyyy-mm-dd hh24:mi:ss’) from dual;

// 转换函数
date->char,number->char to_char
char->date to_date
char->number to_number

// 时间转换练习
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) || ‘ 今天是 ‘ || to_char(sysdate, ‘day’) from dual;
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss “今天是” day’) from dual;
select to_date(‘2015-07-19 16:50:50 今天是 星期日’, ‘yyyy-mm-dd hh24:mi:ss “今天是” day’) from dual;

// 使用转换函数时一些代替值
9     数字
0     零
$     美元
L     本地货币符号
.     小数点
,     千位符

select to_char(sal, ‘L99,999.99‘) as “sal” from emp;
返回结果
sal
——————–
¥2,850.00

// case when 语法
select ename, job, sal as “张前薪水”,
case job
when ‘PRESIDENT’ then sal + 1000
when ‘MANAGER’ then sal + 800
else sal + 400
end as “涨后薪水”
from emp
order by “涨后薪水” desc;

// decode 函数,第一个和最后一个参数以外,中间的都是过滤条件
select ename, job, sal as “张前薪水”,
decode(job,
‘PRESIDENT’, sal + 1000,
‘MANAGER’, sal + 800,
sal + 400)
as “涨后薪水”
from emp
order by “涨后薪水” desc;

// 字符串转小写
select lower(‘HELLO’) from dual;
结果:hello
// 字符串转大写
select upper(‘hello’) from dual;
结果:HELLO
// 首字母大写
select initcap(‘what is your name’) from dual;
结果:What Is Your Name
// 字符串连接函数
select concat(‘hello’, ‘ world’) from dual;
结果:hello world
// 截取字符串部分内容
select substr(‘hello world’, 3) from dual;          // 从第3位开始一直到字符串最后
结果:llo world
select substr(‘hello world’, 3, 5) from dual;      // 从第3位开始截取5个字符
结果:llo w
// 获取字符串长度和所用字节数
select length(‘hello world’) from dual;
结果:11
select lengths(‘hello world’) from dual;
结果:8
// 查找一个字符串中是否包含一个子串
select instr(‘hello world’, ‘wor’) from dual;
结果:7(子串第一个字母的位置)
没找到返回0
// 填充字符串
select lpad(‘hello’, 20, ‘*’) from dual;
结果:***************hello          // 最后结果字节数是第二个参数指定的数字
select rpad(‘hello’, 20, ‘^’) from dual;
结果:hello^^^^^^^^^^^^^^^     // 同上
// 删除字符串开头和末尾的内容
select trim(‘H’ from ‘HellHo worldHH’) from dual;
结果:ellHo world          // 注意中间的是不删除的
// 替换字符串内容
select replace(‘hello world’, ‘l’, ‘*’) from dual;
结果:he**o wor*d
// 四舍五入
select round(45.29, 1) from dual;
结果:45.3
select round(45.29) from dual;
结果:45
select round(45.29, -1) from dual;
结果:50
// 截断
select trunc(15.789, 2) from dual;
结果:15.78
select trunc(15.789, 1) from dual;
结果:15.7
select trunc(15.789) from dual;
结果:15
select trunc(15.789, -1) from dual;
结果:10
// 求余
select mod(100, 30) from dual;
结果:10

说说你的想法