네이버 클라우드 캠프 60일차 230719

[Naver Cloud Camp 7] 교육 정리

네이버 클라우드 캠프 60일차 230719

우기37 2023. 7. 19. 20:47

#1 교육정리

1) MySQL - DQL(Data Query Language)

2) MySQL - FK(Foreign Key)

#2 MySQL - DQL(Data Query Language)

DQL?)

DQL은 Data Query Language의 약자로 데이터를 쿼리하는데 사용되는 SQL문을 뜻한다. 쿼리(Query)의 사전적 의미는 묻다, 질문하다 라는 뜻이다.

데이터베이스의 상황에 빗대어 보면, “데이터베이스 서버에게 데이터를 달라고 요청하는 것” 이라고 해석할 수 있다. 대표적으로 SELECT 문이 있다.

SELECT 문장은 앞서 살펴보았던 INSERT나 UPDATE 같은 작업과 비교해 봤을 때, 애플리케이션에서 사용되는 쿼리 중에서도 가장 비율이 높고, 잘 알고 사용하지 못하면 성능상 문제가 발생할 수 있기 때문에 가장 중요한 건 SQL이다. INSERT나 UPDATE 작업은 대부분 행(row) 단위로 처리되지만, SELECT는 여러 개의 테이블로부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 여러 개의 테이블들을 어떻게 읽을 것인가에 따라서 성능이 크게 달라질 수 있다. 정리하면, SELECT 문장은 테이블에 저장된 데이터를 꺼내오는 핵심 쿼리문이다.

Select 정리)

SELECT의 기본 형식에 대해서 배웠던 내용들을 정리하기전에 알아야할 것이 탐색하는데 들어가는 비용측면에서도 고려해야 한다는 것이다.

SELECT를 잘못 사용하면 극히 일부의 내용만을 필요로 하는 작업에서 여러번 전체 테이블 풀스캔(Full Scan)해야 하는 사태가 발생할 수 있다. 연습하려는 테이블은 비록 데이터 수가 20개도 안되는 양이라 별 차이가 없겠지만 현업에서 쓰이는 테이블은 양이 어마어마하기 때문에 풀스캔을 하지 않는 방법으로 SELECT문을 DB에 보내는 것이 좋다.

MySQL 공식 문서인 document를 들어가면 아래와 같이 SELECT Statement를 확인 할 수 있습니다.

아래 주소를 들어가시면 더욱 자세하게 문장에 대해서 알아 볼 수 있습니다.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

출처 : https://dev.mysql.com/doc/refman/8.0/en/select.html

select 문법)

## 테스트 용 테이블 및 데이터 준비

create table test1 (
  no int primary key auto_increment,
  name varchar(20) not null,
  class varchar(10) not null,
  working char(1) not null,
  tel varchar(20)
);

desc test1; 으로 table 요약을 살펴봅니다.

insert로 값 넣어주기

insert into test1(name,class,working) values('aaa','java100','Y');
insert into test1(name,class,working) values('bbb','java100','N');
insert into test1(name,class,working) values('ccc','java100','Y');
insert into test1(name,class,working) values('ddd','java100','N');
insert into test1(name,class,working) values('eee','java100','Y');
insert into test1(name,class,working) values('kkk','java101','N');
insert into test1(name,class,working) values('lll','java101','Y');
insert into test1(name,class,working) values('mmm','java101','N');
insert into test1(name,class,working) values('nnn','java101','Y');
insert into test1(name,class,working) values('ooo','java101','N');

## select
- 테이블의 데이터를 조회할 때 사용하는 명령이다.

/* 모든 컬럼 값 조회하기. 컬럼 순서는 테이블을 생성할 때 선언한 순서이다.*/
select * from 테이블;
select * from test1;

/* 특정 컬럼의 값만 조회할 때 => "프로젝션(projection)"이라 부른다.*/
select 컬럼명,컬럼명 from 테이블;
select no, name, tel from test1;

/* 가상의 컬럼 값을 조회하기*/
select no, concat(name,'(',class,')') from test1;

### 조회하는 컬럼에 별명 붙이기
- 별명을 붙이지 않으면 원래의 컬럼명이 조회 결과의 컬럼이름으로 사용된다.
- 위의 예제처럼 복잡한 식으로 표현한 컬럼인 경우 컬럼명도 그 식이 된다.
- 이런 경우 별명을 붙이면 조회 결과를 보기 쉽다.

/* 컬럼에 별명 붙이기*/
select 컬럼명 [as] 별명 ...
select
no as num,
concat(name,'(',class,')') as title
from test1;

/* as를 생략해도 된다.*/
select
no num,
concat(name,'(',class,')') title
from test1;

### 조회할 때 조건 지정하기
- where 절과 연산자를 이용하여 조회 조건을 지정할 수 있다.
- 이렇게 조건을 지정하여 결과를 선택하는 것을 "셀렉션(selection)" 이라 한다.

select ... from ... where 조건...
select *
from test1
where no > 5;

## 연산자

### OR, AND, NOT
- OR : 두 조건 중에 참인 것이 있으면 조회 결과에 포함시킨다.
- AND : 두 조건 모두 참일 때만 조회 결과에 포함시킨다.
- NOT : 조건에 일치하지 않을 때만 결과에 포함시킨다.

/* 재직자 또는 java100기 학생만 조회하라!*/
select no, name, class, working
from test1
where working='Y' or class='java100';

/* java100기 학생 중에 재직자만 조회하라!*/
select no, name, class, working
from test1
where working='Y' and class='java100';

/* 주의!
* where 절을 통해 결과 데이터를 먼저 선택(selection)한 다음
* 결과 데이터에서 가져올 컬럼을 선택(projection)한다.
* 따라서 실행 순서는:
* from ==> where ==> select
*/
select no, name
from test1
where working='Y' and class='java100';

/* 재직자가 아닌 사람만 조회하라!*/
select no, name, class, working
from test1
where not working = 'Y';

select no, name, class, working
from test1
where working != 'Y';

select no, name, class, working
from test1
where working <> 'Y';

아래와 같이 모두 같은 결과값이 출력됩니다.

/* 학생 번호가 짝수인 경우 전화 번호를 '1111'로 변경하라*/
update test1 set
tel = '1111'
where (no % 2) = 0;

/* 학생 번호가 3의 배수인 경우 전화번호를 '2222'로 변경하라*/
update test1 set
tel = '2222'
where (no % 3) = 0;

/* 전화 번호가 있는 학생만 조회하라!*/
/* => 다음과 같이 null에 != 연산자를 사용하면 조건이 맞지 않는다.*/
select *
from test1
where tel != null;

/* => null인지 여부를 가릴 때는 is 또는 is not 연산자를 사용하라!*/
select *
from test1
where tel is not null;

select *
from test1
where not tel is null;

/* 전화 번호가 없는 학생만 조회하라!*/
/* => null인지 여부를 가릴 때는 = 연산자가 아닌 is 연산자를 사용해야 한다.*/
select *
from test1
where tel = null; /* 실패 */

select *
from test1
where tel is null; /* OK */

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

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);

### between 값1 and 값2
- 두 값 사이(두 값도 포함)에 있는지 검사한다.

select 5 between 3 and 5;

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

insert into test1(name,class,working) values('xxx', 'window27', '1');
insert into test1(name,class,working) values('yyy', 'window27', '0');
insert into test1(name,class,working) values('zzz', 'window28', '1');
insert into test1(name,class,working) values('qqq', 'window28', '0');
insert into test1(name,class,working) values('s01', 'javawin1', '1');
insert into test1(name,class,working) values('s02', 'javawin1', '0');
insert into test1(name,class,working) values('s03', 'javawin1', '0');
insert into test1(name,class,working) values('s04', 'iotjava5', '1');
insert into test1(name,class,working) values('s05', 'iotjava5', '0');
insert into test1(name,class,working) values('s06', 'iotjava5', '0');
insert into test1(name,class,working) values('s011', 'iotjava5', '1');
insert into test1(name,class,working) values('s012', 'iotjava5', '1');
insert into test1(name,class,working) values('s013', 'iotjava5', '1');

/* class 이름이 java로 시작하는 모든 학생 조회하기
* => % : 0개 이상의 문자
*/
select *
from test1
where class like 'java%';

/* class 이름에 java를 포함한 모든 학생 조회하기
이 경우 조회 속도가 느리다.*/
select *
from test1
where class like '%java%';

/* class 이름이 101로 끝나는 반의 모든 학생 조회하기 */
select *
from test1
where class like '%101';

/* 학생의 이름에서 첫번째 문자가 s이고 두번째 문자가 0인 학생 중에서
딱 세자의 이름을 가진 학생을 모두 조회하라!*/

/* => %는 0자 이상을 의미하기 때문에 이 조건에 맞지 않다.*/
select *
from test1
where name like 's0%';

/* => _는 딱 1자를 의미한다.*/
select *
from test1
where name like 's0_';

### 날짜 다루기
- 날짜 함수와 문자열 함수를 사용하여 날짜 값을 다루는 방법.
기존 테이블을 drop 후에 날짜 함수와 문자열 함수를 사용할 테이블을 새로 생성해줍니다.
drop table test1;

create table test1 (
  no int not null,
  title varchar(200) not null,
  content text,
  regdt datetime not null
);

alter table test1
  add constraint primary key (no),
  modify column no int not null auto_increment;

insert into test1(title, regdt) values('aaaa', '2022-01-27');
insert into test1(title, regdt) values('bbbb', '2022-2-2');
insert into test1(title, regdt) values('cccc', '2022-2-13');
insert into test1(title, regdt) values('dddd', '2022-3-2');
insert into test1(title, regdt) values('eeee', '2022-4-15');
insert into test1(title, regdt) values('ffff', '2022-6-7');
insert into test1(title, regdt) values('gggg', '2022-6-17');
insert into test1(title, regdt) values('hhhh', '2022-6-27');
insert into test1(title, regdt) values('iiii', '2022-9-5');
insert into test1(title, regdt) values('jjjj', '2022-10-12');
insert into test1(title, regdt) values('kkkk', '2022-11-22');
insert into test1(title, regdt) values('llll', '2022-11-24');
insert into test1(title, regdt) values('mmmm', '2022-12-31');

- 날짜 값 비교하기

/* 특정 날짜의 게시글 찾기 */
select *
from test1
where regdt = '2022-6-17';

/* 특정 기간의 게시글 조회 */
select *
from test1
where regdt between '2022-11-1' and '2022-12-31';

select *
from test1
where regdt >= '2022-11-1' and regdt <= '2022-12-31';

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

/* 현재 날짜 및 시간 알아내기 */
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(), '2023-2-10');

/* 날짜에서 특정 형식으로 값을 추출하기 */
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'));

그림으로 이해하기)

#3 MySQL - FK(Foreign Key)

# FK(Foreign Key)
- 다른 테이블의 PK(Primary Key)를 참조하는 컬럼이다.
첨부파일 정보가 포함된 게시글을 저장하는 테이블을 정의해 보자.

외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다.

외래키가 포함된 테이블을 자식 테이블이라고 하고 외래키 값을 제공하는 테이블을 부모 테이블이라한다.

## 한 개의 테이블로 게시글과 첨부파일을 저장하기
최대 5개의 첨부파일 경로를 저장할 컬럼을 만든다.

/* 게시글을 저장할 테이블 */
create table test1(
  no int primary key auto_increment,
  title varchar(255) not null,
  content text,
  rdt datetime default now(),
  filepath1 varchar(255),
  filepath2 varchar(255),
  filepath3 varchar(255),
  filepath4 varchar(255),
  filepath5 varchar(255)
);

insert into test1(title,content,filepath1,filepath2,filepath3,filepath4,filepath5)
values ('제목1', '내용', 'a.gif', null, null, null, null);

insert into test1(title,content,filepath1,filepath2,filepath3,filepath4,filepath5)
values ('제목2', '내용', 'b1.gif', 'b2.gif', 'b3.gif', null, null);

insert into test1(title,content,filepath1,filepath2,filepath3,filepath4,filepath5)
values ('제목3', '내용', null, null, null, null, null);

-- 파일 경로를 저장할 수 있는 컬럼이 5개 밖에 없기 때문에 6번째와 7번째 첨부파일을 입력할 수 없다.
insert into test1(title,content,filepath1,filepath2,filepath3,filepath4,filepath5)
values ('제목4', '내용', 'd1.gif', 'd2.gif', 'd3.gif', 'd4.gif', 'd5.gif'/*, 'd6.gif', 'd7.gif'*/);

고민해볼 사항!
- 첨부 파일의 개수를 5 개로 정해 놓았다.
- 따라서 최대 5개의 첨부 파일만 테이블에 저장할 수 있다.
- 첨부파일이 없더라도 5개의 컬럼은 메모리를 차지한다.

실무에서 원하는 것!
- 첨부 파일의 개수에 제한을 받고 싶지 않다.
- 첨부 파일 개수만큼만 값을 저장하고 싶다.

이렇게 같은 데이터를 저장할 컬럼이 중복된 경우에는,
- 중복 컬럼을 별도의 테이블로 분리한다.
- 중복 컬럼의 값이 어느 테이블의 어느 데이터의 값인지 지정한다.

## 게시글과 첨부파일의 정보를 여러 개의 테이블에 분산 저장하기

/* 게시판 테이블 */
create table test1(
  no int not null primary key auto_increment,
  title varchar(255) not null,
  content text,
  rdt datetime not null default now()
);

/* 첨부 파일 테이블 */
create table test2(
  fno int not null primary key auto_increment, /* 첨부파일 고유번호 */
  filepath varchar(255) not null, /* 파일시스템에 저장된 첨부파일의 경로 */
  bno int not null /* 게시글 번호 */
);

게시판 데이터 입력:

insert into test1(no, title) values(1, 'aaa');
insert into test1(no, title) values(2, 'bbb');
insert into test1(no, title) values(3, 'ccc');
insert into test1(no, title) values(4, 'ddd');
insert into test1(no, title) values(5, 'eee');
insert into test1(no, title) values(6, 'fff');
insert into test1(no, title) values(7, 'ggg');
insert into test1(no, title) values(8, 'hhh');
insert into test1(no, title) values(9, 'iii');
insert into test1(no, title) values(10, 'jjj');

첨부파일 데이터 입력:

insert into test2(filepath, bno) values('c:/download/a1.gif', 1);
insert into test2(filepath, bno) values('c:/download/a2.gif', 1);
insert into test2(filepath, bno) values('c:/download/a3.gif', 1);
insert into test2(filepath, bno) values('c:/download/e1.gif', 5);
insert into test2(filepath, bno) values('c:/download/e2.gif', 5);
insert into test2(filepath, bno) values('c:/download/j1.gif', 10);

## FK(Foreign Key) 제약 조건이 없을 때

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

insert into test2(filepath, bno) values('c:/download/x.gif', 100);

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

delete from test1 where no=1;

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

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

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

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

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

예)
/* 기존에 테이블에 무효한 데이터가 있을 수 있기 때문에 먼저 테이블의 데이터를 지운다.*/
delete from test2;

/* fk 컬럼을 설정하기 전에 무효한 데이터를 삭제해야 한다. */
alter table test2
add constraint test2_bno_fk foreign key (bno) references test1(no);

위와 같이 test2 테이블에 FK 제약 조건을 건 다음에 데이터를 입력해보자!

/* 1번 게시물이 존재하지 않기 때문에 데이터를 입력할 수 없다 */
insert into test2(filepath, bno) values('c:/download/a.gif', 1);
insert into test2(filepath, bno) values('c:/download/b.gif', 1);
insert into test2(filepath, bno) values('c:/download/c.gif', 1);

/* 5번, 10번 게시물은 존재하기 때문에 첨부파일 데이터를 입력할 수 있다.*/
insert into test2(filepath, bno) values('c:/download/d.gif', 5);
insert into test2(filepath, bno) values('c:/download/e.gif', 5);
insert into test2(filepath, bno) values('c:/download/f.gif', 10);

/* 2번 게시물은 test2 테이블의 데이터들이 참조하지 않기 때문에 마음대로 지울 수 있다.*/
delete from test1 where no=2; -- OK!

/* 그러나 5번 게시물은 삭제할 수 없다. 왜? test2 테이블의 데이터 중 일부가 참조하기 때문이다.*/
delete from test1 where no=5; -- Error!

## 용어 정리
- test1 처럼 다른 테이블에 의해 참조되는 테이블을 '부모 테이블'이라 부른다.
- test2 처럼 다른 테이블의 데이터를 참조하는 테이블을 '자식 테이블'이라 부른다.

그림으로 이해하기)