데이터 처리 - ALIAS, JOIN, 내장함수, 페이징 처리

|  ALIAS와 * (애스터리스크)

- ALIAS : 별명, 별칭

- * : 전체

select
    m.id as 회원아이디,
    m.password as 비밀번호,
    m.name as 회원명
from member as m;

 

|  JOIN문

- JOIN만 적히는 경우 INNER JOIN이 사용된다.

INNER JOIN		키 값 기준 데이터 결합
LEFT JOIN		키 값 기준 데이터 결합 + 좌측의 나머지
RIGHT JOIN		키 값 기준 데이터 결합 + 우측의 나머지
FULL JOIN		LEFT데이터 X  RIGHT데이터

- 수업에서 제공되었던 테이블은, MEMBER와 MEMBER_DETAIL 이었는데,

  이와 같이 회원 정보를 간편 정보 / 상세 정보로 나누어서 필요에 따라 테이블을 조인하는 식으로 사용한다고 한다.

-- 회원정보 > 로그인 정보
create table member
(
   member_type varchar(10) not null comment '회원구분',
   user_id     varchar(50) not null comment '회원 아이디',
   password    varchar(50) null     comment '비밀번호',
   name        varchar(20) null     comment '이름',
   primary key (member_type, user_id)
) comment '회원정보';

-- 회원상세정보 > 로그인에 필요하지 않은 이외 정보
create table member_detail
(
   member_type   varchar(10)                          not null comment '회원구분',
   user_id       varchar(50)                          not null comment '회원 아이디',
   mobile_no     varchar(12)                          null     comment '휴대폰 번호',
   marketing_yn  bit                                  null     comment '마케팅 수신 여부',
   register_date datetime default current_timestamp() null comment '가입일',
   primary key (member_type, user_id),
   constraint fk_member_detail foreign key (member_type, user_id) references member (member_type, user_id)
) comment '회원상세정보';

- JOIN 코드

-- 조인 (INNER JOIN)
-- ** 단순히 select * from.. 을 쓰면 어떤 DBMS에서는 오류가 날 수 있다.
select
    m.*,
    md.*
from member as m
    join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

-- 중복된 컬럼 제외
select
    m.member_type, m.user_id, m.password, m.name,
    md.mobile_no, md.marketing_yn, md.register_date
from member as m
    join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

-- LEFT JOIN : member(left)를 기준으로 member_detail(right)와 키값이 동일한 데이터를 먼저 솎아내고, 그 후에 member의 나머지를 추가
select
    m.member_type, m.user_id, m.password, m.name,
    md.mobile_no, md.marketing_yn, md.register_date
from member as m
    left join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

-- RIGHT JOIN : RIGHT를 기준으로
select
    m.member_type, m.user_id, m.password, m.name,
    md.mobile_no, md.marketing_yn, md.register_date
from member as m
    right join member_detail as md
        on m.member_type = md.member_type and m.user_id = md.user_id
;

-- FULL JOIN : LEFT x RIGHT
select
    m.member_type, m.user_id, m.password, m.name,
    md.mobile_no, md.marketing_yn, md.register_date
from member as m
    join member_detail as md
;

 

|  내장함수

- 각 DBMS별로 내장되어 있는 함수들이 있다.

- 구분

입력값의 갯수에 따라	- 단일행 함수 : 함수의 입력값이 단일행 값일 때 - 다중행 함수 : 함수의 입력값이 다중행 값일 때 (ex. 집계 함수, 그룹 함수)
데이터 타입에 따라	- 문자형 함수 - 숫자형 함수 - 날짜형 함수 - 변환형 함수 : 데이터 타입 변환 - NULL 관련 함수

- SQL 조건문 : CASE ~ END 

-- SQL 조건문 : 자바의 switch case와 동일
CASE 
  WHEN 조건 THEN 실행
  ELSE 실행
END

- 수업에서 다루었던 코드

-- A. 문자형
-- (1) 비밀번호 * 처리 (substring, length, concat)
-- 오라클의 경우, 문자열 결합 시 : concat(문자열, 결합할 문자) 또는 문자열 || 결합할문자
SELECT
    member_type,
    user_id,
    password,
    name,
    -- concat(substring(password, 1, 2), '**') as password_mask, -- idx가 1부터 시작
    -- length(password) as password_length,
    case
      when length(password) > 2 then concat(substring(password, 1, 2), '**')
      else ''
    end as password_mask
FROM member;

-- B. 데이터 포맷 변환
SELECT register_date,
       date_format(register_date, '%Y.%m.%d') as dt_format
FROM member_detail;

SELECT
    '20220321',
    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;

-- 현재 날짜를 통해 월초와 월말을 구하기
SELECT
    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;

- 보다 자세한 내장 함수 코드 

https://2030bigdata.tistory.com/219

[개미의 걸음 SQLD 2과목] SQL내장함수① 단일행(문자열, 숫자형, 날짜형, 형변환, NULL)함수

 

|  페이징 처리

- 다수의 게시글(또는 카드 등)을 특정 위치에서 특정 갯수만큼 보여주는 것

MySQL, MariaDB	LIMIT를 통해 페이징 처리
Oracle	ROWNUM을 통해 페이징 처리
MSSQL	OFFSET, FETCH를 통해 페이징 처리

📡 MySQL/MariaDB

--!! 주의사항 !! 특히하게 LIMIT은 idx를 0부터 시작한다.

-- 전체 data에 대해 첫번째부터 10개만 출력
SELECT *
FROM member
LIMIT 0, 10; 

-- 나열된 data에 대해 10번째부터 10개만 출력
select
    c.code, c.company_name, c.eng_company_name, c.category,
    row_number() over (order by c.code desc) as row_index
from company c
order by c.code desc
limit 10, 10
;

-- 범위를 지정하여 출력
select *
from
(
    select *
    from
    (
        select
        c.code, c.company_name, c.eng_company_name, c.category,
        row_number() over (order by c.code desc) as row_index
        from company c
        order by c.code desc
    ) t1
    where row_index <= 30
) t2
where row_index > 20;

📡 ORACLE

-- 기본 구문
--- '*'을 사용하면 데이터가 깨진다.
--- 테이블.* 또는 칼럼명을 하나씩 작성 필요.
SELECT
  칼럼1,
  칼럼2,
  ROWNUM as rnum
FROM member
ORDER BY 순서기준

-- 실제 사용
SELECT *
FROM (
		SELECT 
          a.*, ROWNUM as rnum 
        FROM (
                SELECT 
                  * FROM member
                ORDER BY member_no
             ) a
     ) 
WHERE rnum >= 1 AND rnum <= 10;

 

 

[ 참조 및 출처 ]

부트캠프 강의를 들은 후 정리한 내용입니다.

https://programmer93.tistory.com/4

https://m.blog.naver.com/wideeyed/221796538283