[SQLD] - 2과목 1장 SQL 기본

🌳1. 관계형 DB 개요

1. DB

데이터를 일정한 형태로 저장. DBMS를 이용해 효율적인 데이터 관리와 데이터 손상 복구 가능
종류

• 계층형 : 트리 형태의 자료구조에 데이터 저장. 1:N 관계
• 네트워크형 : 오너와 멤버 형태. M:N 관계
• 관계형 : 릴레이션에 저장. 집합 연산과 관계 연산 가능

2. 관계형 DB

정규화를 통해 이상현상 및 중복 데이터 제거
동시성 관리와 병행 제어를 통해 데이터 동시 조작 가능

집합 연산

• 합집합 / 차집합 / 교집합 / 곱집합(각 릴레이션에 존재하는 모든 데이터 조합)

관계 연산

• 선택 : 조건에 맞는 행(튜플) 조회
• 투영 : 조건에 맞는 칼럼(속성) 조회
• 결합 : 공통 속성을 사용해 새로운 릴레이션 생성
• 나누기 : 공통요소를 추출하고 분모 릴레이션의 속성을 삭제 후 중복 행 제거

3. SQL

RDB에서 사용하는 언어
데이터 조회 및 신규 데이터 입력/수정/삭제 기능 제공
종류

• DML (데이터 조작어)
  • SELECT : 데이터 조회 명령어
  • INSERT, UPDATE, DELETE : 데이터 변형 명령어
• DDL (데이터 정의어. 데이터 구조 관련 명령어)
  • CREATE, ALTER, DROP
• DCL (데이터 제어어. DB접근 권한 부여 및 회수 명령어)
  • GRANT, REVOKE
• TCL (트랜잭션 제어어. DML로 조작한 결과를 논리적인 작업 단위 별로 제어)
  • COMMIT, ROLLBACK

4. 테이블(Table)

RDB의 기본 단위
데이터를 저장하는 객체
칼럼과 행의 2차원 구조

🌳2. DDL

1. 데이터 타입

CHAR(L) - 고정 길이 문자열. 할당된 변수 값의 길이가 L 이하일 대 차이는 공백으로 채워짐
VARCHAR2(L), VARCHAR(L) - 가변 길이 문자열. 할당되는 변수 값의 길이의 최대값이 L. 문자열은 가능한 최대 길이로 설정
NUMBER(L,D) - 숫자형. (L은 전체 자리 수. D는 소수점 자리 수)
DATE, DATETIME - 날짜형. 데이터 크기 지정 불필요

2. CREATE TABLE

테이블 및 칼럼 명명 규칙

• 알파벳, 숫자, 언더바, $, # 사용
• 대소문자 구분 X
• 테이블명은 단수형 권고

제약조건 : 데이터 무결성 유지가 목적. 복제 티이블에는 기존 테이블 제약조건 중 NOT NULL만 적용

• PRIMARY KEY : 테이블 당 하나의 기본키 정의 . DBMS가 자동으로 인덱스 생성. NULL 불가
• FOREIGN KEY : 다른 테이블의 기본키를 외래키로 지정. 참조 무결성 제약 조건
  => SQL : ALTER TABLE 테이블명 ADD CONSTRAINT 칼럼명 FOREIGN KEY(칼럼명) REFERENCES 테이블명
• UNIQUE KEY : 행 데이터를 식별하기 위해 생성. NULL 가능.
• DEFAULT : 'DEFAULT 값'으로 기본값 설정
• NOT NULL
• CHECK : 입력값의 종류, 범위 제한

NULL

• 아직 정의되지 않은 값. 현재 데이터를 입력하지 못하는 값.
• NULL과의 연산은 NULL
• 비교연산은 FALSE

3. ALTER TABLE - 테이블 칼럼 관련 변경 명령어

칼럼 추가

• 마지막 칼럼으로 추가. 위치 지정 불가능
• ALTER TABLE 테이블명 ADD (칼럼명 데이터타입)

칼럼 삭제

• 삭제 후 복구 불가
• ALTER TABLE 테이블명 DROP COLUMN 칼럼명

칼럼 설정 변경

• NULL만 있는 경우
• 행이 없는 경우에만 칼럼의 크기 축소 가능
• NULL만 있는 경우에는 데이터 유형도 변경 가능
• NULL이 없으면 NOT NULL 제약조건 추가 가능
• 기본값 변경 작업 이후 발생하는 데이터에 대해서만 기본값 변경
• ALTER TABLE 테이블명 MODIFY(칼럼명 데이터타입 제약조건)

RENAME TABLE

• ALTER TABLE 테이블명 'RENAME TO 테이블명'으로 가능

DROP TABLE

• 테이블의 데이터와 구조 삭제, 복구 불가
• CASCADE CONSTRAINT 옵션으로 관련 테이블의 참조 제약조건 삭제하여 참조 무결성 준수 가능
• CREATE TABLE에서 ON DELETE CASCADE 옵션으로 동일 기능 실현 가능
• DROP TABLE 테이블명

TRUNCATE TABLE

• 테이블의 전체 데이터 삭제 (<-> DROP TABLE은 테이블 자체를 제거)
• 로그를 기록하지 않기 때문에 ROLLBACK 불가
• TRUNCATE TABLE 테이블명

🌳3. DML

1. INSERT : 데이터 입력

INSERT INTO 테이블명 (칼럼명,...) VALUES(필드값,...)
INSERT INTO 테이블명 VALUES(필드값)

2. UPDATE : 데이터 수정

UPDATE 테이블명 SET 칼럼명 = 필드값

3. DELETE : 데이터 삭제

DELETE FROM 테이블명 WHERE 조건절
DELETE FROM 테이블명

• DELETE로 데이터를 삭제해도 테이블 용량은 초기화되지 않음.
• TRUNCATE로 삭제하면 초기화
• DROP은 객체 삭제 명령어

4. SELECT

칼럼 별 데이터 선택

• SELECT 칼럼명 FROM 테이블명

데이터 중복 없이 선택

• SELECT DISTINCT 칼럼명 FROM 테이블명

  전체 칼럼의 데이터 선택

• SELECT * FROM 테이블명

  앨리어스(Alias)

• SELECT 칼럼명 AS "별명" : 출력되는 칼럼명 설정

• FROM 테이블명 별명 : 쿼리 내에서 사용할 테이블명 설정. 칼럼명 중복되는 경우 SELECT절에서 앨리어스 필수

🌳4. TCL

1. 트랜잭션

DB의 논리적 연산 단위.
하나 이상의 SQL문 포함

특성 ACID

• 원자성 : 전부 실행되거나 전혀 실행되지 않음
• 일관성 : 트랜잭션으로 인한 DB 상태의 모순이 없음
• 고립성 : 부분적인 실행 결과에 다른 트랜잭션이 접근 불가능. LOCKING으로 보장
• 영속성 : 트랜잭션의 결과는 영구적으로 저장됨

2. TCL

데이터 무결성 보정을 목적으로 함
영구 변경 전 확인과 연관 작업 동시처리 가능

ORACLE은 SQL 문장을 실행하면 트랜잭션 시작되고 TCL 실행하면 트랜잭션 종료
DDL을 실행하면 자동 커밋(DML 이후 커밋없이 DDL 실행해도 자동 커밋)
DB를 정상적으로 종료하면 자동 커밋, 애플리케이션 등의 이상으로 DB 접속 단절되면 자동 롤백

3. COMMIT

데이터를 DB에 영구적으로 반영하는 명령어.
커밋 시 트랜잭션이 완료되어 LOCKING 해제.
SQL Server은 기본적으로 자동 커밋

COMMIT 전

• 데이터 변경이 메모리 버퍼에만 영향 받았기에 복구 가능
• NOLOGGING 옵션 사용 시 버퍼 캐시의 기록 생략해 입력 성능 향상
• 사용자는 SELECT 절로 결과 확인할 수 있지만 다른 사용자는 현재 결과 볼 수 없음
• 변경된 행에 LOCKING이 설정되어 다른 사용자가 변경할 수 없음
• LOCKING이 안 걸린 상태일 때 여러 사용자가 데이터를 변경하면 상관없음

COMMIT 후

• 변경 사항이 DB에 반영되고 이전 데이터는 복구 불가
• 모든 사용자가 결과를 볼 수 있음
• LOCKING 해제되어 다른 사용자가 행 조작 가능

4. ROLLBACK

트랜잭션 시작 이전의 상태로 되돌리는 명령어
COMMIT 이전 상태로 돌려줌
ROLLBACK 시 LOCKING이 해제

SAVEPOINT

• 트랜잭션 일부만 롤백할 수 있도록 중간상태 저장하는 명령어

• ROLLBACK TO 저장점명 = 해당 저장점 상태로 돌려줌

• 동일 저장점명이 있으면 나중 저장점이 유효

• SQL Server에서는 'BEGIN TRAN'으로 명시해야 가능

🌳5. WHERE 절

1. WHERE

SELECT 칼럼명 FROM 테이블명 WHERE 조건절

2. 연산자

비교연산자

• =, >, >=, <, <=
• 비교 대상 데이터 타입에 따라 자동으로 형 변환되는 경우도 있음

부정비교 연산자

• !=, ^=, <>
• 'NOT 칼럼명 비교연산자'와 동일

SQL 연산자 입력값을 비교하여 논리값 출력

• BETWEEN A AND B : A와 B 사잇값
• IN(리스트) : 리스트 내의 값
• LIKE'문자열' : 문자열의 형태와 일치하는 값
  • %는 0개 이상의 문자. _ 는 1개의 단일 문자
• IS NULL : NULL은 등호로 판단 불가
• NOT BETWEEN A AND B, NOT IN (리스트), IS NOT NULL

논리 연산자

• AND, OR, NOT

우선 순위 : 부정 > 비교 > 논리

• 괄호 / NOT / 비교 및 SQL 연산자 / AND / OR

문자열 비교 방법

• CHAR VS CHAR : 첫 서로 다른 문자열 값으로 비교. 길이가 다른 경우 공백 추가해 길이 맞춤.
• CHAR VS VARCHAR : 첫 서로 다른 문자열 값으로 비교. 길이가 다르면 길이가 긴 값이 크다고 판단. VARCHAR의 공백도 문자로 판단. TRIM함수 이요해 공백 제거하고 판단 가능
• CHAR VS 상수 : 상수를 변수 타입으로 바꿔서 비교

3. 부분 범위 처리

ROWNUM

• SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호
• 조건절 내에서 행의 개수를 제한하는 목적으로 사용

TOP

• 출력 행의 수 제한 함수
• 'TOP(N)'로 N개 행 출력
• 개수 대신 비율로도 제한 가능
• ORDER BY 절이 없으면 ROWNUM과 TOP의 기능이 같음

🌳6. 함수

1. 단일 행 함수

SELECT절, WHERE절, ORDER BY절에 사용 가능.
각 행에 개별적으로 작용
여러 인자를 입력해도 단 하나의 결과만 출력

문자형 함수 : 문자열 입력 시 문자열이나 숫자 반환

• UPPER, LOWER, LENGTH
• CONCAT : 문자열 결합
• SUBSTR : 문자열 부분 추출
• LTRIM, RTRIM, TRIM : 왼쪽, 오른쪽, 양쪽 공백 제거
• ASCII : 아스키 코드값 출력

숫자형 함수

• ABS, SIGN : 절대값, 부호 (-1,0,1 중 출력)
• MOD : 나머지 연산자. %으로 대체 가능
• ROUND, CEIL, FLOOR : 반올림, 올림, 버림 (함수(E,N)으로 소수점 N번째까지 출력)
• TRUNC : 숫자형 부분 추출

날짜형 함수

• SYSDATE : 현재 시각 출력(년, 월, 일, 시, 분, 초)
• EXTRACT : 날짜형 부분 추출
  • SELECT EXTRACT(부분 FROM SYSDATE) FROM DUAL
• +- 숫자, +- 숫자/24 : 일자 연산, 시간 연산
• NEXT_DAY : 지정된 요일 첫 날짜 출력

변환형 함수 : 데이터 타입 변환, 명시적 형 변환 방식

• TO_NUMBER, TO_CHAR, TO_DATE : 문자열로, 숫자로, 날짜로
• CAST, CONVERT

NULL 관련 함수

• NVL(칼럼,값) : NULL 값 변환
• NVL2(칼럼,값,값) : NULL이면 앞의 값 아니면 뒤의 값 출력
• NULLIF(값,값) : 같으면 NULL 아니면 첫 값 출력
• COALESCE(값,값) : NULL이 아닌 첫 값 출력
• ISNULL(칼럼,값) : NULL이면 값으로 대치 아니면 갈럼 값 출력
  

2. 데이터 변환

명시적 형 변환 : 변환형 함수를 이용하여 데이터 타입 변환
암시적 형 변환 : DBMS가 자동으로 데이터 타입 변환

3. 조건문 : IF-THEN-ELSE 형태

CASE WHEN 조건절1 THEN 출력값1 ... ELSE 기본값 END : ELSE 생략시 NULL 출력

• CASE WHEN NULL THEN 출력값 ELSE 기본값은 조건이 없으므로 모든 행에서 기본 값 출력

DECODE(칼럼, 기준값1, 출력값1,..., 기본값) : 기준값 N이면 출력값 N출력

🌳7. GROUP BY, HAVING 절

1. 집계 함수

그룹 별 결과 출력
다중 행 함수 중 하나
GROUP BY절이 없으면 그룹핑 대상이 존재하지 않아 에러 발생
WHERE절에 사용 불가
공집합에서도 연산 수행

ALL, DISTINCT : 전체 출력, 중복 제외 출력
SUN,AVG,MAX,MIN,VARIAN,STDDEV : NULL 제외하고 연산
COUNT : 행 수 출력

• COUNT(*) : NULL 포함
• COUNT(표현식) : NULL 제외

2. GROUP BY

그룹핑 기준 설정
앨리어스 사용 불가

3. HAVING

GROUP BY절에 의한 집계 데이터에 출력 조건을 걺
<-> WHERE절은 SELECT절에 조건을 걸기 때문에 제외된 데이터가 GROUP BY 대상이 아님
일반적으로 GROUP BY 뒤에 위치함

🌳8. ORDER BY절

1. ORDER BY

특정 칼럼을 기준으로 정렬
기본 정렬 기준은 오름차순
1. 컬럼명
2. 앨리어스
3. 칼럼의 SELECT절에서 순서로 칼럼 지정 가능.
SELECT절에 없는 칼럼도 지정 가능
GROUP BY절이 있으면 GROUP BY 대상 칼럼만 지정 가능
Oracle은 NULL을 최대값으로 판단

2. SELECT문 실행 순서

테이블에서 출력 대상이 아닌 것은 제거하고 그룹핑해서 그룹핑된 값이 조건에 맞는 데이터를 계산 및 출력하고 정렬
SELECT 칼럼명 AS '별명' 　　　　　　　　 => 5. 계산 및 출력하고
FROM 테이블명　　　　　　　　　　　　 => 1. 테이블에서
WHERE 조건식　　　　　　　　　　　　　=> 2. 출력 대상이 아닌 것은 제거하고
GROUP BY 칼럼/표현식　　　　　　　　　=> 3. 그룹핑해서
HAVING 조건식　　　　　　　　　　　　  => 4. 그룹핑된 값이 조건에 맞는
데이터를
ORDER BY 칼럼/표현식　　　　　　　　　 => 6. 정렬

🌳9. 조인

1. 조인

여러 테이블을 연결 또는 결합하여 데이터를 출력하는 것
일반적으로 PK나 FK의 연관성에 의해 성립

2. 등가 조인

두 테이블의 칼럼 값이 정확하게 일치하는 경우
대부분 PK와 FK 관계 기반
SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼명 = B. 칼럼명
SELECT 대상 칼럼이 두 테이블 모두에 있는 경우 앨리어스를 지정해야 함

3. 비등가 조인

두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우, 부등호나 BETWEEN 연산자를 통해 조인