Oracle 学习

发表于 | 分类于 | 阅读次数:
字数统计: 3.8k | 阅读时长 ≈ 15

记录 Oracle 学习的方方面面

表的创建与删除

  • 创建表
1
2
3
4
5
create table city(
    id numeric(10) primary key not null,
    name varchar(50),
    province varchar(50)
)

除了上述这种和 MySQL 相似的创建方式外，Oracle 还有另一种创建表的方式

1
2
3
4
5
6
create table city(
 id numeric(10) not null,
 name varchar(50),
 province varchar(50),  //注意此处是有一个逗号的
 constraint primary key (id)
)
  • 删除表的方式

删除表的方式一共有两种，一是使用 truncate，此方法是将表中的数据删除但保留表结构，另一种方法是使用 drop，若使用 drop 则是将整个表全部删除

若想要删除表中的某一行数据则可以使用 delete 方法

1
delete from table_name where column='..'

获取表及表字段

  • 获取表
1
2
3
4
5
select table_name from user_tables; //当前用户的表      

select table_name from all_tables; //所有用户的表  

select table_name from dba_tables; //包括系统表
  • 获取表字段
1
select * from user_tab_columns

若要获取特定表的表名则需要在语句后面加上 where 条件，但需要注意的是，where 后的等值判断必须大写，比如

1
select * from user_tab_columns where TABLE_NAME='CITY1'

oracle 修改表

Oracle 修改使用 alter 关键字

1
2
3
4
5
6
7
8
9
10
11
12
//新增列
ALTER TABLE 表名 ADD 列名 数据类型;
还可以使用 default 关键字为新增的列设置默认值
ALTER TABLE 表名 ADD 列名 数据类型 DEFAULT 10
//修改列的数据类型
ALTER TABLE 表名 MODIFY 列名 数据类型;
//更改列名
ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;
//删除列
ALTER TABLE 表名 DROP COLUMN 列名;
//更改表名
ALTER TABLE 当前表名 RENAME TO 新表名;

索引

  • Oracle 创建索引
1
create index 索引名(必选) on 表名(需要建索引的列)
  • 查看表中创建索引的列
1
select * from all_ind_columns where table_name=表名(大写)
  • 删除索引
1
drop index 索引名

  • 修改索引

    修改索引有重命名，合并，重建。三种情况

    • 合并

      由于数据库在长期使用中会出现碎片导致索引效率下降因此需要合并索引，

      合并索引命令为

      1
      alter index 索引名 coalesce;

  • 重命名

    重命名命令为

    1
    alter index 索引名 rename to 新名称

  • 重建索引

    频繁的更新操作会导致索引树发生倾斜，此外，当对表进行 move 操作时原来的索引会失效，因此需要重建索引

    重建索引命令为

    1
    alter index 索引名 rebuild

表分区

Oracle 中的表分区一共有三种情况，分别是范围分区、列表分区、hash分区。

范围分区

范围分区用于对特定字段进行范围分区，按照给定的字段对表进行划分，格式如下：

1
2
3
4
5
6
7
create table range_city (id numeric(10) not null primary key,name varchar2(50),province varchar2(50))
partition by range(id)
(
partition p10000 values less than(10000),
partition p20000 values less than(20000),
partition p_end values less than(maxvalue)
);

使用范围分区在指定分区时需要划分好分区的范围，这样数据才会落入到划分好的区间中，maxvalue 并不是一个代指而是 Oracle 中真实存在用法

列表分区

列表分区是对特定字段进行归并，格式如下：

1
2
3
4
5
6
7
8
create table list_city(id numeric(10) primary key not null,name varchar2(50),province varchar2(50))
partition by list(name)
(
partition p1 values('临夏回族自治州'),

partition p2 values(default)

);

这样的话所有 name 为 临夏回族自治州 的项都会被划分到同一个分区中，此外使用 default 会将其他未划分的数据划分到 p2 中。

hash 分区

hash 分区顾名思义就是将拥有相同 hash 值字段的数据划分到同一个分区中，分区数最好设置为 2 的倍数，这样可以使数据均匀分布，格式如下：

1
2
3
4
5
6
7
8
9
create table hash_city(id numeric(10)primary key not null,name varchar(50),province varchar(50))
partition by hash(province)
(
partition p1,
partition p2,
partition p3，
partition p4

);

设置分区时仅需要将声明分区名称即可

Oracle 约束

Oracle 的约束有以下几种

  1. not null 非空约束

  2. unique 唯一约束

  3. primary key 主键

  4. foreign key 外键

  5. check 定义一般性约束

not null 和 unique

not null 和 unique 都是在创建表的时候对表的字段进行声明的关键字，其中 unique 允许出现多个空值，因为 unique 是通过索引的 b 树来实现的，如果没有索引就先生成一个，而 b 树是不保存 null 值的，这也导致了使用 where 过滤 null 会进行全表扫描

主键

主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据库要求所有表都应该有主键，不过 Oracle 没有遵循此范例要求，Oracle 中的表可以没有主键。关于主键有几个需要注意的点：

键列必须具有唯一性，且不能为空，其实主键约束相当于 unique + not null 一个表只允许有一个主键

主键所在列必须具有索引，如果不存在，将会在主键添加时自动创建

主键约束可以在定义的时候添加也可以在创建表之后通过 alter table constraint 约束名 primary key (列名) 的方式添加，相较而言，后一种方法更灵活一点

Oracle 外键

外键创建
  • 使用 create table 语句创建

使用 create table 语句创建外键的语法是：

1
2
3
4
5
6
7
8
9
create table table_name
(
    column1 type null/not null,
    column2 type null/not null,
    ...
    constraint fk_column
        foreign key (column1,column2,... column_n)
        references parent_table (column1,column2,...,column_n)
);

语句实例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table parent
(
    p_id numeric(10) not null,
    p_name varchar2(50) not null,
    p_gender varchar2(10)

constraint parent_pk primary key(p_id)
);

create table child
(
    c_id numeric(10) not null,
    c_name varchar2(50) not null,
    c_gender varchar2(10)

constraint child_pk
    foreign key (p_id,p_name)
        references child(p_id,p_name)

);

由上可知，Oracle 中的外键可以有一个或多个

  • 使用 alter table 语句创建
1
2
3
4
alter table table_name
add constraint constraint_name
    foreign key (column1,column2,...column_n)
        references parent_table(column1,column2,...column_n);

使用外键时可以设置级联删除，具体语句如下：

1
2
3
4
alter table table_name
add constraint constraint_name
 foreign key (column1,column2,...column_n)
 references parent_table(column1,column2,...column_n) on delete cascade;

主要就是在添加外键的语句末尾加上 on delete cascade 添加后在主表中删除该字段时子表中相应的数据也会被删除，如果删除子表中的数据主表中响应的数据不会删除，还可以使用on delete set null 在主表删除时将子表对应项设为 null

check

检查约束会在向表中插入或更新数据时发挥作用，若新加入的数据不满足约束将会报错。使用方法如下：

1
2
3
create table a(id numeric(10),name varchar(50),province varchar2(50),

constraint a_ck check(id between 1 and 1000));

也可以在表创建后使用 alter 语句添加，如果表中已经有数据则在添加 check 约束时需要让 check 条件满足表中已有数据的要求

添加完 check 约束后进行 insert 操作，如果插入的数据 id 不在 1-1000 的范围内则会报错。

视图

就我个人理解而言，视图和表在操作上是没有太大区别的，二者的最大不同是视图只是封装了 sql 语句没有实际的物理存储对象，而表是有的。

创建视图

语法：create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,…) ]
as
select …
[ with check option ] [ constraint constraint_name ]
[ with read only ];

tips：

  1. or replace：如果存在同名的视图，则使用新视图替代已有的视图

  2. force：强制创建视图，不考虑基表是否存在，也不考虑当前用户是否拥有基表的访问权限

  3. column1,column2,…：视图的列名，列名的个数必须与 select 查询中列的个数相同；如果 select 查询包含函数或表达式，则必须为其定义列名。此时，既可以用 column1,column2 定义列名也可以在 select 语句中定义列名

  4. with check option：指定对视图执行的 dml 操作必须满足“视图子查询”的条件，即，对视图的增删改操作进行检查，以判断怎删改后的数据是否符合创建视图时的 select 语句的要求，换句话说就是如果将这行数据放到视图的基表上然后使用视图中的 select 语句能否重新读出来。如果不满足要求则会报错，默认情况下 with check option 选项是关闭的

  5. with read only：创建的视图只能用于查询数据，不能用于更改数据

条件查询

exit 和 in 的使用

exit 用于检查子查询结果集

exits 里的子查询结果集非空，exits 子句的值就是 true

exits 里的子查询结果集为空，exits 子句的值就是 false

使用 exits 时可以这样写 select * from table1 where exits (select 1 from table2 where column1 like ‘%a%’)

这样写的话当 table1中的 column

时间函数

  • sysdate

    【功能】：返回当前日期。

    【参数】：没有参数，没有括号

    【返回】：日期

  • add_months(d1,n1)

    【功能】：返回在日期d1基础上再加n1个月后新的日期。

    【参数】：d1，日期型，n1数字型

    【返回】：日期

  • last_day(d1)

    【功能】：返回日期d1所在月份最后一天的日期。

    【参数】：d1，日期型

    【返回】：日期

  • months_between(d1,d2)

    【功能】：返回日期d1到日期d2之间的月数。

    【参数】：d1，d2 日期型

    【返回】：数字如果d1>d2，则返回正数如果d1<d2，则返回负数

    【示例】 select sysdate, months_between(sysdate,to_date(‘2006-01-01’,’YYYY-MM-DD’)), months_between(sysdate,to_date(‘2016-01-01’,’YYYY-MM-DD’)) from dual;

  • NEW_TIME(dt1,c1,c2)

    【功能】：给出时间dt1在c1时区对应c2时区的日期和时间

    【参数】：dt1，d2 日期型

    【返回】：日期时间

    【参数】：c1,c2对应的 时区及其简写

    大西洋标准时间：AST或ADT

    阿拉斯加_夏威夷时间：HST或HDT

    英国夏令时：BST或BDT

    美国山区时间：MST或MDT

    美国中央时区：CST或CDT

    新大陆标准时间：NST

    美国东部时间：EST或EDT

    太平洋标准时间：PST或PDT

    格林威治标准时间：GMT

    Yukou标准时间：YST或YDT

  • round(d1[,c1])

    【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）

    【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)

    【参数表】：c1对应的参数表: 最近0点日期: 取消参数c1或j

    最近的星期日：day或dy或d

    最近月初日期：month或mon或mm或rm

    最近季日期：q

    最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)

    最近世纪初日期：cc或scc

    【返回】：日期

  • trunc(d1[,c1])

    【功能】：返回日期d1所在期间(参数c1)的第一天日期

    【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)

    【参数表】：c1对应的参数表:

    最近0点日期: 取消参数c1或j

    最近的星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）

    最近月初日期：month或mon或mm或rm

    最近季日期：q 最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)

    最近世纪初日期：cc或scc

    【返回】：日期

  • next_day(d1[,c1])

    【功能】：返回日期d1在下周，星期几(参数c1)的日期

    【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)

    【参数表】：c1对应:星期一，星期二，星期三……星期日

    【返回】：日期

    extract(c1 from d1)

  • 【功能】：日期/时间d1中，参数(c1)的值

    【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数)

    【参数表】：c1对应的参数表详见示例

    【返回】：字符

    【示例】 select extract(hour from timestamp ‘2001-2-16 2:38:40 ‘ ) 小时, extract(minute from timestamp ‘2001-2-16 2:38:40 ‘ ) 分钟, extract(second from timestamp ‘2001-2-16 2:38:40 ‘ ) 秒, extract(DAY from timestamp ‘2001-2-16 2:38:40 ‘ ) 日, extract(MONTH from timestamp ‘2001-2-16 2:38:40 ‘ ) 月, extract(YEAR from timestamp ‘2001-2-16 2:38:40 ‘ ) 年 from dual;

    select extract (YEAR from date ‘2001-2-16’ ) from dual;

  • localtimestamp

    【功能】：返回会话中的日期和时间

    【参数】：没有参数，没有括号

    【返回】：日期

  • current_timestamp

    【功能】：以timestamp with time zone数据类型返回当前会话时区中的当前日期

    【参数】：没有参数，没有括号

    【返回】：日期

  • current_date

    【功能】：返回当前会话时区中的当前日期

    【参数】：没有参数，没有括号

    【返回】：日期

    【示例】select current_date from dual;

    13

  • dbtimezone

    【功能】：返回时区

    【参数】：没有参数，没有括号

    【返回】：字符型

  • SESSIONTIMEZONE

    【功能】：返回会话时区

    【参数】：没有参数，没有括号

    【返回】：字符型

  • INTERVAL c1 set1

    【功能】：变动日期时间数值

    【参数】：c1为数字字符串或日期时间字符串，set1为日期参数

    【参数表】：set1具体参照示例

    【返回】：日期时间格式的数值,前面多个+号以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟

case 的用法

case 和 java 中的 switch 用法是相似的，case 的用法如下：

1
2
3
4
5
case 运算
when 条件1 then 结果1
when 条件2 then 结果2
else 结果3
end

比如说需要根据表的 id 字段的奇偶来判断对其他字段的操作

1
2
3
4
case mod(id,2)
when 0 then ...
else ...
end

nvl 和 nvl2

nvl 和 nvl2 都是用来判断数据是否为 null，并进行处理的函数。其中 nvl 接受两个参数 nvl(expr1,expr2),nvl2 接受两个参数 nvl2(expr1,expr2,expr3)。

当 nvl 的第一个参数 expr1 不为 null，那么就会返回 expr1，否则返回 expr2。

当 nvl2 的第一个参数 expr1 不为 null，那么就会返回 expr2，否则返回expr3

row_number()

对于分组后进行排名最好使用 row_number(),不要使用group by 和 order by 的组合，row_number() 使用时后面需要接上 over 语句，over 语句内指定分组以及排序的以及，order 支持多个字段，示例如下：

1
select e.department_id,first_name||' '||last_name name,salary,row_number()over(partition by e.department_id order by salary,hire_date)rn from employees e join id on e.department_id=id.department_id;

带子查询的 update

在使用 update 时如果使用了子查询，那么 Oracle 会先将待更新表的全部数据取出然后依次与 where 中的条件进行比较，若满足则进行子查询对数据进行更新

看一下需求：

更新临时表中time_id等于月初第一天的数据(条件)，且当promo_category=‘TV’时unit_cost=unit_cost+1；将当promo_category=’NO PROMOTION’时，unit_cost=0；其他保持不变

1
2
3
4
5
6
7
8
update costs_2019_11_12_ChenCheng c1 set unit_cost=(select case
when p.promo_category='NO PROMOTION' then 0
  when p.promo_category='TV' then c1.unit_cost+1
    end 
    from  promotions p
  where p.promo_id=c1.promo_id and time_id=trunc(time_id,'mm'))
  where exists(select 1 from promotions p where c1.promo_id=p.promo_id 
  and c1.time_id=trunc(time_id,'mm') and (p.promo_category='NO PROMOTION' or p.promo_category='TV'));

比如这样的一个语句，在进行更新时会首先对表 costs_2019_11_12_ChenCheng 进行遍历，遍历时将数据与 where 子句中的条件进行比较，若满足则进行更新。还有一点需要注意，比如上表，在进行多表关联更新可以直接对待更新表起别名然后在 where 子句中进行比较，不需要在子查询中使用 join。

-----------本文结束感谢您的阅读-----------
0%