3.1 order by
当使用SELECT语句查询表中的数据时,结果集不按任何顺序进行排序。要对结果集进行排序,请使用ORDER BY子句。
ORDER BY子句允许:对单个列或多个列排序结果集。
按升序或降序对不同列的结果集进行排序。
下面说明了ORDER BY子句的语法:
SELECT column1, column2,...FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...
SQL
ASC表示升序,DESC表示降序。默认情况下,如果不明确指定ASC或DESC,ORDER BY子句会按照升序对结果集进行排序。
下面我们来学习和练习一些使用ORDER BY子句的例子。
2. MySQL ORDER BY示例
请参见示例数据库(yiibaidb)中的customers表,customers表的结构如下所示 -
以下查询从customers表中查询联系人,并按contactLastname升序对联系人进行排序。
SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname;
SQL
执行上面查询,得到以下结果 -
mysql> SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname;
----------------- ------------------
| contactLastname | contactFirstname |
----------------- ------------------
| Accorti | Paolo |
| Altagar,G M | Raanan |
| Andersen | Mel |
| Anton | Carmen |
| Ashworth | Rachel |
| Barajas | Miguel |
| Benitez | Violeta |
| Bennett | Helen |
| Berglund | Christina |
| Bergulfsen | Jonas |
| Bertrand | Marie |
... ....
| Young | Julie |
| Young | Mary |
| Young | Dorothy |
----------------- ------------------
122 rows in set
Shell
如果要按姓氏降序对联系人进行排序,请在ORDER BY子句中的contactLastname列后面指定DESC,如下查询:
SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC;
SQL
执行上面查询,得到以下结果 -
mysql> SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC;
----------------- ------------------
| contactLastname | contactFirstname |
----------------- ------------------
| Young | Jeff |
| Young | Julie |
| Young | Mary |
... ...
| Anton | Carmen |
| Andersen | Mel |
| Altagar,G M | Raanan |
| Accorti | Paolo |
----------------- ------------------
122 rows in set
Shell
如果要按姓氏按降序和名字按升序排序联系人,请在相应列中分别指定DESC和ASC,如下所示:
SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC,
contactFirstname ASC;
SQL
执行上面查询,得到以下结果 -
mysql> SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC,
contactFirstname ASC;
----------------- ------------------
| contactLastname | contactFirstname |
----------------- ------------------
| Young | Dorothy |
| Young | Jeff |
| Young | Julie |
| Young | Mary |
| Yoshido | Juri |
| Walker | Brydey |
| Victorino | Wendy |
| Urs | Braun |
| Tseng | Jerry |
... ...
| Brown | Julie |
| Brown | William |
| Bertrand | Marie |
| Bergulfsen | Jonas |
| Berglund | Christina |
| Bennett | Helen |
| Benitez | Violeta |
| Barajas | Miguel |
| Ashworth | Rachel |
| Anton | Carmen |
| Andersen | Mel |
| Altagar,G M | Raanan |
| Accorti | Paolo |
----------------- ------------------
122 rows in set
Shell
在上面的查询中,ORDER BY子句首先按照contactLastname列降序对结果集进行排序,然后按照contactFirstname列升序对排序结果集进行排序,以生成最终结果集。
MySQL ORDER BY按表达式排序示例
ORDER BY子句还允许您根据表达式对结果集进行排序。请参阅以下orderdetails表结构 -
以下查询从orderdetails表中选择订单行记录项目。它计算每个订单项的小计,并根据订单编号,订单行号(orderLineNumber)和小计(quantityOrdered * priceEach)对结果集进行排序。
SELECT
ordernumber,
orderlinenumber,
quantityOrdered * priceEach
FROM
orderdetails
ORDER BY
ordernumber,
orderLineNumber,
quantityOrdered * priceEach;
SQL
执行上面语句,总共有 2996 行结果集,以下是部分结果集片断 -
为了使查询更易于阅读,可以按列别名进行排序,方法如下:
SELECT
ordernumber,
orderlinenumber,
quantityOrdered * priceEach AS subtotal
FROM
orderdetails
ORDER BY
ordernumber,
orderLineNumber,
subtotal;
SQL
执行上面语句,总共有 2996 行结果集,以下是部分结果集片断 -
上面表达式中,使用subtotal作为表达式quantityOrdered * priceEach的列别名,并根据小计别名(subtotal)对结果集进行排序。
MySQL ORDER BY与自定义排序顺序
ORDER BY子句允许使用FIELD()函数为列中的值定义自己的自定义排序顺序。
看看下面 orders表的结构如下所示 -
例如,如果要按以下顺序基于以下状态的值对订单进行排序:In Process
On Hold
Cancelled
Resolved
Disputed
Shipped
可以使用FIELD()函数将这些值映射到数值列表,并使用数字进行排序; 请参阅以下查询:
SELECT
orderNumber, statusFROM
orders
ORDER BY FIELD(status,
'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');
SQL
执行上面查询语句,得到以下结果 -
mysql> SELECT
orderNumber, status
FROM
orders
ORDER BY FIELD(status,
'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');
------------- ------------
| orderNumber | status |
------------- ------------
| 10420 | In Process |
| 10421 | In Process |
| 10422 | In Process |
| 10423 | In Process |
| 10424 | In Process |
| 10425 | In Process |
| 10334 | On Hold |
| 10401 | On Hold |
| 10407 | On Hold |
| 10414 | On Hold |
| 10167 | Cancelled |
| 10179 | Cancelled |
| 10248 | Cancelled |
| 10253 | Cancelled |
| 10260 | Cancelled |
| 10262 | Cancelled |
| 10164 | Resolved |
| 10327 | Resolved |
... ...
| 10413 | Shipped |
| 10416 | Shipped |
| 10418 | Shipped |
| 10419 | Shipped |
------------- ------------
326 rows in set
3 MySQL GROUP BY与聚合函数
可使用聚合函数来执行一组行的计算并返回单个值。 GROUP BY子句通常与聚合函数一起使用以执行计算每个分组并返回单个值。
例如,如果想知道每个状态中的订单数,可以使用COUNT函数与GROUP BY子句查询语句,如下所示:
SELECT
status, COUNT(*) AS total_number
FROM
orders
GROUP BY status;
SQL
执行上面查询语句,得到以下结果 -
要按状态获取所有订单的总金额,可以使用orderdetails表连接orders表,并使用SUM函数计算总金额。请参阅以下查询:
SELECT
status, SUM(quantityOrdered * priceEach) AS amount
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)GROUP BY status;
SQL
执行上面查询,得到以下结果 -
类似地,以下查询返回订单号和每个订单的总金额。
SELECT
orderNumber,
SUM(quantityOrdered * priceEach) AS total
FROM
orderdetails
GROUP BY orderNumber;
SQL
执行上面查询,得到以下结果 -
2.3 MySQL GROUP BY用表达式示例
除了列之外,可以按表达式对行进行分组。以下查询获取每年的总销售额。
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)WHERE
status = 'Shipped'GROUP BY YEAR(orderDate);
SQL
执行上面查询,得到以下结果 -
在这个例子中,我们使用YEAR函数从订单日期(orderDate)中提取年份数据。只包括已发货(Shipped)状态的订单。 请注意,SELECT子句中出现的表达式必须与GROUP BY子句中的相同。
4. MySQL GROUP BY与HAVING子句
可使用HAVING子句过滤GROUP BY子句返回的分组。以下查询使用HAVING子句来选择2013年以后的年销售总额。
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)WHERE
status = 'Shipped'GROUP BY year
HAVING year > 2013;
SQL
执行上面查询,得到以下结果 -
3.2 order by子句与自然语言排序
在本教程中,您将使用ORDER BY子句了解MySQL中的各种自然排序技术。
下面让我们使用一个示例数据来开始学习自然排序技术。
假设我们有一个items的表,其中包含两列:id和item_no。使用以下CREATE TABLE语句创建items表,如下:
CREATE TABLE IF NOT EXISTS items (
id INT AUTO_INCREMENT PRIMARY KEY,
item_no VARCHAR(255) NOT NULL);
SQL
我们使用INSERT语句将一些数据插入到items表中:
INSERT INTO items(item_no)VALUES ('1'),
('1C'),
('10Z'),
('2A'),
('2'),
('3C'),
('20D');
SQL
当我们查询选择数据并按item_no排序时,得到以下结果:
这不是我们的预期的结果,我们想要看到的结果如下:
这被称为自然排序。不幸的是,MySQL不提供任何内置的自然排序语法或函数。 ORDER BY子句以线性方式排序字符串,即从第一个字符开始的每个字符一次。
为了克服这个问题,首先我们将item_no列分成两列:prefix 和 suffix。 prefix列存储item_no的数字部分,suffix列存储字母部分。然后根据这些列对数据进行排序,如下所示:
SELECT
CONCAT(prefix, suffix)FROM
items
ORDER BY prefix , suffix;
SQL
查询首先对数据进行数字排序,并按字母顺序对数据进行排序。我们就得到预期的结果。
这个解决方案的缺点是必须在插入或更新之前将item_no值分成两部分。 此外,当查询数据时,必须将这两列组合成一列。
如果item_no数据格式相当标准,则可以使用以下查询执行自然排序,而无需更改表的结构。
SELECT
item_no
FROM
items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;
SQL
执行上面查询语句,得到以下结果 -
SELECT
item_no
FROM
items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;
Shell
在这个查询中,首先使用类型转换将item_no数据转换为无符号整数。 其次,使用ORDER BY子句对数字进行数字排序,然后按字母顺序排列。
下面来看看经常要处理的另一个常见的数据。
TRUNCATE TABLE items;INSERT INTO items(item_no)VALUES('A-1'),
('A-2'),
('A-3'),
('A-4'),
('A-5'),
('A-10'),
('A-11'),
('A-20'),
('A-30');
SQL
排序后的预期结果如下:
为了得到上面这个结果,可以使用LENGTH函数。 请注意,LENGTH函数返回字符串的长度。 这个做法是首先对item_no数据进行排序,然后按列值排序,如以下查询:
SELECT
item_no
FROM
items
ORDER BY LENGTH(item_no) , item_no;
SQL
执行上面查询,得到以下结果 -
mysql> SELECT
item_no
FROM
items
ORDER BY LENGTH(item_no) , item_no;
---------
| item_no |
---------
| A-1 |
| A-2 |
| A-3 |
| A-4 |
| A-5 |
| A-10 |
| A-11 |
| A-20 |
| A-30 |
---------
9 rows in set
Shell
如下所看到数据就是自然排序的。
但是,如果所有上述解决方案都不适合。 则需要在应用程序层执行自然排序。 一些语言支持自然排序功能,例如,PHP提供了使用自然排序算法对数组进行排序的natsort()函数。
3.3 Having 子句
在SELECT语句中使用HAVING子句来指定一组行或聚合的过滤条件。
HAVING子句通常与GROUP BY子句一起使用,以根据指定的条件过滤分组。如果省略GROUP BY子句,则HAVING子句的行为与WHERE子句类似。请注意,HAVING子句将过滤条件应用于每组分行,而WHERE子句将过滤条件应用于每个单独的行。
MySQL HAVING子句示例
让我们举一些使用HAVING子句的例子来看看它是如何工作。 我们将使用示例数据库(yiibaidb)中的orderdetails表进行演示。
可以使用GROUP BY子句来获取订单号,查看每个订单销售的商品数量和每个销售总额:
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS total
FROM
orderdetails
GROUP BY ordernumber;
SQL
执行上面查询语句,得到以下结果(部分) -
现在,可以通过使用HAVING子句查询(过滤)哪些订单的总销售额大于55000,如下所示:
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS totalFROM
orderdetailsGROUP BY ordernumberHAVING total > 55000;
SQL
执行上面查询语句,得到以下结果 -
可以使用逻辑运算符(如OR和AND)在HAVING子句中构造复杂过滤条件。 假设您想查找哪些订单的总销售额大于50000,并且包含超过600个项目,则可以使用以下查询:
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach*quantityOrdered) AS total
FROM
orderdetails
GROUP BY ordernumber
HAVING total > 50000 AND itemsCount > 600;
SQL
执行上面查询语句,得到以下结果 -
假设您想查找所有已发货(status='Shiped')的订单和总销售额大于55000的订单,可以使用INNER JOIN子句将orders表与orderdetails表一起使用,并在status列和总金额(total)列上应用条件,如以下查询所示:
执行上面查询,得到以下结果 -
HAVING子句仅在使用GROUP BY子句生成高级报告的输出时才有用。 例如,您可以使用HAVING子句来回答统计问题,例如在本月,本季度或今年总销售额超过10000的订单。