📌 DB
- 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것
✅ DBMS
- 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 필요한 데이터를 복구하기 위한 강력한 기능의 SW
⭐ SQL
- 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어
정조제
✅ DDL(데이터 정의어)
- CREATE, DROP, ALTER, RENAME
✅ DML(데이터 조작어)
- SELECT, INSERT, UPDATE, DELETE
✅ DCL(데이터 제어어)
- GRANT, REVOKE
✅ TCL
- COMMIT, REVOKE
⭐ 테이블
- DB의 기본 단위
- 데이터를 저장하는 객체
- 가로 = 행 = 로우 = 튜플 = 인스턴스
- 세로 = 열 = 컬럼
⭐ 정규화
- 데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 이상현상을 방지하기 위해 중복을 제거
⭐ 기본키
- 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 컬럼
⭐ 외부키
- 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 컬럼
📌 DDL
⭐ 데이터 유형
- CHAR(s) : 고정길이 문자열 정보, 최대길이만큼 공간채움
ex) 'AA' = 'AA ' - VARCHAR2(s) : 가변길이 문자열 정보, 할당된 변수값의 바이트만 적용
ex) 'AA' != 'AA ' - NUMBER : 정수, 실수 등 숫자정보
- DATE : 날짜와 시각 정보
CREATE TABLE 테이블명(
);⭐ 테이블 생성 주의사항
- 객체를 의미할 수 있는 이름 사용
- 단수형 사용
- 테이블명은 다른 테이블명과 중복되면 안됨
- 한 테이블 내의 컬럼명은 중복될 수 없음
- 각 컬럼들을 ( )로 묶어 지정
- 각 컬럼들은 ','로 구분되고 ';'로 끝남
- 컬럼 뒤에 데이터 유형은 꼭 지정되야함
- 테이블명과 컬럼명은 반드시 문자로 시작해야함
- 예약어 사용불가
- A-Z, a-z, 0-9, _, $, #만 사용가능
⭐ 제약조건
- 데이터의 무결성 유지
- PRIMARY KEY(기본키) : 중복x, NOT NULL, 테이블당 1개
- UNIQUE KEY(고유키) : 중복 x, NULL 가능
- NOT NULL
- CHECK : 입력 값 범위 제한
- FOREIGN KEY(외래키) : NULL 가능, 속성중복 가능, 테이블당 여러개 생성 가능, 테이블 생성시 설정 가능, 참조무결성 제약
⭐ DDL SQL
-- 테이블 생성
CREATE TABLE PLAYER(
PLAYER_ID CHAR(7) NOT NULL,
PLAYER_NAME VARCHAR2(20) NOT NULL
);
-- 테이블 구조 변경(컬럼 추가)
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;
-- 테이블 구조 변경(컬럼 삭제)
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
-- 테이블 구조 변경(컬럼 수정)
-- 여러 컬럼 동시수정 X, 한번에 한개의 컬럼만 수정
ALTER TABLE 테이블명 MODIFY 컬럼명 제약조건;
-- 제약조건 삭제
DROP CONSTRAINT 제약조건명;
-- 제약조건 추가
ADD CONSTRAINT 제약조건명 조건(컬럼명);
-- 테이블 이름 변경
RENAME 이전테이블명 TO 이후테이블명;
-- 컬럼 이름 변경
RENAME COLUMN 이전컬럼명 TO 이후컬럼명;
-- 테이블 삭제
DROP TABLE 테이블명;
-- 테이블 데이터 삭제
TRUNCATE TABLE 테이블명;⭐ DELETE, MODIFY ACTION
- CASCADE : Master 삭제시 Child 같이 삭제
- SET NULL : Master 삭제시 Child 해당 필드 NULL
- SET DEFAULT : Master 삭제시 Child 해당 필드 DEFAULT값 설정
- RESTRICT : Child테이블에 PK값이 없는 경우만 Master 삭제가능
- NO ACTION : 참조무결성을 위반하는 삭제/수정 액션 취하지않음
⭐ INSERT ACTION
- AUTOMATIC : Master테이블에 PK없는 경우 Master PK생성 후 Child 입력
- SET NULL : Master테이블에 PK없는 경우 Child를 NULL로 처리
- SET DEFAULT : Master테이블에 PK없는 경우 Child를 DEFAULT값으로 입력
- DEPENDENT : Master테이블에 PK가 존재할때만 Child입력 허용
- NO ACTION : 참조무결성을 위반하는 입력 액션 취하지않음
✅ AUTO COMMIT
- ORACLE : DDL 후 AUTO COMMIT
- SQL Server : AUTO COMMIT X
📌 DML
⭐ DML SQL
-- 데이터 삽입
INSERT INTO 테이블명(컬럼1, 컬럼2) VALUES (값1, 값2);
-- 데이터 변경
UPDATE 테이블명 SET 컬럼명 = 값;
-- 데이터 삭제
DELETE FROM 테이블명;
-- 데이터 선택
SELECT 컬럼1, 컬럼2 FROM 테이블명;
SELECT 컬럼1 AS ~~ FROM 테이블명;✅ DISTINCT
- 중복시 1회만 출력
⭐ 와일드카드
* : 모든
% : 모든
_ : 한 글자✅ 합성연산자 : 문자와 문자 연결
|| : oracle
+ : sql server⭐ DROP vs TRUNCATE vs DELETE
| 종류 | 타입 | ROLLBACK 여부 | COMMIT | 초기화 | 삭제여부 | 로그 |
|---|---|---|---|---|---|---|
| DROP | DDL | ROLLBACK 불가 | AUTO COMMIT | 용량 초기화 | 테이블 정의 자체 삭제 | 로그X |
| TRUNCATE | DDL | ROLLBACK 불가 | AUTO COMMIT | 최초용량 제외 초기화 | 최초 생성된 초기상태 | 로그X |
| DELETE | DML | ROLLBACK 가능 | 사용자 COMMIT | 초기화 X | 데이터만 삭제 | 로그O |
📌 TCL
✅ 트랜잭션
- 밀접히 관리되어 분리될 수 없는 1개 이상의 DB 조작, 논리적 연산단위
✅ COMMIT
- 올바르게 반영된 데이터를 DB에 반영
✅ ROLLBACK
- 트랜잭션 시작 이전의 상태로 되돌림
- COMMIT 되지 않은 모든 트랜잭션을 되돌림
✅ SAVEPOINT
- 저장 지점
-- ORACLE
SAVEPOINT S1;
ROLLBACK TO S1;
COMMIT;
-- SQL SERVER
SAVE TRANSACTION SVTR1;
ROLLBACK TRANSACTION SVTR1;⭐ 트랜잭션의 특성
ACID
- 원자성(Atomicity) : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함
- 일관성(Consistency) : 트랜잭션 실행 전 DB 내용이 잘못되지 않으면 트랜잭션 실행 후에도 잘못되지 않아야 함
- 고립성(Isolation) : 트랜잭션 실행 도중 다른 트랙잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
- 지속성(Durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장됨
⭐ 트랜잭션의 격리성이 낮은 경우 발생할 수 있는 문제점
- Dirty Read : 다른 트랜잭션에 의해 수정되었지만, 아직 commit되지 않은 데이터를 읽는 것
- Non-Repeatable Read : 같은 쿼리에 대해, 다른 트랜잭션이 값을 수정하여 두쿼리 결과가 다르게 나타나는 현상
- Phantom Read : 같은 쿼리에 대해, 첫번째 쿼리에 없던 유령레코드가 두번째 쿼리에 나타나는 현상
⭐ 연산자의 종류
BETWEEN A AND B
-- A와 B사이에 있으면 됨(A, B포함)
IN (LIST)
-- 리스트에 있는 값 중 어느 하나라도 일치
NOT IN (LIST)
-- 리스트의 값과 일치하지 않음
LIKE '비교문자열'
-- 비교문자열과 형태가 일치(%, _)
IS NULL
-- NULL값인 경우
IS NOT NULL
-- NULL값이 아닌 경우
!=, ^=, <>
-- 같지 않다✅ NULL
- 수치연산은 NULL 반환
ex) NULL + 3 = NULL - 비교연산은 FALSE 반환
ex) NULL < 3 = FALSE - IS NULL, IS NOT NULL(!=나 <> 사용불가)
INSERT INTO 서비스 VALUES('999', '');
-- ORACLE에서는 ''를 NULL로 인식
-- SQL SERVER에서는 공백으로 인식
SELECT * FROM 서비스 WHERE 서비스명 IS NULL; -- ORACLE
SELECT * FROM 서비스 WHERE 서비스명 = ''; -- SQL SERVER
✅ 연산자 우선순위
- (괄호) => NOT => 비교연산자(>, <, IN, BETWEEN, LIKE) => AND => OR
✅ ROWNUM
- 원하는 만큼의 행을 가져올 때 사용(ORACLE)
- TOP : SQL Server
-- ORACLE
SELECT NAME FROM PLAYER WHERE ROWNUM = 1;
-- SQL Server
SELECT TOP(1) NAME FROM PLAYER;📌 함수
✅ 단일행함수 VS 다중행함수
- 함수의 입력 행수에 따라 구분
- 단일행 함수와 다중행함수 둘다 단일값 반환
⭐ 단일행 함수
- SELECT, WHERE, ORDER BY, UPDATE SET 절에서 사용가능
- 행에 개별적 조작
- 여러 인자가 있어도 결과는 1개만 출력
- 함수 인자에 상수, 변수, 표현식 사용가능
- 함수 중첩 가능
⭐ 문자형 함수
-- 문자열을 소문자로
LOWER(문자열)
-- 문자열을 대문자로
UPPER(문자열)
-- 문자의 ASCII 값 반환
ASCII(문자)
-- ASCII 값에 해당하는 문자 반환
CHR / CHAR(ASCII번호)
-- 문자열 1, 2를 연결(||, +와 동일)
CONCAT('DBMS', ' SQL') -> 'DBMS SQL'
-- 문자열 중 m위치에서 n개의 문자 반환
SUBSTR / SUBSTRING('SQL Expert', 5, 3) -> 'Exp'
-- 문자열 길이를 숫자값으로 반환
LEN / LENGTH(문자열)
-- 해당 문자열 제거
LTRIM('xxxYYZZXYZxx', 'x') -> 'YYZZXYZxx'
RTRIM('xxxYYZZXYZxx', 'x') -> 'xxxYYZZXYZ'
TRIM('x' FROM 'xxxYYZZXYZxx') -> 'YYZZXYZ'⭐ 숫자형 함수
SIGN(숫자) -- 양수면 1, 음수면 -1, 0이면 0 반환
MOD(숫자1, 숫자2) -- 숫자1을 숫자2로 나눈 나머지 반환
CEIL(숫자) -- 크거나 같은 최소 정수 반환(올림)
FLOOR(숫자) -- 작거나 같은 최대 정수 반환(내림)
ROUND(숫자) -- 반올림
ABS(숫자) -- 절대값
ROUND(38.5235, 3) -> 38.524 -- 반올림
ROUND(38.5235, 1) -> 38.5
ROUND(38.5235) -> 39
TRUNC(38.5235, 3) -> 38.523 -- 소수점 버림(내림)
TRUNC(38.5235, 1) -> 38.5
TRUNC(38.5235) -> 38⭐ 날짜형 함수
SYSDATE, GETDATE() : 현재 날짜와 시간 출력
EXTRACT, DATEPART : 날짜에서 데이터 출력
TO_NUMBER(TO_CHAR(d, 'YYYY')) : 연도를 숫자로 출력
1 = 하루
1/24 = 1시간
1/24/60 = 1분
1/24/(60/10) = 10분⭐ NULL 관련 함수
NVL(식1, 식2) -- 식1의 값이 NULL이면 식2 출력, 아니면 식1
ISNULL(식1, 식2) -- 식1의 값이 NULL이면 식2 출력, 아니면 식1
NULLIF(식1, 식2) -- 식1, 식2가 같다면 NULL 출력, 아니면 식1
COALESCE(식1, 식2, 식3, ...) -- NULL이 아닌 최초의 표현식 출력, 모두 NULL이면 NULL반환⭐ NULL의 특성
- 아직 정의되지 않은 값, 0또는 공백과 다르다
- 테이블을 정의할때 NOT NULL 또는 PRIMARY KEY로 정의되지않은 모든 컬럼은 NULL을 가질수 있다
- NULL을 포함하는 연산의 결과는 NULL이다
- 결과값을 NULL이 아닌 다른값을 얻고자 할 때 NVL / IS NULL 함수를 사용
⭐ 다중행 함수
- 집계함수 : COUNT, SUM, AVG, MIN, MAX
- 그룹함수 : ROLLUP, CUBE, GROUPING SETS
- 윈도우함수 : RANK, DENSE_RANK, ROW_NUMBER
⭐ 다중행 집계함수
- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수
- GROUP BY 절은 행들을 소그룹화 한다
- SELECT, HAVING, ORDER BY 절에 사용 가능
- ALL : DEFAULT옵션, 생략가능
- DISTINCT : 같은 값을 하나의 데이터로 간주 옵션
COUNT(*) -- NULL 포함 행의 개수
COUNT(컬럼) -- NULL 제외 행의 개수
SUM, AVG -- NULL 제외 합계, 평균
STDDEV -- 표준편자
VARIAN -- 분산
MAX, MIN -- 최대, 최소⭐ GROUP BY, HAVING 절의 특징
- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계함수를 사용
- 집계함수의 통계정보는 NULL값을 가진 행을 제외하고 수행한다
- GROUP BY 절에서는 SELECT절과 달리 ALIAS 사용 불가
- 집계함수는 WHERE절에 올 수 없다
- WHERE절은 전체데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킴
- HAVING절에는 집계함수를 이용하여 조건표시 가능
- HAVING절은 일반적으로 GROUP BY 뒤에 위치
-- SEARCHED_CASE_EXPRESSION
CASE WHEN LOC = 'A' THEN 'B'
-- SIMPLE_CASE_EXPRESSION
CASE LOC WHEN 'A' THEN 'B'
DECODE(LOC, 'A', 'B')
-- ELSE NULL이 생략되있음
-- 위 문장들은 같은의미이다.✅ GROUP BY를 하면 ORDER BY 절에 GROUP BY 컬럼 또는 집계함수 컬럼만 사용가능
⭐ ORDER BY 특징
- SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 컬럼을 기준으로 정렬하여 출력하는데 사용
- ORDER BY 절에 컬럼명 대신 ALIAS명이나 컬럼순서를 나타내는 정수도 사용가능
- DEFAULT값은 오름차순이며 DESC옵션을 통해 내림차순 정렬이 가능
- SQL 문장의 제일 마지막에 위치
- SELECT 절에서 정의하지 않은 컬럼 사용가능
- 날짜형 데이터 타입은 오름차순으로 정렬하면 1월1일이 12월1일보다 먼저 출력됨
✅ NULL
- ORACLE에서는 NULL을 가장 큰 값으로 취급한다.(오름차순 정렬하면 제일 마지막)
- SQL Server에서는 NULL을 가장 작은 값으로 취급한다.(오름차순 정렬하면 제일 처음)
⭐ SELECT 실행 순서
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
⭐ SQL Server의 WITH TIES
SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC;
-- 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원은 같이 출력(2명이상 나올수도있음)📌 JOIN
- 두 개 이상의 테이블을 연결 또는 결합하여 데이터를 출력하는 것
- 일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립됨
- 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능함
- DBMS옵티마이저는 FROM절에 나열된 테이블들을 임의로 2개씩 묶어서 JOIN을 처리
✅ JOIN 개수
- N개의 테이블을 JOIN하기 위해서는 (
N-1)개의 JOIN 조건이 필요하다.
⭐ EQUI JOIN
- 2개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하는 경우에 사용
- 대부분 PK, FK의 관계를 기반으로 함
- '=' 연산자에 의해서만 수행되고 그 이외의 비교연산자를 사용하는 경우는 NON EQUI JOIN이다
SELECT *
FROM EMP, DEPT
WHERE EMP.DNAME = DEPT.DNAME;
SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DNAME = DEPT.DNAME;⭐ NON EQUI JOIN
- 2개의 테이블 간에 컬럼 값들이 정확하게 일치하지 않는 경우에 사용
- '='연산자가 아닌 BETWEEN, >, <= 등의 연산자를 사용
- 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있음
SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; 