MySQL（十）操纵表及全文本搜索
一、创建表
MySQL不仅用于表数据操作，还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。
创建表一般有如下两种方式：
①使用具有交互式创建和管理表的工具；
②直接使用MySQL语句操纵表；
1、表创建基础
使用程序创建表，可使用SQL中的create table语句，需要以下两个信息：
①新表的名字，在关键字create table后给出；
②表列的名字和定义，用逗号分隔；
例如：
create table usertables
(
user_id int not null auto_increment,
user_name char(50) null,
user_address char(50) null,
user_city char(50) null,
user_email char(50) null,
user_mobile char(50) null,
primary key (user_id)
)engine=InnoDB;
这条SQL语句中，表名usertables紧跟在create table之后，实际的表定义（所有列）括在圆括号内，各列间用逗号分隔，表的主键用primary key指定为user_id。
PS:创建新表时，指定的表名必须不存在（如果只想在一个表不存在时创建它，应在表名前给出if not exists：这样做不检查表模式是否与打算创建的表模式匹配，只检查表名是否存在）。
2、使用null值
null就是没有值或者缺值；允许null值的列也允许在插入行时不给出该列的值，不允许null值的列不接受该列没有值的行（插入或更新行时，该列必须有值）；
每个表列或者是null列，或者是not null列，这种状态在创建时由表的定义规定，比如上面的例子；或者表中混合存在null和not null列。
PS：null为默认值，如果不指定not null，则认为指定的是null。
3、主键
主键值必须唯一，即表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一；如使用多个列，则这些列的组合值必须唯一。
创建表时，主键都用类似primary key的语句定义：primary key（column）；创建由多个列组成的主键，应该以逗号分隔的列表给出各列名（主键可以在创建表时定义，或者在创建表之后定义）。
4、自动增量
例如：cust_id int nut null auto_increment,
auto_increment告诉MySQL，本列每当增加一行时自动增量；每次执行一个insert操作时，MySQL自动对该列增量，给该列赋予下一个可用的值;
每个表只允许一个auto_increment列，而且它必须被索引（比如，通过使它成为主键）
last_insert_id：此函数指示MySQL返回最后一个auto_increment值，然后可将它应用于后续MySQL语句。
5、指定默认值
如果在插入行时没有给出值，MySQL允许指定此时使用的默认值；默认值用create table语句的列定义中的default关键字指定，例如：
create table usertables
(
user_id int not null，
user_name char(50) not null,
user_address char(50) not null,
user_city char(50) not null default shanghai,
user_email char(50) not null,
user_mobile char(50) not null,
primary key (user_id)
)engine=InnoDB;
这条语句中创建包含组成user信息的表，user_city列包含user的所在城市，该列的描述添加了default shanghai，在未给出城市的情况下使用上海（MySQL不允许使用函数作为默认值，它只支持常量）。
6、引擎类型
MySQL有一个具体管理和处理数据的内部引擎，使用create table语句时，该引擎具体创建表；使用select语句或进行其他数据库处理时，该引擎内部处理请求（引擎隐藏在DBMS内，不需要过多关注）。
MySQL相比于其他DBMS的区别在于，它具有多种引擎；因为各个引擎有不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
如果省略engine=语句，则使用默认引擎（很可能是MYISAM），但并不是所有情况都这样，所以，engine语句很重要！
常见的几种搜索引擎：
InnoDB:一个可靠的事务处理引擎，不支持全文本搜索；
MEMORY：在功能上等同于MyISAM,数据存储在内存（不是磁盘），速度很快（特别适合临时表）；
MyISAM：一个性能极高的引擎，支持全文本搜索，但不支持事务处理；
PS:引擎类型可以混用，但缺陷在于：外键不能跨引擎，即：使用一个引擎的表不能引用具有使用不同引擎的表的外键。
二、更新表
为更新表定义，可以使用alter table语句（很少使用）；为了更新表结构，必须给出如下信息：
①alter table之后给出要更改的表名（该表必须存在，否则会报错）
②所做更改的列表；
例如：alter table usertable add user_country char(50);
这条SQL语句给usertable表增加了一个user_country的列，必须明确其数据类型。
删除刚添加的列，可以这样：
alter table usertable drop column user_country;
PS:alter table的一种常见用途是定义外键。
如果要多比较复杂的表进行更改，一般需要手动删除过程，涉及的步骤如下：
①用新的列布局创建一个表；
②使用insert select语句从旧表复制数据到新表，如果有必要，可使用转换函数和计算字段；
③检验包含所需数据的新表；
④重命名旧表（如果确定，可以删除它）；
⑤用旧表原来的名字重命名新表；
⑥根据需要，重新创建触发器、存储过程、索引和外键。
三、删除表
删除表（删除整个表而不是其内容），使用drop table语句，例如：
drop table usertable；
这条语句删除usertable表（假设它存在）；删除表没有确认，也不能撤销，执行这条语句将永久删除该表。
还可以重命名表，例如：
rename table usertable to usertablebase；
这条语句用rename table语句重命名一个表；还可以对多个表进行重命名，每个表之间用逗号间隔。
四、全文本搜索
1、启用索引
MySQL支持几种基本的数据库引擎，MySQL最长用的两个引擎为：MyISAM和InnoDB：
MyISAM支持全文本搜索，查询效率高；但局限在于不支持事务和外键；
InnoDB支持事务和外键，和MyISAM各有优劣；
与全文本搜索功能类似的有通配符和正则表达式匹配，但性能较低，通常会匹配表的所有行，而且这些搜索极少使用表索引，不能做到明确控制，且返回的结果不智能化；
在使用全文本搜索时，mysql不需要分别查看每个行，不需要分析和处理每个词，只需索引被搜索的列（需要随着数据的改变不断重新索引）
一般在创建表时启用全文本搜索（必须索引被搜索的列），create table语句接受fulltext子句，它给出被索引列的一个逗号分隔的列表；
例如：
CREATE TABLE productnotes
(
note_id int not null auto_incerement,
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 table语句定义表productnotes并列出它所包含的列即可；其中MySQL根据子句fulltext对（note_text）进行索引。
在定义之后，MySQL自动维护该索引；在增加、删除、或更新行时，索引随之自动更新。
PS：不要再导入数据时使用fulltext，这样有助于更快的导入数据。
2、执行索引
启用索引后，使用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作为搜索文本。
PS：传递给match（）的值必须与fulltext（）定义中的相同；如果指定多个列，则必须列出它们（次序正确）；除非使用binary方式，否则全文本搜索不区分大小写（上面的例子没有使用该方式）。
like子句具有和全文本搜索相同的功能，但区别在于：全文本搜索的特点是对结果进行排序，具有较高等级的行先返回（如果排序多个搜索项，则包含多数匹配词的行将具有更高的优先级）。
3、查询扩展
作用:用来设法放宽所返回的全文本搜索结果的范围（MySQL对数据和索引进行两遍扫描来完成搜索）。
检索过程：
①进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
②MySQL检查这些匹配行并选择所有有用的词（将会简要的解释MySQL如何断定什么有用什么无用）；
③MySQL再次进行全文本搜索，这次不仅使用原来的条件，还是用所有有用的词。
用法：where子句中against指定的搜索表达式后跟with query erpansion;
比如：where match(note_text) against('rabbit' with query erpansion);
PS:表中的行越多（行的文本越多），实用查询扩展返回的结果越好。
4、布尔文本搜索
MySQL还支持另一种全文本搜索方式，称为布尔方式（boolean mode）；使用布尔方式需要提供一下条件：
①要匹配的词；
②要排斥的词（如果某行包含这个词，则不返回，即使它包含其他指定的词也是如此）；
③排列提示（指定某些词比其他词重要，更重要的词返回的等级更高）；
④表达式分组；
⑤其他的内容。
PS：即使没有fulltext索引也可以使用布尔方式（但这种方式很缓慢）。
例如：select note_text
from productnotes
where match(note_text) against('heavy -rope*' in bollean mode);
此全文本搜索检索包含词heavy的所有行，其中使用了in boolean mode以及布尔操作符，-rope*指示MySQL排除包含rope*（任何以rope开始的词）的行。
全文本布尔操作符列表如下：
下面列举几个例子，说明这些操作符的使用：
①这个搜索匹配包含次rabbit和biat的行：
select note_text
from froductnotes
where match(note_text) against('+rabbit +bait' in boolean mode);
②没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行：
select note_text
from froductnotes
where match(note_text) against('rabbit bait' in boolean mode);
③这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait：
select note_text
from froductnotes
where match(note_text) against('"rabbit bait"' in boolean mode);
PS：在布尔方式中，不按照等级降序排序返回的行。
5、全文本搜索使用说明
①在索引全文本数据时，短词被忽略且从索引中删除（短词定义为3个或3个以下字符的词：如果需要可以更改）；
②MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本搜索时总被忽略（如果需要，可以覆盖这个列表）；
③MySQL50％规则：如果一个词出现在50％以上的行中，则将它作为一个非用词忽略；50％规则不用于in boolean mode；
④如果表中的行数少于3行，则全文本搜索不返回结果；
⑤忽略词中的单引号，例如don't索引为dont；
⑥不具有词分隔符的语言不能恰当的返回全文本搜索结果；
⑦仅在MyISAM数据库引擎中支持全文本搜索。
