MySQL入门学习笔记之三

使用子查询

使用子查询进行过滤

子查询从内向外处理

SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num 
                   FROM orderitems
                   WHERE prod_id = 'TNT2';

作为计算字段使用子查询

orders表
customers表

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

联结表

联结

创建联结–注意限定列名

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

前两个列在一张表中，prod_price在另一张表中

【注】应该保证所有联结都有WHERE子句。

内部联结—FROM 子句、INNER JOIN

上面使用的也是内部联结

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

联结多个表— AND

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id 
    AND orderitems.prod_id = products.prod_id
    AND order_num = 20005;

创建高级联结

表别名

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
    AND OI.order_num = o.order_num;

使用不同类型的联结

除了内部联结外，还有

自联结

使用子查询
SELECT prod_id, prod_name
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';

自然联结

自然连接排除多次出现，使每个列只返回一次。
【由我们来完成这项工作，选择那些唯一的列，比如id】

外部联结

检索所有客户以及其订单（内部联结）

SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

检索所有客户，包括没有订单的客户

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

没有订单的客户的订单项是NULL

组合查询—-UNION

两种情况使用组合查询：
1 在单个查询中从不同的表返回类似结构的数据
2 对单个表执行多个查询，按单个查询返回数据

例如

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
1 必须由两条或更多SELECT语句构成
2 列数据类型必须兼容

UNION 从查询结果中自动去除了重复的行。使用UNION ALL可以出现重复行。

对查询结果排序

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)
ORDER BY vend_id, prod_price;

全文本搜索

MyISAM支持全文本搜索，InnoDB不支持。

LIKE通配符和正在表达式的缺点

1 性能较差
2 不能明确控制
3 虽然灵活，但不够智能

全文本搜索

启用全文本搜索支持

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;

【不要在导入数据时使用FULLTEXT】
应该首先导入所有数据，再修改表。

进行全文搜索

结合Match() 函数：指定被搜索的列
和Against() 函数：指定要使用的搜索表达式

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

【注】传递给Match（）的值必须与FULLTEXT（）定义中的相同。指定多个列，则必须列出它们（并且次序相同）；
【注】全文本搜索不区分大小写
【注】全文本搜索对结果排序（文本中词靠前的行等级值高于靠后的行），Like不能

也可通过通配符实现
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

使用查询扩展

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

查询原理–MySQL对数据库和索引进行两遍扫描完成搜索

1 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
2 其次，检查这些匹配行，并选择所有有用的词
3 再次进行全文本搜索，使用原来的条件加上查找到的有用的词。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

布尔文本搜索–全文本搜索的另一种方式

【注】即使没有FULLTEXT索引也可以使用。操作非常缓慢

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

'+rabbit +bait'
搜索包含rabbit和bait的行

'rabbit bait'
搜索包含rabbit和bait至少一个的行

'"rabbit bait"'
搜索短语 rabbit bait

'>rabbit <carrot'
匹配rabbit和carrot，增加前者等级，降低后者等级

'+safe +(<combination)'
搜索匹配词safe和combination，降低后者等级