의존성 주입이란 애플리케이션 작동에 필요한 객체를 제 3자로부터 제공받는 것이다. 그리고 그 제 3자가 스프링 컨테이너이다. 그래서 스프링 컨테이너는 애플리케이션에서 사용되는 객체(스프링 빈)의 라이프사이클(객체 생성, 의존성 주입, 소멸)을 관리한다. 스프링 컨테이너의 종류는 다음과 같다.

• BeanFactory

  • spring-bean.jar에 포함되어 있는 스프링 컨테이너 인터페이스이다. 객체 생성, 의존성 주입과 같은 간단한 기능만 제공한다.
  • 구현 클래스 : XmlBeanFactory

• ApplicationContext

  • spring-context.jar에 포함되어 있는 스프링 컨테이너 인터페이스이다. 객체 생성, 의존성 주입, 관점지향 프로그래밍, 국제화, EJB 연동 등 다양한 기능을 제공한다.
  • 구현 클래스 : ClasspathXmlApplicationContext, FilesystemXmlApplicationContext, GenericXmlApplicationContext

• WebApplicationContext

  • spring-web.jar에 포함되어 있는 스프링 컨테이너 인터페이스이다. ApplicationContext 인터페이스와 기능은 유사하고, 웹 환경에 최적화되어 있다.
    • 구현 클래스 : XmlWebApplicationContext

스프링 빈

위에서 언급했듯이 스프링 빈이란 스프링 컨테이너가 관리하는 Java 객체를 의미한다. 스프링 컨테이너가 스프링 빈을 관리하도록 하기 위해서는 스프링 빈 설정파일에 객체들의 정보를 정의해서 스프링 빈으로 등록해야 한다.

의존성 주입

객체간의 의존관계

package com.sample.controller;

import com.sample.dao.UserOracleDao;

public class UserController {

    private UserOracleDao userOracleDao;

    public void setUserOracleDao(UserOracleDao userOracleDao) {
        this.userOracleDao = userOracleDao;
    }

    // 의존성 주입이 되었는지 확인하는 메서드.
    public void test() {
        System.out.println(userOracleDao);
    }
}

UserController는 UserOracleDao를 멤버변수로 갖고 있고, UserOracleDao를 매개변수로 전달받아 자기 자신의 멤버변수에 대입하는 메서드를 갖고 있다. 그리고 대입된 멤버변수인UserOracleDao를 출력하는 메서드를 갖고 있다. 즉, UserController는 UserOracleDao를 의존한다.

스프링 빈 등록

Spring이란 Java 기반의 엔터프라이즈 애플리케이션 개발을 위한 오픈소스 경량급 애플리케이션 프레임워크다.

• 엔터프라이즈 애플리케이션
  • 엔터프라이즈 애플리케이션은 기업 또는 정부와 같은 환경에서 작동하도록 설계된 대규모 소프트웨어 시스템이다. 은행 거래 애플리케이션이나 정부24같은 인터넷 행정 처리 웹사이트 등 전산 시스템이라 불리는 것들이 그 예시이다.

• 오픈소스
  • 소스코드가 공개되어 있고 특별한 비용지불없이 자유롭게 이용가능하다는 뜻이다.

• 경량급
  • 스프링이 불필요하게 반복되는 코드없이 작성되었다는 의미이다. 스프링 등장 이전의 엔터프라이즈급 프레임워크는 다양한 경우를 처리하기 위해 다양한 기능을 갖도록 만들다 보니 하나의 기능을 위해 너무 많은 구조가 필요하게 되었다. 그러나 스프링은 이러한 복잡성을 해결했다.

• 애플리케이션 프레임워크
  • 특정 계층이나 기술에 국한되지 않고 애플리케이션 전 영역을 지원하는 프레임워크를 말한다. 일반적으로 다른 프레임워크들은 웹 영역이나 데이터베이스 영역과 같이 특정한 분야에 대해서만 지원했다. 반면 스프링은 프로젝트 전체를 설계하는 용도로 사용할 수 있다.

Spring의 주요 특징

• 의존성 주입 (Dependency Injection)

• 제어의 역전 (Inversion Of Control)

• 관점지향 프로그래밍 지원 (Aspect Oriented Programming)

• MVC 패턴

• POJO 기반의 구성 (Plain Old Java Object)

• 트랜잭션 지원

의존성 주입

'의존성'을 가진다는 것은 A 객체가 동작하기 위해 B 객체를 필요로 한다는 의미이다. 즉, 두 객체가 서로 결합되어 있는 형태이다. 그리고 '의존성 주입'은 필요한 객체를 직접 생성하는 것이 아니라 외부로 부터 제공받는 방식을 말한다.

그렇다면 어떻게 외부로 부터 필요한 객체를 '주입'받는가? 스프링 프레임워크가 이러한 의존성 주입을 지원한다. 스프링은 객체들 간의 의존관계를 파악해서, 스프링이 직접 객체를 생성하고 제공한다. 스프링의 가장 핵심적인 특징이 의존성 주입이다.

필요한 객체를 직접 생성하는 것이 아닌, 외부로부터 객체를 제공받으면 클래스간의 결합도를 낮출 수 있다. 의존 관계에 있는 두 클래스 중 한 클래스가 수정되어도 다른 클래스는 수정할 필요가 없다는 의미다. 즉, 확장성이 높은 유연한 프로그램을 만들 수 있다. 이것이 의존성 주입을 사용하는 이유이다.

제어의 역전

제어의 역전이란 객체의 생명주기를 개발자가 아닌 제 3자가 관리하도록 하는 디자인 패턴이다. 스프링 프레임워크에서는 컨테이너가 그 역할을 수행한다.

위에서 언급했듯이 의존성 주입은 필요한 객체를 스프링 프레임워크로부터 제공받는 것이다. 그래서 의존성 주입은 제어의 역전을 달성하기 위한 방법 중 하나이다.

관점지향 프로그래밍 지원

관점지향 프로그래밍이란 프로그램의 공통기능과 핵심기능을 분리시키는 것이다. 로그출력 같은 것이 공통기능의 예시이다. 이를 통해 코드 중복을 피하고 유지보수를 용이하게 할 수 있다.

물론 공통기능을 수행하는 클래스를 따로 만들어 공통기능에 대한 메소드를 필요할 때 마다 호출할 수 있지만, 이것이 관점지향 프로그래밍은 아니다. 관점지향 프로그래밍은 핵심 기능을 수행하는 클래스 내에 공통기능과 관련된 코드가 존재하지 않도록 하는 것이 핵심이다.

MVC 패턴

POJO 기반의 구성

Plain Old Java Object로 직역하면 순수한 자바 객체이다. POJO 기반의 구성이라는 것은 스프링 프레임워크를 이용할 때, 일반적인 Java 언어로 코드를 작성할 수 있다는 의미이다.

이 점이 중요한 이유는 코드가 특정 기술에 종속되면 객체지향의 장점을 잃어버리기 때문이다. 예를 들어 어떤 프레임워크를 사용하기 위해 그 프레임워크가 제공하는 API를 상속, 구현해해야 한다면 코드의 확장성이 떨어진다.

스프링 이전의 프레임워크들은 이러한 문제점을 갖고 있어 개발자가 해당 프레임워크에 종속되었다. 그러나 스프링 프레임워크는 스프링 기술과 관련된 코드가 애플리케이션 코드에 등장하지 않도록 했다.

선언적 트랜잭션 지원

트랜잭션이란 데이터베이스의 상태를 변화시키는 하나의 논리적인 단위이다. 예를 들어 그룹웨어에서 쪽지 기능이 처리된다고 하자. 주고 받은 쪽지는 발신자 측의 보낸 쪽지함에, 수신자 측의 받은 편지함에 동시에 저장되어야 한다. 이 중 어느 한 과정이라도 실패한다면 쪽지 기능이 성공적으로 수행됬다고 할 수 없다. 이처럼 트랜잭션은 여러 번의 데이터베이스 액세스 작업이 묶여있는 논리적인 단위이다.

서비스 클래스의 메서드에는 여러 번의 데이터 베이스 액세스 작업이 포함되어 있는데, 이 중 하나의 작업이라도 실패한다면 나머지 작업도 취소되어야 한다. 그런데 스프링 프레임워크를 사용하면 트랜잭션 처리를 어노테이션이나 xml파일의 설정을 통해 할 수 있다. 개발자가 트랜잭션을 위한 코드를 작성할 필요가 없다는 말이다. 그리고 이러한 방법을 '선언적 트랜잭션'이라고 한다.

라이브러리 vs 프레임워크

라이브러리는 개발에 필요한(자주 사용되는) 기능의 구현체다. 개발자는 필요한 기능이 구현되어 있는 라이브러리를 다운받아서 그 라이브러리의 기능을 사용한다.

반면, 프레임워크는 프로그램의 뼈대나 근간을 이루는 코드들의 묶음이다. 달리 말해, 개발 방식 혹은 개발 패턴의 구현체다. 개발자는 해당 프레임워크가 제시하는 방식에 맞게 클래스나 인터페이스, 환경설정 정보를 구성하면 프레임워크가 지원하는 기능을 제공받을 수 있다.

즉, 라이브러리와 프레임워크의 주요한 차이점은 '누가 제어를 하느냐'이다. 개발자는 라이브러리를 '직접' 호출해서 사용하고, 개발자가 작성한 애플리케이션 코드는 프레임워크에 의해 사용된다. 프레임워크의 경우 처럼 프로그램의 흐름이 개발자가 아닌 제 3자에게 넘어간 것을 '제어의 반전'이라고 하는데, 이 역시 Spring에 반영되어 있다.

웹 애플리케이션

웹 애플리케이션(web application) 또는 웹 앱은 소프트웨어 공학적 관점에서 인터넷이나 인트라넷을 통해 웹 브라우저에서 이용할 수 있는 응용 소프트웨어를 말한다.

소프트웨어를 브라우저에서 이용할 수 있다는 것은 기존의 응용 소프트웨어 혹은 프로그램이라고 불리는 것들을 생각해보면 이해하기 쉽다. 이러한 것들은 하드 디스크에 설치해서 실행했다. 즉, 운영체제 위에서 작동했다. 그러나 웹 애플리케이션은 이보다 한 층 더 올라가 브라우저 위에서 이용할 수 있는 소프트웨어이다. 그리고 프로그램은 사용자의 입력을 받아 설계된 명령어에 따른 결과를 반환한다.

그래서 웹 애플리케이션은 사용자와 '상호작용'하며 브라우저에서 이용가능한 프로그램이다.

브라우저에 접속해서 웹 사이트에 들어가는게 원래 그런거 아닌가? 라고 생각할 수 있지만 그렇지 않다. 과거의 웹 사이트는 브라우저의 요청에 따라 그저 서버에 저장된 문서를 사용자에게 보여줄 뿐이었다. 이러한 웹 사이트를 정적 웹 페이지라 부른다.

그러나 웹 애플리케이션이라고 부르는 것들은 앞서 언급했다시피 사용자와 상호작용하는 기능을 수행한다. 즉, 사용자의 입력에 따라 특정한 연산을 수행하고 그에 따른 결과를 반환한다. 이러한 이유로 웹 애플리케이션을 동적 웹 페이지라고 부른다. 오늘날 대부분의 웹 사이트는 동적 웹 페이지로 구성되어 있다. 대표적인 예가 로그인이다. 로그인은 특정 사용자임을 인증하고 해당 사용자에 따라 다른 화면을 보여준다. 로그인 뿐만 아니라 결제, 댓글, 좋아요와 같은 기능들이 모두 동적 웹 페이지의 예이다.

웹 서버와 웹 애플리케이션 서버

앞서 설명했듯이 웹 브라우저는 '요청'하고 서버는 컨텐츠를 '응답'한다. 브라우저의 주소창에 https://naver.com 를 입력하는 것 그 자체가 NAVER 웹 페이지에 접근하겠다는 요청이다.

웹 서버는 브라우저의 요청에 대해 정적 컨텐츠를 응답한다. 단지 서버에 저장된 컨텐츠를 어떠한 가공없이 응답으로 보낸다. 그러나 웹 애플리케이션 서버(WAS : Web Application Server)는 브라우저의 요청에 따라 특정한 연산을 수행한 결과 즉, 동적 컨텐츠를 응답한다. 그리고 이 WAS에 웹 애플리케이션이 배포되어 있다. 웹 애플리케이션의 로직은 WAS에서 실행되고 사용자는 브라우저에서 웹 애플리케이션에 요청을 보낸다.

WAS는 동적 컨텐츠 뿐만 아니라 정적 컨텐츠도 응답할 수 있기 때문에 WAS만으로도 웹 시스템을 구성할 수 있다. 그러나 WAS의 부담을 줄이기 위해 웹 서버는 정적 컨텐츠만 응답하도록 하고, WAS는 웹 애플리케이션의 로직을 수행하도록 하여 동적 컨텐츠를 응답하도록 구성한다.

HTTP

HTTP(HyperText Transfer Protocol)는 컴퓨터간의 데이터 교환방식을 정의한 약속이다. 웹 브라우저와 웹 서버는 서로 요청과 응답을 주고 받으며 '통신'한다. 여기서 요청하는 측을 클라이언트, 요청에 대해 응답을 제공하는 측을 서버라고 한다. Http는 이처럼 클라이언트와 서버가 주고받는 메세지의 양식을 규정한 통신규약이다. 그리고 웹 브라우저와 웹 서버는 이 Http를 기반으로 작동한다.

Servlet이란?

클라이언트 요청을 처리하고 그 결과를 다시 클라이언트에게 전송하는 Servlet 클래스의 구현 규칙을 지킨 동적 컨텐츠를 생성하기 위한 Java 프로그램이다. 클라이언트의 요청에 대해 동적 컨텐츠를 응답하기 때문에 당연히 웹 애플리케이션 '서버'에서 작동하는 프로그램이다.

이 말이 어떤 의미인지 이해하기 위해 Servlet이 없을 때, 웹 애플리케이션이 브라우저의 요청을 수행하도록 개발자가 어떻게 해야하는지 생각해보자. 아래 사진은 HTTP의 클라이언트의 요청메세지와 서버의 응답메세지이다. 만약 Servlet을 사용하지 않는다면 개발자는 이 메세지를 컴퓨터가 해석할 수 있도록 '파싱'해야한다. HTTP메세지는 그저 문자열이기 때문이다. 요청 메세지를 분석해서 웹 애플리케이션 로직을 수행하는 데 필요한 데이터를 읽어오고, 로직을 수행한 뒤 그 결과를 반환하기 위해 응답 메세지를 구성해야 한다. Servlet이 있다면 이러한 웹 애플리케이션 로직 전, 후의 작업을 대신 수행하여 개발자는 로직에 보다 집중할 수 있다.

즉, 개발자는 클라이언트의 각 요청에 필요한 Servlet 클래스들을 구현해두고, 요청이 올 때마다 그에 대한 처리를 Servlet 객체가 대신하도록 한다. Servlet 객체는 생성, 로직 실행, 소멸을 반복하며 클라이언트의 요청을 처리한다. 그런데 이러한 Servlet 객체(프로그램)의 동작을 개발자가 직접 제어하지 않는다. '서블릿 컨테이너'라고 하는 것이 Servlet 객체의 동작을 관리한다. 다음은 WAS와 서블릿 컨테이너의 구조이다.

HttpServlet 클래스의 구조

HTTP를 기반으로 클라이언트와 서버가 통신할 수 있도록 Servlet을 구현하려면 HttpServlet 클래스를 상속받은 클래스를 구현해야 한다. *다시 말해, 웹 브라우저의 요청을 처리하기 위한 Servlet 클래스를 정의하려면 반드시 HttpServlet를 상속받아야 한다. * HttpServelt 클래스 또한 상위 추상 클래스(GenericServlet)와 인터페이스(Servlet)를 구현한 클래스이다. 이제 그 상속 구조를 알아보자.

• ** Servlet < Interface >**

  • 모든 서블릿 클래스가 반드시 구현해야하는 인터페이스다.
  • 서블릿의 생명 주기와 관련된 추상 메소드가 정의되어 있다.
  • 주요 메소드
    • void init()
      • 서블릿 객체가 초기화될 때 실행되는 메소드다.
    • void service(ServletRequest request, ServletResponse response)
      • 서블릿 객체가 클라이언트의 요청을 처리할 때 실행되는 메소드다.
    • void destroy()
      • 서블릿 객체가 소멸될 때 실행되는 메소드다.

• ** GenericServlet < Abstract class >**

  • Servlet 인터페이스를 구현하는 추상 클래스다.
  • Service()를 제외한 Servlet 인터페이스의 메소드를 모두 구현해 두었다.
  • Servlet 인터페이스에 정의되어 있지 않은 추가 메소드를 제공한다.
  • 주요 메소드
    • String getInitParameter(String name) {...}
      • 서블릿 초기화 파라미터값을 반환한다.
    • ServletConfig getServletConfig() {...}
      • ServletConfig 객체를 반환한다.
    • ServletContext getServletContext() {...}
      • ServletContext 객체를 반환한다.

• ** HttpServlet < Abstract class >**

  • GenericServlet 클래스를 상속받아 HTTP 프로토콜에 알맞은 동작을 수행하도록 service() 메소드를 재정의한 클래스다.
  • HttpServletRequest는 HTTP요청에 있는 정보를 서블릿에게 전달하기 위해 사용되는 객체다.
  • HttpServletResponse는 클라이언트에게 보낼 HTTP응답을 구성하기 위해 사용되는 객체다.
  • 주요 메소드
    • void doGet(HttpServletRequest request, HttpServletResponse response) {...}
      • GET방식의 요청을 처리하는 메소드다.
    • void doPut(HttpServletRequest request, HttpServletResponse response) {...}
      • PUT방식의 요청을 처리하는 메소드다.
    • void doPost(HttpServletRequest request, HttpServletResponse response) {...}
      • POST방식의 요청을 처리하는 메소드다.
    • void doDelete(HttpServletRequest request, HttpServletResponse response) {...}
      • DELETE방식의 요청을 처리하는 메소드다.
    • void service(HttpServletRequest request, HttpServletResponse response) {...}
      • 클라이언트의 요청 방식에 알맞은 메소드를 실행하도록 정의되어 있다.
      • 요청방식에 상관없이 항상 실행되는 메소드다.

• ** 사용자 정의 Servlet 클래스** public class HelloServlet extends HttpServlet {...}

  • HttpServlet 클래스를 상속받아 정의하는 클래스다.
  • ** 어떠한 요청방식을 처리할 것인지 개발자의 의도대로 service()메소드를 재정의한다.**

Servlet의 동작 과정

1. 웹 브라우저(클라이언트)가 URL요청을 보내면 웹 서버는 HTTP Request 메세지를 해석한 뒤, 서블릿에 대한 요청이면 메세지를 서블릿 컨테이너로 보낸다.

2. 서블릿 컨테이너가 HttpServletRequest, HttpServletResponse 객체를 생성한다. (서블릿이 직접 생성하지 않는다.)

3. 서블릿 컨테이너가 web.xml을 분석해서 어느 서블릿에 대한 요청인지 찾는다.

4. 해당 서블릿이 최초 요청이라면, 서블릿 클래스를 메모리에 로드하고 객체를 생성한다. *이후의 요청은 이 때 생성한 객체를 사용하여 요청을 처리한다. 이처럼 객체를 한번만 생성하여 사용하는 것을 '싱글톤 패턴'이라고 한다. *

5. init() 메서드를 실행해서 서블릿 객체의 초기화작업을 수행한다. 이 메서드는 서블릿 객체가 생성된 다음에 호출되는 메서드다. 그래서 최초 요청이 아니라면 init()는 실행되지 않는다.

6. service() 메서드를 실행해서 클라이언트의 요청을 처리한다. 이 때 서블릿 컨테이너가 생성한 HttpServletRequest, HttpServletResponse 객체를 인자로 전달받는다.

7. service() 메서드가 종료되면 서블릿 프로그램이 종료된다. 그리고 서블릿 컨테이너는 실행결과를 웹 서버에 전달하고, HttpServletRequest, HttpServletResponse 객체를 소멸시킨다. 웹 서버는 클라이언트의 요청에 대해 응답을 보낸다.

8. destroy() 메서드는 서블릿이 수정되었을 때 실행된다. 이미 메모리에 로드되어 있는 서블릿을 소멸시켜야 수정된 서블릿을 로드할 수 있기 때문이다.

코드와 콘솔창을 통해 보다 자세히 알아보자.

import java.io.IOException;
import java.io.PrintWriter;
import java.rmi.ServerException;

import jakarta.servlet.ServletConfig;
import jakarta.servlet.ServletContext;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

public class HelloServlet extends HttpServlet {

    // 서블릿 최초 요청 시 init()가 실행된다.
    @Override
    public void init() throws ServletException {
        System.out.println("HelloServlet의 init() 메서드 실행 ...");
    }

    // 소스코드가 변경되면 destroy()가 실행되어 객체가 소멸된다.
    @Override
    public void destroy() {
        System.out.println("HelloServlet의 destroy() 메서드 실행 ...");
    }

    // HTML 컨텐츠를 응답으로 보낸다.
    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServerException, IOException {
        System.out.println("HelloServlet의 service() 메서드 실행 ...");

        // HttpServletRequest 객체를 이용해 응답메세지의 타입을 html로 지정한다.
        response.setContentType("text/html; charset=utf-8");

        // 웹 브라우저에 출력하기 위한 객체를 HttpServletRequest객체를 이용해 생성한다.
        PrintWriter out = response.getWriter();

        // 위에서 생성한 PrintWriter 객체를 이용해 웹 브라우저에 출력할 컨텐츠를 작성한다.
        out.print("<!DOCTYPE html>");
        out.print("<html lang='ko'>");
        out.print("<head>");
        out.print("<meta charst='utf-8'>");
        out.print("<title>헬로 서블릿</title>");
        out.print("</head>");
        out.print("<h1>헬로 서블릿</h1>");
        out.print("<p>나는 서블릿입니다.</p>");
        out.print("<p>서블릿의 라이프 사이클 메서드를 확인하세요.</p>");
        out.print("<p></p>");
        out.print("</body>");
        out.print("</html>");
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://jakarta.ee/xml/ns/jakartaee" xmlns:web="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="https://jakarta.ee/xml/ns/jakartaee https://jakarta.ee/xml/ns/jakartaee/web-app_5_0.xsd http://xmlns.jcp.org/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="5.0">

      <!--
    서블릿의 경로와 별칭을 지정한다.
    -->
    <servlet>
        <servlet-name>hello-servlet</servlet-name>
        <servlet-class>com.sample.servlet.HelloServlet</servlet-class>
    </servlet>
      <!--
    위에서 지정한 서블릿의 별칭과 URL주소를 매핑한다.
    -->
    <servlet-mapping>
        <servlet-name>hello-servlet</servlet-name>
        <url-pattern>/hello</url-pattern>
    </servlet-mapping>    

</web-app>

만약 여기서 소스코드가 수정되면 자동으로 destroy() 메서드가 실행되어 HelloServlet 객체가 소멸된다.

단점

코드를 보면 알 수 있듯 Java 소스코드 내에 HTML 태그가 삽입되어 있다. 위 예시는 간단한 HTML 컨텐츠를 응답으로 보내는 형태이지만, 동적 컨텐츠 응답이라는 서블릿의 목적에 맞게 코드를 작성하면 훨씬 더 복잡해진다. 또한 서블릿 클래스를 작성한 후, URL주소와 해당 서블릿을 web.xml을 통해 매핑해야 한다. 이러한 단점을 극복하기 위해 JSP가 등장했다.

JSP(Java Server Page)

JSP는 HTML내에 Java코드를 삽입하여 '웹 서버'에서 동적 웹 페이지를 생성하여 웹 브라우저에 제공하는 서버 사이드 스크립트 언어다. 위에서 언급한 Servlet의 단점을 극복하기 위해 등장했다. 웹 브라우저로부터 요청이 들어오면 WAS는 JSP파일을 Java코드로 이루어진 Servlet으 변환한다. 그리고 이후의 과정은 Servlet이 실행되는 과정과 똑같다.

아래는 웹 브라우저로부터 요청이 들어온 후, JSP가 실행되는 과정이다.

Reference

https://ko.wikipedia.org/wiki/%EC%9B%B9_%EC%95%A0%ED%94%8C%EB%A6%AC%EC%BC%80%EC%9D%B4%EC%85%98 https://medium.com/@chrisjune_13837/web-%EC%9B%B9%EC%84%9C%EB%B2%84-%EC%95%B1%EC%84%9C%EB%B2%84-was-app%EC%9D%B4%EB%9E%80-692909a0d363 https://blog.naver.com/allstar927/90161809512 https://ssup2.github.io/theory_analysis/Servlet_Servlet_Container/ https://woojong92.tistory.com/entry/Servlet%EA%B5%AC%EC%A1%B0%EC%99%80-HttpServlet-%ED%81%B4%EB%9E%98%EC%8A%A4 https://dinfree.com/lecture/backend/javaweb_2.2.html

• 한 테이블에 저장되어 있는 데이터들이 상하(부모, 자식) 관계를 가질 때, 이 구조를 이용해서 테이블을 조회하는 SQL이다.

• 계층형 쿼리를 이용해서 트리 구조의 형태로 결과를 조회할 수 있다.

• 일반적으로 조직도나 카테고리, 메뉴 등의 정보가 트리구조를 가지고 있다.

형식

SELECT [LEVEL,] 컬럼1, 컬럼2
FROM 테이블명
[WHERE 조건식]
START WITH 조건식
CONNECT BY PRIOR 조건식
-- []안의 SQL은 생략 가능

• LEVEL은 조회되는 각 행에 대한 레벨을 출력하는 의사컬럼이다. 예를 들어, 최상위행은1, 그 하위행은 2, 그 하위의 하위행은 3을 출력한다.

• START WITH절은 계층 검색의 시작점을 지정한다.

• CONNECT BY절은 계층 검색의 방향을 지정한다.

  • 상위에서 하위로 검색 : CONNECT BY PRIOR 부모컬럼 = 자식컬럼
  • 하위에서 상위로 검색 : CONNECT BY PRIOR 자식컬럼 = 부모컬럼

상위행에서 하위행으로 조회

-- 100번 직원 및 부하직원을 조회하기
-- MANAGER_ID가 EMPLOYEE_ID를 참조한다.
SELECT LEVEL , EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

100번 직원부터 검색을 시작하고 LEVEL컬럼의 숫자가 해당 레코드가 어느 계층에 있는지 나타낸다. MANAGER_ID는 EMPLOYEE_ID를 참조하므로 EMPLOYEE_ID가 부모컬럼, MANAGER_ID가 자식컬럼이다. 상위에서 하위로 검색한다.

하위행에서 상위행으로 조회

-- 206번직원의 상사을 조회하기
SELECT LEVEL, EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 206
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
ORDER BY LEVEL DESC;

테이블의 데이터를 특정 컬럼을 기준으로 행들을 그룹화하여 결과를 조회하는 함수이다. 분석함수에는 순위함수(RANK, DENSE_RANK, ROW_NUMBER)와 집계함수(SUM, MIN, MAX, AVG, COUNT)가 있다.

순위함수

SELECT 순위함수() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명 정렬옵션) AS 컬럼1,
       컬럼2,
       컬럼3,
       . . .
FROM 테이블명
-- PARTITION BY절은 생략 가능하다.

ORDER BY절에 기준이되는 컬럼과 정렬옵션을 지정하고 PARTITION BY절에 그룹화할 컬럼을 지정한다. PARTITION BY절은 생략이 가능한데, 이러한 경우 테이블 전체를 대상으로 순위를 조회한다.

-- 급여를 기준으로 직원들을 정렬했을 때 급여 순위가 1~3위에 해당하는 직원 3명 조회
SELECT RANK, FIRST_NAME, SALARY
FROM (SELECT RANK() OVER (ORDER BY SALARY DESC) AS RANK, FIRST_NAME, SALARY
      FROM EMPLOYEES)
WHERE RANK >=1 AND RANK <=3;

RANK()함수는 동점자를 같은 순위로 간주한다. 그래서 SALARY가 17000인 직원 두명을 2위로 출력했다.

-- 직원들의 부서별 급여 순위 조회
SELECT DENSE_RANK() OVER (PARTITION BY E.DEPARTMENT_ID ORDER BY E.SALARY DESC) AS DENSE_RANK,
       E.FIRST_NAME,
       D.DEPARTMENT_NAME,
       E.SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

먼저 DEPARTMENT_ID별로 행을 그룹화한 후, SALARY에 따라 내림차순으로 순위를 부여했다. RANK()와 마찬가지로 동점자는 같은 순위로 간주한다.

-- 직원들의 직군별 급여 순위 조회
SELECT ROW_NUMBER() OVER (PARTITION BY E.JOB_ID ORDER BY E.SALARY DESC) AS ROWNUMBER,
       E.FIRST_NAME, 
       J.JOB_TITLE, 
       E.SALARY
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID;

JOB_ID별로 행을 그룹화한 후, SALARY에 따라 내림차순으로 순위를 부여했다. 앞의 RANK(), DENSE_RANK()와는 다르게 ROW_NUMBER()는 같은 값이어도 다른 순위를 부여한다.

RANK, DENSE_RANK, ROW_NUMBER의 차이

-- 직원들을 급여가 높은 순으로 순위를 부여해서 조회
-- RANK(), DENSE_RANK(), ROW_NUMBER의 차이
SELECT RANK() OVER (ORDER BY SALARY DESC)            AS SALARY_RAKING, 
       DENSE_RANK() OVER (ORDER BY SALARY DESC)     AS SALARY_DENSE_RANKING,
       ROW_NUMBER() OVER (ORDER BY SALARY DESC)     AS SALARY_ROW_NUMBER,
       FIRST_NAME, 
       SALARY
FROM EMPLOYEES;

그림에서 볼 수 있듯이 ROW_NUMBER()는 데이터가 같은 값이어도 다른 순위를 매긴다. 그러나 RANK()와 DENSE_RANK()는 같은 값이면 같은 순위를 매긴다. 그리고 RANK()는 차순위를 동순위의 개수만큼 더한 값으로 매기지만, DENSE_RANK()는 동순위 다음 바로 다음 값을 차순위로 매긴다.

집계함수

SELECT 집계함수(컬럼명) OVER (PARTITION BY 컬럼명) AS 컬럼1,
       컬럼2,
       컬럼3,
       . . .
FROM 테이블명
-- PARTITION BY절은 생략 가능하다.
-- PARTITION BY절이 다중행 함수의 GROUP BY절의 역할을 대신한다. 

PARTITION BY절에 그룹화할 컬럼을 지정하고 집계함수의 괄호 안에 분석하고자 하는 컬럼을 지정한다. 다중행 함수에서 GROUP BY절로 그룹화했다면 분석함수에서는 PARTITION BY절이 그 역할을 대신한다.

-- 직원 이름, 급여, 부서 평균급여, 직원급여와 부서평균급여 차이 조회
-- 분석함수 활용
SELECT  FIRST_NAME, 
        SALARY, 
        TRUNC(AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID)) AS AVG_SALARY, 
        SALARY - TRUNC(AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID)) AS SALARY_GAP
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID;

DEPARTMENT_ID로 행을 그룹화한 후, SALARY의 평균을 계산했다. 이 결과를 이용해 부서 평균급여, 직원급여와 부서 평균급여의 차이를 출력한다.

-- 다중행함수(그룹함수) 활용
SELECT B.FIRST_NAME, 
       B.SALARY, 
       A.AVG_SALARY, 
       B.SALARY - A.AVG_SALARY AS SALARY_GAP
FROM (SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY)) AS AVG_SALARY
      FROM EMPLOYEES
      GROUP BY DEPARTMENT_ID) A, 
      EMPLOYEES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY B.EMPLOYEE_ID;

위 집계함수의 예제를 다중행함수를 이용해서 조회하는 쿼리다. 다중행 함수를 이용하면 GROUP BY절에 지정된 컬럼만 SELECT절에서 조회할 수 있으므로 DEPARTMENT_ID를 제외한 다른 컬럼을 조회하려면 서브쿼리로 작성해야 한다. 집계함수는 GROUP BY절을 사용하지 않기 때문에 쿼리를 보다 간결하게 작성할 수 있다.

조건에 맞는 최상위 레코드 n개 혹은 최하위 레코드 n개를 조회하는 쿼리다. ROWNUM이라는 컬럼을 사용한다. ROWNUM은 조회된 행에 행번호를 1번부터 순서대로 부여하는 '의사컬럼'이다.

SELECT ROWNUM, COLUMN1, COLUMN2, COLUMN3 . . .
FROM (SELECT COLUMN1, COLUMN2, COLUMN3 . . .
      FROM 테이블
      ORDER BY 분석대상컬럼명 정렬방향)
WHERE ROWNUM <= N;

-- 직원들을 급여순으로 순위를 매겼을 때 1, 2, 3위 직원의 이름과 급여 조회
SELECT ROWNUM, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 3;

FROM절의 서브쿼리에서 직원이름, 급여를 조회하고 급여의 내림차순으로 정렬한다. 그 다음 메인쿼리에서 서브쿼리에서 조회된 테이블에 ROWNUM컬럼을 통해 순서대로 행번호를 부여한다. ROWNUM <= 3이라는 조건을 붙이면 상위 세 명의 이름과 급여를 조회할 수 있다.

테이블의 특정 컬럼에 저장될 수 있는 데이터의 조건이다. 제약조건을 지정하면 조건에 부합하지 않는 데이터는 저장할 수 없다. 오라클에서 사용하는 제약조건은 다음과 같다.

• NOT NUUL

  • 지정한 컬럼에 NULL을 허용하지 않는다.

CREATE TABLE 테이블명 (
    컬럼명 자료형 NOT NULL);

• UNIQUE

  • 지정한 컬럼의 값은 유일한 값이어야 한다. 즉, 중복을 허용하지 않는다.
  • 그러나 NULL은 중복이 가능하다.

    CREATE TABLE 테이블명 (
    컬럼명 자료형 UNIQUE);

• PRIMARY KEY

  • 테이블의 각 행을 대표하는 컬럼에 정의되는 제약조건이다.
  • 지정한 컬럼의 값이 유일한 값이면서 NULL을 허용하지 않는다.
  • 테이블에 한 컬럼만 지정 가능하다.
  • 하나 이상의 컬럼을 사용해서 정의할 수 있다. (복합키)

    CREATE TABLE 테이블명 (
    컬럼명 자료형 PRIMARY KEY);

• FOREIGN KEY

  • 해당 컬럼의 값이 다른 테이블(혹은 같은 테이블)의 특정 컬럼이 가지고 있는 값과 관련있는 값만 가져야 한다.
  • 다른 컬럼이 참조하는 컬럼은 반드시 기본키 제약조건이 정의된 컬럼이어야 한다.
  • 예를 들어, 주문 테이블의 주문자 아이디는 회원 테이블의 회원 아이디 컬럼의 값과 일치하는 값만 허용된다. ```sql
• • 컬럼레벨 제약조건 정의, []는 생략 가능 CREATE TABLE 테이블명 ( 컬럼명 자료형 [CONSTRAINT 제약조건] REFERENCES 참조테이블 (참조컬럼));

-- 테이블레벨 제약조건 정의, []는 생략 가능 CREATE TABLE 테이블명 ( [CONSTRAINT 제약조건] FOREIGN KEY (컬럼명) REFERENCES 참조테이블 (참조컬럼));

* CHECK

  * 제시된 조건을 만족하는 값만 가질 수 있게 한다.
  * 예를 들어, 성별 컬럼은 '남', '여' 둘 중 한 값만 가질 수 있다.
```sql  
CREATE TABLE 테이블명 (
    컬럼명 자료형 CHECK (조건식));

제약조건 정의 방법

-- 컬럼레벨 제약조건 정의, []는 생략 가능
CREATE TABLE 테이블명 (
    컬럼명 자료형 [CONSTRAINT 제약조건별칭] 제약조건,
    컬럼명 자료형 [CONSTRAINT 제약조건별칭] 제약조건,
                                             . . .);

-- 테이블레벨 제약조건 정의, []는 생략 가능
CREATE TABLE 테이블명 (
    컬럼명 자료형,
    컬럼명 자료형,
    . . . 
    [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명1),
    [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명2, 컬럼명3),
                                              . . .);

• 하나의 컬럼에 여러 제약조건을 지정할 경우 첫 번째 제약조건은 컬럼레벨에서 정의하고 나머지는 테이블레벨에서 정의한다.

• 두 개 이상의 컬럼에 같은 제약조건을 정의해야할 경우 테이블레벨로 정의한다.

• 일반적으로 하나의 컬럼에 NOT NULL 제약조건과 다른 제약조건을 같이 정의할 때, NOT NULL 제약조건은 컬럼레벨로 정의하고 나머지 컬럼의 제약조건은 테이블레벨로 정의한다.

주의사항

제약조건은 테이블에 데이터를 저장할 때 뿐만 아니라 변경, 삭제할 때에도 제약조건을 검사한다.

예를 들어, SAMPLE_CART_ITEMS 테이블의 PRODUCT_NO는 SAMPLE_PRODCUTS의 PRODUCT_NO 컬럼의 값을 참조한다. 그러면 SAMPLE_CART_ITEMS FOREIGN KEY는 PRODUCT_NO이고, SAMPLE_PRODCUTS의 PRIMARY KEY는 PRDUCT_NO이다.

이때, SAMPLE_PRODUCTS 테이블의 PRODUCT_NO는 함부로 UPDATE나 DELETE작업을 수행할 수 없다. SAPLE_CART_ITEMS 테이블에서 이미 PRODUCT_NO를 참조하고 있기 때문이다. 만약 SAPLE_CART_ITEMS 테이블에서 참조하고 있는 PRODUCT_NO를 변경, 삭제하려고 한다면 다음과 같은 오류가 발생할 것이다.

일련번호를 자동으로 생성하는 데이터베이스 객체다. 중복을 허용하지 않는, 행을 구분하기 위한 기본키 값을 생성하는 데 주로 사용된다.

시퀀스 생성과 생성 옵션

-- 생성옵션은 생략 가능하다.
CREATE SEQUNCE 시퀀스명
INCREMENT BY value
START WITH value
MAXVALUE value
MINVALUE value
CACHE value

• INCREMENT BY value : 한번에 value값 만큼 증가시킨다. 기본값은 1이다.

• START WITH value : 시작값을 value로 지정한다. 기본값은 1이다. START WITH값은 시퀀스 생성 이후 절대로 수정할 수 없다. 만약 수정될 경우 일련번호가 중복될 수도 있기 때문이다.

• MAXVALUE value : 최대값을 value로 지정한다. 기본값은 NOMAXVALUE다.

• MINVALUE value : 최대값을 value로 지정한다. 기본값은 NOMINVALUE다.

• CACHE value : 지정된 value개 만큼 일련번호를 미리 생성해서 메모리에 캐시한다.기본값은 CACHE 20이다. NOCACHE로 설정하면 일련번호를 미리 생성해두지 않는다.

시퀀스 사용

-- 새 일련번호 발행
시퀀스명.NEXTVAL;

-- 현재 일련번호 조회
시퀀스명.CURRVAL;

CURRVAL는 시퀀스에서 마지막으로 생성한 번호를 반환한다. 그래서 CURRVAL를 시퀀스 생성 직후 사용하면 번호가 발행된 적이 없으므로 오류가 발생한다.

시퀀수 수정 및 삭제

-- 시퀀스 수정
ALTER SEQUENCE 시퀀스명;
INCREMENT BY value
START WITH value
MAXVALUE value
MINVALUE value
CACHE value

-- 시퀀스 삭제
DROP SEQUENCE 시퀀스명;

앞서 언급했다시피 START WITH 값은 수정할 수 없다.

하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체다. SELECT문을 저장한 것이므로 가상의 테이블이라 볼 수 있고, 물리적인 데이터가 저장된 것은 아니다. 그래서 INSERT, UPDATE, DELETE 작업의 수행이 사실상 불가능하다.

FROM절에서 사용하는 서브쿼리인 '인라인 뷰'와 비슷하지만 인라인 뷰는 SQL문 실행 이후 사라지지만, 뷰 객체는 영구적으로 존재한다.

뷰의 사용목적

앞서 공부했듯이 하나의 SQL문 내에 여러 개의 SELECT문이 존재할 수 있다. 그러한 SELECT문을 뷰로 저장해두면 전체 SQL문의 복잡도를 줄일 수 있다.

하나의 데이터베이스에는 여러 사용자가 접근할 수 있다. 접근하는 사용자에 따라 특정 데이터를 숨기기 위해 사용한다. 예를 들어, 어떤 작업을 수행하기 위해 직원테이블을 다루어야 하는데 직원의 주민등록번호나 연봉은 민감한 정보다. 이때 작업자가 이러한 민감한 정보를 조회하는 것을 막기 위해 작업에 필요한 컬럼만 SELECT한 뷰를 제공한다.

뷰의 생성 및 삭제

-- 뷰 생성
CREATE OR REPLACE VIEW 뷰이름
AS SELECT문;

-- 뷰 삭제
DROP VIEW 뷰이름;

데이터 검색 성능의 향상을 위해 테이블의 컬럼에 사용하는 데이터베이스 객체이다. 테이블에 저장된 특정 행의 주소를 책의 목록처럼 만든 것이다. 인덱스를 사용해서 원하는 데이터를 검색하는 방법이 Index Scan이고, 인덱스를 사용하지 않고 데이터의 처음부터 끝까지 검색하는 방법이 Table Full Scan이다.

B-tree

B-tree는 아래 그림의 첫 번째와 같이 데이터가 트리형태로 저장되어 있다. 숫자가 있는 원을 '노드'라고 하고, 최상단에 있는 노드를 루트 노드(Root Node)라고 한다. 루트 노드를 기준으로 좌우가 균형을 이루고 있기 때문에 Balanced Tree를 줄여서 B-tree라고 한다. (사람들이 그렇게 추측한다.)

검색하고자 하는 값이 현재 검색한 값보다 작으면 왼쪽으로, 크면 오른쪽으로 검색한다. 이러한 B-트리가 RDBMS에서 주로 사용하는 인덱스의 구조다. 그림에서 볼 수 있듯이 19를 검색하려고 할 때, 일반적인 오름차순 배열은 4번을 검색해야하지만, B-tree구조라면 2번만에 검색할 수 있다.

인덱스 생성 및 삭제

기본키 혹은 고유키로 지정된 컬럼은 자동으로 인덱스가 생성된다. 그 외의 컬럼에 인덱스를 생성하기 위해서는 CREATE문을 사용해야 한다. 사용방법은 다음과 같다.

-- 인덱스 생성
CREATE INDEX 인덱스명
ON 테이블명 (컬럼명1 정렬옵션
            컬럼명2 정렬옵션
             ...              );

-- 인덱스 삭제
DROP INDEX 인덱스명;

인덱스 사용이 무조건 좋을까?

그렇지는 않다. B-tree로 구성된 인덱스를 '탐색'하려면 우선 B-tree구조로 '정렬'이 되어야 한다. 그래서 인덱스를 사용하면 데이터 조회는 보다 빠르게 완료할 수 있지만, 데이터 생성 및 수정은 데이터를 정렬해서 저장해야 하기 때문에 오히려 느려질 수 있다. 즉, 인덱스 사용은 생성/수정 작업의 성능을 희생하고 조회작업의 성능을 개선하는 것이다.

또한, 인덱스를 통해 데이터를 탐색할 때 'key'라는 값을 통해 원하는 값을 찾아나간다. 이 값을 저장하기 위한 공간도 따로 할당해야 하기 때문에 무분별한 인덱스 사용은 전체 쿼리 성능을 비효율적으로 만들 수 있다.

데이터 정의어라 부르는 DDL(Data Definition Language)은 데이터베이스 내의 데이터를 저장하고 관리하기 위해 필요한 여러 '객체'들을 생성, 수정, 삭제하기 위해 사용하는 명령어다. 그래서 DDL에는 CREATE문, ALTER문, RENAME문, TRUNCATE문, DROP문이 있다.

앞서 다뤘던 DML은 하나의 트랜잭션으로 묶고 COMMIT과 ROLLBACK을 통해 해당 SQL문의 작업내용을 제어했다. 즉, SQL문이 실행되고 트랜잭션 명령어를 또 실행해야 DB에 작업내용이 반영된다.

그러나 DDL은 그렇지 않다. DDL은 명령어를 실행하는 즉시 작업내용이 DB에 반영된다. 따라서 자동으로 COMMIT되고 ROLLBACK은 불가능하기 때문에 DDL을 사용할 때는 주의가 필요하다.

DDL은 테이블뿐만 아니라 다른 객체(시퀀스, 뷰 등)에서도 사용 가능하지만 지금은 테이블을 중점적으로 다루며 각 명령어들을 다루도록 하겠다.

CAREATE문

-- 새 테이블 생성
CREATE TABLE 테이블명 (
    컬럼1 자료형 제약조건
    컬럼2 자료형 제약조건
    컬럼3 자료형 제약조건
            .
            .
            .
    );

자료형은 해당 컬럼에 저장될 데이터의 타입을 정의한다. 자주 쓰이는 데이터타입은 다음과 같다. 고정길이는 할당된 데이터공간이 10byte이고 저장한 데이터가 3byte라면 나머지 7byte는 공백으로 채워지는 형식이다. 가변길이는 데이터공간이 저장되는 데이터만큼 할당되는 형식이다.

제약조건은 각 컬럼에 특정 데이터만 저장될 수 있도록 또는 데이터의 중복을 방지하도록 조건을 지정하는 것이다. 이와 관련한 여러 제약조건이 있는데 추후에 다루도록 한다.

ALTER문

-- ADD절로 컬럼 추가
ALTER TABLE 테이블명
ADD 컬럼 자료형;

-- RENAME절로 컬럼이름 변경
ALTER TABLE 테이블명
RENAME 기존 컬럼명 TO 새 컬럼명;

-- MODIFY절로 열 길이(데이터 저장공간) 변경
ALTER TABLE 테이블명
MODIFY 컬럼 자료형;

-- DROP절로 특정 열 삭제
ALTER TABLE 테이블명
DROP COLUMN 컬럼;

RENAME문

-- 테이블 이름 변경
RENAME 기존 테이블명 TO 새 테이블명;

TRUNCATE문

-- 테이블 내의 모든 데이터를 삭제한다.
TRUNCATE TABLE 테이블명;

DROP문

-- 테이블을 삭제한다.
DROP TABLE 테이블명;

논리적인 작업 단위를 구성하는 데이터 조작어(DML)의 모음이다. 즉, 어떤 기능을 수행하기 위한 최소 작업 단위이다.

트랜잭션의 사용 목적

예를 들어 계좌이체 기능을 생각해보자. A계좌의 10만원을 B계좌로 송금하려고 한다. 그렇다면 A계좌와 B계좌의 잔액을 변경하기 위한 UPDATE문이 각각 한번씩 실행되어야 한다. 그런데 데이터 전송 중 오류가 발생하여 첫 번째 A계좌에 대한 UPDATE문이 실행되고 작업이 종료되었다.

결과적으로 A계좌에서는 돈이 빠져나갔지만 B계좌에는 송금이 이루어지지 않은 상태이다. 따라서 이러한 사고를 막기 위해서는 A, B 계좌의 UPDATE문이 모두 수행되던가 혹은 모두 수행되지 않던가 해야 한다.

이를 위해 '트랜잭션'을 사용한다. 특정 작업을 위한 SQL문을 한 세트로 묶어 그것들이 모두 수행되거나 모두 수행되지 않도록 하는 것이다.

트랜잭션 처리 명령어

• COMMIT : 트랜잭션 내의 모든 DML 실행 결과를 데이터베이스에 영구적으로 반영시킨다.

• ROLLBACK : 트랜잭션 내의 모든 DML 실행 결과를 데이터베이스 반영을 전부 취소한다.

트랜잭션의 시작과 종료

• 트랜잭션의 시작

  • 첫번째 DML 명령이 실행될때 새 트랜잭션 자동으로 시작된다.

• 트랜잭션의 종료

  • COMMIT 또는 ROLLBACK 명령이 실행될 때 기존 트랜잭션이 종료된다.
  • 트랜잭션이 종료되면 새로운 트랜잭션이 자동으로 시작된다.
  • SQL 명령어 편집툴을 종료할때 기존 트랜잭션이 종료된다.
  • 시스템에 장애가 발생할 때 기존 트랜잭션이 종료된다. ```sql
• • 트랜잭션 자동으로 시작, hong사용자가 22번, 23번 상품을 장바구니에 저장함 INSERT INTO SAMPLE_CART_ITEMS (USER_ID, PRODUCT_NO, ITEM_AMOUNT) VALUES ('hong', 22, 3); INSERT INTO SAMPLE_CART_ITEMS (USER_ID, PRODUCT_NO, ITEM_AMOUNT) VALUES ('hong', 23, 1);
• • 22번, 23번 상품의 재고수량을 변경 UPDATE SAMPLE_PRODUCTS SET PRODUCT_STOCK = PRODUCT_STOCK - 3 WHERE PRODUCT_NO = 22; UPDATE SAMPLE_PRODUCTS SET PRODUCT_STOCK = PRODUCT_STOCK - 1 WHERE PRODUCT_NO = 23;
• • 트랜잭션 내 모든 DML 작업의 실행결과를 영구적으로 DB에 반영,
• • 트랜잭션 종료와 동시에 트랜잭션 시작 COMMIT;

-- 트랜잭션 내 모든 DML 작업의 실행결과의 DB반영 취소 -- 위에서 실행된 DML작업의 반영은 취소할 수 없다. 다른 트랜잭션의 작업이기 때문이다. -- 현재 트랜잭션 내에서는 DML작업을 전혀 수행하지 않았기 때문에 취소할 작업이 없다. -- 트랜잭션 종료와 동시에 트랜잭션 시작 ROLLBACK;

-- 사용자 등록 INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL) VALUES ('ahn', 'zxcv2134', '안중근', 'ahn@gmailcom'); -- ahn사용자가 21번 상품을 장바구니에 저장 INSERT INTO SAMPLE_CART_ITEMS (USER_ID, PRODUCT_NO, ITEM_AMOUNT) VALUES ('ahn', 21, 4); -- 21번 상품의 재고수량 변경 UPDATE SAMPLE_PRODUCTS SET PRODUCT_STOCK = PRODUCT_STOCK - 4 WHERE PRODUCT_NO = 21; -- 이 트랜잭션 내에서 실행된 DML작업의 실행결과의 DB반영을 취소 ROLLBACK;

```

세션과 읽기 일관성

데이터베이스에서 세션이란 데이터베이스에 접속하여 작업을 수행한 후, 종료하기까지의 기간이다. 그리고 세션이 여러 개라는 말은 특정 데이터베이스에 접속해서 작업하고있는 연결에 여러 개라는 의미이다. 예를 들어, 특정 계정으로 명령 프롬프트창과 sql developer에 각각 접속해 작업한다면 세션은 두 개가 된다.

앞서 다룬 트랜잭션은 특정 작업을 위한 SQL문의 집합이라고 했다. 그리고 COMMIT과 ROLLBACK을 통해 트랜잭션의 시작과 종료를 제어한다. 그렇다면 세션은 데이터베이스의 접속시점부터 종료까지의 기간이므로 하나의 세션은 여러 트랜잭션을 포함한다. 다시 말해 세션이 트랜잭션보다 큰 범위의 개념이다.

그러면 특정 세션에서 데이터를 변경중 일 때 다른 세션에서는 해당 데이터가 어떻게 보일까? 데이터를 다루고 있는 세션에서 COMMIT 혹은 ROLLBACK이 되기 전까지는 본래의 데이터가 출력된다. 이것이 읽기 일관성이다. 예를 들어, 명령 프롬프트에서 특정 테이블의 행을 삭제했다면 이 작업이 COMMIT되기 전까지는 sql developer에서는 삭제되기 전의 데이터가 출력된다.

• SELECT문의 내부에 정의된 또 다른 SELECT문을 서브쿼리라고 한다.

• 서브쿼리의 결과를 활용해서 기능을 수행하는, 즉, 서브쿼리를 포함하는 SELECT문을 메인쿼리라고 한다.

• 서브쿼리를 포함시킬 수 있는 절은 다음과 같다.

  • WHERE절
  • HAVING절
  • FROM절

서브쿼리의 특징 및 주의점

• 서브쿼리는 메인쿼리보다 먼저 실행된다.

• 서브쿼리는 반드시 괄호로 묶고 조건식의 오른쪽에 위치시킨다.

• 서브쿼리의 실행결과가 단일행인지, 다중행인지에 따라 적절한 연산자를 사용해야 한다.

단일행 서브쿼리

• SELECT문의 실행 결과로 단 하나의 행을 출력하는 서브쿼리를 의미한다.

• 단일행 연산자

  • '같지 않음'을 제외한 나머지 연산자는 일반적인 비교연산자와 동일하다.
  • 단일행 서브쿼리의 '같지 않음'을 의미하는 연산자는 <>, ^=, !=이다.

-- 부서아이디가 60인 직원 중에서 전체직원의 평균급여보다 급여를 많이받는 직원의 아이디, 이름, 급여, 부서이름을 조회
SELECT E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID = 60
AND E.SALARY < (SELECT AVG(SALARY)
                FROM EMPLOYEES);

다중행 서브쿼리

• SELECT문의 실행 결과로 여러 개의 행을 출력하는 서브쿼리를 의미한다.

• 서브쿼리의 결과가 여러 행이므로 앞서 다룬 단일행 연산자와는 다른 연산자를 사용해야 한다.

• 다중행 연산자는 다음과 같다.

  • IN
  • >ANY, <ANY
  • >ALL, <ALL

IN 연산자

다중행 연산자 중 IN 연산자는 앞서 WHERE절에서 다룬 IN 연산자와 기능이 동일하다.

-- 'Seattle'에 소재지를 두고 있는 부서에 속한 직원의 이름과 소속 부서 조회
SELECT D.DEPARTMENT_NAME, E.FIRST_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND E.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE LOCATION_ID = (SELECT LOCATION_ID
                                             FROM LOCATIONS
                                             WHERE CITY = 'Seattle'));

우선 Seattle의 소재지 아이디를 찾고, 해당 소재지에 위치한 부서의 부서 아이디를 찾아야 한다. 그래서 서브쿼리 2개가 중첩되었다. 각각의 서브쿼리를 실행해보며 전체 SQL문을 이해해본다.

-- Seattle의 소재지 아이디 조회
SELECT LOCATION_ID
FROM LOCATIONS
WHERE CITY = 'Seattle';

두 번째 서브쿼리를 다르게 작성하면 아래와 같다.

-- 소재지가 Seattle(소재지아이디가 1700)인 부서의 부서아이디 조회
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID = 1700;  

위에서 획득한 Seattle의 LOCATION_ID를 통해 Seattle에 위치한 부서의 DEPARTMENT_ID를 획득했다. 총 21개의 DEPARTMENT_ID를 획득했고, 마지막으로 이 부서에 속한 직원들의 이름과 소속 부서 이름을 조회한다. 최종 결과는 다음과 같다.

ANY 연산자

기본적으로 ANY연산자는 출력된 서브쿼리의 여러 행중 적어도 하나가 조건식을 만족하면 메인쿼리의 조건식을 true로 판정한다. 그래서 등가 비교 연산자와 함께 =ANY로 사용하면 IN 연산자와 같은 역할을 한다.

그러나 대소 비교 연사자와 함께 <ANY, >ANY 처럼 사용하는 경우는 조금 다르다. 결론부터 말하자면 A >ANY B는 A가 서브쿼리B 결과의 여러 행중에서 적어도 한 행보다 크면 해당 조건식을 true로 판정한다. 예제를 통해 알아보자.

-- 80번 부서에 소속된 직원의 급여보다 급여를 많이 받는 직원의 아이디, 이름, 급여 조회
-- >ANY는 80번 부서의 어느 급여보다도 크면 true. 즉, 80번 부서의 최저 급여보다 커야 함.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID != 80
AND SALARY >ANY (SELECT SALARY
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = 80);

즉, 이 예제에서 WHERE SALARY >ANY (SELECT ...)는 WHERE SALARY > (SELECT MIN(SALARY) ...) 와 같다.

ALL 연산자

A >ALL B는 A가 서브쿼리B 결과의 모든 행보다 크면 해당 조건식을 true로 판정한다.

-- 80번 부서에 소속된 직원의 급여보다 급여를 많이 받는 직원의 아이디, 이름, 급여 조회
-- >ALL은 80번 부서의 모든 급여보다 커야 함. 즉, 80번 부서의 최대 급여보다 커야 함.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID != 80
AND SALARY >ALL (SELECT SALARY
                FROM EMPLOYEES
                WHERE DEPARTMENT_ID = 80);

ANY의 예제와 비슷한 예제이다. 서브쿼리의 모든 행보다 큰 값만 ture판정한다는 점이 다르다.

즉, 이 예제에서 WHERE SALARY >ALL (SELECT ...)는 WHERE SALARY > (SELECT MAX(SALARY) ...) 와 같다.

HAVING절의 서브쿼리

-- 직원수가 가장 많은 부서의 아이디와 사원수 조회
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                   FROM EMPLOYEES
                   WHERE DEPARTMENT_ID IS NOT NULL
                   GROUP BY DEPARTMENT_ID);

FROM절의 서브쿼리

SELECT문을 통해 테이블의 특정 데이터를 추출한 후 FROM절에서 서브쿼리로 사용하는 방법이다. 즉, 서브쿼리로 조회한 테이블을 기존 테이블과 조인하는 것이다. '인라인 뷰'라고도 한다.

SELECT E.FIRST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME, J.JOB_TITLE
FROM (SELECT FIRST_NAME, DEPARTMENT_ID, JOB_ID
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID = 60) E,
                    DEPARTMENTS D,
                           JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID;

FROM절에 너무 많은 서브쿼리를 작성하면 가독성이나 성능이 저하될 수 있기 때문에 아래와 같이 WITH절을 사용하기도 한다.

다중열 서브쿼리

서브쿼리의 SELECT절에 비교할 컬럼을 여러 개 지정하는 방법이다. 메인쿼리에서 비교할 컬럼을 괄호로 묶고, 서브쿼리의 SELECT절에서는 괄호로 묶은 컬럼과 같은 자료형을 갖는 컬럼을 지정한다.

-- 부서별 최저급여 조회했을 때, 각 부서에서 최저급여를 받는 직원의 이름, 급여, 부서아이디 조회
SELECT DEPARTMENT_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, SALARY) IN (SELECT DEPARTMENT_ID, MIN(SALARY)
                                     FROM EMPLOYEES
                                  WHERE DEPARTMENT_ID IS NOT NULL 
                                  GROUP BY DEPARTMENT_ID);

상호연관 서브쿼리

• 서브쿼리가 메인쿼리에서 사용되는 컬럼을 참조하는 방법이다.

• 서브쿼리에서 메인쿼리의 컬럼/표현식을 사용한다.

• 일반적인 서브쿼리는 메인쿼리보다 먼저 실행되고, 단 한번만 실행되지만 상호연관 서브쿼리는 그렇지 않다.

• 상호연관 서브쿼리는 메인쿼리의 모든 행에 대해서 매번 실행된다. 따라서 쿼리의 성능이 떨어지는 원인이 될 수 있다.

SELECT A.DEPARTMENT_ID, A.EMPLOYEE_ID, A.FIRST_NAME, A.SALARY
FROM EMPLOYEES A
WHERE A.SALARY = (SELECT MIN(B.SALARY)
                  FROM EMPLOYEES B
                  WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
                  GROUP BY B.DEPARTMENT_ID)
ORDER BY A.DEPARTMENT_ID ASC;

메인쿼리에서 EMPLOYEES테이블이 사용되고 서브쿼리에서도 EMPLOYEES테이블의 컬럼이 사용된다. 서브쿼리에서 EMPLOYEES테이블을 DEPARTMENT_ID로 그룹화하고 각 부서의 최저급여를 찾는다. 그리고 메인쿼리에서 그 최저급여와 같은 값을 갖고있는 행을 찾아 출력한다. 이 과정이 모든 행에대해 매번 실행된다. 이러한 이유로 쿼리 성능을 저하시키는 원인이 될 수있다.

스칼라 서브쿼리

• SELECT절에 적는 서브쿼리이다.

• 스칼라 서브쿼리의 결과는 반드시 1행 1열이어야 한다.

• 메인쿼리의 결과만큼 반복수행된다.

SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, (SELECT COUNT(*)
                                            FROM EMPLOYEES B
                                            WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID) AS CNT
FROM DEPARTMENTS A;

• 두 개 이상의 테이블을 가로로 연결해서 데이터를 조회하는 것.

• 여러 테이블에 흩어져 있는 정보중에서 사용자가 원하는 정보만 가져와서 가상의 테이블처럼 활용한다.

예를 들어, EMP테이블에 있는 사원들이 속한 부서이름을 알고 싶다. 그러나 EMP테이블에는 사원들이 속한 부서의 부서번호(DEPTNO)만 있을 뿐, 부서이름은 존재하지 않는다. 부서이름은 DEPT테이블에 존재한다. 그래서 EMP테이블과 DEPT테이블을 조인하여 원하는 데이터를 조회한다.

조인의 방법

• 오라클 조인

  SELECT A.COLUMN1, B.COLUMN2
  FROM TABLE1 A ,TABLE2 B
  WHERE A.COLUMN1 = B.COLUMN2;

  A와 B는 각각 TABLE1과 TABLE2의 별칭이다. 조인 조건과 조회할 데이터에 사용되는 컬럼을 별칭과 함께 지정한다. TABLE1의 COLUMN1과 TABLE2의 COLUMN2가 일치하는 행을 출력한다.

• ANSI 조인

  SELECT A.COLUMN1, B.COLUMN2
  FROM TABLE1 A JOIN TABLE2 B
  ON A.COLUMN1 = B.COLUMN2;

  ANSI 조인은 모든 RDBMS에서 사용하는 표준 조인 방법이다. 오라클 조인과 다른 점은 FROM절의 두 테이블을 콤마(,) 대신 JOIN으로 연결하고, 조인 조건을 WHERE절 대신 ON으로 표현한다.

조인이 수행될 때는 두 개 이상의 테이블이 사용된다. 둘 중 먼저 읽는 테이블을 선행 테이블, 뒤에 오는 테이블을 후행 테이블이라고 한다. 선행 테이블은 조회할 데이터가 적은 테이블로 선택하는 것이 효율적이다.

크로스 조인 (Cartesian product)

• 조인 조건을 지정하지 않고, 두 개 이상의 테이블을 조인하는 것.

• 조인 조건을 지정하지 않으므로 조인된 테이블의 모든 데이터를 전부 가져온다.

  • 예를 들어, A테이블과 B테이블의 행의 개수가 각각 10개, 4개라면 A테이블의 한 행당 B테이블의 모든행이 연결되어 총 40개의 행이 조회된다. 따라서 조인 작업에 참조되는 테이블의 행의 개수를 모두 곱한 값이 조회결과가 된다.

SELECT *
FROM DEPARTMENTS, LOCATIONS;

등가 조인 (Equi Join)

• 테이블을 연결한 후, 출력할 행을 각 테이블의 특정 열과 일치하는 데이터를 기준으로 선정한다.

• 일반적으로 많이 사용되는 조인 방식이다.

• 내부 조인 혹은 단순 조인이라고도 부른다.

SELECT E.FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.SALARY >= 10000;

DEPARTEMENTS 테이블의 DEPARTMENT_NAME은 EMPLOYEES 테이블에 존재하지 않지만 조회가 가능한 이유는 DEPARTMENTS 테이블을 조인했기 때문이다.

SELECT E.FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.SALARY >= 10000;

위 예제에 LOCATIONS 테이블을 추가로 조인했다. DEPARTMENTS, LOCATIONS 테이블 둘 다 LOCATION_ID 컬럼이 존재한다. 이 컬럼의 데이터가 같은 행을 출력했다. D.LOCATION_ID = L.LOCATION_ID 조건이 존재하지 않는다면 EMPLOYEES, DEPARTMENTS 테이블과 LOCATIONS 테이블이 크로스 조인된다.

비등가 조인 (Non-Equi Join)

• 조인 조건을 지정할 때, 조인 대상 테이블의 특정 컬럼이 크거나 작은 경우의 조건으로 데이터를 조회한다.

SELECT E.FIRST_NAME, E.SALARY, G.SALARY_GRADE
FROM EMPLOYEES E, SALARY_GRADES G
WHERE G.MIN_SALARY <=  E.SALARY AND E.SALARY <= G.MAX_SALARY;

SALARY_GRADES 테이블은 MIN_SALARY와 MAX_SALARY에 따라 SALARY_GRADE가 나뉘어 진다. SALARY_GRADES 테이블과 EMPLOYEES 테이블을 조인한 후, EMPLOYEES 테이블의 SALARY가 SALARY_GRADE의 어느 등급에 속하는지 출력한다.

외부 조인 (Outer join)

• 외부조인은 한쪽 테이블에는 데이터가 있고 다른쪽 테이블에는 데이터가 존재하지 않을 때, 데이터가 있는 쪽 테이블의 내용을 누락없이 출력하는 조인방법이다.

SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

EMPLOYEE_ID가 178인 행은 조인 결과에 포함되지 않았는데, 이는 EMPLOYEES테이블에서 EMPLOYEE_ID가 178인 행의 DEPARTMENT_ID가 NULL이기 때문이다.

이처럼 조인 기준 컬럼의 데이터가 없는 행도 강제로 출력하기 위해 외부 조인을 사용한다.

SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID (+);

NULL이 존재하지 않는 쪽(D.DEPARTMENT_ID)에 (+)를 붙여 외부 조인을 사용했다. EMPLOYEES 테이블에서 DEPARTMENT_ID가 NULL인행도 출력이된다.

자체 조인 (Self Join)

• 하나의 테이블을 이용해서 조인을 구성한다.

• 하나의 테이블에 안에 상위데이터, 하위데이터가 있는 경우 상위데이터와 하위데이터를 서로 연관지어서 조회할 때 자체 조인이 필요하다.

• 하나의 테이블을 역할을 각각 나누어서 조인해야 한다.

-- 모든 직원아이디, 직원이름과 해당 직원의 관리자아이디, 관리자이름 조회한다.
SELECT EMP.EMPLOYEE_ID AS EMP_ID, EMP.FIRST_NAME AS EMP_NAME, 
       MGR.EMPLOYEE_ID AS MGR_ID, MGR.FIRST_NAME AS MGR_NAME
FROM EMPLOYEES EMP, EMPLOYEES MGR
WHERE EMP.MANAGER_ID = MGR.EMPLOYEE_ID (+)
ORDER BY EMP.EMPLOYEE_ID ASC;

EMPLOYEES 테이블을 직원정보를 담는 테이블과 매니저정보를 담는 테이블로 구분해서 자체 조인했다. EMP의 MANAGER_ID와 MGR의 EMPLOYEE_ID가 일치하는 조건으로 조인하면 각 직원의 매니저 아이디와 그 매니저의 직원 아이디가 연결된다.

그런데 EMPLOYEE_ID가 100인 직원은 매니저가 존재하지 않으므로(MANAGER_ID의 값이 NULL) 조회 결과에 포함되지 않을 것이다. 이를 해결하기 위해 MGR.EMPLOYEE_ID에 (+)를 추가하여 외부 조인했다.

• 여러 행이 입력되고, 한 행씩 반환한다.

• 중첩은 한 번만 허용된다.

• WHERE절에서 사용할 수 없다.

• 그룹 함수가 아니거나 그룹 함수가 포함되어 있지 않은 표현식은 SELECT절에 사용할 수 없다.

  • 그룹 함수의 결과는 1행 혹은 그룹당 1행인데, 그룹 함수와 관련이 없는 컬럼은 그룹 함수의 결과와 행 개수가 일치하지 않기 때문이다.

COUNT 함수

• COUNT (*) : 조회된 행의 개수를 반환

  SELECT COUNT (*)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID =80;

  DEPARTMENT_ID가 80인 직원들의 행의 개수를 반환한다. 즉, 부서아이디가 80인 직원들의 수를 조회한다.

• COUNT (컬럼 혹은 표현식) : 컬럼 혹은 표현식이 NULL이 아닌 행의 개수만 반환

  SELECT COUNT (COMMISSION_PCT)
  FROM EMPLOYEES;

  COMMISSION_PCT가 NULL이 아닌 행의 개수를 반환한다. 즉, 커미션을 받는 직원 수를 조회한다

SUM, AVG, MIN, MAX 함수

• MIN (컬럼 혹은 표현식) : 컬럼 혹은 표현식의 최소값를 반환한다.
• MAX (컬럼 혹은 표현식) : 컬럼 혹은 표현식의 최대값를 반환한다.
• SUM (컬럼 혹은 표현식) : 컬럼 혹은 표현식의 합계를 반환한다.
• AVG (컬럼 혹은 표현식) : 컬럼 혹은 표현식의 평균를 반환한다.

위 함수 모두 NULL값은 제외된다.

SELECT MIN(SALARY) AS SALARY_MIN,
       MAX(SALARY) AS SALARY_MAX, 
       SUM(SALARY) AS SALARY_SUM, 
       TRUNC(AVG(SALARY)) AS SALARY_AVG
FROM EMPLOYEES;

GROUP BY절

그 동안 다룬 그룹 함수는 지정된 테이블의 여러 행을 입력받아 하나의 결과만 반환했다. GROUP BY절을 사용하면 특정 컬럼을 기준으로 그룹화하여 행을 입력받고 그룹당 한 행씩 결과를 반환받을 수 있다. 그리고 GROUP BY절에 컬럼을 여러 개 입력하면 지정된 순서대로 그룹화 한다.

위에서 언급했다시피 그룹 함수가 아니거나 그룹 함수가 포함되어 있지 않은 표현식은 SELECT절에 사용할 수 없다. 그러나 GROUP BY절에 등장한 컬럼 혹은 표현식은 SELECT절에 그룹 함수와 함께 적을 수 있다. 그룹 함수 결과의 개수가 그룹화한 행의 개수와 일치하기 때문이다.

또한, ORDER BY절에서는 컬럼의 별칭을 사용할 수 없다. ORDER BY절이 별칭이 지정되는 SELECT절보다 나중에 실행되기 때문이다.

SELECT DEPARTMENT_ID,
           TO_CHAR(HIRE_DATE, 'YYYY') AS HIRE_YEAR,
           COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY');

SELECT DEPARTMENT_ID,
       MIN(SALARY) AS SALARY_MIN,
       MAX(SALARY) AS SALARY_MAX, 
       SUM(SALARY) AS SALARY_SUM, 
       TRUNC(AVG(SALARY)) AS SALARY_AVG
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID;

HAVING절

HAVING절은 GROUP BY절을 통해 그룹화한 결과값의 범위를 필터링하는데 사용한다. 그래서 HAVING절은 GROUP BY절이 존재할 때만 사용할 수 있다. HAVING절과 WHERE절은 조회할 값을 특정 기준으로 필터링한다는 점에서 유사하지만 분명히 차이가 있다. 보다 정확히 이해하기 위해 각 절의 실행순서를 살펴본다.

1. FROM : 조회할 테이블 확인
2. ON : 조인 조건 확인
3. JOIN : 테이블 조인 (병합)

4. WHERE : 행 추출 조건 확인 5. GROUP BY : 특정 컬럼을 기준으로 행을 그룹화 6. HAVING : 그룹화 이후 행 추출 조건 확인

7. SELECT : 조회할 컬럼 지정
8. DISTINCT : 중복 제거
9. ORDER BY : 행 정렬**

중요한 부분은 4,5,6이다. WHERE절에서 먼저 실행되고, 그 후에 GROUP BY절과 HAVING절이 실행된다. 즉, WHERE절에서 지정된 조건으로 먼저 데이터를 필터링한 후, 해당 데이터를 갖고 GROUP BY절에서 지정된 컬럼을 기준으로 그룹화한다. 마지막으로 그룹화된 데이터를 HAVING절에서 지정된 조건으로 필터링한다.

다시 말해, WHERE절은 그룹화할 대상을 필터링한다. 따라서 그룹화할 대상이 감소되고 쿼리 성능이 향상된다.

SELECT TO_CHAR(HIRE_DATE, 'YYYY') AS 입사년도,
       COUNT(*) AS 사원수
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 50, 80)
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
HAVING COUNT(*) >= 10
ORDER BY 입사년도;

ROLLUP

GROUP BY절에 2개 이상의 컬럼 혹은 표현식이 있으면 첫 번째 식을 기준으로 그룹화를 하고, 해당 그룹안에서 두 번째 식을 기준으로 다시 그룹화한다. 이런 경우 ROLLUP은 첫 번째 그룹에 대한 부분합을 계산한다.

SELECT DEPARTMENT_ID, 
           JOB_ID, 
           COUNT(*) AS CNT,
           SUM(SALARY)
FROM EMPLOYEES
GROUP BY ROLLUP (DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID ASC, JOB_ID ASC;

• SQL을 작성할 때 유용한 기능이 제공되는 함수다.
• DBMS 제품마다 차이가 있다.
• '함수'라는 의미 그대로 특정 값을 입력하면 함수의 연산 과정에 따라 다른 값이 반환된다.
• 단일행 함수와 다중행 함수(그룹 함수)로 나뉜다

단일행 함수

• 한 행씩 입력되고, 한 행씩 반환한다.
• 중첩해서 사용이 가능하다.
• 문자함수, 숫자함수, 날짜함수, 변환함수, 기타함수(nvl, case, decode)가 있다.

문자함수

• LENGTH (컬럼명 혹은 표현식) : 문자열의 길이를 반환한다.

  SELECT FIRST_NAME, LENGTH (FIRST_NAME)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  DEPARTMENT_ID가 60인 직원들의 이름의 글자 수를 출력했다.

• LOWER (컬럼명 혹은 표현식) : 문자를 소문자로 변환한다.
• UPPER (컬럼명 혹은 표현식) : 문자를 대문자로 변환한다.

  SELECT FIRST_NAME, LOWER (FIRST_NAME), UPPER (FIRST_NAME)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  DEPARTMENT_ID가 60인 직원들의 이름을 각각 대문자 소문자로 바꾸어 출력한다.

• SUBSTR (컬럼명 혹은 표현식, 시작위치) : 문자열에서 지정된 시작위치부터 끝까지 잘라서 반환한다.
• SUBSTR (컬럼명 혹은 표현식, 시작위치, 길이) : 문자열에서 지정된 시작위치부터 길이만큼 잘라서 반환한다.

  SELECT JOB_ID, SUBSTR (JOB_ID, 4), SUBSTR (JOB_ID, 4, 2)
  FROM EMPLOYEES
  WHERE SALARY >= 10000;

  SALARY가 10000 이상인 직원들의 JOB_ID와 JOB_ID의 네 번째 글자부터 끝까지, 네 번째 글자부터 길이2만큼 잘라서 출력한다.

• INSTR (컬럼명 혹은 표현식, '텍스트') : 지정된 텍스트가 등장하는 위치를 반환한다.
• INSTR (컬럼명 혹은 표현식, '텍스트', 검색 시작 위치) : 지정된 텍스트가 등장하는 위치를 검색 시작 위치부터 찾아서 반환한다.

  SELECT FIRST_NAME, 
       INSTR (FIRST_NAME, 'A'), 
       INSTR (FIRST_NAME, 'e', 2)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  DEPARTMENT_ID가 60인 직원들의 이름에서 'A'가 등장하는 위치와, 두 번째 위치에서 'e'가 등장하는 위치를 출력한다.

• LPAD (컬럼명 혹은 표현식, 길이, '텍스트') : 지정된 길이보다 짧으면 왼쪽에 텍스트를 채워서 지정된 길이로 맞춘다.
• RPAD (컬럼명 혹은 표현식, 길이, '텍스트') : 지정된 길이보다 짧으면 오른쪽에 텍스트를 채워서 지정된 길이로 맞춘다.

  SELECT FIRST_NAME, LPAD (FIRST_NAME, 15, '#'), 
                   RPAD (FIRST_NAME, 15, '#')
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  

• CONCAT (컬럼명 혹은 표현식, 컬럼명 혹은 표현식) : 두 값을 합쳐서 새로운 텍스트를 반환한다.

  SELECT CONCAT (FIRST_NAME, LAST_NAME)
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  DEPARTMENT_ID가 60인 직원들의 FIRST_NAME과 LAST_NAME을 결합해서 출력한다.

• 컬럼명 혹은 텍스트 || 컬럼명 혹은 텍스트 : 텍스트를 연결한다. (JAVA에서 문자열을 잇는 +와 비슷하다.)

  SELECT FIRST_NAME || ' ' || LAST_NAME
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  위의 CONCAT과 비슷하지만 FRIST_NAME과 LAST_NAME 뿐만 아니라'||'를 통해 공백문자도 결합해서 출력했다.

• REPLACE (컬럼명 혹은 문자열, '텍스트', '대체할 텍스트') : 텍스트에 해당하는 문자를 지정된 텍스트로 대체한다.

  SELECT FIRST_NAME, REPLACE (FIRST_NAME, 'a', '*')
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  

숫자 함수

숫자 함수를 설명하기 위해 DUAL테이블을 활용한다. DUAL테이블은 1행1열 테이블로 함수나 산술계산 결과를 단순히 확인하기 위해 사용한다.

• ROUND (컬럼 혹은 표현식) : 소수점 첫 번째 자리에서 반올림한다.
• ROUND (컬럼 혹은 표현식, 자리수) : 지정된 자리수까지 반올림한다. (음수도 가능)

  SELECT ROUND (123.45), -- 소수점 첫 번째 자리에서 반올림한다. 
           ROUND (123.4567, 3), -- 소수점 세 번째자리까지 반올림한다.
           ROUND (123.4567, 0), -- 1의 자리까지 반올림한다. (소수점 첫 번째 자리에서 반올림 한다.)
           ROUND (123.4567, -1), -- 10의 자리까지 반올림한다.
           ROUND (123.4567, -2) -- 100의 자리까지 반올림한다.
  FROM DUAL;

  

• TRUNC (컬럼 혹은 표현식) : 소수점부분을 전부 버린다.
• TRUNC (컬럼 혹은 표현식, 자리수) : 지정된 자리수만 남기고 버린다.

  SELECT TRUNC (1234.567), -- 소수점 이하를 전부 버린다.
           TRUNC (1234.56, 1), -- 소수점 첫 번째자리만 남기고 버린다.
           TRUNC (1234.1, 0), -- 1의 자리만 남기고 버린다.
           TRUNC (1234.1, -2), -- 100의 자리만 남기고 버린다.
           TRUNC (1234.1, -3) -- 1000의 자리만 남기고 버린다.
  FROM DUAL;

  

• MOD (컬럼 혹은 표현식, 숫자) : 숫자만큼 나눈 나머지 값을 반환한다.

  SELECT 5/2, MOD (5, 2)
  FROM DUAL;

  

날짜 함수

• SYSDATE : 시스템의 현재 날짜와 시간정보가 포함된 날짜정보를 반환한다. 입력값이 없는 함수는 ()를 생략한다.

  SELECT SYSDATE
  FROM DUAL;

  

• MONTHS_BETWEEN (날짜, 날짜) : 두 날짜 사이의 개월 수를 반환한다.

  SELECT FIRST_NAME, 
       HIRE_DATE, 
       MONTHS_BETWEEN (SYSDATE, HIRE_DATE),
       TRUNC (MONTHS_BETWEEN (SYSDATE, HIRE_DATE))
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 60;

  

DEPARTMENT_ID가 60인 직원의 이름과 고용일, 고용일과 현재 날짜 사이의 개월 수 차이를 출력했다. MONTHS_BETWEEN함수는 소수점 까지 표현하므로 TRUNC함수를 중첩해서 소수점을 버린 컬럼도 출력했다.

• ADD_MONTHS (날짜, 개월 수) : 지정된 날짜에서 개월 수 만큼 경과한 날짜를 반환한다.

  SELECT SYSDATE,
       ADD_MONTHS (SYSDATE, 1), 
       ADD_MONTHS (SYSDATE, 2),  
       ADD_MONTHS (SYSDATE, -3)
  FROM DUAL;

  

• ROUND (날짜) : 정오를 기준으로 날짜를 반올림한다. 즉, 정오가 넘어가면 날짜가 하루 증가된 값을 반환한다. (시, 분, 초 값은 전부 0이다.)
• TRUNC (날짜) : 날짜에서 시분초값을 전부 버린다.

  SELECT SYSDATE,
       ROUND (SYSDATE),
       TRUNC (SYSDATE)
  FROM DUAL;

  

• 날짜 + 숫자 : 지정된 날짜에서 숫자만큼 경과된 날짜를 반환한다.
• 날짜 - 숫자 : 지정된 날짜에서 숫자만큼 이전의 날짜를 반환한다.

위 함수에서 정수 1을 하루로 간주한다. 예를 들어, SYSDATE - 1이면 현재 시스템 날짜에서 하루 전의 날짜, 시간을 반환한다. 또는 SYSDATE + 6/24이면 현재 시스템 날짜에서 6시간이 지난 날짜, 시간을 반환한다.

SELECT SYSDATE + 3,        
            SYSDATE - 3,    
            SYSDATE + 3/24, 
            SYSDATE - 3/24
FROM DUAL;

형변환 함수

• TO_CHAR (날짜, '포맷문자') : 날짜를 지정된 포맷의 문자열로 변환한다.

날짜의 포맷문자는 다음과 같다.

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       TO_CHAR(SYSDATE, 'MM')
       HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR (HIRE_DATE, 'MM') = TO_CHAR (SYSDATE, 'MM');

시간의 포맷문자는 다음과 같다.

SELECT TO_CHAR (SYSDATE, 'HH:MI:SS AM'),
       TO_CHAR (SYSDATE, 'HH24:MI:SS A.M.')
FROM DUAL;

• TO_CHAR(숫자, '포맷문자') : 숫자를 지정된 포맷의 텍스트로 변환한다.

숫자의 포맷문자는 다음과 같다.

SELECT FIRST_NAME, TO_CHAR(SALARY, '$99,999.99')
FROM EMPLOYEES
WHERE SALARY >= 15000;

• TO_NUMBER ('텍스트', '포맷문자') : 지정된 포맷문자와 일치하는 텍스트를 숫자로 변환한다

  SELECT TO_NUMBER('1,234', '9,999') + TO_NUMBER('1,234', '9,999')
  FROM DUAL;

  

• TO_DATE('텍스트', '포맷문자') : 지정된 포맷문자와 일치하는 텍스트를 날짜로 변환

  SELECT TO_DATE ('2001/01/01', 'YYYY/MM/DD')
  FROM DUAL;

  

기타함수(NVL, DECODE)

• NVL (컬럼 혹은 표현식, 대체할 값) : 지정된 컬럼 혹은 표현식의 값이 NULL이면 대체할 값을 반환한다. 원래 값과 대체할 값의 타입이 같은 타입이어야 한다.

  SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT,
            NVL(COMMISSION_PCT, 0),
            SALARY + SALARY*NVL(COMMISSION_PCT, 0) REAL_SALARY
  FROM EMPLOYEES
  WHERE SALARY >= 10000;

  SALARY가 10000이상인 직원들의 EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT 그리고 SALARY에 COMMISSION_PCT를 반영한 컬럼을 출력했다. `NVL`함수를 사용한 컬럼의 COMMISSION_PCT는 값이 NULL이면 0으로 표현된다.

• NVL2 (컬럼 혹은 표현식, NULL이 아닐 때 대체할 값, NULL일 때 대체할 값) : 지정된 컬럼이 NULL이거나 NULL이 아닐 때 대체할 값을 각각 반환한다. 대체할 값은 데이터타입이 동일한 타입이어야 한다.

  SELECT EMPLOYEE_ID, FIRST_NAME, NVL2 (COMMISSION_PCT, '커미션 받음', '커미션 받지 않음')
  FROM EMPLOYEES
  WHERE SALARY >= 10000;

  SALARY가 10000이상인 직원들의 EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT의 여부를 출력했다.

• DECODE 함수 : 특정 컬럼이나 표현식의 값에 따라 다른 값을 반환한다.

  SELECT 컬럼1, 컬럼2
  DECODE (컬럼 혹은 표현식, 값1, 표현식1,     -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종 결과가 된다.
                        값2, 표현식2,     -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종 결과가 된다.
                        값3, 표현식3,     -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종 결과가 된다.
                        표현식4)          -- 컬럼 혹은 표현식의 값이 값1,2,3, 모두와 일치하지 않으면 표현식4가 최종결과가 된다.
       컬럼3 ...
  FROM 테이블명;

SELECT FIRST_NAME, DEPARTMENT_ID,
            DECODE (DEPARTMENT_ID, 10, 'A팀', 
                                   20, 'A팀', 
                                   30, 'A팀', 
                                   40, 'A팀', 
                                   50, 'B팀', 
                                   60, 'B팀', 
                                   70, 'B팀', 
                                   80, 'C팀', 
                                   'D팀') AS TEAM
FROM EMPLOYEES;

CASE문

CASE문은 DECODE함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 지정할 때 사용한다. 하지만 CASE문은 일반적인 프로그래밍 언어의 if-else if문 처럼 DECODE보다 유연하게 사용할 수 있다.

SELECT 컬럼1, 컬럼2 ...
CASE
          WHEN 조건식 THEN 표현식
          WHEN 조건식 THEN 표현식
          WHEN 조건식 THEN 표현식
          ELSE 표현식
 END
 FROM 테이블명

SELECT FIRST_NAME, SALARY,
            CASE
            WHEN SALARY >= 10000 THEN SALARY*0.1
            WHEN SALARY >= 5000 THEN SALARY*0.2
            ELSE SALARY*0.3
            END AS BONUS
FROM EMPLOYEES;

데이터 조작어라 부르는 DML(Data Manipulation Language)은 데이터베이스 내에 저장된 데이터를 조회, 추가, 수정, 삭제하기 위해 사용하는 명령어다. 그래서 DML에는 SELECT문, INSERT문, UPDATE문, DELETE문이 있다.

SELECT문과 FROM절

SELECT문은 데이터베이스에 저장되어 있는 데이터를 조회하는 데 사용한다. SELECT문의 FROM절은 조회할 테이블을 지정하고, SELECT절은 조회할 테이블의 열을 지정한다.

SELECT *
FROM 테이블명;

*는 해당 테이블의 모든 열을 조회한다는 의미이다. 위와 같은 경우 FROM절에서 지정한 테이블의 모든 열을 조회한다.

특정 컬럼을 지정하여 조회

SELECT 컬럼1, 컬럼2, 컬럼3 ...
FROM 테이블명;

위와 같이 작성하여 원하는 컬럼만 지정해 조회할 수 있다.

다음은 EMPLOYEES테이블에서 직원아이디, 이름, 연봉 컬럼을 조회한 예시다.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES;

연산자를 활용한 컬럼

연산자를 활용해서 컬럼을 조회할 수 있다. 컬럼 한 개에 대한 연산식 뿐만 아니라 두 컬럼을 연산자를 통해 결합하여 하나의 컬럼처럼 조회할 수 있다.

다음은 EMPLOYEES테이블의 SALARY에 12를 곱한 값을 나타내는 컬럼을 추가로 출력한 예시다.

SELECT FIRST_NAME, SALARY, SALARY*12
FROM EMPLOYEES;

다음은 JOBS테이블의 MAX_SALARY와 MIN_SALARY의 차를 나타내는 컬럼을 추가로 출력한 예시다. 각각의 MAX_SALARY와 MIN_SALARY컬럼을 연산자로 조합하여 하나의 컬럼처럼 출력했다.

SELECT JOB_ID, MAX_SALARY-MIN_SALARY
FROM JOBS;

컬럼의 별칭 지정

SELECT 컬럼 별칭
FROM 테이블명;

SELECT 컬럼 "별칭"
FROM 테이블명;

SELECT 컬럼 AS 별칭
FROM 테이블명;

SELECT 컬럼 AS "별칭"
FROM 테이블명;

위 네 가지 방법으로 컬럼의 별칭을 지정할 수 있다. 별칭을 지정하면 조회 결과에 컬럼이름 대신 별칭이 나온다. 가독성을 위해 첫 번째 방법은 지양하는 것이 좋다고 생각한다.

다음은 조회할 컬럼을 지정한 후 별칭을 적용한 예시다.

SELECT FIRST_NAME AS 이름, SALARY AS 월급, SALARY*12 AS 연봉
FROM EMPLOYEES;

DISTINCT

SELECT DISTINCT 컬럼
FROM 테이블명;

DISTINCT는 중복되는 데이터가 있는 행을 제외하고 출력하는 SQL의 예약어이다. 위와 같이 SELECT절의 컬럼 앞에 DISTINCT를 붙여 해당 컬럼의 값이 중복되는 행을 제외하고 출력할 수 있다.

SELECT DISTINCT MANAGER_ID
FROM EMPLOYEES;

SELECT DISTINCT MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES;

첫 번째 방법은 MANAGER_ID값이 같은 행을 제외한 후 출력하고, 두 번째 방법은 MANAGER_ID, DEPARTMENT_ID 둘 다 같아야 중복되는 행이라 간주한다.

ORDER BY절

ORDER BY절은 조회한 데이터를 특정 컬럼을 기준으로 정렬하는 데 사용한다. SELECT문을 작성할 때, 여러 절 중 가장 마지막 부분에 작성한다.

SELECT 컬럼1, 컬럼2, 컬럼3 ...
FROM 테이블명
ORDER BY 정렬기준 정렬옵션

정렬옵션으로는 ASC와 DESC가 있다. ASC는 Ascending의 약자로 오름차순을 뜻하며, DESC는 Descending의 약자로 내림차순을 뜻한다. 정렬옵션을 생략하면 기본값은 ASC가 적용된다.

오름차순

SELECT FIRST_NAME AS 이름,
       HIRE_DATE AS 입사일, 
       SALARY AS 월급
FROM EMPLOYEES
ORDER BY 입사일 ASC;

입사일을 기준으로 오름차순 정렬했다. ORDER BY에 컬럼명이 아니라 별칭을 적용해도 에러가 발생하지 않는 이유는 ORDER BY절이 가장 마지막에 실행되기 때문이다. SELECT절에서 먼저 컬럼의 별칭이 지정된 후 ORDER BY절이 실행된다.

내림차순

SELECT FIRST_NAME AS 이름,
           HIRE_DATE AS 입사일, 
           SALARY AS 월급
FROM EMPLOYEES
ORDER BY 월급 DESC;

월급을 기준으로 내림차순 정렬했다.

여러 개의 정렬 기준 사용

SELECT FIRST_NAME AS 이름,
           JOB_ID AS 직종, 
           SALARY AS 월급
FROM EMPLOYEES
ORDER BY 직종 ASC, 월급 DESC;

첫 번째 정렬 기준으로 JOB_ID를 지정했고, JOB_ID가 같을 경우 SALARY를 내림차순하여 정렬했다.

주의 사항

ORDER BY절을 통해 조회된 데이터를 정렬하는 것은 많은 자원을 소모한다. 그래서 출력할 데이터를 선정하는 시간보다 정렬하는 데 시간이 더 걸려 서비스 응답 속도가 느려질 수 있다. 따라서 정렬이 꼭 필요한 경우가 아니라면 ORDER BY절은 넣지 않는 것이 좋다.

WHERE절

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 조건식

WHERE절은 SELECT문으로 데이터를 조회할 때 특정 조건을 기준으로 원하는 행을 출력하는 데 사용한다. WHERE절 또한 연산자를 사용하여 더욱 구체적으로 데이터를 조회할 수 있다.

WHERE절의 조건식에 사용되는 컬럼명은 별칭을 사용할 수 없다. SQL의 실행순서 상 WHERE절이 SELECT절보다 먼저 실행되기 때문이다. 즉, WHERE절로 먼저 필터링을 한 후 SELECT절을 통해 컬럼에 별칭이 지정된다.

WHERE절에서 사용하는 연산자나 기호에 대해 주의해야 할 점은 다음과 같다.

• SQL에서 값이 같은지 비교하는 연산자는 =이다. (일반적인 프로그래밍 언어처럼 ==이 아니다.)

• SQL에서 문자열을 나타내는 기호는 작은 따옴표('')다. 일반적인 프로그래밍 언어처럼 큰 따옴표("")가 아니다.

• 값이 NULL인 경우 비교 연산자를 통해 조회할 수 없다.

• 예약어(SELECT, FROM 등)는 대소문자를 구분하지 않지만, 데이터의 '값'은 대소문자를 구분한다. 예를 들어, SQL을 작성할 때 JOB_ID의 IT_PROG과 IT_Prog는 다르게 간주한다.

비교 연산자

SELECT FIRST_NAME AS 이름,
           SALARY AS 월급
FROM EMPLOYEES
WHERE SALARY >= 10000;

SALARY를 기준으로 SALARY가 10000이상인 행만 출력했다. 위에서 언급했던 것 처럼 WHERE절에는 별칭을 사용할 수 없다.

SELECT FIRST_NAME AS 이름,
           JOB_ID AS 직종,
           SALARY AS 월급
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG';

JOB_ID가 IT_PROG인 행만 출력했다.

NULL

데이터베이스에서 null은 아직 값이 결정되지 않았은 것을 의미한다. 그리고 null이 포함된 산술 연산의 결과는 항상 null이다. Java에서 null의 의미와 다르다.

위에서 언급했다시피 값이 null인 경우, 비교 연산자를 통해 조회할 수 없다. 대신에 IS NULL 혹은 IS NOT NULL이라는 예약어를 사용한다.

SELECT FIRST_NAME AS 이름,
           JOB_ID AS 직종,
           COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL;

COMMISSION_PCT가 없는 직원의 이름, 직종, COMMISSION_PCT를 출력한 예시다. COMMISSION_PCT가 없는 행만 출력했으므로 결과도 당연히 COMMISSION_PCT열의 모든 값은 null이다.

논리 연산자

논리 연산자를 통해 여러 조건을 조합하여 WHERE절을 작성할 수 있다.

SELECT FIRST_NAME AS 이름,
           JOB_ID AS 직종,
           SALARY AS 급여,
           COMMISSION_PCT
FROM EMPLOYEES
WHERE (COMMISSION_PCT IS NOT NULL) AND SALARY >= 10000;

COMMISSION_PCT가 있는 직원들 중, 급여가 10000이상인 직원들의 이름, 직종, 급여를 출력했다.

SELECT FIRST_NAME AS 이름,
           JOB_ID AS 직종,
           SALARY AS 급여,
           DEPARTMENT_ID AS 부서아이디
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 40 OR DEPARTMENT_ID = 70 OR DEPARTMENT_ID = 110;

DEPARTMENT_ID가 40,70,110 중 하나인 직원들의 이름, 직종, 급여, 부서아이디를 출력했다.

BETWEEN

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 컬럼 BETWEEN 하한값 AND 상한값;

BETWEEN은 지정한 컬럼의 값의 범위에 속하는 행을 출력한다. 위에서 다룬 논리 연산자로 표현하면 WHERE 하한값 <= 컬럼 AND 컬럼 <= 상한값과 같다.

SELECT FIRST_NAME AS 이름,
           SALARY AS 급여
FROM EMPLOYEES
WHERE SALARY BETWEEN 5000 AND 10000;

급여가 5000이상, 10000이하인 직원들의 이름과 급여를 출력했다.

IN

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 컬럼 IN (값1, 값2 ...);

IN은 지정한 컬럼에서 괄호 안의 값들 중 하나와 일치하는 행을 출력한다. 위에서 다룬 논리 연산자로 표현하면 WHERE 컬럼 = 값1 OR 컬럼 = 값2와 같다.

SELECT FIRST_NAME AS 이름,
           DEPARTMENT_ID AS 부서아이디
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (40, 70, 110);

DEPARTMENT_ID가 40, 70, 110 중 하나인 직원들의 이름과 부서아이디를 출력했다.

LIKE '패턴'

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 컬럼 LIKE '문자_'

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 컬럼 LIKE '_문자'

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 컬럼 LIKE '문자%'

SELECT 컬럼1, 컬럼2 ...
FROM 테이블명
WHERE 컬럼 LIKE '%문자%'

LIKE 뒤의 _, %는 SQL의 패턴문자다. 패턴문자의 의미는 다음과 같다.

• _는 임의의 문자 하나를 의미한다.

  • 예를 들어, NAME LIKE '박_'는 NAME 컬럼에서 성이 '박'이고 이름이 한 글자인 사람을 조회한다.

• %는 0개 이상의 임의의 문자열을 의미한다

  • 예를 들어, NAME LIKE '박%'은 NAME 컬럼에서 성이 '박'인 사람을 조회한다. 문자 박 뒤로 어느 문자열이 와도 상관이 없다.

  • TITLE LIKE '%자바%'는 TITLE 컬럼에서 자바가 포함되어 있는 책을 조회한다.

SELECT EMPLOYEE_ID AS 직원아이디
       FIRST_NAME AS 이름
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'A%';

FIRST_NAME이 'A'로 시작하는 직원들의 직원아이디와 이름을 출력했다.

INSERT문

• 테이블에 새로운 행을 추가한다.

  INSERT INTO 테이블명 (컬럼1, 컬럼2, 컬럼3)
  VALUES (값1, 값2, 값3);

-- SAMPLE_USERS 테이블에 새로운 행을 추가한다.
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME)
VALUES ('hong', 'zxcv1234', 'hong@gmail.com', '홍길동');

UPDATE문

• 테이블에 존재하는 행의 데이터를 변경한다.
• WHERE절에 조건식이 없으면 모든 행에서 해당 컬럼의 값을 변경한다.
• WHERE 조건식이 있으면 조건식을 만족하는 행에서만 해당 컬럼의 값을 변경한다.

  UPDATE 테이블
  SET
    컬럼1 = 값1,
    컬럼2 = 값2,
    컬럼3 = 값3
  WHERE 조건식

  ```sql
• • SAMPLE_USERS 테이블에서 USER_TEL이 존재하는 행의 USER_POINT를 10000으로 변경한다. UPDATE SAMPLE_USERS SET USER_POINT = 10000 WHERE USER_TEL IS NOT NULL;

DELETE문

• 테이블에 저장된 행을 삭제한다.
• WHERE절에 조건식이 없으면 테이블의 모든 행을 삭제한다.
• WHERE 조건식이 있으면 조건식을 만족하는 행만 삭제한다.

  DELETE FROM 테이블명
  WHERE 조건식

  ```sql
• • SAMPLE_PRODUCTS 테이블에서 PRODUCT_MAKER가 '삼성'인 행을 삭제한다. DELETE SAMPLE_PRODUCTS WHERE PRODUCT_MAKER = '삼성';

    <img src="https://velog.velcdn.com/images/dev-yongjun/post/1a077ce3-d822-4c36-b97d-742b7c72bfb7/image.png" align="left">
    <img src="https://velog.velcdn.com/images/dev-yongjun/post/34c220af-7924-4b1a-b898-4bdfbbcadea6/image.png" align="left">
    

다음과 같은 그림을 'ER(Entity-Relational) 다이어그램' 이라고 한다. 앞으로 SQL을 배우며 사용할 테이블 간의 관계를 나타낸 그림이다. 가장 위의 텍스트가 테이블 이름이고 그 아래에 있는 것들이 컬럼 이름이다. 예를 들어, REGIONS 테이블은 region_id, region_name 컬럼을 갖고 있다.

• EMPLOYEES

  • employee_id는 사원의 고유한 번호다.

  • job_id는 JOBS테이블의 job_id를 참조한다.

  • manager_id는 EMPLOYEES 테이블의 employee_id를 참조한다.

  • department_id는 DEPARTMENTS 테이블의 department_id를 참조한다.

• DEPARTMENTS

  • department_id는 DEPARTMENTS 테이블의 고유한 번호다.

  • manager_id는 EMPLOYEES 테이블의 employee_id를 참조한다. 절대 EMPLOYEES 테이블의 manager_id를 참조하는 것이 아니다.

  • location_id는 LOCATIONS 테이블의 location_id를 참조한다.

• LOCATIONS

  • location_id는 소재지의 고유한 번호다.

  • country_id는 COUNTRIES 테이블의 country_id를 참조한다.

• COUNTRIES

  • country_id는 국가의 고유한 아이디다.

  • region_id는 REGIONS 테이블의 region_id를 참조한다.

• REGIONS

  • region_id는 지역의 고유한 번호다.

• JOBS

  • job_id는 직종의 고유한 아이디다.

• JOB_HISTORY

  • employee_id와 start_date가 고유한 값이다. 각각이 고유한 값이 아니라 이 둘을 결합한 것을 고유한 값으로 본다.

  • employee_id는 EMPLOYEES 테이블의 employee_id를 참조한다.

  • job_id는 JOBS테이블의 job_id를 참조한다.

  • department_id는 DEPARTMENTS 테이블의 department_id를 참조한다.

기본키(Primary Key)와 외래키(Foreign Key)

위에서 언급한 '고유한 값'을 기본키(Primary Key)라고 한다. 즉, 행을 구분하는 유일한 값이다. 절대 중복될 수 없다. 그리고 한 테이블에 포함되어 있으면서 다른 테이블의 기본키로 지정된 키를 외래키(Foreign Key)라고 한다. 특정 테이블에서 다른 테이블을 참조할 때 외래키를 활용한다.

데이터베이스에서 참조한다는 것은 예를 들어, EMPLOYEES 테이블에서 각 직원의 부서명을 알고 싶을 때 EMPLOYEES 테이블 내에 있는 department_id를 활용해서 DEPARTMENTS 테이블의 department_name을 찾는다는 의미이다. EMPLOYEES 테이블내에는 부서명(department_name)이 없기 때문이다.

이때 department_id는 EMPLOYEES 테이블에서는 외래키이고, DEPARTMENTS 테이블에서는 기본키가 된다.

복합키(Composite Key)

복합키(Composite Key)는 여러 열을 조합하여 기본키처럼 사용하는 키를 뜻한다. 상황에 따라 하나의 기본키만으로는 행을 식별할 수 없는 경우가 있기 때문이다.

예를 들어, JOB_HISTORY 테이블이 그렇다. 이 테이블은 직원들의 직종 변경 이력을 담고 있다. 그래서 특정 직원의 직종 변경 이력이 여러 번이라면 테이블 내에 employee_id가 중복될 수 있고, 직종 변경 날짜가 같은 직원이 있다면 start_date가 중복될 수 있다.

따라서 employee_id와 start_date를 조합한 형태를 기본키로 사용한다. 이를 복합키라고 한다.

1:N 관계

그림에서 빨간색 동그라미 부분이 1:N 관계 즉, 일대다 관계이다. 일대다 관계의 의미는 한 테이블의 레코드가 다른 테이블의 레코드를 여러 개 가질 수 있다는 것이다.

예를 들어, COUNTRIES 테이블의 region_id는 REGIONS 테이블의 region_id를 참조한다. 그리고 REGIONS 테이블의 region_id는 COUNTRIES 테이블에서 여러 개의 region_id를 가질 수 있다.

그림에서 볼 수 있듯이, REGIONS 테이블에서 region_id는 중복되지 않고 반드시 한 개씩 존재하지만, COUNTRIES 테이블에서 region_id는 여러 개 존재한다.

• 관계형 데이터베이스에 접근할 때 사용하는 프로그래밍 언어다.

• 직역하면 구조화된 '질의' 언어다. 즉, 사용자가 원하는 동작을 DBMS에게 요청할 때 사용한다.

SQL의 종류

• DDL(Data Definition Language)

  • 데이터 정의 언어
  • 데이터베이스의 구조를 정의하는 명령어다.
  • 데이터베이스의 주요 객체를 생성,삭제,변경하는 작업 수행한다.

주요 명령어는 다음과 같다.

• DML(Data Manipulation Language)

  • 데이터 조작 언어
  • 데이터베이스에서 데이터를 추가/조회/변경/삭제하는 작업을 수행한다.

주요 명령어는 다음과 같다.

• DCL(Data Control Language)

  • 데이터 제어 언어
  • 권한제어, 트랜잭션 제어 작업을 수행한다.

주요 명령어는 다음과 같다.

데이터베이스란?

• 데이터를 저장해 놓은 곳

• 여러 사람들이 공유하고 사용할 목적으로 통합관리되는 정보의 집합

• 관련성 있는 데이터의 모음

관계형 데이터베이스

• 관계형 모델에서는 데이터베이스를 관계(Relation)의 집합으로, 관계를 행(Tuple)의 집합으로, 행을 속성(Attribute)의 집합으로 보는 것.

• 관계형 데이터베이스에서는 관계가 테이블, 행이 레코드, 속성이 컬럼으로 대치된다.

• 관계라는 이름으로 불리는 이유는 레코드와 컬럼이 어떤 관계에 의해 모여진 집합으로 보기 떄문이다

• 관계형 데이터 모델에서 속성은 해당 속성이 가질 수 있는 모든 값에 대한 도메인을 가지며, 원자적이어야한다.

관계형 데이터베이스의 특성을 표로 이해해보자

여기서 '속성이 원자적이어야 한다.' 라는 말의 의미는 해당 속성이 더 이상 하위 개념으로 분류될 수 없어야 한다는 의미다. 예를 들어 직급이라는 속성은 사원, 대리, 팀장 등으로 나눌 수 있다 그러나 위의 그림처럼 과장이라는 속성은 더 이상 나눌 수 없다.

DBMS(DataBase Management System)

• 데이터베이스를 관리하고 활용하기위한 프로그램

• 문서작성을 할 때 워드프로세서 프로그램을 사용하는 것처럼 데이터베이스를 다룰 때에도 DBMS를 사용

• 대표적인 DBMS는 오라클, MySQL, MS SQL Server, DB2, Maria DB, H2, HSQLDB 등이 있다.