Home

Jeong-Yoon
25 Feb 2020

Mysql 02

select

  • 검색, 조회를 한다.

from

  • 테이블

where

  • 특정 테이블로부터 조회를 할 건데 조건에 맞는 것만 조회

group by

  • 어떠한 조건을 주어 데이터를 꺼내올 건데 해당 컬럼에 있는 데이터 별로 꺼내온다.
-- 직급별 직급명과 사원 수를 조회하세요.
> select title, count(*) from emp group by title;
+--------------------+----------+
| title              | count(*) |
+--------------------+----------+
| 사장               |        1 |
| 기획부장           |        1 |
| 영업부장           |        1 |
| 총무부장           |        1 |
| 인사부장           |        1 |
| 과장               |        5 |
| 영업대표이사        |        5 |
| 사원               |       10 |
+--------------------+----------+

-- 직급별 사원 수를 조회하세요.
> SELECT title, count(*) FROM emp GROUP BY title HAVING count(*) >= 5;
+--------------------+----------+
| title              | count(*) |
+--------------------+----------+
| 과장               |        5 |
| 영업대표이사        |        5 |
| 사원               |       10 |
+--------------------+----------+
  • Primary key (PK) : 기본 키, 대표 키
    • 테이블을 유일하게 식별하기 위한 키
    • 중복이 허용되지 않는다.
    • NULL값을 허용하지 않는다.
  • Foreign Key (FK) : 외래 키, 참조 키
    • 다른 테이블의 기본 키를 참조하기 위한 키
    • *단, 논리상으로 연결 기준이 되는 컬럼이라면 상관없다.
  • EQUIJOIN
    • employees와 dept_manager 테이블을 JOIN, first_name, last_name, from_date 조회 ```sql

      SELECT CONCAT(last_name, ‘ ‘, first_name) AS name, from_date FROM employees, dept_manager WHERE employees.emp_no = dept_manager.emp_no; +———————–+————+ | name | from_date | +———————–+————+ | Markovitch Margareta | 1985-01-01 | | Minakawa Vishwani | 1991-10-01 | | Alpin Ebru | 1985-01-01 | | Legleitner Isamu | 1989-12-17 | | Ossenbruggen Shirish | 1985-01-01 | | Sigstam Karsten | 1992-03-21 | | Wegerle Krassimir | 1985-01-01 | | Cools Rosine | 1988-09-09 | | Kieras Shem | 1992-08-02 | | Ghazalie Oscar | 1996-08-30 | | Hagimont DeForest | 1985-01-01 | | DasSarma Leon | 1992-04-25 | | Onuegbe Peternela | 1985-01-01 | | Hofmeyr Rutger | 1989-05-06 | | Quadeer Sanjoy | 1991-09-12 | | Pesch Dung | 1994-06-28 | | Kaelbling Przemyslawa | 1985-01-01 | | Zhang Hauke | 1991-03-07 | | Staelin Arie | 1985-01-01 | | Kambil Hilary | 1991-04-08 | | Butterworth Tonny | 1985-01-01 | | Giarratana Marjo | 1988-10-17 | | Spinelli Xiaobin | 1992-09-08 | | Weedman Yuchang | 1996-01-03 | +———————–+————+ – WHERE 조건절에 employees.emp_no와 같이 테이블 명을 써준 이유 – emp_no 컬럼명이 여러번 나오기 때문에 식별하기 위해. SELECT CONCAT(employees.last_name, ‘ ‘, employees.first_name) AS name, dept_manager.from_date FROM employees, dept_manager WHERE employees.emp_no = dept_manager.emp_no;

– employees와 titles 테이블을 JOIN하여 이름과 직책을 조회하세요.

SELECT CONCAT(last_name, ‘ ‘, first_name) AS name, title FROM employees e, titles t WHERE e.emp_no = t.emp_no;

– 현재 회사 상황을 반영한 직원별 근무부서를 사번, 직원 전체이름, 근무부서 형태로 출력해보세요.

SELECT e.emp_no AS ‘사번’, CONCAT(e.last_name, ‘ ‘, e.first_name) AS name, dp.dept_name AS ‘근무부서’ FROM employees e, dept_emp d, departments dp WHERE e.emp_no = d.emp_no AND d.dept_no = dp.dept_no AND d.to_date LIKE ‘9999%’; SELECT e.emp_no AS ‘사번’, CONCAT(e.last_name, ‘ ‘, e.first_name) AS name, d.dept_name AS ‘근무부서’ FROM dept_emp de JOIN employees e ON (de.emp_no = e.emp_no) JOIN departments d ON (d.dept_no = de.dept_no) WHERE de.to_date LIKE ‘9%’;

– 현재 회사에서 지급되고 있는 급여체계를 반영한 결과를 출력하세요. 사번, 전체이름, 연봉 이런 형태로 출력하세요. – 직원별 사번, 전체 이름, 연봉 조회

SELECT e.emp_no AS ‘사번’ , CONCAT(e.last_name, ‘ ‘, e.first_name) AS ‘이름’ , s.salary AS ‘급여’ FROM employees e JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date LIKE ‘9%’;

- WHERE절에 JOIN조건 이외에도 추가적인 조건 추가도 가능하다.
```sql
SELECT CONCAT(e.last_name, ' ', e.first_name) AS name, t.title AS '직책'
  FROM employees e, titles t
  WHERE e.emp_no = t.emp_no
  AND e.gender = 'F';
  AND t.title = 'Engineer';
  • INNER JOIN
    • 두 테이블 데이터의 교집합 
      SELECT CONCAT(e.last_name, ' ', e.first_name) AS '이름', t.title AS '직책'
FROM employees e INNER JOIN titles t
ON e.emp_no = t.emp_no;
  • ON
    • JOIN 조건을 명시하기 위함. 
      -- 3중 조인(테이블 3개를 조인)
SELECT DISTINCT e.emp_no, e.hire_date
FROM employees e JOIN titles t
ON (e.emp_no = t.emp_no)
JOIN dept_manager d
ON(e.emp_no = d.emp_no);
+--------+------------+
| emp_no | hire_date  |
+--------+------------+
| 110022 | 1985-01-01 |
| 110039 | 1986-04-12 |
| 110085 | 1985-01-01 |
| 110114 | 1985-01-14 |
| 110183 | 1985-01-01 |
| 110228 | 1985-08-04 |
| 110303 | 1985-01-01 |
| 110344 | 1985-11-22 |
| 110386 | 1988-10-14 |
| 110420 | 1992-02-05 |
| 110511 | 1985-01-01 |
| 110567 | 1986-10-21 |
| 110725 | 1985-01-01 |
| 110765 | 1989-01-07 |
| 110800 | 1986-08-12 |
| 110854 | 1989-06-09 |
| 111035 | 1985-01-01 |
| 111133 | 1986-12-30 |
| 111400 | 1985-01-01 |
| 111534 | 1988-01-31 |
| 111692 | 1985-01-01 |
| 111784 | 1988-02-12 |
| 111877 | 1991-08-17 |
| 111939 | 1989-07-10 |
+--------+------------+
-- FROM employees e JOIN title t를 오타를 내는데 employees.title 이라고 에러가 나오는 이유는
-- employees를 기준으로 JOIN하기 때문.
  • CROSS JOIN
    • 데이터 개수만큼 교차(곱)된 집합
    • A 테이블 5개, B 테이블 9개의 상태라면 결과는 45개 
      SELECT CONCAT(e.last_name, ' ', e.first_name) AS '이름',
t.title AS '직책'
FROM employees e CROSS JOIN titles t;
WHERE e.emp_no = t.emp_no;
-- 5개, 5개만 있어도 25개, 10000개 10000개가 있으면 100000000개가 생김.
-- 따라서, 조건 없이 실행 시키면 데이터가 많이 때문에 실행시키면 먹통이 된다.
  • NATURAL JOIN
    • EQUI JOIN과 동일
    • 두 테이블의 동일한 이름을 가진 컬럼이 모두 조인 
      SELECT CONCAT(e.last_name, ' ', e.first_name) AS '이름',
d.dept_no AS '부서번호'
FROM employees e NATURAL JOIN dept_manager d;
SELECT CONCAT(e.last_name, ' ', e.first_name) AS '이름',
d.dept_no AS '부서번호'
FROM employees e, dept_manager d
WHERE e.emp_no = d.emp_no;
+-----------------------+--------------+
| 이름                  | 부서번호     |
+-----------------------+--------------+
| Markovitch Margareta  | d001         |
| Minakawa Vishwani     | d001         |
| Alpin Ebru            | d002         |
| Legleitner Isamu      | d002         |
| Ossenbruggen Shirish  | d003         |
| Sigstam Karsten       | d003         |
| Wegerle Krassimir     | d004         |
| Cools Rosine          | d004         |
| Kieras Shem           | d004         |
| Ghazalie Oscar        | d004         |
| Hagimont DeForest     | d005         |
| DasSarma Leon         | d005         |
| Onuegbe Peternela     | d006         |
| Hofmeyr Rutger        | d006         |
| Quadeer Sanjoy        | d006         |
| Pesch Dung            | d006         |
| Kaelbling Przemyslawa | d007         |
| Zhang Hauke           | d007         |
| Staelin Arie          | d008         |
| Kambil Hilary         | d008         |
| Butterworth Tonny     | d009         |
| Giarratana Marjo      | d009         |
| Spinelli Xiaobin      | d009         |
| Weedman Yuchang       | d009         |
+-----------------------+--------------+
  • Subquery (서브 쿼리)
    • 한번 조회된 결과를 조건으로 다시 조회하는 방법
      ```sql – [형태] SELECT [컬럼] FROM [테이블] WHERE [연산자] (SELECT [컬럼] FROM [테이블])

– Fai Bale이 근무하는 부서의 직원의 사번, 전체 이름을 조회하세요. – 1. ‘Fai Bale’이 근무하는 부서의 ID 조회

SELECT de.dept_no FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no AND CONCAT(e.first_name, ‘ ‘, e.last_name) = ‘Fai Bale’; – 2. 부서 ID ‘d004’에 근무하는 직원의 사번, 전체 이름, 부서 이름을 조회하세요. SELECT e.emp_no AS ‘사번’, CONCAT(e.first_name, ‘ ‘, e.last_name) AS ‘이름’, d.dept_name AS ‘부서’ FROM employees e, departments d, dept_emp de WHERE e.emp_no = de.emp_no AND de.dept_no = d.dept_no AND de.dept_no = ‘d004’; – 3. ‘Fai Bale’이 근무하는 부서의 직원의 사번, 전체 이름, 부서 이름을 조회하세요. SELECT e.emp_no AS ‘사번’, CONCAT(e.first_name, ‘ ‘, e.last_name) AS ‘이름’, d.dept_name AS ‘부서’ FROM employees e, departments d, dept_emp de WHERE e.emp_no = de.emp_no AND de.dept_no = d.dept_no AND de.dept_no = (SELECT de.dept_no FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no AND CONCAT(e.first_name, ‘ ‘, e.last_name) = ‘Fai Bale’) LIMIT 30; – LIMIT 30 : 결과를 30개만 보여줘라. +——–+———————–+————+ | 사번 | 이름 | 부서 | +——–+———————–+————+ | 10003 | Parto Bamford | Production | | 10004 | Chirstian Koblick | Production | | 10010 | Duangkaew Piveteau | Production | | 10018 | Kazuhide Peha | Production | | 10020 | Mayuko Warwick | Production | | 10024 | Suzette Pettey | Production | | 10026 | Yongqiao Berztiss | Production | | 10029 | Otmar Herbst | Production | | 10030 | Elvis Demeyer | Production | | 10032 | Jeong Reistad | Production | | 10035 | Alain Chappelet | Production | | 10044 | Mingsen Casley | Production | | 10045 | Moss Shanbhogue | Production | | 10047 | Zvonko Nyanchama | Production | | 10051 | Hidefumi Caine | Production | | 10063 | Gino Leonhardt | Production | | 10069 | Margareta Bierman | Production | | 10081 | Zhongwei Rosen | Production | | 10083 | Vishv Zockler | Production | | 10084 | Tuval Kalloufi | Production | | 10085 | Kenroku Malabarba | Production | | 10096 | Jayson Mandell | Production | | 10098 | Sreekrishna Servieres | Production | | 10102 | Paraskevi Luby | Production | | 10106 | Eben Aingworth | Production | | 10109 | Mariusz Prampolini | Production | | 10113 | Jaewon Syrzycki | Production | | 10117 | Kiyotoshi Blokdijk | Production | | 10119 | Domenick Peltason | Production | | 10120 | Armond Fairtlough | Production | +——–+———————–+————+ SELECT e.emp_no, CONCAT(e.first_name, ‘ ‘, e.last_name) FROM employees e, dept_emp de WHERE de.dept_no = (SELECT de.dept_no FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no AND CONCAT(e.first_name, ‘ ‘, e.last_name) = ‘Fai Bale’);

– 평균 급여보다 많이 받는 직원의 전체 이름과 현재 연봉을 조회

SELECT CONCAT(e.first_name, ‘ ‘, e.last_name) AS ‘이름’, s.salary AS ‘연봉’ FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date LIKE ‘9%’ AND s.salary >= (SELECT AVG(salary) FROM salaries);

– 현재 가장 적은 평균 급여를 받고 있는 직책에서 평균 급여를 구하세요.

SELECT AVG(s.salary), t.title FROM salaries s, titles t WHERE s.emp_no = t.emp_no AND s.to_date LIKE ‘%9’ GROUP BY t.title;

SELECT AVG(as.salary) FROM (SELECT AVG(s.salary), t.title FROM salaries s, titles t WHERE s.emp_no = t.emp_no AND s.to_date LIKE ‘%9’ GROUP BY t.title) as;

SELECT AVG(s.salary) FROM salaries s, titles t WHERE s.emp_no = t.emp_no AND t.title = (SELECT t.title, AVG(s.salary) AS title FROM salaries s, titles t WHERE s.emp_no = t.emp_no AND s.to_date LIKE ‘%9’ GROUP BY t.title ORDER BY AVG(s.salary) LIMIT 1) GROUP BY t.title AND s.to_date LIKE ‘9%’;

SELECT t.title, AVG(s.salary) FROM salaries s, titles t WHERE s.emp_no = t.emp_no AND s.to_date LIKE ‘9%’ GROUP BY t.title ORDER BY AVG(s.salary) LIMIT 1;

SELECT MIN(a.salary) FROM (SELECT t.title AS title, AVG(s.salary) AS salary FROM salaries s, titles t WHERE s.emp_no = t.emp_no AND s.to_date LIKE ‘9%’ GROUP BY t.title) a;

### 다중 행 쿼리
- WHERE 절에서 사용 되는 다중 행 쿼리 연산자
- IN
    * 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중 하나라도 일치
    * = 이라고 생각하면 된다.
- (비교 연산자) ANY
    * 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중 하나 이상 일치
- (비교 연산자) ALL
    * 메인 쿼리의 비교 조건이 서브 쿼리의 결과 모두 일치

```sql
-- 현재 속해있는 부서의 평균 급여보다 많은 사원의 사번, 전체이름, 연봉을 조회하세요.
> SELECT AVG(s.salary) 
  FROM salaries s, dept_emp de
  WHERE s.emp_no = de.emp_no
  AND s.to_date LIKE '9%'
  GROUP BY de.dept_no;
+------------+---------+
| salary     | dept_no |
+------------+---------+
| 67665.6241 | d005    |
| 88842.1590 | d007    |
| 67841.9487 | d004    |
| 63795.0217 | d003    |
| 67932.7147 | d008    |
| 65382.0637 | d006    |
| 80014.6861 | d001    |
| 66971.3536 | d009    |
| 78644.9069 | d002    |
+------------+---------+

-- 2. 70000보다 많은 급여를 받는 사원의 사번, 전체 이름, 연봉을 조회하세요.
> SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), s.salary
  FROM employees e, salaries s
  WHERE e.emp_no = s.emp_no
  AND s.salary >= 70000;

-- 3. 
  SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), s.salary
  FROM employees e, salaries s
  WHERE e.emp_no = s.emp_no
  AND s.salary > ANY (SELECT AVG(s.salary) 
  FROM salaries s, dept_emp de
  WHERE s.emp_no = de.emp_no
  AND s.to_date LIKE '9%'
  GROUP BY de.dept_no);

-- 현재 급여가 70000 이상인 직원의 사번, 전체 이름을 조회하세요.
> SELECT e.emp_no AS '사번', CONCAT(e.first_name, ' ', e.last_name) AS '이름'
  FROM employees e
  WHERE e.emp_no IN (SELECT s.emp_no
                    FROM salaries s
                    WHERE salary >= 70000
                    AND to_date LIKE '9%')
  LIMIT 30;

-- 현재 급여가 70000 이상의 count 출력.
> SELECT count(*) FROM employees e
  WHERE e.emp_no IN (SELECT s.emp_no
                    FROM salaries s
                    WHERE salary >= 70000
                    AND to_date LIKE '9%');
> SELECT COUNT(salary)
FROM salaries
WHERE salary >= 70000
AND to_date LIKE '9%';
+----------+
| count(*) |
+----------+
|   118917 |
+----------+

> SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), s.salary, de.dept_no
  FROM employees e, salaries s, dept_emp de, (SELECT AVG(s.salary) AS salary, de.dept_no AS dept_no 
  FROM salaries s, dept_emp de
  WHERE s.emp_no = de.emp_no
  AND s.to_date LIKE '9%'
  GROUP BY de.dept_no) tmp
  WHERE e.emp_no = s.emp_no AND e.emp_no = de.emp_no
  AND tmp.dept_no = de.dept_no
  AND s.salary > tmp.salary
  AND de.dept_no = tmp.dept_no
  ORDER BY de.dept_no, s.salary;


-- 부서별로 가장 높은 연봉을 받는 직원의 사번, 전체 이름, 연봉, 부서 번호 조회
-- 1. 부서별로 가장 높은 연봉인 부서 번호와 연봉을 조회하세요.
> SELECT MAX(s.salary), de.dept_no 
  FROM salaries s, dept_emp de
  WHERE s.emp_no = de.emp_no
  AND de.to_date LIKE '9%'
  GROUP BY de.dept_no;

> SELECT e.emp_no, CONCAT(e.first_name, ' ', e.last_name), s.salary, de.dept_no
  FROM employees e, salaries s, dept_emp de
  WHERE e.emp_no = s.emp_no AND de.emp_no = e.emp_no

-- 'Engineer' 들 보다 급여를 많이 받는 직원의 정보를 조회
-- 1. 'Engineer'들의 급여를 조회하세요.
> SELECT s.salary FROM salaries s, titles t
  WHERE s.emp_no = t.emp_no
  AND s.to_date LIKE '9%'
  AND t.title = 'Engineer';
-- 2. 50000이상 급여를 받는 직원의 정보를 조회하세요.
> SELECT e.* FROM employees e, salaries s
  WHERE e.emp_no = s.emp_no
  AND s.salary >= 50000
  AND s.to_date LIKE '9%';

> SELECT e.*, s.salary FROM employees e, salaries s
  WHERE e.emp_no = s.emp_no
  AND s.to_date LIKE '9%'
  AND s.salary > ALL (SELECT s.salary FROM salaries s, titles t
                      WHERE s.emp_no = t.emp_no
                      AND s.to_date LIKE '9%'
                      AND t.title = 'Engineer');

SELECT MIN(a.AVG(s.salary)) FROM (SELECT AVG(s.salary) FROM titles t, salaries s
                 WHERE t.emp_no = s.emp_no AND s.to_date LIKE '9%'
                  GROUP BY t.title) a;

SELECT t.title AS '직책',
            AVG(s.salary) AS '평균급여'
  FROM title t, salaries s
  WHERE t.emp_no = s.emp_no AND MIN(SELECT t.title AS '직책',
                  AVG(s.salary) AS '평균급여'
                  FROM titles t, salaries s
                 WHERE t.emp_no = s.emp_no AND s.to_date LIKE '9%'
                  GROUP BY t.title);

SELECT MAX(s.salary) FROM salaries s, titles t
  WHERE s.emp_no = t.emp_no
  AND s.to_date LIKE '9%'
  AND t.title = 'Engineer';
+---------------+
| MAX(s.salary) |
+---------------+
|        140784 |
+---------------+

Til next time,
Jeong-Yoon at 00:00

scribble
‹ ›