--테이블 생성구문
--CREATE TABLE 테이블 이름(컬럼이름 자료형(길이), 컬럼이름 자료형(길이) ...);
--테이블 이름, 컬럼이름은 대/소문자 구분이 없어서 일딴 대충 대문자로 씀[권장사항정도] 그리고 스네이크 표기를 사용한다
CREATE TABLE MEMBER_TBL(MEMBER_ID VARCHAR2(20), MEMBER_PW VARCHAR2(30), MEMBER_NAME VARCHAR2(20));
INSERT INTO MEMBER_TBL VALUES('USER01', '1231234', 'USER01');
INSERT INTO MEMBER_TBL VALUES('USER02', '1232321', 'USER02');
INSERT INTO MEMBER_TBL VALUES('USER03', '5082124', 'USER03');
INSERT INTO MEMBER_TBL VALUES('USER01', '5082124', 'USER04');
UPDATE MEMBER_TBL SET MEMBER_PW = '5555' WHERE MEMBER_ID = 'USER01';
SELECT FROM MEMBER_TBL;
--데이터 무결성을 위해 제약조건을 걸어야 함 ID 같은경우 중복 될 수없게 제한 OR NULL값을 허용하지 않기...
--제약조건
INSERT INTO MEMBER_TBL VALUES('USER05', NULL, 'USER05');
SELECT FROM MEMBER_TBL WHERE MEMBER_ID = 'USER01' AND MEMBER_PW = '5555';
--------------------------------------------------------------------NOT NULL--------------------------------------------------------------------
--NOT NULL
CREATE TABLE MEMBER_TBL_NN(MEMBER_ID VARCHAR2(20) NOT NULL, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20));
INSERT INTO MEMBER_TBL_NN VALUES('UESR01', '1234', '유저1');
--NULL 삽입하기. (불가능)
INSERT INTO MEMBER_TBL_NN(MEMBER_ID, MEMBER_NAME) VALUES('USER02', '유저2');
INSERT INTO MEMBER_TBL_NN VALUES('UESR01', NULL, '유저1');
SELECT FROM MEMBER_TBL_NN;
--------------------------------------------------------------------UNIQUE--------------------------------------------------------------------
--UNIQUE
--중복 삽입하기
--COLUMN LEVEL에서 삽입
CREATE TABLE MEMBER_TBL_UNQ(MEMBER_ID VARCHAR2(20) UNIQUE, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20));
INSERT INTO MEMBER_TBL_UNQ VALUES('UESR01', '1234', '유저1');
INSERT INTO MEMBER_TBL_UNQ VALUES('UESR02', '1234', '유저2');
INSERT INTO MEMBER_TBL_UNQ VALUES('UESR01', '1234', '유저3');
SELECT FROM MEMBER_TBL_UNQ;
--TABLE LEVEL에서 삽입
CREATE TABLE MEMBER_TBL_UNQ2(MEMBER_ID VARCHAR2(20), MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), UNIQUE(MEMBER_ID));
INSERT INTO MEMBER_TBL_UNQ2 VALUES('UESR01', '1234', '유저1');
SELECT FROM MEMBER_TBL_UNQ2;
--???
CREATE TABLE MEMBER_TBL_UNQ3(MEMBER_ID VARCHAR2(20) UNIQUE, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_PHONE CHAR(13) UNIQUE);
INSERT INTO MEMBER_TBL_UNQ3 VALUES('UESR01', '1234', '유저1', '010-4229-3332');
INSERT INTO MEMBER_TBL_UNQ3 VALUES('UESR02', '1234', '유저1', '010-4229-3332');
SELECT FROM MEMBER_TBL_UNQ3;
--아래처럼 만들 경우 아이디와 전화번호가 동시에 중복되는 제약조건이 형성됨 난감함
CREATE TABLE MEMBER_TBL_UNQ4(MEMBER_ID VARCHAR2(20), MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_PHONE CHAR(13), UNIQUE(MEMBER_ID, MEMBER_PHONE));
INSERT INTO MEMBER_TBL_UNQ4 VALUES('UESR01', '1234', '유저1', '010-4229-3332');
INSERT INTO MEMBER_TBL_UNQ4 VALUES('UESR01', '1234', '유저1', '010-4229-3334');
SELECT FROM MEMBER_TBL_UNQ4;
--이렇게 만들어야 하나 하나 테이블 레벨에서 삽입한 조건과 동일하게 만들 수 있음
CREATE TABLE MEMBER_TBL_UNQ4(MEMBER_ID VARCHAR2(20), MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_PHONE CHAR(13), UNIQUE(MEMBER_ID), UNIQUE(MEMBER_PHONE));
--NOT NULL + UNIQUE
CREATE TABLE MEMBER_TBL_UNQ5(MEMBER_ID VARCHAR2(20) UNIQUE NOT NULL, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_PHONE CHAR(13) UNIQUE);
INSERT INTO MEMBER_TBL_UNQ5 VALUES('USER01', '1234', '유저1', '010-4229-3332');
INSERT INTO MEMBER_TBL_UNQ5 VALUES('USER01', '1234', '유저1', '010-4229-3332');
INSERT INTO MEMBER_TBL_UNQ5 VALUES('USER02', '1234', '유저2', '010-4229-3334');
UPDATE MEMBER_TBL_UNQ5 SET MEMBER_PHONE = NULL WHERE MEMBER_ID = 'UESR02';
UPDATE MEMBER_TBL_UNQ5 SET MEMBER_ID = 'USER01' WHERE MEMBER_ID = 'USER02';
SELECT FROM MEMBER_TBL_UNQ5;
--------------------------------------------------------------------PRIMARY KEY--------------------------------------------------------------------
--PRIMARY KEY (기본키)
--TABLE당 1개만 사용가능한 고유식별자 역할, NOT NULL과 UNIQUE 의미를 가지고있음
CREATE TABLE MEMBER_TBL_PK1(MEMBER_ID VARCHAR2(20) PRIMARY KEY, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_PHONE CHAR(13) NOT NULL UNIQUE);
--TABLE LEVEL에서도 설정 가능
CREATE TABLE MEMBER_TBL_PK2(MEMBER_ID VARCHAR2(20), MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_PHONE CHAR(13) NOT NULL UNIQUE, PRIMARY KEY(MEMBER_ID));
INSERT INTO MEMBER_TBL_PK1 VALUES('USER01', '1234', '유저01','010-1111-1111');
INSERT INTO MEMBER_TBL_PK1 VALUES('USER01', '1234', '유저02','010-2222-2222');
INSERT INTO MEMBER_TBL_PK1 VALUES(NULL, '1234', '유저02','010-2222-2222');
SELECT FROM MEMBER_TBL_PK1;
--------------------------------------------------------------------FOREIGN KEY 참조제약조건 REFERENCES--------------------------------------------------------------------
--FOREIGN KEY : (외래키) 참조 무결 제약조건 (외래종의 유입을 막기위함)
--최소 조건이 UNIQUE일 필요가 있음
CREATE TABLE MEMBER_TABLE(MEMBER_ID VARCHAR2(20) PRIMARY KEY, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_NICKNAME VARCHAR2(20) UNIQUE, MEMBER_PHONE CHAR(13) UNIQUE);
INSERT INTO MEMBER_TABLE VALUES('USER01', '1234', '유저일', '저일이', '010-1111-1234');
INSERT INTO MEMBER_TABLE VALUES('USER02', '1234', '유저이', '유둘이', '010-2222-1234');
INSERT INTO MEMBER_TABLE VALUES('USER03', '1234', '유저삼', '저삼이', '010-3333-1234');
INSERT INTO MEMBER_TABLE VALUES('USER04', '1234', '유저사', NULL, '010-4444-1234');
SELECT FROM MEMBER_TABLE;
--FOREIGN KEY - REFERENCES
CREATE TABLE BOARD1(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20) REFERENCES MEMBER_TABLE(MEMBER_ID), BOARD_DATE DATE);
INSERT INTO BOARD1 VALUES(1, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', 'USER01', SYSDATE);
INSERT INTO BOARD1 VALUES(2, '헤리포터', '천둥 번개 속 헤그리드가 어두운 문 앞에 서 ...', 'USER02', SYSDATE);
INSERT INTO BOARD1 VALUES(3, '겨울왕국', '엘사 왜 이리도 나를 외롭게 두는거야 ...', 'USER03', SYSDATE);
INSERT INTO BOARD1 VALUES(4, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', 'USER04', SYSDATE);
SELECT FROM BOARD1;
--TABLE LEVEL에서 참조키 설정하기
CREATE TABLE BOARD2(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20), BOARD_DATE DATE, FOREIGN KEY(BOARD_WRITER) REFERENCES MEMBER_TABLE(MEMBER_ID));
INSERT INTO BOARD2 VALUES(1, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', 'USER01', SYSDATE);
INSERT INTO BOARD2 VALUES(2, '헤리포터', '천둥 번개 속 헤그리드가 어두운 문 앞에 서 ...', 'USER02', SYSDATE);
INSERT INTO BOARD2 VALUES(3, '겨울왕국', '엘사 왜 이리도 나를 외롭게 두는거야 ...', 'USER03', SYSDATE);
INSERT INTO BOARD2 VALUES(4, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', 'USER04', SYSDATE);
SELECT FROM BOARD2;
--외래키 참조 조건에 매치되지 않는 오류 발생 (UNIQUE 하지 않음)
CREATE TABLE BOARD3(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20), BOARD_DATE DATE, FOREIGN KEY(BOARD_WRITER) REFERENCES MEMBER_TABLE(MEMBER_NAME));
CREATE TABLE BOARD4(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20), BOARD_DATE DATE, FOREIGN KEY(BOARD_WRITER) REFERENCES MEMBER_TABLE(MEMBER_NICKNAME));
INSERT INTO BOARD4 VALUES(1, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', (SELECT MEMBER_NICKNAME FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER01'), SYSDATE);
INSERT INTO BOARD4 VALUES(2, '헤리포터', '천둥 번개 속 헤그리드가 어두운 문 앞에 서 ...', (SELECT MEMBER_NICKNAME FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER02'), SYSDATE);
INSERT INTO BOARD4 VALUES(3, '겨울왕국', '엘사 왜 이리도 나를 외롭게 두는거야 ...', (SELECT MEMBER_NICKNAME FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER03'), SYSDATE);
INSERT INTO BOARD4 VALUES(4, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', (SELECT MEMBER_NICKNAME FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER04'), SYSDATE);
INSERT INTO BOARD4 VALUES(5, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', NULL, SYSDATE);
SELECT FROM BOARD4;
--테이블의 컬럼을 지정하지 않을시 해당 테이블의 PRIMARY KEY를 자동으로 지정함
CREATE TABLE BOARD5(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20) REFERENCES MEMBER_TABLE, BOARD_DATE DATE);
INSERT INTO BOARD5 VALUES(1, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER01'), SYSDATE);
INSERT INTO BOARD5 VALUES(2, '헤리포터', '천둥 번개 속 헤그리드가 어두운 문 앞에 서 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER02'), SYSDATE);
INSERT INTO BOARD5 VALUES(3, '겨울왕국', '엘사 왜 이리도 나를 외롭게 두는거야 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE WHERE MEMBER_ID = 'USER03'), SYSDATE);
INSERT INTO BOARD5 VALUES(4, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...','저삼이', SYSDATE);
INSERT INTO BOARD5 VALUES(5, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...',NULL, SYSDATE);
SELECT FROM BOARD5;
--NEW MEBER TBL
CREATE TABLE MEMBER_TABLE_FK(MEMBER_ID VARCHAR2(20) PRIMARY KEY, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20), MEMBER_NICKNAME VARCHAR2(20) UNIQUE, MEMBER_PHONE CHAR(13) UNIQUE);
CREATE TABLE BOARD_FK1(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20) REFERENCES MEMBER_TABLE_FK, BOARD_DATE DATE);
SELECT FROM MEMBER_TABLE_FK;
SELECT FROM BOARD_FK1;
INSERT INTO MEMBER_TABLE_FK VALUES('USER01', '1234', '유저일', '저일이', '010-1111-1234');
INSERT INTO MEMBER_TABLE_FK VALUES('USER02', '1234', '유저이', '저둘이', '010-2222-1234');
INSERT INTO MEMBER_TABLE_FK VALUES('USER03', '1234', '유저삼', '저삼이', '010-3333-1234');
INSERT INTO MEMBER_TABLE_FK VALUES('USER04', '1234', '유저사', '저넷이', '010-4444-1234');
INSERT INTO BOARD_FK1 VALUES(1, '어린왕자', '너를 기다리는 그 시간이 내 가슴을 설레게 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER01'), SYSDATE);
INSERT INTO BOARD_FK1 VALUES(2, '헤리포터', '천둥 번개 속 헤그리드가 어두운 문 앞에 서 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER02'), SYSDATE);
INSERT INTO BOARD_FK1 VALUES(3, '겨울왕국', '엘사 왜 이리도 나를 외롭게 두는거야 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER03'), SYSDATE);
INSERT INTO BOARD_FK1 VALUES(4, '죽음의 수용소에서', '삶과 죽음의 기로를 매일 지나는 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER04'), SYSDATE);
SELECT FROM BOARD_FK1;
--참조중인 데이터 삭제하기
DELETE FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER04';
DELETE FROM BOARD_FK4 WHERE BOARD_NO = 4;
--참조 무결성때문에 삭제가 불가능한 경우 프라이머리키가 레퍼런스되고 있을 때 데이터를 삭제하기 곤란함.
--CHILD RECORD FOUND!
--ON DELETE SET NULL : 사용시 참조중이더라도 삭제가 가능하고 해당 레퍼런스 데이터를 NULL로 대체함
CREATE TABLE BOARD_FK2(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20) REFERENCES MEMBER_TABLE_FK ON DELETE SET NULL, BOARD_DATE DATE);
INSERT INTO BOARD_FK2 VALUES(4, '죽음의 수용소에서', '삶과 죽음의 기로를 매일 지나는 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER04'), SYSDATE);
SELECT FROM BOARD_FK2;
--ON DELETE CASCADE : 참조중인 데이터를 지우면서 지워지는 데이터를 참조중인 데이터도 같이 제거함. **** 좀 외워라 게시판 만들어야지 **
CREATE TABLE BOARD_FK3(BOARD_NO NUMBER PRIMARY KEY, BOARD_TITLE VARCHAR2(100) NOT NULL, BOARD_CONTENT VARCHAR2(1000) NOT NULL, BOARD_WRITER VARCHAR2(20) REFERENCES MEMBER_TABLE_FK ON DELETE CASCADE, BOARD_DATE DATE);
INSERT INTO BOARD_FK3 VALUES(4, '죽음의 수용소에서', '삶과 죽음의 기로를 매일 지나는 ...', (SELECT MEMBER_ID FROM MEMBER_TABLE_FK WHERE MEMBER_ID = 'USER04'), SYSDATE);
SELECT FROM BOARD_FK3;
--------------------------------------------------------------------CHECK--------------------------------------------------------------------
CREATE TABLE MEMBER_TBL_CHECK(MEMBER_ID VARCHAR2(20) PRIMARY KEY, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20) NOT NULL, MEMBER_GENDER CHAR(6) CHECK(MEMBER_GENDER IN('남자', '여자')));
INSERT INTO MEMBER_TBL_CHECK VALUES('USER1', '1234', '유저일', '남자');
INSERT INTO MEMBER_TBL_CHECK VALUES('USER2', '1234', '유저이', '여자');
INSERT INTO MEMBER_TBL_CHECK VALUES('USER3', '1234', '유저삼', 'GIRL');
SELECT FROM MEMBER_TBL_CHECK;
--------------------------------------------------------------------기본값 설정 DEFAULT--------------------------------------------------------------------
--DEFAULT : 기본값 지정 INSERT에서만 동작함.
CREATE TABLE MEMBER_TBL_DEFAULT(MEMBER_ID VARCHAR2(20) PRIMARY KEY, MEMBER_PW VARCHAR2(30) NOT NULL, MEMBER_NAME VARCHAR2(20) DEFAULT 'ㅇㅇ', MEMBER_NICKNAME VARCHAR2(20) DEFAULT '별명없음', MEMER_AGE NUMBER DEFAULT 0);
INSERT INTO MEMBER_TBL_DEFAULT(MEMBER_ID, MEMBER_PW) VALUES('USER01', '1234');
SELECT * FROM MEMBER_TBL_DEFAULT;
