230413 서른 여덟 번째 수업_SQL

DDL(DATA DEFINITION LANGUAGE):

데이터 언어
실제 데이터 값이 아닌 구조 자체를 정의하는 언어
주로 DB관리자, 설계자가 사용함

자료형

• 문자: CHAR(BYTE크기) | VARCHAR(BYTE 크기) => 반드시 크기 지정해줘야 함
  -CHAR: 최대 200BYTE까지 지정 가능
  고정길이(지정한 크기보다 더 적은 값이 들어와도 공백으로라도 채워서 처음 지정한 크키만큼 고정)
  고정된 데이터를 넣을 때 사용
  -VARCHAR2 : 최대 400BYTE까지 지정 가능
  가변길이(담긴 값에 따라 공간의 크기가 맞춰짐)
  몇글자가 들어올지 모를 경우 사용
• 숫자: NUMBER([BYTE크기]) => 크기지정을 거의 하지 않음
  크기지정을 할 겨우 22BYTE의 가변길이
• 날짜: DATE

CREATE:

객체를 생성하는 구문

1. 테이블 생성
   
   [표현식]

   CREATE TABLE 테이블명 (
   컬럼명 자료형(크기),
   컬럼명 자료형(크기),
   컬럼명 자료형
   ...
   )

CREATE TABLE MEMBER (
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    MEM_PWD VARCHAR(20),
    MEM_NAME VARCHAR(20),
    GENDER CHAR(3),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(50),
    MEM_DATE DATE
);

• 사용자가 가지고 있는 테이블 정보
  -데이터 딕셔너리: 다양한 객체들의 정보를 저장하고 있는 시스템 테이블 등
  -USER_TABLES: 이 사용자가 가지고 있는 테이블의 전반적인 구조를 확인할 수 있는 시스템 테이블

SELECT * FROM USER_TABLES;

-[참고] USER_TAB_COLUMNS: 이 사용자가 가지고 있는 테이블들의 모든 컬럼의 전반적인 구조를 확인할 수 있는 시스템

SELECT * FROM USER_TAB_COLUMNS;

2. 컬럼에 주석 달기(컬럼에 대한 설명):

주석을 수정하려면 주석내용을 변경 후 다시 실행하면 덮어쓰기됨

[표현법]

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

COMMENT ON COLUMN MEMBER.MEM_NO IS '회원번호';
COMMENT ON COLUMN MEMBER.MEM_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEM_PWD IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEM_NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.GENDER IS '성별(남,여)';
COMMENT ON COLUMN MEMBER.PHONE IS '전화번호';
COMMENT ON COLUMN MEMBER.EMAIL IS '이메일';
COMMENT ON COLUMN MEMBER.MEM_DATE IS '회원가입일';

테이블에 데이터를 추가시키는 구문

INSERT INTO 테이블명 VALUES();

INSERT INTO MEMBER VALUES(1,'user01','pass01','홍길동','남','02-1234-5678','hong@naver.com','23/4/13');
INSERT INTO MEMBER VALUES(2,'user02','pass02','남길순','여',null,NULL,SYSDATE);
INSERT INTO MEMBER VALUES(NULL,NULL,NULL,NULL,NULL,null,NULL,NULL);

제약조건 CONSTRAINTS:

-원하는 데이터값(유효한 형식의 값)만 유지하기 위해 특정 컬럼에 설정을 하는 제약
-데이터 무결성 보장을 목적으로 함.
-제약조건을 부여하는 방식 크게 2가지로 나눌 수 있음(1.컬럼 레벨 방식 2.테이블 레벨 방식)
NOT NULL은 오로지 컬럼 레벨 방식만 됨

• 종류:
  NOT NULL, UNIQUE, CHECK(조건), PRIMARY KEY, FOREIGN KEY
  
  

1. NOT NULL 제약조건 :

해당 컬럼에 반드시 값이 존재해야만 함(즉, 컬럼에 절대 NULL이 들어와서는 안됨)
삽입/수정시 NULL값을 허용하지 않도록 제한

-- 컬럼 레벨 방식: 컬럼명 자료형 제약조건
CREATE TABLE MEM_NOTNULL (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(50)
);

INSERT INTO MEM_NOTNULL VALUES(1, 'user01', 'pass011', '홍길동','남',null, null);
INSERT INTO MEM_NOTNULL VALUES(2, 'user02', null, '남길동','남',null, 'abc@google.com');
--바로 위에 식: NOT NULL 제약조건 위배되는 오류 발생

2. UNIQUE 제약 조건:

해당 컬럼에 중복된 값이 들어가서는 안되는 경우
컬럼값에 중복값을 제한하는 제약조건
삽입/수정시 기존에 있는 데이터 중복값이 있을 경우 오류 발생
-오류 구문은 제약조건명으로 알려줌

내가 정해준 이름으로 명시하며 오류구문 뜸
-특별히 제약조건명을 지정해주지 않으면 자동으로 숫자로 부여

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL UNIQUE,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --컬럼 레벨 방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, 
    GENDER CHAR(3),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(50)
);

--테이블 레벨 방식
CREATE TABLE MEM_UNIQUE2(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_NO), UNIQUE(MEM_ID)
); 

3. 제약조건 부여시 제약조건명까지 넣어주는 방법

-컬럼 레벨 방식
CREATE TABLE 테이블명(
컬럼명 자료형(크기) [CONSTRAINT 제약조건명] 제약조건,
컬럼명 자료형(크기),
...
);

-테이블 레벨 방식
CREATE TABLE 테이블명 (
컬럼명 자료형(크기),
컬럼명 자료형(크기),
[CONSTRAINT 제약조건명] 제약조건(컬럼명)
);

CREATE TABLE MEM_UNIQUE3(
    MEM_NO NUMBER CONSTRAINT MEMNO_NN NOT NULL,
    MEM_ID VARCHAR2(20) CONSTRAINT MEMID_NN NOT NULL,
    MEM_PWD VARCHAR2(20) CONSTRAINT MEMPWD_NN NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEMNAME_NN NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50),
    CONSTRAINT MEMID_UQ UNIQUE(MEM_ID)
);

4. CHECK(조건식) 제약조건:

해당 컬럼에 들어올 수 있는 값에 대한 조건을 제시해 줄 수 있다
해당 조건에 만족하는 데이터값만 입력하도록 할 수 있다.

-->>컬럼 레벨 방식
CREATE TABLE MEM_CHECK (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN('남','여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50)
);

-->>테이블 레벨 방식
CREATE TABLE MEM_CHECK2 (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_ID),
    CHECK(GENDER IN('남','여'))
);

5. PRIMARY KEY(기본키) 제약 조건:

테이블에서 각 행들을 식별하기 위해 사용될 컬럼에 부여하는 제약조건(식별자의 역할)
EX) 회원번호, 학번, 사원번호, 부서코드, 직급코드, 주문번호 등

PRIMARY KEY 제약조건을 부여하면 그 컬럼에 자동으로 NOT NULL + UNIQUE 제약 조건을 의미
-대체적으로 검색, 삭제, 수정 등에 기본키의 컬럼값을 이용함

-유의사항: 한 테이블당 오로지 한 개만 설정 가능

-- >> 컬럼 레벨 방식
CREATE TABLE MEM_PRIKEY (
    MEM_NO NUMBER CONSTRAINT MEMNO_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN('남','여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50)
);
-->>테이블 레벨 방식
CREATE TABLE MEM_PRIKEY2 (
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50),
    CONSTRAINT MEMID_NN2 UNIQUE(MEM_ID),
    CONSTRAINT GENDER_CH CHECK(GENDER IN('남','여')),
    CONSTRAINT MEMNO_PK2 PRIMARY KEY(MEM_NO)
);
  
CREATE TABLE MEM_PRIKEY4 (
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(50),
    PRIMARY KEY(MEM_NO, MEM_ID) -- 2개의 컬럼을 묶어서 PRIMARY KEY 제약조건 부여(복합키)
);
--2개의 컬럼값을 묶은 것이 고유해야 됨
  
INSERT INTO MEM_PRIKEY4 VALUES(2, 'USER02','PASS04','최동순','여',NULL,NULL); --UNIQUE 위배
INSERT INTO MEM_PRIKEY4 VALUES(NULL, NULL,'PASS02','김길순','여',NULL,NULL); -- NOT NULL위배
-- > PRIMARY KEY로 묶여있는 각 컬럼에는 절대 NULL을 허용하지 않는다

* 복합키

--복합키 사용 예(어떤 회원이 어떤 상품을 찜했는지 데이터를 보관하는 테이블)
*USERID, 상품ID
1, A
1, B
1, C
1 B -부적합: 양쪽 다 같은 값이 들어오면 PRIMARY KEY 위배
2, A
2, C
2, A -부적합

6. FOREIGN KEY(외래키) 제약 조건:

다른 테이블에 존재하는 값만 들어와야되는 특정 컬럼에 부여하는 제약 조건
--> 다른 테이블을 참조한다고 표현
--> 주로 FOREIGN KEY 제약 조건에 의해 테이블 간의 관계가 형성됨

-컬럼 레벨 방식

컬럼명 자료형 REFERENCES 참조할테이블명(참조할컬럼명)
컬럼명 자료형 [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명(참조할컬럼명)

-테이블 레벨 방식

FOREING KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)
[CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)

--> 참조할컬럼명 생략시 참조할테이블에 PRIMARY KEY로 지정된 컬럼으로 매칭

CREATE TABLE MEM2(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50),
    --GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) --컬럼 레벨 방식
    GRADE_ID NUMBER,
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) --테이블 레벨 방식
);
INSERT INTO MEM2 VALUES(1, 'user01','pass01','홍길동','남',null,null,null);
--외래키 제약조건이 부여된 컬럼에 기본적으로 NULL허용됨
INSERT INTO MEM2 VALUES(2, 'user02','pass02','홍길동','남',null,null,20);
INSERT INTO MEM2 VALUES(3, 'user03','pass03','홍길동','남',null,null,70);
--MEM_GRADE(부모테이블) -|--------<- MEM2(자식테이블)
                                
-- 이때 부모테이블에서 데이터값을 삭제할 경우 문제발생   
--자식테이블이 이미 사용하고 있는 값이 있을 경우
--부모테이블로부터 무조건 삭제가 안 되는 삭제 제한 옵션이 걸려있음

7. 삭제 옵션:

-부모테이블의 데이터 삭제 시 그 데이터를 사용하고 있는 자식테이블의 값을 어떻게 처리할 것인지
-자식테이블 생성시 외래키 제약조건 부여할 때 삭제옵션 지정가능

-ON DELETE RESTRICTED(기본값): 삭제제한옵션으로, 자식테이블이 데이터를 사용하고 있으면
부모데이터는 삭제 안 됨
-ON DELETE SET NULL: 자식테이블이 데이터를 사용하고 있으면 자식테이블의 값을 NULL로 변경하고
부모데이터 삭제
-ON DELETE CASCADE: 자식테이블이 데이터를 사용하고 있으면 자식테이블의 데이터(행)를 삭제하고
부모데이터도 삭제

CREATE TABLE MEM3(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남','여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE ON DELETE SET NULL
);
--GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE)--> MEM_GRADE테이블의 PRIMARY KEY와 외래키를 걸면 컬럼명을 안 써도 됨

8. DEFAULT 기본값:

컬럼을 선정하지 않고 INSERT시 NULL이 아닌 기본값을 INSERT하고자 할 때 세팅해둘 수 있는 값

컬럼명 자료형 DEFAULT 기본값 [제약조건]

CREATE TABLE MEMBER2(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_NAME VARCHAR2(20) NOT NULL,
    MEM_AVG NUMBER DEFAULT 0,
    HOBBY VARCHAR2(20) DEFAULT '없음',
    MEM_DATE DATE DEFAULT SYSDATE
);

SUBQUERY를 이용하여 테이블 복사 생성:

테이블 복사하는 개념

[표현식]

CREATE TABLE 테이블명
AS 서브쿼리;

--EMPLOYEE 테이블을 복제한 새로운 테이블 생성
CREATE TABLE EMPLOYEE_COPY 
AS SELECT *
    FROM EMPLOYEE;
--컬럼, 데이터값, 제약조건 같은 경우 NOT NULL만 복사됨
--DEFAULT와 COMMENTS는 COPY안 됨

CREATE TABLE EMPLOYEE_COPY2
AS SELECT EMP_ID, EMP_NAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE 1=0; --조건이 같은 데이터가 하나도 없다는 의미(데이터 안 가져온다는 뜻)
                --구조만 복사하고자할 때 쓰이는 구문
                
CREATE TABLE EMPLOYEE_COPY3
AS SELECT EMP_ID, EMP_NAME, SALARY, SALARY*12 연봉
    FROM EMPLOYEE;
--★서브쿼리 SELECT절에 산술식 또는 함수식 기술된 경우 반드시 별칭 부여해야함

테이블을 다 생성한 후 제약조건 추가:

ALTER TABLE 테이블명 변경할 내용;

-PRIMARY KEY: ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명);
-FOREIGN KEY: ALTER TABLE 테이블명 ADD FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼명)]
==> (참조할컬럼명)은 PRIMARY KEY일 때는 생략가능
-UNIQUE: ALTER TABLE 테이블명 ADD UNIQUE(컬럼명)
-CHECK: ALTER TABLE 테이블명 ADD CHECK(컬럼에 대한 조건식);
-NOT NULL: ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

-- EMPLOYEE_COPY 테이블에 PRIMARY KEY 제약조건 추가
ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY(EMP_ID);

-- EMPLOYEE 테이블에 DEPT_CODE에 외래키제약조건 추가
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT;

-- EMPLOYEE 테이블에 JOB_CODE에 외래키제약조건 추가
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(JOB_CODE) REFERENCES JOB;

--연습문제: DEPARTMENT 테이블에 LOCATION_ID에 외래키제약조건 추가 (LOCATION테이블의 LOCAL_CODE)
ALTER TABLE DEPARTMENT ADD FOREIGN KEY(LOCATION_ID) REFERENCES LOCATION;

--연습문제: EMPLOYEE_COPY 테이블에 EMP_ID와 EMP_NO의 컬럼에 COMMENT 넣어주기
COMMENT ON COLUMN EMPLOYEE_COPY.EMP_NO IS '회원번호';
COMMENT ON COLUMN EMPLOYEE_COPY.EMP_ID IS '회원아이디';