Many times in actual business we don’t just query a table.
In the e-commerce system, query which users have not purchased products.
The bank may query violation records, and at the same time query the user's
Query the winning information and the basic information of the winner.
The above is just a column situation, so we need to query the two tables together.
In the above business, multiple tables need to be joined together to query to get results, and the essence of multi-table joint query is: table connection.
Table connection
When you need to query fields in multiple tables, you can use table connection to achieve it. Table joins are divided into inner joins and outer joins.
Inner join: Join those records whose fields in the two tables have a join relationship that match the join relationship to form a record set.
Outer join: Other unmatched records will be selected and divided into outer left join and outer right join.
Before learning the experiment, I prepared two simulated data tables for everyone:
User table to store user information
Order table, which stores which user purchased which product
user table creation statement
##CREATE TABLE IF NOT EXISTS
user ( uid int(11) NOT NULL, username varchar(30) NOT NULL, password char(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS
order_goods ( oid int(11) NOT NULL, uid int(11) NOT NULL, name varchar(50) NOT NULL, buytime int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user table data is as follows:##uid##1京天1234562 王小二2456673王宝强12355314Jing Boran123455Fan Bingbing黄晓明anglebabyTFBOYS##9An Xiaochao 12tfddwd10Gao Xiaofeng3124qwqw11李小强323fxfvdvd12李小超311aqqee13汉小平121rcfwrfq123123tcsd3cxvdfs
username
password
##5
5abcwa
6
abcdeef
7
caption
8
abcdwww
##14
宋小康
15
Tong Xiaogang
order_goods data is as follows:
##uidnamebuytime110Apple Mouse121231323iphone 12s123121241##3453iphone keyboard12123413##Note: is above The uid in the order_goods table refers to the uid field in the user table. In the above table, the data row with oid is 1 and the user with uid is 10. For the user with uid 10 in the user table: Gao Xiaofeng. The user purchased an Apple mouse. The purchase time buytime is a unix timestamp.
oid
12
Sprite
13232333
15
##34242123
Inner connectionBasic syntax 1:
CategoryDetailed explanationBasic syntaxselect table 1.field [as alias], table n. field from table 1 [alias], table n where condition;select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user,order_goods where user.uid = order_goods.uid ;Query which users in the product table have purchased products and display the user information
Example
Example description
Note: In the following example, the from table uses table aliases.
Because the table name is too long, it is easy to make mistakes every time you write it. We can follow the table directly with an abbreviated English string. When splicing fields earlier, just use the abbreviation string.field.
mysql> select u.uid ,u.username as username,o.oid,o.uid,o.name as shopname from user u,order_goods o where u.uid = o.uid;
+-----+-----------+-----+-----+---------------+
| uid | username | oid | uid | shopname |
+-----+-----------+-----+-----+---------------+
| 10 | Gao Xiaofeng | 1 | 10 | Apple Mouse |
| 3 | Wang Baoqiang | 2 | 3 | iphone 12s |
| 12 | Li Xiaochao | 3 | 12 | Sprite |
| 15 | Tong Xiaogang | 4 | 15 | |
| 3 | Li Wenkai | 5 | 3 | iphone keyboard |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)
Basic syntax 2:
Category
Detailed explanation
Basic syntax
select table 1. field [as alias], table n. field from table 1 INNER JOIN table n on condition;
Example
select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on user.uid = order_goods .uid;
Example description
Query which users in the product table have purchased products and display the user information
The result is consistent with Basic Grammar 1.
mysql> select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on user.uid = order_goods. uid;
+-----+-----------+-----+-----+---------------+
| uid | username | oid | uid | shopname |
+-----+-----------+-----+-----+---------------+
| 10 | Gao Xiaofeng | 1 | 10 | Apple Mouse |
| 3 | Wang Baoqiang | 2 | 3 | iphone 12s |
| 12 | Li Xiaochao | 3 | 12 | Sprite |
| 15 | Tong Xiaogang | 4 | 15 | |
| 3 | Wang Baoqiang | 5 | 3 | iphone keyboard |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)
Outer connection
##ExplanationDetailed explanationBasic syntaxselect table 1. field [as alias], table n. field from table 1 LEFT JOIN table n on conditions;Exampleselect * from user left join order_goods on user.uid = order_goods.uid;Example description Take the left as Mainly, check which users have not purchased goods and display the user information
Outer connections are divided into left connections and right links. The specific definitions are as follows.
Left join: Contains all records in the left table even if there are no matching records in the right table
SubquerySometimes, when we query, the required condition is the result of another select statement, then we need to use a subquery. Keywords used for subqueries include in, not in, =, !=, exists, not exists, etc. CategoryDetailed explanationBasic syntaxselect field from table where field in (condition)Example 1select * from user where uid in (1,3,4);Example 1 DescriptionQuery the specified user according to idExample 2select * from user where uid in ( select uid from order_goods);Example 2 DescriptionDisplay user information that has purchased goods##
Example 1:
mysql> select * from user where uid in (1,3,4);
+-----+-----------+----------+
| uid | username | password |
+-----+-----------+----------+
| 1 | Jing Tian | 123456 |
| 3 | Wang Baoqiang | 1235531 |
| 4 | Jing Boran | 123455 |
+-----+-----------+----------+
3 rows in set (0.00 sec)
Example 2:
mysql> select * from user where uid in (select uid from order_goods) ;
+-----+-----------+----------+
| uid | username | password |
+-----+-----------+----------+
| 10 | Gao Xiaofeng | 3124qwqw |
| 3 | Wang Baoqiang | 1235531 |
| 12 | Li Xiaochao | 311aqqee |
| 15 | Tong Xiaogang | 3cxvdfs |
+-----+-----------+----------+
4 rows in set (0.00 sec)
##mysql> select * from emp where deptno in (select deptno from dept);Record unionUse union and The union all keyword is used to query the data from two tables according to certain query conditions, and then merge the results and display them together. The main difference between the two is that the results are directly merged together, while union is the result of performing a distinct operation on the results after union all, and removing duplicate records.
CategoryDetailed explanationBasic syntaxselect statement 1 union[all] select statement 2Exampleselect * from user where uid in (1,3,4);Example descriptionCombine the results of user information in the product table and user information in the user table
mysql> select uid from user union select uid from order_goods;
The courseware is not available for download at the moment. The staff is currently organizing it. Please pay more attention to this course in the future~
Students who have watched this course are also learning
