orcl数据库命令——前篇

一、常用基础命令

1.本机连接虚拟机oracle数据库
    sqlplus scott/tiger@192.168.56.101:1521/orcl

2.查看当前用户
    show user;

3.查看员工表结构
    desc emp;
    
4.查看所有表
    select * from tab;
    
5.修改密码
    sqlplus scott/tiger as sysdba
    alter user scott identified by tiger

6.设置行宽
    set linesize 200

7.设置ename列宽度
    col ename for a8

8.命令行中select查询中from写成了form,更改错误继续执行
    a.错误行号
    b.c /form/from
    c./

9.SQL语句中的null
    a. 包含null的表达式都为null
        sal+null=null
    b. null永远!=null
        is null或is not null
    c.如果集合中含有null，不能使用not in；但可以使用in
    d.oracle中null值最大
    e.组函数自动滤空;可以嵌套滤空函数来屏蔽他的滤空功能

10.清理屏幕命令
    host cls

11.打开本机默认文本编辑器命令
    ed

12.去掉重复记录关键词
    distinct
    

13.select 3+2 from ? 这个问号处的伪表名
    dual

14.字符串拼接连接符
    ||

15.录屏
    spool F:/test.txt
    spool off

16.select * from 表名 where 条件
    (包括 between and、in、not in、is null、like...)

17.查看参数设置
    select * from v$nls_parameters;
    
18.修改日期格式
    alter session set NLS_DATE_FORMAT='yyyy-mm-dd'
    
19.转义字符使用(名字中含有_)
     select * from emp where ename like '%\_%' escape '\'
    
20.排序关键字
    order by(升序asc,降序desc)
    
21.设置每页记录数
    set pagesize 20

22.排序空值放最后
    select * from emp order by comm desc nulls last  
    
23.字符行数
    (1)大小写:
        1> lower("hello"): 转小写
        2> upper("hello"): 转大写
        3>initcap("hello"): 首字母大写
    (2)截取:
        1> substr(a,b): 从a中，第b位开始取
        2> substr(a,b,c) 从a中，第b位开始取，取c位
    (3)长度:
        1> length('北京'): 字符数
        2> lengthb('北京'): 字节数
    (4)填充:
        1> lpad('abcd',10,'*'): 左填充
        2> rpad('abcd',10,'*'): 右填充
    (5)替换:
        1> replace('Hello World','l','*'): 替换为指定字符

二、函数部分

24.数值函数
        1> 四舍五入:round(a,b): a:传入数字,b:保留小数位数
        2> 截断: trunc(a,b): a:传入数字,b:截取小数位数
        3> 取余: mod(a,b): a%b
25.查询当前时间:
    select sysdate from dual;
    select systimestamp from dual;
        
26.格式化时间:
    to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff')
    
27.日期数学运算
    sysdate+1:当前日期加1天
    两个日期相减,返回相差天数
        
28.日期函数
    1> months_between(sysdate,hiredate):两个日期之间相差的月数
    2> add_months(sysdate,12),当前日期加上12个月
    3> next_day(sysdate,'星期日'):当前日期的下个星期日
    4> last_day(sysdate): 这个月的最后一天
    5> round(sysdate,'month'): 四舍五入到月; round(sysdate,'year'): 四舍五入到年
    6> trunc(sysdate,'month'): 截取到月; trunc(sysdate,'year'): 截取到年    
    
29.转换函数:
    1> to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day')结果是: 2016-03-13 15:26:12今天是星期日
    2>  to_char(12345678.23,'L999,999,999.99')结果是: ￥12,345,678.23
    
30.通用函数:
    1> nvl2(a,b,c) 当a=null的时候，返回c；否则返回b
    2> nullif(a,b) 当a=b的时候，返回null；否则返回a
    3> coalesce(a,b,c..) 从左到右 找到第一个不为null的值
    
31.case表达式:
    case表达式:
        case job when 'PRESIDENT' then sal+1000
                  when 'MANAGER' then sal+800
                  else sal+400
        end
        
32.decode函数:
    decode(job,'PRESIDENT',sal+1000,
                     'MANAGER',sal+800,
                              sal+400)
                              
33.多行函数:
    1> sum、count、avg
    2>分组:group by .. having ..
    
34.group by增强:
    1> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
    2> 格式化:break on deptno skip 2
    3> 取消格式化: break on null

三、多表查询中的连接

35.等值连接:
    select e.empno,e.ename,e.sal,d.dname
    from emp e,dept d
    where e.deptno=d.deptno;
    
36.不等值连接:
    select e.empno,e.ename,e.sal,s.grade
    from emp e,salgrade s
    where e.sal between s.losal and s.hisal;
    
37.外连接:
    1> 左外连接：where e.deptno=d.deptno(+)
    2> 右外连接:where e.deptno(+)=d.deptno
    
38.自连接: 通过表的别名，将同一张表视为多张表
    select e.ename 员工姓名,b.ename 老板姓名
    from emp e,emp b
    where e.mgr=b.empno;
    
39.层次查询(level伪列):
    select level,empno,ename,mgr
    from emp
    connect by prior empno=mgr
    start with mgr is null
    order by 1;