Golden Code SQL notes (7)

Attach text for easy copy

page247-Table Connection 269--Table connection--build table and test data--t_customer Customer Information table--t_ordertype order type--t_order order information CREATE TABLE T_customer (Fid INT not NULL , FName VARCHAR () not null,fage INT, PRIMARY KEY (FID)) CREATE TABLE T_order (FID int. not Null,fnumber VARCHAR (a) not NU Ll,fprice NUMERIC (2), Fcustomerid Int,ftypeid int,primary KEY (FID)) CREATE TABLE t_ordertype (FID INT not null,fname VA Rchar () Not null,primary KEY (FID)) insert into T_customer (FID, FName, Fage) VALUES (1, ' Tom ', +) insert into T_customer (F ID, FName, Fage) VALUES (2, ' MIKE ', +) insert into T_customer (Fid, FName, Fage) VALUES (3, ' JACK ', ' V ') insert into T_customer (F ID, FName, Fage) VALUES (4, ' Tom ', +) insert into T_customer (Fid, FName, Fage) VALUES (5, ' LINDA ', NULL) insert INTO T_ordertyp E (FID, FName) VALUES (1, ' marketcrder ') insert into T_ordertype (FID, FName) VALUES (2, ' Limitorder ') insert INTO T_ordertype (Fid, FName) VALUES (3, ' Stop Order ') insert into T_ordertype (FID, FName) VALUES (4, ' Stoplimit Order ') inserts into T_order (FID, Fnumber, F Price,fcustomerid, FTYpeid) VALUES (1, ' K001 ', 100,1, 1) INSERT into T_order (Fid, Fnumber, Fprice,fcustomerid, Ftypeid) VALUES (2, ' K002 ', 200,1, 1 INSERT into T_order (FID, Fnumber, Fprice,fcustomerid, Ftypeid) VALUES (3, ' T003 ', 300,1, 1) INSERT into T_order (FID, Fnumbe R, Fprice,fcustomerid, Ftypeid) VALUES (4, ' N002 ', 100,2, 2) INSERT into T_order (Fid, Fnumber, Fprice,fcustomerid, Ftypeid) VALUES (5, ' N003 ', 500,3, 4) INSERT into T_order (Fid, Fnumber, Fprice,fcustomerid, Ftypeid) VALUES (6, ' T001 ', 300,4, 3) inser T into T_order (Fid, Fnumber, Fprice,fcustomerid, Ftypeid) VALUES (7, ' T002 ', 100,null,1)-- The Join keyword joins one or more tables in relation to each other as a result set--inner join joins two tables, and contacts them based on the association of the two tables--using an inner join to refer to which words in the table interleaved the association. And you need to specify what--conditions are connected within the connection syntax INNER JOIN table_name on condition--most systems, inner in the inner join is optional, INNER join is the default link mode select * FR OM T_orderselect * from T_customerselect * from T_order INNER JOIN T_customeron t_customer. Fid = T_order. fid--when using a table connection, it can be not limited to only two tables, because in a very good case--Connecting many tables select * from T_orderselect o. Fid, O. Fnumber, O.fprice, C. FNaMe, OT. Fnamefrom T_order as O INNER JOIN t_customer as CON o. Fcustomerid = c. Fidinner JOIN t_ordertype as Oton o. Ftypeid = ot. fid--no equivalent connection--the above shows that setting a field after on must be equal to a field in another table--in addition to the equivalent connection, there is a non-equivalent connection that can be used in the condition of the connection-less than (<), greater than (>), not equal to (<>) equals operation, You can also use the like--, between and and so on, or even use the function select O. Fid, O. Fnumber, O.fprice, C. fnamefrom t_order as O INNER JOIN t_customer as CON o Fprice < C. Fage * 5--Add the Limit and O. Fcustomerid = c After you can add and to the equivalent connection. fid--Cross Join--cross connection is very simple compared to an inner connection, because it does not have an ON clause--a cross join will involve all the records contained in the result set can be used in two ways-to define the cross-connection, respectively, is the private connection and the display of the connection select * from T_order --7 Data select * FROM T_customer--5 data select * from T_customer, t_order--35 data = 7*5select * from T_customercross JOIN t_or Der-Shows the connection, implicit connection is ignored by default cross join--self-connection-the above is said to be between different data tables, actually participate in the connection of the table can be-the same table, that is, the table and its own connection, such a connection is called self-connected select t1.*, T2. * FROM T_customer T1 INNER joint_customer as T2 on T2. Fid = t1. fid--external connection--left Outer connection outer JOIN right outer connection outer join--Full external connection complete outer join--external connection syntax and internal connection almost the same, The main difference is the handling of NULL values--external connections do not require two tables with matching records, soYou can specify that records in a table always put-to the result set-the left outer join left table to match the data, put the data of the left table in the result set,--regardless of whether there is a matching record in the right table--, can match to the display, the mismatch is displayed as Nullselect * from T_order --7 Data select * FROM T_customer--5 data Select O. fnumber, O. Fprice, O.fcustomerid, C. FName, C. Fage from T_order as O left OUTER JOIN t_customer as CON o fcustomerid =c. fid--right outer joins the right table as the datum to match the data, putting the data of the right table into the result set,--regardless of whether there is a matching record in the left table--, can match to the display, the mismatch is displayed as Nullselect * from T_order--7 Data select * FROM T _customer--5 data Select O. fnumber, O. Fprice, O.fcustomerid, C. FName, C. Fage from T_order as O right OUTER JOIN t_customer as CON o fcustomerid =c. fid--full external connection-almost all databases support left and right external connections, but full external connections--not all databases are supported, such as mysql.--full outer joins are a collection of left and right outer joins, because even in the right table, data that matches the join condition , all records in the left table will also be placed in the result set, as well-there is no matching record in the left table, and all records in the right table will be placed in the result set select O. Fnumber, O. Fprice, O.fcustomerid, C. FName, C. Fage from T_order as O full OUTER JOIN t_customer as CON o fcustomerid =c. Fid

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Golden Code SQL notes (7)