MySQL基础：子查询、内部联结inner join、左外联结left outer join、右外联结right outer join、合并查询union与union all、全文本搜索

第14章 子查询
#列出订购物品TNT2的所有客户的姓名、联系人
1、在orderitems表中由prod_id查找order_num
select order_num from  orderitems where prod_id='TNT2'
2、在orders表中由order_num查找cust_id
select cust_id from orders where order_num in (select order_num from  orderitems where prod_id='TNT2');
3、在customers表中由cust_id查找cust_name,cust_contact
select cust_name,cust_contact from customers 
where cust_id in 
(select cust_id from orders where order_num in (select order_num from  orderitems where prod_id='TNT2'));

#显示customers表中每个客户的姓名、所在州、订单总数
select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers;
#错误示范
select cust_name,cust_state,(select count(*) from orders where cust_id = cust_id) as orders from customers;

 

第15章 内部联结（or 等值联结）
如需要多张数据表的数据进行查询，则可以通过联结运算符实现多个查询。
分类包括：内部联结、外部联结。
内部联结包括：等值和非等值的联结查询、自联结查询。
外部联结包括：左外联结、右外联结。

select vend_name,prod_name,prod_price from vendors,products 
where vendors.vend_id=products.vend_id
order by vend_name,prod_name;
#等价于上式
select vend_name,prod_name,prod_price from vendors 
inner join products on vendors.vend_id=products.vend_id
order by vend_name,prod_name;

#笛卡儿积（检索出的行的数目是第一个表的行数乘第二个表的行数）
select vend_name,prod_name,prod_price from vendors,products 
order by vend_name,prod_name;

#多表连接
select vend_name,prod_name,prod_price,quantity from vendors,products,orderitems
where vendors.vend_id=products.vend_id
and orderitems.prod_id=products.prod_id
and order_num=20005;
#等价于上式
select vend_name,prod_name,prod_price,quantity from vendors
inner join products on vendors.vend_id=products.vend_id
inner join orderitems on orderitems.prod_id=products.prod_id
where order_num=20005;

#列出订购物品TNT2的所有客户的姓名、联系人
#法1
select cust_name,cust_contact from customers 
where cust_id in 
(select cust_id from orders where order_num in (select order_num from  orderitems where prod_id='TNT2'));
#以下两种方法均等价于上式
#法2
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';
#法3
select cust_name,cust_contact from customers 
inner join  orders on orders.cust_id=customers.cust_id
inner join orderitems on orderitems.order_num=orders.order_num
where prod_id='TNT2';

 

 

第16章 高级联结
#使用表别名
select cust_name,cust_contact 
from customers as c,orders as o,orderitems as oi
where o.cust_id=c.cust_id
and oi.order_num=o.order_num
and prod_id='TNT2';

#自联结（属于内部联结）
#搜索物品DTNTR的生产商生产的其它物品的id和名字
select prod_id,prod_name from products where vend_id in (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';
注：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，使用联结比处理子查询快得多

#自然联结(自然联结使每个列只返回一次，迄今为止用到的每个内部联结都是自然联结，可能永远不会用到非自然联结的内部联结)
select c.*,oi.prod_id,oi.quantity,oi.item_price,o.order_num,o.order_date
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';

#外部联结
#检索所有客户id、姓名及其订单（内部联结）
select customers.cust_id,orders.order_num from customers
inner join orders on customers.cust_id=orders.cust_id;
#检索所有客户id、姓名及其订单，包括那些没有订单的客户
#左外联结（以左表作为主表，从左表中选择所有行）
select customers.cust_id,orders.order_num from customers
left outer join orders on customers.cust_id=orders.cust_id;
#右外联结（以右表作为主表，从右表中选择所有行）
select customers.cust_id,orders.order_num from customers
right outer join orders on customers.cust_id=orders.cust_id;#此例中该查询与内部联结查询效果相同
注：左外联结与右外连接可以转换

#带聚集函数的联结
#检索所有客户id、姓名及每个客户所下的订单数
select customers.cust_id,customers.cust_name,count(orders.order_num) as num_ord from customers
INNER JOIN orders on customers.cust_id=orders.cust_id
group by customers.cust_id;
#检索所有客户及每个客户所下的订单数,包括没有下订单的客户
select customers.cust_id,customers.cust_name,count(orders.order_num) as num_ord from customers
LEFT OUTER JOIN orders on customers.cust_id=orders.cust_id
group by customers.cust_id;
#等价于上式
select cust_id,cust_name,(select count(*) from orders where orders.cust_id = customers.cust_id) as num_ord from customers;

 

第17章 合并查询
任何具有多个where子句的select语句都可以作为一个合并查询给出

#需要价格小于等于5的所有物品的一个列表（包括产品id，供应商id，产品价格），还想包括供应商1001和1002生产的所有物品（不考虑价格）
select prod_id,vend_id,prod_price from products where prod_price<=5;
select prod_id,vend_id,prod_price from products where vend_id in (1001,1002);
select prod_id,vend_id,prod_price from products where prod_price<=5 or vend_id in (1001,1002);
#上式等价于
select prod_id,vend_id,prod_price from products where prod_price<=5
UNION
select prod_id,vend_id,prod_price from products where vend_id in (1001,1002);

#union默认不包含重复的行，若想要包含重复的行，用union all
select prod_id,vend_id,prod_price from products where prod_price<=5
UNION ALL
select prod_id,vend_id,prod_price from products where vend_id in (1001,1002);

#对合并查询结果排序，只能使用一条order by子句，且必须出现在最后一条select语句之后
select prod_id,vend_id,prod_price from products where prod_price<=5
UNION
select prod_id,vend_id,prod_price from products where vend_id in (1001,1002)
order by vend_id,prod_price;

 

第18章 全文本搜索
两个最常用的存储引擎是MyISAM和InnoDB
其中，MyISAM支持全文本搜索，而InnoDB不支持
在本例中，只有表productnotes的存储引擎是MyISAM
全文本搜索可以解决like通配符匹配和正则表达式匹配所不能解决的问题

#查看存储引擎
show create table productnotes;

#搜索包含rabbit的note_text
select note_text from productnotes where match(note_text) against('rabbit');
#与上式等价
select note_text from productnotes where note_text like '%rabbit%';

#列出note_text及全文本搜索计算出的等级
select note_text,match(note_text) against('rabbit') as rank from productnotes;

#使用查询扩展，放宽所返回的全文本搜索结果的范围
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);

#布尔文本搜索（即使没有fulltext索引，也可使用）
全文本搜索的另外一种形式，可以提供关于如下内容的细节：
1、要匹配的词
2、要排斥的词：如果某行包含该词，则不返回该行
3、排列提示：指定某些词比其他词更重要，更重要的词等级更高
4、表达式分组

全文本布尔操作符
布尔操作符                                   说明
     +                                  包含，词必须存在
     -                                   排除，词必须不出现
     >                                  包含，而且增加等级值
     <                                  包含，且减少等级值
     ()                                  把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
     ~                                  取消一个词的排序值
     *                                   词尾的通配符
     ""                                  定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
 
 #搜索包含heavy的note_text
select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
select note_text from productnotes where match(note_text) against('heavy');#与上式相同，因为没有指定布尔操作符

#匹配包含heavy,但不包含任意以rope开头的词的行
select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode);

#匹配包含rabbit和bait的行
select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);

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

#匹配短语rabbit bait而不是匹配两个词rabbit和bait
select note_text from productnotes where match(note_text) against('"rabbit bait"' in boolean mode);

#匹配rabbit和carrot，增加前者等级，降低后者等级
select note_text from productnotes where match(note_text) against('>rabbit <carrot' in boolean mode);

#匹配safe和comnination，降低后者等级
select note_text from productnotes where match(note_text) against('+safe +(<combination)' in boolean mode);

注：在布尔方式中，不按等级值降序排序返回的行