1.子查询的使用

  • 进行过滤，试着将下列三个查询select语句组合为1条嵌套查询语句：
select order_num from orderitems where prod_id='tnt2';
select cust_id from orders where order_num in(20005,20007);//两个数字为上面的sql语句检索结果
select cust_name,cust_contact from customers where cust_id in(10001,10004);//两个数字为上面的sql语句检索结果

组合之后：

mysql> 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'));

多个表组合求解，得出给定物品购买者的用户信息。先从物品列表找到包含该物品的订单号，然后在对应的订单里面找到客户ID，最后在客户表中找到cust_id对应的客户信息。

  • 作为计算字段使用子查询
    如题，考虑当使用子查询语句作为计算字段时如何设计sql语句，见下实例：
mysql> select cust_name,cust_contact,(select count(*) from orders where customers.cust_id=orders.cust_id)                    
    -> as orders from customers order by cust_name;

2.内部联结表

联结方式

示例

WHERE子句

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;

显式指定

select vend_name,prod_name,prod_price from vendors INNER JOIN products ON vendors.vend_id=products.vend_id;

使用多个表

select cust_name from customers... where ... AND orders.order_num=orderitems.order_num AND prod_id='tnt2';

3.创建高级联结

联结类型

作用

自联结

用来替换在相同表中检索数据时使用的子查询语句

自然联结

保证每个列只是出现一次，要靠自己保证，基本所有内部联结都是自然联结

外部联结

用于解锁在两个表中没有关联关系的行，即表1中的主键找不到表2中对应的外键

  • 自联结
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';//自联结方式，使用别名更加简单
  • 外部联结
    常常配合LEFT与RIGHT关键字使用指定OUTER JOIN左边还是右边的表为包括其所有行的表：
//下面要求检索所有客户的订单号包括那些还没有订单的客户
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;    //外部联结，可满足题意检索所有客户

4.组合查询

查询方式

示例(所有语句等价)

UNION并

select vend_id,prod_price from products where prod_price <= 5 UNION select vend_id,prod_price from products where vend_id in(1001,1002);

WHERE子句

select vend_id,prod_price from products where prod_price<=5 OR vend_id in(1001,1002);

UNION ALL

UNION默认剔除重复行，而加上ALL之后不剔除重复行

排序

在union中order by子句只能出现在最后面，如：…order by vend_id;

5.全文本搜索

搜索方法

示例

match,against

match指定被搜索的列，against指定要使用的搜索表达式

全文本搜索

select note_text from productnotes where match(note_text) against(‘rabbit’);

LIKE语句

select note_text from productnotes where note_text LIKE ‘%rabbit%’;

查询扩展(with …)

select note_text from productnotes where match(note_text) against(‘rabbit’ with query expansion);

布尔文本搜索

示例

-

select note_text from productnotes where match(note_text) against(‘heavy -rope*’ in boolean mode);

+

select note_text from productnotes where match(note_text) against(’+rabbit +bait’ in boolean mode);

匹配任意一个

select note_text from productnotes where match(note_text) against(‘rabbit bait’ in boolean mode);

匹配整个词

select note_text from productnotes where match(note_text) against(’“rabbit bait”’ in boolean mode);

>

包含，增加等级值，如：… against(’>rabbit’ in boolean mode);

<

包含，降低等级值，如：against(’<carrot’ in boolean mode);

~

取消一个词的排序值

*

词尾通配符

注意：全文本搜索会按找等级值进行排序，而LIKE不会