[SQLD] SQL 기본 및 활용(1)

Dahui Kim·2023년 3월 13일

DCL DDL DML SQLD SQL기본 TCL sql

데이터베이스

목록 보기

3/5

관계형 DB 개요

DB : 데이터를 일정한 형태로 저장해놓은 것
DBMS : 효율적으로 데이터를 관리하고 데이터 손상 복구가 가능하게 하는 소프트웨어
관계형 데이터베이스(RDB; Relational Database) : 정규화를 통해 이상현상 및 중복 데이터를 제거하고 동시성 관리와 병행 제어를 통해 데이터 동시 조작을 가능하게 한다.
- 연산
  - 집합 연산
    - Union(합집합)
    - Difference(차집합)
    - Intersection(교집합)
      - 관계 연산
        
        Selection(선택 연산) : 조건에 맞는 행(튜플) 조회
        
        Projection(투영 연산) : 조건에 맞는 칼럼(속성) 조회
        
        Join(결합 연산) : 공통 속성을 사용하여 새로운 릴레이션 생성
        
        Division(나누기 연산) : 공통 요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거
    - Cartesian Product(곱집합) : 각 릴레이션에 존재하는 모든 데이터를 조합
- 테이블 : DB 기본 단위, 데이터를 저장하는 객체
  - 로우(가로, 행, 튜플, 인스턴스)
  - 컬럼(세로, 열)
  - 테이블의 분할
    - 정규화를 통해 데이터의 불필요한 중복 제거하고 이상현상을 방지한다.
- SQL : 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어
  - DML(Data Manipulation Language, 데이터 조작어, 로우 단위) : ISUD
    - insert,update,delete : 데이터 변형 명령어
    - select : 데이터 조회 명령어
  - DDL(Data Definition Laguage, 데이터 정의어, 테이블 단위) : CA_D
    - create, alter, drop, rename
  - DCL(Data Control Language, 데이터 제어어): DB 접근 권한 부여 및 회수 명령어
    - grant,revoke
  - TCL(Transaction Control Language, 트랜잭션 제어어) : DML로 조작한 결과를 논리적인 작업단위 별로 제어
    - commit, rollback
- ERD(Entity Relationship Diagram)
  - 구성요소 : 엔터티, 관계, 속성
  - 표기법 : IE(Information Engineering) 표기법, Barker(Case Method) 표기법

DDL, Data Definition Language

명명 규칙
- 테이블 명은 다른 테이블의 이름과 중복되어서는 안 된다.
- 테이블 내의 칼럼명은 중복될 수 없다.
- 각 칼럼들은 쉼표( , ) 로 구분되고 세미콜론( ; ) 으로 끝난다.
- 데이터 유형은 꼭 지정되어야한다.
- 테이블명과 칼럼명은 반드시 문자로 시작한다.
- [A-Z, a-z, 0-9, _, $, #] 만 사용가능
데이터 유형
- CHAR(len) : 고정 길이 문자열, 최대 길이만큼 공백으로 공간을 채운다.
- VARCHAR(len), VARCHAR2(len) : 가변 길이 문자열, 할당되는 최대 값이 len이다. MS-SQL, MySQL은 VARCHAR을 사용하고 ORACLE은 VARCHAR2를 사용한다.
- NUMBER(len, dot) : 정수, 실수 등 숫자 정보 (len: 전체 자리수; 1~38 기본값 38, dot: 소수점 자리수; -84 ~ 127 기본값 0)
- DATE, DATETIME : 날짜와 시각 정보
제약조건 : 데이터 무결성 유지 목적
- PRIMARY KEY : 기본키, 테이블 당 하나의 기본키만 정의 가능, 기본키 생성시 DBMS가 자동으로 인덱스 생성, NOT NULL
- FOREIGN KEY : 외래키, 다른 테이블의 기본키를 외래키로 지정, NULL 가능, 여러 속성 가능, 참조 무결성 제약조건
- UNIQUE : 고유키, 행 데이터를 식별하기 위해 생성, NULL 가능
- DEFAULT : ‘DEFAULT 값’으로 기본값 설정
- NOT NULL : NULL 값 입력 금지
- CHECK : 입력값의 종류 및 범위 제한
CREATE TABLE : 테이블 생성

CREATE TABLE 테이블명 (
	칼럼명_1 CHAR(7) NOT NULL,
	칼럼명_2 VARCHAR2(30) NOT NULL
);

ALTER TABLE : 테이블 구조 변경

칼럼 추가

ALTER TABLE 테이블명 ADD(칼럼명_3 VARCHAR2(20));

칼럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 칼럼명_3;

칼럼 수정

ALTER TABLE 테이블명 MODIFY (칼럼명_2 DATE DEFAULT SYSDATE NOT NULL);

CONSTRAINT : 제약조건

제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건;

제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건;

DROP : 테이블의 데이터와 구조 삭제, 복구 불가

DROP TABLE 테이블명;

TRUNCATE : 테이블의 전테 데이터 삭제, 로그를 기록하지 않아 ROLLBACK 불가

TRUNCATE TABLE 테이블명;

RENAME : 이름 변경

테이블 이름 변경

ALTER TABLE 테이블명 RENAME TO 테이블명_2;

칼럼 이름 변경

ALTER TABLE 테이블명 RENAME COLUMN 칼럼명 TO 칼럼명_2;

제약조건명 변경

ALTER TABLE 테이블명 RENAME CONSTRAINT 제약조건명 TO 제약조건명_2

DML, Data Manipulation Language

DDL 명령어는 실행시 AUTO COMMIT 하지만 DML의 경우 COMMIT을 입력해야한다.

INSERT : 데이터 입력

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

UPDATE : 데이터 수정

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

DELETE : 데이터 삭제 TRUNCATE로 삭제하는 것과 달리 DELETE로 데이터를 삭제해도 테이블 용량은 초기화되지 않는다. DROP 명령어는 데이터가 아닌 객체를 삭제한다.

DELETE FROM 테이블명 WHERE 조건절;

SELECT : 데이터 선택
- 산술 연산자 우선순위(NUMBER와 DATE에 적용)
  - () → * → / → + → -
- 와일드 카드
  - * : 모든
  - % : 모든
  - - : 한글자
- 앨리어스(alias)
  - 중복되는 칼럼명 설정
  - 조회된 결과에 별명을 부여하여 칼럼 레이블 변경 가능 칼럼명 바로 뒤에 온다. ALIAS가 공백, 특수문자를 포함할 경우나 대소문자 구분이 필요할 경우 큰따옴표 " 로 묶어 사용한다.
```
SELECT 칼럼명 AS 별명;
```
- 합성 연산자(Concatenation) : 문자와 문자 연결
  - 오라클 : ||
  - SQL Server : +
  - 공통 : CONCAT(str_1, str2)
- DUAL : 오라클의 기본 더미 테이블, 연산 수행을 위해 사용됨
- 칼럼 별 데이터 선택
```
SELECT 칼럼명 FROM 테이블명;
```
- 데이터 중복 없이 선택
```
SELECT DISTINCT 칼럼명 FROM 테이블명;
```

TCL, Transaction Control Language

데이터 무결성 보장을 목적으로 한다. 영구 변경 전 확인과 연과 작업 동시처리가 가능하다.

특징
- 오라클은 SQL 문장을 실행하면 트랜잭션이 시작되고 TCL을 실행하면 트랜잭션이 종료된다.
- DDL을 실행하면 자동 커밋
- DB를 정상적으로 종료하면 자동 커밋, 애플리케이션 등의 이상으로 DB 접속이 단절되면 자동 롤백
COMMIT : 문제없이 처리된 트랜잭션을 데이터베이스에 영구 반영, COMMIT시 LOCKING이 해제됨, SQL Server는 자동 커밋
- 커밋 전
  - 데이터 변경이 메모리 버퍼에만 영향을 받았기 때문에 복구 가능
  - 현재 사용자는 SELECT 문으로 변경 결과 확인 가능
  - 다른 사용자는 현재 사용자가 수행한 결과 확인 불가능
  - 변경된 행은 잠금(LOCKING)이 설정되어있어 다른 사용자가 변경 불가능
- 커밋 후
  - 데이터에 대한 변경사항 DB 영구 반영
  - 이전 데이터 소실
  - 모든 사용자 조회 가능
  - 모든 행 잠금 해제, 다른 사용자 행 조작 가능
ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림, 커밋 되지 않은 모든 트랜잭션을 롤백
- 롤백 후
  - 데이터에 대한 변경사항 취소
  - 이전 데이터 재저장
  - 관련 행 잠금해제, 다른 사용자 행 조작 가능

⇒ COMMIT과 ROLLBACK 사용으로

데이터 무결성 보장
영구적 데이터 변경 전 데이터 변경사항 확인 가능
논리적으로 연관된 작업 그룹핑하여 처리 가
SAVEPOINT : 트랜잭션 일부만 롤백할 수 있도록 중간 상태를 저장하는 명령어
```
SAVEPOINT SAVE_1;
ROLLBACK TO SAVE_1;
COMMIT;
```
트랜잭션 : DB의 논리적 연산 단위, 하나 이상의 SQL 문을 포함한다. 트랜잭션은 분해할 수 없는 최소 단위이기 때문에 ALL or NOTHING(전부 적용하거나 전부 취소)이 적용된다.
트랜잭션 특성 ACID
- Atomicity, 원자성 : 트랜잭션의 연산은 모두 적용되거나 모두 취소되어야한다; ALL or NOTHING.
- Consistency, 일관성 : 트랜잭션의 실행 전 DB에 이상이 없다면 실행 후에도 같아야한다.
- Isolation, 고립성 : 트랜잭션 실행 중, 다른 트랜잭션의 영향을 받아서는 안된다. LOCKING으로 고립성 보
- Durability, 지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 반영되어 저장된다.

WHERE 절

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

비교 연산자 : =, >, ≥ , <, ≤
부정 비교 연산자 : NOT 칼럼명 비교 연산자와 동일
- 부등호 : ≠, ^=, <>
SQL 연산자 (입력값을 비교하여 논리값 출력)
- BETWEEN a AND b : a와 b 값 사이
- IN(list) : 리스트에 있는 값 중 어느 하나라도 일치
- LIKE ‘문자열’ : 문자열의 형태와 일치하는 값
  - %는 0개 이상의 문자, _는 1개의 단일 문자
- IS NULL : NULL 값인 경우(NULL은 등호로 판단 불가)
- IS NOT NULL : NULL 값이 아닌 경우
- NOT IN(list) : 리스트에 있는 값과 일치하지 않음
논리 연산자 : AND, OR, NOT
연산자 우선순위 : () → NOT → 비교연산자 → AND → OR
부분 범위 처리
- ROWNUM : SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호, 원하는 만큼 행을 가져올 때 사용, WHERE 절에서 행의 개수를 제한하는 목적으로 사용 ⇒ROWNUM 조건이 ORDER BY 절보다 먼저 처리되는 WHERE 절에서 처리되어 인라인 뷰에서 먼저 정렬을 수행한 후 메인 쿼리에서 ROWNUM 조건을 사용해야한다.
- TOP : 출력 행의 수 제한 함수 TOP(N)로 N개 행 출력, 각 행에 개별적으로 작용, 여러 이자를 입력해도 단 하나의 결과만 출력

함수

사용자 정의 함수(User Defined Function)
내장함수( Built-In Function) : 벤더에서 제공하는 함수
- 단일행 함수 : 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 사용
- 다중행 함수 : 여러 레코드의 값들을 입력 인수로 사용

단일행 함수

단일행 함수의 종류 : 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수
- 특징
  1. SELECT, WHERE, ORDER BY 절에서 사용 가능
  2. 행에 개별적 조작
  3. 여러 인자가 있어도 결과는 1개만 출력
  4. 함수 인자에 상수, 변수, 표현식 사용 가능
  5. 함수 중첩 가능
- 문자형 함수
  - LOWER : 대문자 → 소문자
  - UPPER : 소문자 → 대문자
  - ASCII : 문자의 ASCII 값 반환
  - CHR : ASCII 값에 해당하는 문자 반환
  - CONCAT : 두 문자열을 연결
```
CONCAT('문자열_1','문자열_2');
-- 문자열_1 문자열_2
```
  - SUBSTR : 문자열 중 m 위치에서 n개의 문자를 반환
```
SUBSTR('문자열입니다.',3,3);
-- 열입니
```
  - LENGTH : 문자열 길이를 반환
  - LTRIM(문자열, 지정문자) : 문자열 왼쪽부터 확인해 지정 문자가 처음 나타나는 동안 해당 문자를 제거, 기본값 빈 칸
  - RTRIM(문자열, 지정문자) : 문자열 오른쪽부터 확인해 지정 문자가 처음 나타나는 동안 해당 문자를 제거, 기본값 빈 칸
  - TRIM(문자열, 지정문자) : 문자열 머리말,꼬리말, 양쪽 확인해 지정 문자가 처음 나타나는 동안 해당 문자를 제거, 기본값 빈 칸
- 숫자형 함수
  - SIGN(num) : 숫자가 양수일 경우 1, 음수는 -1 0일 경우 0 반환
  - MOD(num_1, num_2) : 숫자1을 숫자2로 나눈 나머지 반환
  - CEIL(num) : 소수점 올림, 정수 반환
  - FLOOR(num) : 소수점 내림, 정수 반환
  - ROUND(num_1, num_2) : 소수점 num_2 자리에서 반올림, 기본값 0
  - TRUNC(num_1,num_2) : 소수점 num_2 자리 뒤 절삭, 가본값 0
  - SIN, COS, TAN : 삼각함수 값 반환
  - EXP(), POWER(), SQRT(), LOG(), LN() : 지수, 거듭제곱, 제곱근, 자연로그
- 날짜형 함수
  - SYSDATE()/GETDATE() : 현재 날짜와 시각 반환
  - EXTRACT(’YEAR’ | ‘MONTH’ | ‘DAY’ from data) : 년/월/일 데이터 추출
  - DATEPART(’YEAR’ | ‘MONTH’ | ‘DAY’ | ‘HOUR’ | ‘MINUTE’ | ‘SECOND’, data) : 해당 데이터 추출
  - TO_NUMBER(TO_CHAR(data, ‘YYYY’)) / YEAR(data) : 해당 데이터 추출
  - 1=하루, 1/24=1시간, 1/24/60=1분
    - 날짜±숫자=날짜, 날짜-날짜=날짜 수, 날짜+숫자/24=날짜+시간
- 변환형 함수
  - 명시적 변환(Explicit) 변환 : 데이터 변환형 함수로 변환하도록 명시
  - 암시적 변환(Implicit) 변환 : 데이터베이스가 자동으로 변환하여 계산
- NULL 관련 함수
  - NVL(expr_1, expr_2) : expr_1의 값이 NULL 이면 expr_2 출력
  - ISNULL(expr_1, expr_2) : expr_1의 값이 NULL 이면 expr_2 출력
  - NULLIF(expr_1, expr_2) : expr_1의 값이 expr_2와 값으면 NULL을 아니면 expr_1을 출력
  - COALESCE(expr_1, expr_2) : NULL이 아닌 첫번째 값 출력
- 조건문
  - CASE WHEN 조건절 THEN 출력값 ELSE 기본값 END : ELSE 생략 시 NULL 출력

GROUP BY HAVING 절

SELECT DISTINCT 칼럼명 ALIAS명
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼/표현식
HAVING 그룹의 조건식;

집계함수(Aggregate Function)

여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수

집계 함수는 WHERE 절에 올 수 없다.
집계함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
SELECT, HAVING, ORDER BY 절에 사용 가능
- ALL : 기본 옵션, 생략 가능
- DISTINCT : 같은 값을 하나의 데이터로 간주 옵션
주로 숫자 유형에 사용하며 MAX, MIN,COUNT 함수는 문자, 날짜에도 적용가능하다.
종류
- COUNT() : NULL 값을 포함한 모든 행의 수를 출력
- COUNT(expr) : NULL 값인 것을 제외한 행의 수를 출력
- SUM() : NULL을 제외한 합계 출력
- AVG() : NULL을 제외한 평균 출력
- MAX() : 최대값
- MIN() : 최소값
- STDDEV() : 표준 편차를 출력
- VARIAN() : 분산을 출력

GROUP BY, HAVING 절

FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 추가로 사용한다.

GROUP BY
- GROUP BY 절은 행들을 소그룹화
- GROUP BY 절에서는 ALIAS 사용 불가
HAVING
- HAVING 절은 일반적으로 GROUP BY 뒤에 위치
- HAVING 절에는 집계함수를 이용하여 조건 표시

ORDER BY 절

ORDER BY 정렬 : 특정 칼럼을 기준으로 정렬, 기본값은 오름차순(ASC)
- SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬
- ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능
- DESC 옵션으로 내림차순 정렬
- SQL 문장 제일 마지막에 위치
- SELECT로 정의하지 않은 칼럼 사용가능
- 오라클에서는 NULL이 가장 크고 SQL Server에서는 가장 작다.
SELECT 문장 실행 순서
1. FROM : 발췌 대상 테이블 참조
2. WHERE : 발췌 대상 데이터가 아닌 것은 제거
3. GROUP BY : 행들을 소그룹화
4. HAVING : 그룹핑된 값의 조건에 맞는 것만을 출력
5. SELECT : 데이터 값을 출력 및 계산
6. ORDER BY : 데이터를 정렬
WITH TIES : ORDER BY 절의 조건 기준으로 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N + 동일 정렬 순서 데이터를 추가로 반환하도록 하는 옵션

JOIN

두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것

일반적으로 PK나 FK 값의 연관에 의해 JOIN이 성립되나 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.
두 개의 집합 간에만 JOIN이 일어난다.
- FROM 절에 3 개의 테이블이 나열되어도 특정 2개의 테이블만 먼저 조인되고 그 조인된 새로운 결과 집합과 남은 테이블이 다음 차례로 조인되는 것
EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용, 대부분 PK, FK의 관계를 기반으로 한다.
- JOIN 조건은 = 연산자를 사용해 WHERE 절에 기술한다.
- N개의 테이블 조인에서 JOIN 조건은 N-1개 이상 필요
- 조건절에 ALIAS명 사용이 가능하다 ⇒ 이 경우 WHERE절과 SELECT 절에서는 테이블명이 아닌 ALIAS를 사용해야한다.
NON EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용