Oracle数据库常用基本操作

最新推荐文章于 2024-07-28 02:42:10 发布

小马哥ma

最新推荐文章于 2024-07-28 02:42:10 发布

阅读量2.6k

点赞数 5

分类专栏： oracle 文章标签：数据库 sql oracle 数据仓库 database

本文链接：https://blog.csdn.net/m0_58871547/article/details/123363808

版权

oracle 专栏收录该内容

6 篇文章 2 订阅

订阅专栏

这篇博客详细介绍了数据库中的表操作，包括创建、复制、删除表以及添加、修改、删除列。还涉及到了表的约束，如主键、外键、唯一性和非空约束。此外，讨论了数据的插入、更新和删除操作，以及视图、索引、序列的创建与管理。最后提到了用户权限、表分区以及同义词和数据库链接的创建和管理。

摘要由CSDN通过智能技术生成

1、对表的操作
--语法结构：创建表
CREATE TABLE 表名1 (列名数据类型 [,列名数据类型]...);

--语法结构：复制表结构
CREATE TABLE 表名1 AS SELECT * FROM 表名2 WHERE 1=2;

--语法结构：复制表结构+表里的信息(根据结果集来创建表)
CREATE TABLE 表名1 AS SELECT * FROM 表名2 where.....;

--语法结构：删除表
DROP TABLE 表名;

--语法结构：添加列
ALTER TABLE 表名 ADD 列名数据类型;

--语法结构：修改列类型
ALTER TABLE 表名 MODIFY 列名数据类型;

--语法结构：修改列名
ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;

--语法结构：删除列
ALTER TABLE 表名 DROP COLUMN 列名;

--语法结构：修改表名
ALTER TABLE 表名 RENAME TO 新表名;

--语法结构：给表添加注释
COMMENT ON TABLE 表名 IS 注释;

--语法结构：对列添加注释
COMMENT ON COLUMN 表名.列名 IS '注释信息';

--语法结构：创建索引（目的：提高查询效率）
CREATE [UNIQUE] INDEX 索引名 ON 表名(列名1[,列名2…])

--语法结构：删除索引
DROP INDEX 索引名

--语法结构：完整的创建序列
CREATE SEQUENCE 序列名
[START WITH num] --- 从哪里开始，一般是 1
[INCREMENT BY increment] --- 每次增加多少
[MAXVALUE num|NOMAXVALUE] --- 最大值，一般是28个9
[MINVALUE num|NOMINVALUE] --- 最小值，一般是1
[CYCLE|NOCYCLE] ---- 是否循环，一般是不循环：NOCYCLE
[CACHE num|NOCACHE] --- 在内存中保留多个个序号，一般是 CACHE 数字

一般用它的 nextval ，来做自增长

--语法结构：简易的创建序列
CREATE SEQUENCE 序列名;

----语法结构：删除序列。序列不能更改，只能删除重建
DROP SEQUENCE 序列名;

--语法结构：创建视图
CREATE OR REPLACE [{FORCE|NOFORCE}] VIEW 视图名
--FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。
--NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
AS
SELECT 查询
[WITH READ ONLY CONSTRAINT] ---- 表示视图只读，不加则表示视图可以增删改数据

--语法结构：删除视图
DROP VIEW 视图名

--------------------------------------2、表的约束：约束不能修改，只能删除重建
按照约束用途分类：
1.PRIMARY KEY：主键约束：额外约定（必须遵守）：一张表只有一个主键，也可以没有主键
2.FOREIGN KEY：外键约束
3.CHECK：检查约束
4.UNIQUE：唯一约束
5.NOT NULL：非空约束

--语法格式：ALTER TABLE命令
ALTER TABLE 表名 ADD CONSTRAINT 约束名约束内容。

--语法格式：添加主键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名1[,列名2...])

--语法格式：添加外键约束
ALTER TABLE 主表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名1[,列名2...]) REFERENCES 从表名(列名1[,列名2...])

--语法格式：添加CHECK约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)

--语法格式：添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)

--语法格式：添加非空约束
ALTER TABLE 表名 MODIFY 列名 NOT NULL

--语法格式：删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名

约束也可以在创建表的时候添加
CREATE TABLE student_1(
sno VARCHAR2(10) PRIMARY KEY,
sname VARCHAR2(30) NOT NULL,
ssex VARCHAR2(2) CHECK(ssex='男' OR ssex='女')，
sage NUMBER(3),
sbirthday DATE
);

-----------------------3、对数据的操作（数据操纵语言（DML））:在执行完后，需要做提交的操作（commit）
--语法结构：数据插入
INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)
--列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
--值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。

--语法结构：INSERT向表中插入一个结果集
INSERT INTO 表名1(列名1，列名2……)SELECT 查询结果集;
--在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，
--结果集中的列的数量与表中的列的数量一致。

--语法结构：数据更新操作
UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件（WHERE 条件没有的话则是全表更新）

--语法结构：数据删除操作
DELETE FROM 表名 WHERE 条件

--语法结构：TRUNCATE（也是删除数据，把表中的数据全部删除---- 清空数据）（数据定义语言 DDL）
TRUNCATE TABLE 表名

---------------------------------------------------------4、用户
--语法结构：创建用户
CREATE USER 用户名 IDENTIFIED BY password [ACCOUNT LOCK|UNLOCK];

--语法结构：给用户授权
GRANT CONNECT，RESOURCE TO 用户名; --- 直接将connect角色，resource角色授予用户。
--- CONNECT角色:连接数据库。RESOURCE:正常使用数据库

GRANT CREATE TABLE TO 用户名; --- 给用户创建表的权限
GRANT CREATE VIEW TO 用户名; --- 给用户创建视图的权限

--语法结构：收回用户权限
REVOKE 角色|权限 FROM 用户（角色）

--语法结构：修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码;

--语法结构：给用户加锁/解锁
ALTER USER 用户名 ACCOUNT LOCK/UNLOCK;

---------------------------------------------------------5、表分区（创建分区表）
1、范围分区
【例】按入职日期进行范围分区
CREATE TABLE 表名 (列名数据类型 [,列名数据类型]...)
PARTITION BY RANGE (HIREDATE)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('1981-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS01, --①
PARTITION part2 VALUES LESS THAN (TO_DATE('1982-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS02,
PARTITION part3 VALUES LESS THAN (TO_DATE('1983-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS03,
PARTITION part4 VALUES LESS THAN (TO_DATE('1988-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS04,
PARTITION part5 VALUES LESS THAN (MAXVALUE) TABLESPACE CUS_TS05
)

2.列表分区：
【例】按DEPTNO进行LIST分区
CREATE TABLE 表名 (列名数据类型 [,列名数据类型]...)
PARTITION BY LIST (DEPTNO)
(
PARTITION MYEMP_DEPTNO_10 VALUES (10),
PARTITION MYEMP_DEPTNO_20 VALUES (20) ,
PARTITION MYEMP_DEPTNO_30 VALUES (30) ,
PARTITION MYEMP_DEPTNO_40 VALUES (40)
)

3.散列分区:
CREATE TABLE 表名 (列名数据类型 [,列名数据类型]...)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)
简写：
CREATE TABLE 表名 (列名数据类型 [,列名数据类型]...)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

4.组合分区(组合范围散列分区)
基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
【例】按入职日期进行范围分区，再按DEPTNO进行LIST子分区
CREATE TABLE 表名 (列名数据类型 [,列名数据类型]...)
PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST (DEPTNO)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
(
SUBPARTITION P1A VALUES (10) ,
SUBPARTITION P1B VALUES (20),
SUBPARTITION P1C VALUES (30),
SUBPARTITION P1D VALUES (40)
),
PARTITION P2 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
(
SUBPARTITION P2A VALUES (10) ,
SUBPARTITION P2B VALUES (20),
SUBPARTITION P2C VALUES (30),
SUBPARTITION P2D VALUES (40)
),
PARTITION P3 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
(
SUBPARTITION P3A VALUES (10) ,
SUBPARTITION P3B VALUES (20),
SUBPARTITION P3C VALUES (30),
SUBPARTITION P3D VALUES (40)
),
PARTITION P4 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD'))
(
SUBPARTITION P4A VALUES (10) ,
SUBPARTITION P4B VALUES (20),
SUBPARTITION P4C VALUES (30),
SUBPARTITION P4D VALUES (40)
)
)
5.复合分区(复合范围散列分区)
基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区
CREATE TABLE 表名 (列名数据类型 [,列名数据类型]...)
partition by range(transaction_date) subpartition by hash(transaction_id)
subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);

1)添加分区
以下代码给表添加了一个P3分区
ALTER TABLE 表名 ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意：以上添加的分区界限应该高于最后一个分区界限。

以下代码给表的P3分区添加了一个P3SUB1子分区
ALTER TABLE 表名 MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2)删除分区
以下代码删除了P3表分区：
ALTER TABLE 表名 DROP PARTITION P3;

在以下代码删除了P4SUB1子分区：
ALTER TABLE 表名 DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3)截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。
当表中即使只有一个分区时，也可以截断该分区。
通过以下代码截断分区：
ALTER TABLE 表名 TRUNCATE PARTITION P2;

通过以下代码截断子分区：
ALTER TABLE 表名 TRUNCATE SUBPARTITION P2SUB2;

4)合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，
注意的是，不能将分区合并到界限较低的分区。
以下代码实现了P1 P2分区的合并：
ALTER TABLE 表名 MERGE PARTITIONS P1,P2 INTO PARTITION P2;

5)拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE 表名 SBLIT PARTITION 分区名 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6)接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，
注意的是，接合分区只能用于散列分区中。
通过以下代码进行接合分区：
ALTER TABLE 表名 COALESCA PARTITION;

7)重命名表分区
以下代码将P21更改为P2
ALTER TABLE 表名 RENAME PARTITION P21 TO P2;

---------------------------------------------------------6、其他
1、创建同义词
CREATE [OR REPLACE] [PUBLIC] SYNONYM 同义词名 FOR 用户名.对象名;

--对象包括：表、视图、序列、过程、函数、程序包等
--默认情况下创建私有同义词，只能被当前创建用户使用

CREATE OR REPLACE PUBLIC SYNONYM EMP FOR SCOTT.EMP; ---创建公有同义词，这样的话其他的用户直接访问EMP就是访问了scott用户下的emp表了

2、删除同义词
DROP SYNONYM 同义词名;

3、给用户授权创建/删除同义词
GRANT
CREATE PUBLIC SYNONYM ,
DROP PUBLIC SYNONYM
TO SCOTT ;

1、创建DBlink
create [public] database link dblink名
connect to 数据库用户名 identified by "数据库密码"
using 'TNS连接串信息';

--TNS连接串信息：可以使用整串信息，也可以使用代表这串信息的别名

create public database link TESTLINK1 connect to scott identified by "123456" USING 'ORCL2';

2、查看用户是否具备创建dblink 权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='用户名';

3、授权用户创建dblink 的权限
GRANT
CREATE PUBLIC DATABASE LINK ,
DROP PUBLIC DATABASE LINK
TO 用户名;