Multi-Table query and Table query

Multi-Table query and Table query

/*
Create a one-to-many table
Main table: Product category Table category
From table: Product table products
Products uses the primary key in category as the foreign key
*/
-- Create a product category table category: category primary key, category name
Create table category (
Cid int primary key AUTO_INCREMENT,
Cname VARCHAR (20)
);

-- Insert test data to category
Insert into category (cname) VALUES ('clothing '), ('appliance'), ('toy'), ('shoes and hats ');

-- Create product table products: Primary Key of the product, product name, product price, and product category table (foreign key)
Create table products (
Pid int primary key AUTO_INCREMENT,
Pname VARCHAR (20 ),
Price DOUBLE,
Category_cid INT
);

/*
Set the field category_cid in the table to the foreign key of the primary table.
Format:
Alter table add [constraint] [foreign key name] foreign key (foreign key field name) from the table)
References primary table (primary key of the primary table );
*/
Alter table products add constraint cid_FK foreign key (category_cid)
REFERENCES category (cid );

-- Add data to the products table. The foreign key uses the primary key of the primary table.
Insert into products (pname, price, category_cid) VALUES ('undercoats ', 1 );
Insert into products (pname, price, category_cid) VALUES ('army coat );
Insert into products (pname, price, category_cid) VALUES ('batch );
Insert into products (pname, price, category_cid) VALUES ('super Apsara, 69,3 );
Insert into products (pname, price, category_cid) VALUES ('high heels );

-- Add data to the products table and use a primary key that does not exist in the primary table
Insert into products (pname, price, category_cid) VALUES ('refrigerator ', 5 );

-- Delete data whose primary key is 4 in the category table
-- You cannot directly Delete the data in the master table that has been used by the slave table. You must first Delete the data in the slave table.
Delete from products WHERE pid = 5;
Delete from category WHERE cid = 4;

-- Add common data to the item table without foreign key data. The default value is null.
Insert into products (pname, price) VALUES ('refrigerators, 1800 );

/*
Many-to-many relationships
Main table: item table, order table
Intermediate table: Use the primary key (foreign key) of the commodity table and the primary key (foreign key) of the Order table)
*/
-- Create order table orders: Order primary key, total item amount
Create table orders (
Oid int primary key AUTO_INCREMENT,
Totalprice DOUBLE
);

-- Add data to the order table
Insert into orders (totalprice) VALUES (310), (169), (2400 );

-- Create intermediate table orderitem: Use the primary key (foreign key) of the commodity table and the primary key (foreign key) of the Order table)
Create table orderitem (
Pid INT,
Oid INT
);

-- Add the cid field in the intermediate table as the foreign key of the primary key in products.
Alter table orderitem add foreign key (pid) REFERENCES products (pid );

-- Add the field oid in the intermediate table as the foreign key of the primary key in orders.
Alter table orderitem add foreign key (oid) REFERENCES orders (oid );

-- Add data to the intermediate table
Insert into orderitem (pid, oid) VALUES );
-- Add a data pid to the intermediate table that does not exist
Insert into orderitem (pid, oid) VALUES (6, 1 );
-- Add a data oid that does not exist to the intermediate table
Insert into orderitem (pid, oid) VALUES (1, 4 );

-- Delete data with id 4 in products (the intermediate table is in use and cannot be deleted directly)
Delete from products WHERE pid = 4;

-- The data in the intermediate table can be deleted directly.
Delete from orderitem WHERE pid = 1 AND oid = 1;

Multi-Table query
At least two tables can be queried together.

Cross-join query: The queried data is incorrect (Cartesian Product)

Internal join: [inner join on]

Implicit inner join: Inner join on omitted
Select * from Table A, Table B where Table A. Primary Key = Table B. Foreign key
Display: Write inner join on
Select * from Table A inner join table B on Table A. Primary Key = Table B. Foreign key

External link:
Left outer join (mainly the table on the left): left [outer] join on
Select * from Table A left [outer] join table B on Table A. Primary Key = Table B. Foreign key
Right outer join (dominated by the table on the right): right [outer] join on
Select * from Table A right [outer] join table B on Table A. Primary Key = Table B. Foreign key

Subquery:
The query result of one SQL statement is used as the result of another SQL statement (condition, another table, result)
Select * from Table A where field = (select * from Table B)
SELECT * FROM category, products;

-- Implicit inner join on
SELECT * FROM category, products WHERE category. cid = products. category_id;

-- Simplify implicit inner join and use the keyword as to alias the table
SELECT * FROM category c, products p WHERE c. cid = p. category_id;

-- Show inner join: Write inner join on
SELECT * FROM category c inner join products p ON c. cid = p. category_id;

-- As long as the appliance and cosmetics are connected in the display, the where condition can be written later)
SELECT * FROM category c inner join products p ON c. cid = p. category_id
WHERE c. cid <> 2;

-- As long as the appliance and cosmetic products are implicitly connected, multiple conditions are connected using logical operators)
SELECT * FROM category c, products p WHERE c. cid = p. category_id AND c. cid <> 2;

-- Query the items that have been shelved (displayed)
SELECT * FROM category c inner join products p ON c. cid = p. category_id WHERE p. flag = 1;

-- Query the items that have been shelved (implicit)
SELECT * FROM category c, products p WHERE c. cid = p. category_id AND p. flag = 1;

-- Left outer join (mainly the table on the left): left [outer] join on
-- The table on the left is the primary table, and data on the left is not displayed on the right. Use null to display the data.
SELECT * FROM category c left outer join products p ON c. cid = p. category_id;

-- Right outer join (dominated by the table on the right): right [outer] join on
-- Mainly on the right, no data on the right, not displayed on the left
SELECT * FROM category c right outer join products p ON c. cid = p. category_id;

-- Left outer join (mainly the table on the left): left [outer] join on
SELECT * FROM products p left outer join category c ON c. cid = p. category_id;

-- Count the quantity of each type of item
SELECT cname, COUNT (category_id) FROM category c left outer join products p
ON c. cid = p. category_id
Group by cname;

-- Subquery: the query result of an SQL statement, which is used as the result of another SQL statement (condition, another table, and result)
-- Use an implicit inner connection to query "Cosmetic" product details
SELECT * FROM category c, products p WHERE c. cid = p. category_id AND c. cname = 'cosmetic'

-- Use the subquery to query the "Cosmetic" product details
SELECT * FROM products WHERE category_id = 3;
SELECT * FROM products WHERE category_id =
(SELECT cid FROM category WHERE cname = 'cosmetic ');

-- The result of an SQL statement is used as another table of another SQL statement.
SELECT * FROM products p,
(SELECT * FROM category WHERE cname = 'cosmetic ') c
WHERE p. category_id = c. cid;

-- Query the details of shelved goods in the categories of "Cosmetics" and "Household Appliances"
-- Use conditional subquery
SELECT * FROM products WHERE category_id IN
(SELECT cid FROM category WHERE cname = 'cosmetic 'OR cname = 'appliance ');

-- Use another table as a subquery
SELECT * FROM products p, mybase2mysqlmysqlmybase2category
(SELECT * FROM category WHERE cname = 'cosmetic 'OR cname = 'appliance') c
WHERE p. category_id = c. cid;