What kinds of MYSQL connection queries do you know?

Preface

If the query information comes from multiple tables, these tables must be joined for query. Join is the most common method to connect records from different tables. Through join query, multiple tables can be processed as one table. Join queries are divided into inner joins and outer joins.

1. Cross-connect

A cross join, also known as a "Cartesian join" or "cross product", is the basis of all types of inner joins.

Example: To implement the route table and vehicle table, the statements are as follows:

`select * from line CROSS JOIN vehicle is equivalent to: select * from line, vehicle

Second, inner join

Inner join is a very common join operation in applications and is generally the default join type.

The syntax is as follows:

SELECT fieldlist FROM table1 【INNER】JOIN table2 ON 
table1.column1=table2.column2 【where condition】

3. Natural Connection

A natural join is a special inner join that requires that the basis columns of the two connected tables must be the same fields (with the same field names and the same field attributes).
The syntax is as follows:

select fieldlist FROM table1 NATURAL JOIN table2 【where condition】

4. Multi-table join query

If the information to be queried comes from multiple tables, a multi-table join query can be established by connecting two tables in reverse order.

The three-table join query syntax is as follows:

SELECT fieldlist FROM table1 JOIN table2 ON 
tabke1.column1=table.column2_1 JOIN table3 on
 table2.column2_2=table.column3【where condition】

If you directly list all the tables to be connected in the FROM clause and then specify the connection conditions in the WHERE clause, this is a simple multi-table query, which has the same function as an inner join.

The query syntax for joining two tables is as follows:

SELECT fieldlist FROM table1, table2
HERE table1.column1=table2.column2 [and other conditions]

Five, outer connection

In an inner join query, only records that meet the join conditions appear in the query results. However, in actual use, if the records that do not meet the connection conditions also appear in the query results, an outer join query is required. Outer joins are divided into left outer joins and right outer joins.

The syntax is as follows:

`SELECT field name FROM table name 1 LEFT|RIGHT|FULL [OUTER] JOIN table name 2 ON table name 1. field name 1 = table name 2. field name 2`

1. Left outer join

The result set of the outer join includes all records in the left table and the records in the right table that meet the join conditions. The column values ​​from the table that do not meet the join conditions in the result set are nullo.

2. Right outer join

A right outer join is the reverse of a left outer join. The result set of a right outer join includes all records in the right table and the records in the left table that meet the join condition. The column values ​​from the left table that do not meet the join condition are nullo.

Operation:

Get the license plate number, model and driver name of all non-air-conditioned vehicles, the route number of the route they belong to, the starting station and the terminal station information;

SELECT
	v.plateNO license plate number,
	v.model model,
	d. NAME driver's name,
	l.lineNo line number,
	l.from_station starting station,
	l.end_station terminal station FROM
	line l,
	vehicle v,
	driver
WHERE
	v.type = 'Non-air-conditioned car'
AND l.lineID = v.lineID
AND d.driverID = v.driverID;

Get all the driver information of Bus Company 2. Requires output of driver's name, ID card, gender and phone number;

SELECT
	NAME Driver's name,
	licenseNO ID card,
	d.gender,
	phone FROM
	vehicle v,
	driver d,
	line l
WHERE
	v.driverID = d.driverID
AND v.lineID = l.lineID
AND company = 'Bus Company 2'
GROUP BY 

Check the license plate number, model, route number, starting station and terminal station of all non-air-conditioned vehicles;

SELECT
v.plateNO license plate number,
	v.model model,
	l.lineNo line number,
	l.from_station starting station,
	l.end_station terminal station FROM
	line l,
	vehicle v
WHERE
	v.type = 'Non-air-conditioned car'
AND l.lineID = v.lineID;

Display the basic information of all routes and their assigned vehicles, and query the relevant information of the drivers assigned to the vehicles, requiring the output of the driver's name, gender, phone number, license plate number, model, route number, starting station and terminal station;

	SELECT
	d.NAME driver's name,
	licenseNO ID card,
	d.gender,
	phone,
	v.plateNO license plate number,
	v.model model,
	l.lineNo line number,
	l.from_station starting station,
	l.end_station terminal station FROM
	vehicle v,
	driver d,
	line l
WHERE
	v.driverID = d.driverID
AND v.lineID = l.lineID
GROUP BY driver_name;

6. Get the order details of the order ID 4, and output the product name, unit price and quantity.

SELECT
	c.cName customer name,
	o.ordersDate order date,
	g.goodsName product name,
	g.unitPrice unit price,
	od.quantity number of pieces FROM
	ordersdetail od,
	goods g,
	orders o,
	customer c
WHERE
	c.cName = 'Wang Chuanhua'
AND c.customerID = o.customerID
AND o.ordersID = od.ordersID
AND od.goodsID = g.goodsID;

Get the order details of the customer "Wang Chuanhua", requiring to display the customer name, order ID, order date, product name, unit price and number of pieces.

SELECT
	c.cName customer name,
	o.ordersDate order date,
	g.goodsName product name,
	g.unitPrice unit price,
	od.quantity number of pieces FROM
	ordersdetail od,
	goods g,
	orders o,
	customer c
WHERE
	c.cName = 'Wang Chuanhua'
AND c.customerID = o.customerID
AND o.ordersID = od.ordersID
AND od.goodsID = g.goodsID;

Use the left connection to obtain the basic information and order information of all customers, requiring the output of customer name, phone number, order ID and order time.

SELECT
	c.cName customer name,
	c.phone,
	o.ordersID Order ID,
	o.ordersDate Order time FROM
	 customer c
LEFT JOIN orders o on c.customerID=o.customerID;

8. Use the right link to obtain the basic information and order information of all customers, requiring the output of customer name, phone number, order ID and order time.

SELECT
	c.cName customer name,
	c.phone,
	o.ordersID Order ID,
	o.ordersDate Order time FROM
	orders
right JOIN customer c on c.customerID=o.customerID;

Summarize

This is the end of this article about MYSQL connection query. For more relevant MYSQL connection query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!