SQL: Oracle DB "TABLE"의 CRUD

Beautify.log·2021년 12월 26일

Check NOT NULL Table foreign key oracle-db primary key sql unique key 무결성 시퀀스

Oracle DB

목록 보기

4/4

안녕하세요. 이번 포스팅에서는 Oracle DB의 TABLE에 대해 살펴보려고 합니다.

바로 테이블의 CRUD인데요. TABLE은 기본적인 저장의 단위로 ROW와 COLUMN으로 이루어져 있습니다.

테이블을 생성부터 내용의 CRUD, 무결성, 시퀀스에 대해 알아봅시다.

테이블의 CRUD

테이블 생성하기

SQL에서 테이블은 기본적인 자료 저장의 단위이기 때문에 테이블이 없다면 어떤 정보를 읽어오거나 값을 넘겨줄 수 없을 것입니다.

이전에도 이미 살펴보았지만 테이블 생성하는 방법을 다시 한 번 확인해보겠습니다.

CREATE TABLE TB_TEST(
	COL1 VARCHAR2(10 BYTE),
	COL2 VARCHAR2(10 BYTE),
	COL3 VARCHAR2(10 BYTE)
);

이렇게 CREATE를 사용해서 테이블을 만들어주었습니다. 내부에는 만들 컬럼이 들어가고 각 컬럼의 자료형을 지정해주었습니다.

컬럼 뒤에는 차지할 공간의 크기를 알려줍니다.

테이블 복제하기

테이블을 생성하여 데이터를 입력한 후에 원본 테이블을 잘못 건들거나 의도치 않게 실수하게 되면 중요한 정보가 손실될 수 있으므로, 어떤 테스트 작업을 할 때는 테이블을 복제하여 사용하는 것이 좋습니다.

이 때 테이블 전체를 복사할 수도 있고 테이블 템플릿만 복사할 수도 있습니다.

CREATE TABLE TB_EXAMPLE
AS SELECT * FROM TB_TEST;

이렇게 AS SELECT를 통해 원본 테이블을 복제해서 새로운 테이블로 만들어낼 수 있습니다.

데이터를 포함하지 않고 테이블을 복제하기 위해서는 AS SELECT 뒤에 성립하지 않는 조건의 조건문을 붙여줍니다.

CREATE TABLE TB_EXAMPLE
AS SELECT * FROM TB_TEST
WHERE 1 = 2;

또한 복제 후 컬럼명을 변경해줄 수도 있습니다.

CREATE TABLE TB_EXAMPLE(NAME, PHONE, EMAIL)
AS SELECT * FROM TB_TEST
WHERE 1 = 2;

이렇게 복제한 데이터를 옮길 테이블에 가져올 데이터에 대해 이름을 붙여주면 됩니다.

단, 원본 테이블에서 가져올 데이터의 이름 순서와 같아야 합니다.

테이블 수정

테이블을 수정할 때는 ALTER를 사용합니다. 이 때 어떤 테이블의 이름을 ~~로 바꾸겠다와 같은 형식으로 씁니다.

이름 바꾸기

alter table TB_TEST04
rename to TB_TEST99

컬럼을 추가해줄 수도 있습니다.

컬럼 추가하기

alter table TB_TEST99
add DEPT_NAME number(3);

이렇게 alter에서 add로 추가해줍니다.

컬럼 수정, 삭제

-- 컬럼 수정
alter table TB_TEST99
modify DEPT_NAME varchar2(20);

-- 컬럼 삭제
alter table TB_TEST99
drop column DEPT_NAME;

컬럼을 수정할 때는 alter...modify를 사용하고, 컬럼을 삭제할 때는 alter...drop을 사용합니다.

테이블 삭제

table을 삭제하고자 할 때는 drop table로 삭제할 수 있습니다.

drop table TB_TEST01;
drop table TB_TEST02;
drop table TB_TEST03;
drop table TB_TEST04;

이 때, purge recyclebin을 추가로 입력해주면 휴지통에 남지도 않습니다

그러므로 꼭 종요한 파일이 있는지 확인하며 작업을 진행해주세요!

컬럼의 CRUD

테이블에 값 추가, 원본 테이블 복제해서 값 넣기

create table DEFT(DEPTNO, DNAME, MGR, LOC)
as select DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
from DEPARTMENTS
where 1 = 2;

실습파일에서 DEPARTMENTS의 정보를 일부 가지고 있는 테이블을 틀만 가져와보겠습니다.

그리고 각각의 컬럼에 원하는 내용을 입력해주겠습니다. 내용을 입력할 때는 insert into 테이블명 values...와 같은 형식으로 입력합니다.

insert into DEFT(deptno, dname, mgr, loc)
values (10, '기획부', 100, 042);

insert into DEFT(deptno, dname)
values (20, '관리부');

insert into DEFT
values(30, 'IT부', 333, 02);

insert into DEFT(dname, deptno, mgr, loc)
values('경리부', 40, 200, 031)

총 네번째 행까지 만들어주고 각각의 컬럼에 데이터를 밀어넣어줍니다.

원래 4개의 컬럼을 만들어주었으나, 두번째와 같이 개수를 맞춰 쓰지 않으면 해당하지 않는 항목은 NULL로 반환됩니다.

insert into DEFT            --- not enough values
values(30, 'IT부', 40)

별도로 어떤 자리에 이런 값을 쓰겠다고 선언해주지 않은 경우 전체 컬럼에 해당하는 값을 넣어줘야 하며 그렇게 하지 않을 경우 not enough values가 반환됩니다

컬럼 삭제하기

delete
from DEFT
where DNAME = 100;

delete
from DEFT
where mgr is null;

컬럼을 삭제할 때는 delete를 사용합니다. 테이블에서 삭제할 컬럼을 명시해주고 delete해 주면 됩니다. 이 때, 조건을 붙여주면 해당하는 값만 삭제됩니다.

컬럼 수정하기

컬럼 수정시에는 update를 사용합니다.

update DEFT
set mgr = 300
where DEPTNO = 30;

update DEFT
set deptno = 50, mgr = 500
where DNAME = '영업부;'

update로 컬럼을 수정할 때 어떤 테이블을 수정할 것인지 먼저 선언해주고 그 다음 set을 통해 값을 조정해줍니다. 자바의 setter와 다소 닮아 있지요?

무결성

기본 개념

무결성이란 컬럼을 지정하는 성질을 말합니다. 이전에 우리가 조인이라는 개념을 살펴볼 때, 기준 테이블과 조인할 테이블에서 서로 중복되는 데이터를 조인해주었는데요.

이 때 Key라는 개념에 대해 보았습니다. 종류로는 Primary Key, Unique Key, Foreign Key가 있었고 추가적으로 CHECK와 NOT NULL이라는 개념도 있습니다.

정확한 정의를 한 번 살펴볼게요.

구분	정의
`Primary Key`	기본키, `NULL`(빈칸)이나 중복을 허용하지 않음.
`Unique Key`	고유한 값, `NULL`을 허용하지만 중복은 허용하지 않음.
`Foreign Key`	외래키, 테이블과 테이블을 연결해줌. 외래키로 설정된 컬럼은 `Primary Key`나 `Unique Key`로 설정되어 있어야 함.
`CHECK`	범위를 지정하여 사용, 지정된 값 이외에 `NULL`을 사용함
`NOT NULL`	빈칸을 허용하지 않음

`PRIMARY KEY`

PRIMARY KEY로 지정할 컬럼에 constraint 컬럼별칭 PRIMARY KEY로 만들어주면 해당 컬럼은 기본키가 됩니다.

create table TB_TEST1(
    PKCOL VARCHAR2(10), constraint PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR(20),
    COL2 VARCHAR(30)
);

insert into TB_TEST1(pkcol, col1, col2)
values ('aaa', 'AAA', 'BBB');

insert into TB_TEST1(PKCOL)
values ('bbb');

insert into TB_TEST1(pkcol, col1, col2)
values('ccc', 123, '456');

`UNIQUE KEY`

UNIQUE KEY로 지정할 컬럼에 constraint 컬럼별칭 unique로 만들어주면 해당 컬럼은 고유키가 됩니다.

create table tb_test(
    ukcol varchar2(10) constraint uk_test_0 unique,
    col1 varchar(20),
    col2 varchar(20)
);

insert into tb_test(ukcol, col1, col2)
values('AAA', '111', 123);

insert into tb_test(col1, col2)
values('111', 123);

`CHECK`

CHECK는 지정된 반환값만 사용하게 해줍니다. NULL을 허용합니다.

create table TB_CHECK(
    col1 varchar2(2),
    col2 varchar2(40),
    constraint chk_01 check(col2 > 0 and col2 <=10)
);

여기에서 col2에는 0보다 크고 10보다 작거나 같은 숫자만 들어올 수 있습니다.

`FOREIGN KEY`

다른 테이블과 연결해줄 때 외래키는 아주 중요한 역할을 합니다. 외래키를 지정하기 위해서는 우선 PRIMARY KEY를 가진 기준 테이블이 있어야 하며, 이와 연결해 줄 테이블에 외래키를 지정합니다.

이 때 외래키는 constraint 외래키_지정_테이블_별칭 foreign key(외래키로_지정할_컬럼명) references 기준테이블(기준테이블의_Primary_Key) 의 형식으로 지정합니다.

create table TB_DEPT(
    DEPARTMENT_ID varchar2(10),
    DEPARTMENT_NAME varchar2(20),
    LOCATION_ID number,
    constraint PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)
);

create table TB_EMP(
    ENPNO varchar2(10),
    ENAME varchar2(20),
    DEPARTMENT_ID VARCHAR2(10),
    constraint FK_EMP_TEST foreign key(DEPARTMENT_ID)
    references TB_DEPT(DEPARTMENT_ID)
);

시퀀스 `sequence`

기본 개념

시퀀스는 값이 순차적으로 자동 증가하는, 순번을 반환하는 데이터 베이스 객체입니다.
일반적으로 Primary Key에 중복을 방지하기 위해 사용합니다.

만약 게시판에 글이 하나씩 추가될 때마다 글마다 번호가 생긴다고 할 때 100번까지 글 번호가 생성되어 있다면 다음 글 번호는 101이 되도록 세팅해야 하겠죠?

이 때 기존의 글 번호 중에서 가장 큰 숫자에 1을 더해주는 방식의 로직을 만들어야 하는데 시퀀스를 사용하면 별도의 로직을 필요로 하지 않게 됩니다.

시퀀스 생성

시퀀스를 그럼 한 번 만들어보겠습니다. 생성문법은 CREATE로 테이블을 만드는 것과 동일하고 증감숫자, 시작숫자, 최소값과 최대값이 필요합니다.

CREATE SEQUENCE MY_SEQUENCE
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 100
NOCYCLE
CACHE;

가장 위에는 생성 문법으로 시퀀스를 생성하고 시퀀스명을 붙였습니다.
그 다음으로 증가폭을 설정해주고, 시작숫자와 최소값, 최대값을 설정합니다.
이 때 최대값에 도달하면 더이상 순환하지 않는 형태인 NOCYCLE로 지정해주었고, 메모리에 시퀀스 값을 미리 할당할지 여부를 CACHE로 지정하여 미리 할당하도록 지정했습니다.

기본적인 형태에 대해 정리해보면 다음과 같습니다.

CREATE SEQUENCE <시퀀스 이름>
INCREMENT BY <증감 숫자>
START WITH <시작 숫자>
MINVALUE <최소값> (또는 NOMINVALUE : defalut 설정, 증가일 때 1, 감소일 때 -1028)
MAXVALUE <최대값> (또는 NOMAXVALUE : default 설정, 증가일 때 1027, 감소일 때 -1)
CYCLE (또는 NOCYCLE, CYCLE설정시 최대값 도달하면 최소값부터 다시시작)
CACHE (또는 NOCACHE, 시퀀스값 메모리 할당 여부)

시퀀스 적용

시퀀스를 사용하기 위해 테이블을 하나 만들어줍시다.

CREATE TABLE TEST(
	BOARD_NUM NUMBER
)

이전에 만든 시퀀스를 적용하려면 INSERT INTO로 적용해야겠죠?

-- SEQUENCE
CREATE SEQUENCE MY_SEQUENCE
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 100
NOCYCLE
CACHE;

-- 적용하기
INSERT INTO BOARD_NUM
VALUES (MY_SEQUENCE.NEXTVAL)

시퀀스를 삽입할 때는 .NEXTVAL를 통해 값을 증가폭만큼 증가하여 갱신시켜줍니다.

시퀀스 조회하기

시퀀스를 듀얼타입으로 조회해봅시다. 듀얼은 임시 테이블이며 작업트리에 별도로 생성되지 않습니다.

값의 조회만 간단히 하고 싶을 때 사용할 수 있습니다.

다음과 같이 조회할 수 있습니다.

SELCET MY_SEQUENCE.CURRVAL FROM DUAL;	-- 해당 시퀀스의 값을 조회
SELECT * FROM MY_SEQUENCE;	-- 전체 시퀀스 조회

시퀀스를 조회할 때 CURRVAL를 사용하는데 이는 Current Value의 약어로 해당 시퀀스의 현재 값을 의미합니다. 전체 시퀀스를 조회할 때는 와일드카드를 써서 조회할 수 있습니다.

시퀀스 수정하기

시퀀스를 수정할 때는 ALTER 문법을 사용하며 구문 안에 나머지 정보들을 다시 작성해주면 됩니다. 기본형식과 사용 예제는 다음과 같습니다.

-- 시퀀스 수정 기본
ALTER SEQUENCE <시퀀스명>
INCREMENT BY <증가 폭>
NOMINVALUE 또는 MINVALUE <최소값> 
NOMAXVALUE 또는 MAXVALUE <최대값>
CYCLE 또는 NOCYCLE <사이클 설정>
CACHE 또는 NOCACHE <캐시 설정 여부>

-- 시퀀스 수정하기
ALTER SEQUENCE MY_SEQUENCE
INCREMENT BY 2
MINVALUE 2
MAXVALUE 100
NOCYCLE
CACHE;

시퀀스 삭제하기

시퀀스를 삭제하기 위해서는 테이블을 삭제해 주는 방법과 같은 방법을 적용하면 됩니다.

DROP SEQUENCE <시퀀스명>

이렇게하면 시퀀스가 삭제됩니다.

오늘 정리할 내용은 여기까지 입니다. 복습에 도움이 되시길 바랍니다!

Beautify.log

tried ? drinkCoffee : keepGoing;

이전 포스트

SQL: Oracle DB "TABLE"의 CRUD

Oracle DB

테이블의 CRUD

테이블 생성하기

테이블 복제하기

테이블 수정

이름 바꾸기

컬럼 추가하기

컬럼 수정, 삭제

테이블 삭제

컬럼의 CRUD

테이블에 값 추가, 원본 테이블 복제해서 값 넣기

컬럼 삭제하기

컬럼 수정하기

무결성

기본 개념

`PRIMARY KEY`

`UNIQUE KEY`

`CHECK`

`FOREIGN KEY`

시퀀스 `sequence`

기본 개념

시퀀스 생성

시퀀스 적용

시퀀스 조회하기

시퀀스 수정하기

시퀀스 삭제하기

SQL: Oracle DB "JOIN"과 "SUB QUERY"

0개의 댓글

SQL: Oracle DB "TABLE"의 CRUD

Oracle DB

테이블의 CRUD

테이블 생성하기

테이블 복제하기

테이블 수정

이름 바꾸기

컬럼 추가하기

컬럼 수정, 삭제

테이블 삭제

컬럼의 CRUD

테이블에 값 추가, 원본 테이블 복제해서 값 넣기

컬럼 삭제하기

컬럼 수정하기

무결성

기본 개념

PRIMARY KEY

UNIQUE KEY

CHECK

FOREIGN KEY

시퀀스 sequence

기본 개념

시퀀스 생성

시퀀스 적용

시퀀스 조회하기

시퀀스 수정하기

시퀀스 삭제하기

SQL: Oracle DB "JOIN"과 "SUB QUERY"

0개의 댓글

`PRIMARY KEY`

`UNIQUE KEY`

`CHECK`

`FOREIGN KEY`

시퀀스 `sequence`