데이터베이스 오라클 데이터 정의어(CREATE, DROP, ALTER)

데이터 정의어

CREATE -> 테이블 생성    CREATE TABLE 테이블명

DROP -> 테이블 삭제      "

ALTER -> 데이터 수정     "

 

데이터 타입

VARCHAR : 문자(가변길이 : 고정이아니라 변동가능), 최대 2000byte

VARCHAR2 : 최대 4000byte

CHAR : 문자(고정길이)

EX)  VARCHAR(50)  -> ABC입력 -> 3byte 차지

       CHAR(50)  -> ABC입력 ->글자는3byte이지만 이미 설정된 50byte를 그대로 차지

 

NUMBER : 숫자(가변길이)

LONG : 숫자,문자(최대크기가 2GB까지 들어간다)

CLOB : 최대크기 4GB 까지들어간다

BFILE : 파일저장

 

 

CREATE(테이블생성)

테이블명 생성규칙(컬럼명도 동일규칙)

1.숫자로 시작 안함

2.이름은 30byte 이하

3.예약어로 테이블명 생성불가(SELECT, UPDATE...)

4.$, #, _ 이런 특수문자로 생성가능

--기본형식
CREATE TABLE 테이블명(
    컬럼명 데이터타입,
    컬럼명2 데이터타입,
    컬럼명3 데이터타입
);

EX)
CREATE TABLE 연습2(
    이름 VARCHAR(50),
    수학 NUMBER,
    영어 NUMBER(5,2) --크기가 5자리이며 소숫자리2개까지 (123.45)
);

우클릭으로도 테이블생성가능 (CREATE)

---

ALTER(데이터 수정)

 

--기본형식
ALTER TABLE 테이블명
	ADD 추가컬럼명 데이터타입;
    
--EX)
ALTER TABLE 연습2
    ADD 과학 NUMBER;
    
--EX2)
ALTER TABLE EX_DEPT
    ADD TEL VARCHAR(50);
SELECT * FROM EX_DEPT;

이렇게 데이터 추가 가능

컬럼에 데이터를 넣지않았으므로 NULL

 

컬럼명 변경하기

--기본형식

ALTER TABLE 테이블명
	RENAME COLUMN 변경전컬럼명 TO 변경후컬럼

EX)

    RENAME COLUMN TEL TO PHONE;
SELECT * FROM EX_DEPT;

 

데이터 타입 변경

기본형식

ALTER TABLE 테이블명
	MODIFY 변경할컬럼명 변경할컬럼의데이터타입

EX)

ALTER TABLE EX_DEPT
    MODIFY DEPTNO NUMBER(5);
DESC EX_DEPT;

※이미 입력되어있는 데이터타입의 크기보다 변경할데이터타입의 크기가 작으면 변경되지않음

 

---

DROP(컬럼 삭제)

기본형식

ALTER TABLE 테이블명
	DROP COLUMN 삭제할컬럼명;

EX)

ALTER TABLE EX_DEPT
	DROP COLUMN PHONE;
SELECT * FROM EX_DEPT;

 

테이블 이름 변경

--기본형식
RENAME 변경전테이블명 TO 변경후테이블명

EX)
RENAME EX_DEPT TO 부서;
SELECT * FROM 부서;
--테이블명이 변경됬으므로 조회도 변경된 테이블명으로

 

테이블 데이터를 삭제

(DML데이터 조작 INSERT,UPDATE,DELETE -> ROLLBACK,COMMIT가능)

(데이터 정의 CREATE, ALTER, DROP, TRUNCATE -> ROLLBACK,COMMIT 불가능)

TRUNCATE TABLE 테이블명;

DELETE FROM 테이블명;

--같은 의미지만 TRUNCATE는 롤백불가

--테이블삭제
DROP TABLE 테이블명

 

---

응용문제

 

 1. 사원 테이블을 생성

    컬럼명     데이터타입       크기
    사원번호    숫자              4
    사원이름    가변형문자         50
    직책       가변형문자          50
    상관번호    숫자              50
    입사일      날짜
    급여        숫자            10
    추가수당     숫자           10
    부서번호     숫자           4

 

CREATE TABLE 사원 (
    사원번호 NUMBER(4),
    사원이름 VARCHAR(50),
    직책 VARCHAR(50),
    상관번호 NUMBER(4),
    입사일 DATE,
    급여 NUMBER(10),
    추가수당 NUMBER(10),
    부서번호 NUMBER(4)
);

DESC 사원;
SELECT * FROM 사원;

 

 

2. 사원테이블에 '비고' 컬럼을 추가하시오

데이터 타입은 가변문자 10BYTE짜리

ALTER TABLE 사원
    ADD 비고 VARCHAR(10);
DESC 사원;
SELECT * FROM 사원;

 

3. 사원테이블에 있는 '비고' 컬럼을 '호봉'으로 수정

ALTER TABLE 사원
    RENAME COLUMN 비고 TO 호봉;
    SELECT * FROM 사원;

 

4. EMP테이블에 있는 데이터를 사원테이블에 저장

    호봉은 NULL로 채우기

INSERT INTO 사원
    SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,
    SAL,COMM,DEPTNO,NULL FROM EMP;
    -- 호봉을 NULL로 채워주기위해
    SELECT * FROM 사원;
    
    INSERT INTO 사원(사원번호,사원이름,직책,상관번호,입사일,급여,추가수당,부서번호)
    SELECT * FROM EMP;
    --호봉에는 NULL로 채우기위해 호봉컬럼은 적지않음
     SELECT * FROM 사원;

 

5.사원테이블에 직책컬럼의 크기를 20으로 수정

ALTER TABLE 사원
    MODIFY 직책 VARCHAR(20);
DESC 사원;

 

6.사원테이블에

직책이 CLERK는 사무원으로 변경

직책이 SALESMAN 판매원으로 변경

직책이 MANAGER 매니저

직책이 PRESIDENT 사장

직책이 ANALYST 분석

UPDATE 사원
     SET 직책 = '사무원'
WHERE 직책 = 'CLERK';

UPDATE 사원
     SET 직책 = '판매원'
WHERE 직책 = 'SALESMAN';

UPDATE 사원
     SET 직책 = '매니저'
WHERE 직책 = 'MANAGER';

UPDATE 사원
     SET 직책 = '사장'
WHERE 직책 = 'PRESIDENT';

UPDATE 사원
     SET 직책 = '분석가'
WHERE 직책 = 'ANALYST';

SELECT * FROM 사원;

 

7.추가수당이 없는 직원들은 급여의 10%만큼 준다

  (사장은 제외)

UPDATE 사원
    SET 추가수당 = 급여*0.1 --급여의 10%를 추가수당으로 준다
WHERE 직책 != '사장' --사장제외
      AND (추가수당 IS NULL -- 추가수당이 NULL 이거나
      OR 추가수당 = 0); --추가수당이 0이다
SELECT * FROM 사원;

 

8.회사의 수평구조

   사원테이블에 상관번호 컬럼을 삭제

ALTER TABLE 사원
    DROP COLUMN 상관번호;

 

9.신입사원을 받음
-- 사원번호 9999
-- 사원이름 캡틴티모
-- 직책 인턴
-- 입사일 오늘날짜
-- 급여 500
-- 추가수당 NULL
-- 부서번호 40
-- 호봉 NULL

INSERT INTO 사원 VALUES(9999,'캡틴티모','인턴',SYSDATE,500,NULL,40,NULL );
SELECT * FROM 사원;

 

10. 30번 부서에서 진행중인 프로젝트가 망함
    그래서 정리해고함
    급여가 1500보다 많이 받는 사람은 해고(삭제)
    다른사람들은 부서를 40번부서로 이동

DELETE FROM 사원 WHERE 부서번호 = 30 AND 급여 > 1500;
--30번부서에서 급여가 1500넘게 받는사원데이터 삭제
UPDATE 사원 SET 부서번호 = 40
    WHERE 부서번호 = 30;
--부서번호가 30번이번 40으로 데이터변경