目录
1. 组合查询
多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。
有两种基本情况，其中需要使用组合查询：
- 口在单个查询中从不同的表返回类似结构的数据；
- 口对单个表执行多个查询，按单个查询返回数据。
组合查询和多个WHERE条件
多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。
UNION与WHERE
UNION几乎总是完成与多个WHERE条件相同的工作，UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE.
1.1 使用union
可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条select语句，将他们的结果组合成单个结果集。
mysql> select vend_id,prod_id,prod_price from products where prod_price <=5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
4 rows in set (0.00 sec)
mysql> select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
5 rows in set (0.00 sec)
//第一条SELECT检索价格不高于5的所有物品。第二条SELECT使用IN找出供应商1001和1002生产的所有物品。
//组合这两条语句 使用union
//UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集
mysql> select vend_id,prod_id,prod_price from products where vend_id in (1001,1002)
-> union select vend_id,prod_id,prod_price from products where prod_price <=5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | FC | 2.50 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)
//作为参考，这里给出使用多条WHERE子句而不是使用UNION的相同查询
mysql> select vend_id,prod_id,prod_price from products where prod_price <=5 or vend_id in(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)
//UNION从查询结果集中自动去除了重复的行
//这是UNION的默认行为，如果想返回所有匹配行，可使用UNION ALL而不是UNION.
mysql> select vend_id,prod_id,prod_price from products where vend_id in (1001,1002)
-> union all select vend_id,prod_id,prod_price from products where prod_price <=5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
9 rows in set (0.00 sec)
//对组合查询结果进行排序
mysql> select vend_id,prod_id,prod_price from products where vend_id in (1001,1002)
-> union all select vend_id,prod_id,prod_price from products where prod_price <=5 order by vend_id,prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | FC | 2.50 |
| 1003 | TNT1 | 2.50 |
| 1003 | SLING | 4.49 |
+---------+---------+------------+
9 rows in set (0.00 sec)
2. 全文本搜索
并非所有引擎都支持全文本搜索
MySQL支持几种基本的数据库引擎，并非所有的引擎都支持本书所描述的全文本搜索。两个最常使用的引擎为My ISAM和InnoDB，前者支持全文本搜索，而后者不支持。样例表（productnotes表）使用MyISAM。如果你的应用中需要全文本搜索功能，应该记住这一点。
在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。
2.1 使用全文本搜索
一般在创建表时启用全文本搜索。CREATE TABLE语句 接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。
mysql> show create table productnotes;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| productnotes | CREATE TABLE `productnotes` (
`note_id` int(11) NOT NULL AUTO_INCREMENT,
`prod_id` char(10) NOT NULL,
`note_date` datetime NOT NULL,
`note_text` text,
PRIMARY KEY (`note_id`),
FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT（note_text）的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
可以在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。
不要在导入数据时使用FULLTEXT
更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引.应该首先导入所有数据，然后再修改表，定义FULLTEXT.这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。
2.2 进行全文本搜索
在索引之后，使用两个函数Match（）和Against（）执行全文本搜索，
其中Match（）指定被搜索的列，Against（）指定要使用的搜索表达式。
mysql> select * from productnotes;
+---------+---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_id | prod_id | note_date | note_text |
+---------+---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 101 | TNT2 | 2005-08-17 00:00:00 | Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping. |
| 102 | OL1 | 2005-08-18 00:00:00 | Can shipped full, refills not available.
Need to order new can if refill needed. |
| 103 | SAFE | 2005-08-18 00:00:00 | Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
| 104 | FC | 2005-08-19 00:00:00 | Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| 105 | TNT2 | 2005-08-20 00:00:00 | Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended. |
| 106 | TNT2 | 2005-08-22 00:00:00 | Matches not included, recommend purchase of matches or detonator (item DTNTR). |
| 107 | SAFE | 2005-08-23 00:00:00 | Please note that no returns will be accepted if safe opened using explosives. |
| 108 | ANV01 | 2005-08-25 00:00:00 | Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| 109 | ANV03 | 2005-09-01 00:00:00 | Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. |
| 110 | FC | 2005-09-01 00:00:00 | Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
| 111 | SLING | 2005-09-02 00:00:00 | Shipped unassembled, requires common tools (including oversized hammer). |
| 112 | SAFE | 2005-09-02 00:00:00 | Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw. |
| 113 | ANV01 | 2005-09-05 00:00:00 | Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
| 114 | SAFE | 2005-09-07 00:00:00 | Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor. |
+---------+---------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
14 rows in set (0.00 sec)
//Match（note_text）指示MySQL针对指定的列进行搜索
//Against（'rabbit'）指定词rabbit作为搜索文本。
mysql> select note_text from productnotes where match(note_text) against('rabbit');
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
//上述搜索也可用like完成
mysql> select note_text from productnotes where note_text like '%rabbit%';
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)上述两条SELECT语句都不包含ORDER BY子句。后者（使用LIKE）以不特别有用的顺序返回数据。前者（使用全文本搜索）返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。这很重要。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。
mysql> select note_text,match(note_text) against('rabbit') as rank from productnotes\G;
*************************** 1. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
rank: 0
*************************** 2. row ***************************
note_text: Can shipped full, refills not available.
Need to order new can if refill needed.
rank: 0
*************************** 3. row ***************************
note_text: Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.
rank: 0
*************************** 4. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
rank: 1.5905543565750122
*************************** 5. row ***************************
note_text: Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.
rank: 0
*************************** 6. row ***************************
note_text: Matches not included, recommend purchase of matches or detonator (item DTNTR).
rank: 0
*************************** 7. row ***************************
note_text: Please note that no returns will be accepted if safe opened using explosives.
rank: 0
*************************** 8. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
rank: 0
*************************** 9. row ***************************
note_text: Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
rank: 0
*************************** 10. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
rank: 1.6408053636550903
*************************** 11. row ***************************
note_text: Shipped unassembled, requires common tools (including oversized hammer).
rank: 0
*************************** 12. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
rank: 0
*************************** 13. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
rank: 0
*************************** 14. row ***************************
note_text: Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.
rank: 0
14 rows in set (0.00 sec)
ERROR:
No query specified在SELECT而不是WHERE子句中使用Match()和Against()。这使所有行都被返回（因为没有WHERE子句），Match()和Against()用来建立一个计算列（别名为rank），此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。正如所见，不包含词rabbit的行等级为0（因此不被前一例子中的WHERE子句选择）。确实包含词rabbit的两个行每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。
2.3 使用查询扩展
mysql> select note_text from productnotes where match(note_text) against('anvils');
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//使用查询扩展
mysql> select note_text from productnotes where match(note_text) against('anvils' with query expansion);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping. |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
| Please note that no returns will be accepted if safe opened using explosives. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw. |
| Matches not included, recommend purchase of matches or detonator (item DTNTR). |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)2.4 布尔文本搜索
即使没有FULLTEXT索引也可以使用
布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
//此全文本搜索检索包含词heavy的所有行（有两行）。
//其中使用分析了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符
//因此，其结果与没有指定布尔方式的结果相同。
mysql> select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
//匹配包含heavy但不包含任意以rope开始的词的行
mysql> select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//匹配包含词rabbit和bait的行
mysql> select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//没有指定操作符，这个搜索匹配包含rabbit和bait 的至少一个词的行。
mysql> select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode);
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
//这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait.
mysql> select note_text from productnotes where match(note_text) against('"rabbit bait"' in boolean mode);
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//匹配rabbit和carrot，增加前者的等级，降低后者的等级。
mysql> select note_text from productnotes where match(note_text) against('>rabbit <bait' in boolean mode);
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode);
+-----------------------------------------------------------------------------------------------------------------------+
| note_text |
+-----------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)3. 插入数据
mysql> describe customers;
+--------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
//插入完整的行
mysql> insert into customers values(null,'aa','sxhj','hjs','a','1234','china',null,null);
Query OK, 1 row affected (0.01 sec)
//插入行的一部分
mysql> insert into customers(cust_name,cust_city,cust_state) values('bb','sxyc','b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into customers(cust_name,cust_email,cust_city,cust_country) values('cc','cc@1234.com','sxhj','china');
Query OK, 1 row affected (0.01 sec)
//插入多行
mysql> insert into customers(cust_name,cust_email,cust_city,cust_country) values('dd','dd@1234.com','sxhj','china'),('ee','ee@1234.com','sxhj','ch');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
//插入某些查询的结果
mysql> insert into customers(cust_name,cust_email,cust_city,cust_country) select cust_name,cust_email,cust_city,cust_country from customers where cust_id=10010;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
| 10006 | aa | sxhj | hjs | a | 1234 | china | NULL | NULL |
| 10007 | bb | NULL | sxyc | b | NULL | NULL | NULL | NULL |
| 10008 | cc | NULL | sxhj | NULL | NULL | china | NULL | cc@1234.com |
| 10009 | dd | NULL | sxhj | NULL | NULL | china | NULL | dd@1234.com |
| 10010 | ee | NULL | sxhj | NULL | NULL | ch | NULL | ee@1234.com |
| 10011 | ee | NULL | sxhj | NULL | NULL | ch | NULL | ee@1234.com |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
11 rows in set (0.00 sec)提高整体性能
数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MysQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。
如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW-PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：
INSERT LOW_PRIORITY INTO
这也适用于UPDATE和DELETE语句。
提高INSERT的性能
此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
4. 更新和删除数据
mysql> update customers set cust_email='eeee@1234.com' where cust_id=10011;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//注意 ","
mysql> update customers set cust_email='eeeee@1234.com',cust_name='eeeee' where cust_id=10011;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//删除某个列的值，可设置它为NULL
mysql> update customers set cust_country=null where cust_id=10011;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from customers where cust_id=10012;
Query OK, 1 row affected (0.01 sec)
mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
| 10006 | aa | sxhj | hjs | a | 1234 | china | NULL | NULL |
| 10007 | bb | NULL | sxyc | b | NULL | NULL | NULL | NULL |
| 10008 | cc | NULL | sxhj | NULL | NULL | china | NULL | cc@1234.com |
| 10009 | dd | NULL | sxhj | NULL | NULL | china | NULL | dd@1234.com |
| 10010 | ee | NULL | sxhj | NULL | NULL | ch | NULL | ee@1234.com |
| 10011 | eeeee | NULL | sxhj | NULL | NULL | NULL | NULL | eeeee@1234.com |
| 10013 | ee | NULL | sxhj | NULL | NULL | ch | NULL | ee@1234.com |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
12 rows in set (0.00 sec)在UPDATE语句中使用子查询
UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。
IGNORE关键字
如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值），为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：
UPDATE IGNORE customers..
删除表的内容而不是表
DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。
更快的删除
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快
（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。换句话说，如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。
使用强制实施引用完整性的数据库，这样MysQL将不 许删除具有与其他表相关联的数据的行。
维护引用完整性
重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。
在使用关系表时，仅在关系列中插入合法的数据非常重要。如果在products表中插入拥有非法供应商ID（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。
为防止这种情况发生，可指示MySQL只允许在products表的供应商ID列中出现合法值（即出现在vendors表中的供应商）
这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
