EQUAL & NOT EQUA

如同字面上意思，篩選出指定相符的資料，可以以=來表示。
而NOT EQUAL也可以以!=表示，而我們也可以將NOT加在column_table之前表false。
也就是 SELECT * FROM employee WHERE salary=8000 (true);
加上NOT後 SELECT * FROM employee WHERE NOT salary=8000 (false);

LIKE & NOT LIKE

也是如同EQUAL
SELECT * FROM employee WHERE first_name LIKE "H%"; 姓氏為H開頭的
SELECT * FROM employee WHERE NOT first_name LIKE "H%"; 姓氏非H開頭的

當使用LIKE時，須注意，如果單純使用她會不區分大小寫，如果要區分大小寫記得在LIKE後面加上BINARY即可。

未加上BINARY之前。

mysql> SELECT * FROM employee WHERE last_name LIKE "%D%";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |

加上BINARY之後。

mysql> SELECT * FROM employee WHERE last_name LIKE BINARY "%D%";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
1 row in set (0.01 sec)

抑或是我們可以在創建TABLE前，將BINARY加在資料類型之後，在操作上就會自動區分大小寫。

CREATE TABLE user( username varchar(10) BINARY , password varchar(20) );

Greater Than & Less Than

要獲取大於等於或小於等於的資料，只須加>= or <=符號即可

SELECT * FROM employee where salary>=6000;

| id | first_name | last_name | title                  | salary | hire_date  | notes |
+------------+-----------+------------------------+--------+--------+
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
+------------+-----------+------------------------+--------+--------+

Between

Between就是字面上的意思，就是在某兩值之間。

mysql> SELECT * FROM employee where salary>=6000 OR salary<=8000;
等同於
mysql> SELECT * FROM employee where salary BETWEEN 6000 AND 8000;

IN & NOT IN

in , not in 可查詢指定column中的值。

mysql> SELECT * FROM employee where salary IN (6000,7200);
等同於
mysql> SELECT * FROM employee where salary=6000 OR salary=7200;

CASE

為表達式是一種通用的條件表達式，可以利用其去對我們的資料做條件判斷並創建新的column。

CASE
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END AS column_name
...

我們依照薪水高低去做，高中低的判斷，並且創建一個名為Tag的column，且以薪水由高到低去做排序。

SELECT
  first_name,
  last_name,
  title,
  salary,
  case
    when salary >= 7000 then "high"
    when salary <= 6000 then "low"
    else "medium"
  END AS Tag
FROM employee
order by
  salary desc;

+------------+-----------+------------------------+--------+--------+
| first_name | last_name | title                  | salary | Tag    |
+------------+-----------+------------------------+--------+--------+
| Melinda    | Clifford  | Project Manager        |   8500 | high   
| Harley     | Gilbert   | Software Architect     |   8000 | high   |
| Harry      | Clifford  | Database Administrator |   6800 | medium |
| Jack       | Chan      | Test Engineer          |   6500 | medium |
| Vivian     | Dickens   | Database Administrator |   6000 | low    |
| Robin      | Jackman   | Software Engineer      |   5500 | low    |
+------------+-----------+------------------------+--------+--------+

綜合以上作個練習題
1.我們需要按照票房多=>少，並篩選出在美國的電影且為2000-2010年，票房超過1億美元的電影。

SELECT
  title,
  director_name,
  title_year,
  gross,
  imdb_score
FROM movie
WHERE
  title_year BETWEEN 2000
  AND 2010
  AND country = "USA"
  AND gross > 100000000
order by
  gross desc;

2.給電影評分，按照imdb多=>少 評分規則為imdb評分8分以上5顆星, 7-8分給予4顆星....低於五分給予1顆星

SELECT
  title,
  director_name,
  title_year,
  gross,
  imdb_score,
  CASE
    WHEN imdb_score >= 8 THEN "*****"
    WHEN imdb_score >= 7
    AND imdb_score < 8 THEN "****"
    WHEN imdb_score >= 6
    AND imdb_score < 7 THEN "***"
    WHEN imdb_score >= 5
    AND imdb_score < 6 THEN "**"
    ELSE "*"
  END AS STAR
FROM movie
order by
  imdb_score DESC;