2023.11.20 5일차 회고

5일차 공부

- 전날 수업 내용 복습

- 타입 변환 CAST, CONVERT 사용 시 유의사항 공부

- 특정 날짜, 기간 조회

- 제약조건(NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE)

- NOT NULL, AUTO_INCREMENT 공부 및 실습

- UNIQUE, UNIQUE ALTER문 공부 및 실습

- PRIMARY KEY, FOREIGN KEY 공부 및 실습

- ON UPDATE CASCADE 공부 및 실습

- DEFAULT 공부

- 흐름제어 CASE, IF 공부 및 실습

- 트랜잭션 공부

벌써 1주차가 지나고 2주차가 시작되었다. 블로그를 아직까지 한번도 빼먹지 않고 꾸준히 쓰고있다. 블로그를 처음 시작하면서 복습도 할겸 매일 블로그 글을 작성해야겠다고 생각했는데 블로그에 글쓰기가 나름 재밌어서 꾸준히 가능할 것 같다. 글을 써본지 언제인지 기억도 잘 안나는데 이렇게 블로그에 글을 쓰면서 그날 느낀 생각들이나 공부한 내역들을 정리하니까 좋다. 공부도 꾸준히하고 블로그도 매일 작성 할것이다. 월요일이어서 아침부터 조금은 피곤한 날이었지만 이겨내고 오늘도 마무리한다.

5일차 공부 메모

4일차 수업 복습 진행

타입 변환(함수)

CAST, CONVERT 사용 시 유의사항
- 최신버전
  - CAST(’123’ as INT) 방식으로 int 사용 가능
  - CAST(’123’ as signed) 방식으로 signed(또는 unsigned) 사용가능
- 구버전
  - CAST(’123’ as signed) 방식으로 signed(또는 unsigned)만 사용가눙
- 여기서 signed는 부호있는 정수. 즉, 음수/양수 모두 포함
  - unsigned는 부호 없는 정수로서 0이상 양수를 의미

특정 날짜, 기간 조회

= 날짜 데이터 조회하는 방식 중 많이 사용하는 방식

DATA_FORMAT(date, format)을 활용한 조회
- Y, mm, dd, H, i, (대소문자 구분)
LIKE를 사용하여 문자열 형식으로 조회
- SELECT * FROM post where created_time like ‘2023%’;
BETWEEN 연산자
- 특정 날짜 범위를 지정하여 데이터를 검색
- WHERE created_time BETWEEN ‘2021-01-01’ AND ‘2023-11-17’
날짜 비교 연산자
- WHERE created_time = > ‘2021-01-01’ AND created_time < = ‘2023-11-17’

오늘 날짜 관련 함수

now()

*실습 : date_format, like, between, 비교연산자를 각각 사용하여 2023년에 생성된 데이터 출력

SELECT * FROM post where created_time like '2023%'; SELECT * FROM post where created_time between '2021-01-01' AND '2022-12-31';

*실습 : now()를 활용해서 오늘 날짜에 생성된 데이터 출력하기

select * from post where date_format(created_time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d');

제약조건(constraint)

데이터를 입력받을 떄 실행되는 검사 규칙
CREATE 문으로 테이블을 생성 또는 ALTER 문으로 필드를 추가할 때 설정
종류
- NOT NULL
- PRIMARY KEY → NOT NULL, UNIQUE, 한 테이블당 1개
- FOREIGN KEY
- UNIQUE → 한 테이블에 여러개 설정가능

제약조건 - NOT NULL

default 값은 nullable
not null제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 한다.
문법 예제
- CREATE TABLE author
- (
- id INT NOT NULL
- name VARCHAR(30),
- …
- );
- ALTER문을 써서 post의 title을 not null 조건으로 바꿔보자
  - 이미 NULL인 데이터는 삭제
```
-- ALTER문을 써서 post의 title을 not null 조건 변경
alter table post modify column title VARCHAR(255) not null;
```
AUTO_INCREMENT 키워드와 함께
- 새로운 레코드가 추가될 때마다 1씩 증가된 값을 저장
- author, post 테이블
- author, post 테이블의 id에 auto_increment로 바꿔보자
  - ALTER TABLE author MODIFY COLUMN id INT AUTO_INCREMENT;
- auto_increment는 무조건 추가하기

auto_increment = 내부적으로 카운트 값을 갖고있다.

auto_increment를 걸고 6번 low 데이터 지운 후 새로운 데이터 추가하면

7번 low로 데이터가 들어간다.(한칸 건너 뜀)

= 실무에서는 auto_increment를 걸지 않고 카운트 테이블을 따로 만든다.

삭제된 low 데이터를 바라보고 있는 다른테이블에 FK가 걸려있는 데이터들은 삭제된 low 데이터기

삭제될 때 설정된 제약조건 작업(set null, cascade, regtric)으로 변경되기 때문에 특정 low에 고민할 필요없다.

===== auto_increment 실습 =====

-- auto_increment 실습
-- id컬럼에 auto_increment 설정
 alter table post modify column id INT AUTO_increment;
-- 새로운 데이터 추가(8 low)
 insert into post(title, contents) values('you', 'ggggggg');
-- 새로 넣은 데이터 삭제(8 low)
 delete from post where id = 8;
-- 다시 데이터 추가
 insert into post(title, contents) values('you', 'ggggggg');
-- 다시 데이터 추가 시 9 low로 추가 확인

제약조건 - UNIQUE

UNIQUE 제약 조건을 설정하면, 해당 필드는 값이 unique 해야함을 의미

방법 1
- CREATE TABLE 테이블이름
- (필드명 필드타입 UNIQUE,
- …);
방법 2
- CREATE TABLE 테이블이름
- (필드이름 필드타입, …, [CONSTRAINT 제약조건이름] UNIQUE (필드이름);
- UNIQUE 제약 조건을 별도로 정의하며, 선택적으로 제약 조건에 이름을 부여하는 방법
UNIQUE 제약 조건을 설정하면, 해당 필드는 자동으로 인덱스(INDEX)로 설정
- show index from 테이블명;
- index삭제 : ALTER TABLE 테이블이름 DROP INDEX 인덱스명;
- 제약조건 삭제시 index 삭제, index 삭제시 제약조건 삭제

===== UNIQUE 실습 =====

UNIQUE ALTER문

방법 1
- ALTER TALBE 테이블이름 MODIFY COLUMN 필드이름 필드타입 UNIQUE
방법 2(별도의 제약조건이름 옵션)
- ALTER TABLE 테이블 이름 ADD CONSTRAINT 제약조건이름 UNIQUE(필드이름)
- 제약조건이름은 직접 만들기
제약조건 제거
- 제약조건 목록 조회
  - select * from information_schema.key_column_usage where table_name = 'author';
- ALTER TABLE 테이블 명 DROP CONSTRAINT 제약조건이름;
- ALTER TABLE 테이블 명 DROP FOREIGN KEY 제약조건이름;
author 테이블 email에 unique 제약 조건 추가
- 컬럼 제약 조건으로 추가
  - ALTER TABLE author MODIFY COLUMN email VARCHAR(255) UNIQUE;
- 제약 조건 제거 및 index제거
  - ALTER TABLE author DROP CONSTRAINT email;
- 테이블 제약조건 추가형식으로 추가
  - ALTER TABLE author ADD CONSTRAINT email2 UNIQUE(email);

제약조건 - PRIMARY KEY

PRIMARY KEY 제약 조건을 가진 컬럼을 기본키(pk)라 함
- NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가진다.
PRIMARY KEY는 테이블당 오직 하나의 필드에만 설정
- UNIQUE는 한 테이블의 여러 필드에 설정 가능
- NOT NULL도 물론 여러 필드에 설정 가능
없던 PK를 설정하기 위한 ALTER문 예제
- 방법 1
  - ALTER TABLE 테이블이름
  - MODIFY COLUMN 필드이름 필드타입 PRIMARY KEY
- 방법 2(별도의 제약조건이름 옵션)
  - ALTER TABLE 테이블이름
  - ADD CONSTRAINT 제약조건이름 PRIMARY KEY (필드이름)

제약조건 - FOREIGN KEY

외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할
- 기준이 되는 다른 테이블의 내용응ㄹ 참조해서 레코드가 입력
- 하나의 테이블을 다른 테이블에 의존하게 만드는 것
- 다른 테이블의 필드는 반드시 UNIQUE나 PROMARY KEY 제역 조건이어야 함
문법
- CREATE TABLE 테이블이름
- (
- 필드이름 필드타입,
- …,
- [CONSTRAINT 제약조건이름]
- FOREIGN KEY (필드이름)
- REFERENCES
참조되는 테이블에서 데이터의 수정이나 삭제가 발생시 영향
- ON DELETE
- ON UPDATE
- 기본값은 delete, update 모두 restrict옵션이 걸려 있으므로, 변경하고 싶다면 각각 지정필요
위 설정 시 동작 옵션
- CASCADE
  - 참조되는 테이블에서 데이터를 삭제/수정하면 같이 삭제/수정
- SET NULL
  - 참조되는 테이블(author)에서 데이터를 삭제/수정하면 post테이블 데이터는 NULL로 변경
- RESTRICT
  - fk로 잡은 테이블의 데이터가 남아 있으면, fk 대상 데이터 수정/삭제 불가
  - 동작옵션을 주지 않으면 기본은 RESTRICT

제약조건 - ON UPDATE CASCADE

외래키 제약조건에서 ON UPDATE CASCADE등의 옵션
POST 테이블에 ON UPDATE CASCADE 설정
- 먼저, 기존의 foreign key 제약조건을 조회 후 삭제
- SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
- WHERE TABLE_NAME = ‘post’;
- ALTER TABLE post DROP FOREIGN KEY post_ibfk_1;
- ALTER TABLE post DROP INDEX author_id;
- FOREIGN KEY를 없애도 INDEX는 남아있기때문에 INDEX는 따로 지워줘야 함
새롭게 제약조건 추가
- ALTER TABLE post ADD CONSTRAINT post author fk FOREIGN KEY(author_id)
- REFERENCES author(id) ON UPDATE CASCADE;
테스트
- 삭제
  - 삭제불가
  - restrict와 동일
- 수정
  - author 테이블의 id 수정 시 post테이블의 id도 같이 수정

===== 제약조건 - ON UPDATE CASCADE =====

외래키 제약조건에서 ON DELETE CASCADE등의 옵션

ON DELETE CASCADE 테스트
- 삭제시
  - author 삭제 시 post 같이 삿제
- 수정시
  - author id 수정 불가
  - restrict와 동일
ON DELETE SET NULL ON UPDATE SET NULL 또한 동일 방법으로 테스트

describe post; show create table post; SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'post'; ALTER TABLE post ADD CONSTRAINT post_author_fk FOREIGN KEY(author_id) REFERENCES author(id) ON UPDATE CASCADE; ALTER TABLE post DROP FOREIGN KEY post_author_fk; -- 제약조건 삭제 ALTER TABLE post DROP INDEX post_author_fk; -- index 삭제 show index from post; -- 삭제 후 제약조건 확인, -- SET NULL 제약조건 적용 ALTER TABLE post ADD CONSTRAINT post_author_fk FOREIGN KEY(author_id) REFERENCES author(id) ON DELETE SET NULL ON UPDATE SET NULL; -- SET NULL == 메인 테이블인 author가 삭제되어도 연결되어있는 post 데이터는 삭제되지 않고 author_id만 null값으로 세팅.

제약조건 - DEFAULT

데이터를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본값을 저장
문법
- CREATE TABLE Test
- (
- ID INT,
- Name VARCHAR(30) DEFAULT ‘Anonymous’);
시간 세팅시 가장 많이 사용
- ALTER TABLE author ADD create_at DATETIME DEFAULT CURRENT_TIMESTAMP;
- ALTER TABLE post ADD create_at DATETIME DEFAULT CURRENT_TIMESTAMP;

흐름제어

CASE
- CASE value
- WHEN [compare_value1] THEN result1 ⇒ if(value == compare_value1)
- WHEN [compare_value2] THEN result2 … else if(value == compart_value2)
- ELSE result
- END
CASE와 END로 이루어져있고, 원하는 조건내에 존재하지 않으면 ELSE문을 타고,
ELSE문이 없을경우 null을 return

===== 흐름제어 CASE실습 =====

-- 흐름제어 실습
select id, title, contents,
case
 when author_id = 1 then 'First Author'
 when author_id = 2 then 'Second Author'
 when author_id i null then 'Anonymous'
 ELSE 'Others'
end
as author_type from post;

IF()
IF(a, b, c)
a는 조건, b는 참일경우 반환값, c는 거짓일 경우 반환값
만약 a조건이 참이면 b를 반환하고, 거짓이면 c를 반환합니다.
SELECT IF(0<1, ‘yes’, ‘no’);
IFNULL(a, b)
- 만약 a의 값이 NULL이 아니면 a 그 자체를 반환하고, NULL이면 b를 반환
- select id, title, contents, ifnull(author_id, ‘anonymous’) from post;

-- if문 실습
SELECT IF(0<1, 'yes', 'no');
select id, title, contents, ifnull(author_id, 'anonymous') as author_type from post;
select id, title, contents, if(id = 1, 'first author', 'others') as author_type from post;
select id, title, contents, if(author_id is not null, author_id, 'anonymous') as author_type from post;
select id, title, contents, if(author_id is null, 'anonymous', author_id) as author_type from post;

MARIA DB 심화 시작

트랜잭션 설명할 수 있을정도로 익히기

COMMIT, ROLLBACK 실습
- sql툴에서 auto_commit 모드 해제
- 예시) insert into board_test.author(id, name, email) values(2, 'test', 'test@naver,com'); insert into board_test.post(title, contents, author_id) values('hello', 'hello is', 10);
  - 위 코드 실행후 commit하면 첫번째 쿼리만 확정
  - 위 코드 실행후 rollback하면 모두 취소

===== 트랜잭션 예시=====

insert into author(id, name, email) values(5, 'test', 'test@naver,com');
commit;
insert into author(id, name, email) values(6, 'test', 'test@naver,com');
insert into post(title, contents, author_id) values('hello', 'hello is', 10); rollback;

== 1번째 줄 쿼리는 2번째 줄 commit으로 DB 영구저장 됌

== 3번째 줄 쿼리는 트랜잭션으로 실행되었지만 commit은 안된상태(영구저장 X)

== 4번째 줄 쿼리는 적용X DB fk때문에 일부로 안들어가는 쿼리

== rollback 시 3번째줄 쿼리만 rollback.(4번째 쿼리는 이미 실패, 1번째 쿼리는 이미 Commit 완료

한 서비스에 한 트랜잭션이라고 이해.

commit은 한 서비스(사용자의 하나의 액션 ex.)주문)가 정상적으로 진행되면 자동 Commit
예외 = 강제 rollback 시켜야 할때.

데이터베이스 프로젝트
- 서비스 선정, DB 시각화(모델링), DDL문, DML문 테스트 GitHub repo 채운다는 생각으로

항상 새로운 공부를 하려고 해야한다.
- 해본거 60~70%, 안해본거가 있어야한다. 개발자의 퍼포먼스가 개개인 마다 압도적으로 차이날수있다.

저작자표시

'한화시스템 백엔드 SW교육' 카테고리의 다른 글

한화시스템 BEYOND 캠프 3기 2주차 회고 (0)	2023.12.05
2023.11.21 6일차 회고 (4)	2023.11.22
한화시스템 BEYOND 캠프 3기 1주차 회고 (3)	2023.11.20
2023.11.17 4일차 회고 (2)	2023.11.17
2023.11.16 3일차 회고 (2)	2023.11.16

'한화시스템 백엔드 SW교육' 카테고리의 다른 글

티스토리툴바