3-1. SQL 기본

박영빈·2023년 9월 1일

SQLD 이기적

SQL Developer

목록 보기

3/5

관계형 데이터베이스(Relation Database)

1970년 E.F.Codd 박사가 처음 소개한 데이터베이스
릴레이션 간의 조인 연산을 통해 합집합, 교집합, 차집합 등을 만들 수 있다.

데이터를 어떠한 형태로 사용하느냐에 따라 계층형, 네트워크형, 관계형으로 나뉜다.
계층형 데이터베이스는 트리 형테의 자료구조에 데이터를 관리하며, 1대N 관계 표현 가능
네트워크형 데이터베이스는 Owner-Member 형태로 데이터를 관리하며, 1대N, M대N 표현 가능
관계형 데이터베이스는 릴레이션에 데이터를 관리하며 릴레이션을 사용해 집합/관계 연산 가능

데이터베이스 관리 시스템(Database Management System)은 계층형, 네트워크형, 관계형 데이터베이스를 관리하는 소프트웨어를 의미한다.
현재 가장 많이 사용되는 것은 Oracle, MS-SQL, MySQL, Sybase 등이 있으며 모두 관계형 데이터베이스를 지원한다.

관계형 데이터베이스 집합 연산과 관계 연산

집합 연산
- 합집합(Union) : 두 개의 릴레이션을 하나로 합하는 것, 중복된 튜플은 한 번만 조회
- 차집합(Difference) : 본 릴레이션에는 존재하지만 다른 릴레이션에 존재하지 않는 것을 조회
- 교집합(Intersection) : 두 릴레이션 간의 공통된 것을 조회
- 곱집합(Cartesian Product) : 각 릴레이션에 존재하는 모든 데이터를 조합하여 연산
관계 연산
- 선택 연산(Selection) : 릴레이션에서 조건에 맞는 튜플만을 조회
- 투영 연산(Projection) : 릴레이션에서 조건에 맞는 속성만을 조회
- 결합 연산(Join) : 여러 릴레이션에서 공통된 속성을 사용해서 새로운 릴레이션을 생성
- 나누기 연산(Division) : 기준 릴레이션에서 나누는 릴레이션이 갖는 속성과 동일한 값을 갖는 튜플을 추출하고 나누는 릴레이션의 속성을 삭제하여 중복된 행을 제거

SQL(Structured Query Language) 종류

SQL은 데이터베이스의 데이터 구조를 정의, 조작, 제어할 수 있는 절차형+비절차형 언어
ANSI/ISO 표준을 준수하므로 데이터베이스 관리 시스템이 변경되어도 그대로 사용 가능

DDL(Data Definition Language) : 데이터베이스의 구조를 정의하는 언어(CREATE, ALTER, DROP, TRUNCATE 등)
DML(Data Manipulation Language) : 테이블에서 데이터를 입력, 수정, 조회, 삭제하는 기능(INSERT, UPDATE, DELETE, SELECT 등)
DCL(Data Control Language) : 데이터베이스 사용자에게 권한을 부여하거나 회수하는 기능(GRANT, REVOKE 등)
TCL(Transaction Control Language) : 트랜잭션을 제어하는 명령(COMMIT, ROLLBACK, SAVEPOINT 등)

트랜잭션(Transaction)

트랜잭션이란 데이터베이스의 작업을 처리하는 단위이다.

원자성(Atomicity) : 트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 함(ALL or Nothing), 즉 처리가 완전하지 않으면 실행되지 않은 상태와 같아야 함.
일관성(Consistency) : 트랜잭션의 실행결과로 데이터베이스의 상태가 모순되지 않아야 함
고립성(Isolation) : 트랜잭션 실행 중에 생성하는 중간결과는 다른 트랜잭션이 접근할 수 없음.
영속성(Durability) : 트랜잭션이 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 함

SQL문 실행 순서

Parsing(문법 확인 및 구문 분석) → Execution(옵티마이저가 수립한 계획에 따라 실행) → Fetch(데이터를 읽어서 전송)

DDL

테이블 생성

Create Table 문을 사용한다.

Create Table EMP
(
	empno number(10) primary key,
	ename varchar2(20),
	sal number(6)
);
# 다음과 같이 작성한다.

Create Table (테이블명)
(
	(칼럼명) (데이터 타입)(10) (제약조건),
	...
);

제약조건에는
- primary key : 기본키 지정(not null + unique)
- not null : 필수 입력 값 지정(null이 default)
- unique : 해당 테이블에서 값이 유일해야 함
- default “값” : 값을 입력하지 않을시 해당 값으로 초기화
- constraint : constraint 이름 primary key(컬럼1, 컬럼2) 과 같이 여러 컬럼을 기본키로 지정하거나 이름을 별도로 지정할 수 있음. unique나 다른 것도 적용 가능
- foreign key : foreign key(기존 컬럼) reference 참조테이블(참조컬럼) 과 같이 사용하여 외래키를 지정, constraint 물론 가능
  - CASCADE : ON DELETE CASCADE 지정 시, 기존 테이블 데이터 삭제 시 참조 테이블 튜플도 삭제됨

테이블 변경

테이블명 변경, 칼럼 추가, 변경, 삭제 등에 사용한다.
ALTER TABLE 문을 사용한다.

# 테이블명 변경
ALTER TABLE EMP
	RENAME TO NEW_EMP;

# 칼럼 추가
ALTER TABLE MP
	ADD (age number(2) default 1);

# 칼럼 변경
ALTER TABLE EMP
	MODIFY (ename varchar2(40) not null);

# 칼럼 삭제
ALTER TABLE EMP
	DROP COLUMN age;

# 칼럼명 변경
ALTER TABLE EMP
	RENAME COLUMN ename to new_ename;

테이블 삭제

DROP TABLE 문을 사용한다.
테이블의 구조와 데이터를 모두 삭제한다.

DROP TABLE EMP CASCADE CONSTRAINT;

CASCADE CONSTRAINT 는 해당 테이블을 외래키로 참조하는 모든 제약사항도 삭제한다.

뷰 생성과 삭제

뷰란 테이블로부터 유도된 가상의 테이블이다.
실제 데이터를 갖지 않고, 테이블을 원하는 칼럼만 조회할 수 있게 한다.
뷰는 Date Dictionary에 SQL문 형태로 저장되며 실행 시에 참조된다.
참조한 테이블이 변경되면 뷰도 변경된다.
뷰에 대한 입력, 수정, 삭제에는 제약이 존재한다.
특정 칼럼만 조회하도록 하여 보안성을 향상시킨다.
한번 생성한 뷰는 변경할 수 없고 삭제 후 재생성하여야 한다.

# 뷰 생성
CREATE VIEW T_EMP AS
	SELECT * FROM EMP;

# 뷰 조회
SELECT * FROM T_EMP; # 테이블 조회와 동일하게 사용

# 뷰 삭제
DROP VIEW T_EMP;

DML

INSERT문

테이블에 데이터를 입력하는 DML문이다.

INSERT INTO table (col1, col2, ...) VALUES(data1, data2, ...);

문자열 입력시에는 작은따옴표로 감싸야한다.
모든 칼럼에 대해 데이터를 입력하는 경우, 칼럼명 작성을 생략할 수 있다.
INSERT 문을 실행한다고 바로 데이터가 입력되는 것이 아니라 최종적으로 TCL문인 Commit문을 실행해야 반영된다.
- Auto Commit으로 설정된 경우, Commit을 실행하지 않아도 된다.

INSERT INTO DEPT_TEST
	SELECT * FROM DEPT;
# 이와 같이 DEPT의 모든 데이터를 조회하여 DEPT_TEST에 전부 바로 삽입할 수도 있다.

ALTER TABLE DEPT NOLOGGING;
# 데이터베이스에 데이터 입력시 로그파일에 그 정보를 기록하는데, Nologging 옵션을 통해
# 로그파일 기록을 최소화시켜 성능을 향상 시킬 수 있다.
# Buffer Cache 메모리 영역을 생략하고 기록한다.

UPDATE문

입력한 데이터의 값을 수정하려면 UPDATE문을 사용한다.
원하는 조건으로 데이터를 검색하여 해당 데이터를 수정할 수 있다.
- 조건문이 없다면 테이블의 모든 데이터를 수정하므로 유의할 것

UPDATE EMP SET ENAME = '조조'
	WHERE EMPNO = 100;

DELETE문

원하는 조건을 검색하여 해당되는 행을 삭제한다.
- 마찬가지로 조건을 검색하지 않으면 모든 데이터를 삭제하므로 유의할 것
DELETE로 데이터를 지운다고 테이블의 용량이 감소하지는 않는다.

DELETE FROM EMP
	WHERE EMPNO = 100;

테이블의 용량을 줄이려면 TRUNCATE TABLE 테이블명; 을 사용해야 한다.

SELECT문

데이터를 조회하기 위해 사용한다.

SELECT * FROM EMP
	WHERE 사원번호 = 1000;

# 특정 칼럼만 출력
SELECT EMPNO, ENAME FROM EMP;

# 특정 문자 결함
SELECT ENAME || '님' FROM EMP; # 꽁치님 <- 이렇게 출력됨

ORDER BY : 정렬하여 출력할 때 사용한다.
- ASCE(오름차순)과 DESC(내림차순)이 존재하며 default는 오름차순이다.
- 이 작업은 데이터베이스 메모리를 많이 사용한다. 따라서 대량의 데이터 정렬 시 성능 저하가 발생한다.
- Oracle 데이터베이스에서는 메모리 내부의 SORT_AREA_SIZE를 사용하는데 이 부분이 너무 작으면 성능 저하가 발생한다.
- 정렬을 회피하기 위해 인덱스를 생성할 때 사용자가 원하는 형태로 생성해야 한다.

SELECT * FROM EMP
	ORDER BY ENAME, SAL DESC;
# ENAME으로 오름차순 정렬하고 SAL로 내림차순 정렬한다.

Index를 사용한 정렬 회피?

DISTINCT와 Alias

# DISCTINCT문은 칼럼명 앞에 지정하여 중복된 데이터를 한 번만 조회한다.
# 10, 10, 20, 20, 20, 30 => 10, 20, 30
SELECT DISTINCT DEPTNO FROM EMP;

# Alias는 테이블명이나 칼럼명이 길 때 별칭으로 사용하게 한다.
SELECT ENAME AS "이름" FROM EMP a
	WHERE a.EMPNO = 1000;
# 출력 결과에서 ENAME은 이름으로 출력된다.

WHERE문

비교 연산자 : =, <, >, ≤, ≥
부정 비교 연산자 : ≠, ^=, <>, NOT
논리 연산자 : AND, OR, NOT
SQL 연산자 : LIKE ‘%비교문자열%’, BETWEEN A AND B, IN (list), IS NULL

LIKE문 : 와일드카드를 사용해서 데이터를 조회한다.

% : 어떤 문자를 포함한 모든 것을 조회 → ‘박%’은 박영빈, 박수 등 모두 조회

_ : 한 개의 단일 문자를 의미

SELECT * FROM EMP WHERE ENAME LIKE 'test%' # test로 시작하는 ENAME을 조회

SELECT * FROM EMP WHERE ENAME LIKE '%1' # 1로 끝나는 ENAME을 조회

SELECT * FROM EMP WHERE ENAME LIKE '%est%' # 가운데 est가 들어가는 ENAME을 조회

SELECT * FROM EMP WHERE ENAME LIKE 'test_' # test로 시작하고 한 글자만 더 있는 ENAME을 조회

BETWEEN문 : 지정된 범위에 있는 값을 조회한다.

SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000; # 급여가 1000~2000인 직원 조회

SELECT * FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000; # 급여가 1000~2000가 아닌 직원 조회도 가능

IN : OR의 의미를 가져서 하나의 조건만 만족해도 조회가 된다.

SELECT * FROM EMP WHERE JOB IN ('CLERK', 'MANAGER');
# 둘 중 하나만 해당되도 조회가 된다.

SELECT * FROM EMP WHERE (JOB, ENAME) IN (('CLERK', 'test1'), ('MANAGER', 'test4'));
# 이렇게 묶어서도 검사 가능

NULL
- 성질 : 모르는 값, 값의 부재, NULL과 숫자 혹은 날짜를 더하면 NULL, 비교시 알수없음 반환
- NULL 조회 시 IS NULL 혹은 IS NOT NULL을 사용해야 한다.
- NVL(Oracle) : NULL이면 다른 값으로 바꾸는 함수, NVL(AGE, 0) → null이면 0으로 교체
- NVL2(Oracle) : NVL과 Decode를 합쳐놓은 함수, NVL2(AGE, 1, 0) → null이면 0, 아니면 1 반환
- NULLIF(Oracle, MS-SQL, MySQL) : 두 값이 같으면 NULL 아니라면 첫 번째 값 반환하는 함수, NULLIF(exp1, exp1) → 두 값이 같으면 NULL, 다르면 exp1를 반환
- COALESCE(Oracle, MS-SQL) : NULL이 아닌 최초 값 반환하는 함수, COALESCE(exp1, exp2, exp3, …) → NULL이 아닌 최초 값 반환

GROUP문

테이블에서 소규모 행을 그룹화하여 합계, 평균, 최솟값, 최대값 등을 계산할 수 있다.
HAVING구에 조건문을 사용한다.
Grouping된 결과에 대한 조건문을 사용한다.

SELECT DEPTNO, SUM(SAL) FROM EMP
	GROUP BY DEPTING;
# DEPTNO로 그룹을 만들고 각 그룹마다 SAL의 합계를 구한다.

SELECT DEPTNO, SUM(SAL) FROM EMP
	GROUP BY DEPTING; HAVING SUM(SAL) > 10000;
# 급여합계가 10000 이상인 결과만 조회한다.

집계함수 종류 : COUNT(개수), SUM(합), AVG(평균), MAX/MIN(최대/최소), STDDEV(표준편차), VARIANCE(분산)

# 부서(DEPTNO)별, 관리자(MGR)별 급여평균 계산
SELECT DEPTNO, MGR, AVG(SAL) FROM EMP
	GROUP BY DEPTNO, MGR;

# 직업별 급여합계 중 급여합계가 1000이상인 직업
SELECT JOB, SUM(SAL) FROM EMP
	GROUP BY JOB HAVING SUM(SAL);

# 사원번호 1000~1003번의 부서별 급여합계
SELECT DEPTNO, SUM(SAL) FROM EMP
	WHERE EMPNO BETWEEN 1000 AND 1003
	GROUP BY DEPTNO;

SELECT문 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

명시적 형변환과 암시적 형변환

암시적 형변환 : 비교와 같은 작업에서 두 데이터 타입이 다르다면 시스템이 자동으로 형변환
명시적 형변환 : 개발자가 직접 형변환을 하는 것
- TO_NUMBER(문자열) : 문자열을 숫자로 변환
- TO_CHAR(숫자 혹은 날짜, [FORMAT]) : 숫자 혹은 날짜를 지정된 FORMAT의 문자열로 변환
- TO_DATE(문자열, FORMAT) : 문자열을 지정된 FORMAT의 날짜형으로 변환

내장 함수

DUAL 테이블 : Oracle이 자동으로 생성해주는 더미테이블, 테스트할 때 사용 가능
문자열 함수
- ASCII(문자) : 문자 혹은 숫자를 ASCII 코드 값으로 변환
- CHR/CHAR(ASCII) : ASCII코드 값을 문자로 변환
- SUBSTR(문자열, M, N) : 문자열에서 M번째 위치에서 N개를 자름
- CONCAT(문자열1, 문자열2) : 문자열1 뒤에 문자열2를 결합
- LOWER(문자열), UPPER(문자열) : 영문자를 소문자 혹은 대문자로 변환
- LENGTH/LEN(문자열) : 공백을 포함한 문자열의 길이를 반환
- LRTIM, RTRIM, TRIM(문자열, 지정문자) : 왼쪽/오른쪽/양쪽에서 지정문자를 삭제함, 지정 없을 시 공백을 지운다.
날짜형 함수
- SYSDATE : 오늘 날짜를 날짜 타입으로 알려준다.
- EXTRACT(YEAR FROM SYSDATE) : 날짜에서 년, 월, 일을 조회한다.
숫자형 함수
- ABS(숫자) : 절대값을 반환
- SIGN(숫자) : 양수, 음수, 0을 구별
- MOD(숫자1, 숫자2) : 숫자1을 숫자2로 나눈 나머지를 반환
- CEILING/FLOOR(숫자) : 숫자의 천장함수, 바닥함수
- ROUND(숫자, M) : 숫자를 소수점 M의 자리에서 반올림
- TRUNC(숫자, M) : 소수점 m자리에서 버림

DECODE문과 CASE문

DECODE문 : 해당 문장으로 IF문을 구현가능

DECODE(EMPNO, 1000, 'TRUE', 'FALSE');
# EMPNO가 1000이면 TRUE를, 아니면 FALSE를 출력

CASE문 : IF-THEN ~ELSE 문처럼 사용 가능

SELECT CASE
	WHEN EMPNO = 1000 THEN 'A'
	WHEN EMPNO = 1001 THEN 'B'
	ELSE 'C'
	END
FROM ENP;

ROWNUM과 ROWID

ROWNUM은 ORACLE 데이터베이스의 SELECT 문 결과에 대해 논리적인 일련번호를 부여한다.
조회되는 행 수를 제한할 때 많이 사용되며 ROWNUM을 사용해서 페이지 단위 출력을 하고자 한다면 인라인 뷰를 사용해야 한다.

SELECT * FROM (SELECT ROWNUM list, ENAME FROM EMP)
	WHERE list <= 5;
# ROWNUM이 5개까지 조회

ROWID : ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값
- 오브젝트 번호(1~6) : 오브젝트 별로 갖는 유일한 값
- 상대 파일 번호(7~9) : 테이블스페이스에 속해 있는 데이터 파일에 대한 상대 파일번호
- 블록 번호(10~15) : 데이터 파일 내부에서 어느 블록에 데이터가 있는지 알려준다
- 데이터 번호(16~18) : 데이터 블록에 데이터가 저장되어 있는 순서

WITH구문

WITH는 서브쿼리를 이용해서 뷰처럼 사용할 수 있는 구문

WITH VIEWDATA AS (SELECT * FROM EMP UNION ALL SELECT * FROM EMP)
# 뷰처럼 임시 테이블을 만든다.
SELECT * FROM VIEWDATA WHERE EMPNO = 1000;

DCL

GRANT

데이터베이스 사용자에게 권한을 부여한다. (연결, 입력, 수정, 삭제, 조회)
권한 : SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL

GRANT SELECT, INSERT, UPDATE ON EMP TO PYB;
# PYB 유저에게 EMP 테이블에 대해 조회, 삽입, 수정 권한을 줌

GRANT SELECT, INSERT, UPDATE ON EMP TO PYB WITH GRANT OPTION;
# 권할을 부여할 수 있는 권한을 부여한다.

GRANT SELECT, INSERT, UPDATE ON EMP TO PYB WITH ADMIN OPTION;
# 테이블에 대한 모든 권한을 부여한다.

REVOKE

데이터베이스 사용자에게 부여된 권한을 회수한다.

REVOKE
 previleges ON object FROM user;

TCL

COMMIT

INSERT, UPDATE, DELETE문으로 변경한 데이터를 데이터베이스에 반영한다.
COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제된다.
COMMIT이 완료되면 다른 모든 데이터베이스 사용자는 변경된 데이터를 조작 가능하다.
COMMIT을 실행하면 하나의 트랜잭션 과정을 종료한다.
COMMIT;

ROLLBACK

ROLLBACK을 실행하면 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료한다.
단, 이전에 COMMIT한 곳까지만 복구한다.
ROLLBACK이 실행되면 LOCK이 해제되고, 다른 사용자도 데이터베이스를 조작 가능하다.

SAVEPOINT

트랜잭션을 작게 분할하여 관리하는 것으로, 지정 위치 이후의 트랜잭션만 ROLLBACK이 가능해진다.
SAVEPOINT <SAVEPOINT 이름>
ROLLBACK <SAVEPOINT 이름>

박영빈

안녕하세요<br>반가워요<br>안녕히가세요

이전 포스트

2. 데이터 모델링의 이해

다음 포스트