SQL

I. SQL 개요

 

1. 정의

SQL(Structured Query Language)은 관계형 데이터베이스에 접근(생성, 변경, 삭제 조회)하기위한 비절차적 표준 언어.

 

2. 종류

1. DML(Data Manipulation Language, 데이터 조작어)

2. DDL(Data Definition Language, 데이터 정의어)

3. Transaction Control Language

4. Session Control Language

5. System Control Language

6. 임베디드 SQL

 

II. DML(Data Manipulation Language, 데이터 조작어)

스키마를 가진 오브젝트(테이블, 뷰, ...)가 데이터를 조회하거나 변겨으 수정, 삭제하는 행위에 대한 SQL 문장

i. SELECT

데이터 조회

1. 구성

1.1 SELECT

추출할 데이터 항목 기술

ex) 테이블의 컬럼명/레코드 수/컬럼을 더한 값, 날짜, ...

1.2 FROM

무슨 테이블의 데이터를 조회할 것인지 기술

다시 SELECT 문장을 작성하여 인라인뷰를 만들 수도 있다.

여러 개의 테이블이 기술도어 있으면 카르테시안 조인으로 테이블을 참조한다.

1.3. WHERE

대상이 되는 테이블에서 가져올 데이터에 대해 조건을 부여하여 데이터를 조회하는 경우에 기술한다.

1.4. HIERARCHY

부모와 자식으로 연결된 테이블에서 계층 구조로 데이터를 가져오기 위해 기술한다.

1.5. GROUP BY

가져온 데이터를 그룹으로 조합하여 그룹에 대해서 데이터를 생성한다.

기술되는 항목은 반드시 SELECT 절에 존재하는 컬럼이어야 한다.

그룹으로 묶인 다음에 컬럼에는 그룹 함수(SUM, COUNT, ...)가 반드시 따라와야 한다.

1.5.1. HAVING

GROUP BY 절에서 그룹으로 구성한 내용들 중 조건을 부여하여 조건에 해당되는 그룹의 데이터를 생성한다.

1.6. UNION

동일한 구성의 SQL 문장을 작성하여 통합하거나 제외시키는 일을 수행한다.

1.7. ORDER BY

가져온 데이터를 주어진 컬럼값에 의해 오름차순이나 내림차순으로 정렬한다.

ASC(기본 값), DESC

1.8. FOR UPDATE

데이터를 가져온 테이블에 잠금(Lock)을 설정하여 다른 SQL 문장이 해당 테이블의 테이터를 수정하지 못하게 한다.

1.9. 문장 처리 순서

FROM -> WHERE -> GROUP BY -> (HAVING) -> SELECT -> ORDER BY

(내부적으로는 최적화된 알고리즘을 가지고 수행되고, 위와 같은 순서로 발생하지 않음)

1.10. 예시

--사원 테이블에서 사장이 아닌 사원들의 급여 합이 천만 원이 넘는 부서와 금액을 조회
SELECT DEPTNO AS 부서번호,
       SUM(SAL) AS 급여총액
FROM EMP
WHERE JOB <> '사장'
GROUP BY DEPTNO
HAVING SUM(SAL) > 10000000
ORDER BY DEPTNO ASC;

2. 종류

2.1. 단순 SELECT

한 개의 테이블에서 데이터 출력

2.1.1 예시

- 조회, 컬럼 이름 변경, 컬럼에 문자열 삽입

--조회
--'*' 이용
SELECT *
FROM EMP;
--컬럼 이름 이용
SELECT EMPNO, ENAME, JOB
FROM EMP;
--

--컬럼 이름 변경
--AS 사용
SELECT EMPNO AS 사원번호, ENAME AS 사원명, JOB AS 직책
FROM EMP;
--공백 사용
SELECT EMPNO 사원번호, ENAME 사원명, JOB 직책
FROM EMP;
--

--컬럼에 문자열 삽입
SELECT '문자열', ENAME, JOB
FROM EMP
--

- NULL 값 판단, NULL 체크

--'=' 사용
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB = NULL;
--'IS' 사용
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB IS NULL;

- DISTINCT

중복 제거: 뒤에 오는 컬럼값을 모두 조합하여 중복되는 로우를 제거한다.

DISTINCT GROUP BY 차이 추가하자

SELECT DISTINCT DEPTNO
FROM EMP;

- LIKE/SUBSTR

LIKE SUBSTR 차이: LIKE는 인덱스를 이용하고, SUBSTR은 컬럼의 문자열을 변형하기 때문에 인덱스를 사용하지 않는다.

--LIKE 사용
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE ENAME LIKE '이%';
--SUBSTR 사용
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE SUBSTR(ENAME, 1, 1) = '이%';

- BETWEEN

SELECT EMPNO, ENAME, JOB, HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN '20201116' AND '20210203'

- IN/OR

IN OR 성능 차이 알아보자

--IN 사용
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB IN ('점원', '관리자', '총무');
--OR 사용
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB = '점원'
OR    JOB = '관리자'
OR    JOB = '총무';

 

2.2. 조인 SELECT

테이블 관계에 의해 발생하는 조인 뿐 아니라 테이블간 비교 연산자(=, <, >, ...), 조인 문장을 실행하는 방법(Nested Loop Join, Sort merge Join, Hash Join, ...)에 따라 조인 문장을 구분하기도 한다.

2.2.1. 카르테시안 조인(Cartaesian Join)

테이블간의 모든 경우의 수에 대해서 로우가 생성되는 조인 방식이다.

조인된 테이블 사이에 조건이 걸리지 않을 경우에 발생한다.

SELECT DNAME, ENAME
FROM DEPT, EMP;

2.2.2. 내부 조인(Inner Join, 이너 조인)

조인되는 테이블간에 서로 연결되는 컬럼이 존재하여 이것에 의해 연결된 로우의 데이터가 출력된다.

일반적으로 테이블간의 관계에서 참조 무결성 관계를 통해 발생된 컬럼에 의해 조인이 발생한다.

일반적으로 부르는 조인은 내부조인이다.

-- DEPTNO를 통한 관계가 존재한다. 관계를 통해 테이블을 조인한다.
-- 'A'와 'B' 등 테이블의 별칭을 지정할 수 있다.
-- 컬럼 이름이 조인된 테이블 내에서 독릭접이라면 컬럼 이름만 기입하면 된다.
-- 다른 테이블에도 동일한 컬럼 이름이 존재한다면 '테이블이름.컬럼이름'으로 기술해야 한다.
 --FROM DEPT A INNNER JOIN EMP B
SELECT A.DNAME, B.ENAME
FROM DEPT A, EMP B
WHERE A.DEPTNO = B.DEPTNO
ORDER BY A.DNAME ASC;

2.2.3. 외부 조인(Outer Join, 아우터 조인)

조인되는 테이블에서 B 테이블에 연결되는 컬럼값이 존재하지 않더라도(NULL 값) A 테이블의 데이터를 가져올 수 있는 조인 방법이다.

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.

OR, IN, SUBQUERY와 함께 사용할 수 없다.

--오라클
SELECT A.DNAME, B.ENAME
FROM DEPT A, EMP B
WHERE A.DEPTNO (+) = B.DEPTNO 
--MySQL
SELECT A.DNAME, B.ENAME
FROM DEPT A LEFT (OUTER) JOIN EMP B

 

2.3. 그룹 SELECT

조회하고자 하는 컬럼값 전체를 출력하지 않고 값을 더하거나 평균을 구하는 등 일정한 그룹에 의해 가공하여 출력하게 하는 SELECT

2.3.1. COUNT

테이블의 로우 수를 출력, 테이블 로우 수

--EMP 테이블의 전체 로우 수 구하기

--'*' 사용
SELECT COUNT(*)
FROM EMP;
--일반 상수 사용
SELECT COUNT('A')
FROM EMP;
--컬럼 사용, 해당 컬럼이 NOT NULL이거나 데이터값이 모두 존재하는 경우에만 이용해야 전체 로우 수를 구할 수 있다.
SELECT COUNT(EMPNO)
FROM EMP;

2.3.2. SUM

컬럼값을 더하여 더하여 출력, 컬럼값 덧셈

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;

2.3.3. AVG

컬럼값의 평균을 구하여 출력, 컬럼값 평균

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

2.3.4. MIN

대상이 되는 로우에서 제일 작은 컬럼값에 대한 로우 출력, 최솟값

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO;

2.3.5. MAX

대상이 되는 로우에서 제일 큰 컬럼값에 대한 로우 출력, 최댓값

SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO;

2.4. 다중 SELECT

2.4.1. 네스티드 서브쿼리(Nested Subquery)

메인쿼리 절을 수행하기 위해 또 다른 SELECT 문장이 필요한 경우에 작성

WHERE절이나 HAVING 절에서 사용

서브쿼리 조인 성능

--서브쿼리와 조인을 이용한 문장 처리 기준은 SQL 문장의 성능이 어떤 경우에 더 유리한지 판단하여 결정한다.


--1. 비교 연산자를 이용한 단일값 비교 (=, <>, <, >, <=, >=)
--네스티드 서브쿼리 이용
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
FORM EMP
WHERE DEPTNO = (SELECT DEPTNO
                FROM DEPT
                WHERE DNAME = '생산부');
--조인 이용
SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.DEPTNO
FORM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND   B.DEPTNO = '생산부';
--


--2. IN을 이용하여 다중값 비교
--네스티드 서브쿼리 이용
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
FORM EMP
WHERE DEPTNO IN (SELECT DEPTNO
                 FROM EMP
                 WHERE JOB = '과장');
--조인 이용
SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.DEPTNO
FORM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND   B.JOB = '과장';
--

--3. EXISTS/NOT EXISTS을 이용하여 다중값 비교
--상수값 이용
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
FORM DEPT A
WHERE EXISTS (SELECT 'X'
              FROM EMP B
              WHERE A.DEPTNO = B.DEPTNO
              AND   JOB = '과장');
--컬럼값 이용
SELECT A.EMPNO, A.ENAME, A.JOB, A.HIREDATE, A.SAL, A.DEPTNO
FORM DEPT A
WHERE EXISTS (SELECT 'DEPTNO'
              FROM EMP B
              WHERE A.DEPTNO = B.DEPTNO
              AND   JOB = '과장');
--

2.4.2. 인라인뷰(Inline View)

FROM 절에서 사용된다.

개발중에 뷰가 필요하다고 판단되어 모든 경우에 뷰를 생성할 경우 관리할 양이 너무 많아지게 된다. 그렇다고 SQL 문장을 나누기는 트랜잭션 관리나 성능상 곤란한 경우에 인라인뷰를 이용하면 편리하다.

--인라인 뷰 사용
SELECT DISTINCT A.JOB
FROM (SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = '10') A,
     (SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = '20') B,
     (SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = '30') C
WHERE A.JOB = B.JOB
AND   B.JOB = C.JOB;

--일반 조인 이용
SELECT DISTINCT A.EMPNO, A.ENAME, A.JOB
FROM EMP A, EMP B, EMP C
WHERE A.JOB = B.JOB
AND   B.JOB = C.JOB;
AND   A.DEPTNO = '10';
AND   B.DEPTNO = '20';
AND   C.DEPTNO = '30';

--네스티드 서브쿼리를 이용한 경우
SELECT DISTINCT A.EMPNO, A.ENAME, A.JOB
FROM EMP A
WHERE A.DEPTNO = 10
AND   EXISTS (SELECT B.JOB
              FROM EMP B
              WHERE A.JOB = B.JOB
              AND   B.DEPTNO = 20)
AND   EXISTS (SELECT C.JOB
              FROM EMP C
              WHERE A.JOB = C.JOB
              AND   C.DEPTNO = 30);

 

2.5. 집합연산 SELECT

2.5.1. UNION

합집합(중복 제외), SQL 합집합, 쿼리 합집합, 중복 제외 합집합

두 개의 테이블에서 조회하여 중복을 제외한 모든 로우 출력

두 테이블에 동일한 수의 컬럼이 존재해야 한다. 컬럼의 이름은 달라도 무관하다.

두 테이블에서 조회하는 데이터의 타입이 서로 일치해야 한다.

두 테이블에 있는 내용을 비교해야 하므로 DBMS 메모리에서 추가적으로 정렬 작업 및 비교하는 작업이 발생하여 성능이 저하될 수 있다.

SELECT ORDERNM
FROM I_ORDER
UNION
SELECT ORDERNM
FROM C_ORDER;

2.5.2. UNION ALL

SQL 합집합(중복 포함), SQL 합집합, 쿼리 합집합, 중복 포함 합집합

두 개의 테이블에서 조회하여 중복을 포함한 모든 로우 출력

모든 데이터를 조회하므로 메모리에서 추가적으로 정렬 작업이 발생하여 비교하는 작업이 없으므로 성능이 좋다.

SELECT ORDERNM
FROM I_ORDER
UNION ALL
SELECT ORDERNM
FROM C_ORDER;

2.5.3. INTERSECT

SQL 교집합, SQL 교집합, 쿼리 교집합

두 개의 테이블에 모두 존재하는 데이터에 대한 한 로우만 출력(한 번만)

두 테이블에 있는 내용을 비교해야 하므로 DBMS 메모리에서 추가적으로 정렬 작업 및 비교하는 작업이 발생하여 성능이 저하될 수 있다.

SELECT ORDERNM
FROM I_ORDER
INTERSECT
SELECT ORDERNM
FROM C_ORDER;

2.5.4. MINUS

SQL 차집합, SQL 차집합, 쿼리 차집합

첫번째 테이블에만 존재하는 데이터에 대해 한 로우만 출력

두 테이블에 있는 내용을 비교해야 하므로 DBMS 메모리에서 추가적으로 정렬 작업 및 비교하는 작업이 발생하여 성능이 저하될 수 있다.

SELECT ORDERNM
FROM I_ORDER
MINUS
SELECT ORDERNM
FROM C_ORDER;

 

ii. INSERT

데이터 입력, 데이터 삽입

1. INSERT INTO

데이터가 입력되어야 할 테이불이나 뷰를 지정한다.

 

2. VALUES

INSERT INTO 절에 정의한 테이블이나 뷰에 입력될 데이터를 기술한다.

단건 처리일 경우에는 데이터를 직접 입력한다.

여러 건을 처리할 경우에는 SUBQUERY 문장을 이용하여 처리할 수도 있다.

 

단건 INSERT

한 개의 로우만 입력한다.

--테이블 컬럼 지정
INSERT INTO ITEAM (ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE)
       VALUSE ('1003', '팔찌-XG', 50, 20000, '청색', '20020405');

--테이블 컬럼 지정 안함
--테이블 전체 컬럼에 대해 데이터를입력하는 경우에는 컬럼을 생략해도 무방(테이블 컬럼의 순서와 타입이 일치해야함)
INSERT INTO ITEAM
       VALUSE ('1003', '팔찌-XG', 50, 20000, '청색', '20020405');

 

다건 INSERT

테이블에 여러 개의 로우를 입력한다.

읽어오는 테이블에서 ORDER BY 절을 사용할 수 없다.

입력할 테이블과 읽어오는 테이블의 컬럼 타입이 동일해야 한다.

컬럼을 생략하기 위해서는 두 테이블이 동일한 스키마 구조를 가져야 한다.

두 테이블이 다르고 테이블의 일부 컬럼이 동일하다면 반드시 컬럼명을 명시해서 문장을 작성한다.

데이터를 임시로 백업받은 이후에 테이블에 배치 작업이나 스키마 변경 작업을 수행할 때 많이 이용한다.

--두 테이블의 컬럼 지정
INSERT INTO ITEAM_T (ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE)
       SELECT ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE
       FROM ITEM;
--두 테이블의 컬럼 지정 안함
INSERT INTO ITEAM_T
       SELECT * FROM ITEM;
--입력 테이블의 컬럼 지정 안함
INSERT INTO ITEAM_T (ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE)
       FROM ITEM;
--읽는 테이블의 컬럼 지정 안함
INSERT INTO ITEAM_T (ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE)
       SELECT ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE
       FROM ITEM;

 

NULL INSERT

테이블의 특정 컬럼에 NULL을 입력한다.

테이블 구조가 NOT NULL 제약조건이 걸려 있는 경우에는 NULL을 입력할 수 없다.

숫자 형식도 컬럼을 지정할 때도 작은따옴표를 사용해야 한다.

--테이블 컬럼 지정, 컬럼값에 '' 지정
INSERT INTO ITEAM (ITEMCD, ITEMNM, QTY, PRICE, COLOR, MADEDATE)
       VALUSE ('1003', '팔찌-XG', '', 20000, '청색', '20020405');
--테이블 컬럼 지정 안함, 컬럼값에 '' 지정
INSERT INTO ITEAM
       VALUSE ('1003', '팔찌-XG', '', 20000, '청색', '20020405');
--테이블 컬럼에 NULL 대상 컬럼을 제외
INSERT INTO ITEAM
       VALUSE ('1003', '팔찌-XG', '', 20000, '청색', '20020405');

 

2.1.4 UPDATE문: 데이터 수정

1. UPDATE

데이터를 수정해야 할 테이블이나 뷰를 지정한다.

 

2. SET

수정해야 할 컬럼을 기술하고 수정해야 할 값을 지정한다

 

3. WHERE

대상이 되는 테이블에서 가져올 데이터에 대해 조건을 부여하여 데이터를 조회하는 경우에 기술한다.

서브쿼리를 작성하여 조건을 다양하게 만들 수 있다.

 

--조건에 따른 UPDATE
UPDATE ITEM
SET QTY = 500
WHERE IREMCD = '1001';
--테이블 전체 UPDATE
UPDATE ITEM
SET QTY = 500

 

 

 

2.1.4 DELETE문: 

1. DELETE

데이터를 삭제할 테이블이나 뷰를 지정한다.

 

2. WHERE

대상이 되는 테이블에서 가져올 데이터에 대해 조건을 부여하여 데이터를 조회하는 경우에 기술한다.

서브쿼리를 작성하여 조건을 다양하게 만들 수 있다.

--조건에 따른 DELETE
DELETE ITEM
SET QTY = 500
WHERE IREMCD = '1001';
--테이블 전체 DELETE
DELETE FROM ITEM

 

 

1.2.2. DDL(Data Definition Language, 데이터 정의어)

 

 

 

1.2.3. Transaction Control Language

1.2.4. Session Control Language

1.2.5. System Control Language

1.2.6. 임베디드 SQL

 

 

 

 

 

작성중 입니다...