20211103 데이터베이스05

1. 상품테이블의 단가 중 단가가 높은 상품 순서대로 순위를 포함하여 상품테이블을 출력하시오

SELECT
	g.g_code AS'상품코드'
	,g.g_name AS '상품명'
	,g.g_price AS '상품가격'
	,(case 
		when (@gPrice = g.g_price) then @rank
		when (@gPrice := g.g_price) then @rank := @rank + 1
		END) AS '순위'
FROM
	tb_goods AS g
	join
	(SELECT @rank := 0, @gPrice := 0) AS r
ORDER BY g.g_price DESC;

변수는  마지막에 실행.

 

1. from 의 select 구문으로 변수 0으로 초기화.

select 구문의 case 문 실행.

@gPrice(0) = 800,000 과 같으면 rank 는 0이다 (pass)

@gPrice 에 800,000 을 대입. 그러면 @rank는 @rank(0)+1

 

2. @gPrice 는 지금 800,000 = 800,000  그러므로 1 출력.

 

3. @gPrice(800,000) =/=770,000 이다. (pass)

@gPrice 에 770,000 을 대입하고 @rank (1) +1 = 2 이다.

 

 

 

2. 

SELECT
	g.g_code AS'상품코드'
	,g.g_name AS '상품명'
	,g.g_price AS '상품가격'
	,(case 
		when (@gPrice = g.g_price) then @rank
		when (@gPrice := g.g_price) then @rank := @checkValue + 1
		END) AS '순위'
	,(@checkValue := @checkValue+ 1) AS '체크' 
FROM
	tb_goods AS g
	join
	(SELECT @rank := 0, @gPrice := 0, @checkValue := 0) AS r
ORDER BY g.g_price DESC;

	rank	price	checkvalue
초기	0	0	0
g024	1	800,000	1
g052	1	800,000	2
g021	3	770,000	3
g026	4	700,000	4
g016	5	600,000	5
g011	5	600,000	6
g007	7	500,000	7

1. rank : 0 , price : 0 , chekvalue : 0

g024에서 초기 price 값 0 과 80만원은 같지않으므로

다음 when 문에서 rank 는 0에서 1이 플러스 되어 1이 된다.

checkvalue는 0에서 1을 플러스하여 1이 된다.

 

2. g052에서 80만원과 80만원은 같으므로,

rank 1 값을 그대로 가져오고

checkvalue는 1 + 1 하여 2가 된다.

 

3. g021에서 80만원과 77만원은 같지 않으므로,

rank 는 checkvalue 2 + 1 하여 3이 되고

checkvalue 는 2+1하여 3이 된다.

 

 

 

3. 회원 별 로그인 평균 횟수보다 많이 로그인한 회원 아이디와 로그인 횟수를 조회 하시오. (관리자 제외)

 

1)

SELECT
	m.m_id AS '회원아이디'
	,COUNT(*) AS '로그인횟수'
FROM
		tb_member AS m
		INNER join
		tb_login AS l
		ON
		m.m_id = l.login_id
		and
		m.m_level > 1
GROUP BY m.m_id
HAVING COUNT(*) >
		(/*회원 평균 로그인 회수*/
		SELECT
			AVG(avg_login.로그인횟수)
		FROM
		(/*회원별 로그인 횟수*/
			SELECT
				m.m_id
				,COUNT(*) AS '로그인횟수'
			FROM
				tb_member AS m
				INNER join
				tb_login AS l
				ON
				m.m_id = l.login_id
				and
				m.m_level > 1
			GROUP BY m.m_id) AS avg_login)

 

2) 변수활용

SELECT
	result.m_id
	,result.로그인횟수
from	
	(SELECT
		m.m_id
		,COUNT(*) AS '로그인횟수'
		,@sumlogin := @sumlogin + COUNT(*)
		,@cnt := @cnt+1
	
	FROM
		tb_member AS m
		INNER join
		tb_login AS l
		on
		m.m_id = l.login_id
		and
		m.m_level >1
		join
		(SELECT @sumlogin := 0, @cnt := 0) AS c
	GROUP BY m.m_id) AS result	
WHERE 
	result.로그인횟수 > (@sumlogin/@cnt)

 

 

4. 회원 별 구매이력 중 구매금액이 가장 높은 상위 30%만 조회하시오.

/* 구매자 총 구매액*/
SELECT
	*
from
	(SELECT
		(case 
		when @gPrice = result.amt then @rank
		when @gPrice := result.amt then @rank := @checked + 1
		END ) AS rank
		,result.o_id
		,result.amt
		,@checked := @checked +1 AS cnt
	from
		(SELECT
			o.o_id
			,SUM(g.g_price*o.o_amount) AS amt
		FROM
			tb_order AS o
			INNER JOIN
			tb_goods AS g
			on
			o.o_g_code = g.g_code
		GROUP BY o.o_id	
		ORDER BY amt DESC) AS result
		join
		(SELECT @gPrice := 0, @rank := 0, @checked := 0) AS r) AS total
WHERE
	total.rank <= (@checked * 0.3)

 

---

VIEW

뷰 :

•사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블이다 ( 가상의 논리테이블)

 

뷰 작동 방식

뷰의 특징 :

- 기본테이블로부터 유도된 테이블이기에 테이블과 같은 형태의 구조이며 조작도 테이블과 거의 같다.

- 가상 테이블이기에 데이터의 논리적 독립성을 제공한다.

- 뷰를 통해서만 데이터를 접근하여 뷰에 나타나지 않는 데이터 보안에 도움.

- 필요한 데이터만 뷰로 정의해서 처리하기에 관리가 용이하고 명령문이 간단함.

 

뷰의 단점 :

- 가상 테이블이기에 데이터의 논리적 독립성을 제공한다.

- ALTER VIEW문을 사용할 수 없다. (뷰의 정의를 변경할 수 없다.)

- 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약이 따른다.

 

MySql 뷰의 제약사항 :

- 뷰의 정의는 select 구문이 허용하는 where, group by 와 같은 것을 모두 포함 가능.

- TEMPORARY 뷰를 만들 수 없다.

- 뷰와 함께 연결되는 트리거를 만들 수 없다.

- 뷰 정의에 있는 SELECT 구문은 다음과 같은 구성을 포함할 수 없다.

   - FROM 절 안에 서브쿼리 (인라인 뷰x, 스칼라 서브쿼리 o, 서브쿼리 o)

   - TEMPORARY 테이블로 참조

   - 사용자 변수로 참조

 

 

1. 뷰 생성 문장 ( ex) 휴먼회원 조회)

CREATE VIEW v_sleep_member
AS
	select
		m.m_id	AS memberId
		,m.m_email AS memberEmail
	from
		tb_member AS m
		left join
		tb_login AS l
		on
		m.m_id = l.login_id
	where
		l.login_id IS NULL;

2. 뷰 조회

SELECT
	m.memberId
	,m.memberEmail
FROM
	v_sleep_member AS m

3. 뷰 삭제

DROP VIEW v_member;

 

 

2-1. 생성된 뷰

1) 알고리즘

- MERGE : view를 정의하고 있는 테이블을 select 문으로 조회

- TEMPTABLE :  VIEW를 정의하고 있는 SELECT 쿼리의 결과를 임시 테이블에 저장하는 방식

 

2)SQL security

- definer : 생성한 계정의 권한을 따름

- invoker : 실행한 계정의 권한을 따름

 

3) 업데이트 옵션 

- casecaded : 뷰를 통해서 뷰를 만들었을 때, 스키마 구조에 모두 영향을 준다.

- local : 단독.