DB : 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것을 의미한다.
DBMS : 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW
SQL : 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어
•
DDL(Data Definition Language) : 데이터 정의어
•
DML(Data Manipulation Language) : 데이터 조작언어
•
DCL(Data Control Language) : 데이터 제어어
SQL 구문 실행순서
•
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
테이블 : DB 기본 단위, 데이터를 저장하는 객체
•
가로 = 행 = 로우 = 튜플 = 인스턴스
•
세로 = 열 = 컬럼
정규화 : 데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 이상현상을
방지하기 위해 중복 제거.
기본키 : 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼.
외부키 : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼.
SELECT 구문
•
컬럼
•
상수
•
연산자
•
함수
DISTINCT
•
중복된 값을 제거해 출력.
ORDER BY
•
기본은 오름차순.
•
내림차순 출력하기 위해서는 DESC 키워드 사용.
WHERE 구문
•
조건에 맞는 행(row) 검색.
연산자
•
=, <>, >, >=, <, <=, BETWEEN, IN, LIKE, IS NULL
AND, OR, NOT
•
복합 조건을 위한 연산자.
GROUP BY 구문
•
그룹화된 결과 출력.
HAVING 구문
•
그룹화된 결과에 조건을 부여.
JOIN
•
두 개 이상의 테이블을 연결하여 데이터 검색.
내부조인
•
일치하는 것만 출력.
외부조인
•
일치하지 않는 것도 출력.
서브쿼리
•
쿼리 안에 쿼리 사용.
•
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY 사용 가능.
EXISTS, NOT EXISTS
•
서브쿼리 안의 결과가 존재하는지 검사.
ANY, ALL
•
서브쿼리 결과 중 최소, 최대값과 전체 조건을 만족하는지 검사.
UNION
•
두 개 이상의 SELECT 결과를 합침.
INTERSECT
•
두 개 이상의 SELECT 결과 중 공통된 것을 출력.
MINUS
•
SELECT 결과 중 첫 번째 쿼리의 결과에서 두 번째 쿼리의 결과를 제외한 것을 출력.
(2과목에서는 노션 AI를 사용해봤는데 여기까지 한번에 작성해주었다. ㄷㄷ)
DDL(Data Definition Language) - 데이터 구조를 정의
•
CREATE : 테이블 생성.
•
ALTER : 테이블 구조 변경.
•
ADD : 추가
•
DROP : 삭제
•
MODIFY : 수정
DML(Data Manipulation Language) - 데이터 자체를 수정
•
SELECT : 데이터 선택
•
INSERT : 테이블에 데이터 추가.
•
UPDATE : 테이블 내 데이터 변경.
•
DELETE : 테이블 내 데이터 삭제.
DCL(Data Control Language) - 데이터에 대한 접근 권한
•
GRANT : 권한 부여.
•
REVOKE : 권한 취소.
→
GRANT CREATE TABLE TO PJS;
REVOKE CREATE TABLE FROM PJS;
TCL
트랜잭션 : 밀접히 관련되어 분리될 수 없는 1개 이상의 DB 조작. 논리적 연산단위
COMMIT : 올바르게 반영된 데이터를 DB에 반영
ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림. COMMIT 되지 않은 모든 트랜잭션을 롤백함.
SAVEPOINT : 저장 지점
트랜잭션의 특성
1.
원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함.
2.
일관성 : DB 트랜잭션 실행 전 내용이 잘못 되지 않으면 실행 후도 잘못 되지 않아야 함.
3.
고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
4.
지속성 : 트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장된다.
연산자의 종류
•
BETWEEN a AND b : a와 b 값 사이에 있으면 됨.
•
IN (list) : 리스트에 있는 값중 어느 하나라도 일치.
•
IS NULL : NULL 값인 경우. (Oracle은 VARCHAR2 빈 문자열을 NULL로 판단)
•
IS NOT NULL : NULL 값이 아닌 경우.
•
NOT IN (list) : list의 값과 일치하지 않는다.
•
LIKE ‘비교문자열’ : 비교문자열과 형태가 일치.
CONCAT(‘RDBMS’,‘ SQL’) → ‘RDBMS SQL’
SUBSTR(‘SQL Expert’,5,3) → ‘Exp’
LTRIM(‘xxxYYZZxYZ’,‘x’) → ‘YYZZxYZ’
TRIM(‘x’ FROM ‘xxYYZZxYZxx’) → ‘YYZZxYZ’
CEIL(숫자) : 크거나 같은 최소 정수 리
FLOOR(숫자) : 작거나 같은 최대 정수 리턴
ROUND(38.5235,3) -> 38.524
TRUNC(38.5235,3) -> 38.523
NULL 관련 함수 (NULL은 0 또는 공백 아님)
•
NVL(식1, 식2) : 식1의 값이 NULL이면 식2 출력. 공집합을 바꿔주진 않음.
•
NULLIF(식1, 식2) : 식1이 식2와 같으면 NULL을, 아니면 식1을 출력.
•
COALESCE(식1, 식2) : NULL이 아닌 최초의 표현식, 모두 NULL이면 NULL반환.
e.g. COALESCE(NULL, NULL, ‘abc’) → ‘abc’
다중행 집계 함수
1.
여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.
2.
GROUP BY 절은 행들을 소그룹화 한다.
3.
SELECT, HAVING, ORDER BY 절에 사용 가능.
•
ALL : Default 옵션. 생략 가능
•
DISTINCT : 같은 값을 하나의 데이터로 간주 옵션 (중복 제거)
COUNT(*) : NULL 포함 행의 수
COUNT( ) : NULL 표현식 제외 행의 수
SUM, AVG : NULL 제외 합계, 평균 연산
STDDEV : 표준 편차
VARIAN : 분산
MAX, MIN : 최대값, 최소값
GROUP BY, HAVING 절의 특징
1.
GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
2.
집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
3.
GROUP BY 절에서는 ALIAS 사용 불가.
4.
WHERE 집계 함수는 절에 올 수 없다.
5.
HAVING 절에는 집계함수를 이용하여 조건 표시o
6.
HAVING 절은 일반적으로 GROUP BY 뒤에 위치.
ORDER BY 특징
1.
SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬하여 출력하는데 사용한다.
2.
ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능하다.
3.
DEFAULT 값으로 오름차순(ASC)이 적용되며 DESC 옵션을 통해 내림차순으로 정렬이 가능하다.
4.
SQL 문장의 제일 마지막에 위치한다.
5.
SELECT 절에서 정의하지 않은 칼럼 사용 가능
Oracle 에서는 NULL을 가장 큰 값으로 취급하며,
SQL Server 에서는 NULL을 가장 작은 값으로 취급한다.
SQL Server WITH TIES
SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC;
→ 급여가 높은 2명을 내림차순으로 출력하는데 같은 ,
급여를 받는 사원은 같이 출력한다
JOIN : 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
일반적으로 행들은 PK FK 나 값의 연관에 의해 JOIN이 성립된다. 어떤 경우에는 PK, FK 관계가
없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.
5가지 테이블을 JOIN 하기 위해서는 최소 4번의 JOIN 과정이 필요하다. (N-1)
EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로정확하게 일치하는 경우에 사용. 대부분 PK, FK의 관계를 기반으로 한다.
NON EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용.
‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용
집합 연산자 : 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용. SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용 가능.
일반 집합 연산자
1.
UNION : 합집합(중복 행 1개로) 정렬
2.
UNION ALL : 합집합(중복 행도 표시) 정렬 X
3.
INTERSECT : 교집합(중복 행 1개로)
4.
MINUS : 차집합(중복 행 1개로)
5.
CROSS JOIN : 곱집합(PRODUCT)
ALIAS , 는 처음 테이블 정렬은 마지막 테이블 기준
CROSS JOIN = 카티시안 곱
양쪽 집합의 M*N건의 데이터 조합이 발생한다.
계층형 질의 : 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용.
START WITH : 계층 구조 전개의 시작 위치 지정
CONNECT BY : 다음에 전개될 자식 데이터 지정
PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터 (부모 -> 자식)방향으로 전개하는 순방향 전개를 한다. 반대는 역방향 전개
NOCYCLE : 동일한 데이터가 전개되지 않음
ORDER SIBLINGS BY : 형제 노드간의 정렬 수행
WHERE : 모든 전개를 수행한 후에 지정된 조건을
만족하는 데이터만 추출한다. (필터링)
서브 쿼리
단일 행 비교 연산자 : =,<,>,<> 등
다중 행 비교 연산자 : IN, ALL, ANY, SOME 등
스칼라 서브쿼리 : 한 행, 한 컬럼만을 반환하는 서브쿼리
반환 데이터에 따른 서브쿼리 종류
•
단일행 서브쿼리 : 실행결과 1건 이하
•
다중행 서브쿼리 : 실행결과 여러 건
•
다중컬럼 서브쿼리 : 실행결과 컬럼 여러 개
인라인 뷰 : FROM 절에서 사용되는 서브쿼리. ORDER BY를 사용 가능
뷰 : 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. 가상 테이블이라고도 함.
실행 시점에 SQL 재작성하여 수행됨.
뷰 사용 장점
1.
독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
2.
편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
3.
보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용.
ROLLUP : Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level 의 Subtotal 이 생성된다. 인수 순서에 주의.
GROUPING : NULL 값이면 1, 아니면 0
CUBE : 결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하 심함.
2^N
CUBE(A, B) = GROUPING SETS(A, B, (A,B ), ())
GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있다 다양한 소계 집합 생성 가능.
순위 관련 함수
•
RANK : 동일한 값에 대해서는 동일한 순위를 부여
(1,2,2,4)
•
DENSE_RANK : 동일한 순위를 하나의 등수로 간주
(1,2,2,3)
•
ROW_NUMBER : 동일한 값이라도 고유한 순위 부여
(1,2,3,4)