📅 2023년 11월 20일

오늘도 DML관련해서 더 많은 실습을 진행했으며, 제약 조건과 트랜잭션에 관한 공부했다. [DB 3일차]

---

5일차 : DDL 복습, 제약조건, 흐름제어 , 트랜잭션, DB동시성 이슈, DB격리 수준

DDL 복습

select * from posts;

insert into posts(id) values(11);

select * from posts where date_format(created_tile1, '%Y') = '2023';

select * from posts where created_tile1 like '2023%';

select * from posts where created_tile1 between '2023-01-01' and '2023-12-31';

select * from posts where created_tile1 >= '2023-01-01' and created_tile1 <= '2023-12-31';

select * from posts where date_format(created_tile1, '%Y') = date_format(now(), '%Y');

ALTER TABLE posts modify column title varchar(255) not null;

auto_increment

alter table author modify column id int auto_increment;

흔히 말하는 넘버링 을 위해 사용하며 update 가 가능하다 (중복은 안됨) 가장 마지막의 입력한 값을 기준으로 숫자를 증가시킨다.

---

제약 조건 : unique , foreign key

-- 제약 조건 검사
select * from information_schema.key_column_usage where table_name = 'author';

alter table author drop constraint email;

alter table author add constraint email2 unique(email);	-- unique 제약조건 추가

alter table author modify column email varchar(250);    -- 제약조건은 사라지지 않는다.

-- on update, delete 설정

-- 제약 조건 검사
select * from information_schema.key_column_usage where table_name = 'posts'; 

alter table posts drop foreign key post_auth_fk;	-- 외래키 삭제

alter table posts drop index post_auth_fk;	-- 인덱스 삭제

-- 외래키 추가
alter table posts add constraint post_auth_fk foreign key(auth_id) references author(id) on update cascade; 

-- on update cascade : author 테이블 수정시 posts 테이블도 자동 수정
update author set id = 29 where id = 26;

-- set null : 참조 수정시 null 입력
alter table posts add constraint post_auth_fk foreign key(auth_id) references author(id) on delete set null on update set null;

show create table posts;

-- CREATE TABLE `posts` (
--    `id` int(11) NOT NULL,
--    `title` varchar(255) NOT NULL,
--    `content` varchar(3000) DEFAULT NULL,
--    `auth_id` int(11) DEFAULT NULL,
--    `price` decimal(10,3) DEFAULT NULL,
--    `created_tile1` datetime DEFAULT current_timestamp(),
--    `created_tile2` datetime(6) DEFAULT current_timestamp(6),
--    `created_tile3` datetime DEFAULT current_timestamp(),
--    PRIMARY KEY (`id`),
--    KEY `post_auth_fk` (`auth_id`),
--    CONSTRAINT `post_auth_fk` FOREIGN KEY (`auth_id`) REFERENCES `author` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
--  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

primary key : unique + not null
오직 하나의 필드에만 설정 가능

foreign key : 외래키라고 부르며 한 테이블을 다른 테이블과 연결시켜주는 역할
다른 테이블의 필드는 반드시 unique나 primary key이어야 함

cascade : 참조되는 테이블에서 데이터를 삭제/수정 시 같이 삭제/수정
setnull : 참조되는 테이블에서 데이터를 삭제/수정 시 null로 변경
restrict : foreign key로 잡은 테이블의 데이터가 남아 있으면, foreign key 대상 데이터 삭제/수정 불가
동작 옵션을 설정하지 않으면 기본 restrict 이다.

---

default

alter table author add create_at datetime default current_timestamp;

시간 설정 시 가장 많이 사용

---

흐름제어

case value
when [compart_value1] then [result1]
when [compart_value1] then [result2]
else [result3]
end

-- case 예시
select id title, content, 
(case auth_id
when 1 then 'first_author'
when 2 then 'second_author'
else 'others'
end) as author_type
from posts

-- if 예시
if(a, b, c)
-- a = 조건
-- b = 참일 경우
-- c = 거짓일 경우
select if(0<1, 'yes', 'no');

-- ifnull 예시
ifnull (a, b)
select id title, content, ifnull(auth_id, 'anonymous') from posts;
-- 만약 a의 값이 null이 아니라면 a를 반환하고 null이면 b를 반환

---

트랜잭션

-- 트랜잭션 실습 : auto_commit 해제 후 실습
insert into author(name, email) values('test', 'test@naver,com');
insert into posts(id, title, content, auth_id) values('20','hello', 'hello is', 1);
select * from author;
select * from posts;

-- commit;
-- rollback;
select * from author;

-- 정리
insert into author(name, email) values('test', 'test@naver,com');
commit;
insert into author(name, email) values('test', 'test@naver,com');
insert into posts(id, title, content, auth_id) values('20','hello', 'hello is', 1);
rollback;

트랜잭션 : 논리적인 작업 단위로 처리되어야 하는 하나 이상의 SQL 문의 집합
commit : 트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 저장
rollback : 트랜잭션의 변경사항을 모두 취소하고, 데이터베이스를 트랜잭션 시작 이전의 상태로 되돌리는 것

---

DB 동시성 이슈

• dirty read
  수정 중인 데이터를 읽을 수 있는 문제
  해결책 : Read Committed 격리성

• Non-Repeatable Read
  동일한 조회 쿼리를 두 번 이상 실행할 때, 그 중간에 다른 트랜잭션에서 데이터를 수정하여 한 트랜잭션의 결과가 다르게 나타나는 문제
  해결책 : Repeatable Read 격리성

• Phantom Read
  한 트랜잭션이 같은 조회쿼리를 여러 번 실행했을 때, 그 중간에 다른 트랜잭션에서 새로운 데이터를 추가/삭제하여 다르게 나타나는 문제
  해결책 : DB마다 차이가 있어 해결하기 위해 Serializable 격리수준이 필요할수도 있음. 해결 전략을 생각해볼 것.

---

DB 격리수준

• Read Uncommitted
  데이터가 변경되었다면, 커밋되지 않았다 하더라도 읽을 수 있도록 하는 격리수준

• Read Committed
  다른 트랜잭션이 커밋된 데이터만 읽을 수 있는 격리수준.

• Repeatable Read
  한 번 읽은 데이터는 같은 트랜잭션 내에서는 항상 같은 값을 갖도록 하는 격리수준

• Serializable
  데이터베이스 차원에서 동시에 특정 데이터에 접근하는 것을 차단

---

github 데이터베이스 실습 내용