SQL 문법 정리

JOIN

member2 테이블

member_datail 테이블

1) INNER JOIN

• 교집합 기준으로 left, right 테이블 데이터들을 가져온다.

select m.member_type, m.user_id, m.password, m.name
    , md.member_type, md.marketing_yn, md.register_date
from member2 as m
    join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

2) LEFT JOIN

• member2를 기준으로 member_detail의 데이터들을 join한다.

select m.member_type, m.user_id, m.password, m.name
    , md.member_type, md.marketing_yn, md.register_date
from member2 as m
    left join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

3) RIGHT JOIN

• member_detail을 기준으로 member2의 데이터들을 join하므로
  select * from member2와 같은 출력 형태가 나온다.

select m.member_type, m.user_id, m.password, m.name
    , md.member_type, md.marketing_yn, md.register_date
from member2 as m
    right join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

4) FULL JOIN

• member2의 데이터 5개, member_datail의 데이터 3개를 곱해 5 * 3 = 15개의 데이터가 출력 된다.
• join에 조건을 쓰지 않는다.

select m.member_type, m.user_id, m.password, m.name
    , md.member_type, md.marketing_yn, md.register_date
from member2 as m
    join member_detail as md
;

SWITCH문

💡 switch 문법

CASE로 시작해서 END로 닫는다.

CASE
	WHEN 조건1 THEN 결과값1
    WHEN 조건1 THEN 결과값1
    WHEN 조건1 THEN 결과값1
    ELSE 그 외 결과값
END

비밀번호 앞자리 2개 표기하고 나머지 *처리

SELECT member_type, user_id, password, name
    ,
    case
        when length(password) > 2 then concat(substring(password, 1, 2), '**')
        else ''
    end as password_length
from member2;

GROUP_CONCAT()

• 항목이 여러개인 경우 콤마(,)를 이용해서 한 컬럼에 표시
  ex)

select a.*
    , (
        select group_concat(title)
        from movie m
        where country = '한국'
            and open_flag = '개봉'
            and m.director = a.name
    ) as movie_title

• https://mentha2.tistory.com/182
• https://needjarvis.tistory.com/746

DBMS 내장함수

📌 단일행 함수

• 조회된 행마다 하나의 결과를 반환한다.
• 단일행 함수는 중첩해서 사용할 수 있다.
• 함수의 종류로 문자함수, 숫자함수, 날짜함수, 변환함수, 기타함수가 있다.

📌 다중행 함수 (그룹 함수)

• 조회된 행을 그룹으로 묶고, 행 그룹 당 하나의 결과를 반환한다.
• group by 절을 사용해서 조회된 행을 그룹으로 묶고, 다중행 함수로 각 그룹 당 하나의 결과(합계, 평균, 분산, 표준편차, 최고값, 최저값 등)을 계산해낸다.
• 다중행 함수의 중첩은 select절에서만 허용되고, 한번만으로 중첩 횟수가 제한된다.
• 집계함수(agrregate function)라고도 한다

데이터 표시 포맷 (날짜 -> 문자열, 문자열 -> 날짜)

날짜 -> 문자열 변환 : date_format

select register_date
    ,date_format(register_date, '%Y.%m.%d') as dt_format
from member_detail;

문자열 -> 날짜 변환 : str_to_date

select '20220321' as dt_string
    , str_to_date('20220321', '%Y%m%d') as dt_date
from dual;

날짜연산 : date_add(date, interval 1 month)

select '20220321' as dt_string
    , str_to_date('20220321', '%Y%m%d') as dt_date
    , date_add(str_to_date('20220321', '%Y%m%d'), interval 1 month) as dt_date2
from dual;

ex) 현재 월초와 월말 구하기

select now()
    , date_format(now(), '%Y-%m-01') as start_date
    , date_add(date_add(str_to_date(date_format(now(), '%Y-%m-01'), '%Y-%m-%d'), interval 1 month), interval -1 day) as end_date
from dual;

페이징 처리

select *
from member2
-- limit 0, 2
limit 2, 2 -- index 2위치(3번째)부터 2개 가져옴
;

VEIW 테이블

• 실질적으로 존재하는 테이블이 아니고 정의만 가지고 이 ㅆ음
• 가상 read only 테이블
• select만 가능
• 문법

create view 뷰테이블명
	쿼리 작성
;

drop view 뷰테이블명;

ex)

create view v_member_marketing_yes as

select m.member_type, m.user_id, m.name
    , md.mobile_no, md.marketing_yn, md.register_date
from member2 m
    join member_detail md on md.member_type = m.member_type and md.user_id = m.user_id
where marketing_yn = true;
;

함수

함수 생성

ex)

create function sf_password(password varchar(255))
    returns varchar(255)
begin
    return '****';

end;

함수 삭제

drop function 함수명;`

함수 사용

select password, sf_password(password) as password_mask
from member2;

프로시저

프로시저 생성

create procedure sp_select_member()
begin

    select *
        from member2;

end;

프로시저 삭제

drop procedure sp_select_member;

프로시저 사용

call sp_select_member();

delimiter

중간 문장의 ;에 의해 끝을 잘못 인식하는 경우가 있어 delimiter를 사용해 전체 쿼리의 시작과 끝을 알려준다.
DataGrip과 같은 훌륭한 툴들은 사용할 필요 없다.

delimiter $$

create procedure sp_select_member()
begin

    select *
    from member_detail;

    select *
        from member2;

end $$

delimiter ;

트리거

• 비밀번호 변경 전 데이터들을 따로 테이블을 만들어서 저장

트리거 생성

• for-each문, old키워드 사용
• member_detail 테이블에 트리거 항목으로 트리거 생성
  ex )

create trigger tg_member_mobile_no_history
    before update on member_detail   -- member_detail이 update되기 이전에
    for each row
    begin

        insert into member_datail_history
            (
                 member_type,
                 user_id,
                 mobile_no,
                 update_date
            )
            values
            (
                old.member_type,
                old.user_id,
                old.mobile_no,
                now()
            );

    end;

member_detail 테이블 업데이트 후 member_detail_history 테이블 항목

어떤 번호가 어떤 번호로 변경되었는지

• new_mobile_no 항목 생성

alter table member_datail_history add new_mobile_no varchar(50) after mobile_no;

• 트리거 드롭 후 다시 생성
  insert에 new_mobile_no, values에 new.mobile_no 추가

create trigger tg_member_mobile_no_history
    before update on member_detail   -- member_detail이 update되기 이전에
    for each row
    begin

        insert into member_datail_history
            (
                 member_type,
                 user_id,
                 mobile_no,
                 new_mobile_no,
                 update_date
            )
            values
            (
                old.member_type,
                old.user_id,
                old.mobile_no,
                new.mobile_no,
                now()
            );

    end;

• 업데이트 실행
  

데이터 그립 ERD 보는 법

• https://code-algo.tistory.com/18?category=950591

참고 URL

• https://rachel921.tistory.com/25#:~:text=%EB%82%B4%EC%9E%A5%ED%95%A8%EC%88%98%EB%9E%80%20DBMS%EC%97%90%EC%84%9C,%EB%A7%8C%20%ED%95%98%EB%A9%B4%20%EB%90%9C%EB%8B%A4%EB%8A%94%20%EC%9D%B4%EC%95%BC%EA%B8%B0%20%EC%9E%85%EB%8B%88%EB%8B%A4.
• https://yoo-hyeok.tistory.com/
• https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EB%82%B4%EC%9E%A5%ED%95%A8%EC%88%98-%EC%A0%95%EB%A6%AC#%EC%A7%91%EA%B3%84%ED%95%A8%EC%88%98_%EC%A1%B0%EA%B1%B4_having
• https://velog.io/@yoondgu/%EC%98%A4%EB%9D%BC%ED%81%B4%EC%9D%98-%EB%82%B4%EC%9E%A5%ED%95%A8%EC%88%98