Database 데이터 타입과 제한 조건

데이터 타입

• 데이터 타입이 필요한 이유는 추후에 연산 작업을 하기 위함
• UNSIGNED 타입을 사용하여 양수만 표현이 가능하여 표현값 2배로 증가
• text 타입은 disk에 저장되고 varchar는 메모리에 저장되고, index 사용이 어려움 → 메모리 기반은 빠르다 영구히 저장되진 않지만 디스크에 백업하는 등 의 동작을 통해서 반영구적으로 보관 → 디스크 느리다 메모리에 비해 성능이 떨어짐
• blob 타입

<image db 저장 방법>

1. blob 타입으로 바이너리 데이터 저장
2. disk에 저장 후 파일 경로를 db에 저장

→ EC2(컴퓨터-디스크 내장), S3(별도의 저장소)

-- tinyint는 -128~127까지 표현
-- author 테이블에 age 컬럼 추가
alter table author add column age tinyint;

insert into author(id, email, age) values(5, 'hello@naver.com',125);

alter table author modify column age tinyint unsigned;
insert into author(id, email, age) values(6, 'hello@naver.com',200);

-- decimal 실습
-- 총 10개의 자릿수에 3자리가 소수부
alter table post add column price decimal(10,3);

-- decimal 소수점 초과 값 입력 후 짤림 확인
insert into post(id,title,price) values(7,'hello',3.12312);

-- update : price 1234.1
update post set price=1234.1 where id=7;

-- blob 바이너리데이터 실습
-- author 테이블에 profile_image 컬럼을 blob형식으로 추가
alter table author add column profile_image blob;
insert into author(id, email, profile_image) values(6, 'eunji@naver.com', LOAD_FILE('이미지 경로'));

-- enum : 삽입될 수 있는 데이터 종류를 한정하는 데이터 타입
-- role 컬럼
alter table author add column role enum('user', 'admin') not null;
alter table author modify column role enum('admin', 'user') not null default 'user';

-- date 타입
-- author 테이블에 birth_day 컬럼을 date로 추가
-- 날짜 타입의 insert는 문자열 형식으로 insert
alter table author add column birth_day date;
insert into author(id, email, birth_day) values(10, 'eunji@naver.com', '2001-06-19');

-- datetime 타입
-- author, post 둘다 create_time 컬럼을 datetime으로 추가
alter table author add column created_time datetime;
alter table post add column created_time datetime;

insert into author(id, email, created_time) values(13, 'eunji@naver.com', '2024-05-17 12:00:00');
insert into post (id, title , created_time) values(6, '제목', '2024-05-17 12:00:00');

-- 자동으로 현재 시간 추가
alter table author modify column created_time datetime default current_timestamp;

-- 비교연산자
-- and 또는 &&
select * from post where id >= 2 and id <= 4;
select * from post where id between 2 and 4;
-- or 또는 ||
-- not 또는 !
select * from post where not(id < 2 or id > 4);

-- NULL인지 아닌지
select * from post where contents is null;

-- in(리스트 형태), not in(리스트 형태)
select * from post where id in (1,2,3,4);
select * from post where id not in (1,2,3,4);

-- like : 특정 문자를 포함하는 데이터를 조회하기 위해 사용하는 키워드
select * from post where title like '%o' #o로 끝나는 title 검색
select * from post where title like 'h%' #h로 시작하는 title 검색
select * from post where title like '%l%' #단어의 중간에 l라는 키워드가 있는 경우 검색
select * from post where title not like '%o' #o로 끝나는 title이 아닌 것을 검색

-- ifnull(a,b) : 만약에 a가 null이면 b 반환, null이 아니면 a 반환
select title, contents, author_id from post;

-- REGEXP : 정규표현식을 활용한 조회
select * from author where name regexp '[a-z]';
select * from author where name regexp '[가-힣]';

-- 날짜 변환 : 숫자 -> 날짜, 문자 -> 날짜
-- CAST와 CONVERT
select CAST(20200101 AS DATE);
select CAST('20200101' AS DATE);
select CONVERT(20200101 , DATE);
select CONVERT('20200101' , DATE);

-- datetime 타입 조회 방법
select * from post where created_time like '2024-05%';
select * from post where created_time <= '2024-12-31' and created_time>='1999-01-01';
select * from post where created_time between '1999-01-01' and '2024-12-31';

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

-- 오늘 날짜 출력
select now();

제약조건(constraint)

restrict → 연관된 post가 있는 경우 못지움

cascade→ author 지울시 연관된 post 글까지 지워버림

set null → author 지울 시 연관된 post의 author_id 가 null 로 바뀜

-- not null 조건 추가
alter table 테이블명 modify column 컬럼명 타입 not null;

-- auto_increment
alter table author modify column id bigint auto_increment;

-- author.id 제약조건 추가시 fk로 인해 문제 발생
-- ->fk 먼저 제거 이후에 author.id에 제약 조건 추가
select * from information_schema.key_column_usage where table_name = 'post';
alter table post drop foreign key post_ibfk_1;

-- 삭제된 제약 조건 다시 추가
alter table post add constraint post_ibfk_1 foreign key(author_id) references author;

-- uuid : 특정 항목을 전 세계적으로 유일하게 식별
alter table post add column user_id char(36) default (UUID());

-- unique 제약 조건 -> index가 생성됨
alter table author modify column email varchar(255) unique;
show index from author;

-- on delete cascade 테스트 -> 부모 테이블의 id를 수정하면 수정안됨
select * from information_schema.key_column_usage where table_name = 'post';
alter table post drop foreign key post_ibfk_1;
alter table post add constraint post_ibfk_1 foreign key(author_id) references author(id) on delete cascade;

-- on update cascade
alter table post add constraint post_ibfk_1 foreign key(author_id) references author(id) on delete cascade on UPDATE cascade;

-- delete는 set null, update는 cascade로 변경
alter table post add constraint post_ibfk_1 foreign key(author_id) references author(id) on delete set null on update cascade;