[MySQL] 화면 기획서 보고 테이블 만들어보기

📝MySQL Workbench 화면 기획서 보고 테이블 만들어보기

1. 회원가입 화면에는 이메일과 비밀번호, 이름을 입력받아 회원가입
2. 내 일정목록 화면에는 일정내용, 완료할기한, 완료여부를 표시
+ 할일은 최대 100자 입력 가능, 완료 여부는 체크박스 표시
+ 일정은 25개씩 가장 먼 미래의 일정부터 순서대로 표시
3. 친구들의 일정목록 화면에는 친구 추가하면 친구들의 일정이 현재시간 기준으로 지나지 않은 일정,
현재시간으로 지난(이전) 일정목록을 가져온다

 

화면 기획서를 보고 테이블 생성 > 테스트 데이터 인서트 > 화면에 필요한 SQL문 작성 (클라이언트에게 전달할 데이터 가공)
순서 : 단일 테이블 처리 가능한 것부터 작성하고 메인 화면처럼 여러 테이블 조인하는 것은 맨 나중에 작성한다

회원가입 화면에서 받아온 정보를 저장할 user 테이블 생성

기본키 id, 이메일, 비밀번호, 이름, 가입한 날짜를 저장할 컬럼 생성

(가입한 날짜는 Timestamp로 디폴트값 now() 설정)

일정등록에서 받아온 정보를 저장할 schedule 테이블 생성

기본키 id, 일정등록한 유저아이디(Foreign Key), 일정, 일정날짜, 완료여부, 등록한 날짜를 저장할 컬럼 생성

(등록한 날짜는 Timestamp로 디폴트값 now() 설정)

친구목록(친구추가)에서 받아온 정보를 저장할 follow 테이블 생성

팔로우한사람(Foreign Key), 팔로우받은사람(Foreign Key), 팔로우한 날짜를 저장할 컬럼 생성

(등록한 날짜는 Timestamp로 디폴트값 now() 설정)

 

테스트/활용

-- 회원가입 작성 SQL TEST
insert into user(email, password, nickname)
values('abc@navr.com','abcd1234','홍길동');

select * from user;

-- 일정 작성하는 SQL TEST
insert into schedule(user_id, content, date)
values(101, '내일 점심에 스벅 커피 한잔', '2022-12-10 13:25');

insert into schedule(user_id, content, date)
values(101, '일요일에 맛있는 저녁', '2022-12-12 18:30');

select * from schedule
order by created_at desc;

-- 일정 가져오는 SQL (유저 아이디 1번 가져오기)
select *
from schedule
where user_id = 1
order by date desc
limit 0, 25;

-- 일정완료 버튼을 누르면 일정 완료시키는 SQL
update schedule
set is_completed = 1
where id = 1002;

select * from schedule
order by created_at desc;

-- 친구들 일정을 가져오는 SQL(내 아이디는 1이라고 가정)
select s.user_id, u.nickname, s.content, s.date, s.is_completed
from follow f
join schedule s on s.user_id = f.followee_id
join user u on u.id = s.user_id
where follower_id = 1 and s.date > now()
order by s.date desc
limit 0, 25;