[SQLD 개념 정리] Part2-3. 관리 구문

Yujeong·2025년 3월 18일
SQLD

SQLD 개념 정리

목록 보기
5/5
post-thumbnail

Part2. SQL 기본 및 활용

CHATER 3. 관리 구문

1. DML

  • DML ⭐️⭐️
    • Data Manipulation Language
    • DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어
    • SELECT, INSERT, UPDATE, DELETE, MERGE
    • 명령어 실행 후 별도의 COMMIT을 해주어야 데이터가 반영되고, ROLLBACK도 가능
      • SQL Server는 자동 커밋됨

  • INSERT ⭐️⭐️
    • 테이블에 데이터를 입력하는 명령어
    • 방법1
      INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES(데이터1, 데이터2, ...);
      • 명시되지 않은 컬럼에는 NULL 값이 입력됨
      • 기본키나 NOT NULL 제약조건이 걸린 컬럼에는 NULL값이 입력될 수 없으니 주의해야 함
    • 방법2
      INSERT INTO 테이블명 VALUES (전체 컬럼에 입력될 데이터 리스트);
      • 전체 컬럼에 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야 함
      • 순서가 바뀌어서 데이터 유형이 맞지 않거나 누락된 데이터가 있어서 전체 컬럼 개수와 맞지 않을 경우 에러 발생
    • INSERT 시 참조 무결성 제약 조건
      액션설명
      AUTOMATIC부모 테이블에 PK가 없는 경우 부모 테이블에 PK 생성 후 자식 테이블에 입력
      DEPENDENT부모 테이블에 PK가 존재할 때만 자식 테이블에 입력 허용
      SET NULL부모 테이블에 값이 없으면 외래 키를 NULL로 설정
      SET DEFAULT부모 테이블에 값이 없으면 외래 키를 기본값(Default)으로 설정
      NO ACTION부모 테이블에 해당 값이 없으면 삽입 실패 (무결성 위반)

💡INSERT 공백 문자 ⭐️

  • INSERT INTO SERVICE VALUES('999', '', '2015-11-11')
    • 오라클: 공백 문자 NULL 로 입력됨
    • SQL Server: 빈 문자로 입력됨
  • SELECT * FROM SERVICE WHERE SERVICE_NAME IS NULL;
    • 오라클: 조회 가능
    • SQL Server: 조회 불가능
  • SELECT * FROM SERVICE WHERE SERVICE_NAME = ‘’;
    • 오라클: UNKNOWN, SERVICE_NAME=NULL 비교
    • SQL Server: 조회 가능
  • UPDATE ⭐️⭐️

    • 이미 저장된 데이터를 수정하고 싶을 때 사용하는 명령어

    • WHERE 절이 없으면 테이블의 모든 행(row)이 변경됨

      UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);
UPDATE 테이블명 (SET 컬럼명1 = 데이터1, 컬럼명2 = 데이터2 ...);

  • DELETE ⭐️⭐️

    • 이미 저장된 데이터를 삭제하고 싶을 때 사용하는 명령어

    • WHERE 절이 없으면 테이블의 모든 행(row)이 삭제됨

      DELETE FROM 테이블명 (WHERE 수정할 데이터에 대한 조건);

  • MERGE ⭐️

    • 테이블에 새로운 데이터를 입력하거나 이미 저장되어 있는 데이터에 대한 변경 작업을 한 번에 할 수 있도록 해주는 명령어

      MERGE
	INTO 타겟 테이블명
	USING 비교 테이블명
		ON 조건
  WHEN MATCHED THEN
	  UPDATE SET 컬럼명1 = 데이터1, 컬럼명2 = 데이터2, ...
	WHEN NOT MATCHED THEN
		INSERT (컬럼명1, 컬럼명2, ...) VALUES (데이터1, 데이터2, ...);

2. TCL

  • TCL ⭐️⭐️
    • Transaction Control Language
    • 트랜잭션을 제어하는 명령어
    • COMMIT, ROLLBACK, SAVEPOINT

  • 트랜잭션 특징(ACID) ⭐️⭐️
    트랜잭션 특징의미
    원자성(Atomicity)트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야 한다.
    일관성(Consistency)트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 한다.
    예를 들어, 이미 결제된 셔츠 수량과 남아있는 셔츠 재고의 합은 항상 처음 보유하고
    있었던 셔츠의 총 수량과 같아야 한다.
    고립성(Isolation)하나의 트랜잭션은 고립되어 수행되어야 한다.
    예를 들어, 내가 구매하고자 하는 티셔츠를 지금 다른 사람이 계산하고 있다면 나는
    재고 데이터를 참조하거나 변경할 수 없고 그 사람의 계산이 끝날 때까지 기다려야 한다.
    지속성(Durability)트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 한다.
    모든 트랜잭션이 로그에 남겨진 뒤 COMMIT 되어야 하고, 이를 통해 시스템 장애가 발생하더라도 복구 가능해야 한다.

  • COMMIT ⭐️⭐️
    • INSERT, UPDATE, DELETE 명령어 실행 후 변경된 내용을 확정, 반영하는 명령어
    • COMMIT을 실행하지 않으면 메모리까지만 반영됨
      메모리는 휘발성이기 때문에 언제든 사라질 수 있고, 다른 사용자는 변경된 값을 조회할 수 없음

  • ROLLBACK ⭐️⭐️
    • INSERT, UPDATE, DELETE 명령어 실행 후 변경된 내용을 취소하는 명령어
    • ROLLBACK하면 변경하기 전 값으로 복구됨

  • SAVEPOINT ⭐️⭐️

    • ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어

    • ROLLBACK 뒤에 특정 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구됨

      INSERT INTO TEST_TABLE VALUES (3, 'APPLE', 'C');
SAVEPOINT A;
UPDATE TEST_TABLE SET COL2 = 'BANANA' WHERE COL1 IN (1,3);
SAVEPOINT B;
DELETE FROM TEST_TABLE WHERE COL2 = 'ORANGE';
ROLLBACK TO A;
DELETE FROM TEST_TABLE WHERE COL2 = 'BANANA';
COMMIT;

    • 초기 테이블:

      COL1COL2COL3
      1BANANAA
      2ORANGEB

    • 최종 COMMIT 후 테이블:

      COL1COL2COL3
      2ORANGEB
      3APPLEC

💡 오랜시간 COMMIT이나 ROLLBACK을 하지 않으면?
Lock에 걸려서 다른 사용자가 데이터를 변경할 수 없는 상황이 발생할 수 있다.

3. DDL

  • DDL ⭐️⭐️
    • Data Definition Language
    • 데이터를 정의하는 명령어
    • CREATE, ALTER, DROP, RENAME, TRUNCATE
    • 자동 커밋됨(Auto COMMIT)

  • 데이터 유형
    유형데이터 타입
    문자CHAR, VARCHAR, CLOB
    숫자NUMBER
    날짜DATE
    • 한글, 영어 byte 수 다름
    • CHAR: 고정, VARCHAR: 가변

  • CREATE ⭐️⭐️

    • 테이블 생성하기 위한 명령어

      CREATE TABLE 테이블명 (
	컬럼명1 데이터 타입 (DEFAULT / NULL 여부),
	...
);

    • 예시

      CREATE TABLE PROFESSOR (
	PROFESSOR_ID NUMBER NOT NULL,
	PROFESSOR_NAME VARCHAR(20) NOT NULL,
	SUBJECT_ID VARCHAR2(5) NOT NULL,
	ADDRESS VARCHAR2(100),
	CONSTRAINT PROFESSOR_PK PRIMARY KEY (PROFESSOR_ID),
	CONSTRAINT PROFESSOR_FK (SUBJECT_ID) REFERENCES SUBJECT(SUBJECT_ID)
);

      💡 테이블 생성 시 지켜야 할 규칙 ⭐️

      • 테이블명은 고유해야 한다.
      • 한 테이블 내에서 컬럼명은 고유해야 한다.
      • 컬럼명 뒤에 데이터 유형과 데이터 크기가 명시되어야 한다.
      • 컬럼에 대한 정의는 () 괄호 안에 기술한다.
      • 각 컬럼은 , 콤마로 구분된다.
      • 테이블명과 컬럼명은 숫자로 시작될 수 없다.
      • 마지막은 ; 세미콜론으로 끝난다.
         
        ✔︎ 에러를 발생시키진 않지만 지키면 좋은 규칙
      • 테이블은 각각의 정체성을 나타내는 이름을 가져야 한다.
      • 컬럼명을 정의할 때는 다른 테이블과 통일성이 있어야 한다.
  • CONSTRAINT ⭐️⭐️
    • 테이블을 생성할 때, 제약조건을 정의해줄 수 있음
    • 저장될 데이터의 무결성, 정확성, 일관성을 유지
    • 제약조건 종류
      • PRIMARY KEY : 기본키. 테이블에 저장된 각각의 행(row)에 대한 고유성 보장. 한 테이블에 하나씩만 정의할 수 있고, PK로 지정된 컬럼에는 NULL 값이 입력될 수 없고 자동으로 UNIQUE 인덱스로 정의됨
      • UNIQUE KEY : 고유키. 기본키와 유사하게 테이블에 정의된 각각의 행(row)에 대한 고유성을 보장하기 위한 제약조건이지만, NULL 값이 허용된다는 차이점 있음
      • NOT NULL : 해당 컬럼에 NULL 값이 입력되는 것을 허용하지 않음
      • CHECK : 컬럼에 저장될 수 있는 값의 범위 제한 CONSTRAINT CHECK_DEL_YN CHECK(DEL_YN IN ('Y','N')) → DEL_YN 컬럼에 ‘Y’나 ‘N’만 입력될 수 있도록 CHECK_DEL_YN라는 이름의 제약조건 정의
    • 참조 무결성 규정 관련 옵션
      옵션의미
      CASCADEParent값 삭제 시 Child값 같이 삭제
      RESTRICTChild 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 Parent 값 삭제 및 수정 가능
      SET NULLParent값 삭제 시 Child의 해당 컬럼 NULL 처리
      SET DEFAULTParent 값 삭제 시 Chid의 해당 컬럼 DEFAULT 값으로 변경
      NO ACTION참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가
    • CTAS

      • 기존에 존재하는 테이블 복사해서 생성하고 싶은 경우

      • 제약조건은 NOT NULL 조건만 복사되고, 나머지 제약조건은 초기화 됨

        CREATE 테이블명 AS SELECT * FROM 복사할 테이블명;

  • ALTER ⭐️⭐️
    • 테이블 구조 변경 명령어
    • ADD COLUMN

      • 새로운 컬럼 추가

      • 항상 맨 끝에 추가되고, 위치 지정 불가

        ALTER TABLE 테이블명 ADD 컬럼명 데이터 유형;
ALTER TABLE 테이블명 ADD (컬럼명 데이터 유형);
    • DROP COLUMN

      • 기존에 있던 컬럼 삭제

      • 한번 삭제한 컬럼은 복구할 수 없음

        ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
    • MODIFY COLUMN

      • 기존에 있던 컬럼 변경

      • 데이터 유형, DEFAULT 값, NOT NULL 제약조건 변경 가능

      • 모든 데이터의 크기가 줄이고자 하는 컬럼의 크기보다 작을 때만 줄일 수 있음
        크기 늘리는 건 상관없음

      • 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형 변경 가능

      • DEFAULT 값 변경 시에는 변경 이후 저장되는 데이터에만 적용됨

      • NULL 값이 저장되어 있지 않은 컬럼에만 NOT NULL 제약 조건 추가 가능

        ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형 [DEFAULT 값] [NOT NULL],
													컬럼명2 데이터 유형 ...);
													
# EX
ALTER TABLE TEST MODIFY COL1 NUMBER DEFAULT 0;
ALTER TABLE TEST MODIFY (COL1 NUMBER DEFAULT 0);

      • SQL Server

        -- 한 번에 한 개만 변경 가능
ALTER TABLE 테이블명 ALTER COLUMN COL1 데이터 유형 [NOT NULL];
    • RENAME COLUMN

      • 기존에 있던 컬럼 이름 변경

        ALTER TABLE 테이블명 RENAME COLUMN 변경할 컬럼명 TO 변경할 이름;
    • ADD CONSTRAINT

      • 제약조건 추가

        ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);
    • DROP TABLE

      • 테이블 삭제

      • CASCADE CONSTRAINT 옵션 명시하면, 삭제하려는 테이블을 참조하고 있는 테이블도 삭제

        DROP TABLE 테이블명 [CASCADE CONSTRAINT];
    • TRUNCATE TABLE

      • 테이블에 저장되어 있는 데이터 모두 제거

        TRUNCATE TABLE 테이블명;

💡 DROP vs. TRUNCATE vs. DELETE ⭐️⭐️

DROPTRUNCATEDELETE
DDLDDLDML
AUTO COMMITAUTO COMMITUSER COMMIT
ROLLBACK xROLLBACK xROLLBACK o
사용한 storage 모두 release o할당된 storage 남기고 release o사용한 storage release x
정의 자체 삭제데이터만 삭제(테이블 생성된 초기 상태)데이터만 삭제

4. DCL

  • DCL ⭐️
    • Data Control Langauge
    • USER를 생성하고 권한을 부여하는 명령어
    • CREATE USER, ALTER USER, DROP USER

  • USER 관련 명령어
    • 하나의 Database는 여러 개의 USER를 가질 수 있음
    • CREATE USER

      • 사용자 생성

        CREATE USER 사용자명 IDENTIFIED BY 패스워드;
    • ALTER USER

      • 사용자 변경

        ALTER USER 사용자명 IDENTIFIED BY 패스워드;
    • DROP USER

      • 사용자 삭제

        DROP USER 사용자명;

  • 권한 관련 명령어 ⭐️
    • GRANT

      • 사용자에게 권한 부여

        GRANT 권한 TO 사용자명;
    • REVOKE

      • 사용자의 권한 회수

        REVOKE 권한 FROM 사용자명;

  • ROLE 관련 명령어

    • ROLE: 특정 권한들을 하나의 세트처럼 묶는 것

    • ROLE 생성

      CREATE ROLE 롤명;

    • ROLE에 권한 부여

      GRANT 권한 TO 롤명;

    • ROLE을 사용자에게 부여

      GRANT 롤명 TO 사용자명;

  • PUBLIC

    • 데이터베이스에서 모든 사용자를 의미하는 키워드

      GRANT SELECT ON EMPLOYEE TO PUBLIC;
profile
Yujeong
공부 기록
이전 포스트

[SQLD 개념 정리] Part2-2. SQL 활용

0개의 댓글