데베구_0412

allzeroyou·2022년 4월 12일

데이터베이스

목록 보기

12/25

Q/A 타임

질문1 부속질의 & WHERE IN

1)저번 화요일 수업 때 배운 "sub query"와 챕터 3의 sql 기초에서 배운 "부속 질의"가 둘 다 부속질의인데, 무슨 차이가 있는 건지 궁금합니다.

2)또한 WHERE 절에서 어떨 때 WHERE 조건 IN을 사용하고 어떨때는 IN을 사용하지 않는데, 어떨때 where 절에 IN을 사용하는지 궁금합니다.

답변)
1)같은 부속질의임.

3장: where절에서 사용하는 부속질의
4장: select절, from절 에서 사용할 수 있는 부속질의임.

2)IN은 집합안에 속해있느냐 아니냐 임
주 질의의 속성값이 부속질의의 제공값에 속하는지 체크하는 역할.

예를 들어,

질의) 출판사가 굿스포츠 or 대한미디어인것을 뽑고싶음.

SELECT * FROM Book WHERE publisher = '굿스포츠' or publisher = '대한미디어';
# 비교연산자를 통한 조건달기 => 단일행에 대한 비교임.
SELECT * FROM Book WHERE publisher in ('굿스포츠', '대한미디어');
# IN을 사용함
# 위, 아래는 같은 결과가 도출

즉, WHERE 절에 조건을 달기 위해 IN을 사용
그냥 중첩 서브쿼리를 사용하면 단일 행밖에 도출이 안되니까, 여러 행을 도출하기 위해 IN(ANY, ALL, EXISTS 등의 연산자)을 사용함.

질문2 상관부속질의가 뭔가요?

답변)
쿼리문을 작성할때, 메인 쿼리가 있고 그 메인 쿼리를 보완해주는 서브 쿼리(부속 질의)가 있음

이때, 서브 쿼리가 들어가는 절에 따라 종류가 나뉘어 있음.

SELECT절-스칼라
FROM절-인라인뷰
WHERE절-중첩질의

이때 상관부속질의는
메인 쿼리에서 작성한 쿼리가 부속 질의에 영향을 주는 것
(상관관계가 있음을 뜻함)

조인 vs 부속질의

한 테이블에 sql문을 작성하여 질의하는데 다른 테이블의 내용이 필요한 경우 조인이나 부속질의를 통해 다른 테이블에서 데이터를 가져오는 sql문 작성

조인: 테이블 + 테이블 => 필요데이터 추출
부속질의: 하나의 테이블 정보를 가지고 두개의 테이블에서 확인

크게 3가지의 부속질의가 있고 위치/역할에 따라 SELECT 부속질의(스칼라 부속질의), FROM 부속질의(인라인 뷰), WHERE 부속 질의(중첩질의)로 구분.

WHERE 부속질의(중첩질의)

주 질의에서 필요한 조건을 다른 테이블에서 가져오는 질의문.
1) 비교 연산자
=, >, <, >=, <= 을 이용하는 방법

예제) orders 테이블에서 평균판매금액보다 작거나 같은 행을 찾아 orderid와 saleprice 출력

SELECT orderid, saleprice 

from orders

where saleprice <= ( select avg(saleprice) from orders);

결과

✏️ 정리
WHERE 비교1 비교연산자 비교2
순서로 작성하며, 비교 2에는 부속질의를 이용해 타 테이블의 내용을 가져올 수 있다.

2) IN, NOT IN
IN: where 절에서 여러 값을 or 관계로 묶어 나열하는 조건을 걸때 쓸 수 있는 키워드
IN 연산자는 조건의 범위를 지정하는데 사용됨.
값은 콤마(,)로 구분해 괄호 내 묶으며, 이 값 중에서 하나 이상 일치하면 조건에 맞는 걸로 실행됨.

example

-- OR조건 표현
WHERE Condition = 'A'
OR Condition = 'B'

-- IN조건
WHERE Condition IN ('A','B')

IN조건 장점

목록에 넣을 값이 여러개 일때, IN 연산자가 OR보다 쓰기도, 보기도 이해하기도 쉽다.
IN을 사용하면 조건 순서를 보다 쉽게 관리 할수 있고, 연산자 수도 줄어든다
IN 연산자가 OR 연산자 보다 실행 속도가 빠르다

3) ALL, SOME(ANY)
비교연산자와 함께 쓰이며, 비교하고자 하는 부속질의의 모든 값보다 크거나 작음을 비교할 때는 ALL, 부속질의의 어느 값 하나라도 크거나 작음을 비교할 때는 SOME이나 ANY 사용.

예제

SELECT orderid, saleprice
FROM orders
WHERE saleprice > 
ALL
(SELECT saleprice
FROM Orders
WHERE custid = '3');

부속질의의 custid가 3인 주문의 saleprice와 비교해 주질의의 saleprice가 전부 클 경우 출력

4) EXISTS, NOT EXISTS
주질의가 제공한 속성 값으로 부속질의의 만족하는 값이 있을 때 참, 해당 행의 데이터 출력

예제

select sum(saleprice) "total"

from orders od

where exists
(select * from customer cs
where address like  '%대한민국%'
and cs.custid = od.custid);

-> customer 테이블과 order테이블의 custid가 동일하면서 대한민국 주소를 가진 고객들이 order 테이블에 있으면 해당 고객 들의 saleprice의 총합을 출력

SELECT 부속질의(스칼라 부속 질의)

스칼라는 단일값을 뜻하며, SELECT 절에 사용함.
결과값이 단일행, 단일값이 특징.

-- 정대리 급여와 테이블 전체 평균 급여를 구하시오.
SELECT name, salary, (
  SELECT ROUND(AVG(salary),-1)
  FROM employee) AS '평균급여'
FROM employee
WHERE name = '정대리';

FROM 부속질의(인라인 뷰)

FROM 문에 나타나는 서브쿼리.
뷰는 실제 물리적인 테이블이 아닌, 가상의 테이블을 말한다.
이때, 서브 쿼리가 FROM 절에 사용될 경우 무조건 AS 별칭을 지정해주어야 한다.

-- 인라인 뷰(Inline View)
-- 잘못된 구문 - 꼭 파생 테이블엔 별칭을 정해줘야 합니다.
SELECT * FROM (SELECT * FROM employee WHERE office_worker='사원') (X)
/* SQL 오류 (1248): Every derived table must have its own alias */
 
 
-- 직급이 사원인 사람들의 이름과 급여를 구하시오.
SELECT EX1.name,EX1.salary
FROM (
  SELECT *
  FROM employee AS Ii
  WHERE Ii.office_worker='사원') EX1;

질문3 조인 쿼리 질문

4장 연습문제 6-1번은 판매가격이 2만원 이상인 도서의 도서번호, 도서 이름, 고객이름, 출판사, 판매가격을 보여주는 highorders 뷰를 생성하라는 질의입니다.

CREATE VIEW highorders(bookid, bookname, name, publisher, saleprice ) AS

SELECT b.bookid, b.bookname, c.name, b.publisher, o.saleprice 

 FROM Book b, Orders o , Customer c

 WHERE saleprice >= 20000;

위 쿼리문의 조인 조건이 틀린 것. 동등 조건을 걸어주면 된다!

조인을 하려고 하는데 이때 아무런 조건을 주지 않고 SELECT 시켜서 의도한 동등 조인이 아닌, 카디전프로덕트 연산이 되서 그럼

뷰 => 쿼리로만 존재하는 것.
뷰가 실행이 되었을때 뷰를 정의했던 sql문이 실행됨.

SELECT *
FROM book b, orders o, customer c
WHERE b.bookid = o.bookid AND o.custid = c.custid
# 조인이 된 걸 확인 후 동등 조인 조건을 달자
AND saleprice >= 20000;

중간고사 관련 정보
"mysql 평균함수" 등 키워드 중심으로 구글에 검색해 정보를 얻는 능력을 키우세요.

ch5 데이터베이스 프로그래밍

저장 프로그램 - 프로시저

프로시저: sql 여러개가 합쳐서 데이터 처리함을 의미
프로시저 vs 함수 차이점: 리턴값이 있으냐없으냐에 차이점.

SHOW DATABASES;
USE madang;

# 프로시저 정의
delimiter // # delimiter : 구문 구분자

CREATE procedure userproc1(
	IN name varchar(20))
BEGIN
# 사용할 sql 문
SELECT * FROM book WHERE bookname = name;
END 
//
delimiter ; -- 끝났음을 알려주는 기호 (//)
 
# 프로시저 실행
CALL userproc1('축구의 역사');