Mysql-(iv)-correlation query

set up a data table

//Department table
Create TableDept (IDint Primary Key, Deptnamevarchar( -))
Employee TableCreate TableEmployee (IDint Primary Key, EmpNamevarchar( -), DeptIDint，--Department Name        CONSTRAINTEmployee_dept_fkFOREIGN KEY(DeptID)ReferencesDept (ID) foreign key Name foreign key reference table (reference field))

=============================================================================================================== =============

Multi-Table query = correlated Query

=============================================================================================================== =============

Xxxxxxxxx query employees and their departments

1.1 Cross-query (duplicate records are generated)

SELECT  EmpName, deptname from   employee,dept;

1.2 In-Connection queries (only results that meet the criteria are displayed)

---Multi-table query rules

1) which tables are queried

2) determine which fields

3) connection condition between table and table

SELECT empname,deptname    2) determine which fields              from  employee,dept     1 ) query which tables                        where empoyee.deptid=dept.id      3) join condition between table and table

1.3 Another syntax for intra-connection queries

SELECT empname,deptname               from Employee              INNER JOIN Dept               on Employee.deptid=

Using an alias query

SELECT e.empname,d.deptname                                       from   employee e                       INNERJOIN  Dept d                                                  on E.deptid=  D.id;

-----------------query employees in each department

SELECT d.deptname,e.empname                   from Dept D, employee e                WHERE  = E.deptid

Mysql-(iv)-correlation query