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

업데이트: June 22, 2022

개요

png

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

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

데이터베이스

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

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

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

SQL (Structured Query Language)

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

png

TABLE

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

2. DDL (Data Definition Language)

데이터 유형

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

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

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

Twitter Facebook LinkedIn

Yonggeun Shin

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

개요

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

2. DDL (Data Definition Language)

3. DML (Data Manipulation Language)

4. TCL (Transaction Control Language)

Reference

공유하기

댓글남기기

참고

[논문리뷰] Searching for Best Practices in Retrieval-Augmented Generation

[Impala] Metadata Cache Invalidation

[Hadoop] Hadoop Ecosystem과 구성요소들에 대한 이해(HDFS, MapReduce, Hive, Imapla, Spark, Kudu, Sqoop 등)

[Git] Git & Github의 동작원리 이해하기 및 주요 명령어 정리