검수요청.png검수요청.png

데이터 조작어

위키원
qkfkrpf98 (토론 | 기여)님의 2021년 8월 19일 (목) 15:44 판
이동: 둘러보기, 검색

데이터 조작 언어(DML, Data Manipulation Language)은 데이터베이스 사용자(DBA, DataBase Administrator) 또는 응용 프로그램 소프트웨어가 컴퓨터 데이터베이스(Database)에 대해 데이터 검색, 등록, 삭제, 갱신을 위한, 데이터베이스 언어(Database Language) 또는 데이터베이스 언어 요소이다. 사용자와 데이터베이스 관리시스템(DBMS,database management system)간의 인터페이스를 제공하며 응용 프로그램을 통하여 사용자가 데이터베이스의 데이터를 조작할 수 있도록 하기 위해 포트란(Fortran), 코볼(COBOL) 등의 호스트 언어에 데이터베이스 기능을 추가시켜 만든 언어이다.[1][2]

개요

데이터베이스 언어인 DDL, DML, DCL 중 DML 즉, 데이터 조작어는 데이터베이스 내의 검색, 삽입, 수정, 삭제의 명령을 수행한다. 쉽게 말해 사용자가 데이터를 처리할 수 있게 하는 도구이며 데이터를 조작하여 저장하는 일련의 과정을 트랜잭션(transaction)이라고 하는데 그런 트랜잭션을 다루는 언어이다. 대표적인 조작어에는 질의어(query language)가 있으며 질의어는 터미널(terminal)에서 주로 이용하는 비절차적 데이터 언어이다. 데이터 조작 언어에는 SELECT, INSERT, UPDATE, DELETE등이 있으며 각각 SELECT는 데이터의 검색, INSERT는 데이터의 삽입, UPDATE는 데이터의 수정, DELETE는 데이터의 삭제 역할을 한다.[1]

형태

  • 절차적 조작 언어 : 사용자가 어떤 데이터가 필요하면, 필요한 데이터를 어떻게 구하는지 절차에 맞게 구체적으로 명시하는 언어
  • 비절차적 조작 언어 : 사용자가 어떤 데이터가 필요하지만을 명시하고 어떻게 구하는지 명시하지 않는 언어로서, 배우기 쉽고 사용하기 쉬우나 코드의 효율성 면에서 비효율적이다.[3]

조건

  • 사용하기 쉽고 자연 언어에 가까워야한다.
  • 데이터에 대한 연산뿐만 아니라 (view) 내의 데이터나 데이터 간의 관계를 정확하고 완전하게 명시할 수 있어야 한다.
  • 데이터 언어의 효율적인 구현을 지원해야 한다. 즉 데이터 언어의 구문이 DBMS가 제공하는 기본적인 연산과 관련을 갖도록 해야 한다.[3]

검색

SELECT문은 하나 또는 그 이상의 테이블에서 데이터를 추출하는 SQL의 데이터 조작 언어(DML) 중 하나이다. 이것은 데이터베이스 중 하나 또는 그 이상의 테이블에서 데이터를 추출하기 위한 명령으로 데이터 조작 언어 (DML)에서 가장 많이 사용된다.[4] SELECT문은 테이블 전체를 불러올 수 있고 일부 열, 일부행만 지정하겨 불러오는 것도 가능하며 테이블간 결합하여 검색하는 것이 가능하다.

일반적 검색문
SELECT [PREDICATE] 테이블명.속성명[테이블명.속성명, ...]
FROM 테이블명1,[테이블명2, ...]
[WHERE 조건];
[GROUP BY 컬럼명1 ,[컬럼명2, ...] [HAVING 조건식]
[ORDER BY 컬럼명1 ,[컬럼명2, ...]

SELECT절은 내가 원하는 테이블의 속성을 선택하여 데이터를 가져오도록 하는 것이다. [PREDICATE]에는 불러올 튜플을 제한하는 명령어인데 ALL, DISTINCT가 있다. ALL은 말 그대로 지정된 모든 튜플(Tuple)을 검색하게 되며 기호로 '*' 을 사용할 수 있다. 생략되면 자동으로 모든 튜플을 검색한다. DISTINCT는 검색 시 중복된 튜플이 있으면 하나만 보여주게 된다. FROM절은 SELETE가 어느 테이블에서 검색할 것인가를 지정하게 된다. WHERE을 사용하여 조건을 부여해 원하는 데이터만 가져오게 할 수 있다. 요약하면 무엇을 가져올지(SELECT), 어디서 가져올지(FROM), 검색 조건(WHERE)라고 할 수 있다.[5]

검색문 활용 예시
SELECT 번호 FROM 학생 WHERE 이름 = '김통신' 

첫 절부터 해석하면 '번호'속성을 모두 검색하는데 그 학생은 '학생'테이블에 존재하는 속성을 검색한다. 단, '이름'속성값이 '김통신'이다. 즉,학생 중 이름이 '김통신'인 학생번호를 검색하는 SQL문이 된다.

지정 검색

검색문에서 원하는 데이터를 얻기 위해 WHERE 조건 연산자를 사용하여 데이터를 필터링해야 한다. WHERE절에 사용할 수 있는 연산자의 종류는 비교 연산자, 논리 연산자, BETWEEN A AND B, IN, LIKE 연산자 등이 있다.

비교 연산자[5]
연산자 의미
= 같다.
<> 또는 != 같지 않다.
< ~보다 작다.
<= ~보다 작거나 같다.
!< ~보다 작지 않다.
> ~보다 크다.
>= ~보다 크거나 같다.
!> ~보다 크지 않다.
비교 연산자 예시

'직원'테이블의 '월급'속성값이 200이상인 직원의 속성 모두를 검색하는 SQL문이다.

SELECT * FROM 직원 WHERE 월급 >= 200;
논리 연산자
연산자 의미
AND 제시된 모든 조건을 충족하는 데이터만 도출
OR 제시된 조건 중 하나 이상의 조건이 충족하는 데이터 도출
NOT 조건이 충족되지 않는 데이터 도출
논리 연산자 활용 예시

'사원'테이블에서 나이가 22세 이고, 월급이 200이상인 직원의 속성 모두를 검색하는 SQL문이다.

SELETE * FROM 사원 WHERE 나이 = '22' AND 월급 >= 200;
그외 연산자
연산자 의미
BETWEEN A AND B A와 B사이의 데이터 도출
IN 제시된 특정 데이터 중 일치하는 데이터 도출
LIKE'김%' 데이터 첫 문자가 '김'으로 시작되는 데이터 도출
BETWEEN 연산자 활용 예시

'직원'테이블에 '월급'이 300에서 500사이인 직원의 모든 속성을 검색하는 SQL문이다.

SELECT * FROM 직원 WHERE 월급 BETWEEN 300 AND 500;
LIKE연산자 활용 예시

'직원'테이블에 '이름'이 '유'로 시작하는 직원의 모든 속성을 검색하는 SQL문이다.

SELECT * FROM 직원 WHERE 이름 LIKE '유%';

이렇게 꼭 앞자리의 검색이 아니여도 사용할 수 있다. '%유'를 사용하게 되면 뒷자리가 '유'로 끝나는 단어를 검색하게 된다. 또한 '_'의 사용으로 글자수를 지정할 수 있다. '__피자'를 사용하면 앞 두 글자 뒤에 피자가 붙는 데이터를 검색하게 할 수 있다.

IN 연산자 활용 예시

'직원'테이블에 부서가 '개발', '기획'인 직원의 모든 속성을 검색하는 SQL문이다.

SELECT * FROM 직원 WHERE 부서 IN ('개발', '기획');

IN연산자는 BETWEEN AND 연산자나 OR연산자보다 가독성이 훨씬 좋기 때문에 BETWEEN AND, OR연산자를 IN연산자로 바꿔서 사용하는 경우가 많다. 하지만 IN 연산자는 OR 연산자로 완벽하게 바꿀 수 있지만OR 연산자는 IN연산자로 완벽하게 바꿀 수 없다.[6]

그룹 검색

GROUP BY구문을 사용하여 하나 이상의 열을 기준으로 그룹으로 묶어 결과를 가져오는 집계함수로써 데이터를 묶어서 처리할 때 사용된다. 보통 집계, 통계등에 많이 사용되어 SQL 구문에서 중요한 역할을 하게 된다. 또한 HAVING 구문을 추가사용하여 GROUP BY절에 집계한 결과에 조건을 정해줄 수 있다.[7]

그룹 검색 예시
SELECT 학년, AVG(키)
FROM 학생
GROUP BY 학년 HAVING AVG(height) >= 170;

'학생'테이블에서 학년과 키를 검색하는 SQL문 인데, 그 키의 평균이 170이상인 학년만 출력하게 된다. GROUP BY구문으로 '학년'을 그룹화하고, 그 HAVING 구문을 사용하여 '학년'그룹에 170이상의 제약조건을 설정하게 된다. 여기서 사용된 'AVG'는 집계함수이며, 평균을 나타낼 때 사용한다.

집계 함수[8]
함수 내용
COUNT() 특정 컬럼의 행의 개수를 카운트한다.
SUM() 특정 컬럼의 총합을 나타낸다.
MAX() 특정 컬럼의 최대값을 반환한다.
MIN() 특정 컬럼의 최솟값을 반환한다.
AVG() 선택된 컬럼의 평균을 계산한다.
STDDEV() 특정 컬럼의 표준편차를 계산한다.
VARIAN() 툭종 컬럼의 분산을 계산한다.

정렬 검색

기본적으로 SELECT문을 사용할 때 출력되는 결과물은 테이블에 입력된 순서로 출력이 된다. 하지만 데이터의 내림차순 혹은 오름차순으로 정렬된 데이터를 필요로 할 때 사용하는 것이 ORDER BY 절이다.ORDER BY는 항상 SELECT문의 맨 마지막에 위치하며 가장 나중에 실행된다.[9]

일반적 정렬 검색
SELECT [PREDICATE] 테이블명.속성명[테이블명.속성명, ...]
FROM 테이블명1,[테이블명2, ...]
[WHERE 조건]
ORDER BY 속성명(ASC/DESC)[,속성명, ...];
정렬 검색 예시
SELECT 학생번호
FROM 학생
ORDER BY 학생번호 ASC;

'학생' 테이블의 '학생번호'를 검색할 때 '학생번호'를 오름차순으로 정렬하는 SQL문이다. ASC는 오름차순 정렬을 할 때 사용되고, DESC는 내림차순으로 정렬할 때 사용하면 된다. ASC는 생략하고 사용할 수 있다.

하위 질의

다른 테이블과 결합하여 결과를 얻어 내려하면 얻기 어려운 경우가 있다. 그럴 때 하위질의 즉, 서브쿼리(sub query)를 사용하여 필요한 데이터를 얻어낼 수 있다. SELECT문안에 다시 SELECT문이 기술된 형태이며, 서브쿼리의 결과를 메인 쿼리에서 받아 처리하는 구조로 '중첩된 쿼리'라고도 부른다. 단일 SELECT문으로 조건식을 만들기 어렵고 조건이 복잡할 때는 완전히 다른 테이블에서 데이터를 조회하여 메인쿼리의 조건으로 만족시켜 사용할 때 유용하다. 다시말해 쿼리문을 두 번 작성하여 결과를 출력해야 하는 SELECT문을 한 번만 작성해서 처리할 수 있도록 한다. 서브 쿼리의 종류는 단일행, 다중행, 다중컬럼 서브쿼리가 있으며 위치에 따라 여러 절에서 서브쿼리를 사용할 수 있다.[10]

단일행 서브쿼리

SELECT C1, C2, C3
FROM T1
WHERE C1 = (SELECT C1 FROM T2 WHERE C2 = '3');

'T1'테이블의 C1, C2, C3 속성을 검색할 때, 'T2'의 있는 'C2'의 내용이 '3' 인 'C1'의 속성이 T1의 'C1' 속성에 참조된다. WHERE문의 SELECT문을 한번 더 넣어줌으로써 다른 테이블의 결과도 사용할 수 있게 된다. 서브쿼리가 단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용될 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다.[11]

다중행 서브쿼리

SELECT C1, C2, C3
FROM T1
WHERE C1 IN (SELECT C1
            FROM T2
            WHERE C2 = '3');

서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)과 함께하여 사용한다. T2의 테이블의 C2가 '3'인 조건을 만족하는 C1의 값이 2건 이상인 경우에는 '='가 아닌 'IN'을 사용하여 표현한다.

다중 행 비교 연산자[11]
연산자 의미
IN 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미
ALL 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미
ANY, SOME 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미
EXISTS 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미
  • IN 연산자는 다음올 괄호안의 값(서브쿼리)들 중 하나라도 일치하는 메인쿼리를 반환하게 된다.
  • ANY, SOME 연산자 앞에 비교 연산자를 사용할 수 있는데 '='와 사용했을 때 IN 연산자와 같은 기능을 하지만 보통은 알아보기 쉽도록 IN 연산자를 사용한다. '<' 연산자를 사용했을 경우 서브쿼리의 결과 값 중 최대 값보다 낮은 메인쿼리 값이 나오게 되고, 반대로 '>'연산자를 사용했을 경우 서브쿼리 결과 중 최소값보다 높은 메인쿼리 값이 나오게 된다. 예를 들어 서브쿼리의 결과 값이 (950, 1200, 2500, 2800)이 나왔을 때 '< ANY' 가 사용되면 2800보다 낮은 값들로 메인쿼리 값으로 출력된다.
  • ALL 연산자 서브쿼리의 모든 결과가 조건식에 맞아야만 메인쿼리의 조건식이 되는 연산자이다. 즉, IN 연산자는 임의의 값과 동일하면 되지만 ALL연산자는 모든 값을 만족해야 한다. '<'의 연산자를 사용했을 때 ANY, SOME 연산자는 서브쿼리 결과의 최대 값 보다 낮은 결과가 나오는 메인 쿼리의 조건식이 되지만 ALL 연산자는 서브쿼리의 모든 결과 값을 충족해야하기 때문에 최소값을 기준으로 낮은 값이 결과가 되는 메인쿼리의 조건문이 된다.
  • EXISTS 연산자는 서브쿼리에 결과 값이 1개 이상 존재하면 조건식 모두 출력, 존재하지 않으면 모두 출력하지 않는 연산자이다. 특정 서브쿼리 결과 값의 존재 유무를 통해 메인쿼리의 데이터 노출 여부를 결정해야 할 때 간혹 사용된다.

다중컬럼 서브쿼리

SELECT C1, C2,C3
FROM T1
WHERE (C1, C2) IN (SELECT C1, C2 FROM T1 WHERE C2 ='3');

서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식을 다중컬럼 서브쿼리라 한다. 메인쿼리에 비교할 열을 괄호로 묶어 명시하고, 서브쿼리에서는 괄호로 묶은 데이터와 같은 자료형 데이터를 SELECT절에 명시하여 사용할 수 있다.

위치에 따른 서브쿼리

서브쿼리는 다양한 위치(SELECT, FROM, WHERE, HAVING, ORDER BY 등) 뒤에서 괄호와 함께 SELECT문을 이용하여 사용가능하다. 그 중 FROM절에 사용되면 (View)처럼 결과가 동적으로 생성된 테이블로 사용할 수 있다. 이것을 인라인 뷰(Inline View) 라고 한다. 인라인 뷰로 동적으로 생성된 테이블이여서 인라인 뷰의 속성은 자유롭게 참조가 가능하다.[12]

결합 검색

두 개의 테이블을 연결(결합)하여 데이터를 출력하는 것을 JOIN이라고 하는데, 결합 검색일 조인 검색이라고 한다. 두 개 이상의 테이블을 조인하기 위해 외래키(foreign key)가 적어도 하나 이상 있어야한다. INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, SELF JOIN 등 방법이 있다.[13]

일반적인 INNER 조인
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.속성 = 테이블2.속성;
SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.속성 = 테이블2.속성;
SELECT * FROM 테이블1 JOIN 테이블2 USING 테이블1.속성 = 테이블2.속성;
SELECT * FROM 테이블1 NATURAL JOIN 테이블2;
일반적인 OUTER 조인
SELECT * FROM 테이블1 (LEFT/RIGHT/FULL) JOIN 테이블2 ON 테이블1.속성 = 테이블2.속성;
SELECT * FROM 테이블1 WHERE 테이블1.속성 = 테이블2.속성(+); //LEFT OUTER JOIN
SELECT * FROM 테이블1 WHERE 테이블1.속성(+) = 테이블2.속성; //RIGHT OUTER JOIN
일반적인 SELF 조인
SELECT * FROM 테이블1 AS A JOIN 테이블 AS B ON A.속성 = B.속성;

INNER 조인은 쉽게 말하여 교집합이라 생각하면 된다. 기존 테이블과 조인한 테이블의 중복된 값을 보여준다. OUTER 조인은 조건에 해당하지 않는 속성값도 NULL을 대입하여 결과값에 표현하여 나타낸다. 즉, LEFT OUTER JOIN은 테이블2의 맞지 않는 테이블1의 튜플에 NULL을 대입해서라도 출력되고, 반대로 RIGHT OUTER JOIN은 테이블1에 맞지 않는 테이블2의 튜플에 NULL값을 넣어서 출력이 된다. FULL OUTER JOIN은 테이블1,2 가 가지고있는 데이터 모두를 검색하게 된다. SELF JOIN은 자기 자신을 'AS'로 치환하여 조인하는 방법으로 하나의 테이블에서 여러번 복사하여 조인된다. 자신이 가지고 있는 속성을 다양하게 변형시켜 활용할 경우에 자주 사용된다.

삽입

원래 있던 테이블에 데이터를 입력할때는 INSERT문을 사용한다. 사용할 때 나열된 속성과 값 타입, 갯수가 일치해야 하며 속성의 위치와 값이 다르게 된다면 원치않는 위치에 데이터가 삽입되는 현상이 발생한다.[14]

일반적 삽입문
INSERT INTO 테이블명(속성1, 속성2, ...)
VALUES (값1, 값2, ...)
삽입문 활용 예시
SELECT INTO 학생(이름, 학생번호, 학년)
VALUES (김정보, 60811, 6);

VALUES에 데이터 값을 기입하여 순서대로 이름, 학생번호, 학년에 데이터 값을 삽입하게 된다. 이 때문에 삽입문을 사용할 때 속성의 순서와 값의 개수는 꼭 일치해야한다. INSERT절에 속성명을 생략할 경우 테이블에 존재하는 모든 속성의 값을 순서대로 넣어줘야 한다. 예를 들어 '학생'테이블에 학년, 번호, 이름, 성별, 키의 속성이 존재하면 순서대로 모두 값을 넣어줘야한다.

SELECT 삽입문 활용 예시
INSERT INFO 학생(학년, 이름, 성별)
SELECT * FROM 학생2

많은 양의 데이터를 삽입할 때 SELECT문을 사용하여 테이블을 지정한 뒤 조회한 값을 INSERT하는 방법이 있다. '학생2' 테이블을 지정하여 모든 속성 값을 학생 테이블에 학년, 이름, 성별에 삽입하는 SQL문이다.

수정

UPDATE는 이미 존재하는 값을 변경하기 위해 사용되며 SELECT문으로 가장 많이 사용된다. 데이터 수정에 있어 WHERE 절 조건식을 사용하여 조건식에 일치하는 데이터만 바뀌게 설정해 주어야 한다. 그렇지 않을 때 모든 데이터가 지정되어 다량의 데이터가 원하지 않는 값으로 변경될 수 있어 주의해야 한다.[15]

일반적인 변경문
UPDATE 테이블명 
SET 속성1 = 변경할 값,
속성2 = 변경할 값
WHERE 조건식;
변경문 활용 예시
UPDATE 학생 SET 키 = 146 WHERE 이름 = '김정보' ;

삭제

DELETE문은 WHERE절에서 제시한 조건을 만족하는 튜플만 삭제한다. WHERE문을 생략할 경우 모든 튜플을 삭제한다. 이 경우 모든 튜플은 사라지지만 테이블이 삭제되는게 아니므로 데이터 정의어(DDL, Data Definition Language)의 DROP과는 다르다.

일반적인 삭제문
DELETE FROM 테이블명 WHERE 조건문;
삭제문 활용 예시
DELETE FROM 학생 WHERE 학생번호 = 60102;

각주

  1. 1.0 1.1 미니송, 〈데이터베이스 언어(DDL, DML, DCL) - DO의 IT〉, 《출처》, 2017-12-05
  2. 데이터 조작 언어〉, 《위키백과》
  3. 3.0 3.1 subsay , 〈데이터베이스의 개념 - 데이터베이스 언어〉, 《티스토리》, 2017-09-20
  4. Select (SQL)〉, 《위키백과》
  5. 5.0 5.1 datata29, 〈(SQL) SELECT 문 (SELECT, FROM ,WHERE...etc) 〉, 《velog》, 2021-02-09
  6. 홍큐리티, 〈SQL-관계 연산자와 LIKE 연산자, BETWEEN, IN〉, 《티스토리》, 2019-12-04
  7. 진격의 파파, 〈SQL GROUP BY 구문〉, 《티스토리》, 2013-10-08
  8. 브이아이풋볼, 〈[1]〉, 《네이버블로그》, 2019-11-16
  9. Gom Guard, 〈(SQL) 4. 정렬해서 출력하기 - ORDER BY 절〉, 《티스토리》, 2017-12-09
  10. 서브쿼리란?〉, 《더북》
  11. 11.0 11.1 꽁담, 〈(DATABASE) 서브쿼리란? 서브쿼리 사용해보기〉, 《티스토리》, 2018-11-13
  12. SNOWPLE, 〈SQL / MySQL 서브쿼리(SubQuery)〉, 《티스토리》, 2011-12-14
  13. 양갱맨, 〈정보처리기사 실기 SQL(2)-JOIN〉, 《티스토리》, 2019-04-07
  14. 코딩팩토리, 〈[Oracle 오라클 INSERT문(삽입) 사용법 & 예제]〉, 《티스토리》, 2019-11-13
  15. [sql-%ED%85%8C%EC%9D%B4%EB%B8%94-%EC%95%88%EC%9D%98-%EC%BB%AC%EB%9F%BC-%EA%B0%92-%EB%B3%80%EA%B2%BD-%EC%88%98%EC%A0%95-update (SQL) 테이블 안의 컬럼 값 변경, 수정, UPDATE]〉, 《WEBISFREE》, 2016-07-06

참고자료

같이 보기


  검수요청.png검수요청.png 이 데이터 조작어 문서는 데이터에 관한 글로서 검토가 필요합니다. 위키 문서는 누구든지 자유롭게 편집할 수 있습니다. [편집]을 눌러 문서 내용을 검토·수정해 주세요.