子查询和连接

子查询

概念：现在其他SQL语句内的SELECT子句
例如：

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

其中，SELECT * FROM t1 称为Outer Query/Outer Statement（外级查询），SELECT col2 FROM t2，称为SubQuery（子查询）

需要注意的：

1.子查询指嵌套在查询内部，且必须始终出现在圆括号内

2.子查询可以包含多个关键字或条件

3.子查询的外层查询可以是：SELECT，INSERT，UPDATE，SET或DO

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

使用比较运算符的子查询

比较运算符：
=, > , < , >=, <=, <>, !=, <=>

语法结构：

 operand comparison_operator subquery

当子查询返回值多于1个时需要用以下修饰的比较运算符：
ANY，SOME，ALL

遵循原则：

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

使用[NOT] IN 的子查询

语法结构：
operand comparison_operator[NOT] IN (subquery)
= ANY 运算符与IN等效
!=ALL或<>ALL运算符与NOT IN等效

使用[NOT] EXISTS的子查询

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

当数据表中记录的字段有重复时，为了提高查询数据表的查询速度，可以使用外键(事实外键)方法，此时需要按照分类分组的方法：GROUP BY[字段名] ，然后需要将得到的记录写入到另一张表中，理论上有两种方法，第一种是土方法，一条一条录入，除此之外还有一种简便的方法，将查询结果写入另一张表,命令为INSERT…SELECT

INSERT …SELECT：
语法：

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

写入成功后就要参照新表来更新旧表，这时就需要学会进行多表的更新

多表更新

语法:

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

举一个栗子：
想要把users0表中的goods_cate字段和users1表中的goods_cate进行连接
其中条件字段（连接条件）是：

 mysql> UPDATE users0 INNER JOIN users1 ON goods_cate=cate_name SET goods_cate = cate_id;

CREATE …SELECT(一步到位)

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

语法：

 CREATE TABLE [IF NOT EXISTS] table_name [(creata_definition,....)] select_statement

注：当两个表的连接字段名称相同时，就要使用别名(AS)

TIP：查询表的结构除了SHOW COLUMNS tbl_name之外还可以DESC tbl_name

连接

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

语法结构：

  table_reference #第一张表{[INNER|CROSS] JOIN|{LEFT|RIGHT}[OUTER] JOIN} #连接类型  table_reference  #第二张表 ON conditional_expr #连接条件

简单说就是“A表+连接类型+B表+连接条件”

数据表参照：
table_reference

tbl_name[[AS] alias] | table_subquery [AS] alias

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

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

连接类型：

INNER JOIN，内连接

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

在MySQL中，JOIN，CROSS JOIN，和INNER JOIN是等价的

LEFT [OUTER] JOIN,左外连接

显示左表中的全部和右表中符合条件的记录

数据表B的结果集依赖数据表A

数据表A的结果集根据左连接条件依赖所有数据表（B表除外）

左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）

如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

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

RIGHT [OUTER] JOIN,右外连接

与LEFT相反，不赘述

连接条件

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

通常使用ON关键字来设定连接条件
使用WHERE进行结果结果集记录的过滤

多表连接

再加一个连接条件进行连接,看栗子：

mysql> SELECT goods_id, good_name,cate_name,brand_name,good_price FROM users0 AS g INNER JOIN users1 AS c ON g.cate_id = c.cate_id INNER JOIN users2 AS b ON g.brand_id=b.brand_id\G

无限级分类表设计

先创建一个表：

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

插入一些记录后：

然后需要自身连接：同一个数据表对自身进行连接

脑补有另一张相同的表（子表），进行连接，注意要AS别名，如下栗：

SELECT s.type_id,s.type_name,*p.type_name FROM users3 AS s LEFT JOIN users3 AS p ON s.parent_id = p.type_id [GROUP BY...] [ORDER BY...];

想要显示数量时用：
*count(p.type_name) name_count

多表删除

查找重复记录：

mysql> SELECT good_name FROM users0 GROUP BY good_name HAVING count(good_name) >= 2;

语法结构：

DELETE tbl_name[.*] [,tbl_name[.*]]... FROM table_references [WHERE where_condition]

栗子：

mysql> DELETE t1 FROM users0 AS t1 LEFT JOIN (SELECT good_name FROM users0 GROUP BY good_name HAVING count(good_name) >= 2) AS t2 ON t1.good_name=t2.good_name;