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。

CASEWHEN condition THEN resultWHEN condition THEN result...ELSE resultEND 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 TagFROM employeeorder 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_scoreFROM movieWHERE  title_year BETWEEN 2000  AND 2010  AND country = "USA"  AND gross > 100000000order 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 STARFROM movieorder by  imdb_score DESC;