Mysql-高级查询语句

[Mysql-高级查询语句]

本博客中数据来源于：mysql官方练习数据库

该数据库结构为：

1.控制语句

1.1DISTINCT

distinct用于记录该字段有多少不同的字段。如下所示，title表中第二个字段title有多条重复记录。

使用DISTINCT语句可以方便查询该字段有多少不同的值。

mysql> SELECT distinct title FROM `titles`;
+--------------------+
| title              |
+--------------------+
| Assistant Engineer |
| Senior Staff       |
| Engineer           |
| Staff              |
| Technique Leader   |
| Senior Engineer    |
| Manager            |
+--------------------+
7 rows in set (0.18 sec)

配合count使用，可以计算该列不同值的个数。

mysql> SELECT count(distinct title) FROM `titles`;
+-----------------------+
| count(distinct title) |
+-----------------------+
|                     7 |
+-----------------------+
1 row in set (0.09 sec)

1.2 WHERE

使用where语句可以查看限定条件下的数据。

employees表中记录不同员工的雇佣日期。

通过where语句可以筛选出所有雇佣日期为1986-06-26的雇员

mysql> select * from employees where hire_date = '1986-06-26';
+--------+------------+----------------+-----------------+--------+------------+
| emp_no | birth_date | first_name     | last_name       | gender | hire_date  |
+--------+------------+----------------+-----------------+--------+------------+
|  10001 | 1953-09-02 | Georgi         | Facello         | M      | 1986-06-26 |
|  21891 | 1960-08-25 | Shuzo          | Bernatsky       | M      | 1986-06-26 |
|  27425 | 1958-11-06 | Maik           | Gunderson       | M      | 1986-06-26 |
|  29250 | 1952-08-11 | Radoslaw       | Besancenot      | M      | 1986-06-26 |
|  32297 | 1955-03-24 | Chandrasekaran | Loncour         | M      | 1986-06-26 |
|  35301 | 1956-01-31 | Basim          | Worfolk         | F      | 1986-06-26 |
|  35565 | 1957-12-04 | Chiradeep      | Yeung           | F      | 1986-06-26 |
|  41692 | 1961-08-08 | True           | Tokunaga        | M      | 1986-06-26 |
————————————————————————————————————————————————————————————————————————————————
| 478432 | 1962-08-10 | Florian        | Beeson          | F      | 1986-06-26 |
| 478983 | 1958-08-21 | Hugo           | Litvinov        | F      | 1986-06-26 |
| 483397 | 1956-05-05 | Xuedong        | Vanwelkenhuysen | M      | 1986-06-26 |
| 499666 | 1955-12-03 | Martine        | Pollock         | M      | 1986-06-26 |
+--------+------------+----------------+-----------------+--------+------------+
83 rows in set (0.07 sec)

配合and语句可以筛选出雇佣日期为1986-06-26且性别（gender）为F（女性）的雇员。

mysql> select * from employees where hire_date = '1986-06-26' AND gender = 'F';
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
|  35301 | 1956-01-31 | Basim      | Worfolk     | F      | 1986-06-26 |
|  35565 | 1957-12-04 | Chiradeep  | Yeung       | F      | 1986-06-26 |
|  50733 | 1952-06-07 | Hironoby   | Nivat       | F      | 1986-06-26 |
|  52077 | 1964-12-14 | Rosella    | Streit      | F      | 1986-06-26 |
————————————————————————————————————————————————————————————————————————
| 458304 | 1963-12-01 | Shuzo      | Kirkerud    | F      | 1986-06-26 |
| 464413 | 1964-02-16 | Mart       | Lorho       | F      | 1986-06-26 |
| 478432 | 1962-08-10 | Florian    | Beeson      | F      | 1986-06-26 |
| 478983 | 1958-08-21 | Hugo       | Litvinov    | F      | 1986-06-26 |
+--------+------------+------------+-------------+--------+------------+
35 rows in set (0.06 sec)

配和or语句可以筛选两个条件符合其中一个的记录。如雇佣日期为1986-06-26或1986-09-17的雇员。

mysql> select * from employees where hire_date = '1986-06-26' or hire_date = '1986-09-17';
+--------+------------+----------------+-----------------+--------+------------+
| emp_no | birth_date | first_name     | last_name       | gender | hire_date  |
+--------+------------+----------------+-----------------+--------+------------+
|  10001 | 1953-09-02 | Georgi         | Facello         | M      | 1986-06-26 |
|  17761 | 1958-04-19 | Adin           | Matheson        | F      | 1986-09-17 |
|  21891 | 1960-08-25 | Shuzo          | Bernatsky       | M      | 1986-06-26 |
|  23606 | 1956-06-15 | Theirry        | Zirintsis       | M      | 1986-09-17 |
|  23680 | 1962-07-14 | Mary           | Navazio         | F      | 1986-09-17 |
————————————————————————————————————————————————————————————————————————
|  28104 | 1953-08-12 | Dmitry         | Marsiglia       | F      | 1986-09-17 |
|  29250 | 1952-08-1