웹 백엔드 - 1. SQL & JDBC 프로그래밍(1)

데이터베이스란?

 

• 데이터의 집합
• 여러 프로그램들의 통합된 정보를 저장하여 운영할 수 있는 공용 데이터의 집합
• 효율적으로 저장, 검색, 갱신할 수 잇도록 데이터 집합들끼리 연관시키고 조직화 되어야 한다.

 

 

데이터 베이스의 특성

• 실시간 접근이 가능해 사용자의 요구를 즉시처리할 수 있다.
• 정확한 값을 유지하기 위해 삽입, 삭제, 수정 작업 등을 이용하여 데이터를 지속적으로 갱신한다.
• 동시에 여러 사람이 동일한 테이터에 접근하고 이용할 수 있다.
• 사용자가 요구하는 데이터의 내용인 데이터의 값에 따라 참조할 수 있어야 한다.

 

 

데이터베이스 관리 시스템(Database Management System = DBMS)

• 데이터베이스를 관리하는 소프트웨어
• 필수 3기능
  - 정의기능 : 데이터베이스의 논리적, 물리적 구조를 정의
  - 조작기능 : 데이터를 검색, 삭제, 갱신, 삽입, 삭제하는 기능
  - 제어기능 : 데이터베이스의 내용 정확성과 안전성을 유지하도록 제어하는 기능
• 장점
  - 데이터 중복을 최소화 할 수 있다.
  - 데이터의 일관성 및 무결성을 유지할 수 있다.
  - 데이터의 보안을 보장한다.
• 단점
  - 운영비가 비싸다.
  - 백업 및 복구에 대한 관리가 복잡하다.
  - 부분적 데이터베이스 손실이 전체 시스템을 정지시킬 수 있다.

 

데이터베이스를 데이터를 저장할 수 있는 냉장고에 비유한다면, DBMS는 냉장고 안의 공간을 관리하는 시스템을 의미한다. 

---

SQL이란?

• DBMS에 명령을 내리기 위한 언어 (Structured Query Language)
• 데이터를 보다 쉽게 검색 및 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 언어이다.
• DML(Data Manipulation Language) : 데이터를 조작하기 위해 사용한다. (SELECT, INSERT, UPDATE, DELETE 등)
• DDL(Data Definition Language) : 데이터베이스의 스키마를 정의하거나 조작하기 위해 사용한다. (CREATE, DROP, ALTER 등)
• DCL(Data Control Language) : 데이터를 제어하기 위해 사용한다. 권한 관리 및 데이터의 보안, 무결성 등을 정의한다. (GRANT, REVOKE 등)

 

 

테이블(table)의 구성요소

관계형DB의 경우 데이터가 테이블형태로 저장된다.

이미지 출처: https://www.boostcourse.org/web326/lecture/258482/

 

• 테이블 : RDBMS의 기본적인 저장구조로 한 개 이상의 column과 0개 이상의 row로 구성된다.
• 열(Column) : 테이블 상에서의 단일 종류의 데이터를 나타냄. 특정 데이터 타입 및 크기를 가지고 있습니다.
• 행(Row) : Column들의 값의 조합. 레코드라고 불림. 기본키(PK)에 의해 구분. 기본키는 중복을 허용하지 않으며 없어서는 안 됩니다.
• Field : Row와 Column의 교차점으로 Field는 데이터를 포함할 수 있고 없을 때는 NULL 값을 가지고 있습니다.

 

 

DML(select, insert, update, delete)

 

select

기본문형

select (distinct) 컬럼명 (alias)
from 테이블명;

• select : 검색하고자 하는 데이터를 나열한다.
• distinct : 중복행을 제거한다. (옵션사항)
• alias : 선택한 컬럼에 다른 이름을 부여헌다.
• from : 선택한 컬럼이 있는 테이블을 명시한다.

 

 

select * : 전체 데이터를 검색한다.

 

employee 테이블의 모든 데이터를 보여준다.

 

 

select column1, column2 ... : 컬럼을 콤마(,)로 구별해서 나열한다.

 

선택한 컬럼만 골라서 보여준다.

 

 

select column1 컬럼1, column2 컬럼2 ... : 컬럼에 대한 별칭을 부여해 나타낸다.

 

선택한 컬럼에 별칭을 붙여 나타내주고 있다.

 

as를 통해 별칭 설정을 명시적으로 해준 모습.

 

 

select concat(column1, '-', column2) : 문자열 결합함수인 concat을 사용해 두개의 컬럼을 하나로 합쳐 보여준다.

 

alias 사용시 오류를 피하기 위해 ''를 붙여줬다.

 

 

select distinct : 중복행을 제거하여 보여준다.

 

중복된 부서번호는 제외하고 보여준다.

 

 

 

 

order by 절

select (distinct) 칼럼명 (alias)
from 테이블명
order by 칼럼이나 표현식(asc 혹은 desc)

• asc : 오름차순으로 정렬, 기본 값으로 생략 가능하다.
• desc : 내림차순으로 정렬

 

 

order by column1 : 이름을 기준으로 오름차순 정렬한다.

 

이름을 기준으로 한 알파벳 오름차순 순으로 정렬된 것을 확인할 수 있다.

 

 

order by column1 desc : 이름을 기준으로 내림차순 정렬한다.

 

이름을 기준으로 알파벳 내림차순 정렬

 

 

 

 

where 절

select (distinct) 칼럼명 (alias)
from 테이블명
where 조건식
order by
칼럼이나 표현식(asc 혹은 desc)

• 조건식은 컬럼이름이나 표현식의 상수, 연산자로 구성되어 있다.

 

 

산술비교 연산자

예) employee 테이블에서 고용일(hiredate)이 1981년 이전의 사원 이름과 고용일을 출력하시오.

 

 

 

예) employee 테이블에서 월급이 1000이상 2000이하인 모든 데이터를 월급의 내림차순으로 정렬해서 출력하시오.

 

 

 

IN 키워드

예) employee 테이블에서 부서번호가 10 또는 30인 사원이름과 부서번호를 출력하시오.

 

 

 

예) employee 테이블에서 부서번호가 10 또는 30이고, 월급이 1000 이상 2000 이하인 모든 데이터를 출력하시오.

 

 

 

LIKE 키워드

와일드 카드를 사용해 특정 문자를 포함한 값에 대한 조건을 처리한다.

• % : 0에서부터 여러 개의 문자열을 나타낸다.
• _ : 단 하나의 문자를 나타낸다.

 

예) employee 테이블에서 이름에 'A'가 포함된 사원의 이름과 직업을 출력하시오.

 

 

 

예) employee 테이블에서 이름의 세 번째 글자가 A인 모든 컬럼을 출력하시오.

 

 

 

UCASE(),  LCASE(), UPPER(), LOWER()

대문자나 소문자로 변경한다.

 

 

예) employee 테이블에서 이름은 대문자로, 직업은 소문자로 출력하시오.

 

이름은 대문자로, 직업은 소문자로 변경해 출력한다.

 

 

예) employee 테이블에서 이름을 소문자로 출력하시오.

 

lower를 사용해 이름을 소문자로 변경해 출력했다.

 

 

substring()

문자열을 끊어서 보여준다. 이 때 첫 번째 문자열의 인덱스는 0이 아닌 1로 시작한다.

 

 

예) employee 테이블에서 월급이 1000 이상 2000 이하의 이름을 첫 번째부터 세 번째 문자만 출력하고, 이름의 오름차순으로 정렬하시오.

 

 

 

 LPAD, RPAD

설명을 하기보단 표로 직관적으로 이해하는 게 빠를 것 같아. 표를 첨부한다.

이미지 출처: https://www.boostcourse.org/web326/lecture/258485

 

예) employee 테이블에서 10자리의 name을 출력하되 name이 10자리가 안되면 *로 채워서 출력하시오.

이름이 10자리가 넘는 경우 이름이 잘린 채 10자리만 출력된다. 지정한 숫자를 넘으면 정보가 잘린 채 출력된다.

 

 

 

TRIM, LTRIM, RTRIM

공백을 제거하는 함수로 공백을 제거하여 출력한다.

이미지 출처: https://www.boostcourse.org/web326/lecture/258485

 

 

ABS(x)

x의 절대값을 구한다.

 

 

 

 

MOD(n, m), %

n을 m으로 나눈 나머지 값을 출력한다.

 

 

 

 

cast 형변환

type을 변경하는데 유용한 cast함수
사용법 : cast(expression as type) / convert(expression, type)

MYSQL타입

• BINARY
• CHAR
• DATE
• DATETIME
• SIGNED {INTEGER}
• TIME
• UNSIGNED {INTEGER}

 

 

예) 현재 날짜와 시간 (now())를 날짜형식으로 변환해 출력하시오.

 

 

 

그룹함수

여러 개의 컬럼의 값을 이용해 결과값을 하나만 만들어 내는 것

다양한 그룹함수들이 존재한다.

 

 

예) employee 테이블의 salary 평균을 구하시오.

 

 

 

예) employee 테이블에서 부서번호가 30인 직원의 급여 평균과 총 합계를 출력하시오.

 

 

 

예) employee 테이블에서 부서별 직원의 부서번호, 급여 평균과 총합계를 출력

 

 

 

 

그 외.

• FLOOR(x) : x보다 크지 않은 가장 큰 정수를 반환합니다. BIGINT로 자동 변환합니다. 버림
• CEILING(x) : x보다 작지 않은 가장 작은 정수를 반환합니다. 올림
• ROUND(x) : x에 가장 근접한 정수를 반환합니다. 반올림
• POW(x,y) POWER(x,y) : x의 y 제곱 승을 반환합니다.
• GREATEST(x,y,...) : 가장 큰 값을 반환합니다. == max()
• LEAST(x,y,...) : 가장 작은 값을 반환합니다. == min()
• CURDATE(), CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYMMDD 형식으로 반환합니다.
• CURTIME(), CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으로 반환합니다.
• NOW(), SYSDATE() , CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS 형식으로 반환합니다. 
• DATE_FORMAT(date,format) : 입력된 date를 format 형식으로 반환합니다.
• PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차이 개월을 반환합니다.

 

 

 

참고.

www.ciokorea.com/news/35385