2-1. SQL 기본

1. 관계형 데이터베이스 개요

데이터 베이스

데이터를 일정한 형태로 저장해 놓은 것, DBMS(데이터베이스 관리 소프트웨어)를 이용하여 효율적인 데이터 관리와 데이터 손상 복구 가능

 

관계형 DB(RDB; Relational Database)

정규화를 통해 이상현상 및 중복 데이터 제거, 데이터 무결성 보장, 데이터 회복/복구 가능, 병행 제어, 동시성 관리를 통해 데이터를 공유, 데이터 표현 방법 등을 체계화

 

SQL(Structured Query Language)

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

 

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

 

테이블(Table)

RDB의 기본 단위로써 데이터를 저장하는 객체, 칼럼과 행의 2차원 구조. 테이블은 하나 이상의 칼럼을 가져야 한다

 

 

ERD (Entity Relationship Diagram)

관계의 의미를 직관적으로 표현할 수 있는 수단

구성요소 : 엔티티(E), 관계(R), 속성

표기법 종류 : IE 표기법(Information Engineering) , Barker 표기법(Case Method)

 

 

 

---

2. DDL

DDL (Data Definition Language)

스키마, 도메인, 테이블, 뷰, 인덱스를 정의/변경/제거할 때 사용

 

데이터 타입 (Oracle, SQL Server)	설명
CHAR(L)	고정 길이 문자열, 할당된 변수 값의 길이가 L 이하일 때 차이는 공백으로 채워짐
VARCHAR2(L), VARCHAR(L)	가변 길이 문자열, 할당되는 변수 값의 길이의 최대값이 L임, 문자열은 가능한 최대 길이로 설정
NUMBER(L,D)	숫자형 (L은 전체 자리 수 D는 소수점 자리 수) ‒ SQL Server은 NUMERIC DECIMAL FLOAT REAL 등
DATE, DATETIME	날짜형, 데이터 크기 지정이 필요하지 않음

 

CREATE TABLE

CREATE TABLE 테이블명 (칼럼명1 데이터타입1 제약조건1,
											 칼럼명2 데이터타입2 제약조건2, ...);

-- Foreign Key 설정
ALTER TABLE 테이블명 ADD CONSTRAINT 칼럼명 FOREIGN KEY (칼럼명) REFERENCES 테이블명(칼럼명);

• 테이블 및 칼럼 명명 규칙 : 알파벳 대소문자, 숫자, ‘_’(언더바), ‘$’,‘#’ 사용. 테이블 명은 다른 테이블 명과 중복되면 안되고, 테이블 내의 칼럼명도 중복되면 안된다.
• 제약조건(Constraint): 데이터 무결성 유지가 목적, 복제 테이블에는 기존 테이블 제약조건 중 NOT NULL만 적용
  • PRIMARY KEY : 테이블 당 하나의 기본키만 정의 가능, 기본키 생성시 DBMS가 자동으로 인덱스를 생성함, NULL 불가
  • FOREIGN KEY : 으로 다른 테이블의 기본키를 외래키로 지정, 참조 무결성 제약조건 선택 가능, 여러개 가능
  • UNIQUE KEY : 행 데이터를 식별하기 위해 생성함, NULL 가능
  • DEFAULT : ‘DEFAULT 값’으로 기본값 설정
  • NOT NULL : 명시적으로 NULL 입력 방지
  • CHECK : 입력값의 종류 및 범위 제한

 

ALTER TABLE : 테이블의 칼럼 관련 변경 명령어

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

-- 칼럼 삭제 ; 삭제 후 복구 불가, 한번에 하나의 칼럼만 삭제 가능
ALTER TABLE 테이블명 DROP COLUMN 칼럼명;

-- 칼럼 설정 변경 
ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입 제약조건);

-- 칼럼 속성 변경
ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 데이터타입;

-- 칼럼명 변경 
ALTER TABLE 테이블명 RENAME 기존 칼럼명 TO 새로운 칼럼명;

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

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

 

RENAME TABLE : 테이블명 변경

-- Oracle
RENAME 기존 테이블명 TO 새로운 테이블명;

-- SQL Server
SP_NAME 기존 테이블명 , 새로운 테이블명;

 

DROP TABLE : 테이블 제거

-- 테이블 전부 삭제, 회복 불가
DROP TABLE 테이블명;

-- 테이블 의 일부 칼럼 삭제, 회복 불가
ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명; 

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

 

TRUNCATE TABLE

TRUNCATE TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;

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

 

 

 

---

3. DML

DML (Data Manipulation Language)

DDL 에 의해 생성된 스키마 내에서 데이터를 조작하는 데 사용되는 일련의 SQL 명령

 

 

INSERT : 데이터 입력

INSERT INTO 테이블명 (컬럼명들) VALUES (각 컬럼명에 넣을 VALUE_LIST); 
INSERT INTO 테이블명 (컬럼명 생략시 전체 컬럼 취급) VALUES (전체 COLUMN에 넣을 VALUE_LIST);

 

UPDATE : 데이터 수정

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

 

DELETE : 데이터 삭제

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

-- 조건절이 없으면 전체 테이블 삭제
DELETE FROM 삭제를 원하는 정보가 들어있는 테이블명 (WHERE 조건절); 

 

SELECT

-- 전체 칼럼의 데이터 선택
SELECT * FROM 테이블명;

-- 칼럼 별 데이터 선택
SELECT 칼럼명 FROM 테이블명;

-- 데이터 중복 없이 선택
SELECT DISTINCT 칼럼명 FROM 테이블명;

 

※ 와일드카드 : 모든 글자 (*,&), 한 글자 (-)

 

산술 연산자와 합성 연산자

• 산술 연산자 우선순위 : ( ) → * → / → + → —
• 문자열의 합성 연산자: ‘+’(플러스), CONCAT 함수로도 2개 문자열 합성 가능, Oracle에서는 ‘||’(수직선 2개)도 가능
• DUAL : Oracle의 기본 더미 테이블, 연산 수행을 위해 사용됨

 

 

 

---

4. TCL

TCL (Transaction Control Language)

트랜잭션을 처리하는 SQL문

 

트랜잭션

데이터 베이스의 논리적 연산 단위, 하나 이상의 SQL문을 반드시 포함

 

트랜잭션 특성 ACID

 

특성	설명
원자성 (Atomicity)	전부 실행되거나 전혀 실행되지 않음 (All or Nothing)
일관성 (Consistency)	트랜잭션 실행 전 DB 내용이 잘못 되지 않으면 실행 후도 잘못 되지 않아야 함
고립성 (Isolation)	부분적인 실행 결과에 다른 트랜잭션이 접근할 수 없음, LOCKING으로 고립성 보장
지속성 (Durability)	성공적인 트랜잭션의 결과는 영구적으로 저장됨

 

COMMIT

• 데이터를 DB에 영구적으로 반영하는 명령어
• COMMIT 후 변경 사항이 DB에 반영되고 이전 데이터는 복구 불가, 모든 사용자가 결과를 볼 수 있음, LOCKING이 해제되어 다른 사용자가 행을 조작할 수 있음

UPDATE PLAYER SET HEIGHT = 100; 
COMMIT; -- SQL

 

ROLLBACK

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

-- Oracle SQL 
 UPDATE PLAYER SET HEIGHT = 100; -- 480개의 행이 수정되었다. 
 ROLLBACK; -- 롤백이 완료되었다. 
 
-- SQL 
 BEGIN TRAN UPDATE PLAYER SET HEIGHT = 100; 
 ROLLBACK; -- 롤백 완료

 

SAVEPOINT

• 트랜잭션 일부만 롤백 할 수 있도록 중간상태를 저장하는 명령어
• 저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백 rksmd

-- Oracle
SAVEPOINT 세이브포인트명; 
ROLLBACK TO 세이브포인트명;

-- SQL Server
SAVE TRANSACTION 세이브포인트명;
ROLLBACK TRANSACTION 세이브포인트명;

 

 

 

---

5. WHERE 절

WHERE : 자신이 원하는 자료만을 검색하기 위해 이용

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

※ 와일드카드: 1) ‘%’(퍼센트)는 0개 이상의 문자 2) ‘_’(언더바)는 1개의 단일 문자

 

연산자 종류

• 비교 연산 : =, >, >=, <, <=
• 부정 비교 연산 : !=, ^=, <>, NOT 칼럼명 =, NOT 칼럼명 >
• SQL 연산 : Between a AND b, IN (list), Like ‘비교문자열’(’%’=0개 이상 문자열, ‘_’=1개 단일 문자), IS NULL
• 부정 SQL 연산 : NOT Between a AND b, NOT IN (list), IS NOT NULL
• 논리 연산 : AND, OR, NOT

 

우선순위

• ‘()’(괄호) → 부정 연산자(NOT) → 비교 연산자(=, >, >=, <, <=) 및 SQL 비교 연산자(BETWEEN a AND b, IN (리스트), LIKE ‘문자열’, IS NULL) → 논리 연산자 (AND → OR)

 

IS NULL vs IS NOT NULL

• IS NULL : NULL값이면 True 아니면 False (=Null인 것만 찾기)
• IS NOT NULL : 널이 아닌 경우를 찾기 위해 사용
• NULL과의 모든 사칙연산 결과는 NULL
• NULL값과 수치 연산은 NULL값을 리턴 / NULL값과 비교 연산은 거짓(False)를 리턴

 

부분 범위 처리

• ROWNUM (Oracle): SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호, 조건절 내에서 행의 개수를 제한하는 목적으로 사용함
• TOP (SQL Server): 출력 행의 수 제한 함수, ’TOP (N)’로 N개 행 출력, 개수 대신 비율로도 제한 가능 ※ ORDER BY절이 없으면 ROWNUM과 TOP의 기능이 같음

 

 

 

---

6. 함수

단일 행 함수

내장함수 중 파라미터가 하나~여러 개인 함수

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

 

 

문자형 함수

문자열 입력 시 문자열이나 숫자 반환

 

함수	설명
LOWER, UPPER	영어 소문자 / 대문자만 반환
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	지정된 요일 첫 날짜 출력

 

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

함수 설명

함수	설명
[Oracle] TO_NUMBER, TO_CHAR, TO_DATE	문자열을 숫자로, 숫자나 날짜를 문자열로, 문자열을 날짜로
[SQL Server] CAST, CONVERT	문자열을 숫자로, 숫자나 날짜를 문자열로, 문자열을 날짜로

 

NULL 관련 함수

 

함수	설명
NVL(칼럼,값)	NULL 값 변환
NVL2(칼럼,값,값)	NULL이면 앞의 값 아니면 뒤의 값 출력
NULLIF(값,값)	같으면 NULL 다르면 첫 값 출력
COALESCE(값,값,…)	NULL이 아닌 첫 값 출력
ISNULL(칼럼,값)	NULL이면 값으로 대치 아니면 칼럼 값 출력

 

데이터 변환

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

 

Case 절

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

-- Oracle에서만 사용되는 함수, 기준값n이면 출력값n 출력
DECODE (칼럼, 기준값1, 출력값1, …, 기본값)

 

 

 

---

7. Group By, Having 절

집계 함수(Aggregate Function)

여러 행들의 그룹이 모여서 그룹 당 단 하나의 결과를 돌려주는 다중행 함수 중 하나. SELECT 절, HAVING 절, ORDER BY 절에 사용 가능

 

집계 함수	설명
COUNT(*)	NULL 포함 행 수 출력
COUNT(표현식)	NULL 제외 행 수 출력
SUM, AVG, MAX, MIN, VARIAN, STDDEV	NULL 제외하고 연산 (↔ 숫자 연산은 NULL 출력)
ALL, DISTINCT	전체 출력, 중복 제외 출력

 

GROUP BY : 그룹핑 기준 설정, 앨리어스 사용 불가

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

 

HAVING

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

 

 

 

 

---

8. Order By 절

ORDER BY

특정 칼럼을 기준으로 정렬, 기본 정렬기준은 오름차순

ORDER BY에는 GROUP BY의 컬럼이나 Select의 컬럼만 사용 가능

-- SELECT 문의 실행 순서 및 설명
SELECT 칼럼명 AS “별명” -- 5. 도출된 결과를 출력/계산
FROM 테이블명 -- 1. 테이블에서
WHERE 조건식 -- 2. 조건에 안맞는 것 제거
GROUP BY 칼럼/표현식 -- 3. 행들을 그룹화
HAVING 조건식 -- 4. 그룹핑된 값의 조건에 맞는것만 도출
ORDER BY 칼럼/표현식 -- 6. 출력 결과를 정렬 후 출력

 

 

 

---

9. 조인 (JOIN)

조인

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

 

등가 조인(EQUI JOIN)

두 테이블의 칼럼 값이 정확히 일치하는 경우, 대부분 PK와 FK 관계를 기반으로 함. ‘=’ 연산자 사용

-- WHERE절 사용
SELECT 칼럼a, 칼럼b FROM 테이블1 A, 테이블2 B WHERE A.칼럼명 = B.칼럼명;

-- ON절 사용
SELECT 칼럼a, 칼럼b From 테이블1 A INNER JOIN 테이블2 B ON A.칼럼명 = B.칼럼명;

 

비등가 조인 (Non EQUI JOIN)

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