혼공 SQL(Oracle)- 테이블

데이터 타입 설정

1. 문자형

데이터 유형	정의
CHAR(n)	고정 길이 데이터 타입(최대 2000byte) 지정된 길이보다 작은 데이터 입력될 시 나머지 공간 공백으로 채워진다.
VARCHAR2(n)	가변 길이 데이터 타입(최대 4000byte) 지정된 길이보다 작은 데이터 입력될 시 나머지 공간은 채우지 않는다.

일반적으로 숫자/ 영문 1바이트, 한글(유니코드)은 2바이트이다.

2. 실수형

데이터 유형	정의
NUMBER(P,S)	P, S로 표현 숫자 데이터 타입
	p: 1 ~ 38, s: -84 ~ 127 p(precision):유효자리수, s(scale): 소수점 유효자리

3. 날짜형

데이터 유형	정의
DATE	고정 길이 날짜
TIMESTAMP	밀리초(ms)까지 표현 데이터 타입

테이블 생성 및 삭제

DDL(Data Definition Language) - 데이터 정의어
데이터베이스를 정의하는 언어를 말하며 데이터를 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 언어를 말한다.

종류	역할
CREATE	데이터베이스, 테이블 등을 생성하는 역할을 한다.
ALTER	테이블을 수정하는 역할을 한다.
DROP	데이터베이스, 테이블을 삭제하는 역할을 한다.

테이블 생성 형식

CREATE TABLE  테이블이름(
컬럼 데이터타입 [DEFAULT default값] [컬럼 레벨 제약조건],
컬럼 데이터타입 [DEFAULT default값] [컬럼 레벨 제약조건],
…
[테이블 레벨 제약조건],
…
);

-- 테이블 생성
CREATE TABLE 테스트 ( 
    문자열 VARCHAR2(100),
    숫자 NUMBER,
    날짜 DATE DEFAULT SYSDATE
);


INSERT INTO 테스트 VALUES ('DJ42' , 1234, TO_DATE('2022/02/12'));
INSERT INTO 테스트(문자열, 숫자) VALUES ('DJ2' , 1235);
INSERT INTO 테스트 VALUES ('BAS' , 9435,'2026/11/05');
SELECT * FROM 테스트;

-- 테이블 삭제
DROP TABLE 테스트;

테이블 이름 생성 조건

• 문자로 시작 (숫자로 시작 안됨)
• 테이블 이름은 30바이트 이하 ( 영문자 30 )
• 사용가능한 문자는 A-Z, a-z, _ , $ , # , 0-9
• 다른 테이블과 같은 이름을 사용 안됨
• SQL 명령어를 이름으로 사용안됨 ( select, from , where, table, ..... )

-- 예제) product_id(number 타입), product_name(varchar2 타입, 20자리), menu_date(date 타입) 열이 있는 sample_product 이름의 테이블을 생성해 보세요.
CREATE TABLE  sample_product ( 
    product_name VARCHAR2(80),
    product_id NUMBER,
    menu_date DATE DEFAULT SYSDATE
);

DESC sample_product;
-- 예제) 위에서 만든 테이블을 삭제해 보세요. DESCRIBE 절로 테이블이 잘 삭제되었는지 확인하세요
DROP TABLE sample_product;

• DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어 들지 않는다. 원하는 데이터만 지울 수 있다. 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.

• TRUNCATE 명령어는 용량이 줄어 들고, 인덱스 등도 모두 삭제 된다. 테이블은 삭제하지는 않고, 데이터만 삭제한다. 한꺼번에 다 지워야 한다. 삭제 후 절대 되돌릴 수 없다.

• DROP 명령어는 데이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.

테이블 제약 조건

제약 조건

제약 조건이란 테이블 단위에서 데이터의 무결성을 보장해주는 규칙이다. 제약 조건은 테이블에 데이터가 입력, 수정, 삭제되거나 테이블이 삭제, 변경될 경우 잘못된 트랜잭션이 수행되지 않도록 결함을 유발할 가능성이 있는 작업을 방지하는 역할을 담당한다.

※무결성 (Integrity) - 정보가 원 그대로 변형되지 않고 전달되는 성질 - 원래의 정보또는 신호가 전송/저장/변환 중에 또는 그후에도 동일함을 유지하는 것

무결성 제약조건	설명
NOT NULL	열이 NULL 값을 포함할 수 없음
UNIQUE KEY(UK) - 고유키	테이블의 모든 행에서 고유한 값을 갖는 열 또는 열조합을 지정
PRIMARY KEY(PK) - 기본키	해당 열은 반드시 존재하고 유일해야 함, (NOT NULL, UK 제약조건 결합한 형태)
FOREIGN KEY(FK) - 외래키	한열과 참조된 테이블의 열간의 외래 키 관계를 설정
CHECK	해당 열에 저장 가능한 데이터 값의 범위나 조건 지정

제약 조건 범위

제약 조건을 정의하는 방법은 두가지 방법이 제공된다.

• 컬럼 레벨 정의 : 하나의 컬럼을 정의하면서 같이 정의하는 방법
• 테이블 레벨 정의 : 테이블 생성 명령어 마지막에 기술하는 방법

주의할 것은 NOT NULL 제약조건은 컬럼 레벨 정의만 할 수 있다.

컬렘 레벨 제약

CREATE TABLE emp ( 
     이름 VARCHAR2(100),
     성별 VARCHAR2(5) NOT NULL CHECK(성별 IN('M','W')), -- NULL 안됨!
     직원번호 NUMBER PRIMARY KEY, -- 기본키 제약조건!!
     월급 NUMBER CHECK(월급 > 150),
      UNIQUE(이름) --중복안됨!!
);
 
INSERT INTO emp VALUES ('BAS' ,'M' , 1, 200);
INSERT INTO emp VALUES ('BAS' ,NULL , 1, 200);  -- NULL  X
INSERT INTO emp VALUES ('BAS' ,'W', 3, 170);   -- 이름 UNIQUE X
INSERT INTO emp VALUES ('SSS' ,'M', 1, 190);    -- ID PRIMARY KEY X
INSERT INTO emp VALUES ('SSS' ,'Q', 2, 220);      -- 성별 제외 입력
INSERT INTO emp VALUES ('SSS' ,'W', 2,100);   -- 월급 제약조건 X
INSERT INTO emp VALUES ('SSS' ,'W', 2,NULL);   -- NULL 가능.
DESC emp;
SELECT * FROM EMP;
DROP TABLE emp;

-- 예제 ) members 라는 새 테이블을 만듭니다. (제약조건 이름은 생략가능)
CREATE TABLE members2 ( 
     member_id NUMBER PRIMARY KEY ,   -- 기본키
     first_name VARCHAR2(50) NOT NULL,  -- NULL 입력 불가
     last_name VARCHAR2(50) NOT NULL,  -- NULL 입력 불가
     gender VARCHAR2(5) NOT NULL CHECK(gender IN('Man','Woman')), -- 값이  'Man'  또는 'Woman'
     birth_day DATE DEFAULT sysdate,  -- 디폴트값  SYSDATE
     email VARCHAR2(200) NOT NULL,   -- 유니크, NULL 입력불가
     UNIQUE(email) --중복안됨!   
);
DESC members2
DROP TABLE members2;

제약조건 설정

-- 외래키 fk  기본키pk 제약조건
CREATE TABLE 부서(
    부서번호 NUMBER PRIMARY KEY,
    부서이름 VARCHAR2(100)
);
DROP TABLE 부서;
INSERT INTO 부서 VALUES (1, '개발');
INSERT INTO 부서 VALUES (2, '경영');
INSERT INTO 부서 VALUES (3, '회계');

CREATE TABLE 직원(
    직원번호 NUMBER PRIMARY KEY,
    이름 VARCHAR2(100),
    부서번호 NUMBER,
    FOREIGN KEY(부서번호) REFERENCES 부서(부서번호)
--    ON DELETE CASCADE -- 외래키로 참조하고 있는 열이 삭제되면 참고하고 있는 열도 삭제
--    ON DELETE SET NULL -- 외래키로 참조하고 있는 열이 삭제되면 참고하고 있는 열 NULL 세팅
);
DROP TABLE 직원;
-- 참조되는 컬럼은 PK이거나 UK(Unique key)만 가능
INSERT INTO 직원 VALUES (1, '나무식',1);
INSERT INTO 직원 VALUES (2, '엄준식',1);
INSERT INTO 직원 VALUES (3, '김찬식',2);
INSERT INTO 직원 VALUES (4, '나무식',null); -- 널 가능 


SELECT * FROM 직원;

외래키 관계의 참조열은 삭제시 되지 않음. > 직원테이블에서 해당부서번호를 참조하고 있기 때문에!, 참조하고 있지 않으면 삭제됨.

그러나 자식 열에서 ON DELETE CASCADE를 넣게 되면 자식열도 삭제할수 있음.

DELETE FROM 부서 where 부서번호 = 1;

제약조건 조회, 수정, 삭제

-- 제약 조건 조회
SELECT * FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = '부서'; -- 테이블 대문자.

SELECT * FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = '직원'; -- 테이블 대문자.

-- 제약 조건 추가(테이블 생성뒤 추가하기)
    CREATE TABLE 과목(
        과목번호 NUMBER ,
        과목명 VARCHAR2(100)
    );
DROP TABLE 과목;
INSERT INTO 과목 VALUES('01','데이터베이스');
INSERT INTO 과목 VALUES('02','프로그래밍');

CREATE TABLE 학생(
    학생번호 NUMBER ,
    이름 VARCHAR2(100),
    과목번호 NUMBER
  
); 
DROP TABLE  학생;
--제약조건 추가
ALTER TABLE 과목 ADD PRIMARY KEY(과목번호);
ALTER TABLE 과목 ADD  UNIQUE(과목명);

ALTER TABLE 학생 ADD  PRIMARY KEY(학생번호);
ALTER TABLE 학생 MODIFY 이름 NOT NULL;
ALTER TABLE 학생 ADD FOREIGN KEY(과목번호) REFERENCES 과목(과목번호);

INSERT INTO 학생 VALUES(1,'준식',1);
INSERT INTO 학생 VALUES(2,'무기',2);
INSERT INTO 학생 VALUES(3,'바가',2);

-- 제약 조건 삭제 하기 !! 
ALTER TABLE 과목 DROP CONSTRAINT SYS_C007076;
ALTER TABLE 과목 DROP CONSTRAINT SYS_C007075;

테이블 컬럼(열) 수정, 삭제

--- 테스트 (열이름만 복사)
CREATE TABLE EMP_TEMP
AS SELECT * FROM employees
WHERE 1 != 1; --(열이름만 복사)

SELECT * FROM EMP_TEMP;
DROP TABLE  EMP_TEMP ;

CREATE TABLE EMP80
AS SELECT email 이메일 FROM employees
WHERE department_id = 80; 
SELECT * FROM EMP80;        
drop table emp80;