‣ DDL
⇢DDL 개념
- 데이터 정의어 (Data Definition Language)
- 테이블(Table), 뷰(View) 등 데이터베이스 객체를
생성/수정/삭제기능을 담당 - 트랜잭션 Transaction 대상이 아님 : 실행 후에는 작업을 취소할 수 없음
트랜잭션: 반드시 한번에 수행해야 하는 작업
👉 한마디로❗ DDL은 데이터베이스 객체를 생성/수정/삭제 하는 기능이며 실행 후에는 작업을 취소할 수 없습니다.
⇢DDL 종류
CREATE: 데이터베이스 객체 생성ALTER: 데이터베이스 객체 수정DROP: 데이터베이스 객체 삭제TRUNCATE: 데이터베이스 객체 데이터 및 저장 공간 삭제 (내용만 삭제)
→ 테이블로 따지면 모든 행만 지움 (열은 안 지음)
⇢ALTER (테이블 수정)
-
칼럼 추가 ADD
→ ALTER TABLE 테이블명ADD칼럼명 데이터타입 [제약조건]
(제약조건은 필요시) -
칼럼 수정 MODIFY
→ ALTER TABLE 테이블명MODIFY칼럼명 데이터타입 [제약조건] -
칼럼 삭제 DROP COLUMN
→ ALTER TABLE 테이블명DROP COLUMN칼럼명 -
칼럼 이름 RENAME COLUM
→ ALTER TABLE 테이블명RENAME COLUM기존칼럼명TO신규칼럼명
(칼럼 이름 바꾸는 문법) -
테이블 이름 RENAME TO
→ ALTER TABLE 테이블명RENAME TO신규테이블명
(테이블 이름 바꾸는 문법) -
PK/FK 제약조건 (추가, 삭제)
-
PK
- 추가
→ ALTER TABLE 테이블명ADD CONSTRAINT제약조건명 PRIMARY KEY(칼럼) - 삭제
→ ALTER TABLE 테이블명DROP CONSTRAINT 제약조건명
→ ALTER TABLE 테이블명DROP PRIMARY KEY
• PK에서만 가능(PK는 테이블 당 하나씩 밖에 줄 수 없기 때문)
- 추가
-
FK
- 추가
→ ALTER TABLE 자식테이블명ADD CONSTRAINT제약조건명 FOREIGN KEY(칼럼) REFERENCES 부모테이블명(참조할칼럼) - 삭제
→ ALTER TABLE 테이블명DROP CONSTRAINT제약조건명 - 일시중지
→ ALTER TABLE 테이블명DISABLE CONSTRAINT제약조건명 - 활성화(다시 시작)
→ ALTER TABLE 테이블명ENABLE CONSTRAINT제약조건명
- 추가
⇢DROP (테이블 삭제)
- 테이블 삭제하기
DROP TABLE 테이블_이름;- 제약조건 테이블 삭제
DROP TABLE 테이블_이름 CASCADE CONSTRAINTS;‣ DML
⇢DML 개념
- 데이터 조작어 (Data manipulation language)
- 테이블(Table)이나 뷰(View) 등의 데이터베이스 객체에 행(Row)을
삽입/수정/삭제하는 기능을 담당 - 트랜잭션 대상 : 실행 후에 작업의 완료 또는 취소 처리 가능
(작업 완료 시저장 = COMMIT,작업 취소 = ROLLBACK)COMMIT: 작업을 저장한다. COMMIT이 완료된 작업은 ROLLBACK으로 취소 불가ROLLBACK: 작업을 취소한다. COMMIT이후작업을 취소한다.
👉 한마디로❗ DML은 데이터베이스 객체에 행을 삽입/수정/삭제 하는 기능이며 실행 후에 작업의 취소가 가능합니다.
⇢DML 종류
1) 행 삽입 : INSERT INTO VALUES
2) 행 수정 : UPDATE SET WHERE
3) 행 삭제 : DELETE FROM WHERE
👉DML은 행을 조작함.
-- 참고.
-- 자격증시험에서는 DML의 범주를 INSERT,UPDATE,DELETE + SELECT로 보기도 한다.⇢시퀀스 SEQUENCE / INSERT INTO
: 순차적으로 증가하는 숫자를 자동으로 생성하는 데이터베이스 객체
CREATE로 시퀀스를 생성한 뒤, 생성된 번호를INSERT문으로 삽입- 삽입 기본 구문 :
INSERT INTO테이블명(칼럼,칼럼,...)VALUES(삽입할 테이터 내용,테이터,...)- 시퀀스명.
NEXTVAL
⇒ 시퀀스 : 번호 만들기
⇒ NEXTVAL : 번호 뽑기
- 시퀀스명.
CREATE SEQUENCE DEPT_SEQ
INCREMENT BY 1 -- 1씩 증가하는 번호를 만든다.(디폴트)
START WITH 1 -- 1부터 번호를 만든다.(디폴트)
NOMAXVALUE -- 번호의 상한선이 없다.(디폴트)
NOMINVALUE -- 번호의 하한선이 없다.(디폴트)
NOCYCLE -- 번호 순환이 없다.(디폴트)
CACHE 20 -- 20개씩 번호를 미리 만들어 둔다.(디폴트)
NOORDER -- 번호를 순서대로 사용하지 않는다.(디폴트)
-- 번호를 순서대로 사용하는 ORDER 옵션으로 바꿔서 시퀀스를 생성한다.
⇢UPDATE (데이터 수정)
UPDATE 테이블
SET 업데이트할내용, 업데이트할내용, ...
WHERE 조건식 (어떤 것들을 업데이트 할 것인가)
- (조건식 생략 시, 전체 행 수정)
-- 1. 부서번호가 3인 부서의 지역을 '인천'으로 변경하시오.
UPDATE DEPARTMENT_T
SET LOCATION = '인천' -- SET절의 등호(=)는 대입연산자
WHERE DEPT_NO = 3; -- WHERE절의 등호(=)는 동등비교연산자
⇢DELETE (데이터 삭제)
DELETE (아무것도 적지 않음)
FROM 테이블
WHERE 조건식
- (조건식 생략 시, 전체 행 삭제)
DELETE
FROM DEPARTMENT_T
WHERE LOCATION = '인천'; --동등비교연산자‣ DCL
- 데이터 제어어 (Data Control Languag)
- 데이터베이스에 접근하고 객체 사용에 권한을 주거나 회수하는 역할을 한다.
GRANT : 권한 부여
REVOKE : 권한 박탈, 회수
‣ TCL
- 트랜잭션 제어어 (Transaction Control Language)
- 트랜잭션 : SQL 명령들을 하나의 논리적인 작업 단위로 처리
① COMMIT : 트랜잭션내의 모든 SQL 실행으로 인해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료
② ROLLBACK : 트랜잭션내의 모든 SQL 실행으로 인해 변경된 작업 내용을 모두 취소하고 트랜잭션을 종료
‣ 실습
⇢실습 DDL
-- 테이블 삭제
DROP TABLE CUSTOMER_TBL;
DROP TABLE BANK_TBL;
-- BANK_TBL 생성
CREATE TABLE BANK_TBL (
BANK_CODE VARCHAR2(20 BYTE) NOT NULL
, BANK_NAME VARCHAR2(30 BYTE) NULL
, CONSTRAINT PK_BANK PRIMARY KEY(BANK_CODE)
);
-- CUSTOMER_TBL 생성
CREATE TABLE CUSTOMER_TBL (
NO NUMBER NOT NULL
, NAME VARCHAR2(30 BYTE) NOT NULL
, PHONE VARCHAR2(30 BYTE) NULL UNIQUE
, AGE NUMBER CHECK(AGE BETWEEN 0 AND 100) --CHECK 연산자
, BANK_CODE VARCHAR2(20 BYTE)
, CONSTRAINT PK_CUST PRIMARY KEY(NO)
, CONSTRAINT FK_BANK_CUST FOREIGN KEY(BANK_CODE) REFERENCES BANK_TBL(BANK_CODE)
);
⇢실습 ALTER
-- 1. 은행 테이블에 연락처(BANK_TEL) 칼럼을 추가하시오.
ALTER TABLE BANK_TBL ADD BANK_TEL VARCHAR2(15 BYTE) NOT NULL;
-- 2. 은행 테이블의 은행명(BANK_NAME) 칼럼의 데이터타입을 VARCHAR2(15 BYTE)로 변경하시오.
ALTER TABLE BANK_TBL MODIFY BANK_NAME VARCHAR2(15 BYTE);
-- 3. 고객 테이블의 나이(AGE) 칼럼을 삭제하시오.
ALTER TABLE CUSTOMER_TBL DROP COLUMN AGE;
-- 4. 고객 테이블의 고객번호(NO) 칼럼명을 CUST_NO로 변경하시오.
ALTER TABLE CUSTOMER_TBL RENAME COLUMN NO TO CUST_NO;
-- 5. 고객 테이블에 GRADE 칼럼을 추가하시오. ('VIP', 'GOLD', 'SILVER', 'BRONZE' 중 하나의 값을 가지도록 한다. ->CHECK 제약조건)
ALTER TABLE CUSTOMER_TBL ADD GRADE VARCHAR2(6 BYTE) CHECK(GRADE = 'VIP' OR GRADE = 'GOLD' OR GRADE = 'SILVER' OR GRADE 'BRONZE') NOT NULL; -- OR연산자 쓰는 방법
ALTER TABLE CUSTOMER_TBL ADD GRADE VARCHAR2(6 BYTE) CHECK(GRADE IN ('VIP', 'GOLD', 'SILVER', 'BRONZE')); -- IN연산자 사용 방법 (권장)
-- 6. 고객 테이블의 고객명(NAME)과 연락처(PHONE) 칼럼 이름을 CUST_NAME, CUST_PHONE 으로 변경하시오.
ALTER TABLE CUSTOMER_TBL RENAME COLUMN NAME TO CUST_NAME;
ALTER TABLE CUSTOMER_TBL RENAME COLUMN PHONE TO CUST_PHONE;
-- 7. 고객 테이블의 연락처(CUST_PHONE) 갈럼을 필수 칼럼으로 편경하시오.
ALTER TABLE CUSTOMER_TBL MODIFY CUST_PHONE VARCHAR2(30 BYTE) NOT NULL;
-- 8. 고객 테이블의 고객명(CUST_NAME) 칼럼의 필수 제약조건을 없애시오.
ALTER TABLE CUSTOMER_TBL MODIFY CUST_NAME VARCHAR2(30 BYTE) NULL;
-- 반드시 NULL을 명시해야 한다. (CREATE에서는 안 적으면 NULL이지만 ALTER에서 안 적으면 안 바뀐다는 뜻.)
테이블 구조 확인
-- 테이블 구조 확인하기(DESCRIBE)
DESC BANK_TBL;
DESC CUSTOMER_TBL;
⇢실습 DML
DROP TABLE EMPLOYEE_T;
DROP TABLE DEPARTMENT_T;
-- 부서(DEPARTMENT_T) 테이블
CREATE TABLE DEPARTMENT_T (
DEPT_NO NUMBER NULL
, DEPT_NAME VARCHAR2(15 BYTE) NOT NULL
, LOCATION VARCHAR2(15 BYTE) NOT NULL
, CONSTRAINT PK_DEPART PRIMARY KEY(DEPT_NO)
);
-- 사원(EMPLOYEE_T) 테이블
CREATE TABLE EMPLOYEE_T (
EMP_NO NUMBER NULL
, NAME VARCHAR2(20 BYTE) NOT NULL
, DEPART NUMBER NULL
, POSITION VARCHAR2(20 BYTE) NULL
, GENDER CHAR(2 BYTE) NULL
, HIRE_DATE DATE NULL
, SALARY NUMBER NULL
, CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EMP_NO)
, CONSTRAINT FK_DEPART_EMP FOREIGN KEY(DEPART) REFERENCES DEPARTMENT_T(DEPT_NO) ON DELETE SET NULL
);
⇢실습 시퀀스 / INSERT INTO
--부서 시퀀스, 인서트 생성
DROP SEQUENCE DEPT_SEQ;
CREATE SEQUENCE DEPT_SEQ ORDER;
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, LOCATION) VALUES(DEPT_SEQ.NEXTVAL, '영업부', '대구');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, LOCATION) VALUES(DEPT_SEQ.NEXTVAL, '인사부', '서울');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, LOCATION) VALUES(DEPT_SEQ.NEXTVAL, '총무부', '대구');
INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, LOCATION) VALUES(DEPT_SEQ.NEXTVAL, '기획부', '서울');
--사원 시퀀스, 인서트 생성
DROP SEQUENCE EMP_SEQ;
CREATE SEQUENCE EMP_SEQ
START WITH 1001
ORDER;
INSERT INTO EMPLOYEE_T(EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY) VALUES(EMP_SEQ.NEXTVAL, '구창민', 1, '과장', 'M', '95-05-01', '5000000'); -- 날짜는 하이픈(-) 또는 슬래시(/)로 구분
INSERT INTO EMPLOYEE_T(EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY) VALUES(EMP_SEQ.NEXTVAL, '김민서', 1, '사원', 'M', '17-09-01', '2500000'); -- 날짜는 하이픈(-) 또는 슬래시(/)로 구분
INSERT INTO EMPLOYEE_T(EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY) VALUES(EMP_SEQ.NEXTVAL, '이은영', 2, '부장', 'F', '90/09/01', '5500000'); -- 날짜는 하이픈(-) 또는 슬래시(/)로 구분
INSERT INTO EMPLOYEE_T(EMP_NO, NAME, DEPART, POSITION, GENDER, HIRE_DATE, SALARY) VALUES(EMP_SEQ.NEXTVAL, '한성일', 2, '과장', 'M', '93/04/01', '5000000');
-- 날짜는 하이픈(-) 또는 슬래시(/)로 구분
COMMIT;
⇢실습 UPDATE
-- 2. 부서번호가 2인 부서에 근무하는 모든 사원들의 연봉을 500000 증가시키시오.
UPDATE EMPLOYEE_T
SET SALARY = SALARY + 500000
WHERE DEPART = 2;
⇢실습 DELETE
-- 2. 지역이 '서울'인 부서를 삭제하시오. ('서울'에 근무하는 사원이 있다. -> ON DELETE SET NULL 옵션에 의해서 부서정보가 NULL 값으로 처리된다.)
DELETE
FROM DEPARTMENT_T
WHERE LOCATION = '서울';
개발이 세상에서 제일 재밌어요