mysql portable 命令_MySQL必知必会

最新推荐文章于 2022-07-04 15:40:14 发布

尛松鼠

最新推荐文章于 2022-07-04 15:40:14 发布

阅读量415

点赞数

文章标签： mysql portable 命令

本文链接：https://blog.csdn.net/weixin_32532029/article/details/113310215

版权

第1章　　了解数据库

1.1　数据库基础

1.1.1　什么是数据库

数据库是一个以某种有组织的方式存储的数据集合。

数据库(databases)：保存有组织的数据的容器(通常是一个文件或一组文件)。

容易混淆：数据库软件应称为DBMS(数据库管理系统)。数据库是通过DBMS创建个操纵的容器。你使用的是DBMS，它替你访问数据库。

1.1.2　表

表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录，或者其他信息清单。

表(table)：某种特定类型数据的结构化清单。

关键：存储在表中的数据是一种类型的数据或一个清单。绝不应该将顾客的清单与订单的清单存储在同一个数据库表中。这样做将使以后的检索和访问很困难。应该创建两个表，每个清单一个表。

数据库中的每个表都有一个名字，用来表示自己。此名字是唯一的，着表示数据库中没有其他表具有相同的名字。

表名：表名的唯一性取决于多个因素，如数据库名和表名等的结合。这表示，虽然在相同数据库中不能两次使用相同的表名，但在不同的数据库中却可以使用相同的表名。

表具有一些特性，这些特性定义了数据在表中如何存储，如可以存储什么样的数据，数据如何分解，各部分信息如何命名，等等。描述表的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及整个数据(和其中表的关系)。

模式(scheme)：关于数据库和表的布局及特性的信息。

1.1.3　列和数据类型

表由列组成，列中存储着表中某部分的信息。

列(column)：表中的一个字段。所有表都是由一个或多个列组成的。

理解列的最好办法是将数据库想象为一个网格。网格中每一列存储着一条特定的信息。例如，在顾客表中，一个列存储着顾客编号，另一个存储着顾客名，二地址、城市、州以及邮政编码全都存储在各自的列中。

分解数据：正确地将数据分解为多个列几位重要。例如，城市、州、编码应该总是独立的列。通过把它分解开，才有可能利用特定的列对数据进行排序和过滤(如，找出特定州或特定城市的所有顾客)。如果城市和州组合在一个列中，则按州进行排序或过滤会很困难。

数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。例如，如果劣种存储的为数字(或是订单中的物品数)，则相应的数据类型应该为数值类型。如果勒种存储的是日期、文本、注释、金额等，则应该用桥当的数据类型规定出来。

数据类型(datatype)：所容许的数据的类型。每个表列都有相应的数据类型，它限制(或容许)该列中存储的数据。

数据类型限制可存储在勒种的数据种类(例如，防止在数值字段中录入字符值)。数据类型还帮助正确地排序数据，并在优化磁盘使用方面起重要的作用/因此，在创建表时必须对数据类型给予特别的关注。

1.1.4　行

表中的数据时按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，哇和你哥哥中垂直的列为表列，水平行为表行。

例如，顾客表可以每行存储一个顾客。表中的行数为记录的总数。

行(row)：表中的一个记录。

1.1.5　主键

表中每一行都应该由可以唯一标识自己的一列(或一组列)。一个顾客表可以使用顾客编号列，而订单表可以使用订单ID，雇员表可以使用雇员ID或雇员社会保险号。

主键(primary key)：一列(或一组列)，其值能够唯一区分表中每个行。

唯一标识表中每行的这个列(或这组列)称为主键，主键用来表示一个特定的行。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。

应该总是定义主键：虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。

表中的任何列都可以作为主键，只要它满足以下条件：

1、任意两行都不具有相同的主键值；

2、每个行都必须具有一个主键值(主键列不允许NULL值)。

主键通常定义在表的一列上，但这并不是必需的，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须时唯一的(但单个列的值可以不唯一)。

主键的最好习惯：

1、不更新主键列中的值；

2、不重用主键列的值；

3、不在主键列中使用可能会更改的值。(例如，如果使用一个名字作为主键以标识某个供应商，当该供应商个兵和更改其名字时，必须更改这个主键。)

还有一种非常重要的键，称为外键。

1.2　什么是SQL

SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。

与其他语言(如，英语以及Java和Visual Basic这样的程序设计语言)不一样，SQL由很少的词构成，这是有意而为的。设计SQL的目的是很好地完成一项任务，即提供一种从数据库中读写数据的简单有效的办法。

第2章　　MySQL简介

2.1　什么是MySQL

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

2.1.1　客户机-服务器软件

DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机-服务器的DBMS。前者(包括诸如Microsoft Access和FileMaker)用于桌面用途，通常不用于高端或更关键的应用。

MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机-服务器的数据库。客户机-服务器应用分为两个不同的部分。服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机上。

与数据文件打交道的只有服务器软件。关于数据、数据添加、删除和数据更新的所有请求都由服务器软件完成。这些请求或更改来自运行客户机软件的计算机。客户机是与用户打交道的软件。例如，如果你请求一个按字母顺序列出的产品表，则客户机软件通过网络提交该请求给服务器软件。服务器软件处理这个请求，根据需要过滤、丢弃和排序数据，然后把结果送回到你的客户机软件。

客户机和服务器软件可能安装在两台计算机或一台计算机上。不管它们在不在相同的计算机上，为进行所有数据库交互，客户机软件都要与服务器软件进行通信。

服务器软件为MySQL DBMS。你可以在本地安装的副本上运行，也可以连接到运行在你具有访问权的远程服务器上的一个副本。

客户机可以是MySQL提供的工具、脚本语言(如Perl)、Web应用开发语言(如ASP、ColdFusion、JSP和PHP)、程序应用语言(如C、C++、Java)等。

2.1.2　MySQL版本

使用最新的MySQL版本。

2.2　MySQL工具

2.2.1　mysql命令行实用程序

每个MySQL安装都有一个名为mysql的简单命令行实用程序。这个实用程序没有下拉菜单、流行的用户界面、鼠标支持或任何类似的东西。

在操作系统命令提示符下输入：mysql -u root -p 后，输入密码登录。

命令输入在mysql>之后；

命令用;或\g结束，仅按Enter不执行命令；

输入help或\h获得帮助，也可以输入更多的文本获得特定命令的帮助(如，输入help select获得使用SELECT语句的帮助)；

输入quit或exit退出命令行实用程序。

2.2.2　使用Navicat

第3章　　使用MySQL

3.1　连接

MySQL与所有客户机-服务器DBMS一样，要求在能执行命令之前登录到DBMS。登录名可以与网络登录名不相同(假定你使用网络)。MySQL在内部保存自己的用户列表，并且把每个用户与各种权限关联起来。

主机名(计算机名)——如果连接到本地MySQL服务器，为localhost；

端口(如果使用默认端口3306之外的端口)；

一个合法的用户名；

用户口令(如果需要)。

3.2　选择数据库

创建数据库：

CREATE DATABASE crashcourse;

在crashcourse数据库中运行create.sql和populate.sql(下载资料：https://files.cnblogs.com/files/Lamfai/mysql_scripts.zip)。

删除数据库使用DROP DATABASE crashcourse;

在你能执行任意数据库操作前，需要悬着一个数据库，为此可是要USE关键字。必须先使用USE打开数据库，才能读取其中的数据。

选择数据库：

USE crashcourse;

关键字(key word)：作为MySQL语言组成部分的一个保留字。绝不要用关键字命令一个表或列。MySQL关键字参照。

3.3　了解数据库和表

数据库、表、列、用户、权限等的信息被存储在数据库和表中，可用MySQL的SHOW命令来显示这些信息(MySQL从内部表中提取这些信息)。

输入：

SHOW DATABASES;

输出：

分析：SHOW DATA;返回可用数据库的一个列表。包含在这个列表中的可能是MySQL内部使用的数据库(如例子中的mysql和information_schema)。

为了获得一个数据库内的表的列表，使用SHOW TABLES;

输入：

SHOW TABLES;

输出：

分析：SHOW TABLES；返回当前选择的数据库内可用表的列表。SHOW也可以用来显示表列。

输入：

SHOW COLUMNS

FROM customers;

输出：

分析：SHOW COLUMNS要求给出一个表名(这个例子中的FROM customers)，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息(如字段cust_id的auto_increment)。

自动变量：某些表列需要唯一值。例如，订单编号、雇员ID或顾客ID。在每个行添加到表中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值(这样做必须记住最后一次使用的值)。这个功能就是所谓的自动增值。如果需要它，则必须在用CREATE语句创建表时把它作为表定义的组成部分。

DESCRIBE语句：MySQL支持用DESCRIBE作为SHOW COLUMNS FROM 的一种快捷方式。换句话说，DESCRIBE customers;是SHOW COLUMNS FROM customers;的一种快捷方式。

SHOW ATATUS：用于显示广泛的服务器状态信息；

SHOW CREATE DATABASE crashcourse和SHOW CREATE TABLE products，分别用来显示创建特定数据库或表的MySQL语句；

SHOW GRANTS，用来显示授予用户(所有用户或特定用户)的安全权限；

SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。

第4章　　检索数据

4.1　SELECT语句

SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。大概，最经常使用的SQL语句就是SELECT语句了。它的用途是从一个或多个表中检索信息。

为了使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。

4.2　检索单个列

输入：

SELECT prod_name

FROM products;

输出：

分析：上述语句利用SELECT语句从products表中检索一个名为prod_name的列。所需的列名在SELECT关键字之后给出，FROM关键字支出从其中检索数据的表名。

未排列数据：如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义。返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的。

结束SQL语句：多条SQL语句必须以分号(;)分割。MySQL如同多数DBMS一样，不需要再单条SQL语句后加分号。但特定的DBMS可能必须在单条SQL语句后加上分号。当然，如果愿意可以总是加上分号。事实上，即使不一定需要，但加上分号可能没有坏处。如果你使用的是mysql命令行，必须加上分号来结束SQL语句。

SQL语句和大小写：请注意，SQL语句不区分大小写，因此SELECT与select是相同的。同样，写成Select也没有关系。建议对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。

使用空格：在处理SQL语句时，其中所有空格都被忽略。SQL语句可以在一行上给出，也可以分成许多行。将SQL语句分成多行更容易阅读和调试。

4.3　检索多个列

要想从一个表中检索多个列，使用相同的SELECT语句。唯一的不同是必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。

当心逗号：在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。如果在最后一个列名后加了逗号，将出现错误。

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

输入：

SELECTprod_id, prod_name, prod_priceFROM products;

输出：

分析：在这个例子中，指定了3个列名，列名之间用逗号分隔。

数据表示：SQL语句一般返回原始的、无格式的数据。数据的格式化是一个表示问题，而不是一个检索问题。

4.4　检索所有列

除了指定所需的列外，SELECT语句还可以检索所有的列而不必逐个列出它们。这可以通过在世纪列名的位置使用星号(*)通配符来达到。

输入：

SELECT *

FROM products;

分析：如果给定一个支配符(*)，则返回表中所以列。列的顺序一般是列在表定义中出现的顺序。但有时候并不是这样的，表的模式的变化(如添加或删除列)可能会导致顺序的变化。

使用通配符：一般，除非你确实需要表中的每个列，否则最好别使用通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

4.5　检索不同的行

SELECT返回所有匹配的行。但是，如果你不想要每个值每次都出现，怎么办？例如，假如你想得出products表中产品的所有供应商ID：

输入：

SELECTvend_idFROM products;

输出：

分析：SELECT语句返回14行(即使表中只有4个供应商)，因为products表中列出了14个产品。那么如何检索出有不同值的列表呢？解决方法是使用DISTINCT关键字，次关键字指示MySQL只返回不同的值。

输入：

SELECT DISTINCTvend_idFROM products;

输出：

分析：MySQL只返回不同(唯一)的vend＿id行，因此只返回４行。如果使用DISTINCT关键字，它必须直接放在列名的前面。

DISTINCT关键字应用于所有列而不仅是前置它的列，如果给出SELECT　DISTINCT　vend＿id，prod＿price，除非指定的两个列都不同，否则所有列都将被检索出来。

4.6　限制结果

为了返回第一行或前几行，可使用LIMIT子句。

输入：

SELECTprod_nameFROMproducts

LIMIT5;

输出：

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

为得出下一个５行，可指定要检索的开始行和行数。

输入：

SELECTprod_nameFROMproducts

LIMIT5,5;

输出：

分析：LIMIT　５，５指示MySQL返回从行５开始的５行。第一个数为开始未知，第二个数为要检索的行数。待一个值的LIMIT总是从第一行开始，给出的数为返回的行数。待两个值的LIMIT可以指定从行号为第一个值的位置开始。

行０：检索出来的第一行为行０而不是行１.因此，LIMIT　１，１将检索出第二行而不是第一行。

在行数不够时：LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行(例如，给出LIMIT　１０，５，但只有１３行)，MySQL将只能返回它能返回的那么多行。

LIMIT　４　OFFSET　３意为从行３开始取４行，就像LIMIT　３，４一样。

4.7　使用完全限定的表名

迄今为止使用的SQL例子只通过列名引用列，也可能会使用完全限定的名字来引用列(同时使用表名和列名)。

输入：

SELECTproducts.prod_nameFROM products;

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

输入：

SELECTproducts.prod_nameFROM crashcourse.products;

第5章　　排序检索数据

5.1　排序数据

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

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

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

输入：

SELECTprod_nameFROMproductsORDER BY prod_name;

输出：

分析：这条语句除了指示MySQL对prod_name列以字母排序数据的ORDER BY子句外，与前面的语句相同。

通过非选择列进行排序：通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

5.2　按多个列排序

为了按多个列排序，值要指定列名，列名之间用逗号分开即可(就像选择多个列时所做的那样)。

下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序。

输入：

SELECTprod_id,prod_price,prod_nameFROMproductsORDER BY prod_price,prod_name;

输出：

重要的时理解在按多个列排序时，排序完全按所规定的顺序进行。换句话说，对于上述例子中的输出，尽在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_name列中所有的值都时唯一的，则不会按prod_name排序。

5.3　指定排序方向

数据排序不限定升序排序(从A到Z)。这只是默认的排序顺序，还可以使用ORDER BY 子句降序(从Z到A)顺序排序。为了进行降序排序，必须指定DESC关键字。

下面的例子按价格以降序排序产品(最贵的排在最前面)：

输入：

SELECTprod_id,prod_price,prod_nameFROMproductsORDER BY prod_price DESC;

输出：

下面的例子以降序排序产品(最贵的在最前面)，然后再对产品名排序：

输入：

SELECTprod_id,prod_price,prod_nameFROMproductsORDER BY prod_price DESC,prod_name;

输出：

分析：DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列(在每个价格内)仍然按标准的升序排序。

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

与DESC相反的关键字时ASC(ASCENDING)，在升序排序时可以指定它。但实际上，ASC没有多塔用处，因为升序时默认的(如果既不指定ASC也不指定DESC，则假定为ASC)。

区分大小写和排序顺序：取决于数据库如何设置。在字典(dictionary)排序顺序中，A被视为于a相同，这是MySQL(和大多数数据库管理系统)的默认行为。但是，许多数据库管理员能够在需要时改变这种行为(如果你的数据库包含大量外语字符，可能必须这样做)。

关键的问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到，你必须请求数据库管理员的帮助。

使用ORDER BY和LIMIT的祝贺，能够找出一个列中最高或最低的值。下面的例子演示如何找出最昂贵物品的值：

输入：

SELECTprod_priceFROMproductsORDER BY prod_price DESCLIMIT1;

输出：

分析：prod_price DESC保证行时按照由最昂贵到最便宜检索的，而LIMIT 1告诉MySQL仅返回一行。

ORDER BY子句的位置：在给出ORDER BY 子句时，应该保证它位于FROM子句之后。如果使用LIMIT 。它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

第6章　　过滤数据

6.1　使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件(search criteria)，搜索条件也称为过滤条件(filter condition)。

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名(FROM子句)之后给出，如下所示：

输入：

SELECTprod_name,prod_priceFROMproductsWHERE prod_price = 2.50;

输出：

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

这个例子采用了简单的相等测试：它检查一个列是否具有指定的值，据此进行过滤。但是SQL允许做的事情不仅仅时相当测试。

SQL过滤于应用过滤：数据可以在应用层过滤。为此目的，SQL的SELECT语句为客户机应用检索出超出实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。通常，这种实现并不令人满意。因此，对数据库进行了优化，以便快速有效地对数据进行过滤。让客户机应用(或开发语言)处理数据库的工作将会极大地影响到应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

WHERE子句的位置：在同时使用ORDER BY 和WHERE子句时，应该让ORDER BY 位于WHERE之后，否则将会产生错误。

6.2　WHERE子句操作符

6.2.1　检查单个值

输入：

SELECTprod_name,prod_priceFROMproductsWHERE prod_name = 'fuses';

输出：

分析：检查WHERE prod_name = 'fuses'语句，它返回prod_name的值为Fuses的一行。MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配。

列出价格小于10美元的所有产品：

输入：

SELECTprod_name,prod_priceFROMproductsWHERE prod_price < 10;

输出：

检索价格小于等于10美元的所有产品(输出的结果比上面例子输出的结果多两种产品)：

输入：

SELECTprod_name,prod_priceFROMproductsWHERE prod_price <= 10;

输出：

6.2.2　不匹配检查

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

输入：

SELECTvend_id, prod_nameFROMproductsWHERE vend_id <> 1003;

输出：

何时使用引号：如果仔细挂差上述WHERE子句中使用的条件，会看到又得知括在单引号中(如前面使用的'fuses')，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

下面时相同的例子，其中使用!=而不是<>操作符：

输入：

SELECTvend_id, prod_nameFROMproductsWHERE vend_id != 1003;

6.2.3　范围值检查

为了检查某个范围的值，可是要BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。例如，BETWEEN操作符可用来检索价格在5么远和10美元之间或日期在指定的开始日期和结束日期之间的所有产品。

使用BETWEEN操作符检索价格在5美元和10美元之间的所有产品：

输入：

SELECTprod_name,prod_priceFROMproductsWHERE prod_price BETWEEN 5 AND 10;

输出：

分析：在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分割。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

6.2.4　空值检查

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含控制NULL。

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

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是 IS NULL 子句。其语法如下：

输入：

SELECTprod_nameFROMproductsWHERE prod_price IS NULL;

这条语句返回美元价格(空prod_price字段，不是价格为0)的所有产品，由于表中没有这样的行，所有没有返回数据。但是，customers表确实包含具有空值的列，如果在文件中没有某个顾客的电子邮件地址，则cust_email列将包含NULL值。

输入：

SELECTcust_idFROMcustomersWHERE cust_email IS NULL;

输出：

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

第7章　　数据过滤

7.1　组合WHERE子句

为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以使用两种方式使用：以AND子句的方式或OR子句的方式使用。

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

7.1.1　AND操作符

为了通过不知一个列进行过滤，可使用AND操作符给WHERE子句附加条件。

输入：

SELECTprod_id, prod_price, prod_nameFROMproductsWHERE vend_id = 1003 AND prod_price <= 10;

输出：

分析：此SQL语句检索由供应商1003制造且价格小于或等于10美元的所有产品的名称和价格。这条SELECT语句中的WHERE子句包含两个条件，并且用AND关键字联结它们。AND指示DBMS只返回满足所有给定条件的行。如果某个产品由供应商1003制造，但它的价格高于10美元，则不检索它。类似，如果产品价格小于10美元，但不是由指定供应商制造的也不被检索。

AND：用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。添加多个过滤条件，每添加一条就要使用一个AND。

7.1.2　OR操作符

OR操作符和AND操作符不同，它指示MySQL健硕匹配任一条件的行。

输入：

SELECTprod_name, prod_priceFROMproductsWHERE vend_id = 1002 OR vend_id = 1003;

输出：

分析：此SQL语句检索由一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。如果这里使用的时AND操作符，则没有数据返回(此时创建的WHERE子句不会检索到匹配的产品)。

OR：WHERE子句中使用的关键字，用来标识检索匹配任一给定条件的行。

7.1.3　计算次序

WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。

但是，组合AND和OR带来了一个有趣的问题。假如需要列出价格为10美元(含)以上且由1002或1003制造的所有产品。

输入：

SELECTprod_name, prod_priceFROMproductsWHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

输出:

分析：返回的行中由两行价格小于10美元，显然，返回的行未按预期的进行过滤。原因在于计算的次序。SQL(像多数语言一样)在处理OR操作符前，优先处理AND操作符。当SQL看到上述WHERE子句时，它理解为由供应商1003制造的任何价格为10美元(含)以上的产品，或者由供应商1002制造的任何产品，而不管其价格如何。换句话说，由于AND在祭祀u按次序中优先级更高，操作符被错误地组合了。

此问题的解决办法时使用小括号明确地分组相应的操作符。

输入：

SELECTprod_name, prod_priceFROMproductsWHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

输出：

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

在WHERE子句中使用小括号：任何时候使用具有AND和OR操作符的WHERE子句，都应该使用小括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实时你想要的东西也是如此。使用小括号美元什么坏处，它能消除歧义。

7.2　IN操作符

小括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在小括号中。

输入：

SELECTprod_name, prod_priceFROMproductsWHERE vend_id IN(1002,1003)ORDER BY prod_name;

输出：

分析：此SELECT语句检索供应商1002和1003制造的所有产品。IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在小括号中。

IN操作符完成于OR相同的功能。

输入：

SELECTprod_name, prod_priceFROMproductsWHERE vend_id =1002 OR vend_id =1003

ORDER BY prod_name;

输出：

IN操作符的优点

1、在使用长的合法选项清单时，IN操作符的语法更清楚且更直观；

2、在使用IN时，计算的次序更容易管理(因为使用的操作符更少)；

3、IN操作符一般比OR操作符清单执行更快；

4、IN的最大优点时可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

IN：WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。

7.3　NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否认它之后所跟的任何条件。

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

输入：

SELECTprod_name, prod_priceFROMproductsWHERE vend_id NOT IN(1002,1003)ORDER BY prod_name;

输出：

分析：这里的NOT否定跟在它之后的条件，因此，MySQL不是匹配1002和1003的vend_id，而是匹配1002和1003之外供应商的vend_id。对于简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT时非常有用的。例如，在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单。

MySQL中的NOT：MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

第8章　　用通配符进行过滤

8.1　LIKE操作符

利用通配符可创建比较特定数据的搜索模式。

通配符(wildcard)：用来匹配值的一部分的特殊字符。

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

通配符本身实际是SQL的WHERE自己中由特殊含义的字符，SQL支持机中通配符。

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

谓词：操作符在它作为谓词(predicate)时不是操作符。

8.1.1　百分号(%)通配符

最常使用的通配符时百分号(%)。在搜索串中，%表示任何字符出现任意次数。

例如，为了找出所有以词jet起头的产品，可使用以下SELECT语句：

输入：

SELECTprod_id,prod_nameFROMproductsWHERE prod_name LIKE 'JET%';

输出：

分析：此例子使用了搜索模式’jet%‘。在执行这条子句时，将检索任意以jet起头的词。%告诉MySQL接受jet之后的任意字符，不管它有多少字符。

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

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

下面的例子使用两个通配符，它们位于模式的两端：

输入：

SELECTprod_id,prod_nameFROMproductsWHERE prod_name LIKE '%anvil%';

输出：

分析：搜索模式'%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以s起头以e结尾的所有产品。

输入：

SELECTprod_nameFROMproductsWHERE prod_name LIKE 's%e';

除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。

注意尾空格：尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果它后面由一个或多个空格，则子句WHERE prod_name LIKE ‘%anvil’ 将不会匹配它们，因为在最后的l后由多余的字符。解决这个问题的一个简单的办法是在搜索模式最好附加一个%。一个更好的办法是使用函数唔叼首尾空格。

注意NULL：虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%' 也不能匹配用值NULL作为产品名的行。

8.1.2　下划线(_)通配符

另一个有用的通配符是下划线(_)。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

输入：

SELECTprod_id,prod_nameFROMproductsWHERE prod_name LIKE '_ ton anvil';

输出：

分析：此WHERE子句中的搜索模式给出了后面更由文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配1，第二行中匹配2。.5 ton anvil产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。对照一下，下面的SELECT语句使用%通配符，返回三行产品：

输入：

SELECTprod_id,prod_nameFROMproductsWHERE prod_name LIKE '% ton anvil';

输出：

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。

8.2　使用通配符的技巧

MySQL的通配符很有用，但这种功能是由代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。

注意：

1、不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

2、在确实需要使用通配符时，除非绝对有必要，否则不要把他们用在搜索模式的开始处。把通配符置于搜索模式的开始出，搜索起来时最慢的。

3、仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

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

9.1　正则表达式

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

参阅《正则表达式必知必会》。

9.2　使用MySQL正则表达式

正则表达式的作用是匹配文本，将一个模式(正则表达式)与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

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

9.2.1　基本字符匹配

下面的语句检索列prod_name包含文本1000的所有行：

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '1000'

ORDER BY prod_name;

输出：

分析：除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句。它告诉MySQL：REGEXP后所跟的东西作为正则表达式(与文本正文1000匹配的一个正则表达式)处理。

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '.000'

ORDER BY prod_name;

输出：

分析：这里使用了正则表达式.000。.是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此，1000与2000都匹配且返回。

LIKE与REGEXP：在LIKE和REGEXP之间由一个重要的差别。请看以下两条语句：

SELECTprod_nameFROMproductsWHERE prod_name LIKE '1000'

ORDER BYprod_name;SELECTprod_nameFROMproductsWHERE prod_name REGEXP '1000'

ORDER BY prod_name;

如果执行上述两条语句，会发现第一条语句不返回数据，而第二条语句返回一行。为什么？

LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找不到它，相应的行也不会被返回(除非使用通配符)。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。

那么，REGEXP能不能用来匹配整个列值(从而起与LIKE相同的作用)？答案是坑定的，使用＾和＄(Anchor)即可。

匹配不区分大小写：MySQL中的正则表达式匹配不区分大小写(即，大写和小写都匹配)。为区分大小写，可使用BINARY关键字，如WHERE prod＿name REGEXP BINARY ＇JetPack .000＇。

9.2.2　进行OR匹配

为搜索两个串之一(或者为这个串，或者为另一个串)，使用|，如下所示：

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '1000|2000'

ORDER BY prod_name;

输出：

分析：语句中使用了正则表达式1000|2000。|为正则表达式的OR操作符。它表示匹配其中之一，因此1000和2000都匹配并返回。

使用|从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。

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

9.2.3　匹配几个字符之一

匹配任何单一字符。但是，如果你只想匹配特定的字符，可通过指定一组用[和]括起来的字符完成，如下所示：

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[123] Ton'

ORDER BY prod_name;

输出：

分析：这里使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回(没有3ton)。

[ ]是另一种形式的OR语句。事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[ ]来定义OR语句查找什么。

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '1|2|3 Ton'

ORDER BY prod_name;

输出：

分析：这并不是期望的输出。两行要求的行被检索出来，但还检索了另外3行。之所以这样是由于MySQL嘉定你的意思是'1'或'2'或'3 ton'。除非把字符|括在一个集合中，否则他将应用于整个串。

字符集合也可以被否定，即它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处防止一个^即可。因此，尽管[123]匹配字符1、2或3，，但[^123]却匹配除这些字符外的任何东西。

9.2.4　匹配范围

集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：

[0123456789]

为简化这种类型的集合，可使用-来定义一个范围。下面的式子功能上等同于上述数字列表：

[0-9]

范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符。

举一个例子：

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[1-5] Ton'

ORDER BY prod_name;

输出：

分析：这里使用正则表达式[1-5] Ton。[1-5]定义了一个范围，整个表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton匹配，所以返回.5 ton。

9.2.5　匹配特殊字符

正则表达式语言由具有特殊含义的特殊字符构成。

找出包含.字符的值：

输入：

SELECTvend_nameFROMvendorsWHERE vend_name REGEXP '.'

ORDER BY vend_name;

输出：

分析：这并不是期望的输出，.匹配任意字符，因此每个行都被检索出来。

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

输入：

SELECTvend_nameFROMvendorsWHERE vend_name REGEXP '\\.'

ORDER BY vend_name;

输出：

分析：这才是期望的输出。\\.匹配 . ，所以只检索除一行。这种处理就是所谓的转义(escaping)，正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括 .、 |、 [ ]以及迄今为止使用过的其他特殊字符。

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

匹配\：为了匹配反斜杠(\)字符本身，需要使用\\\。

\或\\？：多数正则表达式实现使用单个反斜杠转义特殊字符，一边能适应这些字符本身。但MySQL要求两个反斜杠(MySQL自己解析一个，正则表达式库解析另一个)。

9.2.6　匹配子分类

存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类(character class)。

9.2.7　匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配，该行被检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如，你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s(如果存在)，等等。

这可以用列出的正则表达式重复元字符来完成。

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '\$[0-9] sticks?\$'

ORDER BY prod_name;

输出：

分析：正则表达式 \$[0-9] sticks?\$ ，\\ (匹配)，[0-9]匹配任意数字(这个例子中为1和5)，sticks?匹配stick和sticks(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现)，\\)匹配)。没有?，匹配stick和sticks会非常困难。

匹配连在一起的4位数字：

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[[:digit:]]{4}'

ORDER BY prod_name;

输出：

分析：[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符(任意数字)出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

在使用正则表达式，编写某个特殊的表达式几乎总是有不止一种方法。

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'

ORDER BY prod_name;

9.2.8　定位符

为了匹配特定位置的文本，需要使用以下的定位符：

找出以一个属(包括以小数点开始的数)开始的所有产品：

输入：

SELECTprod_nameFROMproductsWHERE prod_name REGEXP '^[0-9\\.]'

ORDER BY prod_name;

输出：

分析：^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。没有^，则还要多检索4个别的行(那些中间有数字的行)。

^的双重用途：^有两种用法。在集合中(用[和]定义)，用它来否定该集合，否则，用来指串的开始处。

使REGEXP起类似LIKE的作用：LIKE和REGEXP的不同之处在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使ERGEXP的作用与LIKE一样。

简单的正则表达式测试：可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0(没有匹配)或1(匹配)。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：

SELECT 'hello' REGEXP '[0-9]';

这个例子显然将返回0(因为文本hello中没有数字)。

第10章　　创建计算字段

10.1　计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

字段(field)：基本上与列(column)的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机(如应用程序)的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。

客户机与服务器的格式：可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

10.2　拼接字段

vendors表包含供应商名和位置信息。假如要生成一个供应商报表，需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。

此报表需要单个值，而表中数据存储在两个列vend_name和vend_country中。此外，需要用括号将vend_country括起来，这些东西都没有明确存储在数据库表中。

拼接(concatenate)：将值联结一起构成单个值。

解决方法是把两个列拼接起来。在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

MySQL的不同之处：多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。当把SQL语言转换成MySQL语句时一定要把这个区别铭记在心。

输入：

SELECT Concat(vend_name,' (',vend_country,')')

FROMvendors

ORDER BY vend_name;

输出：

分析：Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

上面的SELECT语句连接以下4个元素：

1、存储在vend_name列中的名字；

2、包含一个空格和一个左小括号的串；

3、存储在vend_country列中的国家；

4、包含一个右小括号的串。

通过删除数据右侧多余的空格来整理数据，这可以使用MySQL的RTrim()函数来完成：

输入：

SELECT Concat(RTrim(vend_name),' (',vend_country,')')

FROMvendors

ORDER BY vend_name;

分析：RTrim()函数去掉值右边的所以空格。通过使用RTrim()，各个列都进行了整理。

Trim函数：MySQL除了支持RTrim()(去掉串右边的空格)，还支持LTrim()(去掉串左边的空格)以及Trim()(去掉串左右两边的空格)。

使用别名

SQL支持列别名。别名(alias)时一个字段或值的替换名。别名用AS关键字赋予。

输入：

SELECT Concat(RTrim(vend_name),' (',vend_country,')') ASvend_title

FROMvendors

ORDER BYvend_name;

输出：

分析：SELECT语句本身与以前使用的相同，只不过这里的语句中计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算机的名为vend_title的计算字段。从输出中可以看到，结果与以前的相同，但现在列名为vend_title，任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样。

别名的其他用途：常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它，在原来的名字含混或容易误解时扩充它，等等。

导出列：别名有时也称为导出列(derived column)，不管称为什么，它们所代表的都是相同的东西。

10.3　执行算数计算

计算字段的另一常见用途时对检索出的数据进行算数计算。举一个例子，orders表包含收到的所有订单，orderitems表包含每个订单中的各项物品。

下面的SQL语句检索订单号20005中的所有物品：

SELECTprod_id, quantity, item_price

FROMorderitems

WHERE order_num = 20005;

输出：

items_price列包含订单中每项物品的单价。如下汇总物品的价格(单价乘以订购数量)：

输入：

SELECT prod_id, quantity, item_price, quantity*item_price ASexpanded_price

FROMorderitems

WHERE order_num = 20005;

输出：

分析：输出中显示的expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算列，就像使用其他列一样。

MySQL支持以下列出的基本算数操作符。此外，小括号可用来区分优先顺序。

如何测试计算：SELECT提供了测试和实验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT3*2;将返回6，SELECT Trim(‘abc’);将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。

第11章　　使用数据处理函数

11.1　函数

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

函数没有SQL的可移值性强：能运行在多个系统上的代码称为可移值的(portable)。相对来说。多数SQL语句时可移值的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移值性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。

如果不使用这些函数，编写某些应用程序代码会很艰难。

如果你决定使用函数，应该保证做好代码注释，以便以后你(或其他人)能确切地知道所编写SQL代码地含义。

11.2　使用函数

大多数SQL实现支持以下类型地函数：

1、用于处理文本串(如删除或填充值，转换值为大写或小写)的文本函数；

2、用于在数值数据上进行算数操作(如返回绝对值，进行代数运算)；

3、用于处理日期和时间值并从这些值中提取特定成分(例如，返回两个日期之差，检查日期有效性等)的日期和时间函数。

4、返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数。

11.2.1　文本处理函数

使用Uooer()函数：

输入：

SELECT vend_name, Upper(vend_name) ASvend_name_upcase

FROMvendors

ORDER BYvend_name;

输出：

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

以下列出了某些常用的文本处理函数：

SOUNDEX时一个将任何文本串转换为描述其语音表示的字母数字模式的算法，SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL(就像多数DBMS一样)都提供对SOUNDEX的支持。

使用Soundex()函数的例子：

customers表中有一个顾客Coyote Inc.，其联系名为Y.Lee。但如果这是输入错误，此联系名实际应该是Y.Lie，怎么办？显然，按正确的联系名搜索不会返回数据，如下所示：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_contact = 'Y.Lie';

使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE Soundex(cust_contact) = Soundex('Y lie');

输出：

分析：WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音很相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤了所需的数据。

11.2.2　日期和时间处理函数

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

一般，应用程序不使用用来存储日期和时间地格式，因此日期和时间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时间函数在MySQL语言中具有重要地作用。

以下列出了某些常用的日期和时间处理函数：

要注意MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01.虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性(如。04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……)。

应该总是使用4为数字的年份：支持2位数字的年份，MySQL处理00-69位2000-2069，处理70-99为7970-1999。虽然它们可能是打算要的年份，但使用完整的4位数字年份更可靠，因为MySQL不必做出任何假定。

因此，基本的日期比较应该很简单：

SELECTcust_id, order_num

FROMorders

WHERE order_date = '2005-09-01';

输出：

分析：此SELECT语句正常运行。它检索出一个订单记录，该订单记录的order_date为2005-09-01。但是，使用WHERE order_date = '2005-09-01' 可靠吗？order_date的数据类型为datetime。这种数据存储日期及时间值。样例表中的值全都具有时间值00：00：00，但实际中很可能并不总是这样。如果用当前日期和时间存储订单日期(因此你不仅知道订单日期。还知道下订单当天的时间)，怎么办？比如，存储的order_date值为2005-09-01　11：30：05，则WHERE order_date = '2005-09-01'失败。即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失败。

解决方法是指示MySQL仅将给出的日期与列中的日期部分进行不叫，而不是将给出的日期与整个列值进行比较。为此，必须使用Date()函数。Date(order_date)指示MySQL仅提取列的日期部分，更可靠的SELECT语句为：

输入：

SELECTcust_id, order_num

FROMorders

WHERE Date(order_date) = '2005-09-01';

如果要的是日期，请使用Date()：如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time()函数，在你只想要时间时应该使用它。

检索出2005年9月下的所有订单(简单的相等测试不行，因为它也要匹配月份中的天数)：

方法一输入：

SELECTcust_id, order_num

FROMorders

WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

输出：

分析：BETWEEN操作符用来把2005-09-01和2005-09-30定义为一个要匹配的日期范围。

方法二(不需要记住每个月中有多少天或不需要操心闰年2月)输入：

SELECTcust_id, order_num

FROMorders

WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

分析：Year()是一个从日期(或日期时间)中返回年份的函数。类似，Month()从日期中返回月份。因此，WHERE Year(order_date) = 2005 AND Month(order_date) = 9 检索出order_date为2005年9月的所有行。

11.2.3　数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期-时间处理函数的使用那么频繁。

在主要DBMS的函数中，数值函数是最一致最统一的函数。

以下是常用的数值处理函数：

第12章　汇总数据

12.1　聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下几种：

1、确定表中行数(或者满足某个条件或包含某个特定值的行数)。

2、获得表中行组的和。

3、找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。

聚集函数(aggregate function)：运行在行组上，计算和返回单个值的函数。

12.1.1　AVG()函数

AVG()函数通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

使用AVG()返回products表中所有产品的平均价格：

输入：

SELECT AVG(prod_price) ASavg_price

FROMproducts;

输出：

分析：此SELECT语句返回值avg_price，它包含products表中所有产品的平均价格，avg_price是一个别名。

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

返回特定供应商提供产品的平均价格：

输入：

SELECT AVG(prod_price) ASavg_price

FROMproducts

WHERE vend_id = 1003;

输出：

分析：这条SELECT语句与前一条的不同之处在于它包含了WHERE子句。此WHERE子句仅过滤出vend_id位1003的产品，因此avg_price中返回的值指示该供应商的产品的平均值。

只用于单列：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，为了获得多个列的平均值，必须使用多个AVG()函数。

NULL值：AVG()函数忽略列值位NULL的行。

12.1.2　COUNT()函数

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

COUNT()函数有两种使用方式。

使用COUNT(*)对表中的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

下面的例子返回customers表中客户的总数：

输入：

SELECT COUNT(*) ASnum_cust

FROMcustomers;

输出：

分析：在此例子中，利用COUNT(*)对所有行计数，不管行中各列有什么值。计数值在num_cust中返回。

下面的例子只对具有电子邮件地址的客户计数：

输入：

SELECT COUNT(cust_email) ASnum_cust

FROMcustomers;

输出：

分析：这条语句使用COUNT(cust_email)对cust_email列中有值的行进行计数。在此例子中，cust_email的计数位3(表示5个客户中只有3个客户有电子邮件地址)。

NULL值：如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号(*)，则不忽略。

12.1.3　MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：

输入：

SELECT MAX(prod_price) ASmax_price

FROMproducts;

输出：

分析：MAX()返回products表中最贵的物品的价格。

对非数值数据使用MAX()：虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

NULL值：MAX()函数忽略列值为NULL的行。

12.1.4　MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名，如下所示：

输入：

SELECT MIN(prod_price) ASmin_price

FROM products;

输出：

分析：其中MIN()返回products表中最便宜物品的价格。

对非数值数据使用MIN()：MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

NULL值：MIN()函数忽略列值为NULL的行。

12.1.5　SUM()函数

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

下面举一个例子，orderitems表包含订单中实际的物品，每个物品有相应的数量(quantity)。可如下检索所订购物品的总数(所有quantity值之和)：

输入：

SELECT SUM(quantity) ASitems_ordered

FROMorderitems

WHERE order_num = 20005;

输出：

分析：函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。

SUM()也可以用来合计计算值。

合计每项物品的item_price*quantity，得出总的订单金额：

输入：

SELECT SUM(item_price*quantity) AStotal_price

FROMorderitems

WHERE order_num = 20005;

输出：

12.2　聚集不同值

对所有的行执行计算，指定ALL参数或不给参数(因为ALL是默认行为)；

只包含不同的值，指定DISTINCT参数。

ALL为默认：ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

输入：

SELECT AVG(DISTINCT prod_price) ASavg_price

FROMproducts

WHERE vend_id = 1003;

输出:

分析：在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

如果指定列名，则DISTINCT只能用于COUNT()、DISTINC不能用于COUNT(*)，因此不允许使用COUNT(DISTINCT)，否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

将DISTINCT用于MAX()和MAX()：虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

12.3　组合聚集函数

实际上SELECT语句可根据需要包含多个聚集函数。

输入：

SELECT COUNT(*) ASnum_items,

MIN(prod_price) ASprice_min,

MAX(prod_price) ASprice_max,

AVG(prod_price) ASprice_avg

FROM products;

输出：

分析：这里用单条SELECT语句执行了4个聚集计算，返回4个值(products表中物品的数目，产品价格的最高、最低以及平均值)。

取别名：在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的SQL更易于理解和使用(以及将来容易派出故障)。

第13章　　分组数据

13.1　数据分组

SQL聚集函数可用来汇总数据，这使我们能够对行进行计数，计算和平均数，获得最大和最小值而不用检索所有数据。

目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。

返回供应商1003提供的产品数目：

输入：

SELECT COUNT(*) ASnum_prods

FROMproducts

WHERE vend_id = 1003;

输出：

分析：分组允许把数据分为多个逻辑组，以便对没个组进行聚集计算。

13.2　创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

输入：

SELECT vend_id, COUNT(*) ASnum_prods

FROMproducts

GROUP BY vend_id;

输出：

分析：上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段(用COUNT(*)函数建立)。GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个标计算num_prods一次。从输出中可以看到，供应商1001有3个产品，供应商1002有2个产品，供应商1003有7个产品，而供应商1005有2个产品。

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中潜逃了分组，数据将在最后规定的分组上进行汇总。话句话说，在建立分组时，指定的所以列都一起计算(所以不能从个别的列取回数据)。

GROUP BY子句中列出的每个列都必须时检索列霍有效的表达式(但不能时聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY 子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用ROLLUP：使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总(针对每个分组)的值，如下所示：

输入：

SELECT vend_id, COUNT(*) ASnum_prods

FROMproducts

GROUP BY vend_id WITH ROLLUP;

输出：

13.3　过滤分组

HAVING非常类似于WHERE。WHERE过滤行，而HAVING过滤分组。

HAVING支持所有WHERE操作符，它们的句法时相同的，只是关键字有差别。

输入：

SELECT cust_id, COUNT(*) ASorders

FROMorders

GROUP BYcust_id

HAVING COUNT(*) >=2;

输出：

分析：最后增加了一行HAVING子句，它过滤COUNT(*) >=2(两个以上的订单)的那些分组。

HAVING和WHERE的差别：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的取别，WHERE排除的行不包括在分组中，这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

同时使用WHERE和HAVING子句列出具有2个(含)以上、价格为10(含)以上的产品的供应商：

输入：

SELECT vend_id, COUNT(*) ASnum_prods

FROMproducts

WHERE prod_price >= 10

GROUP BYvend_id

HAVING COUNT(*) >= 2;

输出：

分析：这条语句中，第一行时使用了聚集函数的基本SELECT，它与前面的例子很相像。WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或以上的分组。如果没有WHERE子句，将会多检索出两行(供应商1002，销售的所有产品价格都在10以下；供应商1001，销售3个产品，但只有一个产品的价格大于等于10)。如下：

输入：

SELECT vend_id,COUNT(*) ASnum_prods

FROMproducts

GROUP BYvend_id

HAVING COUNT(*) >= 2;

输出：

13.4　分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们时非常不同的。

第一项的差别极为重要。不要忘记ORDER BY，一般在使用GROUP BY子句时，应该也给出ORDER BY子句，这是保证数据正确排序的唯一方法。千万不要依赖于GROUP BY排序数据。

输入：

SELECT order_num, SUM(quantity*item_price) ASordertotal

FROMorderitems

GROUP BYorder_num

HAVING SUM(quantity*item_price) >=50;

输出：

为按总计订单价格排序输出，需要添加ORDER BY子句，如下所示：

SELECT order_num, SUM(quantity*item_price) ASordertotal

FROMorderitems

GROUP BYorder_num

HAVING SUM(quantity*item_price) >=50

ORDER BY ordertotal;

输出：

分析：在这个例子中，GROUP BY子句用来按订单号(order_num列)分组数据，以便SUM(*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出。

13.5　SELECT 子句顺序

第14章　　使用子查询

14.1　子查询

查询(query)：任何SQL语句都是查询。但此术语一般指SELECT语句。

SQL还允许子查询(subquery)，即嵌套在其他查询中的查询。

14.2　利用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的ordersitems表中。orders表不存储客户信息。它值存储客户的ID。实际的客户信息存储在customers表中。

现在，假如需要列出订购物品TN2的所有客户，应该怎样检索？

(1)检索包含物品TN2的所有订单的编号；

(2)检索具有前一步骤列出的订单编号的所有客户的ID；

(3)检索前一步步骤返回的所有客户ID的客户信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

也可以使用子查询来把3个查询组合成一条语句。

第一条SELECT语句的含义很明确，对于prod_id为TNT2的所有订单物品，它检索其order_num列，输出列出两个包含此物品的订单。

输入：

SELECTorder_num

FROMorderitems

WHERE prod_id = 'TNT2';

输出：

下一步，查询具有订单20005和20007的客户ID。利用IN子句，编写如下的SELECT语句：

输入：

SELECTcust_id

FROMorders

WHERE order_num IN(20005,20007);

输出：

现在，把第一个查询(返回订单号的那一个)变为子查询组合两个查询。

输入：

SELECTcust_id

FROMorders

WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

输出：

分析：在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。

首先，它执行了查询

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'

此查询返回两个订单号：20005和20007。然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。

外部查询变成

SELECT　　cust_id

FROMorders

WHERE order_num IN(20005,20007);

可以看到，输出是正确的并且与前面硬编码WHERE子句返回的值相同。

格式化SQL：包含子查询的SELECT语句难以阅读和调试，特别是它们较为复杂时更是如此。把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。

现在得到了订购物品TNT2的所有客户的ID。下一步时检索这些客户ID的客户信息。检索两列的SQL语句为：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_id IN(10001,10004);

输出：

可以把其中的WHERE子句转换为子查询而不是硬编码这些客户ID：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_id IN(SELECTcust_id

FROMorders

WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

输出：

分析：为了执行SELECT语句，MySQL实际上必须执行3条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。

可见，在WHERE子句中使用子查询能够编写出功能很强并且很亮或的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

列必须匹配：在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于(=)、不等于(<>)等。

子查询和性能：这里给出的代码有效并获得所需的结果，但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。

14.3　作为计算字段使用子查询

使用子查询的另一方法时创建计算字段。加入需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

为了执行这个操作，遵循下面的步骤：

(1)从customers表中检索客户列表。

(2)对于检索出的每个客户，统计其在orders表中的订单数目。

可使用SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对改客户的订单进行计数。

对客户10001的订单进行计数：

输入：

SELECT COUNT(*) ASorders

FROMorders

WHERE cust_id = 10001;

为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询：

输入：

SELECTcust_name,cust_state,

(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) ASorders

FROMcustomers

ORDER BY cust_name;

输出：

分析：这条SELECT语句对customers表中每个客户返回3列：cust_name、cust_state 和 orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名。

相关子查询(correlated subquery)：涉及外部查询的子查询。这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法(表明和列名有一个句点分割)。

虽然子查询在构造这种SELECT语句时极有用，但必须注意限制有歧义性的列名。

用子查询建立(和测试)查询的最可靠的方法是逐渐进行，这与MySQL查理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它，对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后(找出查询为什么不正常)的大量时间，并且极大地提高了查询一开始就正常工作的可能性。

第15章　　联结表

15.1　联结

SQL 最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作。

15.1.1　关系表

相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据库一个表。各表通过某些常用的值(即关系设计中方的关系(relational))互相关联。

在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。verdors表包含所有供应商信息，每个供应商占一行。每个供应商具有唯一的标识。此标识称为主键(primary key)，可以是供应商ID或任何其他唯一值。

products表只存储产品信息，它除了存储供应商ID(vendors表中的主键)外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键(foreign)：外键为某个表中的一列，它包含另一个表中的主键值，定义了两个表之间的关系。

这样做的好处如下：

1、供应商信息不重复，从而不浪费时间和空间；

2、如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；

3、由于数据无重复，显然数据是一致的，这使得处理数据更简单。

总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比关系数据库要好。

可伸缩性(scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。

15.1.2　为什么要使用联结

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性：它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在与查询的执行当中。在使用关系表时，仅在关系列中插入合法的数据非常重要。

如果在products表中插入拥有非法供应商ID(即没有在vendors表中出现)的供应商生产的产品，则这些产品时不可访问的，因为它们没有关联到某个供应商。为防止这种情况发生，可指示MySQL只允许在products表的供应商ID列中出现合法值(即出现在vendors表中的供应商)。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。

15.2　创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

输入：

SELECTvend_name,prod_name,prod_price

FROMvendors, products

WHERE vendors.vend_id =products.vend_id

ORDER BY vend_name,prod_name;

输出：

分析：SELECT语句与之前所有语句一样指定要检索的列。这里，最大的差别是所指定的两个列(prod_name和prod_price)在一个表中，而另一个列(vend_name)在另一个表中。

这条语句的FROM子句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

可以看到要匹配的两个列以vendors.vend_id和products.vend_id指定。这里需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪一个(它们有两个，每个表中一个)。

完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名(用一个点分隔的表名和列名)。如果引用一个没有用表明限定的具有二义性的列名，MySQL将返回错误。

15.2.1　WHERE子句的重要性

在一条SELECT语句中联结几个表时，相应的关系时在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西

WHERE子句作为过滤条件，它只包含那些匹配给定条件(这里时联结条件)的行。没有WHERE子句，第一个表的每个行将与第二个表中的每个行匹配，而不管它们逻辑上是否可以配在一起。

笛卡儿积(cartesian products)：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

输入：

SELECTvend_name,prod_name,prod_price

FROMvendors,products

ORDER BY vend_name,prod_name;

输出：

分析：这里相应的笛卡儿积不是我们所想要的。这里返回的数据用每个供应商匹配了每个产品，它包括了供应商不正确的产品。实际上有的供应商根本没有产品。

不要忘了WHERE子句：应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该i保证WHERE子句的正确性。

叉联结：有时我们会听到返回称为叉联结(cross join)的笛卡儿积的联结类型。

15.2.2　内部联结

目前为止所用的联结称为等值联结(equijoin)，它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

输入：

SELECTvend_name,prod_name,prod_price

FROM vendors INNER JOINproducts

ON vendors.vend_id = products.vend_id;

输出：

分析：这里两个表之间的关系时FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

输出和下面的MySQL语句返回完全相同的数据：

SELECTvend_name,prod_name,prod_price

FROMvendors,products

WHERE vendors.vend_id = products.vend_id;

使用哪种语法：ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

15.2.3　联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

输入：

SELECTprod_name,vend_name,prod_price,quantity

FROMorderitems,products,vendors

WHERE products.vend_id =vendors.vend_id

AND orderitems.prod_id =products.prod_id

AND order_num = 20005;

输出：

分析：此例子显示编号为20005的订单中的物品。订单物品存储在orderitems表中。每个产品按其产品ID存储，它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的记录中。这里的FROM子句列出了3个表，而WHERE子句定义了这两个联结条件，而第三个联结条件用来过滤出订单20005中的物品。

性能考虑：MySQL在运行时关联指定的每个表以处理联结，这种处理可能时非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

子查询，返回订购产品TNT2的客户列表：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

子查询并不总是执行复杂SELECT操作的最有效的方法。

使用联结的相同查询：

输入：

SELECTcust_name, cust_contact

FROMcustomers, orders, orderitems

WHERE customers.cust_id =orders.cust_id

AND orderitems.order_num =orders.order_num

AND prod_id = 'TNT2';

输出：

分析：这个查询中返回数据需要i使用3个表。但这里我们没有在嵌套子查询中使用它们，二十使用了两个联结。这里有3个WHERE子句条件。前两个关联联结中的表，后一个过滤产品TNT2的数据。

多做实验：为执行任一给定的SQL操作，一般存在不知一种方法。很少有绝对正确或绝对错误的方法。性能可能会收到操作类型、表中数据量、是否存在索引或建以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最合适具体情况的方法。

第16章　　创建高级联结

16.1　使用表别名

给列起别名:

输入：

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') ASvend_title

FROMvendors

ORDER BY vend_name;

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

1、缩短SQL语句；

2、允许在单条SELECT语句中多次使用相同的表。

输入：

SELECTcust_name, cust_contact

FROM customers AS c, orders AS o, orderitems ASoi

WHERE c.cust_id =o.cust_id

AND oi.order_num =o.order_num

AND prod_id = 'TNT2';

分析：可以看到，FROM子句中3个表全都具有别名。customers AS c 建立c作为customers的别名。这使得能使用省写的c而不是全名customers。表列名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。

列别名只在查询执行中使用。与列别名不一样，表别名不返回客户机。

16.2　使用不同类型的联结

16.2.1　自联结

使用表别名的主要原因之一时能在单条SELECT语句中不知一次引用相同的表。

假如你发现某物品(其ID为DINTR)存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

输入：

SELECTprod_id,prod_name

FROMproducts

WHERE vend_id =(SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

输出：

分析：这是第一种解决方案，它使用了子查询。内部的SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品。

使用联结的相同查询：

输入：

SELECTp1.prod_id, p1.prod_name

FROM products AS p1, products ASp2

WHERE p1.vend_id =p2.vend_id

AND p2.prod_id = 'DTNTR';

输入：

分析：此查询中需要的两个表实际上时相同的表，因此products表在FROM子句中出现了两次。虽然这是合法的，但对products的引用具有二义性，因为MySQL不知道你引用的时products表中的哪个实例。为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。

用自联结而不用子查询：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果时相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确认哪一种的性能更好。

16.2.2　自然联结

无论何时对表进行联结，应该至少有一个列出现不止一个表中(被联结的列)。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符(SELECT *)，对所有其它表的列使用明确的子集来完成。

16.2.3　外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成一下工作：

1、对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

2、列出所有产品以及订购数量，包括没有人订购的产品；

3、计算平均销售规模，包括那些至今尚未下订单的客户。

在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

使用一个简单的内部联结，检索所有客户及其订单：

输入：

SELECTcustomers.cust_id, orders.order_num

FROM customers INNER JOINorders

ON customers.cust_id = orders.cust_id;

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行：

SELECTcustomers.cust_id, orders.order_num

FROM customers LEFT OUTER JOINorders

ON customers.cust_id = orders.cust_id;

输出：

分析：这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型(而不是在WHERE子句中指定)。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN 右边的表，而LEFT指出的是OUTER JOIN左边的表)。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表(customers表)中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN。

输入：

SELECTcustomers.cust_id, orders.order_num

FROM customers RIGHT OUTER JOINorders

ON orders.cust_id = customers.cust_id;

没有*=操作符：MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS是很流行的。

外部联结的类型：存在两种基本的外部联结形式：左外部联结和右外部联结，它们之间的唯一差别是所关联的表的顺序不同。话句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

16.3　使用带聚集函数的联结

聚集函数用来汇总数据。虽然至今为止聚集函数的所有例子只是从单个表汇总数据，但这些函数也可以与联结一起使用。

检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

输入：

SELECTcustomers.cust_name,

customers.cust_id,

COUNT(orders.order_num) ASnum_ord

FROM customers INNER JOINorders

ON customers.cust_id =orders.cust_id

GROUP BY customers.cust_id;

输出：

分析：此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

聚集函数也可以方便地与其他联结一起使用：

输入：

SELECTcustomers.cust_name,

customers.cust_id,

COUNT(orders.order_num) ASnum_ord

FROM customers LEFT OUTER JOINorders

ON customers.cust_id =orders.cust_id

GROUP BY customers.cust_id;

输出：

分析：这里例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。

16.4　使用联结和联结条件

1、注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。

2、保证使用正确的联结条件，否则将返回不正确的数据。

3、应该总是提供联结条件，否则会得到笛卡儿积。

4、在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也有用，但应该在一起测试它们前分别测试每个联结。这将使故障排除更为简单。

第17章　　组合查询

17.1　组合查询

多数SQL查询只包含一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果返回。这些组合查询通常称为并(union)或复合查询(compound query)。

有两种基本情况，其中需要使用组合查询：

1、在单个查询中从不同的表返回类似结构的数据；

2、对单个表执行多个查询，按单个查询返回数据。

17.2　创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。

17.2.1　使用UNION

UNION的使用很简单，所需做的指示给出每条SELECT语句，在各条语句之间放上关键字UNION。

单条语句：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5;

输出：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE vend_id IN(1001,1002);

输出：

分析：第一条SELECT检索价格不高于5的所有物品。第二条SELECT使用IN找出供应商1001和1002生产的所有物品。

为了组合这两条语句，按如下进行：

输入：

SELECTvend_id,prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

UNION

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE vend_id IN(1001,1002);

输出：

分析：这条语句有前面的两条SELECT语句组成，语句中用UNION关键字分隔。UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

作为参考，这里给出使用多条WHERE子句而不是使用UNION的相同查询：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

OR vend_id IN(1001,1002);

输出：

分析：在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表(而不是单个表)中检索数据的情形，使用UNION可能会使处理更简单。

17.2.2　UNION规则

1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔(因此，如果组合4条SELECT语句，将要使用3个UNION关键字)。

2、UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。

3、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型(例如，不同的数值类型或不同的日期类型)。

17.2.3　包含或取消重复的行

UNION从查询结果集中自动去除了重复的行(换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样)。在使用UNION时，重复的行呗自动取消。

这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

UNION ALL

SELECTvend_id,prod_id,prod_price

FROMproducts

WHERE vend_id IN(1001,1002);

输出：

分析：使用UNION ALL，MySQL不取消重复的行，因此这里的例子返回9行，其中由一行出现两次。

UNION与WHERE：UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE自己完成不了的工作。如果确实需要每个条件的匹配行全部出现(包括重复行)，则必须使用UNION ALL而不是WHERE。

17.2.4　对组合查询结果排序

SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

排序前面UNION返回的结果：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

UNION

SELECTvend_id,prod_id,prod_price

FROMproducts

WHERE vend_id IN(1001,1002)

ORDER BY vend_id, prod_price;

输出：

分析：这条UNION在最后一条SELECT语句中使用了ORDER BY子句。虽然ORDER BY子句似乎指示最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

组合不同的表：使用UNION的组合查询可以应用不同的表。

第18章　　全文本搜索

18.1　理解全文本搜索

并非所有引擎都支持全文本搜索：MySQL支持几种基本的数据库引擎。并非所有的引擎都支持全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配(哪些行包含它们)，哪些词不匹配，它们的频率等等。

18.2.1　启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

输入：

CREATE TABLEproductnotes

(

note_id　　int 　　　　　 NOT NULLAUTO_INCREMENT,

prod_id　　char(10) NOT NULL,

note_date　datetime NOT NULL,

note_text text NULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

)ENGINE = MyISAM;

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

可以在创建表时指定FULLTEXT，或者在稍后指定(在这种情况下所有已有数据必须立即索引)。

不要在导入数据时使用FULLTEXT：更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据(而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间)。

18.2.2　进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('rabbit');

输出：

分析：此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against(‘rabbit’)指定词rabbit作为搜索问文本。由于由两行包含词rabbit，这两个行被返回。

使用完整的Match()说明：传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。

搜索不区分大小写：除非使用BINARY方式，否则全文本搜索不区分大小写。

刚才的搜索可以简单地用LIKE子句完成：

输入：

SELECTnote_text

FROMproductnotes

WHERE note_text LIKE '%rabbit%';

输出：

---恢复内容结束---

第10章　　创建计算字段

10.1　计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

字段(field)：基本上与列(column)的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

10.2　拼接字段

vendors表包含供应商名和位置信息。假如要生成一个供应商报表，需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。

此报表需要单个值，而表中数据存储在两个列vend_name和vend_country中。此外，需要用括号将vend_country括起来，这些东西都没有明确存储在数据库表中。

拼接(concatenate)：将值联结一起构成单个值。

解决方法是把两个列拼接起来。在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

MySQL的不同之处：多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。当把SQL语言转换成MySQL语句时一定要把这个区别铭记在心。

输入：

SELECT Concat(vend_name,' (',vend_country,')')

FROMvendors

ORDER BY vend_name;

输出：

分析：Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

上面的SELECT语句连接以下4个元素：

1、存储在vend_name列中的名字；

2、包含一个空格和一个左小括号的串；

3、存储在vend_country列中的国家；

4、包含一个右小括号的串。

通过删除数据右侧多余的空格来整理数据，这可以使用MySQL的RTrim()函数来完成：

输入：

SELECT Concat(RTrim(vend_name),' (',vend_country,')')

FROMvendors

ORDER BY vend_name;

分析：RTrim()函数去掉值右边的所以空格。通过使用RTrim()，各个列都进行了整理。

Trim函数：MySQL除了支持RTrim()(去掉串右边的空格)，还支持LTrim()(去掉串左边的空格)以及Trim()(去掉串左右两边的空格)。

使用别名

SQL支持列别名。别名(alias)时一个字段或值的替换名。别名用AS关键字赋予。

输入：

SELECT Concat(RTrim(vend_name),' (',vend_country,')') ASvend_title

FROMvendors

ORDER BYvend_name;

输出：

别名的其他用途：常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它，在原来的名字含混或容易误解时扩充它，等等。

导出列：别名有时也称为导出列(derived column)，不管称为什么，它们所代表的都是相同的东西。

10.3　执行算数计算

计算字段的另一常见用途时对检索出的数据进行算数计算。举一个例子，orders表包含收到的所有订单，orderitems表包含每个订单中的各项物品。

下面的SQL语句检索订单号20005中的所有物品：

SELECTprod_id, quantity, item_price

FROMorderitems

WHERE order_num = 20005;

输出：

items_price列包含订单中每项物品的单价。如下汇总物品的价格(单价乘以订购数量)：

输入：

SELECT prod_id, quantity, item_price, quantity*item_price ASexpanded_price

FROMorderitems

WHERE order_num = 20005;

输出：

分析：输出中显示的expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算列，就像使用其他列一样。

MySQL支持以下列出的基本算数操作符。此外，小括号可用来区分优先顺序。

第11章　　使用数据处理函数

11.1　函数

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

如果不使用这些函数，编写某些应用程序代码会很艰难。

如果你决定使用函数，应该保证做好代码注释，以便以后你(或其他人)能确切地知道所编写SQL代码地含义。

11.2　使用函数

大多数SQL实现支持以下类型地函数：

1、用于处理文本串(如删除或填充值，转换值为大写或小写)的文本函数；

2、用于在数值数据上进行算数操作(如返回绝对值，进行代数运算)；

3、用于处理日期和时间值并从这些值中提取特定成分(例如，返回两个日期之差，检查日期有效性等)的日期和时间函数。

4、返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数。

11.2.1　文本处理函数

使用Uooer()函数：

输入：

SELECT vend_name, Upper(vend_name) ASvend_name_upcase

FROMvendors

ORDER BYvend_name;

输出：

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

以下列出了某些常用的文本处理函数：

使用Soundex()函数的例子：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_contact = 'Y.Lie';

使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE Soundex(cust_contact) = Soundex('Y lie');

输出：

11.2.2　日期和时间处理函数

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

以下列出了某些常用的日期和时间处理函数：

因此，基本的日期比较应该很简单：

SELECTcust_id, order_num

FROMorders

WHERE order_date = '2005-09-01';

输出：

输入：

SELECTcust_id, order_num

FROMorders

WHERE Date(order_date) = '2005-09-01';

检索出2005年9月下的所有订单(简单的相等测试不行，因为它也要匹配月份中的天数)：

方法一输入：

SELECTcust_id, order_num

FROMorders

WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

输出：

分析：BETWEEN操作符用来把2005-09-01和2005-09-30定义为一个要匹配的日期范围。

方法二(不需要记住每个月中有多少天或不需要操心闰年2月)输入：

SELECTcust_id, order_num

FROMorders

WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

11.2.3　数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期-时间处理函数的使用那么频繁。

在主要DBMS的函数中，数值函数是最一致最统一的函数。

以下是常用的数值处理函数：

第12章　汇总数据

12.1　聚集函数

1、确定表中行数(或者满足某个条件或包含某个特定值的行数)。

2、获得表中行组的和。

3、找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。

聚集函数(aggregate function)：运行在行组上，计算和返回单个值的函数。

12.1.1　AVG()函数

AVG()函数通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

使用AVG()返回products表中所有产品的平均价格：

输入：

SELECT AVG(prod_price) ASavg_price

FROMproducts;

输出：

分析：此SELECT语句返回值avg_price，它包含products表中所有产品的平均价格，avg_price是一个别名。

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

返回特定供应商提供产品的平均价格：

输入：

SELECT AVG(prod_price) ASavg_price

FROMproducts

WHERE vend_id = 1003;

输出：

只用于单列：AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，为了获得多个列的平均值，必须使用多个AVG()函数。

NULL值：AVG()函数忽略列值位NULL的行。

12.1.2　COUNT()函数

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

COUNT()函数有两种使用方式。

使用COUNT(*)对表中的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

下面的例子返回customers表中客户的总数：

输入：

SELECT COUNT(*) ASnum_cust

FROMcustomers;

输出：

分析：在此例子中，利用COUNT(*)对所有行计数，不管行中各列有什么值。计数值在num_cust中返回。

下面的例子只对具有电子邮件地址的客户计数：

输入：

SELECT COUNT(cust_email) ASnum_cust

FROMcustomers;

输出：

分析：这条语句使用COUNT(cust_email)对cust_email列中有值的行进行计数。在此例子中，cust_email的计数位3(表示5个客户中只有3个客户有电子邮件地址)。

NULL值：如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号(*)，则不忽略。

12.1.3　MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：

输入：

SELECT MAX(prod_price) ASmax_price

FROMproducts;

输出：

分析：MAX()返回products表中最贵的物品的价格。

NULL值：MAX()函数忽略列值为NULL的行。

12.1.4　MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名，如下所示：

输入：

SELECT MIN(prod_price) ASmin_price

FROM products;

输出：

分析：其中MIN()返回products表中最便宜物品的价格。

NULL值：MIN()函数忽略列值为NULL的行。

12.1.5　SUM()函数

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

下面举一个例子，orderitems表包含订单中实际的物品，每个物品有相应的数量(quantity)。可如下检索所订购物品的总数(所有quantity值之和)：

输入：

SELECT SUM(quantity) ASitems_ordered

FROMorderitems

WHERE order_num = 20005;

输出：

分析：函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。

SUM()也可以用来合计计算值。

合计每项物品的item_price*quantity，得出总的订单金额：

输入：

SELECT SUM(item_price*quantity) AStotal_price

FROMorderitems

WHERE order_num = 20005;

输出：

12.2　聚集不同值

对所有的行执行计算，指定ALL参数或不给参数(因为ALL是默认行为)；

只包含不同的值，指定DISTINCT参数。

ALL为默认：ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

输入：

SELECT AVG(DISTINCT prod_price) ASavg_price

FROMproducts

WHERE vend_id = 1003;

输出:

分析：在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

12.3　组合聚集函数

实际上SELECT语句可根据需要包含多个聚集函数。

输入：

SELECT COUNT(*) ASnum_items,

MIN(prod_price) ASprice_min,

MAX(prod_price) ASprice_max,

AVG(prod_price) ASprice_avg

FROM products;

输出：

分析：这里用单条SELECT语句执行了4个聚集计算，返回4个值(products表中物品的数目，产品价格的最高、最低以及平均值)。

第13章　　分组数据

13.1　数据分组

SQL聚集函数可用来汇总数据，这使我们能够对行进行计数，计算和平均数，获得最大和最小值而不用检索所有数据。

目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。

返回供应商1003提供的产品数目：

输入：

SELECT COUNT(*) ASnum_prods

FROMproducts

WHERE vend_id = 1003;

输出：

分析：分组允许把数据分为多个逻辑组，以便对没个组进行聚集计算。

13.2　创建分组

分组是在SELECT语句的GROUP BY子句中建立的。

输入：

SELECT vend_id, COUNT(*) ASnum_prods

FROMproducts

GROUP BY vend_id;

输出：

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY 子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用ROLLUP：使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总(针对每个分组)的值，如下所示：

输入：

SELECT vend_id, COUNT(*) ASnum_prods

FROMproducts

GROUP BY vend_id WITH ROLLUP;

输出：

13.3　过滤分组

HAVING非常类似于WHERE。WHERE过滤行，而HAVING过滤分组。

HAVING支持所有WHERE操作符，它们的句法时相同的，只是关键字有差别。

输入：

SELECT cust_id, COUNT(*) ASorders

FROMorders

GROUP BYcust_id

HAVING COUNT(*) >=2;

输出：

分析：最后增加了一行HAVING子句，它过滤COUNT(*) >=2(两个以上的订单)的那些分组。

同时使用WHERE和HAVING子句列出具有2个(含)以上、价格为10(含)以上的产品的供应商：

输入：

SELECT vend_id, COUNT(*) ASnum_prods

FROMproducts

WHERE prod_price >= 10

GROUP BYvend_id

HAVING COUNT(*) >= 2;

输出：

输入：

SELECT vend_id,COUNT(*) ASnum_prods

FROMproducts

GROUP BYvend_id

HAVING COUNT(*) >= 2;

输出：

13.4　分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作，但它们时非常不同的。

输入：

SELECT order_num, SUM(quantity*item_price) ASordertotal

FROMorderitems

GROUP BYorder_num

HAVING SUM(quantity*item_price) >=50;

输出：

为按总计订单价格排序输出，需要添加ORDER BY子句，如下所示：

SELECT order_num, SUM(quantity*item_price) ASordertotal

FROMorderitems

GROUP BYorder_num

HAVING SUM(quantity*item_price) >=50

ORDER BY ordertotal;

输出：

13.5　SELECT 子句顺序

第14章　　使用子查询

14.1　子查询

查询(query)：任何SQL语句都是查询。但此术语一般指SELECT语句。

SQL还允许子查询(subquery)，即嵌套在其他查询中的查询。

14.2　利用子查询进行过滤

现在，假如需要列出订购物品TN2的所有客户，应该怎样检索？

(1)检索包含物品TN2的所有订单的编号；

(2)检索具有前一步骤列出的订单编号的所有客户的ID；

(3)检索前一步步骤返回的所有客户ID的客户信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

也可以使用子查询来把3个查询组合成一条语句。

第一条SELECT语句的含义很明确，对于prod_id为TNT2的所有订单物品，它检索其order_num列，输出列出两个包含此物品的订单。

输入：

SELECTorder_num

FROMorderitems

WHERE prod_id = 'TNT2';

输出：

下一步，查询具有订单20005和20007的客户ID。利用IN子句，编写如下的SELECT语句：

输入：

SELECTcust_id

FROMorders

WHERE order_num IN(20005,20007);

输出：

现在，把第一个查询(返回订单号的那一个)变为子查询组合两个查询。

输入：

SELECTcust_id

FROMorders

WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

输出：

分析：在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。

首先，它执行了查询

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'

此查询返回两个订单号：20005和20007。然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。

外部查询变成

SELECT　　cust_id

FROMorders

WHERE order_num IN(20005,20007);

可以看到，输出是正确的并且与前面硬编码WHERE子句返回的值相同。

现在得到了订购物品TNT2的所有客户的ID。下一步时检索这些客户ID的客户信息。检索两列的SQL语句为：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_id IN(10001,10004);

输出：

可以把其中的WHERE子句转换为子查询而不是硬编码这些客户ID：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_id IN(SELECTcust_id

FROMorders

WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

输出：

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于(=)、不等于(<>)等。

子查询和性能：这里给出的代码有效并获得所需的结果，但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。

14.3　作为计算字段使用子查询

使用子查询的另一方法时创建计算字段。加入需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

为了执行这个操作，遵循下面的步骤：

(1)从customers表中检索客户列表。

(2)对于检索出的每个客户，统计其在orders表中的订单数目。

可使用SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对改客户的订单进行计数。

对客户10001的订单进行计数：

输入：

SELECT COUNT(*) ASorders

FROMorders

WHERE cust_id = 10001;

为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询：

输入：

SELECTcust_name,cust_state,

(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) ASorders

FROMcustomers

ORDER BY cust_name;

输出：

子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名。

虽然子查询在构造这种SELECT语句时极有用，但必须注意限制有歧义性的列名。

第15章　　联结表

15.1　联结

SQL 最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作。

15.1.1　关系表

外键(foreign)：外键为某个表中的一列，它包含另一个表中的主键值，定义了两个表之间的关系。

这样做的好处如下：

1、供应商信息不重复，从而不浪费时间和空间；

2、如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；

3、由于数据无重复，显然数据是一致的，这使得处理数据更简单。

总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比关系数据库要好。

可伸缩性(scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。

15.1.2　为什么要使用联结

15.2　创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

输入：

SELECTvend_name,prod_name,prod_price

FROMvendors, products

WHERE vendors.vend_id =products.vend_id

ORDER BY vend_name,prod_name;

输出：

15.2.1　WHERE子句的重要性

在一条SELECT语句中联结几个表时，相应的关系时在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西

笛卡儿积(cartesian products)：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

输入：

SELECTvend_name,prod_name,prod_price

FROMvendors,products

ORDER BY vend_name,prod_name;

输出：

不要忘了WHERE子句：应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该i保证WHERE子句的正确性。

叉联结：有时我们会听到返回称为叉联结(cross join)的笛卡儿积的联结类型。

15.2.2　内部联结

输入：

SELECTvend_name,prod_name,prod_price

FROM vendors INNER JOINproducts

ON vendors.vend_id = products.vend_id;

输出：

输出和下面的MySQL语句返回完全相同的数据：

SELECTvend_name,prod_name,prod_price

FROMvendors,products

WHERE vendors.vend_id = products.vend_id;

15.2.3　联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

输入：

SELECTprod_name,vend_name,prod_price,quantity

FROMorderitems,products,vendors

WHERE products.vend_id =vendors.vend_id

AND orderitems.prod_id =products.prod_id

AND order_num = 20005;

输出：

子查询，返回订购产品TNT2的客户列表：

输入：

SELECTcust_name,cust_contact

FROMcustomers

WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));

子查询并不总是执行复杂SELECT操作的最有效的方法。

使用联结的相同查询：

输入：

SELECTcust_name, cust_contact

FROMcustomers, orders, orderitems

WHERE customers.cust_id =orders.cust_id

AND orderitems.order_num =orders.order_num

AND prod_id = 'TNT2';

输出：

第16章　　创建高级联结

16.1　使用表别名

给列起别名:

输入：

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') ASvend_title

FROMvendors

ORDER BY vend_name;

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

1、缩短SQL语句；

2、允许在单条SELECT语句中多次使用相同的表。

输入：

SELECTcust_name, cust_contact

FROM customers AS c, orders AS o, orderitems ASoi

WHERE c.cust_id =o.cust_id

AND oi.order_num =o.order_num

AND prod_id = 'TNT2';

列别名只在查询执行中使用。与列别名不一样，表别名不返回客户机。

16.2　使用不同类型的联结

16.2.1　自联结

使用表别名的主要原因之一时能在单条SELECT语句中不知一次引用相同的表。

输入：

SELECTprod_id,prod_name

FROMproducts

WHERE vend_id =(SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

输出：

使用联结的相同查询：

输入：

SELECTp1.prod_id, p1.prod_name

FROM products AS p1, products ASp2

WHERE p1.vend_id =p2.vend_id

AND p2.prod_id = 'DTNTR';

输入：

16.2.2　自然联结

16.2.3　外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成一下工作：

1、对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

2、列出所有产品以及订购数量，包括没有人订购的产品；

3、计算平均销售规模，包括那些至今尚未下订单的客户。

在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

使用一个简单的内部联结，检索所有客户及其订单：

输入：

SELECTcustomers.cust_id, orders.order_num

FROM customers INNER JOINorders

ON customers.cust_id = orders.cust_id;

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行：

SELECTcustomers.cust_id, orders.order_num

FROM customers LEFT OUTER JOINorders

ON customers.cust_id = orders.cust_id;

输出：

输入：

SELECTcustomers.cust_id, orders.order_num

FROM customers RIGHT OUTER JOINorders

ON orders.cust_id = customers.cust_id;

没有*=操作符：MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS是很流行的。

16.3　使用带聚集函数的联结

聚集函数用来汇总数据。虽然至今为止聚集函数的所有例子只是从单个表汇总数据，但这些函数也可以与联结一起使用。

检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

输入：

SELECTcustomers.cust_name,

customers.cust_id,

COUNT(orders.order_num) ASnum_ord

FROM customers INNER JOINorders

ON customers.cust_id =orders.cust_id

GROUP BY customers.cust_id;

输出：

聚集函数也可以方便地与其他联结一起使用：

输入：

SELECTcustomers.cust_name,

customers.cust_id,

COUNT(orders.order_num) ASnum_ord

FROM customers LEFT OUTER JOINorders

ON customers.cust_id =orders.cust_id

GROUP BY customers.cust_id;

输出：

分析：这里例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。

16.4　使用联结和联结条件

1、注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。

2、保证使用正确的联结条件，否则将返回不正确的数据。

3、应该总是提供联结条件，否则会得到笛卡儿积。

第17章　　组合查询

17.1　组合查询

有两种基本情况，其中需要使用组合查询：

1、在单个查询中从不同的表返回类似结构的数据；

2、对单个表执行多个查询，按单个查询返回数据。

17.2　创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。

17.2.1　使用UNION

UNION的使用很简单，所需做的指示给出每条SELECT语句，在各条语句之间放上关键字UNION。

单条语句：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5;

输出：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE vend_id IN(1001,1002);

输出：

分析：第一条SELECT检索价格不高于5的所有物品。第二条SELECT使用IN找出供应商1001和1002生产的所有物品。

为了组合这两条语句，按如下进行：

输入：

SELECTvend_id,prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

UNION

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE vend_id IN(1001,1002);

输出：

分析：这条语句有前面的两条SELECT语句组成，语句中用UNION关键字分隔。UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。

作为参考，这里给出使用多条WHERE子句而不是使用UNION的相同查询：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

OR vend_id IN(1001,1002);

输出：

17.2.2　UNION规则

1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔(因此，如果组合4条SELECT语句，将要使用3个UNION关键字)。

2、UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。

3、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型(例如，不同的数值类型或不同的日期类型)。

17.2.3　包含或取消重复的行

UNION从查询结果集中自动去除了重复的行(换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样)。在使用UNION时，重复的行呗自动取消。

这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

UNION ALL

SELECTvend_id,prod_id,prod_price

FROMproducts

WHERE vend_id IN(1001,1002);

输出：

分析：使用UNION ALL，MySQL不取消重复的行，因此这里的例子返回9行，其中由一行出现两次。

17.2.4　对组合查询结果排序

排序前面UNION返回的结果：

输入：

SELECTvend_id, prod_id, prod_price

FROMproducts

WHERE prod_price <= 5

UNION

SELECTvend_id,prod_id,prod_price

FROMproducts

WHERE vend_id IN(1001,1002)

ORDER BY vend_id, prod_price;

输出：

组合不同的表：使用UNION的组合查询可以应用不同的表。

第18章　　全文本搜索

18.1　理解全文本搜索

18.2.1　启用全文本搜索支持

一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

输入：

CREATE TABLEproductnotes

(

note_id　　int 　　　　　 NOT NULLAUTO_INCREMENT,

prod_id　　char(10) NOT NULL,

note_date　datetime NOT NULL,

note_text text NULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

)ENGINE = MyISAM;

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

可以在创建表时指定FULLTEXT，或者在稍后指定(在这种情况下所有已有数据必须立即索引)。

18.2.2　进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('rabbit');

输出：

使用完整的Match()说明：传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。

搜索不区分大小写：除非使用BINARY方式，否则全文本搜索不区分大小写。

刚才的搜索可以简单地用LIKE子句完成：

输入：

SELECTnote_text

FROMproductnotes

WHERE note_text LIKE '%rabbit%';

输出：

分析：上述两条SELECT语句都不包含ORDER BY 子句。后者(使用LIKE)以不特别有用的顺序返回数据。前者(使用全文本搜索)返回以文本匹配的良好程度配许的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的登记比作为第20个词的行高。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能使你真正想要的行)。

输入：

SELECTnote_text,

Match(note_text) Against('rabbit') ASrank

FROM productnotes;

输出：

分析：在SELECT而不是WHERE子句中使用Match()和Against()。这里所有行都被返回(因为没有WHERE子句)。Match()和Against()用来建立一个计算列(别名为rank)，此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见，不包含词rabbit的行登记为0(因此不被前一例子中的WHERE子句选择)。确实包含词rabbit的两个行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。

排序多个搜索项：如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。

全文搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

18.2.3　使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。

1、找出所有提到anvils的注释。

2、找出只有一个注释包含词anvils。

3、找出可能与你的搜索有关的所有其他行，级市它们不包含词anvils。

在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

1、首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；

2、其次，MySQL检查这些匹配并选择所有有用的词；

3、再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

首先进行一个简单的全文本搜索，没有查询扩展：

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('anvils');

输出：

分析：只要一行包含词anvils，因此只返回一行。

下面时相同的搜索，这次使用查询扩展：

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

输出：

分析：这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行的两个词(customer和recommend)，所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级第三。第三行确实也没有设计ancils(按它们的产品名)。

查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。

行越多越好：表中的行越多(这些行中的文本就越多)，使用查询扩展返回的结果越好。

18.2.　布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式(boolean mode)。

以布尔方式，可以提供如下内容的细节：

1、要匹配的词；

2、要排斥的词(如果某行包含这个词，则不反悔该行，即使它包含其他指定的词也是如此)；

3、排列提示(指定某些词比其他词更重要，更重要的词等级更高)；

4、表达式分组；

5、另外一些内容。

即使没有FULLTEXT索引也可以使用：布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，级市没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。

演示IN BOOLEAN MODE：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

输出：

分析：此全文本搜索检索包含词heavy的所有行(有两行)。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔操作符的结果相同。

匹配包含heavy但不包含任意以rope开始的词的行：

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('heavy - rope*' IN BOOLEAN MODE);

输出：

分析：至此只返回一行。这一次仍然匹配词heavy，但 -rope*明确地指示MySQL排除包含rope*(任何以rope开始的词，包括ropes)的行，这就是为什么上一个例子的第一行被排除的原因。

-排除一个词，而*时截断操作符(可相像为用于词尾的一个通配符)。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

输出：

分析：这个搜索匹配包含词rabbit和bait的行。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

输出：

分析：没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

输出：

分析：这个搜索匹配短语rabbit bait 而不是匹配两个词rabbit和bait。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('>rabbit

输出：

分析：匹配rabbit和carrot，增加前者的等级，降低后者的等级。

输入：

SELECTnote_text

FROMproductnotes

WHERE Match(note_text) Against('+safe +(

输出：

分析：这个搜索匹配词safe和combination，降低后者的等级。

排序而不排列：在布尔方式中，不按等级值降序排序返回的行。

18.2.5　全文本搜索的使用说明

1、在搜索全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要，这个数目可以更改)。

2、MySQL带有一个内建的非用词(stopword)列表，这些词在索引全文数据时总是被忽略。如果需要，可以覆盖这个列表(请参照MySQL文档以了解如何完成此工作)。

3、许多词出现的频率很高，搜索它们没有用处(返回太多的结果0。因此，MySQL规定了一条50% 规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。

4、如果表中的行数少于3行，则全文本搜索不返回结果(因为每个词或者不出现，或者至少出现在50%的行中)。

5、忽略词中的单引号。例如，don't索引为don't。

6、不具有词分隔符(包括汉语和日语)的语言不能恰当地返回全文本搜索结果。

7、仅在MyISAM数据库引擎中支持全文本搜索。

8、没有邻近操作符：邻近操作符是许多全文本搜索支持的一个特性，它能搜索相邻的词(在相同的句子中、相同的段落中或者下特定数目的词的部分中，等等)。

第19章　　插入数据

19.1　数据插入

INSERT是用来插入(或添加)行到数据库表的。插入可以用几种方式使用：

1、插入完整的行；

2、插入行的一部分；

3、插入多行；

4、插入某些查询的结果。

插入及系统安全：可针对每个表或每个客户，利用MySQL的安全机制禁止使用INSERT语句。

19.2　插入完整的行

把数据插入表中的最简单的方法是使用基本的INSERT语法，它要求指定表名和被插入到新行中的值。

输入：

INSERT INTOCustomers

VALUES(

NULL,

'Pep E. LaPew',

'100 Main Street',

'Los Angeles',

'CA',

'90046',

'USA',

NULL,

NULL);

没有输出：INSERT语句一般不会产生输出。

分析：此例子插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值(假定允许对该列指定空值)。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。你不想给出一个值(这是MySQL的工作0，又不能省略此列，所以指定一个NULL值(它被MySQL忽略，MySQL在这里插入下一个可用的cust_id值)。

虽然这种语法简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖与表中列的定义次序，并且还依赖于次序容易获得的信息。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此，编写依赖于特定次序的SQL语句时很不安全的。如果这样做，有时难免会出问题。

编写INSERT语句的更安全(不过更繁琐)的方法如下：

输入：

INSERT INTOCustomers(

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email

)

VALUES(

'Pep E. LaPew2',

'100 Main Street2',

'Los Angeles2',

'CA',

'90046',

'USA',

NULL,

NULL);

分析：此例子完成与钱一个INSERT语句完全相同的工作，但在表名后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项。VALUES中的第一个值对应与第一个指定的列名。第二个值对应于第二个列名，如此等等。

因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。其优点是，即使表的结构改变，此INSERT语句仍然能正确工作。你会发现cust_id的NULL值是不必要的，cust_id列并没有出现在列表中，所以不需要任何值。

下面的INSERT语句填充所有列(与前面的一样)，但以一种不同的次序填充。因为给出了列名，所以插入结果仍然正确：

输入：

INSERT INTOcustomers(

cust_name,

cust_contact,

cust_email,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country)

VALUES(

'Pep E. LaPew',

NULL,

'100 Main Street',

'Los Angeles',

'GA',

'90046',

'USA');

总是使用列的列表：一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化。

仔细地给出值：不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对每个列出的列给出一个值。如果不这样，将会产生一条错误消息，相应的行插入不成功。

使用这种语法，还可以省略列。这表示可以只给某些列提供值，给其他列不提供值。(当列名被明确列出时，cust_id可以省略)。

省略列：如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。

1、该列定义为允许NULL值(无值或空值)。

2、在表定义在中给出默认值。这表示如果不给出值，将使用默认值。

3、如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误信息，并且相应的行插入不成功。

提高整体性能：INSERT操作可能很耗时(特别是由很多索引需要更新时)，而且它可能降低等等待处理的SELECT语句的性能。

如果数据检索时最重要的，则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级。

INSERT LOW_PRIORITY INTO

这也适用UPDATE和DELETE。

19.3　插入多个行

INSERT可以插入一行到一个表中。也可以使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束。

输入：

INSERT INTOCustomers(

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

)

VALUES(

'PeP E. LaPew',

'100 Main Stree',

'Los Angeles',

'CA',

'90046',

'USA');

INSERT INTOCustomers(

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

)

VALUES(

'M. Martian',

'42 Galaxy Way',

'New York',

'NY',

'112113',

'USA');

或者，只要每条INSERT与语句中的列名(和次序)相同，可以如下组合各语句：

输入：

INSERT INTOCustomers(

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country

)

VALUES(

'PeP E. LaPew',

'100 Main Stree',

'Los Angeles',

'CA',

'90046',

'USA'),

(

'M. Martian',

'42 Galaxy Way',

'New York',

'NY',

'112113',

'USA');

分析：其中单条INSERT语句中由多组值，每组值用一对圆括号括起来，用逗号分隔。

提高INSERT的性能：此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。

19.4　插入检索出的数据

INSERT一般用来给标插入一个指定列值的行。但是INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT，顾名思义，它是由一条INSERT语句和一条SELECT语句组成的。

假如你想从另一表中合并客户列表到你的customers标。不需要每次读取一行，然后再将它用INSERT插入，可以如下进行：

第20章　　更新和删除数据

20.1　更新数据

为了更新(修改)表中的数据，可使用 UPDATE 语句。可采用两种方式使用 UPDATE：

(1)更新表中特定行；

(2)更新表中所有行。

不要省略 WHERE 子句：在使用 UPDATE 时一定要注意细心。因为稍不注意，就会更新表中所有行。

基本的 UPDATE 语句由3部分组成，分别是：

(1)要更新的表；

(2)列名和它们的新值；

(3)确定要更新行的过滤条件。

客户10005现在有了电子邮件地址，因此他的记录需要更新，语句如下：

输入：

UPDATEcustomersSET cust_email = 'email@fudd.com'

WHERE cust_id = 10005;

UPDATE 语句总是以要更新的表的名字开始。在此例子中，要更新的表的名字为 customers。SET 命令用来将新值赋给被更新的列。如这里所示，SET 子句设置cust_email 列为指定的值：

SET cust_email = 'elmer@fudd.com'

UPDATE 语句以 WHERE 子句结束，它告诉 MYSQL 更新哪一行。没有 WHERE 子句，MYSQL 将会用这个电子邮件地址更新 customers 表中所有行，这不是我们所希望的。

更新多个列的语法稍有不同，如更新客户10005的 cust_name 和 cust_email 列：

输入：

UPDATEcustomersSET cust_name = 'The Fudds',

cust_email= 'elmer@fudd.com'

WHERE cust_id = 10005;

在更新多个列时，只需要使用单个 SET 命令，每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。

在UPDATE语句中使用子查询：UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。

IGNORE关键字：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时一个出现错误，则整个 UPDATE 操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误，也继续进行更新，可使用 IGNORE 关键字。如下所示：

UPDATE IGNORE customers...

为删除某个列的值，可设置它为NULL(假定表定义允许 NULL 值)。如下进行：

输入：

UPDATEcustomersSET cust_email = NULL

WHERE cust_id = 10005;

其中 NULL 用来去除cust_email 列中的值。

20.2　删除数据

为了从一个表中删除(去掉)数据，使用 DELETE 语句。可以两种方式使用 DELETE ：

(1)从表中删除特定的行；

(2)从表中删除所有行。

不要省略 WHERE 子句：在使用 DELETE 时，一定要注意细心。因为稍不注意，就会错误狄删除表中所有行。

下面的语句从customers表中删除客户10006：

输入：

DELETE FROMcustomersWHERE cust_id = 10006;

DELETE FROM 要求制定从中删除数据的表名。WHERE子句过滤要删除的行。

DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。为了删除指定的列，请使用 UPDATE 语句。

DELETE 语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。

如果想从表中删除所有行，不要使用 DELETE 。可使用 TRUNCATE TABLE语句，它完成相同的工作，但速度更快(TRUNCRATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据)。

20.3　更新和删除的指导原则

(1)除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

(2)保证每个表都有主键，尽可能像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。

(3)在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE子句不正确。

(4)使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

MySQL 没有撤销(undo)按钮。应该非常小心地使用 UPDATE 和DELETE，否则你会发现自己更新或删除了错误的数据。

第21章

21.1　创建表

MySQL不仅用于表数据操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。

一般有两种创建表的方法：

使用具有交互式创建和管理表的工具；

表也可以直接用MySQL语句操纵。

为了用程序创建表，可使用SQL的CREATE TABLE语句。值得注意的是，在使用交互式工具时，实际上使用的是MySQL语句。但是，这些语句不是用户编写的，界面工具会自动生成并执行相应的MySQL语句(更改现有表时也是这样)。

21.1.1　表创建基础

为利用CREATE TABLE创建表，必须给出下列信息：

新表的名字，在关键字CREATE TABLE之后给出；

表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能会包括其他关键字或选项，但至少要包括表的名字和列的细节。下面的MySQL语句创建customers表：

输入：

CREATE TABLEcustomers

(

cust_id int NOT NULLAUTO_INCREMENT,

cust_name char(50) NOT NULL,

cust_address char(50) NULL,

cust_city char(50) NULL,

cust_state char(5) NULL,

cust_zip char(10) NULL,

cust_country char(50) NULL,

cust_contact char(50) NULL,

cust_email char(255) NULL,

PRIMARY KEY(cust_id)

) ENGINE=InnoDB;

分析：表名紧跟在CREATE TABLE关键字后面。实际的表定义(所有列)括在圆括号之中。各列之间用逗号分隔。这个表由9列组成，每列的定义以列名(它在表中必须是唯一的)开始，后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，cust_id指定作为主键列。整条语句由右圆括号后的分号结束。

在创建新表时，指定的表明必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配，它只是查看表名是否存在，并且仅在表名不存在时创建它。

21.1.2　使用NULL值

NULL值就是没有值或缺值，允许NULL值得列允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。

每个表列或者时NULL列，或者时NOT NULL列，这种状态在创建时由表的定义规定。

输入：

CREATE TABLEorders

(

order_num int NOT NULLAUTO_INCREMENT,

order_date datetime NOT NULL,

cust_id int NOT NULL,

PRIMARY KEY(order_num)

) ENGINE=InnoDB;

分析：这条语句创建本书中所用的orders表。orders包含3个列，分别是订单号、订单日勤和客户ID。所有3个列都需要，因此每个列的定义都含有关键字NOT NULL。这将会组织插入没有值的列。如果试图插入没有值的列，将返回错误，且插入失败。

下一个列子将创建混合了NULL和NOT NULL列的表：

输入：

CREATE TABLEvendors

(

vend_id int NOT NULLAUTO_INCREMENT,

vend_name char(50) NOT NULL,

vend_address char(50) NULL,

vend_city char(50) NULL,

vend_state char(5) NULL,

vend_zip char(10) NULL,

vend_country char(50) NULL,

PRIMARY KEY(vend_id)

) ENGINE=InnoDB;

分析：这条语句创建本书中使用的vendors表。供应商ID和供应商名字列是必须的，因此指定为NOT NULL。其余5个列全都允许NULL值，所以不指定NOT NULL。NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。

不要把NULL值与空串乡混淆。NULL值是没有值，它不是空串，如果指定‘’(两个单引号，期间没有字符)，这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

21.1.3　主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

单个列作为主键，其中主键使用以下的类似的语句定义：

PRIMARY KEY(vend_id)

为创建由多个列组成的主键，应该以逗号分隔的列表给出个列名，如下所示：

CREATE TABLEorderitems

(

order_num int NOT NULL,

order_item int NOT NULL,

prod_id char(10) NOT NULL,

quantity int NOT NULL,

item_price decimal(8,2) NOT NULL,

PRIMARY KEY(order_num, order_item)

) ENGINE=InnoDB;

orderitems表包含orders表中每个订单的细节。每个订单由多项物品，但每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等等。因此，订单号(order_num列)和订单物品(order_item列)的组合是唯一的。从而适合作为主键，其定义为：

PRIMARY KEY (order_num, order_item)

主键可以在创建表时定义(如这里所示)，或者在创建表之后定义。

主键和NULL值：主键为其唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列并不能作为唯一标识。

21.1.4　使用AUTO_INSCREMENT

AUTO_INSCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量(从而才有这个关键字AUTO_INSCREMENT)，给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。

每个表只允许一个AUTO_INSCREMENT列，而且它必须被索引(如，通过使它成为主键)。

覆盖AUTO_INSCREMENT：如果一个列被指定为AUTO_INSCREMENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的(至今尚未使用过)即可，该值将被用来替代自动生成的值，后续的增量将开始使用该手工插入的值。

确定AUTO_INSCREMENT值：让MySQL生成(通过自动增量)主键的一个特点时你不知道这些值都是谁。

使用last_insert_id()函数

SELECT last_insert_id()

此语句返回最后一个AUTO_INSCREMENT值，然后可以将它用于后续的MySQL语句。

21.1.5　指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

CREATE TABLEorderitems

(

order_num int NOT NULL,

order_item int NOT NULL,

prod_id char(10) NOT NULL,

quantity int NOT NULL,

item_price decimal(8,2) NOT NULL,

PRIMARY KEY(order_num, order_item)

) ENGINE=InnoDB;

分析：这条语句创建包含组成订单的各物品的orderitems表(订单本身存储在orders表中)。quantity列包含订单中每项物品的数量。在此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1。

不允许函数：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

使用默认值而不是NULL值：许多数据库开发人员使用默认值而不是NULL列、特别时对用于计算或数据分组的列更是如此。

21.1.6　引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。

但MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

当然，你完全可以忽略这些数据库引擎。如果省略ENGINE=语句，则使用默认引擎(很可能时My ISAM)，多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

以下是几个需要知道的引擎：

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；

MEMORY在功能上等同于My ISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合临时表)；

My ISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。

外键不能跨引擎：混用引擎类型由一个打缺陷，外键(用于强制性实施引用完整性)不能跨引擎，即使用一个引擎的表不能引用具有使用不同外键的表的外键。

21.2　更新表

为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该在被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

为了使用ALTER TABLE更改表结构，必须给出下面的信息：

在ALTER TABLE之后给出要更改的表名(该表必须存在，否则将出错)。

所做更改的列表。

输入：

ALTER TABLEvendors

ADD vend_phone CHAR(20);

分析：这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。

删除杠杆添加的列，可以这样做：

输入：

ALTER TABLEvendors

DROP COLUMN vend_phone;

ALTER TABLE的一种常见用途是定义外键。

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCESorders (order_num);

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCESproducts (prod_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCEScustomers (cust_id);

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

这里，由于要更改4个不同的表，使用了4条ALTER TABLE语句。为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

复杂的表结构更改一般需要手动删除过程，它设计以下步骤：

(1)用新的列布局创建一个新表；

(2)使用INSERT SELECT语句，从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段。

(3)检验包含所需数据的新表；

(4)重命名旧表(如果确定，可以删除它)；

(5)用旧表原来的名字重命名新表；

(6)根据需要，重新创建触发器、存储过程、索引和外键。

小心使用ALTER TABLE：使用ALTER TABLE要几位小心，应该在进行改动前作一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销。如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失列中的所有数据。

21.3　　删除表

删除表(删除整个表而不是其内容)非常简单，使用DROP TABLE语句即可。

输入：

DROP TABLE customers2;

分析：这条语句删除customers2 表(假设它存在)。删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

21.4　重命名表

使用RENAME TABLE语句重命名一个表：

输入：

RENAME TABLE customers2 TO customers;

分析：RENAME TABLE所做的仅是重命名一个表。可以使用下面的语句对多个表重命名：

RENAME TABLE backup_customers TOcustomers,

backup_vendors TOvendors,

backup_products TO products;

第22章　　使用视图

22.1　视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

输入：

SELECTcust_name,cust_contact

FROMcustomers,orders,orderitems

WHERE customers.cust_id =orders.cust_id

AND orderitems.order_num =orders.order_num

AND prod_id = 'TNT2';

分析：此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品(或多个产品)的相同数据，必须修改最后的WHERE子句。

现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以轻松地检索出相同的数据：

输入：

SELECTcust_name,cust_contact

FROMproductcustomers

WHERE prod_id ='TNT2';

这就是视图的作用，product customers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是

一个SQL查询(与上面用以正确联结表的相同的查询)。

22.1.1　为什么使用视图

视图的一些常见应用：

(1)重用SQL语句；

(2)简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节；

(3)使用表的组成部分而不是整个表；

(4)保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；

(5)更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据(添加和更新数据存在)。

重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

在部署使用了大量视图的应用前，应该进行测试。

22.1.2　视图的规则和限制

(1)与表一样，视图必须唯一命名(不能给视图与别的视图或表相同的名字)。

(2)对于可以创建的视图数码没有限制。

(3)为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

(4)视图可以镶嵌，即可以利用从其他视图中检索数据的查询来构造一个视图。

(5)ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。

(6)视图不能索引，也不能由关联的触发器或默认值。

(7)视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

22.2　使用视图

视图的创建：

(1)视图用CREATE VIEW语句来创建；

(2)使用SHOW CREATE VIEW viewname;来查看创建视图的语句。

(3)用DROP删除视图，其语法为DROP VIEW viewname;

(4)更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

22.2.1　利用视图简化复杂的联结

视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及链接。

输入：

CREATE VIEW productcustomers AS

SELECTcust_name, cust_contact,prod_id

FROMcustomers,orders,orderitems

WHERE customers.cust_id =orders.cust_id

AND orderitems.order_num = orders.order_num;

分析：这条语句创建一个名为product customers的视图，它联结三个表，以返回已订购了任意产品的所有产品的列表。如果执行SELECT*FROM productcustomers，将列出订购了任意产品的客户。

为检索订购了产品TNT2的客户，可如下进行：‘

输入：

SELECTcust_name, cust_contact

FROMproductcustomers

WHERE prod_id = 'TNT2';

输出：

分析：这条语句通过WHERE子句从视图中检索特定数据。在MySQL处理此查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据。

可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

创建可重用的视图：创建不受特定数据限制的视图是一种好办法。例如，上面创建的视图返回生产所有产品的客户而不仅仅是生产TNT2的客户，扩展视图的范围不仅使得它能被重用，而且甚至更有用。这样做不需要创建和维护多个类似视图。

22.2.2　用视图重新格式化检索出的数据

视图的另一常见用途是重新格式化检索出的数据。下面的SELECT语句在单个组合计算列中返回供应商名和位置：

输入：

SELECT Concat(RTrim(vend_name),' (', RTrim(vend_country), ')')

ASvend_title

FROMvendors

ORDER BY vend_name;

输出：

现在，加入经常需要这个格式的结果，不必再每次需要时执行联结，创建一个视图，每次需要时使用它即可。为把此语句转换为视图，可按如下进行：

CREATE VIEW vendorlocations AS

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')

ASvend_title

FROMvendors

ORDER BY vend_name;

分析：这条语句与以前的SELECT语句相同的查询创建视图。为了检索出以前创建所有邮件标签的数据，可如下进行：

SELECT *

FROM vendorlocations;

22.2.3　用视图过滤不想要的数据

视图对于应用普通的WHERE子句也很有用。例如，可以定义customeremaillist视图，它过滤没有电子邮件地址的客户。为此目的，可使用下面的语句：

输入：

CREATE VIEW customeremaillist AS

SELECTcust_id, cust_name,cust_email

FROMcustomers

WHERE cust_email IS NOT NULL

分析：显然，在发送电子邮件到邮件列表时，需要排除没有电子邮件地址的用户。这里的WHERE子句过滤了cust_email列中具有NULL值的那些行，使它们不被检测出来。

现在，可以像使用其他表一样使用视图customeremaillist

输入：

SELECT *

FROM customeremaillist;

输出：

WHERE子句与WHERE子句：如果从视图检索数据时使用了一条WHERE子句，则两组子句(一组在视图中，另一组时传递给视图的)将自动组合。

22.2.4　使用视图与计算字段

视图对于简化计算字段的使用特别有用。它检索某个特定订单中的物品，计算每种物品的总价格：

输入：

SELECTprod_id,

quantity,

item_price,

quantity * item_price ASexpanded_price

FROMorderitems

WHERE order_num = 20005;

输出：

为将其转换为一个视图，如下进行：

输入：

CREATE VIEW orderitemsexpanded AS

SELECTorder_num,

prod_id,

quantity,

item_price,

quantity * item_price ASexpanded_price

FROM orderitems;

为检测订单20005的详细内容(上面的输出)，如下进行：

输入：

SELECT *

FROMorderitemsexpanded

WHERE order_num = 20005;

输出：

可以看到，视图非常容易创建，而且很好使用。正确使用，视图可极大地简化复杂的数据处理。

22.2.5　更新视图

通常，视图是可更新的(即，可以对它们使用INSERT、UPDATE和DELETE)。更新一个视图将更新其基表。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确认被更新的基数据，则不允许更新(包括插入和删除)。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

(1)分组(使用GROUP BY和HAVING)

(2)联结

(3)子查询

(4)并

(5)聚集函数(Min()、Count()、Sum()等)

(6)DISTINCT

(7)导出(计算)列

视图主要用于数据检索。一般，应该将视图用于检索(SELECT语句)，而不用于更新(INSERT、UPDATE和DELETE)。

第23章　　使用存储过程

23.1　存储过程

MySQL5添加了对存储过程的支持。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

23.2　为什么需要使用存储过程

(1)通过把处理封装在容易使用的单元中，简化复杂的操作；

(2)由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的；

(3)简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。

(4)提高性能。因为使用存储过程比使用单独的SQL语句要快。

(5)存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们很重要。不过，在将SQL代码转换为存储过程前，页必须知道它的一些缺陷。

(1)一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

(2)你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许它们创建存储过程。

尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。

MySQL将编写存储过程的安全性和访问与执行存储过程的安全和访问区分开来。这是好事情。即使你不能(或不想)编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

23.3　使用存储过程

使用存储过程=需要知道如何执行(运行)它们。存储过程的执行远比其定义更经常遇到。

23.3.1　执行存储过程

MySQL 称存储过程的执行为调用，因此 MySQL 执行存储过程的语句为 CALL。CALL 接受存储过程的名字以及需要传递给它的任意参数。

输入：

CALL productpricing(@pricelow,@pricehigh,@priceaverage);

分析：其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

23.3.2　创建存储过程

CREATE PROCEDUREproductpricing()BEGIN

SELECT Avg(prod_price) ASpriceaverageFROMproducts;END;

分析：此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的 SELECT 语句。

在 MySQL 处理这段代码时，它创建一个新的存储过程product-pricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。

使用存储过程：

输入：

CALL productpricing();

输出：

分析：CALL productpricing();执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号(即使不传递参数也需要)。

23.3.3　删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。

为删除刚创建的存储过程，可使用以下语句：

输入：

DROP PROCEDURE productpricing;

分析：这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名。

仅当存在时删除：如果指定的过程不存在，则 DROP PROCEDURE 将产生一个错误。当过程存在想删除它时(如果过程不存在也不产生错误)可使用 DROP PROCEDURE IF EXISTS。

23.3.4　使用参数

productpricing 只是一个简单的存储过程，它简单地显示 SELECT 语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

变量(variable)内存中一个特定的位置，用来临时存储数据。

尛松鼠

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql portable 命令_MySQL必知必会

目录第1章　　了解数据库1.1　数据库基础1.1.1　什么是数据库数据库是一个以某种有组织的方式存储的数据集合。数据库(databases)：保存有组织的数据的容器(通常是一个文件或一组文件)。容易混淆：数据库软件应称为DBMS(数据库管理系统)。数据库是通过DBMS创建个操纵的容器。你使用的是DBMS，它替你访问数据库。1.1.2　表表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存...
复制链接

扫一扫