[Oracle] 테이블 생성 및 데이터 insert

• 크리에이트 테이블 생성 제약조건
  제약조건은 테이블에 데이터를 집어 넣을 때 제약조건을 설정해서 null이나 중복값이 들어가지 않는 등.

 

• DDA(DATA DEFINITION LANGUAGE) : 데이터 정의 언어
  객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DROP)하는 구문

 

• 오라클에서의 객체
   : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 
  패키지(PACKAGE), 트리거(TRIGGER), 동의어(SYNONYM),
  프로시져(PROCEDURE), 함수(FUNCTION), 사용자(USER)

-- 테이블 만들기
-- CREATE TABLE 테이블명(컬럼명 자료형(크기), 컬럼명 자료형(크기), ...)
CREATE TABLE MEMBER(
  MEMBER_ID VARCHAR2(20),
  MEMBER_PWD VARCHAR2(20),
  MEMBER_NAME VARCHAR2(20)
);

-- 컬럼에 주석 달기
-- COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';

-- 데이터 딕셔너리(데이터 사전) 시험
-- DDL수행 내용을 관리하는 메타 정보를 담고 있는 뷰
-- 직접적으로 데이터를 수정, 삭제, 저장하지 못하고 조회만 할 수 있음
SELECT * FROM USER_TABLES;

SELECT * FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'MEMBER';

DESC MEMBER;

-- 제약조건
-- 테이블 작성 시 각 컬럼에 대해 값 기록에 대한 제약조건을 설정할 수 있다.
-- 데이터 무결성을 보장하기 위한 목적
-- 입력/수정/삭제하는 데이터에 문제가 없는지 자동으로 검사하는 목적
-- PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY
SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM USER_CONS_COLUMNS;

-- NOT NULL : 해당 컬럼에 반드시 값이 기록되어야 하는 경우에 사용
--            삽입/수정 시 NULL값을 허용하지 않도록 컬럼 레벨에서 제한
CREATE TABLE USER_NOCONS( 
 USER_NO NUMBER,
 USER_ID VARCHAR2(20),
 USER_PWD VARCHAR2(30),
 USER_NAME VARCHAR2(30),
 GENDER VARCHAR2(10),
 PHONE VARCHAR2(30),
 EMAIL VARCHAR2(50)
);

INSERT INTO USER_NOCONS
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

SELECT * FROM USER_NOCONS;

INSERT INTO USER_NOCONS
VALUES (2, NULL, NULL, NULL, NULL,
         '010-1234-5678', 'hong123@kh.or.kr');

SELECT * FROM USER_NOCONS;

CREATE TABLE USER_NOTNULL (
  USER_NO NUMBER NOT NULL, -- 컬럼 제약조건 설정
  USER_ID VARCHAR2 (20) NOT NULL, 
  USER_PWD VARCHAR2 (30) NOT NULL,
  USER_NAME VARCHAR2 (20) NOT NULL,
  GENDER VARCHAR2 (10),
  PHONE VARCHAR2 (30),
  EMAIL VARCHAR2 (50)
);

INSERT INTO USER_NOTNULL
VALUES (1, 'user01', 'pass01', NULL, NULL,
        '010-1234-5678', 'hong123@kh.or.kr');

SELECT * FROM USER_NOTNULL;

SELECT *
FROM USER_CONSTRAINTS C1
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'USER_NOTNULL';

-- UNIQUE 제약조건 : 컬럼에 입력 값에 대해 중복을 제한하는 제약조건
--                 컬럼 레벨에서 설정 가능, 테이블 레벨에서 설정 가능
SELECT * FROM USER_NOTNULL;

INSERT INTO USER_NOTNULL
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

SELECT * FROM USER_NOCONS;

CREATE TABLE USER_UNIQUE (
  USER_NO NUMBER,
  USER_ID VARCHAR2(20) UNIQUE NOT NULL,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

INSERT INTO USER_UNIQUE
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UCC.CONSTRAINT_NAME = 'SYS_C0070759';

SELECT * FROM USER_UNIQUE;

CREATE TABLE USER_UNIQUE2 (
  USER_NO NUMBER,
  USER_ID VARCHAR2(20),
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  UNIQUE (USER_ID)  -- 테이블 레벨에서 제약조건 설정
);

INSERT INTO USER_UNIQUE2
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UNIQUE2
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

-- 두 개의 컬럼을 묶어서 하나의 UNIQUE 제약조건 설정
-- 테이블 레벨에서만 설정 가능

CREATE TABLE USER_UNIQUE3 (
  USER_NO NUMBER,
  USER_ID VARCHAR2(20),
  USER_PWD VARCHAR2(30),
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  UNIQUE (USER_NO, USER_ID)  -- 테이블 레벨에서 제약조건 설정
);

INSERT INTO USER_UNIQUE3
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UNIQUE3
VALUES (2, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

--INSERT INTO USER_UNIQUE3
--VALUES (1, 'user01', 'pass01', '홍길동', '남',
--        '010-1234-5678', 'hong123@kh.or.kr');

SELECT
       UC.TABLE_NAME
     , UCC.COLUMN_NAME
     , UCC.CONSTRAINT_NAME
     , UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UCC.CONSTRAINT_NAME = 'SYS_C007062';

-- 제약조건에 이름 설정
CREATE TABLE CONS_NAME (
  TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_TEST_DATA1 NOT NULL,
  TEST_DATA2 VARCHAR2(20) CONSTRAINT UN_TEST_DATA2 UNIQUE,
  TEST_DATA3 VARCHAR2(30),
  CONSTRAINT UN_TEST_DATA3 UNIQUE(TEST_DATA3)
);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CONS_NAME';

-- CHECK 제약조건 : 컬럼에 기록되는 값에 조건 설정을 할 수 있음
-- CHECK (컬럼명 비교연산자 비교값)
-- 주의 : 비교값은 리터럴만 사용할 수 있음, 변하는 값이나 함수 사용 못함
CREATE TABLE USER_CHECK(
  USER_NO NUMBER,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

INSERT INTO USER_CHECK
VALUES (1, 'user01', 'pass01', '홍길동', '남',
        '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_CHECK
VALUES (2, 'user02', 'pass02', '이순신', '남자',
        '010-5678-9012', 'lee123@kh.or.kr');

SELECT *
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.CONSTRAINT_NAME = 'SYS_C007067';

CREATE TABLE TEST_CHECK (
  TEST_NUMBER NUMBER,
  CONSTRAINT CK_TEST_NUMBER CHECK(TEST_NUMBER > 0)
);

INSERT INTO TEST_CHECK
VALUES (10);
INSERT INTO TEST_CHECK
VALUES (-10);

CREATE TABLE TBL_CHECK(
  C_NAME VARCHAR2(10),
  C_PRICE NUMBER,
  C_LEVEL CHAR(1),
  C_DATE DATE,
  CONSTRAINT CK_C_NAME CHECK(C_PRICE >= 1 AND C_PRICE <= 99999),
  CONSTRAINT CK_C_LEVEL CHECK(C_LEVEL = 'A' OR C_LEVEL = 'B' OR C_LEVEL = 'C'),
  CONSTRAINT CK_C_DATE CHECK(C_DATE >= TO_DATE('2016/01/01', 'YYYY/MM/DD'))
  
);

-- 회원 가입용 테이블 생성(USER_TEST)
-- 컬럼명 : USER_NO(회원번호)
--        USER_ID(회원아이디) -- 중복 금지, NULL 값 허용 안함
--        USER_PWD(회원 비밀번호) -- NULL값 허용 안함
--        PNO(주민등록번호) -- 중복 금지, NULL값 허용 안함
--        GENDER(성별) -- '남' 혹은 '여'로 입력
--        PHONE(연락처)
--        ADDRESS(주소)
--        STATUS(탈퇴여부) -- NOT NULL, 'Y' 혹은 'N'으로 입력
-- 각 컬럼에 제약조건 이름 부여 (본인의 규칙에 따라)
-- 5명 이상 회원 정보 INSERT
-- 각 컬럼별로 괄호 안에 있는 텍스트를 이용해 코멘트 생성
CREATE TABLE USER_TEST(
  USER_NO NUMBER,
  USER_ID VARCHAR(20) UNIQUE NOT NULL,
  USER_PWD VARCHAR(20) NOT NULL,
  PNO VARCHAR(30) UNIQUE NOT NULL,
  GENDER VARCHAR(10) CHECK(GENDER IN ('남', '여')),
  PHONE VARCHAR(30),
  ADDRESS VARCHAR(100),
  STATUS VARCHAR(10) NOT NULL CHECK(STATUS IN ('Y', 'N'))
);

INSERT INTO USER_TEST
VALUES (1, 'wooah01', 'pass01', '940110-2222222', '여', '010-9999-9999', '서울특별시 강남구 테헤란로 14길 6 남도빌딩 1F', 'Y');

INSERT INTO USER_TEST
VALUES (2, 'jjong02', 'pass02', '930303-1111111', '남', '010-8888-8888', '서울특별시 강남구 테헤란로 14길 6 남도빌딩 2F', 'N');

INSERT INTO USER_TEST
VALUES (3, 'hojung03', 'pass03', '210112-4444444', '여', '010-7777-7777', '서울특별시 강남구 테헤란로 14길 6 남도빌딩 3F', 'N');

INSERT INTO USER_TEST
VALUES (4, 'wook03', 'pass04', '950731-1212121', '남', '010-6666-6666', '서울특별시 강남구 테헤란로 14길 6 남도빌딩 4F', 'N');

INSERT INTO USER_TEST
VALUES (5, 'bumbum03', 'pass05', '910112-1313131', '남', '010-5555-5555', '서울특별시 강남구 테헤란로 14길 6 남도빌딩 5F', 'Y');