MySQL SELECT排序、结果筛选之操作

最近由于工作较忙，故没什么时间学习，但还是在百忙之中抽出时间来!

首先，这是我们要操作的资料。

CREATE TABLE IF NOT EXISTS employee(  id INT NOT NULL AUTO_INCREMENT,  first_name VARCHAR(100) NOT NULL,  last_name VARCHAR(100) NOT NULL,  title VARCHAR(100) DEFAULT NULL,  salary DOUBLE DEFAULT NULL,  hire_date DATE NOT NULL,  notes TEXT,  PRIMARY KEY (id));INSERT INTO employee (first_name, last_name, title, salary, hire_date) VALUES     ('Robin', 'Jackman', 'Software Engineer', 5500, '2001-10-12'),    ('Taylor', 'Edward', 'Software Architect', 7200, '2002-09-21'),    ('Vivian', 'Dickens', 'Database Administrator', 6000, '2012-08-29'),    ('Harry', 'Clifford', 'Database Administrator', 6800, '2015-12-10'),    ('Eliza', 'Clifford', 'Software Engineer', 4750, '1998-10-19'),    ('Nancy', 'Newman', 'Software Engineer', 5100, '2007-01-23'),    ('Melinda', 'Clifford', 'Project Manager', 8500, '2013-10-29'),    ('Jack', 'Chan', 'Test Engineer', 6500, '2018-09-07'),    ('Harley', 'Gilbert', 'Software Architect', 8000, '2000-07-17');

使用 SELECT * FROM table_name order by column; :可将指定资料进行升幂排序，会依照其资料类型进行排序(数字按照大小、字母按照前后顺序)

mysql> SELECT * FROM employee order by hire_date;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  ||  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  ||  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  ||  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+

mysql> SELECT * FROM employee order by title;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  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  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  ||  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  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+9 rows in set (0.00 sec)

SELECT * FROM table_name order by column desc; :而最后面加上desc，可将排序设为降幂

mysql> SELECT * FROM employee order by salary desc;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  ||  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  ||  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+9 rows in set (0.00 sec)

SELECT * FROM employee order by 1 desc; 其资料可使用数字替代如1代表column 1。

mysql> SELECT * FROM employee order by 1 desc;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  ||  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  ||  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+9 rows in set (0.01 sec)

mysql> SELECT * FROM employee order by 4,3; :当然我们也可以一次进行多个排序，也就是以第4栏排好后，再依照第4栏为基準下去排序第3栏位，可以依照只有排第4栏位及排好第4栏位后再去排第3栏位的差异。

mysql> SELECT * FROM employee order by 4;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  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  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  ||  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  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+9 rows in set (0.00 sec)

可发现第3栏位的排序也依照字母顺序做了变化。

mysql> SELECT * FROM employee order by 4,3;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  ||  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  ||  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  ||  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+9 rows in set (0.00 sec)

利用SELECT * FROM employee order by salary limit 3; : 可以限制返回结果的数量，并且可搭配order,desc等排序方法，来排出如前三高薪水或是前三低薪水等等资料。

mysql> SELECT * FROM employee order by salary limit 3;+----+------------+-----------+-------------------+--------+------------+-------+| id | first_name | last_name | title             | salary | hire_date  | notes |+----+------------+-----------+-------------------+--------+------------+-------+|  5 | Eliza      | Clifford  | Software Engineer |   4750 | 1998-10-19 | NULL  ||  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  ||  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |+----+------------+-----------+-------------------+--------+------------+-------+3 rows in set (0.01 sec)mysql> SELECT * FROM employee order by salary desc limit 3;+----+------------+-----------+--------------------+--------+------------+-------+| id | first_name | last_name | title              | salary | hire_date  | notes |+----+------------+-----------+--------------------+--------+------------+-------+|  7 | Melinda    | Clifford  | Project Manager    |   8500 | 2013-10-29 | NULL  ||  9 | Harley     | Gilbert   | Software Architect |   8000 | 2000-07-17 | NULL  ||  2 | Taylor     | Edward    | Software Architect |   7200 | 2002-09-21 | NULL  |+----+------------+-----------+--------------------+--------+------------+-------+3 rows in set (0.00 sec)

SELECT * FROM employee order by salary desc limit 2,3; : 可以返回从第一个参数之值(2)开始，返回第二个参数之值(3)数量的资料。

mysql> SELECT * FROM employee order by salary desc limit 2,3;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+3 rows in set (0.00 sec)

而当我们不知总数量比数，为了确保获取全部资料，可以设置官方公开之最大资料数量，SELECT * FROM table_name LIMIT 2,18446744073709551615; : 第二个参数设为18446744073709551615可以确保获取全部的资料。

mysql> SELECT * FROM employee order by salary desc limit 2,18446744073709551615;+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  ||  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  ||  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  ||  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  ||  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+7 rows in set (0.00 sec)

而当今天在一个情境下，我们忘记资料的全名，但是记得片段字，如我们想找一个C开头的last_name就可以使用。

SELECT * FROM table_name WHERE column LIKE "C%";

mysql> SELECT *    -> FROM employee    -> WHERE last_name    -> LIKE "C%";+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  ||  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  ||  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+4 rows in set (0.01 sec)

中间有i的资料

mysql> SELECT *    -> FROM employee    -> WHERE last_name    -> LIKE "%o%";+----+------------+-----------+------------------------+--------+------------+-------+| id | first_name | last_name | title                  | salary | hire_date  | notes |+----+------------+-----------+------------------------+--------+------------+-------+|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  ||  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  ||  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |+----+------------+-----------+------------------------+--------+------------+-------+3 rows in set (0.00 sec)

如果知道特定长度，可以用_ __an ，即可获取"Chan"。

mysql> SELECT *    -> FROM employee    -> WHERE last_name    -> LIKE "__an";+----+------------+-----------+---------------+--------+------------+-------+| id | first_name | last_name | title         | salary | hire_date  | notes |+----+------------+-----------+---------------+--------+------------+-------+|  8 | Jack       | Chan      | Test Engineer |   6500 | 2018-09-07 | NULL  |+----+------------+-----------+---------------+--------+------------+-------+1 row in set (0.00 sec)

当我们资料中有包含 % , _ 符号时，可以利用\来区分其 % 及 _ ，比如

SELECT * FROM employee WHERE last_name LIKE "%%%";

寻找中间有%且前后都有字的last_name资料。

mysql> SELECT *    -> FROM employee    -> WHERE last_name    -> LIKE "%\%%";+----+------------+-----------+-------+--------+------------+-------+| id | first_name | last_name | title | salary | hire_date  | notes |+----+------------+-----------+-------+--------+------------+-------+| 10 | ds%        | cc%c      | Tes_t |    777 | 2018-10-10 | NULL  |+----+------------+-----------+-------+--------+------------+-------+1 row in set (0.00 sec)

寻找中间有 _ 且前后都有字的last_name资料。

mysql> SELECT *    -> FROM employee    -> WHERE title    -> LIKE "%\_%";+----+------------+-----------+-------+--------+------------+-------+| id | first_name | last_name | title | salary | hire_date  | notes |+----+------------+-----------+-------+--------+------------+-------+| 10 | ds%        | cc%c      | Tes_t |    777 | 2018-10-10 | NULL  |+----+------------+-----------+-------+--------+------------+-------+1 row in set (0.00 sec)