SQL is required and SQL is required

SQL is required and SQL is required
1. LIKE Operator

1.1Percent sign (%) Wildcard

SELECTProd_id, prod_nameFROMProductsWHEREProd_nameLIKE'Fish%'

The search mode 'fish % 'is used in this example '. When this clause is executed, any words starting with Fish will be retrieved. % Tells the DBMS to accept any character after the Fish, no matter how many characters it has.

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

Wildcards can be used anywhere in the search mode, and multiple wildcards can be used. The following example uses two wildcards located at both ends of the pattern:
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '% bean bag %'

Note that, in addition to matching one or more characters, % can also match 0 characters. % Represents 0, 1, or multiple characters at a given position in the search mode.

Warning NULL
Wildcard % looks like it can match anything, but with an exception, this is NULL. Clause WHERE prod_name LIKE '%' does not match the row whose product name is NULL.

1.2 underline (_) wildcard

SELECTProd_id, prod_nameFROMProductsWHEREProd_nameLIKE'__Inch teddy bear'

The purpose of the underline is the same as that of %, but it only matches a single character, not multiple characters.

1.3 square brackets ([]) wildcard

The square brackets ([]) wildcard is used to specify a character set. It must match a character at a specified position (the location of the wildcard.

Note: collections are not always supported.
Unlike the wildcards described above, not all DBMS support [] used to create a set. Only Microsoft's Access and SQL Server support the set. To determine whether the DBMS you are using supports a set, see the relevant documentation.

SELECT cust_contact
FROM MERs
WHERE cust_contact LIKE '[JM] %'
Order by cust_contact;

You can use the prefix character ^ (out of font size) to deny this wildcard.

2. Tips for using wildcards

As you can see, the SQL wildcard is very useful. However, this feature is costly, that is, wildcard search generally takes longer processing time than other searches discussed earlier. Here are some tips to remember when using wildcards.

• Do not over-use wildcards. If other operators can achieve the same purpose, use other operators.
• When you do need wildcards, try not to use them at the beginning of the search mode. Placing the wildcard at the beginning is the slowest search.
• Pay attention to the location of the wildcard. If the error is put, the desired data may not be returned.

In short, wildcard is an extremely important and useful search tool, which will be used frequently in the future.

3. Create a calculated Field

3.1 splice Fields

Mysql Syntax:

SELECT Concat(Vend_name, '(', vend_country ,')'ASVend_titleFROMVendors
ORDERVend_name;

Concat () removes unnecessary spaces.

The SELECT statement itself is the same AS previously used, except that the calculated field is followed by the text AS vend_title. It instructs SQL to create a calculated field named vend_title that contains the specified calculation result. As you can see from the output, the results are the same as before, but now the column name is vend_title. Any client application can reference this column by name, just as it is an actual table column.
Note:ASOptional
In many DBMS, The AS keyword is optional, but it is best to use it, which is considered a best practice.
Tip: other functions of the alias
Aliases have other purposes. Common purposes include Renaming an actual table column name when it contains invalid characters (such as spaces) and extending it when the original name is ambiguous or misunderstood.
Warning alias
An alias can be either a word or a string. If it is the latter, the string should be enclosed in quotation marks. Although this method is valid, it is not recommended to do so. The multi-word name is highly readable, but it may cause various problems to the client application. Therefore, the most common use of aliases is to rename the names of multiple words to the names of a word.
Description: Export Columns
Aliases are also known as derived columns. Whatever they are, they represent the same thing.

3.2 perform arithmetic calculation

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

SELECTOrder_num,
Prod_id,
Quantity,
Item_price,
Quantity * item_priceASExpanded_price
FROMOrderItems
WHEREOrder_num= 20008;

The expanded_price column displayed in the output is a calculated field, which is calculated as quantity * item_price.

 

Tip: how to test computing
The SELECT statement provides a good way to test, test, and calculate functions. Although SELECT is usually used to retrieve data FROM a table, after the FROM clause is omitted, it simply accesses and processes the expression. For example, SELECT 3*2; 6 is returned, SELECT Trim ('abc'); returns abc, SELECT Now (); returns the current date and time using the Now () function. Now you understand that you can use the SELECT statement for testing as needed.

4. Use Functions

4.1 Text Processing Data

All letters are capitalized:

SELECTVend_name,UPPER (Vend_name)Vend_name_upcase
FROMVendors
ORDERVend_name;

 

As you can see, UPPER () converts the text to uppercase. Therefore, every vendor in this example is listed twice. The first time is the value stored in the Vendors table, and the second time is converted to uppercase as the column vend_name_upcase.

4.2 date and time processing functions

This varies a lot in different databases.

MySQL and MariaDB have various date processing functions, but do not have DATEPART (). MySQL and MariaDB users can use a function named YEAR () to extract a YEAR from a date:
Input:
SELECTOrder_num
FROMOrders
WHERE YEAR (Order_date)= 2012;

4.3 numeric processing functions

Numeric functions are similar. You just need to read the document to solve the problem.

Author: Jin Xiao
Source: http://www.cnblogs.com/jinxiao-pu/p/6817187.html
The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent and provide a connection to the original article on the article page.

If you think it is good, click a recommendation!