MySQL Multi-table query

First, the SQL statement that builds the table:

paging operation: Starting position with limit (parameter 1, parameter 2) (parameter 1)=(Page No.-1)*number of bars displayed per page (parameter 2)1. Classification TableCreate TableCategory (CIDvarchar( +)Primary Key, CNAMEvarchar( -));2. Commodity tableCreate Tableproduct (PIDvarchar( +)Primary Key, PNamevarchar( +), priceDouble, category_idvarchar( +));Alter TableProductAdd Foreign Key(category_id)Referencescategory (CID);3. Add a foreign key columnAlter TableProductAddcategory_idvarchar( +);4. Add ConstraintsAlter TableProductAdd constraintProduct_fkForeign Key(category_id)Referencescategory (CID);5. Order FormCreate TableOrders (OIDvarchar( +)Primary Key, TotalpriceDouble);6. Order Item tableCreate TableOrderItem (OIDvarchar( -), PIDvarchar( -));7. Federated PRIMARY KeyAlter TableOrderItemAdd Primary Key(oid,pid);8the primary foreign key relationship for the order table and the Order Item tableAlter TableOrderItemAdd constraintOrderitem_orders_fkForeign Key(OID)Referencesorders (OID);9primary foreign key relationships for commodity tables and order item tablesAlter TableOrderItemAdd constraintOrderitem_product_fkForeign Key(PID)Referencesproduct (PID);Insert  intoCategory (Cid,cname)Values('c001','Home Appliances');Insert  intoCategory (Cid,cname)Values('c002','Apparel');Insert  intoCategory (Cid,cname)Values('c003','Cosmetics');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('P001','Lenovo',' the','c001');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('P002','Haier',' the','c001');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p003','Thunder God',' the','c001');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p004','JACK JONES .',' -','c002');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p005','Jeanswest',' $','c002');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p006','Playboy',' the','c002');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p007','Jin Ba',' -','c002');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p008','Chanel',' -','c003');Insert  intoProduct (PID,PNAME,PRICE,CATEGORY_ID)Values('p009','Suitable for Materia medica',' $','c003'); Declare foreign KEY constraints1. The relationship from OrderItem to productAlter TableOrderItemAdd Foreign Key(PID)Referencesproduct (PID);

Two add foreign keys

Primary foreign key relationships for commodity tables and commodity category tables
MySQL>altertableaddconstraintforeignkey  References category (CID);

Primary foreign key relationship for order table and Order Item table
ALTER TABLE OrderItem ADD constraint ORDERITEM_ORDERS_FK foreign key (OID) references orders (OID);

Primary foreign key relationships for commodity tables and order item tables
ALTER TABLE OrderItem ADD constraint ORDERITEM_PRODUCT_FK foreign key (PID) references product (PID);

added: One-to-many, many-to-many, one-to-one chart of principles

　　

Three multi-table query

1 cross-connect query: The product of two tables is obtained

MySQL>Select* from Category,product;

2 Internal Connection Query

2.1 Implicit in-connection

MySQL Multi-table query