mysql--Connection Query

• Internal connection Query
  • Meaning: Find the relationship between a table and a table or a bridge. The connection query is used when querying two or more than two tables.
  • join| Cross join| INNER join via on join condition (all three ways) is usually connected with join, on the back of the connection condition

    --Query Cms_user id,username the provinces table and the user table have IDs--Query the provinces in the provinces table, PronameSELECTCms_user.id,username,proname fromCms_user,provinces;--This query is not correct, because there are IDs in all two tables, and the result of the query is not the actual desired result, but the form of the Cartesian product .--Cms_user's proid corresponds to the ID in the province tableSELECTCms_user.id,username,proname fromcms_user,provincesWHERECms_user.proid=provinces.id;--Inner Connection (this is the same as the Where in the above (first write the table to query things, then write the conditions)--querying the Id,username,email,sex in the Cms_user table--querying the Proname in the provinces tableSELECTU.id,u.username,u.email,u.sex,p.proname fromCms_user asU--connect the first table  INNER JOINProvinces asP--second table of connections onU.proid=P.id;--Specify the connection condition by on, where the condition is that when the user's table is proid, the ID in the province table--the first table to query: query Cms_user id,username,sex--second table to query: query provinces prpname--the join condition after on is: The proid of the user table equals the ID of the province table. --condition: cms_user sex for the male userSELECTU.id,u.username,u.sex,p.proname fromCms_user asuJOINProvinces asP onU.proid=p.idWHEREU.sex='male';--Filter the grouped results, according to Proname group, select the number of people greater than or equal to 1, and according to the ID of the user table in ascending order, limit the number of bars displayed (the first two)SELECTU.id,u.username,u.sex,p.proname,COUNT(*) astotalsum,group_concat (Sex) fromCms_user asuJOINProvinces asP--Connection onU.proid=p.idWHEREU.sex='male'GROUP  byP.proname having COUNT(*)>=1ORDER  byU.idASCLIMIT0,2;;--querying the Id,title in Cms_news--querying the Catename in Cms_cate--connection Condition: n.cid=c.idSELECTN.id,n.title,c.catename--write the query first, fromCms_news asN--write a few more tables to connect toJOINCms_cate asC onN.cid=C.id;--Write Connection Conditions--cms_news Id,title--cms_cate Catename--cams_admin username, role--connection conditions: Conditions for Cms--news and cms--cate: n.cid=c.id cms_news and Cams_admin connection conditions:SELECTN.id,n.title,c.catename,a.username,a.role fromCms_news asN--Three-table connection Cms_news and cms_cate,,,cms_admin respectively established connectionJOINCms_cate asC onN.cid=c.idJOINCms_admin asa onN.aid=a.ID;

• Outer JOIN query
  • If the error data (junk data) is inserted, the internal connection cannot be queried for this junk data.
  • Left outer connection: The LEFT JOIN first shows all the records of the right table and then goes to the table to find the records that match the join criteria.
  • Right outer join: Right JOIN shows all the records of the table in full and the records of the left table that match the join criteria

    -- Left outer connection SELECT U.id,u.username,u.email,u.sex,p.proname  from  as u           Left JOIN  as P     on u.proid=p.id;

• FOREIGN key operation
  • 　　

    --Create a Departmental table department (primary table)--ID depnameCREATE TABLE IF  not EXISTSDeparment (IDTINYINTUNSIGNEDKEYAuto_increment,depnameVARCHAR( -) not NULL UNIQUE) ENGINE=INNODB;INSERTDeparment (Depname)VALUES('Teaching Department'),('Marketing Department'),('Operations Department'),('Supervision Department');--Create an Employee table sub-table employeeCREATE TABLE IF  not EXISTSEmployee (IDTINYINTUNSIGNEDKEYAuto_increment,usernameVARCHAR( -) not NULL UNIQUE, DepidTINYINTUNSIGNED) ENGINE=INNODB;INSERTEmployee (USERNAME,DEPID)VALUES('KING',1),('QUEUE',2),('Zhangsan',4),('LISI',4),('Wangwu',5);--Query employee's number, user name, department name two tables--Connection Condition: e.depid=d.id;--Internal connection QuerySELECTE.id,e.username,d.depname fromDeparment asDJOINEmployee ase onE.depid=d.id;--Delete the Steering Department (department disbanded, theoretically employees also have to dissolve, but when the staff table, found that there are employees under the supervision Department)--that is, if you are working on a parent table and you find that there is data in the child table associated with the parent table, you must do something, where the foreign key can be usedSELECT  fromDeparmentWHEREProname='Supervision Department';--Create a foreign key--Create a table--Create a parent table, create a child table, establish a foreign key in a child tableCREATE TABLE IF  not EXISTSDeparment (IDTINYINTUNSIGNEDKEYAuto_increment,depnameVARCHAR( -) not NULL UNIQUE) ENGINE=INNODB;CREATE TABLE IF  not EXISTSEmployee (IDTINYINTUNSIGNEDKEYAuto_increment,usernameVARCHAR( -) not NULL UNIQUE, DepidTINYINTUNSIGNED,FOREIGN KEY(Depid)REFERENCESDeparment (ID)--FOREIGN Key) ENGINE=INNODB;

mysql--Connection Query