프로그래머스 SQL/DB Essentials - DML 조회

mohadang·2022년 7월 2일
dbstudy
0

SQL/DB Essentials

목록 보기
4/7
post-thumbnail
  1. 검색(90%)
  • SELECT
  1. 갱신(10%)
  • INSERT
  • DELETE
  • UPDATE

SELECT

  • 표준 SQL에서는 FROM을 생략할 수 없다.

    • 하지만 MySQL, SQL Server는 생략 가능
    • SELECT LLENGTH('AAAAA') AS length;

  • SELECT문은 프로그램, 컬럼은 변수 역할

    • SELECT name, buyPrice * quantityInStock FROM products;

  • SELCT 실행 순서

    5.SELECT
1.FROM
2.WHERE
3.GROUP BY
  4.HAVING
6.ORDER BY

  • 중복값 제거(DISTINCT)

    • SELECT DISTINCT jobTitle FROM employees;

  • 컬럼 별칭(AS)

    • AS는 생량 가능
    • SELECT orderNo AS 주문번호;
    • 별명에 공백이 들어가면 따옴표로 묶어야함

  • WHERE에서는 별칭을 사용할 수 없음

    • 잘못된 방식
    SELECT orderNo, quantity * priceEach 주문액
FROM orderDetails
WHERE orderNo = 10100 AND 주문액 >= 2000;
    • 올바른 방식
    SELECT orderNo, quantity * priceEach 주문액
FROM orderDetails
WHERE orderNo = 10100 AND quantity * priceEach >= 2000;

-- quantity * priceEach 연산은 한번 수행

  • WITH - 자주 사용

    • 임시 테이블 생성하여 쿼리를 조함
    WITH temp AS
(
  SELECT orderNo, productCode, quantity, priceEach, quantity * priceEach AS 주문액
  FROM orderDetails
)
SELECT orderNo, productCode, quantity, priceEach, 주문액
FROM temp
WHERE orderNo = 10100 AND 주문액 >= 2000;
    • 단 쿼리를 2번 호출함으로 성능면에서 불리함

  • CONCAT

    • 컬럼과 컬럼, 혹은 컬럼과 문자열을 연결하여 새로운 컬럼을 생성
    SELECT CONCAT(firstName, ' ', lastName) AS 이름, jobTitle AS 직책
FROM employees
WHERE <> 'Sales Rep';

SELECT name AS 상품명, CONCAT('USD', buytPrice) AS 구매 단가;

  • CASE

    SELECT orderNo, orderDate
  CASE status
    WHEN 'In Process' THEN '처리중'
    WHEN 'Shipped' THEN '배송중'
    ELSE '없음'
  END AS status
FROM orders
WHERE YEAR(orderDate) = 2005 AND MONTH(orderDate) = 5
      SELECT CONCAT(firstName, ' ', lastName) AS 이름,
    CASE
      WHEN (MONTH(NOW()) > MONTH(birthDate)) OR
           (MONTH(NOW()) = MONTH(birthDate)) AND
           (DAY(NOW()) = DAY(birthDate))
      THEN YEAR(NOW()) - YEAR(birthDate)
      ELSE YEAR(NOW()) - YEAR(birthDate) - 1
    END AS 만나이
  FROM employees;
    - 중첩 가능
SEKECT customerId, checkNo, amounnt,
  CASE
    WHEN amount >= 6000 THEN 'A'
    ELSE
    (
      CASE
        WHEN amount >= 4000 THEN 'B'
        WHEN amount >= 2000 THEN 'C'
        ELSE 'D'
      END
    )
  END AS '지불 규모'
FROM payments
    WITH dumy AS
(
  SELECT productCode, name, buyPrice, quantityInStock, buyPrice * quantityInStock AS 재고액
  FROM s_products
)
SELECT productCode, name, buyPrice, quantityInStock, 재고액, 
    CASE 
      WHEN 재고액 >= 500000 THEN 'A'
      WHEN 재고액 >= 400000 THEN 'B'
      WHEN 재고액 >= 300000 THEN 'C'
      ELSE 'D'
    END AS 재고등급
FROM dumy

  • 비교 연산자

    • <> : 같지 않다.

  • IN

    • 값이 있는지 검사
    SELECT productCode, name, vendor, productLine
FROM products
WHERE (vendor, productLine) IN (('AAA', 'BBB'), ('CCCC', 'DDDD));

  • LIKE

    • LIKE pattern
    • % : 0개 문자 이상의 임의의 문자열
    • _ : 1개의 단일 문자
    • =, <>은 동일성 검사를 위한 연산자임
    • 표준 SQL의 연산자는 대소문자를 구분 안함
      • MYSQL에서 대소문자 구분 하려면 RLINKE같은 정규 표현식 사용 해야함
    SELECT *
FROM employees
WHERE jobTitle LIKE '%Sales Manager%';

  • BETWEEN

    SELECT *
FROM products
WHERE MSRP BETWEEN 160 AND 180;
    SELECT *
FROM products
WHERE vendor BETWEEN 'sa' AND 'sx'
ORDER BY vendor;
    vendor BETWEEN 'sa' AND 'sz' 는 vendor LIKE '%s'와 같지 않다
sza를 vendor BETWEEN 'sa' AND 'sz' 는 거르지 못한다

  • NULL

    • NULL값과의 산술 연산은 NULL
    • NULL과과 비교할 수 없음
      • NULL값과의 비교 연산(>, >=, <=, =, <>)은 언제나 FALSE
        SELECT *
FROM offices
WHERE state = NULL -- 언제나 FALSE
    • IS NULL, IS NOT NULL 사용 해야함
      SELECT *
FROM offices
WHERE state IS NULL -- OK

  • GROUP BY

    • GROUP 절은 기준 컬럼 값에 따라, 로우를 그룹별로 묶는 기능
    • 기준 컬럼 값이 같은 로우끼리 묶어 테이블을 여러 그룹으로 나누는 기능을 제공
    • 그룹별로 하나의 로우가 생성됨. 따라서 결과는 다중행이 리턴됨
    • 일반적으로 로우를 그룹핑한 후, SELECT 절에서 집단 함수를 적용함
      • 집단 함수를 사용하기 위한 전단계
    SELECT territory, COUNT(*) 지점수
FROM offices
GROUP BY territory;

    -GROUP BY 절을 실행하면 그룹핑 기준 컬럼과 집단 함수 기준 컬럼만으로 구성된 임시 테이블(중복 로우 포함)을 메모리에 생성
    - 즉 그 이외의 컬럼들은 메모리에서 제거함
    - 제거된 컬럼을 SELECT 절에서 사용할 수 없음

  • 집단 함수

    • COUNT(*) : PK 컬럼 값의 개수
      SELECT COUNT(scale), COUNT(DISTINCT scale)
    • COUNT(컬럼) : NULL 값을 제외한 컬럼 값의 개수(기준 컬럼 값이 NULL이 아닌 행의 개수)
    • SUM(컬럼) : NULL값을 제외한 컬럼 값의 합계
    • AVG(컬럼)
    • MIN(컬럼)
    • MAX(컬럼)
    • STDDEV(컬럼) : NULL 값을 제외한 컬럼 값의 표준편차
    • VARIAN(컬럼) : NULL 값을 제외한 컬럼 값의 분산
    • 집단 함수는 WHERE절에 사용할 수 없음(HAVING 절에는 가능)
      • WHERE 절에서는 한 개의 로우에서 얻을 수 있는 정보만으로 로우 조건식을 서술

  • GROUP BY 없이 집단 함수 사용

    SELECT productCode, COUNT(*) 상품수
FROM products
WHERE productLine IN ('Ships', 'Trains')
    • FROM절에 있는 테이블을 하나의 그룹으로 인식
    • 위 예시에서 productCode는 의미없다, 그룹 누적에 대한 정보가 아닌 그냥 첫번째 로우의 정보가 추력된다.

  • GROUP BY 절은 중첩 불가능

    • 국가별 고객수의 평균을 검색
    SELECT ROUND(AVG(COUNT(*)), 2) '평균 고객수'
FROM customers
GROUP BY country;

    • GROUP BY가 AVG와 COUNT중 어디에 적용되는지 모호함

    • 정답

    WITH temp AS
(
  SELECT country 국가, COUNT(*) 고객수
  FROM customers
  GROUP BY country
)
SELECT ROUND(AVG(고객수), 2) '평균 고객수'
FROM temp;
    • GROUP BY를 사용한다는 뜻은 통계 데이터를 추출한 다는 뜻임
    • GROUP BY를 사용할때 통계 데이터로 추출되지 않은 일반 컬럼은 의미 없음
      • MySQL에서는 이런 일반 컬럼을 사용할때는 에러로 처리할 수 있다.

  • HAVING

    • GROUP BY 절로 생성한 그룹 중, HAVING 절의 그룹 조건식을 만족하는 그룹만 선택

    • WHERE 로우 조건식 : 로우 조건식이 true인 로우만 선택

    • HAVING 그룹 조건식 : 그룹 조건식이 true인 그룹만 선택

    • WHERE과 GROUP BY를 잘 사용하여 성능을 높일 수 있다.

      - 비 효율적
SELECT productLine 상품라인, COUNT(*) 상품수
FROM products
GROUP BY productLine HAVING productLIne IN ('Ships', 'Trains');

- 더 효율적, WHERE에서 데이터를 간추린 후 GROUP으로 묶음
SELECT productLine 상품라인, COUNT(*) 상품수
FROM products
WHERE productLIne IN ('Ships', 'Trains');
GROUP BY productLIne;

    • GROUP BY, HAVING 절 조함은 WHERE 절 조합보다 더 느리다 따라서 가능하면 WHERE절로 데이터를 추리는 것을 권고

  • ORDER BY

    • ORDER BY 절에서 간편하게 컬럼 대신 컬럼 위치 사용 가능
    SELECT *
FROM employees
WHERE jobTitle <> 'Sales Rep'
ORDER BY 4 DESC, 2

    • ORDER BY 절에는 SELECT 목록에 나타나지 않은 컬럼이 포함될 수 있음

    • GROUP BY 절을 같이 사용하면, SELECT 목록에 나타나지 않은 컬럼을 ORDER BY 절에 사용할 수 없음

    SELECT vendor
FROM products
GROUP BY vendor
ORDER BY name; //정렬에 영향을 끼치지 않음

// vendor에 대한 그룹 정보만 있고 name은 없기에 name은 있으나 마나이다.

  • LIMIT

    • offset : 시작할 위치
      • 생략 가능하며 기본 0
    • row_count : 결과에 포함할 최대 로우 수
    SELECT orderNo
FROM orders
ORDER BY orderDate DESC
LIMIT 10, 5

SELECT 문에서는 정렬을 완료한 후 데이터를 출력하는 것이 아니라, 데이터를 먼저 검색한 후 검색 결과에 대해 정렬 작업을 실행함. 즉 ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음

  • 윈도우 함수를 사용한 Top-N Query 처리

    • Top-N Query
      • 순위(등수)가 높은 n개 로우를 검색
        • 순서가 아님
      • 동점자 처리 기능을 제공해야함
      • ORDER BY 절은 사용할 수 없음
    • 윈도우 함수
      • ROW_NUMBER() 함수 : 로우에 일련번호 부여, pagination에 활용
        • pagination : 페이지 크기에 따라서 페이지 별로 출력
      • RANK() 함수 : 로우 순위 제공, 동점자 처리
    • 주문을 최근 날짜 순으로 순위를 부여하여 출력, 단 각 행에 행 번호를 부여함
    SELECT 
  ROW_NUMBER() OVER (ORDER BY orderDate DESC) as rowNo,
  orderNo, orderDate
  RANK() OVER (ORDER BY orderDate DESC) AS dateRank
FROM orders;
    • 윈도우 함수 사용시 주의
      • 윈도우 함수는 SELECT 절에서만 사용
      • WHERE 절, GROUP BY 정에서 컬럼 별칭 사용 불가
      • 윈도우 함수를 WHERE에서 사용하고 싶다면 WITH 을 사용해야함
    • 페이지 조회하는 쿼리 구현
    WITH temp AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY order Date DESC) as rowNo,
         orderNo, orderDate, status, customerId
  FROM orders
)

SELECT rowNo, orderNo, orderDate, status, customerId
FROM temp
WHERE rowNo BETWEEEN 40 AND 44
profile
mohadang
mohadang
이전 포스트

프로그래머스 SQL/DB Essentials - Schema

다음 포스트

프로그래머스 SQL/DB Essentials - DML 갱신

0개의 댓글