1 ---------------------------Connection Query--------------------------------2 --cross-connect queries3 --A subquery can be placed before the from to replace the displayed information4 SelectName asName, Sex,age, (SelectName fromBumenwhereBumen.code=Biao2.bumen) department, (SelectCeo fromBumenwhereBumen.code=Biao2.bumen) asCeo fromBiao25 --Connection query Two tables must be linked. 6 --If two tables have the same column name, you need to specify which table is the column, format: Table name. Column Name7 --a convenient way to connect queries,8 SelectBiao2.name,sex,age,bumen.name,ceo fromBiao2,bumenwhereBiao2.bumen=Bumen.code9 --join on another notation join (join) on (in.. ON) format: From table name 1 join table name 2 on two table column relationshipTen SelectBiao2.name,sex,age,bumen.name,ceo fromBiao2JoinBumen onBiao2.bumen=Bumen.code One --connect two tables with join on the Middle three columns (Biao2 name,sex,age and bumen Name,zhineng,ceo) A SelectBiao2.name,sex,age,bumen.name,zhineng,ceo fromBiao2 - JoinBumen onBiao2.bumen=Bumen.code - --three keywords before--------join on------------- the --If two tables do not have a primary foreign key relationship, only two tables are displayed if the keyword is not added before the join - -- Full - --add full before join to display all information for two tables (including information not in two tables) - SelectBiao2.name,sex,age,bumen.name,zhineng,ceo fromBiao2 + Full JoinBumen onBiao2.bumen=Bumen.code - -- Left + --add left before join to show all the information (including information that is not related) to the table on the top and sort by the table on the left. A SelectBiao2.name,sex,age,bumen.name,zhineng,ceo fromBiao2 at Left JoinBumen onBiao2.bumen=Bumen.code - -- Right - --add right before join to display all the information (including information that is not related) to the table on the left and sort by the right table. - SelectBiao2.name,sex,age,bumen.name,zhineng,ceo fromBiao2 - Right JoinBumen onBiao2.bumen=Bumen.code - in -------------------------Federated Query--------------------------- - --Vertical union query, to --the column data type for union two tables must correspond to be connected. With automatic weight-down function + --in a table - Select * fromBiao2whereAge>= the the Union * Select * fromBiao2whereAge<= - $ --In two tablesPanax Notoginseng SelectName,bumen fromBiao2 - Union the SelectCeo,code fromBumen
20141104--sql connection query, federated query