제 1절 관계형 데이터베이스 개요
✨ 데이터베이스 정의
-넓은 의미: 일상적인 정보들을 모아 놓은 것 자체
-일반적으로는 !!
특정 기업이나 조직 또는 개인이 필요에 의해(EX: 부가가치가 발생하는) 데이터를 일정한 형태로 저장해 놓은 것
-하지만 관리 대상이 되는 데이터 양이 많아지고 같은 데이터를 여러 사람이 동시에 여러 용도로 사용하게 되면서 개인이 관리하는 SW만으로는 한계에 부딪히게 되었다. 많은 사용자들이 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터 손상을 피하고 필요시에 필요한 데이터를 복구하기 위한 SW가 필요하게 되었다
이것을 만족시켜주는 시스템은 DBMS(Database Management System) 데이터베이스 관리 시스템
✨ 데이터베이스 발전
-1960년대: 플로우차트
-1970년대: 계층형, 망형 DB
-1980년대: 관계형 DB / (EX) oracle, sybase, DB2
-1990년대: 객체관계형 DB
✨ 관계형 데이터베이스
- 1970 영국의 수학자 E.F.Codd 박사의 논문에서 처음 소개
- 파일 시스템 단점 : 동시에 입력/수정/삭제할 수 없기 때문에 정보의 관리가 어려움 복사본 파일을 만들어 사용할 경우 데이터의 불일치성이 발생
- 관계형 DB의 장점 : 정규화를 통해 이상 현상을 제거하고 데이터 중복을 피함 동시성 관리, 병행 제어를 통해 데이터를 공유 데이터의 표현 방법 등 체계화 할 수 있고, 데이터 표준화, 품질 확보 보안기능, 데이터 무결성 보장, 데이터 회복/복구 기능
✨ SQL(Structured Query Language)
-최초 이름은 SEQUEL, 1986부터 ANSI/ISO를 통해 표준화되고 정의됨
-각 벤더의 RDBMS는 표준화된 SQL 이외에 벤더 차별화 및 이용 편리성 위해 추가기능 구현
-SQL 문장은 단순한 스크립트가 아니라 독립된 하나의 개발 언어
-SQL은 관계형 데이터베이스에 대한 전담 접속 용도로 사용되며 세미콜론(;)으로 분리되어있다. (다른 언어는 접속 X)
-SQL 명령어는 3가지 SAVEPOINT 그룹인 DDL, DML, DCL로 구분 TCL의 경우 굳이 나눈다면 일부에서 DCL로 분류, but 성격이 다르므로 별도의 4번째로 분리
| DML(데이터 조작어) | Select, Insert, Update, Delete |
| DDL(데이터 정의어) | 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어 Create, Alter, Drop, Rename |
| DCL(데이터 제어어) | DB에 접근하고 객체들을 사용하도록 권한 주고, 회수하는 명령어 Grant, Revoke |
| TCL(트랜잭션 제어어) | 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어 Commit, Rollback |
✨ 테이블
어느 특정한 주제와 목적으로 만들어지는 일종의 집합
데이터를 저장하는 객체로서 관계형 데이터베이스의 기본 단위
| 테이블 | 행과 칼럼의 2차원 구조를 가진 데이터의 저장 장소이며, 데이터베이스의 가장 기본적인 개념 |
| 칼럼/열 | 2차원 구조를 가진 테이블에서 세로 방향으로 이루어진 하나하나의 특정 속성(더이상 나눌 수 없는 특성) |
| 행 | 2차원 구조를 가진 테이블에서 가로 방향으로 이루어진 연결된 데이터 |
선수와 관련된 데이터를 저장할 때 모든 데이터를 하나의 테이블로 저장하지 않는다.
선수 테이블(PLAYER)과 구단 테이블(TEAM)로 분할하여서 저장 = 정규화
-> 분할된 테이블은 칼럼의 값(FK)에 의해 연결
정규화하면? 데이터 입력/수정/삭제시 발생할 수 있는 이상현상 방지!
| 정규화 | 테이블을 분할하여 데이터의 정합성을 확보하고, 불필요한 중복을 줄이는 프로세스 |
| 기본키 | 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼 한 개 이상? 복합식별자인 경우 PK여도 속성이 여러 개일 수 있다. |
| 외부키 | 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼 나의 테이블에선 필요 없는 칼럼이지만 다른 테이블과 JOIN하기 위해서 필요함 |
✨ ERD
구성요소 : 엔티티(Entity), 관계(Relationship), 속성(Attribute) 3가지
표기법 : IE(Information Engineering) 표기법, Barker(Case Method) 표기법
제 2절 DDL
✨ 데이터 유형
특정 칼럼을 정의할 때 선언한 데이터 유형은 그 칼럼을 받아들일 수 있는 자료의 유형을 규정한다.
선언한 유형이 아닌 다른 종류의 데이터가 들어오려고 하면 DB는 에러를 발생시킴 🙅♀️🙅♀️
(EX) 몸무게 정보를 모아놓은 공간에 '박지성'이라는 문자가 입력 ? 숫자 유형만 들어갈 수 있는 칼럼에 문자가 입력되어서 잘못된 데이터라고 판단 !
데이터 유형과 더불어서 지정한 크기도 중요한 기능을 제공한다.
선언 당시에 지정한 데이터 크기를 넘어선 자료가 입력되는 상황도 에러를 발생시키는 중요한 요인 🙅♀️ 🙅♀️
# 숫자 타입
ANSI/ISO 기준 : Numeric, Decimal, Dec, Small Int, Integer, Int, Big int, Float, Real, Double Precision SQL Server / Sybase : 작은 정수, 정수, 큰 정수, 실수 등 ＋ Money, Small Money
Oracle : 숫자형 타입에 대해서 Number 한 가지 타입만 지원
벤더에서 ANSI/ISO 표준을 사용할 땐 기능을 중심으로 구현하므로 표준과 다른 용어 사용 허용
✨ 테이블 칼럼이 가지고 있는 데표적인 4가지 유형
| Character(s) | 고정길이 문자열, Oracle, SQL Server 모두 CHAR로 표현 s는 기본길이 1바이트, 최대 Oracle 2,000 SQL Server 8,000바이트, 최대 s만큼의 길이를 가짐. 빈 공간은 채워짐 |
| Varchar(s) | Character Varying의 약자. 가변길이 문자열 Oracle은 VARCHAR2, SQL Server는 VARCHAR로 표현 s는 최소 1바이트, 최대 Oracle 4,000 SQL Server 8,000 바이트, s만큼의 최대 길이 but 가변적으로 줄어들어 조정(limit) |
| Numeric | 정수, 실수 등 숫자 정보. Oracle은 Number, SQL Server는 10가지 이상 타입 Oracle은 처음 전체 자리 수, 그 뒤에 그 중 소수 부분 자리 수 지정 정수 부분이 6자리, 소수 부분이 2자리 -> Number(8,2) |
| Datetime | 날짜와 시각 정보 Oracle은 DATE, SQL Server는 DATETIME으로 표현 Oracle은 1초 단위, SQL Server는 3.33ms(밀리세컨) 단위 관리 |
✨ 문자열 유형 비교 (CHAR vs VARCHAR)
1. 저장 영역
VARCHAR 유형은 가변 길이이므로 필요힌 영역은 실제 데이터 크기뿐이다.
길이가 당야한 칼럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합하다.
저장 측면에서도 CHAR 유형보다 작은 영역에 저장할 수 있으므로 장점이 있다. (실제로 정의된 영역보다 DATA 크기가 작으면 남은 영역에 공백 채우지 않고, 실제 DATA 크기만 저장하니깐)
2. 문자열 비교 방법
CHAR에서는 문자열을 비교할 때 공백을 채워서 비교한다. 우선 짧은 쪽의 끝에 공백을 추가해서 2개의 데이터가 같은 길이가 되도록 한다. 그리고 앞에서부터 한 문자씩 비교 -> 공백만 다른 문자열은 같다고 판단
VARCHAR에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급 -> 끝의 공백이 다르면 다른 문자로 판단
CHAR 유형에서.. 'AA' = 'AA '
비교 시 왼쪽 문자가 오른쪽 문자와 길이가 같아지기 위해서 공백이 추가됨 -> 'AA '가 되기때문에
VARCHAR 유형에서.. 'AA' != 'AA '
오른쪽 문자의 공백도 하나의 문자이기 때문에 오른쪽과 왼쪽의 문자는 같지만 공백의 존재때문에 다른 문자라고 판단
✨ CREATE TABLE
가. 테이블과 칼럼 정의
테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정한다.
기본키는 단일 칼럼이 아닌 여러 개의 칼럼으로도 만들어질 수 있다.
테이블과 테이블 간에 정의된 관계는 기본키와 외부키를 활용해서 설정하도록 한다.
나. CREATE TABLE
테이블을 생성하는 구문 형식이다.
CREATE TABLE 테이블명(
칼럼명 DATATYPE [DEFAULT 형식],
칼럼명 DATATYPE [DEFAULT 형식],
칼럼명 DATATYPE [DEFAULT 형식]
);테이블 생성 시에 주의해야 할 규칙이 있다.
테이블명은 객체를 의미할 수 있는 적절한 이름 사용. 가능한 단수형 권고
테이블 명은 다른 테이블의 이름과 중복 X
한 테이블 내에서는 칼럼명 중복되게 지정 X
테이블 이름 지정하고 각 칼럼들은 괄호 "()"로 묶어서 지정
각 칼럼은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.
🎇 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
=> 데이터베이스에서 "일관성"을 유지하는 데 중요한 측면 중 하나는 다른 테이블과의 관계를 고려하여 칼럼의 사용을 일관되게 유지하는 것입니다. 특히 외래 키(Foreign Key)와 기본 키(Primary Key)를 사용하여 테이블 간의 관계를 정의할 때 이러한 일관성이 중요!!!
칼럼 뒤에 데이터 유형 꼭 지정
테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계 존재
벤터에서 사전에 정의한 예약어 사용 X
A-Z, a-z, 0-9, $, #, _ 문자만 허용된다.
한 테이블 안에서 칼럼 이름은 달라야 하지만, 다른 테이블의 칼럼 이름과는 같을 수 있다.
테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명 = 대문자로
DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 X
칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.
제약조건은 칼럼의 데이터 유형 뒤에 NOT NULL을 정의한 사례와 같은 칼럼 LEVEL 정의 방식
테이블 생성 마지막에 모든 제약조건을 기술하는 테이블 LEVEL 정의 방식이 있다. -> PLAYER_PK, PLAYER_FK
다. 제약조건
제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.
| PRIMARY KEY(기본키) | 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키 정의. 하나의 테이블에 하나의 기본키 제약만 정의 가능. 기본키 제약 정의 시 DBMS가 자동으로 UNIQUE 인덱스 생성 기본키 구성하는 칼럼에 NULL 입력 불가 기본키 제약 = 고유키 제약 & NOT NULL 제약 |
| UNIQUE KEY(고유키) | 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키 정의 NULL은 고유키 제약 대상이 아님 -> NULL 값을 가진 행이 여러 개 가 있더라도 고유키 제약 위반되지 X NULL값 가능!! |
| NOT NULL | NULL 값의 입력을 금지 |
| CHECK | 입력할 수 있는 값의 범위 등을 제한. TRUE, FALSE 논리식 지정 |
| FOREIGN KEY | 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 생성됨. 외래키 지정시 참조 무결성 제약 옵션 선택 가능. |
기본키는 중복되는 값이 없으면서 NULL이어도 안 되고, 고유키는 중복되는 값은 없지만 NULL이어도 된다.
PRIMARY KEY (기본키):
- 예시: 사용자(User) 테이블에서 사용자 ID를 기본키로 설정하는 경우
CREATE TABLE User (
UserID INT PRIMARY KEY,
UserName VARCHAR(50),
-- other columns
);UNIQUE KEY (고유키):
- 예시: 이메일 주소가 중복되지 않도록 하는 경우
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE,
-- other columns
);NOT NULL:
- 예시: 주문 테이블에서 주문 날짜는 비어있을 수 없는 경우
CREATE TABLE Order (
OrderID INT PRIMARY KEY,
OrderDate DATE NOT NULL,
-- other columns
);CHECK:
- 예시: 나이가 18세 이상이어야 하는 경우
CREATE TABLE Person (
PersonID INT PRIMARY KEY,
Age INT CHECK (Age >= 18),
-- other columns
);NULL: 공집합, 공백이나 숫자 0과는 전혀 다른 값이며, '아직 정의되지 않은 미지의 값' OR '현재 데이터를 입력하지 못하는 경우'
DEFAULT: 칼럼의 값이 지정되어 있지 않을 경우 사전에 설정 가능.
데이터 입력시 명시된 값을 지정하지 않은 경우에 NULL 값이 입력되고, DEFAULT 값을 정의했다면 해당 칼럼에 NULL 값이 입력되지 않고 사전에 정의된 기본 값이 자동으로 입력된다.
라. 생성된 테이블 구조 확인
[Oracle]
DESCRIBE 테이블명;
DESC 테이블명;
DESCRIBE PLAYER;[SQL Server]
exec sp_help 'dbo.테이블명' go
exec sp_help 'dbo.PLAYER' go- exec는 저장 프로시저나 함수를 실행하는 데 사용됩니다. execute(실행하다)
- dbo는 스키마를 나타내며, dbo.PLAYER에서 PLAYER는 테이블의 이름을 나타냅니다. database owner
- sp_help는 SQL Server에서 제공되는 저장 프로시저 중 하나로, 특정 테이블이나 개체에 대한 정보를 반환합니다.
마. SELECT 문장을 통한 테이블 생성 사례
DML 문장 중에서 SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법 CTAS(Create Table ~ As Select ~)이 있다.
기존 테이블을 이용한 CTAS 방법을 이용하면 칼럼별로 데이터 유형을 다시 재정의하지 않아도 된다.
🎇 CTAS 기법 사용시 주의할 점
기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용
기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 사라짐
제약 조건을 추가하기 위해선 ALTER TABLE 기능을 사용해야 한다.
SQL Server에서는 Select ~ Into~ 를 활용하여 같은 결과 얻을 수 있다.
칼럼 속성에 identify를 사용했다면 identify 속성까지 같이 적용된다.
IDENTITY ??
"IDENTITY"는 일부 데이터베이스 시스템에서 사용되는 용어로, 특정 열의 값을 자동으로 생성하고 증가시키는 데 사용됩니다. 이는 주로 기본키(primary key)나 일련번호로 사용되며, 데이터베이스에서 자동으로 고유한 값을 할당하고 싶을 때 유용합니다.
예를 들어, SQL Server에서는 IDENTITY 속성을 사용하여 열을 자동으로 증가시키는데, 이를 통해 개발자는 삽입할 때마다 고유한 값을 생성하지 않아도 됩니다.
아래에 ID 열은 IDENTITY(1,1)로 정의되어 있습니다. 이는 ID 열이 자동으로 1씩 증가하며, 1부터 시작하여 고유한 값을 생성한다는 것을 의미합니다.
CREATE TABLE ExampleTable (
ID INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(50),
-- other columns
);[Oracle]
CREATE TABLE 테이블명_TEMP AS SELECT * FROM 테이블명;
CREATE TABLE TEAM_TEMP
AS SELECT * FROM TEAM;
DESC TEAM_TEMP;
//TEAM 테이블과 같은 테이블 구조 응답해줌[SQL Server]
SELECT * INTO 테이블명_TEMP FROM 테이블명;
SELECT * INTO TEAM_TEMP FROM TEAM;
exec sp_help 'dbo.TEAM_TEMP' go✨ ALTER TABLE
한 번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다.
하지만 업무적인 요구 사항이나 시스템 운영상 테이블을 사용하는 도중에 변경해야 할 일들이 발생할 수도 있다.
이 경우 주로 칼럼을 추가/삭제하거나 제약 조건을 추가/삭제하는 작업을 진행하게 된다.
가. ADD COLUMN
기존 테이블에 필요한 칼럼을 추가하는 명령어이다.
새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.
ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터유형;[예제] PLAYER 테이블에 ADDRESS(데이터 유형은 가변 문자로 자릿수 80자리로 설정) 칼럼을 추가한다.
[Oracle]
ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));[SQL Server]
ALTER TABLE PLAYER ADD (ADDRESS VARCHAR(80));
나. DROP COLUMN
테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다.
한번에 하나의 칼럼만 삭제 가능하며, 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 한다.
🎇 한 번 삭제된 칼럼은 복구가 불가능하다.
WHY? DDL 문은 데이터베이스의 구조를 변경하는 데 사용되며, 실행 후에는 변경 사항이 즉시 커밋되어 롤백이 불가능합니다. 이는 데이터 정의 언어(DDL)의 특성으로, 테이블 구조 변경과 같은 중요한 작업은 안전성을 위해 즉시 반영되기 때문입니다.
ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;[예제] 앞에서 PLAYER 테이블에 새롭게 추가한 ADDRESS 칼럼을 삭제한다.
[Oracle]
ALTER TABLE PLAYER DROP COLUMN ADDRESS;[SQL Server]
ALTER TABLE PLAYER DROP COLUMN ADDRESS;
다. MODIFY COLUMN
테이블에 존재하는 칼럼에 대해서 ALTER TABLE 명령을 이용해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대한 변경을 포함할 수 있다.
[Oracle]
ALTER TABLE 테이블명
MODIFY (칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL],
칼럼명2 데이터유형 ...);[SQL Server]
SQL Server에서는 ALTER TABLE에서 ALTER COLUMN을 사용하여 열의 속성을 변경
ALTER TABLE 테이블명
ALTER COLUMN 칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL],
칼럼명2 데이터유형 ...;🎇 칼럼을 변경할 때는 몇 가지 사항을 고려해서 변경해야 한다
-해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다.(기존의 데이터가 훼손될 수 있기 때문)
- 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼 폭 줄이기 가능
- 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형 변경 가능
- 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미침
- 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건 추가 가능
[예제] TEAM 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4)->VARCHAR2(8)으로 변경하고, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고, 모든 행의 ORIG_YYYY 칼럼에 NULL이 없으므로 제약조건을 NULL -> NOT NULL로 변경한다.
[Oracle]
ALTER TABLE TEAM_TEMP
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);[SQL Server]
//(1)
//ORIG_YYYY 열에 대해 데이터 타입을 변경하면서 동시에 기본값을 '20020129'로 설정하고 NOT NULL 제약조건을 추가
ALTER TABLE TEAM_TEMP
ALTER COLUMN ORIG_YYYY VARCHAR(8) DEFAULT '20020129' NOT NULL;
//(2)
//ORIG_YYYY 열에 대해 데이터 타입을 변경하고 NOT NULL 제약조건을 추가한 후, 따로 다시 기본값을 설정하는 문장
//이 경우, ALTER CONSTRAINT 문을 사용하여 명시적으로 기본값 제약조건을 추가
ALTER TABLE TEAM_TEMP
ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL;
ALTER TABLE TEAM_TEMP
ALTER CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY;
RENAME COLUMN
테이블을 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우에 쓰인다.
[Oracle]
ALTER TABLE 테이블명
RENAME COLUMN 변경해야 할 칼럼명(구) TO 새로운 칼럼명(신);
ALTER TABLE PLAYER
RENAME COLUMN PLAYER_ID TO TEAM_ID;
ALTER TABLE PLAYER
RENAME COLUMN TEAM_ID TO PLAYER_ID;[SQL Server]
sp_rename 'dbo. 테이블명.칼럼명(구)', '새로운 칼럼명(신)', 'COLUMN';
sp_rename 'dbo.TEAM_TEMP.TEAM_ID' ,'TEAM_TEMP_ID', 'COLUMN';- sp_rename은 Microsoft SQL Server에서 사용되는 저장 프로시저로, 데이터베이스 개체의 이름을 변경하는 데 사용됩니다. 구체적으로는 테이블, 열, 인덱스, 제약조건 등의 이름을 변경할 수 있습니다.
- dbo는 SQL Server에서 스키마(schema)를 나타냅니다. 스키마는 데이터베이스 내에서 테이블, 뷰, 프로시저 등의 개체를 그룹화하는 데 사용됩니다. dbo는 기본 스키마로 "database owner"의 약자입니다.
RENAME COLUMN으로 칼럼명이 변경되면, 해당 칼럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 이지만, ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시되어 있는 기능은 아니고 OrACLE 등 일부 DBMS에서만 지원하는 기능이다.
라. DROP CONSTRAINT
테이블 생성 시 부여했던 제약조건을 삭제하는 명령어
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;[예제] PLAYER 테이블의 외래키 제약조건을 삭제한다.
[Oracle/SQL Server]
ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_FK;
마. ADD CONSTRAINT
테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 제약조건(칼럼명);[예제] PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다. 제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.
테이블 LEVEL의 제약조건 !!
[Oracle/SQL Server]
ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK
FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);[예제] PLAYER 테이블이 참조하는 TEAM 테이블을 제거해본다.
PLAYER, TEAM 테이블 모두 TEAM_ID 칼럼에 NOT NULL 제약조건이 있다.
따라서 TEAM을 참조하고 있는 PLAYER의 TEAM_ID 외래키가 있는데 TEAM 테이블을 제거해버리면 오류가 난다.
[Oracle/SQL Server]
DROP TABLE TEAM;[예제] PLAYER 테이블이 참조하는 TEAM 테이블의 데이터를 삭제해본다.
무결성 제약조건이 위배되어서 데이터가 삭제되지 않는다.
[Oracle/SQL Server]
DELETE TEAM
WHRER TEAM_ID = 'K10';참조 제약 조건을 추가하면 PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID 칼럼을 참조하게 된다.
참조 무결성 옵션에 따라서 만약 TEAM 테이블이나 TEAM 테이블의 데이터를 삭제하려 할 경우 외부(PLAYER 테이블)에서 참조되고 있기 때문에 삭제가 불가능하게 제약을 할 수 있다.
외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.
✨RENAME TABLE
테이블의 이름을 변경할 수 있다.
[Oracle]
RENAME 변경전 테이블명(구) TO 변경후 테이블명(신);[SQL Server]
sp_rename 'dbo.테이블명(구)', '테이블명(신)';[예제] RENAME 문장을 이용하여 TEAM 테이블명을 다른 이름으로 변경하고, 다시 TEAM 테이블로 변경한다.
[Oracle]
RENAME TEAM TO TEAM_BACKUP;
RENAME TEAM_BACKUP TO TEAM;[SQL Server]
sp_rename 'dbo.TEAM', 'TEAM_BACKUP';
sp_rename 'dbo.TEAM_BACKUP', 'TEAM';✨DROP TABLE
테이블을 잘못 만들었거나 테이블이 더 이상 필요 없을 경우 해당 테이블을 삭제해야 한다.
DROP TABLE 테이블명 [CASCADE CONSTRAINT];DROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제한다.
CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.
SQL Server에는 CASCADE 옵션 존재 X -> 테이블 삭제하기 전에 참조하는 FK 제약 조건 또는 참조하는 테이블을 먼저 삭제해야 한다.
[Oracle/SQL Server]
DROP TABLE PLAYER;
DESC PLAYER;
ERROR: 설명할 객체를 찾을 수 없다.✨TRUNCATE TABLE
테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다.
테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.
[Oracle]
TRUNCATE TABLE TEAM;
DESC TEAM;
테이블 구조 나옴[SQL Server]
TRUNCATE TABLE TEAM;
exec sp_help 'dbo.TEAM'
go
테이블 구조 나옴
DROP 테이블의 경우는 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없다.
반면 TRUNCATE TABLE의 경우는 테이블 구조는 그대로 유지한 채 데이터만 전부 삭제하는 기능이다.
DELETE vs TRUNCATE
TRUNCATE는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML(DELETE)로 분류할 수도 있지만 내부 처리 방식이나 자동 커밋 특성 등으로 인해 DDL로 분류하였다.
DELETE와 TRUNCATE는 처리하는 방식 자체가 다르다.
테이블 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다.(TRUNCATE가 한 번에 삭제되니깐)
단, TRUNCATE TABLE은 정상적인 복구가 불가능하다. (자동 커밋되므로 LOG가 남지 않음)
첫번째 개념은 TRUNCATE TABLE(DDL, 자동커밋 O)과 DELETE TABLE(DML, 자동커밋 X)의 차이이다.
DROP TABLE의 경우는 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없다. 따라서 DROP 문을 쓴 뒤에 DESCRIBE 테이블명을 쓰게 되면 설명할 객체를 찾을 수 없다고 응답한다.
반면에 TRUNCATE TABLE의 경우는 테이블 구조는 그대로 유지한 채 데이터만 전부 삭제하는 기능이다.
데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML로 구분할 수도 있지만 내부 처리 방식이나 자동 커밋 특성 등으로 인해 DDL로 구분되었다.
DELETE와 TRUNCATE가 비슷하다고 생각할 수 있지만 처리하는 방식 자체가 다르다. 테이블 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE을 사용하는 것이 좋다. 하지만 TRUNCATE TABLE의 경우는 자동 커밋이 되기 때문에 이전 기록이 남지 않으므로 정상적인 복구가 불가능하므로 주의해야 한다.
간단히 말하자면 DDL은 자동 커밋이 되므로 이전 기록이 남지 않고, DML은 자동 커밋이 되지 않아서 복구는 할 수 있다. 여기서 TRUNCATE TABLE은 DML인 DELETE TABLE문과 유사하지만 자동 커밋이 되기 때문에 DDL로 구분한 것이다.
제 3절 DML
만들어진 테이블에 관리하기를 원하는 자료들을 입력, 수정, 삭제, 조회하는 DML
✨INSERT
테이블에 데이터를 입력하는 방법은 두 가지 유형이 있으며, 한 번에 한 건만 입력된다.
//(1)
INSERT INTO 테이블명(COLUMN_LIST)
VALUES (COLUMN_LIST에 넣을 VALUE_LIST);
//(2)
INSERT INTO 테이블명
VALUES (전체 COLUMN에 넣을 VALUE_LIST);해당 칼럼명과 입력되어야 하는 값을 서로 1:1 매핑해서 입력하면 된다.
해당 칼럼의 데이터 유형이 CHAR나 VARCHAR2 등 문자 유형일 경우 '(SINGLE QUOTATION)로 입력할 값을 입력한다.
숫자일 경우 ' 붙이지 X
(1)
테이블의 칼럼을 정의할 수 있음
이때 칼럼의 순서는 테이블의 칼럼 순서와 매치할 필요 없으며, 정의하지 않은 칼럼은 Default로 NULL 값이 입력된다.
단 PK나 NOT NULL로 지정된 칼럼은 NULL이 허용되지 않음
(2)
모든 칼럼에 데이터를 입력하는 경우
굳이 COLUMN_LIST를 언급하지 않아도 되지만, 칼럼의 순서대로 빠짐없이 데이터가 입력되어야 함
[예제] 선수 테이블에 박지성 선수의 데이터를 일부 칼럼만 입력한다.
INSERT INTO PLAYER
(PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);INSERT 문장에서 BACK_NO가 마지막에 정의되어있더라도 테이블에는 칼럼 순서대로 데이터가 입력된다. 칼럼명이 정의되지 않은 경우 NULL 값이 입력된다(아무값도 X)
[예제] 해당 테이블에 이청용 선수의 데이터를 입력해본다. (모든 칼럼 입력)
INSERT INTO PLAYER
VALUES ('2002010', '이청용', 'K07', '','BlueDragon','2002','MF','17',NULL,NULL,'1',180,69);데이터를 입력하는 경우 정의되지 않은 미지의 값에는 두개의 SINGLE QUOTATION '' 을 붙여서 표현하거나, NULL이라고 명시적으로 표현할 수 있다.
✨UPDATE
입력한 정보 중에 잘못 입력되거나 변경이 발생하여 정보를 수정해야 하는 경우가 발생할 수 있다.
UPDATE 다음에 수정되어야 할 칼럼이 존재하는 테이블명을 입력하고 SET 다음에 수정되어야 할 칼럼명과 해당 칼럼에 수정되는 값으로 수정이 이루어진다.
UPDATE 테이블명
SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;
[예제] 선수 테이블의 백넘버를 일괄적으로 99로 수정한다.
UPDATE PLAYER
SET BACK_NO = 99;[예제] 선수 테이블의 포지션을 일괄적으로 'MF'로 수정한다.
UPDATE PLAYER
SET POSITION = 'MF';✨DELETE
테이블의 정보가 필요 없게 되었을 경우 데이터 삭제를 수행한다.
DELETE FROM 다음에 삭제를 원하는 자료가 저장되어 있는 테이블명을 입력하고 실행한다.
이때 FROM 문구는 생략이 가능한 키워드이며, WHRER 절을 사용하지 않는다면 테이블 전체 데이터가 삭제된다.
DELETE FROM 삭제를 원하는 정보가 들어있는 테이블명;[예제] 선수 테이블의 데이터를 전부 삭제한다.
DELETE FROM PLAYER;
DDL(DELETE TABLE, 자동 커밋 O) VS DML(TRUNCATE TABLE, 자동 커밋 X)
SQL Server는 예외적인 사항 존재!
데이터베이스는 DDL 명령어와 DML 명령어를 처리하는 방식에 있어서 차이를 보인다.
DDL 명령어의 경우에는 직접 데이터베이스의 테이블에 영향을 미치기 때문에 DDL 명령어를 입력하는 순간 명령어에 해당하는 작업이 즉시 완료된다. (AUTO COMMIT)
DML 명령어의 경우, 조작하려는 테이블을 메모리 버퍼에 올려놓고 작업을 하기 때문에 실시간으로 테이블에 영향을 미치지 않는다. 따라서 버퍼에서 처리한 DML 명령어가 실제 테이블에 반영되기 위해서는 COMMIT 명령어를 입력하여 TRANSACTION을 종료해야 한다.
테이블 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 삭제된 데이터를 로그로 저장하는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE이 좋다. (DML 보다는 자동 커밋이 되는 DDL이 나음)
단, TRUNCATE TABLE의 경우 삭제된 데이터의 로그가 없으므로 ROLLBACK이 불가능하다.
그러나 SQL Server의 경우는 DML도 AUTO COMMIT으로 처리되기 때문에 실제 테이블에 반영하기 위해 COMMIT 명령어를 입력할 필요가 없다. SQL Server의 경우 사용자가 임의적으로 트랜잭션을 시작한 후 TRUNCATE TABLE을 이용하여 데이터를 삭제한 이후 오류가 발견되어, 다시 복구를 원할 경우 ROLLBACK 문을 이용하여 테이블 데이터를 원 상태로 되돌릴 수 있다.
✨SELECT
사용자가 입력한 데이터는 언제라도 조회가 가능하다.
SELECT [ALL/DISTINCT] 보고 싶은 칼럼명, 보고 싶은 칼럼명, ...
FROM 해당 칼럼들이 있는 테이블명;ALL: Default 옵션이므로 별도로 표시하지 않아도 된다. 중복된 데이터가 있어도 모두 출력한다.
DISTINCT: 중복된 데이터가 있는 경우 1건으로 처리해서 출력한다.
[예제] 조회하기를 원하는 칼럼명을 SELECT 다음에 콤마 구분자(,)로 구분하여 나열하고, FROM 다음에 해당 칼럼이 존재하는 테이블명을 입력하여 실행시킨다. 입력한 선수들의 데이터를 조회한다.
SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO
FROM PLAYER;
- DISTINCT 옵션
[예제] 선수 테이블의 포지션 정보를 ALL과 DISTINCT 옵션으로 확인해본다.
SELECT ALL POSITION
FROM PLAYER;
//ALL은 생략 가능한 키워드이므로 아래 SQL 문장도 같은 결과 출력
SELECT POSITION
FROM PLAYER;SELECT DISTINCT POSITION
FROM PLAYER;DISTINCT 실행 결과를 보면 480개 행 모두 출력된 것이 아니라 포지션 종류인 4개의 행과 포지션 데이터가 아직 미정인 NULL까지 5건의 데이터만 출력되었다.
- WILDCARD 사용하기
입력한 정보들을 보기위해 PLAYER 테이블에서 보고 싶은 정보들이 있는 칼럼들을 선택하여 조회해보는 것이다.
해당 테이블의 모든 칼럼 정보를 보고 싶을 경우에 와일드카드로 애스터리스크(*)를 사용해서 조회할 수 있다.
SELECT *
FROM 테이블명;[예제] 입력한 선수들의 정보를 모두 조회한다.
SELECT *
FROM PLAYER;레이블은 기본적으로 대문자로 보이고, 첫 라인에 보이는 레이블 정렬은?
좌측 정렬: 문자 및 날짜 데이터
우측 정렬: 숫자 데이터
- ALIAS 부여하기
조회된 결과에 일종의 별명을 부여해서 칼럼 레이블을 변경할 수 있다.
-칼럼명 바로 뒤에 온다.
-칼럼명과 ALIAS 사이에 AS, as 키워드를 사용할 수 있다. (option)
-이중 인용부호는 ALIAS가 공백, 특수문자를 포함할 경우와 대소문자 구분이 필요할 경우 사용된다.
[예제] 입력한 선수들의 정보를 칼럼 별명을 이용하여 출력한다.
SELECT PLAYER_NAME AS 선수명, POSITION AS 위치, HEIGHT AS 키, WEIGHT AS 몸무게
FROM PLAYER;
//칼럼 별명에서 AS를 꼭 사용하지 않아도 되므로
//아래 SQL은 위 SQL과 같은 결과를 출력한다.
SELECT PLAYER_NAME 선수명, POSITION 위치, HEIGHT 키, WEIGHT 몸무게
FROM PLAYER;칼럼 별명을 적용할 때 별명 중간에 공백이 들어가는 경우 " "를 사용해야 한다. SQL Server의 경우 " " , ' ', [ ] 3가지 방식으로 별명 부여 가능하다.
SELECT PLAYER_NAME "선수 이름", POSITION "그라운드 포지션", HEIGHT "키", WEIGHT "몸무게"
FROM PLAYER;
✨산술 연산자와 합성 연산자
- 산술 연산자(사칙연산)
산술 연산자는 NUMBER과 DATE 자료형에 대해 적용되며 일반적으로 수학의 4칙 연산과 유사하다. 우선순위를 위한 괄호 적용이 가능하다.
산술 연산을 사용하거나 특정 함수를 적용하게 되면 칼럼의 LABEL이 길어지게 됨 -> 기존의 칼럼에 대해 새로운 의미 부여했으므로 적절한 별명(ALIAS) 부여하는 게 좋다.
우선순위 [ () ] -> [ * ] -> [ / ] -> [ + ] -> [ - ] 괄호 곱하기 나누기 더하기 빼기 순서!
[예제] 선수들의 키에서 몸무게를 뺀 값을 알아본다.
SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게"
FROM PLAYER;[예제] 선수들의 키와 몸무게를 이용해서 BMI 비만지수를 측정한다.
(ROUND() 함수는 반올림을 위한 내장 함수)
세번째 소수점에서 반올림되어서 두번째 소수점까지만 표시함
SELECT PLAYER_NAME 이름, ROUND(WEIGHT/((HEIGHT/100)*(HEIGHT/100)),2) "BMI 비만지수"
FROM PLAYER;
- 합성 연산자( + , || )
문자와 문자를 연결하는 합성 연산자를 사용하면 별도의 프로그램 도움 없이도 SQL 문장만으로도 유용한 리포트를 출력할 수 있다.
🎇 합성 연산자의 특징
-문자와 문자를 연결하는 경우 2개의 수직바(||)에 의해 이루어진다. - Oracle
-문자와 문자를 연결하는 경우 + 표시에 의해 이루어진다. - SQL Server
-두 벤더 모두 공통적으로 CONCAT(string1, string2) 함수를 사용할 수 있다.
-칼럼과 문자 또는 다른 칼럼과 연결시킨다.
-문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.
[예제] 다음과 같은 선수들의 출력 형태를 만들어 본다.
출력 형태) 선수명 선수, 키 cm, 몸무게 kg
예) 박지성 선수, 176 cm, 70 kg[Oracle]
SELECT PLAYER_NAME || '선수, ' || HEIGHT || 'cm, ' || WEIGHT || 'kg' 체격정보
FROM PLAYER;
//체격정보는 별명임[SQL Server]
SELECT PLAYER_NAME + '선수, ' + HEIGHT + 'cm,' + WEIGHT +'kg' 체격정보
FROM PLAYER;
제 4절 TCL
✨트랜잭션 개요
트랜잭션은 데이터베이스의 논리적 연산단위이다.
트랜잭션이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작이다.
-> 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.
트랜잭션은 분할할 수 없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소한다.
즉, 트랜잭션은 ALL OR NOTING의 개념이다.
EX) 은행에서의 계좌이체 상황
= 이체할 금액 인출&이체할 계좌 확인 후 인출한 금액 더하기(두가지 STEP)
이런 계좌이체 같은 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합을 트랜잭션이라고 한다.
데이터베이스 응용 프로그램 = 트랜잭션의 집합
- TCL(트랜잭션 컨트롤 언어)은?
-COMMIT: 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것
-ROLLBACK: 트랜잭션 시작 이전의 상태로 되돌리는 것
-저장점
- 트랜잭션 대상이 되는 SQL문은?
UPDATE, INSERT, DELETE 등 데이터 수정하는 DML 문
SELECT 문장은 직접적인 트랜잭션의 대상은 아니지만, SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다.
SELECT FOR UPDATE는 특정 행을 선택할 때 해당 행에 대한 배타적인 잠금을 요청하는 SQL 문장입니다. 이를 통해 선택된 행에 대한 변경 작업을 수행하는 동안 다른 트랜잭션들이 해당 행에 대한 수정을 시도하는 것을 방지할 수 있습니다. 이것은 동시성 제어 메커니즘 중 하나로 사용됩니다.
예를 들어, employees 테이블에서 특정 직원의 정보를 업데이트하는 상황을 가정해보겠습니다.
-- 특정 직원의 정보를 업데이트하기 위해 해당 행에 배타적 잠금 요청
START TRANSACTION;
-- 특정 직원의 정보를 선택하면서 배타적 잠금 요청
SELECT * FROM employees WHERE employee_id = 123 FOR UPDATE;
-- 선택된 행에 대한 변경 작업을 수행
UPDATE employees SET salary = 60000 WHERE employee_id = 123;
-- 트랜잭션 종료 (배타적 잠금이 해제됨)
COMMIT;이 예제에서 FOR UPDATE는 employees 테이블에서 employee_id가 123인 특정 직원의 행을 선택할 때 해당 행에 배타적인 잠금을 요청합니다. 이러한 잠금 요청은 트랜잭션이 커밋되거나 롤백될 때까지 지속됩니다. 이로 인해 다른 트랜잭션에서 동시에 같은 행에 대한 수정을 시도하는 것을 방지합니다.
참고: SELECT FOR UPDATE는 다양한 데이터베이스 시스템에서 지원되며, 사용법은 데이터베이스 종류에 따라 약간 다를 수 있습니다.
- 트랜잭션의 특성 (고일초지원)
| 원자성 | 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (all or nothing) |
| 일관성 | 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다. |
| 고립성 | 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다. |
| 지속성 | 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다. |
트랜잭션의 특성(특히 원자성)을 충족하기 위해 데이터베이스는 다양한 레벨의 잠금 기능을 제공하고 있는데, 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다.
잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고, 다른 트랜잭션으로부터 간섭이나 방해를 받지 않는 것이 보장된다. 그리고 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만 해제할 수 있다.
✨COMMIT
입력할 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료할 수 있다.
- COMMIT이나 ROLLBACK 이전의 데이터 상태는?
-단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
-현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
-다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
-변경된 행은 잠금이 설정되어서 다른 사용자가 변경할 수 없다.
현재 사용자는 트랜잭션 내에서 실행된 SQL 문장들에 의해 변경된 데이터를 확인할 수 있습니다. 트랜잭션이 완료되기 전에는 다른 사용자에게는 보이지 않습니다.
COMMIT이 실행되기 전까지는 다른 사용자가 현재 사용자가 수행한 변경 사항을 볼 수 없습니다. 트랜잭션이 커밋되기 전까지는 변경 사항이 다른 사용자에게 공개되지 않습니다.
트랜잭션 내에서 변경된 행에는 배타적 잠금이 설정될 수 있습니다. 이는 트랜잭션이 커밋되거나 롤백되기 전까지 다른 사용자가 해당 행을 변경할 수 없도록 합니다.
[Oracle]
[예제] PLAYER 테이블에 데이터를 입력하고 COMMIT을 실행한다.
INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
COMMIT;[예제] PLAYER 테이블에 데이터를 수정하고 COMMIT을 실행한다.
UPDATE PLAYER
SET HEIGHT = 100;
COMMIT;[예제] PLAYER 테이블에 있는 데이터를 삭제하고 COMMIT을 실행한다.
DELETE FROM PLAYER;
COMMIT;COMMIT 명령어는 이처럼 INSERT, DELETE, UPATE 문장을 사용한 후에 이런 변경 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용한다.
- COMMIT이나 ROLLBACK 이후의 데이터 상태는?
-데이터에 대한 변경 사항이 데이터베이스에 반영된다.
-이전 데이터는 영원히 잃어버리게 된다.
-모든 사용자는 결과를 볼 수 있다.
-관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
트랜잭션(Transaction)은 데이터베이스에서 원자성, 일관성, 고립성, 지속성을 보장하기 위한 개념입니다. 트랜잭션을 사용하면 데이터베이스 조작 시 일련의 작업을 묶어서 한 단위로 처리할 수 있습니다.
트랜잭션이 진행 중일 때 해당 트랜잭션에서의 변경 사항은 트랜잭션이 커밋될 때까지 데이터베이스에 반영되지 않습니다. 따라서 트랜잭션이 진행 중일 때 다른 사용자는 해당 변경 사항을 볼 수 없습니다. 이를 통해 트랜잭션을 사용한 데이터베이스 조작은 원자성을 보장하며, 다른 사용자에게는 일관성을 유지합니다.
- SQL Server의 COMMIT(3가지 방식)
Oracle은 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며 DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료된다.
하지만 SQL Server에서는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다.
DML 구문이 성공이면 자동으로 COMMIT이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리가 됨
[SQL Server]
[예제] PLAYER 테이블에 데이터를 입력한다.
INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES('1997035', 'K02', '이운재', 'GK', 182, 82, 1);[예제] PLAYER 테이블에 데이터를 수정한다.
UPDATE PLAYER
SET HEIGHT = 100;[예제] PLAYER 테이블에 있는 데이터를 삭제한다.
DELETE FROM PLAYER;
1) AUTO COMMIT
SQL Server의 기본 방식이며, DML, DDL을 수행할 떄마다 DBMS가 트랜잭션을 컨트롤
명령어가 성공적으로 수행되면 자동으로 COMMIT 수행하고 오류가 발생하면 ROLLBACK 수행
2) 암시적 트랜잭션
Oracle과 같은 방식ㄴ
트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리
인스턴스 단위 또는 세션 단위로 설정할 수 있다.
인스턴스 단위로 설정 -> 서버 속성 창의 연결화면에서 기본연결 옵션 중 암시적 트랜잭션에 체크 해주면 됨
이것은 SQL Server의 인스턴스 전체에 대한 설정으로, 모든 연결이 암시적 트랜잭션으로 시작되도록 설정합니다. 이 설정을 변경하면 해당 SQL Server 인스턴스에 연결되는 모든 세션에 대한 기본 동작이 변경됩니다.
세션 단위로 설정 -> 세션 옵션 중 SET IMPLICIT TRANSACTION ON 사용
개별적인 세션 수준에서 암시적 트랜잭션을 활성화하는 것입니다. 이것은 현재 세션에서만 적용되며, 다른 세션에는 영향을 미치지 않습니다. 즉, 세션마다 암시적 트랜잭션이 사용될지 여부를 개별적으로 지정할 수 있습니다.
예를 들어, SQL Server Management Studio(SSMS)에서 다음과 같은 T-SQL 명령을 사용하여 현재 세션에서 암시적 트랜잭션을 활성화할 수 있습니다:
SET IMPLICIT_TRANSACTIONS ON;
3) 명시적 트랜잭션
트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식
BEGIN TRANSACTION(BEGIN TRAN도 가능)로 트랜잭션 시작
COMMIT TRANSACTION(TANSACTION 생략 가능) 또는 ROLLBACK TRANSACTION( TANSACTION 생략 가능)으로 트랜잭션을 종료
ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 지점까지 모두 ROLLBACK 수행
✨ROLLBACK
테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있는데 이때 데이터베이스에서 롤백 기능을 사용한다. (DML의 INSERT, UPDATE, DELETE에 대해..)
롤백은 데이터 변경 사항이 취소되어 데이터 이전 상태로 복구되며, 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
[Oracle]
[예제] PLAYER 테이블에 데이터를 입력하고 ROLLBACK을 실행한다.
INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
ROLLBACK;[예제] PLAYER 테이블에 데이터를 수정하고 ROLLBACK을 실행한다.
UPDATE PLAYER
SET HEIGHT = 100;
ROLLBACK;[예제] PLAYER 테이블에 있는 데이터를 삭제하고 ROLLBACK을 실행한다.
DELETE FROM PLAYER;
ROLLBACK;
- SQL Server의 ROLLBACK
SQL Server는 AUTO COMMIT이 기본 방식이므로 임의적으로 ROLLBACK을 수행하려면 명시적으로 트랜잭션을 선언해야 한다. 트랜잭션의 시작과 끝 지점을 표시하기.
[SQL Server]
[예제] PLAYER 테이블에 데이터를 입력하고 ROLLBACK을 실행한다.
BEGIN TRAN
INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
ROLLBACK;BEGIN TRAN[예제] PLAYER 테이블에 데이터를 수정하고 ROLLBACK을 실행한다.
BEGIN TRAN
UPDATE PLAYER
SET HEIGHT = 100;
ROLLBACK;[예제] PLAYER 테이블에 있는 데이터를 삭제하고 ROLLBACK을 실행한다.
BEGIN TRAN
DELETE FROM PLAYER;
ROLLBACK;- ROLLBACK 후의 데이터 상태?
-데이터에 대한 변경 사항은 취소된다.
-이전 데이터는 다시 재저장된다.
-관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
- COMMIT과 ROLLBACK을 사용함으로써 얻을 수 있는 효과
-데이터 무결성 보장
-영구적인 변경을 하기 전 데이터의 변경 사항 확인 가능
-논리적으로 연관된 작업을 그룹핑하여 처리 가능
✨SAVEPOINT
저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있다.
복수의 저장점을 정의할 수 있으며, 동일 이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.
SVPT1이라는 저장점을 정의
SAVEPOINT SVPT1;저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명 지정
ROLLBACK TO SVPT1;
SAVE TRANSACTION SVTR1;
저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 지정한다.
ROLLBACK TRANSACTION SVTR1;[예제] SAVEPOINT를 지정하고, PLAYER 테이블에 데이터를 입력한 다음 롤백(ROLLBACK)을 이전에 설정한 저장점까지 실행한다.
[Oracle]
SAVEPOINT SVPT1;
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
ROLLBACK TO SVPT1;[SQL Server]
SAVE TRAN SVPT1;
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
ROLLBACK TRAN SVPT1;
[예제] 먼저 SAVEPOINT를 지정하고 PLAYER 테이블에 있는 데이터를 수정한 다음 롤백(ROLLBACK)을 이전에 설정한 저장점까지 실행한다.
[Oracle]
SAVEPOINT SVPT2;
UPDATE PLAYER
SET WEIGHT = 100;
ROLLBACK TO SVPT2;[SQL Server]
SAVE TRAN SVPT2;
UPDATE PLAYER
SET WEIGHT = 100;
ROLLBACK TRAN SVPT2;
[예제] SAVEPOINT를 지정하고, PLAYER 테이블에 있는 데이터를 삭제한 다음 롤백(ROLLBACK)을 이전에 설정한 저장점까지 실행한다.
[Oracle]
SAVEPOINT SVPT3;
DELETE FROM PLAYER;
ROLLBACK TO SVPT3;[SQL Server]
SAVE TRAN SVPT3;
DELETE FROM PLAYER;
ROLLBACK TRAN SVTP3;
[그림 Ⅱ-1-11]에서 보듯이 저장점 A로 되돌리고 나서 다시 B와 같이 미래 방향으로 되돌릴 수는 없다.
일단 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 되기 때문이다.
즉, ‘ROLLBACK TO A’를 실행한 시점에서 저장점 A 이후에 정의한 저장점 B는 존재하지 않는다.
저장점 지정 없이 “ROLLBACK”을 실행했을 경우 반영안된 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 되돌아간다.
[예제] 새로운 트랜잭션을 시작하기 전에 PLAYER 테이블의 데이터 건수와 몸무게가 100인 선수의 데이터 건수를 확인한다. ※ 몸무게를 확인할 수 있는 WHERE 절 조건과 데이터 건수를 집계하기 위한 COUNT 함수는 1장 5절과 6절에서 설명한다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
-------
480
1개의 행이 선택되었다.
SELECT COUNT(*)
FROM PLAYER
WHERE WEIGHT = 100;
COUNT(*)
-------
0
1개의 행이 선택되었다.[예제] [그림 Ⅱ-1-11]을 확인하기 위해 새로운 트랜잭션을 시작하고 SAVEPOINT A와 SAVEPOINT B를 지정한다. (툴에 AUTO COMMIT 옵션이 적용되어 있는 경우 해제함)
새로운 트랜잭션 시작
INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
1개의 행이 만들어졌다.
SAVEPOINT SVPT_A;
저장점이 생성되었다.
UPDATE PLAYER
SET WEIGHT = 100;
481개의 행이 수정되었다.
SAVEPOINT SVPT_B;
저장점이 생성되었다.
DELETE FROM PLAYER;
481개의 행이 삭제되었다.
현재 위치에서 [예제] CASE 1,2,3을 순서대로 수행해본다.[예제] CASE1. SAVEPOINT B 저장점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
--------
0
1개의 행이 선택되었다.
ROLLBACK TO SVPT_B;
롤백이 완료되었다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
-------
481
1개의 행이 선택되었다.행을 DELETE하기 전으로 돌아갔고, INSERT까지 수행했으므로 480 +1 = 481개의 행 출력
[예제] CASE2. SAVEPOINT A 저장점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
SELECT COUNT(*)
FROM PLAYER
WHERE WEIGHT = 100;
COUNT(*)
-------
481
1개의 행이 선택되었다.
ROLLBACK TO SVPT_A;
롤백이 완료되었다.
SELECT COUNT(*)
FROM PLAYER
WHERE WEIGHT = 100;
COUNT(*)
-------
0
1개의 행이 선택되었다.모든 행의 WEIGHT를 100으로 하기 전으로 돌아갔으니깐 0개의 행이 출력
[예제] CASE3. 트랜잭션 최초 시점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
-------
481
1개의 행이 선택되었다.
ROLLBACK;
롤백이 완료되었다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
-------
480
1개의 행이 선택되었다.✨트랜잭션 정리
해당 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행시 그 변경되는 데이터의 무결성을 보장하는 것이 커밋(COMMIT)과 롤백(ROLLBACK)의 목적이다
커밋은 "변경된 데이터를 테이블에 영구적으로 반영해라”라는 의미를 갖는 것이고,
롤백(ROLLBACK)은 “변경된 데이터가 문제가 있으니 변경 전 데이터로 복귀하라”라는 의미이다.
저장점(SAVEPOINT/SAVE TRANSACTION)은 “데이터 변경을 사전에 지정한 저장점까지만 롤백하라”는 의미이다.
Oracle의 트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK을 실행한 시점에서 종료된다.
단, 다음의 경우에는 COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
- 부연하면, DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
- 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
- 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.
SQL Server의 트랜잭션은 DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식이다. 다음의 경우는 Oracle과 같이 자동으로 트랜잭션이 종료된다.
- 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.
제 5절 WHERE절
✨WHERE 조건절 개요
자료를 검색할 때 SELECT 절과 FROM 절만을 사용하여 기본적인 SQL 문장을 구성한다면, 테이블에 있는 모든 자료들이 결과로 출력되어 실제로 원하는 자료를 확인하기 어려울 수 있다.
사용자들은 자신이 원하는 자료만을 검색하기 위해서 SQL 문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있다.
WHERE 절에는 두 개 이상의 테이블에 대한 조인 조건을 기술하거나 결과를 제한하기 위한 조건을 기술할 수도 있다. WHERE 절의 JOIN 조건에 대해서는 1장 9절에서 설명하고 FROM 절의 JOIN에 대해서는 2장 1절에서 설명하도록 한다. 현실의 데이터베이스는 많은 사용자나 프로그램들이 동시에 접속하여 다량의 트랜잭션을 발생하고 있다. WHERE 조건절을 사용하지 않고 필요 없는 많은 자료들을 데이터베이스로부터 요청하는 SQL 문장은 대량의 데이터를 검색하기 위해 데이터베이스가 설치되어 있는 서버의 CPU나 MEMORY와 같은 시스템 자원(Resources)들을 과다하게 사용한다. 또한 많은 사용자들의 QUERY에 대해 바로바로 처리를 해주지 못하게 되고, 또한 검색된 많은 자료들이 네트워크를 통해서 전달됨으로써 문제점들을 발생시킨다. 이런 문제점을 방지하기 위해 WHERE 절에 조건이 없는 FTS(Full Table Scan) 문장은 SQL 튜닝의 1차적인 검토 대상이 된다. (FTS가 무조건 나쁜 것은 아니며 병렬 처리 등을 이용해 유용하게 사용하는 경우도 많다.)
기본적인 SQL 문장은 Oracle의 경우 필수적으로 SELECT 절과 FROM 절로 이루어져 있다. SQL Server, Sybase 문장은 SELECT 목록에 상수, 변수 및 산술식(열 이름 없이)만 포함되는 경우는 FROM 절이 필요 없지만, 테이블의 칼럼이 사용된 경우는 FROM 절이 필요하다. WHERE 절은 조회하려는 데이터에 특정 조건을 부여할 목적으로 사용하기 때문에 FROM 절 뒤에 오게 된다.
SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명]
FROM 테이블명 WHERE 조건식;
WHERE 절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성된다.
- 칼럼(Column)명 (보통 조건식의 좌측에 위치)
- 비교 연산자
- 문자, 숫자, 표현식 (보통 조건식의 우측에 위치)
- 비교 칼럼명 (JOIN 사용시)
✨연산자의 종류
WHERE 절에 조건식을 사용할 때, 사용되는 비교 연산자에 대해서 살펴본다. 연산자에 대해서 알아보기 전에 위에서 나왔던 조건을 조금 더 복잡하게 바꾸어 본다.
위의 요구 조건을 모두 만족하는 Query 문장을 구성하기 위해서는 다양한 연산자들을 사용해야만 한다. WHERE 절에 사용되는 연산자는 3가지 종류가 있다.
- 비교 연산자 (부정 비교 연산자 포함)
- SQL 연산자 (부정 SQL 연산자 포함)
- 논리 연산자
연산자의 우선순위를 살펴보면 다음과 같다.
- 괄호로 묶은 연산이 제일 먼저 연산 처리된다.
- 연산자들 중에는 부정 연산자(NOT)가 먼저 처리되고,
- 비교 연산자(=,>,>=,<,<=), SQL 비교 연산자(BETWEEN a AND b, IN (list), LIKE, IS NULL)가 처리되고,
- 논리 연산자 중에서는 AND, OR의 순으로 처리된다.
만일 이러한 연산에 있어서 연산자들의 우선순위를 염두에 두지 않고 WHERE 절을 작성한다면 테이블에서 자기가 원하는 자료를 찾지 못하거나, 혹은 틀린 자료인지도 모른 채 사용할 수도 있다. 실수하기 쉬운 비교 연산자와 논리 연산자의 경우 괄호를 사용해서 우선순위를 표시하는 것을 권고한다.
✨비교 연산자
비교 연산자의 종류는 [표 Ⅱ-1-17]과 같으며, 비교 연산자들을 적절히 사용하여 다양한 조건을 구성할 수 있다.
앞의 요구 사항을 다음과 같이 비교 연산자를 적용하여 표현할 수 있다.
각각의 예를 보면 비교 연산자로 소속팀, 포지션, 키와 같은 칼럼(Column)들을 특정한 값들과 조건을 비교하는데 사용되는 것을 알 수 있다.
[예제] 첫 번째 요구 사항인 소속팀이 삼성블루윙즈라는 조건을 WHERE 조건절로 옮겨서 SQL 문장을 완성하여 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = K02;
WHERE TEAM_ID = K02 *
3행에 오류:
ERROR: 열명이 부적합하다.
실행 결과는 “열(COLUMN)명이 부적합하다.”라는 에러 메시지를 보이고 SQL 문장의 세 번째 줄에 오류가 있다고 나와 있다.
TEAM_ID라는 팀명의 데이터 타입은 CHAR(3)인데 비교 연산자 오른쪽에 K02의 값을 작은따옴표(' ')나 큰따옴표(" ")와 같은 인용 부호로 묶어서 처리하지 않았기 때문에 발생하는 에러이다.
CHAR 변수나 VARCHAR2와 같은 문자형 타입을 가진 칼럼을 특정 값과 비교하기 위해서는 인용 부호(작은따옴표, 큰따옴표)로 묶어서 비교 처리를 해야 한다.
하지만 NUMERIC과 같은 숫자형 형태의 값은 인용부호를 사용하지 않는다.
[예제] 첫 번째 요구 사항을 수정하여 다시 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02';
[예제] 세 번째 요구 사항인 포지션이 미드필더(MF)인 조건을 WHERE 조건절로 옮겨서 SQL 문장을 완성하여 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE POSITION = 'MF';
추가적으로 문자 유형간의 비교 조건이 발생하는 경우는 [표 Ⅱ-1-18]과 같이 처리한다.
[예제] 네 번째 요구 사항인 "키가 170 센티미터 이상"인 조건도 WHERE 절로 옮겨서 SQL 문장을 완성하여 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE HEIGHT >= 170;
문자 유형 칼럼의 경우 WHERE TEAM_ID = K02 사례에서 ' ' 표시가 없는 경우 에러가 발생하였지만, 숫자 유형 칼럼의 경우 숫자로 변환이 가능한 문자열(Alpha Numeric)과 비교되면 상대 타입을 숫자 타입으로 바꾸어 비교한다.
예를 들면 [예제]의 WHERE HEIGHT >= 170 조건을 WHERE HEIGHT >= '170' 이라고 표현하더라도, HEIGHT라는 칼럼이 숫자 유형의 변수이므로 내부적으로 ‘170’이라는 문자열을 숫자 유형 170으로 바꾸어 처리한다.
✨SQL 연산자
SQL 연산자는 SQL 문장에서 사용하도록 기본적으로 예약되어 있는 연산자로서 모든 데이터 타입에 대해서 연산이 가능한 4가지 종류가 있다.
앞의 요구 사항을 다음과 같이 비교 연산자와 SQL 비교 연산자를 적용하여 표현할 수 있다.
1) 소속팀코드 IN (삼성블루윙즈 코드(K02), 전남드래곤즈 코드(K07))
2) 포지션 LIKE 미드필더(MF)
3) 키 BETWEEN 170 센티미터 AND 180 센티미터
- IN (list) 연산자
[예제] 소속팀 코드와 관련된 IN (list) 형태의 SQL 비교 연산자를 사용하여 WHERE 절에 사용한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02','K07');[예제] 사원 테이블에서 JOB이 MANAGER이면서 20번 부서에 속하거나, JOB이 CLERK이면서 30번 부서에 속하는 사원의 정보를 IN 연산자의 다중 리스트를 이용해 출력하라.
SELECT ENAME, JOB, DEPTNO FROM EMP
WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));사용자들이 잘 모르고 있는 다중 리스트를 이용한 IN 연산자는 SQL 문장을 짧게 만들어 주면서도 성능 측면에서도 장점을 가질 수 있는 매우 유용한 연산자이므로 적극적인 사용을 권고한다. 다만, 아래 SQL 문장과는 다른 결과가 나오게 되므로 용도를 구분해서 사용해야 한다.
SELECT ENAME, JOB, DEPTNO FROM EMP
WHERE JOB IN ('MANAGER','CLERK') AND DEPTNO IN (20,30);- LIKE 연산자
[예제] 요구 사항의 두 번째 조건에 대해서 LIKE 연산자를 WHERE 절에 적용해서 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE POSITION LIKE 'MF';LIKE의 사전적 의미는 ‘~와 같다’이다.
따라서 위와 같은 경우라면 비교 연산자인 ‘=’을 사용해서 작성해도 같은 결과를 얻을 수 있을 것이다.
그러나 만약 “장”씨 성을 가진 선수들을 조회할 경우는 어떻게 할까? 이런 문제를 해결하기 위해서 LIKE 연산자에서는 와일드카드(WildCard)를 사용할 수 있다. 와일드카드(WildCard)란 한 개 혹은 0개 이상의 문자를 대신해서 사용하기 위한 특수 문자를 의미하며, 이를 조합하여 사용하는 것도 가능하므로 SQL 문장에서 사용하는 스트링(STRING) 값으로 용이하게 사용할 수 있다.
[예제] “장”씨 성을 가진 선수들의 정보를 조회하는 WHERE 절을 작성한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE PLAYER_NAME LIKE '장%';
- BETWEEN a AND b 연산자
[예제] 세 번째로 키가 170 센티미터 이상 180센티미터 이하인 선수들의 정보를 BETWEEN a AND b 연산자를 사용하여 WHERE 절을 완성한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE HEIGHT BETWEEN 170 AND 180;
BETWEEN a AND b는 범위에서 'a'와 'b'의 값을 포함하는 범위를 말하는 것이다.
- IS NULL 연산자
NULL(ASCII 00)은 값이 존재하지 않는 것으로 확정되지 않은 값을 표현할 때 사용한다. 따라서 어떤 값보다 크거나 작지도 않고 ‘ ’(공백, ASCII 32)이나 0(Zero, ASCII 48)과 달리 비교 자체가 불가능한 값인 것이다.
연산 관련 NULL의 특성은 다음과 같다.
- NULL 값과의 수치연산은 NULL 값을 리턴한다.
- NULL 값과의 비교연산은 거짓(FALSE)을 리턴한다.
- 어떤 값과 비교할 수도 없으며, 특정 값보다 크다, 적다라고 표현할 수 없다.
따라서 NULL 값의 비교는 비교 연산자인 “=”, “>”, “>=”, “<”, “=”를 통해서 비교할 수도 없고, 만일 비교 연산을 하게 되면 결과는 거짓(FALSE)을 리턴하고, 수치 연산자(+,-,*,/ 등)를 통해서 NULL 값과 연산을 하게 되면 NULL 값을 리턴한다.
NULL 값의 비교 연산은 IS NULL, IS NOT NULL 이라는 정해진 문구를 사용해야 제대로 된 결과를 얻을 수 있다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE POSITION = NULL;
선택된 레코드가 없다.[예제]의 실행 결과로 “선택된 레코드가 없다.”라는 메시지가 출력되었다.
앞에서 살펴본 대로 WHERE 절에서 POSITION = NULL을 사용했는데 문법 에러가 나지는 않았지만 WHERE 절의 조건이 거짓(FALSE)이 되어 WHERE 절의 조건을 만족하는 데이터를 한건도 얻지 못하게 된 것으로 의미 없는 SQL이 되고 말았다. (null과 비교 연산을 하게 되면 false를 출력한다.)
[예제] POSITION 칼럼(Column) 값이 NULL 값인지를 판단하기 위해서는 IS NULL을 사용하여 다음과 같이 SQL 문장을 수정하여 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, TEAM_ID
FROM PLAYER
WHERE POSITION IS NULL;
✨논리 연산자
논리 연산자는 비교 연산자나 SQL 비교 연산자들로 이루어진 여러 개의 조건들을 논리적으로 연결시키기 위해서 사용되는 연산자라고 생각하면 된다. [표 Ⅱ-1-21]울 보고 실제로 적용되는 예를 통해 사용방법을 이해한다.
[예제] 예를 들어 “소속이 삼성블루윙즈”인 조건과 “키가 170 센티미터 이상”인 조건을 연결해 보면 “소속이 삼성블루윙즈이고 키가 170 센티미터 이상인 조건을 가진 선수들의 자료를 조회”하는 것이 되는 것이다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02' AND HEIGHT >= 170;[예제] “소속이 삼성블루윙즈이거나 전남드래곤즈”인 조건을 SQL 비교 연산자로, “포지션이 미드필더(MF)”인 조건을 비교 연산자로 비교한 결과를 논리 연산
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02','K07') AND POSITION = 'MF';실행 결과를 보면 소속이 (삼성블루윙즈이거나 전남드래곤즈이고) 포지션이 미드필더(MF)인 선수들의 데이터가 조회되었음을 확인할 수 있다.
[예제] 요구 사항을 하나씩 하나씩 AND, OR 같은 논리 연산자를 사용하여 DBMS가 이해할 수 있는 SQL 형식으로 질문을 변경한다. 요구 사항을 순서대로 논리적인 조건을 적용한다.
소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들이어야 하고, 포지션이 미드필더(MF:Midfielder)이어야 한다. 키는 170 센티미터 이상이고 180 이하여야 한다.
1) 소속팀이 삼성블루윙즈 OR 소속팀이 전남드래곤즈
2) AND 포지션이 미드필더
3) AND 키는 170 센티미터 이상
4) AND 키는 180 센티미터 이하
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'
AND POSITION = 'MF'
AND HEIGHT >= 170
AND HEIGHT <= 180;실행 결과의 내용을 보면 포지션이 미드필더(MF: MidFielder)가 아닌 선수들의 명단이 출력되었다.
원하는 데이터는 삼성블루윙즈이거나 전남드래곤즈 중 포지션이 미드필더(MF: Midfielder)인 선수들에 대한 자료만 요청했는데 포지션이 DF나 FW인 선수가 같이 출력된 것이다.
[예제]에서 “소속팀 코드가 삼성블루윙즈(K02) 이거나 전남드래곤즈(K07)”라는 조건을 만족하고 “포지션이 미드필더(MF)”인 조건을 동시에 만족해야 하는데, 위의 SQL 문장에서는 괄호가 누락됨으로서 OR 논리 연산자보다 AND 논리 연산자를 먼저 실행하기 때문에 잘못된 결과를 나타낸 것이다.
논리 연산자들이 여러 개가 같이 사용되었을 때의 처리 우선순위는 ( ), NOT, AND, OR의 순서대로 처리된다.
[예제] 잘못된 결과를 보여 준 SQL 문장을 괄호를 사용하여 다시 적용한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE (TEAM_ID = 'K02' OR TEAM_ID = 'K07')
AND POSITION = 'MF'
AND HEIGHT >= 170
AND HEIGHT <= 180;[예제] IN (list)와 BETWEEN a AND b 연산자를 활용하여 같은 결과를 출력하는 SQL 문장을 작성한다. 두개의 SQL 문장은 DBMS 내부적으로 같은 프로세스를 거쳐 수행되므로 당연히 실행 결과도 같다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02','K07')
AND POSITION = 'MF'
AND HEIGHT BETWEEN 170 AND 180;앞서 살펴본 SQL 비교 연산자인 ‘IN’과 논리 연산자인 ‘OR’은 결과도 같고 내부적으로 처리하는 방법도 같다. 즉, 소속팀이 삼성블루윙즈이거나 전남드래곤즈인 선수들을 조회할 때 WHERE 절에 TEAM_ID = ‘K02’ OR TEAM_ID = ‘K07’라는 논리 연산자 조건과 TEAM_ID IN (‘K02’,‘K07’)라는 SQL 연산자 조건은 같은 기능이다. 그리고 “HEIGHT >= 170 AND HEIGHT <= 180” 라는 비교 연산자 조건과 “HEIGHT BETWEEN 170 AND 180”이라는 SQL 비교 연산자 조건도 결과도 같고 내부적으로 처리되는 방법도 같은 기능이다.
✨부정 연산자
비교 연산자, SQL 비교 연산자에 대한 부정 표현을 부정 논리 연산자, 부정 SQL 연산자로 구분할 수 있다.
[예제] 삼성블루윙즈 소속인 선수들 중에서 포지션이 미드필더(MF:Midfielder)가 아니고, 키가 175 센티미터 이상 185 센티미터 이하가 아닌 선수들의 자료를 찾아본다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02'
AND NOT POSITION = 'MF'
AND NOT HEIGHT BETWEEN 175 AND 185;
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02'
AND NOT POSITION = 'MF'
AND HEIGHT NOT BETWEEN 175 AND 185;[예제] 국적(NATION) 칼럼의 경우 내국인들은 별도 데이터를 입력하지 않았다. 국적 칼럼이 NULL이 아닌 선수와 국적을 표시하라.
SELECT PLAYER_NAME 선수이름, NATION 국적
FROM PLAYER
WHERE NATION IS NOT NULL;
✨ROWNUM, TOP 사용
- ROWNUM
Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column으로써 SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호이며, 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다.
한 건의 행만 가져오고 싶을 때는
- SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM = 1; 이나
- SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= 1; 이나
- SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < 2; 처럼 사용할 수 있다.
Oracle에서 ROWNUM은 1부터 시작하며, 따라서 가장 먼저 조회된 행이 ROWNUM = 1이 되기 때문에 이 쿼리는 첫 번째 행만을 반환합니다.
두 건 이상의 N 행을 가져오고 싶을 때는 ROWNUM = N; 처럼 사용할 수 없으며
- SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N; 이나
- SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM<n+1; 처럼
출력되는 행의 한계를 지정할 수 있다.
추가적인 ROWNUM의 용도로는 테이블 내의 고유한 키나 인덱스 값을 만들 수 있다.
UPDATE MY_TABLE
SET COLUMN1 = ROWNUM;
- TOP 절
SQL Server는 TOP 절을 사용하여 결과 집합으로 출력되는 행의 수를 제한할 수 있다. TOP 절의 표현식은 다음과 같다.
TOP (Expression) [PERCENT] [WITH TIES]- Expression : 반환할 행의 수를 지정하는 숫자이다.
- PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타낸다.
- WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.
한 건의 행만 가져오고 싶을 때는
SELECT TOP(1) PLAYER_NAME FROM PLAYER; 처럼 사용할 수 있다.
두 건 이상의 N 행을 가져오고 싶을 때는
-SELECT TOP(N) PLAYER_NAME FROM PLAYER; 처럼 출력되는 행의 개수를 지정할 수 있다.
SQL 문장에서 ORDER BY 절이 사용되지 않으면 Oracle의 ROWNUM과 SQL Server의 TOP 절은 같은 기능을 하지만, ORDER BY 절이 같이 사용되면 기능의 차이가 발생한다. 이 부분은 1장 8절 ORDER BY 절에서 설명하도록 한다.
제 6절 함수
✨내장 함수 개요
함수는 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다.
본 절에서는 각 벤더에서 제공하는 데이터베이스를 설치하면 기본적으로 제공되는 SQL 내장 함수에 대해 설명한다.
내장 함수는 SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데 사용된다.
내장 함수는 벤더별로 가장 큰 차이를 보이는 부분이지만, 핵심적인 기능들은 이름이나 표현법이 다르더라도 대부분의 데이터베이스가 공통적으로 제공하고 있다.
내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다. 다중행 함수는 다시 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 나눌 수 있는데, 집계 함수는 다음 절에서, 그룹 함수는 2장 5절에서, 윈도우 함수는 2장 6절에서 설명하도록 하고 본 절에서는 단일행 함수에 대해서만 설명한다.
함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 가지고 있다. 단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다. 다중행 함수의 경우도 여러 레코드의 값들을 입력 인수로 사용하는 것이다.
- 단일행 함수 (Single-Row Functions):
- 각 행에 대해 개별적으로 작용하며, 한 번에 하나의 행을 처리합니다.
- 각 행에서 값을 계산하고 각 행에 대해 결과를 반환합니다.
- 대표적인 예로 문자열 함수, 숫자 함수, 날짜 함수 등이 있습니다.
- 예: UPPER(column), ROUND(column)
- 다중행 함수 (Multi-Row Functions):
- 여러 행을 그룹으로 묶어 하나의 결과를 반환합니다.
- 주로 그룹화된 데이터에 대한 집계나 통계를 계산할 때 사용됩니다.
- GROUP BY 절과 함께 자주 사용됩니다.
- 대표적인 예로 집계 함수가 있습니다.
- 예: SUM(column), AVG(column)
SELECT UPPER(name) AS upper_name
FROM employees;SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
단일행 함수는 처리하는 데이터의 형식에 따라서 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수로 나눌 수 있다.
벤더에서 제공하는 내장 함수는 상당히 종류가 많고 벤더별로 사용법이 틀린 경우가 많아, 본 절에서는 Oracle과 SQL Server에서 공통으로 사용하는 중요 함수 위주로 설명을 한다. 함수에 대한 자세한 내용이나 버전에 따른 변경 내용은 벤더에서 제공하는 매뉴얼을 참조하기 바란다. 아래 함수의 예에서 SUBSTR / SUBSTRING으로 표시한 것은 같은 기능을 하지만 다르게 표현되는 Oracle 내장 함수와 SQL Server 내장 함수를 순서대로 표현한 것이다.
단일행 함수의 중요한 특징은 다음과 같다.
- SELECT, WHERE, ORDER BY 절에 사용 가능하다.
- 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.
- 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.
- 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.
- 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능하다.
SELECT 절에서는 값을 변환하거나 연산하여 반환하며, WHERE 절에서는 특정 조건을 기반으로 행을 필터링하고, ORDER BY 절에서는 정렬 기준으로 활용
✨문자형 함수
문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수이다. 몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.
- 문자형 함수들이 적용되었을 때 리턴되는 값을 예를 들어 설명한다.
[예제] ‘SQL Expert’라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다.
[Oracle] - FROM 절이 반드시 있어야 함!!
SELECT LENGTH('SQL Expert') FROM DUAL;Oracle 데이터베이스에서 제공되는 가상의 테이블입니다. 이 테이블은 특정 목적으로 만들어진 더미(dummy) 테이블이며, 주로 상수나 계산된 값을 조회하고자 할 때 사용됩니다.
상수 값 조회:
- DUAL 테이블은 상수 값을 조회하는 데 사용됩니다. 예를 들어, SELECT 'Hello, World!' FROM DUAL;과 같이 문자열 상수를 반환할 수 있습니다.
SELECT 'Hello, World!' FROM DUAL;계산된 값 조회:
- DUAL을 사용하여 간단한 수식이나 함수의 결과를 조회할 수 있습니다.
SELECT SYSDATE FROM DUAL; -- 현재 날짜 및 시간 조회Oracle에서 DUAL 테이블은 시스템에 이미 존재하고 있으며, 사용자가 직접 생성하거나 변경할 수 없습니다. 이는 Oracle에서 기본적으로 제공하는 시스템 테이블로 간주됩니다.
예제 및 실행 결과를 보면 함수에 대한 결과 값을 마치 테이블에서 값을 조회했을 때와 비슷하게 표현한다. Oracle은 SELECT 절과 FROM 절 두 개의 절을 SELECT 문장의 필수 절로 지정하였으므로 사용자 테이블이 필요 없는 SQL 문장의 경우에도 필수적으로 DUAL이라는 테이블을 FROM 절에 지정한다.
DUAL 테이블의 특성은 다음과 같다.
- 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
- SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
- DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 1건 포함하고 있다.
[Oracle]
DESC DUAL;
칼럼 NULL 가능 데이터 유형
---------------- -------- -----------
DUMMY VARCHAR2(1)[Oracle]
SELECT * FROM DUAL;
DUMMY
-----
X
1개의 행이 선택되었다.
반면 Sybase나 SQL Server의 경우에는 SELECT 절만으로도 SQL 문장이 수행 가능하도록 정의하였기 때문에 DUAL이란 DUMMY 테이블이 필요 없다. 그러나 Sybase나 SQL Server의 경우에도 사용자 테이블의 칼럼을 사용할 때는 FROM 절이 필수적으로 사용되어야 한다.
[예제] ‘SQL Expert’라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다.
[SQL Server] - FROM 절 없어도 됨!!
SELECT LEN('SQL Expert') AS ColumnLength;
ColumnLength
----------
10
[예제] 선수 테이블에서 CONCAT 문자형 함수를 이용해 축구선수란 문구를 추가한다.
SELECT CONCAT(PLAYER_NAME, ' 축구선수') 선수명
FROM PLAYER;
CONCAT 함수는 Oracle의 '||' 합성 연산자와 같은 기능이다.
SELECT PLAYER_NAME || ' 축구선수' AS 선수명
FROM PLAYER;
SQL Server에서 위의 예제와 같은 결과를 얻으려면 아래와 같이 수행하면 된다.
SELECT PLAYER_NAME + ' 축구선수' AS 선수명
FROM PLAYER;
실행 결과를 보면 실제적으로 함수가 모든 행에 대해 적용되어 ‘~ 축구선수’라는 각각의 결과로 출력되었다.
특별한 제약 조건이 없다면 함수는 여러 개 중첩하여 사용이 가능하다. 함수 내부에 다른 함수를 사용하며 안쪽에 위치해 있는 함수부터 실행되어 그 결과 값이 바깥쪽의 함수에 인자(Argument)로 사용되는 것이다.
[예제] 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하시오. 연결연산자의 결과가 LENGTH(SQL Server는 LEN 사용) 함수의 인수가 된다.
[Oracle] || 합성 연산자 사용! 길이는 LENGTH() 함수
SELECT STADIUM_ID, DDD||TEL as TEL, LENGTH(DDD||TEL) as T_LEN
FROM STADIUM;[SQL Server] + 합성연산자 사용! 길이는 LEN() 함수
SELECT STADIUM_ID, DDD+TEL a s TEL, LEN(DDD+TEL) as T_LEN
FROM STADIUM;✨숫자형 함수
숫자형 함수는 숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수이다.
- 숫자형 함수들이 적용되었을 때 리턴되는 값을 예를 들어 설명한다.
[예제] 소수점 이하 한 자리까지 반올림 및 내림하여 출력한다.
SELECT ENAME, ROUND(SAL/12,1), TRUNC(SAL/12,1) FROM EMP;
[예제] 정수 기준으로 반올림 및 올림하여 출력한다.
SELECT ENAME, ROUND(SAL/12), CEILING(SAL/12) FROM EMP;- 올림 (Ceiling):
- 대부분의 언어에서 ceil 또는 ceiling 함수가 사용됩니다.
- 정확한 정의는 "주어진 숫자 이상의 가장 작은 정수"입니다.
- 내림 (Floor):
- 대부분의 언어에서 floor 함수가 사용됩니다.
- 정확한 정의는 "주어진 숫자 이하의 가장 큰 정수"입니다.
- 내림 (Truncate):
- 대부분의 언어에서 trunc 또는 floor 함수가 사용됩니다.
- 정확한 정의는 "소수 부분을 제거하여 가장 가까운 정수로 내림"입니다.
- 반올림 (Round):
- 대부분의 언어에서 round 함수가 사용됩니다.
- 정확한 정의는 "주어진 숫자에서 가장 가까운 정수로 반올림"입니다.
✨날짜형 함수
날짜형 함수는 DATE 타입의 값을 연산하는 함수이다.
Oracle의 TO_NUMBER(TO_CHAR( )) 함수의 경우 변환형 함수로 구분할 수도 있으나 SQL Server의 YEAR, MONTH,DAY 함수와 매핑하기 위하여 날짜형 함수에서 설명한다. EXTRACT/DATEPART는 같은 기능을 하는 Oracle 내장 함수와 SQL Server 내장 함수를 표현한 것이다
DATE 변수가 데이터베이스에 어떻게 저장되는지 살펴보면, 데이터베이스는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다.
날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다. 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. 즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.
[예제] Oracle의 SYSDATE 함수와 SQL Server의 GETDATE( ) 함수를 사용하여 데이터베이스에서 사용하는 현재의 날짜 데이터를 확인한다. 날짜 데이터는 시스템 구성에 따라 다양하게 표현될 수 있으므로 사용자마다 다른 결과가 나올 수 있다.
[Oracle]
SELECT SYSDATE FROM DUAL;
SYSDATE
--------
12/07/18[SQL Server]
SELECT GETDATE() AS CURRENTTIME;
CURRENTTIME
-------------
2012-07-18 13:10:02.047[Oracle]
[예제] 사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력한다. 아래 4개의 SQL 문장은 같은 기능을 하는 SQL 문장이다.
SELECT ENAME, HIREDATE,
EXTRACT(YEAR FROM HIREDATE) 입사년도,
EXTRACT(MONTH FROM HIREDATE) 입사월,
EXTRACT(DAY FROM HIREDATE) 입사일
FROM EMP;SELECT ENAME, HIREDATE,
TO_NUMBER(TO_CHAR(HIREDATE, 'YYYY')) 입사년도,
TO_NUMBER(TO_CHAR(HIREDATE, 'MM')) 입사월,
TO_NUMBER(TO_CHAR(HIREDATE, 'DD')) 입사일
FROM EMP;SELECT ENAME, HIREDATE,
DATEPART(YEAR, HIREDATE) 입사년도,
DATEPART(MONTH, HIREDATE) 입사월,
DATEPART(DAY, HIREDATE) 입사일
FROM EMP;SELECT ENAME, HIREDATE,
YEAR(HIREDATE) 입사년도,
MONTH(HIREDATE) 입사월,
DAY(HIREDATE) 입사일
FROM EMP;✨반환형 함수
변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수이다.
변환형 함수는 크게 두 가지 방식이 있다.
암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.
명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수는 다음과 같다.
변환형 함수를 사용하여 출력 형식을 지정할 때, 숫자형과 날짜형의 경우 상당히 많은 포맷이 벤더별로 제공된다. 벤더별 데이터 유형과 함께 데이터 출력의 포맷 부분은 벤더의 고유 항목이 많으므로 매뉴얼을 참고하기 바라며, 아래는 대표적인 사례 몇 가지만 소개한다. [예제] 날짜를 정해진 문자 형태로 변형한다.
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜,
TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형
FROM DUAL;
날짜 문자형
--------- ----------------
2012-07-19 2012. 7월 , 월요일SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS CURRENTDATE;
CURRNETDATE
----------
2012/07/19- GETDATE(): 현재 날짜와 시간을 반환하는 함수입니다. 데이터베이스 시스템에 따라 다르지만 대부분의 시스템에서는 현재 시스템 시간을 기준으로 합니다.
- CONVERT(VARCHAR(10), GETDATE(), 111): 날짜를 특정한 형식의 문자열로 변환하는 함수입니다.
- VARCHAR(10): 문자열의 최대 길이를 나타내는 것으로, 여기서는 10자로 설정되어 있습니다.
- 111: 날짜 형식을 지정하는 코드입니다. 111은 "년/월/일" 형식을 나타냅니다.
- AS CURRENTDATE: 결과의 컬럼에 대한 별칭(alias)으로, 결과를 "CURRENTDATE"라는 이름으로 나타내게 됩니다.
따라서 이 쿼리는 현재 날짜를 "년/월/일" 형식의 문자열로 변환하여 "CURRENTDATE"라는 이름의 결과 컬럼으로 반환합니다. 결과는 데이터베이스 시스템의 현재 날짜와 시간에 따라 다를 것입니다.
[예제] 금액을 달러와 원화로 표시한다.
SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 환율반영달러, TO_CHAR(123456789,'L999,999,999') 원화
FROM DUAL;
환율반영달러 원화
------------ ----------
$102,880.66 \123,456,789
두 번째 칼럼의 L999에서 L은 로칼 화폐 단위를 의미한다.
[예제] 팀(TEAM) 테이블의 ZIP 코드1과 ZIP 코드2를 숫자로 변환한 후 두 항목을 더한 숫자를 출력한다.
[Oracle]
SELECT TEAM_ID, TO_NUMBER(ZIP_CODE1,'999') + TO_NUMBER(ZIP_CODE2,'999') 우편번호합
FROM TEAM;
이 SQL 쿼리는 TEAM 테이블에서 TEAM_ID와 ZIP_CODE1, ZIP_CODE2 컬럼을 사용하여 새로운 컬럼인 우편번호합을 계산하는 것입니다. 각 행에서 ZIP_CODE1과 ZIP_CODE2를 숫자로 변환하고, 그 결과를 더하여 우편번호합이라는 이름의 새로운 컬럼을 만듭니다.
여기서 사용된 함수들에 대한 설명은 다음과 같습니다:
- TO_NUMBER(ZIP_CODE1,'999'): ZIP_CODE1을 세 자리 숫자로 변환합니다. '999'는 세 자리 숫자로 표현되도록 하는 포맷입니다.
- TO_NUMBER(ZIP_CODE2,'999'): ZIP_CODE2를 세 자리 숫자로 변환합니다.
- TO_NUMBER(ZIP_CODE1,'999') + TO_NUMBER(ZIP_CODE2,'999'): 위에서 변환한 두 값을 더합니다.
따라서 이 쿼리는 TEAM 테이블에서 TEAM_ID와 ZIP_CODE1, ZIP_CODE2를 이용하여 각 팀의 우편번호를 합한 값을 우편번호합 컬럼으로 가져오는 것입니다. 결과는 TEAM_ID와 우편번호합 두 개의 컬럼으로 이루어진 새로운 결과 세트가 될 것입니다.
SELECT TEAM_ID, CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합
FROM TEAM;✨CASE 표현
CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다. ANSI/ISO SQL 표준에는 CASE Expression이라고 표시되어 있는데, 함수와 같은 성격을 가지고 있으며 Oracle의 Decode 함수와 같은 기능을 하므로 단일행 내장 함수에서 같이 설명을 한다.
일반 프로그램의 IF-THEN-ELSE-END 로직과 같다.
IF SAL > 2000
THEN REVISED_SALARY = SAL
ELSE REVISED_SALARY = 2000
END-IF.
같은 기능을 하는 CASE 표현이다.
SELECT ENAME,
CASE WHEN SAL > 2000
THEN SAL
ELSE 2000
END REVISED_SALARY
FROM EMP;
CASE 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있고, Oracle의 경우 DECODE 함수를 사용할 수도 있다.
IF-THEN-ELSE 논리를 구현하는 CASE Expressions은 Simple Case Expression과 Searched Case Expression 두 가지 표현법 중에 하나를 선택해서 사용하게 된다.
CASE
SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSION 조건
ELSE 표현절
END
첫 번째 SIMPLE_CASE_EXPRESSION은 CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고, 다음 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 아닌지 판단하는 문장으로 EQUI(=) 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용할 수 있는 장점이 있다. Oracle의 DECODE 함수와 기능면에서 동일하다.
CASE EXPR
WHEN COMPARISON_EXPR THEN RETURN_EXPR
ELSE 표현절
END
[예제] 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분하라.
SELECT LOC,
CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'DALLAS' THEN 'CENTER'
ELSE 'ETC'
END as AREA
FROM DEPT;
두 번째 SEARCHED_CASE_EXPRESSION은 CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN 절에서 EQUI(=) 조건 포함 여러 조건(>, >=, <, <=)을 이용한 조건절을 사용할 수 있기 때문에 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 적용할 수 있는 장점이 있다.
CASE
WHEN CONDITION THEN RETURN_EXPR
ELSE 표현절
END
[예제] 사원 정보에서 급여가 3000 이상이면 상등급으로, 1000 이상이면 중등급으로, 1000 미만이면 하등급으로 분류하라.
SELECT ENAME,
CASE WHEN SAL >= 3000 THEN 'HIGH'
WHEN SAL >= 1000 THEN 'MID'
ELSE 'LOW'
END AS SALARY_GRADE
FROM EMP;
CASE 표현은 함수의 성질을 가지고 있으므로, 다른 함수처럼 중첩해서 사용할 수 있다.
[예제] 사원 정보에서 급여가 2000 이상이면 보너스를 1000으로, 1000 이상이면 5000으로, 1000 미만이면 0으로 계산한다.
SELECT ENAME, SAL,
CASE WHEN SAL >= 2000 THEN 1000
ELSE (CASE WHEN SAL >= 1000
THEN 500
ELSE 0
END)
END as BONUS
FROM EMP;
case문 표현 방식은 두 가지로 나뉘고, 중첩이 가능하다. 각각의 표현 방식을 잘 알아두자.
✨ NULL 관련 함수 ✨✨
가. NVL/ISNULL 함수
다시 한 번 NULL에 대한 특성을 정리한다.
- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
- 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
- 널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다.
- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 ‘x’ 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.
NVL/ISNULL 함수를 유용하게 사용하는 예는 산술적인 계산에서 데이터 값이 NULL일 경우이다.
칼럼 간 계산을 수행하는 경우 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없는 경우가 발생한다.
이런 경우는 NVL 함수를 사용해서 숫자인 0으로 변환을 시킨 후 계산을 해서 원하는 데이터를 얻는다. 관계형 데이터베이스의 중요한 데이터인 NULL을 처리하는 주요 함수는 다음과 같다.
Oracle의 경우 NVL 함수를 사용한다.
NVL (NULL 판단 대상,‘NULL일 때 대체값’)SELECT NVL(NULL, 'NVL-OK') NVL_TEST
FROM DUAL;
NVL_TEST
-------
NVL-OK
1개의 행이 선택되었다.SELECT NVL('Not-Null', 'NVL-OK') NVL_TEST
FROM DUAL;
NVL_TEST
-------
Not-Null
1개의 행이 선택되었다.
SQL Server의 경우 ISNULL 함수를 사용한다.
ISNULL (NULL 판단 대상,‘NULL일 때 대체값’)SELECT ISNULL(NULL, 'NVL-OK') ISNULL_TEST ;
ISNULL_TEST
---------
NVL-OK
1개의 행이 선택되었다.SNULL('Not-Null', 'NVL-OK') ISNULL_TEST ;
ISNULL_TEST
---------
Not-Null
1개의 행이 선택되었다.
[예제] 선수 테이블에서 성남 일화천마(K08) 소속 선수의 이름과 포지션을 출력하는데, 포지션이 없는 경우는 '없음'으로 표시한다.
[Oracle]
SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'[SQL Server]
SELECT PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'
[예제] NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 벤더 공통적으로 CASE 문장으로 표현할 수 있다
SELECT PLAYER_NAME 선수명, POSITION,
CASE WHEN POSITION IS NULL
THEN '없음'
ELSE POSITION
END AS 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'
[예제] 급여와 커미션을 포함한 연봉을 계산하면서 NVL 함수의 필요성을 알아본다.
SELECT ENAME 사원명, SAL 월급, COMM 커미션,
(SAL * 12) + COMM 연봉A, (SAL * 12) + NVL(COMM,0) 연봉B
FROM EMP;
실행 결과에서 월급에 커미션을 더해서 연봉을 계산하는 산술식이 있을 때 커미션에 NULL 값이 있는 경우 커미션 값에 NVL() 함수를 사용하지 않으면 연봉A의 계산 결과가 NULL이 되어서 잘못 계산한 결과를 확인할 수 있다. 따라서 연봉B 결과와 같이 NVL(COMM,0)처럼 NULL 값을 0으로 변환하여 연봉을 계산해야 하는 것이다. 물론 곱셈을 사용해야 하는 경우에는 NVL(COMM,1)을 해야 한다. 그러나 NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생할 수 있으므로 굳이 NVL 함수를 사용할 필요가 없다. 다중행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 다중행 함수의 대상에서 제외한다. 예를 들면 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 성적에 대해서 평균값을 구하게 된다. 자세한 내용은 1장 7절에서 추가로 설명한다.
나. NULL과 공집합
- 일반적인 NVL/ISNULL 함수 사용
STEP1. 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저를 출력한다.
- 공집합의 NVL/ISNULL 함수 사용
SELECT 1 FROM DUAL WHERE 1 = 2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리이며, 위와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 하고, NULL 데이터와는 또 다르게 이해해야 한다.
STEP1. 공집합을 발생시키기 위해 사원 테이블에 존재하지 않는 'JSC'라는 이름으로 데이터를 검색한다.
STEP2. NVL/ISNULL 함수를 이용해 공집합을 9999로 바꾸고자 시도한다.
STEP3. 적절한 집계 함수를 찾아서 NVL 함수 대신 적용한다.
STEP4. 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 9999로 출력하게 한다.
Oracle의 SQL*PLUS 같이 화면에서 데이터베이스와 직접 대화하는 환경이라면, 화면상에서 “데이터를 찾을 수 없다.”라는 문구로 공집합을 구분할 수 있지만, 다른 개발 언어 내에 SQL 문장이 포함된 경우에는 NULL과 공집합을 쉽게 구분하기 힘들다. 개발자들은 NVL/ISNULL 함수를 사용해야 하는 경우와, 집계 함수를 포함한 NVL/ISNULL 함수를 사용해야 하는 경우와, 1장 7절에서 설명할 NVL/ISNULL 함수를 포함한 집계 함수를 사용하지 않아야 될 경우까지 잘 이해해서 NVL/ISNULL 함수를 정확히 사용해야 한다.
공집합 -> NULL BY 집계함수!
- 공집합 (Empty Set):
- 공집합은 특정 집합에 속하는 원소가 하나도 없는 경우를 나타냅니다.
- 데이터베이스에서는 결과 집합이 비어 있는 경우를 가리킬 때 "공집합"이라는 용어를 사용합니다.
- 예를 들어, SELECT 문의 WHERE 조건에 맞는 행이 없는 경우 해당 결과 집합은 공집합입니다.
- NULL:
- NULL은 데이터가 없거나 알 수 없는 값을 나타냅니다.
- 데이터베이스에서 컬럼 값이 없는 경우, 해당 값은 NULL일 수 있습니다.
- 집계 함수 (예: AVG, SUM)를 사용할 때, 데이터가 하나도 없는 경우에 해당 집계 함수의 결과는 NULL이 됩니다.
따라서 "공집합을 NULL로 집계함수로 대체한다"는 말은 특정 집계 함수에 대한 입력 데이터가 없는 경우 그 결과로 NULL이 반환된다는 것을 의미합니다. 이는 집합 자체가 비어 있는 경우나 해당 데이터의 값이 NULL인 경우 등에 해당할 수 있습니다.
다. NULLIF
NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다. 특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있다.
NULLIF (EXPR1, EXPR2)
[예제] 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR를 표시한다.
[예제] NULLIF 함수를 CASE 문장으로 표현할 수 있다.
실행 결과를 보면 MGR의 값이 7698이란 상수가 같은 경우 NUIF칼럼에 NULL이 표시되었다. KING이 속한 행의 NUIF 칼럼에 NULL이 표시된 것은 원래 MGR 데이터가 NULL이었기 때문이다.
라. 기타 NULL 관련 함수 (COALESCE)
COALESCE 함수는 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다. 만일 모든 EXPR이 NULL이라면 NULL을 리턴한다.
COALESCE (EXPR1, EXPR2, …)
[예제] 사원 테이블에서 커미션을 1차 선택값으로, 급여를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우는 NULL로 표시한다.
제 7절 GROUP BY, HAVING 절
✨집계 함수
여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 다중행 함수 중 집계 함수(Aggregate Function)의 특성은 다음과 같다.
- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.
- GROUP BY 절은 행들을 소그룹화 한다.
- SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.
ANSI/ISO에서 데이터 분석 기능으로 분류한 함수 중 기본적인 집계 함수는 본 절에서 설명하고, ROLLUP, CUBE, GROUPING SETS 같은 GROUP 함수는 2장 5절에서, 다양한 분석 기능을 가진 WINDOW 함수는 2장 6절에서 설명한다.
집계 함수명 ( [DISTINCT | ALL] 칼럼이나 표현식 )
- ALL : Default 옵션이므로 생략 가능함
- DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션임
자주 사용되는 주요 집계 함수들은 다음과 같다. 집계 함수는 그룹에 대한 정보를 제공하므로 주로 숫자 유형에 사용되지만, MAX, MIN, COUNT 함수는 문자, 날짜 유형에도 적용이 가능한 함수이다.
[예제] 일반적으로 집계 함수는 GROUP BY 절과 같이 사용되지만 아래와 같이 테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로도 사용 가능하다.
실행 결과를 보면 COUNT(HEIGHT)는 NULL값이 아닌 키(HEIGHT) 칼럼의 건수만 출력하므로 COUNT(*)의 480보다 작은 것을 볼 수 있다. 그 이유는 COUNT(*) 함수에 사용된 와일드카드(*)는 전체 칼럼을 뜻하는데 전체 칼럼이 NULL인 행은 존재할 수 없기 때문에 결국 COUNT(*)는 전체 행의 개수를 출력한 것이고, COUNT(HEIGHT)는 HEIGHT 칼럼 값이 NULL인 33건은 제외된 건수의 합이다.
✨GROUP BY 절
WHERE 절을 통해 조건에 맞는 데이터를 조회했지만 테이블에 1차적으로 존재하는 데이터 이외의 정보, 예를 들면 각 팀별로 선수가 몇 명인지, 선수들의 평균 신장과 몸무게가 얼마나 되는지, 또는 각 팀에서 가장 큰 키의 선수가 누구인지 등의 2차 가공 정보도 필요하다. GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.
SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼(Column)이나 표현식]
[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 절 뒤에 위치한다.
일부 데이터베이스의 과거 버전에서 데이터베이스가 GROUP BY 절에 명시된 칼럼의 순서대로 오름차순 정렬을 자동으로 실시(비공식적인 지원이었음)하는 경우가 있었으나, 원칙적으로 관계형 데이터베이스 환경에서는 뒤에서 언급할 ORDER BY 절을 명시해야 데이터 정렬이 수행된다. ANSI/ISO 기준에서도 데이터 정렬에 대한 내용은 ORDER BY 절에서만 언급되어있지, GROUP BY 절에는 언급되어 있지 않다.
[예제] K-리그 선수들의 포지션별 평균키는 어떻게 되는가란 요구 사항을 접수하였다. GROUP BY 절을 사용하지 않고 집계 함수를 사용했을 때 어떤 결과를 보이는지 포지션별 평균키를 구해본다.
GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹 단위의 칼럼과 집계 함수를 사용할 수 있다. 그렇지 않으면 예제와 같이 에러를 발생하게 된다.
[예제] SELECT 절에서 사용된 포지션이라는 한글 ALIAS를 GROUP BY 절의 기준으로 사용해본다.
실행 결과를 살펴보면 GROUP BY 절에 “포지션”이라고 표시된 부분에 에러가 발생했다는 것을 알 수 있다. 칼럼에 대한 ALIAS는 SELECT 절에서 정의하고 ORDER BY 절에서는 재활용할 수 있지만, GROUP BY 절에서는 ALIAS 명을 사용할 수 없다는 것을 보여 주는 사례이다.
[예제] 포지션별 최대키, 최소키, 평균키를 출력한다. (포지션별이란 소그룹의 조건을 제시하였기 때문에 GROUP BY 절을 사용한다.)
실행 결과를 보면 포지션별로 평균키 외에도 인원수, 키대상 인원수, 최대키, 최소키가 제대로 출력된 것을 확인할 수 있다. ORDER BY 절이 없기 때문에 포지션 별로 정렬은 되지 않았다. 추가로 포지션과 키 정보가 없는 선수가 3명이라는 정보를 얻을 수 있으며, 포지션이 DF인 172명 중 30명은 키에 대한 정보가 없는 것도 알 수 있다. GK, DF, FW, MF의 최대키, 최소키, 평균키를 구할 때 키 값이 NULL인 경우는 계산 대상에서 제외된다. 즉, 포지션 DF의 최대키, 최소키, 평균키 결과는 키 값이 NULL인 30명을 제외한 142명을 대상으로 수행한 통계 결과이다.
✨HAVING 절
[예제] K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 표시하라는 요구 사항이 접수되었으므로 WHERE 절과 GROUP BY 절을 사용해 SQL 문장을 작성한다.
실행 결과에서 WHERE 절의 집계 함수 AVG(HEIGHT) 부분에서 “집계 함수는 허가되지 않는다”는 에러 메시지가 출력되었다. 즉, WHERE 절에는 AVG()라는 집계 함수는 사용할 수 없다.
WHERE 절은 FROM 절에 정의된 집합(주로 테이블)의 개별 행에 WHERE 절의 조건절이 먼저 적용되고, WHERE 절의 조건에 맞는 행이 GROUP BY 절의 대상이 된다. 그런 다음 결과 집합의 행에 HAVING 조건절이 적용된다. 결과적으로 HAVING 절의 조건을 만족하는 내용만 출력된다.
즉, HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있다.
Q. where절에서 집계함수가 허용되지 않는 이유는 어떤 집합에 대해 집계해야 되는지 몰라서 맞지? 보통은 group by로 테이블을 그룹핑하고서 각 그룹에 대한 집계를 having절에 써줌으로써 한 뒤에 select하기 때문에
맞습니다. WHERE 절에서 집계 함수를 사용할 수 없는 이유 중 하나는 WHERE 절은 개별 레코드에 대한 조건을 검사하기 때문입니다. 즉, 각 행의 조건을 판단하는 단계에서는 행이 속한 그룹에 대한 집계를 알 수 없습니다.
반면에 GROUP BY 절 다음에 오는 HAVING 절은 그룹에 대한 조건을 판단하므로 집계 함수를 사용할 수 있습니다. GROUP BY 절을 통해 데이터를 그룹화하고, HAVING 절을 사용하여 각 그룹에 대한 조건을 지정합니다.
일반적으로 집계 함수는 전체 집합이나 그룹에 대한 특정 조건을 검사하는 용도로 사용되므로, HAVING 절이나 SELECT 절에서 주로 활용됩니
[예제] HAVING 조건절에는 GROUP BY 절에서 정의한 소그룹의 집계 함수를 이용한 조건을 표시할 수 있으므로, HAVING 절을 이용해 평균키가 180 센티미터 이상인 정보만 표시한다.
실행 결과에서 전체 4개 포지션 중에서 평균 키가 180cm가 넘는 2개의 데이터만 출력된 것을 확인할 수 있다.
[예제] SQL 문장은 GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행한다.
GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행하더라도 문법 에러도 없고 결과물도 동일한 결과를 출력한다.
그렇지만, SQL 내용을 보면, 포지션이란 소그룹으로 그룹핑(GROUPING)되어 통계 정보가 만들어지고, 이후 적용된 결과 값에 대한 HAVING 절의 제한 조건에 맞는 데이터만을 출력하는 것이므로 논리적으로 GROUP BY 절과 HAVING 절의 순서를 지키는 것을 권고한다.
[예제] K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가란 요구 사항이 접수되었다. WHERE 절과 GROUP BY 절을 사용한 SQL과 GROUP BY 절과 HAVING 절을 사용한 SQL을 모두 작성한다.
GROUP BY 소그룹의 데이터 중 일부만 필요한 경우, GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산을 하는 방법과, GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링 하는 두 가지 방법을 사용할 수 있다.
같은 실행 결과를 얻는 두 가지 방법 중 HAVING 절에서 TEAM_ID 같은 GROUP BY 기준 칼럼에 대한 조건을 추가할 수도 있으나, 가능하면 WHERE 절에서 조건절을 적용하여 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 사용 측면에서 바람직하다.
WHERE 절을 사용하는 경우:
- WHERE 절은 데이터를 필터링하는 데 사용됩니다.
- 그룹화 이전에 조건을 적용하므로 집계 함수를 계산하는 대상 행을 줄일 수 있습니다.
- GROUP BY에 의해 그룹화된 전체 데이터 집합을 줄이는 데 효과적입니다.
SELECT TEAM_ID, AVG(SCORE) AS AVG_SCORE
FROM PLAYER
WHERE TEAM_ID = 'A'
GROUP BY TEAM_ID;HAVING 절을 사용하는 경우:
- HAVING 절은 그룹화된 데이터에 대한 조건을 적용하는 데 사용됩니다.
- GROUP BY 이후에 계산된 집계 함수에 대한 조건을 지정할 수 있습니다.
SELECT TEAM_ID, AVG(SCORE) AS AVG_SCORE
FROM PLAYER
GROUP BY TEAM_ID
HAVING AVG(SCORE) > 80;따라서, WHERE 절을 사용하여 가능한 한 일찍 필터링하고 GROUP BY 이후에 HAVING 절을 사용하여 추가적인 집계 조건을 적용하는 것이 자원 사용 측면에서 효율적일 수 있습니다. 이것은 불필요한 계산을 줄이고 쿼리 성능을 향상시킬 수 있습니다.
일반적으로 집계 함수가 아닌 경우 GROUP BY 절에 대한 조건은 WHERE 절에 포함시킬 수 있습니다. 그러나 집계 함수를 사용하는 경우에는 HAVING 절을 사용해야 합니다.
집계 함수를 사용하고 있다면, GROUP BY 절에 지정한 열 이외의 열에 대한 조건은 HAVING 절에 지정해야 합니다. 이는 데이터를 그룹화하고 나서 조건을 적용하기 때문입니다.
[예제] 포지션별 평균키만 출력하는데, 최대키가 190cm 이상인 선수를 가지고 있는 포지션의 정보만 출력한다.
-> 모든 테이블에 대해 그룹핑 후 조건을 적용해야 되므로 HAVING절에 조건 적어야 됨.
SQL을 보면 SELECT 절에서 사용하지 않는 MAX 집계 함수를 HAVING 절에서 조건절로 사용한 사례이다.
즉, HAVING 절은 SELECT 절에 사용되지 않은 칼럼이나 집계 함수가 아니더라도 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
이 부분은 1장 8절의 SELECT 문장의 실행 순서에서 추가 설명한다.
여기서 주의할 것은 WHERE 절의 조건 변경은 대상 데이터의 개수가 변경되므로 결과 데이터 값이 변경될 수 있지만, HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드의 개수만 변경될 수 있다.
실행 결과를 보면 다른 결과 값의 변경 없이 MAX(HEIGHT)가 189cm로 190cm 미만인 MF 포지션의 데이터만 HAVING 조건에 의해 누락된 것을 확인할 수 있다. (다른 포포지션의 통계 정보는 다음과 같ss=bg_gray>포지션 인원수 키대상 최대키 최소키 평균키 ----- ----- ----- ----- ---- ---- MF 162 162 189 165 176.31
먼저 테이블에 대해 WHERE 조건절을 통해 조건에 맞는 행만 남기기 -> GROUP BY 있으면 그룹 짓기 -> 그루핑한 그룹에 대해 HAVING 조건에 맞는 칼럼만 남기기
모든 테이블(전체 칼럼)에 대해 그룹핑 후 조건을 적용해야 하면 HAVING 절을 사용해야 되고, 일부에 대해 결과 출력해야 하면 FROM절이나 HAVING 절을 사용해야 한다.
왜 WHERE 절에서는 결과 데이터 값 변경되고, HAVING은 출력되는 레코드 개수만 변경되는 거지????
그루핑된 것들에서 집계 함수로 계산된 값 중에서 조건에 맞지 않는 값은 제거하는 거니깐 레코드 개수만 변경된다는 것이고, WHRER은 맨 처음에 실행되어서 그루핑 전에 실행되어서 인가?
올바른 이해입니다. WHERE 절은 데이터를 필터링하는 역할을 하는데, 이는 데이터베이스의 레코드 수 자체를 변경할 수 있습니다. WHERE 절은 테이블의 레코드를 선별하여 쿼리에 사용되는 레코드의 수를 변경합니다.
반면에 HAVING 절은 GROUP BY와 함께 사용되어 집계 함수로 그룹화된 데이터에 조건을 적용합니다. HAVING 절은 이미 그룹화된 데이터에 대한 조건을 확인하는 단계에서 사용되기 때문에 전체 결과의 레코드 수는 변경되지 않습니다.
예를 들어, 다음과 같은 쿼리를 생각해보겠습니다:
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING AVG(salary) > 60000;이 경우, WHERE 절은 초기에 employees 테이블에서 salary > 50000인 레코드를 필터링하고, GROUP BY와 AVG 함수를 이용하여 각 부서의 평균 연봉을 계산합니다. 그런 다음 HAVING 절이 이미 계산된 그룹의 결과에 조건을 적용합니다. 이때 HAVING 절은 이미 그룹화된 데이터를 대상으로 하기 때문에 전체 결과의 레코드 수는 변경되지 않습니다.
따라서 WHERE 절은 레코드 자체를 필터링하는데 사용되고, HAVING 절은 이미 계산된 결과에 조건을 적용하여 필터링하는데 사용됩니다.
✨CASE 표현을 활용한 월별 데이터 집계
“집계 함수(CASE( ))~GROUP BY” 기능은, 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법이다.
부서별로 월별 입사자의 평균 급여를 알고 싶다는 고객의 요구사항이 있는데, 입사 후 1년마다 급여 인상이나 보너스 지급과 같은 일정이 정기적으로 잡힌다면 업무적으로 중요한 정보가 될 수 있다.
STEP1. 개별 데이터 확인
[예제] 먼저 개별 입사정보에서 월별 데이터를 추출하는 작업을 진행한다. 이 단계는 월별 정보가 있다면 생략 가능하다.
STEP2. 월별 데이터 구분
[예제] 추출된 MONTH 데이터를 Simple Case Expression을 이용해서 12개의 월별 칼럼으로 구분한다. 실행 결과에서 보여 주는 ENAME 칼럼은 최종 리포트에서 요구되는 데이터는 아니지만, 정보의 흐름을 이해하기 위해 부가적으로 보여 주는 임시 정보이다. FROM 절에서 사용된 인라인 뷰는 2장 4절에서 설명한다.
STEP3. 부서별 데이터 집계
[예제] 최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여를 알고 싶다는 요구사항이므로 부서별 평균값을 구하기 위해 GROUP BY 절과 AVG 집계 함수를 사용한다. 직원 개인에 대한 정보는 더 이상 필요 없으므로 제외한다. ORDER BY 절을 사용하지 않았기 때문에 부서번호별로 정렬이 되지는 않았다.
하나의 데이터에 여러 번 CASE 표현을 사용하고 집계 함수가 적용되므로 SQL 처리 성능 측면에서 나쁜 것이 아니냐는 생각을 할 수도 있다. 그렇지만, 같은 기능을 하는 리포트를 작성하기 위해?을 활용하면 복잡한 프로그램이 아닌 하나의 SQL 문장으로 처리 가능하므로 DBMS 자원 활용이나 처리 속도에서 훨씬 효율?? 차이는 더 크질 수 있다. 개발자들은 가능한 하나의 SQL 문장으로 비즈니스적인 요구 사항을 처리할 수 있도록 노력해야 한다.
[예제] Simple Case Expression으로 표현된 위의 SQL과 같은 내용으로 Oracle의 DECODE 함수를 사용한 SQL 문장을 작성한다.
DECODE 함수를 사용함으로써 SQL 문장이 조금 더 짧아졌다. CASE 표현과 Oracle의 DECODE 함수는 표현상 서로 장단점이 있으므로 어떤 기능을 선택할 지는 사용자의 몫이다.
✨집계 함수와 NULL 처리
제 8절 ORDER BY 절
✨ORDER BY 정렬
✨SELECT 문장 실행 순서
✨TOP N 쿼리
제 9절 조인
✨JOIN 개요
✨EQUI JOIN
✨Non EQUI JOIN
✨3개 이상 TABLE JOIN
요약
'데이터베이스응용(SQLD)' 카테고리의 다른 글
| [ORACLE] USER 생성 (0) | 2023.10.01 |
|---|
