ORACLE 完整技术库（持续补充中）

Geneartion_Z

已于 2023-09-07 10:40:49 修改

阅读量110

点赞数

文章标签： oracle 数据库

于 2023-09-05 09:58:16 首次发布

本文链接：https://blog.csdn.net/Geneartion_Z/article/details/132685277

版权

ORACLE技术库

数据库对象管理

数据表空间和索引表空间

数据表空间是用来存储数据库中的数据表的空间，它包含了数据表的数据和对应的索引。数据表空间是用来存储数据的物理文件，可以由一个或多个数据文件组成。
索引表空间是用来存储数据库中的索引的空间，它包含了索引的数据和对应的索引文件。索引表空间也是用来存储数据的物理文件，可以由一个或多个数据文件组成。
数据表空间和索引表空间的主要区别在于它们存储的内容不同。数据表空间存储数据表的数据，而索引表空间存储索引的数据。数据表空间主要用于存储表的数据，而索引表空间主要用于存储索引的数据。
此外，数据表空间和索引表空间也可以分别设置不同的存储参数，如大小、自动扩展等。这样可以根据实际情况来优化数据表和索引的存储性能。
需要注意的是，数据表空间和索引表空间通常是一一对应的关系，即一个数据表对应一个索引表。但也可以有多个索引表共享一个数据表空间的情况，这样可以节省存储空间。

账号操作

创建用户

CREATE USER USERNAME IDENTIFIED BY PASSWORD;

授权用户访问权限

GRANT PRIVILEGE_NAME TO USERNAME;

撤销用户访问权限

REVOKE PRIVILEGE_NAME FROM USERNAME;

修改用户密码

ALTER USER USERNAME IDENTIFIED BY NEW_PASSWORD;

锁定用户

ALTER USER USERNAME ACCOUNT LOCK;

解锁用户

ALTER USER USERNAME ACCOUNT UNLOCK;

删除用户

DROP USER USERNAME;

创建角色

CREATE ROLE ROLE_NAME;

授权角色访问权限

GRANT PRIVILEGE_NAME TO ROLE_NAME;

撤销角色访问权限

REVOKE PRIVILEGE_NAME FROM ROLE_NAME;

删除角色

DROP ROLE ROLE_NAME;

表空间操作

创建表空间：

CREATE TABLESPACE 表空间名
DATAFILE '数据文件路径'
SIZE 大小
AUTOEXTEND ON NEXT 扩展大小 MAXSIZE 最大大小;

修改表空间：

ALTER TABLESPACE 表空间名
ADD DATAFILE '数据文件路径'
SIZE 大小;

删除表空间：

DROP TABLESPACE 表空间名
INCLUDING CONTENTS;

扩展表空间：

ALTER TABLESPACE 表空间名
ADD DATAFILE '数据文件路径'
SIZE 扩展大小;

缩减表空间：

ALTER DATABASE DATAFILE '数据文件路径'
RESIZE 缩减大小;

查看表空间：

SELECT * FROM DBA_TABLESPACES;

查看表空间的数据文件：

SELECT * FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '表空间名';

查看表空间的使用情况：

SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL_BYTES,
SUM(BYTES - BLOCKS*BLOCK_SIZE) AS USED_BYTES,
SUM(BLOCKS*BLOCK_SIZE - BYTES) AS FREE_BYTES
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '表空间名'
GROUP BY TABLESPACE_NAME;

DQL

查询数据

--去重计数
SELECT COUNT(DISTINCT COLUMN_NAME) FROM TABLE_NAME;

--判断 字段中是否包含字符 ，纯数字为true 
REGEXP_LIKE(FIELD_NAME, '^[[:digit:]]+$')

函数大全

空值选择 DECODE

DECODE(字段或字段的运算，值1，值2，值3)

取年份

TO_CHAR(XXX,'YYYY')

DML

更新数据

UPDATE TABLE_NAME SET COLUMN1 = VALUE1, COLUMN2 = VALUE2 WHERE CONDITION;

删除数据

DELETE FROM TABLE_NAME WHERE CONDITION;

插入数据

INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, ...) VALUES (VALUE1, VALUE2, ...);
INSERT INTO TABLE_NAME SELECT * FROM OTHER_TABLE;

4.一条INSERT语句插入多条数据

INSERT ALL 
    INTO ADS_SALES_MONTH VALUES('四月',60000) 
    INTO ADS_SALES_MONTH VALUES ('五月',90000) 
SELECT * FROM DUAL;

DDL语句

创建表

CREATE TABLE TABLE_NAME (
    COLUMN1 DATATYPE CONSTRAINT,
    COLUMN2 DATATYPE CONSTRAINT,
    ...
);

修改表结构

ALTER TABLE TABLE_NAME ADD COLUMN DATATYPE CONSTRAINT;
ALTER TABLE TABLE_NAME MODIFY COLUMN DATATYPE CONSTRAINT;
ALTER TABLE TABLE_NAME DROP COLUMN;

3.修改字段名

ALTER TABLE  TD_DIM_CUSTOMER RENAME    COLUMN NEW_CUSTOMER_NAME  TO  CUSTOMER_NEW_NAME;

约束

1.非空约束：NOT NULL

含义：该字段不允许出现空值
–建表时建立约束

CREATE TABLE TABLE_NAME(COLUMN_NAME2 NUMBER [CONSTRAINT CONSTRAINT_NAME] NOT NULL);

–建表后建立约束

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NOT NULL;
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME  NULL; --该字段恒为空

2.唯一约束： UNIQUE

含义：该字段不允许出现重复数据

--建表时建立约束
CREATE TABLE 表名 (字段名 字段类型 [CONSTRAINT 约束名] UNIQUE，字段名 字段类型，.........)--列级约束
CREATE TABLE 表名( AA NUMBER,BB NUMBER ,CC NUMBER,CONSTRAINT 约束名 UNIQUE(AA))--表级约束   表结构建立完成时，从表的结构上建立约束
--建表后建立约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(字段名)；
ALTER TABLE PPPP ADD CONSTRAINT PPPP_BB_UN UNIQUE(BB);

3.主键约束 PRIMARY KEY

含义：相当于非空和唯一的组合题，非空且唯一（注意点：–一个表只能有一个主键）
–建表时建立约束

CREATE TABLE 表名 (字段名 字段类型 [CONSTRAINT 约束名] PRIMARY KEY ，字段名 字段类型，.........)--列级约束
CREATE TABLE 表名 (字段名 字段类型  ，字段名 字段类型，.........，CONSTRAINT 约束名 PRIMARY KEY（字段名）)--表级约束
CREATE TABLE PPP(AA NUMBER ,BB NUMBER UNIQUE,CC NUMBER ，CONSTRAINT PPP_AA_PK PRIMARY KEY(AA));
--建表后建立约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(字段名)；
CREATE TABLE PPP(AA NUMBER ,BB NUMBER ,CC NUMBER)
ALTER TABLE PPP ADD CONSTRAINT PPP_AA_PK PRIMARY KEY(AA);

4.检查约束

含义：检查数据是否符合规定的录入范围
–建表时建立约束

CREATE TABLE 表名 （字段名 字段类型 CHECK(条件)，字段名 字段类型，.....）  --列级约束

CREATE TABLE 表名 （字段名 字段类型 CONSTRAINT 约束名 CHECK(条件)，字段名 字段类型，.....）  --列级约束
CREATE TABLE PPP(AA NUMBER CONSTRAINT PPP_AA_CH CHECK(AA BETWEEN 1 AND 10),BB NUMBER ,CC NUMBER );
INSERT INTO PPP VALUES(1,2,3333);
DROP TABLE PPP
CREATE TABLE 表名 （字段名 字段类型 ，字段名 字段类型，.....,CONSTRAINT 约束名 CHECK(条件)）  --表级约束
CREATE TABLE PPP(AA NUMBER ,BB NUMBER ,CC NUMBER ,CONSTRAINT PPP_AA_CH CHECK(AA BETWEEN 1 AND 10));

--建表后建立约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件);
ALTER TABLE PPP ADD CONSTRAINT PPP_BB_CH CHECK(BB BETWEEN 1 AND 10)
CREATE TABLE PPP (AA NUMBER ,BB VARCHAR2(50));
ALTER TABLE PPP ADD CONSTRAINT PPP_BB_CH CHECK(BB IN ('男','女'))
INSERT INTO PPP VALUES(1111,'女')

5.外键约束

SELECT * FROM EMP 
含义：当前列中的数据要从另一张表的主键列或者唯一列获取
--建表时建立约束
CREATE TABLE 表名 （字段名 字段类型 REFERENCES 主表名 (主键列或者唯一列)，字段名 字段类型，.....）  --列级约束

CREATE TABLE 表名 （字段名 字段类型 CONSTRAINT 约束名 REFERENCES 主表名 (主键列或者唯一列)，字段名 字段类型，.....）  --列级约束
DROP TABLE PPP
CREATE TABLE PPP(AA NUMBER CONSTRAINT PPP_AA_FK REFERENCES DEPT(DEPTNO),BB NUMBER )
INSERT INTO PPP VALUES(10,5555);
INSERT INTO PPP VALUES('',5555);  --主表列没有空值一样插入空值
SELECT * FROM DEPT

CREATE TABLE 表名 （字段名 字段类型 ，字段名 字段类型，.....，
            CONSTRAINT 约束名 FOREIGN KEY (外键列)  REFERENCES 主表名 (主键列或者唯一列)）  --表级约束
CREATE TABLE PPP (AA NUMBER ,BB NUMBER , CONSTRAINT PPP_AA_FK FOREIGN KEY(AA) REFERENCES DEPT(DEPTNO));
INSERT INTO PPP VALUES(100,5555);

--建表后建立约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键列)  REFERENCES 主表名 (主键列或者唯一列) ;

--级联删除，级联更新，查询一下，什么意思？怎么用？

注意点：
一个表只能有一个主键，主键列可以覆盖多个字段，覆盖字段全部重复才算做重复数据

约束的删除

非主流方式删除约束

 ALTER TABLE 表名 DROP PRIMARY KEY ;--删除主键约束
 ALTER TABLE 表名 DROP UNIQUE(字段名);--删除唯一约束

主流方式删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名

约束的重命名

ALTER TABLE 表名 RENAME CONSTRAINT 旧约束名 TO 新约束名

禁止约束

ALTER TABLE 表名 DISABLE CONSTRAINT 约束名（CASCADE--外键列使用）

激活约束

ALTER TABLE 表名 ENABLE CONSTRAINT 约束名 ; --注意点：字段中符合约束条件才能激活

–总体上来说，同一用户下，约束名不能重复

CREATE TABLE PPPPP(AA NUMBER CONSTRAINT FK_AA_PPP NOT NULL)   --约束名重复，报错

–主键约束不能和唯一约束建在同一个字段上

索引

创建索引

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1, COLUMN2, ...);

删除索引

DROP INDEX INDEX_NAME;

视图

创建视图

CREATE VIEW VIEW_NAME AS SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME WHERE CONDITION;

修改视图

ALTER VIEW VIEW_NAME AS SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME WHERE CONDITION;

删除视图

DROP VIEW VIEW_NAME;

子查询 WITH 1 AS

WITH  
    SQL1 AS (SELECT TO_CHAR(A) S_NAME FROM TEST_TEMPA),  
    SQL2 AS (SELECT TO_CHAR(B) S_NAME FROM TEST_TEMPB WHERE NOT EXISTS (SELECT S_NAME FROM SQL1 WHERE ROWNUM=1))  
SELECT * FROM SQL1  
UNION ALL  
SELECT * FROM SQL2

以A表中的值快速更新B表中记录的方法

问题描述

有两张表，A表记录了某些实体的新属性，B表记录了每个实体的旧属性，现在打算用A中的属性值去更新B中相同实体的旧属性，如下图所示：

在这里插入图片描述

常规解决办法

常规解决思路：从A表中每读出一条记录，去B表更新对应实体的属性值。用一段存储过程来模拟这个问题为：

begin
    for x in (select tbbh,dlbm from a)
        loop
        update b set b.dlbm=x.dlbm where b.tbbh=x.tbbh;
        end loop;
        commit;
end;

在B表TBBH字段未创建索引的情况下，耗时约17.95s。
在B表TBBH字段创建索引的情况下，耗时约1.18s。

优化解决办法

常规办法是逐条进行更新，那可不可以进行批量的更新呢？答案是肯定的。我们可以这样操作。

在A表的TBBH字段上创建主键约束
alter table a add primary key (tbbh) using index;

对A、B表的联合视图进行更新
update (select b.tbbh ,a.dlbm adlbm ,b.dlbm bdlbm from a,b where a.tbbh=b.tbbh ) set bdlbm=adlbm;