[Oracle] 오라클 - DDL (테이블 생성 CREATE/가상컬럼/서브쿼리)

[Oracle]
오라클 - DDL (테이블 생성 CREATE/가상컬럼/서브쿼리)

---

✔️ 데이터 정의 언어(DDL)

데이터 정의 언어(DDL) 및 데이터 타입

: 데이터 베이스 구조, 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용

: 롤백 불가. (취소 불가)

: 외부 스키마 명세를 정의한다.

: 데이터베이스의 논리, 물리적 구조와 사상등을 정의한다.

: 스키마에 사용되는 제약 조건에 대한 명세도 가능

 

✨VARCHAR2(n)  **** '2' 꼭 넣어주기!! 오라클은 2 써줘야한다. 

❌ CHAR 은 짧은 데이터 입력하면 공백으로 채워서 사용 X

 

 

 

✔️ 데이터 타입

-- 데이터 타입 정보 확인

SELECT DATA_TYPE, DATA_LENGTH, CHAR_LENGTH, CHAR_USED
         FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME ='테이블명';

 

테이블 : 관계형 데이터베이스를 구성하는 기본 데이터 구조

행과 열의 구조를 가지고, 입력, 수정, 삭제, 추출을 한다.

권한이 있어야 접근 및 생성이 가능하다.

 

테이블 생성 : 테이블에 대한 구조를 정의하고, 저장공간을 할당하는 과정이다.

테이블 명은 객체 이름과 중복되지 않아야한다. 컬럼명 중복 지정 불가~~~

 

✨ 기본키 : 유일성, 검색하기 용이한 것. 중복 절대X 널값 X

✨ UNIQUE : 여러개 설정이 가능

 

 

 

✔️ 테이블 생성 및 수정 삭제

-- 테이블 생성 (기본형식)

CREATE TABLE 테이블명
         (
              컬럼명  타입[(크기)]  [제약조건]
              ,컬럼명  타입[(크기)]  [제약조건]
          );

💡 VARCHAR2 : 4000바이트까지 저장 가능
NUMBER : 최대 유효 자리수 30자
DATE : 시분초까지 저장 가능 (사이즈 명시 안함)

 

 

-- 기본키는 자동으로 NOT NULL

CREATE TABLE test 
(   
    num NUMBER(10) PRIMARY KEY NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    city VARCHAR2(30)
);

 

 

-- 테이블 목록 확인

SELECT * FROM tab;

 

 

-- 컬럼 및 컬럼 타입 확인

DESC test;
SELECT * FROM col WHERE tname = 'TEST';
SELECT * FROM cols WHERE table_name = 'TEST';

 

 

 

✔️ 테이블 생성 - 가상 컬럼 생성

✨ 가상 컬럼은 왜 만들까?

디스크에 저장되지 않는 수식 등을 저장하기 위해서 가상 컬럼을 만든다.

(ex) 점수 평균 값 구하기 or 합계 구하기 등 )

가상 컬럼은 값을 추가하거나 수정할 수 없다. (수식만 저장함)

 

✨ 컬럼은 컬럼이지만, 수식을 저장할 수 있는 컬럼

but, 무조건 만들 수 있는건 아니다. 

CREATE TABLE demo(
		hak VARCHAR2(30) PRIMARY KEY,
		name VARCHAR2(30) NOT NULL,
		kor NUMBER(3) NOT NULL,
		eng NUMBER(3) NOT NULL,
		mat NUMBER(3) NOT NULL,
		tot NUMBER(3) GENERATED ALWAYS AS (kor+eng+mat) VIRTUAL,
		ave NUMBER(3) GENERATED ALWAYS AS ((kor+eng+mat)/3) VIRTUAL			
	);
        DESC demo;

💡 평균이랑 합계는 가상 컬럼으로 만들었다.

** GENERATED ALWAYS : 구분용 키워드다. AS 뒤에 수식 집어넣음

 

 

 

🔒 문제

가상 컬럼 만들 때, CASE WHEN 사용도 가능하다.

CREATE TABLE test3 (
        empNo VARCHAR2(30) PRIMARY KEY,
        name VARCHAR2(30) NOT NULL,
        pay NUMBER(10) NOT NULL,
        tax NUMBER(10) GENERATED ALWAYS AS (
            TRUNC( 
                CASE 
                    WHEN pay >= 3000000 THEN pay * 0.03
                    WHEN pay >= 2000000 THEN pay * 0.02
                    ELSE 0
                END, -1
            )
        )
    );

 

 

 

✔️ 테이블 생성 - 서브쿼리로 테이블 생성

✨ 서브쿼리로 생성 왜?

이미 존재하는 테이블을 가지고 테이블을 만들 때 서브쿼리를 이용한다.

 

-- 기본형식

CREATE  TABLE  테이블명 [(컬럼명, 컬럼명,...)] AS subquery;

💡 테이블명 과 컬럼을 주고 뒤에 AS + 서브쿼리를 작성한다.

 

 

-- 테이블 구조 및 값 복사

CREATE TABLE emp1 AS
		SELECT empNo, name, sal, bonus, sal+bonus pay (별명줘야함) FROM emp --에러
				(컬럼명 규칙 위반)
				--NOT NULL을 제외한 다른 제약 조건은 복사되지 않음

💡 서브쿼리로 이미 존재하는 테이블의 값을 가져올 때, 컬럼명 규칙 주의해야한다.
1) 컬럼명 규칙 : sal+bonus 와 같은 컬럼은 별명을 붙여야한다.
2) 다른 제약조건들은 복사되지 않는다. (NOT NULL만 복사된다.)

 

 

-- 조건에 맞는 데이터만 복사

    CREATE TABLE emp2(사번, 이름, 생년월일) AS 
    	SELECT empNo, name, TO_DATE(SUBSTR(rrn,1,6), 'RRMMDD') FROM emp
        	WHERE TO_DATE(SUBSTR(rrn,1,6), 'RRMMDD') >= '2000-01-01';

 

 

-- 데이터 빼고 구조만 복사하고 싶을 때

 CREATE TABLE emp3 AS
                SELECT * FROM emp WHERE 1 = 0;
				--거짓이라서 데이터 안나옴

💡 WHERE 1 이 = 0 이 아니기 때문에 거짓이라서 하나의 데이터도 복사되지 않는다.