Day12_DB기초 (DDL/DML/DCL/TCL)

2023.08.29

 

Day12 네 줄 요약

- DDL : 데이터 정의어 (CREAT / ALTER / DROP / ...)

- DML : 데이터 조작어 (INSERT / SELECT / UPDATE / DELETE, 혹은 CRUD)

- DCL : 데이터 제어어 (GRANT / REVOKE / ...)

- TCL : 트랜잭션 제어어 (COMMIT / ROLLBACK)

 

---

 

DB?

 

DB(DataBase)

"특정 조직의 여러 사용자가 공유하여 사용할 수 있도록 통합해서 저장한 운영데이터의 집합"

"TABLE들이 모여 이루는 데이터 단위"

 

ISOS, (Integrated / Stored / Operational / Structured)

1) Integrated Data (통합데이터)
   - 자료의 중복을 배제한 데이터 모임
2) Stored Data (저장데이터)
   - 컴퓨터가 접근할 수 있는 저장 매체에 저장된 자료
3) Operational Data (운영데이터)
   - 지속적으로 꾸준히 유지해야 하는 데이터
4) Shared Data (공유데이터)
   - 여러 사용자가 함께 사용할 수 있고, 사용 목적이 다를 수 있기 때문에 유연성있게 DB를 설계

 

 

DB의 특징

1) 실시간 접근성
   - 사용자가 요구하는 데이터에 실시간으로 응답할 수 있어야 한다.
2) 동시 공유
   - 다수의 사용자가 동시에 이용할 수 있도록 하고,
   - 여러 사용자가 다른 데이터를 동시에 사용하는 것은 물론,
   - 같은 데이터도 동시에 사용할 수 있도록 지원해야 한다.
3) 내용기반 참조
   - 저장된 메모리의 주소나 위치가 아닌 데이터의 내용으로 참조한다.
4) 계속적인 변화
   - 현실세계의 상태를 정확히 반영해야 하기 때문에,
   - 끊임없이 변화하는 세계에 맞춰 DB속 데이터도 계속 변해야 한다.
   - 이를 위해 삽입, 삭제, 수정이 필요하다.

 

 

DB 관련 용어

DBA : DataBase Administrator, DB 관리자

DBMS : DataBase Management System, 데이터들을 효율적으로 관리하기 위해 제공되는 시스템

          - Oracle, mySQL, MsSQL, ...

 

 

DB 기본 문법

1) DDL(Data Definition Language) : 데이터 정의어
   - DB의 TABLE, USER등을 생성하거나 재정의 및 제거
   - CREATE / ALTER / DROP / ...

             - ALTER 종류 : ADD, RENAME COLUMN, MODIFY, DROP COLUMN

-- DDL 예제
CREATE TABLE RASPSTU(
    STU_NUM     NUMBER,
    STU_NAME    NVARCHAR2(5),
    STU_ADDR    NVARCHAR2(100),
    STU_PHONE   NVARCHAR2(13),
    STU_BIRTH   DATE,
    STU_HOBBY   NVARCHAR2(20),
    STU_GROUP   NUMBER
);

-- Q1. STU_AGE COL 추가            --ADD
ALTER TABLE RASPSTU ADD STU_AGE NUMBER;
-- Q2. STU_NUM -> STU_NUMBER      --RENAME
ALTER TABLE RASPSTU RENAME COLUMN STU_NUM TO STU_NUMBER;
-- Q3. STU_ADDR 크기를 100 -> 50   --MODIFY
ALTER TABLE RASPSTU MODIFY STU_ADDR NVARCHAR2(50);
-- Q4. STU_PHONE 컬럼 삭제         --DROP
ALTER TABLE RASPSTU DROP COLUMN STU_PHONE;

실행결과

 

 

2) DML(Data Manipulation Language) : 데이터 조작어
   - 데이터를 삽입, 조회(검색), 수정, 삭제
   - INSERT / SELECT / UPDATE / DELETE
   - C(create) / R(read) / U(update) / D(delete)

-- DML 예제
-- INSERT : 데이터 삽입
-- 1) 모든 컬럼 데이터를 삽입할 때
-- INSERT INTO [TABLE_NAME] VALUES(COL1_DATA, COL2_DATA, COLN_DATA);

INSERT INTO RASPSTU VALUES(8,'JAEHA', '경기도 김포시', '1995-10-12', 'JAVA', 4, 29);

 

-- 2) 특정 컬럼의 데이터를 삽입
-- INSERT INTO [TABLE_NAME] (COL1_NAME, COL4_NAME) VALUES(COL1_DATA, COL4_DATA);
INSERT INTO RASPSTU (STU_NUMBER, STU_NAME) VALUES (2, '홍길동');

 

-- SELECT : 데이터 조회(검색)
-- 1) 모든 컬럼
SELECT * FROM RASPSTU;

 

-- 2) 특정 컬럼
SELECT STU_NAME, STU_NUMBER FROM RASPSTU;

 

-- UPDATE : 데이터 수정
-- 1) 모든 컬럼의 데이터 수정
-- UPDATE [TABLE_NAME] SET [COL1_NAME] = [수정할 COL1_DATA], [COL2_NAME] = [수정할 COL2_DATA];
UPDATE RASPSTU SET STU_GROUP = 6, STU_HOBBY = '강의';

해당 COLUMN의 모든 data가 변경됨

 

-- 2) 특정 컬럼의 데이터 수정
-- SET [COL1_NAME] = [수정할 COL1_DATA], [COL2_NAME] = [수정할 COL2_DATA];
-- WHERE COL_NAME = 'COL_DATA';
UPDATE RASPSTU SET STU_ADDR = '부천시 중동', STU_BIRTH = '990909' WHERE STU_NAME = '홍길동';

STU_NAME = '홍길동' 인 곳에 STU_ADDR, STU_BIRTH data 추가

 

-- DELETE : 데이터 삭제
-- 1) 모든 데이터 삭제
-- DELETE FROM [TABLE_NAME];
DELETE FROM RASPSTU;

 

-- 2) 특정 데이터 삭제
-- DELETE FROM [TABLE_NAME]
-- WHERE COL_NAME = COL_DATA;
DELETE FROM RASPSTU WHERE STU_NUMBER = 8;

STU_NUMBER = 8 (JAEHA) data 삭제

 

-- ALIAS(별칭) : AS
SELECT STU_NUMBER AS 번호, STU_NAME AS 이름, STU_ADDR AS 주소,
STU_BIRTH AS 생년월일, STU_HOBBY AS 취미, STU_AGE AS 나이, STU_GROUP AS 조
FROM RASPSTU;

별칭을 정의한 순서대로 COLUMN 재정렬 (이 경우 '나이'와 '조'가 서로 바뀜)

 

-- ORDER BY (정렬)
-- 오름차순 : ASC (기본값이기 때문에 생략가능)
-- 내림차순 : DESC
SELECT STU_NUMBER AS 번호, STU_NAME AS 이름, STU_ADDR AS 주소,
STU_BIRTH AS 생년월일, STU_HOBBY AS 취미, STU_AGE AS 나이, STU_GROUP AS 조
FROM RASPSTU ORDER BY STU_GROUP ASC;

'조' COLUMN에서 오름차순으로 정렬한 결과

 

3) DCL(Data Control Language) : 데이터 제어어
   - 계정에 권한을 부여하거나 회수하는 기능
   - GRANT : 권한 부여
   - REVOKE : 권한 회수

-- DCL 예제
-- 1) 접속 권한 : CONNECT
GRANT CONNECT TO ICIA;
-- 2) 모든 권한 : DBA
GRANT DBA TO ICIA;

 

 

4) TCL(Transaction Control Language) : 트랜잭션 제어어
   - DML문이 실행 후 DBMS에 저장하거나 실행 전으로 되돌리기 위해 사용하는 SQL문

         - DDL 문의 CREAT / ALTER / DROP 의 실행여부와는 관계 없음 (오직 DML 문만)

   - COMMIT : DML문의 결과를 영구적으로 DB에 저장

             - DML 실행 후에 COMMIT을 해야지 DB에 저장됨, SELECT해서 보이는 것은 단순히 보여지는 것 뿐
   - ROLLBACK : DML문의 결과를 취소, 데이터를 되돌릴 때
   - 마지막 COMMIT 직후 상태로 돌아감

-- 마지막 COMMIT 이후의 data는 그 다음 COMMIT 전까지 DB에 저장 안 됨. ROLLBACK 하면 사라짐
INSERT INTO RASPSTU VALUES(8, 'JAEHA', '경기도 김포시', '1995-10-12', 'JAVA', 4, 29);
COMMIT;
INSERT INTO RASPSTU VALUES(2, '홍길동', '부천시 중동', '990909', 'SQL', 6, 25);
ROLLBACK;

 

---

 

TABLE

 

DB를 구성하는 데이터의 기본 단위

 

TABLE 생성시 주의사항

1) TABLE & COLUMN 이름 규칙
   - 항상 문자로 시작(한글가능, 숫자는 불가능)
   - 30byte 이하로(영어는 30자, 한글은 15자)
   - 같은 이름의 Table은 만들 수 없음
   - 영문자, 한글, 숫자와 특수문자($/#/_) 사용가능
   - SQL 키워드(CREATE, SELECT, DROP, ALTER, ...) 사용불가

2) 자료형 (☆ 위주로 쓰임)
   - VARCHAR(byte길이) : 가변문자열, 가변길이 데이터타입(최대 4000byte)
             - 지정된 길이보다 짧은 데이터가 입력 될 경우 나머지 공간은 채우지 않는다.
   - CHAR(byte길이) : 고정문자열, 고정길이 데이터타입(최대 2000byte)
                - 지정된 길이보다 짧은 데이터가 입력 될 경우 나머지 공간을 채운다.
                          ex)  VARCHAR(5) : 'AA' 입력 시 'AA'
                                 CHAR(5)    : 'AA' 입력 시 'AA___' (남은 공간만큼 공백으로 채움)

☆- NVARCHAR2(byte길이) : VARCHAR + 국가별 문자세트 (유니코드 지원 가변 문자열)
☆- NUMBER : 숫자 데이터(정수, 실수 모두 사용가능), 자릿수 안정해도 무관

             - 맨 앞자리 '0'은 없는 것으로 인식 (0829 -> 829)
                          ex)  NUMBER(5) : 5자리 정수
                                 NUMBER(3,2) : 3자리 정수, 2자리 소수점
☆- DATE : 날짜 데이터(년/월/일, 시/분/초 까지 표현가능)

                          ex)  STU_BIRTH = '951012'; or '1995-10-12';
   - CLOB : 대용량 텍스트 데이터 타입으로 최대 4GB까지 저장가능
   - BLOB : 대용량 텍스트 유니코드 타입으로 최대 4GB까지 저장가능
   - BFILE : 대용량 이전 데이터 파일을 최대 4GB까지 저장가능