MySQL 必知必会 学习笔记三

18 全文本搜索

LIKE关键字利用通配操作符匹配文本(和部分文本)，利用LIKE能够查找包含特殊值或部分值的行(不管这些值位于列内什么位置).

使用正则表达式，可以编写查找所需要的复杂的匹配模式。
这两种方法都存在限制:
①性能：通常要求MySQL尝试匹配表中所有行，因此搜索可能很耗时。
②明确控制：很难明确地控制匹配什么或不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或不匹配
③智能化的结果:它们不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将返回包含该词的所有行，而不区分包含单个匹配或多个匹配的行。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

这些限制可以用全文本搜索来解决

18.2 使用全文本搜索

为了进行全文本搜索，必须索引被搜索到的列，而且要随着数据的改变不断地重新索引。在对表进行适当设计后，MySQL会自动进行所有的索引额重新索引。

在索引之后，SELECT与Match()和Against()一起使用以实际执行搜索

启用全文本搜索支持
一般在创建表时启用全文本搜索。CREATE TABLE 语句接受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;

这条CREATE TBALE 语句定义表productnotes并列出它所包含的列。
列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。FULLTEXT也可以索引多个列。

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

可以在创建表时指定FULLTEXT，也可以稍后指定。
不要在导入数据时使用FULLTEXT。

进行全文本搜索
在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式
SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’);

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

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

也可以使用LIKE完成,检索出的次序可能不同，因为LIKE在没用ORDER BY排序时以不特别有用的顺序返回数据：
SELECT note_text FROM productnotes WHERE note_text LIKE ‘%rabbit%’;

使用全文本搜索返回以文本匹配良好程度排序的数据。全文本搜索的一个重要
部分就是对结果排序，具有较高等级的行优先返回。

要演示排序如何工作，可以：
SELECT note_text ,Match(note_text) Against(‘rabbit’) AS rank FROM productnotes;

在SELECT而不是在WHERE子句中使用Match()和Against()，使所有行都被返回。
Match()和Against()用来建立一个计算列rank。此列包含全文本搜索计算出的等级值。文本词靠前的行的等级值比靠后的行等级高。结果以等级以降级排序。

如果指定多个搜索项，则包含多数匹配词的那些行等级值较高。

使用查询扩展

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

你想找出所有提到anvils的注释。只有一个注释包含词anvils，但还想找出与搜索有关的所有其他行，即使不包含anvils。

这也是查询扩展的一项任务。子安使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：
①首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行。
②其次，MySQL检查这些匹配行并选择所有有用的词。
③再其次，MySQL再次进行全文本搜索，不仅使用原来的条件，还使用所有有用的词。

一个简单的全文本搜索，没有查询扩展，只返回一行包含词anvils：
SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘anvils’);

使用查询扩展:
SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘anvils’ WITH QUERY EXPANSION);

返回了7行，第一行含词anvils，因此等级最高。
第二行与anvils无关，但因为它包含第一行中的两个词(customer 和recommend)，所以也被检索出来。
第三行也包含这两个词，但位置更靠后且分开得更远。

表中的行越多，使用查询扩展返回的结果越好。

布尔文本搜索
全文本搜索的另一种形式：布尔方式。
以布尔方式，可以提供关于如下内容的细节:
①要匹配的词
②要排斥的词（如果某行包含这个词，则不返回，即使包含其他指定的词）
③排列提示
④表达式分组
⑤另外一些内容

即使没有FULLTEXT索引也可以使用布尔方式。

演示IN BOOLEAN MODE:

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

-rope*指示MySQL排除包含rope*(任何以rope开始的词)。

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

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

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

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

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

这个搜索匹配短语rabbit bait而不是匹配两个词

SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);

匹配rabbit 和bait，增加前者的等级，降低后者的等级。

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

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

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

全文本搜索的使用说明

①在索引全文本数据时，短词被忽略且从索引中排除。短词定义为具有3个或以下字符的词（可以更改）
②MySQL带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略，可以修改此列表。
③许多次出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则把它作为一个非用词忽略，不用于IN BOOLEAN MODE
④如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或不出现或至少出现在50%的行中）
⑤忽略词中的单引号。
⑥不具有词分隔符（汉语）的语言不能恰当地返回全文本搜索结果。
⑦仅在MyISAM数据库引擎中支持全文本搜索。

19 插入数据

19.1 数据插入

INSERT是用来插入（或添加）行到数据库的，插入可以哟并几种方式使用：
①插入完整的行
②插入行的一部分
③插入多行
④插入某些查询的结果

19.2 插入完整的行

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

此例子插入一个新客户到customers表中。但这种语法并不安全，因为太依赖与表中列的定义次序，还依赖与其次序容易获得信息。

编写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语句相同的工作，但在表名后明确给出列名。在插入行时，MySQL将使用VALUES列表中的相应值填入列表中的对应项。

因为给出了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。即使表的结构改变，此INSERT语句仍正确工作。

一般不要使用没有明确给出列的列表的INSERT语句。
必须给出VALUES的正确数目。
如果表的定义允许，则在INSERT操作中可以省略列，省略的列必须包含一下某个条件:
①该列定义为允许的NULL值
②在表定义中给出默认值，这表示如果不给出值，使用默认值、

19.3 插入多个行

可以使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束。

或者每条INSERT语句中的列名和次序相同，可以合并语句:

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

19.4 插入检索出的数据

可以利用INSERT将一条SELECT语句的结果插入表中，就是INSERT SELECT。

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

INSERT INTO customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) SELECT cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country FROM custnew;

SELECT语句从custnew检索出要插入的值，而不是列出它们。如果这个表为空，则没有行被插入（仍是合法的）。

这个例子在INSERT和SELECT语句中使用了相同的列名，但是MySQL不关心SELECT返回的列名，它使用的是列的位置。因此SELECT中第一行将用来填充表列中指定的第一个列。

20 更新和删除数据

20.1 更新数据

更新表中的数据，使用UPDATE语句，可采用两种方式使用UPDATE:
①更新表中特定行
②更新表中所有行

基本的UPDATE语句由3部分组成，分别是:
①要更新的表
②列名和它们的新值
③确定要更新行的过滤条条件

客户10005现在有了电子邮件地址，因此要更新，语句如下:

UPDATE customers SET cust_email='elmer@fudd.com' WHERE cust_id=10005;

UPDATE语句以要更新的表名开始，要更新的表的名字为customers，SET命令用来将新值赋给被更新的列，WHERE子句告诉MySQL更新哪一行。如果没有WHERE,表中所有行都会被更新。

更新多个列:

UPDATE customers SET cust_name='The Fudds', cust_email='elmer@fudd.com' WHERE cust_id=10005;

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

如果用UPDATE更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消。为即使发生错误，也继续进行更新，可使用IGNORE关键字，如下:
UPDATE IGNORE customers…

为了删除某个列的值，可将它设置为NULL（假如表定义运行NULL值）

UPDATE customers SET cust_email=NULL WHERE cust_id=10005;

20.2 删除数据

从一个表中删除数据，使用DELETE语句。可以两种方式使用DELETE：
①从表中删除特定的行
②从表中删除所有的行（没有使用WHERE子句时）

下面的语句从customers表中删除一行:

DELETE FROM customers WHERE cust_id=10006;

DELETE删除整行而不是删除列，为了删除指定的列，请使用UPDATE语句。

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

如果想从表中删除所有行，不要使用DELETE，可使用TRUNCATE TABLE语句，它完成同样的工作，但速度更快。

20.3 更新和删除的指导原则

①除非确实删除和更新每一行，否则绝对不要使用不带WHERE的UPDATE和DALETE语句
②保证每个表都有主键，尽可能像WHERE子句那样使用它
③在对UPDATE和DELETE语句使用WHERE子句之前，应该使用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确
④使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

21 创建和操纵表

21.1 创建表

MySQL不仅用于表数据操纵，还可以用来执行数据库和表的所有操作，所有表本身的创建和处理。

有两种创建表的方法：
①使用具有交互式创建和管理表的工具
②直接使用MySQL语句操纵。

表创建基础
为利用CREATE TABLE 创建表，必须给出下列信息：
①新表的名字，在关键字CREATE TABLE之后给出
②表列的名字和定义，用逗号分隔

下面的MySQL语句创建本书所用的customers表

表的主键可以在创建表时用PRIMARY KEY关键字指定，这里cust_id指定作为主键列。

MySQL语句中忽略空格，你可以在一个长行上输入，也可以分成多行。

在创建新表时，指定的表名必须不存在，否则将出错。
如果要防止以外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它。
如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS.

使用NULL值
允许NULL值的列允许在插入行时不给出该列的值，不允许NULL值的列不接受该列没有值的行，即在插入或更新行时，该行必须有值。

每个表或是NULL列，或NOT NULL列，这种状态在创建时由表的定义规定，如下:

三个列都需要，因此每个列的定义都含有关键字NOT NULL，这将阻止插入没有值的列。

下面例子将创建混合了NULL和NOT NULL列的表：

主键再介绍
主键值必须唯一，即表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一，如果使用多个列，则这些列的组合值必须唯一。

CREATE TABLE用单个列作为主键:
PRIMARY KEY(vend_id)

由多个列组成的主键，应用逗号分隔开:

主键可以在创建表时定义，或者在创建表之后定义

使用AUTO_INCREMENT

cust_id int NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。
这样给每个行分配一个唯一的cust_id,从而可以用做主键值

每个表值允许一个AUTO_INCREMENT列，而它必须被索引（如通过使它称为主键）

如果一个列被指定为AUTO_INCREMENT,那它需要使用特殊的值吗？你可以在INSERT语句中指定一个值，只要它是唯一的即可，该值将被用来替代自动生成的值，后续的增量将开始使用该手工插入的值。

让MySQL生成主键的一个缺点时你不知道这些值都是谁，你可以使用last_insert_id()函数来获取这个值,如下:
SELECT last_insert_id()
此语句返回最后一个AUTO_INCREMENT值。

指定默认值
在插入时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE 语句的列定义中的DEFAULT关键字指定。


quantity描述文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1.

MySQL不允许使用函数作为默认值，它只支持常量。

引擎类型
迄今为止使用的CREATE TABLE 语句全都以ENGINE=InnoDB语句结束。

与其他DBMS（数据库管理系统，Database Management System），MySQL有一个具体管理和处理数据的内部引擎。

在你使用CREATE TABLE 语句时，该引擎具体创建表，而你使用SELECT语句或其他数据库处理时，该引擎在内部处理你的请求。

MySQL有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都执行CREATE TABLE和SELECT等命令。

不同引擎具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

如果省略ENGINE=语句，则使用默认引擎（可能时MyISAM），多数SQL语句都会默认使用它。

几个需要知道的引擎：
①InnoDB时是一个可靠的事务处理引擎，它不支持全文本搜索
②MEMORY在功能等同于MyISAM，但由于数据存储在内存（而不是磁盘）中，速度很快（特别适合与临时表）
③MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。
混用引擎类型有一个大缺陷，外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

21.2 更新表

为更新表定义，可使用ALTER TABLE 语句。但理想状态下，当表中存储数据以后，该表就不应该再被更新。

为了使用ALTER TABLE更改表结构，必须给出下面的信息：
①在ALTER TABLE之后给出要更改的表名（该表必须存在，否则出错）
②所做更改的表

下面的例子给表添加一个列:

ALTER TABLE vendors ADD vend_phone CHAR(20);

这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型

删除刚刚添加的列:

ALTER TABLE vendors DROP COLUMN vend_phone;

ALTER TABLE一种常见用途是定义外键，下面是定义本书中的表所用的外键代码:

为了对单个表进行多个修改，可以使用单挑ALTER TABLE语句，每个更改用逗号分隔。

复杂的表结构更改一般需要手动删除过程，涉及以下步骤:
①用新的列布局创建一个新表
②使用INSERT SELECT语句从旧表复制数据到新表。
③检验包含所需数据的新表
④重命名旧表（如需可以删除）
⑤用旧表原来的名字重新命名新表
⑥根据需要，重新创建触发器，存储过程，索引和外键。

21.3 删除表

删除表，使用DROP TABLE语句即可:
DROP TABLE customers;
删除表没有确认，也不能撤销。

21.4 重名命表

使用RENAME TABLE 语句可以重命名一个表:
RENAME TBALE customers2 TO customers;

可以使用下面的语句对多个表重命名:
RENAME TABLE backup_customers TO customers,backup_vendors TO vendors,backup_products TO products;

22 使用视图

22.1 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用动态检索数据的查询。

看下面的例子（15章中）：

SELECT cust_name,cust_contact 
FROM customers,orders,orderitems 
WHERE customers.cust_id=orders.cust_id 
  AND orderitems.order_num=orders.order_num
  AND prod_id=’TNT2’;

假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以轻松地检索出相同的数据

SELECT cust_name,cust_contact 
FROM productcustomers
WHERE  prod_id='TNT2';

这就是视图的作用，productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询。

为什么使用视图
①重用SQL语句
②简化复杂的SQL操作
③使用表的组成部分而不是整个表。
④保护数据。可以给用于授予表的特定部分的访问权限而不是整个表的访问权限
⑤更改数据格式和表示

在视图创建之后，可以用于表基本相同的方式利用它们。
视图仅仅时用来查看存储在别处的数据的一种设施，视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据，视图将返回改变过的数据。

视图的规则和限制
①与表名一样，视图必须唯一命名
②对于可以创建的视图数目没有限制
③为了创建视图，必须拥有足够的访问权限
④视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
⑤ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY 将被覆盖。
⑥视图不能索引，也不能有关联的触发器和默认值
⑦视图可以和表一起使用

22.2 使用视图

视图的创建:
①用CREATE VIEW 语句来创建
②使用SHOW CREATE VIEW viewname，来查看创建视图的语句
③用DROP删除视图，其语法为DROP VIEW viewname
④更新视图时，可以先用DROP 再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的如图不存在，第2条更新语句会创建一个视图，存在则替换原有视图。

利用视图简化复杂的联结
这条语句创建一个名为productcustomers的视图，它联结三个表，以返回以订购了任意产品的所有客户的列表。

CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
   AND orderitems.order_num=orders.order_num;

如果执行SELECT * FROM productcustomers，将列出订购了任意产品的客户。

为检索订购了TNT2的客户：

SELECT cust_name,cust_contact 
FROM productcustomers
WHERE  prod_id='TNT2';

视图极大地简化了复杂SQL语句的使用。利用视图，可以一次编写基础的SQL，然后根据需要多次使用。

用视图重新格式化检索出的数据
视图的另一常见用途就是重新格式化检索出的数据。

来面例子来自第10章，在单个组合计算列中返回供应商名和位置：

SELECT Concat(RTrim(vend_name),’ (‘,RTrim(vend_country),’)’) AS vend_titile 
FROM vendors 
ORDER BY vend_name; 

假如经常需要这个格式的结果，创建一个视图，每次需要时使用即可。为此语句转换为视图，按如下进行:

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')') AS vend_titile 
FROM vendors 
ORDER BY vend_name; 

用SELECT * FROM vendorlocations 可以检索出创建的数据

用视图过滤不想要的数据
视图对于普通的WHERE子句也很有用。

例如，可以定义customeremaillist视图，它过滤没有电子邮件地址的客户，可使用下面的语句:

CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email 
FROM customers
WHERE cust_email IS NOT NULL;

如果从视图检索数据时使用了一条WHERE子句，则两组子句(一组在视图中，另一组是传递给视图)将自动组合。

使用视图与计算字段

视图对于简化计算字段的使用特别有用，下面是第10章的一个例子，它检索某个特定订单中的物品，计算每种物品ed总价格:

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price 
FROM orderitems 
WHERE order_num=20005; 

将其转换为一个视图:

CREATE VIEW orderitemsexpanded AS
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price 
FROM orderitems;

未检索订单20005的详细内容，上面的输出，如下:

SELECT *
FROM orderitemsexpanded
WHERE order_num=20005;

更新视图
通常，视图是可更新的。更新一个视图将更新其基表。如果你对视图增加或删除行，实际上是对基表增加或删除行。

但并非所有视图都是可更新的，如果MySQL不能正确地确定被更新的基数据，则不允许更新。这意味着如果视图定义中有以下操作，则不能进行视图的更新:
①分组（使用GROUP BY和HAVING）
②联结
③子查询
④并:
⑤聚集函数
⑥DISTINCT
⑦导出（计算）列

视图一般用于检索而不用于更新