Explain how to use SELECT statements for MySQL learning notes for beginners

Last time we introduced: Suitable for beginnersMySQLTake notes for MySQL commands.SELECT statementNext, let's take a look at this part.

The complete syntax of the SELECT statement is:

 
 

  
  
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]   
  
  
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}   
  
  
FROM tableexpression[,…][IN externaldatabase]   
  
  
[WHERE…]   
  
  
[GROUP BY…]   
  
  
[HAVING…]   
  
  
[ORDER BY…]   
  
  
[WITH OWNERACCESS OPTION] 
 
 

Note:The Section enclosed by braces ([]) is optional. The section enclosed by braces ({}) indicates that one of them must be selected.

1.FROM clause

The FROM clause specifies the field source in the SELECT statement. The FROM clause is followed by one or more expressions (separated by commas ), the expression can be a single table name, saved query, or compound result obtained by inner join, left join, or right join. If a table or query is stored IN an external database, specify its complete path after the IN clause.

For example, the following SQL statement returns all customers with orders:

 
 

  
  
SELECT OrderID,Customer.customerID   
  
  
FROM Orders Customers   
  
  
WHERE Orders.CustomerID=Customers.CustomeersID 
 
 

2. ALL, DISTINCT, DISTINCTROW, and TOP predicates

(1) ALL returns ALL records that meet the SQL statement conditions. If this predicate is not specified, the default value is ALL. For example:

 
 

  
  
SELECT ALL FirstName,LastName   
  
  
FROM Employees  
 
 

(2) DISTINCT if the selected fields of multiple records have the same data, only one is returned.

(3) If DISTINCTROW has repeated records, only one is returned.

(4) TOP displays several records at the beginning and end of the query. You can also return the percentage of the record, which is to use the top n percent clause where N represents the percentage ).

For example, the maximum order amount of 5% is returned.

 
 

  
  
SELECT TOP 5 PERCENT*   
  
  
FROM [ Order Details]   
  
  
ORDER BY UnitPrice*Quantity*(1-Discount) DESC 
 
 

3. Use the AS clause to get an alias for a field

If you want to get a new title for the returned column, or after calculating or summarizing the field, a new value is generated and you want to display it in a new column, it is retained with.

For example, if the returned FirstName field is named nick name

 
 

  
  
SELECT FirstName AS NickName ,LastName ,City   
  
  
FROM Employees  
 
 

For example, return a new column to show the inventory value.

 
 

  
  
SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock   
  
  
FROM Products 
 
 

WHERE clause specifies query Conditions

For example, return the order from January 1, January.

 
 

  
  
SELECT OrderID, CustomerID, OrderDate   
  
  
FROM Orders   
  
  
WHERE OrderDate>#1/1/96# AND OrderDate<#1/30/96#  
 
 

Note:

In Mcirosoft jet SQL, dates are bounded. The date can also be replaced by the Datevalue () function. When comparing character data, you must add single quotation marks ''. Trailing spaces are ignored during comparison. Example: WHERE OrderDate> #96-1-1 #
It can also be expressed as: WHERE OrderDate> Datevalue ('2017/96 ')

Use the NOT expression to reverse:

For example, view the order after January 1, January 1: WHERE Not OrderDate <= #1/1/96 #

Range: BETWEEN and not ):

BETWEEN... AND... The operator specifies a closed interval to be searched.

For example, return the order "WHERE OrderDate Between # January/96 # And # February/96 #" Between 1/1 And 2/1 #

List IN, not in ):

The IN operator is used to match any value IN the list. An IN clause can replace a series of conditions connected by an OR clause.

For example, you need to find all customers who live in London, Paris, or Berlin.

 
 

  
  
SELECT CustomerID, CompanyName, ContactName, City   
  
  
FROM Customers   
  
  
WHERE City In(‘London’,’ Paris’,’ Berlin’)  
 
 

Pattern Matching (LIKE)

The LIKE operator checks whether the value of a field containing string data matches a specified pattern.

Wildcard characters used in the LIKE Operator

Wildcard characters:

• ? Any single character
• * Any length of Characters
• #0 ~ A single number between 9
• [Character List] any value in the Character List
• [! Character List] any value not in the Character List
• -Specify the character range. The upper and lower limits are the values on both sides.

For example, return the customer with the zip code between 171) 555-0000 to 171) 555-9999.

 
 

  
  
SELECT CustomerID ,CompanyName,City,Phone   
  
  
FROM Customers   
  
  
WHERE Phone Like ‘(171)555-#### 
 
 

Here is an introduction to the use of SELECT statements for MySQL learning notes for beginners. I hope this introduction will be helpful to you!