SQL

𝐝𝐚𝐭𝐚𝐛𝐚𝐬𝐞
/
𝐬𝐪𝐥 𝐝𝐚𝐭𝐚𝐛𝐚𝐬𝐞
/👩🏻‍💻 SQLD 자격증 오답노트
/
SQL
Search
Duplicate
Share
SQL
태그
SQLD
DDL
DML
DCL
TCL
SQL
SQL 은 관계형 데이터 베이스에 대해서 데이터의 구조를 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차형 + 비 절차형 언어이다.
SQL 문의 실행 순서
•
파싱 → 실행 → 인출
•
파싱(parsing)
◦
SQL 문의 문법을 확인하고 구문 분석한다.
◦
구문 분석한 SQL 문은 Library Cache 에 저장한다.
•
실행(execution)
◦
옵티마이저가 수립한 실행 계획에 따라 SQL 을 실행한다.
•
인출(fetch)
◦
데이터를 읽어서 전송한다.
DDL
DDL
•
관계형 데이터 베이스의 구조를 정의하는 언어이다. 
•
테이블 생성 / 변경 / 삭제 …
 
CREATE
•
새로운 테이블 생성
•
CREATE TABLE ‘테이블명’ ();
•
데이터 타입
NUMBER
숫자형 데이터 타입
VARCHAR2
가변길이 문자열
CHAR
데이터 타입을 고정된 크기의 문자로 지정
DATE
날짜형 타입으로 지정할 때 사용한다.
 NUMBER(10, 2) : 소수점 둘째 자리까지 저장
SYSDATE : 오늘의 날짜를 조회한다. 
※ DEFAULT 옵션을 사용하면 오늘 날짜를 기본값으로 지정할 수 있다.
•
CASCADE 
◦
테이블 생성시 사용할 수 있는 옵션. 참조 관계가 있을 경우 참조되는 데이터를 자동으로 반영할 수 있다.
◦
마스터 테이블 생성 후 다음 테이블을 생성할 때 ON DELETE CASCADE 옵션을 사용한다.
◦
이 후 특정 데이터를 삭제하면 두 테이블의 데이터가 전부 삭제된다. ⇒ 참조 무결성 
ALTER 
•
생성된 테이블 변경
•
테이블명 변경 
ALTER TABLE '테이블명'
	RENAME TO NEW '테이블명';
SQL
복사
•
칼럼 추가
ALTER TABLE '테이블명'
	ADD (AGE NUMBER(2) DEFAULT 1);
SQL
복사
•
칼럼 변경
ALTER TABLE '테이블명'
	MODIFY (ENAME VARCHAR2(40) NOT NULL);
SQL
복사
•
칼럼 삭제
ALTER TABLE '테이블명'
	DROP COLUMN AGE;
SQL
복사
•
칼럼명 변경
ALTER TABLE '테이블명'
	RENAME COLUMN ENAME TO NEW_ENAME;
SQL
복사
DROP 
•
해당 테이블 삭제, 데이터 구조 뿐만 아니라 저장된 데이터도 모두 삭제된다.
•
CASCADE CONSTRAINT 옵션을 사용할 수 있다.
뷰의 생성과 삭제
•
뷰는 테이블로부터 유도된 가상의 테이블이다.
•
실제 데이터를 가지고 있지 않고 특정 테이블을 참조해서 원하는 칼럼만 조회할 수 있다.
•
뷰는 데이터 딕셔너리에 SQL 형태로 저장하되 실행시에 참조된다.
•
참조한 테이블이 변경되면 뷰도 변경된다.
•
뷰에 대한 입력, 수정, 삭제에는 제약이 있다.
•
특정 칼럼만 조회시켜 보안성을 향상 시킨다.
•
한번 생성된 뷰는 변경할 수 없고, 삭제 후 재생성 해야한다. (ALTER 문 사용 불가)
•
뷰의 삭제에는 DROP  VIEW 를 사용하지만 참조한 테이블이 삭제되지는 않는다.
•
장점
◦
보안기능
◦
데이터 관리 간단
◦
SELECT 문이 간단해진다.
◦
하나의 테이블에 여러개의 뷰 생성 가능
•
단점
◦
독자적인 인덱스 가질 수 없다
◦
삽입, 수정, 삭제 연산 제약
◦
데이터 구조 변경 불가
DML
DML
•
테이블에서 데이터를 입력, 수정, 삭제, 조회한다.
INSERT
INSERT INTO '테이블명' ('칼럼1', '칼럼2') VALUES ('값1', '값2');

// 생략버전 (모든 칼럼의 값을 입력할 때)
INSERT INTO '테이블명' VALUES ('값1', '값2');
SQL
복사
•
INSERT 문을 실행 후 최종적으로 데이터를 저장하려면 COMMIT 을 실행해야 한다.
•
만약 AUTO COMMIT 으로 설정된 경우에는 하지 않아도 된다.
SELECT 문으로 입력
// SELECT 문을 사용하여 데이터 조회 후 해당 테이블에 바로 삽입이 가능하다.
INSERT INTO '테이블명'
	SELECT * FROM '테이블명';
SQL
복사
Nologging 사용
•
데이터 베이스에 데이터를 입력하면 로그파일에 그 정보를 기록한다.
•
CHECK POINT 라는 이벤트가 발생하면 로그파일의 데이터를 데이터 파일에 저장한다.
•
NOLOGGING 옵션은 로그파일의 기록을 최소화시켜서 입력 시 성능을 향상시킨다.
•
BUFFER CACHE 라는 메모리 영역을 생략하고 기록한다.
ALTER TABLE '테이블명' NOLOGGING;
SQL
복사
UPDATE
•
입력된 데이터의 값을 수정
•
원하는 조건으로 데이터를 검색해서 해당 데이터 수정
 조건문을 입력하지 않으면 모든 데이터가 수정되므로 유의해야한다.
UPDATE '테이블명'
	SET '칼럼명' = '값'
	WHERE '칼럼명' = '값';
SQL
복사
DELETE
•
원하는 조건을 검색해서 해당되는 행을 삭제한다.
•
데이터를 삭제한다고 해서 테이블의 용량이 초기화되지는 않는다.
 조건문을 입력하지 않으면 모든 데이터가 삭제되므로 유의해야한다.
DELETE FROM '테이블명'
	WHERE '칼럼명' = '값';
SQL
복사
데이터의 삭제
•
ORACLE 데이터베이스는 저장공간을 할당할 때 EXTENT 단위로 할당하며, 데이터가 입력되면 EXTENT 에 저장된다. 만약 이 크기가 MAX_EXTENTS 를 넘어서게 되면 용량 초과 오류가 발생한다. 
•
DELETE 문은 삭제 여부만 표시하기 때문에 용량은 초기화되지 않는다. 테이블의 용량을 초기화하기 위해서는 TRUNCATE TABLE ‘테이블명’ 을 사용해야한다.
SELECT
•
SELECT 문은 특정 칼럼이나 특정 행을 조회할 수 있다.
SELECT ENAME || '님' FROM EMP;

// EMP 테이블의 모든 행에서 ENAME 칼럼을 조회한다.
// 단, ENAME 칼럼 뒤에 '님' 이라는 문자를 결합한다.
SQL
복사
ORDER BY
•
정렬 시점은 모든 실행이 끝난 후에 데이터를 출력해주기 바로 전!
•
데이터베이스 메모리를 많이 사용한다. 때문에 대량의 데이터 정렬은 성능 저하를 발생 시킨다.
•
정렬을 하기 위해 메모리 내부에 할당된 SORT_AREA_SIZE 를 사용한다. 만약 이 값이 너무 작으면 성능 저하가 발생한다.
•
정렬을 피하기 위해서 인덱스를 생성할 때 사용자가 원하는 형태로 생성해야 한다.
•
특별한 지정이 없으면 오름차순 정렬이다.
인덱스를 사용한 정렬 회피
기본적으로 테이블을 생성하고 SELECT 문을 실행하면, 기본키를 기준으로 오름차순 정렬되어서 조회된다. 내림차순으로 조회하고 싶다면 아래와 같이 작성하자.
SELECT /*+ INDEX_DESC('별칭') */
FROM '테이블명' '별칭';
SQL
복사
DISTINCT 와 ALIAS
•
칼럼명 앞에 지정하여 중복된 데이터를 한번만 조회하게 한다.
•
테이블명이나 칼럼명이 너무 길어서 간략하게 할 때 사용한다.
WHERE
•
비교 연산자
=, <, ≤, >, ≥
•
부정 비교 연산자
≠, ^=, <>, NOT 칼럼명=, NOT 칼럼명>
•
논리연산자
◦
AND : 조건을 모두 만족해야 참이 된다.
◦
OR : 조건 중 하나만 만족해도 참이 된다.
◦
NOT : 참이면 거짓으로 바꾸고 거짓이면 참으로 바꾼다.
•
SQL 연산자
◦
LIKE ‘%비교 문자열%’ : 비교 문자열을 조회한다. ‘%’ 는 모든값을 의미한다.
◦
BETWEEN A AND B : A 와 B 사이의 값을 조회한다.
◦
IN
▪
OR 을 의미하며, LIST 값 중에 하나만 일치해도 조회된다.
▪
괄호를 사용하여 여러 개의 칼럼에 대한 조건을 지정할 수 있다.
SELECT * FROM '테이블명'
	WHERE ('칼럼1', '칼럼2')
	IN (('값1', '값2'), ('값1', '값2'));
SQL
복사
◦
IS NULL : NULL 값을 조회한다.
 NULL
•
모르는 값을 의미한다.
•
값의 부재를 의미한다.
•
숫자 혹은 날짜를 더하면 NULL 이 된다.
•
어떤 값과 비교할 때 ‘알 수 없음’ 이 반환된다.
•
관련함수
// NVL
NVL('칼럼', 0) => 칼럼 값이 NULL 이면 0 으로 바꾼다.

// NVL2
NVL2('칼럼', 1, 0) => 칼럼 값이 NULL 아니면 1 을, NULL 이면 0 을 반환.

// NULLIF (ORACLE, MS-SQL, MYSQL)
NULLIF('값1', '값2') => 두개의 값이 같으면 NULL 을 아니면 '값1' 을 반환.

// COALESCE (ORACLE, MS-SQL)
COALESCE('값1', '값2', '값3')
=> 값1 이 NULL 이 아니면 값1 을 반환. 
	 NULL 이면 그 뒤의 값을 판단하여 값을 반환한다.
SQL
복사
•
부정 SQL 연산자
◦
NOT BETWEEN A AND B
◦
NOT IN
◦
IS NOT NULL
와일드 카드
•
% : 어떤 문자를 포함한 모든 것
•
_ : 한 개의 단일 문자
GROUP BY
•
테이블에서 소규모 행을 그룹화 하여 합계, 평균, 최댓값, 최솟값 등을 계산한다.
•
HAVING 구에 조건문 사용 (WHERE 절을 사용하게 되면 GROUP BY 대상에서 제외됨)
•
GROUPING 된 결과에 대한 조건문을 사용
•
ORDER BY 를 사용해서 정렬 가능
•
집계함수
COUNT()
행 수를 조회 (NULL 제외)
MAX() / MIN()
최댓값과 최솟값
SUM()
합계 계산
STDDEV()
표준편차
AVG()
평균 계산
VARIANCE()
분산계산
SELECT 문 실행 순서
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
명시적 형변환과 암시적 형변환
•
명시적 형변환
◦
형변환 함수를 사용해서 데티터 타입 일치 시키는 것
◦
형변환 함수
TO_NUMBER(문자열)
문자열을 숫자로 변환
TO_CHAR(숫자 혹은 날짜, [FORMAT])
숫자 혹은 날짜를 지정된 FORMAT 의 문자로 변환
TO_DATE(문자열, FORMAT)
문자열을 지정된 FORMAT 의 날짜형으로 변환한다.
•
암시적 형변환
DBMS 가 자동으로 형변환 하는 것.
 인덱스 컬럼에 암시적 형변환이 발생하면 인덱스를 사용할 수 없다. 이는 명시적 형변환을 사용함으로써 해결이 가능하다.
문자열 함수
•
ASCII(문자) : 문자 혹은 숫자를 ASCII 코드값으로 변환한다.
•
CHR / CHAR(ASCII 코드값)
◦
ASCII 코드 값을 문자로 변환한다.
◦
ORACLE 은 CHAR 을, MSSQL, MYSQL 은 CHAR 사용
•
SUBSTR(문자열, M, N) : 문자열에서 M 번째 위치부터 N 개 자른다.
•
CONCAT(문자열1, 문자열2)
◦
문자열 결합
◦
ORACLE 은 ||, MYSQL 은 + 을 사용할 수 있다.
•
LOWER / UPPER : 영문자를 소문자 / 대문자로 변환한다.
•
LENGTH 혹은 LEN(문자열) : 공백을 포함해서 문자열의 길이를 알려준다.
•
LTRIM / RTRIM / TRIM(문자열, 지정문자)
◦
왼쪽 / 오른쪽 / 전부 에서 지정된 문자를 삭제한다.
◦
지정문자가 없으면 공백 삭제
날짜형 함수
•
SYSDATE
오늘의 날짜를 날짜 타입으로 알려준다.
•
EXTRACT(YEAR FROM SYSDATE)
날짜에서 년, 월, 일을 조회한다.
숫자형 함수
ABS(숫자)
절댓값
CEIL/CEILING(숫자)
올림
SIGN(숫자)
양수, 음수, 0 구별
FLOOR(숫자)
내림
MOD(숫자1, 숫자2)
숫자1을 숫자2로 나누어 나머지를 계산한다. (= %)
ROUND(숫자, M)
소수점 M 자리에서 반올림
TRUNC(숫자, M)
소수점 M 자리에서 삭
DECODE 와 CASE
•
DECODE (= IF)
DECODE ('칼럼', '값', '참일때 반환할 값', '거짓일때 반환할 값')
SQL
복사
•
CASE (= IF ~THEN ~ELSE-END)
CASE [EXPRESSION]
	WHEN CONDITION_1 THEN RESULT_1
	WHEN CONDITION_2 THEN RESULT_2
	...
	WHEN CONDITION_N THEN RESULT_N
	ELSE RESULT
END
SQL
복사
ROWNUM 과 ROWID
•
ROWNUM
// 한 행을 조회
SELECT * FROM EMP
WHERE ROWNUM <= 1;

// 그 이상의 행을 조회하기 위해서는 인라인뷰를 사용하고 별칭을 사용해야 한다.
// 인라인 뷰 : FROM 절에 SELECT 문을 사용하는 경우를 말한다.
// SQLSERVER 에서는 TOP 을 사용하고, MYSQL 에서는 LIMIT 을 사용한다.
SELECT *
FROM (
	SELECT ROWNUM LIST, ENAME
	FROM EMP
	)
	WHERE LIST <= 5;
SQL
복사
•
ROWID
◦
ORACLE 데이터베이스 내애서 데이터를 구분할 수 있는 유일한 값이다.
◦
SELECT 문으로 확인할 수 있다.
◦
데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는 지 알 수 있다.
WITH
•
서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다.
•
별칭을 지정할 수 있다.
•
옵티마이저는 인라인뷰나 임시 테이블로 판단한다.
WITH W_EMP AS (
SELECT * 
 FROM EMP
	 WHERE DEPTNO = 30
);

SELECT * FROM W_EMP;
SQL
복사
DCL
DCL
•
데이터 베이스 사용자에게 권한을 부여하거나 회수한다
•
GRANT, REVOKE 문이 있다.
•
DCL → DDL → DML, TCL
GRANT
GRANT 'PRIVILEGES' ON '테이블명' TO '데이터베이스사용자';
SQL
복사
•
권한종류
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL
•
WITH GRANT OPTION
◦
특정 사용자에게 권한을 부여할 수 있는 권한을 ㄹ부여한다.
◦
A → B → C 권한 취소하면 모든 권한이 회수된다.
•
WITH ADMIN OPTION
◦
테이블에 대한 모든 권한을 부여한다
◦
A → B → C 권한 취소하면 B 사용자 권한만 취소
REVOKE
REVOKE 'PRIVILEGES' ON '테이블명' FROM '데이터베이스사용자';
SQL
복사
TCL
TCL
•
트렌잭션을 제어하는 명령어이다.
•
COMMIT, ROLLBACK, SAVEPOINT 문이 있다.
트랜잭션의 특성
•
원자성
◦
연산의 전부가 실행되거나 전혀 실행되지 않아야 한다.
◦
트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야함
•
일관성
◦
트랜잭션 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다.
◦
트랜잭션 실행 후에도 일관성이 유지되어야 한다.
•
고립성
◦
트랜잭션 실행 중에 생성하는 연산의 중간결과는 다른 트랜잭션이 접근할 수 없다.
◦
즉, 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다.
•
영속성
트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 한다.
TCL
COMMIT
•
변경 이전 데이터는 COMMIT 을 하면 잃어버린다.
•
다른 모든 데이터베이스 사용자는 변경된 테이블을 볼 수 있다.
•
COMMIT 이 완료되면 LOCK 이 해제되고, 변경된 데이터를 조작할 수 있다.
•
COMMIT 을 실행하면 하나의 트랜잭션 과정을 종료한다.
ROLLBACK
•
데이터에 대한 변경 사용를 모두 취소하고 트랜잭션을 종료한다. 단, 이전에 COMMIT 한 곳 까지만 복구한다.
•
ROLLBACK 을 실행하면 LOCK 이 해제되고, 데이터를 조작할 수 있다.
•
SAVEPOINT 와 관계없이 데이터의 모든 변경사항을 저장하지 않는다.
SAVEPOINT
•
트랜잭션을 작게 분할하여 관리하는 것이다.
•
SAVEPOINT 지정 : SAVEPOINT <SAVEPOINT 명>
•
SAVEPOINT 롤백 : ROLLBACK TO <SAVEPOINT 명>
NUMBER	숫자형 데이터 타입
VARCHAR2	가변길이 문자열
CHAR	데이터 타입을 고정된 크기의 문자로 지정
DATE	날짜형 타입으로 지정할 때 사용한다.
COUNT()	행 수를 조회 (NULL 제외)	MAX() / MIN()	최댓값과 최솟값
SUM()	합계 계산	STDDEV()	표준편차
AVG()	평균 계산	VARIANCE()	분산계산
TO_NUMBER(문자열)	문자열을 숫자로 변환
TO_CHAR(숫자 혹은 날짜, [FORMAT])	숫자 혹은 날짜를 지정된 FORMAT 의 문자로 변환
TO_DATE(문자열, FORMAT)	문자열을 지정된 FORMAT 의 날짜형으로 변환한다.
ABS(숫자)	절댓값	CEIL/CEILING(숫자)	올림
SIGN(숫자)	양수, 음수, 0 구별	FLOOR(숫자)	내림
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지를 계산한다. (= %)	ROUND(숫자, M)	소수점 M 자리에서 반올림
		TRUNC(숫자, M)	소수점 M 자리에서 삭