ORACLE SQL 데이터 조작, 정의 개념 정리

테이블의 데이터를 제어하고 조작하는 데 쓰이는 DML(Data Manipulation Language)와 DDL(Data Definition Language), 제약조건, 트랜잭션에 대해 알아보자.

테이블을 추가, 수정, 삭제하는 조작어

데이터 추가 : INSERT

• 열, 데이터 개수를 맞춰줘야 하고, 열 길이를 초과하지 않게 데이터를 입력해야 함.

INSERT INTO 테이블 이름(열1, 열2, 열3)
VALUES (열1 데이터, 열2데이터, 열3 데이터)

참고

• 날짜 데이터 입력 방법
  • 문자열: YYYY/MM/DD, YYYY-MM-DD
  • TO_DATE 함수 : TO_DATE('05/06/2021', 'DD/MM/YYYY')
  • SYSDATE : 데이터 입력 시점
• NULL입력방법 : '', NULL 입력

• VALUES 대신 서브쿼리로 데이터를 추가할 수 있다. 서브쿼리를 이용하면 SELECT 문으로 한번에 여러 행의 데이터를 추가할 수 있음.

INSERT INTO (열1, 열2, 열 3)
SELECT 열, 열, 열 
FROM 테이블 
WHERE  조건 ;
*주의 : VALUES 쓰면 안 됨. 

데이터 수정 : UPDATE

UPDATE 변경할 테이블 
SET 변경할 열 = 데이터, 변경할 열2 = 데이터2
WHERE 데이터 변경 대상 행 조건 

• 서브쿼리를 사용해 데이터 수정하기

UPDATE 변경할 테이블 
SET (변경할 열1, 변경할 열2) = (SELECT 문);

데이터 삭제 : DELETE

DELETE FROM 테이블이름
WHERE 삭제행 찾을 조건식;

전체 삭제: 
DELETE FROM 테이블 이름;

트랜잭션

• 뜻 : 데이터에 대한 하나의 논리적 실행 단계. 더이상 분할할 수 없는 최소 수행 단위.
• DB 트랜잭션이 안전하게 수행되는 것을 보장하기 위한 특징 : ACID 원자성, 일관성, 독립성, 지속성

참고

• DDL (CREATE, ALTER, DROP) : 자동완료(트랜잭션 시작, DDL구문 처리, 커밋)
• DML (INSERT, UPDATE, DELETE) : 자동완료X. (트랜잭션 시작, DML구문 처리, 커밋or롤백 명령어 필요) 만약 커밋과 롤백을 하지 않으면 임시 저장소에만 저장되고, 실제 DB에는 반영X

• 커밋과 롤백

COMMIT : 수정사항을 영원히 반영하고 싶을 때 사용. 커밋은 한 트랜잭션이 끝날 때마다 해주는 게 좋다. 예컨대 계좌이체 트랜잭션이 전체 수행되면 커밋해주는 방식

ROLLBACK : 마지막 커밋 직후로 돌아감. 커밋 완료한 것을 되돌릴 수는 없음. UPDATE한 것은 DELETE 해주는 방식으로 수정할 수밖에.

세션

• 의미 : DB접속-> DB 관련작업 수행 -> 접속 종료하기까지 전체 기간을 말함. 섹션별로 임시저장소가 있는데, 트랜잭션의 내용이 저장돼있음. SQLPLUS, 오라클 DEVELOPER를 열고 있으면 세션이 2개라는 식으로 표현.

• 읽기 일관성 : 어떤 DB가 변경 중일 때, 다른 세션에서는 변경 전 원래 데이터를 그대로 보여주는 것을 말함. 그들은 수정 중인 데이터를 볼 필요가 없기 때문. COMMIT을 해줘야 데이터가 반영됨.

LOCK

• 의미 : 트랜잭션이 완료되기 전까지 다른 세션에서 조작할 수 없도록 잠금을 걸어두는 기능

데이터 정의어(CREATE, ALTER, RENAME, TRUNCATE, DROP)

• 데이터 정의어 특징: 명령어를 수행하자마지 데이터베이스에 수행한 내용이 바로 반영된다. 즉, 바로 COMMIT 되기 때문에 ROLLBACK 실행취소가 불가능하다.

테이블 생성 : CREATE

테이블 이름 규칙 : 문자로 시작, 이름 30BYTE 이하, 같은 사용자의 테이블 이름은 중복 불가, 특수문자 $#_ 사용가능

CREATE TABLE 소유 계정. 테이블 이름(
열1이름 자료형,
열2이름 자료형,
);

• 기존 테이블 복사해서 새 테이블 생성

CREATE TABLE 새 테이블명
AS SELECT * FROM 복사할 테이블;

• 다른 테이블 일부 복사해 테이블 생성

CREATE TABLE 새 테이블명
AS SELECT * FROM 복사할 테이블 
WHERE 조건;

테이블 변경 : ALTER

• 테이블 열 추가 : ADD

ALTER TABLE 테이블명
ADD 열이름 자료형;

• 열 이름 변경 : RENAME

ALTER TABLE 테이블명
RENAME COLUMN 기존열이름 TO 새로운열이름;

• 열 자료형 변경 : MODIFY

ALTER TABLE 테이블명
MODIFY 열이름 새자료형;

• 열 삭제 : DROP

ALTER TABLE 테이블명
DROP COLUMN 열명;

• 테이블 이름 변경 : RENAME

RENAME 테이블명 TO 새테이블명;

• 테이블 데이터 삭제 : TRUNCATE

TRUNCATE TABLE 테이블명;

• 테이블 삭제 : DROP

DROP TABLE 테이블명; 
주의 : COMMIT 안 됨!!!

객체 종류

데이터 사전

• 데이터 사전 : DB를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블로 데이터베이스가 생성되는 시점에 자동으로 생성
• DB 메모리 , 성능, 사용자, 권한, 객체 등 중요 데이터 보관. 따라서 사용자는 직접 접근할 수 없고, 뷰를 통해 조회 가능.

인덱스

• 순서 번호 붙이기. 특정 열에 지정할 수 있다. PRIMARY KEY나 UNIQUE KEY일 경우 자동으로 인덱스가 생성된다.

    CREATE INDEX 인덱스 이름
    ON 테이블이름 (열 이름 1, ASC or DESC,
    열 이름 2, ASC or DESC);
    
    인덱스 삭제
    DROP INDEX 인덱스 이름;

뷰

• 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체를 뜻함
• 뷰를 만들면 일일이 SELECT문을 입력하지 않고도 쉽게 테이블을 조회할 수 있다. 따라서 SELECT문의 복잡도를 완화할 수 있다.
• 또한 테이블의 특정 열을 노출하지 않도록 설정할 수 있어 보안성도 확보할 수 있다.

생성
CREATE VIEW 뷰 이름 (열이름1, 열이름2)
AS SELECT문;

삭제
DROP VIEW 뷰 이름;

* WITH CHECK OPTION CONSTRAINT : 지정한 제약조건은 DML 가능
* WITH READ ONLY : 뷰의 열람만 가능하도록 

제약조건

제약조건 설정하기

• 테이블 생성 시 제약조건 설정하기

CREATE TABLE 테이블명(열이름 자료형 NOT NULL); 
(NOT NULL은 예시)

• 제약조건 확인

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

• 이미 생성한 테이블에 제약조건 설정하기

ALTER TABLE 테이블명MODIFY (열이름 NOT NULL);
단, 해당 열에 제약조건에 반하는 데이터가 있을 경우 
변경이 안 되므로 데이터를 수정한 후 제약조건을 설정해야 한다. 

• 제약조건 이름 변경하기

ALTER TABLE 테이블명 
RENAME CONSTRAINT 제약조건명 TO 새제약조건명;

• 제약조건 삭제

 ALTER TABLE 테이블명DROP CONSTRAINT 제약조건명;

제약조건 종류

• UNIQUE : 열에 저장할 데이터의 중복을 허용하지 않음. 단, NULL 저장은 가능하며, NULL은 해당사항 없음이라는 뜻이므로 중복가능,
• NOT NULL : 데이터의 공백을 허용하지 않음
• PRIMARY KEY : UNIQUE + NOT NULL 데이터의 중복을 허용하지 않으며 NULL도 허용하지 않음.
• FOREIGN KEY : 외래키. 아래 자세한 설명.

FOREIGN KEY

• 타 테이블의 PRIMARY KEY를 참조하겠다는 의미

  테이블 A의 특정 열을 참조하는 테이블 B의 경우, 테이블A는 부모키를, 테이블 B는 자식키를 보유하고 있다고 보면 된다. 이 경우 테이블 B의 FOREIGN KEY 열은 테이블 A의 PRIMARY KEY 값과 NULL 외의 값을 저장할 수 없음. 예컨대 테이블 A의 C열에 (10, 20, 30)이 있고, 테이블 B의 D열이 이를 참조한다면 D에는 (10, 20, 30)과 NULL만 들어올 수 있음.

    CREATE TABLE 테이블명 
    (열이름 열자료형 CONSTRAINT 제약조건이름 
    REFERENCES 참조테이블(참조할 열));

• 외래키 주의사항
  참조관계가 있을 때 삭제 : 자식키를 삭제한 뒤 부모 키를 삭제자식키를 NULL값으로 설정한 뒤 부모 키를 삭제한다 자식 테이블의 FOREIGN KEY 제약조건을 해제한다

CHECK

• 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용
• 테이블 생성 시 제약 조건

CREATE TABLE 테이블명 (
설정컬럼명 컬럼자료형 CONSTRAINT 제약조건이름 
CHECK(제약조건(EX.LENGTH(LOGIN_PWD) >3));

DEFAULT

• 특정 열에 저장할 값이 지정되지 않았을 경우 기본값을 지정
• 테이블 생성 시 제약 조건

CREATE TABLE 테이블명(
설정컬럼명 컬렴자료형 DEFAULT 제약조건(EX.'1234')
);

사용자 관리

• 실무에서 사용하는 여러 종류의 서비스는 한 사용자가 관리하기에는 데이터 분량이 너무 방대하거나 복잡해서 업무에 따라 사용자(데이터관리자)를 나눔.

• 사용자 생성

CREATE USER 사용자이름
IDENTIFIED BY 패스워드 ;

• 사용자 권한 부여

GRANT 권한명 TO 사용자이름;
WITH ADMIN OPTION : 다른 사용자에게 권한을 부여할 수 있는 권한도 함께 지급

• 객체 권한 부여

GRANT 객체 권한
ON 스키마, 객체 이름
TO 사용자 이름;

WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 지급 

• 권한 취소

REVOKE 시스템 권한 
FROM 사용자 이름 

• PW변경

ALTER USER 사용자명
IDENFITIED BY 새 비번;

• 사용자삭제

DROP USER 사용자명 ;