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
username
password
##1
京天
123456
2
王小二
245667
3
王宝强
1235531
4
Jing Boran
123455
##5
Fan Bingbing
5abcwa
6
黄晓明
abcdeef
7
anglebaby
caption
8
TFBOYS
abcdwww
##9
An Xiaochao
12tfddwd
10
Gao Xiaofeng
3124qwqw
11
李小强
323fxfvdvd
12
李小超
311aqqee
13
汉小平
121rcfwrfq
##14
宋小康
123123tcsd
15
Tong Xiaogang
3cxvdfs
order_goods data is as follows:
oid
##uid
name
buytime
1
10
Apple Mouse
1212313
2
3
iphone 12s
123121241
##3
12
Sprite
13232333
4
15
##34242123
5
3
iphone keyboard
12123413
##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.
Inner connectionBasic syntax 1:
Category
Detailed explanation
Basic syntaxselect table 1.field [as alias], table n. field from table 1 [alias], table n where condition;
Example
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 ;
Example description
Query which users in the product table have purchased products and display the user information
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
##Explanation
Detailed explanation
Basic syntax
select table 1. field [as alias], table n. field from table 1 LEFT JOIN table n on conditions;
Example
select * 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.
Category
Detailed explanation
Basic syntax
select field from table where field in (condition)
Example 1
select * from user where uid in (1,3,4);
Example 1 Description
Query the specified user according to id
Example 2
select * from user where uid in ( select uid from order_goods);
Example 2 Description
Display 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.
Category
Detailed explanation
Basic syntax
select statement 1 union[all] select statement 2
Example
select * from user where uid in (1,3,4);
Example description
Combine 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
![Front-end project-Shangyou [HTML/CSS/JS technology comprehensive practice]](https://img.php.cn/upload/course/000/000/068/63bfcb2974f91471.jpg)
![Zero-based front-end course [Vue advanced learning and practical application]](https://img.php.cn/upload/course/000/000/068/63bbd15353895588.jpg)
![WEB front-end tutorial [HTML5+CSS3+JS]](https://img.php.cn/upload/course/000/000/068/63ba60c8da520536.jpg)
![Apipost practical application [api, interface, automated testing, mock]](https://img.php.cn/upload/course/000/000/068/63b3c967a20f4861.jpg)
