Oracle知识清单

0 oracle常用命令

详细

1. 对表中数据操作

1.1 增

• insert into 表名(列名1，列名2) values(数值1，数值2)
• insert into 表名 values(数值1，数值2)
  详情

1.2 删

• delete from 表名 where 条件
  详情

1.3 改

• update 表名 set ‘改后列’=新值 where 条件

详细

1.4 查

• min max sum avg count 等聚合函数
  详情

• where 列名 between 2001 and 2999；
  详情

• and or not

• in 值是否在后面的列表中存在

• 是否是空值

  • 不为空：where 列名 is not null
  • 为空：where 列名 not null

• 字符串查询substr

  • 模糊查询 like _ %

• 统计中的null

  • nvl nvl2

• 排序 order by asc(升序默认) 列1 desc 列2

  • 第s k

• 分组

  • group by
    • having
  • 分组之后数据合并显示
    • WMSYS.WM_CONCAT
      详情

• 表的分页

  • between 输出
  • rownum

• 并集合union

  • 列相同，两个表合成一个，剔除重复
  • nuion all 不剔除重复

• decode

  • DECODE(字段,if1,then1,if2,then2,if3,then3,…,else)

• when then

  • 根据工资分三个等级

• exists

  • where exists (select) 判断是否为空

• distinct 去重
  对查询出的结果集去重

SQL> select distinct ename ,job from emp;

ENAME                JOB
-------------------- ------------------
WARD                 SALESMAN
SMITH                CLERK
CLARK                MANAGER
TURNER               SALESMAN
JAMES                CLERK
JONES                MANAGER
MARTIN               SALESMAN
ADAMS                CLERK
SCOTT                ANALYST
KING                 PRESIDENT
FORD                 ANALYST

• 多表连接查询
  详情
  • 左连接
    • select * from 表1 left jion 表2 on a.id=b.id
    • select * from 表1,表2 where a.id=b.id(+)
  • 右连接
    • 三个表连接

2. 对表本身操作

2.1 新建表

• create table 表明(字段名)；
  详情

2.2 修改表

详情

2.2.1 增加列

• alter table 表名 add 新列名 结构属性();

2.2.2 删除列

• alter table 表名 drop column 列名

2.2.3 修改列

• alter table 表名 modify 列名 修改后的数据类型

2.2.4 重新命名列

• alter table 表名 rename column 列名1 to 列名2

2.3 删除表

• drop table 表明
  详情

2.4 增加约束

• 主键

  • alter table 表名 add primary key(id);
  • alter table 表名 drop primary key;

• 外键

  • alter table 表名 add constraint 约束名称 foreign key（列名）references 表名2(列);
  • alter table 表名 add foreign key（列名）references 表名2(列);

• 非空

  • alter table 表名 modify 列名 not null;
  • alter table 表名 modify 列名 null;

• 默认

  • alter table 表名 modify 列名 default(‘9999’);
  • alter table 表名 modify 列名 default(’’);

• 唯一

  • alter table 表名 add unique(列名)
  • alter table 表名 drop unique(列名)

• 检查

  • check( 列名 >=0 and xxx )
  • alter table (表明) add check( 列 in(‘男’,‘女’) )

2.5 序列（sequence）

• 删除序列 drop sequence seq;
  创建简单序列 create sequence seq;
• seq.currval seq.nextval
• cache不能小于increment by

3. 对库本身操作

3.1 修改一些显示设置

• 时间格式设置
  • alter session set nls_date_format=‘yyyy-mm-dd HH24:mi:ss’

3.2 新建 删除 用户

• 赋予权限
  • grant dba to user；
• 加锁解锁用户
  • alter user 同户名 account (un)lock;
  • 加锁：alter user 用户名 account lock;
• 删除用户
  • drop user gaoming cascade ;
• 修改用户密码
  • alter user 用户名 identify by xxx;

4. 常用函数

4.1 自定义函数和执行

• create or replace function 函数名(参数) return 返回值 as begin return end;
• 四种方法执行函数
  • 3 exec 函数名称(参数)； 4 call 函数名称(参数) ；
• select sysdate from dual;

4.2 数学函数

• 向上取整ceil(n)

• 向下取整floor(n)

• 四舍五入round(m,n)
  round(数字,n位数):四舍五入 ，保留n位小数
  

• 求余mod(m,n)
  

• 次方power(m,n)

• trunc(m,n)
  trunc(数字,n位数):舍尾，保留n位小数
  四是看6 7进1
  

4.3 字符串

• 替换

  • replace(列名,‘李’,‘张’)
    

• 去掉左右两边的字符

  • trim(‘m’ from 列名)
    trim：去掉任意字符 java里是去空格
    
    

• 大小写转换

  • lower()
    

  • upper()

  • 首字符大写initcap(Ename)
    

• 字符串截取

  • substr(被截取字符串,截取起始位置,截取长度)
    substr(str,begin,len) ：从1开始数
    

• 查找子串的位置

  • instr(‘列’,‘查找的串’)
    insrt(str,substr) // 在str里找substr的位置
    

• 居中居右

  • rpad(列名,长度,‘可选填充字符’)
  • lpad(Ename,5,’+’)
    lpad/rpad：填充(左填充右填充)
    

• 字符串长度

  • length(列名)
    length字符数 lengthb 字节数
    英文/数字：一个字符就是一个字节
    如果中文/符号：
    utf-8编码格式下： 一个汉字/符号 占3个字节
    gbk： 一个汉字/符号 占2个字节
    查看当前系统编码格式：
    select * from nls_database_parameters ;

4.4 时间与字符串的那些事

• 获取当前时间

  • sysdate
    

• 字符串->时间

  • to_date( ‘2018-1-1’,‘yyyy-mm-dd’ )
    

• 时间转->字符串

  • to_char(system,‘yyyy-mm-dd’)

• trunc——时间截取函数

  • 查询当月有多少天
  • 默认截取
    • trunc(列名)
  • 截取到月
    • trunc(列名,‘mm’)
  • 截取到周
    • trunc(newtime,‘d’)
  • 截取到年
    • trunc(newtime,‘yy’)
  • 截取到小时
    • trunc(newtime,‘hh’)
  • 截取到分钟
    • trunc(newtime,‘mi’)

• 日期运算

  • 日期直接 +1 -1
    • 是对天数进行处理
  • 求后一个月份
    • add_months(sysdate,1)
    • add_months(newtime,3) 三个月后
  • 下一个星期二
    • next_day(newtime,‘星期二’)
  • 所在月的最后一天：
    • last_day(newtime)

• 计算两个日期直接的间隔的月

• select months_between(to_date(‘12/1/2015’,‘mm/dd/yyyy’),to_date(‘1/11/2015’,‘mm/dd/yyyy’)) “MONTHS” FROM DUAL;