SQL 기본 오브 기본 다뤄보기

SQL

설명

Structured Query Language (구조적 질의 언어)

데이터베이스 상에서 자료를 처리하고, 데이터베이스에 자료를 넣거나 뺄 때, 조건에 맞는 자료 등을 가져올 때 등에 사용되는 쿼리 언어이다

표준 SQL은 ANSI SQL이 정립되어 있으며, 아래에서 설명할 DBMS에서는 이 ANSI SQL을 기본으로 지원하기 때문에 어떠한 DBMS를 선택하든 ANSI SQL을 사용하지 못하는 일은 없다

명령어의 역할에 따라 DDL, DML, DCL로 구분된다

용어

• Column (컬럼)
  • 테이블에서의 세로줄 하나하나를 컬럼이라고 한다
  • 하나의 컬럼에는 같은 형식, 같은 필드명의 데이터들이 들어온다
  • JSON 으로 치면 key에 해당한다
  • 칼럼명 = 필드명 이라고 생각하자
• Row
  • 여러 컬럼을 가진 하나의 가로줄
  • 레코드와 같은 의미로, 단일 데이터 항목을 의미한다
  • 컬럼과 로우를 행과 열에 각각 대응시키면 매우 헷갈리니 조심하자
• Record (레코드)
  • 테이블에 저장되는 데이터를 레코드라 하며, 로우와 같은 의미이다
  • 테이블에는 0개 이상의 레코드들이 저장된다
  • 실질적인 데이터들이라고 할 수 있다
• Field (필드)
  • 테이블에 저장되는 데이터의 각 요소들 = 컬럼과 로우의 교차점을 필드라고 한다
  • 요소에 값이 없을 경우 NULL, 아니면 각 데이터 타입에 해당하는 값이 들어간다
  • 각 필드에는 이름을 지정해 주어야 하며, 이것은 표의 헤더에 해당한다
• Table
  • 관계형 데이터베이스의 기본적인 저장 구조로, 행과 열로 이루어진 표라고 볼 수 있다
• Schema
  • 데이터베이스의 구조와 제약조건을 기술한 명세서이다
  • CREATE TABLE을 진행할 때 필드명과 데이터 형식, 데이터 조건 (PRIMARY KEY, NOT NULL 등) 을 적어내려가는데, 이것을 스키마의 예시라고 할 수 있다
  • 스키마를 지정해주면 데이터베이스의 덩치가 어마어마하게 커지더라도 일관성 있는 데이터 형식을 유지할 수 있다
• Database
  • 구조화된 데이터의 모임
  • 데이터베이스는 테이블 여러 개로 이루어져 있고, 테이블은 레코드 여러 개로 이루어져 있다고 생각하자

데이터 제약 조건

데이터의 무결성을 위해, 데이터 추가 시에 필드별로 특별히 추가할 수 있는 조건이다

무결성이라 함은 해당 데이터를 보호하여 이상한 상태로 변질되지 않도록 (정확성과 일관성을 지켜) 막아주는 것이다

CREATE 문으로 테이블을 생성할 때 같이 지정해줄 수 있고, ALTER 문으로 수정할 수 있다

• NOT NULL
  • 해당 필드는 NULL 값을 저장할 수 없다
• UNIQUE
  • 해당 필드는 중복된 값을 가질 수 없다
  • 한 레코드에서 foo 필드에 이미 bar라는 값을 가지고 있다면, 다른 레코드에서는 foo 필드에 bar를 가질 수 없다
  • ID 등 고유값에 주로 사용한다
• PRIMARY KEY (기본 키)
  • NOT NULL과 UNIQUE가 기본으로 적용되므로, 두 제약의 특징을 모두 가지고 있다
  • 한 테이블당 하나의 기본 키를 가질 수 있으며, 해당 테이블을 식별하는 데에 이용된다
  • 다른 테이블의 외래 키와 연결되기도 한다
  • 공백을 가질 수 없다
• FOREIGN KEY (외래 키)
  • 이 제약으로 지정된 필드를 통해 한 테이블을 다른 테이블과 연결할 수 있다
  • 외래 키는 자신이 참조하는 기본 키와 같은 속성을 갖는다
  • 예를 들어, ‘수강 과목’ 테이블은 [학생 ID] 외래 키와 [교수 ID] 외래 키를 가질 수 있고, 이는 각각 ‘학생’ 테이블의 [학생 ID] 기본 키와 ‘교수' 테이블의 [교수 ID] 기본 키를 참조하는 방식으로 테이블의 종속성을 부여할 수 있다
• AUTO_INCREMENT
  • 레코드가 추가될 때마다 필드 값이 자동으로 증가하도록 할 수 있다
  • 예를 들어 ID 필드에 이 제약조건이 설정되어 있다면, 이전에 저장된 레코드의 ID 필드 값이 3일 경우, 다음 레코드의 ID 필드 값은 4가 된다
  • 레코드 인덱스와 같이 데이터가 추가될 때마다 설정하기 번거로운 필드에 이 제약조건을 걸어주면 된다
  • 각 DBMS에 따라 문법이 조금씩 다르다 (언더바 (_) 가 있는 경우도 있고 없는 경우도 있다)
• DEFAULT
  • 해당 필드에 값이 들어오지 않을 경우 지정되는 기본값을 설정할 수 있다
  • 예를 들어, 생일 필드의 기본값을 1970년 1월 1일로 설정할 수 있다

관계형 데이터베이스

Relational Database

데이터를 행과 열의 조합인 표 형태로 저장하는 데이터베이스 형식이다

현재 압도적으로 많이 사용되는 데이터베이스 형식으로, 데이터의 정렬과 탐색 속도가 상당히 빠르고 신뢰도가 높다

SQL 쿼리를 이용하여 데이터를 찾거나 삭제하는 유형이 이 관계형 데이터베이스로, SQL 문법이 상당히 간단하면서도 데이터 탐색을 위한 문법들이 상당히 세세하게 구현되어 있기 때문에 상세한 검색이 가능하다

그외 데이터베이스

• 계층형 데이터베이스
  • 폴더와 파일 등의 계층구조로 데이터를 저장한다
  • 부모-자식간 관계를 갖는다
  • 데이터 접근 속도가 매우 빠르다
  • 부모자식간 관계 때문에 부모를 삭제하면 자식도 같이 지워질 수 있다는 단점이 있다
  • 특정 레코드의 내용이 중복될 위험이 크다
• 네트워크형 데이터베이스
  • 망 형태로 연결된 네트워크와 같이 데이터를 저장한다
  • 계층형 데이터베이스의 단점을 보완하여 상하 종속적인 관계에서 벗어나고, 데이터의 중복도 방지하였다
  • 다만 상당히 복잡한 구조를 띄기 때문에 구조의 변경이 까다롭다
• 객체형 데이터베이스
  • 객체 그대로 데이터베이스에 저장한다
  • 쿼리가 관계형에 비해 복잡하다
• 키-값형 데이터베이스
  • JSON처럼 키와 값이 하나의 묶음으로 저장되는 단순한 데이터베이스
  • 속도가 빠르고, 분산 저장에 용이하지만 탐색이 어렵다
• 문서형 데이터베이스
  • 객체형과 유사하고, 스키마가 필요없어 데이터 스키마가 자주 바뀌는 경우에 적합하다
  • 삽입, 조회, 갱신은 빠르지만 관계형의 GROUP BY와 같은 가공 쿼리를 응용하긴 힘들다고 한다
  • MongoDB가 대표적인 문서형 데이터베이스로, 각 레코드를 JSON 이나 XML을 이용하여 저장한다

DDL

Data Definition Language (데이터 정의 언어)

데이터의 관계를 정의하기 위한 언어이다

한번 쯤 들어봤을 CREATE, DROP 등이 여기 포함된다

CREATE

CREATE DATABASE 데이터베이스명;

새로운 데이터베이스를 생성한다

MySQL에서는 데이터베이스를 여러 개 생성했을 경우 USE 문법을 이용하여 현재 선택중인 데이터베이스를 변경할 수 있다고 한다

 

CREATE SCHEMA 스키마명;

특정 DBMS에서는 스키마를 생성할 수도 있다…만 DBMS마다 역할이 다르다

MySQL에서는 CREATE SCHEMA가 CREATE DATABASE와 같은 역할을 한다

PostgreSQL은 현재 선택된 데이터베이스에 스키마를 추가한다

Oracle DB에서는 여러 테이블과 뷰를 한번에 생성할 수 있다

 

CREATE TABLE 테이블명(
    필드명1 데이터타입 데이터속성,
    필드명2 데이터타입 데이터속성,
    ...
);

테이블을 생성할 땐 각 필드의 데이터 타입과 속성을 지정해줄 수 있다

이때 추가한 필드들은 ALTER 문으로 수정할 수 있다

 

위 명령으로 이런 형태의 테이블이 생성된다

DROP

DROP DATABASE 데이터베이스명;

CREATE DATABASE를 통해 생성했던 데이터베이스를 삭제할 수 있다

 

DROP TABLE 테이블명;

현재 선택된 데이터베이스 내의 테이블을 삭제한다

RENAME

RENAME TABLE 이전테이블명 TO 새로운테이블명;

테이블의 이름을 바꾼다

MySQL에서는 RENAME을 지원하며, 그 외의 경우 ALTER 를 사용하면 이름을 바꿀 수 있다

 

RENAME TABLE 이전테이블명1 TO 새로운테이블명1,
                         이전테이블명2 TO 새로운테이블명2,
                         이전테이블명3 TO 새로운테이블명3,
                         이전테이블명4 TO 새로운테이블명4;

RENAME 문의 장점은 한번에 여러 테이블명을 바꿀 수 있다는 것이다

 

RENAME TABLE 현재DB명.테이블명 TO 다른DB명.테이블명

한 데이터베이스에서 다른 데이터베이스로 테이블을 통째로 옮길 수도 있다

ALTER

ALTER TABLE 테이블명 추가조건;

테이블을 수정한다

추가조건을 덧붙여 칼럼을 추가하거나 삭제하고, 칼럼의 데이터 형식과 제약을 수정할 수 있다

MySQL 외의 다른 DBMS에서 테이블명을 바꿀 때에도 사용된다

 

ALTER TABLE 테이블명 ADD 칼럼명 칼럼속성; // 칼럼 추가
ALTER TABLE 테이블명 DROP 칼럼명; // 칼럼 삭제
ALTER TABLE 테이블명 MODIFY COLUMN 칼럼명 데이터속성; // 칼럼 수정
ALTER TABLE 테이블명 CHANGE COLUMN 이전칼럼명 새로운칼럼명 데이터속성; // 칼럼 이름까지 수정
ALTER TABLE 이전테이블명 RENAME 다른테이블명; // 테이블 이름 변경

칼럼의 생성, 수정, 삭제 및 테이블의 변경을 진행할 수 있다

특정 DBMS에서는 MODIFY COLUMN 대신 ALTER COLUMN을 사용하니 주의하자

TRUNCATE

TRUNCATE TABLE 테이블명;

테이블을 삭제 후 재생성한다

쉽게 말해 모든 칼럼 (필드) 를 제거하고 초기 상태의 테이블로 만들겠다는 의미이다

DML

Data Manipulation Language (데이터 조작 언어)

데이터를 관리하는 (추가, 수정, 삭제) 언어이다

DELETE, INSERT 등 마찬가지로 많이 들어봤던 키워드들이 여기 포함된다

데이터베이스 내부 데이터의 CRUD를 담당한다고 생각하자

SELECT도 DML에 포함되기도 한다

INSERT

INSERT INTO 테이블명 (필드명1, 필드명2, ...) 
    VALUES (데이터1, 데이터2, ...);

테이블에 레코드를 추가한다

필드명을 적고 순서대로 데이터값을 적으면 순서에 맞추어 추가된다

 

INSERT INTO 테이블명 VALUES (데이터1, 데이터2, ...)

필드명을 생략해도 된다

단 필드가 NOT NULL이 아니거나, DEFAULT가 설정되어 있거나, AUTO_INCREMENT 키워드가 설정되어 있어야 한다

 

INSERT INTO 테이블명 (필드이름1, 필드이름2, ...)
    SELECT 필드명1, 필드명2, ...
    FROM 데이터를가져올테이블명
    WHERE 조건

SELECT 문과 함께 사용해서 다른 테이블로부터 조건을 충족하는 결과를 가져와 추가할 수도 있다

DELETE

DELETE FROM 테이블명 WHERE 조건

테이블 안의 특정 레코드를 삭제한다

WHERE 옆에 조건을 달음으로써 특정 조건을 만족하는 레코드만을 지울 수 있다

UPDATE

UPDATE 테이블명 SET 필드명1=데이터1, 필드명2=데이터2, ... WHERE 조건

테이블 안의 특정 레코드를 수정한다

DELETE와 마찬가지로 WHERE 옆의 조건을 만족하는 레코드만 수정할 수 있다

DCL

Data Control Language (데이터 제어 언어)

사용자를 관리하고, 사용자별로 관계 및 데이터를 관리하거나 접근하는 권한을 다루는 언어이다

GRANT, REVOKE 등이 여기 포함된다

GRANT

GRANT SQL문법 ON 테이블명 TO 유저명

사용자에게 데이터베이스 객체를 특정 문법을 이용하여 접근할 수 있는 권한을 부여한다

예를 들어 GRANT SELECT ON … 은 사용자에게 해당 테이블에 SELECT를 사용할 수 있는 권한을 부여하므로, 해당 유저는 데이터를 열람할 권리만 가질 뿐 변경은 할 수 없다

REVOKE

REVOKE SQL문법 ON 테이블명 FROM 유저명

사용자에게 부여한 권한을 취소한다

GRANT와 비슷하게 사용하며, 테이블에 특정 문법을 사용할 권리를 유저에게서 회수한다

REVOKE DELETE ON … 은 사용자에게 테이블 레코드를 삭제할 권리를 회수하므로, 사용자는 더이상 DELETE 를 사용할 수 없다

TCL

Transaction Control Language (트랜젝션 제어 언어)

DCL에서 트랜젝션을 제어하는 명령만을 TCL이라고 부르기도 한다

COMMIT, ROLLBACK 등이 포함된다

데이터의 무결성을 보장하며, 영구적으로 데이터베이스를 업데이트하기 전에 데이터의 변경사항을 미리 확인하고 반영할지 말지 결정할 수 있다

또한 Git에서 커밋하듯 작업을 커밋으로 그룹화할 수 있다

COMMIT

COMMIT;

트랜젝션의 작업 결과를 DB에 반영한다

마지막 커밋 이후부터 작업한 내용의 모음을 트랜젝션이라고 하며, 트랜젝션 과정이 완료되어 COMMIT 문법을 통해 실제 DB에 반영하면 하나의 커밋이 생성되고, DB가 업데이트된다

ROLLBACK

ROLLBACK;

트랜젝션의 작업을 취소하고 이전 상태로 되돌린다

작업 중에 문제가 발생했을 경우 ROLLBACK을 통해 마지막 커밋한 지점으로 데이터베이스를 복구한다

트랜젝션 작업 중 하나라도 문제가 발생하면, 트랜젝션 작업 전체를 취소해야 하기 때문에 커밋 단위를 잘 생각하는 것이 좋다

 

ROLLBACK TO 세이브포인트명;

이전 커밋이 아니라 지정한 세이브포인트로 롤백하고 싶을 때는 세이브포인트 이름을 지정한다

SAVEPOINT

SAVEPOINT 세이브포인트명;

ROLLBACK을 이용하여 트랜젝션을 취소하게 되면 해당 트랜젝션의 모든 내용물이 다 날아가므로, 중간중간 세이브포인트를 지정하여 거기까지만 취소할 수 있다

커밋을 하기는 애매한데 트랜젝션의 덩치가 커지는 것을 원치 않을 때 사용하면 좋다

세이브포인트 이름을 지정하여 현재 상태를 세이브포인트로 임시저장할 수 있고, 이때 지정한 세이브포인트명을 이용하여 롤백할 수 있다

DQL

Data Query Language (데이터 질의 언어)

저장된 데이터를 조회하는 가장 기본적인 언어로, 단 하나의 문법밖에 없지만 가장 많이 사용된다

SELECT를 DML에 포함하는 경우도 있다

SELECT

SELECT {필드명 | *} FROM 테이블명 WHERE 조건

레코드를 조회한다

조건을 덧붙여서 조건에 해당하는 레코드만 조회하는 것도 가능하다

필드명을 지정할 경우 해당 필드의 값만 조회할 수 있고, * (아스터리스크) 를 사용하면 모든 필드의 값을 조회할 수 있다

여담

스키마, 칼럼, 레코드 등등 알쏭달쏭한 용어들과 정말 기본적인 문법들만 한번 정리해 보았다

DBMS (다음 포스팅) 에 따라 SQL 문법이 조금씩 달라지지만, ANSI SQL은 전부 사용가능하기 때문에 알고 있으면 다른 문법을 이해하는 데에도 도움이 될 것 같다

내가 한평생 데이터를 만질 일이 얼마나 되겠어. 했는데 ㅎ… 그래도 생각보다 많이 어렵진 않은 것 같다

테이블 JOIN 들어가는 순간 좀 달라지는것 같지만… 심화 내용은 천천히 살펴봐야지

---

참고자료

[DB개론] 2장. 데이터베이스 모델

[DB] DDL, DML, DCL 이란?

DB 기본 용어 (SQL, DDL, DML, DCL, TCL)

IT 일기

코딩교육 티씨피스쿨

SQL CREATE TABLE Statement

SQL CREATE DATABASE Statement

오라클 테이블 권한 부여 ( GRANT )

[MSSQL] Select문 사용법 & 예제

SQL Commands: DML, DDL, DCL, TCL, DQL with Query Example