Yonggeun Shin

Yonggeun Shin

시행착오가 밑거름이 되는 공간

[SQLD (2-1)] SQL 기본(1) DDL,DML,TCL

업데이트:

개요

png

이번 포스팅은 데이터 자격 검정인 SQL 개발자(SQLD) 시험을 기반으로 공부했던 자료이다.
시험을 위한 공부이긴 했지만, 정리했던 자료가 향후에 도움이 많이 될 것 같다.


1. 관계형 데이터 베이스 개요

데이터베이스

  • 특정 기업이나 조직 또는 개인이 필요에 의해(ex: 부가가치가 발생하는) 데이터를 일정한 형태로 저장해 놓은 것

DBMS (데이터베이스 관리 시스템)

  • 효율적인 데이터의 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 소프트웨어

SQL (Structured Query Language)

  • 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어

png

png

TABLE

  • 데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본 단위
  • 가로 = 행 = 로우 = 튜플 = 인스턴스
  • 세로 = 열 = 컬럼 = 속성?
  • 테이블 관계 용어
    • 정규화 : 데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 이상현상(Anomaly)을 방지하기 위해 중복제거
    • 기본키 : 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 컬럼
    • 외부키 : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 컬럼
  • 데이터 언어
    • 비절차적 데이터 조작어(DML) : 사용자가 무슨(What) 데이터를 원하는 지만을 명세함
    • 절차적 데이터 조작어 : 어떻게(How) 데이터를 접근해야 하는지를 명세함
      • PL/SQL(Oracle), T-SQL (SQL Server) 등이 있음


2. DDL (Data Definition Language)

데이터 유형

png

png

CREATE TABLE (테이블 생성)

CREATE　TABLE　테이블이름 (
칼럼명1 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식]
) ;

CREATE TABLE PLAYER (
  PLAYER_ID CHAR(7) NOT NULL,           # NOT NULL은 제약조건 (column level)
  PLAYER_NAME VARCHAR(20) NOT NULL,
  TEAM_ID CHAR(3) NOT NULL,
  E_PLAYER_NAME VARCHAR(40),
  NICKNAME VARCHAR(30),
  JOIN_YYYY CHAR(4),
  POSITION VARCHAR(10),
  BACK_NO TINYINT,
  NATION VARCHAR(20),
  BIRTH_DATE DATE,
  SOLAR CHAR(1),
  HEIGHT SMALLINT,
  WEIGHT SMALLINT,
  CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),                        # 제약조건 (table level)
  CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)  # 제약조건 (table level)
) ;
  • 테이블 생성 시에 주의해야 할 몇 가지 규칙
    • 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
    • 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
    • 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
    • 테이블 이름을 지정하고 각 칼럼들은 괄호 ( ) 로 묶어 지정한다.
    • 각 칼럼들은 콤마 ,로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ;으로 끝난다.
    • 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)
    • 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
    • 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
    • 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
    • A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
    • 테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자 로 만들어진다.
    • DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
    • 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
    • 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
    • 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

제약조건 (아래 표가, 컬럼 정의할때 쓸수 있는 것들, NOT NULL 처럼 PRIMARY KEY 도 쓸 수 있음)

  • 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약

png

  • DEFAULT : NULL값일 경우 NULL 대신 입력할 값을 지정하는 것임
  • 생성된 테이블 구조 확인
    • DESCRIBE 테이블명, DESC 테이블명

ALTER TABLE (테이블 구조 변경)

  • 컬럼 추가 (ADD)

    ALTER TABLE 테이블명
ADD 추가할 칼럼명 데이터 유형;
    
ALTER TABLE PLAYER
ADD ADDRESS VARCHAR(80);
  • 컬럼 삭제 (DROP COLUMN)
    • 데이터가 있거나 없거나 모두 삭제 가능
    • 한 번에 하나의 칼럼만 삭제 가능
    • 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 함
    • 한 번 삭제된 칼럼은 복구가 불가능
    ALTER TABLE 테이블명
DROP COLUMN 삭제할 칼럼명;
    
ALTER TABLE PLAYER
DROP COLUMN ADDRESS;
  • 컬럼 수정 (MODIFY) - Oracle
    • 컬럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경
    # 오라클표기 
ALTER TABLE 테이블명
MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],
칼럼명2 데이터 유형 …);
    
ALTER TABLE TEAM_TEMP
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

  • 컬럼 수정 (ALTER COLUMN) - SQL Server

    ALTER TABLE 기관분류
ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;
    
ALTER TABLE 기관분류
ALTER COLUMN 등록일자 DATE NOT NULL;
    • SQL-Server에서는 여러 컬럼을 동시에 수정하는 구문 지원 하지 않음 (따로해야함)
    • SQL-Server에서는 괄호를 사용하지 않음
    • 주의점
      • 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못함 (기존 데이터 훼손 가능)
      • 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.
      • 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.
      • 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.
      • NOT NULL 제약조건이 있었는데, ALTER할때 넣지 않으면 NULL로 변경됨

  • 컬럼명 변경 (RENAME COLUMN)

    ALTER TABLE 테이블명
RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;
    
ALTER TABLE PLAYER
RENAME COLUMN PLAYER_ID TO TEMP_ID;

  • 제약조건 삭제 (DROP CONSTRAINT)

    ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
    
ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_FK;

  • 제약조건 추가 (ADD CONSTRAINT)

    ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);
    
ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

RENAME TABLE (테이블 이름 변경)

RENAME 변경전 테이블명 TO 변경후 테이블명;

RENAME TEAM TO TEAM_BACKUP;

DROP TABLE (테이블 삭제)

  DROP TABLE 테이블명 [CASCADE CONSTRAINT];
  
  DROP TABLE PLAYER;
  • CASCADE CONSTRAINT 옵션 : 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제

TRUNCATE TABLE (테이블의 데이터만 삭제, 테이블 구조는 재사용 가능)

  TRUNCATE TABLE PLAYER;


3. DML (Data Manipulation Language)

  • INSERT INTO ~ VALUES : 테이블에 데이터 입력

    # 1번째 방법
INSERT INTO 테이블명 (COLUMN_LIST)
VALUES (COLUMN_LIST에 넣을 VALUE_LIST);
  
INSERT INTO PLAYER
(PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);
  
# 2번째 방법
INSERT INTO 테이블명
VALUES (전체 COLUMN에 넣을 VALUE_LIST);
  
INSERT INTO PLAYER
VALUES ('2002010','이청용','K07','','BlueDragon','2002','MF','17',NULL, NULL,'1',180,69);
    • Column과 1:1로 매핑 되어야함
    • 두번째 방법의 경우 모든 컬럼 값을 입력해야하고, 안할거면 '' 또는 NULL 입력\
    • 칼럼 데이터 유형이 CHAR, VARCHER2 인 경우 '' 를 입력해야하지만, 숫자일 경우 자동 인식
    • TABLE 컬럼 중 NOT NULL 인 컬럼 있으면 무조건 함께 INSERT 해야함. (명시를 안하면 NULL 이 들어가야하므로)

  • UPDATE ~ SET (입력한 정보를 수정)

    UPDATE 테이블명
SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;
  
UPDATE PLAYER
SET BACK_NO = 99;  # 모든 BACK_NO를 99로 수정

  • DELETE (FROM) (데이터 삭제)

    DELETE [FROM] 삭제를 원하는 정보가 들어있는 테이블명;
  
DELETE FROM PLAYER;  # WHERE 절이 없으므로, PLAYER 테이블 전체 삭제
    • DDL은 AUTO COMMIT이지만, DML은 COMMIT 명령어를 입력하여 TRANSACTION을 종료해야 실제 테이블에 반영됨
    • Oracle은 AUTOCOMMIT 이지만, SQL-Server는 따로 COMMIT 해야함

  • SELECT ~ FROM (데이터 조회)

    SELECT [ALL/DISTINCT] 보고 싶은 칼럼명, 보고 싶은 칼럼명, ...
FROM 해당 칼럼들이 있는 테이블명;
  
SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO
FROM PLAYER;
  
SELECT DISTINCT POSITION
FROM PLAYER;  # UNIQUE한 값만 출력
    • ALL : Default 옵션 중복된 데이터가 있어도 모두 출력
    • DISTINCT : 중복된 데이터가 있는 경우 1건으로 처리해서 출력

    • 와일드카드(Wild card) * : 모든

      SELECT *
FROM 테이블명;   # 모든 컬럼 출력
    • ALIAS : 일종의 별명, 컬럼 레이블을 변경하여 출력 (보기좋게)
      • ALIAS에 공백이 있으면 ""를 사용해야함
      SELECT PLAYER_NAME AS 선수명, POSITION AS 위치, HEIGHT AS 키, WEIGHT AS 몸무게
FROM PLAYER;  # AS는 생략가능
    
SELECT PLAYER_NAME "선수 이름", POSITION "그라운드 포지션", HEIGHT "키", WEIGHT "몸무게"
FROM PLAYER;

산술연산자

  SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게"
  FROM PLAYER;
  • 사칙연산 등을 수행해서 출력함
  • 이 경우 Label name이 길어지기 때문에 ALIAS로 별칭을 사용하는 것을 권장

합성연산자

  • 문자와 문자를 연결하는 경우 2개의 수직 바(||)에 의해 이루어진다. (Oracle)
  • 문자와 문자를 연결하는 경우 + 표시에 의해 이루어진다. (SQL Server)
  • 두 벤더 모두 공통적으로 CONCAT(string1, string2) 함수를 사용할 수 있다.
  • 칼럼과 문자 또는 다른 칼럼과 연결시킨다.

  • 문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.

    SELECT PLAYER_NAME || '선수,' || HEIGHT || 'cm,' || WEIGHT || 'kg' 체격정보
FROM PLAYER;
    
# 예시) 예) 박지성 선수, 176 cm, 70 kg

추가로 정리 (https://docu94.tistory.com/141)

인덱스 생성하는 방법

  CREATE INDEX 생성될 인덱스명 ON 스키마(속성명);

외래키에 대한 설명

  • 테이블 생성시 설정할 수 있다.
  • 외래키 값은 NULL 값을 가질 수 있다.
  • 한 테이블에 여러개가 존재할 수 있다.
  • 외래키 값은 참조 무결성 제약을 받을 수 있다.

참조 무결성 규정

  • 예시

    FOREIGN KEY (외래키가 될 컬럼) REFERENCES 참조할 스키마(속성명) ON DELETE CASCADE;
  • DELETE(MODIFY) Action
    • CASCADE : Master 삭제 시, Child 같이 삭제
    • SET NULL : Master 삭제 시, Child 해당 필드 Null
    • SET DEFAULT : Master 삭제 시, Child 해당 필드 Default 값으로 설정
    • RESTRICT : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
    • No Action : 참조 무결성을 위반하는 삭제 / 수정 액션을 취하지 않음
  • INSERT Action
    • AUTOMATIC : Master 테이블에 PK가 없는 경우, Master 테이블 PK 생성 후 Child 입력
    • SET NULL : Master 테이블에 PK가 없는 경우, Child 외부키를 Null값으로 처리
    • SET DEFAULT : Master 테이블에 PK가 없는 경우, Child 외부키를 지정된 기본값으로 입력
    • DEPENDENT : Master 테이블에 PK가 존재할때만 Child 입력 허용
    • No Action : 참조 무결성을 위반하는 입력 액션을 취하지 않음
  • DELETE vs TRUNCTUATE vs DROP
    • TRUNCTUATE 과 DROP 은 DDL로, 로그를 남기지 않지만(AUTO COMMIT), DELETE는 로그를 남김(COMMIT 해야함)
    • DELETE : 테이블의 데이터를 모두 삭제함, 디스크 사용량을 초기화(없앰) 하지 않음
    • DROP : 테이블의 데이터를 모두 삭제함, 디스크 사용량도 초기화(없앰) 함, 테이블의 스키마 정의 삭제함
    • TRUNCTUATE : 테이블의 데이터를 모두 삭제함, 디스크 사용량도 초기화(없앰) 함, 테이블의 스키마 정의 삭제하지 않음


4. TCL (Transaction Control Language)

트랜잭션

  • 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작, 데이터베이스의 논리적 연산단위
  • 최소 단위이므로 전부 적용 or 전부 취소임 ⇒ All or Nothing (ex. 계좌이체)
  • COMMIT : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것 (ROLLBACK 불가)
  • ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌리는 것, COMMIT 되지 않은 모든 트랜잭션을 롤백함
  • SAVEPOINT : 저장 지점

트랜잭션의 특성

png

png

트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제 유형

  • Dirty Read : 다른 트랜잭션에 의해 수정되었지만, 아직 커밋되지 않은 데이터를 읽는 것
  • Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
  • Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

COMMIT

  DELETE FROM PLAYER;
  480개의 행이 삭제되었다.
  
  COMMIT;
  커밋이 완료되었다.
  • SQL server는 AUTO COMMIT이며, 오류가 발생하면 자동 ROLLBACK

ROLLBACK

  DELETE FROM PLAYER;
  480개의 행이 삭제되었다.
  
  ROLLBACK;
  롤백이 완료되었다.

SAVEPOINT

  SAVEPOINT SVPT1;
  저장점이 생성되었다.
  
  INSERT INTO PLAYER
  (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
  VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
  1개의 행이 만들어졌다.
  
  ROLLBACK TO SVPT1;
  롤백이 완료되었다.

ROLLBACK과 SAVEPOINT 원리

png

  • 그냥 ROLLBACK은 모든 변경사항 취소 (마지막 COMMIT으로 돌아감)
  • ROLLBACK A → ROLLBACK B (미래로 가기 불가)

정리

  • CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
  • 부연하면, DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
  • 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
  • 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동 으로 롤백된다.
  • SQL Server의 트랜잭션은 DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식이다. 다음의 경우는 Oracle과 같이 자동으로 트랜잭션이 종료된다.
  • 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.


Reference

[데이터 전문가 포럼] SQL 개발자 스터디 교재

https://yurimac.tistory.com/40

댓글남기기

참고