[DataBase] 03. SQL(Structured Query Language)(1)

 

SQL(Structured Query Language)(1)

#Database/Concepts/Temp

(10/10) 굉장히 공들여서 작성한 글이여서 원래 동아리 스터디 용도로 일부 공개 했었는데, 더 많은 분들이 봤으면 하는 마음에 오픈!

게시글 길이 제한으로 글을 분리해서 업로드하는 점 양해 바랍니다

첫 번째 챕터에서 데이터를 정의하는 언어 DDL, 데이터를 조작하는 언어 DML에 대해서 배웠고 데이터베이스에 접근하고 조작하는데 사용하는 표준적인 언어로 SQL에 대해서 간단히 소개하였다. 이번 챕터에서는 SQL에 대해 자세히 다룬다. SQL의 중요성에 대해선 첫 번째 챕터에서 다뤘으므로 넘어간다.

SQL Parts - 개요

SQL은 DDL, DML, DCL, TCL 모두를 포함하고 있다. 이 4가지에 대해서 배울 예정이다.

• DDL (데이터 정의 언어, Data Definition Language)
• DML (데이터 조작 언어, Data Manipulation Language)
• DCL (데이터 제어 언어, Data Control Language)
• TCL (트랜잭션 제어 언어, Transaction Control Language)

• DDL
  • Data definition language (DDL)은 데이터 구조, 특히 데이터베이스 스키마를 정의하기 위한 컴퓨터 프로그래밍 언어와 유사한 구문입니다.
  • DDL 문은 데이터베이스 객체(테이블, 인덱스, 사용자 등)를 생성, 수정, 제거합니다.
  • 일반적인 DDL 문은 CREATE, ALTER, DROP입니다.
• DML
  • DML은 데이터베이스에서 정보를 쿼리하고 튜플을 삽입, 삭제, 수정할 수 있는 기능을 제공합니다.

아래 내용들은 감이 잘 안올 수 있는데, 나중에 따로 자세히 다루므로 SQL은 무결성, 뷰 정의, …. 를 위해 명령어를 포함하고 있구나 ~ 정도만 알고 넘어가자

• 무결성 (Integrity)
  • DDL은 integrity constraints(무결성 제약 조건)을 지정하기 위한 명령을 포함합니다.
• 뷰 정의 (View Definition)
  • DDL은 View를 정의하기 위한 명령을 포함합니다.
• 트랜잭션 제어 (Transaction Control)
  • 트랜잭션의 시작과 끝을 지정하기 위한 명령을 포함합니다.
• 내장 SQL 및 동적 SQL (Embedded SQL and Dynamic SQL)
  • SQL 문이 일반 목적 프로그래밍 언어(C/C++) 내에 어떻게 내장될 수 있는지를 정의합니다.
• 권한 부여 (Authorization)
  • Relation 및 view에 대한 접근 권한을 지정하기 위한 명령을 포함합니다.

이제 본격적으로 세부적인 내용에 대해 다뤄보자

DDL(데이터 정의 언어, Data Definition Language)

SQL 데이터 정의 언어 (DDL)는 relation에 대한 정보를 명시(지정)하는 기능을 제공한다.

• 포함되는 내용:
  • 각 relation의 스키마
  • 각 attribute와 관련된 값의 유형
  • integrity constraints(무결성 제약 조건)
  • 각 relation에 대해 유지해야 하는 인덱스 집합
  • 각 relation에 대한 보안 및 authorization(권한 정보)
  • 디스크에 있는 각 관계의 물리적 저장 구조

이 중에서 제약 조건에 대해서 먼저 살펴보자.

무결성 제약 조건 (Integrity Constraints)

어떤 개발자가 계좌 송금과 관련된 프로그램을 개발하고 있다. 이 때 실수로 잔액이 송금 금액보다 작은 경우에 따로 처리를 하지 않았다고 해보자.
만약 SQL에 단순히 계좌 잔액을 정수형으로 정의해뒀다면, 계좌 잔액이 음수가 되는 케이스가 발생하게 될 것이다. 이런 경우를 방지하기 위해서는 기본적으로 계좌 잔액에 대한 Attribute Domain에 대한 제약조건을 설정하여 계좌 잔액이 0 이상의 값만 허용하도록 해야할 것이다.

이 외에도 데이터에 대한 접근 뿐만 아니라 다양한 케이스에 대한 제약 조건을 꼼꼼히 설정해야 데이터의 무결성을 유지할 수 있을 것이다. 데이터의 정확성과 일관성을 위해서 설정하는 규칙, 제약 조건을 무결성 제약 조건(Integrity Constraints)이라고 한다.

• 무결성 제약 조건은 일련의 규칙입니다.
  • 데이터의 품질을 유지하기 위해 사용됩니다.
  • 무결성 제약 조건은 데이터 삽입, 업데이트, 기타 프로세스가 데이터 무결성에 영향을 미치지 않도록 수행되도록 보장합니다.
  • 따라서 무결성 제약 조건은 데이터베이스에 대한 우발적인 손상을 방지하기 위해 사용됩니다.

무결성 제약조건은 도메인 제약 조건, 엔티티 무결성 제약 조건, 참조 무결성 제약 조건, 키 제약 조건 등으로 분류할 수 있다.

각각에 대해 자세히 알아보자

 

도메인 제약 조건 (Domain Constraints)

특정 Attribute(속성)의 값이 특정 데이터 유형이나 범위에 맞는지 확인하는 것이다.
계좌 잔액이 음수가 될 수 없음을 정의하는 것을 예로 들 수 있다.

• 도메인 제약 조건은 attribute에 대한 유효한 값 집합의 정의로 정의될 수 있습니다.
• 도메인의 데이터 타입에는 string, character, integer, time, date, currency 등이 포함됩니다.
  • attribute의 값은 해당 도메인 타입이어야 한다.

• Age 의 'A'는 허용되지 않음--> 나이(Age)는 integer attribute 입니다.

도메인에 대해서는 아래에서 자세히 다룬다.

---

아래 엔티티 무결성 제약 조건과 키 제약 조건에서 엔티티라는 용어가 등장하는데 다음을 참고하자.

엔티티(Entity)란?

엔티티는 데이터베이스 내에서 독립적으로 존재할 수 있는 객체 또는 개체를 의미. 현실 세계에서 물리적이거나 논리적인 개체로서 식별 가능한 개체이다. 현실을 예로 들면 예를 들어, 사람, 사물, 장소, 이벤트 등이 될 수 있다. 엔티티는 각각 고유하게 식별 가능한 속성을 가지고 있다.

• 예시) 학생 (Student): 학교에서 학생 하나하나는 각각의 엔티티이다. 각 학생은 이름, 나이, 학번 같은 속성을 가지고 있다. 예를 들어, “홍길동”이라는 학생은 StudentID: 001, Name: 홍길동, Age: 20 같은 정보를 갖고 있는 개별 엔티티이다.

엔티티 집합이란?

엔티티 집합은 동일한 종류의 엔티티들이 모여 있는 그룹이다. 데이터베이스에서 테이블과 비슷하다.
(테이블과 비슷하지만, 테이블과 엔티티는 서로 다른 단계에서 사용되는 개념이다)

• 예시) 학생 집합 (Student Set): 학교에서 모든 학생들을 모아놓은 집합이 학생 집합이다. 학교의 학생 데이터베이스에서 “홍길동”, “김영희”, “이민호” 같은 모든 학생의 정보를 포함하는 테이블이 학생 집합을 나타낸다. 이 테이블은 학생들의 이름, 학번, 나이 등의 정보를 포함한다.

요약하자면
엔티티는 현실 세계의 개별 객체입니다. (예: 특정 학생, 특정 자동차)
엔티티 집합은 같은 종류의 모든 엔티티를 모아놓은 그룹입니다. (예: 모든 학생들, 모든 자동차들)

참고) 테이블은 실제 데이터베이스 시스템에서 데이터를 저장하고 관리하는 구체적인 구조를 지칭하는 개념이고, 엔티티 집합은 데이터베이스의 논리적 또는 개념적 모델에서 정의되며 현실 세계의 엔티티를 어떻게 모델링할지에 대한 설계 단계에서 사용되는 개념이다. 구체적 vs 추상적

---

엔티티 무결성 제약 조건 (Entity Integrity Constraints)

• 엔티티 무결성 제약 조건은 PK값이 null이 될 수 없음을 명시합니다.
• Primary key값은 relation의 개별 행(=entity)을 식별하는 데 사용되며, primary key가 null 값을 가지면 해당 행을 식별할 수 없습니다.
• 테이블은 primary key 필드 외의 null 값을 포함할 수 있습니다.

⠀

• ID에 null 값은 허용되지 않음: Primary key에 해당하는 Attribute는 NULL 값을 가질 수 없습니다.

참고) PK는 Primary Key의 줄임말입니다.

참조 무결성 제약 조건 (Referential Integrity Constraints)

• 참조 무결성 제약 조건은 두 테이블 간에 지정되는 제약조건이다.
• 참조 무결성 제약 조건에서는 테이블 1의 foreign key(외래 키)가 테이블 2의 primary key(기본 키)를 참조하면 테이블 1(참조한 테이블)의 foreign key의 모든 값은 null이거나 테이블 2(참조된 테이블)에 있어야 합니다.

• 위 예시에서 202라는 값이 참조된 테이블(Table 2)의 PK에 정의되어 있지 않다.
  • PK에 정의되어 있다고 표현하는 이유는 Primary Key 에 해당하는 Attribute는 각 레코드마다 고유한 값을 가지기 때문이다. 이에 대한 내용은 아래 키 제약 조건을 참고하자

키 제약 조건 (Key Constraints)

테이블 내의 각 레코드를 고유하게 식별할 수 있도록 하는 제약 조건이다.

• Key는 엔티티 집합 내에서 엔티티를 고유하게 식별하는 데 사용되는 엔티티 집합입니다.
  • 학생들 무리에서 각 학생들을 고유하게 식별하는데 사용되는 것.
• 엔티티 집합은 여러 키를 가질 수 있지만 그 중 하나는 Primary Key가 됩니다.
  • 각 학생을 식별할 수 있는 방법은 여러가지가 있지만, 그 중 학번을 PK로 사용한다.
• Primary key는 고유한 값을 포함할 수 있으며 관계형 테이블에서 NULL 값을 가질 수 없습니다.
  • A라는 학생과 B라는 학생의 학번이 같을 순 없으며, 입학 시 학번을 무조건 부여받는다.

• 예시) entitiy set 중 Primary key인 Customer ID의 값은 unique 해야 하는데, 1001이라는 값이 중복되었다.

Primary Key(기본 키)

• Primary key는 각 레코드에 대해 고유한 관계형 데이터베이스의 키입니다.
• 관계형 데이터베이스는 항상 하나의 Primary key를 가져야 합니다.
• Primary key는 일반적으로 관계형 데이터베이스 테이블의 열로 나타납니다.

Foreign Key (외래 키)

• Foreign key는 두 테이블 간의 데이터를 연결하는 관계형 데이터베이스 테이블의 attribute 집합입니다.
  • 각 학생의 수강신청 목록 테이블을 정의할 때 학생 정보 테이블의 PK은 학번을 참조하여 수강신청 목록 테이블의 FK로 사용할 수 있다.
• Foreign key는 다른 테이블의 Primary key를 참조하므로 테이블 간의 참조 역할을 합니다.
• Foreign key는 다른 테이블에서 가져다 쓴다.
• Foreign key는 다른 테이블의 Primary key와 일치하는 값인 테이블의 속성입니다.

Foreign Key (외래 키) 예시 (1)

• 고객(Customer)과 연락처(Contact) 테이블

• Foreign Key를 사용했을 때 장점 : 데이터 저장에 사용되는 공간을 줄일 수 있고, Table Lock에 유리하다
  • Table Lock이 뭔지 모른다면 그냥 넘어가세용

Foreign Key (외래 키) 예시 (2)

SQL의 도메인 유형

도메인 제약조건을 지정할 때 사용해야 할 도메인 유형이다.

• char(n): 사용자 지정 길이 n의 고정 길이 문자 문자열
• varchar(n): 사용자 지정 최대 길이 n의 가변 길이 문자 문자열
• int: 기계 종속적인(machine-dependent) 정수의 유한한 부분 집합
• smallint: 기계 종속적인 작은 정수의 부분 집합
• numeric(p, d): user-specified precision(사용자 지정 정밀도) p 자리와 소수점 오른쪽 d 자리를 가지는 고정 소수점 수
  • 예: numeric(3,1) 은 44.5를 정확하게 저장할 수 있지만 444.5 또는 0.32는 저장할 수 없음
• real, double precision: 기계 종속적인 정밀도를 가지는 부동 소수점 수
• float(n): 최소 n 자릿수를 가지는 사용자 지정 정밀도의 부동 소수점 수

이제 위에서 배운 제약조건을 적용해서 테이블(Relation)을 만들어보자

테이블 생성 구문

• SQL Relation은 create table 명령을 사용하여 정의됩니다:

• r은 relation의 이름입니다.
• 각 $A_i$는 relation r의 스키마에서 attribute 이름입니다.
• $D_i$는 attribute $A_i$의 도메인 값의 데이터 유형입니다.

예시로 instructor 테이블을 생성해보자. 위에서 배운 제약조건을 적용하기 앞서 일단 도메인 유형만 신경써서 테이블을 생성해보자.

create table instructor (
  ID char(5),
  name varchar(20),
  dept_name varchar(20),
  salary numeric(8,2)
)

일반적인 프로그래밍 언어들과 다르게 도메인 유형이 Attribute 뒤에 온다는게 특징이다.

테이블 생성 시 무결성 제약 조건

이제 위에서 배운 무결성 제약조건을 적용해서 instructor 테이블을 수정해보자

• 무결성 제약 조건의 유형
  • primary key (A1, ..., An)
  • foreign key (Am, ..., An) references r
  • not null
• SQL은 무결성 제약 조건을 위반하는 데이터베이스의 업데이트를 방지할 수 있다.

create table instructor (
  ID char(5),
  name varchar(20) not null,
  dept_name varchar(20),
  salary numeric(8,2),
  primary key (ID),
  foreign key (dept_name) references department
);

이 외에도 student 테이블, course 테이블을 생성해보자

create table student (
  ID varchar(5),
  name varchar(20) not null,
  dept_name varchar(20),
  tot_cred numeric(3,0),
  primary key (ID),
  foreign key (dept_name) references department
);

create table course (
  course_id varchar(8),
  title varchar(50),
  dept_name varchar(20),
  credits numeric(2,0),
  primary key (course_id),
  foreign key (dept_name) references department
);

여기까지가 SQL의 DDL에 해당하는 내용이였다. 이제는 SQL의 DML 중 Query에 해당하는 구문에 대해 다뤄보겠다.

Query 구문

기본 쿼리 구조

• 일반적인 SQL 쿼리의 형식은 다음과 같다:⠀

• $A_i$는 attribute를 나타냅니다.
• $R_i$는 relation를 나타냅니다.
• P는 조건문입니다.
• SQL 쿼리의 결과는 relation입니다.

SQL Select

특정 Relation(r)에서 특정 조건(P)를 만족하는 레코드에서 원하는 Attiribute(A)만 뽑아서 가져온다.

• SQL Select 문은 데이터베이스에서 데이터를 검색하는 데 사용됩니다.
• 레코드를 가져옵니다.
• 데이터베이스에서 반환된 데이터는 결과 테이블에 저장됩니다.

SELECT column1, column2, ...
FROM table
WHERE condition;

• SELECT: 표시할 열들
• FROM: 사용할 테이블
• WHERE: 조건을 만족하는 행들만 반환

SQL 일반 구문

SELECT column-names
FROM table-name;

SQL Select 예시

SELECT *
FROM Employee;

참고) 에스터리스크 '*'는 select 절에서 "모든 Attribute”를 의미한다.

SELECT FirstName, FatherName, City, Phone
FROM Employee;

Select 절

• select 절은 쿼리 결과에 원하는 Attribute들을 나열한다.
• 관계 대수의 투영 연산(projection operation)에 해당한다.

참고) SQL 이름은 대소문자를 구분하지 않습니다 (즉, 대문자 또는 소문자를 사용할 수 있습니다).

ex) Name ≡ NAME ≡ name

예시: 모든 강사의 이름 찾기

select name from instructor;

• SQL은 쿼리 결과에 중복을 허용합니다.
• 중복을 제거하려면 select 뒤에 distinct 키워드를 삽입합니다.

모든 강사의 부서 이름을 찾고 중복을 제거

select distinct dept_name from instructor;

• all 키워드는 중복을 제거하지 않도록 지정합니다.

select all dept_name from instructor;

from 절

• from 절은 쿼리에 포함된 relation(테이블)을 나열합니다.
• 관계 대수의 카테시안 곱 연산(Cartesian product operation)에 해당합니다.

예시: 강사와 가르치는 강좌의 카티션 곱 찾기

select *
from instructor, teaches;

• 모든 가능한 강사-강좌 쌍을 생성하며, 두 relation의 모든 attribute을 포함한다.
• 카테시안 곱에서 말했던 내용이지만, 공통 attribute(e.g., ID)에 대해, 결과 테이블의 attribute은 relation의 이름을 사용하여 이름이 변경됩니다 (e.g., instructor.ID).

카테시안 곱 (X) 복습!

• 두 relation의 정보를 결합할 수 있습니다.
• 두 relation의 모든 튜플 조합을 생성합니다.
• relation r1과 r2의 카테시안 곱은 r1 X r2로 나타냅니다.

 

from절 예시

예시: 강사 이름과 강좌 ID 찾기

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

예시: 미술 부서의 모든 강사 이름과 강좌 ID 찾기

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID and instructor.dept_name = 'Art';

예시: suppliers 테이블

SELECT *
FROM suppliers
WHERE supplier_id < 400
ORDER BY city DESC;

• ORDER BY와 DESC에 대해선 아래서 다룬다.

where 절

• where 절은 결과가 충족해야 하는 조건을 지정합니다.
• where 절은 SQL DML 문과 함께 사용되며(select 뿐만 아니라 update, delete 등의 DML과도 같이 쓰인다는 말), 다음 일반 형식을 갖습니다:

SQL-DML-Statement
FROM table_name
WHERE predicate;

• 관계 대수(relational algebra)의 조건(predicative)에 해당합니다.
• where 절은 결과가 충족해야 하는 조건을 지정합니다.
• 관계 대수의 선택 조건(selection predicate)에 해당합니다.

예시: 컴퓨터 과학(Comp. Sci.) 부서의 모든 강사를 찾기

select name
from instructor
where dept_name = 'Comp. Sci.';

예시: 연봉이 80000 이상인 컴퓨터 과학 부서의 모든 강사를 찾기

select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 80000;

• SQL은 논리 연산자 and, or, not을 사용할 수 있습니다.
• 논리 연산자의 피연산자는 <, <=, >, >=, =, <>와 같은 비교 연산자를 포함할 수 있습니다.
• 비교는 산술 표현의 결과에도 적용될 수 있습니다.

또한 SQL은 Where 절에서 between 비교 연산자를 사용할 수 있다.

예시: 연봉이 $90,000에서 $100,000 사이인 모든 강사의 이름을 찾기 (즉, >= $90,000 그리고 <= $100,000)

select name
from instructor
where salary between 90000 and 100000;

튜플 비교

SQL 쿼리에서 여러 열(column) 또는 속성(attribute)을 동시에 비교하여 조건을 설정하는 방법으로, 각 열을 순서대로 비교하여 결과를 도출한다. 주로 Where 절에서 사용된다.

• 튜플 비교의 형식: (column1, column2, ..., columnN) = (value1, value2, ..., valueN)

예시

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

튜플의 표시 순서 정렬

쿼리의 결과로 얻은 릴레이션에서 튜플의 표시 순서를 정렬할 수 있다.

예시: 모든 강사의 이름을 알파벳 순서로 나열하기

select distinct name
from instructor
order by name;

• 각 attribute에 대해 내림차순을 desc, 오름차순을 asc로 지정할 수 있습니다.
  • 위 예시처럼 오름차순, 내림차순을 지정하지 않으면 기본적으로 오름차순으로 설정된다

예시: 이름을 내림차순으로 정렬

select distinct name
from instructor
order by name desc;

여기까지 쿼리에 해당하는 SQL 구문(select)를 살펴보았다. 이제부터 테이블 데이터에 대한 연산을 살펴보겠다.