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로 추가 확인

auto_increment설정 및 설정 확인(7->9)

제약조건 - 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%, 안해본거가 있어야한다. 개발자의 퍼포먼스가 개개인 마다 압도적으로 차이날수있다.