MySQL query example for beginners

Last time we introduced: suitableBeginnersMySQL management experience of the MySQL study notes. Here we will introduceMySQL QueryOperations, including function queries and subqueries. Let's take a look at this part.

Function Query

The so-called function query is actually an operation query, which can perform fast and efficient operations on the database. it selects matching data for the purpose of selecting a query and then processes the data in batches. function query includes update query, delete query, add query, and generate Table query.

1. Update Query

The UPDATE clause can change the data in one or more tables at the same time. It can also change the values of multiple fields at the same time.

Update query syntax:

UPDATE table name

SET new value

WHERE Criterion

For example, the order volume of UK customers increased by 5% and the cargo volume increased by 3%.

UPDATE OEDERS

SET OrderAmount = OrderAmount * 1.1

Freight = Freight * 1.03

WHERE ShipCountry = 'U'

2. Delete Query

The DELETE clause allows you to DELETE a large amount of outdated or redundant data.

Note: The query object is deleted as a whole record.

Syntax of the DELETE clause:

DELETE [Table name. *]

FROM source table

WHERE Criterion

Example: delete all Orders 94 years ago

 
 

  
  
DELETE *   
  
  
FROM Orders   
  
  
WHERE OrderData<#94-1-1#   
  
  
 
 
 

3. append Query

The INSERT clause can append one or more records to the end of one or more tables.

The INTO clause specifies the table that accepts the new record.

The valueS keyword specifies the data value included in the new record.

Syntax of the INSERT clause:

Insetr into target table or query (Field 1, Field 2 ,...)

ValueS (value 1, value 2 ,...)

Example: Add a customer

 
 

  
  
INSERT INTO Employees(FirstName,LastName,title)   
  
  
valueS(‘Harry’,’Washington’,’Trainee’)   
  
  
 
 
 

4. Generate a table Query

All matching records can be copied to a new table at a time. A backup or copy of the records is usually created or serves as the basis of the report.

The select into clause is used to create the query syntax for the generated table:

SELECT Field 1, Field 2 ,...

INTO new table [IN external database]

FROM source database

WHERE Criterion

For example, create an archive backup for the order

 
 

  
  
SELECT *   
  
  
INTO OrdersArchive   
  
  
FROM Orders   
  
  
 
 
 

Joint Query

The UNION operation combines the results of multiple queries into a single result set for display.

General Syntax of UNION operations:

[Table] query 1 UNION [ALL] query 2 UNION...

Example: return the names and cities of all suppliers and customers in Brazil

 
 

  
  
SELECT CompanyName,City   
  
  
FROM Suppliers   
  
  
WHERE Country = ‘Brazil’   
  
  
UNION   
  
  
SELECT CompanyName,City   
  
  
FROM Customers   
  
  
WHERE Country = ‘Brazil’   
  
  
 
 
 

Note:

By default, the UNION clause does not return repeated records. to display ALL records, add the ALL option.

The UNION operation requires that the query has the same number of fields. However, the field data types do not have to be the same.

You can use the group by clause or HAVING clause to GROUP each query parameter. to display the returned data in the specified order, you can use the oreer by clause at the end of the last query.

Cross Query

Cross-query can calculate the sum, average, Count, or other sum of data. The data is grouped by two types of information: one is displayed on the left of the table, the other is displayed on the top of the table.

Microsoft Jet SQL uses the TRANSFROM statement to create the cross tabulation query syntax:

TRANSFORM aggfunction

SELECT statement

Group by clause

Struct tfield [IN (value1 [, value2 [,…]) ]

Aggfounction refers to the SQL accumulation function,

SELECT the field as the title in the SELECT statement,

GROUP BY GROUP

Note:

The field or expression used to create a column title IN the query result set. The optional IN clause is used to limit its value.

Value indicates the fixed value of the created column title.

For example, the number of orders received by each employee in each quarter of 1996 is displayed:

 
 

  
  
TRANSFORM Count (OrderID)
  
  
SELECT FirstName & ''& LastName AS FullName
  
  
FROM Employees inner join Orders
  
  
ON Employees. EmployeeID = Orders. EmployeeID
  
  
WHERE DatePart ("yyyy", OrderDate) = '201312'
  
  
Group by FirstName & ''& LastName
  
  
Order by FirstName & ''& LastName
  
  
POVOT DatePart ("q", OrderDate) & 'quarterly'
 
 

Subquery

A subquery can be understood as a set of queries. A subquery is a SELECT statement.

1. Compare the expression value with the single value returned by the subquery.

Syntax:

Expression comparision [ANY | ALL | SOME] (subquery)

Note:

Any and some predicates are synonyms and are used together with comparison operators (=, <, >,< =, >=. returns a Boolean value of True or False. ANY means that the expression is compared with a series of values returned by the subquery one by one. If a comparison produces a True result, ANY tests return a True value (both the result of the WHERE clause ), the current record corresponding to this expression will be included in the results of the primary query. the ALL test requires that the expression and a series of values returned by the subquery produce the True result before returning the True value.

For example, the unit price returned by the primary query is higher than the unit price of any product whose discount is greater than or equal to 25%.

 
 

  
  
SELECT * FROM Products   
  
  
WHERE UnitPrice>ANY   
  
  
(SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25)  
 
 

2. Check whether the expression value matches a value of a group returned by the subquery.

Syntax:

[NOT] IN (subquery)

For example, products whose inventory value is greater than or equal to 1000 are returned.

 
 

  
  
SELECT ProductName FROM Products   
  
  
WHERE ProductID IN   
  
  
(SELECT PrdoctID FROM [Order DEtails]   
  
  
WHERE UnitPrice*Quantity>= 1000)   
 
 

3. Check whether the subquery returns any records.

Syntax:

[NOT] EXISTS (subquery)

Example: Search UK customers with EXISTS

 
 

  
  
SELECT ComPanyName,ContactName   
  
  
FROM Orders   
  
  
WHERE EXISTS   
  
  
(SELECT *   
  
  
FROM Customers   
  
  
WHERE Country = ‘UK’ AND   
  
  
Customers.CustomerID= Orders.CustomerID)  
 
 

The MySQL query example, which is suitable for beginners, is introduced here. I hope this introduction will be helpful to you!