MYSQL学习

1. 联结join

                                /* 联结 */


SELECT prod_id, prod_name,vend_id from products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

#自联结
SELECT p1.prod_id, p1.prod_name FROM products as p1,products as p2 WHERE p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR'
UNION

SELECT p1.prod_id, p1.prod_name FROM products as p1,products as p2 WHERE p1.vend_id = p2.vend_id and p2.prod_id = 'FU1';

#自然联结
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price 
from customers as c, orders as o, orderitems as oi 
WHERE c.cust_id = o.cust_id
AND oi.order_num =o.order_num
AND prod_id = 'FB';

#外部联结
-- 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户
SELECT c.cust_id,o.order_num FROM customers c 
left JOIN orders o on c.cust_id = o.cust_id ;

SELECT c.cust_id,c.cust_name,count(o.order_num) AS num FROM customers c INNER JOIN orders o on c.cust_id = o.cust_id GROUP BY c.cust_id;
/*
LEFT JOIN:省略了outer关键字 表示取左边字段全集的值，若右边的表中没出现此值则为null。
RIGHT JOIN:与 LEFT JOIN 正好相反。
join:相当于 inner join。
*/
-- 列出所有产品以及订购数量，包括没有人订购的产品
SELECT p.prod_id,COUNT(oi.prod_id) num FROM products p left JOIN orderitems oi on p.prod_id = oi.prod_id GROUP BY p.prod_id;

/*注意 有聚集函数时一般存在group by关键字

2. UNION（EXCEPT）

                   /*组合查询*/
/* UNION
1.必须由两条或两条以上的select语句组成，以union为分隔
2.每个查询必须包含相同的列、表达式或聚集函数（不需要相同次序）
3.列数据类型必须兼容：类型不必完全相同

注意：
union会自动去重，若要更改此设置可以使用union all；
order by只能出现在最后一条select语句之后
*/

3. 全文本搜索

                   /*全文本搜索*/
/*
MyIsam支持全文本搜索
INNODB不支持全文本搜索

为什么要使用全文本搜索？
前面介绍了like和正则，他们都具有三个特性
	1.性能较低，要扫描所有行
	2.需要明确控制
	3.不能提供一个智能化的结果：不会匹配与该词相关的词语
	
使用全文本搜索，必须索引被搜索的列，而且随着数据的改变不断地重新索引。对表进行重新设计之后，mysql会自动进行所有的索引和重新索引


如何使用全文本搜索？
1.可以在创建表时使用FULLTEXT(key)关键字，注意engine=MyISAM。也可以稍后指定
2.定义之后，mysql会自动维护该索引在，在增删改时，索引会随之自动更新。
3.注意不要再导入数据时使用fulltext，更新索引需要花时间。如果正在导入数据到一个新表，此时不应该使用fulltext索引，应该先导入数据，然后再修改表，有助于更快地导入数据。
4.索引之后，使用函数Match()和Against()执行全文本搜索，match指定被搜索的列，Against制定我们要使用的搜索表达式。
*/

# 注意：
/*1.match和against会根据匹配程度生成一个rank，rank越高，行就会越靠前*/
-- 例子
SELECT note_text from productnotes
WHERE MATCH(note_text) against('rabbit');

SELECT note_text，MATCH(note_text) against('rabbit') as ranks from productnotes;

/*2.可以使用查询扩展* match(xxx) again('xxx' with QUERY expansion) */
SELECT note_text from productnotes
WHERE MATCH(note_text) against('rabbit' with QUERY expansion);

/*3.可以使用布尔文本搜索
可以搜索：
	1.要匹配的词
	2.要排斥的词（即使包含其他指定词）
	3.指定排列顺序（指定某些词更重要，该词rank更高）
	4.表达式分组
	
	操作符格式：
	+  包含
	-  排除
	>  包含并增加rank
	<  包含并减少rank
	() 把词组成子表达式，如 +(<word)意思是包含word并降低rank
	~  取消一个词的排序值
	*  词尾通配符
	""定义短语
*/
-- 布尔本文搜索格式：
...
match(note_text) Against('heavy -rope*'IN boolean MODE);

/*注意事项：
	1.短词会被自动排除，短词长度可自由定义
	2.存在stopword列表，里面的词会被自动忽略，可以更改覆盖
	3.如果有个词出现频率在50%以上，则会自动忽略
	4.根据注意事项3可知，表中行数要大于等于3才能使用全文本搜索
	5.会自动忽略单引号，如don't = dont
	6.不具有词分隔符
	7.仅在MyISAM中支持全文本搜索
	
*/

4. 增删改与表结构

                    /* 增删改表 创建表 */

-- 增
/*
  INSERT INTO 表名(字段1,字段2...) 
	VALUES ( '值1','值2'...)/(检索出来的值，一一对应
	
	使用 INSERT LOW_PRIORITY INTO ...可以降低insert语句的优先级，同样适用于update和delete
	可以一次插入多个行，用(),分隔开来
*/

-- 删
/*
	DELETE FROM 表名
	WHERE 字段名 = xxx;
*/

-- 改
/*
	可以更新表中的特定行或者所有行
	要记得用where不然数据全改了
*/
UPDATE customers
set cust_name = 'cris chu',
		cust_city = 'zhuhai'
where cust_id= '10005';

#删改注意事项：1.在删改前要先select测试一下，保证过滤的是正确的记录，以防编写的where子句不正确

-- 创建表
/*
	null不是空串，只是空值
	PRIMARY KEY() 设置主键
	ENGINE=xxx：指定引擎，一般使用MyISAM就够用，按照实际需求来，1. innoDB：事务处理引擎，不支持全文本搜索
																															2. memory：功能与MyISAM相同，但是数据储存在内存中，适合临时表
																															3. MyISAM：性能极高，支持全文本搜索，但不支持事务处理
																															！！注意：引擎混用时不能使用外键！！
	AUTO INCREMENT 定义字段为自增字段，注意：可以在insert的时候指定一个独一无二的值，后续的增量将从该指定值开始自增
	DEFAULT：默认值 使用方法 default 1（默认值为1），注意：最好使用default而不是null
	
*/
CREATE TABLE orderitems1
(	
	order_num int not null AUTO_INCREMENT,
	order_item int not null,
	PRIMARY KEY(order_num,order_item)
)ENGINE=INNODB;

create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade  # 同步更新
    on delete cascade,  # 同步删除
    foreign key(author_id) references author(id)
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);


-- 更新表
/*
	用于更新表的结构
	ALTER TABLE 表名 ADD/DROP
	常见用途：定义外键
	对单个表做多个更新需要用逗号分隔。
	
	语法：
	
1. 修改表名
      ALTER TABLE 表名
                          RENAME 新表名;
2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
3. 删除字段
      ALTER TABLE 表名
                          DROP 字段名;
4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
      ALTER TABLE 表名
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
*/
ALTER TABLE vendors
drop  vend_phone 

-- 删除表
/*
DROP TABLE 表名;
注意：操作不可撤销
*/

-- 重命名表
/*
	RENAME TABLE customers2 TO customers;
	可以对多个表同时操作，用逗号隔开。
*/