MySQL必知必会 Day4

第18章 全文本搜索

18.1 理解全文本搜索
 MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。
 在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。
18.2 使用全文本搜索
 18.2.1 启用全文本搜索

CREATE TABLE product_notes 
( 
note_id int NOT NULL AUTO_INCREMENT, 
note_date datetime NOT NULL, 
note_text text NULL, 
PRIMARY KEY(note_id), 
FULLTEXT(note_text) 
) ENGINE=MyISAM;
//在note_text列进行了全文搜索。FULLTEXT表示该列启用全文搜索
//不要在导入数据时使用FULLTEXT

 18.2.2 进行全文本搜索

SELECT note_text 
FROM product_notes 
WHERE Match(note_text) Against(‘rabbit’);
//检索单个列note_text。Match表示对指定列搜索，而rabbit为指定文本
//因此，包含rabbit的行会被返回
其实也可以用LIKE子句完成
SELECT note_text 
FROM product_notes 
WHERE note_text LIKE '%rabbit%';
//%匹配0或多个字符
/*但是，全文本会以文本匹配的良好程度排序。
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能
是你真正想要的行）。
1:A fat rabbit 
2:A rabbit 
用全文本搜索出来就的排序就会下面这样（rabbit在前面），like就可能是上面的。 
2:A rabbit 
1:A fat rabbit  */

 18.2.3 使用查询扩展
 查询扩展用来设法放宽所返回的全文本搜索结果的范围。只用于MySQL版本4.1.1或更高级的版本
使用查询扩展可能会找到一些你不需要的行。

SELECT note_text 
FROM product_notes 
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);
//含有rabbit可能2行，使用查询扩展可能查到大于2行，因为数据库把觉得有关系的行也查出来了

 18.2.4 布尔文本搜索
布尔文本也是全文本搜索

SELECT note_text
FROM productsnotes
WHERE Math(note_text) Against ('heavy -rope*'  IN BOOLEAN MODE);
//为了匹配heavy但不包含任意以rope词的行
//使用的是IN BOOLEAN 关键字
//    布尔操作符             说 明
//    +                包含，词必须存在
//    -                排除，词必须不出现
//    >                包含，而且增加等级值
//    <                包含，且减少等级值
//    ()        把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
//    ~               取消一个词的排序值
//    *               词尾的通配符（截断）
//    ""      定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
SELECT note_text
FROM productsnotes
WHERE Math(note_text) Against ('+rabbit +bait'  IN BOOLEAN MODE);
//包含rabbit和bait的行
SELECT note_text
FROM productsnotes
WHERE Math(note_text) Against ('rabbit bait'  IN BOOLEAN MODE);
//至少包含两个中的一个
SELECT note_text
FROM productsnotes
WHERE Math(note_text) Against ('"rabbit bait"'  IN BOOLEAN MODE);
//匹配短语rabbit bait
SELECT note_text
FROM productsnotes
WHERE Math(note_text) Against ('>rabbit <carrot'  IN BOOLEAN MODE);
//匹配两个词，rabbit等级增加，降低后者等级
SELECT note_text
FROM productsnotes
WHERE Math(note_text) Against ('+safe +(<combination)'  IN BOOLEAN MODE);
//匹配这两个词，降低后者的排序等级

 18.2.5 全文本搜索的使用说明
（如有需要，再去查看书籍，这些要点记录了也不知道哪是重点）

第19章 插入数据

19.1 插入数据(INSERT)
19.2 插入完整的行

INSERT INTO Customers
VALUES(null,'a', 'b', 'v');
//第一行是id，该列有MySQL自动增量，让MySQL写一个id，记录顺序
INSERT INTO Customers(cust_name,cust_city,cust_State)
VALUES('a', 'b', 'v');
//这两个写法完成的工作一样，第二种写法给出了列名
//只要数据和列名一致，就不会出错，第一种的顺序必须和表中一致，不太好。    

一种优化：INSERT比较费时，当多个客户访问时，如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级
19.3 插入多个行
使用多条插入语句，或者一条，一次提交

INSERT INTO Customers(cust_name,cust_city,cust_State)
VALUES('a', 'b', 'v');
INSERT INTO Customers(cust_name,cust_city,cust_State)
VALUES('aa', 'bb', 'vv');
//或者
INSERT INTO Customers(cust_name,cust_city,cust_State)
VALUES('a', 'b', 'v'),('aa', 'bb', 'vv');//这种性能比上面快

19.4 插入检索出的数据

将一个名为custnew中的数据导入customers表中
INSERT INTO Customers(cust_name,cust_city,cust_State)
SELECT cust_name,cust_city,cust_State
FROM custnew;

第20章 更新和删除数据

20.1 更新数据

UPDATE customers
SET cust_email = 'aa@cc.com'
WHERE cust_id = 1005;
//更新多个列
UPDATE customers
SET cust_email = 'aa@cc.com',cust_name = 'haha'
WHERE cust_id = 1005;
//删除某个列的值，可以置为NULL
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 1005;

20.2 删除数据

DELETE FROM customers
WHERE cust_id = 10006;//一行都没了
//DELETE删除行，而不是表本身。删除全部行，表也还在。而且这个删是逐行删
//因此建议使用 TRUNCATE TABLE来删除所有行，实际过程是删除表后，新建一个，而不是逐行删数据

20.3 更新和删除的指导原则
如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。由于没有undo（撤销）按钮，所以请谨慎使用UPDATE和DELETE。

第21章 创建和操纵表

21.1 创建表
 21.1.1 创建表基础

CREATE TABLE customers(cust_id  int   NOT NULL AUTO_INCREMENT,
                       cust_name char(50) NOT NULL,
                       cust_city char(50) NOT NULL,
                       cust_State char(50)  NULL,
                       PRIMARY KEY(cust_id)
                      )ENGINE = InnoDB;
//创建新表时，如果表名已经存在，就会覆盖
//最好是手工删除该表，然后重建，而不是简单的创建表覆盖
//如果仅想在表不存在时创建，应该在表名后跟一个IF NOT EXISTS

 21.1.2 使用NULL值
 如同21.1.1的例子，都家了关键字NOT NULL，这将会阻止插入没有值的列，返回错误，并插入失败。并且在创建表时，默认是NULL，NOT NULL需要指定。
 21.1.3 主键再介绍
 主键是可有由多个列组成的
PRIMARY KEY(order_num,order_item) 就表示有两个列组成这个表的主键
 21.1.4 使用AUTO_INCREMENT
 如同21.1.1的例子创建时使用了该关键字
 AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。
并且该值从1开始增长，当你把第一条记录删除时，再插入第二条数据时，主键值是2，不是1。
 21.1.5 指定默认值

//DEFAULT关键字
CREATE TABLE customers(cust_id  int   NOT NULL AUTO_INCREMENT,
                       cust_name char(50) NOT NULL,
                       cust_city char(50) NOT NULL DEFAULT 1,
                       cust_State char(50)  NULL,
                       PRIMARY KEY(cust_id)
                      )ENGINE = InnoDB;

 21.1.6 引擎类型
MySQL具有多种引擎：
InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
外键不能跨引擎，也就是说，一个引擎的表不能引用使用了其他引擎的表的外键。
21.2 更新表

//添加列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
//删除列
ALTER TABLE vendors
DROP COLUMN vend_phone;
//ALTER TABLE一种常见的用途是定义外键
alter table 需要建立外键的表 
add constraint 外键名 foreign key （需要建立外键的字段名） references 外键表(外键字段)

21.3 删除表

DROP TABLE customer2;

21.4 重命名表

RENAME TABLE customer2 TO aaa;
//对多个表重命名
RENAME TABLE customer2 TO aaa，
             bb TO cc,
             DD to ee;

第22章 使用视图

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

//第15章的例子
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE orders.cust_id = customers.cust_id
     AND orderitems.order_num = orders.order_num
     AND prod_id = 'TNT2';
//可以看到，需要这个数据的人，必须了解相关表的结构,并知道如何创建查询和对表进行联结。
//而视图就是为了简化这个行为

 22.1.1 为什么使用视图
 重用SQL语句。
 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
 使用表的组成部分而不是整个表。
 保护数据。可以给用户授予表的特定部分的访问权限而不是整个的访问权限。
 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

 22.1.2 视图的规则和限制
 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
 对于可以创建的视图数目没有限制。
 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
 ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
 视图不能索引，也不能有关联的触发器或默认值。
 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

22.2 使用视图
 视图用CREATE VIEW语句来创建。
 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
 用DROP删除视图，其语法为DROP VIEW viewname;。

 22.2.1 利用视图简化复杂的联结

CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = order.cust_id
    AND orderitems.order_num = order.order_num
//这创建了一个视图，联结了三个表。
//换句话说，这个视图里有三个表的部分数据，以后可以直接从这个视图查某些数据
//比如
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
//这就是从视图中检索特定的数据（prod_id的数据在视图中）

 22.2.2 用视图重新格式化检索出的数据（视图又一功能）
也就是说，如果经常使用一些联结时，可以把这些转换成视图，以后就不用每次查询，直接使用转换过的视图就可以了。
 22.2.3 用视图过滤不想要的数据
 22.2.4 使用视图与计算字段
22.2.5 更新视图
视图是可以进行更新的，但不是所有的都可以（并且大部分都不能更新）。应该注意的是，视图主要用于数据检索。
有如下操作，则不能更新视图：
 分组（使用GROUP BY和HAVING）；
 联结；
 子查询；
 并；
 聚集函数（Min()、Count()、Sum()等）；
 DISTINCT；
 导出（计算）列。