MySQL database advanced query and multi-table query

MySQL multi-table query

Add a worksheet

-- User table (user)
CREATE TABLE `user`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User id (primary key)',
	`username` VARCHAR(50) COMMENT 'User name', 
	`age` CHAR(3) COMMENT 'User age'
);

-- Orders
CREATE TABLE `orders`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Order ID (primary key)',
	`price` DOUBLE COMMENT 'Order price',
	`user_id` INT COMMENT 'User id (foreign key)'
);

-- Add a foreign key to an existing table. The syntax is as follows: alter table table name add constraint [foreign key name] foreign key (foreign key field) references parent table (primary key field);
ALTER TABLE orders ADD CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES `user` (id);

-- Add data to the user table INSERT INTO USER VALUES(1,'第一',11);
INSERT INTO USER VALUES(2,'小等',12);
INSERT INTO USER VALUES(3,'张三',33);
INSERT INTO USER VALUES(4,'李四',24);
INSERT INTO USER VALUES(5,'王五',17);
INSERT INTO USER VALUES(6,'赵六',36);
INSERT INTO USER VALUES(7,'七七',18);
INSERT INTO USER VALUES(8,'粑粑',NULL);

-- Insert data into the orders table INSERT INTO orders VALUES(111,1314,3);
INSERT INTO orders VALUES(112,122,3);
INSERT INTO orders VALUES(113,15,4);
INSERT INTO orders VALUES(114,315,5);
INSERT INTO orders VALUES(115,1014,NULL);
INSERT INTO orders VALUES(116,666,6);
INSERT INTO orders VALUES(117,1111,1);
INSERT INTO orders VALUES(118,8888,NULL);

Cartesian Product

• In mathematics, the Cartesian product, also known as the direct product, of two sets X and Y is expressed as X × Y, where the first object is a member of X and the second object is a member of all possible ordered pairs of Y. (Cartesian product) Baidu Encyclopedia
• What is the Cartesian product?

SELECT * FROM `user`,`orders`;

• The data found in the figure above is of no use to us programmers.
• So how do we eliminate the Cartesian product? Primary and foreign key constraints are required to remove duplicate data.

SELECT * FROM `user` AS u,`orders` AS o WHERE u.`id`=o.`user_id`;

1. Inner Join

1.1 Implicit inner join

• Multiple table names appear directly after from, which is an implicit inner join
• select * from table a, table b where a.id = b.a_id;

SELECT * FROM `user` AS u,`orders` AS o WHERE u.`id`=o.`user_id`;

1.2 Display inner join (recommended)

• Use inner join to link tables, followed by on and conditions. (inner can be omitted)
• select * from table a inner join table b on a.id = b.a_id;
• Query adult user and order data;

SELECT * FROM `user` u JOIN `orders` o ON u.`id`=o.`user_id` WHERE age >= 18;

2. Outer Join

• External links can display all data of a single table, including null;

2.1 Right external links

• Display all data in the table on the right
• Use right outer join to link the tables, followed by on and conditions. (outer can be omitted)
• select * from table a right outer join table b on a.id=b.a_id;

SELECT * FROM `user` u RIGHT JOIN `orders` o ON u.`id`=o.`user_id`;

Left table data (user)

The data in the right table (orders)

2.2 Left outer link (recommended)

• Display all data in the left table
• Use left outer join to link tables, followed by on and conditions. (outer can be omitted)
• select * from table a left outer join table b on a.id=b.a_id;

SELECT * FROM `user` u LEFT JOIN `orders` o ON u.`id`=o.`user_id`;

Left table data (user)

The data in the right table (orders)

3. Subquery

• Subqueries, nested feeling. The query results are used as conditions for another query.
• Query the order data of the oldest user

SELECT * FROM orders o WHERE o.`user_id` IN (
	SELECT u.`id` FROM `user` u WHERE u.`age` IN (
		SELECT MAX(u.`age`) FROM `user` u
	)
);

4. Full connection (not supported by MySQL)

• Full connection, all data in the left and right tables including null. Equivalent to the combination of right outer link and left outer link.
• select * from table a full outer join table b on a.id=b.a_id; (MySQL does not support this and will not be demonstrated)

For other MySQL articles, please see the following links

MySQL DDL statements

MySQL CRUD statements

MySQL Aggregate Functions

MySQL multi-table query

END…

This is the end of this article about advanced query and multi-table query of MySQL database. For more relevant MySQL advanced query and multi-table query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!