MySQL additions and deletions to the "multi-table joint query"

Many times in the actual business we do not just query a single table.

1. In an e-commerce system, query which users have not purchased a product.

2. The bank may query the violation record, and also query the user's

3. Inquire about the winning information and the basic information of the winning person.

The above is only a column of the situation we need to put two tables together to query.

While the above business requires multiple tables to be combined in a query to have results, and the nature of the multi-table union query is: Table connection.

Table Connection

When you need to query the fields in multiple tables, you can use table joins to implement them. Table joins are divided into inner and outer joins.

1. Inner junction: Those records that have a junction relationship in two tables that conform to the join relationship are those that form the junction of the recordset.

2. Outside connection: Will select other mismatched records, divided into the outer left and outside the right junction.

Before I studied the experiment, I prepared two simulation data sheets for everyone:

1. User tables, storing user information
2. Order form to store which user has purchased which product

User table Creation Statement

CREATE TABLE IF not EXISTS user (
uidInt (one) is not NULL,
usernamevarchar (+) is not NULL,
passwordchar (+) not NULL
) Engine=innodb DEFAULT Charset=utf8;

CREATE TABLE IF not EXISTS order_goods (
oidInt (one) is not NULL,
uidInt (one) is not NULL,
namevarchar (not NULL),
buytimeInt (one) not NULL
) Engine=innodb DEFAULT Charset=utf8;

The user table data is as follows:

UID	username	Password
1	King Sweet	123456
2	Wangxiao	245667
3	Wangbaoqiang	1235531
4	Missy	123455
5	Fan Bing	5abcwa
6	Huang Xiaoming	Abcdeef
7	Anglebaby	Caption
8	Tfboys	Abcdwww
9	Anchou	12tfddwd
10	Gao Xiao	3124qwqw
11	Li Xiaoqiang	323fxfvdvd
12	Xiaochao	311aqqee
13	Hanxiaoping	121rcfwrfq
14	Song well-off	123123tcsd
15	Kang	3cxvdfs

The Order_goods data is as follows:

OID	UID	name	Buytime
1	10	Apple Mouse	1212313
2	3	iphone 12s	123121241
3	12	Snow BI	13232333
4	15		34242123
5	3	iphone keyboard	12123413

Note: the UID in the Order_goods table in the table above refers to the UID field in the user table. The data row for the OID in the table above is 1, and the user with UID 10. For users with UID 10 in the user table: Gao Xiao. The user purchased the product as an Apple mouse. Purchase time Buytime is a Unix timestamp.

Internal connection

Basic syntax One:

category	Detailed Solution
Basic syntax	Select 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 list have purchased the product and display the user information

Note: The From table in the following example uses a table alias.

Because the name of the table is too long, it is easy to write incorrectly each time. We can follow a shorthand English string directly after the table. When stitching fields in front, use shorthand strings directly. 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.u Id
+-----+-----------+-----+-----+---------------+
| UID | Username | OID | UID | Shopname |
+-----+-----------+-----+-----+---------------+
| 10 |   Gao Xiao |  1 | 10 | Apple Mouse |
| 3 |   Wangbaoqiang |   2 | 3 | iphone 12s |
| 12 |   Xiaochao |  3 | 12 | Sprite |
| 15 |   Kang |  4 |               15 | |
| 3 |   Li Wenkei |   5 | 3 | iphone Keyboard |
+-----+-----------+-----+-----+---------------+
5 rows in Set (0.00 sec)

Basic syntax Two:

category	Detailed Solution
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 list have purchased the product and display the user information

The results are consistent with basic syntax 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   & nbsp  |
 +-----+-----------+-----+-----+---------------+
 |  10 | Gao Xiao    |   1 |  10 | Apple Mouse      |
 |   3 | wangbaoqiang    |   2 |   3 | iphone 12s    |
 |  12 | Xiaochao    |   3 |  12 | Sprite          |
 |  15 | Kang    |   4 |  15 |               |
 |   3 | wangbaoqiang    |   5 |   3 | iphone keyboard   |
 +-----+-----------+-----+-----+---------------+
 5 rows in Set (0.00 sec)

External connection

Description	detailed
Basic syntax	Select table 1. field [as Alias], table N. field from table 1 left JOIN table n on condition;
Example	SELECT * from the user left join order_goods on user.uid = Order_goods.uid;
Example description	Main to the left, query which users have not purchased the product, and display the user information

The outer joins are divided into left and right links, which are defined as follows.

Left join: Contains all the records in the left table or even the records that do not match it in the right table

Mysql> SELECT * from the user left join order_goods on user.uid = Order_goods.uid;
+-----+-----------+------------+------+------+---------------+-----------+
| UID | Username | password | OID | UID | name | Buytime |
+-----+-----------+------------+------+------+---------------+-----------+
| 10 | Gao Xiao |    3124QWQW |   1 | 10 |   Apple Mouse | 1212313 |
| 3 | Wangbaoqiang |    1235531 |    2 | 3 | iphone 12s | 123121241 |
| 12 | Xiaochao |    311aqqee |   3 | 12 |  Sprite | 13232333 |
| 15 | Kang |    3cxvdfs |   4 |               15 |  | 34242123 |
| 3 | Wangbaoqiang |    1235531 |    5 | 3 |  iphone Keyboard | 12123413 |
| 1 | Jing-Sweet | 123456 | NULL | NULL |      NULL | NULL |
| 2 | Wangxiao | 245667 | NULL | NULL |      NULL | NULL |
| 4 | Missy | 123455 | NULL | NULL |      NULL | NULL |
| 5 | Fan Bingbing | 5ABCWA | NULL | NULL |      NULL | NULL |
| 6 | Huang Xiaoming | Abcdeef | NULL | NULL |      NULL | NULL |
| 7 | Anglebaby | Caption | NULL | NULL |      NULL | NULL |
| 8 | Tfboys | abcdwww | NULL | NULL |      NULL | NULL |
| 9 | Anchou | 12TFDDWD | NULL | NULL |      NULL | NULL |
| 11 | Lee Qiang | 323fxfvdvd | NULL | NULL |      NULL | NULL |
| 13 | Hanxiaoping | 121RCFWRFQ | NULL | NULL |      NULL | NULL |
| 14 | Song-well-off | 123123TCSD | NULL | NULL |      NULL | NULL |
+-----+-----------+------------+------+------+---------------+-----------+
Rows in Set (0.00 sec)

Right connection: Contains all the records in the right-hand table, even those that do not match it in the right table

category	Detailed Solution
Basic syntax	Select table 1. field [as Alias], table N. field from table 1 right JOIN table n on condition;
Example	SELECT * from the user right join order_goods on user.uid = Order_goods.uid;
Example description	Query which users in the product list have purchased the product and display the user information

Mysql> SELECT * from the user right joins order_goods on user.uid = Order_goods.uid;
+------+-----------+----------+-----+-----+---------------+-----------+
| UID | Username | password | OID | UID | name | Buytime |
+------+-----------+----------+-----+-----+---------------+-----------+
| 10 | Gao Xiao |   3124QWQW |  1 | 10 |   Apple Mouse | 1212313 |
| 3 | Wangbaoqiang |   1235531 |   2 | 3 | iphone 12s | 123121241 |
| 12 | Xiaochao |   311aqqee |  3 | 12 |  Sprite | 13232333 |
| 15 | Kang |   3cxvdfs |  4 |               15 |  | 34242123 |
| 3 | Wangbaoqiang |   1235531 |   5 | 3 |  iphone Keyboard | 12123413 |
+------+-----------+----------+-----+-----+---------------+-----------+
5 rows in Set (0.00 sec)

Sub-query

Sometimes, when we query, the condition that we need is the result of another SELECT statement, then we need to use a subquery. The keywords used for subqueries include in, not in, =,! =, exists, not exists, and so on.

category	Detailed Solution
Basic syntax	Select field from table where field in (conditional)
Example 1	SELECT * from the user where UID in (1,3,4);
Example 1 illustrates	Query the specified user by ID
Example 2	SELECT * from the user where UID in (select UID from order_goods);
Example 2 illustrates	Display the user information of the purchased item

Example 1:

Mysql> SELECT * from the user where UID in (1,3,4);
+-----+-----------+----------+
| UID | Username | password |
+-----+-----------+----------+
| 1 | Jing-Sweet | 123456 |
| 3 | Wangbaoqiang | 1235531 |
| 4 | Missy | 123455 |
+-----+-----------+----------+
3 Rows in Set (0.00 sec)

Example 2:

Mysql> SELECT * from the user where UID in (select UID from order_goods);
+-----+-----------+----------+
| UID | Username | password |
+-----+-----------+----------+
| 10 | Gao Xiao | 3124QWQW |
| 3 | Wangbaoqiang | 1235531 |
| 12 | Xiaochao | 311aqqee |
| 15 | Kang | 3cxvdfs |
+-----+-----------+----------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from emp where deptno in (select Deptno from dept);

Record Union

Using the Union and UNION ALL keywords, the results of the two tables are combined to display when they are queried by a certain query condition. The main difference between the two is that the results are merged directly together, and the Union is a distinct of the result after the union all, eliminating the result of duplicate records.

category	Detailed Solution
Basic syntax	SELECT statement 1 Union[all] SELECT statement 2
Example	SELECT * from the user where UID in (1,3,4);
Example description	Combine user information in a product table with the results of user information in a user table

Mysql> Select UID from the user union select UID from Order_goods;
+-----+
| UID |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+-----+
Rows in Set (0.00 sec)

Transferred from: http://www.php.cn/code/4708.html

MySQL additions and deletions to the "multi-table joint query"