MySQL子查询与连接

一、修改默认数据及数据表默认编码（utf8为例子）

SHOW CREATE DATABASE db_name;//查看数据库编码格式
ALTER DATABASE db_name CHARACTER SET = utf8;//修改数据库编码格式
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;//修改表的编码格式
SET NAMES utf8;//设置utf8编码格式

二、子查询

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

例如

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。

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

1.使用比较运算符的子查询：=,>,<,>=,<=,<>,!=,<=>

语法结构：operand comparison_operator subquery

eg：SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(avg(goods_price)) FROM tdb_goods);

使用子查询返回多个结果的时候：利用ANY、SOME或ALL修饰获取一个结果：

operand comparison_operator {ANY|SOME|ALL} (subquery)

2.使用[NOT] IN 的子查询

语法结构：OPERAND comparison_operator [NOT] IN (subquery)

=any 运算符与IN等效。!=ALL或<>ALL运算符与NOT IN等效。

3.将查询结果写入数据表（子查询）

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

eg:INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP  BY goods_cate;

4.多表更新

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

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

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

eg:UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name
    -> SET goods_cate=cate_id;

如果更新表时两个表的列定义有名称相等的，可以利用表明或者取别名区分，例如

UPDATE tdb_goods AS a INNER JOIN tdb_goods_brands AS b ON a.brand_name=b.brand_name
    -> SET a.brand_name=brand_id;

三、连接

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

语法结构：table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

数据表可以使用tbl_name AS alias_name 或tbl_name alias_name 赋予别名。table_subquery可以作为子查询使用在FROM字句中，这样的子查询必须为其赋予别名

1.连接类型

INNER JOIN：内连接，在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。显示左表及右表符合连接条件的记录；

LEFT [OUTER] JOIN：左外连接；显示左表的全部及右表符合连接条件的记录

RIGHT [OUTER] JOIN ：右外连接;显示右表的全部记录及左表符合连接条件的记录

2.连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替。通常使用ON关键字来设定条件，使用WHERE关键字进行结果集记录的过滤。

3.多表连接

eg：SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods
    -> INNER JOIN tdb_goods_cates AS a on tdb_goods.cate_id=a.cate_id
    -> INNER JOIN tdb_goods_brands AS b on tdb_goods.brand_id=b.brand_id;

4.左外连接说明

A LEFT JOIN B join_condition
• 数据表B的结果集依赖数据表A。
• 数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。
• 左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）
• 如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。
• 如果使用内连接查找的记录在连接数据表中部存在，并且在WHERE子句中尝试一下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行。

5.无限分类的数据表设计

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

6.多表删除

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

先利用形如：SELECT goods_id goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2;找到重复的列，

然后利用表自身与找出的重复进行连接，将重复的删除，只保留自己想要的那一个；

例如：DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 ON t1.goods_nname=t2.goods_name WHERE t1.goods_id>t2.goods_id;