mysql联表多条去重_MySQL高级

本文大纲

环境

• win10-64

• MySQL Community Server 5.7.1

  mysqld –version可查看版本

• 官方文档

SQL执行顺序

手写顺序

我们可以将手写SQL时遵循的格式归结如下：

select distinct
    from  join  on wheregroup byhavingorder bylimit <offset>,<rows>

• distinct，用于对查询出的结果集去重(若查出各列值相同的多条结果则只算一条)

• join，关联表查询，若将两个表看成两个集合，则能有7种不同的查询效果(将在下节介绍)。

• group by，通常与合计函数结合使用，将结果集按一个或多个列值分组后再合计

• having，通常与合计函数结合使用，弥补where条件中无法使用函数

• order by，按某个标准排序，结合asc/desc实现升序降序

• limit，如果跟一个整数n则表示返回前n条结果；如果跟两个整数m,n则表示返回第m条结果之后的n条结果(不包括第m条结果)

MySQL引擎解析顺序

而我们将SQL语句发给MySQL服务时，其解析执行的顺序一般是下面这样：

from
    
on
    
 join
    
where
    
group by
    
having
    
select
    
order by
    
limit
    offset,rows

了解这个对于后续分析SQL执行计划提供依据。

七种Join方式

下面我们创建部门表tbl_dept和员工表tbl_emp对上述7种方式进行逐一实现：

• 部门表：主键id、部门名称deptName，部门楼层locAdd

mysql> CREATE TABLE `tbl_dept` (
    ->  `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->  `deptName` VARCHAR(30) DEFAULT NULL,
    ->  `locAdd` VARCHAR(40) DEFAULT NULL,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• 员工表：主键id，姓名name、所属部门deptId

mysql> CREATE TABLE `tbl_emp` (
    ->  `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->  `name` VARCHAR(20) DEFAULT NULL,
    ->  `deptId` INT(11) DEFAULT NULL,
    ->  PRIMARY KEY (`id`),
    ->  KEY `fk_dept_id` (`deptId`)
    ->  #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
    -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入一些测试数据：

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('技术部',11);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('美工部',12);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('总裁办',13);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('人力资源',14);
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('后勤组',15);
Query OK, 1 row affected (0.10 sec)

mysql> insert into tbl_emp(name,deptId) values('jack',1);
Query OK, 1 row affected (0.11 sec)

mysql> insert into tbl_emp(name,deptId) values('tom',1);
Query OK, 1 row affected (0.08 sec)

mysql> insert into tbl_emp(name,deptId) values('alice',2);
Query OK, 1 row affected (0.08 sec)

mysql> insert into tbl_emp(name,deptId) values('john',3);
Query OK, 1 row affected (0.13 sec)

mysql> insert into tbl_emp(name,deptId) values('faker',4);
Query OK, 1 row affected (0.10 sec)

mysql> insert into tbl_emp(name) values('mlxg');
Query OK, 1 row affected (0.13 sec)

mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | 技术部   | 11     |
|  2 | 美工部   | 12     |
|  3 | 总裁办   | 13     |
|  4 | 人力资源 | 14     |
|  5 | 后勤组   | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from tbl_emp;
+----+-------+--------+
| id | name  | deptId |
+----+-------+--------+
|  1 | jack  |      1 |
|  2 | tom   |      1 |
|  3 | alice |      2 |
|  4 | john  |      3 |
|  5 | faker |      4 |
|  7 | ning  |   NULL |
|  8 | mlxg  |   NULL |
+----+-------+--------+
7 rows in set (0.00 sec)

两表的关联关系如图所示：

1、左连接(A独有+AB共有)

查询所有部门以及各部门的员工数：

mysql> select t1.id,t1.deptName,count(t2.name) as emps from tbl_dept t1 left join tbl_emp t2 on t2.deptId=t1.id group by deptName order by id;
+----+----------+------+
| id | deptName | emps |
+----+----------+------+
|  1 | 技术部   |    2 |
|  2 | 美工部   |    1 |
|  3 | 总裁办   |    1 |
|  4 | 人力资源 |    1 |
|  5 | 后勤组   |    0 |
+----+----------+------+
5 rows in set (0.00 sec)

2、右连接(B独有+AB共有)

查询所有员工及其所属部门：

mysql> select t2.id,t2.name,t1.deptName from tbl_dept t1 right join tbl_emp t2 on t2.deptId=t1.id;
+----+-------+----------+
| id | name  | deptName |
+----+-------+----------+
|  1 | jack  | 技术部   |
|  2 | tom   | 技术部   |
|  3 | alice | 美工部   |
|  4 | john  | 总裁办   |
|  5 | faker | 人力资源 |
|  7 | ning  | NULL     |
|  8 | mlxg  | NULL     |
+----+-------+----------+
7 rows in set (0.04 sec)

3、内连接(AB共有)

查询两表共有的数据：

mysql> select deptName,t2.name empName from tbl_dept t1 inner join tbl_emp t2 on t1.id=t2.deptId;
+----------+---------+
| deptName | empName |
+----------+---------+
| 技术部   | jack