MySQL必知必会之15-20

15.联结表

SQL最强大的功能之一就是能在数据检索查询的执行中联结表。联结是利用SQL的SELECT能执行的最重要的操作。

关系表的设计是要保证把信息分解成多个表，一类数据一个表，各表通过一定的关系互相关联。

外键：外键为某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性：能够适应不断增加的工作量而不失败。

15.1 联结

联结是一种机制，用来在一条select语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

15.2 创建联结

SELECT vend_name,prod_name,prod_price FROMvendors,products WHERE vendo.vend_id = products.vend_id ORDER BYvend_name,prod_name;

+-------------+----------------+------------+

| vend_name | prod_name | prod_price |

+-------------+----------------+------------+

| ACME | Bird seed |10.00 |

| ACME | Carrots |2.50 |

| ACME | Detonator |13.00 |

| ACME | Safe | 50.00 |

| ACME | Sling | 4.49 |

| ACME| TNT (1 stick) |2.50 |

| ACME | TNT (5 sticks) | 10.00 |

| Anvils R Us | .5 ton anvil |5.99 |

| Anvils R Us | 1 ton anvil |9.99 |

| Anvils R Us | 2 ton anvil |14.99 |

| Jet Set | JetPack 1000 | 35.00 |

| Jet Set | JetPack 2000 |55.00 |

| LT Supplies | Fuses | 3.42 |

| LT Supplies | Oil can |8.99 |

+-------------+----------------+------------+

14 rows in set (0.05 sec)

应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。

15.3 内部联结

基于两个表之间相等测试的联结称为等值联结，也叫内部联结。可以使用稍微不同的语法来实现这种类型的联结。

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

这里两个表之间的关系式FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

15.4联结多个表

SELECT cust_name,cust_contact FROMcustomers,orders,orderitems WHERE customers.cust_id = orders.cust_id ANDorderitems.order_num = orders.order_num AND prod_id = 'TNT2';

+----------------+--------------+

| cust_name | cust_contact |

+----------------+--------------+

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

+----------------+--------------+

2 rows in set (0.02 sec)

这里实现了14章中子查询的功能。

16.创建高级联结

16.1使用表别名

SELECT cust_name,cust_contact FROM customersAS c,orders AS o,orderitemsAS oi WHERE c.cust_id = o.cust_id AND oi.order_num=o.order_numAND prod_id = 'TNT2';

表别名布局能用于WHERE子句还可以用于SELECT的列表，ORDER BY子句以及语句的其他部分。

16.2使用不同类型的联结

自联结，自燃联结和外部联结

16.2.1 自联结

SELECT p1.prod_id,p1.prod_name FROMproducts AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id ='DTNTR';

16.2.2 自然联结

自然联结排除多次出现，使每个列值返回一次。事实上，我们建立的每个内部联结都是自然联结。

16.2.3外部联结

mysql> SELECTcustomers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN

orders ON customers.cust_id =orders.cust_id;

+---------+-----------+

| cust_id | order_num |

+---------+-----------+

|10001 | 20005 |

|10001 | 20009 |

|10002 | NULL |

|10003 | 20006 |

|10004 | 20007 |

|10005 | 20008 |

+---------+-----------+

6 rows in set (0.00 sec)

与内部联结关联两个表中的行不同的是外部联结还包括没有关联的行。在使用OUTER JOIN时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出OUTER JOIN右边的表，LEFT指出OUTER JOIN左边的表）。

16.3使用带聚集函数的联结

mysql> SELECTcustomers.cust_name,customers.cust_id,COUNT(orders.order_num) AS N

num_ord FROM customers INNER JOIN orders ONcustomers.cust_id = orders.cust_id G

ROUP BY customers.cust_id;

+----------------+---------+----------+

| cust_name | cust_id | Nnum_ord |

+----------------+---------+----------+

| Coyote Inc. |10001 | 2 |

| Wascals |10003 | 1 |

| Yosemite Place | 10004 | 1 |

| E Fudd |10005 | 1 |

+----------------+---------+----------+

4 rows in set (0.00 sec)

17.组合查询

组合查询也通常称为并（union）或复合查询

有两种基本情况，其中需要使用组合查询：
1）在单个查询中从不同的表返回类似结构的数据

2）对单个表执行多个查询，按单个查询返回数据

17.1创建组合查询

SELECT vend_id,prod_id,prod_price FROM productsWHERE prod_price <=5 UNION SELECT vend_id,prod_id,prod_price FROM productsWHERE vend_id IN(1001,1002);

将UNION换位UNION ALL可以可以包含不同SELECT子句选中的重复的列

17.2 UNION规则

1）UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。

2）UNION中的 每个查询必须包含相同的列、表达式或聚集函数

3）列数据类型可以不完全相同，但必须兼容

17.3对组合查询结果排序

ORDER BY必须出现在最后一条SELECT语句之后

18.全文本搜索

在进行全文本搜索之前，首先应将表中的一列或多列设为FULLTEXT。然后使用Match()和Against()执行全文搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

18.1进行全文本搜索

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

+-------------------------------------------------------------------------------

----------------------------------------+

| note_text

|

+-------------------------------------------------------------------------------

----------------------------------------+

| Customer complaint: rabbit has been ableto detect trap, food apparently less

effective now. |

| Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, andsuitable for use as rabbit bait. |

+-------------------------------------------------------------------------------

----------------------------------------+

2 rows in set (0.05 sec)

上面输出的两行结果的输出顺序是进过排序后得到的，也就是包含词rabbit作为第3个词的等级比作为第20个词的行高。下面演示全文本搜索如何排序工作。

mysql> SELECT note_text,Match(note_text)Against('rabbit') AS rank FROM productn

otes;

+-------------------------------------------------------------------------------

-----------------------------------------------------------------------------+--

----------------+

| note_text

| r

ank |

+-------------------------------------------------------------------------------

-----------------------------------------------------------------------------+--

----------------+

| Customer complaint:

Sticks not individually wrapped, too easyto mistakenly detonate all at once.

Recommend individual wrapping. | 0 |

| Can shipped full, refills not available.

Need to order new can if refill needed.

| 0 |

| Safe is combination locked, combinationnot provided with safe.

This is rarely a problem as safes aretypically blown up or dropped by customers

.| 0 |

| Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, andsuitable for use as rabbit bait.

| 1.59055435657501 |

| Included fuses are short and have beenknown to detonate too quickly for some

customers.

Longer fuses are available (item FU1) andshould be recommended. |

0 |

| Matches not included, recommend purchaseof matches or detonator (item DTNTR).

|

0 |

| Please note that no returns will beaccepted if safe opened using explosives.

|

0 |

| Multiple customer returns, anvils failingto drop fast enough or falling backw

ards on purchaser. Recommend that customerconsiders using heavier anvils. |

0 |

| Item is extremely heavy. Designed for dropping,not recommended for use with s

lings, ropes, pulleys, or tightropes. |

0 |

| Customer complaint: rabbit has been ableto detect trap, food apparently less

effective now. | 1

.64080536365509 |

| Shipped unassembled, requires commontools (including oversized hammer).

|

0 |

| Customer complaint:

Circular hole in safe floor can apparentlybe easily cut with handsaw.

| 0 |

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

sed for dropping, recommend ANV02 or ANV03instead. | 0 |

| Call from individual trapped in safeplummeting to the ground, suggests an esc

ape hatch be added.

Comment forwarded to vendor. | 0 |

+-------------------------------------------------------------------------------

-----------------------------------------------------------------------------+--

----------------+

14 rows in set (0.03 sec)

这里，在SELECT而不是WHERE子句中使用Match()和Against()。这使所有行都被返回。Match()和Against()用来建立一个计算列，此列包含全文本搜索计算出的等级值。等级有MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。

18.2使用查询扩展

查询扩展用来设法房款所返回的全文本搜素结果的范围。在使用查询扩展时，MySQL对数据和索引进行两边扫描来完成搜索：

1） 首先，进行一个基本的全文本搜索，找粗与搜索条件匹配的所有行

2） 其次，MySQL检查这些匹配行并进行选择所有有用的词（我们将会简要地解释MySQL如何判定什么有用，什么无用）

3） 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词

mysql> select note_text FROMproductnotes WHERE Match(note_text) Against('anvils

' WITH QUERY EXPANSION);

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| note_text

|

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| Multiple customer returns, anvils failingto drop fast enough or falling backw

ards on purchaser. Recommend that customerconsiders using heavier anvils. |

| Customer complaint:

Sticks not individually wrapped, too easyto mistakenly detonate all at once.

Recommend individual wrapping. |

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

sed for dropping, recommend ANV02 or ANV03instead. |

| Please note that no returns will beaccepted if safe opened using explosives.

|

| Customer complaint: rabbit has been ableto detect trap, food apparently less

effective now.|

| Customer complaint:

Circular hole in safe floor can apparentlybe easily cut with handsaw.

|

| Matches not included, recommend purchaseof matches or detonator (item DTNTR).

|

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

7 rows in set (0.00 sec)

这里第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customer和recommend）,所以也被检索出来。接下去的几行也会如此。

18.3布尔文本搜索

MySQL支持全文本搜索的另外一种形式，布尔搜索，可以提供如下内容的细节：

1） 要匹配的词

2） 要排斥的词（如果某行包含这种词，则不返回改行，即使它包含要匹配的词）

3） 排列提示（指定某些词比其他次更重要，更重的词等级更高）

4） 表达式分组

5） 另外一些内容

布尔方式不同于一般的全文本搜索的地方在于：即使没有定义FULLTEXT索引，也可以使用它。

布尔操作符与说明如下：

+ 包含，词必须存在

- 排除，词必须不出现

> 包含，而且增加等级值

<包含，且减少等级值

() 把词组成子表达式

~ 取消一个词的排序值

* 词尾的通配符

“ ” 定义一个短语

SELECT note_text FROM productnotes WHERE Match(note_text)Against('heavy' IN BOOLEAN MODE);//输出结果如下

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| note_text

|

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| Item is extremely heavy. Designed fordropping, not recommended for use with s

lings, ropes, pulleys, or tightropes. |

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

sed for dropping, recommend ANV02 or ANV03instead. |

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

2 rows in set (0.00 sec)

这里使用了关键字IN BOOLEAN MODEN

mysql> SELECT note_text FROMproductnotes WHERE Match(note_text) Against('heavy

-rope*' IN BOOLEAN MODE);

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| note_text

|

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

| Customer complaint:

Not heavy enough to generate flying starsaround head of victim. If being purcha

sed for dropping, recommend ANV02 or ANV03instead. |

+-------------------------------------------------------------------------------

---------------------------------------------------------------------------+

1 row in set (0.00 sec)

这里-rope*明确地指示MySQL排除包含rope*。

下面举几个例子

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘+rabbit +bait’ IN BOOLEAN MODE);//这个搜索匹配包含词rabbit和bait的行

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘rabbit bait’ IN BOOLEAN MODE);//没有指定操作符，这个搜索匹配rabbit和bait中的至少一个词的行

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘”rabbit +bait”’ IN BOOLEAN MODE);//这个词匹配短语rabbitbait而不是匹配两个词rabbit和bait

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘>rabbit <bait’ IN BOOLEAN MODE);//增加rabbit的等级，降低bait的等级

SELECT note_text FROM productnotes WHEREMatch(note_text) Against(‘+safe +(<cobination)’ IN BOOLEAN MODE);//这个搜索匹配词safe和combination，降低后者的等级

19.插入数据

19.1插入完整的行

mysql> INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi

p,cust_country,cust_contact,cust_email)VALUES('Pep E. Lapew','100 main Street',

'LosAngeles','CA','90046','USA','NULL','NULL');

Query OK, 1 row affected (0.06 sec)

19.2插入多个行

mysql> INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi

p,cust_country) VALUES('Pep E. Lapew','100main Street',

'Los Angeles','CA','90046','USA'),(‘M.Martian’,’42 Galaxy Way’,’New York’,’NY’,’11213’,’USA’);

19.3插入检索出的数据

INSERT INTOcustomers(cust_name,cust_address,cust_city,cust_state,cust_zi

p,cust_country)SELECTcust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_countryFROM custnew;

20.更新和删除数据

20.1跟新数据

两种方式

1）更新表中所有行,不加WHERE

2）更新表中特定行，加WHERE

UPDATE语句由3个部分组成，分别是：

1） 要更新的表

2） 列名和他们的新值

3） 确定要更新的过滤条件

mysql> UPDATE customers SET cust_name ='The Fudds',cust_email = '[e-mail]

' WHERE cust_id = 10005;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1Warnings: 0

20.2删除数据

两种方式：

1） 从表中删除特定的行，不省略WHERE子句

2） 从表中删除所有行，省略WHERE子句

DELETE FROM customers WHERE cust_id = 10006;

DELETE删除的是表的内容而不是表本身

要删除所有行，不要使用DELETE，用TRUNCATETABLE;

==参考MySQL必知必会