[31일 차] 데이터베이스 심화 (공통 코드)

---

25/03/11 (화)

예에 화요일입니다.

어제는 관장님께서 스파링을 해주셔서 많이 배웠습니다.

그리고 무엇보다 백준 골드가 눈앞입니다.

오늘 열심히 풀어서 골드를 보내버리도록 하겠습니다.

---

1.  WorkShop

1) 고립 수준 (Isolation Level)

	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(MySQL은 거의 없음)
SERIALIZABLE	없음	없음	없음

 

Read Uncommitted

• 커밋하지 않은 데이터 조차도 접근할 수 있는 격리 수준
• 다른 트랜잭션의 작업이 커밋 또는 롤백되지 않아도 즉시 보임
• 커밋 또는 롤백이 되지 않은 산태임에도 접근 가능
• Dirty Read 문제 발생

 

Read Committed

• 트랜잭션 수행이 완료되고 Commit된 데이터만 다른 트랜잭션에서 Read
• non - repeatable read 문제 발생

 

Read Committed (phantom read)

 

Reapetable Read

• non-repeatable read 문제 해결하는 격리 수준
• 커밋된 데이터만 읽을 수 있음

---

갑자기 경찰한테 전화가 와서 못들었습니다..

 

2.  강의

공통 코드

1) 대소문자 구분

-- 대소문자 테스트
create table bin_table (
    user_id varchar(5) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bi;
create table ci_table (
    user_id varchar(5) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into bin_table values ('abc');
insert into ci_table values ('abc');

select * from bin_table where user_id = 'ABC';  -- X 
select * from ci_table where user_id = 'ABC';  -- O
select * from bin_table where user_id = 'abc';  -- O 
select * from ci_table where user_id = 'abc';  -- O
-- 대소문자를 구분하지 않도록 만든 테이블 또는 스키마에서 대소문자를 구분해야 한다면??
-- ci_table ( 'aaa' )
select * from ci_table where user_id = 'ABC';  -- O
select * from ci_table where user_id = 'abc';  -- O
select * from ci_table where binary user_id = 'ABC';  -- X
select * from ci_table where binary user_id = 'abc';  -- O
select * from ci_table where binary( user_id ) = 'ABC';  -- X
select * from ci_table where binary( user_id ) = 'abc';  -- O
-- 조회조건 컬럼이 index 를 가진 경우 오른족에 함수를 사용하는 게 바람직
select * from ci_table where user_id = binary( 'ABC');  -- X
select * from ci_table where user_id = binary( 'abc');  -- O

 

2) 집계 처리

-- hr 스키마
-- 집계 처리 
-- 1. partition by
-- 전체 사원 리스트
select * from employees;
-- 전체 사원의 평균 급여
select avg(salary) from employees; -- 1 row ( 6461.682243 )
-- 부서별 평균 급여
select department_id, avg(salary) from employees group by department_id; -- null 포함 부서수만큼 row 
-- 전체 사원의 리스트를 뽑되, 컬럼은 employee_id, department_id, 부서별 평균 급여
-- #1 inline-view ( null )
select e.employee_id, e.department_id, das.avg_salary
  from employees e,
          ( select department_id, avg(salary) avg_salary from employees group by department_id ) das
 where e.department_id = das.department_id;
 
 -- #2 scalar
 select e.employee_id, e.department_id,
           ( select avg(ee.salary) from employees ee where ee.department_id = e.department_id) avg_salary
  from employees e;
 
 -- #3 partition by
 select employee_id, department_id, avg(salary) over ( partition by department_id ) avg_salary
   from employees;
-- partition by department_id, job_id   
 select employee_id, department_id, job_id, avg(salary) over ( partition by department_id, job_id ) avg_salary
   from employees
   order by department_id, job_id;
-- 2. group_concat()
-- row => column concat
select first_name from employees limit 5; -- 5개의 row 로 출력
-- comma 가 default 구분자
select group_concat(e.first_name) from (select first_name from employees limit 5) e; -- Steven,Neena,Lex,Alexander,Bruce
select group_concat(e.first_name separator '|') from (select first_name from employees limit 5) e; -- Steven|,Neena|,Lex|,Alexander|,Bruce|
-- 3. rollup
-- 소계
select job_id, sum(salary) from employees group by job_id;
select job_id, sum(salary) from employees group by job_id with rollup;
select department_id, job_id, sum(salary) from employees group by department_id, job_id;
select department_id, job_id, sum(salary) from employees group by department_id, job_id with rollup;

 

3) 공통 코드

-- 공통코드 테이블을 2개로 분리, 그룹(대분류)을 표현하는 테이블과 코드 및 코드값을 표현하는 테이블 2개로 나눈 방식
create table group_code(
    group_code char(3) not null,
    group_code_name varchar(50) not null,
    primary key(group_code)
);
create table code(
    group_code char(3) not null,
    code char(3) not null,
    code_name varchar(50) not null,
    use_yn char(1) null,
    primary key (group_code, code)
);

• 공통 코드는 비즈니스 로직에 의해서 우리가 머릿속으로 만들어 낸 것들이 주로 해당

---

-- 프론트에서 회원 구분 목록 요청 ( 회원가입 시에 라디오버튼과 함게 선택할 수 있도록 하기 위해서 )
-- 공통코드가 없다면 하드코딩 ( 업무적으로 합의 )
-- 공통코드를 활용하면
select * from group_code;  -- 001 확인
select * from code where group_code = '001';
select code, code_name from code where group_code = '001' and use_yn = 'Y';
-- 위 결과를 백엔드에서는 프론트에게 내려주면 (json) 프론트는 라디오버튼 등 화면 구성 보여준다. 
-- 회원은 선택하고 프론트는 선택된 값( 이름, 이메일 등 code = 010 과 함께 )을 전송하게 되고
-- 백엔드는 users 테이블에 insert 한다. 
-- insert into users ( user_id, user_name, email, user_clsf ) values ( 100, '백길동', 'back@gildong.com', '010');
insert into users ( user_id, user_name, email, user_clsf ) values ( 100, '백길동', 'back@gildong.com', '010');
-- 프론트에서 전체 회원 목록을 필요로 한다. 이 때 회원구분코드외에 회원구분코드명까지 함께 전달한다.
select user_id, user_name, email, user_clsf,
         (select code_name from code where group_code = '001' and code = user_clsf ) user_clsf_name from users;

 

2. 실습

2.1) 강사님 코드

오전 수업

-- 대소문자 테스트
create table bin_table (
    user_id varchar(5) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bi;
create table ci_table (
    user_id varchar(5) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into bin_table values ('abc');
insert into ci_table values ('abc');

select * from bin_table where user_id = 'ABC';  -- X 
select * from ci_table where user_id = 'ABC';  -- O
select * from bin_table where user_id = 'abc';  -- O 
select * from ci_table where user_id = 'abc';  -- O
-- 대소문자를 구분하지 않도록 만든 테이블 또는 스키마에서 대소문자를 구분해야 한다면??
-- ci_table ( 'aaa' )
select * from ci_table where user_id = 'ABC';  -- O
select * from ci_table where user_id = 'abc';  -- O
select * from ci_table where binary user_id = 'ABC';  -- X
select * from ci_table where binary user_id = 'abc';  -- O
select * from ci_table where binary( user_id ) = 'ABC';  -- X
select * from ci_table where binary( user_id ) = 'abc';  -- O
-- 조회조건 컬럼이 index 를 가진 경우 오른족에 함수를 사용하는 게 바람직
select * from ci_table where user_id = binary( 'ABC');  -- X
select * from ci_table where user_id = binary( 'abc');  -- O
---------------------------------------------------------------------------------------
-- hr 스키마
-- 집계 처리 
-- 1. partition by
-- 전체 사원 리스트
select * from employees;
-- 전체 사원의 평균 급여
select avg(salary) from employees; -- 1 row ( 6461.682243 )
-- 부서별 평균 급여
select department_id, avg(salary) from employees group by department_id; -- null 포함 부서수만큼 row 
-- 전체 사원의 리스트를 뽑되, 컬럼은 employee_id, department_id, 부서별 평균 급여
-- #1 inline-view ( null )
select e.employee_id, e.department_id, das.avg_salary
  from employees e,
          ( select department_id, avg(salary) avg_salary from employees group by department_id ) das
 where e.department_id = das.department_id;
 
 -- #2 scalar
 select e.employee_id, e.department_id,
           ( select avg(ee.salary) from employees ee where ee.department_id = e.department_id) avg_salary
  from employees e;
 
 -- #3 partition by
 select employee_id, department_id, avg(salary) over ( partition by department_id ) avg_salary
   from employees;
-- partition by department_id, job_id   
 select employee_id, department_id, job_id, avg(salary) over ( partition by department_id, job_id ) avg_salary
   from employees
   order by department_id, job_id;
-- 2. group_concat()
-- row => column concat
select first_name from employees limit 5; -- 5개의 row 로 출력
-- comma 가 default 구분자
select group_concat(e.first_name) from (select first_name from employees limit 5) e; -- Steven,Neena,Lex,Alexander,Bruce
select group_concat(e.first_name separator '|') from (select first_name from employees limit 5) e; -- Steven|,Neena|,Lex|,Alexander|,Bruce|
-- 3. rollup
-- 소계
select job_id, sum(salary) from employees group by job_id;
select job_id, sum(salary) from employees group by job_id with rollup;
select department_id, job_id, sum(salary) from employees group by department_id, job_id;
select department_id, job_id, sum(salary) from employees group by department_id, job_id with rollup;

 

오후 수업

-- 프론트에서 회원 구분 목록 요청 ( 회원가입 시에 라디오버튼과 함게 선택할 수 있도록 하기 위해서 )
-- 공통코드가 없다면 하드코딩 ( 업무적으로 합의 )
-- 공통코드를 활용하면
select * from group_code;  -- 001 확인
select * from code where group_code = '001';
select code, code_name from code where group_code = '001' and use_yn = 'Y';
-- 위 결과를 백엔드에서는 프론트에게 내려주면 (json) 프론트는 라디오버튼 등 화면 구성 보여준다. 
-- 회원은 선택하고 프론트는 선택된 값( 이름, 이메일 등 code = 010 과 함께 )을 전송하게 되고
-- 백엔드는 users 테이블에 insert 한다. 
-- insert into users ( user_id, user_name, email, user_clsf ) values ( 100, '백길동', 'back@gildong.com', '010');
insert into users ( user_id, user_name, email, user_clsf ) values ( 100, '백길동', 'back@gildong.com', '010');
-- 프론트에서 전체 회원 목록을 필요로 한다. 이 때 회원구분코드외에 회원구분코드명까지 함께 전달한다.
select user_id, user_name, email, user_clsf,
         (select code_name from code where group_code = '001' and code = user_clsf ) user_clsf_name from users;
----------------------------------------------------------------------------------------------------------
CREATE FUNCTION `fun_code` (
    p_group_code char(3),
    p_code char(3)
)
RETURNS varchar(45)
BEGIN
    declare r_code_name varchar(45);
    
    select code_name 
      into r_code_name
      from code 
    where group_code = p_group_code 
       and code = p_code;
RETURN r_code_name;
END
----------------------------------------------------------------------------------------------------------
-- 공통코드 테이블을 2개로 분리, 그룹(대분류) 을 표현하는 테이블과 코드ㅍ 및 코드값을 포현하는 테이블 2개로 나눈 방식
create table group_code( -- 회원구분, 회원상태, .......
    group_code char(3) not null,  -- 
    group_code_name varchar(50) not null,
    primary key(group_code)
);
create table code( -- 회원구분의 일반회원, 준회원, .......
    group_code char(3) not null,
    code char(3) not null,
    code_name varchar(50) not null,
    use_yn char(1) null,
    primary key (group_code, code)
);
insert into group_code values ( '001', '회원구분');
insert into group_code values ( '002', '회원상태');
insert into group_code values ( '003', '주문상태');
-- 회원구분 공통코드
insert into code values ('001', '010', '일반회원', 'Y');
insert into code values ('001', '020', '준회원', 'Y');
-- insert into code values ('001', '001', '일반회원', 'Y');
-- 회원상태 공통코드
insert into code values ('002', '010', 'VIP', 'Y');
insert into code values ('002', '020', 'GOLD', 'Y');

 

3. 마무리

3.1) WorkShop

• 조 단위 : 공통 코드 테이블  2개 이상의 방법으로 구성
• 개인 : 개인 프로젝트에 공통 코드 적용하기

 

3.2) 더 공부할 것

• LinkedList
• 관리자는 관리자용 어플리케이션을 만들어라