MySQL WHERE相关基本操作

为了方便快速写SQL指令，可以在VS CODE中写好以后，在其SQL文件点击右键copy path，而后开启mysql cmd，将複製的路径贴上，在最前面加上source 即可将我们其路径的mysql指令複製至cmd中。

ex: source D:\Web\MySQL\CRUD\test.sql

注意:SQL文件的路径需要用\ 而不是/，且路径最好不要有空格space或者中文字符。

在SELECT时，可利用as对指定之column取别名。

SELECT first_name as fn , last_name as ln from employee;+---------+----------+| fn           | ln       |+---------+----------+| Robin   | Jackman  || Taylor  | Edward   || Vivian  | Dickens  || Harry   | Clifford || Eliza   | Clifford || Nancy   | Newman   || Melinda | Clifford || Jack    | Chan     || Harley  | Gilbert  |+---------+----------+9 rows in set (0.01 sec)

WHERE: 当我们资料库资料太多，又想过滤出自己想要的资料，可以利用where。

SELECT * from employee where title="Software Engineer";过滤出只属于title为Software Engineer的row。+----+------------+-----------+-------------------+--------+------------+-------+| id | first_name | last_name | title             | salary | hire_date  | notes |+----+------------+-----------+-------------------+--------+------------+-------+|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer |   4750 | 1998-10-19 | NULL  ||  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  |+----+------------+-----------+-------------------+--------+------------+-------+3 rows in set (0.01 sec)

OR:可以过滤其中一个符合条件的资料。

mysql> SELECT * from employee where title="Software Engineer" OR salary="5500";;+----+------------+-----------+-------------------+--------+------------+-------+| id | first_name | last_name | title             | salary | hire_date  | notes |+----+------------+-----------+-------------------+--------+------------+-------+|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer |   4750 | 1998-10-19 | NULL  ||  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  |+----+------------+-----------+-------------------+--------+------------+-------+

AND:如果要增加过滤条件可使用 and ，返回两者都符合条件的资料。

mysql> SELECT * from employee where title="Software Engineer" AND salary="5500";+----+------------+-----------+-------------------+--------+------------+-------+| id | first_name | last_name | title             | salary | hire_date  | notes |+----+------------+-----------+-------------------+--------+------------+-------+|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |+----+------------+-----------+-------------------+--------+------------+-------+

NOT:我们可以使用NOT来过滤掉不要的资料，留下要的资料。

mysql> SELECT * from employee where NOT title="Software Architect";+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | 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  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+7 rows in set (0.02 sec)

当然我们也可以混合应用其语法，如AND搭配NOT。

mysql> SELECT * FROM employee WHERE last_name != "Clifford";+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+6 rows in set (0.01 sec)

显示所有lastName非Clifford且title不为Software Engineer的资料。

mysql> SELECT * FROM employee WHERE last_name != "Clifford" AND title !="Software Engineer";+----+------------+-----------+------------------------+--------+------------+-------+| 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  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+4 rows in set (0.01 sec)