不支持 with 子句中串联式查询名的形式_MySQL最全操作手册 中（建议收藏）

十四、使用子查询(IN)

14.1 利用子查询进行过滤

查询语句 A：

SELECT FROM WHERE ;

查询语句 B：

SELECT FROM WHERE  IN ();

查询语句 A、B 组合：

SELECT FROM WHERE  IN (    SELECT     FROM     WHERE );

注意：

•在 WHERE 子句中使用子查询，应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列。

14.1.1 应用实例

问题：查询订购物品 TNT2 的所有客户信息。

customers 顾客表(部分)：

cust_id(唯一顾客id)	cust_name(顾客名)	cust_contact(顾客联系名)
1001	Coyote Inc.	Y Lee
1002	Mouse House	Jerry Mouse
1003	Wascals	Jim Jones
1004	Yosemite Place	Y Sam
1005	E Fudd	E Fudd

orders 顾客订单表(部分)：

order_num(唯一订单号)	cust_id(顾客id)
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

orderitems 订单产品表(部分)：

order_num(订单号)	prod_id(产品id)
20005	ANV01
20005	ANV02
20005	TNT2
20006	JP2000
20007	TNT2
20008	FC
20009	ANV03

输入：

SELECT cust_name, cust_contact -- 检索前一步骤返回的所有客户ID的客户信息FROM customersWHERE cust_id IN (    SELECT cust_id -- 检索具有前一步骤列出的订单编号的所有客户的ID    FROM orders    WHERE order_num IN (        SELECT order_num -- 检索包含物品 TNT2 的所有订单的编号        FROM orderitems        WHERE prod_id = 'TNT2'    ));

输出：

cust_name(顾客名)	cust_contact(顾客联系名)
Coyote Inc.	Y Lee
Yosemite Place	Y Sam

14.2 作为计算字段使用子查询

SELECT , (    SELECT     FROM     WHERE .  .) AS FROM ;

注意：

•任何时候只要列名可能有多义性，就必须使用相关子查询语法(表名和列名由一个句点分隔)。

14.2.1 应用实例

问题：查询 customers 表中每个客户的订单总数。

customers 顾客表(部分)：

cust_id(唯一顾客id)	cust_name(顾客名)	cust_state(顾客国籍)
1001	Coyote Inc.	MI
1002	Mouse House	OH
1003	Wascals	IN
1004	Yosemite Place	AZ
1005	E Fudd	IL

orders 顾客订单表(部分)：

order_num(唯一订单号)	cust_id(顾客id)
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

输入：

SELECT cust_name, cust_state, (    SELECT COUNT(*)    FROM orders    WHERE orders.cust_id = customers.cust_id) AS orders -- orders 是一个由圆括号中的子查询建立的计算字段，该子查询对检索出的每个客户执行一次FROM customersORDER BY cust_name;

输出：

cust_name(顾客名)	cust_state(顾客国籍)	orders(顾客订单总数)
Coyote Inc.	MI	2
E Fudd	IL	1
Mouse House	OH	0
Wascals	IN	1
Yosemite Place	AZ	1

十五、联结表(INNER JOIN)

15.1 等值联结

SELECT ., .FROM , WHERE . = .;

注意：

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

15.2 笛卡儿积

概念：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT ., .FROM , ;

15.3 内部联结(等值联结的不同语法实现)

SELECT ., .FROM  INNER JOIN     ON . = .;

15.4 联结多个表

SELECT ., ., .FROM , , WHERE . = .    AND . = .;

注意：

•不要联结不必要的表。联结的表越多，性能下降越厉害。

15.4.1 应用实例

问题：查询订购物品 TNT2 的所有客户信息。

customers 顾客表(部分)：

cust_id(唯一顾客id)	cust_name(顾客名)	cust_contact(顾客联系名)
1001	Coyote Inc.	Y Lee
1002	Mouse House	Jerry Mouse
1003	Wascals	Jim Jones
1004	Yosemite Place	Y Sam
1005	E Fudd	E Fudd

orders 顾客订单表(部分)：

order_num(唯一订单号)	cust_id(顾客id)
20005	10001
20006	10003
20007	10004
20008	10005
20009	10001

orderitems 订单产品表(部分)：

order_num(订单号)	prod_id(产品id)
20005	ANV01
20005	ANV02
20005	TNT2
20006	JP2000
20007	TNT2
20008	FC
20009	ANV03

输入：

SELECT cust_name, cust_contactFROM customers, orders, orderitemsWHERE customers.cust_id = orders.cust_id -- 根据顾客id联结顾客表和订单表    AND orderitems.order_num = orders.order_num -- 根据订单编号联结订单产品表和订单表    AND prod_id = 'TNT2'; -- 过滤产品 TNT2 的数据

输出：

cust_name(顾客名)	cust_contact(顾客联系名)
Coyote Inc.	Y Lee
Yosemite Place	Y Sam

十六、创建高级联结(LEFT/RIGHT OUTER JOIN)

16.1 使用表别名

SELECT ., .FROM  AS ,  AS WHERE . = .;

16.2 自联结

SELECT ., .FROM  AS ,  AS WHERE . = .;

16.2.1 应用实例

问题：假如某产品(其 ID 为 DTNTR)存在问题，因此想知道生产该产品的供应商生产的其他产品是否也存在这些问题。

products 产品表(部分)：

prod_id(唯一产品id)	vend_id(供应商id)	prod_name(产品名)
ANV01	1001	.5 ton anvil
ANV02	1001	1 ton anvil
ANV03	1001	2 ton anvil
DTNTR	1003	Detonator
FB	1003	Bird seed
FC	1003	Carrots
FU1	1002	Fuses
JP1000	1005	JetPack 1000
JP2000	1005	JetPack 2000
OL1	1002	Oil can
SAFE	1003	Safe
SLING	1003	Sling
TNT1	1003	TNT (1 stick)
TNT2	1003	TNT (5 sticks)

输入：

SELECT p1.prod_id, p1.prod_nameFROM products AS p1, products AS p2WHERE p1.vend_id = p2.vend_id -- 根据供应商id进行自联结    AND p2.prod_id = 'DTNTR'; -- 过滤产品 DTNTR 的数据

输出：

prod_id(唯一产品id)	prod_name(产品名)
DTNTR	Detonator
FB	Bird seed
FC	Carrots
SAFE	Safe
SLING	Sling
TNT1	TNT (1 stick)
TNT2	TNT (5 sticks)

16.3 自然联结

•自然联结排除多次出现，使每个列只返回一次。•迄今为止我们建立的每个内部联结都是自然联结。

16.4 外部联结

16.4.1 左外联结

SELECT ., .FROM  LEFT OUTER JOIN     ON . = .;

16.4.2 右外联结

SELECT ., .FROM  RIGHT OUTER JOIN     ON . = .;

注意：

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

十七、组合查询(UNION)

17.1 不包含重复行

SELECT , FROM WHERE UNION SELECT , FROM WHERE 

17.2 包含重复行

SELECT , FROM WHERE UNION ALLSELECT , FROM WHERE 

UNION 规则：

•UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。•UNION 中的每个查询必须包含相同的列、表达式或聚集函数。•列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含地转换的类型。•在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。

十八、全文本搜索(Match、Against)

并非所有的引擎都支持全文本搜索。两个最常使用的引擎为 MyISAM 和 InnoDB，前者支持全文本搜索，而后者不支持。

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能是你真正想要的行)。

不包含搜索文本的行等级为 0。包含搜索文本的行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。

如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。

18.1 启用全文本搜索支持

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。

一般在创建表时启用全文本搜索。CREATE TABLE 语句接受 FULLTEXT 子句，它给出被索引列的一个逗号分隔的列表。

更新索引要花时间，如果正在导入数据到一个新表，此时不应该启用 FULLTEXT 索引。应该首先导入所有数据，然后再修改表，定义 FULLTEXT。

18.2 进行全文本搜索

SELECT FROM WHERE Match() Against('');

注意：

•传递给 Match() 的值必须与 FULLTEXT() 定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。•除非使用 BINARY 方式，否则全文本搜索不区分大小写。

18.3 使用查询扩展

在使用查询扩展时，MySQL 对数据和索引进行两遍扫描来完成搜索：

•首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有 行。•其次，MySQL 检查这些匹配行并选择所有有用的词。•再其次，MySQL 再次进行全文本搜索，这次不仅使用原来的条件， 而且还使用所有有用的词。

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

18.4 布尔文本搜索

布尔方式不同于迄今为止使用的全文本搜索语法的地方在于， 即使没有定义 FULLTEXT 索引，也可以使用它。

SELECT FROM WHERE Match() Against('' IN BOOLEAN MODE);

18.4.1 全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语(与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语)

18.4.2 应用实例

productnotes 产品注释表(部分)：

note_id(唯一注释id)	node_text(注释文本)
101	Customer complaint:\r\nSticks not individually wrapped, too easy to mistakenly detonate all at once.\r\nRecommend individual wrapping.
102	Can shipped full, refills not available.\r\nNeed to order new can if refill needed.
103	Safe is combination locked, combination not provided with safe.\r\nThis is rarely a problem as safes are typically blown up or dropped by customers.
104	Quantity varies, sold by the sack load.\r\nAll guaranteed to be bright and orange, and suitable for use as rabbit bait.
105	Included fuses are short and have been known to detonate too quickly for some customers.\r\nLonger fuses are available (item FU1) and should be recommended.
106	Matches not included, recommend purchase of matches or detonator (item DTNTR).
107	Please note that no returns will be accepted if safe opened using explosives.
108	Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
109	Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
110	Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
111	Shipped unassembled, requires common tools (including oversized hammer).
112	Customer complaint:\r\nCircular hole in safe floor can apparently be easily cut with handsaw.
113	Customer complaint:\r\nNot heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
114	Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.\r\nComment forwarded to vendor.

18.4.2.1 实例一

输入：

SELECT note_textFROM productnotesWHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

分析：这个搜索匹配包含词 rabbit 和 bait 的行。

18.4.2.2 实例二

输入：

SELECT note_textFROM productnotesWHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

分析：没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。

18.4.2.3 实例三

输入：

SELECT note_textFROM productnotesWHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

分析：这个搜索匹配短语 rabbit bait 而不是匹配两个词 rabbit 和 bait。

18.4.2.4 实例四

输入：

SELECT note_textFROM productnotesWHERE Match(note_text) Against('>rabbit 

分析：匹配 rabbit 和 carrot，增加前者的等级，降低后者的等级。

18.4.2.5 实例五

输入：

SELECT note_textFROM productnotesWHERE Match(note_text) Against('+safe +(

分析：这个搜索匹配词 safe 和 combination，降低后者的等级。

18.5 全文本搜索的使用说明

•在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有 3 个或 3 个以下字符的词(如果需要，这个数目可以更改)。•MySQL 带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。•许多词出现的频率很高，搜索它们没有用处(返回太多的结果)。因此，MySQL 规定了一条 50% 规则，如果一个词出现在 50% 以上的行中，则将它作为一个非用词忽略。50% 规则不用于 IN BOOLEAN MODE。•如果表中的行数少于 3 行，则全文本搜索不返回结果(因为每个词或者不出现，或者至少出现在 50% 的行中)。•忽略词中的单引号。例如，don't 索引为 dont。•不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。•如前所述，仅在 MyISAM 数据库引擎中支持全文本搜索。

十九、插入数据(INSERT INTO)

19.1 插入完整的行

INSERT INTO (    ,    ,    ...) VALUES (    '',    '',    ...);

注意：

•

如果提供列名，则必须对每个列出的列给出一个值。

•

如果不提供列名，则必须给每个表列提供一个值。

•

如果表的某些列满足以下某个条件，则可以在 INSERT 操作中省略。

1.该列定义为允许 NULL 值(无值或空值)。

2.在表定义中给出默认值。这表示如果不给出值，将使用默认值。

•

可以通过在 INSERT 和 INTO 之间添加关键字 LOW_PRIORITY，指示 MySQL 降低 INSERT 语句的优先级。这也适用于 UPDATE 和 DELETE 语句。

19.2 插入多个行

INSERT INTO (    ,    ,    ...) VALUES (    '',    '',    ...), (    '',    '',    ...);

19.3 插入检索出的数据

INSERT INTO (    ,    ,    ,    ...)SELECT     ,    ,    ,    ...FROM ;

注意：MySQL 在此使用的是列的位置，SELECT 中的第一列(不管其列名)将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。

二十、更新和删除数据(UPDATE、DELETE)

20.1 更新数据

UPDATE SET  = '',     = '',    ...WHERE ;

注意：

•不要省略 WHERE 子句，因为稍不注意，就会更新表中所有行。•UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。•可使用 IGNORE 关键字避免更新操作被异常取消。•为了删除某个列的值，可设置它为 NULL(假如表定义允许 NULL 值)。

20.2 删除数据

DELETE FROM WHERE ;

注意：

•不要省略 WHERE 子句，因为稍不注意，就会错误地删除表中所有行。•如果想从表中删除所有行，不要使用 DELETE。可使用 TRUNCATE TABLE 语句，它完成相同的工作，但速度更快(TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。

20.3 更新和删除的指导原则

•除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。•保证每个表都有主键，尽可能像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。•在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。•使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

二十一、创建和操纵表(CREATE、ALTER、DROP、RENAME)

21.1 创建表

DROP TABLE IF EXISTS ;CREATE TABLE  (      ,      ,    ...    PRIMARY KEY (, , ...)) ENGINE =  CHARACTER SET = ;

21.1.1 使用NULL值

•允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受该列没有值的行。•不要把 NULL 值与空串相混淆，空串是一个有效的值，NULL 值用关键字 NULL 而不是空串指定。•主键中只能使用不允许 NULL 值的列。

21.1.2 主键

•表中的每个行必须具有唯一的主键值。•如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

21.1.3 使用 AUTO_INCREMENT

CREATE TABLE  (     ,  AUTO_INCREMENT,    ...) AUTO_INCREMENT =  ...;

•列尾追加 AUTO_INCREMENT 表示本列每当增加一行时自动增量。•每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引。•可使用 last_insert_id() 函数获得指定表的最后一个 AUTO_INCREMENT 值。

21.1.4 使用默认值

CREATE TABLE  (     ,  DEFAULT ,    ...) ...;

注意：

•MySQL 不允许使用函数作为默认值，它只支持常量。

21.1.5 引擎类型

引擎 | 描述 |:-|:-| InnoDB | 一个可靠的事务处理引擎，它不支持全文本搜索。MEMORY | 功能等同于 MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表)。MyISAM | 一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

注意：

•一个数据库中的不同表可以使用不同的引擎类型，但使用一个引擎的表不能引用具有使用不同引擎的表的外键。

21.2 更新表

21.2.1 增加列

ALTER TABLE ADD  ;

21.2.2 删除列

ALTER TABLE DROP COLUMN ;

21.2.3 增加外键约束

ALTER TABLE ADD CONSTRAINT FOREIGN KEY ()REFERENCES  ();

21.2.3 删除表

DROP TABLE ;

21.2.4 重命名表

RENAME TABLE  TO ;

写在最后：

关注微信公众号：CodeFish 回复 “电子书” 即可获取全网最全、最高清的电子书资源！