自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。
mysql有时在信息查询时需要进行对自身连接(自连接),所以我们需要为表定义别名。
我们举例说明,下面是商品采购表,我们需要找到采购价格比惠惠高的所有信息。
一般情况我们看到这张表我们第一时间用语句进行操作:
1 SELECT * FROM shoping WHERE price>27
可想而知,这是有多么简单,假设你并不知道数据库表详细数据或者数据量相当庞大呢?作为一个数据库管理员,我们就要用别的方式迅速找出所需要的数据。
分步查询
1
2
|
SELECT price FROM shopping WHERE name = '惠惠' //得出price查询结果为27 SELECT * FROM shopping WHERE price>27 |
我们可以获取到如下表信息:
与采用自连接的方式相比,这种方法需要对中间结果进行人工干预,显然不利于程序中的自动处理操作。
自连接方式:
1
2
3
4
5
|
SELECT b.* from shopping as a,shopping as b where a. name = '惠惠' and a.price<b.price order by b.id2018-03-06 |
获取到如下表信息:
注意点:
别名 a,b虽然名称不同,但是同一个表,定义别名的目的是更方便在自身进行删选。
执行select通过(中间表)所得到的b.*,,就是最终结果。
再举个实例:
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TABLE dept( -- 部门表 deptno INT PRIMARY KEY , -- 部门编号 dname VARCHAR (14), -- 部门名称 loc VARCHAR (13) -- 地点 ); ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO dept VALUES (10, 'Accounting' , 'New York' ) ; INSERT INTO dept VALUES (20, 'Research' , 'Dallas' ) ; INSERT INTO dept VALUES (30, 'Sales' , 'Chicago' ) ; INSERT INTO dept VALUES (40, 'Operations' , 'Boston' ) ; INSERT INTO dept VALUES (50, 'Admin' , 'Washing' ) ;<br><br> |
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TABLE emp( -- 员工表 empno INT NOT NULL PRIMARY KEY , -- empno员工号 ename VARCHAR (10), -- ename员工姓名 job VARCHAR (10), -- job工作 mgr INT , -- mgr上级人员编号 hiredate DATETIME, -- 受雇日期 sal DOUBLE , -- 薪金 comm DOUBLE , -- 佣金 deptno INT , -- 部门编号 FOREIGN KEY (deptno) REFERENCES dept(deptno) ); ENGINE=INNODB DEFAULT CHARSET=utf8;<br><br> |
1
2
3
4
5
6
7
8
|
INSERT INTO emp VALUES (7369, 'Smith' , 'Clerk' ,7902, '1980-12-17' ,800,0,20) ; INSERT INTO emp VALUES (7499, 'Allen' , 'Salesman' ,7698, '1981-2-20' ,1600,300,30) ; INSERT INTO emp VALUES (7844, 'Turner' , 'Salesman' ,7499, '1981-9-8' ,1500,0,30) ; INSERT INTO emp VALUES (7698, 'Tom' , 'Manager' ,0, '1981-9-8' ,6100,600,40) ; INSERT INTO emp VALUES (7876, 'Adams' , 'Clerk' ,7900, '1987-5-23' ,1100,0,20) ; INSERT INTO emp VALUES (7900, 'James' , 'Clerk' ,7698, '1981-12-3' ,2400,0,30) ; INSERT INTO emp VALUES (7902, 'Ford' , 'Analyst' ,7698, '1981-12-3' ,3000, NULL ,20) ; INSERT INTO emp VALUES (7901, 'Kik' , 'Clerk' ,7900, '1981-12-3' ,1900,0,30) ; |
打开创建好的表:
如果说列出所有员工的姓名及其直接上级的姓名,我们可以通过自连接进行这样的操作:
1
|
SELECT e.ename,( SELECT ename FROM emp d WHERE d.empno=e.mgr) AS boss FROM emp e; |
查询的结果:
上面的是一张职员信息表,如果我要查询这张表中的每个职员的上司,那么必须使用自连接来查询.所以为了能实现这个查询,需要给这张表取两个别名,而且查询中所用的所有数据都需要加上表的别名前缀,因为两个表的数据列完全一样.
转自:https://www.cnblogs.com/weichao1996/articles/8519973.html