MYSQL必知必会 再捋[17-20]（四）

（十七）组合查询

1 组合查询

MYSQL允许执行多个查询（多个SELECT语句）,并将结果作为单个查询结果集返回。称为并 或 复合查询

 

组合查询基本情况：

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

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

 

组合查询和多个WHERE条件： 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句
都可以作为一个组合查询给出，在以下段落中可以看到这一点。
这两种技术在不同的查询中性能也不同。

 

2 创建组合查询

利用UNION，可给出多条SELECT语句，将他们的结果组合成单个结果集

SELECT vend_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5;

 

SELECT vend_id, prod_id, prod_price

FROM products

WHERE vend_id IN (1001, 1002)

 

组合查询：

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

UNION指示MySQL执行两条SELECT语句，并把输出组
合成单个查询结果集。

 

WHERE 实现

SELECT vned_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5

OR vend_id IN (1001, 1002);

 

 

2.2 UNION规则

1 UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。
2 UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过分析各个列不需要以相同的次序列出）。
3 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以
隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

 

2.3 包含或取消重复的行

UNION从查询结果集中自动去除了重复的行

UNION ALL 可以返回所有匹配行

 

SELECT vend_id, prod_id, prod_price

FROM products

WHERE prod_price <= 5

UNION ALL

SELECT vend_id, prod_id, prod_price

FROM products

WHERE vend_id IN (1001, 1002)

分析：UNION ALL 不取消重复行

 

 

2.4 对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句

不允许使用多条ORDER BY子句

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)

ORDER BY vend_id, prod_price

分析： 实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

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

 

 

 

 

 

 

 

 

（十八）全文本搜索

1 理解全文本搜索

并非所有引擎都支持全文本搜索:(重点重点重点)

MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索，两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。这就是为什么虽然本书中创建的多数样例表使用InnoDB，而有一个样例表（productnotes表）却使用MyISAM的原因。如果你的应用中需要全文本搜索功能，应该记住这一点。

 

1.1  LIKE 通配符匹配文本 和 正则进行文本搜索 存在的几个重要限制：

性能——通配符和正则匹配通常要求mysql尝试匹配表中所有行（这种搜索极少使用表索引），因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

明确控制——使用通配符和正则表达式匹配，很难（而且并不总
是能）明确地控制匹配什么和不匹配什么。

智能化的结果——虽然基于通配符和正则表达式的搜索提供了非
常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。

 

 

全文本搜索解决以上限制：

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

 

2 使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改
变不断地重新索引。在对表列进行适当设计后， MySQL会自动进行所有的索引和重新索引。在索引之后， SELECT可与Match()和Against()一起使用以实际执行搜索。

 

2.1 启用全文本搜索支持

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

FULLTEXT子句的使用：

CREATE TABLE productnotes

(

note_id   int NOT NULL AUTO_INCREMENT,

prod_id   char(10)     NOT NULL,

note_date  datetiem   NOT NULL,

note_text     text              NULL,

PRIMARY KEY(note_id),

FULLTEXT(note_text)  

)ENGINE=MyISAM

分析：

1 使用了MYSQL的引擎 MYISAM (支持全文本搜索)

2 Note_text：为了进行全文本搜索，子句FULLTEXT（note_text）的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

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

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

 

不要在导入数据时使用FULLTEXT:

1 更新索引要花时间，虽然不是很多，但毕竟要花时间，

2 如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引，3 应该首先导入所有数据，然后在修改表，定义FULLTEXT,这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。

 

 

2.2 进行全文搜索

索引之后：使用两个函数Match()和Against()执行全文本搜索

Match( )：指定被搜索的列

Against( ):指定要使用的搜索表达式

 

SELECT note_text

FROM productnotes

WHERE MATCH(note_text) Against(‘rabbit’)

 

使用LIKE子句完成：

SELECT note_text

FROM productnotes

WHERE note_text LIKE ‘%ribbit%’

 

对比分析：上述两条SELECT语句都不包含ORDER BY子句。

后者（使用LIKE）以不特别有用的顺序返回数据。

前者（使用全文本搜索）返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。

 

使用完整的Match()说明：  

传递给Match()的值必须与FULLTEXT（）定义中的相同，如果指定多个列，则必须列出他们（次序正确）

 

搜索不区分大小写：除非使用BINARY方式

 

SELECT note_text,

Match(note_text) Against(‘rabbit’) as rank

FROM productnotes;

分析：

全文本搜索如何排除行：排除那些等级为0的行，

如何排序结果：按等级以降序排序

1 在SELECT中使用Match()和Against()而不是WHERE子句。这使所有行都被返回（因为没有WHERE子句）。

2 Match()和Against()用来建立一个计算列（别名为rank），此列包含全文本搜索计算出的等级值。

3 等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见，不包含词rabbit的行等级为0（因此不被前一例子中的WHERE子句选择）。

4 确实包含词rabbit的两个行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。

 

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

 

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

 

2.3 使用查询扩展

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

使用查询扩展，MYSQL对数据和索引进行两遍扫描来完成搜索。

1 首先，进行一个基本的全文本搜索，找出与搜索调价匹配的所有行

2 其次，mysql检查这些匹配行并选择所有有用的词。

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

 

简单全文本搜索，没有查询扩展:   SELECT note_text

FROM productnotes

WHERE Match(note_text)Against(‘anvils’)

使用查询扩展：

SELECT note_text

FROM productnotes

WHERE Match(note_text)Against(‘anvils’WITH QUERY EXPASION)

查询扩展极大第增加了返回的行数，但也增加实际上并不想要的行数

 

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

 

2.4 布尔文本搜索

Mysql支持全文本搜索的另外一种形式，称为布尔方式

 

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

1要匹配的词；
2要排斥的词（如果某行包含这个词，则不返回该行，即使它包含
其他指定的词也是如此）；
3排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
4 表达式分组；

 

即使没有FULLTEXT索引也可以使用，但这是一种非常缓慢的操作

（其性能将随着数据量的增加而降低）
q

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

 

全文本布尔操作符
+ 包含，词必须存在
-  排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语（与单个词的列表不一样，它匹配整个短语以
便包含或排除这个短语）

 

SELECT note_text

FROM productnotes

WHERE MATCH(note_text)

 

匹配包含词rabbit和bait的行

Against(‘+rabbit +bait’IN BOOLEAN MODE);

匹配包含rabbit和bait中的至少一个词的行

Against(‘rabbit bait’IN BOOLEAN MODE);

匹配短语rabbit bait而不是匹配两个词rabbit和bait。

Against(‘rabbit bait’IN BOOLEAN MODE);

增加前者的等级，降低后者的等级

Against(‘>rabbit <carrot’IN BOOLEAN MODE);

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

Against(‘+safe +(<combination)’IN BOOLEAN MODE);

 

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

 

2.5 全文本搜索的使用说明

1 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为
那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
2 MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参
阅MySQL文档以了解如何完成此工作）。
3 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。
因此， MySQL规定了一条50%规则，如果一个词出现在50%以上
的行中，则将它作为一个非用词忽略。 50%规则不用于IN BOOLEANMODE。
4 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。忽略词中的单引号。例如， don't索引为dont。
5 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文
本搜索结果。
6 如前所述，仅在MyISAM数据库引擎中支持全文本搜索

 

MYSQL全文本搜索现在还不支持邻近操作符

 

（十九）插入数据

1 数据插入

INSERT是用来插入行到数据库表

插入方式：

插入完成的行

插入行的一部分

插入多行

插入某些查询的结果

 

插入及系统安全

可针对每个表或每个用户，利用mysql的安全机制禁止使用INSERT语句。

2 插入完整的行

（下面不安全，避免使用）

INSERT INTO Customers

VALUES(NULL,

‘Pep E.lapew’,

 ‘100 Main Street’,

  ‘Los Angeles’,

   ‘CA’,

   ‘90046’,

   ‘USA’,

   ‘NULL’,

   ‘NULL’,);

 

INSERT语句一般不会产生输出

INSERT INTO customers(列名，...)VALUES(值)

 

总是使用列的列表：

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

 

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

 

省略列 如果表的定义允许，则可以在INSERT操作中省略某
些列。

省略的列必须满足以下某个条件。
1 该列定义为允许NULL值（无值或空值）。
2 在表定义中给出默认值。这表示如果不给出值，将使用默
认值。

 

提高整体性能

数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。

INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。
如果数据检索是最重要的（通常是这样），则你可以通过在
INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL
降低INSERT语句的优先级，如下所示：

INSERT LOW PRIORITY INTO  

也适用于： UPDATE  DELETE

 

 

3 插入多个行

1 INSERT INTO customers(列名，...)VALUES(值)； INSERT INTO customers(列名，...)VALUES(值)；

 

2 INSERT INTO customers(列名，...)VALUES(值)，(值)；

 

其中单条INSERT语句有多组值，每组值用一对圆括号括起来，

用逗号分隔。

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

 

4 插入检索出的数据

INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中 。

这就是所谓的INSERT SELECT

 

INSERT INTO customers(列名)

SELECT 列名

FROM 导入的表名

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据

（二十） 更新和删除数据

1 更新数据  UPDATE

两种方式：

更新表中特定行

更新表中所有行

不能省略WHERE子句

 

UPDATE与安全  可以限制和控制UPDATE语句的使用。

UPDATE语句的3部分组成：

1 要更新的表

2 列名和他们的新值

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

UPDATE customers

SET cust_email = ‘elmer@fudd.com’

WHERE cust_id = 10005

 

更新多个列：

UPDATE customers

SET cust_name = ‘The Fudds’,

Cust_email = ‘elmer@fudd.com’

WHERE cust_id = 10005

 

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

 

IGNORE关键字 中途更新错误，也继续进行更新，可使用IGNORE关键字。

UPDATE IGNORE customers...

删除某个列的值，可设置它为NULL:

UPDATE customers

SET cust_email = NULL

WHERE cust_id = 10005;

其中NULL用来去除cust_email列中的值。

 

2 删除数据 使用DELETE

可以两种方式使用DELETE：

1 从表中删除特定的行

2 从表中删除所有行

不要省略WHERE子句，避免删除表中所有行

DELETE与安全 可以限制和控制DELETE语句的使用

DELETE FROM customers

WHERE cust_id = 10006;

 

 

删除表的内容而不是表： DELETE语句从表中删除行，甚至是删除表中所有行，但是，DELETE不删除表本身。

 

更快的删除：如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，他完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）

 

使用UPDATE或DELETE时所遵循的习惯：

1 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE
子句的UPDATE或DELETE语句。
2 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。

3 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
4 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

 

小心使用 MySQL没有撤销（ undo）按钮。应该非常小心地
使用UPDATE和DELETE，否则你会发现自己更新或删除了错误
的数据。