第一章 了解SQL

数据库基础

什么是数据库

数据库像一个文件柜,是一个存放数据的物理位置,不管数据是什么以及如何组织的。
数据库(database):保存有组织的数据的容器(通常是一个文件或一组文件)

我们不直接访问数据库,而是通过DBMS(数据库管理系统)来间接访问数据库。

什么是SQL

SQL是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。
SQL有以下优点:

  • SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL。
  • SQL简单易学。
  • SQL可以进行非常复杂和高级的数据库操作。

将资料放入文件柜时,不是随便将它们扔进某个抽屉就完事了,而是在文件柜中创建文件,然后将相关资料放入特定的文件中。
数据库中,表就相当于文件,表是一种结构化的文件,可用来存储某种特定类型的数据。
表(table):某种特定类型数据的结构化清单。
在表中的数据是一种类型的数据或一个清单。例如顾客信息(清单)与商家信息(清单)不应该放在一张表中,应该分别创建一张表存储。
数据库中的每个表都有一个名字,用来标识自己,此名字在库中是唯一的,以防冲突。

表名

表名的唯一性取决于多个因素,如数据库名加表名。故在不同数据库中可以使用相同表名。

表具有一些特性,这些特性定义了数据在表中如何存储。描述表中这些特性就是模式。
模式(schema):关于数据库和表的布局及特性的信息。

列和数据类型
表由列组成。列中存储着表中的某部分信息。
列(column):表中的一个字段。所有表都是由一个或多个列组成的。
数据库表就类似于一个网格,网格中的每列存储着一条特定的信息。

分解数据 正确地分解数据为多个列极为重要。这样才有可能利用特定的列对数据进行排序和过滤。

数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。
数据类型(datatype):所容许的数据的类型。每个表列都有相应的数据类型,它限制该列中存储的数据。
数据类型帮助正确地排序数据,并在优化磁盘使用方面起重要的作用。

表中的数据是按行存储的,所保存的每个记录存储在自己的行内。如果将表想象为网格,网格中垂直的列为表列,水平行为表行。
行(row):表中的一个记录。

记录or行? 有些用户提到行(row)时称其为数据库记录(record)。很大程度上,这两个术语可以相互代替,但从技术上,行才是正确的术语。

主键

表中每一行都应该有可以唯一标识自己的一列(或一组列)。
主键(primary key):一列(或一组列),其值能够唯一区分表中每个行。
唯一标识表中每行的这个列(或这组列)称为主键。主键用来表示一个特定的行。

应该总是定义主键 虽然并不总是需要主键,但大多数时候都应保证创建的每个表具有一个主键,以便于以后的数据操作和管理。

表中的任何列都可以作为主键,只需满足以下条件:

  • 任意两行都不具有相同的主键值。
  • 每行都必须具有一个主键值(主键列不允许NULL值)。
    主键通常定义在表的一列上,但也可以一起使用多个列作为主键。以上条件也必须应用到构成主键的所有列,所有列值的组合必须唯一(但单个列的值可以不唯一)。

主键的好习惯 除MySQL主键规则外,应该坚持的几个好习惯为:

不更新主键列中的值
不重用主键列中的值
不在主键列中使用可能会更改的值。

第二章MySQL简介

MySQL简介

本章将介绍什么是MySQL,以及在MySQL中可以应用什么工具。

什么是MySQL

数据的所有存储、检索、管理和处理是由数据库软件——DBMS(数据库管理系统)完成的。而MySQL就是一种DBMS,即它是一种数据库软件。

MySQL的优点:

  • 成本——MySQL是开源的,可以免费使用。
  • 性能——MySQL执行非常快。
  • 可信赖——很多大公司都用MySQL处理重要数据。
  • 简单——MySQL很容易安装和使用。

    客户机-服务器软件

    DBMS可分为两类:一类是基于共享文件系统的DBMS,另一类是基于客户机-服务器的DBMS。前者用于桌面用途,通常不用于高端或更关键的应用。

MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机-服务器的数据库。客户机-服务器应用分为两个不同的部分。服务器部分是负责所有数据访问和处理的一个软件,只有服务器软件才与数据文件打交道。客户机部分是与用户打交道的软件,客户机将用户的请求通过网络提交给服务器软件,服务器软件处理这个请求,并返回结果。

有多少计算机? 客户机和服务器软件可能安装在一台或两台计算机上。不管它们在不在同一台计算机上,客户机软件都要与服务器软件进行通信,来进行所有数据库交互。
  • 服务器软件为MySQL DBMS。
  • 客户机可以是MySQL提供的工具、脚本语言、web应用开发语言、程序设计语言等。

MySQL工具

常用的客户机应用

MySQL命令行实用程序

每个MySQL安装都有一个名为mysql的简单命令行实用程序。

在操作系统命令提示符下输入mysql将出现以下提示(需要设置环境变量):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
MySQL选项和参数 如果仅输入mysql,可能会出现一个错误消息。因为可能需要安全证书,或者MySQL没有运行在本地或默认端口上。这时你可以使用一组命令行参数来指定登录名、端口等信息。完整命令行选项和参数列表可用mysql --help获得。
  • 命令输入在mysql>之后。
  • 命令用;或\g结束,仅按Enter不执行命令。
  • 输入help或\h获得帮助。
  • 输入quit或exit退出命令行实用程序。

第三章使用MySQL

使用MySQL

本章将学习如何连接和登录到MySQL,如何执行MySQL语句,以及如何获得数据库和表的信息。

连接

MySQL与所有客户机-服务器DBMS一样,要求在能执行命令之前登录到DBMS。MySQL在内部保存自己的用户列表,并且把每个用户与各种权限关联起来。

连接到MySQL,需要以下信息:

  • 主机名(计算机名)——如果连接到本地MySQL服务器,为localhost。
  • 端口(如果使用默认端口3306以外的端口)。
  • 一个合法的用户名。
  • 用户口令(如果需要)

选择数据库

连接MySQL后,需要选择一个数据库来执行数据库操作。我们可以使用USE关键字。

关键字:作为MySQL语言组成部分的一个保留字。绝不要用关键字命名一个表或列。

例如,我们使用名为crashcourse的数据库:

mysql> USE crashcourse;
Database changed

USE语句不返回任何结果,只显示某种形式的通知。例如,显示Database changed消息表示数据库选择成功。

必须先使用USE打开数据库,才能读取其中的数据!

了解数据库和表

数据库、表、列、用户、权限等信息被存储在数据库和表中(MySQL存在名为MySQL的数据库来存储这些信息)。但内部的表一般不直接访问,可以用MySQL的SHOW命令来显示这些信息。

显示MySQL中存储的数据库,可使用SHOW DATABASES;。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| course             |
| crashcourse        |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
8 rows in set (0.01 sec)
SHOW DATABASES; 返回可用数据库的一个列表。
使用USE选择完数据库后,我们可用SHOW TABLES;来显示数据库中存储的表。
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers             |
| orderitems            |
| orders                |
| productnotes          |
| products              |
| vendors               |
+-----------------------+
6 rows in set (0.01 sec)
SHOW TABLES; 返回当前选择的数据库内可用表的列表
我们也可以用SHOW COLUMNS FROM ‘表名’;来显示对应表中的列信息。
mysql> SHOW COLUMNS FROM customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int       | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
  • SHOW COLUMNS要求给出一个表名,它对每个字段返回一行,行中信息包括字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息。
  • 自动增量(auto_increment) 某些表需要唯一值,例如订单编号等。在每个行添加到表中时,MySQL可用自动地为每个行分配下一个可用编号,不用手动分配唯一值。这就是自动增量,使用自动增量需要在创建表时,把它作为表定义的组成部分。
  • DESCRIBE语句 MySQL支持用DESCRIBE替换SHOW COLUMNS FROM。例如SHOW COLUMNS FROM customers;可换成DESCRIBE customers;
    其他SHOW语句:
  • SHOW STATUS 显示广泛的服务器状态信息。
  • SHOW GRANTS 显示授予用户的安全权限。
  • SHOW ERRORS和SHOW WARNINGS 显示服务器错误和警告消息。

更多的SHOW语句可以通过HELP SHOW来获取。

第四章检索数据

检索数据

本章将介绍如何使用SELECT语句从表中检索一个或多个数据列。

SELECT语句

SQL语句是由简单的英语单词关键字构成的,每个SQL语句都由一个或多个关键字构成。最常用的SQL语句就是SELECT语句,它的用途是从一个或多个表中检索信息。

使用SELECT检索表数据,必须有两个信息:

从什么地方选择。
要选择什么。

检索单个列

最简单的SQL SELECT语句:

mysql> SELECT prod_name
    -> FROM products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.03 sec)

上述语句利用SELECT语句从products表中检索一个名为prod_name的列。所需的列名在SELECT关键字后给出,FROM关键字指出所需列所在的表名。

如上的一条简单SELECT语句将返回表中所有行。数据没有过滤,也没有排序。

  • 未排序数据 如果读者自己尝试这个查询,可能会发现显示输出的数据顺序与上文不同。这种情况很正常。如果没有明确排序查询结果,则返回的数据的顺序没有特殊意义。只要返回相同数目的行就是正常的。
  • 结束SQL语句 多条SQL语句必须以分号(;)分隔。
  • SQL语句和大小写 SQL语句不区分大小写。为使代码易于阅读和调试,我们习惯将关键字全部大写,将所有列和表名全部小写。
  • 使用空格 处理SQL语句时,其中所有空格都被忽略。为了代码可读性,我们可以将语句分为多行。

检索多个列

检索多个列与检索单个列唯一不同的是,SELECT关键字后拥有多个列名,列名间用逗号分隔。

下面SELECT语句从products表中选择3列:

mysql> SELECT prod_id, prod_name, prod_price
    -> FROM products;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| FC      | Carrots        |       2.50 |
| FU1     | Fuses          |       3.42 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
| OL1     | Oil can        |       8.99 |
| SAFE    | Safe           |      50.00 |
| SLING   | Sling          |       4.49 |
| TNT1    | TNT (1 stick)  |       2.50 |
| TNT2    | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+
14 rows in set (0.00 sec)

与前一个例子一样,这条语句使用SELECT语句从表products中检索数据。但指定了三个列名,列名间用逗号分隔

  • 数据表示 SQL语句一般返回原始的、无格式的数据。因此数据格式化显示(对齐、符号、分隔)一般在显示该数据的应用程序中规定。

    检索所有列

    除了指定列外,SELECT语句还可以检索所有列而不必一一列出所有列。这可以在列名处使用星号(*)通配符来实现:

mysql> SELECT *
    -> FROM products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)

给定一个通配符(*),则返回表中所有列。列的顺序一般是列在定义中出现的顺序,但表模式的变化(如添加或删除列)可能会导致顺序的变化。

  • 使用通配符 通配符很方便,但检索不需要的列通常会降低检索和应用程序的性能。所有没有使用必要时,不使用。
  • 检索未知列 通配符有一大优点。能检索出名字未知的列。

    检索不同的行

    如果我们想要products表中所有供应商ID:

mysql> SELECT vend_id
    -> FROM products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+
14 rows in set (0.01 sec)

SELECT语句返回14行(即使表中只有4个供应商),因为products表中有14个产品记录。这与我们的预期结果不符。

为了解决重复的数据,我们使用DISTINCT关键字,此关键字指示MySQL只返回不同的值。

mysql> SELECT DISTINCT vend_id
    -> FROM products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.00 sec)

SELECT DISTINCT vend_id告诉MySQL只返回不同的vend_id行,因此只返回4行。如果使用DISTINCT关键字,它必须放在所有列名的前面。

  • 不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。

限制结果

SELECT语句返回所有匹配的行。但我们可以使用LIMIT子句来返回第一行或前几行:

mysql> SELECT prod_name
    -> FROM products
    -> LIMIT 5;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| Bird seed    |
+--------------+
5 rows in set (0.00 sec)

此语句使用SELECT语句检索单个列。LIMIT 5指示MySQL返回不多于5行。

我们还可以指定要检索的开始行和行数,例如得出上个结果的下一个5行:

mysql> SELECT prod_name
    -> FROM products
    -> LIMIT 5, 5;
+--------------+
| prod_name    |
+--------------+
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Oil can      |
+--------------+
5 rows in set (0.00 sec)

LIMIT 5, 5指示MySQL返回从行5开始的5行。第一个数为开始位置,第二个数为要检索的行数。

所以,带一个值的LIMIT总是从第一行开始,给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。

  • 行0 第一行的行号为0而不是1。所以,LIMIT 1, 1将返回第二行。
  • 行数不够时 LIMIT中指定的行数为检索的最大行数。若行数不足最大行数,将只会检索出所有的行。即行数可能不等于最大行数。
  • MySQL 5的LIMIT语法 LIMIT中的开始行与最大行数容易弄混,所有MySQL 5支持LIMIT的另一个语法,将逗号改成关键字OFFSET。例如,LIMIT 5 OFFSET 5。

使用完全限定的表名

目前的SQL语句的例子只通过列名引用列。但为了区分不同表中可能存在相同的列名,我们可以使用完全限定的名字来引用列(同时使用表名和列名):

mysql> SELECT products.prod_name
    -> FROM products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.01 sec)

这条语句在功能上等于本章最开始的那条语句,但这里指定了一个完全限定的列名。

表名也可以是完全限定的(同时使用数据库名和表名):

mysql> SELECT products.prod_name
    -> FROM crashcourse.products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

这条语句在功能上也等于上一个例子。

第五章排序检索数据

排序检索数据

本章将讲授如何使用SELECT语句的ORDER BY子句,根据需要排序检索数据。

排序数据

下面SQL语句返回数据库的单列,但输出没有特定的顺序:

mysql> SELECT prod_name
    -> FROM products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.02 sec)

检索出的数据并不是纯粹的随机顺序。如果不排序,数据一般以底层表中出现的顺序显示(数据添加到表的顺序)。但底层表数据顺序会被数据更新或删除影响。所以如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。

子句(clause) SQL语句由子句构成,有些子句必须,而有些子句可选。一个子句通常由一个关键字和所提供的数据组成。

为了明确地排序用SELECT语句检索出的数据,可以使用ORDER BY子句。
ORDER BY子句取一个或多个列的名字,据此对输出进行排序。

mysql> SELECT prod_name
    -> FROM products
    -> ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.01 sec)

这条语句使用ORDER BY语句指示MySQL对prod_name列以字母顺序排序数据,其他与上个例子相同。

  • 通过非选择列进行排序 通常,ORDER BY子句中使用的列是结果显示列。但也可以不使用结果列中的列进行排序。

按多个列排序

我们排序经常需要按不止一个列进行数据排序。例如按名字排序需要先按姓排序后按名排序。

为了按多个列排序,只需指定列名,列名间用逗号分开(同选择多个行那样)。

下面代码检索三个列,并按两个列对结果排序(先按价格排序,后按名称排序)。

mysql> SELECT prod_id, prod_price, prod_name
    -> FROM products
    -> ORDER BY prod_price, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
| FU1     |       3.42 | Fuses          |
| SLING   |       4.49 | Sling          |
| ANV01   |       5.99 | .5 ton anvil   |
| OL1     |       8.99 | Oil can        |
| ANV02   |       9.99 | 1 ton anvil    |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| DTNTR   |      13.00 | Detonator      |
| ANV03   |      14.99 | 2 ton anvil    |
| JP1000  |      35.00 | JetPack 1000   |
| SAFE    |      50.00 | Safe           |
| JP2000  |      55.00 | JetPack 2000   |
+---------+------------+----------------+
14 rows in set (0.00 sec)

注意:按多个列排序时,排序完全按所规定的顺序进行。类似于按顺序成组排序,每次排序都是在组内排序,例如若prod_price没有重复的值(即每行自成一组),那再按prod_name排序将对结果无影响(每组只有一条记录)。


指定排序方向

数据排序不限于升序排序(从A到Z)。这只是默认排序顺序,还可以通过DESC关键词进行降序排序。

按价格以降序排序产品(最贵的在前面):

mysql> SELECT prod_id, prod_price, prod_name
    -> FROM products
    -> ORDER BY prod_price DESC;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| JP2000  |      55.00 | JetPack 2000   |
| SAFE    |      50.00 | Safe           |
| JP1000  |      35.00 | JetPack 1000   |
| ANV03   |      14.99 | 2 ton anvil    |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| ANV02   |       9.99 | 1 ton anvil    |
| OL1     |       8.99 | Oil can        |
| ANV01   |       5.99 | .5 ton anvil   |
| SLING   |       4.49 | Sling          |
| FU1     |       3.42 | Fuses          |
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
+---------+------------+----------------+
14 rows in set (0.00 sec)

对多个列排序时,只需对需要降序的列后添加DESC关键字:

mysql> SELECT prod_id, prod_price, prod_name
    -> FROM products
    -> ORDER BY prod_price DESC, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| JP2000  |      55.00 | JetPack 2000   |
| SAFE    |      50.00 | Safe           |
| JP1000  |      35.00 | JetPack 1000   |
| ANV03   |      14.99 | 2 ton anvil    |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| ANV02   |       9.99 | 1 ton anvil    |
| OL1     |       8.99 | Oil can        |
| ANV01   |       5.99 | .5 ton anvil   |
| SLING   |       4.49 | Sling          |
| FU1     |       3.42 | Fuses          |
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
+---------+------------+----------------+
14 rows in set (0.00 sec)

DESC关键字只应用于位于其前面的列名。上个例子只对prod_price降序,而prod_name还是升序排序。

与DESC相反的关键字是ASC(ASCENDING),可以用它来指定升序排序。但ASC没多大用,因为升序是默认的。

使用ORDER BY和LIMIT的组合,可以找出一个列中最大或最小的值:

mysql> SELECT prod_price
    -> FROM products
    -> ORDER BY prod_price DESC
    -> LIMIT 1;
+------------+
| prod_price |
+------------+
|      55.00 |
+------------+
1 row in set (0.00 sec)

prod_price DESC保证行是从最贵到最便宜,LIMIT 1告诉MySQL返回一行。

  • 在多个列上降序排序 如果想在多个列上降序排序,那需要对每个列都指定DESC关键字。
  • 区分大小写和排序顺序 对文本性的数据进行排序时,一般按字典(dictionary)序,不区分大小写,a等同于A。若想改变排序规则,请联系数据库管理员。
  • ORDER BY子句的位置 在给出ORDER BY子句时,必须保证它在FROM子句后。而LIMIT子句必须位于ORDER BY子句后。顺序不对将产生错误信息。

第六章过滤数据

过滤数据

本章将讲授如何使用SELECT语句的WHERE子句指定搜索条件。

使用WHERE子句

数据库一般包含大量的数据,很少需要检索表中所有的行。通常只会根据需求来提取部分表数据。只检索所需数据需要指定搜索条件(search criteria),搜索条件也称为过滤条件(filter condition)。

在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤。WHERE关键字位于FROM关键字后。

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_price = 2.50;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
2 rows in set (0.01 sec)

这条语句从products表中检索两个列,但不返回所有行,只返回prod_price值为2.50的行。

  • SQL过滤与应用过滤 数据也可以在应用层过滤。让数据库返回大量原始数据,然后在客户机进行遍历检索。但这将会极大影响客户机性能,并且客户机应用将缺乏可伸缩性,再者多余数据导致网络宽带的浪费。
  • WHERE子句的位置 在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE后,否则会产生错误(ORDER BY的使用:MySQL必知必会——第五章排序检索数据)。

WHERE子句操作符

MySQL支持的条件操作符:

操作符说明
=等于
<>、!=不等于
<小于
<=小于等于
>大于
>=大于等于
BETWEEN在指定的两个值间

检查单个值

检索文本值:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_name = 'fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+
1 row in set (0.00 sec)

WHERE prod_name = 'fuses’语句,它返回prod_name的值为Fuses的一行。

列出价格小于10的所有产品:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_price < 10;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| .5 ton anvil  |       5.99 |
| 1 ton anvil   |       9.99 |
| Carrots       |       2.50 |
| Fuses         |       3.42 |
| Oil can       |       8.99 |
| Sling         |       4.49 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
7 rows in set (0.00 sec)

列出价格小于等于10的所有产品:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_price <= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

不匹配检查

列出不是由供应商1003制造的所有产品:

mysql> SELECT vend_id, prod_name
    -> FROM products
    -> WHERE vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1002 | Oil can      |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
+---------+--------------+
7 rows in set (0.01 sec)

这里的 <> 可以替换成 !=

  • 何时使用引号 观察上述例子,会看到有些过滤条件的值括在单引号内(如,‘fuses’)。这里单引号是用来限定字符串的。在MySQL中,将值与字符串类型的列进行比较需要限定引号。而与数值列进行比较的值不用引号。

范围值检查

为了检查某个范围的值,可以使用BETWEEN操作符。其语法与其他操作符略有不同,它需要两个值,范围的开始值与结束值。
检索价格在5和10之间的产品:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_price BETWEEN 5 AND 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Oil can        |       8.99 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

使用BETWEEN时,必须指定它的开始值与结束值。这两个值必须用AND关键字分隔。数据过滤时,开始值与结束值也包括在结果内。

空值检查

在创建表时,可以指定其中的列是否可以不包含值。一个列不包含值时,称其为包含空值NULL。

NULL 无值(no value),它与字段包含0、空字符或仅仅包含空格不同。

SELECT语句有一个特殊的WHERE子句,IS NULL子句,用来检查具有NULL值的列。

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_price IS NULL;
Empty set (0.00 sec)

当要检索的列没有无值的行时,不返回数据。

mysql> SELECT cust_id
    -> FROM customers
    -> WHERE cust_email IS NULL;
+---------+
| cust_id |
+---------+
|   10002 |
|   10005 |
+---------+
2 rows in set (0.01 sec)

这条语句显示没有填写邮箱的顾客。

  • NULL与不匹配 在通过过滤选择出不具有特定值的行时,你可能希望返回具有NULL值的行。但未知具有特殊的含义,数据库不知道它们是否匹配,所有过滤时不会返回具有NULL值的行。

第七章数据过滤

数据过滤

本章讲授如何组合WHERE子句以建立功能更强的更高级的搜索条件。我们还将学习如何使用NOT和IN操作符。

组合WHERE子句
上章(MySQL必知必会——第六章过滤数据)介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了更强的过滤控制,MySQL允许给出多个WHERE子句。使用方式:以AND子句或以OR子句的方式使用。

操作符(operator) 用来联结或改变WHERE子句中的子句的关键字。也称逻辑操作符(logical operator)。

AND操作符

可使用AND操作符给WHERE子句附加条件,从而通过不止一个列进行过滤:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1003 AND prod_price <= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

此SQL语句检索由供应商1003制造且价格小于等于10的所有产品的名称和价格。这条SELECT语句中的WHERE子句包含两个过滤条件,并且用AND关键字联结它们。AND指示MySQL只返回满足所有过滤条件的行。

AND 用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行。

上述例子只包含一个关键字AND的语句,把两个过滤条件组合在一起。还可以添加多个过滤条件,每添加一条就要使用一个AND。

OR操作符

与AND操作符不同的是,IN操作符指示MySQL检索匹配任一条件的行:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

此SQL语句检索由任一个指定供应商(1002、1003)制造的所有产品的产品名和价格。OR操作符告诉MySQL匹配任一条件而不是同时匹配所有条件(如果这里用的是AND,将无结果,因为没有商品同时属于两个供应商)。

OR WHERE子句中使用的关键字,用来表示检索匹配任一给定条件的行。

计算次序

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。但两者结合会带来一些问题,例如,我们需要列出价格10(包含)以上且由1002或1003制造的产品:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
6 rows in set (0.00 sec)

这条语句返回的行中有两行价格小于10,并不符合我们的预期结果。这就是计算次序带来的问题,SQL在处理OR操作符前,优先处理AND操作符。所以,此SQL语句检索的是由供应商1003制造的价格10以上的产品或者由供应商1002制造的产品。

面对计算次序问题,我们可以使用圆括号明确地分组相应的操作符。

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
4 rows in set (0.00 sec)

这条SELECT语句与前一条唯一差别是,这条语句前两个条件用圆括号括了起来。圆括号的优先级比AND和OR操作符高,MySQL首先将圆括号内的OR条件过滤。所以,此SQL语句变成了检索由供应商1002或1003制造的且价格都在10(包含)以上的产品,符合我们的预期。

在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。圆括号可以消除歧义。

IN操作符

圆括号在WHERE子句中还可与IN操作符搭配,IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN的条件清单用圆括号括住,并条件间用逗号分隔。

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id IN (1002, 1003)
    -> ORDER BY prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

此SELECT语句检索供应商1002和1003制造的所以产品。

IN操作符的功能和OR一样,例如以下代码功能与上个例子相同:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003
    -> ORDER BY prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

IN操作符的优点:

在过滤条件较多时,IN操作符的语法更清楚直观。
使用IN时,计算次序更好管理。
IN操作符一般比OR操作符执行更快。
IN最大优点是可以包含其他的SELECT语句,更能动态地建立WHERE子句。
IN WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当。

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能,否定位于它后的任何条件。

NOT WHERE子句中用来否定后跟条件的关键字。

例如,列出除1002和1003之外的所有供应商的产品:

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id NOT IN (1002, 1003)
    -> ORDER BY prod_name;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
5 rows in set (0.00 sec)

此语句中的NOT否定后面的条件,因此,MySQL匹配1002和1003之外供应商的产品。

NOT在复杂的子句中比较实用,例如,与IN操作符联合使用,能方便找出与条件列表不匹配的行。

MySQL中的NOT MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反。

第八章用通配符进行过滤

用通配符进行过滤

本章介绍什么是通配符、任何使用通配符以及怎样使用LIKE操作符进行通配搜索,以便对数据进行复杂过滤。

LIKE操作符

前面介绍的操作符都是针对已知值进行过滤的。但我们如何搜索未知值呢?例如,如何搜索产品名中包含文本anvil的所有产品?简单的比较操作符只能比较整体,针对部分必须使用通配符。利用通配符可创建比较特定数据的搜索模式。

通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件。
通配符本身实际是SQL的WHERE子句中有特殊含义的字符。

在SELECT的子句中使用通配符,必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

谓词 在操作符作为谓词时,操作符不作操作符作用。从技术上说,LIKE是谓词而不是操作符。

百分号(%)通配符

最常使用的通配符是百分号(%)。在搜索模式中,%表示任何字符出现任意次数。
搜索以jet开头的产品:

mysql> SELECT prod_id, prod_name
    -> FROM products
    -> WHERE prod_name LIKE 'jet%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)

此语句使用了搜索模式’jet%’。这条语句检索任意以jet开头的词。%指示MySQL接受jet后的任意字符,不限数量。

区分大小写 根据MySQL的配置方式,搜索可以是区分大小写的。

通配符可在搜索模式中任意位置使用,并且不限数量。
搜索名字中含anvil的产品:

mysql> SELECT prod_id, prod_name
    -> FROM products
    -> WHERE prod_name LIKE '%anvil%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.00 sec)

搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值。

通配符也可以在搜索模式中间出现。
搜索以s开头e结尾的产品:

mysql> SELECT prod_id, prod_name
    -> FROM products
    -> WHERE prod_name LIKE 's%e';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| SAFE    | Safe      |
+---------+-----------+
1 row in set (0.01 sec)

注意,除了一个或多个字符外,%还能不匹配字符。

注意尾空格 尾空格可能会干扰通配符匹配。例如,使用搜索模式’%anvil’匹配以anvil为结尾的词,若以anvil为结尾的词后有多余空格,将不会匹配。解决方法:使用函数去掉首尾空格。
注意NULL %通配符可以匹配任何东西除了NULL。

下划线(_)通配符

另一个有用的通配符是下划线(_)。下划线的用途与%一样,但只匹配单个字符。
例如:

mysql> SELECT prod_id, prod_name
    -> FROM products
    -> WHERE prod_name LIKE '_ ton anvil';
+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)

此WHERE子句中的搜索模式为’_ ton anvil’:第一行中下划线匹配1,第二行下划线匹配2。
下划线与百分号的对比:

mysql> SELECT prod_id, prod_name
    -> FROM products
    -> WHERE prod_name LIKE '% ton anvil';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+
3 rows in set (0.00 sec)

此WHERE子句与上面唯一区别是_换成了%。多了第一行,%匹配了.5两个字符。_与%能匹配0个字符不一样,_总是匹配一个字符,不能多也不能少。

使用通配符的技巧

MySQL通配符很有用。但这是有代价的:通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。以下是一些通配符使用技巧:

  • 不用过度使用通配符。 如果其他操作符能达到相同目的,则不用通配符。
  • 最好不要将通配符用在搜索模式开始处。 把通配符置于搜索模式开始处,搜索起来是最慢的。
  • 注意通配符的位置。 通配符位置不对,可能不会返回
    ————————————————

第九章用正则表达式进行搜索

用正则表达式进行搜索
本章将学习如何在MySQL WHERE子句内使用正则表达式来更好地控制数据过滤。

正则表达式介绍

前两章中使用匹配、比较和通配符查找数据,对于简单的过滤已经足够了。但面对更加复杂的条件,使用正则表达式可能更加方便快捷。

正则表达式是用来匹配文本的特殊的串(字符集合)。

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。

正则表达式用正则表达式语言来建立。与其他语言一样,你需要学习相应的语法和指令。

学习更多内容 正则表达式的内容较多,本章介绍了正则表达式的基础。更多内容需要自己去了解。

使用MySQL正则表达式

MySQL用WHERE子句对正则表达式提供了初步的支持。

仅为正则表达式语言的一个子集 MySQL仅支持多数正则表达式实现的一个很小的子集。

基本字符匹配

检索prod_name列包含文本1000的行:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.03 sec)

除关键字LIKE被REGEXP代替外,这条语句很像LIKE
语句(MySQL必知必会——第八章用通配符进行过滤)。它指示MySQL:REGEXP后所跟的东西作为正则表达式处理。

这个例子看不出正则表达式的优点,让我们再看一个例子:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '.000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

这里使用了正则表达式.000。’.'是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符。

LIKE与REGEXP 在LIKE和REGEXP之间有一个重要的差别。LIKE匹配整个列,而REGEXP在列值内进行匹配。当然,REGEXP可以通过定位符来匹配整个列。
匹配不区分大小写 MySQL中的正则表达式匹配不区分大小写。为区分大小写,可使用BINARY关键字,如WHERE prod_name REGEXP BINARY ‘JetPack .000’。

进行OR匹配

为搜索两个串之一,可以使用|:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000|2000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

语句中使用了正则表达式1000|2000. '|'为正则表达式的OR操作符,它表示匹配其中之一。

两个以上的OR条件 可以给出两个以上的OR条件。例如’1000|2000|3000’将匹配1000或2000或3000.

匹配几个字符之一

我们可以通过指定一组用 [ 和 ] 括起来的字符,来匹配特定的单一字符。

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[123] Ton'
    -> ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

这里使用了正则表达式[123] Ton。[123]定义一组字符,它的意思是匹配1或2或3。

[]是另一种形式的OR语句。而[123] Ton是[1|2|3] Ton的缩写。

有时候带[]是必要的:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1|2|3 Ton'
    -> ORDER BY prod_name;
+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+
5 rows in set (0.00 sec)

这不是预期结果。因为’1|2|3 Ton’匹配的是1或2或3 Ton,而不是1 Ton或2 Ton或3 Ton。所以我们应该用’[1|2|3] Ton’来进行匹配。

字符集合也可以被否定,即,它们将匹配除指定字符外的任何东西。否定字符集,只需在集合开始处放置一个^即可。。

匹配范围

集合可用来定义要匹配的一个或多个字符。例如,匹配数字0到9:[0123456789],我们可以简化这种类型的集合,用-来定义一个范围,例如,匹配数字0到9:[0-9]。也可以[3-6]等等。范围不一定只是数值,例如[a-z]匹配任意字母。

应用:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[1-5] Ton'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

这里使用正则表达式[1-5] Ton,[1-5]定义了个范围,匹配1到5.

匹配特殊字符

正则表达式语言由特殊含义的特殊字符构成,例如,’.’、’[]’、’|’ 和 ‘-’ 等。但我们如何匹配这些特殊字符呢?

搜索包含.字符的值,尝试:

mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '.'
    -> ORDER BY vend_name;
+----------------+
| vend_name      |
+----------------+
| ACME           |
| Anvils R Us    |
| Furball Inc.   |
| Jet Set        |
| Jouets Et Ours |
| LT Supplies    |
+----------------+
6 rows in set (0.01 sec)

这条语句并不符合预期结果,’.’ 匹配任意字符,所以每个行都被检索出来了。

为了匹配特殊字符,必须用\作为前导。\-表示查找-,\.表示查找 ‘.’ 。

mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '\\.'
    -> ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)

这个语句符合预期结果,\.匹配 ‘.’,最后只检索出目标的一行。这个处理我们称作转义(escaping),正则表达式中有特殊意义的字符都需要经过转义才可匹配。

\也可用来引用元字符(具有特殊意义的字符):

元字符 说明
\f 换页
\n 换行
\r 回车
\t 制表
\v 纵向制表
匹配\ 为了匹配反斜杠(\)字符本身,需要使用\\。
\或\? 多数正则表达式实现使用单个反斜杠转义特殊字符。但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)。

匹配字符类

为了方便工作,对一些常用的匹配,我们可以使用预定义的字符集,称为字符类(character class)。

类 说明
[:alnum:] 任意字母和数字(同[a-zA-Z0-9])
[:alpha:] 任意字母(同[a-zA-Z])
[:blank:] 空格和制表(同[\t])
[:cntrl:] ASCⅡ控制字符(ASCⅡ0到31和127)
[:digit:] 任意数字(同[0-9])
[:graph:] 与[:print:]相同,但不包括空格
[:lower:] 任意小写字母(同[a-z])
[:print:] 任意可打印字符
[:punct:] 除[:alnum:]和[:cntrl:]外的字符
[:space:] 包括空格在内的任意空白字符(同[\f\n\r\t\v])
[:upper:] 任意大写字母(同[A-Z])
[:xdigit:] 任意十六进制数字(同[a-fA-F0-9])

匹配多个实例

目前为止的正则表达式都是匹配一次出现,如果存在,就会被检索出。但有时我们需要对匹配的数目进行更强的控制。我们可以利用正则表达式的重复元字符来完成。

重复元字符:

元字符 说明

  • 0个或多个匹配
  • 1个或多个匹配(等于{1,})
    ? 0个或一个匹配(等于{0,1})
    {n} 指定数目的匹配
    {n,} 不少于指定数目的匹配
    {n,m} 匹配数目的范围(m不超过255)
    例子:
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
    -> ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

此语句用了正则表达式\([0-9] sticks?\),其中\(匹配(,[0-9]匹配数字,sticks?匹配stick和sticks(?使s变得可选),\)匹配)。若没有?,stick与sticks的匹配将变得困难。

匹配连在一起的四个数字:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[[:digit:]]{4}'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

此语句用了正则表达式[[:digit:]]{4},其中[:digit:]匹配任意数字,{4}指定前面的字符需出现4次。

正则表达式总是有不止一种方法表示一个表达式,上面例子也可以写成0-90-9。

定位符

目前所学的例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本,我们需要使用定位符。
定位元字符:

元字符 说明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
查找以 . 或数字开头的产品:

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '^[0-9\\.]'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

匹配串的开始。因此[0-9\.]只匹配以 . 或任意数字为串第一个字符的行。

  • ^的双重用途 ^有两种用法。在集合中(用(和)定义),用它来否定集合。集合外,用来指串的开始处。
  • 使REGEXP起类似LIKE的作用 前面所述,LIKE与REGEXP不同在于,LIKE匹配整个串而REGEXP匹配子串。利用定位符,用^开始每个表达式,用$结束每个表达式可以使REGEXP的作用与LIKE一样。
  • 简单的正则表达式测试 可以在不使用数据库表的情况下,用SELECT来测试正则表达式。REGEXP检查总是返回0(不匹配)或1(匹配)。可以用带文字串的REGEXP来测试表达式:SELECT ‘hello’ REGEXP ‘[0-9]’;,这个例子返回0。

    第十章创建计算字段

创建计算字段

本章介绍什么是计算字段,如何创建计算字段以及怎样从应用程序中使用别名引用它们。

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。例如,想在一个字段中显示公司名和地址,但公司名和地址在不同的列中等等。

我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,再让客户机应用程序或报告程序中重新格式化。

这就需要计算字段来实现了。计算字段并不实际存在于数据库表中。它是运行时在SELECT语句内创建的。但只有数据库知道SELECT语句中哪些列是实际的表列,哪些列是计算字段。从客户机角度看,它们没有差别。

字段(field) 基本上与列(column)的意思相同,经常互换使用。但数据库列一般称列,而字段通常用在计算字段的连接上。
客户机与服务器格式 许多转换和格式化工作都可在SQL语句或客户机应用程序上完成,但一般SQL语句比客户机应用程序快很多,因为DBMS就是设计来快速有效地完成这种处理的。

拼接字段

vendors表包含供应商名和位置信息。假设我们要生成一个供应商报表,需要在供应商名中按name(location)的格式列出供应商位置。

这列的信息,分别储存在vend_name列和vend_country列中。此外我们还需要用括号将地址括起来。为了解决问题,我们可以把两个列拼接起来。

拼接(concatenate) 将值联结到一起构成单个值。
MySQL的不同之处 多数DBMS使用+或者||来实现拼接,MySQL则使用Concat()函数来实现。
MySQL的SELECT语句中,可使用Concat()函数来拼接两个列:

mysql> SELECT Concat(vend_name, ' (', vend_country, ')')
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA)                                 |
| Anvils R Us (USA)                          |
| Furball Inc. (USA)                         |
| Jet Set (England)                          |
| Jouets Et Ours (France)                    |
| LT Supplies (USA)                          |
+--------------------------------------------+
6 rows in set (0.03 sec)

Concat()拼接串,即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串,各个串之间用逗号分隔。以上语句拼接4个元素:

存储在vend_name列中的名字。
包含一个空格和一个左圆括号的串。
存储在vend_country列中的国家。
包含一个右圆括号的串。
结果返回包含以上元素的单个列值(计算字段)。

第八章(MySQL必知必会——第八章用通配符进行过滤)中提到的通过删除数据右侧多余空格来整理数据,可以使用MySQL的RTrim()函数来完成:

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------------------------------------------------+
| Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') |
+----------------------------------------------------------+
| ACME (USA)                                               |
| Anvils R Us (USA)                                        |
| Furball Inc. (USA)                                       |
| Jet Set (England)                                        |
| Jouets Et Ours (France)                                  |
| LT Supplies (USA)                                        |
+----------------------------------------------------------+
6 rows in set (0.00 sec)

RTrim()函数去掉值右边的所以空格。

Trim函数 MySQL除了支持RTrim(),还支持LTrim()以及Trim(),去掉右、左以及两端空格。

使用别名

SELECT语句可以拼接字段。但新计算的列并没有名字,它只是一个值。未命名的列不能应用于客户机应用,因为客户机没有办法引用它。

为了解决这个问题,SQL支持列别名。列名(alias)是一个字段或值的替换名。别名用AS关键字赋予。

mysql> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
    -> FROM vendors
    -> ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

此语句除计算字段后跟了AS vend_title其他都与前面一致。它指示了SQL创建一个包含指定计算的名为vend_title的计算字段。任何客户机都可以通过名字来引用这个计算字段。

别名的其他用途 别名还要其他用途。常见的用途包括,实际列名不合法时重新命名,原列名易混淆时扩充它,等等。
导出列 别名有时也称为导出列(derived column),它们代表相同的东西。

执行算术计算

计算字段另一常用用法是,对检索出的数据进行算数运算。

例如,以下是订单号20005中的所有物品:

mysql> SELECT prod_id, quantity, item_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+
4 rows in set (0.01 sec)

item_price列包含订单中每项物品的单价,我们来计算物品的总价(单价*数量):

mysql> SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

结果中的expended_price列是一个计算字段,计算为quantityitem_price,即数量单价。

MySQL支持的基本算数操作符:

操作符 说明


  • / 除
    圆括号可以区分优先顺序。

第十一章使用数据处理函数

使用数据处理函数

本章介绍什么是函数,MySQL支持哪种函数,以及如何使用这些函数。

函数

与绝大多数计算机语言一样,SQL支持利用函数来处理数据。函数一般在数据上执行,它给数据的转换和处理提供了方便。

例如,去掉尾空格的RTrim()函数。

函数没有SQL的可移植性强 能运行在多个系统上的代码称为可移植的(portable)。相对来说,多数SQL语句是可移植的,在SQL实现间有差异时,这些差异通常容易处理。但函数的可移植性不强,每种主要的DBMS的实现都有其他实现不支持的函数。为了代码的可移植性,不赞成使用特殊实现的功能。如果使用函数,应保证做好代码注释,以便能确切地知道所编写SQL代码的含义。

使用函数

大多数SQL实现支持以下类型的函数:

处理文本串(如,删除或填充值,转换值为大小写)的文本函数。
在数值数据上进行算数操作(如,返回绝对值,进行代数运算)的数值函数。
处理日期和时间值并从这些值中提取特定成分(如,返回两个日期差,检查日期有效性等)的日期和时间函数。
返回DBMS正使用的特殊信息(如,返回用户登录信息,检查版本细节)的系统函数。

文本处理函数

上章(MySQL必知必会——第十章创建计算字段)中的RTrim()函数就是一个文本处理函数。我们再来看另一个Upper()函数:

mysql> SELECT vend_name, Upper(vend_name) AS vend_name_upcase
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+
6 rows in set (0.02 sec)

Upper()将文本转换为大写。

常用的文本处理函数:

函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
例如,我们要找联系名为Y.Lie是顾客,但表中数据人为输入错误成Y.Lee的查询
直接查询:

mysql> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE cust_contact = 'Y.Lie';
Empty set (0.01 sec)

很显然,并没有查到,我们来尝试使用Soundex()函数:

mysql> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE Soundex(cust_contact) = Soundex('Y.Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.00 sec)

因为Y.Lie与Y Lee发音相似,所有我们成功的匹配到了数据。

日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。

一般应用程序不使用这种特殊格式,所以日期和时间处理函数在MySQL语言中具有重要作用。

常用的日期和时间处理函数:

函数 说明
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回格式化的日期或时间串
Day() 返回日期的天数部分
DayOfWeek() 对于日期,返回对应的星期几
Hour() 返回时间的小时部分
Minute() 返回时间的分钟部分
Month() 返回日期的月份部分
Now() 返回当前日期和时间
Second() 返回时间的秒部分
Time() 返回日期时间的时间部分
Year() 返回日期的年份部分
MySQL使用的日期格式,不论是插入或更新还是用WHERE子句进行过滤,格式都必须为yyyy-mm-dd。例如,2022年1月20日,格式为2022-01-20。可能有其他的格式,但yyyy-mm-dd排除了多义性,应该首选这个格式。

应该总是使用4位数字的年份 MySQL支持两位数字的年份,将00-69处理为2000-2069,将70-99处理为1970-1999。但为了避免多义性,不使用该方式。
基本日期比较:

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE order_date = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.01 sec)

此SELECT语句正常运行。它检索出一个order_date为2005-09-01的订单记录。

但使用WHERE order_date = '2005-09-01’并不可靠,order_date的数据类型为datetime,这种类型存储日期和时间值。样例中的时间值全部为00:00:00,但现实中不总是。如果order_date的时间值不为00:00:00,那WHERE order_date = '2005-09-01’将匹配失败。
我们可以使用Date()函数将日期值取出进行对比:

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

这种日期比较更为可靠。除等值操作符外,其他操作符(MySQL必知必会——第六章过滤数据)也可以用于日期时间类。

如果需要的是日期,请使用Date() 如果仅想要日期,使用Date()是一个良好的习惯。如果想要时间,则使用Time()。
检索2005年9月的所有订单:

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

BETWEEN操作符把2005-09-01和2005-09-30定义为一个要匹配的范围值。

还有一种不需要记住每月有多少天或不用操心闰年2月的办法:

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

Year()从日期中返回年份,Month()从日期中返回月份。

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算,因此使用频率不是很高。

讽刺的是,在主要的DBMS的函数中,数值函数是最一致最统一的函数。

常用的数值处理函数:

函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

第十二章汇总数据

汇总数据

本章介绍什么是SQL的聚集函数以及如何利用它们汇总表的数据。

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数。通过这些函数,MySQL查询可用于检索数据,以便分析和报表生成。

常见类型:

确定表中行数(或满足某个条件或包含某个特定值的行数)。
获得表中行组的和。
找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。
这些都是对表中数据(而不是实际数据本身)汇总。故,返回实际数据是对时间和处理资源的一种浪费。

MySQL给出了5个聚集函数:

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列之和
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
标准偏差 MySQL还支持一系列的标准偏差聚集函数。

AVG()函数

AVG()通过对表中行数计数并计算特定列值之和,求得该列的平均值。

返回products表中所有产品的平均价格:

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.02 sec)

此SELECT语句返回值avg_price,它包含products表中所有产品的平均价格。avg_price是一个别名(MySQL必知必会——第十章创建计算字段)。

AVG()也可以用来确定特定列或行的平均值:

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM products
    -> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.01 sec)

此SELECT语句包含了WHERE子句,仅过滤出vend_id为1003的产品,所以avg_price仅返回供应商1003的产品的平均值。

只用于单个列 AVG()只能确定特定数值列的平均值,且列名必须作为参数。
NULL值 AVG()函数忽略列值为NULL的行。

COUNT()函数

COUNT()函数进行计数。可用COUNT()确定表中行的数目或符合特定条件的行的数目。

使用COUNT()函数的两种方式:

使用COUNT(*)对表中行的数目进行计数,包含NULL值列。
使用COUNT(column)对特定列中具有值的行就行计数,忽略NULL值。
返回customers表中客户的总数:

mysql> SELECT COUNT(*) AS num_cust
    -> FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

此语句利用COUNT(*)对所有行计数。

对具有邮件地址的客户计数:

mysql> SELECT COUNT(cust_email) AS num_cust
    -> FROM customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

这条SELECT语句使用COUNT(cust_email)对cust_email列中有值的行进行计数。

NULL值 如果指定列名,则指定列的值为NULL的行将被忽略,但如果函数用*号,则不忽略。

MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名。

返回products表中最贵的物品价格:

mysql> SELECT MAX(prod_price) AS max_price
    -> FROM products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+
1 row in set (0.00 sec)

对非数值数据使用MAX() MAX()一般用来找出最大的数值或日期值,但MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。用于文本数据时,返回最后一行。
NULL值 MAX()函数忽略列值为NULL的行。

MIN()函数

MIN()返回指定列的最小值。MIN()要求指定列名。

返回products表中最便宜的物品价格:

mysql> SELECT MIN(prod_price) AS min_price
    -> FROM products;
+-----------+
| min_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.00 sec)

对非数值数据使用MAX() MIN()与MAX()类似,MySQL允许将它用来返回任意列中的最小值,包括返回文本列中的最小值。用于文本数据时,返回最前面一行。
NULL值 MIN()函数忽略列值为NULL的行。

SUM()函数

SUM()返回指定列值的和(总计)。

检索订单号20005所订购物品的总数(所有quantity值之和):

mysql> SELECT SUM(quantity) AS items_ordered
    -> FROM orderitems
    -> WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.00 sec)

SUM()也可以合计计算值,计算总订单金额(item_price*quantity):

mysql> SELECT SUM(item_price*quantity) AS total_price
    -> FROM orderitems
    -> WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+
1 row in set (0.00 sec)

在多个列上进行计算 利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算。
NULL值 SUM()函数忽略列值为NULL的行。

聚集不同值

以上聚集函数都可以如下使用:

对所有行执行计算,指定ALL参数或不给参数(ALL为默认)。
只包含不同的值,指定DISTINCT参数。
使用DISTINCT参数检索特定供应商提供的产品的平均价格:

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
    -> FROM products
    -> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)

使用DISTINCT后,avg_price比第一个例子更高,因为多个物品具有相同的价格,排除它们提升了平均价格。

ALL为默认 ALL参数不需要指定,因为它是默认行为。
注意 DISTINCT不能用于COUNT(*),否则会产生错误。即,DISTINCT必须使用列名,不能用于计算或表达式。
将DISTINCT用于MIN()和MAX() 虽然DISTINCT可以用于MIN()和MAX(),但没有价值,最大最小值只有一个。

组合聚集函数

SELECT语句可根据需要包含多个聚集函数:

mysql> SELECT COUNT(*) AS num_items,
              MIN(prod_price) AS price_min,
              MAX(prod_price) AS price_max, 
              AVG(prod_price) AS price_avg
    -> FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

这里用单条SELECT语句执行4个聚集计算。

取别名 在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名。唯一的名字使你的代码更易于理解和使用。

最后修改:2022 年 01 月 22 日
如果觉得我的文章对你有用,请随意赞赏