SQL 기본

SQL 문장들의 종류

명령어의 종류	명령어	설명
데이터 조작어 (DML)	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE라고도 한다.
	INSERT UPDATE DELETE	데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말한다. 예를 들어 데이터를 테이블에 새로운 행을 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어들을 DML이라고 부른다.
데이터 정의어(DDL)	CREATE ALTER DROP RENAME	테이브롹 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변형하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 한다.
데이터 제시어(DCL)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라고 부른다.
트랜잭션 제시어(TCL)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말한다.

테이블 칼럼에 대한 정의 변경

Oracle

ALTER TABLE 테이블명
MODIFY (
칼럼명1 데이터유형[DEFAULT 식] [NOT NULL],
칼럼명2 데이터유형[DEFAULT 식] [NOT NULL],
...);

SQL Server

ALTER TABLE 테이블명
ALTER (
칼럼명1 데이터유형[DEFAULT 식] [NOT NULL],
칼럼명2 데이터유형[DEFAULT 식] [NOT NULL],
...);

NULL

- 공백이나 숫자 0과는 전혀 다른값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다르다.

- ‘아직 정의되지 않은 미지의 값’이거나 ‘현재 데이터를 입력하지 못하는 경우’를 의미한다.

NULL의 특성

- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.

- 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.

- 널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다.

- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로, 문자 유형 데이터인 경우는 블랭크보다는 ‘x’ 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

NULL의 연산

- NULL 값과의 연산(+,-,*,/ 등)은 NULL 값을 리턴

- NULL 값과 비교 연산자 (=, >, ≥, <. ≤)은 거짓(FALSE)을 리턴

- 특정 값보다 크다, 적다라고 표현할 수 없음

제약조건의 종류

- PRIMARY KEY(기본키)

- UNIQUE KEY(고유키)

- NOT NULL

- CHECK

- FOREIGN KEY(외래키)

테이블 생성의 주의사항

- 테이블 명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.

- 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

- 한 테이블 내에서는 칼럼명을 중복되게 지정될 수 없다.

- 테이블 이름을 지정하고 각 칼럼들은 괄호 “( )”로 묶어 지정한다.

- 각 칼럼들은 콤마 “,”로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ”;”으로 끝난다.

- 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스내에서는 일관성 있게 사용하는 것이 좋다. (데이터 표전화 관점)

- 칼럼 뒤에 데이터 유형은 꼭 지정되어야한다.

- 테이블 명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

- 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.

- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

테이블의 불필요한 칼럼삭제

ALTER TABLE 테이블명
DROP COLUMN 삭제할 칼럼명;

테이블에 데이터를 입력하는 두 가지 유형

INSERT INTO 테이블명
(COLUMN_LIST) VALUES
(COLUMN_LIST에 넣을 VALUES_LIST);

INSERT INTO 테이블명
VALUES
(COLUMN_LIST에 넣을 VALUES_LIST);

INSERT 구문

SQL Server : IDENTITY [(seed, increment)]

- Seed : 첫번째 행이 입력될 때의 시작값

- 증가되는 값

- 해당 컬럼에 값을 넣을 경우 Error 발생 (Error 발생을 막기 위해서는 IDENTITY_INSERT를 OFF로 하면되나 이런 경우 IDENTITY를 쓴 의미가 없어짐)

CHECK

- 체크란 입력을 할 때, 해당 값이 조건에 부합하는지 확인하여 부합할 경우 입력을 하고, 합당하지 않을 경우 거부하는 제약조건입니다.

CREATE TABLE [테이블명] (
	[컬럼명] [데이터형식] [제약조건],
	CONSTRAINT [CHECK명] CHECK ([조건절])
);

입력된 데이터의 수정

UPDATE 테이블명
SET 수정되어야할 칼럼명 = 수정되기를 원하는 새로운 값;

테이블에 입력된 데이터 조회

SELECT [ALL/DISTINCT] 보고싶은 칼럼명1, 보고싶은 칼럼명2, ...
FROM 해당 칼럼들이 있는 테이블명;

#ALL : Default 옵션이므로 별도로 표시하지 않아도 된다. 중복된 데이터가 있어도 모두 출력한다.
#DISTINCT : 중복된 데이터가 있는 경우 1건으로 처리해서 출력한다.

DROP TABLE

- DDL

- Rollback 불가능

- Auto Commit

- 테이블이 사용했던 Storage를 모두 Release

- 테이블의 정의 자체를 완전히 삭제함

TRUNCATE TABLE

- DDL (일부 DML 성격 가짐)

- Rollback 불가능

- Auto Commit

- 테이블이 사용했던 Storage중 최초 테이블 생성시 할당된 Storage만 남기고 Release

- 테이블을 최초 생성된 초기상태로 만듬

- 데이터 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다.

- 데이터가 삭제되면 테이블의 용량은 초기화

- 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.

DELETE TABLE

- DML

- Commit 이전 Rollback 가능

- 사용자 Commit

- 데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음

- 데이터만 삭제, 테이블의 용량 감소 X

트랙잭션의 특징

- 트랜잭션은 데이터베이스의 논리적 연산자단위로서 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터 베이스 조작을 가리킨다.

- 원자성(atomicity) : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야한다. (All or Nothing)

- 일관성(consistency) : 트랜잭션이 실행되기전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

- 고립성(isolation) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

- 지속성(durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

ROLLBACK

- 테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수 있는데 데이터베이스에서는 롤백 기능을 사용한다.

- 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

BEGIN TRANSACTION

- 트랜잭션을 시작하고 COMMIT TRANSACTION(TRANSACTION은 생략 가능) 또는 ROLLBACK TRANSACTION(TRANSACTION은 생략 가능)으로 트랜잭션을 종료한다. (BEGIN TRAN 구문도 가능)

- ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

저장점(SAVEPOINT)

- 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

Oracle

SAVEPOINT SVPT1;
...
ROLLBACK TO SVPT1;

SQL Server

SAVE TRANSACTION SVTR1;
...
ROLLBACK TRANSACTION SVTR;

WHERE 절

- FROM 다음에 위치하며, 조건식은 아래의 내용으로 구성된다.

- 칼럼명 (보통 조건식의 좌측에 위치)

- 비교연산자

- 문자, 숫자, 표현식 (보통 조건식의 우측에 위치)

- 비교 칼럼명 (JOIN 사용시)

연산자의 우선순위

1. 괄호로 묶은 연산자

2. 부정 연산자(NOT)

3. 비교 연산자 (=, >, ≥, <. ≤)와 SQL 비교 연산자 (BETWEEN a AND b, IN (list), LIKE, IS NULL0

4. 논리 연산자 중 AND, OR의 순으로 처리

부정 비교 연산자

- !=, ^=, <> : 같지않다.

- NOT 칼럼명 = ~와 같지 않다.

- NOT 칼럼명 > : ~보다 크지 않다.

BETWEEN a AND b

: a와 b의 값 사이에 있으면 된다. (a와 b 값이 포함됨)

NOT

: 참이면 거짓으로 바꾸고 거짓이면 참으로 바꾼다.

IN (list)

: OR을 의미하며 리스트에 있는 값 중에서 어느 하나라도 일치하면 조회된다.

NOT IN (list)

SELECT * 
FROM a 
WHERE a.key NOT IN ( SELECT b.key FROM b )

- list와 불일치한 것을 조회한다.

- B 테이블을 먼저 접근

- B.key를 IN 리스트에 나열 후 a.key에 공급

- B 테이블 : ‘공급자 역할’

EXISTS

: 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다.

NOT EXISTS

SELECT * 
FROM a 
WHERE NOT EXISTS ( SELECT * FROM b WHERE a.key = b.key )

- NOT EXISTS는 동일한 튜플이 하나도 없어야 TRUE

- A 테이블을 먼저 접근

- 구해진 a의 row들을 NOT EXISTS절의 b테이블에서 필터링

- B 테이블 : ‘확인자 역할’

함수

- 함수는 벤더에서 제공하는 함수인 내장함수와 사용자가 정의할 수 있는 함수로 나눌 수 있다.

- 내장 함수는 다시 단일행 함수와 다중행 함수로 나눌 수 있으며, 다중행 함수는 집계함수, 그룹함수, 윈도우 함수로 구분된다.

내장함수

- 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분할 수 있다.

- 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용 가능하다.

- 1:M 관계의 두 테이블을 조인할 경우 M쪽에 다중행이 출력되므로 단일행 함수는 사용할 수 있다.

- 다중행 함수도 단일행 함수와 동일하게 단일 값만 반환한다.

DUAL 테이블의 특성

- 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.

- SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.

- DUMMY라는 문자열 유형의 칼럼에 ‘X’라는 값이 들어 있는 행을 1건 포함하고 있다.

ANY (서브쿼리)

- 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미

- 비교연산자로 “>”를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로 서브쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족

- SOME 과 동일

ALL (서브쿼리)

- 서브쿼리의 결과에 존재하는 모든값을 만족하는 조건을 의미

- 비교연산자로 “>”를 사용했다면 메인쿼리는 서브쿼리의 모든 값을 만족해야하므로 서브쿼리의 결과의 최대값보다 큰 모든 건이 조건을 만족

GROUP BY 문장

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

GROUP BY 절과 HAVING 절의 특성

- GROUP BY 저를 통해 소그룹별 기준을 정한후, SELECT 절에 집계 함수를 사용한다.

- 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.

- GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.

- 집계 함수는 WHERE절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY절보다 WHERE절이 먼저 수행된다.

- WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.

- HAVING절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.

- GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한조건을 두어 조건을 만족하는 내용만 출력한다.

- HAVING절은 일반적으로 GROUP BY 절 뒤에 위치한다.

ORDER BY 문장

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

ORDER BY 절 특징

- 기본적인 정렬 순서는 오름차순이다.

- 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다.

- 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력된다.

- Oracle에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치한다.

- 반면, SQL Server에서는 NULL 값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 먼저, 내림차순으로 정렬했을 경우에는 가장 마지막에 위치한다.

SELECT 문장 실행 순서

1. 발췌 대상 테이블을 참조한다. (FROM)

2. 발췌 대상 데이터가 아닌 것을 제거한다. (WHERE)

3. 행들을 소그룹화 한다. (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만 출력한다. (HAVING)

5. 데이터 값을 출력/계산한다. (SELECT)

6. 데이터를 정렬한다. (ORDER BY)

CASE 구문

CASE
	WHEN 조건1 THEN 출력값1
	WHEN 조건2 THEN 출력값2
	ELSE 출력값3
END

#ELSE 조건 생략했을 경우 NULL 값으로 출력된다.

EQUI JOIN 문장

SELECT 테이블명1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2

#WHERE 절에 JOIN 조건을 넣는다.

ANSI/ISO SQL 표준 EQUI JOIN 문장

SELECT 테이블명1.칼럼명, 테이블2.칼럼명, ...
FROM 테이블1
INNER JOIN 테이블2
ON 테이블1.칼럼명1 = 테이블2.칼럼명2

#ON 절에 JOIN 조건을 넣는다.

 두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN이라고 하며, 일반적인 경우 행들을 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립된다. 하지만, 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.

ROWNUM

- SELECT문의 결과에 대해서 논리적인 일렬번호를 부여한다.

- 조회되는 행 수를 제한할 대 많이 사용된다.

- 한 개의 행을 가지고 올 수 있으나, 여러 개의 행을 가지고 올 때는 인라인 뷰를 사용해야한다.

ROWID

- Oracle 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이다.

- ‘SELECT ROWID, EMPNO FROM EMP’와 같은 SELECT문으로 확인할 수 있다.

- 데이터가 어떤 데이터 파일, 어떤 블록에 저장되어 있는지를 알 수 있다.

TOP(Expression) [PERCENT] [WITH TIES]

- WITH TIES : ORDER BY 조건 기준으로 TOP N 의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+ 동일 정렬 순서데이터를 추가 반환하도록 지정하는 옵션 (마지막 기준 공통일 경우 모두 출력)