How to Use the order by clause of MySQL learning notes for beginners

Last time we introduced: Suitable for beginnersMySQLFor more information about the usage of SELECT statements for learning notes, we will introduce some suitable MySQL learning notes for beginners.Order by clauseNext, let's take a look at this part.

Sort results by order by clause

The order by clause sorts the query results BY one or more up to 16 fields, which can be ASC (Ascending ORDER) or DESC (descending ORDER). The default value is ascending. The ORDER clause is usually placed at the end of an SQL statement. Multiple fields are defined in the ORDER clause.

Example:

 
 

  
  
SELECT ProductName,UnitPrice, UnitInStock   
  
  
FROM Products   
  
  
ORDER BY UnitInStock DESC , UnitPrice DESC, ProductName  
 
 

In the order by clause, you can replace the field name with the position number in the field selection list. You can mix the field name and position number.

For example, the following statement produces the same effect as the preceding one.

 
 

  
  
SELECT ProductName,UnitPrice, UnitInStock   
  
  
FROM Products   
  
  
ORDER BY 1 DESC , 2 DESC,3 
 
 

Multi-Table query using connection relationships

Example: Find the names of suppliers and customers in the same city

 
 

  
  
SELECT Customers.CompanyName, Suppliers.ComPany.Name   
  
  
FROM Customers, Suppliers   
  
  
WHERE Customers.City=Suppliers.City  
 
 

For example, find out the products and orders with the product inventory greater than the order quantity of the same product.

 
 

  
  
SELECT ProductName,OrderID, UnitInStock, Quantity   
  
  
FROM Products, [Order Deails]   
  
  
WHERE Product.productID=[Order Details].ProductID   
  
  
AND UnitsInStock>Quantity  
 
 

Another method is to use the jnner join exclusive to Microsof jet SQL.

Syntax:

 
 

  
  
FROM table1 INNER JOIN table2   
  
  
ON table1.field1 comparision table2.field2  
 
 

Comparision is the comparison operator used by the WHERE clause.

 
 

  
  
SELECT FirstName,lastName,OrderID,CustomerID,OrderDate   
  
  
FROM Employees   
  
  
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID 
 
 

Note:
Inner join cannot be used to connect Memo OLE Object Single Double data type fields.

JOIN multiple ON clauses in a JOIN statement. Syntax:

 
 

  
  
SELECT fields   
  
  
FROM table1 INNER JOIN table2   
  
  
ON table1.field1 compopr table2.field1 AND   
  
  
ON table1.field2 compopr table2.field2 OR   
  
  
ON table1.field3 compopr table2.field3  
 
 

Yes.

 
 

  
  
SELECT fields   
  
  
FROM table1 INNER JOIN   
  
  
table2 INNER JOIN [( ]table3   
  
  
[INNER JOER] [( ]tablex[INNER JOIN]   
  
  
ON table1.field1 compopr table2.field1   
  
  
ON table1.field2 compopr table2.field2   
  
  
ON table1.field3 compopr table2.field3  
 
 

External connections return more records and keep unmatched records in the results. No matter there are no records that meet the conditions, all records on the other side are returned.

 
 

  
  
FROM table [LEFT|RIGHT]JOIN table2   
  
  
ON table1.field1comparision table.field2 
 
 

Use the left join to create an external join. All the data in the table on the left of the expression is displayed.

For example, all products are returned regardless of the order quantity.

 
 

  
  
SELECT ProductName ,OrderID   
  
  
FROM Products   
  
  
LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID  
 
 

The difference between the right join and the left join is that, no matter whether there are matching records in the left table, it returns all records from the left table.

For example, if you want to know the customer information and calculate the customer distribution in each region, you can use a right connection to return the customer information even if there are no customers in a region.
NULL values do not match each other. You can use an external connection to test whether the fields in a joined table have null values.

 
 

  
  
SELECT *   
  
  
FROM talbe1   
  
  
LEFT JOIN table2 ON table1.a=table2.c  
 
 

Grouping and summarizing query results

In SQL syntax, the GROUP BY and HAVING clauses are used to summarize data.Group by clauseSpecifies which fields are grouped. After grouping records, use the HAVING clause to filter these records.

Syntax of the group by clause:

 
 

  
  
SELECT fidldlist   
  
  
FROM table   
  
  
WHERE criteria   
  
  
[GROUP BY groupfieldlist [HAVING groupcriteria]]  
 
 

Note: Microsoft Jet Database cannot group remarks or OLE object fields. The Null Value in the group by field is used for grouping, but cannot be ignored. No Null value is calculated in any SQL aggregate function. A group by clause can contain up to ten fields, and the sorting priority is arranged from left to right.

For example, After grouping by title in the employee table of the 'wa 'region, find all titles with the same title with more than 1 employee.

 
 

  
  
SELECT Title ,Count(Title) as Total   
  
  
FROM Employees   
  
  
WHERE Region = ‘WA’   
  
  
GROUP BY Title   
  
  
HAVING Count(Title)>1 
 
 

This article introduces how to use the order by clause of MySQL learning notes for beginners. I hope this article will be helpful to you!