1. Learning content
Addition and subtraction of Table 2
What is set operation?
A set represents "the sum of all kinds of things" in the field of mathematics and a set of records in the field of database. Specifically, the execution results of tables, views and queries are a set of records, and the elements in them are each row in the table or query results. In standard SQL, union, intersect and excel are used to merge, intersect and difference the search results respectively. Operators such as union, intersect and excel used to perform set operations are called set operators
2.1 addition UNION of table
SQL statement: (represents the union of two sets)
SELECT product_id, product_name FROM Product UNION SELECT product_id, product_name FROM Product2;
Note: UNION and other set operators usually remove duplicate records
2.1.2 UNION and OR predicate
SQL statement: (the two results are consistent)
-- use OR predicate SELECT * FROM Product WHERE sale_price / purchase_price < 1.3 OR sale_price / purchase_price IS NULL; **-- use UNION SELECT * FROM Product WHERE sale_price / purchase_price < 1.3 UNION SELECT * FROM Product WHERE sale_price / purchase_price IS NULL; **
2.1.3 UNION ALL
For set operations that contain duplicate rows, add ALL after UNION
2.2 MySQL 8.0 does not support INTERSECT
2.3 subtraction of difference set, complement set and table
2.3.1 MySQL 8.0 does not support excel operation
MySQL 8.0 does not support table subtraction operator excel. However, with the help of the NOT IN predicate, we can also realize the subtraction of the table.
Exists only in the Product table but not in the Product2 table:
-- use IN Implementation method of clause SELECT * FROM Product WHERE product_id NOT IN (SELECT product_id FROM Product2)
2.3.2 INTERSECT AND and predicates
For two query results of the same table, their INTERSECT can actually connect the search conditions of the two queries with AND predicate equivalently.
2.4 symmetry difference
The symmetry difference between two sets A and B refers to the set composed of elements belonging only to A or B. The intersection of two sets can be regarded as the intersection of two sets, and the symmetry difference between the two sets is removed.
However, in MySQL 8.0, the of two tables or query results cannot be obtained directly. Therefore, it is not suitable to use the above idea to calculate the symmetry difference. Fortunately, there are difference set operations that can be used. It can be seen intuitively that the symmetry difference of two sets is equal to A-B and B-A, so this idea can be used to calculate the symmetry difference in practice.
Use the symmetry difference between the Product table and the Product2 table to query which products are in only one table
-- use NOT IN Implement the difference set of two tables SELECT * FROM Product WHERE product_id NOT IN (SELECT product_id FROM Product2) UNION SELECT * FROM Product2 WHERE product_id NOT IN (SELECT product_id FROM Product)
4 exercise questions
Find out the basic information of products with a price higher than 500 in product and product2.
select * from product where sale_price > 500 union select * from product2 where sale_price > 500;
With the help of the implementation of symmetric difference, find the intersection of product and product2.
select * from (select * from product union select * from product2) as p where product_id not in (SELECT product_id FROM product WHERE product_id NOT IN (SELECT product_id FROM product2) UNION SELECT product_id FROM product2 WHERE product_id NOT IN (SELECT product_id FROM product));
Which stores sell the highest priced goods in each category?
select p1.shop_id,p1.shop_name,p1.quantity, p2.product_id,p2.product_name,p2.product_type,p2.sale_price, mp.max_price as 'The highest selling price of this kind of goods is' from shopproduct as p1 inner join product as p2 on p1.product_id = p2.product_id inner join (select product_type ,max(sale_price)as max_price from product group by product_type )as mp on mp.product_type = p2.product_type and p2.sale_price = mp.max_price;
Use the inner link and the associated sub link to query the highest selling price of each category of goods.
Inner link SQL statement: (consistent with the above question)
select p.product_id,p.product_id,p.product_type,p.sale_price, mp.max_price as 'Maximum price of such goods' from product as p inner join(select product_type,max(sale_price) as max_price from product group by product_type)as mp on p.product_type = mp.product_type and p.sale_price = mp.max_price;
Associated subquery SQL statement:
select p.product_id,p.product_type,p.sale_price, mp.max_price as 'Maximum price of such goods' from product as p, (select product_type,max(sale_price) as max_price from product group by product_type) as mp where p.product_type = mp.product_type and p.sale_price = mp.max_price;
Use the associated sub query: in the product table, retrieve the product_id, produc_name, slae_price, and sort according to the selling price of goods from low to high, and sum the selling prices cumulatively.
SELECT p.product_id, p.product_name, p.sale_price, (select sum(sale_price) from product as p1 where p.sale_price > p1.sale_price or(p.sale_price=p1.sale_price) )as 'Cumulative summation' from product as p order by sale_price;
Tianchi Longzhu SQL training camp daily task4 punch in