Some uses of MySQL self-connection

A self-connection is a use of a connection, but not a type of connection, because his nature is to use a table as two tables.

MySQL sometimes needs to make a connection to itself (self-connect) in the information query, so we need to define an alias for the table.

For example, below is a list of commodity purchases and we need to find all the information about the purchase price that is higher than the benefit.

In general, we see this table. We use statements to do the first time:

1 SELECT * from shoping WHERE price>27

It is conceivable how simple it is to assume that you do not know the database table detail data or the amount of data is quite large? As a database administrator, we are going to find the data we need in a different way.

Step into the query

SELECT price from shopping where name= ' Megumi '///to arrive at price query result 27SELECT * from shopping where price>27

We can obtain the following table information:

Compared with the self-connected approach, this method requires manual intervention of intermediate results, which is obviously not conducive to automatic processing in the program.

Self-connect mode:

SELECT b.* from shopping as a,shopping as Bwhere A.name= ' Whitney ' and A.price<b.price order by b.id2018-03-06

Gets the following table information:

Note the point:

Alias A, B although the name is different, but the same table, the purpose of defining aliases is more convenient to delete themselves.

The b.* that is obtained by executing select through (the intermediate table) is the final result.

To give an example :

CREATE Table Dept (--Department table Deptno INT PRIMARY KEY,--Department number dname varchar,--Department name Loc VARCHAR (13)--location); Engine=innodb DEFAULT charset=utf8;insert into dept values (' Accounting ', ' New York '); INSERT into dept values (' Res Earch ', ' Dallas '); INSERT into dept values ("Sales", ' Chicago '), insert into dept values (+, ' Operations ', ' Boston '); Nsert into Dept VALUES (' Admin ', ' washing ');

CREATE table EMP (--Employee table empno INT not NULL PRIMARY KEY,--empno Employee number ename varchar ()--ename employee Name Job VARCHAR,--Job M GR INT,--Mgr Superior person number hiredate datetime,--Hire date sal double,--salary comm Double,--Commission deptno INT,--Department number foreign KEY (DEPTNO) Referen CES Dept (DEPTNO)); Engine=innodb DEFAULT Charset=utf8;

INSERT into EMP values (7369, ' Smith ', ' clerk ', 7902, ' 1980-12-17 ', 800,0,20); INSERT into EMP values (7499, ' Allen ', ' Salesma N ', 7698, ' 1981-2-20 ', 1600,300,30); INSERT into EMP VALUES (7844, ' Turner ', ' salesman ', 7499, ' 1981-9-8 ', 1500,0,30); I Nsert into EMP values (7698, ' Tom ', ' Manager ', 0, ' 1981-9-8 ', 6100,600,40); INSERT into EMP values (7876, ' Adams ', ' clerk ', 790 0, ' 1987-5-23 ', 1100,0,20); INSERT into EMP VALUES (7900, ' James ', ' clerk ', 7698, ' 1981-12-3 ', 2400,0,30); INSERT INTO EMP VAL UES (7902, ' Ford ', ' Analyst ', 7698, ' 1981-12-3 ', 3000,null,20); INSERT into EMP VALUES (7901, ' Kik ', ' clerk ', 7900, ' 1981-12-3 ', 1900,0,30);

　　

　　

Open the Created table:

If the names of all employees and their immediate superiors are listed, we can do so by self-connection:

Select E.ename, (select ename from emp d WHERE d.empno=e.mgr) as the boss from EMP E;

Results of the query:

The above is a staff information table, if I want to query this table each employee's boss, then must use the self-connection to query. So in order to implement this query, you need to give the table two aliases, and all the data used in the query needs to be prefixed with the table alias, because the data columns of the two tables are identical.

Last words: Everything starts hard, the middle is difficult, finally are very difficult!!!

Some uses of MySQL self-connection