【Mysql】教程全解（一） select与select distinct

MySQL示例数据库

在本MySQL教程中，大部分操作是基于yiibaidb这个数据库作为学习MySQL示例数据库，这样的话有助于您快速有效地使用MySQL。yiibaidb数据库是一个典型汽车零售商数据库模型。它包含典型的业务数据，如客户，产品，销售订单，销售订单等。

第一步： 从MySQL示例数据库文章中下载示例数据库(yiibaidb)，有关示例数据库的结构，示例数据库下载地址： http://www.yiibai.com/downloads/yiibaidb.zip

第二步： 将下载的文件解压缩到临时文件夹中。为了简单起见，我们将把它解压缩到D:\worksp，如下所示 

MySQL示例数据库模式由以下表组成：

• customers: 存储客户的数据。

• products: 存储汽车的数据。

• productLines: 存储产品类别数据。

• orders: 存储客户订购的销售订单。

• orderDetails: 存储每个销售订单的订单产品数据项。

• payments: 存储客户订单的付款数据信息。

• employees: 存储所有员工信息以及组织结构，例如，直接上级(谁向谁报告工作)。

• offices: 存储销售处数据，类似于各个分公司。

表与表之间的关系，请参考以下ER图 ：

第三步： 创建数据库并导入数据

创建数据库 ：

mysql> CREATE DATABASE IF NOT EXISTS yiibaidb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;mysql> use yiibaidb;

导入数据：

mysql> use yiibaidb;mysql> source D:/worksp/yiibaidb.sql;

第四步： 测试导入结果

mysql> select city,phone,country from `offices`; --------------- ------------------ ----------- | city          | phone            | country   | --------------- ------------------ ----------- | San Francisco | 1 650 219 4782  | USA       || Boston        | 1 215 837 0825  | USA       || NYC           | 1 212 555 3000  | USA       || Paris         | 33 14 723 4404  | France    || Beijing       | 86 33 224 5000  | China     || Sydney        | 61 2 9264 2451  | Australia || London        | 44 20 7877 2041 | UK        | --------------- ------------------ ----------- 7 rows in set (0.00 sec)

01

查询语句

1.1 SELECT

显示如何使用简单的SELECT语句来查询单个表中的数据。

使用SELECT语句从表或视图获取数据。表由行和列组成，如电子表格。 通常，我们只希望看到子集行，列的子集或两者的组合。SELECT语句的结果称为结果集，它是行列表，每行由相同数量的列组成。

请参阅示例数据库(yiibaidb)中的以下employees表的结构。它有8列：员工人数，姓氏，名字，分机，电子邮件，办公室代码，报告，职位等。

SELECT语句控制要查看哪些列和行。例如，如果只对所有员工的名字，姓氏和职位感兴趣，或者您只想查看其职位是销售代表的每位员工的信息，则SELECT语句可帮助您执行这些操作。

我们来看一下SELECT语句的语法：

SELECT    column_1, column_2, ..
.FROM    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE  conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

SQL

SELECT语句由以下列表中所述的几个子句组成：

• SELECT之后是逗号分隔列或星号(*)的列表，表示要返回所有列。

• FROM指定要查询数据的表或视图。

• JOIN根据某些连接条件从其他表中获取数据。

• WHERE过滤结果集中的行。

• GROUP BY将一组行组合成小分组，并对每个小分组应用聚合函数。

• HAVING过滤器基于GROUP BY子句定义的小分组。

• ORDER BY指定用于排序的列的列表。

• LIMIT限制返回行的数量。

语句中的SELECT和FROM语句是必须的，其他部分是可选的。

在随后的教程中将更详细地了解每个子句。在本教程中，我们将重点介绍SELECT语句的简单形式用法。

MySQL SELECT语句示例

SELECT语句允许通过在SELECT子句中指定逗号分隔列的列表来查询表的部分数据。 例如，如果要仅查看员工的名字，姓氏和职位，请使用以下查询：

SELECT    lastname, firstname, jobtitle
FROM    employees;

即使员工表中有很多列，SELECT语句只返回表中所有行的三列数据，如下图所示：

mysql> SELECT lastname, firstname, jobtitle FROM employees; ----------- ----------- ---------------------- | lastname  | firstname | jobtitle             | ----------- ----------- ---------------------- | Murphy    | Diane     | President            || Patterson | Mary      | VP Sales             || Firrelli  | Jeff      | VP Marketing         || Patterson | William   | Sales Manager (APAC) || Bondur    | Gerard    | Sale Manager (EMEA)  || Bow       | Anthony   | Sales Manager (NA)   || Jennings  | Leslie    | Sales Rep            || Thompson  | Leslie    | Sales Rep            || Firrelli  | Julie     | Sales Rep            || Patterson | Steve     | Sales Rep            || Tseng     | Foon Yue  | Sales Rep            || Vanauf    | George    | Sales Rep            || Bondur    | Loui      | Sales Rep            || Hernandez | Gerard    | Sales Rep            || Castillo  | Pamela    | Sales Rep            || Bott      | Larry     | Sales Rep            || Jones     | Barry     | Sales Rep            || Fixter    | Andy      | Sales Rep            || Marsh     | Peter     | Sales Rep            || King      | Tom       | Sales Rep            || Nishi     | Mami      | Sales Rep            || Kato      | Yoshimi   | Sales Rep            || Gerard    | Martin    | Sales Rep            | ----------- ----------- ---------------------- 23 rows in set

注意比较以下两个语句返回列有什么区别 -

语句-1

SELECT lastname, firstname, jobtitle FROM employees;

语句-2

SELECT * FROM employees;

SQL

如果要获取employees表中所有列的数据，可以列出SELECT子句中的所有列名，或者只需使用星号(*)表示您想要从表的所有列获取数据，如下查询：

mysql> SELECT * FROM employees; ---------------- ----------- ----------- ----------- ----------------------- ------------ ----------- ---------------------- | employeeNumber | lastName  | firstName | extension | email                 | officeCode | reportsTo | jobTitle             | ---------------- ----------- ----------- ----------- ----------------------- ------------ ----------- ---------------------- |           1002 | Murphy    | Diane     | x5800     | [e-mail]    | 1          | NULL      | President            ||           1056 | Patterson | Mary      | x4611     | [e-mail]  | 1          |      1002 | VP Sales             ||           1076 | Firrelli  | Jeff      | x9273     | [e-mail]  | 1          |      1002 | VP Marketing         ||           1088 | Patterson | William   | x4871     | [e-mail] | 6          |      1056 | Sales Manager (APAC) ||           1102 | Bondur    | Gerard    | x5408     | [e-mail]     | 4          |      1056 | Sale Manager (EMEA)  ||           1143 | Bow       | Anthony   | x5428     | [e-mail]        | 1          |      1056 | Sales Manager (NA)   ||           1165 | Jennings  | Leslie    | x3291     | [e-mail]  | 1          |      1143 | Sales Rep            ||           1166 | Thompson  | Leslie    | x4065     | [e-mail]  | 1          |      1143 | Sales Rep            ||           1188 | Firrelli  | Julie     | x2173     | [e-mail]  | 2          |      1143 | Sales Rep            ||           1216 | Patterson | Steve     | x4334     | [e-mail] | 2          |      1143 | Sales Rep            ||           1286 | Tseng     | Foon Yue  | x2248     | [e-mail]     | 3          |      1143 | Sales Rep            ||           1323 | Vanauf    | George    | x4102     | [e-mail]    | 3          |      1143 | Sales Rep            ||           1337 | Bondur    | Loui      | x6493     | [e-mail]    | 4          |      1102 | Sales Rep            ||           1370 | Hernandez | Gerard    | x2028     | [e-mail]   | 4          |      1102 | Sales Rep            ||           1401 | Castillo  | Pamela    | x2759     | [e-mail]   | 4          |      1102 | Sales Rep            ||           1501 | Bott      | Larry     | x2311     | [e-mail]      | 7          |      1102 | Sales Rep            ||           1504 | Jones     | Barry     | x102      | [e-mail]      | 7          |      1102 | Sales Rep            ||           1611 | Fixter    | Andy      | x101      | [e-mail]    | 6          |      1088 | Sales Rep            ||           1612 | Marsh     | Peter     | x102      | [e-mail]     | 6          |      1088 | Sales Rep            ||           1619 | King      | Tom       | x103      | [e-mail]       | 6          |      1088 | Sales Rep            ||           1621 | Nishi     | Mami      | x101      | [e-mail]      | 5          |      1056 | Sales Rep            ||           1625 | Kato      | Yoshimi   | x102      | [e-mail]       | 5          |      1621 | Sales Rep            ||           1702 | Gerard    | Martin    | x2312     | [e-mail]     | 4          |      1102 | Sales Rep            | ---------------- ----------- ----------- ----------- ----------------------- ------------ ----------- ---------------------- 23 rows in set

它返回employees表中的所有列和行。应该使用星号(*)进行测试。建议显式获取数据的列，原因如下：

• 使用星号(*)可能会返回不使用的列的数据。 它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。

• 如果明确指定列，则结果集更可预测并且更易于管理。 想象一下，当您使用星号(*)并且有人通过添加更多列来更改表格数据时，将会得到一个与预期不同的结果集。

• 使用星号(*)可能会将敏感信息暴露给未经授权的用户。

1.2  SELECT  DISTINCT 

从表中查询数据时，可能会收到重复的行记录。为了删除这些重复行，可以在SELECT语句中使用DISTINCT子句。

DISTINCT子句的语法如下：

SELECT DISTINCT    columns
FROM    table_name
WHERE    where_conditions;

SQL

2. MySQL DISTINCT示例

下面来看看一个使用DISTINCT子句从employees表中选择员工的唯一姓氏(lastName)的简单示例。

首先，使用SELECT语句从employees表中查询员工的姓氏(lastName)，如下所示：

SELECT    lastname
FROM    employees
ORDER BY lastname;

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT lastname FROM employees ORDER BY lastname; ----------- | lastname  | ----------- | Bondur    || Bondur    || Bott      || Bow       || Castillo  || Firrelli  || Firrelli  || Fixter    || Gerard    || Hernandez || Jennings  || Jones     || Kato      || King      || Marsh     || Murphy    || Nishi     || Patterson || Patterson || Patterson || Thompson  || Tseng     || Vanauf    | ----------- 23 rows in set

Shell

可看到上面结果中，有好些结果是重复的，比如：Bondur，Firrelli等，那如何做到相同的结果只显示一个呢？要删除重复的姓氏，请将DISTINCT子句添加到SELECT语句中，如下所示：

SELECT DISTINCT    lastnameFROM    employeesORDER BY lastname;

SQL

执行上面查询，得到以下输出结果 -

mysql> SELECT DISTINCT lastname FROM employees ORDER BY lastname; ----------- | lastname  | ----------- | Bondur    || Bott      || Bow       || Castillo  || Firrelli  || Fixter    || Gerard    || Hernandez || Jennings  || Jones     || Kato      || King      || Marsh     || Murphy    || Nishi     || Patterson || Thompson  || Tseng     || Vanauf    | ----------- 19 rows in set

Shell

当使用DISTINCT子句时，重复的姓氏(lastname)在结果集中被消除。

3. MySQL DISTINCT和NULL值

如果列具有NULL值，并且对该列使用DISTINCT子句，MySQL将保留一个NULL值，并删除其它的NULL值，因为DISTINCT子句将所有NULL值视为相同的值。

例如，在customers表中，有很多行的州(state)列是NULL值。 当使用DISTINCT子句来查询客户所在的州时，我们将看到唯一的州和NULL值，如下查询所示：

SELECT DISTINCT    state
FROM    customers;

SQL

执行上面查询语句后，输出结果如下 -

mysql> SELECT DISTINCT state FROM customers; --------------- | state         | --------------- | NULL          || NV            || Victoria      || CA            || NY            || PA            || CT            || MA            || Osaka         || BC            || Qubec         || Isle of Wight || NSW           || NJ            || Queensland    || Co. Cork      || Pretoria      || NH            || Tokyo         | --------------- 19 rows in set

Shell

4. MySQL DISTINCT在多列上的使用

可以使用具有多个列的DISTINCT子句。 在这种情况下，MySQL使用所有列的组合来确定结果集中行的唯一性。

例如，要从customers表中获取城市(city)和州(state)的唯一组合，可以使用以下查询：

SELECT DISTINCT    state, city
FROM    customers
WHERE    state IS NOT NULL
ORDER BY state , city;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT DISTINCT state, city FROM  customers WHERE state IS NOT NULL ORDER BY state ,city; --------------- ---------------- | state         | city           | --------------- ---------------- | BC            | Tsawassen      || BC            | Vancouver      || CA            | Brisbane       || CA            | Burbank        || CA            | Burlingame     || CA            | Glendale       || CA            | Los Angeles    || CA            | Pasadena       || CA            | San Diego      || CA            | San Francisco  || CA            | San Jose       || CA            | San Rafael     || Co. Cork      | Cork           || CT            | Bridgewater    || CT            | Glendale       || CT            | New Haven      || Isle of Wight | Cowes          || MA            | Boston         || MA            | Brickhaven     || MA            | Cambridge      || MA            | New Bedford    || NH            | Nashua         || NJ            | Newark         || NSW           | Chatswood      || NSW           | North Sydney   || NV            | Las Vegas      || NY            | NYC            || NY            | White Plains   || Osaka         | Kita-ku        || PA            | Allentown      || PA            | Philadelphia   || Pretoria      | Hatfield       || Qubec         | Montral        || Queensland    | South Brisbane || Tokyo         | Minato-ku      || Victoria      | Glen Waverly   || Victoria      | Melbourne      | --------------- ---------------- 37 rows in set

没有DISTINCT子句，将查询获得州(state)和城市(city)的重复组合如下：

SELECT    state, city
FROM    customers
WHERE    state IS NOT NULL

ORDER BY state , city;

SQL

执行上面查询，得到以下结果 -

5. DISTINCT子句与GROUP BY子句比较

如果在SELECT语句中使用GROUP BY子句，而不使用聚合函数，则GROUP BY子句的行为与DISTINCT子句类似。

以下语句使用GROUP BY子句来选择customers表中客户的唯一state列的值。

SELECT    state
FROM    customers
GROUP BY state;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT  state FROM customers GROUP BY state; --------------- | state         | --------------- | NULL          || BC            || CA            || Co. Cork      || CT            || Isle of Wight || MA            || NH            || NJ            || NSW           || NV            || NY            || Osaka         || PA            || Pretoria      || Qubec         || Queensland    || Tokyo         || Victoria      | --------------- 19 rows in set

可以通过使用DISTINCT子句来实现类似的结果：

mysql> SELECT DISTINCT state FROM customers; --------------- | state         | --------------- | NULL          || NV            || Victoria      || CA            || NY            || PA            || CT            || MA            || Osaka         || BC            || Qubec         || Isle of Wight || NSW           || NJ            || Queensland    || Co. Cork      || Pretoria      || NH            || Tokyo         | --------------- 19 rows in set

SQL

一般而言，DISTINCT子句是GROUP BY子句的特殊情况。 DISTINCT子句和GROUP BY子句之间的区别是GROUP BY子句可对结果集进行排序，而DISTINCT子句不进行排序。

如果将ORDER BY子句添加到使用DISTINCT子句的语句中，则结果集将被排序，并且与使用GROUP BY子句的语句返回的结果集相同。

SELECT DISTINCT    state
FROM    customers
ORDER BY state;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT DISTINCT state FROM customers ORDER BY state; --------------- | state         | --------------- | NULL          || BC            || CA            || Co. Cork      || CT            || Isle of Wight || MA            || NH            || NJ            || NSW           || NV            || NY            || Osaka         || PA            || Pretoria      || Qubec         || Queensland    || Tokyo         || Victoria      | --------------- 19 rows in set

Shell

6. MySQL DISTINCT和聚合函数

可以使用具有聚合函数(例如SUM，AVG和COUNT)的DISTINCT子句中，在MySQL将聚合函数应用于结果集之前删除重复的行。

例如，要计算美国客户的唯一state列的值，可以使用以下查询：

SELECT    COUNT(DISTINCT state)
FROM    customers
WHERE    country = 'USA';

SQL

执行上面查询，得到以下结果 -

mysql> SELECT  COUNT(DISTINCT state) FROM customers WHERE country = 'USA'; ----------------------- | COUNT(DISTINCT state) | ----------------------- |                     8 | ----------------------- 1 row in set

Shell

7. MySQL DISTINCT与LIMIT子句

如果要将DISTINCT子句与LIMIT子句一起使用，MySQL会在查找LIMIT子句中指定的唯一行数时立即停止搜索。

以下查询customers表中的前3个非空(NOT NULL)唯一state列的值。

mysql> SELECT DISTINCT state FROM customers WHERE state IS NOT NULL LIMIT 3; ---------- | state    | ---------- | NV       || Victoria || CA       | ---------- 3 rows in set

Shell

在本教程中，我们学习了使用MySQL DISTINCT子句的各种方法，例如消除重复行和计数非NULL值。