MySQL 逻辑及运算子类型资料之基本操作

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;