2023.11.03(금) - Day 5

• 강의 내용
  • SQL 실습

실습 환경 세팅

wget http://192.168.xxx.xxx:xxxx/test_db%2dmaster.zip

unzip test_db-master.zip

cd test_db-master

mysql -u root -p < employees.sql

mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON employees.* TO 'ltw'@'%';

mysql> exit;

• 첨부 DB -> VM Database -> MYSQL Workbench에서 load

SQL 기본 문법

• 기본 문법
  • ORDER BY
    • 출력 결과를 오름차순 정렬, 내림차순 정렬

      SELECT emp_no, salary
      FROM salaries
      ORDER BY salary DESC'

  • GROUP BY
    • 특정 속성별로 묶어서 결과를 조회할 때
  • 함수
    • 평균, 최대값, 최소값, COUNT 등
      AVG(), MAX(), MIN(), COUNT()
  • HAVING
    • WHERE절과 비슷한데 GROUP BY로 묶은 값에 대해 조건으로 검색할 때
      
      
  • IN
    • IN으로 지정된 값들 중에 있는 것만 조회
  • JOIN
    - 두 개의 테이블을 하나로 합치는 기능
    • (INNER, LEFT OUTER, RIGHT OUTER)
  • 서브 쿼리
    - WHERE절 등에서 SELECT를 한 번 더 사용하는 방식
    
    
  • IN, JOIN, 서브 쿼리 등은 주로 여러 테이블에서 사용.

SQL 실습

• ORDER BY

  • salaries 테이블에서 salary를 기준으로 오름차순 정렬해서 emp_no, salary 조회

    SELECT emp_no, salary
    FROM salaries
    ORDER BY salary;      // (default: ASC 오름차순)

    
    
  • salaries 테이블에서 salary를 기준으로 내림차순 정렬한 후
    emp_no, salary 조회

    SELECT emp_no, salary
    FROM salaries
    ORDER BY salary DESC;

    
    
  • salaries 테이블에서 salary를 기준으로 오름차순 정렬해서
    emp_no, salary를 조회하는데 salary가 같으면 from_date를 기준으로 오름차순 정렬

    SELECT emp_no, salary, from_date
    FROM salaries
    ORDER BY salary, from_date;

    
    

• GROUP BY

  • 특정 속성별로 묶어서 결과를 조회할 때
    • employees 테이블에서 남, 여 사원의 수를 출력하시오.

      SELECT gender, COUNT(emp_no)
      FROM employees
      GROUP BY gender;

      SELECT gender, COUNT(emp_no)
      FROM employees
      GROUP BY gender
      HAVING gender='M';

      
      

• HAVING

  • WHERE과 비슷한데 GROUP BY로 묶은 결과에 대해 조건으로 조회할 때
    • 사원 별 급여의 평균이 80,000이 넘는 사원의 emp_no

      SELECT emp_no, AVG(salary)
      FROM salaries
      GROUP BY emp_no
      HAVING AVG(salary) > 80000;

      
      
    • emp_no가 15,000 이상인 사람들 중에서
      사원 별 급여의 평균이 80,000이 넘는 사원의 emp_no와 평균 급여를 출력하시오.

      SELECT emp_no, AVG(salary)
      FROM salaries
      WHERE emp_no >= 15000
      GROUP BY emp_no
      HAVING AVG(salary) > 80000;

      
      
    • Engineer 직군의 직급 별 사원의 수가 90,000 초과인 직급을 출력하시오.

      SELECT title AS '직군', COUNT(emp_no) AS '사원 수'
      FROM titles
      WHERE emp_no >= 15000
      GROUP BY emp_no
      HAVING AVG(salary) > 80000;

      
      
    • Engineer 직군의 직급 별 사원의 수

      SELECT title AS '직군', COUNT(emp_no) AS '사원 수'
      FROM titles
      WHERE title LIKE '%Engineer'
      GROUP BY title;

      
      

• IN

  • 직급 'Senitor Engineer', 'Engineer', 'Assistant Engineer'인
    사람들 중에서 emp_no, title을 출력하시오.

    SELECT emp_no, title
    FROM titles
    WHERE title IN ('Senitor Engineer', 'Engineer', 'Assistant Engineer')

  
  

• JOIN

  • 두 개의 테이블을 하나로 합치는 기능
    (INNER, LEFT JON, RIGHT JOIN)
    • employees 테이블의 emp_no와 dept_emp 테이블의 emp_no

      SELECT *
      FROM employees
      INNER JOIN dept_emp 
      ON employees.emp_no=dept_dmp.emp.no;

      SELECT *
      FROM employees
      INNER JOIN dept_emp
      ON employees.emp_no=dept_emp.no
      WHERE employees.emp_no >= 10020;

      
      
    • employees 테이블의 emp_no와 dept_emp 테이블의 emp_no가 같은 값들을
      두 테이블을 하나로 합쳐서 모든 속성들을 출력

      SELECT *
      FROM employees
      LEFT OUTER JOIN dept_emp
      ON employees.emp_no=dept_emp.emp_nol

    • 평균 급여가 120,000 이상인 사원의 first_name과 평균 급여를 출력하시오.

      SELECT first_name, AVG(salary)
      FROM employees
      LEFT OUTER JOIN salaries
      ON employees.emp_no=Salaries.emp_no
      GROUP BY employees.emp_no
      HAVING AVG(salary) > 120000;

    • 부서별 평균 급여와 부서의 이름을 출력하시오.

      SELECT departments.dept_name, AVG(salary)
      FROM dept_emp
      LEFT JOIN salaries
      ON salaries.emp_no=dept_emp.emp_no
      LEFT JOIN departments
      ON dept_emp.dept_no=departments.dept_no
      GROUP BY dept_emp.dept_no;

    • 직급 별 최대 급여와 직급 이름을 출력하시오.

      SELECT MAX(salary), title
      FROM salaries
      JOIN titles ON salaries.emp_no=titles.emp_no
      GROUP BY title;

      
      

• 서브 쿼리

  • SELECT 문 안에서 SELECT문을 한 번 더 사용하는 것.

    SELECT 속성
    FROM 테이블
    WHERE 조건
    HAVING 조건
    ORDER BY 속성
    // 모두 서브 쿼리 가능

  • 조건에서 서브 쿼리 넣는 경우가 그나마 제일 많다.

    SELECT emp_no
    FROM salaries
    WHERE salary > 100000;

    SELECT *
    FROM employees
    WHERE emp_no IN (SELECT emp_no FROM salaries WHERE salary > 100000);

    SELECT *
    FROM (SELECT emp_no, salary FROM salaries WHERE salary) AS result;

이론

• cf.) computer의 시간 표현
  • Bit
    • 0 or 1
  • Encoding
    • ASCII, UTF-8