MySQL进阶（三）——多表操作

连接(参照别的表来更新本表)

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

      语法结构

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

     连接类型：INNER JOIN,内连接

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

           LEFT [OUTER] JOIN,左外连接

           RIGHT [OUTER] JOIN,左外连接

     例子：

           UPDATE goods INNER JOIN goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

     数据表参照

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

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

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

     连接条件

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

           

     内连接：显示连接的两个表符合连接条件的记录。

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

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

     左外连接

            A LEFT JOIN B join_condition

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

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

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

          如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，该条记录的关于B表的相关字段为空。

   例子：

   SELECT goods_id,goods_name,cate_name FROM goods LEFT JOIN goods_cates ON goods.cate_id=goods_cates.cate_id;

   

   右外连接

   

   例子：

   SELECT goods_id,goods_name,cate_name FROM goods RIGHT  JOIN goods_cates ON goods.cate_id=goods_cates.cate_id;

多表更新

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

   例子：

      UPDATE goods AS a INNER JOIN goods_brands AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;

多表连接

   例子：

      SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM goods AS g INNER JOIN goods_cates AS c ON g.cate_id = c.cate_id INNER JOIN goods_brands AS b ON g.brand_id=b.brand.id;

CREATE...SELECT

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

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

   例子：

      CREATE TABLE goods_brands(

        brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

        brand_name VARCHAR(40) NOT NULL

      )SELECT brand_name FROM goods GROUP BY brand_name;

 

自身连接（应用于无限极分类）

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

   例子：

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

 

多表删除

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

   例子：

      DELETE t1 FROM goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name)>=2) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;