--2일차 - Table Object
-- TABLE
-- - 데이터베이스내에서 데이터 저장단위
-- - 컬럼(COLUMN)과 레코드(RECORD)로 구성
-- >> 컬럼의 정의를 통해 테이블을 생성
-- >> 컬럼 : 컬럼명 데이터타입 속성
-- 속성 : DEFAULT(기본값) NOT NULL(속성X, 제약조건이지만 속성으로 취급)
-- - 하나의 테이블에는 하나의 기본키(PRIMARY KEY)를 가질 수 있다
-- >> PRIMARY KEY : 컬럼내에서 동일한 데이터나 NULL의 값을 가질수 없는 제약
-- : 하나 혹은 복수개(COMPISITE INDEX)의 컬럼으로 구성
-- - ORACLE DATA TYPE
-- >> 문자형 : CHAR(n)-fixed, VARCHAR2(바이트수n)non-fixed
-- NCAHR(n), NVARCHAR2(글자수n)
-- >> 숫자형 : NUMBER(전체자리수m,소수점자리수n)
-- >> 날짜형 : DATE(년월일 ~)
-- - TABLE CREATION
-- CREATE TABLE [TBL_NAME](
-- [COL_NAME] [DATA_TYPE] [PROPERTY],
-- : : : ,
-- [COL_NAME] [DATA_TYPE] [PROPERTY]
-- OR
-- [CONSTRAINTS] >> 비추
-- )TABLESPACE [TBS_NAME];
-- ALTER USER : sys
ALTER USER TEST1
QUOTA UNLIMITED ON USERS; -- 정식버전이 아닌경우 QUOTA값 지정해줘야함
-- MEMBERS
-- 회원코드, 이름, 전화번호, 포인트
CREATE TABLE MEMBERS (
M_CODE NUMBER(4,0) NOT NULL,
M_NAME NVARCHAR2(5),
M_PHONE NVARCHAR2(13),
M_POINTS NUMBER(5,0);
)TABLESPACE USERS;
-- 컬럼 수정
ALTER TABLE MEMBERS
MODIFY(M_POINTS NUMBER(5,0) DEFAULT 1000); --M_POINT 컬럼에 디폴트값 주기
-- POINTS TABLE
-- 회원코드, 날짜, 포인트, 적립 및 사용여부
CREATE TABLE POINTS(
P_MCODE NUMBER(4,0) NOT NULL,
P_DATE DATE DEFAULT SYSDATE NOT NULL,
P_POINTS NUMBER(5,0) NOT NULL,
P_USE NUMBER(1,0) DEFAULT 0 NOT NULL -- 0: 적립
)TABLESPACE USERS;
-- 데이터 입력
INSERT INTO MEMBERS(M_CODE, M_NAME, M_PHONE, M_POINTS)
VALUES(1001, 'ZZA2', NULL, DEFAULT); --일단은 이렇게 모두 표기할 것!
INSERT INTO MEMBERS(M_CODE, M_NAME, M_PHONE)
VALUES(1000, 'ZZANG', '010-1234-5678');
INSERT INTO MEMBERS(M_CODE, M_NAME)
VALUES(1002, 'ZZA3');
-- 제약조건
-- 1. UNIQUE : 컬럼내에 데이터가 입력된다면 반드시 유일해야 된다. (NULL값 중복 인정)
ALTER TABLE MEMBERS
ADD CONSTRAINT M_PHONE_UK UNIQUE(M_PHONE);
-- 2. NOT NULL
INSERT INTO MEMBERS(M_CODE, M_NAME, M_PHONE, M_POINTS)
VALUES(NULL, 'HZ', NULL, DEFAULT);
--SQL 오류: ORA-01400: NULL을 ("KPHONG"."MEMBERS"."M_CODE") 안에 삽입할 수 없습니다
-- 3. PRIMARY KEY = UNIQUE + NOT NULL
ALTER TABLE MEMBERS
ADD CONSTRAINT M_CODE_PK PRIMARY KEY(M_CODE);
INSERT INTO MEMBERS(M_CODE, M_NAME, M_PHONE, M_POINTS)
VALUES(1004, 'ZZANG', NULL, DEFAULT);
--SQL 오류: ORA-00001: 무결성 제약 조건(KPHONG.M_CODE_PK)에 위배됩니다
-- 4. CHECK KEY
ALTER TABLE MEMBERS
ADD CONSTRAINT M_PHONE_CK CHECK(M_PHONE LIKE '01%'); --'01'로 시작하는 모든 문자
INSERT INTO MEMBERS(M_CODE, M_NAME, M_PHONE, M_POINTS)
VALUES(1005, 'ZZANG', '02-123-456', DEFAULT);
--SQL 오류: ORA-02290: 체크 제약조건(KPHONG.M_PHONE_CK)이 위배되었습니다
-- 5. FOREIGN(REFERENCE) KEY
ALTER TABLE POINTS
ADD CONSTRAINT P_MCODE_FK FOREIGN KEY(P_MCODE)
REFERENCES MEMBERS(M_CODE); -- P_MCODE 가 M_CODE 를 참조!
INSERT INTO POINTS(P_MCODE, P_DATE, P_POINTS, P_USE)
VALUES(1010,DEFAULT, 1000, DEFAULT);
--SQL 오류: ORA-02291: 무결성 제약조건(KPHONG.P_MCODE_FK)이 위배되었습니다- 부모 키가 없습니다
-- 검색 구문
SELECT * FROM USER_TABLES; --USERS 의 모든 테이블 데이터 검색
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = 'MEMBERS'; --MEMBERS 테이블의 모든 COL검색
SELECT SYSDATE FROM DUAL; -- 시스템 시간 검색
SELECT * FROM MEMBERS; -- MEMBERS 의 모든 데이터 검색
SELECT * FROM USER_CONSTRAINTS; -- USERS 의 모든 제약조건 검색
--TABLE MANAGEMENT - ALTER, DROP
-- 1. ALTER >> ADD, MODIFY, DROP
-- ADD : 컬럼을 추가, 제약조건을 추가
-- MODIFY : 컬럼을 수정
-- DROP : 컬럼을 삭제, 제약조건을 삭제
-- * 제약조건은 수정이 불가능하므로 삭제 후 다시 추가!!!
-- 제약조건 삭제시 주의 점
ALTER TABLE MEMBERS
DROP CONSTRAINT M_CODE_PK;
--SQL 오류: ORA-02273: 고유/기본 키가 외부 키에 의해 참조되었습니다
ALTER TABLE MEMBERS
DROP CONSTRAINT M_CODE_PK CASCADE; --참조키까지 동시에 삭제
-- TABLE COPY & DATA COPY
-- TABLE COPY : 테이블 구조 + 데이터를 복사 + 속성
-- : 제약조건은 복사되지 않는다.
CREATE TABLE MEMBERS2
AS
SELECT * FROM MEMBERS;
-- TABLE COPY : 테이블 구조 + 특정 데이터 복사 + 속성
CREATE TABLE MEMBERS3
AS
SELECT * FROM MEMBERS WHERE M_CODE > 1002;
--TABLE COPY : 테이블 구조만 복사
CREATE TABLE MEMBERS4
AS
SELECT * FROM MEMBERS WHERE 1=2; -- 컬럼만 생성
-- DATA COPY : 복사되어질 테이블이 존재
INSERT INTO MEMBERS4
SELECT * FROM MEMBERS;
/* 데이터 업데이트 */
-- UPDATE [TBS_NAME] SET [COL_NAME] = [NEW VALUE], ....
-- WHERE [조건]
SELECT * FROM MEMBERS4;
UPDATE MEMBERS4 SET M_POINTS = 2000 WHERE M_CODE = 1000;
/* 데이터 삭제 */
-- DELETE FROM [TBL_NAME] WHERE [CONDITION];
DELETE FROM MEMBERS4 WHERE M_PHONE IS NULL; -- NULL 이라면
/* 확인/ 취소 */
COMMIT;
ROLLBACK;