오라클 DBMS 정리 02

06 Nov 2018

Reading time ~6 minutes

오라클 DBMS 정리 02 - DDL, DCL

테이블 생성

-- 테이블명은 어떤 컬럼을 갖고 있는지 유추 가능한 연관있는 이름이어야 함
CREATE TABLE 테이블명(    
    컬럼명 데이터형(크기),
    ...
);

가장 간단한 형태의 테이블 생성

-- 계정이 가지고 있는 테이블의 목록보기
-- tab을 dictionary라고 함
SELECT tname FROM tab;        

-- 테이블 구조 조회 (sqlplus문)
DESC 테이블명

대소문자 섞어 사용해도 오라클이 모두 대문자화
- 입력되는 값들은 예외

02-01

BIN으로 시작하는 테이블은 휴지통에 있는 테이블들 PURGE RECYCLEBIN으로 삭제 가능

-- class4_info 테이블 생성
-- 번호, 이름, 나이, 시력, 주민번호, 이메일, 입력일 저장
-- 번호는 숫자 0~9999
-- 이름은 문자열
-- 나이는 숫자 0~999
-- 시력은 실수
-- 주민번호는 고정길이 문자열
-- 이메일은 가변길이 문자열
-- 단, 한글 5자까지
CREATE TABLE class4_info (
  num NUMBER(4),
  name VARCHAR2(15)        
  ssn CHAR(14),            
  age NUMBER(3),
  eye NUMBER(3,2),
  email VARCHAR2(50),
  input_date DATE
);

-- 생성된 테이블의 구조 보기 : sqlplus문
DESC Class4_info

-- 생성된 테이블 명 조회 : SQL문
SELECT tname FROM tab;

INSERT

INSERT INTO 테이블명(컬럼명, ... )
VALUES (값, ...);

테이블에 레코드를 삽입할 때 사용
데이터는 순차적으로 입력되지 않음(append나 add가 아님)
SUBQUERY로 한번에 여러행을 INSERT 가능
일반적으로 한번에 하나의 레코드를 추가
컬럼명은 생략 가능
- 생략을 하게되면 테이블의 컬럼의 순서대로 값을 모두 넣어야 함(권장하지 않음)
컬럼명은 테이블의 순서와 상관없이 정의가능
- 정수, 실수 : 그냥 값을 넣으면 됨(홑따옴표 없이)
- 문자열 : 고정이든 가변이든 홑따옴표을 이용해서 값을 넣음
  - ‘값’, ‘오늘은 화요일’
- 날짜 : 현재 날짜 입력시 SYSDATE, 다른 날짜 입력시 날짜형식의 문자열을 입력
  - SYSDATE의 경우 시분초의 정보도 들어감
  - ‘2018-10-24’ 시분초 없이 단순히 날짜만 바꿔서 넣고싶으면 날짜형식을 맞춰서
컬럼명은 대소문자 구분안함, 문자 입력값들은 대소문자 구분함

-- 컬럼명을 생략하는 경우 : 테이블의 컬럼 순서대로 값을 넣어야 함
INSERT INTO class4_info
VALUES (1, 'YOUNG', '900000-0000000', 29, 1.5, 'young@young.com', SYSDATE);

-- 컬럼명을 명시 안하면 생길 수 있는 오류
-- 문자열일 경우 이름과 이메일이 바뀌어서 입력돼도 오류인지 모를 수 있음
INSERT INTO class4_info
VALUES (2, 'sol@sol.com', '930815-0000000', 26, 0.8, 'sol', SYSDATE);

-- 컬럼명을 명시하는 경우 : 테이블의 컬럼 순서와 상관 없이 원하는 컬럼만 값을 넣을 수 있다(권장)
INSERT INTO class4_info (name, age, input_date)    
VALUES ('Sol', 26, SYSDATE);    
                    
-- INSERT할 때 컬럼을 생략하면 NULL이 들어감
-- NULL의 뜻은 숫자도 문자열도 아닌 사용할 수 없는 값 
-- NULL은 조회되지 않는다
-- 문자열은 컬럼을 생략하거나 홑따옴표 두개를 공백없이 붙여넣으면 NULL이 들어감
-- 숫자는 컬럼을 생략하는 경우에만 NULL이 들어감(0은 NULL이 아니다!)
INSERT INTO class4_info (age, name, email)
VALUES (30, 'Dong', 'dong@dong.com');

SELECT

SELECT   컬럼명, ...
FROM     테이블명

테이블에 원하는 컬럼의 모든 레코드를 조회할 때 사용
컬럼명은 테이블안에 존재하는 컬럼, 작성 시 테이블명 먼저 명시하고 컬럼명 작성하는게 편함
컬럼명을 모르고 *(asterisk)를 사용하면 모든 컬럼을 조회
- ‘*‘하고 컬럼명을 같이 사용할 순 없음
컬럼명은 테이블의 컬럼의 순서와는 관계가 없음

-- 모든 오라클 속성확인
show all

-- autocommit OFF 되어있음
-- autocommit이 보통 OFF, DML작업 후 COMMIT이 필요함

트랜잭션(Transaction)

DB 업무 처리의 단위
대상쿼리 : INSERT, UPDATE, DELETE
- 대상쿼리는 메모리에만 기록됨(File영역으로 바로 안씀)
- 때문에 비정상종료 시 insert 같은 기록이 레코드로 안남게됨
- 대상쿼리 작업을 다음에도 사용하고 싶다면 COMMIT
- COMMIT하면 Session 메모리영역에 있던 쿼리작업내용이 File영역에 쓰여지고, 모든 접속자에게 알려짐
- create는 대상쿼리 아니기 때문에 File영역에 바로 써버림
- 대상쿼리가 하나로 완료되는 경우와 대상쿼리가 여러개 묶여서 완료되는 경우가 있음
COMMIT과 ROLLBACK은 DCL(Data Control Language)
Transaction이 완료되면 COMMIT으로 완료명령을 내려야 함
- COMMIT이란 접속자 세션의 내용(메모리)을 File영역(HDD, SDD)에 기록하고 변경된 내용을 모든 접속자에게 통지하는 일
ROLLBACK은 접속자 세션의 내용을 취소하는 것
- COMMIT된 데이터는 ROLLBACK되지 않는다 (메모리에 남은게 없기 때문)
autocommit을 설정하면 퀴리문 하나로 트랜잭션이 완료됨
- exit으로 나가게 되면 정상종료되면서 commit됨

-- autocommit 사용 명령
SET autocommit ON

INSERT INTO Class4_info(num, name, ssn, age, eye, email, input_date)
VALUES(3, 'sol', '930815-1234567', 26, 0.7, 'sol@sol.com', SYSDATE);

INSERT INTO Class4_info (name, email, num, ssn, age, input_date, eye)
VALUES ('영근', 'oh@test.com', 4, '900000-0000000', 28, SYSDATE, 0.8);

COMMIT;
-- 위와 같은 경우 INSERT 2개가 하나의 트랜잭션을 이룬다 라고 말할 수 있다.

COMMIT을 하면 임시적인 메모리 데이터가 영속적인 파일 데이터로 저장됨
오라클에서 null은 아무 값이 없는 상태를 뜻함

02-02

연산자(오라클)

연산에 사용되는 예약된 부호들
산술연산자
- +, -, *, /, mod()
- mod() : 나눈 나머지를 구하는 함수
- SELECT의 조회컬럼에서 사용가능
붙임 연산자
- SELECT 조회컬럼에서 사용가능
관계 연산자
- >, <, >=, <=, =, !=(<>)
논리 연산자
- AND, OR, NOT
- AND나 OR은 관계연산자를 붙일 때 사용
- AND는 전항과 후항이 모두 참일 때만 참 반환
- OR은 전항 또는 후항이 참이면 참 반환
  - 전항 후항 모두 거짓일 때만 거짓 반환
문자열 연산자
- LIKE
- ’%’와 ‘_‘와 같이 씀
NULL 비교 연산자
- IS NULL, IS NOT NULL
포함 연산자
- IN(), NOT IN()

UPDATE

UPDATE 테이블명
SET 컬럼명 = 변경할 값, 컬럼명 = 변경할 값, ...
WHERE 컬럼명 = 기준값;

컬럼의 값을 변경할 때 사용
- Transaction 대상쿼리, 작업 후 COMMIT/ROLLBACK 필수
SUBQUERY 사용가능
기준값에 해당하는 모든 레코드의 컬럼값이 변경됨
- 0행 ~ n행이 변경이 됨
WHERE는 조건 설정
- 컬럼명 연산자 기준값 논리연산자 컬럼명 연산자 기준값
  - 연산자로 관계연산자가 들어옴(=, !=(<>), >, <, >=, <=)
  - 논리연산자로 여러 조건을 이을 수 있음

DELETE

-- 모든 레코드 삭제
DELETE FROM 테이블명;

-- 특정 레코드 삭제
DELETE FROM 테이블명
WHERE 컬럼명=기준값;

레코드를 삭제할 때 사용
- 쿼리로 컬럼의 값 하나를 없앨 순 없음(UPDATE를 해야 함)
Transaction 대상쿼리, 작업 후 COMMIT/ROLLBACK 필수
SUBQUERY 사용가능

TRUNCATE

TRUNCATE TABLE 테이블명;

DELETE보다 삭제 속도가 빠름
truncate는 테이블의 레코드를 절삭할 때 사용, rollback 불가능
- delete는 rollback이 가능
WHERE절 없음, 모든 레코드를 자를 때 사용
DELETE + COMMIT = TRUNCATE

02-03

ROLLBACK

ROLLBACK;

Transaction 취소 작업
- 메모리에서 작업한 내용을 다시 되돌리는 것
  - 직전 COMMIT 이후까지 되돌림
COMMIT된 데이터는 취소할 수 없다
SAVEPOINT와 함께 사용하면 특정 지점까지 취소시킬 수 있음

SAVEPOINT

SAVEPOINT 저장점명;
...
ROLLBACK TO 저장점명;

트랜잭션이 여러개의 쿼리로 이루어진 경우 SAVEPOINT를 사용
- 저장점들은 COMMIT되면 사라짐
- 같은 이름의 저장점을 생성하면 이전 저장점이 사라진다.
쿼리를 수행하기 전 설정
ROLLBACK에서 저장점을 지정하여 원하는 위치까지 되돌릴 수 있음

02-04

DROP

-- 테이블 삭제
DROP TABLE 테이블명;

-- Oracle Version 10g 부터는 DROP된 테이블은 휴지통으로 들어간다

-- 휴지통 내용보기(sqlplus문)
SHOW RECYCLEBIN

-- 휴지통을 비우기 전까지 .DBF(table space)의 크기는 변하지 않는다
-- 테이블 명을 조회하면 삭제된 테이블명(BIN~~~)이 조회된다, 다른 작업은 아무것도 못 함

-- 휴지통 비우기(sqlplus문)
PURGE RECYCLEBIN

-- 휴지통 비워야 DBF 크기가 줄어든다.

-- 휴지통에 버려진 테이블 복구(sqlplus문)
-- 복구할 땐 원본 테이블명이 없어야 복구됨
FLASHBACK TABLE 테이블명 TO BEFORE DROP; 

데이터베이스의 모든 객체를 삭제할 때 사용
DDL(Data Definition Language)
SHOW, PURGE, FLASHBACK은 sqlplus문(오라클 한정일 수 있음)

SELECT

SELECT    컬럼명, 컬럼명 alias, 컬럼명 연산자, 함수(컬럼명)
FROM      테이블명
WHERE     조건 - 모든 연산자가 올 수 있음
GROUP BY  그룹으로 묶을 컬럼 
ORDER BY  정렬

테이블에서 필요한 값 조회
- 원하는 값을 잘 뽑아내기위해 가장 잘 써야 함
SELECT/FROM 필수, WHERE절, GROUP BY절, ORDER BY절 (절은 생략가능)

ALIAS

컬럼명 alias
컬럼명 AS alias

-- 테이블 alias는 조인하는 경우 아니면 잘 안씀
FROM 테이블명 alias 

-- 부여된 alias는 WHERE절에서 바로 아래에 있는 WHERE절에서 사용할 수 없음

-- 같은 컬럼을 다른 컬럼명으로 여러개 쓸 수 있다
SELECT num, name, input_date AS i_date, input_date AS i_date2
FROM CLASS4_INFO;

컬럼명 또는 테이블명에 사용하는 별칭
쿼리 내에 긴 컬럼/테이블명을 짧게 줄일 수 있음
alias는 그 쿼리내에서만 사용
- 다음 쿼리까지 유지돼 반영되어 사용되진 않음

숙제풀이

오라클DBMS