MySql必知必会 笔记chapter17-21

17 组合查询
用union来组合多条sql查询

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)；
select vend_id, prod_id, prod_price from products where prod_price<=5 or vend_id in (1001, 1002);

union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列次序可以不同）；列数据类型必须兼容。
union默认自动去除了重复的行，可以使用union all返回所有行（where完成不了返回所有行）
在用union组合查询时，只能使用一条order by子句，必须出现在最后一条select语句之后。它可以排序所有select语句返回结果

18 全文本搜索
MyISAM支持全文本搜索，InnoDB不支持全文本搜索
已有的搜索方法 like 和 正则表达式由限制 性能，明确控制，智能化的结果
使用前需要先索引，之后select可以与match（）和against（）使用
1 使用fulltext启用全文本搜素

create table productnotes
(
	note_id 		int			not null auto_increment,
	prod_id		char(10)	not null,
	note_date	datetime	not null,
	note_text		test			null,
	fulltest(note_text)
) engine = myisam;

不要在导入数据时使用fulltext
2 使用match和against进行全文本搜素

select note_text from productnotes where match(note_text) against('rabbit');

传递给match的值必须与fulltext定义中相同，如果指定多个列，则必须按顺序列出，一般情况下不区分大小写（binary）

select note_text, match(note_text) against('rabbit') as rank from productnotes; #rank对搜索结果排序

3 使用查询扩展

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);#扩展结果

布尔查询

select note_text from productnotes where match(note_text) against('heavy' in boolean mode);#是不是用boolean效果一样
select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode); #-rope*排除以rope开始的词的行

+包含， - 排除， >包含，且增加等级值， <包含，减少等级值， ()把词组成子表达式， ~取消一个词的排序值，
*词尾的通配符， “”定义一个短语

select note_text from productnotes where match(note_text) aganist('+rabbit +bait' in boolean mode);#包含rabbit和bait的行
select note_text from productnotes where match(note_text) aganist('rabbit bait' in boolean mode);#包含rabbit和bait中至少一个

select note_text from productnotes where match(note_text) aganist(' "rabbit bait" ' in boolean mode);#rabbit bait 短语匹配，不能分开
select note_text from productnotes where match(note_text) aganist('>rabbit <carrot' in boolean mode); #匹配rabbit和carrot，一个增加等级一个减少
select note_text from productnotes where match(note_text) aganist('+safe +(<combination)' in boolean mode); #匹配safe和combination，降低combination的等级
在布尔方式中，虽然有等级，但并不是按照等级值排序返回

注意点：
在索引是，短词（具有3个或3个以下字符的词）被忽略；
mysql内建非用词（stopword），这些词在索引是被忽略；
mysql规定一条50%规则，如果一个词出现在50%以上的行中，将被忽略，但不用于in boolean mode；
表中行少于3行，不返回结果，因为每个词或者不出现，或者至少出现在50%行中；
忽略单引号，don’t索引为dont；
不具有次分隔符的语言不能恰当的返回全文本搜索结果；
没有临近操作符。

19插入数据insert
插入单行

insert into customers (cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_county, cust_contact, cust_email)
			values('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

可以在INSERT操作中省略某些列,满足以下某个条件,该列定义为允许NULL值（无值或空值); 在表定义中给出默认值。
insert low_priority into 降低优先级，同样适用update和delete
插入多行

insert into customers (cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_county)
			values('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA')，
						('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

插入检索出的数据

insert into customers (cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_county, cust_contact, cust_email)
			select cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_county, cust_contact, cust_email 
			from custnew;  
--这个例子吧一个custnew的表中数据导入customers表中，注意主键值不能重复，否则出错，可以省略主键这一列，自动产生。列名不一定要一致，按照位置匹配。

20更新和删除数据

update customers set cust_email ='elmer@fudd.com' where cust_id=10005;

update ignore customers… 即使发生错误，也继续进行更新

delete from customers where cust_id=10006;

delete删除行而不是删除列。为了删除指定的列，使用update语句
delete不删除表本身
truncate table更快的完成删除所有行（实际是删除原来的表并重新创建一个表）

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
• 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
• 使用强制实施引用完整性的数据库（关于这个内容，请参阅第15章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

21 创建和操作表
创建表

create  table customers
(
	cust_id 			int				not null auto_increment,
	cust_name		char(50)		not null,
	cust_adress		char(50)		null,
	cust_city			char(50)		null 	default 'usa',
	cust_state		char(5)		null,
	cust_zip			char(10)		null,
	cust_country	char(50)		null,
	cust_contact	char(50)		null,
	cust_email		char(50)		null,
	primary key (cust_id)
)engine=innodb;

指定的表名必须不存在，否则将出错。如果仅在表不存在时创建它，应在表名后给出if not exists

null就是没有值或者缺值，not null不允许没有值。null不等于‘’
主键只能使用 not null
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）
select last_insert_id()返回最后一个auto_increment的值
default 1可以用于在未给出数量时使用的默认值，不允许使用函数作为默认值，只支持常量。
大多数sql的默认引擎myisam

• InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
• MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
• MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。

更新表alter table vendors add vend_phone char(20);
删除刚刚添加的列alter table vendors drop column vend_phone;
定义外键alter table orderitems add constraint fk_orderitems_orders foreign key(order_num) references orders (order_num);

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表；
• 使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第19章）从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
• 检验包含所需数据的新表；
• 重命名旧表（如果确定，可以删除它）；
• 用旧表原来的名字重命名新表；
• 根据需要，重新创建触发器、存储过程、索引和外键。

删除表drop table customers2;
重命名表rename table customers2 to customers, backup_vendors to vendors;