Docker实战之Oracle学习笔记

前言

公司现在用的数据库是 oracle , 所以就搭建一个测试环境学习。

docker-compose配置

version: '2'
services:
  oracle:
    image: sath89/oracle-xe-11g
    container_name: oracle
    ports:
      - 1521:1521
      - 8082:8080
    volumes:
      - ./oracle/data:/u01/app/oracle

启动docker

docker-compose -f docker-compose-oracle.yml up -d

连接

• Connection Type: Basic
• Host: localhost
• Port: 1521
• Service Name: xe
• Username: system
• Password: oracle

基本概念

• 数据库: Database
• 实例: Instance
• 表空间: Tablespace(schema)
• 用户: User
• 表: Table

---

• Database-Instance: OneToMany
• User-Tablespace: ManyToMany
• Tablespace-Table: OneToMany

基本操作

自增序列

-- 查看所有序列
SELECT * FROM user_sequences;
-- 创建序列
CREATE SEQUENCE USER_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
-- 删除指定序列
DROP SEQUENCE USER_SEQ;

创建表

-- TESTSPACE 表空间, USER 表
CREATE TABLE "TESTSPACE"."USER" (
"ID" NUMBER(20,0) NOT NULL,
"USERNAME" VARCHAR2(128) NOT NULL,
"CREATE_TIME" TIMESTAMP(6) NULL,
"UPDATE_TIME" TIMESTAMP(6)  NULL, 
PRIMARY KEY ("ID")
);

COMMENT ON table USER IS '用户表';
COMMENT ON COLUMN "TESTSPACE"."USER"."ID" IS '主键';

自增主键

1. 基于序列

INSERT INTO PERSON(ID, NAME) VALUES (USER_SEQ.NEXTVAL, 'admin');
commit ;

2. 基于序列+触发器

CREATE OR REPLACE TRIGGER USER_TRG BEFORE INSERT ON USER FOR EACH ROW 
BEGIN
    SELECT USER_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;