[SQL]DDL(1) - CREATE, INSERT, 제약조건

DDL(DATA DEFINITION LANGUAGE)

   : 데이터 정의 언어로써, Oracle에서 제공하는 객체(Object)를 새롭게 만들고(Create), 구조를 변경(Alter)하고,

     삭제(Delete)하는 명령문이다. 즉, 구조 자체를 정의하는 언어로, DB관리자(설계자)가 사용한다.

  - Oracle에서의 객체(DB를 이루는 구조물)

    → 테이블(TABLE), 사용자(USER), 함수(FUNCTION), 뷰(VIEW) 등

 

 

 

 

CREATE TABLE

  : CREATE TABLE 테이블명(

    컬럼명 자료형,

    컬럼명 자료형, ...

     );

 

  (1) 테이블(TABLE)

    : 행(ROW)과 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체 중 하나.

      모든 데이터는 테이블을 통해 저장된다.

      → 데이터를 조작하려면 반드시 테이블을 만들어야 한다.

 

 

 

  (2) 자료형

    : 문자(CHAR(크기)/VARCHAR2(크기)) → 크기는 BYTE 단위, 숫자와 영문자, 특수문자는 한 글자 당 1 BYTE이며,

      한글은 한 글자 당 3 BYTE이다.

   ① CHAR(바이트 수): 최대 2,000 BYTE까지 지정할 수 있고, 고정 길이가 정해져 있기 때문에 아무리 작은 값이

                              들어와도 나머지 부분을 공백으로 채워서 처음 할당한 크기를 유지한다.

                              CHAR는 주로 들어올 값의 글자 수가 정해져 있을 때 사용한다. ex) 성별(남/여), 주민등록번호

 

   ② VARCHAR2(바이트 수): 최대 4,000 BYTE까지 지정할 수 있고, 가변 길이라서 작은 값이 들어온 경우에 해당 값에

                                     맞춰 크기가 즐어든다.

                                     VARCHAR2는 주로 들어올 값의 글자 수가 정해져 있지 않을 때 사용한다. 

                                     ex) 이름, 아이디, 비밀번호 등

   ③ 숫자(NUMBER): 정수와 실수 상관 없이 NUMBER이다.

   ④ 날짜(DATE): 연/월/일/시/분/초 형식으로 시간을 지정한다.

 

-- 회원들의 데이터를 담기 위한 테이블 MEMBER를 생성하기
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PW VARCHAR2(20),
    MEMBER_NAME VARCHAR(21),
    MEMBER_BDATE DATE  -- SQL은 '_'으로 구분한다.(낙타봉표기법X)
);

 아이디와 비밀번호, 이름 같은 데이터들은 글자 수가 정해져 있지 않으므로 VARCHAR2를 사용한다. 아이디와 비밀번호는 대개 영어와 숫자로 입력하므로 한 글자 당 1바이트로 총 20글자가 된다. 그리고 이름은 보통 한글로 입력하므로 한 글자 당 3바이트로 총 7글자가 된다. 생일은 날짜이므로 DATE로 지정한다.

 

 

 

 (3) 데이터 딕셔너리 

   : 다양한 객체들의 정보를 저장하고 있는 시스템 테이블

   ① USER_TABLES

     : 현재 사용자 계정이 가지고 있는 테이블들의 전반적인 구조를 확인할 수 있다.

SELECT *
FROM USER_TABLES;

 

   ② USER_TAB_COLUMNS

     : 현재 사용자 계정이 가지고 있는 테이블들의 모든 컬럼 정보를 조회할 수 있다.

SELECT *
FROM USER_TAB_COLUMNS;

 

 

반응형

 

컬럼에 주석 달기

   : 컬럼에 대한 설명을 작성한다.

  - COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

 

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PW IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.MEMBER_BDATE IS '회원생일';

 첫 번째 구문으로 봤을 때, 컬럼명 'MEMBER_ID'은 '회원아이디'로 변경된다.

 

 

 

 

제약조건(CONSTRAINTS)

   : 원하는 데이터값만을 유지(데이터 무결성 보장)하기 위해 특정 컬럼마다 설정하는 제약이다.

  - 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 있는지 자동으로 검사한다.

  - 제약조건에는 NOT NULL과 UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY가 있다.

  - 컬럼에 제약조건을 부여하는 방식에는 컬럼 레벨 방식과 테이블 레벨 방식이 있다.

    ⊙ 컬럼 레벨 방식 : 컬럼명 자료형 제약조건

      - 제약조건 부여 시 제약조건명을 지정하는 방법

        CREATE TABLE 테이블명(

            컬럼명 자료형 제약조건1 제약조건2,

            컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,

            컬럼명 자료형, ...

        );

    ⊙ 테이블 레벨 방식 : 제약조건(컬럼명)

      - 제약조건 부여 시 제약조건명을 지정하는 방법

        CREATE TABLE 테이블명(

            컬럼명 자료형,

            컬럼명 자료형,

            ...,

            CONSTRAINT 제약조건명 제약조건(컬럼명)

        );

 

 (1) NOT NULL

   : 해당 컬럼에 반드시 값이 존재해야할 경우에 사용한다.(NULL값을 허용하지 않는다.)

  - 컬럼 레벨 방식으로만 작성할 수 있다.

 

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(21) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15)
);

 회원의 회원번호와 아이디, 비밀번호, 이름은 필수 입력 사항이므로 NULL값이 들어갈 수 없게 NOT NULL로 제약조건을 설정해놓는다.

 

INSERT INTO MEM_NOTNULL VALUES(2, NULL, NULL, '김철순', NULL, NULL);
-- 오류 발생.

INSERT INTO MEM_NOTNULL VALUES(2, 'user1', 'userpw1', '김철순', NULL, NULL);
-- 정상적으로 삽입됨.

 첫 번째 삽입 구문에서 MEM_ID와 MEM_PW 부분에 NULL값을 삽입하려 했으므로 NOT NULL이라는 제약조건에 걸려서 오류가 발생하였다.

 두 번째 삽입 구문에서 GENDER와 PHONE 부분에 NULL값을 삽입하였는데 정상적으로 실행되었다. 이는 GENDER와 PHONE에는 NOT NULL이라는 제약조건이 걸려있지 않기 때문에 잘 실행된 것이다.

 

 

 

 (2) UNIQUE

   : 컬럼에 중복값을 제한하는 경우에 사용한다.

  - 삽입/수정 시 기존 컬럼값 중 중복값이 존재하는 경우에 추가나 수정이 되지 않도록 한다.

  - 컬럼 레벨 방식과 테이블 레벨 방식 모두 가능하다.

 

 - 컬럼 레벨 방식

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR(21) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15)
);

 한 컬럼에 여러 제약조건을 걸 수 있는데, 이때 콤마(,) 없이 제약조건들을 나열하면 된다. 

 

 - 테이블 레벨 방식

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL, 
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(21) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    UNIQUE(MEM_NAME)
);

 NOT NULL 옆에 UNIQUE를 나열하는 것이 아니라 맨밑에다 'UNIQUE(컬럼명)'으로 작성한다.

 

INSERT INTO MEM_UNIQUE VALUES(1, 'userid', 'userpw', '박보리', NULL, NULL);
INSERT INTO MEM_UNIQUE VALUES(2, 'userid2', 'userpw2', '박보리', NULL, NULL);

 MEM_NAME(회원이름)에 UNIQUE라는 제약조건이 걸려있어서 같은 값이 중복으로 들어갈 수 없으므로 두 번째 INSERT 구문은 오류가 뜬다.

 

 

 

 (3) CHECK

   : 컬럼에 기록할 값에 대해 조건을 설정할 때 사용한다.

  - CHECK(조건식)

 

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR(15)
);

 성별을 입력할 때 GENDER에 '남' 또는 '여'만 들어올 수 있게 한다. 둘 중 하나가 들어올 때 GENDER에 입력되어야 하므로 IN()을 사용한다.

 

 

 

 (4) PRIMARY KEY(기본키)

   : 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 부여한다.(각 행들을 구분할 수 있는 식별자 역할)

  - 식별자의 조건 : 중복값X, NULL값X ⇒ NOT NULL + UNIQUE

  - 주의! 한 테이블 당 한 컬럼에만 지정할 수 있다.

 

CREATE TABLE MEM_PRIMARYKEY(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL ,  
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    MEM_DATE DATE NOT NULL
);

 한 테이블 당 한 컬럼에만 PRIMARY KEY를 부여할 수 있으므로 MEM_NO 이외의 컬럼에 PRIMARY KEY를 부여하면 오류('TABLE CAN HAVE ONLY ONE PRIMARY KEY')가 발생한다.

 제약조건에 제약조건명을 붙여줄 경우에는 '컬럼명 자료형' 옆에 'CONSTRAINT 제약조건명 제약조건'을 작성하면 된다. 위 구문에선 MEM_NO 컬럼의 제약조건 PRIMARY KEY에 대해 'MEM_PK'라는 이름을 붙여주었다. 이때 주의할 점은 한 번 정해진 제약조건명은 중복하여 작성할 수 없다. 만약 중복하여 작성하면 오류('NAME ALREADY USED BY AN EXISTING CONSTRAINT')가 발생한다.

 

 

 

 (5) FOREIGN KEY(외래키)

   : 해당 컬럼에 다른 테이블에 존재하는 값이 들어와야 하는 컬럼에 부여한다.

  - '다른 테이블을 참조한다'고 표현한다.(부모 테이블)

  - 참조된 다른 테이블의 데이터값만 들어올 수 있다.

     ex) MEMBER 계정에서 MEM 테이블의 GRADE_ID 컬럼에는 MEM_GRADE 테이블의 GRADE_CODE 컬럼에

          존재하는 데이터값들만 들어올 수 있다.

    ⇒ 외래키로 다른 테이블과의 관계를 형성할 수 있다.(JOIN) 

  - 컬럼명 자료형 REFERENCES 참조할 테이블명(참조할 컬럼명)

    → 참조할 컬럼명은 생략할 수 있으며, 이때 자동으로 참조할 테이블의 기본키에 해당하는 컬럼이 참조할

        컬럼으로 들어온다.

  - 주의! 참조할 컬럼의 타입과 외래키로 지정한 컬럼의 타입이 같아야 한다.

 

-- 회원 등급에 대한 데이터를 보관하는 부모 테이블 만들기
CREATE TABLE MEM_GRADE(
    GRADE_CODE CHAR(2) PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

-- MEM_GRADE 테이블에 값 넣기
INSERT INTO MEM_GRADE VALUES('G1', '일반회원');
INSERT INTO MEM_GRADE VALUES('G2', '우수회원');
INSERT INTO MEM_GRADE VALUES('G3', '특별회원');

-- 회원 정보를 보관하는 자식 테이블 만들기
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(21) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    MEM_DATE DATE NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE)  -- 외래키 지정
);

-- MEM 테이블에 값 넣기
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PW, MEM_NAME, GRADE_ID) 
VALUES(1, 'user1', 'userpw1', '소고귀', 'G1');
-- 정상적으로 삽입됨.

INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PW, MEM_NAME, GRADE_ID) 
VALUES(2, 'user2', 'userpw2', '소고귀', 'G4');
-- 오류(parent key not found) 발생. 부모 키에는 G1, G2, G3 밖에 없다.

INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PW, MEM_NAME, GRADE_ID) 
VALUES(3, 'user3', 'userpw2', '소고귀', NULL);
-- NULL값이 정상적으로 삽입됨.

 

   ① 부모 테이블(MEM_GRADE)에서 데이터값을 삭제할 경우

-- MEM_GRADE 테이블의 GRADE_CODE 컬럼에서 G1 삭제하기
DELETE FROM MEM_GRADE WHERE GRADE_CODE = 'G1';

 오류('child record found')가 발생한다. 그 이유는 자식 테이블에서 해당 값을 참조하고 있어서 삭제할 수 없기 때문이다. 기본적으로 삭제를 제한하는 옵션이 걸려있어 부모 테이블에서 삭제할 수 없는 것인데, 이는 외래키 제약조건을 부여할 때 삭제 제한 옵션을 따로 부여하면 된다.

 

   ② 부모 테이블의 데이터값이 삭제됐을 경우에 자식 테이블에는 어떻게 처리를 할 것인지

     : 외래키 삭제 옵션으로 지정할 수 있다.

     ⓐ ON DELETE SET NULL : 부모 데이터를 삭제할 때 해당 데이터를 사용하는 자식 데이터를 NULL로 바꾼다.

     ⓑ ON DELETE CASCADE : 부모 데이터를 삭제할 때 해당 데이터를 사용하는 자식 데이터를 같이 삭제한다.

     ⓒ ON DELETE RESTRICTED : 삭제 제한 기본 옵션

 

 

 

 (6) DEFAULT

   : 특정 컬럼에 들어올 값에 대해 기본값을 설정할 때 사용한다.(제약조건 아님.)

  - 제약조건과 함께 작성할 경우에는 DEFAULT를 먼저 설정한 후 제약조건을 작성해야 한다.

 

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,  
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

 MEM_DATE(회원가입일)에 회원 정보가 삽입된 때의 날을 입력하려고 할 땐 DEFAULT(기본값)를 SYSDATE로 설정한다. 

 

INSERT INTO MEM_CHECK VALUES(1, 'boat', 'boatyo12', '황조롱이', '남', '010-1234-5678', DEFAULT);
-- 정상적으로 삽입됨.

INSERT INTO MEM_CHECK VALUES(1, 'boat', 'boatyo12', '황조롱이', '남', '010-1234-5678');
-- 오류 발생. 컬럼에 값을 덜 입력함.

INSERT INTO MEM_CHECK(MEM_NO, MEM_ID, MEM_PW, MEM_NAME, GENDER) 
VALUES(1, 'boat', 'boatyo12', '황조롱이', '남');
-- 컬럼 형식을 지정해주면 지정되지 않은 컬럼에 대해서는 NULL값이 들어가고, 
-- DAFAULT가 설정되어 있다면 DEFAULT값이 들어간다.

 

 

 

 

서브쿼리를 이용한 테이블 생성

   : 테이블을 복사하는 개념이라고 생각하면 된다.

  - CREATE TABLE 테이블명

     AS 서브쿼리;

 

 - EMPLOYEE 테이블을 복사하기

CREATE TABLE EMPLOYEE_COPY
AS SELECT *
   FROM EMPLOYEE;

 컬럼과 컬럼값을 모두 복사하며, 제약조건 NOT NULL도 복사한다. 그러나 제약조건 PRIMARY KEY는 복사하지 않는다. 이를 통해 알 수 있는 것은 서브쿼리를 통해 테이블을 생성할 경우에 제약조건은 NOT NULL만 복사된다는 점이다.

 

 - EMPLOYEE 테이블의 컬럼값은 제외하고 컬럼 구조만 복사하기

CREATE TABLE EMPLOYEE_COPY2
AS SELECT *
   FROM EMPLOYEE
   WHERE 0 = 1;

 WHERE절에 FALSE 조건('0 = 1')을 넣어주면 컬럼 구조만 복사된다.

 

 - 전체 사원의 사번과 이름, 급여, 연봉을 조회한 결과를 복사한 테이블 만들기

CREATE TABLE EMPLOYEE_COPY3
AS SELECT EMP_NO, EMP_NAME, SALARY, SALARY * 12 연봉
   FROM EMPLOYEE;

 서브쿼리에서 SELECT절에 산술연산 또는 함수식이 기술된 경우에는 별칭을 붙여줘야 한다.