MySQL简单操作(6)-子查询与连接

子查询

子查询（Subquery）是指出现在其他SQL语句内的SELECT子句

例如：

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

其中 SELECT * FROM t1 称为Outer Query/Outer Statement；
SELECT col2 FROM t2 称为Subquery

• 子查询指嵌套在查询内部，且必须始终出现在圆括号內
• 子查询可以包括多个关键字或条件，如 DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等
• 子查询的外层查询可以是： SELECT,INSERT,UPDATE,SET或DO

子查询返回值

子查询可以返回标量,一行，一列或子查询

由比较运算符引发的子查询

使用比较运算符的子查询

=, >, <, >=, <=, <>, !=, <=>

语法结构

operand comparison_operator subquery

用 ANY, SOME 或 ALL 修饰的比较运算符

operand comparison_operator ANY(subquery)

operand comparison_operator SOME(subquery)

operand comparison_operator ALL(subquery)

运算符\关键字	ANY	SOME	ALL
>, >=	最小值	最小值	最大值
<, <=	最大值	最大值	最小值
=	任意值	任意值	-
<>, !=	-	-	任意值

由[NOT] IN引发的子查询

语法结构

operand comparison_operator [NOT] IN (subquery)

• =ANY 运算符与 IN 等效
• !=ALL 或 <>ALL 运算符与 NOT IN 等效

由[NOT] EXISTS引发的子查询

如果子查询返回任何行，EXISTS 将返回TRUE；否则为FALSE

使用 INSERT…SELECT 插入记录

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...

多表更新

UPDATE table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]

CREATE…SELECT

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tabl_name 
[(create_definition, ...)]
select_statement

连接的语法

MySQL在SELECT语句，多表更新，多表删除语句中支持JOIN操作

table_reference
{[INNER | CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

数据表参照

table_reference

tal_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用tab_name AS alias_name 或tab_name alias_name赋予别名

table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

连接类型

• INNER JOIN, 內连接
  在MySQL中，JOIN， CROSS JOIN和INNER JOIN是等价的
• LEFT [OUTER] JOIN, 左外连接
• RIGHT [OUTER] JOIN, 右外连接

连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替

通用使用ON关键字来设定条件条件；
使用WHERE关键字来进行结果记录的过滤。

內连接INNER JOIN

显示左表及右表符合连接条件的记录

举例：

SELECT goods_id, goods_name, cate_name FROM tab_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

左外连接 LEFT [OUTER] JOIN

显示左表的全部记录及右表符合条件连接的记录

A LEFT JOIN B join_condition

• 数据表B的结果集依赖数据A
• 数据表A的结果集根据左连接条件依赖所有数据表(B表除外)
• 左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）
• 如果数据表A的某条记录符号WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

举例：

SELECT goods_id, goods_name, cate_name FROM tab_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

右外连接 RIGHT [OUTER] JOIN

显示右表的全部记录及左表符合条件连接的记录

举例：

SELECT goods_id, goods_name, cate_name FROM tab_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

多表连接

举例：

SELECT goods_id, goods_name, cate_name, brand_name FROM tab_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

关于连接的几点说明

外连接

如果使用內连接查找的记录在数据表中不存在，并且在WHERE子句中尝试以下操作：col_namd IS NULL时，如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录停止搜索更多的行

无限级分类表设计

举例：

CREATE TABLE tab_goods_types(
    type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(20) NOT NULL,
    parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

自身连接

同一个数据表对其自身进行连接

举例：

SELECT s.type_id, s.type_name, p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p
ON s.parent_id = p.type_id;

多表删除

DELETE tab_name[.*] [, tal_name[.*]] ...
FROM table_references
[WHERE where_condition]