데이터베이스란? - 넓은 의미 : 일상적인 정보를 모아 놓은 것 자체 - 일반적 의미 : 특정 기업이나 조직, 개인이 필요에 따라 데이터를 일정한 형태로 저장해 놓은 것 => 많은 사용자들은 더 효율적인 데이터 관리 뿐 아니라 예기치 못한 사건으로 인한 데이터 손상을 피하고 필요한 데이터를 복구하기 위한 소프트웨어가 요구되었는데, 이런 기본적인 요구사항을 만족시켜주는 시스템을 DBMS(Database Management System)이라 함.
DBMS의 장점
인증된 사용자만 참조할 수 있도록 보안 기능을 제공함(무결성) Ex) 테이블 생성 시 사용할 수 있는 다양한 제약조건을 이용해 사용자가 실수로 조건에 위배되는 데이터를 입력한다든지, 관계를 연결하는 중요 데이터 삭제를 방지함
시스템의 갑작스러운 장애로부터 사용자가 입력/수정/삭제하던 데이터가 제대로 반영될 수 있도록 보장해주는 기능과, 시스템 다운/재해 등의 상황에도 데이터를 회복/복구 기능을 제공
데이터베이스의 발전 역사역사역사...ㅠ
관계형 데이터베이스(Relational Database) - 1970년 E.F.Codd 박사의 논문에서 처음으로 관계형 데이터베이스가 소개 - 다수기업에선 기존 관계형 데이터베이스에 객체 지원 기능을 추가한 객체 관계형 데이터베이스 사용 - 파일 시스템은 하나의 파일을 많은 사용자가 "동시검색 가능" but) "동시 입력/수정/삭제 불가능"
관계형 데이터베이스의 장점
정규화를 통한 합리적인 테이블 모델링을 통해 "이상(ANOMALY)현상 제거/데이터 중복제거/동시성관리/병행제어가 가능"하므로 많은 사용자가 동시에 데이터 공유 및 조작가능
메타 데이터를 총괄 관리가 가능하므로, 데이터의 성격 속성 또는 표현방법들을 체계화 가능
데이터 표준화를 통한 데이터 품질을 확보 할 수 있음
SQL (Structured Query Language)
SQL이란? - 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터를 제어하기 위해 사용하는 언어 - 일반적인 프로그래밍 언어와 달리, SQL은 관계형 데이터베이스에 대한 전담접속용도로 사용 - 특정 데이터들의 집합에서 필요로 하는 데이터를 꺼내 조회하고 새로운 데이터를 입력/수정/삭제하는 행위 즉, 데이터베이스와 대화를 가능하도록 하는 매개역할을 SQL이 함
SQL 문장들의 종류
명령어의 종류
명령어
설명
데이터 조작어 (DML, Data Maniqulation Language)
SELECT
데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어로 RETRIEVE라고 함
INSERT UPDATE DELETE
데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말함. 예를들어 테이블에 새로운 행을 집어넣거나, 기존 데이터를 수정하거나 원하지 않는 데이터를 삭제하는 것들의 명령어들을 DML이라 함
데이터 정의어 (DDL, Data Definition Language)
CREATE ALTER DROP RENAME
테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어. 구조를 생성/변경/삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라 함
데이터 제어어 (DCL, Data Control Language)
GRANT REVOKE
데이터 베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라 함
트랜잭션 제어어 (TCL, Transaction Control Language)
COMMIT ROLLBACK
논리적인 작업 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션)별로 제어하는 명령어를 말함
STANDARD SQL 개요
테이블
테이블이란? - 데이터를 저장하는 객체로서 관계형 데이터베이스의 기본단위로, 모든 자료는 테이블에 등록됨 - 어느 특정한 주제와 목적으로 만들어지는 일종의 집합
테이블 용어
용어
설명
테이블
행과 컬럼의 2차원 구조를 가진 데이터 저장 장소이며, 가장 기본적인 개념
컬럼/열
2차원 구조를 가진 테이블에서 세로방향으로 이루어진 하나하나의 특정 속성으로 더이상 나눌 수 없는 특성임
행
2차원 구조를 가진 테이블에서 가로방향으로 연결된 데이터
테이블 관계 용어
용어
설명
정규화
데이터를 분할해 데이터의 정합성을 확보하고, 불필요한 중복을 줄이는 프로세스 => 데이터를 한 테이블이 아닌 복수 테이블로 분할/저장하여 불필요한 중복 제거 목적
기본키(PK)
테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼
외부키(FK)
다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼
ERD (Enity Relationship Diagram)
ERD란? - 데이터들 간에는 어떤 의미관계가 존재하고 다른 테이블과도 어떤 의미의 연관성이나 관계를 가짐 - 이와 같은 관계의 의미를 직관적으로 표현할 수 있는 좋은 수단이 ERD임
ERD의 구성요소
구분
정의
엔터티 (Entity)
DB가 표현하려고 하는 유형,
관계 (Relationship)
Entity간의 의미 있는 연결 또는 연관성을 의미하는 요소
속성 (Attribute)
Entity나 Relationship의 특성을 나타내는 요소로, 이름을 가진 정보의 가장 작은 논리적 단위
데이터 유형
데이터 유형이란? - 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준 - 특정 컬럼을 정의할 때, 선언한 데이터 유형은 그 컬럼이 받아들일 수 있는 자료의 유형을 규정함
Vendor별 데이터 유형
ANSI/ISO 기준 - NUMERIC Type의 하위 개념으로 NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE, PRECISION
SQL Server와 Sybase - ANSI/ISO 기준의 하위개념에 맞추어, 작은 정수형/정수형/큰 정수형/실수형 등 여러 숫자타입 제공하며, 추가로 MONEY/SMALLMONEY 등의 숫자타입도 가짐
Oracle - NUMBER 한가지 숫자 데이터형만 지원
자주 쓰이는 데이터 유형
데이터 유형
설명
CHARACTER(s)
- 고정길이 문자열 정보 (Oracle, SQL Server 모두 CHAR로 표현 - s는 기본 길이 1바이트, 최대길이 Oracle 2000바이트, SQL Server 8000바이트 - s만큼 최대 길이/고정길이를 갖고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우에는 그 차이 길이만큼 공간으로 채워짐
VARCHAR(s)
- CHARACTER VARYING의 약자로, 가변 길이 문자열 정보(Oracle은 VARCHAR2로 표현, SQL Server는 VARCHAR로 표현) - s는 최소 길이 1바이트, 최대 길이 Oracle 4000바이트, SQL Server 8000바이트 - s만큼의 최대 길이를 갖지만 가변길이로 조정되므로, 할당된 변수값의 바이트만 적용 (Limit개념)
NUMERIC
- 정수, 실수 등 숫자 정보(Oracle은 NUMBER로, SQL Server는 10가지 이상의 숫자 타입을 가짐) - Oracle은 처음에 전체 자리 수를 지정하고, 그 다음 소수 부분의 자리 수를 지정 Ex) 정수부분이 6자리, 소수부분이 2자리면 NUMBER(8,2)가 됨
DATETIME
- 날짜와 시각 정보(Oracle은 DATE로 표현, SQL Server는 DATETIME으로 표현) - Oracle은 1초 단위, SQL Server는 3.33ms(millisecond) 단위 관리
CHAR vs VARCHAR
차이점
CHAR
VARCHAR
저장영역
-고정 길이
- 가변길이 - 길이가 다양한 컬럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 컬럼에 적합 - CHAR보다 작은 영역에 저장가능
문자열 비교 방법
- 공백을 채우고 앞에서부터 한문자씩 - 끝의 공백만 다른 문자열은 같다고 판단
- 맨 처음부터 한 문자씩 비교 - 공백도 하나의 문자로 취급 - 끝의 공백이 다르면 다른문자로 판단함
SELECT 문
SELECT
SELECT 출력할 컬럼명, 출력할 컬럼명, ...
FROM 출력할 컬럼들이 있는 테이블명;
SELECT문에 사용되는 명령어
DISTINCT 옵션
SELECT [ALL/DISTINCT] 출력할 컬럼명, 출력할 컬럼명, ...
FROM 출력할 컬럼들이 있는 테이블명;
- ALL : Default 옵션으로, 중복된 데이터가 있어도 모두 출력함 - DISTINCT : 중복된 데이터가 있을 경우 1건으로 처리해 출력함
아스타리스크(*) 사용 - 해당 테이블의 모든 컬럼 정보를 보고싶을 때 사용
SELECT *
FROM 테이블명;
ALIAS 부여하기 - 조회된 결과에 일종의 별명을 부여해 컬럼 레이블을 변경
AS 사용 (AS는 옵션이라 없어도 됨)
SELECT 출력할 컬럼명 AS 별명
FROM 테이블명;
" "(Double quotation) 사용
SELECT 출력할 컬럼명 "별명"
FROM 테이블명;
- ALIAS가 공백, 특수문자를 포함할 경우 or 대소문자 구분이 필요할 때 사용
SQL Server의 경우 ' ', [ ] 방식도 가능
산술 연산자와 합성 연산자
산술 연산자 - NUMBER와 DATE자료형에 대해 적용됨 - (), *, /, +, - 의 우선순위를 가짐
SELECT 출력할 컬럼명 AS 별명, 컬럼명 - 컬럼명 AS 별명
FROM 테이블명;
합성 연산자 - 문자와 문자를 연결하는 합성(CONCATENATION) 연산자 - 컬럼과 문자 또는 다른 컬럼과 연결함 - 문자 표현식의 결과에 의해 새로운 컬럼을 생성함
Oracle의 경우 - 2개의 수직바( || ) 또는 CONCAT(문자열, 문자열)을 이용
SELECT 컬럼명 || 추가할 문자열
FROM 테이블 명;
SQL Server의 경우 - 더하기 표시(+) 또는 CONCAT(문자열, 문자열)을 이용
단일행 함수의 특징 - SELECT, WHERE, ORDER BY 절에 사용가능 - 각 행들에 대해 개별적으로 작용해 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴 - 여러 인자를 입력해도 단 하나의 결과만 리턴 - 함수의 인자로 상수/변수/표현식이 사용 가능하고, 하나의 인수 or 여러개의 인수 가질 수 있음 - 특별한 경우가 아니면 함수의 인자로 함수를 사용하는, 함수의 중첩이 가능
다중행 함수(Multi-Row Function)
함수명 (컬럼이나 표현식 [, Arg1, Arg2, ... ])
- 집계함수 (1장 5절) - 그룹함수 (2장 3절) - 윈도우함수 (2장 4절)
문자형 함수
단일행 문자열 함수의 종류
문자형 함수
함수 설명
LOWER(문자열) / UPPER(문자열)
문자열의 알파벳 문자를 소문자/대문자로 바꾸어 줌
ASCII(문자)
문자나 숫자를 ASCII 코드 번호로 바꾸어 줌
CHR / CHAR (ASCII번호)
ASCII 코드 번호를 문자나 숫자로 바꾸어 줌
CONCAT(문자열1, 문자열2)
문자열1과 문자열2를 연결함
SUBSTR / SUBSTRING(문자열, m[,n])
문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려줌 n이 생략되면 마지막 문자까지임
LENGTH/LEN(문자열)
문자열의 개수를 숫자값으로 돌려줌
LTRIM(문자열 [,지정문자])/ LTRIM(문자열)
문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거함 (지정문자가 생략되면 공백 값이 디폴트) SQL Server에서는 LTRIM함수에 지정문자 사용불가 공백만 제거가능
문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당문자를 제거함 (지정문자가 생략되면 공백 값이 디폴트) SQL Server에서는 LTRIM함수에 지정문자 사용불가 공백만 제거가능
TRIM([leading|trailing|both] 지정문자 FROM 문자열) / TRIM(지정문자 FROM 문자열)
문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정문자를 제거함 (leading | trailing | both가 생략되면 both가 디폴트) SQL Server에서는 TRIM함수에 leading, trailing, both를 사용할 수 없음. 즉, 양쪽에 있는 지정문자만 제거 할 수 있음)
참고) dual 테이블 : 간단하게 함수를 이용해서 계산 결과값을 확인하고 싶을 때 - SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블 - 사용자 SYS가 소유하며 모든 사용자가 엑세스 가능한 테이블 - DUMMY라는 문자열 유형 컬럼에 'X'값이 들어있는 행 1건을 포함하고 있음
문자열을 주어진 FORMAT형태인 날짜 타입으로 변환 expression을 주어진 style형태인 목표 데이터 유형으로 변환
CASE 표현
단일행 CASE 표현의 종류
CASE 표현
함수 설명
CASE SIMPLE_CASE_EXPRESSION 조건 [ELSE 디폴트값] END
SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건 내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE 절을 수행
CASE SEARCHED_CASE_EXPRESSION 조건 [ELSE 디폴트값] END
SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건 내의 THEN절을 수행하고, 조건이 맞지 않으면 ELSE절을 수행
DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ..., 디폴트값])
Oracle에서만 사용되는 함수, 표현식 값이 기준값1이면 값1을 출력하고, 기준값2이면 값2를 출력함. 기준값이 없으면 디폴트값 출력 ※ SIMPLE_CASE_EXPRESSION 조건과 동일
CASE
SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSION 조건
[ ELSE 디폴트값 ]
END
NULL 관련 함수
NVL/ISNULL 함수
NULL이란? - 아직 정의되지 않은 값으로 0또는 공백과 다름 - 테이블을 생성할 때 NN 또는 PK로 정의되지 않은 모든 데이터 유형은 NULL값 포함 가능 - NULL 값을 포함하는 연산의 경우 결과값도 NULL임 - 결과가 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용함
단일행 NULL 관련 함수의 종류
일반형 함수
함수 설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)
표현식1의 결과 값이 NULL이고 표현식1,2의 결과 데이터 타입이 같다면 표현식2의 값을 출력함 ※ NULL 관련 가장 많이 사용되는 함수이므로 상당히 중요
NULLIF(표현식1, 표현식2)
표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴함
COALESCE(표현식1, 표현식2, ...)
임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄 모든 표현식이 NULL이라면 NULL을 리턴
NULL과 공집합
공집합이란? - 조건에 맞는 데이터가 한 건도 없는 경우 - NULL 데이터와는 다르게 이해해야함
NULL vs 공집합
NULL - NULL은 NVL/ISNULL 함수를 사용하면 원하는 값이 출력되도록 할 수 있음 사용예시
공집합 - 공집합은 NVL/ISNULL 함수를 사용해도 공집합이 출력됨 - 그러므로 그룹함수와 NVL/ISNULL 함수를 같이 이용해서 원하는 값 출력 가능 사용예시
NULLIF
NULLIF(EXPR1, EXPR2)
- EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴함
기타 NULL 관련 함수 (COALESCE)
COALESCE(EXPR1, EXPR2)
- 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을, 모두 NULL이라면 NULL을 리턴함
WHERE 절
WHERE 조건절 개요
WHERE 절의 필요성 - SELECT 절과 FROM 절만 사용해 자료 검색시, 실제로 원하는 자료를 확인하기 어려울 수 있음 - SQL문장에 WHERE절을 이용해 자료를 제한할 수 있음 - 데이터 베이스에 많은 사용자나 프로그램들이 동시에 접속해 다량의 트랜젝션을 발생하고 있는데, WHERE절을 사용하지 않아 필요없는 자료까지 요청하는 SQL문장은 데이터베이스가 설치된 서버의 CPU나 메모리 등의 시스템 자원을 과다하게 사용하게 됨
WHERE 절의 구조
SELECT [DISTINCT/ALL] 컬럼명 [ALIAS명]
FROM 테이블명
WHERE 조건식;
연산자의 종류
연산자의 종류
구분
연산자
연산자의 의미
비교 연산자
= > >= < <=
같다 보다 크다 보다 크거나 작다 보다 작다 보다 작거나 같다
SQL 연산자
IN (list) LIKE '비교문자열' BETWEEN a AND b IS NULL
리스트에 있는 값 중에서 어느 하나라도 일치한다. 비교 문자열과 형태가 일치한다 (%, _ 사용) a이거나, a와 b의 값 사이거나, b의 값을 가짐 NULL값을 갖는다
논리 연산자
AND OR NOT
앞의 조건과 뒤의 조건을 만족해야함 앞의 조건 혹은 뒤의 조건 중 하나라도 만족해야함 뒤에 오는 조건의 반대되는 결과를 돌려줌
부정 비교 연산자
!= ^= <> NOT 컬럼명 = NOT 컬럼명 >
같지않음 같지않음 같지않음(ISO표준, 모든 운영체제에서 사용 가능) ~와 같지않음 ~보다 크지않음
부정 SQL 연산자
NOT BETWEEN a AND b NOT IN (list) IS NOT NULL
a가 아니고, a와 b사이 값도 아니고, b도 아님 list의 값과 일치하지 않음 NULL값을 갖지 않음
연산자의 우선순위 => 괄호 ( ) > 비교 연산자, SQL 연산자 > NOT 연산자 > AND > OR
비교 연산자
비교 연산자의 종류
구분
연산자
연산자의 의미
비교 연산자
= > >= < <=
같다 보다 크다 보다 크거나 작다 보다 작다 보다 작거나 같다
- CHAR/VARCHAR2 같은 문자형 타입을 가진 컬럼을 특정 값과 비교 => 작은따옴표로 묶어 비교 처리 - 숫자 유형 컬럼이 숫자로 변환 가능한 문자열과 비교 => 상대 타입을 숫자타입으로 바꾸어 비교 처리
문자 유형 비교 방법
구분
비교 방법
비교 연산자의 양쪽이 모두 CHAR 유형 타입인 경우
길이가 서로 다른 CHAR형 타입이면 작은 쪽에 스페이스를 추가해 길이를 같게 한 후 비교 서로 다른 문자가 나올 때까지 비교 달라진 첫 번째 문자의 값에 따라 크기를 결정함 문자 끝 블랭크 수만 다르다면 서로 같은 값으로 결정함
비교 연산자의 어느 한쪽이 VARCHAR 유형 타입인 경우
서로 다른 문자가 나올 때까지 비교 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후 길이가 긴 것이 크다고 판단 길이가 같고 다른 것이 없다면 같다고 판단 VARCHAR는 NOT NULL까지 길이이므로, 문자 끝 공백(Blank)도 문자로 취급함
상수값과 비교할 경우
상수 쪽을 변수 타입과 동일하게 바꾸고 비교 변수 쪽이 CHAR 유형이면 위의 CHAR경우, VARCHAR 유형이면 위의 VARCHAR경우를 적용
SQL 연산자
SQL 연산자의 종류
구분
연산자
연산자의 의미
SQL 연산자
IN (list) LIKE '비교문자열' BETWEEN a AND b IS NULL
리스트에 있는 값 중에서 어느 하나라도 일치한다. 비교 문자열과 형태가 일치한다 (%, _ 사용) a이거나, a와 b의 값 사이거나, b의 값을 가짐 NULL값을 갖는다
IN (list) 연산자 - WHERE 컬럼명 IN (값1, 값2, ...) 형태 - 컬럼 여러개의 값을 비교하고 싶을때, (컬럼명1, 컬럼명2, ... ) 형태 사용
LIKE 연산자 - 비교 연산자 '='와 비슷한 역할을 하지만, 와일드카드(WildCard)를 사용할 수 있다는 차이가 있음
와일드 카드
설명
%
0개 이상의 어떤 문자를 의미
_ (언더바)
1개인 단일 문자를 의미
BETWEEN a AND b 연산자
IS NULL 연산자 - NULL 값의 비교는 비교 연산자를 통해 비교할 수 없고, 만일 비교를 하면 거짓(FALSE)을 리턴함 - NULL 비교 연산은 IS NULL, IS NOT NULL이라는 정해진 문구를 사용해야 제대로된 결과가 나옴
논리 연산자
논리 연산자의 종류
구분
연산자
연산자의 의미
논리 연산자
AND OR NOT
앞의 조건과 뒤의 조건을 만족해야함 앞의 조건 혹은 뒤의 조건 중 하나라도 만족해야함 뒤에 오는 조건의 반대되는 결과를 돌려줌
논리 연산자의 우선순위 => 괄호 ( ) > NOT > AND > OR
부정 연산자
부정 연산자의 종류
구분
연산자
연산자의 의미
부정 비교 연산자
!= ^= <> NOT 컬럼명 = NOT 컬럼명 >
같지않음 같지않음 같지않음(ISO표준, 모든 운영체제에서 사용 가능) ~와 같지않음 ~보다 크지않음
부정 SQL 연산자
NOT BETWEEN a AND b NOT IN (list) IS NOT NULL
a가 아니고, a와 b사이 값도 아니고, b도 아님 list의 값과 일치하지 않음 NULL값을 갖지 않음
GROUP BY, HAVING 절
집계함수
집계함수란? - 여러 행들의 그룹이 모여 그룹당 단 하나의 결과를 돌려주는 함수 - 집계함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행함
집계함수명([DISTINCT | ALL] 컬럼이나 표현식)
- ALL : Default 옵션이므로 생략 가능 - DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션
집계함수의 종류
집계함수
사용 목적
COUNT(*)
NULL 값을 포함한 행의 수를 출력
COUNT(표현식)
표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력
SUM([DISTINCT | ALL] 표현식)
표현식의 NULL값을 제외한 합계를 출력
AVG([DISTINCT | ALL] 표현식)
표현식의 NULL값을 제외한 합계를 출력
MAX([DISTINCT | ALL] 표현식)
표현식의 최댓값을 출력 (문자, 날짜 데이터 타입도 가능)
MIN([DISTINCT | ALL] 표현식)
표현식의 최솟값을 출력 (문자, 날짜 데이터 타입도 가능)
STDDEV([DISTINCT | ALL] 표현식)
표현식의 표준 편차를 출력
VARIANCE/VAR([DISTINCT | ALL] 표현식)
표현식의 분산을 출력
기타 통계 함수
Vendor별로 다양한 통계식을 제공함
GROUP BY 절
GROUP BY 절이란? - 테이블에 1차적으로 존재하는 테이터 이외의 정보인 2차 가공 정보를 얻기위해 사용함 - SQL문에서 FROM절과 WHERE절 뒤에 위치 - 데이터들을 작은 그룹으로 분류해 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용함
SELECT [DISTINCT] 컬럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 컬럼이나 표현식]
[HAVING 그룹조건식]
- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계함수를 사용함 - GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없음 - GROUP BY 절보다 WHERE 절이 먼저 수행되므로, 집계함수는 WHERE절에 올 수 없음 - WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거함
GROUP BY 사용예시 - GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT절에서 그룹 단위의 컬럼과 집계함수를 사용 가능 - SELECT에 선언한 ALIAS는 ORDER BY절에서는 재활용이 가능하지만, GROUP BY 절에서는 사용 불가
HAVING 절
HAVING 절이란? - ㅇㄹ
SELECT [DISTINCT] 컬럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 컬럼이나 표현식]
[HAVING 그룹조건식]
- HAVING 절은 GROUP BY 절의 기존 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있음 - GROUP BY 절에 의한 소그룹별로 만들어진 데이터 집계 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력함
HAVING 절 사용 예시 - HAVING 절은 WHERE 절과 비슷하지만, 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 차이 있음
- GROUP BY 절과 HAVING 절의 순서를 바꾸어 수행하여도 문법에러 없이 동일한 결과를 출력함
- SELECT 절에 컬럼/집계함수가 없어도, GROUP BY 절의 기존항목이나 소그룹의 집계함수는 사용 가능
- GROUP BY 소그룹의 데이터 중 일부만 필요할 경우,
GROUP BY 연산 전 WHERE 절에서 조건을 적용해, 필요한 데이터를 얻어 GROUP BY 연산을 하는 방법 - WHERE 절을 이용해 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 활용 측면에서 바람직함
GROUP BY 연산 후 HAVING 절에서 필요한 데이터를 필터링하는 방법
CASE 표현을 활용한 월별 데이터 집계 - '집계합수(CASE())~GROUP BY' 기능은 모델링의 제1정규화로 인해 반복되는 컬럼의 경우 구분 컬럼을 두고 여러 개의 레코드로 만들어진 집합을 정해진 컬럼 수만큼 확장해 집계 보고서를 만드는 유용한 기법임 - 부서별로 월별 입사자의 평균 급여를 알아보자
개별 데이터 확인 - 개별 입사정보에서 월별 데이터를 추출하는 작업 [ 결과문장이 길어져 80번 부서만 출력함 ]
월별 데이터 구분 - 월별 컬럼으로 구분하여 출력 이렇게 컬럼들이 많아지면 다음 라인으로 넘어가므로 eclipse를 이용해 결과물을 보도록 하자컬럼들이 많을때, eclipse 툴을 이용하면 편하게 볼 수 있음
부서별 데이터 집계 -
집계함수와 NULL 처리 - 다중행함수는 모든 입력이 NULL인 경우에만 함수 결과가 NULL이고, 아니면 NULL행을 연산 대상에서 제외 - CASE 표현 시 ELSE 절을 생략하게 되면 Default 값이 NULL이 됨 - 집계함수는 연산할때 데이터에 NULL이 있으면 자동으로 연산에서 빠지기에, NULL을 0으로 바꾸어 연산하는 것은 시스템의 자원을 낭비하는 것임
ORDER BY 절
ORDER BY 절
ORDER BY 절이란? - SQL 문장으로 조회한 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하여 출력할 때 사용 - ORDER BY 절에 컬럼명 대신에 ALIAS명이나 컬럼 순서를 나타내는 정수도 사용가능 - ORDER BY 절의 정렬 방식의 Default값은 ASC(오름차순)이다. / 내림차순은 DESC.
SELECT 컬럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 컬럼이나 조건식]
[HAVING 그룹조건식]
[ORDER BY 컬럼이나 표현식 [ASC 또는 DESC]];
- NULL은 Oracle에서는 가장 큰 값으로 취급하고, SQL Server에서는 가장 작은 값으로 취급함 - 날짜형 데이터 타입은 오름차순으로 정렬할 경우 날짜가 빠른 값이 먼저 출력됨
ORDER BY 사용예시 - ORDER BY절에 적은 컬럼들은, 가장 앞의 컬럼으로 정렬하다가 같은 값이 나오면 뒤의 컬럼으로 정렬됨 - ORDER BY절에 컬럼은 컬럼명이거나 컬럼번호(가장 첫 컬럼이 1번)로 적어도 됨 - 컬럼번호의 경우, SELECT에 가장 처음 적은 컬럼명이 1번임
SELECT 문장 실행 순서
SELECT 문장 실행 순서 - 옵티마이저가 SQL문장의 SYNTAX, SEMANTIC 에러를 점검하는 순서(=문장을 해석하는 순서)와 같음
FROM : 발췌 대상 테이블을 참조함
WHERE : 발췌 대상 데이터가 아닌 것을 제거함
GROUP BY : 행들을 소그룹화함
HAVING : 그룹핑된 값의 조건에 맞는 것만을 출력함
SELECT : 데이터 값을 출력/계산함
ORDER BY : 데이터를 정렬함
사용 예시 - 인라인 뷰의 SELECT 절에서 정의한 컬럼은 메인 쿼리에서도 사용 가능 - 서브 쿼리의 SELECT 절에서 선택되지 않은 컬럼들은 계속 유지되는 것이 아니라 서브 쿼리 범위를 벗어나면 더이상 사용할 수 없게 됨(인라인 뷰도 동일) - GROUP BY 절에서 그룹핑 기준을 정의하게 되면 데이터베이스는 일반적인 SELECT 문장처럼 FROM 절에 정의된 테이블의 구조를 그대로 가지고 가는 것이 아니라, GROUP BY 절의 그룹핑 기준에 사용된 컬럼과 집계함수에 사용될 수 있는 숫자형 데이터 컬럼들의 집합을 새로 만들기때문에, GROUP BY 이후 수행 절인 SELECT 절이나 ORDER BY 절에서 개별 데이터를 사용하는 경우 에러가 발생함 JOB_ID 별 SALARY는 계산할 수 있는 값이 아니므로 오류JOB_ID 별 SALARY의 평균은 계산할 수 있음
조인 (JOIN)
조인 개요
조인이란? - 두 개 이상의 테이블들을 연결해 데이터를 출력하는 것을 조인이라 함 - 일반적으로, PK나 FK 값의 연관에 의해 조인이 성립되지만, 어떤 경우에는 이런 관계가 없이 논리적인 값들의 연관만으로 조인이 성립될 수 있음 - 주의) FROM 절에 여러 테이블이 있어도, SQL에서 데이터를 처리시 단 두개의 집합에만 조인이 일어남 - 예를들어 A, B, C, D 4개의 테이블을 조인하고자 할 경우, 옵티마이저는 (((A JOIN D) JOIN C) JOIN B)와 같이 순차적으로 조인을 처리함
EQUI JOIN
EQUI JOIN이란? - 두 개의 테이블 간에 컬럼 들이 서로 정확하게 일치하는 경우에 사용되는 방법으로 PK↔FK 관계 기반 - 테이블 구조의 PK↔FK 관계를 이용하는 것이지, 반드시 PK↔FK 관계로만 EQUI JOIN이 성립하는 건 아님
EQUI 조인의 형태 - JOIN 조건은 WHERE 절에 기술해야함 => 조인에 사용되는 여러개의 테이블에 같은 컬럼명이 존재하는 경우 (아니라면 없어도 오류는 안남), DBMS는 어떤 컬럼을 사용해야 할지 모르므로 파싱단계에서 오류가 남 => 개발자나 사용자가 조회할 데이터가 어느 테이블에 있는 컬럼인지 쉽게 알 수 있게 하므로 SQL에 대한 가독성이나 유지보수성을 높이는 효과가 있음
묵시적 표현법
SELECT 테이블1.컬럼명, 테이블2.컬럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블2.컬럼명 = 테이블1.컬럼명 -- WHERE 절에 JOIN 조건을 기술함
;
명시적 표현법 (ANSI/ISO 표준 방식)
SELECT 테이블1.컬럼명, 테이블2.컬럼명, ...
FROM 테이블1 INNER JOIN 테이블2
ON 테이블2.컬럼명 = 테이블1.컬럼명 -- ON 절에 JOIN 조건을 기술함
;
Non EQUI JOIN
Non EQUI JOIN이란? - 두 개의 테이블 간에 논리적인 연관관계를 갖고 있으나, 컬럼 값들이 서로 일치하지 않는 경우에 사용 - '=' 연산자가 아닌 다른 (Between, >, >=, <, <= 등) 연산자들을 사용해 JOIN을 수행함 - 데이터 모델에 따라서 Non EQUI JOIN이 불가능한 경우도 있음
사용 예시 - EMP 테이블의 SAL 컬럼이 SAL GRADE 테이블의 LOSAL 컬럼과 HISAL컬럼 사이에 있는 값인지 비교하여 LOSAL 컬럼의 GRADE 값을 얻어오는 예제
3개 이상 TABLE JOIN - 가져오고 싶은 컬럼을 가진 테이블들이 관계가 없을때 다른 테이블을 거쳐 가져오기 위함
OUTER JOIN
OUTER JOIN이란? - EQUI JOIN, Non EQUI JOIN은 모두 조인 조건의 결과가 참인 행들만 반환하는 INNER JOIN임 - 반면 OUTER JOIN은 조인 조건을 만족하지 않는 행들도 함께 반환할 때 사용함 - A테이블과 B테이블을 조인하되, B테이블에 JOIN할 데이터가 있으면 B테이블 데이터를 함께 출력하고, B테이블에 JOIN할 데이터가 없어도 A테이블의 모든 데이터를 출력하고 싶을 때 사용
SELECT 테이블1.컬럼명, 테이블2.컬럼명, ...
FROM 테이블1, 테이블 2
WHERE 테이블2.컬럼명(+) = 테이블1.컬럼명;
- '(+)' 표시의 반대편에 있는 테이블이 OUTER JOIN의 기준 테이블(모든 데이터를 표시하는 테이블)이 됨 - OUTER 조인에 실패한 행들의 경우 기준테이블의 컬럼값이 표시되고, 그 외 테이블에 가져오는 컬럼들은 NULL로 표시됨
표준 조인
FROM 절 조인 형태
ANSI/ISO SQL에서 표시하는 FROM 절의 조인 형태 - INNER JOIN - NATURAL JOIN - USING 조건절 - ON 조건절 - CROSS JOIN - OUTER JOIN
INNER JOIN
INNER JOIN 특징 - 조인의 DEFAULT 옵션으로 조인 조건을 만족하는 행들만 반환함 - CROSS JOIN, OUTER JOIN과 같이 사용 불가
NATURAL JOIN
NATURAL JOIN 특징 - INNER JOIN의 하위개념 - 두 테이블 간에 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI(=) JOIN을 수행 - NATURAL JOIN이 명시되면 추가로 USING 조건절, ON 조건절, WHERE 절에서 조인 조건 정의 불가 - 조인이 되는 테이블의 데이터 성격(도메인)과 컬럼명 등이 동일해야함 - SQL Server에서는 지원하지 않음
사용 예시 - 별도의 조인컬럼을 지정하지 않았지만, 두 개의 테이블에서 DEPARTMENT_ID라는 공통된 컬럼을 인식해 조인을 처리한 것임 - NATURAL JOIN은 조인이 되는 테이블의 데이터 성격과 컬럼명 등이 동일해야하는 제약조건이 있음 - 아래 *(아스타리스크)처럼 별도의 컬럼순서를 지정하지 않으면, NATURAL JOIN의 기준이 되는 컬럼들이 다른 컬럼보다 먼저 출력되고 이때, NATURAL JOIN은 조인에 사용된 같은 이름의 컬럼을 하나로 처리함 - NATURAL JOIN vs INNER JOIN DEPARTMENT_ID가 20인 DEPARTMENT_NAME이 CONSULTING으로 변경됨NATURAL [INNER] JOIN 사용시, 변경한 'CONSULTING'을 DEPARTMENT_NAME으로 가지는 행은 출력되지 않음&amp;amp;nbsp;일반적인 INNER JOIN DEPARTMENT_NAME이 변경된 행은 결과에서 제외됬고, 2개의 컬럼으로 표시되는 차이가 있음
USING 조건절
USING 조건절 특징 - NATURAL JOIN에서는 같은 이름을 가진 모든 컬럼들에 대해 조인이 이루어지지만, FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN 가능 - SQL Server에서는 지원 안함
사용 예시 - 아래 *(아스타리스크)처럼 별도의 컬럼순서를 지정하지 않으면, NATURAL JOIN의 기준이 되는 컬럼들이 다른 컬럼보다 먼저 출력되고 이때, NATURAL JOIN은 조인에 사용된 같은 이름의 컬럼을 하나로 처리함 - USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 조인 컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없음 - DEPARTMENT_NAME이 바뀐 행은 결과에서 제외됨 - USING에 사용된 LOCATION_ID와 DEPARTMENT_ID가 앞에 출력되고, 조인 조건에 참여하지 않은 DEPARTMENT_NAME 컬럼은 2개의 컬럼으로 표시됨
ON 조건절
ON 조건절이란? - 조인 서술부(ON 조건절)와 비 조인 서술부(WHERE 조건절)를 분리해 이해가 쉬움 - 임의의 조인조건을 지정하고, 다른 컬럼명을 조인 조건으로 사용하고, 조인 컬럼을 명시 할 때 사용 - ON 조건절을 사용한 조인의 경우는, 이름이 같은 컬럼들에 대해 접두사를 명확히 지정해야함 - WHERE 절의 조인 조건과 같은 기능을 하면서도, 명시적으로 조인의 조건을 구분할 수 있음 - ON 절에 사용된 괄호는 옵션
ON 조건절 사용 방법
WHERE 절과의 혼용 ㄴ ON 조건절과 WHERE 검색 조건은 충돌없이 사용 가능
ON 조건절 + 데이터 검증 조건 추가 ㄴ ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수 있으나, 검색 조건 목적인 경우는 WHERE 절을 사용할 것을 권고. 다만, 아우터 조인에서 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건의 경우는 ON 절에 표기되어야 함
ON 조건절 예제 ㄴ 같은 이름의 컬럼이라면 USING 조건절 사용가능
다중 테이블 조인
CROSS JOIN
CROSS JOIN이란? - 테이블 간 조인 조건이 없는 경우 생길 수 있는 모든 데이터의 조합 - 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생함 - WHERE 조건 추가 가능하지만 INNER JOIN과 같은 결과를 얻으므로 권고하지 않음
사용예시
- LAST_NAME은 중복없이 107개가 출력됬고, DEPARTMENT_NAME은 중복없이 27개가 출력됨
- 이클립스에서는 최대 500개까지 보여주는 듯함
- 원래 기대했던 결과인 107*27 = 2889개가 출력됨
OUTER JOIN
OUTER JOIN이란? - 대부분의 관계형 DBMS에서 지원하므로 높은 호환성을 가짐 - OUTER JOIN은 조인조건을 FROM 절에서 정의하므로, USING 조건절이나 ON 조건절 필수적으로 사용 - ANSI/ISO SQL 표준 방식의 OUTER JOIN 문법을 사용하면 전통적인 방식의 조인 문법의 문제점 해결가능
조인 조건과 WHERE 절 검색 조건의 구분이 불명확
IN이나 OR 연산자 사용시 에러
(+) 표시가 누락된 조인 및 검색 조건 존재 시, OUTER JOIN이 아닌 INNER JOIN으로 수행
FULL OUTER JOIN 미지원
종류
LEFT OUTER JOIN
개념 - OUTER를 생략하여 LEFT JOIN으로 사용해도 됨 - 좌측 테이블에 해당하는 데이터를 먼저 읽고, 우측 테이블에서 조인 대상 데이터를 읽음 => Table A와 B가 있을 때(Table A가 기준), A와 B를 비교해서 B의 조인 컬럼에 조인 조건을 만족하는 값이 있으면 해당 열의 데이터를 가져오고, 조인 조건을 만족하는 값이 없는 경우에는 B 테이블에서 가져오는 컬럼들을 NULL로 채움
사용예시
- DEPARTMENT_NAME이 NULL인 행의 경우, INNER JOIN(오른쪽)과 달리 LEFT OUTER JOIN(왼쪽)은 NULL 값으로 채워서 출력함
RIGHT OUTER JOIN
의미 - OUTER를 생략하여 RIGHT JOIN으로 사용해도 됨 - 조인 수행 시 LEFT JOIN과 반대로 우측 테이블이 기준이 돼 결과를 생성함 => Table A와 B가 있을 때(Table B가 기준), A와 B를 비교해서 A의 조인 컬럼에 조인 조건을 만족하는 값이 있으면 해당 열의 데이터를 가져오고, 조인 조건을 만족하는 값이 없는 경우에는 A 테이블에서 가져오는 컬럼들을 NULL로 채움
사용예시
- EMPLOYEE_ID가 NULL인 행의 경우, 같이 출력함
FULL OUTER JOIN
의미 - OUTER를 생략하여 FULL JOIN으로 사용해도 됨 - 조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어 결과를 생성함 => Table A와 B가 있을 때(Table A와 B가 기준), RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일함 - 단, 조인에 성공한 행들은 한번만 표시함
사용예시
INNER vs OUTER vs CROSS JOIN 비교
INNER JOIN
SELECT X.KEY1, Y.KEY2
FROM TABLE_A X INNER JOIN TABLE_B Y
ON (K.KEY1 = Y.KEY2)
=> 양쪽 테이블에 모두 존재하는 키 값인 B-B, C-C 총 2개 출력됨
LEFT OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TABLE_A X LEFT OUTER JOIN TABLE_B Y
ON (K.KEY1 = Y.KEY2)
=> TABLE_1이 기준이므로, TABLE_1에서 NULL이 아닌 B-B, C-C, D-NULL, E-NULL 총 4개 출력됨
RIGHT OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TABLE_A X RIGHT OUTER JOIN TABLE_B Y
ON (K.KEY1 = Y.KEY2)
=> TABLE_2가 기준이므로, TABLE_2에서 NULL이 아닌 NULL-A, B-B, C-C 총 3개 출력됨
FULL OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TABLE_A X FULL OUTER JOIN TABLE_B Y
ON (K.KEY1 = Y.KEY2)
=> 양쪽 테이블이 기준이므로, NULL-A, B-B, C-C, D-NULL, E-NULL 총 5개 출력됨
CROSS JOIN
SELECT X.KEY1, Y.KEY2
FROM TABLE_A X CROSS JOIN TABLE_B Y
=> 조인 가능한 모든 경우의 수를 표시하지만, OUTER JOIN은 제외. => TABLE_A의 데이터 수 4개, TABLE_B의 데이터 수 3개, 총 4 * 3 = 12건이 출력됨 B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C인 12개 출력
