데이터베이스 part3 - JOIN, 데이터베이스 설계, SQL 실습

JOIN

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것 (사용자가 필요한 집합 결과 도출)

 

1. INNER JOIN : 교집합 (특정 컬럼 기준)

2. OUTTER JOIN : 한 쪽 집합 모두 출력(특정컬럼 기준).

3. SELF JOIN : 동일 집합끼리 특정 컬럼 기준으로 집합 출력.

4. CROSS JOIN : 두 집합의 곱집합 출력.

5. NATURAL JOIN : 특정 집합의 같은 이름을 가진 컬럼 간의 조인집합을 출력

 

 

 

 

데이터베이스 설계

1. 데이터베이스 설계

  · 1단계 : 요구사항 분석
데이터베이스의 용도 파악
결과물 : 요구사항 명세서

  · 2단계 : 개념적 설계
DBMS에 독립적인 개념적 구조 설계
결과물 : 개념적 스키마 ( E-R 다이어그램)

  · 3단계 : 논리적 설계
DBMS에 적합한 논리적 구조설계
결과물 : 논리적 스키마(릴레이션 스키마)

  · 4단계 : 물리적 설계
DBMS로 구현 가능한 물리적 구조 설계
결과물 : 물리적 스키마

  · 5단계 : 구현
데이터베이스 생성


2. 요구사항 분석

  · 목적
사용자의 요구 사항을 수집 분석하여 개발할 데이터베이스의 용도 파악
업무에 필요한 데이터? 데이터를 이용하여 어떻게 처리? 등 고려
결과물 :  요구 사항 명세서

  · 주요 작업
데이터베이스를 실제로 사용할 사용자의 범위를 결정
사용자가 조직에서 수행하는 업무를 분석
면담, 설문 조사, 업무 관련 문서 분석 등의 방법을 이용해 요구 사항 수집
수집된 요구 사항에 대한 분석 결과를 요구 사항 명세서로 작성

 

 

 

 

SQL 실습

SELECT
	m.m_name AS '이름',
    	l.level_name AS '등급',
    	m.m_email AS '이메일'
FROM
	tb_member_level AS l
    INNER JOIN
    tb_member AS m
    on
    l.level_num	 = m.m_level
WHERE
	l.level_name LIKE '%관리자%';

 

 

SELECT
	m.m_id AS '2월 로그인아이디'
    ,m.m_email AS '이메일'
FROM
	tb_member AS m
INNER JOIN
	tb_login AS l
ON
	m.m_id = l.login_id
WHERE
	MONTH(l.login_date) = 2;
--	YEAR(l.login_date) = 2020;
--	l.login_date BETWEEN '2020-02-01' AND LAST_DAY('2020-02-01');

SELECT
	m.m_id AS '아이디',
    	m.m_email AS '이메일'
FROM
	tb_member AS m
LEFT JOIN
	tb_login AS l
ON
	m.m_id = l.login_id
WHERE
	l.login_date IS NULL

 

 

SELECT
	g.g_seller_id AS '판매자'
	,GROUP_CONCAT(distinct g.g_name ORDER BY g.g_name SEPARATOR ', ') AS '상품목록'
FROM
	tb_goods AS g
GROUP BY g.g_seller_id;

 

 

SELECT
	  group_concat(if(m.m_id = 'id010', m.m_email, NULL)) AS 'id010'
	 ,group_concat(if(m.m_id = 'id011', m.m_email, NULL)) AS 'id011'
	 ,group_concat(if(m.m_id = 'id012', m.m_email, NULL)) AS 'id012'
FROM
	tb_member AS m
	LEFT JOIN 
	tb_login AS l
	ON 
	m.m_id = l.login_id
WHERE 
	l.login_num IS NULL;

 

 

SELECT
	m.m_id
	,m.m_email
FROM 
	tb_member AS m
	LEFT JOIN 
	tb_login AS l
	on
	m.m_id = l.login_id
	AND
	l.login_date > LAST_DAY('2021-02-01')
WHERE 
	l.login_num IS NULL;

SUBSTRING_INDEX

SUBSTRING_INDEX(문자열, 구분자, 구분자 Index)

SELECT SUBSTRING_INDEX('사과,바나나,딸기,포도', ',', 3);

 

SELECT
	m.m_id AS memberID
	,SUBSTRING_INDEX(m.m_email,'@',1)
FROM 
	tb_member AS m;

 

 

SELECT
	g.g_seller_id AS '아이디'
	,SUM(g.g_price*o.o_amount) AS '총판매액'
FROM 
	tb_goods AS g
	INNER JOIN 
	tb_order AS o
	on
	g.g_code = o.o_g_code
GROUP BY g.g_seller_id

 

 

SELECT
	l.level_name AS '회원레벨'
	,COUNT(m.m_id) AS '인원수'
FROM 
	tb_member AS m
	RIGHT JOIN 
	tb_member_level AS l
	on
	m.m_level = l.level_num
GROUP BY l.level_num
ORDER BY m.m_id 

 

 

 

SELECT
	m.m_id AS '회원아이디'
	,m.m_email AS '이메일'
	,MAX(g.g_price*o.o_amount) AS '구매금액'
FROM 
	tb_order AS o
	INNER JOIN 
	tb_goods AS g
	on
	o.o_g_code = g.g_code
	INNER JOIN 
	tb_member AS m
	on
	o.o_id = m.m_id
GROUP BY m.m_id	

SELECT
	m.m_id
	,MAX(g.g_price * o.o_amount) AS amt
FROM
	tb_member AS m
	INNER JOIN 
	tb_order AS o
	ON
	m.m_id = o.o_id
	INNER JOIN 
	tb_goods AS g
	on
	g.g_code=o.o_g_code
	GROUP BY m.m_id;

SELECT
	result1.m_id
	,result1.m_email
	,result1.g_name
	,result1.amt
FROM
	(  SELECT
			 m.m_id
			,m.m_email
			,g.g_name
			,(g.g_price * o.o_amount) as amt 
		FROM
		 	tb_member AS m
		 	INNER JOIN 
		 	tb_order AS o
		 	ON 
		 	m.m_id = o.o_id
		 	INNER JOIN 
		 	tb_goods AS g
		 	ON 
		 	g.g_code = o.o_g_code) AS result1
	INNER JOIN 
	( SELECT
			 m.m_id
			,MAX(g.g_price * o.o_amount) AS amt
		FROM 
			tb_member AS m
			INNER JOIN 
			tb_order AS o
			on
			m.m_id = o.o_id
			INNER JOIN 
			tb_goods AS g
			ON 
			o.o_g_code = g.g_code
		GROUP BY m.m_id ) AS result2
	ON 
	result1.m_id = result2.m_id
	AND 
	result1.amt = result2.amt
ORDER BY result1.m_id;