• SQL 사용법
  • DML 사용법(계속)
  • PK, FK
  • 조인 예제 테이블 준비

---

DQL(Data Query Language)

데이터를 조회할 때 사용하는 문법

---

쿼리 실행 순서

1. FROM
2. WHERE (SELECTION)
3. GROUP BY
4. HAVING
5. SELECT (PROJECTION)
6. ORDER BY

where 절을 통해 결과 데이터를 먼저 선택(selection)한 다음, 결과 데이터에서 가져올 컬럼을 선택(projection)한다.

---

연산자

사칙연산

+, -, *, /, % 연산자를 사용할 수 있다.

select (4 + 5), (4 - 5), (4 * 5), (4 / 5), (4 % 5);

비교연산

=, !=, >, >=, <, <=, <>

select (4=5), (4!=5), (4>5), (4>=5), (4<5), (4<=5), (4<>5);

like

문자열을 비교할 때 사용한다.

%

% 는 0개 이상의 문자

select *
from test1
where class like 'java%';

_

_ 는 딱 1자를 의미한다.

select *
from test1
where name like 's0_';

날짜 다루기

날짜 함수와 문자열 함수를 사용하여 날짜 값을 다루는 방법.

CURDATE

CURDATE()
CURRENT_DATE
CURRENT_DATE()

SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-05 |
+------------+

n a numeric context (note this is not performing date calculations, 정수형):

SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
|     20190305 |
+--------------+

Data calculation:

SELECT CURDATE() - INTERVAL 5 DAY;
+----------------------------+
| CURDATE() - INTERVAL 5 DAY |
+----------------------------+
| 2019-02-28                 |
+----------------------------+

날짜를 다루는 연산자와 함수

현재 날짜 및 시간 알아내기

select now();

현재 날짜 알아내기

select curdate();

현재 시간 알아내기

select curtime();

주어진 날짜, 시간에서 날짜만 뽑거나 시간만 뽑기

select regdt, date(regdt), time(regdt) from test1;

특정 날짜에 시,분,초,일,월,년을 추가하거나 빼기

date_add(날짜데이터, interval 값 단위);
date_sub(날짜데이터, interval 값 단위);

select date_add(now(), interval 11 day);
select date_sub(now(), interval 11 day);

두 날짜 사이의 간격을 알아내기

datediff(날짜1, 날짜2);
select datediff(curdate(), '2022-5-2');

날짜에서 특정 형식으로 값을 추출하기

date_format(날짜, 형식)
select regdt, date_format(regdt, '%m/%e/%Y') from test1; /* 09/7/2022 */
select regdt, date_format(regdt, '%M/%d/%y') from test1; /* September/07/17 */
select regdt, date_format(regdt, '%W %w %a') from test1; /* Thursday 4 Thu */
select regdt, date_format(regdt, '%M %b') from test1; /* September Sep */
select now(), date_format(now(), '%p %h %H %l'); /* PM 01 13 1 */
select now(), date_format(now(), '%i %s'); /* 05 45 */

문자열을 날짜 값으로 바꾸기

select str_to_date('11/22/2022', '%m/%d/%Y');
select str_to_date('2022.2.12', '%Y.%m.%d');

날짜 값을 저장할 때 기본 형식은 yyyy-MM-dd이다.

insert into test1 (title, regdt) values('aaaa', '2022-11-22');

다음 형식의 문자열을 날짜 값으로 지정할 수 없다.

insert into test1 (title, regdt) values('bbbb', '11/22/2022');

특정 형식으로 입력된 날짜를 date 타입의 컬럼 값으로 변환하면 입력할 수 있다.

insert into test1 (title, regdt) values('bbbb', str_to_date('11/22/2022', '%m/%d/%Y'));

---

FK(Foreign KEY)

다른 테이블의 PK를 참조하는 컬럼이다.

DBMS와 파일 저장

1. 파일저장1
   DBMS에 파일 저장 ⇒ 파일 I/O가 느리다.
   

• 데이터베이스가 매우 커진다.
• 데이터베이스 파일 안에 파일을 저장하고 꺼낸다.

⇒ 파일 I/O가 느리다.

2. 파일저장2
   DBMS에는 파일 경로만 저장 ⇒ DBMS 부담이 줄어든다.
   

→ 실제 파일 I/O는 OS에서 처리 → I/O 속도 빠르다. → DBMS 부담이 줄어든다.

DBMS에 파일을 저장하는 사례

1. 사용자가 그림이 포함된 콘텐트를 저장할 때
   • 그림의 크기가 작을 때(1MB 안팎)
   • 콘텐트가 포함된 전체 그림들의 크기가 너무 크지 않을 때(10MB 이내)
2. 그림 파일의 데이터를 텍스트로 변환하여 콘텐트 전체를 텍스트로 만든다.
3. 그림 데이터가 포함된

그림 ↔ 텍스트 변환

Base64 인코딩/디코딩

→ 바이너리데이터를 텍스트로 다룰 때 사용!

웹 브라우저가 그림을 읽는 과정

• 아무튼 출력하려면 비트맵 이미지로 먼저 바뀌어야 한다.
• JPEG = 그림 데이터 압축 알고리즘
  (손실 압축 → 압축 해제한 데이터는 원래의 데이터와 다르다!)

첨부파일이 있는 게시글을 다루기

1. 한 개의 테이블 사용

• 문제점

1. 첨부파일의 경로 저장 가능 횟수가 제한된다. → 최대 5개
2. 첨부파일이 없는 경우, 컬럼이 낭비된다.

2. 첨부파일 정보를 별도의 테이블로 분리

1. 개수의 제한이 없다.
filepath에는 첨부파일의 데이터 자체가 아닌, 첨부파일의 경로가 들어가게 된다.
파일 자체는 filesystem에 저장한다.
2. 첨부 파일이 없는 경우 메모리가 절약된다.

File System(파일 시스템)

파일탐색기: OS(운영체제)가 관리하는 파일시스템

→ 파일 정보를 다루는 시스템
File = 디렉토리 / 파일

파일정보

• 파일의 이름
• 파일의 크기
• 파일의 경로
• 물리적 위치 정보(트랙, 섹터, ...)
  ...

• track
  수평기록방식 → 수직기록방식
  (같은 면적에 더 많은 데이터를 저장할 수 있다.)
• Window OS 파일시스템: NTFS
  NTFS = FAT32 + 디렉토리/파일에 접근 권한 설정
  FAT32 = FAT(File Allocation Table) + 파일명 최대 255자

File System과 접근 도구들

FK 제약 조건이 없을 때

문제점 1

• 첨부파일 데이터를 입력할 때 존재하지 않는 게시물 번호가 들어 갈 수 있다.
• 그러면 첨부파일 데이터는 무효한 데이터가 된다.

insert into test2(filepath, bno) values('c:/download/x.gif', 100);
-- 100번 게시물은 존재하지 않는다.

문제점 2

첨부 파일이 있는 게시물을 삭제할 때,
해당 게시물을 참조하는 첨부파일 데이터는 무효한 데이터가 된다.

delete from test1 where no=1;
-- 1번 게시물에 첨부파일이 존재한다.

이런 문제가 발생한 이유?

• 다른 테이블의 데이터를 참조하는 경우, 참조 데이터의 존재 유무를 검사하지 않기 때문이다.
• 테이블의 데이터를 삭제할 때 다른 테이블이 참조하는지 여부를 검사하지 않기 때문이다.

해결책?

• 다른 데이터를 참조하는 경우 해당 데이터의 존재 유무를 검사하도록 강제한다.
• 데이터를 삭제하는 경우, 다른 테이터에 의해 참조되는지 여부를 검사하도록 강제한다.
• 이것을 가능하게 하는 문법이 "외부키(Foreign Key)" 이다.

FK(foreign key) 제약 조건 설정

• 다른 테이블의 데이터와 연관된 데이터를 저장할 때 무효한 데이터가 입력되지 않도록 제어하는 문법이다.
• 다른 테이블의 데이터가 참조하는 데이터를 임의의 지우지 못하도록 제어하는 문법이다.
• 그래서 데이터의 무결성(data integrity; 결함이 없는 상태)을 유지하게 도와주는 문법이다.

다른 테이블의 PK를 참조하는 컬럼으로 선언한다.

alter table 테이블명
    add constraint 제약조건이름 foreign key (컬럼명) references 테이블명(컬럼명);

Foreign KEY

ER Diagram

테이블 간의 관계를 표현하는 표기법
E: Entity(table)
R: Relationship

IE(Information Engineering Notation) 표기법

예제: ER Diagram

comment

정리 잘 된 블로그
https://jhnyang.tistory.com/309