[TIL] 240215

Today

• SQL 프로그래밍

  • MariaDB JOIN (SELF, NESTED) 실습 진행
  • MariaDB 서브쿼리 학습 진행중

  # 2024-02-15
  -- 지난 시간 돌아보기~
  -- 실습1
  /*
  주문번호, 주문일자, 주문자ID, 상품코드, 주문수량, 주문금액
  주문테이블, 주문상세테이블 조인 실습
  + 조건절 추가
  1. 주문액이 5000원 초과
  2. 아이디가 member1인 사람(원래는 hong1인데 난 hong친구가 없어서 member로 변경)
  */
  SELECT * from tbl_orderinfo;
  SELECT * from tbl_orderdetail;
  
  SELECT
  		OI.orderNo, OI.orderDate, OI.memberId, OI.orderAmount
  		,OD.goodsCode, OD.orderedCnt, OD.amount
  FROM tbl_orderinfo AS OI
  INNER JOIN tbl_orderdetail AS OD ON OI.orderNo = OD.orderNo
  WHERE OD.amount > 5000 AND OI.memberID = 'member1'
  ;
  
  -- 실습2
  /*
  매출 기간 : 2002-07-31 ~ 2002-08-01
  직원번호, 직원이름, 직원 성(last name), 성별, 매출금액, 판매기간시작, 판매기간 종료일
  소속부서, 부서명
  */
  USE employees;
  SELECT * FROM salaries LIMIT 100;
  SELECT * FROM employees LIMIT 100;
  SELECT * FROM dept_emp LIMIT 100;
  SELECT * FROM departments LIMIT 100;
  
  SELECT
  		EM.emp_no, EM.first_name, EM.last_name, EM.gender
  		, SA.salary, SA.from_date, SA.to_date
  		, DP.dept_no, DP.dept_name
  FROM employees AS EM
  INNER JOIN salaries AS SA ON SA.emp_no = EM.emp_no
  INNER JOIN dept_emp AS DE ON DE.emp_no = EM.emp_no
  INNER JOIN departments AS DP ON DP.dept_no = DE.dept_no
  WHERE SA.from_date BETWEEN '2002-07-31' AND '2002-08-01'
  # WHERE SA.from_date >= '2002-07-31' AND SA.to_date <= DATE('2002-08-01') to_date 날짜가 이상해서 주석처리
  ORDER BY EM.emp_no
  ;
  
  # 선생님 TIP : 이런 데이터 조회 요청사항이 들어왔을 떄 각 항목이 어떤 테이블에 있는지 먼저 정리 후 쿼리를 짜야한다!
  
  -- 실습3
  /*
  tbl_orderinfo 기준으로 tbl_orderdetail 내용을 보여주는 쿼리를 작성하시오.
  주문번호, 주문일자, 주문자 ID, 상품코드, 주문수량, 주문금액
  
  지현 분석
  tbl_orderinfo : orderNo, orderDate, memberId, orderAmount
  tbl_orderdetail : (orderNo), goodsCode, orderedCnt, amount
  */
  SELECT * FROM tbl_orderinfo;
  SELECT * FROM tbl_orderdetail;
  
  SELECT
  		OI.orderNo, OI.orderDate, OI.memberId, OI.orderAmount
  		, OD.goodsCode, OD.orderedCnt, OD.amount
  FROM tbl_orderinfo AS OI
  INNER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
  ;
  
  # orderinfo에 있는 데이터가 일부만 orderdetail에 있다면 Outer JOIN으로 걸어주면 다 볼 수 있음(근데 이 데이터에서는 모두 데이터가 있음)
  SELECT
  		OI.orderNo, OI.orderDate, OI.memberId, OI.orderAmount
  		, OD.goodsCode, OD.orderedCnt, OD.amount
  FROM tbl_orderinfo AS OI
  LEFT OUTER JOIN tbl_orderdetail AS OD ON OD.orderNo = OI.orderNo
  ;
  
  -- NESTED JOIN(다중조인) : 3개 이상의 테이블을 이용해서 JOIN 하는 것
  /*
  ◆ 각 테이블에서 연관된 컬럼으로 ON으로 묶어주는 식으로 구성합니다.
  ◆ JOIN 대상 테이블 중 서로 연관된 부분이 아예 없는 테이블이 있으면 JOIN 설정하면 안됨.
     발생 이유 : 1) DB 설계할 때 애초에 잘못했음, 
  ◆ 여러 테이블에 있는 컬럼일 때는 데이터가 적은쪽 테이블에서 갖고오는게 좋음
  
  + 아까 저 위에서 해본 실습 2번인 NESTED JOIN
  */
  
  SELECT
  		EM.emp_no, EM.first_name, EM.last_name, EM.gender
  		, DP.dept_no, DP.dept_name
  		, TT.title, TT.from_date
  FROM employees AS EM
  INNER JOIN dept_emp AS DE ON DE.emp_no = EM.emp_no
  INNER JOIN departments AS DP ON DP.dept_no = DE.dept_no
  LEFT OUTER JOIN titles AS TT ON TT.emp_no = EM.emp_no
  LIMIT 100
  ;
  
  /*
  어제 배운 LEFT/RIGHT OUTER JOIN의 경우 다중 JOIN문 일경우 LEFT, RIGHT에 여러 테이블이 있어서 
  그럴 떄는 ON에서 사용한 테이블 기준으로 LEFT JOIN문일 경우 좌측에 있는 테이블 중 ON에서 기준으로 쓰인 테이블, 
  RIGHT JOIN문일 경우 우측에 있는 테이블 중 ON에서 기준으로 쓰인 테이블이 기준 데이터로 사용됩니다.
  */
    
    
  -- SELF JOIN : 스스로 조인하기!
  # 목적 : 자신 테이블 안에서 특정 조건으로 데이터를 뽑는다던가..특정 연산을 하고싶다던가 하는 작업을 하고자할 때 사용합니다.
  
  SELECT T1.*
  FROM titles AS T1
  INNER JOIN titles AS T2 ON T1.emp_no = T2.emp_no AND T1.title <> T2.title;
  
  -- 실행계획 분석 : DBMS에서 내가 작성한 쿼리를 어떻게 실행할지 내용을 보고, 해당 쿼리의 튜닝 포인트를 잡거나 분석하는데 쓰일 수 있습니다. (자세히는 추후 배울 예정)
  # MariaDB는 실행분석에 대해 그닥 자세한 정보가 안나오는데 다른 쿼리툴에서는 실행속도 그래프 등 자세한 정보를 제공해주는 곳도 많음
  /*
  [사용법]
  EXPLAIN 실행계획을 볼 쿼리문
  */
  EXPLAIN
  SELECT
  		EM.emp_no, EM.first_name, EM.last_name, EM.gender
  		, DP.dept_no, DP.dept_name
  		, TT.title, TT.from_date
  FROM employees AS EM
  INNER JOIN dept_emp AS DE ON DE.emp_no = EM.emp_no
  INNER JOIN departments AS DP ON DP.dept_no = DE.dept_no
  LEFT OUTER JOIN titles AS TT ON TT.emp_no = EM.emp_no
  LIMIT 100
  ;
  
  EXPLAIN
  SELECT
  *
  FROM employees AS EM
  CROSS JOIN salaries AS SL;
  
  -- 서브쿼리 : 말 그대로 쿼리 안에 쿼리 넣기
  /*
  ◆ 단일행 서브쿼리 : (기본)서브쿼리의 결과(행)이 하나만 전달되는 서브쿼리 입니다.
  ◆ 다중행 서브쿼리 : 서브쿼리의 결과(행)이 여러개인 서브쿼리로 >, < 등의 연산자를 사용할 경우 에러가 납니다.
                     다중행 연산자인 IN/ANY/ALL 키워드를 이용해서 작성해야합니다.
  ◆ 인덱스를 안타서 느리기 떄문에 가급적 사용자제하는게 좋음! (잘 쓰면 탈 수도있지만...)
  */
  
  -- SELECT문 안에서 서브쿼리
  # 예시1
  SELECT *
  
  FROM salaries
  WHERE salary > (
  		SELECT MIN(salary) 
  		FROM salaries 
  		WHERE salary > 81000
  )
  LIMIT 100
  ;
  
  # 예시2
  SELECT *
  FROM salaries
  WHERE emp_no IN (
  		SELECT DISTINCT emp_no 
  		FROM salaries 
  		WHERE salary > 81000
  )
  LIMIT 100
  ;
  
  # 예시3 : NOT 키워드로 해당 서브쿼리 내 데이터가 아닌 데이터만 뽑아낼수가 있음
  SELECT *
  FROM salaries
  WHERE NOT emp_no IN (
  		SELECT DISTINCT emp_no 
  		FROM salaries 
  		WHERE salary > 81000
  )
  LIMIT 100
  ;
  
  # 예시4 
  # EXISTS 키워드는 주어진 값이 존재하는 경우 TRUE를 반환합니다.
  # NOT EXISTS 키워드는 주어진 값이 존재하지 않는 경우 TRUE를 반환합니다.
  # 해당 키워드는 SELECT문 안에서만 사용할 뿐만 아니라 CREATE, DROP문에서도 잘 쓰임.
  SELECT *
  FROM salaries
  WHERE exists (
  		SELECT emp_no 
  		FROM salaries 
  		WHERE salary >= 158220
  )
  LIMIT 100
  ;
  SELECT *
  FROM salaries
  WHERE NOT exists (
  		SELECT emp_no 
  		FROM salaries 
  		WHERE salary > 158220
  )
  LIMIT 100
  ;
  
  USE maria;
  SELECT *
  FROM tbl_orderinfo AS OI
  WHERE EXISTS (
  		SELECT *
  		FROM tbl_orderDetail AS OD
  		WHERE OI.orderNo = OD.orderNo
  );
  
  # 예시5 : 다중행 서브쿼리 사용 예시
  # ANY : 서브 쿼리의 결과 중 어떤 것이라도 만족하는 것 (|| 연산과 비슷)
  # ALL : 서브 쿼리의 결과를 모두 만족하는 것 (&& 연산과 비슷)
  # IN : 서브 쿼리 결과 중 어떤 것 하나라도 일치하는게 있으면 TRUE
  SELECT *
  FROM tbl_member 
  WHERE jobcode >= (
  		SELECT jobCode FROM tbl_member WHERE addr1 LIKE '%서울%' # 이 서브쿼리는 결과가 1개보다 많아서 실행 시 오류가 남
  );   
  
  SELECT *
  FROM tbl_member 
  WHERE jobcode >= ANY ( # 결과가 1개 보다 많아서 이 중 하나에 포함되는 비교키워드로 사용해줌
  		SELECT jobCode FROM tbl_member WHERE addr1 LIKE '%서울%' 
  );   
  
  SELECT *
  FROM tbl_member 
  WHERE jobcode >= ALL ( # 결과가 1개 보다 많아서 이 중 모두 포함되는 경우의 비교키워드로 사용해줌
  		SELECT jobCode FROM tbl_member WHERE addr1 LIKE '%서울%' 
  );

Review

• 정보처리기사 기출문제 2회 완료 (총 5회완료)
  • 4회 : 75 + 80 + 90 + 45 + 40 = 66
    5회 : 85 + 85 + 70 + 65 + 55 = 72
    평균은 70점까지 올라왔으나 아직 4, 5과목이 너무 약한 편

TO DO

• 정처기 기출문제 풀기!!! 이제 이틀남음....