SQL 기본
데이터베이스(Database)와 DBMS(Database Management System)
- 데이터베이스 : 데이터의 집합. 꼭 형식을 갖추지 않아도 엑셀 파일을 모아 둔다면 그것 또한 데이터베이스.
- DBMS : 데이터를 효과적으로 관리하기 위한 시스템. 개인이 파일을 여러 개 묶어서 폴더에 보관하면 데이터를 찾고 관리하는데 많은 비용이 발생. 이를 보다 시스템 적으로 작동하게 만든 시스템을 DBMS라고 한다. (Oracle, MySQL 등)
관계형 데이터베이스 구성 요소
- 계정 : 데이터의 접근 제한을 위한 여러 업무/시스템별 계정이 존재한다.
- 테이블 : DBMS의 DB 안에서 데이터가 저장되는 형식.
- 스키마 : 테이블이 어떠한 구성으로 되어있는지, 어떠한 정보를 가지고 있는지에 대한 기본적인 구조를 정의.
테이블
정의
- 엑셀에서의 워크시트처럼 행(로우)와 열(컬럼)을 갖는 2차원 구조로 구성. 데이터를 입력하여 저장하는 최소단위.
- 컬럼은 속성이라고도 불린다. (모델링 단계마다 부르는 용어가 다르다.)
특징
- 하나의 테이블은 반드시 하나의 유저(계정) 소유.
- 테이블 간 관계는 일대일(1:1), 일대다(1:N), 다대다(N:M)의 관계를 가질 수 있다.
- 테이블명은 중복될 수 없지만, 소유자가 다른 경우 같은 이름 생성 가능.
- 테이블은 행 단위로 데이터가 입력, 삭제되며 수정은 값의 단위로 가능. (특정 데이터를 삭제할 수 없다. 대신 수정은 가능하므로, 보통 NULL값을 넣는다.)
SQL (Structured Query Language)
관계형 데이터베이스에서 데이터 조회 및 조작, DBMS 시스템 관리 기능을 명령하는 언어. 데이터 정의(DDL), 데이터 조작(DML), 데이터 제어 언어(DCL) 등으로 구분. SQL 문법은 대, 소문자를 구분하지 않는다.
관계형 데이터베이스 특징
- 데이터의 분류, 정렬, 탐색 속도가 빠르다.
- 신뢰성이 높고, 데이터의 무결성 보장한다.
- 기존의 작성된 스키마를 수정하기 어렵다.
- 데이터베이스의 부하를 분석하는 것이 어렵다.
데이터의 무결성(integrity)
데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하는 것. 데이터베이스에 저장된 값과 그것이 표현하는 현실의 비즈니스 모델의 값이 일치하는 정확성을 의미. 데이터 무결성을 유지하는 것이 데이터베이스 관리시스템에 중요한 기능.
데이터 무결성 종류
- 개체 무결성 : 테이블의 기본키를 구성하는 컬럼(속성)은 NULL 값이나 중복값을 가질 수 없다.
- 참조 무결성 : 외래키 값은 NULL 이거나 참조 테이블의 기본키 값과 동일해야 한다. (외래키란 참조 테이블의 기본키에 정의된 데이터만 허용되는 구조이므로)
- 도메인 무결성 : 주어진 속성 값이 정의된 도메인에 속한 값이어야 한다.
- NULL 무결성 : 특정 속성에 대해 NULL 을 허용하지 않는 특징.
- 고유 무결성 : 특정 속성에 대해 값이 중복되지 않는 특징.
- 키 무결성 : 하나의 릴레이션(관계)에는 적어도 하나의 키가 존재. (테이블이 서로 관계를 가질 경우 반드시 하나 이상의 조인키를 가진다.)
도메인 : 각 컬럼(속성)이 갖는 범위.
릴레이션 : 테이블간 관계. (개념 모델링에서 사용.)
튜플 : 하나의 행을 의미.
키 : 식별자.
ERD (Entity Relationship Diagram)
ERD 란, 테이블 간 서로의 상관 관계를 그림으로 표현한 것. ERD의 구성요소에는 엔터티(Entity) , 관계(Relationship) , 속성(Attribute) 이 있다. → 현실 세계의 데이터는 이 3가지의 구성으로 모두 표현 가능.
SQL 종류
SQL은 그 기능에 따라 다음과 같이 구분한다.
- DDL (Data Definition Language) : CREATE, ALTER, DROP, TRUNCATE
- DML (Data Manipulation Language) : INSERT, DELETE, UPDATE, MERGE
- DCL (Data Control Language) : GRANT, REVOKE
- TCL (Transaction Control Language) : COMMIT, ROLLBACK
- DQL (Data Query Language) : SELECT
사실 SELECT 문은 따로 SQL 종류 중 어디에도 속하지 않아서 SELECT 문을 위한 DQL이 등장했다.
SELECT 문 구조
SELECT 문은 다음과 같이 6개 절로 구성. 각 절의 순서대로 작성해야 한다. (GROUP BY 와 HAVING 은 서로 바꿀 수 있지만 보통 사용하지 않는다.) SELECT 문의 내부 파싱(문법적 해석) 순서는 나열된 순서와는 다르다. FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY 순서대로 실행.
SELECT * | 컬럼명 | 표현식
FROM 테이블 명 | 뷰명
WHERE 조회 조건
GROUP BY 그룹 컬럼명
HAVING 그룹 필터링 조건
ORDER BY 정렬 컬럼명SELECT 절
SELECT 문장을 사용하여 불러올 컬럼명, 연산 결과를 작성하는 절.
* 을 사용하여 테이블 내 전체 컬럼명을 불러올 수 있다.
원하는 컬럼을 , 로 나열하여 작성 가능. (순서대로 출력.)
표현식이란 원래의 컬럼명을 제외한 모든 표현 가능한 대상(연산식, 기존 컬럼의 함수 변형식 포함.)
특징
- SELECT절에서 표시할 대상 컬럼에 Alias(별칭) 지정 가능.
- 대소문자를 구분하지 않아도 인식한다.
컬럼은 없지만 기존 컬럼의 값을 사용하여 연산결과를 SELECT 절에서 정의하여 출력할 수 있다. 이런 표현 가능한 모든 수식을 표현식이라고 한다. (함수식, 연산식 등)
컬럼 Alias(별칭)
컬럼명 대신 출력할 임시 이름 지정. (SELECT절에서만 정의 가능, 원본 컬럼명은 변경되지 않는다.) 컬럼명 뒤에 AS와 함께 컬럼 별칭 전달. (AS 생략 가능)
Alias 특징 및 주의사항
- SELECT 문보다 늦게 수행되는 ORDER BY 절에서만 컬럼 별칭 사용 가능. (그 외 절에서 사용하면 에러 발생!)
- 한글 사용 가능.
- 이미 존재하는 예약어는 별칭으로 사용 불가. ex. AVG, COUNT, DECODE, SELECT, FROM 등
- 다음의 경우 별칭의 반드시 쌍따옴표 전달 필요
1. 별칭에 공백을 포함하는 경우.
2. 별칭에 특수문자를 포함하는 경우. ("_" 제외)
3. 별칭 그대로 전달할 경우. (입력할 대소를 그대로 출력하고자 할 때)
FROM 절
- 데이터를 불러올 테이블명 또는 뷰명 전달.
- 테이블 여러 개 전달 가능(컴마로 구분) → 조인 조건 없이 테이블명만 나열 시 카타시안 곱 발생 주의.
- 테이블 별칭 선언 가능. (ORACLE 은 AS 사용 불가, SQL Server 는 사용/생략 가능)
테이블 별칭 선언 시 컬럼 구분자는 테이블 별칭으로만 전달 (테이블명으로 사용 시 에러 발생!)
ORACLE 에서는 FROM 절 생략 불가. (의미상 필요 없는 경우 DUAL 테이블 선언.)
ORACLE 23c 버전부터 생략 가능
SQL Server 에서는 FROM 절 필요 없을 경우 생략 가능. (오늘 날짜 조회 시)
뷰 : 테이블과 동일하게 데이터를 조회할 수 있는 객체이지만 테이블처럼 실제 데이터가 저장된 것이 아닌, SELECT문 결과에 이름을 붙여 그 이름만으로 조회가 가능하도록 한 기능.
함수 정의
- input value 가 있을 경우 그에 맞는 output value 를 출력해주는 객체.
- input value 와 output value 의 관계를 정의한 객체.
- FROM 절을 제외한 모든 절에서 사용 가능.
함수 기능
- 기본적인 쿼리문을 더욱 강력하게 해준다.
- 데이터의 계산을 수행.
- 개별 데이터의 항목을 수정.
- 표시할 날짜 및 숫자 형식을 지정.
- 열 데이터의 유형(Data type)을 변환.
함수의 종류 (입력값의 수에 따라)
- 단일행 함수와 복수행 함수로 구분.
- 단일행 함수 : input 과 output 관계가 1 : 1
- 복수행 함수 : 여러 건의 데이터를 동시에 입력받아서 하나의 요약값을 리턴. (그룹함수 또는 집계함수라고도 한다.)
입/출력값의 타입에 따른 함수 분류
- 문자형 함수: 문자열 결합, 추출, 삭제 등을 수행. 단일행 함수 형태. output은 대부분 문자값.(LENGTH, INSTR 제외.)
더보기
문자함수 종류
SQL Server
- SUBSTR → SUBSTRING
- LENGTH → LEN
- INSTR → CHARINDEX
- 숫자형 함수: 숫자를 입력하면 숫자 값을 반환. 단일행 함수 형태의 숫자함수. ORACLE 과 SQL Server 함수 거의 동일.
더보기숫자함수 종류
- 날짜형 함수: 날짜 연산과 관련된 함수. ORACLE 과 SQL Server 함수 거의 다르다.
- 변환함수: 값의 데이터 타입을 변환. 문자를 숫자로, 숫자를 문자로, 날짜를 문자로 변경.
- 그룹함수: 다중행 함수. 여러 값이 input으로 들어가서 하나의 요약된 값으로 반환. GROUP BY와 함께 자주 사용된다.
- 일반함수: Null 치환 함수 등과 같은 기타함수.
WHERE 절
- 테이블의 데이터 중 원하는 조건에 맞는 데이터만 조회하고 싶을 경우 사용. (엑셀의 필터기능과 유사)
- 여러 조건 동시 전달 가능. (AND 와 OR 로 조건 연결)
- NULL 조회 시 IS NULL / IS NOT NULL 연산자 사용. (= 연산자로 조회 불가)
- 연산자를 사용하여 다양한 표현이 가능하다.
- 조건 전달 시 비교 대상의 데이터 타입이 일치하는 것이 좋다. ex. EMP 테이블의 부서번호 컬럼의 데이터 타입은 숫자인데 문자 상수로 비교 시 성능 문제가 발생할 수 있다.
= : 같은 조건을 검색
!= , <> : 같지 않은 조건을 검색
> : 큰 조건을 검색
>= : 크거나 같은 조건을 검색
< : 작은 조건을 검색
<= : 작거나 같은 조건을 검색
BETWWEN A AND B : A와 B 사이에 있는 범위 값을 모두 검색
IN(A, B, C) : A 이거나 B 이거나 C 인 조건을 검색
LIKE : 특정 패턴을 가지고 있는 조건 검색
IS NULL / IS NOT NULL : NULL 값을 검색 / NULL 이 아닌 값을 검색
A AND B : A 조건과 B 조건을 모두 만족하는 값만 검색
A OR B : A 조건이나 B조건 중 한 가지라도 만족하는 값을 검색
NOT A : A가 아닌 모드 조건을 검색
IN 연산자
포함연산자로 여러 상수와 일치하는 조건 전달 시 사용. 상수를 괄호로 묶어서 동시에 전달. (문자와 날짜 상수의 경우 반드시 홑따옴표와 함께)
BETWEEN A AND B 연산자
A보다 크거나 같고 B보다 작거나 같은 조건을 만족. A와 B에는 범위로 묶을 상수값 전달. (문자, 숫자, 날짜 모두 전달 가능) 반드시 A가 B보다 작아야 한다. (반대로 작성 시 아무 것도 출력되지 않는다.)
LIKE 연산자
정확하게 일치하지 않아도 되는 패턴 조건 전달 시 사용. %, _ 와 함께 사용.
% : 자리수 제한 없는 모든이라는 의미
_ : _ 하나 당 한 자리수를 의미하며 모든 값을 표현
NOT 연산자
- 조건 결과의 반대집합. 즉, 여집합을 출력하는 연산자.
- NOT 뒤에 오는 연산 결과의 반대 집합 출력.
- 주로 NOT IN, NOT BETWEEN A AND B, NOT LIKE, NOT NULL 로 사용한다.
GROUP BY 절
- 각 행을 특정 조건에 따라 그룹으로 분리하여 계산하도록 하는 구문식.
- GROUP BY 절에 그룹을 지정할 컬럼을 전달. (여러 개 전달 가능)
- 만약 그룹 연산에서 제외할 대상이 있다면 미리 WHERE 절에서 해당 행을 제외. (WHERE 절이 GROUP BY 절보다 먼저 수행되므로)
- 그룹에 대한 조건은 WHERE 절에서 사용할 수 없다.
- SELECT 절에 집계 함수를 사용하여 그룹연산 결과 표현.
- GROUP BY 절을 사용하면 데이터가 요약되므로 요약되기 전 데이터와 함께 출력할 수 없다.
HAVING 절
- 그룹 함수 결과를 조건으로 사용할 때 사용하는 절.
- WHERE 절을 사용하여 그룹을 제한할 수 없으므로 HAVING 절에 전달.
- HAVING 절이 GROUP BY 절 앞에 올 수는 있지만 뒤에 쓰는 것을 권장.
- 내부적 연산 순서가 SELECT 절보다 먼저이므로 SELECT 절에서 선언된 Alias 사용 불가.
ORDER BY 절
- 데이터는 입력된 순서대로 출력되나, 출력되는 행의 순서를 사용자가 변경하고자 할 때 ORDER BY 사용.
- ORDER BY 뒤에 명시된 컬럼 순서대로 정렬
- 정렬 순서를 오름차순(ASC), 내림차순(DESC) 으로 전달. (생략 시 오름차순 정렬)
- 유일하게 SELECT 절에 정의된 컬럼 별칭 사용 가능.
- SELECT 절에 선언된 순서대로의 숫자 전달 가능. (컬럼명과 숫자 혼합 사용 가능)
정렬 순서 (오름차순)
- 한글 : 가, 나, 다, 라...
- 영어 : A, B, C, D....
- 숫자 : 1, 2, 3, 4....
- 날짜 : 과거 날짜부터 시작해서 최근 날짜.
복합 정렬
먼저 정렬한 값의 동일한 결과가 있을 경우 추가적으로 정렬 가능. → 1차 정렬한 값이 같은 경우 그 값 안에서 2차 정렬 컬럼값의 정렬이 일어난다.
NULL의 정렬
NULL을 포함한 값의 정렬 시 ORACLE 은 기본적으로 NULL을 마지막에 배치.(SQL Server는 처음에 배치) ORACLE 은 ORDER BY 절에 NULLS LAST | NULLS FIRST 을 명시하여 NULL 정렬 순서 변경 가능.
JOIN(조인)
- 여러 테이블의 데이터를 사용하여 동시 출력하거나 참조할 경우 사용.
- FROM절에 조인할 테이블 나열.
- ORACLE 표준은 테이블 나열 순서 중요하지 않다. ANSI 표준은 OUTER JOIN 시 순서 중요.
- WHERE 절에서 조인 조건을 작성. (ORACLE 표준)
- 동일한 열 이름이 여러 테이블에 존재할 경우 열 이름 앞에 테이블 이름이나 테이블 Alias 붙인다.
- N개의 테이블을 조인하려면 최소 N-1개의 조인 조건 필요.
- ORACLE 표준과 ANSI표준이 서로 다르다.
JOIN(조인) 종류
- 조건의 형태에 따라
1) EQUI JOIN(등가 JOIN) : JOIN 조건이 동등 조건인 경우.
2) NON EQUI JOIN : JOIN 조건이 동등 조건이 아닌 경우. - 조인 결과에 따라
1) INNER JOIN : JOIN 조건에 성립하는 데이터만 출력하는 경우.
2) OUTER JOIN : JOIN 조건에 성립하지 않는 데이터도 출력하는 경우. (LEFT/RIGHT/FULL OUTER JOIN으로 나뉜다.) - NATURAL JOIN : 조인조건 생략 시 두 테이블에 같은 이름으로 자연 연결되는 조인.
- CROSS JOIN :조인조건 생략 시 두 테이블의 발생 가능한 모든 행을 출력하는 조인.
- SELF JOIN : 하나의 테이블을 두 번 이상 참조하여 연결하는 조인.
EQUI JOIN (등가 JOIN)
- 조인 조건이 = (equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 얻는 조인 방법.
- SQL 명령문에서 가장 많이 사용하는 조인 방법.
- FROM절에 조인하고자 하는 테이블을 모두 명시.
- FROM절에 명시하는 테이블은 테이블 별칭(Alias) 사용 가능.
- WHERE절에 두 테이블의 공통 컬럼에 대한 조인 조건을 나열.
NON EQUI JOIN
테이블을 연결짓는 조인 컬럼에 대한 비교 조건이 < , BETWEEN A AND B 와 같이 = 조건이 아닌 연산자를 사용하는 경우의 조인 조건.
세 테이블 이상의 조인
관계를 잘 파악하여 모든 테이블이 연결되도록 조인 조건 명시. N개 테이블의 경우 최소 N-1개의 조인 조건 필요.
SELF JOIN
- 한 테이블 내 각 행끼리 관계를 갖는 경우 조인 기법.
- 한 테이블을 참조할 때마다(필요할 때마다) 명시해야 한다.
- 테이블명이 중복되므로 반드시 테이블 별칭 사용.
표준 조인
ANSI 표준으로 작성되는 INNER JOIN, CROSS JOIN, NATURAL JOIN, OUTER JOIN을 말한다.
INNER JOIN
- 내부 조인이라고 하며, 조인 조건이 일치하는 행만 추출. (ORACLE 조인 기본)
- ANSI 표준의 경우 FROM절에 INNER JOIN 혹은 줄여서 JOIN을 명시.
- ANSI 표준의 경우 USING이나 ON 조건절을 필수적으로 사용.
ON 조건절
- 조인할 양 컬럼의 컬럼명이 서로 다르더라도 사용 가능.
- ON 조건의 괄호는 옵션. (생략가능).
- 컬럼명이 같을 경우 테이블 이름이나 별칭을 사용하여 명확하게 지정. (테이블 출처 명확히)
- ON 조건절에서 조인조건 명시, WHERE 절에서는 일반조건 명시. (WHERE 절과 ON 절의 쓰임에 따라)
USING 조건절
- 조인할 컬럼명이 같을 경우 사용.
- Alias나 테이블 이름 같은 접두사 붙이기 불가.
- 괄호 필수.
NATURAL JOIN
- 두 테이블 간의 동일한 이름을 가지는 모든 컬럼들에 대해 EQUI JOIN을 수행.
- USING, ON, WHERE 절에서 조건 정의 불가.
- JOIN에 사용된 컬럼들은 데이터 유형이 동일해야 하며 접두사를 사용불가.
CROSS JOIN
테이블 간
JOIN 조건이 없는 경우 생성 가능한 모든 데이터들의 조합.
(Cartesian product(카타시안곱) 출력) 양쪽 테이블 행 수를 곱한 수의 데이터 조합 발생. (m*n)
OUTER JOIN
- INNER JOIN과 대비되는 조인 방식.
- JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용.
- 두 테이블 중 한쪽에 NULL을 가지면 EQUI JOIN시 출력되지 않는다. → 이를 출력 시 OUTER JOIN 사용.
- 테이블 기준 방향에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 구분.
- OUTER 생략 가능. (LEFT OUTER → JOIN LEFT JOIN)
OUTER JOIN 종류
- LEFT OUTER JOIN: FROM 절에 나열된 왼쪽 테이블에 해당하는 데이터를 읽은 후, 우측 테이블에서 JOIN 대상을 읽어온다. 즉, 왼쪽 테이블이 기준이 되어 오른쪽 테이블 데이터를 채우는 방식. 우측 값에서 같은 값이 없는 경우 NULL 값으로 출력.
- RIGHT OUTER JOIN: LEFT OUTER JOIN의 반대. 즉, 오른쪽 테이블 기준으로 왼쪽 테이블 데이터를 채우는 방식. FROM절에 테이블 순서를 변경하면 LEFT OUTER JOIN으로도 수행 가능.
- FULL OUTER JOIN: 두 테이블 전체 기준으로 결과를 생성하여 중복 데이터는 삭제 후 리턴. LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과의 UNION 연산 리턴과 동일. ORACLE 표준에는 없다. (지원하지 않는다.)
