mysql

1、使用数据处理函数

本章介绍什么是函数，MySQL支持何种函数，以及如何使用这些函数。
大多数SQL实现支持以下类型的函数。
1.用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
2.用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
3.用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
4.返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

1.1 文本处理函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

正如所见，Upper() 将文本转换为大写,下表列出了常用的文本处理函数：

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

1.2 日期和时间处理函数

下表列出了常用的日期和时间处理函数。


首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE 子句进行过滤，日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性
但是，使用WHERE order_date = ‘2005-09-01’ 可靠吗？order_date 的数据类型为datetime 。这种类型存储日期及时间值。样例表中的值全都具有时间值00:00:00 ，但实际中很可能并不总是这样。如果用当前日期和时间存储订单日期（因此你不仅知道订单日期，还知道下订单当天的时间），怎么办？比如，存储的order_date 值为2005-09-0111:30:05 ，则WHERE order_date = ‘2005-09-01’ 失败。即使给出具有该日期的一行，也不会把它检索出来，因为WHERE 匹配失败。
解决办法是指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较。为此，必须使用Date() 函数。Date(order_date) 指示MySQL仅提取列的日期部分，更可靠的SELECT 语句为：

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

如果你想检索出2005年9月下的所有订单，怎么办？

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

1.3 数值处理函数

2、汇总数据

本章介绍什么是SQL的聚集函数以及如何利用它们汇总表的数据。
有的时候只需要汇总数据而不需要检索出数据，这时候如果检索数据就会浪费大量的资源，因此需要聚集函数，

COUNT() 函数有两种使用方式。
1.使用COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值（NULL ）还是非空值。
2.使用COUNT(column) 对特定列中具有值的行进行计数，忽略NULL 值。
以上5个聚集函数都可以如下使用：
1.对所有的行执行计算，指定ALL 参数或不给参数（因为ALL 是默认行为）；
2.只包含不同的值，指定DISTINCT 参数。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

3、分组数据

本章将介绍如何分组数据，以便能汇总表内容的子集。这涉及两个新SELECT 语句子句，分别是GROUP BY 子句和HAVING 子句。
在具体使用GROUP BY 子句前，需要知道一些重要的规定。
1.GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2.如果在GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
3.GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT 中使用表达式，则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。
4.除聚集计算语句外，SELECT 语句中的每个列都必须在GROUP BY 子句中给出。
5.如果分组列中具有NULL 值，则NULL 将作为一个分组返回。如果列中有多行NULL 值，它们将分为一组。
6.GROUP BY 子句必须出现在WHERE 子句之后，ORDER BY 子句之前。

3.2 过滤分组

除了能用GROUP BY 分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。

我们已经看到了WHERE 子句的作用（第6章中引入）。但是，在这个例子中WHERE 不能完成任务，因为WHERE 过滤指定的是行而不是分组。事实上，WHERE 没有分组的概念。

那么，不使用WHERE 使用什么呢？MySQL为此目的提供了另外的子句，那就是HAVING 子句。HAVING 非常类似于WHERE 。事实上，目前为止所学过的所有类型的WHERE 子句都可以用HAVING 来替代。唯一的差别是WHERE 过滤行，而HAVING 过滤分组。

3.3 SELECT子句顺序

4、使用子查询

在SELECT 语句中，子查询总是从内向外处理。

5、联结表

本章将介绍什么是联结，为什么要使用联结，如何编写使用联结的SELECT 语句。

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
 ON vendors.vend_id = products.vend_id;

6、创建高级联结

本章将讲解另外一些联结类型（包括它们的含义和使用方法），介绍如何对被联结的表使用表别名和聚集函数。
表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。
迄今为止，我们使用的只是称为内部联结或等值联结 （equijoin）的简单联结。现在来看3种其他联结，它们分别是自联结、自然联结和外部联结。

6.1 自联结

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 'DTNTR';

此查询中需要的两个表实际上是相同的表，因此products 表在FROM 子句中出现了两次。虽然这是完全合法的，但对products 的引用具有二义性，因为MySQL不知道你引用的是products 表中的哪个实例。

为解决此问题，使用了表别名。products 的第一次出现为别名p1 ，第二次出现为别名p2 。现在可以将这些别名用作表名。例如，SELECT 语句使用p1 前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id 、prod_name 的列。MySQL不知道想要的是哪一个列（即使它们事实上是同一个列）。WHERE （通过匹配p1 中的vend_id 和p2 中的vend_id ）首先联结两个表，然后按第二个表中的prod_id 过滤数据，返回所需的数据。

6.2 自然联结

自然联结 排除多次出现，使每个列只返回一次。

SELECT c.*, o.order_num, o.order_date,
       oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

6.3 外部联结

下面的SELECT 语句给出一个简单的内部联结。它检索所有客户及其订单：

SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
 ON customers.cust_id = orders.cust_id;

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

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
 ON customers.cust_id = orders.cust_id;

在总结关于联结的这两章前，有必要汇总一下关于联结及其使用的某些要点。
1.注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
2.保证使用正确的联结条件，否则将返回不正确的数据。
3.应该总是提供联结条件，否则会得出笛卡儿积。
4.在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

7、组合查询

本章讲述如何利用UNION 操作符将多条SELECT 语句组合成一个结果集。有两种基本情况，其中需要使用组合查询：
1.在单个查询中从不同的表返回类似结构的数据；
2.对单个表执行多个查询，按单个查询返回数据。
UNION 的使用很简单。所需做的只是给出每条SELECT 语句，在各条语句之间放上关键字UNION 。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

正如所见，并是非常容易使用的。但在进行并时有几条规则需要注意。
1.UNION 必须由两条或两条以上的SELECT 语句组成，语句之间用关键字UNION 分隔（因此，如果组合4条SELECT 语句，将要使用3个UNION 关键字）。
2.UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
3.列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
UNION 从查询结果集中自动去除了重复的行
这是UNION 的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL 而不是UNION 。
在用UNION 组合查询时，只能使用一条ORDER BY 子句，它必须出现在最后一条SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY 子句。

8、全文本搜索

MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM 和InnoDB ，前者支持全文本搜索，而后者不支持。
第8章介绍了LIKE 关键字，它利用通配操作符匹配文本（和部分文本）。使用LIKE ，能够查找包含特殊值或部分值的行（不管这些值位于列内什么位置）。
在第9章中，用基于文本的搜索作为正则表达式匹配列值的更进一步的介绍。使用正则表达式，可以编写查找所需行的非常复杂的匹配模式。
虽然这些搜索机制非常有用，但存在几个重要的限制。
性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
明确控制——使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。
在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。
为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。
在索引之后，SELECT 可与Match() 和Against() 一起使用以实际执行搜索。
一般在创建表时启用全文本搜索。CREATE TABLE 语句（第21章中介绍）接受FULLTEXT 子句，它给出被索引列的一个逗号分隔的列表。
下面的CREATE 语句演示了FULLTEXT 子句的使用：

CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_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;

这些列中有一个名为note_text 的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text) 的指示对它进行索引。这里的FULLTEXT 索引单个列，如果需要也可以指定多个列。
在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
可以在创建表时指定FULLTEXT ，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。
在索引之后，使用两个函数Match() 和Against() 执行全文本搜索，其中Match() 指定被搜索的列，Against() 指定要使用的搜索表达式。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

8.2 使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到anvils 的注释。只有一个注释包含词anvils ，但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils 。
这也是查询扩展的一项任务。在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词

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

8.3 布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式 （boolean mode）。以布尔方式，可以提供关于如下内容的细节：
要匹配的词；
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
表达式分组；
另外一些内容。
布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT 索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。

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

为了匹配包含heavy 但不包含任意以rope 开始的词的行，可使用以下查询：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy rope*' IN BOOLEAN MODE);

下表列出全文本布尔操作符：

9、插入数据

INSERT INTO Customers
VALUES(NULL,
   'Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);

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

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country,
   cust_contact,
   cust_email)
VALUES('Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA',
   NULL,
   NULL);

如果数据检索是最重要的（通常是这样），则你可以通过在INSERT 和INTO 之间添加关键字LOW_PRIORITY ，指示MySQL降低INSERT 语句的优先级，如下所示：INSERT LOW_PRIORITY INTO

10、更新和删除数据

如果用UPDATE 语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE 关键字，如下所示：UPDATE IGNORE customers …
下面是许多SQL程序员使用UPDATE 或DELETE 时所遵循的习惯。
1.除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE 子句的UPDATE 或DELETE 语句。
2.保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
3.在对UPDATE 或DELETE 语句使用WHERE 子句前，应该先用SELECT 进行测试，保证它过滤的是正确的记录，以防编写的WHERE 子句不正确。
4.使用强制实施引用完整性的数据库（关于这个内容，请参阅第15章），这样MySQL将不允许删除具有与其他表相关联的数据的行。