ORACLE(feat. Table)

min seung moon·2021년 2월 16일
0

Oracle

목록 보기
14/23

Table

  • 데이터베이스의 핵심 개체

1. 테이블 만들기

  • 테이블은 만드는 방법이 중요한 것이 아니라 테이블을 어떻게 모델링(설계)했느냐가 훨씬 중요

1. 테이블 생성

CREATE TABLE userTBL -- 회원테이블
(   userID      CHAR(8)         NOT NULL PRIMARY KEY, --사용자 아이디(PK)
    userName    NVARCHAR2(10)   NOT NULL, --사용자 이름
    birtYear    NUMBER(4)       NOT NULL, --출생 연도
    addr        NCHAR(2)        NOT NULL, --지역(경기, 서울, 경남 식으록 2글자만 입력)
    mobile1     CHAR(3), --휴대폰의 국번(010, 011, 016, 017, 018, 019 등)
    mobile2     CHAR(8), --휴대폰의 나머지 전화번호(- 제외)
    height      NUMBER(3), --키
    mDate       DATE --회원 가입일
);

CREATE TABLE buyTBL -- 회원구매테이블
(   idNum       NUMBER(8)       NOT NULL PRIMARY KEY, --순번(pk)
    userID      CHAR(8)         NOT NULL, --사용자아이디
    prodName    NCHAR(6)        NOT NULL, --물품 명
    groupName   NCHAR(4), --분류
    price       NUMBER(8)       NOT NULL, -- 단가
    amount      NUMBER(3)       NOT NULL, -- 수량
    FOREIGN KEY (userID) REFERENCES userTBL(userID)
    -- 외래 키 설정하여 buyTBL의 userID를 userTBL(userID)를 참고하여 연결
);

2. 데이터 입력

insert into userTBL values('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
insert into userTBL values('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
insert into userTBL values('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
insert into userTBL values('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');
insert into userTBL values('SSK', '성시경', 1979, '서울', NULL,  NULL,       186, '2013-12-12');
insert into userTBL values('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
insert into userTBL values('YJS', '윤종신', 1969, '경남', NULL,  NULL,       170, '2005-5-5');
insert into userTBL values('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
insert into userTBL values('JLW', '조관우', 1965, '경기', '018', '99999999', 172, '2010-10-10');
insert into userTBL values('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');

create SEQUENCE idSEQ; -- 순차번호 입력을 위해서 시퀀스 생성
insert into buyTBL values(IDSEQ.nextval, 'KBS', '운동화', null,  30,    2);
insert into buyTBL values(IDSEQ.nextval, 'KBS', '노트북', '전자', 1000,  1);
insert into buyTBL values(IDSEQ.nextval, 'JYP', '모니터', '전자', 200,   1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '모니터', '전자', 200,   5);
insert into buyTBL values(IDSEQ.nextval, 'KBS', '청바지', '의류', 50,    3);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '메모리', '전자', 80,    10);
insert into buyTBL values(IDSEQ.nextval, 'SSK', '책',    '서적', 15,     5);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '책',    '서적', 15,     2);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '청바지', '의류', 50,    1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '운동화', null,  30,     2);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '책',    '서적', 15,     1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '운동화', null,  30,     2);

2. 제약 조건

  • 제약 조건이란 데이터의 무결성을 지키이 위한 제한된 조건을 의미

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

  • 테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자를 '기본 키'라고 부름
-- 1. 기본키 생성
CREATE TABLE userTBL(
	userID		CHAR(8)		NOT NULL PRIMARY KEY,
    	userName	NVARCHAR2(10) 	NOT NULL,
    	--- 중간 생략 ---
);

-- 2. PRIMARY KEY를 지정, 이름 짓기
CREATE TABLE userTBL(
	userID		CHAR(8)		NOT NULL CONSTRAINT PK_userTBL_userID PRIMARY KEY,
    	userName	NVARCHAR2(10) 	NOT NULL,
    	--- 중간 생략 ---
);

-- 3. 제약 조건의 이름을 지정
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL,
    	--- 중간 생략 ---
    	mDate	DATE	NULL,
    	CONSTRAINT PK_userID PRIMARY KEY (userID)
);

-- 4. ATER TABLE 구문 사용
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL,
    	--- 중간 생략 ---
    	mDate	DATE	NULL,
);
ALTER TABLE userTBL
	ADD CONSTRAINT PK_userTBL_userID
    	PRIMARY KEY (userID);
        
-- 5. 제품 코드 + 제품일련번호 같이 기본키 지정(중복 기본 키)
CREATE TABLE prodTBL(
	prodCode 	CHAR(3)		NOT NULL,
    	prodID		CHAR(4)		NOT NULL,
    	prodDate 	DATE		NOT NULL,
    	prodCur		CHAR(10)	NULL
);
ALTER TABLE prodTBL
	ADD CONSTRAINT PK_prodTBL_prodCode_prodid
    	PRIMARY KEY (prodCode, prodID);

-- 6. CREATE TABLE 구문 안에 중복 기본 키 설정
CREATE TABLE prodTBL(
	prodCode 	CHAR(3)		NOT NULL,
    	prodID		CHAR(4)		NOT NULL,
    	prodDate 	DATE		NOT NULL,
    	prodCur		CHAR(10)	NULL,
        CONSTRAINT PK_prodTBL_prodCode_prodid PRIMARY KEY (prodCode, prodID)
);

-- 7. 테이블 삭제, with 외래키 제약 조건이 있어도 삭제
DROP TABLE userTBL CASCADE CONTRAINTS;

-- 번외
ALTER TABLE tableName : tableName을 변경하자
ADD CONSTRAINT keyName : 제약 조건을 추가하자, 추가할 제약조건 이름은 keyName이다
PRIMARY KEY(columnName) : 추가할 제약 조건은 기본키 제약 조건이다, 설정은 columnName에 하겠다

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

  • 외래 키 제약 조건은 두 테이블 사이의 관계를 선언함으로써, 데이터 무결성을 보장해주는 역할
-- 1. 외래키 생성
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL   PRIMARY KEY,
    	--- 중간 생략 ---
);
CREATE TABLE buyTBL(
	idNum		NUMBER(8)	NOT NULL 	PRIMARY KEY,
    	userID		CHAR(8)		NOT NULL 	REFERENCES userTBL(userID),
    --- 중간 생략 ---
);

-- 2. 직접 외래 키의 이름을 지정
CREATE TABLE buyTBL(
	idNum		NUMBER(8)	NOT NULL 	PRIMARY KEY,
    	userID		CHAR(8)		NOT NULL 	
        	CONSTRAINT FK_userTBL_buyTBL REFERENCES userTBL(userID),
    --- 중간 생략 ---
);

-- 3. 외래 키의 이름을 지정할 필요가 없을 경우
CREATE TABLE buyTBL(
	idNum		NUMBER(8)	NOT NULL 	PRIMARY KEY,
    	userID		CHAR(8)		NOT NULL,
    --- 중간 생략 ---
    CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY(userID) REFERENCES userTBL(userID)
);

-- 4. ALTER TABLE 구문 사용
CREATE TABLE buyTBL(
	idNum		NUMBER(8)	NOT NULL 	PRIMARY KEY,
    	userID		CHAR(8)		NOT NULL,
    --- 중간 생략 ---
);
ALTER TABLE buyTBL
	ADD CONSTRAINT FK_userTBL_buyTBL
    	FOREIGN KEY(userID)
    	REFERENCES userTBL(userID);
        
-- 5. ON DELETE CASCADE ( Main Table이 삭제되면 Foreign key로 가리킨 Table의 데이터 삭제)
-- userTBL.userID(PRIMARY KEY)
-- buyTBL.userID(FOREIGN KEY REFERENCES userTBL.userID)
-- buyTBL의 Tuple(data)가 삭제되더라도 userTBL에는 영향이 없음
-- userTBL의 Tuple(data)이 삭제되면 관계있는 buyTBL의 Tuple(data)도 같이 삭제
ALTER TABLE buyTBL
	ADD CONSTRAINT FK_userTBL_buyTBL
    	FOREIGN KEY(userID)
    	REFERENCES userTBL(userID)
        ON DELETE CASCADE;
        
-- 6. 외래 키 제거
ALTER TABLE buyTBL
	DOP CONSTRAINT FK_userTBL_buyTBL;
    
-- 번외
ALTER TABLE tableName : tableName을 변경하자
ADD CONSTRAINT keyName : 제약 조건을 추가하자, 추가할 제약조건 이름은 keyName이다
FOREIGN KEY(columnName) : 외래 키 제약조건을 columnName에 설정
REFERENCES tableName(columnName) : 참조할 기준 테이블은 tableName의 columnName 열이다

3. UNIQUE 제약 조건

  • UNIQUE 제약 조건은 '중복되지 않은 유일한 값'을 입력해야하는 조건
  • PRIMARY KEY랑 유사
-- 1. UNIQUE 생성
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL   PRIMARY KEY,
    	--- 중간 생략 ---
        email	CHAR(30)	NULL,
        CONSTRAINT AK_email UNIQUE (email)
);

-- 2. ALTER 사용하기
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL   PRIMARY KEY,
    	--- 중간 생략 ---
        email	CHAR(30)	NULL
);
ALTER TABLE userTBL
	ADD CONSTRAINT AK_email UNIQUE (email);

4. CHECK 제약조건

  • CHECK 제약 조건은 입력되는 데이터를 점검하는 기능을 함
-- 1. 키는 0 이상이여야 함
ALTER TABLE userTBL
	ADD CONTRAINT CK_height
    	CHECK (height >= 0);
        
-- 2. 휴대폰 국번 체크
ALTER TABLE userTBL
	ADD CONTRAINT CK_mobile1
    	CHECK (mobile1 IN ('010', '011', '016', '017', '018', '019'));
        
-- 2. 휴대폰 국번 체크(조건이 있어도 값 대입)
ALTER TABLE userTBL
	ADD CONTRAINT CK_mobile1
    	CHECK (mobile1 IN ('010', '011', '016', '017', '018', '019'))
        ENABLE NOVALIDATE ;

5. DEFAULT 정의

  • DEFAULT는 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의하는 방법
  • ALTER을 사용시에는 MODIFY가 같이 사용
-- 1. DEFAULT 설정
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL   PRIMARY KEY,
    	--- 중간 생략 ---
        addr	NCHAR(2) DEFAULT '서울' NOT NULL
);

-- 2. ALTER로 설정
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL   PRIMARY KEY,
    	--- 중간 생략 ---
        addr	NCHAR(2) NOT NULL
);
ALTER TABLE userTBL
	MODIFY email DEFAULT '서울';

-- 3. INSERT문에서 DEFAULT 구문을 입력
INSERT INTO userTBL VALUES('LHL', '이해리', DEFAULT);

-- 4. 명시 되지 않으면 DEFAULT로 자동 입력
INSERT INTO userTBL(userID, userName) VALUES('LHL', '이해리');

-- 5. 값이 입력되면 DEFAULT 무시
INSERT INTO userTBL VALUES('LHL', '이해리', '대구');

6. NULL 값 허용

  • NULL 값을 허용할려면 NULL, 허용하지 않으려면 NOT NULL 사용
CREATE TABLE userTBL(
	userID	CHAR(8) NOT NULL   PRIMARY KEY,
    	--- 중간 생략 ---
        addr	NCHAR(2) NULL
);

3. 임시 테이블

  • 임시 테이블은 이름처럼 임시로 잠깐 사용되는 테이블임
  • 임시 테이블의 데이터는 세션 내에서만 존재하며, 세션이 닫히면 자동으로 데이터가 삭제됨.
형식 :
CREATE GLOBAL TEMPORARY TABLE 테이블이름
( 열 정의 ... )
[ON COMMIT DELETE ROWS 또는 ON COMMIT PRESERVE ROWS]

1. 실습

-- 임시 테이블 생성
CREATE GLOBAL TEMPORARY TABLE tempTBL(
    id      CHAR(8),
    uName   NCHAR(10)
);

-- 임시테이블 생성 확인
SELECT table_name, TEMPORARY FROM user_tables;

-- 임시테이블에 값 대입
INSERT INTO tempTBL values('Tomas','토마스');
INSERT INTO tempTBL values('James','제임스');

-- 입력된 값 확인
SELECT *FROM tempTBL;

-- 입력된 값 저장
COMMIT;
-- 입력된 값 확인, COMMIT하면 데이터가 삭제 됨
SELECT *FROM tempTBL;

2. 옵션 변경 확인

-- COMMIT이 아닌 접속을 종료하면 데이터 삭제
CREATE GLOBAL TEMPORARY TABLE tempTBL2(
    id      CHAR(8),
    uName   NCHAR(10)
)ON COMMIT PRESERVE ROWS;

-- 데이터 입력
INSERT INTO tempTBL2 values('Atrur','아서');
INSERT INTO tempTBL2 values('Murdoch','머독');

-- 데이터 저장
COMMIT;

-- 저장된 데이터 확인
SELECT *FROM tempTBL2;

-- 연결 종료 후 임시 테이블 재 접속

-- 저장된 데이터 확인, 데이터 삭제 되어 있음
SELECT *FROM tempTBL2;

3. 임시 테이블 삭제

-- 일반 테이블과 동일
DROP TABLE tempTBL;
DROP TABLE tempTBL2;

4. 테이블 삭제

  • 외래 키 제약 조건의 기준 테이블은 원칙적으로 삭제할 수가 없음
  • 먼저, 외래 키가 생성된 왜래 키 테이블을 삭제해야 함
DROP TABLE 테이블이름 [CASCADE CONSTRAINTS];

5. 테이블 수정

  • 테이블의 수정은 ALTER TABLE문을 사용
ALTER TABLE [ schema. ] table
[ alter_table_properties
 | column_clauses
 | constraint_clauses
 | alter_table_partitioning
 | alter_external_table_clauses
 | move_table_clauses
]
[ enable_disable_clauses
 | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS }
] ...
;

1. 열의 추가

-- 회원 테이블 (userTBL)에 회원의 홈페이지 주소를 추가
ALTER TABLE userTBL
	ADD homepage VARCHAR(30) -- 열 추가
    DEFAULT 'http://www.hanbit.co.kr' -- 디폴트 값
    NULL; -- NULL 허용함
    
-- 여러 개의 열을 추가
ALTER TABLE userTBL
	ADD (homeAddr NVARCHAR2(20), postNum VARCHAR(5) );

2. 열의 삭제

-- userTBL의 homeAddr 열 삭제
ALTER TABLE userTBL
	DROP COLUMN homeAddr;
    
-- 여러개의 열 삭제
ALTER TABLE userTBL
	DROP (homepage, postNum);

3. 열의 이름 변경

ALTER TABLE userTBL
	RENAME COLUMN userName TO uName;

4. 열의 데이터 형식 변경

ALTER TABLE userTBL
	MODIFY (addr NVARCHAR2(10) NULL);

5. 열의 제약 조건 추가 및 삭제

-- 기본 키 삭제 (외래키와 연결이 되어 있으면 오류가 남)
ALTER TABLE userTBL
	DROP PRIMARY KEY
    
-- 외래키 제거한 후 다시 기본키를 제거
ALTER TABLUE buyTBL
	DROP CONSTRAINT SYS_007801; -- 외래 키 이름을 다를 수 있음
ALTER TABLE userTBL
	DROP PRIMARY KEY

6. 테이블 예제 실습

-- 모든 제약 조건을 제이하고 테이블을 다시 만들자!
CREATE TABLE userTBL(
    userID      CHAR(8),
    userName    Nvarchar2(10),
    birthYear    NUMBER(4),
    addr        NCHAR(2),
    mobile1     CHAR(3),
    mobile2     CHAR(8),
    height      NUMBER(3),
    mDate       DATE
);


CREATE TABLE buyTBL(
    idNum       NUMBER(8),
    userID      CHAR(8),
    prodName    NCHAR(6),
    groupName   NCHAR(4),
    price       NUMBER(8),
    amount      NUMBER(3)
);

-- 시퀀스 생성
CREATE SEQUENCE idSEQ;

-- userTBL 데이터 입력
INSERT INTO userTBL
VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTBL
VALUES('KBS', '김범수', NULL, '경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTBL
VALUES('KKH', '김경호', 1871, '전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTBL
VALUES('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');

-- buyTBL 데이터 입력
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'KBS', '운동화', NULL, 30, 2);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'BBK', '모니터', '전자', 200, 5);

-- 기본 키 제약 조건을 생성
ALTER TABLE userTBL
    ADD CONSTRAINT PK_userTBL_userID
    PRIMARY KEY (userID);
    
-- 키본키 확인    
SELECT * FROM USER_CONSTRAINTS
    WHERE OWNER = 'TABLEDB'
    AND TABLE_NAME='USERTBL' AND CONSTRAINT_TYPE = 'P';
    
-- userID열에 외래키를 설정
ALTER TABLE buyTBL
    ADD CONSTRAINT FK_userTBL_buyTBL
    FOREIGN KEY (userID) 
    REFERENCES userTBL(userID);
    
-- 현재 userTBL에 userID에 없는 data가 존재해서 생성 안됨
-- BBK 행을 삭제하고 다시 외래키 설정
DELETE FROM buyTBL WHERE userID = 'BBK';
ALTER TABLE buyTBL
    ADD CONSTRAINT FK_userTBL_buyTBL
    FOREIGN KEY (userID) 
    REFERENCES userTBL(userID);
    
-- buyTBL의 네번째 데이터 다시 입력
-- userTBL에 userID = BBK가 없어 추가 안됨
INSERT INTO buyTBL VALUES(idSEQ.nextval, 'BBK', '모니터', '전자', 200, 5);

-- 외래키 제약 조건을 비활성화하여 데이터 입력후 활성화
-- 비활성화
ALTER TABLE buyTBL
    DISABLE CONSTRAINT FK_userTBL_buyTBL;

-- buyTBL 데이터 입력
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'SSK', '책', '서적', 15, 5);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'EJW', '책', '서적', 15, 2);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'BBK', '운동화', NULL, 30, 2);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'EJW', '책', '서적', 15, 1);
INSERT INTO buyTBL
VALUES(idSEQ.nextval, 'BBK', '운동화', NULL, 30, 2);

-- 활성화, 검사 안하고 활성화
ALTER TABLE buyTBL
    ENABLE NOVALIDATE CONSTRAINT FK_userTBL_buyTBL;
    
-- userTBL의 출생년도를 1900 ~ 2017까지만 설정하도록 CHECK 제약 조건을 설정
ALTER TABLE usertbl
    ADD CONSTRAINT CH_birthYear
    CHECK (birthYear >= 1900 AND birthYear <= 2017)
    ENABLE;

-- 검사없이 제약 조건 추가    
ALTER TABLE usertbl
    ADD CONSTRAINT CH_birthYear
    CHECK (birthYear >= 1900 AND birthYear <= 2017)
    ENABLE NOVALIDATE;
    
-- userTBL 데이터 입력
INSERT INTO userTBL
VALUES('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2013-12-12');
INSERT INTO userTBL
VALUES('LJB', '임재범', 1963, '서울', 016, 66666666, 182, '2009-9-9');
INSERT INTO userTBL
VALUES('YJS', '윤종신', 1969, '경남', NULL, NULL, 170, '2005-5-5');
INSERT INTO userTBL
VALUES('EJW', '은지원', 1972, '경북', 011, 88888888, 174, '2014-3-3');
INSERT INTO userTBL
VALUES('JKW', '조관우', 1965, '경기', 018, 99999999, 172, '2010-10-10');
INSERT INTO userTBL
VALUES('BBK', '바비킴', 1973, '서울', 010, 00000000, 176, '2013-5-5');

-- 바비킴이 회원을 탈퇴하면(=회원 테이블에서 삭제) 구매한 기록도 삭제되는지 확인하자
DELETE FROM userTBL WHERE userID = 'BBK';

-- ON DELETE CASCADE문을 함께 설정
ALTER TABLE buyTBL
    DROP CONSTRAINT FK_userTBL_buyTBL;
ALTER TABLE buyTBL
    ADD CONSTRAINT FK_userTBL_buyTBL
    FOREIGN KEY (userID)
    REFERENCES userTBL(userID)
    ON DELETE CASCADE;
DELETE FROM userTBL WHERE userID = 'BBK';

-- buyTBL에도 따라서 삭제되었는지 확인
SELECT * FROM buyTBL;

-- 이번에는 userTBL에서 CHECK제약 조건이 걸린 출생년도열을 삭제해 보자
--ALTER TABLE 삭제하자,
ALTER TABLE userTBL
    DROP COLUMN birthYear;
    
SELECT * FROM userTBL;
profile
min seung moon
아직까지는 코린이!
이전 포스트

SQL활용(학사정보 데이터베이스 구축 및 활용)

다음 포스트

ORACLE (feat. VIEW)

0개의 댓글