This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is:
https://tianchi.aliyun.com/specials/promotion/aicampsql；
Summary of learning points:
1. Addition and subtraction of tables union, except
2. Connect the join
Learning content:
1. Table addition union:
SELECT product_id, product_name FROM product UNION --union Keep duplicate lines SELECT product_id, product_name FROM product2; --Use in a query WHERE clause, Then use OR Predicate joins two query conditions, The same results can be obtained.
Although the delivery calculation of sets has appeared in the SQL standard for many years, it is a pity that MySQL still does not support INTERSECT operation as of MySQL version 8.0 MySQL 8.0 does not support the subtraction operator excel However, with the help of the NOT IN predicate learned in Chapter 6, we can also realize table subtraction
Bag is a mathematical structure similar to set. The difference is that duplicate elements are allowed in bag. If the same element is added multiple times, there will be multiple elements in the bag Through the above differences between bag and set definitions, we find that it is more appropriate to use bag model to describe the tables in the database Whether to allow element repetition leads to some differences between set and bag Take the intersection of bags as an example. Since bags allow elements to appear repeatedly, for two bags, their union operation will be as follows: 1 Whether the element has appeared in at least one bag, 2 The maximum number of occurrences of this element in two bags is calculated in terms of { Therefore, for a = {1,1,1,2,3,5,7} and B = {1,1,2,2,4,6,8}, their sum is equal to {1,1,1,2,2,3,4,5,6,7,8}
--use product Table and product2 Table symmetry difference to query which goods are only in one table, The results are similar to: -- 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)
2. Internal connection
Find a common column similar to "axis" or "bridge" and connect the two tables with this column This is what join operations do
----Format: FROM shopproduct AS SP INNER JOINproduct AS P SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.product_type ,P.sale_price ,SP.quantity FROMshopproduct AS SP INNER JOIN product AS P ON SP.product_id = P.product_id; --(WHERE shop_name = 'Tokyo' AND product_type = 'clothes' ;) --Execution sequence: FROM clause->WHERE clause->SELECT clause --Use subquery SELECT SP.shop_id, SP.shop_name, SP.product_id ,P.product_name, P.product_type, P.purchase_price FROM shopproduct AS SP INNER JOIN --from product Table find out the information of clothing products (SELECT product_id, product_name, product_type, purchase_price FROM product WHERE product_type = 'clothes')AS P ON SP.product_id = P.product_id;
3. External connection
The inner join discards the rows in the two tables that do not meet the ON condition. The opposite to the inner join is the outer join The outer link will selectively reserve unmatched rows according to the type of outer link
According to the table in which the reserved row is located, there are three forms of outer links: left, right and all outer links
The left link will save the rows in the left table that cannot be matched according to the ON clause. At this time, the rows corresponding to the right table are all missing values; Right join will save the rows in the right table that cannot be matched according to the ON clause. At this time, the rows corresponding to the left table are all missing values; The full outer join will save the rows in the two tables that cannot be matched according to the ON clause at the same time, and the rows in the corresponding other table will be filled with missing values
(the content will be a little difficult and needs to be carefully understood later)
Learning questions and summary:
This part mainly involves the association between two or more tables. The required contents are selected through the bridge between them. The addition and subtraction method of tables is similar to the intersection and union set in mathematics, as well as various forms of internal and external connections of tables. These are relatively difficult contents, which require a deep grasp of knowledge, understanding and application.