SQL must know--------wildcards, calculated fields, functions

1.LIKE operator

1.1 Percent percent (%) wildcard character

SELECT prod_id, Prod_name from Products WHERE prod_name like ' Fish% '

This example uses the search mode ' fish% '. When the sentence is executed, any word that begins with fish will be retrieved. % tells the DBMS to accept any character after fish, regardless of how many characters it has.

Description: Case Sensitive
Depending on the DBMS and its configuration, the search can be case-sensitive. If case-sensitive, the ' fish% ' does not match the fish bean bag toy.

Wildcards can be used anywhere in the search pattern, and multiple wildcard characters can be used. The following example uses two wildcard characters, which are located at both ends of the pattern:
SELECT prod_id, prod_name from products WHERE prod_name like '%bean bag% '

It is important to note that, in addition to matching one or more characters,% can match 0 characters. % represents 0 ,1, or more characters at a given position in the search pattern.

WARNING: please note null
The wildcard% looks like it can match anything, but with an exception, this is null. The clause where prod_name like '% ' does not match the line where the product name is NULL.

1.2 Underline (_) wildcard characters

SELECT prod_id, Prod_name from Products WHERE prod_name like '__ inch teddy Bear '

The underscore is used in the same way as%, but it matches only a single character, not more than one character.

1.3 square brackets ([]) wildcard characters

The square brackets ([]) wildcard is used to specify a character set, which must match one character of the specified position (the position of the wildcard character).

Description: Collection is not always supported
Unlike the wildcard characters described earlier, not all DBMS support the [] that is used to create the collection. Only Microsoft's Access and SQL Server support collections. To determine whether the DBMS you are using supports collections, refer to the appropriate documentation.

SELECT Cust_contact
From Customers
WHERE cust_contact like ' [jm]% '
ORDER by Cust_contact;

This wildcard can be negated with a prefix character ^ (caret).

2. Tips for using wildcard characters

As you can see, a wildcard for SQL is useful. However, this functionality comes at a cost, which is that wildcard searches typically take longer to process than other searches discussed earlier. Here are some tips to keep in mind when using wildcard characters.

• Do not use wildcard characters excessively. If other operators can achieve the same purpose, other operators should be used.
• When you do need to use wildcards, try not to use them at the beginning of the search pattern. The wildcard character is placed at the beginning and the search is the slowest.
• Pay close attention to the position of the wildcard character. If misplaced, you may not return the data you want.

In short, wildcards are an extremely important and useful search tool that we often use in the future.

3. Create a calculated field

3.1 Stitching Fields

MySQL Syntax:

SELECT Concat (Vend_name, ' (', Vend_country, ') ' as Vend_title from vendors
ORDER by Vend_name;

Concat () is mainly to remove extra spaces.

The

select statement itself is the same as previously used, except that the computed field here follows the text as Vend_title. It instructs sql Description: as usually optional
In many dbmsas keyword is optional, but it's best to use it, which is considered a best practice.
Tip: Other uses for aliases
aliases have other uses. Common uses include renaming an actual table column name when it contains an illegal character, such as a space, and expanding it when the original name is ambiguous or misleading.
Warning: an alias
alias can be either a word or a string. In the latter case, the string should be enclosed in quotation marks. Although this practice is legal, it is not recommended to do so. Multi-word names are highly readable, but can cause problems for client applications. Therefore, the most common use of aliases is to rename the column names of multiple words to a single word name.
Description: The export column
alias is sometimes also referred to as an export column (derived column

3.2 Performing arithmetic calculations

Another common use of calculated fields is to perform arithmetic calculations on the retrieved data.

SELECT Order_num,
PROD_ID,
Quantity
Item_price,
quantity*item_price as Expanded_price
from OrderItems
WHERE order_num = 20008;

The Expanded_price column shown in the output is a calculated field, which is calculated as Quantity*item_price.

Tip: How to test a calculation
The SELECT statement provides a good way to test, validate, and evaluate functions. Although select is typically used to retrieve data from a table, the FROM clause is omitted to simply access and manipulate the expression, such as Select 3 * 2; return 6, select Trim (' abc '); return abc,select now (); Use the now () function to return the current date and time. Now you understand that you can use the SELECT statement to test as needed.

4. Using functions

4.1 Text Processing data

All letters become uppercase:

SELECT vend_name, UPPER (vend_name) as Vend_name_upcase
from Vendors
ORDER by Vend_name;

As you can see, UPPER () converts the text to uppercase, so each vendor in this example is listed two times, the first is the value stored in the Vendors table, and the second time is converted to uppercase as a column vend_name_upcase.

4.2 Date and time processing functions

This is a big difference in different databases.

MySQL and MariaDB have various date processing functions, but no datepart (). MySQL and MariaDB users can use a function named year () to extract the years from the date:
Input:
SELECT Order_num
from Orders
WHERE Year (order_date) = 2012;

4.3 Numeric processing functions

The numeric functions are almost the same, and the document can be resolved by itself.

Today's filial piety
Source: http://www.cnblogs.com/jinxiao-pu/p/6817187.html
This article is copyrighted by the author and the blog Park, Welcome to reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to the original link.

Feel good on the point of a recommendation!

SQL must know--------wildcards, calculated fields, functions