(0723) 라이징캠프 3주차 내용 정리

게시물 수, 팔로워 수 이런 건 칼럼으로 넣지 않는다. 쿼리문으로 검색해서 함.

QuickDBD 사용 문법

User
--
userIdx PK int IDENTITY
name varchar(30)
email varchar(50)
profileImgUrl text null
backGroundImgUrl text null
status varchar(10) default='ACTIVE'
createdAt timestamp default=CURRENT_TIMESTAMP
updatedAt timestamp default=CURRENT_TIMESTAMP

Room
--
roomIdx pk int identity
name varchar(20)
imgUrl text null
status varchar(10) default='ACTIVE'
createdAt timestamp default=CURRENT_TIMESTAMP
updatedAt timestamp default=CURRENT_TIMESTAMP

chat
--
chatIdx pk int identity
roomIdx int FK >- Room.roomIdx
message text
status varchar(10) default='ACTIVE'
createdAt timestamp default=CURRENT_TIMESTAMP
updatedAt timestamp default=CURRENT_TIMESTAMP

Member
--
memberIdx pk int identity
roomIdx int
userIdx int
status varchar(10) default='ACTIVE'
createdAt timestamp default=CURRENT_TIMESTAMP
updatedAt timestamp default=CURRENT_TIMESTAMP

QuickDBD 사용해서 간단하게 연습 (링크)

https://app.quickdatabasediagrams.com/#/d/hb9mmp

카카오톡 SQL문 예시

select * from Room
select roomIdx from Member where userIdx=1;

#서브쿼리
select roomIdx, imgUrl, name from Room
where roomIdx in (select roomIdx from Member where userIdx=1);

#join
select Room.roomIdx, imgUrl, name from Room inner join Member on Room.roomIdx=Member.roomIdx
where Member.userIdx=1;

select roomIdx, max(chatIdx) from Chat group by roomIdx;

select Chat.roomIdx, Chat.chatIdx, Chat.message as lastMessage, Chat.createdAt as lastMessageTime, Chat.type as lastMessageType from Chat
inner join (select roomIdx, max(chatIdx) as maxChatIdx from Chat group by roomIdx) lastMessage
where maxChatIdx=Chat.chatIdx;

select Room.roomIdx, imgUrl as roomImgUrl, name as roomName, forLastMessage.chatIdx, forLastMessage.lastMessage, forLastMessage.lastMessageTime, forLastMessage.lastMessageType from Room
inner join Member M on Room.roomIdx = M.roomIdx
inner join (select Chat.roomIdx, Chat.chatIdx, Chat.message as lastMessage, Chat.createdAt as lastMessageTime, Chat.type as lastMessageType from Chat
inner join (select roomIdx, max(chatIdx) as maxChatIdx from Chat group by roomIdx) lastMessage
where maxChatIdx=Chat.chatIdx) as forLastMessage on Room.roomIdx=forLastMessage.roomIdx
where M.userIdx=1 group by roomIdx;

select TIMESTAMPDIFF (HOUR, '2021-09-24 04:44:17', current_timestamp);

select Room.roomIdx, imgUrl as roomImgUrl, name as roomName, forLastMessage.chatIdx, forLastMessage.lastMessage, 
case when TIMESTAMPDIFF (HOUR, '2021-09-24 04:44:17', forLastMessage.lastMessageTime, current_timestamp) > 24 then '어제'
else 
     DATE_FORMAT(forLastMessage.lastMessageTime, '%p %H : %i') end as lastMessageTime, forLastMessage.lastMessageType from Room
inner join Member M on Room.roomIdx = M.roomIdx
inner join (select Chat.roomIdx, Chat.chatIdx, Chat.message as lastMessage, Chat.createdAt as lastMessageTime, Chat.type as lastMessageType from Chat
inner join (select roomIdx, max(chatIdx) as maxChatIdx from Chat group by roomIdx) lastMessage
where maxChatIdx=Chat.chatIdx) as forLastMessage on Room.roomIdx=forLastMessage.roomIdx
where M.userIdx=1 group by roomIdx;