mysql strengthen (4) ~ multi table query: Cartesian product, eliminate Cartesian product operation (equivalent and non equivalent connection), internal connection (implicit connection, display connection), external connection and self connection
1, Cartesian product
1. What is Cartesian product:
Mathematically, there are two sets A={a,b},B={1,2,3}, then the Cartesian product of the two sets = {a,1}, {a,2}, {a,3}, {b,1}, {b,2}, {b,3} lists all the cases, with a total of 2 * 3 = 6 records
In the database, Cartesian product is the table result returned when there is no connection condition in multi table query.
2. In actual development, we should avoid full Cartesian product -- add effective connection conditions [equivalent connection] in where
Connecting n tables requires at least n-1 connection conditions.
■ the so-called multi table query -- "all queries are two tables". If there are five tables A, B, C, D and E, first A and B query to get AB table, then C and ab query to get ABC table, and then
3. Foreign key constraint: foreign key FK, which is used to restrict a column of table B data from the primary key column of table A. random writing is not allowed.
[in development, the validity of data is often checked through java code, and foreign keys are not used!]
■ foreign key function:
- Random writing is not allowed. Refer to the primary key column of another table to determine whether it is legal data
- It has nothing to do with the query. No foreign key will not affect the query. It is just that the data will be checked when inserting data.
- To support foreign keys and transactions in development, the storage engine must be InnoDB
- The performance of using foreign keys is low. In development, foreign key constraints are often deleted to check whether the data is legal through java code (business code)
- Foreign key - in the "multi party", for example, multiple employees belong to the sales department (the same department)
- Adding foreign keys in development often selects "one side" from "one side with more" through the drop-down box.
4. Eliminate Cartesian product (equivalent connection and non equivalent connection)
-
Equivalent connection: the most common connection operation is usually established by multiple tables with primary and foreign key constraints. The equivalent relationship between two fields in the connection condition is established through = (the connection condition is equal sign).
-
Non equivalent connection: multi table connection. The connection condition is not equal sign, but greater than, less than, greater than or equal to, less than or equal to
#Example of equivalent connection (the department number in the table emp refers to the department number in the table dept) - - the main foreign key constraint [but the foreign key is generally not added. Hahaha, the role of foreign key inspection is left to the java business logic code] select * from emp e, dept d where e.deptno = d.deptno; #Non equivalent connection example #Query the employee's name, salary, department name and salary grade (the table salgrade gives the salary range [grade No., minimum wage and maximum wage]) select e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal;
2, Multi table join query (the so-called 'join' -- find the corresponding column name through one column name)
1. Multi table query classification:
Inner connection (implicit / explicit connection), outer connection (left outer connection, right outer connection, all outer connection), self connection
2. Multi table query diagram:
3. When multiple tables query columns with duplicate names, the table name [commonly used alias] must be added before the column name.
# When there are columns with duplicate names in multiple tables, the table name [commonly used alias] must be added before the column name. select * from emp, dept where emp.deptno = dept.deptno; select * from emp e, dept d where e.deptno = d.deptno;
4. Internal connection query
(1) Implicit join syntax (no join):
select [field 1 [, field 2...]]
from table A, table B
where query filter condition and eliminate the connection condition of Cartesian product
[order by sort field 1 asc|desc [, sort field 2[asc|desc],...]]
(2) Display connection syntax (with join) -- common:
select [field 1 [, field 2...]]
from table A [inner] join table B on join conditions for eliminating Cartesian product [join table C on condition for eliminating Cartesian product...]
where query filter criteria
[order by sort field 1 asc|desc [, sort field 2[asc|desc],...]]
#Query employee number, employee name, employee department number and name. select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno; # Displays the equivalent connection of the query. When the column names are the same, you can use using select e.empno, e.ename, d.deptno, d.dname from emp e join dept d using (deptno); #Query employee number, employee name, employee department number and name, and salary grade select e.empno, e.ename, d.deptno, d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
- Natural connection: it refers to the equivalent connection of columns with the same name between tables.
■ internal connection [equivalent connection] - only matching conditions are displayed.
5. External connection
■ the biggest problem of internal connection: it can only be found by matching conditions.
For example, the new employee has no department assigned, but when querying multiple tables, the information of the new employee will not be displayed when querying employee number, name and department.
(1) Left connection / external connection [whichever is the most, who displays all, which table is the most, displays all of the table, and the one that is not connected is null]
- Left (outer) connection: query all the data of the table on the left of the join. If the table on the right does not match, fill it with null.
- Right (outer) connection: query all the data of the table on the right of the join. If the table on the left does not match, fill it with null.
(2) Global external connection query
-
mysql is not supported for the time being, but it can be connected through union +.
select e.ename, d.dname from emp e left join dept d on e.deptno = d.deptno union select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;
6. Self connection (self connection and self connection. One table is used as two tables and distinguished by alias) --- common:
-
In the query statement, a table can be reused many times to complete the need of multiple connections.
#Query the employee name and its corresponding Supervisor Name. The emp table has the column of employee name (the supervisor also belongs to the employee) and the supervisor number - find the corresponding employee name through the supervisor number [link] select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;