mysql的多表操作

多表操作分为多表查询、多表更新、多表删除操作，通常使用到连接语句。

连接分为：

    1. 内连接： INNER JOIN ... ON

    2.  左连接：LEFT JOIN ... ON

    3.  右连接：RIGHT JOIN ... ON

1.多表查询：

现有两张表，employee表,department表：

查询所有员工及其部门信息

内连接查询：（以两个表为基准，将两张表相对应的部分查询出来）

SELECT e.id,e.lastname,d.dept_name FROM employee AS e INNER JOIN department AS d
    -> ON e.dept_id=d.id
    -> ORDER BY e.id;

左连接查询：

（查询以employee为准，employee表的所有信息查询出来，与之相关的department表的信息才查出来，例如没有员工是后勤部的，则后勤部没有显示）

SELECT e.id,e.lastname,d.dept_name FROM employee AS e LEFT JOIN department AS d
    -> ON e.dept_id=d.id
    -> ORDER BY e.id;

右连接查询：

（查询以department为准，department表的所有信息查询出来，与之相关的employee表的信息才查出来，例如"II"员工没有给部门，则"II"员工没有显示）

SELECT e.id,e.lastname,d.dept_name FROM employee AS e RIGHT JOIN department AS d
    -> ON e.dept_id=d.id
    -> ORDER BY e.id;

2.多表更新：

现有两张表：goods表,goods_brand表

将goods表中good_brand改为goods_brand表中id:

UPDATE goods AS g INNER JOIN goods_brand AS b
    -> ON g.good_brand=b.brand_name
    -> SET g.good_brand=b.id;

再将good_brand属性修改：

ALTER TABLE goods CHANGE good_brand brand_id INT UNSIGNED;

3.多表删除：

现如今goods表中有相同的数据：

删除重复记录：

DELETE t1 FROM goods AS t1 LEFT JOIN (SELECT id,good_name FROM goods GROUP BY good_name HAVING count(good_name)>1) AS t2 ON t1.good_name=t2.good_name WHERE t1.id>t2.id;