Oracle 常用命令总结

北冥牧之

已于 2023-12-28 10:52:21 修改

阅读量6.8k

点赞数 2

分类专栏：数据库文章标签： oracle 数据库 dba

于 2022-05-15 17:50:07 首次发布

本文链接：https://blog.csdn.net/qq_42971035/article/details/124176458

版权

数据库专栏收录该内容

20 篇文章 5 订阅

订阅专栏

一、DDL、DML、DCL 简介

1. 什么是 DDL 呢？

DDL（data definition language）：

数据定义语言，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

2. 什么是 DML 呢？

DML（data manipulation language）：

数据操纵语言，它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

3. 什么是 DCL 呢？

DCL（Data Control Language）：

数据控制语言，是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

二、DDL 基本命令

1. DROP

-- 如果存在则删除表
DROP TABLE IF EXISTS `tb_user`;

-- 删除索引
DROP INDEX index_name; 

-- 删除视图
DROP VIEW VU_STUDENT;
DROP VIEW IF EXISTS VU_STUDENT;

-- 删除序列
DROP SEQUENCE IF EXISTS seq01; 

-- 删除存储过程
DROP PROCEDURE PROCEDURE_01;

2. CREATE

CREATE 可以用来创建表、视图、索引、序列号、存储过程等。

2.1. 创建表

create table STUDENT(
         ID NUMBER NOT NULL,     --学生ID
         NAME VARCHAR2(30) NOT NULL, --名称
         GENDER CHARACTER(2)NOT NULL,  -- 性别
         AGE NUMBER(2) NOT NULL,    -- 年龄     
         CLASS_ID NUMBER NOT NULL,   --班级ID
         ADDRESS VARCHAR2(100) NULL   --家庭住址           
);

如果创建表的时候直接创建主键

create table STUDENT(
         ID NUMBER NOT NULL,     --学生ID
         NAME VARCHAR2(30) NOT NULL, --名称
         GENDER CHARACTER(2)NOT NULL,  -- 性别
         AGE NUMBER(2) NOT NULL,    -- 年龄     
         CLASS_ID NUMBER NOT NULL,   --班级ID
         ADDRESS VARCHAR2(100) NULL,   --家庭住址  
         CONSTRAINT "STUDENT_PK" PRIMARY KEY ("ID")         
);

其中，CONSTRAINT "STUDENT_PK" 可带可不带，只是指定一个名字而已，不影响 SQL 的执行。

2.2. 创建视图

CREATE VIEW VU_STUDENT AS SELECT * FROM STUDENT;

这是最简单的做法，当然我们可以加上防重的策略。

CREATE OR REPLACE VIEW VU_STUDENT_02 (COMMONT1, COMMONT2, COMMONT3) AS
SELECT
COMMONT1, COMMONT2, COMMONT3
FROM STUDENT;

你还可以带上强制的命令：FORCE。就算你不带，系统会自动给你带上。这一点mysql和 Maria 等数据库不一样，它们没有 FORCE 这个关键字。

CREATE OR REPLACE FORCE VIEW "DAKE"."VU_STUDENT" (
"ID", "NAME", "GENDER", "AGE", "CLASS_ID", "ADDRESS"
) AS 
SELECT 
"ID","NAME","GENDER","AGE","CLASS_ID","ADDRESS" 
FROM 
STUDENT

关于视图的具体用法，请参考我的另外一篇文章：

数据库的视图

2.3. 创建索引

-- 创建主键索引，不允许重复值
CREATE PRIMARY KEY STUDENNT_PK ON table_name (ID);

-- 创建普通索引，允许重复值
CREATE INDEX index_name ON table_name (column1, column2, ...); 

-- 创建建唯一索引，不允许重复值
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

2.4. 创建序列号

最简单的

CREATE SEQUENCE seq_00;

默认生成的创建脚本

CREATE SEQUENCE "db_name"."seq_00" minvalue 1 maxvalue 
9999999999999999999999999999
increment by 1 start with 1 cache 20 noorder nocycle nokeep global;

2.5. 创建存储过程

DELIMITER $
CREATE OR REPLACE PROCEDURE merge_a_to_b ( )BEGIN
-- CREATE PROCEDURE merge_a_to_b () BEGIN
-- 定义需要插入从a表插入b表的过程变量
DECLARE A_ID VARCHAR (16);
DECLARE A_NAME VARCHAR (16);
-- 游标遍历数据结束标志 
DECLARE done INT DEFAULT FALSE;
-- 游标指向a表结果集第一条-1位置
DECLARE cur_account CURSOR FOR SELECT ID, MIGRATE_TABLE_NAME 
FROM DATA_MIGRATION_RECORD;
-- 游标指向a表结果集最后一条加1位置 设置结束标志
DECLARE CONTINUE HANDLER FOR NOT FOUND  SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历游标
read_loop :
LOOP
--  取值a表当前位置数据到临时变量
FETCH NEXT FROM cur_account INTO A_ID,A_NAME;
 
-- 如果取值结束 跳出循环
IF done THEN LEAVE read_loop; 
END IF;
 
-- 当前数据做 对比 如果b表存在则更新时间 不存在则插入
IF NOT EXISTS ( SELECT 1 FROM DATA_MIGRATION_RECORD_1 
WHERE ID = A_ID AND MIGRATE_TABLE_NAME =A_NAME ) 
THEN
INSERT INTO DATA_MIGRATION_RECORD_1 (ID, MIGRATE_TABLE_NAME) 
VALUES (A_ID, A_NAME);
ELSE 
UPDATE DATA_MIGRATION_RECORD_1  set CRT_TIME = CURRENT_TIMESTAMP
WHERE ID = A_ID AND MIGRATE_TABLE_NAME=A_NAME;
END IF;
 
END LOOP;
CLOSE cur_account;

TRUNCATE TABLE TEST;

END $
DELIMITER ;

关于存储过程，我们改天专门写一篇文章介绍，这里面的知识点相对多一些。

当然，还有其他的，比如创建同义词、触发器、函数等，这里就不一一介绍了，工作中一般情况下用不到。

3. ALTER

-- 增加字段
ALTER TABLE DAKE.STUDENT ADD (COMMONT1 NUMBER);

-- 修改字段
ALTER TABLE DAKE.STUDENT MODIFY (COMMONT5 NVARCHAR(10 byte));

-- 删除字段
ALTER TABLE STUDENT DROP (COMMONT5);


-- 字段的重命名，column是关键字
alter table STUDENT rename column COMMONT5 to COMMONT6;


-- 表的重命名
alter table 表名 rename to 新表名;
rename 表名1 to 新表名;

三、DML 基本命令

无非是增删改查，这里不再赘述。

唯一需要说明的是，Oracle不支持批量插入，网上有对应的解决办法，是否可行，需要自行测试。

-- 正常的插入语句
INSERT INTO STUDENT VALUES (1, '张三', '00', 18, 01, '上海浦东陆家嘴1111号');

-- 或者
INSERT INTO STUDENT(ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) VALUES (
3, '王五', '00', 19, 01, '上海浦东陆家嘴1113号');

但是如果一次性想插入多条怎么半呢？

INSERT INTO STUDENT VALUES 
(2, '李四', '00', 20, 02, '上海浦东陆家嘴1112号'), 
(3, '王五', '00', 19, 01, '上海浦东陆家嘴1113号'), 
(4, '马六', '01', 17, 03, '上海浦东陆家嘴1114号'), 
(5, '杨幂', '01', 28, 03, '上海浦东陆家嘴1115号');

这种在 Oracle 上是行不通的，你需要：

-- Oracle 不支持一次性执行多次insert 语句，只能一条一条执行
INSERT INTO STUDENT(ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (3, '王五', '00', 19, 01, '上海浦东陆家嘴1113号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (4, '马六', '01', 17, 03, '上海浦东陆家嘴1114号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (5, '杨幂', '01', 28, 03, '上海浦东陆家嘴1115号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (6, '黄晓明', '00', 38, 05, '上海浦东陆家嘴1116号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (7, '杨洋', '00', 28, 03, '上海浦东陆家嘴1117号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (8, '成龙', '00', 68, 07, '上海浦东陆家嘴1118号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (9, '赵丽颖', '01', 38, 06, '上海浦东陆家嘴1119号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (10, '迪丽热巴', '01', 36, 02, '上海浦东陆家嘴1120号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (11, '谷爱凌', '01', 18, 06, '上海浦东陆家嘴1121号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (12,'关晓彤', '01', 26, 06, '上海浦东陆家嘴1122号');

INSERT INTO STUDENT (ID, NAME, GENDER, AGE, CLASS_ID, ADDRESS) 
VALUES (13, '宋茜', '01', 38, 09, '上海浦东陆家嘴1123号');

四、其他

4.1. 最常用

-- 当前用户拥有的表
select table_name from user_tables;

-- 所有用户的表
select table_name from all_tables;
select count(1) from all_tables;

-- 包括系统表
select table_name from dba_tables; 
select count(1) from dba_tables; 

-- 查看同义词
SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME 
IN ( 'SYSN_TEST','PUBLIC_TEST');

-- 使用同义词
SELECT * FROM SYSN_TEST;

-- 查看Oracle版本
select * from v$version;

-- 查看表空间
select * from v$tablespace;

-- 查看表空间详细数据文件
select file_name,tablespace_name from dba_data_files;

-- 生成执行计划
explain plan for select * from STUDENT WHERE id = 1;
-- 查看执行计划结果
select * from table(dbms_xplan.display);


-- 查看表索引信息
select * from user_indexes where table_name='STUDENT' ;
select * from user_ind_columns where table_name ='STUDENT' ;

-- 查看所有表索引信息
select t.*, i.index_type from user_ind_columns t, user_indexes i 
where t.index_name = i.index_name and t.table_name = i.table_name;

SELECT t.TABLE_NAME, t.INDEX_NAME, LISTAGG(t.COLUMN_NAME, ',') 
WITHIN GROUP (ORDER BY t.TABLE_NAME)  AS column_index 
from user_ind_columns t,user_indexes i 
where t.index_name = i.index_name 
and t.table_name = i.table_name GROUP BY  
t.TABLE_NAME, t.INDEX_NAME;

-- 查看表 dump 文件情况
SELECT DUMP(DURATION, 16) FROM TEST_01;

-- 查看sql语句，是否已经分配空间；
select dbms_metadata.get_ddl('TABLE','TEST_01') from dual;

4.2. 有则修改，无则新增

如果有符合条件的数据就修改，没有则新增

MERGE INTO TEST_03 A USING TEST_02 B ON (A.ID = B.ID) 
WHEN MATCHED THEN
	UPDATE SET A.NAME = B.NAME
WHEN NOT MATCHED THEN
	INSERT (ID, NAME) VALUES (B.ID, B.NAME);


-- 单表操作，mybatis中使用
MERGE INTO table t1 USING dual ON (A.ID = #{id}) 
WHEN MATCHED THEN
	UPDATE SET t1.NAME = #{name}
WHEN NOT MATCHED THEN
	INSERT (ID, NAME) VALUES (#{id}, #{name});

-- 或者以下写法
MERGE INTO table t1 USING (
    SELECT
        #{id} ID,
        #{name} NAME,
        #{age} AGE,
    FROM DUAL
) t2 ON (t1.AGE= t2.AGE AND t1.NAME= t2.NAME) 
WHEN MATCHED THEN
	UPDATE SET t1.ID= t2.ID
WHEN NOT MATCHED THEN
	INSERT (t1.ID, t1.NAME , t1.AGE) VALUES (t2.ID, t2.NAME , t2.AGE);

4.3. 分组与排序

select 
	ID, 
	NAME, 
	DURATION, 
	row_number() over (partition by NAME order by ID) RM 
FROM (
	select 
		TEST_01.ID, 
		TEST_01.NAME, 
		TEST_01.DURATION,
		RANK() over (partition by TEST_01.NAME order by TEST_01.ID) RK 
	from TEST_01 
) WHERE RK = 1;

4.4. begin和end

BEGIN
    SELECT * FROM USER WHERE ID = #{id};

    UPDATE USER SET
        NAME = #{name}
    WHERE ID = #{id};
END;

这个SQL可以直接写到mybatis中，每个SQL的结束部分要使用分号隔开。

begin-end类似于数据库中的事务，是一个原子操作，但是如果数据量比较大或者对应能要求高，尽量不要使用。但是如果是一般的业务，使用起来则没有任何问题。

4.5. 批量新增-merge into

    <update id="batchInsert">
        MERGE INTO USER T1 USING (
            <foreach collection="list" index="index" item="item" open="" close="" separator="union all">
                SELECT
                    #{item.id} as id,
                    #{item.name} as name,
                    #{item.gender} as gender,
                    #{item.age} as age
                FROM DUAL
            </foreach>
        ) T2 ON (T1.ID = T2.id)
        WHEN MATCHED THEN UPDATE SET
            T1.NAME = T2.name,
            T1.GENDER = T2.gender,
            T1.AGE = T2.age
        WHEN NOT MATCHED THEN INSERT (
            ID,
            NAME,
            GENDER,
            AGE
        ) VALUES (
            T2.id,
            T2.name,
            T2.gender,
            T2.age
        )
    </update>

4.6. WITH AS

    <select id="queryUser" resultType="com.dake.entity.User">
        WITH T1 AS (
            SELECT
                ID,
                NAME,
                GENDER,
                AGE
            FROM
                USER
        ), T2 AS (
            SELECT
                ID,
                NAME,
                GENDER,
                AGE
            FROM
                PERSON T3, T1 WHERE T3.ID = T1.ID
        )
        
        SELECT T1.NAME FROM STUDENT T4 LEFT JOIN T1 ON T1.ID = T4.ID WHERE T4.ID = #{ID}
    </select>