포트폴리오블로그

부속 질의(subquery): 다른 SQL 쿼리 내부에 포함된 SQL 쿼리를 의미함.

1.SELECT 절의 부속질의: select 절에 사용되며, 단일 값(스칼라 값)을 반환함. 각 행에 대해 한 번씩만 실행, 반환 값이 없으면 NULL 반환.

SELECT column1, (SELECT MAX(column2) FROM table2) AS max_column2
FROM table1;

2.FROM절 부속질의: FROM절에 사용되며, 테이블처럼 사용될 수 있는 결과 집합을 반환함. 주로 복잡한 조인 또는 집계 연산을 수행한 결과를 다시 사용하는 경우에 유용. 반드시 별칭( Alias)을 지정해야 함.

SELECT column1, column2
FROM (SELECT column1, column2 FROM table1 WHERE condition) AS subquery_alias;

3.WHERE절의 부속질의: WHERE절에 사용되며, 조건을 평가하는데 사용되는 값을 반환함. 비교 연산자, IN, NOT IN, EXISTS, NOT EXISTS 등과 함께 사용될 수 있음.

• 단일 행 부속질의(Single-Row Subquery): 하나의 행만 반환, =, >, <, >=, <=, <> 등의 비교 연산자와 함께 사용.

SELECT column1
FROM table1
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);

• 다중 행 부속질의(Multiple-Row Subquery): 여러 개의 행을 반환함. IN, NOT IN, EXISTS, NOT EXISTS 연산자와 함께 사용. IN / NOT IN: 부속 질의 결과에 포함되는 / 포함되지 않는 값을 가진 행을 선택.

SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
//EXISTS / NOT EXISTS: 부속 질의 결과가 존재하는 / 존재하지 않는 경우에만 외부 쿼리를 실행
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column2 = table2.column2);

정렬 쿼리 ORDER BY: 결과물에 대해 영향을 미치지는 않지만, 결과가 출력되는 순서를 조절하는 구문. 기본적으로는 오름차순으로 정렬됨, 내리차순으로 정렬하기 위해서는 DESC 키워드를 작성함.

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

ORDER BY 절은 제일 뒤에 와야 함. (성능에 영향을 미치므로 사용하지 않는 것을 권장함.)

GROUP BY: 특정 열의 값을 기준으로 행들을 그룹으로 묶는 데 사용.

• GROUP BY안에 칼럼이 여러개일 경우: (GROUP BY(column1, column2);→column1을 기준으로 그룹을 만든 후 그 안에서 column2를 기준으로 하는 그룹을 만듦.

HAVING: GROUP BY절로 그룹화된 결과에 대한 필터링 조건을 지정하는데 사용됨.

• WHERE절: 그룹화하기 전에 행에 대한 필터링 수행.
• HAVING절: 그룹화된 결과에 대한 필터링 수행.

집계 함수(Aggregate Function): 여러 행의 값들을 모아서 단일 요약 값을 계산하는데 사용되는 함수. 일반적으로 GROUP BY절과 함께 사용되어 그룹별 요약 정보를 얻는데 유용함.

JOIN: 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것. join을 사용하면 여러 테이블에 분산되어 있는 관련 데이터를 효과적으로 조회하고 분석할 수 있음.

1.INNER JOIN(내부조인)

• 두 테이블에서 조인 조건(join condition)을 만족하는 행들만 반환.
• 조인 조건에 일치하지 않는 행은 결과에 포함되지 않음.

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

2. LEFT JOIN(왼쪽 조인)

• 왼쪽 테이블(LEFT JOIN 키워드 왼쪽에 있는 테이블)의 모든 행을 반환하고, 오른쪽 테이블에서 조인 조건을 만족하는 행이 있다면 해당 행의 데이터를 함께 반환.
• 오른쪽 테이블에 조인 조건을 만족하는 행이 없으면 오른쪽 테이블의 열은 NULL 값으로 채워짐.

3.RIGHT JOIN(오른쪽 조인)

• 오른쪽 테이블(RIGHT JOIN 키워드 오른쪽에 있는 테이블)의 모든 행을 반환하고, 왼쪽 테이블에서 조인 조건을 만족하는 행이 있다면 해당 행의 데이터를 함께 반환.
• 왼쪽 테이블에 조인 조건을 만족하는 행이 없다면 왼쪽 테이블의 열은 NULL 값으로 채워짐.

4.FULL OUTER JOIN(전체 외부 조인)

• 왼쪽 테이블과 오른쪽 테이블의 모든 행 반환.
• 조인 조건을 만족하는 행이 있으면 해당 행의 데이터를 함께 반환, 그렇지 않으면 NULL 값으로 채움.

5.CROSS JOIN(교차 조인)

• 두 테이블의 모든 가능한 조합의 행을 반환.
• 조인 조건 없음.
• 결과 집합의 크기가 매우 커질 수 있으므로 주의해서 사용.

UNION:두 쿼리의 결과를 행으로 합치는 것. 대신 두 쿼리의 열의 개수가 같아야 하고, 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 함.

무결성 제약조건(Integrity Constraint): 데이터베이스에 저장된 데이터의 정확성, 일관성, 유효성을 유지하기 위해 설정하는 규칙 또는 조건. 데이터베이스 시스템에 의해 자동으로 검사, 제약 조건을 위반하는 데이터의 삽입, 수정, 삭제를 방지하여 데이터의 신뢰성을 확보하는데 중요한 역할을 함.

1. 개체 무결성 제약조건(Entity Intergrity Constrain)

• 목적: 테이블의 각 행을 고유하게 식별하기 위해 기본 키(Primary key)가 NULL 값을 가질 수 없도록 하는 제약 조건
• 규칙: 기본 키를 구성하는 열은 NULL 값을 가질 수 없음, 테이블 내에서 유일.

2.참조 무결성 제약조건(Referential Intergrity Constraint)

• 목적: 테이블 간의 관계를 설정하고 유지하기 위해 외래 키(Foreign key)가 참조하는 테이블의 기본 키 값과 일치하도록 하는 제약조건
• 규칙: 외래 키는 참조하는 테이블의 기본 키 값과 일치하거나 NULL 값을 가질 수 있음, 값이 변경되거나 삭제될 때 함께 변경되거나 삭제됨.

3. 도메인 무결성 제약조건(Domain Intergrity Constraint)

• 목적: 특정 열의 데이터 타입, 길이, 값의 범위 등을 제한하여 유효한 값만 입력되도록 하는 제약조건
• 규칙: 열의 데이터 타입에 맞는 값만 입력, 열의 길이 초과하는 값 입력 불가, CHECK 제약조건을 사용하여 특정 값의 범위 또는 조건을 만족하는 값만 입력 가능.

4.사용자 정의 무결성 제약조건(User-Defined Integrity Constraint)

• 목적: 특정 비지니스 규칙 또는 요구사항에 따라 사용자가 직접 정의하는 제약조건
• 규칙: CHECK 제약조건, 트리거(Trigger), 저장 프로시저(Stored Procedure) 등을 사용하여 구현 가능.

5.고유 무결성 제약조건(unique Constraint)

• 목적: 테이블의 특정 열의 값이 고유해야 하는 경우 사용.
• 규칙: 하나의 테이블에 여러 개의 고유성 제약조건 설정 가능, NULL 값 가질 수 있음.

장점

• 데이터 품질 향상: 데이터베이스에 저장된 데이터의 정확성, 일관성, 유효성을 유지하여 데이터 품질을 향상시킴.
• 데이터 신뢰성 확보: 무결성 제약조건을 통해 데이터의 신뢰성을 확보, 잘못된 데이터로 인한 오류 방지.
• 응용 프로그램 개발 효율성 향상: 데이터의 유효성 자동 검사→ 검사 로직 구현X, 개발 효율성 향상
• 데이터베이스 유지보수 용이성 향상

인덱스(Index): 테이블의 특정 컬럼(열) 또는 컴럼들의 조합에 대해 데이터를 빠르게 검색할 수 있도록 돕는 자료구조. → 데이터와 해당 데이터가 저장된 위치를 매핑하여 검색 속도를 크게 향상시키는 역할을 함.

장점

• 검색 속도를 빠르게 하여 대량의 데이터 처리 성능을 향상 시킴.
• WHERE, ORDER BY, GROUP BY 절과 같은 쿼리에 효율적.
• 중복 방지 및 유일성 보장 가능

단점

• 추가적인 저장 공간 필요, 인덱스 생성 및 관리에 따른 오버헤드 발생.
• 데이터 수정 작업(CREATE, UPDATE, DELETE) 시 성능 저하 가능.

종류

• 기본인덱스: 단일 칼럼 기준으로 생성
• 복합인덱스: 여러 칼럼을 조합하여 인덱스 생성
• 클러스터형 인덱스: 테이블 데이터 자체가 인덱스 순서로 저장됨.
• 논클러스터형 인덱스: 인덱스만 따로 있고, 실제 데이터는 별도로 저장됨.

클러스터형 인덱스와 논클러스터형 인덱스

//클러스터형 인덱스
CREATE TABLE Student (
  StudentID INT PRIMARY KEY,  -- 기본 키는 자동으로 클러스터형 인덱스
  Name VARCHAR(50),
  Age INT
);

//그림으로 이해
[정렬된 인덱스 = 실제 데이터]
1001 홍길동 20
1002 이영희 21
1003 김민준 19

//논클러스터형 인덱스
CREATE INDEX idx_name ON Student(Name);

//그림으로 이해
[인덱스]
이영희 → 1002
김민준 → 1003

[실제 데이터]
1001 홍길동 20
1002 이영희 21
1003 김민준 19

사용

• 자주 검색되는 컬럼
• 자주 JOIN이나 WHERE절에 사용되는 컬럼
• 자주 정렬(ORDER BY)에 사용되는 컬럼

키(Key): 테이블 내에서 특정 레코드를 고유하게 식별하거나, 다른 테이블과의 관계를 연결하는데 사용→ 테이블 간의 관계를 설정하고 데이터의 무결성을 유지하는데 사용. (한 테이블 안에 같은 레코드는 존재할 수 없음)

기본 키(Primary key): 테이블에서 각 행을 고유하게 식별하는데 사용되는 열 또는 열의 조합.

특징

• 테이블당 하나의 기본 키만 존재.
• NULL 값을 가질 수 없음.
• 테이블 내에 유일하게 존재.

CREATE TABLE Student (
  StudentID INT PRIMARY KEY, -- 기본 키
  Name VARCHAR(50),
  Age INT
);

외래 키(Foreign key): 부모 테이블의 키를 참조하는 속성 또는 키.

특징

• 참조하는 테이블의 기본 키와 데이터 타입이 일치해야 함.
• NULL 값을 가질 수 있음.(NULL 값을 허용하는 경우)
• 테이블 간의 관계를 설정하고 유지하는데 사용됨.

CREATE TABLE Enrollment (
  StudentID INT,       -- 외래 키
  CourseID INT,
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

유니크 키(Unique key): 값의 중복을 허용하지 않는 키.

특징

• 유니크 키가 설정된 칼럼에 입력되는 데이터가 각각 유일하다는 것을 보장해줌.
• 하나의 테이블에 여러 개의 유니크 키를 설정할 수 있음.
• 주로 기본 키를 설정한 후 추가적으로 고유한 식별자가 있는 경우 유니크 키로 설정.
• NULL 값을 가질 수 있음.

후보 키(Candidate Key): 테이블에서 각 행을 고유하게 식별할 수 있는 열 또는 열의 조합.

특징

• 테이블에 여러 개의 후보 키 존재.
• NULL값을 가질 수 없음.
• 후보 키 중에서 기본 키를 선택함.

슈퍼 키(Super Key): 테이블에서 각 행을 고유하게 식별할 수 있는 열 또는 열의 조합.

특징

• 테이블에 여러 개의 슈퍼 키가 존재.
• 슈퍼 키는 테이블의 모든 열을 포함할 수 있음.
• 슈퍼 키를 구성하는 열 중에서 일부 열을 제거해도 고유하게 식별 가능하면 슈퍼 키가 될 수 있음.

대체 키(Alternate Key): 후보 키 중에서 기본 키로 선택되지 않은 나머지 후보 키.

특징

• 기본 키 대체 가능.

AUTO_INCREMENT: 데이터베이스에서 사용되는 속성. 테이블의 특정 열에 자동으로 고유한 숫자 값을 생성하고 할당하는데 사용.

INSERT INTO로 레코드 추가시 AUTO_INCREMENT에 해당하는 컬럼에 NULL을 넣거나 입자 목록에서 생략하면 자동으로 값이 채워짐.

INSERT INTO market VALUES(NULL, '김민준', '사과', 50, 1300, '2023-03-07 10:10:10’);

INSERT INTO market (seller, product, amount, price, reg_date) VALUES('김민준', '사과', 50, 1300, '2023-03-07 10:10:10’);

파일 시스템: 컴퓨터에서 파일들을 저장하고 관리하는 방법을 정의하는 체계. (도서관에서 책을 정리하고 관리하는 시스템과 유사함.)

특징

• 중복성: 각 응용 프로그램이 독립적으로 파일을 다룸→ 데이터가 중복으로 저장될 수 있음.
• 종속성: 응용 프로그램별로 필요한 데이터를 별로로 관리.
• 동시에 파일을 다룸.→ 데이터의 일관성 훼손 가능성 존재.

DBMS(Database Management System): 데이터베이스를 관리하고 운영하는데 사용되는 소프트웨어 시스템. 데이터베이스를 저장, 수정, 삭제 등의 작업을 효율적으로 수행할 수 있도록 도와주는 도구. (논리적으로 연관된 데이터를 모아 구조적으로 통합해 놓은 것.)

특징

• 데이터 중복을 줄이고 데이터를 표준화하여 무결성 유지.→ 중복성 해결.
• DBMS 서버가 데이터의 일관성 유지, 복구, 동시 접근 제어 등의 기능을 수행함.→ 종속성 해결.

파일 시스템과 DBMS

관계형 데이터베이스: 데이터를 테이블 형태로 구성하고, 테이블 간의 관계를 이용하여 데이터를 관리하는 데이터베이스.

엔티티(entity): 현실 세계에서 식별 가능하고 독립적으로 존재하는 객채(object)를 의미함.

특징

• 식별 가능성: 다른 엔티티와 구별될 수 있어야 함.
• 독립성: 다른 엔티티에 의존하지 않고 독립적으로 존재할 수 있어야 함.
• 속성(attribute)보유: 엔티티는 하나 이상의 속성을 가질 수 있음.

엔티티 집합(Entity set): 특정 유형의 엔티티들을 모아 놓은 집합. 같은 속성을 공유하는 엔티티들의 모임.

특징

• 동일한 속성: 엔티티 집합에 속한 모든 엔티티는 동일한 속성을 가짐.
• 테이블로 표현: 관계형 데이터베이스에서 엔티티집합은 테이블(table)로 표현됨.

예시

• 엔티티: 붕어빵 하나하나(각각의 붕어빵)
• 엔티티 집합: 붕어빵 가게에서 파는 모든 붕어빵들의 모임(붕어빵 종류)

스키마(Schema): 데이터베이스가 어떻게 구성되어 있는지, 어떤 데이터가 저장될 수 있는지, 데이터 간의 관계는 어떻게 정의되는지를 설명하는 '청사진' 또는 '설계도' 

스키마의 구성요소

• 테이블(Table): 데이터를 저장하는 기본 단위(행과 열)
• 컬럼(Column): 테이블의 각 속성(필드 이름, 데이터 타입 포함)
• 데이터 타입: 각 컬럼에 저장될 수 있는 값의 유형(예시:INT,VARCHAR 등)
• 제약 조건(Constraints) 정의: 데이터의 무결성을 유지하기 위한 규칙들을 정의함.  (예시: PRIMARY KEY,NOT NULL등)
• 관계(Relationships): 테이블 간의 연결(외래 키 등)
• 뷰(View): 실제 데이터를 저장하지 않고, 테이블을 조합해서 보여주는 가상의 테이블
• 인덱스(Index): 검색 성능을 높이기 위한 보조 구조

스키마의 종류

• 개념 스키마(Conceptual Schema): 전체 데이터베이스의 논리적 구조→ 테이블, 관계, 제약조건 등.
• 외부 스키마(External Schema, Sub Schema): 사용자 관점의 데이터 뷰, 전체 중 일부만 보여줌→ 직원이 자기 부서만 조회 가능
• 내부 스키마(Internal Schema, Storage Schema): 물리적 저장 구조→ 데이터가 실제로 디스크에 어떻게 저장되는지

스키마 VS 테이블→스키마는 테이블을 포함하는 더 넓은 개념.

표 용어

• 테이블 이름: Student
• 열(Column): StudentID, Name, Age, Major
• 행(Row): 각 학생의 정보 한 줄씩
• 레코드(Record): 1행 전체가 하나의 레코드
• 필드(Field): 예를 들어 "이영희"는 Name 열과 두 번째 행이 만나는 필드
• 기본 키: StudentID는 각 학생을 구분해주는 고유 번호