인천 일보 아카데미 30일차 / 문자 합치기(CONCAT)_자르기 (LEFT , RIGHT , SUBSTR), CASE_END 사용 , View / Index , commit / rollback

• 만들어보기

 

 

• 만들어보기 준비물 _TABLE

각 필요한 입력 정보를 INSERT 했습니다

 

 

• 그냥 한번 만들어보기

-- 1.1 후보자 , 정당 테이블 조인
SELECT M.*,P.P_NAME,P.P_TEL1,P.P_TEL2,P.P_TEL3
		FROM TBL_MEMBER_202005 M , TBL_PARTY_202005 P 
			WHERE M.P_CODE = P.P_CODE;
            
-- 1.2 필요한 정보만 조회(컬럼이름 한글로)
SELECT M.M_NO AS '후보번호', M.M_NAME AS '성명',P.P_NAME as '소속정당'
		,M.P_SCHOOL AS '학력', M.M_JUMIN AS '주민번호'  
			,M.M_CITY AS'지역구',P.P_TEL1,P.P_TEL2 ,P.P_TEL3
				FROM TBL_MEMBER_202005 M , TBL_PARTY_202005 P 
					WHERE M.P_CODE = P.P_CODE;

 

 

• 학력 표현해보기 (CASE , END 사용해보기)

SELECT P_SCHOOL,
	CASE
		WHEN P_SCHOOL ='1' THEN '고졸'
       	 	P_SCHOOL이 1이면 고졸
		WHEN P_SCHOOL ='2' THEN '학사'
        	P_SCHOOL이 2이면 학사
        	WHEN P_SCHOOL ='3' THEN '석사'
            	P_SCHOOL이 3이면 석사
        	WHEN P_SCHOOL ='4' THEN '박사'
            	P_SCHOOL이 4이면 박사
        ELSE '없음'
        아니면 없음
	END AS '학력'
    이름을 학력으로 변경
    FROM TBL_MEMBER_202005;

 

 

 

• 주민번호 표현해보기(CONCAT , SUBSTR , LEFT , RIGHT 사용)

-- LEFT , RIGHT 사용
SELECT CONCAT(LEFT(M_JUMIN , 6),'-',RIGHT(M_JUMIN ,7))AS '주민번호' 	
		FROM TBL_MEMBER_202005;
        
-- 앞 6자리 (SUBSTR 사용)
SELECT substr(M_JUMIN, 1, 6) FROM TBL_MEMBER_202005;
-- 뒤 6자리 (SUBSTR 사용)
SELECT substr(M_JUMIN,7,7) FROM TBL_MEMBER_202005;

SELECT CONCAT(SUBSTR(M_JUMIN,1,6),'-',SUBSTR(M_JUMIN,7,7))AS '주민번호' 
		FROM TBL_MEMBER_202005;

LEFT , RIGHT 와 SUBSTR은 서로 장단점이 있는데

 

LEFT , RIGHT는 좀 더 사용하기 간단하다 하지만 왼쪽 , 오른쪽의 시작점이 정해져 있다는 단점이있다

 

SUBSTR은 조건을 2가지 설정해야하지만 자르기위한 시작점을 정할 수 있다는 장점이 있다

 

 

• 대표전화 (CONCAT 합치기)

-- 1.5 대표전화
SELECT CONCAT(P_TEL1 ,'-', P_TEL2 ,'-', P_TEL3)AS'대표전화' 
		FROM TBL_PARTY_202005;
        
간단하게 컬럼 P_TEL1  , P_TEL2 , P_TEL3 의 사이에 
' - ' 넣어 하나로 합쳐 출력했다

 

 

• 만들어보기 완성!

-- 1.6 완성
SELECT M.M_NO AS '후보번호', M.M_NAME AS '성명',P.P_NAME as '소속정당'
		,CASE
		WHEN P_SCHOOL ='1' THEN '고졸'
		WHEN P_SCHOOL ='2' THEN '학사'
        WHEN P_SCHOOL ='3' THEN '석사'
        WHEN P_SCHOOL ='4' THEN '박사'
        ELSE '없음'
	END AS '학력',CONCAT(LEFT(M_JUMIN , 6),'-',RIGHT(M_JUMIN ,7))AS '주민번호'  
			,M.M_CITY AS'지역구',CONCAT(P_TEL1 ,'-', P_TEL2 ,'-', P_TEL3)AS'대표전화'
				FROM TBL_MEMBER_202005 M , TBL_PARTY_202005 P 
					WHERE M.P_CODE = P.P_CODE;

 

 

• 풀어보기_심화

해당 결과 값을 출력하는 출력문 만들기

SELECT V_NAME AS '성명',CASE 
-- 생년월일의 앞숫자를 붙여주는 CASE
	WHEN SUBSTR(V_JUMIN,7,1) IN ('1','2') 
    		THEN CONCAT('19',LEFT(V_JUMIN,2),'년',SUBSTR(V_JUMIN,3,2),'월',SUBSTR(V_JUMIN,5,2),'일')
	WHEN SUBSTR(V_JUMIN,7,1) IN ('3','4') 
    		THEN CONCAT('20',LEFT(V_JUMIN,2),'년',SUBSTR(V_JUMIN,3,2),'월',SUBSTR(V_JUMIN,5,2),'일')
    	ELSE '없음'
	END AS '생년월일'
-- 만 나이를 구하는 CASE
	,concat(cast(date_format(sysdate(), '%Y') as unsigned) - concat(case 
	when substr(v_jumin, 7, 1) in('1', '2') then '19'
	when substr(v_jumin, 7, 1) in('3', '4') then '20'
	end,
	substr(v_jumin, 1, 2)),'세') as '나이'
-- 성별을 구하는 CASE
	,CASE
	WHEN SUBSTR(V_JUMIN,7,1) IN ('1','3') THEN '남'
	WHEN SUBSTR(V_JUMIN,7,1) IN ('2','4') THEN '여'
	ELSE '없음'
	END AS '성별'
    	,M_NO AS '후보번호' , CONCAT(LEFT(V_TIME,2),':',RIGHT(V_TIME,2)) AS '투표시간'
-- 유권자 확인을 구하는 CASE
	,CASE
	WHEN V_COMFIRM = 'Y' THEN '확인'
	WHEN V_COMFIRM = 'N' THEN '미확인'
	ELSE '없음'
	END AS '유권자확인'
FROM TBL_VOTE_202005;

설명 불가 읽어보자

 

 

• View / Index

View : 데이터베이스에 존재하는 일종의 가상 테이블로, 실제 행과 열을 가지고 있지만 데이터를 저장하고 있지는 않다.

              뷰에서는 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만을 수행한다.

              (쿼리가 많아졌는데 찾기힘들다면 View로 저장되어 있는 쿼리는 쉽게 조회가 가능하다)

문법

뷰는 원본 테이블과 같은 이름을 가질 수 없다.
테이블과 비슷한 이름의 뷰를 생성하는 것이 좋다.
create VIEW VOTE_RESULT AS



가상으로 만들고 싶은 쿼리문




SELECT * FROM VOTE_RESULT;

Index : 핵심 키워드 : 검색속도가 빠르다 , 데이터의 위치를 빠르게 찾아주는 역활을 한다

Index 주의 사항 : 저장된 데이터가 자주 변경되는 데이터는 사용하기 힘들다

문법

Index는 정확한 사용법을 배우지 못했다!

 

• Commit / Rollback - Transaction / 트랜잭션 제어어

일부 회사에서는 commit / rollback 을 TCL로 따로 분류하기도 한다

Commit : 메모리에 작성한 내용을 DB에 완전하게 저장함

                단, DCL / DDL 명령어들은 자동 COMMIT을 한다

 

Rollback : 마지막 commit 시점으로 되돌아간다