[SQLD] 과목 2 | 제 1장 SQL 기본

1절 관계형 DB 개요

• 데이터베이스의 발전

- 1960년대 : 플로우차트 중심의 개발 방법을 사용하였으며 파일 구조를 통해 데이터를 저장하고 관리하였다.
- 1970년대 : 데이터베이스 관리 기법이 처음 태동되던 시기였으며 계층형(Hierarchical) 데이터베이스, 망형(Network) 데이터베이스 같은 제품들이 상용화 되었다.
- 1980년대 : 현재 대부분의 기업에서 사용되고 있는 관계형 데이터베이스가 상용화되었으며 Oracle, Sybase, DB2와 같은 제품이 사용되었다.
- 1990년대 : Oracle, Sybase, Informix, DB2, Teradata, SQL Server 외 많은 제품들이 보다 향상된 기능으로 정보시스템의 확실한 핵심 솔루션으로 자리잡게 되었으며, 인터넷 환경의 급속한 발전과 객체 지향 정보를 지원하기 위해 객체 관계형 데이터베이스로 발전하였다.

 

1.   DB

데이터를 일정한 형태로 저장해 놓은 것, DBMS를 이용하여 효율적인 데이터 관리와 데이터 손상 복구 가능

▶ 종류

• 계층형 DB: 트리 형태의 자료구조에 데이터 저장, 1:N 관계 표현
• 네트워크형 DB: 오너와 멤버 형태로 데이터 저장, M:N 관계 표현
• 관계형 DB: 릴레이션에 데이터 저장, 집합 연산과 관계 연산 가능

 

2.   관계형 DB(RDB; Relational Database)

1) 정규화를 통해 이상현상 및 중복 데이터 제거 

2) 동시성 관리와 병행 제어를 통해 데이터 동시 조작 가능

 

▶ 집합 연산

합집합(Union)

차집합(Difference)

교집합(Intersection)

곱집합(Cartesian Product): 각 릴레이션에 존재하는 모든 데이터를 조합

▶ 관계 연산

선택 연산(Selection): 조건에 맞는 행(튜플) 조회

투영 연산(Projection): 조건에 맞는 칼럼(속성) 조회

결합 연산(Join): 공통 속성을 사용하여 새로운 릴레이션 생성

나누기 연산(Division): 공통요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

 

3.   SQL(Structured Query Language)

RDB에서 사용하는 언어, 데이터 조회 및 신규 데이터 입력/수정/삭제 기능 제공

▶ 종류

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

4.   테이블(Table)

RDB의 기본 단위

데이터를 저장하는 객체

칼럼과 행의 2차원 구조

세로 방향을 칼럼(Column), 가로 방향을 행(Row), 칼럼과 행이 겹치는 하나의 공간을 필드(Field)

용어	설명
정규화	테이블을 분할하여 데이터의 정합성을 확보하고, 불필요한 중복을 줄이는 프로세스
기본키	테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼
외부키	다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼
이상현상	데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 현상

5. ERD(Entity Relationship Diagram)

테이블 간 서로의 상관 관계를 그림으로 도식화한 것을 E-R 다이어그램

구성요소 : 엔터티(Entity), 관계(Relationship), 속성(Attribute)

 

 

2절 DDL

 

1.   데이터 타입 (앞은 Oracle 뒤는 SQL Server)

▶ CHAR(L) : 고정 길이 문자열, 할당된 변수 값의 길이가 L 이하일 때 차이는 공백으로 채워짐

▶ VARCHAR2(L), VARCHAR(L) : 가변 길이 문자열, 할당되는 변수 값의 길이의 최대값이 L임, 문자열은 가능한 최대 길이로 설정

▶  NUMBER(L,D) : 숫자형 (L은 전체 자리 수 D는 소수점 자리 수)

- SQL Server은 NUMERIC DECIMAL FLOAT REAL 등

▶ DATE, DATETIME : 날짜형, 데이터 크기 지정이 필요하지 않음

데이터 유형	설명
CHARACTER(s)	- 고정 길이 문자열 정보, (Oracle, SQL, Server 모두 CHAR로 표현) - s는 기본 길이 1바이트, 최대 길이 Oracle 2,000바이트, SQL Server 8,000바이트 - s만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우에는 그 차이 길이만큼 공간으로 채워진다.
VARCHAR(s)	- CHARATER VARYING의 약자로 가변 길이 문자열 정보 (Oracle은 VARCHAR2로 표현, SQL Server는 VARCHAR로 표현) - s는 최소 길이 1바이트, 최대 길이 Oracle 4,000바이트, SQL Server 8,000 바이트 - s만큼의 최대 길이를 갖지만 가변 길이로 조정이 되기 때문에 할당된 변수 값의 바이트만 적용된다. (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 단위 관리

2.   CREATE TABLE

SQL>> CREATE　TABLE　테이블이름 ( 칼럼명1 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식] ) ;

▶ 테이블 및 칼럼 명명 규칙

• A-Z, a-z, 0-9, _, $, # 문자만 허용
• 대소문자 구분하지 않음, 기본적으로 테이블이나 칼럼명은 대문자로 만듦
• 테이블명은 단수형 권고, 다른 테이블명과 중복 불가
• 칼럼은 한 테이블 내에서 칼럼명 중복 불가, 각 칼럼들은 괄호 "( )"로 묶어 지정, 콤마" , "로 구분, 테이블 생성문의 끝은 항상 세미콜론 " ; "

▶ 테이블 생성시 주의사항

• DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
• 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
• 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
• 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

 

▶ 제약조건

데이터 무결성 유지가 목적, 복제 테이블에는 기존 테이블 제약조건 중 NOT NULL만 적용

• PRIMARY KEY(기본키) : 테이블 당 하나의 기본키만 정의 가능, 기본키 생성시 DBMS가 자동으로 인덱스를 생성함, NULL 불가
• FOREIGN KEY(외래키) : 다른 테이블의 기본키를 외래키로 지정, 참조 무결성 제약조건

외래키(Foreign key) 지정 시 

on delete rule(삭제 시), on update rule(변경 시) 옵션 지정

RESTRICT, CASCADE, NO ACTION, SET NULL

1. RESTRICT : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 변경/삭제가 취소됩니다.(제한)

2. CASCADE : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 함께 변경/삭제됩니다.

3. NO ACTION : MYSQL에서는 RESTRICT와 동일합니다.

4. SET NULL : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 참조하고 있는 값은 NULL로 세팅됩니다.

 

SQL>> ALTER TABLE 테이블명 ADD CONSTRAINT 칼럼명 FOREIGN KEY (칼럼명) REFERENCES 테이블명(칼럼명);

• UNIQUE KEY : 행 데이터를 식별하기 위해 생성함, NULL 가능
• DEFAULT : ‘DEFAULT 값’으로 기본값 설정
• NOT NULL : NULL 값의 입력을 금지한다. 디폴트 

※ NULL: 아직 정의되지 않은 값 또는 현재 데이터를 입력하지 못하는 값, NULL과의 1) 수치연산은 NULL 2) 비교연산은 FALSE 출력

• CHECK : 입력값의 종류 및 범위 제한

▶ 생성된 테이블 구조 확인

DESCRIBE 테이블명, sp_help ‘dbo.테이블명’ , ‘DESCRIBE 테이블명;’이 ANSI/ISO 표준

 

3.   ALTER TABLE

테이블의 칼럼 관련 변경 명령어 (칼럼 추가/삭제, 제약조건 추가/삭제)

▶ 칼럼 추가                                           

SQL>> ALTER TABLE 테이블명 ADD (칼럼명 데이터타입);

ex) ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

→ 마지막 칼럼으로 추가됨 (칼럼 위치 지정 불가)

▶ 칼럼 삭제                                           

SQL>> ALTER TABLE 테이블명 DROP COLUMN 칼럼명;

ex) ALTER TABLE PLAYER DROP COLUMN ADDRESS;

→ 삭제 후 복구 불가, 한 번에 하나의 칼럼만 삭제 가능

▶ 칼럼 설정 변경                     

Oracle SQL>> ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입 제약조건); 

SQL Server SQL>> ALTER TABLE 테이블명 ALTER (칼럼명 데이터타입 제약조건); 

ex) ALTER TABLE TEAM_TEMP MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL); 

칼럼을 변경할 때는 몇 가지 사항을 고려해서 변경해야 한다.

- 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. 이는 기존의 데이터가 훼손될 수 있기 때문이다.

- 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

- 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

- 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

▶ 칼럼명 변경                                           

SQL>> ALTER TABLE 테이블명 RENAME COLUMN 칼럼명;

ex) ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;

※ ANSI/ISO 표준에 명시된 기능 아님, Oracle 등 일부 DBMS에서만 지원하는 기능

▶ 제약조건 추가                                           

SQL>> ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

ex) PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다. 제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건

SQL>> ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

ex) PLAYER 테이블이 참조하는 TEAM 테이블을 제거

SQL>> DROP TABLE TEAM;

※ ERROR: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있다. ※ 테이블은 삭제되지 않음

▶ 제약조건 제거                                           

SQL>> ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

ex) ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

 

※ SQL Server에서는 괄호를 사용하지 않고, 여러 컬럼을 동시에 수정하는 구문은 지원하지 않는다.

[예제] Oracle => ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

         SQL Server => ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);

 

4.   RENAME TABLE                                           

▶ 테이블 이름 변경

SQL>> RENAME (변경전)테이블명 TO (변경후)테이블명; (ANSI/ISO 표준)

'ALTER TABLE 테이블명 RENAME TO 테이블명’으로도 가능함

 

5.   DROP TABLE                                           

▶ 테이블 삭제

SQL>> DROP TABLE 테이블명 [CASCADE CONSTRAINT];

▶ 테이블의 데이터와 구조 삭제, 복구 불가

▶ CASCADE CONSTRAINT 옵션으로 관련 테이블의 참조 제약조건도 삭제하여 참조 무결성을 준수할 수 있음 (CREATE TABLE에서 ON DELETE CASCADE 옵션으로도 동일 기능 실현 가능)

 

6.   TRUNCATE TABLE                                           

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

SQL>> TRUNCATE TABLE 테이블명;

▶ 테이블의 전체 데이터 삭제 (↔ DROP TABLE은 테이블 자체를 제거함)

▶ 로그를 기록하지 않기 때문에 ROLLBACK 불가

 

 

3절 DML

1.   INSERT: 데이터 입력                                

SQL>> INSERT INTO 테이블명 (칼럼명, …) VALUES (필드값, …);

SQL>> INSERT INTO 테이블명 VALUES (필드값, …);

 

2.   UPDATE: 데이터 수정                                

SQL>> UPDATE 테이블명 SET 칼럼명=필드값;

 

3.   DELETE: 데이터 삭제                                

SQL>> DELETE FROM 테이블명 WHERE 조건절;

SQL>> DELETE FROM 테이블명;

▶ DELETE로 데이터를 삭제해도 테이블 용량은 초기화되지 않음 (↔ TRUNCATE로 삭제하면 초기화됨)

▶ ≠ DROP은 객체 삭제 명령어

 

4.   SELECT

▶ 칼럼 별 데이터 선택                                

SQL>> SELECT 칼럼명 FROM 테이블명;

▶ 데이터 중복 없이 선택                                

SQL>> SELECT DISTINCT 칼럼명 FROM 테이블명;

▶ 전체 칼럼의 데이터 선택                     

SQL>> SELECT * FROM 테이블명;

※ 앨리어스(Alias)

" SELECT 칼럼명 AS “별명” : 출력되는 칼럼명 설정

" FROM 테이블명 별명 : 쿼리 내에서 사용할 테이블명 설정, 칼럼명이 중복될 경우 SELECT절에서 앨리어스 필수

 

5.   산술 연산자와 합성 연산자

▶ 산술 연산자

- NUMBER와 DATE 자료형에 대해 적용

- 일반적으로 수학에서의 4칙 연산과 동일

- 수학에서와 같이 (), *, /, +, - 의 우선순위를 가짐

▶ 합성 연산자

- 문자와 문자를 연결하는 경우 2개의 수직 바(||)에 의해 이루어진다. (Oracle)

- 문자와 문자를 연결하는 경우 + 표시에 의해 이루어진다. (SQL Server)

- 두 벤더 모두 공통적으로 CONCAT (string1, string2) 함수를 사용할 수 있다.

- 칼럼과 문자 또는 다른 칼럼과 연결시킨다.

- 문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.

‘+’(플러스), CONCAT 함수로도 2개 문자열 합성 가능, Oracle에서는 ‘||’(수직선 2개)도 가능

ex) Oracle >> SELECT PLAYER_NAME || '선수,' || HEIGHT || 'cm,' || WEIGHT || 'kg' 체격정보 FROM PLAYER;

→ 체격정보 정경량선수,173cm,65kg 정은익선수

 

6.   DUAL

Oracle의 기본 더미 테이블, 연산 수행을 위해 사용됨

 

 

4절 TCL

 

1.   트랜잭션

DB의 논리적 연산 단위(분할할 수 없는 최소 단위), 하나 이상의 SQL문을 포함함

▶ 특성 ACID

• 원자성(Atomicity): 전부 실행되거나 전혀 실행되지 않음 (All or Nothing)
• 일관성(Consistency): 트랜잭션으로 인한 DB 상태의 모순이 없음
• 고립성(Isolation): 부분적인 실행 결과에 다른 트랜잭션이 접근할 수 없음, LOCKING으로 고립성 보장
• 영속성(Durability): 트랜잭션의 결과는 영구적으로 저장됨

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

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

 

2.   TCL

데이터 무결성 보장을 목적으로 함, 1) 영구 변경 전 확인과 2) 연관 작업 동시처리 가능

▶ Oracle은 1) SQL 문장을 실행하면 트랜잭션이 시작되고 2) TCL을 실행하면 트랜잭션이 종료됨

▶ DDL을 실행하면 자동 커밋 (DML 이후 커밋 없이 DDL을 실행해도 자동 커밋)

▶ DB를 정상적으로 종료하면 자동 커밋, 애플리케이션 등의 이상으로 DB 접속이 단절되면 자동 롤백

 

3.   COMMIT

데이터를 DB에 영구적으로 반영하는 명령어, 커밋 시 트랜잭션이 완료되어 LOCKING이 해제됨

▶ 자동 COMMIT이 되는경우

- Oracle에서 DML 문장 이후에 커밋없이 DDL 문장이 실행되면 그 전꺼는 자동 COMMIT 됨. 반면 SQL server에서는 DDL 문장 수행 후 AUTO COMMIT 하지 않음

- 데이터베이스를 정상적으로 접속 종료하면 자동 COMMIT 됨

- 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 자동 ROLLBACK 됨.

 

- Oracle은 DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료

- SQL Server은 기본적으로 자동 커밋, DML 구문이 성공이면 자동으로 COMMIT이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리

▶ COMMIT 전

• 데이터 변경이 메모리 버퍼에만 영향을 받았기 때문에 복구 가능 (NOLOGGING 옵션 사용 시 버퍼 캐시의 기록을 생략하여 입력 성능이 향상됨)
• 사용자는 SELECT절로 결과를 확인할 수 있으나 다른 사용자는 현재 결과를 볼 수 없음
• 변경된 행에 LOCKING이 설정되어 다른 사용자가 변경할 수 없음 (LOCKING이 안 걸린 상태일 때 여러 사용자가 데이터를 변경하면 상관없음)

▶ COMMIT 후

• 변경 사항이 DB에 반영되고 이전 데이터는 복구 불가
• 모든 사용자가 결과를 볼 수 있음
• LOCKING이 해제되어 다른 사용자가 행을 조작할 수 있음

 

4.   ROLLBACK

트랜잭션 시작 이전의 상태로 되돌리는 명령어, COMMIT 이전 상태로 돌려줌, ROLLBACK 시 LOCKING이 해제됨

▶ SAVEPOINT

• 트랜잭션 일부만 롤백 할 수 있도록 중간상태를 저장하는 명령어
• ‘ROLLBACK TO 저장점명’ 시 해당 저장점 상태로 돌려줌, 동일한 저장점명이 있으면 나중 저장점이 유효함

SQL >> SAVEPOINT SVPT1;
저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 지정한다.

SQL >> ROLLBACK TO SVPT1;
위와 같이 롤백(ROLLBACK)에 SAVEPOINT 명을 부여하여 실행하면 저장점 설정 이후에 있었던 데이터 변경에 대해서만 원래 데이터 상태로 되돌아가게 된다.

▶ SQL Server에서는 ’BEGIN TRAN’으로 명시해야 가능함

▶ ROLLBACK 후의 데이터 상태는 다음과 같다.

• 데이터에 대한 변경 사항은 취소된다.
• 이전 데이터는 다시 재저장된다.
• 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

▶ COMMIT과 ROLLBACK을 사용함으로써 다음과 같은 효과를 볼 수 있다.

• 데이터 무결성 보장
• 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
• 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

5절 WHERE절

1.   WHERE                                                      

SQL>> SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명] FROM 테이블명 WHERE 조건절;

WHERE 절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성

• 칼럼(Column)명 (보통 조건식의 좌측에 위치)
• 비교 연산자
• 문자, 숫자, 표현식 (보통 조건식의 우측에 위치)
• 비교 칼럼명 (JOIN 사용시)

2.   연산자

▶ 종류

비교 연산자: =, >, >=, <, <=

비교 대상 데이터 타입에 따라 자동으로 형 변환되는 경우도 있음

부정 비교 연산자: ‘NOT 칼럼명 비교연산자’와 동일

부등호: !=, ^=, <> (ISO 표준)

SQL 연산자 입력값을 비교하여 논리값 출력

- BETWEEN A AND B : A와 B 사잇값

- IN (리스트) : 리스트 내의 값

- LIKE ‘문자열’ : 문자열의 형태와 일치하는 값

※ 와일드카드

1) ‘%’(퍼센트)는 0개 이상의 문자 

2) ‘_’(언더바)는 1개의 단일 문자

- IS NULL : NULL은 등호로 판단 불가 어떤 상황에서도

- NOT BETWEEN A AND B, NOT IN (리스트), IS NOT NULL

논리 연산자: AND, OR, NOT

구분	연산자	연산자의 의미
비교 연산자	= > >= < <=	같다. 보다 크다. 보다 크거나 같다. 보다 작다.
SQL 연산자	BETWEEN a AND b IN (list) LIKE '비교문자열' IS NULL	a와 b의 값 사이에 있으면 된다. (a와 b 값이 포함됨) 리스트에 있는 값 중에서 어느 하나라도 일치하면 된다. 비교문자열과 형태가 일치하면 된다. (%, _ 사용) NULL 값인 경우
논리 연산자	AND OR NOT	앞의 조건과 뒤의 조건을 동시에 만족해야 함 앞뒤의 조건 중 하나만 참(TRUE)이면 됨 뒤에 오는 조건에 반대되는 결과를 되돌려 줌
부정 비교 연산자	!= ^= <> NOT 칼럼명 =  NOT 칼럼명 >	같지 않다. 같지 않다. 같지 않다. (ISO 표준, 모든 운영체제에서 사용 가능) ~와 같지 않다. ~보다 크지 않다.
부정 SQL 연산자	NOT BETWEEN a AND b NOT IN (list) IS NOT NULL	a와 b의 값 사이에 있지 않다. (a, b 값을 포함하지 않는다.) list 값과 일치하지 않는다. NULL 값을 갖지 않는다.

▶ 우선순위: 부정 연산자 > 비교 연산자 > 논리 연산자

①   ‘()’(괄호)

②   NOT

③   비교 연산자 및 SQL 연산자

④   AND

⑤   OR

▶ 문자열 비교방법

￘ CHAR vs CHAR : 첫 서로 다른 문자열 값으로 비교 (뒤 순서가 더 큰 값), 길이가 다를 때 공백을 추가하여 길이 맞춤 (공백 수만 다르면 같은 값)

￘ CHAR vs VARCHAR : 첫 서로 다른 문자열 값으로 비교, 길이가 다르면 길이가 긴 값이 크다고 판단, VARCHAR의 공백도 문자로 판단, TRIM 함수로 VARCHAR의 공백 제거하고 판단할 수 있음

￘ CHAR vs 상수 : 상수를 변수 타입으로 바꿔 비교

구분	비교 방법
비교 연산자의 양쪽이 모두 CHAR 유형 타입인 경우	1. 길이가 서로 다른 CHAR형 타입이면 작은 쪽에 SPACE를 추가하여 길이를 같게 한 후에 비교한다. 2. 서로 다른 문자가 나올 때까지 비교한다. 3. 달라진 첫 번째 문자의 값에 따라 크기를 결정한다. 4. BLANK의 수만 다르다면 서로 같은 값으로 결정한다.
비교 연산자의 어느 한 쪽이 VARCHAR 유형 타입인 경우	1. 서로 다른 문자가 나올 때까지 같다고 판단한다. 2. 길이가 서로 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다. 3. 길이가 같고 다른 것이 없다면 같다고 판단한다. 4. VARCHAR는 NOT NULL까지 길이를 말한다.
상수값과 비교할 경우	1. 상수 쪽을 변수 타입과 동일하게 바꾸고 비교한다. 2. 변수 쪽이 CHAR 유형 타입이면 위의 CHAR 유형 타입의 경우를 적용한다. 3. 변수 쪽이 VARCHAR 유형 타입이면 위의 VARCHAR 유형 타입의 경우를 적용한다.

 

3.   부분 범위 처리

▶ ROWNUM (Oracle)

SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호, 조건절 내에서 행의 개수를 제한하는 목적으로 사용함

ex) 1건의 행만 가져오고 싶을 때

SQL >> SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM = 1;

ex) 여러 건의 행을 가져오고 싶을 때

SQL >> (X) SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM = N; 처럼 사용할 수 없음

SQL >> (O) SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N;

▶ TOP (SQL Server)

출력 행의 수 제한 함수, ’TOP (N)’로 N개 행 출력, 개수 대신 비율로도 제한 가능

SQL >> SELECT TOP(1) PLAYER_NAME FROM PLAYER;

TOP (Expression) [PERCENT] [WITH TIES]

- Expression : 반환할 행의 수를 지정하는 숫자이다.

- PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타낸다.

- WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.

※ ORDER BY절이 없으면 ROWNUM과 TOP의 기능이 같음

 

 

6절 함수

1.   단일 행 함수

- SELECT, WHERE, ORDER BY 절에 사용 가능하다.

- 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.

- 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.

- 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.

- 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능

▶ 문자형 함수: 문자열 입력 시 문자열이나 숫자 반환

 LOWER, UPPER, LENGTH

 CONCAT : 문자열 결합

 SUBSTR : 문자열 부분 추출

 LTRIM, RTRIM, TRIM : 왼쪽 공백 제거, 오른쪽 공백 제거, 양쪽 공백 제거

 ASCII : 아스키 코드값 출력

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 바꾸어 준다.
UPPER(문자열)	문자열의 알파벳 문자를 대문자로 바꾸어 준다.
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꾸어 준다.
CHR/CHAR (ASCII 번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT (문자열1, 문자열2)	Oracle, MySQL에서 유효한 함수이며 문자열1과 문자열2를 연결한다. 합성 연산자 ' || '(Oracle)나 ' + '(SQL Server)와 동일하다.
SUBSTR/SUBSTRING (문자열, m[, n ])	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생략되면 마지막 문자까지이다.
LENTH/LEN(문자열)	문자열의 개수를 숫자값으로 돌려준다.
LTRIM (문자열 [, 지정문자 ])	문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트) SQL Server에서는 LTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
TRIM ([leading|trailing|both] 지정문자 FROM 문자열)	문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거한다. (leading | trailing | both 가 생략되면 both가 디폴트) SQL Server에서는 TRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.

문자형 함수 사용	결과 값 및 설명
LOWER('SQL Expert')	'sql expert'
UPPER('SQL Expert')	'SQL EXPERT'
ASCII('A')	65
CHR(65) / CHAR(65)	'A'
CONCAT('RDBMS', 'SQL') 'RDBMS' || 'SQL' / 'RDBMS' + 'SQL'	'RDBMS SQL'
SUBSTR('SQL Expert', 5, 3) SUBSTRING('SQL Expert', 5, 3)	'Exp'
LENGTH('SQL Expert') /  LEN('SQL Expert')	10
LTRIM('xxxYYZZxYZ', 'x') RTRIM('XXYYzzYYzz', 'z') TRIM('x' FROM 'xxYYZZxYZxx')	'YYZZxYZ' 'XXYYzzXY' 'YYZZxYZ'
RTRIM('XXYYZZXYZ         ') -> 공백 제거 및 CHAR와 VARCHAR 데이터 유형을 비교할 때 용이하게 사용된다.	'XXYYZZXYZ'

▶ 숫자형 함수

 ABS, SIGN : 절대값, 부호 (1, 0, -1 중 출력)

 MOD : 나머지, 연산자 ’%’로 대체 가능함

 ROUND, CEIL, FLOOR : 반올림, 올림, 버림 (‘함수(E,N)’으로 소수점 이후 N번째 자리까지 출력)

 TRUNC : 숫자형 부분 추출

숫자형 함수	함수 설명
ABS(숫자)	숫자의 절대값을 돌려준다. ABS(-15) => 15
SIGN(숫자)	숫자가 양수인지, 음수인지 0인지를 구별한다. SIGN(-20) => -1 SIGN(0) => 0 SIGN(+20) => 1
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지 값을 리턴한다. MOD 함수는 % 연산자로도 대체 가능함 (ex: 7%3) MOD(7,3) => 1 7%3 => 1
CEIL/CEILING(숫자)	숫자보다 크거나 같은 최소 정수를 리턴한다. CEILING(38.123) => 39 CEILING(-38.123) => -38
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수를 리턴한다. FLOOR(38.123) => 38 FLOOR(-38.123) => 39
ROUND(숫자 [, m])	숫자를 소수점 m자리에서 반올림하여 리턴한다. m이 생략되는 디폴트 값은 0이다. ROUND(38.5235, 3) => 38.524 ROUND(38.5235, 1) => 38.5 ROUND(38.5235, 0) => 39 ROUND(38.5235) => 39 (인수 0이 Default)
TRUNC(숫자 [, m]_	숫자를 소수 m자리에서 잘라서 버린다. m이 생략되면 디폴트 값은 0이다. SQL SERVER에서 TRUNC 함수는 제공되지 않는다. TRUNC(38.5235, 3) => 38.523
SIN, COS, TAN, ...	숫자의 삼각함수 값을 리턴한다.
EXP(), POWER(), SQRT(), LOG(), LN()	숫자의 지수, 거듭 제곱, 제곱근, 자연 로그 값을 리턴한다.

▶ 날짜형 함수

 SYSDATE : 현재 시각 출력 (년, 월, 일, 시, 분, 초)

 EXTRACT : 날짜형 부분 추출                      SQL>> SELECT EXTRACT(부분 FROM SYSDATE) FROM DUAL;

 ±숫자, ±숫자/24 : 일자 연산, 시간 연산

 NEXT_DAY : 지정된 요일 첫 날짜 출력

날짜형 함수	함수 설명
SYSDATE / GETDATE()	현재 날짜와 시각을 출력한다.
EXTRACT('YEAR' | 'MONTH' | 'DAY' from d) / DATEPART('YEAR' | 'MONTH' | 'DAY', d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. 시간/분/초도 가능함
TO NUMBER(TO_CHAR(d,'YYYY')) / YEAR(d), TO_NUMBER(TO_CHAR(d,'MM')) / MONTH(d), TO_NUMBER(TO_CHAR(d,'DD)) / DAY(d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. Oracle EXTRACT YEAR/MONTH/DAY 옵션이나 SQL Server DEPART YEAR/MONTH/DAY 옵션과 같은 기능이다. TO_NUMBER 함수 제외시 문자형으로 출력됨

연산	결과	설명
날짜 + 숫자	날짜	숫자만큼의 날수를 날짜에 더한다.
날짜 - 숫자	날짜	숫자만큼의 날수를 날짜에서 뺀다.
날짜1 - 날짜2	날짜수	다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다.
날짜 + 숫자/24	날짜	시간을 날짜에 더한다.

▶ 변환형 함수: 데이터 타입 변환, 명시적 형 변환 방식

 TO_NUMBER, TO_CHAR, TO_DATE (Oracle): 문자열을 숫자로, 숫자나 날짜를 문자열로, 문자열을 날짜로

 CAST, CONVERT (SQL Server)

종류	설명
명시적(Explict) 데이터 유형 변환	데이터 반환형 함수로 데이터 유형을 반환하도록 명시해 주는 경우
암시적(Implicit) 데이터 유형 변환	데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우

반환형 함수 - Oracle	함수 설명
TO_NUMBER(문자열)	alphanumeric 문자열을 숫자로 변환한다.
TO_CHAR(숫자 | 날짜 [, FORMAT])	숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환한다.
TO_DATE(문자열 [, FORMAT])	문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다.

반환형 함수 - SQL Server	함수 설명
CAST (expression AS data_type [(length)])	expression을 목표 데이터 유형으로 변환한다.
CONVERT (data type [(length)], expression [, style])	expression을 목표 데이터 유형으로 변환한다.

▶ NULL 관련 함수

• 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
• 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
• 널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다.
• 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 ‘x’ 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.
• Oracle의 경우 ''로 입력이 들어오면  NULL로 인식하여 조회할 때 IS NULL 구문으로, SQL Server는 ''로 인식하여 조회할 때 칼럼명 = '' 으로 조회한다.

일반형 함수	함수 설명
NVL(표현식1, 표현식2) /  ISMULL(표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다. NULL 관련 가장 많이 사용하는 함수이므로 상당히 중요하다.
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL, 같지 않으면 표현식1을 리턴한다.
COALESCE(표현식1, 표현식2, .... )	임이의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.

 

2.   다중행 함수

- 여러 행의 값이 입력

- 단일행 함수와 동일하게 단일 값만을 반환

- 여러 레코드의 값들을 입력 인수로 사용

- 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 나뉨

 

3.   데이터 변환

▶ 명시적 형 변환: 변환형 함수를 이용하여 데이터 타입 변환 (추천)

▶ 암시적 형 변환: 성능 저하가 발생할 수 있으며, DBMS가 자동으로 데이터 타입 변환하여 에러 발생 가능성 높음

 

4.   조건문: IF-THEN-ELSE 형태

▶ CASE WHEN 조건절1 THEN 출력값1 … ELSE 기본값 END : ELSE 생략 시 NULL 출력

※ ‘CASE WHEN NULL THEN 출력값 ELSE 기본값’은 조건이 없으므로 모든 행에서 기본값 출력 (일반적으로 ‘WHEN 칼럼 IS NULL’로 수정 필요)

▶ DECODE (칼럼, 기준값1, 출력값1, …, 기본값) : Oracle 함수, 기준값n이면 출력값n 출력

▶ SIMPLE_CASE_EXPRESSION은 CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고, 다음 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 아닌지 판단하는 문장

>> SELECT LOC, CASE LOC WHEN 'NEW YORK' THEN 'EAST' WHEN 'BOSTON' THEN 'EAST' ELSE 'ETC' END as AREA FROM DEPT;

▶ SEARCHED_CASE_EXPRESSION은 CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN 절에서 EQUI(=) 조건 포함 여러 조건(>, >=, <, <=)을 이용한 조건절을 사용할 수 있기 때문에 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 적용할 수 있는 장점

>>  SELECT ENAME, CASE WHEN SAL >= 3000 THEN 'HIGH' WHEN SAL >= 1000 THEN 'MID' ELSE 'LOW' END AS SALARY_GRADE FROM EMP;

 

 

7절 GROUP BY, HAVING절

1.   집계 함수(Aggregate Function)

그룹별 결과 출력, 다중 행 함수 중 하나, GROUP BY절이 없으면 그룹핑 대상이 존재하지 않아 에러 발생, WHERE절에 사용 불가, 공집합에서도 연산 수행

▶ ALL, DISTINCT : 전체 출력, 중복 제외 출력

▶ SUM, AVG, MAX, MIN, VARIAN, STDDEV : NULL 제외하고 연산 (↔ 숫자 연산은 NULL 출력)

▶ COUNT : 행 수 출력

COUNT(*) : NULL 포함한 행의 수 출력

COUNT(표현식) : NULL 제외한 행의 수 출력

집계 함수	사용 목적
COUNT(*)	NULL 값을 포함한 행의 수를 출력한다.
COUNT(표현식)	표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력한다.
SUM([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 합계를 출력한다.
AVG([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 평균을 출력한다.
MAX([DISTINCT | ALL] 표현식)	표현식의 최대값을 출력한다. (문자, 날짜 데이터 타입도 사용가능)
MIN([DISTINCT | ALL] 표현식)	표현식의 최소값을 출력한다. (문자, 날짜 데이터 타입도 사용가능)
STDDEV([DISTINCT | ALL] 표현식)	표현식의 표준 편차를 출력한다.
VARIAN([DISTINCT | ALL] 표현식)	표현식의 분산을 출력한다.

 

2.   GROUP BY

그룹핑 기준 설정, 앨리어스 사용 불가

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

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다) 
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다. 
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

 

3.   HAVING

GROUP BY절에 의한 집계 데이터에 출력 조건을 걺 (↔ WHERE절은 SELECT절에 조건을 걸기 때문에 제외된 데이터가 GROUP BY 대상이 아님), 일반적으로 GROUP BY 뒤에 위치함

 

 

8절 ORDER BY절

1.   ORDER BY : 특정 칼럼을 기준으로 정렬, 기본 정렬기준은 오름차순

▶ 1) 칼럼명 2) 앨리어스 3) 칼럼의 SELECT절에서 순서로 칼럼 지정 가능, SELECT절에 없는 칼럼도 지정 가능, GROUP BY절이 있으면 GROUP BY 대상 칼럼만 지정 가능

▶ Oracle은 NULL을 최대값으로 판단함 회장님은 상사가 없음 (↔ SQL Server은 최소값으로 판단함)

SQL >> SELECT 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건식] [ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]]

 

2.   SELECT문 실행 순서

▶ 테이블에서 출력 대상이 아닌 것은 제거하고 그룹핑해서 그룹핑된 값이 조건에 맞는 데이터를 계산 및 출력하고 정렬함

SELECT 칼럼명 AS “별명”                                           ⑤ 계산 및 출력하고

FROM 테이블명                                                       ① 테이블에서

WHERE 조건식                                                        ② 출력 대상이 아닌 것은 제거하고

GROUP BY 칼럼/표현식                                             ③ 그룹핑해서

HAVING 조건식                                                       ④ 그룹핑된 값이 조건에 맞는 데이터를

ORDER BY 칼럼/표현식                                              ⑥ 정렬함

 

3. Top N 쿼리

• ROWNUM

Oracle에서 순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM 조건을 같이 사용하는 경우가 있는데 이 두 조건으로는 원하는 결과를 얻을 수 없다. Oracle의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라, 데이터의 일부가 먼저 추출된 후(ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음) 데이터에 대한 정렬 작업이 일어나므로 주의해야 한다.

[예제] SELECT ENAME, SAL FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM < 4 ;

[실행 결과] ENAME SAL ------ ---- KING 5000 SCOTT 3000 FORD 3000 3개의 행이 선택되었다.

• TOP()

반면 SQL Server는 TOP 조건을 사용하게 되면 별도 처리 없이 관련 Order By 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력할 수 있다.

[예제] 사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 같이 출력한다.

[예제] SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

[실행 결과] ENAME SAL ----- --- KING 5000 SCOTT 3000 FORD 3000 3개의 행이 선택되었다.

TOP(2) WITH TIES 옵션은 동일 수치의 데이터를 추가로 더 추출하는 것으로, SCOTT과 FORD의 급여가 공동 2위이므로 TOP(2) WITH TIES의 실행 결과는 3건의 데이터가 출력된다.

 

 

9절 조인

1.   조인

여러 테이블을 연결 또는 결합하여 데이터를 출력하는 것, 일반적으로 PK나 FK의 연관성에 의해 성립

 

2.   등가 조인

두 테이블의 칼럼 값이 정확히 일치하는 경우, 대부분 PK와 FK 관계를 기반으로 함

SQL>> SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼명=B.칼럼명;

▶ SELECT 대상 칼럼이 두 테이블 모두에 있는 경우 앨리어스를 지정해야 함 (양쪽 앨리어스 모두 무관)

 

3.   비등가 조인

두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우, 부등호나 BETWEEN 연산자를 통해 조인