select 이용한 쿼리문
0. 기본 개념
SELECT는 테이블에서 데이터를 추출할 때 사용하는 것
id: 쿼리문 이름
parameterType: 입력 변수 타입, 예시: map, string, int...
retultType: 출력 변수 타입, 예시: 지정 변수(person), map, string...
SELECT {속성} FROM {테이블이름} WHERE {조건}
1. 사번으로 구성원 검색
<select id ="getPerson" parameterType="map" resultType="com.carrot.model.Person">
SELECT * FROM PERSONAL_INFO
WHERE _id = #{id}
</select>#{id}는 검색하려는 구성원의 사번
=> PERSONAL_INFO(구성원) 중 id(사번)이 일치하는 구성원의 모든 속성값 반환함
2. 이름 혹은 단어로 구성원 검색, CONCAT 사용하기
<select id="getPeopleList" parameterType="map" resultType="com.carrot.model.Person">
SELECT * FROM PERSONAL_INFO
WHERE name LIKE CONCAT('%',#{term},'%') AND _id != #{uId}
ORDER BY department LIMIT 10
</select>#{term}은 검색어, #{uId}는 현재 사용자의 id
CONCAT은 문자열을 이어주는 함수
=> PERSONAL_INFO(구성원) 중 name(이름)이 검색어를 포함하고 본인은 제외한 리스트의 모든 속성값을 반환함
이때 department(소속)을 기준으로 정렬하며 최대 10개까지 허용함
3. 모든 구성원의 id 반환
<select id="getAllIdList" resultType="string">
SELECT _id FROM PERSONAL_INFO
</select>조건 없는 select
=> PERSONAL_INFO(구성원)에서 모든 id(사번) 리스트를 반환함
4. 포인트 기록 개수 세기, COUNT 사용하기
<select id="countHistory" parameterType="map" resultType="int">
SELECT COUNT(*) FROM POINT_HISTORY
WHERE point_type = #{type} AND sender_id = #{uId} AND DATE_FORMAT(time_log, "%Y-%m-%d") = CURDATE();
</select>반환 변수 타입은 int(숫자)형
COUNT는 특정 조건을 만족하는 행의 갯수를 세는 함수
#{type}은 포인트의 종류를 나타냄
DATE_FORMAT은 날짜의 형식을 바꿔주는 함수
DATE_FORMAT(time_log, "%Y-%m-%d")은 time_log를 '년-월-일'의 형식으로 바꿔줌
CURDATE()는 현재 날짜를 반환하는 함수
=> POINT_HISTORY(포인트 기록)에서 time_log(로그 시간)이 현재 날짜에 해당하고 sender_id(사번)이 사용자의 사번과 일치하며 point_type(포인트 종류)가 일치하는 행의 갯수를 반환함
5. 랭킹 순위 가져오기, SUM + JOIN + GROUP 사용하기
<select id="getRankingList" parameterType="string" resultType="map">
SELECT PERSONAL_INFO.name, SUM(CARROT_HISTORY.carrot_value) as sum
FROM CARROT_HISTORY
JOIN PERSONAL_INFO ON CARROT_HISTORY.receiver_id = PERSONAL_INFO._id
WHERE year_month_log = #{yearMonth}
GROUP BY CARROT_HISTORY.receiver_id
ORDER BY SUM(carrot_value) DESC, name ASC LIMIT 10
</select>SUM은 특정 조건에 해당하는 각 리스트의 합을 구해주는 함수
as를 사용하면 쿼리 결과에 보이는 속성의 이름을 재정의할 수 있음
JOIN은 여러 테이블을 하나로 보여줄 때 사용됨
JOIN에는 LEFT, RIGHT, INNER 등 다양한 방식이 있지만, 기본 값은 INNER로 설정되어 있음
INNER JOIN은 공통된 부분만을 보여주며 값이 null인 것은 제외함
예시에서는 PERSONAL_INFO와 CARROT_HISTORY를 JOIN함
GROUP은 원하는 조건에 부합하는 값들을 그룹으로 만들어 보여주는 함수
=> CARROT_HISTORY(당근 내역)에서 name(구성원의 이름), SUM(carrot_value)(당근 합산 결과)를 선택함
이때 당근 내역에서 수신자 사번과 구성원 정보에 있는 사번이 일치하는 조건에서 PERSONAL_INFO를 JOIN함
이때 year_month_log(년월)이 일치해야 하고 receiver_id(수신자 사번)을 기준으로 그룹을 형성하여 receiver_id별 SUM을 구함
SUM은 내림차순, name(이름)은 오름차순으로 정렬하고 최대 10개까지 허용함
5. 포인트 기록 10개씩 불러오기
<select id="getPointHistory" parameterType="map" resultType="map">
SELECT point_value as p_val, point_type as p_type,
PERSONAL_INFO.name as p_name, DATE_FORMAT(time_log,'%Y-%m-%d %H:%i:%s') as time_log
FROM POINT_HISTORY p
LEFT JOIN PERSONAL_INFO ON p.receiver_id = PERSONAL_INFO._id
WHERE sender_id = #{uId}
ORDER BY time_log DESC LIMIT #{start_index}, #{count}
</select>FROM(A) LEFT JOIN(B)는 A에 B의 테이블을 붙이는 형식임
A의 값은 모두 나오고 이에 해당하는 B의 값이 없으면 null로 보여줌
#{uId}는 사용자 사번
#{start_index}는 검색 시작할 구간
#{count}는 검색할 개수
=> POINT_HISTORY(포인트 기록)에서 sender_id(전송자 사번)이 사용자 사번이랑 일치하는 것 중에서 point_value(값), point_type(종류), PERSONAL_INFO.name(수신자 이름), time_log(로그 시간)을 반환함
이때, time_log을 내림차순으로 정렬하고 시작점부터 원하는 갯수만큼 반환함
receiver_id(수신자 사번)과 PERSONAL_INFO._id(구성원 테이블의 사번)을 기준으로 POINT_HISTORY에 PERSONAL_INFO(구성원 정보)테이블을 LEFT JOIN 했기 때문에 PERSONAL_INFO.name를 바로 반환할 수 있음
