MySQL必知必会第一章~第八章学习笔记

Chapter1 了解SQL

1.1 数据库基础

学习数据库的良好切入点就是一张最重要的数据库术语清单，并加以说明。

1.1.1 什么是数据库

数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）

误用导致混淆 人们通常用数据库这个术语来指代他们使用的数据库软件，这是不正确的。准确的说，数据库软件应称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。所以访问数据库，都是通过使用DBMS来访问数据库。

1.1.2 表

表 保存某种特定类型数据的结构化清单

表名 同一数据库中的每个表的名字都是唯一的，即在不同的数据库中可以使用相同的表名，表名的唯一性可以取决于++数据库名+表名++的结合。

模式（schema） 描述关于数据库和表的布局（如何存储等）及特性的信息

1.1.3 列和数据类型

列（column） 表中的一个字段，并且具有相应的数据类型，所有表都是由一个或多个列组成的

表中的某一列存储着某一特定信息，如学生表的’age’列存储着学生的年龄这一特定信息。

数据类型（datatype） 每个列都有相应的数据类型，限制（或容许）该列中存储的数据

1.1.4 行

行（row） 表中的一个记录，表中的数据是按行存储的

行和记录 很多人会将表中的行（row）称为数据库记录（record），在很大程度上，这两个术语是可以等价的，但是从技术上说，行才是正确的术语。

1.1.5 主键

主键（primary key） 表中的一列或一组列，其值能够唯一标识表中每个行

大多数数据库设计人员在创建表时应保证建立一个主键，以便于以后的数据操纵和管理。

表中的任何列满足以下条件，就可以作为主键：

• 任意两行都不具有相同的主键值
• 每个行都必须具有一个主键值（主键列不允许NULL值）

如果是一组列的组合作为主键，那么这组列值组合必须是唯一的，其中单个列的值可以不唯一

主键的最好习惯

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

1.2 什么是SQL

SQL是结构化查询语言的缩写，是一种专门用来与数据库通信的语言。

SQL优点：

• 几乎所有重要的DBMS都支持SQL
• SQL简单易学
• SQL是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

注意的一点 虽然SQL是试图被定义成可供所有DBMS使用的语言语法，但是实际上任意两个DBMS实现的SQL都不完全相同，也就是说SQL语法不是完全可移植的

1.3 动手实践

附录A

附录B

1.4 小结

这一章主要学习了一些基本数据库术语。

Chapter2 MySQL简介

2.1 什么是MySQL

数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS(数据库管理系统)完成的，MySQL就是一种DBMS，一种++数据库软件++。

MySQL广泛应用的原因有以下几点：

• 成本低，MySQL免费开放源代码，可以免费使用
• 性能，MySQL执行很快
• 可信赖，高声望公司背书
• 简单，容易安装和使用

2.1.1 客户机-服务器软件

DBMS分为两类：

• 基于共享文件系统的DBMS，用于桌面用途，通常不用于高端或更关键的应用，如Microsoft Acess和FileMaker。
• 基于客户机-服务器的DBMS，如MySQL、Oracle以及Microsoft SQL Sever等数据库。

基于客户机-服务器的数据库

• 服务器部分是负责所有数据访问和处理的一个软件，这个软件运行在称为数据库服务器的计算机上，负责和数据打交道
• 客户机部分是与用户打交道的软件，用户通过客户机软件向服务器软件提出数据处理需求，服务器软件处理这个请求，并将结果通过网络返回到客户机软件
• 客户机软件和服务器软件可能安装在两台计算机或一台计算机上，甭管怎样，它们之间都需要进行通信

MySQL

• 服务器软件为MySQL DBMS，可以在本地安装的副本上运行，也可以连接到运行在具有访问权限的远程服务器上的副本。
• 客户机可以是MySQL提供的工具、也可以是脚本语言（Perl）、Web应用开发语言（ColdFusion等）等。

2.1.2 MySQL版本

使用4.1或更高版本

2.2 MySQL工具

MySQL是一个客户机-服务器DBMS，为了使用MySQL需要有一个客户机，客户机应用的选择有很多，但是在学习MySQL时，最好使用专门用途的实用程序，特别是以下3个工具。

2.2.1 mysql命令行实用程序

windows环境下用命令窗口连接mysql，需要先配置环境变量path，值为mysql安装目录/bin。

配置好环境变量后，在cmd窗口输入如下命令会出现以下提示：

C:\Users\Administrator>mysql -u root -h localhost -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> 之后；
• 命令用;或\g结束；
• 输入help或\h获得帮助；
• 输入quit或exit退出命令行使用程序。

2.2.2 MySQL Administrator

MySQL Administrator（MySQL管理器）是一个图形交互客户机，用来简化MySQL服务器的管理。

MySQL Administrator不作为核心MySQL的组成部分安装，必须从http://dev.mysql.com/downloads/下载。

2.2.3 MySQL Query Browser

MySQL Query Browser作为一个图形交互客户机，用来编写和执行MySQL命令。

MySQL Query Browser也同样必须从http://dev.mysql.com/downloads/下载。

MySQL Query Browser要求输入服务器和登录信息，然后显示应用界面。

2.3 小结

本章介绍了什么是MySQL，以及引入了几个客户机实用程序。

Chapter3 使用MySQL

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

3.1 连接

MySQL在内部保存自己的用户列表，并且把每个用户与各种权限关联起来。

连接到MySQL，需要以下信息：

• 主机名（计算机名）——如果连接到本地MySQL服务器，为localhost
• 端口（默认端口是3306）
• 用户名
• 用户口令

在连接之后，你可以访问你的登录名能够访问的任意数据库和表。

3.2 选择数据库

在连接到MySQL之后，需要先选择一个数据库，才能执行数据库操作，为此，可以使用USE关键字。

关键字（key word） 作为MySQL语言组成部分的一个保留字，决不要用关键字命名一个表或列。

首先，使用如下命令，新建一个crashcourse数据库:

mysql> CREATE SCHEMA IF NOT EXISTS crashcourse;
Query OK, 1 row affected (0.13 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| crashcourse        |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

然后，输入以下命令来使用crashcourse数据库：

mysql> USE crashcourse;
Database changed

USE语句没有返回任何结果，但是会显示出Database changed的消息，表示数据库选择成功。

3.3 了解数据库和表

数据库、表、列、用户、权限等信息被存储在数据库内部表中。一般这些表不能直接访问，可用MySQL的SHOW命令来显示这些信息（MySQL从内部表中提取这些信息）：

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| crashcourse        |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.34 sec)

SHOW databases;返回可用数据库的一个列表。

如果要获得一个数据库内所有表，使用以下命令：

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers             |
| orderitems            |
| orders                |
| productnotes          |
| products              |
| vendors               |
+-----------------------+
6 rows in set (0.00 sec)

SHOW TABLES;返回当前选择的数据库内可用表的列表。

SHOW命令也可以用来显示表列。

mysql> SHOW COLUMNS FROM customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | 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.10 sec)

SHOW COLUMNS;返回指定表名的所有字段信息（字段名、数据类型、是否允许为NULL等）。

自动增量（auto_increment） 某些表列需要唯一值，如订单编号、顾客ID等，在每个航添加到表中时，MySQL会自动地为每个行分配下一个可用编号，不用再手动分配唯一值。

DESCRIBE语句 DESCRIBE customers;是SHOW COLUMNS FROM customers;的一种快捷方式

mysql> DESCRIBE customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | 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.00 sec)

MySQL支持的SHOW语句还有：

• SHOW STATUS; 显示广泛的服务器状态信息
• SHOW CREATE DATABASE和SHOW CREATE TABLE; 分别用来显示创建特定数据库或表
• SHOW GRANTS; 显示授予用户（所有用户或特定用户）的安全权限
• SHOW ERRORS和SHOW WARNINGS; 显示服务器错误或警告信息

3.4 小结

本章介绍了如何连接和登录MySQL，如何使用USE选择数据库，如何用SHOW命令查看数据库、表和内部信息。

Chapter4 检索数据

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

4.1 SELECT语句

SELECT语句的用途是从一个或多个表中检索信息，为了使用其检索数据，必须至少给出两条信息——想选择什么、以及从什么地方选择。

4.2 检索单个列

从products表中检索一个名为prod_name的列：

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)

未排序数据 如果读者实验这个查询，得到的显示结果可能与上面展示的数据存在顺序上的差异，这是正常的。如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义，只要返回相同的行数，就是正常的。

结束SQL语句 多条SQL语句必须以分号(;)分隔。不管是多条SQL语句还是单条SQL语句，加上分号肯定没有坏处。

SQL语句和大小写 请注意，SQL语句不区分大小写，即SELECT可以写成select或者Select，都是一样的。但是为了代码更易于阅读和调试，开发人员一般都喜欢对所有SQL关键字大写，而对所有表名和列使用小写。另外要注意的是，使用数据库名、表名、列名时保持一致，这是最佳方式。

使用空格 在处理SQL语句时，空格都会被忽略。所以将很长的SQL语句分成多行更容易阅读和调试。

4.3 检索多个列

检索多个列时，必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。

当心逗号 选择多个列时，一定要在列名之间加上逗号，除了最后一个列名之后不用加。

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)

数据表示 SQL语句一般返回原始的、无格式的数据。

4.4 检索所有列

除了指定需要检索的列之外，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)

使用通配符 一般，除非确实需要表中的每个列，否则最好别使用*通配符，因为检索不需要的列通常会降低检索和应用程序的性能。

检索未知列 如果不明确表中的列名，那么使用通配符*就可以检索出表中名字未知的列。

4.5 检索不同的行

根据前面的例子，SELECT语句会返回所有匹配的行，但是如果不想要相同的值重复出现，怎么办？先看下面第一个结果：

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.02 sec)

SELECT语句返回14行，但是其实只有4个供应商，因为products表中列出了14个产品，而1个供应商可以有多个产品。那么，如何检索出没有重复值的列表呢？解决办法是DISTINCT关键字，指示MySQL只返回不同的值。

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

不能部分使用DISTINCT DISTINCT关键字应用于所有列，而不是仅针对后面紧跟的那个列。

4.6 限制结果

正常情况下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)

上述语句中的LIMIT 5指示MySQL返回匹配结果的前5行（或者说不多于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行，第一个数为开始位置，第二个数为要检索的行数。

行0 SELECT语句匹配的结果，第一行被称为行0，即行索引是从0开始的。所以，LIMIT 1,1从行1开始返回1行，即返回的是行1，也就是匹配结果中的第二行。

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.00 sec)

mysql> SELECT prod_name FROM products LIMIT 1,1; #返回上面结果中的第二行
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
+-------------+
1 row in set (0.00 sec)

行数不够时 LIMIT中指定要返回的行数是返回的最大行数，因为可能存在行数不够的情形。比如，一共只有10行，但是LIMIT 13，超出检索到的行数，MySQL只会返回10行。

MySQL 5的LIMIT语法 LIMIT 3,4代表从行3开始的4行，如果不常用的话可能会混淆。因此，MySQL 5及其之后的版本支持LIMIT的另一种替代语法，LIMIT 4 OFFSET 3，表示从行3开始取4行，等价于LIMIT 3,4。

4.7 使用完全限定的表名

之前的例子都是通过单一指定列名来检索，在实际应用场景下，可能需要使用完全限定的名字（同时使用表名和列名）来引用列：

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.00 sec)

同样的，表名也可以是完全限定的。

mysql> SELECT products.prod_name FROM crashcourse.products;

4.8 小结

本章学习了如何使用SELECT语句来检索单个表列、多个表列以及所有列。

Chapter5 排序检索数据

本章讲述使用SELECT语句的ORDER BY子句，根据需要排序检索出的数据。

5.1 排序数据

检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示，这可以是数据最初添加到表中的顺序，但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。关系数据库设计理论认为，如果不明确规定排序顺序，则不检索出的数据的顺序应该是没有意义的。

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

为了对SELECT语句检索出的数据作明确地排序，可使用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.00 sec)

上面这个示例指示MySQL对prod_name列以字母顺序排序数据。

通过非选择列进行排序 通常，ORDER BY子句中使用的列为被检索的列，但是，实际上用非检索的列排序数据也是可以的。比如，检索A列并按B列的数据排序。

5.2 按多个列排序

按多个列排序，只要指定列名，列名之间用逗号分开即可：

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)

重要的是理解在按多个列排序时，排序按所规定的列顺序进行。即，对于上面的例子，MySQL先按列prod_price排序，当且仅当存在多个行具有相同的prod_price时，会接着按prod_name进行排序。如果prod_price的值都是唯一的，那么不会再按prod_name排序。

5.3 指定排序方向

数据排序默认是升序排序，ORDER BY子句使用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)

但是，如果打算用多个列排序怎么办？下面的例子以降序排序产品(最贵的在最前面)，然后再对产品名进行排序：

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列指定DESC，因此，prod_price列以降序排列，而prod_name列（在相同价格下）仍按默认的升序排列。

多个列上降序排序 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

与DESC相反的关键字是ASC(ASCENDING)，默认的排序顺序就是升序，所以一般采取升序排序时，不需要特别声明这个关键字。

排序顺序是否区分大小写 A与a在排序时相同么？a在B之后么？这个答案取决于数据库如何设置。

使用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)

ORDER BY子句的位置 需要使用多个子句时，需要注意子句的次序，次序不对将产生错误。ORDER BY子句在FROM子句之后，而LIMIT子句必须位于ORDER BY之后。

5.4 小结

本章学习了如何使用ORDER BY子句对检索结果进行排序。这个子句必须是SELECT语句的最后一条子句，可根据需要，在一个列或多个列上对数据进行排序。

Chapter6 过滤数据

本章将讲述如何使用WHERE子句指定搜索条件。

6.1 使用WHERE子句

通常，只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤，WHERE子句在表名之后给出：

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.00 sec)

SQL过滤和应用过滤 数据也可以在应用层过滤，也就是说客户机在收到SELECT语句返回的检索数据后，可以根据需要提取出行。但是，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这样导致网络带宽的浪费，同时，让客户机应用处理数据库的工作会极大地影响应用的性能。

WHERE子句的位置 同时使用ORDER BY子句和WHERE子句时，ORDER BY子句在WHERE子句之后。

6.2 WHERE子句操作符

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

6.2.1 检查单个值

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)

上例检索prod_name = 'fuses'的行，MySQL在执行匹配时默认不区分大小写，所以fuses和Fuses匹配。

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的所有行（产品）。

6.2.2 不匹配检查

下面检索出不是由供应商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.00 sec)

何时使用引号 仔细观察上面几个例子，在搜索条件是字符串的时候，需要用单引号限定，而是数值的时候，不需要引号。

6.2.3 范围值检查

检查某个范围的值，可以使用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分隔。

6.2.4 空值检查

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

NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

SELECT语句有一个特殊的IS NULL子句来检查具有NULL值的列：

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

上例是检索出没有电子邮箱地址的顾客。

IS NULL子句的反面是IS NOT NULL。

NULL与不匹配 在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行，但是，因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤时或者不匹配过滤时不返回它们。因此在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL值的行。

6.3 小结

本章介绍了如何使用WHERE子句过滤返回的数据。

Chapter7 数据过滤

本章讲授如何组合WHERE子句以建立更高级的搜索条件，并学习如何使用NOT和IN操作符。

7.1 组合WHERE子句

上一章介绍的所有WHERE子句在过滤数据时使用的都是单一的条件，许多实际情形下需要更强的过滤控制，MySQL允许给出多个WHERE子句，这些子句以AND子句或者OR子句的方式使用。

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

7.1.1 AND操作符

通过AND操作符给WHERE子句附加条件：

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

上例中WHERE子句包含两个条件，供应商1003且价格小于等于10美元，用AND关键字联结它们。

AND 用来指示检索满足所有给定条件的行。

上述例子中只是用了一个AND关键字，还可以添加多个过滤条件，每添加一条就要使用一个AND。

7.1.2 OR操作符

OR操作符与AND操作符不同，它指示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操作符只需要满足任一条件。

OR 用来表示检索匹配任一给定条件的行。

7.1.3 计算次序

WHERE可包含任意数目的AND和OR操作符，允许两者结合以进行复杂和高级的过滤。但是，两者同时出现时，优先级顺序是什么样的？看下下面的例子：

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遇到上面的WHERE子句时，它理解为由供应商1003制造的任何价格10美元以上的产品，或者供应商1002制造的任何产品，而不管其价格如何。也就是说，AND在计算次序中的优先级更高。

上述问题最好的解决办法就是使用圆括号明确地分组相应的操作符：

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)

圆括号具有较AND或OR操作符更高的计算次序，DBMS首先过滤圆括号内的OR条件，这时，SQL语句变成了选择由供应商1002或1003制造的且价格都在10美元（含）以上的任何产品。

在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。使用圆括号没有什么坏处，它能消除歧义。

7.2 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)

IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。

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子句。

7.3 NOT操作符

NOT WHERE子句中的NOT有且只有一个功能，用来否定它之后所跟的任何条件。

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)

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

7.4 小结

本章讲授如何使用AND和OR操作符组合成WHERE子句，并且如何明确地管理计算次序，以及如何使用IN和NOT操作符。

Chapter8 用通配符进行过滤

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

8.1 LIKE操作符

前面介绍的所有操作符都是针对已知值进行过滤的，那么在未知情况下该如何过滤，比如怎样搜索产品名中包含文本anvil的所有产品？这个时候需要通配符来创建比较特定数据的搜索模式。

通配符（wildcard） 用来匹配值的一部分的特殊字符。

搜索模式（search pattern） 由字面值、通配符或两者组合构成的搜索条件。

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

谓词 操作符何时不是操作符？答案是在它作为谓词（predicate）时，从技术上来说，LIKE时谓词而不是操作符，虽然最终的结果是相同的，但是应该对此术语有所了解。

8.1.1 百分号（%）通配符

在搜索串中，%表示任何字符出现的任意次数。例如，为了找出所有以词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)

==%==指示MySQL接受jet之后的任意字符，不管它有多少字符。

区分大小写 根据MySQL配置方式，搜索是可以区分大小写的，如果区分大小写，'jet%'与JetPack 1000将不匹配。

通配符可以在搜索模式中的任意位置使用，并且可以使用多个通配符。

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.00 sec)

%代表搜索模式中给定位置的0个、1个或多个字符。

注意尾空格 尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果该词后面有一个或多个空格，则子句LIKE ‘%anvil’不会匹配它。解决这个问题的一个简单办法 时在搜索模式最后附加一个%，即’%anvil%’。一个更好的办法时使用函数去掉首尾空格。

注意NULL %通配符不可以匹配NULL，即使是WHERE prod_name LIKE '%'也不能匹配产品名值为NULL的行。

8.1.2 下划线（_）通配符

下划线只能匹配单个字符。

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)

8.2 使用通配符技巧

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

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，除非绝对有必要，否则不要把他们用在搜索模式的开始处（言下之意是，先检索明确的搜索模式，然后在匹配的行中应用通配符搜索模式）。
• 仔细注意通配符的位置。

8.3 小结

本章介绍了什么是通配符以及如何在WHERE子句中使用SQL通配符，并且还说明了通配符不应该过度使用。