[SQLD 이론정리] Ⅱ. SQL 기본 및 활용 1

제 1장. SQL 기본

SQL

SQL 문장들의 종류

데이터 조작어

• 비절차적 데이터 조작어(DML)는 사용자가 무슨 데이터를 원하는 지만 명세함
• 절차적 데이터 조작어는 어떻게 데이터를 접근해야 하는지 명세함
• 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(Data Sub Language)라고 한다.

 

DDL

CREATE

ON DELETE [옵션] CASCADE, RESTRICT, SET NULL, SET DEFAULT, NO ACTION

Insert Action	설명
Automatic	Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력
Dependent	Master 테이블에 PK가 존재할 때만 Child 입력 허용
SET NULL	Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리
SET DEFAULT	Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력
NO ACTION	참조무결성을 위반하는 입력 액션을 취하지 않음

Delete(/Modify) Action	설명
CASCADE	Master 삭제 시 Child 같이 삭제
RESTRICT	Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
SET NULL	Master 삭제 시 Child 해당 필드 Null
SET DEFAULT	Master 삭제 시 Child 해당 필드 Default 값으로 설정
NO ACTION	참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

테이블명과 칼럼명은 반드시 문자로 시작해야 하며 A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

 

제약조건의 종류

ORACLE의 CHECK 조건을 만족하지 못할 경우 에러가 나나 NULL은 무시됨

 

* SQL SERVER : IDENTITY [ ( seed , increment ) ]
- SEED : 첫번째 행이 입력될 때의 시작값
- increment : 증가되는 값
- 해당 컬럼에 값을 넣을 경우 Error 발생

 

ALTER TABLE

명령어 전체 정리

DROP COLUMN (칼럼 삭제)

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

MODIFY COLUMN (칼럼 수정)

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

[SQL Server]
ALTER TABLE 테이블명 ALTER 컬럼명1 데이터유형 [DEFAULT 식] [NOT NULL];
ALTER TABLE 테이블명 ALTER 컬럼명2 데이터유형 [DEFAULT 식] [NOT NULL];

SQL Server에서는 여러개의 컬럼을 동시에 수정하는 구문은 지원하지 않는다.

또한 SQL Server에서는 괄호를 사용하지 않는다.

NOT NULL 구문을 지정하지 않으면, 기존의 NOT NULL 제약조건이 NULL로 변경되므로 주의해야 한다.

 

ADD CONSTRAINT

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);
[예제] ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);
[예제] ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID);

 

RENAME TABLE

[ANSI 표준 기준/Oracle]
RENAME 변경전 테이블명 TO 변경후 테이블명;
[SQL Server]
sp_rename 변경전 테이블명, 변경후 테이블명;

TRUNCATE TABLE

TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE 을 실행하면 된다.

DML

DELETE

테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 삭제된 데이터를 로그로 저장하는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE 을 권고한다. 단, TRUNCATE TABLE 의 경우 삭제된 데이터의 로그가 없으므로 ROLLBACK 이 불가능하므로 주의해야 한다.

 

테이블 삭제비교

 

	DROP	TRUNCATE	DELETE
명령어 종류	DDL	DDL(일부 DML 성격 가짐)	DML
Rollback 가능여부	Rollback 불가능	Rollback 불가능	Commit 이전 Rollback 가능
Commit	Auto Commit	Auto Commit	사용자 Commit
Storage	테이블이 사용했던 Storage를 모두 Release	테이블이 사용했던 Storage 중 최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음
삭제방식	테이블의 정의 자체를 완전히 삭제함	테이블을 최초 생성된 초기상태로 만듬	데이터만 삭제

 

DCL

DBA(Database Administration) 권한은 SYSTEM, SYS 등의 상위 유저와 그에 해당하는 권한을 가진 경우 부여 가능하다.

사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다.

 

TCL

트랜잭션의 특징

특성	설명
원자성 (Atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (All or Nothing)
일관성 (Consistency)	트랜잭션이 실행되기 전의 DB 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 DB의 내용에 잘못이 있으면 안 된다.
고립성 (Isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.
지속성 (Durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.

 

트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점

Dirty Read	다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
Non-Repeatable Read	한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
Phantom Read	한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상

 

COMMIT

Oracle에서는 DDL 문장 수행 후 자동으로 COMMIT을 수행한다. (AUTO COMMIT이 FALSE로 설정되어 있어도)

BEGIN TRANSACTION(BEGIN TRAN 구문도 가능)으로 트랜잭션을 시작하고 COMMIT TRANSACTION(TRANSACTION은 생략 가능) 또는 ROLLBACK TRANSACTION(TRANSACTION은 생략 가능)으로 트랜잭션을 종료한다. ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 이 수행된다.

 

ROLLBACK

테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백(ROLLBACK) 기능을 사용한다. 롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

 

SAVEPOINT

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

 

WHERE

연산자의 종류

문자 우형 비교

CHAR 타입인 경우 길이가 다르다면 작은 쪽에 SPACE를 추가하여 길이를 같게 한 후에 비교한다.

ANY(서브쿼리)	- 서비쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미 - 비교연산자로 " > " 를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로 서브쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족 - SOME과 동일
ALL(서브쿼리)	- 서브쿼리의 결과에 존재하는 모든값을 만족하는 조건을 의미. - 비교연산자로 " > " 를 사용했다면 메인쿼리는 서브쿼리의 모든 값을 만족해야 하므로 서브쿼리의 결과의 최대값보다 큰 모든 건이 조건을 만족

연산자의 우선순위

NULL

NULL(ASCII 코드 00번)은 공백(BLANK, ASCII 코드 32번)이나 숫자 0(ZERO, ASCII 48)과는 전혀 다른 값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다르다. ‘NULL’은 ‘아직 정의되지 않은 미지의 값’이거나 ‘현재 데이터를 입력하지 못하는 경우’를 의미한다.

 

NULL의 연산

• NULL 값과의 연산(+,-,*,/ 등)은 NULL 값을 리턴
• NULL 값과의 비교연산(=,>,>=,<,<=)을 통해서 비교할 수도 없고, 비교연산을 하더라도 결과는 거짓(FALSE)를 리턴
• 어떤 값과 비교할 수도 없으며, 특정 값보다 크다, 적다라고 표현할 수 없다.
• 비교가 불가하기 때문에 =, != 와 같은 비교연산자가 아닌 IS NULL, IS NOT NULL이라는 키워드를 사용해야 한다.

ORACLE VARCHAR에 ''(공백)이 들어갈 경우 NULL 이 됨. SQL SERVER는 VARCHAR에 ''(공백)이 들어갈 경우 그대로공백으로 들어감.

IN과 NOT IN의 NULL 연산

 

단일행 NULL 관련 함수의 종류

집계함수와 NULL

NULL은 AVG 연산 대상에서 제외됨

 

집계 함수

집계 함수의 종류

 

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 절 뒤에 위치한다.

HAVING 절

테이블 전체가 한개의 그룹이 되는 경우 GROUP BY 없이 단독 HAVING이 올 수 있다.

 

ORDER BY 절

ORDER BY 절 특징

• 기본적인 정렬 순서는 오름차순(ASC)이다.
• 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다.
• 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력된다.
• Oracle 에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에, 내림차순으로 정렬했을 경우에는 가장 먼저 위치한다.
• 반면, SQL Server 에서는 NULL 값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 먼저, 내림차순으로 정렬했을 경우에는 가장 마지막에 위치한다.

SELECT 실행순서

1. 발췌 대상 테이블을 참조한다. (FROM) 2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE) 3.
행들을 소그룹화 한다. (GROUP BY) 4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING) 5.
데이터 값을 출력/계산한다. (SELECT) 6. 데이터를 정렬한다. (ORDER BY)

 

TOP N 쿼리

WITH TIES 옵션은 ORDER BY 절의 조건 기준으로 TOP N 의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+ 동일 정렬 순서 데이터를 추가 반환하도록 지정하는 옵션이다.

 

ORDER BY 숫자

함수

내장함수

함수는 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다. 내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다. 다중행 함수는 다시 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 나눌 수 있다.

 

단일행 함수의 종류

숫자형 함수

TRUNC(숫자, m) : 숫자를 소수 m 자리에서 잘라서 리턴 (m default : 0)

ROUND(숫자, m) : 숫자를 소수 m 자리에서 반올림하여 리턴 (m default : 0)

 

결합 함수

ORACLE : CONCAT / ||

SQL Server : +

 

단일행 문자형 함수의 종류

DUAL 테이블의 특성

• 사용자 SYS 가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
• SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
• DUMMY 라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 1건 포함하고 있다.