25、MySQL-连接查询

1、多表连接查询

mysql>  -- 查询员工表中的员工的编号、姓名，性别，年龄，所属部门名称

mysql> SELECT e.id,e.username,e.sex,e.age,d.depName FROM employee AS e,department AS d WHERE e.depId=d.id;

+----+-----------+------+------+---------+

| id | username  | sex  | age  | depName |

+----+-----------+------+------+---------+

|  1 | 张三      | 男   |   21 | 开发部  |

|  2 | 李四      | 男   |   32 | 视频部  |

|  3 | 王五      | 女   |   26 | 教学部  |

|  4 | 赵六      | 男   |   32 | 运营部  |

|  5 | 田七      | 保密 |   19 | 开发部  |

|  6 | Mr Adword | 男   |   55 | 运营部  |

|  7 | 田七      | 保密 |   19 | 开发部  |

|  8 | 孙八      | 男   |   62 | 视频部  |

|  9 | Mr lili   | 女   |   45 | 开发部  |

+----+-----------+------+------+---------+

2、内连接查询

    表1  [INNER|CROSS] JOIN 表2 ON 连接条件;

mysql> -- 查询员工表中的员工所属部门名称

mysql> SELECT e.id,e.username,e.sex,e.age,d.depName FROM department AS d INNER JOIN employee AS e ON e.depId=d.id;

+----+-----------+------+------+---------+

| id | username  | sex  | age  | depName |

+----+-----------+------+------+---------+

|  1 | 张三      | 男   |   21 | 开发部  |

|  2 | 李四      | 男   |   32 | 视频部  |

|  3 | 王五      | 女   |   26 | 教学部  |

|  4 | 赵六      | 男   |   32 | 运营部  |

|  5 | 田七      | 保密 |   19 | 开发部  |

|  6 | Mr Adword | 男   |   55 | 运营部  |

|  7 | 田七      | 保密 |   19 | 开发部  |

|  8 | 孙八      | 男   |   62 | 视频部  |

|  9 | Mr lili   | 女   |   45 | 开发部  |

+----+-----------+------+------+---------+

9 rows in set (0.00 sec)

mysql> -- 查询员工表中的员工所属部门名称,并增加条件

mysql> SELECT e.id,e.username,e.sex,e.age,d.depName FROM department AS d INNER JOIN employee AS e ON e.depId=d.id WHERE

e.age>25;

+----+-----------+------+------+---------+

| id | username  | sex  | age  | depName |

+----+-----------+------+------+---------+

|  2 | 李四      | 男   |   32 | 视频部  |

|  3 | 王五      | 女   |   26 | 教学部  |

|  4 | 赵六      | 男   |   32 | 运营部  |

|  6 | Mr Adword | 男   |   55 | 运营部  |

|  8 | 孙八      | 男   |   62 | 视频部  |

|  9 | Mr lili   | 女   |   45 | 开发部  |

+----+-----------+------+------+---------+

3、多表内连接查询

     表1  [INNER|CROSS] JOIN 表2 ON 连接条件 JOIN 表3 ON 连接条件...

-- 为员工表添加一条地址记录，用于和地址表联系

mysql> ALTER TABLE employee ADD pId TINYINT UNSIGNED DEFAULT 1;

-- 更新员工表的地址id

mysql> UPDATE employee SET pId =(SELECT id FROM provinces WHERE provinces.pName=employee.addr);

-- 删除原先的addr字段

mysql> ALTER TABLE employee DROP addr;

mysql> SELECT * FROM employee;

+----+-----------+------+---------+------+-------+------+

| id | username  | age  | salary  | sex  | depId | pId  |

+----+-----------+------+---------+------+-------+------+

|  1 | 张三      |   21 | 5432.12 | 男   |     1 |    1 |

|  2 | 李四      |   32 | 6432.00 | 男   |     2 |    2 |

|  3 | 王五      |   26 | 5932.92 | 女   |     3 |    3 |

|  4 | 赵六      |   32 | 6232.14 | 男   |     4 |    4 |

|  5 | 田七      |   19 | 4932.92 | 保密 |     1 |    3 |

|  6 | Mr Adword |   55 | 9432.99 | 男   |     4 |    5 |

|  7 | 田七      |   19 | 4932.92 | 保密 |     1 |    3 |

|  8 | 孙八      |   62 | 9932.14 | 男   |     2 |    4 |

|  9 | Mr lili   |   45 | 9132.99 | 女   |     1 |    5 |

+----+-----------+------+---------+------+-------+------+

--三张表的内链接查询

mysql> -- 查询用户所属部门名称、省份名称、用户的信息

mysql> SELECT e.id,e.username,p.pName,d.depName FROM employee AS e JOIN provinces AS p ON p.id=e.pId JOIN department AS

d ON e.depId=d.id;

+----+-----------+-------+---------+

| id | username  | pName | depName |

+----+-----------+-------+---------+

|  1 | 张三      | 山东  | 开发部  |

|  2 | 李四      | 河北  | 视频部  |

|  3 | 王五      | 北京  | 教学部  |

|  4 | 赵六      | 上海  | 运营部  |

|  5 | 田七      | 北京  | 开发部  |

|  6 | Mr Adword | 美国  | 运营部  |

|  7 | 田七      | 北京  | 开发部  |

|  8 | 孙八      | 上海  | 视频部  |

|  9 | Mr lili   | 美国  | 开发部  |

+----+-----------+-------+---------+

9 rows in set (0.11 sec)