[MySQL] 3.1 데이터 필터링 - WHERE

---

http://www.mysqltutorial.org 내용을 따라 익히며 정리한 글입니다. 예시에 나오는 데이터셋은 MySQL 샘플 데이터셋인 classicmodels DB입니다.

---

1. WHERE 소개

WHERE 를 사용하면 쿼리에서 반환하는 행에 대한 검색 조건을 지정할 수 있다. 다음과 같이 사용한다.

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition;

2. WHERE 예시

WHERE 절에서는 다음과 같이 비교 연산자를 사용할 수 있다.
| 연산자 | 설명|
|:—:|:—:|
|=|같다|
|<> or !=|같지 않다|
|<|작다|
|>|크다|
|<=|작거나 같다|
|>=|크거나 같다|

2-1.

employees 테이블에서 직책이 ‘Sales Rep’인 사람들 추려내시오.

SELECT
    lastname,
    firstname,
    jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep';

+-----------+-----------+-----------+
| lastname  | firstname | jobtitle  |
+-----------+-----------+-----------+
| Jennings  | Leslie    | Sales Rep |
| Thompson  | Leslie    | Sales Rep |
| Firrelli  | Julie     | Sales Rep |
| Patterson | Steve     | Sales Rep |
| Tseng     | Foon Yue  | Sales Rep |
| Vanauf    | George    | Sales Rep |
| Bondur    | Loui      | Sales Rep |
| Hernandez | Gerard    | Sales Rep |
| Castillo  | Pamela    | Sales Rep |
| Bott      | Larry     | Sales Rep |
| Jones     | Barry     | Sales Rep |
| Fixter    | Andy      | Sales Rep |
| Marsh     | Peter     | Sales Rep |
| King      | Tom       | Sales Rep |
| Nishi     | Mami      | Sales Rep |
| Kato      | Yoshimi   | Sales Rep |
| Gerard    | Martin    | Sales Rep |
+-----------+-----------+-----------+
17 rows in set (0.00 sec)

2-2.

employees 테이블에서 jobtitle이 ‘Sales Rep’이면서 officeCode 가 1인 사람들을 추려내시오.

SELECT
    lastname,
    firstname,
    jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND
    officeCode = 1;

+----------+-----------+-----------+
| lastname | firstname | jobtitle  |
+----------+-----------+-----------+
| Jennings | Leslie    | Sales Rep |
| Thompson | Leslie    | Sales Rep |
+----------+-----------+-----------+
2 rows in set (0.01 sec)

2-3.

employees 테이블에서 직책이 ‘Sales Rep’가 아닌 사람들을 추려내시오.

SELECT
    lastname,
    firstname,
    jobtitle
FROM
    employees
WHERE
    jobtitle <> 'Sales Rep'

+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
+-----------+-----------+----------------------+
6 rows in set (0.00 sec)

2-4.

employees 테이블에서 officeCode 가 5보다 큰 사람들을 추려내시오.

SELECT
    lastname,
    firstname,
    officeCode
FROM
    employees
WHERE
    officecode > 5;

+-----------+-----------+------------+
| lastname  | firstname | officecode |
+-----------+-----------+------------+
| Patterson | William   | 6          |
| Bott      | Larry     | 7          |
| Jones     | Barry     | 7          |
| Fixter    | Andy      | 6          |
| Marsh     | Peter     | 6          |
| King      | Tom       | 6          |
+-----------+-----------+------------+
6 rows in set (0.00 sec)