MySQL之连接查询，多表查询（适合练习和初学）

Join连接图

针对以上的多表查询，我们尝试做以下的实验：

Join实验：

CREATE TABLE `t_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL,  `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL,empno int  not null, PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;   INSERT INTO t_dept(deptName,address) VALUES('华山','华山');INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶'); INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺'); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);  INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);   INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);ALTER TABLE `t_dept` add  CEO  INT(11)  ; # CEO=2 值，都应该是t_emp 中id的值。update t_dept set CEO=2 where id=1;update t_dept set CEO=4 where id=2;update t_dept set CEO=6 where id=3;update t_dept set CEO=8 where id=4;update t_dept set CEO=9 where id=5; 

下面来看几个问题：

1   所有有门派的人员信息 

mysql> select * from t_dept d left join t_emp e on d.id = e.deptId; ---- ---------- ----------- ------ ------ -------------- ------ -------- -------- | id | deptName | address   | CEO  | id   | name         | age  | deptId | empno  | ---- ---------- ----------- ------ ------ -------------- ------ -------- -------- |  1 | 华山     | 华山      |    2 |    1 | 风清扬       |   90 |      1 | 100001 ||  1 | 华山     | 华山      |    2 |    2 | 岳不群       |   50 |      1 | 100002 ||  1 | 华山     | 华山      |    2 |    3 | 令狐冲       |   24 |      1 | 100003 ||  2 | 丐帮     | 洛阳      |    4 |    4 | 洪七公       |   70 |      2 | 100004 ||  2 | 丐帮     | 洛阳      |    4 |    5 | 乔峰         |   35 |      2 | 100005 ||  3 | 峨眉     | 峨眉山    |    6 |    6 | 灭绝师太     |   70 |      3 | 100006 ||  3 | 峨眉     | 峨眉山    |    6 |    7 | 周芷若       |   20 |      3 | 100007 ||  4 | 武当     | 武当山    |    8 |    8 | 张三丰       |  100 |      4 | 100008 ||  5 | 明教     | 光明顶    |    9 |    9 | 张无忌       |   25 |      5 | 100009 ||  6 | 少林     | 少林寺    | NULL | NULL | NULL         | NULL |   NULL |   NULL | ---- ---------- ----------- ------ ------ -------------- ------ -------- -------- 10 rows in set (0.04 sec)

2   列出所有用户，并显示其机构信息 

mysql> select * from t_emp e left join t_dept d on d.id = e.deptId; ---- -------------- ------ -------- -------- ------ ---------- ----------- ------ | id | name         | age  | deptId | empno  | id   | deptName | address   | CEO  | ---- -------------- ------ -------- -------- ------ ---------- ----------- ------ |  1 | 风清扬       |   90 |      1 | 100001 |    1 | 华山     | 华山      |    2 ||  2 | 岳不群       |   50 |      1 | 100002 |    1 | 华山     | 华山      |    2 ||  3 | 令狐冲       |   24 |      1 | 100003 |    1 | 华山     | 华山      |    2 ||  4 | 洪七公       |   70 |      2 | 100004 |    2 | 丐帮     | 洛阳      |    4 ||  5 | 乔峰         |   35 |      2 | 100005 |    2 | 丐帮     | 洛阳      |    4 ||  6 | 灭绝师太     |   70 |      3 | 100006 |    3 | 峨眉     | 峨眉山    |    6 ||  7 | 周芷若       |   20 |      3 | 100007 |    3 | 峨眉     | 峨眉山    |    6 ||  8 | 张三丰       |  100 |      4 | 100008 |    4 | 武当     | 武当山    |    8 ||  9 | 张无忌       |   25 |      5 | 100009 |    5 | 明教     | 光明顶    |    9 || 10 | 韦小宝       |   18 |   NULL | 100010 | NULL | NULL     | NULL      | NULL | ---- -------------- ------ -------- -------- ------ ---------- ----------- ------ 10 rows in set (0.00 sec)

3   列出不入派的人员：

mysql> select * from t_emp where deptId is null; ---- ----------- ------ -------- -------- | id | name      | age  | deptId | empno  | ---- ----------- ------ -------- -------- | 10 | 韦小宝    |   18 |   NULL | 100010 | ---- ----------- ------ -------- -------- 1 row in set (0.01 sec)mysql> select * from t_emp e left join t_dept d on e.deptId = d.id where e.deptId is null; ---- ----------- ------ -------- -------- ------ ---------- --------- ------ | id | name      | age  | deptId | empno  | id   | deptName | address | CEO  | ---- ----------- ------ -------- -------- ------ ---------- --------- ------ | 10 | 韦小宝    |   18 |   NULL | 100010 | NULL | NULL     | NULL    | NULL | ---- ----------- ------ -------- -------- ------ ---------- --------- ------ 1 row in set (0.00 sec)

4  所有没人入的门派 ：

mysql> select * from t_dept d left join t_emp e on d.id = e.deptId where e.id is null; ---- ---------- ----------- ------ ------ ------ ------ -------- ------- | id | deptName | address   | CEO  | id   | name | age  | deptId | empno | ---- ---------- ----------- ------ ------ ------ ------ -------- ------- |  6 | 少林     | 少林寺    | NULL | NULL | NULL | NULL |   NULL |  NULL | ---- ---------- ----------- ------ ------ ------ ------ -------- ------- 1 row in set (0.00 sec)

 5 列出所有人员和门派的对照关系

mysql> select * from t_emp e left join t_dept d on e.deptId = d.id    -> union     -> select * from t_emp e right join t_dept d on e.deptId = d.id; ------ -------------- ------ -------- -------- ------ ---------- ----------- ------ | id   | name         | age  | deptId | empno  | id   | deptName | address   | CEO  | ------ -------------- ------ -------- -------- ------ ---------- ----------- ------ |    1 | 风清扬       |   90 |      1 | 100001 |    1 | 华山     | 华山      |    2 ||    2 | 岳不群       |   50 |      1 | 100002 |    1 | 华山     | 华山      |    2 ||    3 | 令狐冲       |   24 |      1 | 100003 |    1 | 华山     | 华山      |    2 ||    4 | 洪七公       |   70 |      2 | 100004 |    2 | 丐帮     | 洛阳      |    4 ||    5 | 乔峰         |   35 |      2 | 100005 |    2 | 丐帮     | 洛阳      |    4 ||    6 | 灭绝师太     |   70 |      3 | 100006 |    3 | 峨眉     | 峨眉山    |    6 ||    7 | 周芷若       |   20 |      3 | 100007 |    3 | 峨眉     | 峨眉山    |    6 ||    8 | 张三丰       |  100 |      4 | 100008 |    4 | 武当     | 武当山    |    8 ||    9 | 张无忌       |   25 |      5 | 100009 |    5 | 明教     | 光明顶    |    9 ||   10 | 韦小宝       |   18 |   NULL | 100010 | NULL | NULL     | NULL      | NULL || NULL | NULL         | NULL |   NULL |   NULL |    6 | 少林     | 少林寺    | NULL | ------ -------------- ------ -------- -------- ------ ---------- ----------- ------ 11 rows in set (0.00 sec)

注意：union可以去掉重复的数据

6 列出所有没入派的人员和没人入的门派

mysql> select * from t_emp e left join t_dept d on e.deptId = d.id where e.deptId is null    -> union    -> select * from t_emp e right join t_dept d on e.deptId = d.id where e.id is null; ------ ----------- ------ -------- -------- ------ ---------- ----------- ------ | id   | name      | age  | deptId | empno  | id   | deptName | address   | CEO  | ------ ----------- ------ -------- -------- ------ ---------- ----------- ------ |   10 | 韦小宝    |   18 |   NULL | 100010 | NULL | NULL     | NULL      | NULL || NULL | NULL      | NULL |   NULL |   NULL |    6 | 少林     | 少林寺    | NULL | ------ ----------- ------ -------- -------- ------ ---------- ----------- ------ 2 rows in set (0.00 sec)

7，求各个门派对应的掌门人名称:

mysql> select * from t_dept d left join t_emp e on d.ceo = e.id; ---- ---------- ----------- ------ ------ -------------- ------ -------- -------- | id | deptName | address   | CEO  | id   | name         | age  | deptId | empno  | ---- ---------- ----------- ------ ------ -------------- ------ -------- -------- |  1 | 华山     | 华山      |    2 |    2 | 岳不群       |   50 |      1 | 100002 ||  2 | 丐帮     | 洛阳      |    4 |    4 | 洪七公       |   70 |      2 | 100004 ||  3 | 峨眉     | 峨眉山    |    6 |    6 | 灭绝师太     |   70 |      3 | 100006 ||  4 | 武当     | 武当山    |    8 |    8 | 张三丰       |  100 |      4 | 100008 ||  5 | 明教     | 光明顶    |    9 |    9 | 张无忌       |   25 |      5 | 100009 ||  6 | 少林     | 少林寺    | NULL | NULL | NULL         | NULL |   NULL |   NULL | ---- ---------- ----------- ------ ------ -------------- ------ -------- -------- 6 rows in set (0.00 sec)mysql> select * from t_dept d inner join t_emp e on d.ceo = e.id; ---- ---------- ----------- ------ ---- -------------- ------ -------- -------- | id | deptName | address   | CEO  | id | name         | age  | deptId | empno  | ---- ---------- ----------- ------ ---- -------------- ------ -------- -------- |  1 | 华山     | 华山      |    2 |  2 | 岳不群       |   50 |      1 | 100002 ||  2 | 丐帮     | 洛阳      |    4 |  4 | 洪七公       |   70 |      2 | 100004 ||  3 | 峨眉     | 峨眉山    |    6 |  6 | 灭绝师太     |   70 |      3 | 100006 ||  4 | 武当     | 武当山    |    8 |  8 | 张三丰       |  100 |      4 | 100008 ||  5 | 明教     | 光明顶    |    9 |  9 | 张无忌       |   25 |      5 | 100009 | ---- ---------- ----------- ------ ---- -------------- ------ -------- -------- 5 rows in set (0.01 sec)

8，求所有当上掌门人的平均年龄:

mysql> select avg(age) from t_dept d inner join t_emp e on d.ceo = e.id; ---------- | avg(age) | ---------- |  63.0000 | ---------- 1 row in set (0.01 sec)mysql> select avg(age) from t_dept d inner join t_emp e on d.ceo = e.id; ---------- | avg(age) | ---------- |  63.0000 | ---------- 1 row in set (0.00 sec)

聚合函数能够自动去重。

 
9，求所有人物对应的掌门名称:

mysql> select e1.id,e1.name,d.deptName,d.address,e2.name as DeptName from t_emp e1 left join t_dept d on e1.deptId = d.id left join t_emp e2 on d.ceo = e2.id; ---- -------------- ---------- ----------- -------------- | id | name         | deptName | address   | DeptName     | ---- -------------- ---------- ----------- -------------- |  1 | 风清扬       | 华山     | 华山      | 岳不群       ||  2 | 岳不群       | 华山     | 华山      | 岳不群       ||  3 | 令狐冲       | 华山     | 华山      | 岳不群       ||  4 | 洪七公       | 丐帮     | 洛阳      | 洪七公       ||  5 | 乔峰         | 丐帮     | 洛阳      | 洪七公       ||  6 | 灭绝师太     | 峨眉     | 峨眉山    | 灭绝师太     ||  7 | 周芷若       | 峨眉     | 峨眉山    | 灭绝师太     ||  8 | 张三丰       | 武当     | 武当山    | 张三丰       ||  9 | 张无忌       | 明教     | 光明顶    | 张无忌       || 10 | 韦小宝       | NULL     | NULL      | NULL         | ---- -------------- ---------- ----------- -------------- 10 rows in set (0.00 sec)

 

来源：http://www.icode9.com/content-2-62801.html