Oracle SQL 기본_11

CREATE SELECT : 기존 테이블을 복사하여 새 테이블 생성하기, 파생 테이블 생성하기

---

​1. CREATE-SELECT의 개념과 표현

​

1) CREATE-SELECT의 개념

​

CREATE-SELECT, 또는 CREATE TABLE- AS SELECT 는 기존에 존재하는 테이블로부터 파생 테이블을 생성할 수 있는 구문입니다. 단순히 빈 테이블을 생성(CREATE TABLE)하는 것 뿐만 아니라 기존 테이블이 가지고 있었던 자료도 함께 복사해 옵니다.(SELECT)

​

- 기존의 테이블 전체를 복사

- 기존 테이블의 일부만 복사

- 기존 테이블의 컬럼을 가공(변형)한 파생 테이블 생성

...

​

이러한 목적을 가지고 있을 때 CREATE TABLE AS SELECT ... 구문을 사용할 수 있습니다.

 

 

2) CREATE-SELECT의 표현

​

(1) 기본식 : 기존에 존재하는 테이블A 전체를 복사한 테이블X를 생성하는 경우

 

create table 테이블이름X
as select * from 테이블이름A;

 

 

(2) 기본식 : 기존에 존재하는 테이블A로부터

- 조건을 만족하는 레코드에 한하여

- 일부 컬럼, 또는 파생 변수를 만들어 이를 포함한 테이블X를 생성하는 경우

 

create table 테이블이름X
as select 컬럼이름A1,컬럼이름A2,... 
from 테이블이름A
where 조건;

 

 

 

* 이하의 예제에서는 hr 연습계정의 employees 테이블을 사용합니다.

 

2. 예제 : CREATE-SELECT 구문의 사용

​

1) 예제 : employees 테이블과 그 레코드 전체를 복사하여 employees_t 테이블을 만들기

​

기 존재하는 테이블을 복사하는 SQL 문장은 다음과 같이 간단하게 구성할 수 있습니다.

 

create table employees_t
as select * from employees;

위 SQL 문장을 실행하면 employees_t 테이블이 생성됩니다.

 

 

생성된 employees_t 테이블을 조회합니다.

 

select * from employees_t;

 

 

employees 테이블의 구조 뿐만 아니라, employees 테이블의 모든 레코드가 employees_t 테이블에 복사되었음을 확인할 수 있습니다.

 

 

2) 예제 : 기존에 존재하는 employees 테이블로부터 파생 테이블 employees_w 만들기

ⓐ 이름(first_name)에 알파벳 'L(l)'을 포함하는 직원들만 대상으로

ⓑ employees_w의 컬럼은 employee_id, 풀네임(first_name과 last_name), 전체 이메일 주소(이메일ID@tenebris.com)

​

파생컬럼을 가진 파생테이블 employees_w를 만드는 것이 본 예제의 목적입니다.

​

- 풀네임 : 컬럼 이름은 empname으로, 그 구성은 first_name과 공백, last_name을 합한 것으로 합니다.

- 전체 이메일 주소 : email 컬럼에 '@tenebris.com'을 붙입니다.

​

CREATE-SELECT 구문에서 SELECT-FROM절에 사용된 AS는 파생 컬럼에 이름을 붙여주는 역할을 합니다.

 

create table employees_w
as select employee_id, 
          first_name||' '||last_name as empname,
          email||'@tenebris.com' as fullemail
    from employees
    where lower(first_name) like '%l%';

 

위 SQL 문장을 실행(Ctrl+Enter)하면 테이블 employees_w가 생성됩니다.

​

​

생성된 employees_w 테이블의 전체 레코드를 조회해 봅니다.

select * from employees_w;

 

employee_id, 풀네임, 이메일주소 전체를 컬럼으로 갖는 파생테이블 employees_w가 의도한 대로 만들어졌음을 확인할 수 있습니다.

 

 

DELETE : 테이블의 데이터(행) 삭제하기, 행 지우기, 레코드 삭제

---

Oracle SQL Developer에는 삭제와 관련된 몇 가지의 명령어가 존재하지만, 그들 간에는 기능상의 차이점이 명확히 존재합니다. (DELETE, TRUNCATE, DROP)

* DELETE, TRUNCATE, DROP 명령어의 차이점에 대해서는 별도의 포스팅으로 다룹니다.

 

 

​1. DELETE의 개념과 표현

​

1) DELETE의 개념

​

DELETE는 지정한 조건을 충족하는 행(row)의 데이터를 삭제하는 명령어입니다. DELETE는 DML 명령어(데이터 조작 언어)에 해당합니다. 메모리 상에서는 행이 차지하고 있었던 공간이 남아 있고, 테이블의 용량은 줄어들지 않습니다.

​

(* DELETE로 행의 데이터만을 지운다고 테이블의 용량이 줄어들지는 않는 것입니다)

 

 

2) DELETE의 표현

​

(1) 기본식 : 테이블A의 모든 행 데이터 지우기

​

WHERE 조건절을 쓰지 않으면, 테이블이 포함하는 모든 행 데이터가 삭제됩니다.

 

delete from 테이블이름A;

 

 

(2) 기본식 : 테이블A에서 지정한 조건을 충족하는 모든 행 데이터 지우기

​

WHERE 조건절을 기술하면, 조건을 충족하는 행의 데이터만 삭제됩니다.

 

delete from 테이블이름A
where 조건;

 

 

 

* 이하의 예제에서는, hr 연습계정에 임의로 생성한 테이블 employees_t를 사용합니다.

 

 

2. 예제 : DELETE의 사용

​

먼저 CREATE TABLE 구문을 사용하여, employees 테이블 전체를 복사한 employees_t 테이블을 만들어 줍니다.

create table employees_t
as select * from employees;

위 SQL 문장을 실행하고 employees_t 테이블의 전체 레코드를 조회하면, employees와 마찬가지로 총 107개의 행을 보유한 테이블이 나타나게 됩니다.

 

 

1) 예제 : 조건을 만족하는 행 삭제하기

- employees_t 테이블에서

- 이름(first_name)에 알파벳 A(a)가 들어가는 모든 행을 삭제하기

 

조건(WHERE절)을 충족하는 행들만을 삭제해야 하므로 DELETE와 WHERE을 함께 사용해야 합니다.

대소문자 구분 없이 포함된 문자를 찾아내기 위해서는 LOWER() LIKE 또는 UPPER() LIKE를 사용합니다.

 

delete from employees_t
where lower(first_name) like '%a%';

위 문장을 실행(Ctrl+Enter)하면, 조건에 만족하는(이름에 알파벳 A 또는 a가 들어간) 모든 행이 삭제됩니다.

 

이름에 A(a)가 들어간 모든 행이 삭제되고 37개의 행만 남았습니다. 남겨진 행은 first_name 컬럼에 A나 a가 포함되지 않은 레코드들입니다.

 

 

2) 예제 : 테이블의 모든 행 삭제하기

- employees_t 테이블의 모든 행 삭제하기

​

모든 행을 삭제할 경우에는 DELETE 구문에 조건절을 붙이지 않으면 됩니다.

 

delete from employees_t;

위 문장을 실행하면, 오라클 SQL 디벨로퍼의 하단 스크립트 출력창에 전체 개수의 행이 삭제되었음을 알리는 메시지가 나타납니다.

 

'예제1)'에서 70개 행을 삭제하고도 남아있었던 37개의 행마저 삭제되었습니다.

 

 

SELECT FROM으로 employees_t 테이블의 전체 레코드를 조회해 보면,

테이블의 컬럼을 비롯한 구조는 남아 있지만 행 데이터는 삭제된 모습을 볼 수 있습니다.

 

 

 

TRUNCATE : 테이블의 모든 행(row) 데이터와 행 자체, 레코드 사용하고 있었던 저장공간도 삭제하기 (≠ TRUNC)

---

TRUNCATE는 DELETE보다 더 많은 것을 삭제하는 명령어입니다.

 

※ 주의 : TRUNCATE와 TRUNC는 다릅니다!

TRUNC는 숫자 데이터를 반올림, 버림하는 등 숫자를 다듬을 때 '버림'을 실행하는 함수입니다.

 

1. TRUNCATE의 개념과 표현

​

- 테이블의 모든 행(row)의 데이터 삭제

- 행 자체도 삭제

- 삭제된 행이 사용하고 있었던 저장공간을 반납함

- 테이블의 인덱스(index)도 삭제됨

​

DELETE는 조건을 만족하는 행 데이터를 삭제하는 데 그쳤던 DML 명령어이지만, TRUNCATE 행 전체의 데이터를 지울 뿐만 아니라 저장공간도 지워 버리는 역할을 합니다. 때문에, TRUNCATE 명령어는 테이블 용량을 줄이게 됩니다.

​

그러나 테이블 내의 행 데이터(레코드)를 지우더라도, 테이블 자체는 지워지지 않고 온전히 남아 있습니다.

테이블 자체를 없애 버리는 DROP 명령어와의 차이는, TRUNCATE 명령어는 테이블 자체를 남겨 둔다는 것입니다.

 

테이블A의 전체 행을 잘라내 버리는(TRUNCATE) 표현식은 다음과 같습니다.

 

truncate table 테이블이름A;

 

 

 

2. 예제 : TRUNCATE의 사용

​

* 이하의 예제에서는 hr 연습계정에 임의로 만든 employees_t 테이블을 사용합니다.

​

예제에 사용하기 위해 CREATE SELECT 명령어로 employees 테이블을 복사한 employees_t 테이블을 만들어 줍니다.

 

create table employees_t
as select * from employees;

 

 

1) 예제 : employees_t 테이블의 전체 행을 잘라 버리기

- TRUNCATE 사용

​

별도의 조건이나 옵션을 지정할 여지 없이 TRUNCATE TABLE 테이블이름을 입력하여 줍니다.

 

truncate table employees_t;

위 SQL 문장을 실행(Ctrl+Enter)하면, TRUNCATE는 '테이블이 잘렸다'는 표현을 사용하여 성공 여부를 알려 줍니다.

 

employees_t 테이블이 잘렸습니다.

​

모든 행이 성공적으로 잘려나갔는지 확인하고자, SELECT FROM 구문으로 employees_t 테이블을 조회합니다.

 

 

모든 행이 삭제되고 비어있는 테이블만 조회되었습니다.

 

DELETE, TRUNCATE 명령어 모두 employees_t 테이블은 보존이 됩니다.

​

비록 모든 행 레코드 데이터는 삭제되었지만, employees_t 테이블은 접속 탭에 여전히 남아 있으며, 각 컬럼의 이름이나 데이터 유형, 제약조건과 같은 테이블의 구조와 정의를 포함하여 빈 테이블 자체는 그대로 있습니다.

 

* 만약에 테이블 자체를 다 삭제하려면 DROP 명령어를 사용해야 합니다.

 

 

DROP : 테이블 삭제

---

1. DROP 의 개념과 표현

​

DROP TABLE은 테이블 자체를 삭제합니다. 행 레코드만 지우거나, 빈 테이블을 남겨두는 것이 없이 테이블 자체를 DBMS로부터 지워버리는 것이 DROP 명령어입니다.

​

- 테이블이 가진 모든 행 레코드 삭제

- 테이블의 컬럼, 제약조건, 데이터유형 정보 등 삭제

- 테이블리스트에서 테이블 지워짐

...​

DROP 테이블은 테이블 자체를 삭제하는 명령어입니다.

​

​

테이블A전체를 삭제해 버리는 SQL 문장을 표현하면 아래와 같습니다.

 

drop table 테이블이름A;

 

 

2. 예제 : DROP 을 사용한 테이블 삭제

​

* 이하의 예제에서는 hr 연습계정의 employees 테이블을 CREATE SELECT 구문을 사용하여 복사한 employees_t 테이블을 사용합니다.

​

employees_t 테이블이 접속 탭의 hr 연습계정이 보유한 테이블 목록에 나타나 있습니다.

​

이 emploees_t 테이블 전체를 삭제하는 SQL 문장은 다음과 같습니다.

 

drop table employees_t;

스크립트 출력 창에 'Table EMPLOYEES_T'이(가) 삭제되었습니다. 라는 메시지가 나타납니다.

​

이 시점에서 employees_t 테이블은 삭제되었습니다.

이후 커밋(commit;)을 하면 테이블 삭제가 확정이 될 것입니다.

 

 

좌측 접속 탭을 새로고침하거나, 오라클 SQL 디벨로퍼 종료 후 재접속하면, hr 연습계정 하위 메뉴인 테이블 목록에서 employees_t 테이블 항목이 사라져 있습니다.

 

 

정말로 employees_t 테이블이 삭제되었는지 확인하기 위해, SELECT FROM 구문으로 employees_t 테이블의 전체 레코드 조회를 시도해 보겠습니다.

select * from employees_t;

 

 

 

테이블 employees_t는 삭제되어 존재하지 않기 때문에, 테이블 자체가 조회되지 않고 오류(ORA-00942)가 발생합니다.

​

ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

00942. 00000 - "table or view does not exist"

*Cause:

*Action:

1행, 15열에서 오류 발생

 

 

 

DELETE, TRUNCATE, DROP 비교, 차이 : 데이터 삭제 vs 행 전체 삭제 vs 테이블 전체 삭제

---

DELETE, TRUNCATE, DROP 비교, 차이 : 데이터 삭제 vs 행 전체 삭제 vs 테이블 전체 삭제

 

Oracle SQL Developer에서 데이터의 삭제와 관련된 명령어로는 DELETE, TRUNCATE, DROP이 있었습니다. 이 명령어들은 '삭제'라는 점에서 공통점을 갖지만, 정확히 무엇을 삭제하느냐가 다릅니다. 즉, 삭제의 범주와 대상에 따라서 위 3가지의 명령어가 구분되고 있습니다.

 

 

삭제되는 범주의 크기를 비교해 보면, DELETE < TRUNCATE < DROP 의 관계가 있습니다.

​

DELETE는 행 일부 또는 전체 데이터를 지우고,

TRUNCATE는 행 전체 데이터 뿐만 아니라 행이 저장되어있던 공간과 인덱스도 삭제하며,

DROP은 인덱스, 행 데이터 뿐만 아니라 테이블 전체를 삭제합니다.

 

DELETE와 TRUNC는 정의된 테이블 자체는 (비어있더라도) 남아있지만, DROP은 테이블까지 완전히 제거되는 것입니다.

 

DELETE, TRUNCATE, DROP의 특성을 항목별로 비교하면 다음과 같습니다.

 

비교항목	DELETE	TRUNCATE	DROP
명령어 분류	DML	DDL	DDL
삭제범위	행 데이터 일부~전체	행 전체+인덱스 등	테이블 전체
행 데이터(레코드)	일부~전체 삭제	전체 삭제	전체 삭제
데이터 저장공간	남겨져 있음	삭제됨	삭제됨
인덱스	남겨져 있음	삭제됨	삭제됨
테이블 컬럼, 제약조건	남겨져 있음	남겨져 있음	삭제됨
테이블 자체	남겨져 있음	남겨져 있음	삭제됨

 

 

UPDATE : 레코드의 값을 갱신하기, 데이터 수정하기, 데이터 업데이트, 값 변경

---

1. UPDATE 명령어의 개념과 표현

​

1) UPDATE의 개념 이해

​

UPDATE는 이미 존재하는 테이블의 레코드 값을 갱신(변경, 수정)해 주는 DML 명령어입니다. 값을 업데이트 시켜 줍니다.

​

※ ALTER, CREATE, INSERT와 UPDATE의 차이점은 명확히 존재합니다.

​

- CREATE : 테이블을 정의, 생성(컬럼이름, 제약조건 등)

- INSERT : 테이블에 데이터(행, 레코드)를 입력

- UPDATE : INSERT로 이미 입력되어 있는 테이블 행의 값을 변경

​- ALTER : 컬럼이름, 제약조건 등 CREATE를 통해 만들어진 테이블의 정의를 변경

​

 

2) UPDATE의 표현

​

테이블A에서 조건에 맞는 특정 레코드에 대하여, 각 컬럼의 값을 입력하는 표현식은 다음과 같습니다.

update 테이블이름A
set 컬럼이름X=값1,
    컬럼이름Y=값2,
    ...
where 조건;

 

* 이 때, 컬럼에 입력되는 값들은 반드시 사전에 테이블에 정의된 제약조건에 위배되지 말아야 합니다.

 

 

* 이하의 예제에서는 hr 연습계정의 employees 테이블을 복사한 'employees_t' 테이블을 사용합니다.

 

 

2. 예제 : UPDATE의 사용

employees 테이블을 복사한 employees_t 테이블에서,

ⓐ 직원번호(employee_id)가 100~110번인 직원들에 한하여

ⓑ 이메일 주소를 '이메일ID@TENEBRIS.COM'으로 바꾸기

​

먼저 CREATE-SELECT 구문을 사용하여, employees 테이블 전체를 복사한(레코드까지 포함) employees_t 테이블을 만들어 줍니다. 만약 이미 같은 이름을 가진 테이블이 존재한다면, 다른 이름으로 바꾸어 주세요.

 

create table employees_t
as select * from employees;

​위 소스코드를 실행하면 employees_t 테이블이 생성되었다는 메시지가 스크립트 출력창에 나타납니다.

생성된 employeees_t 테이블을 조회하면, 다음과 같은 모습임을 확인할 수 있습니다.

employees 테이블과 같습니다.

 

select * from employees_t;

 

직원번호 100~110인 직원들에 대하여, email 컬럼을 이메일ID와 그 이하의 주소가 함께 표시되도록 업데이트하는 SQL 문장을 작성하면 아래와 같습니다.

 

update employees_t
set email=email||'@TENEBRIS.COM'
where employee_id between 100 and 110;

조건(employee_id between 100 and 110)을 충족하는 11개의 행이 UPDATE 되었습니다.

정말 업데이트 되었는지(값이 바뀌었는지) 확인하기 위해, 다시 employees_t 테이블의 전체 레코드를 조회해 보도록 합니다.

 

select * from employees_t;

 

UPDATE 구문에서 지정한 대로 email 컬럼의 값이 알맞게 변경된 모습을 볼 수 있습니다.