MySQL Database syntax-multi-table query exercise one

MySQL Database syntax-multi-table query exercise one

Yun Zhengjie

Copyright Notice: Original works, declined reprint! Otherwise, the legal liability will be investigated.

This blog mainly describes the foreign key constraints of multi-table queries, and how to use external links and internal connections to query data information.

I. Data sheets and test data preparation

1 /*2 @author: Yinzhengjie3 blog:http://www.cnblogs.com/yinzhengjie/tag/%e6%95%b0%e6%8d%ae%e5%ba%93%e4%bb%8e%e5%85%a5%e9%97%a8%e5%88% b0%e7%b2%be%e9%80%9a/4 Email:[email protected]5 */6 7 Create TableDept (8Didint Primary KeyAuto_increment,9Dnamevarchar(Ten)Ten ); One  A Create TableEMP ( -Eidint Primary KeyAuto_increment, -Namevarchar(Ten), theSalaryint, -Dnoint, -     Foreign KeyEMP (DNO)ReferencesDept (did) - ); +  - Insert  intoDeptValues(NULL,'Marketing Department'); + Insert  intoDeptValues(NULL,'Research Department'); A Insert  intoDeptValues(NULL,'Finance Department'); at Insert  intoDeptValues(NULL,'Logistics'); - Insert  intoEmpValues(NULL,'Deng Xi',15000,1); - Insert  intoEmpValues(NULL,'Li Xianjing',8000,2); - Insert  intoEmpValues(NULL,'Yang Mingming',7000,2); - Insert  intoEmpValues(NULL,'Yun Zhengjie',100000000,3); - Insert  intoEmpValues(NULL,'Tao Tao',10000,1); in Insert  intoEmpValues(NULL,'Square agreeable',9800,1); - Insert  intoEmpValues(NULL,'Deng Congcong',13000,NULL); to Insert  intoEmpValues(NULL,'Li Yachu',15000,NULL);

Two. Internal connection and external link

1> The difference between an internal connection and an external link

2> normal internal links (using keywords)

3> implicit internal links (not using keywords, common)

4> left outer link

5> right outer link

Three. Small trial sledgehammer

1>. Querying the owner's department and employee name

2> count the number of people in each department (group by)

3>. Statistical average salary per department (group by)

4>: Department of Statistics Department with average wage greater than the average salary of the company

MySQL Database syntax-multi-table query exercise one