MySQL Learning note 8-12

use regular expressions to search                                                                                                                                                                                                                                                                                                                                     

The function of a regular expression: a special string (character set) used to match text.

Key words: REGEXP

1. Basic character Matching

SELECT Prod_name

From Products

WHERE prod_name REGEXP ' 1000 '

ORDER by Prod_name;

Retrieving columns Prod_name All lines that contain text 1000

. is a special character in the regular expression language. It means matching any one character

SELECT Prod_name

From Products

WHERE prod_name REGEXP '. 000 '

ORDER by Prod_name;

Regular expression matches in MySQL are case-insensitive and case-sensitive, using the binary keyword such as: WHERE prod_name REGEXP BINARY ' JetPack. 000 '

2. Do or match

To search for one of two strings, use | , as shown below:

SELECT Prod_name

From Products

WHERE prod_name REGEXP ' 1000|2000 '

ORDER by Prod_name;

You can also give more than two or conditions: ' 1000|2000|3000 '

3. Match one of several characters

matches any single character. You can do this by specifying a set of characters that are enclosed in [and].

SELECT Prod_name

From Products

WHERE prod_name REGEXP ' [123] Ton '

ORDER by Prod_name

[123] Defines a set of characters, which means matching 1 or 2 or 3, so that 1 ton and 2 ton all match

4. Matching Range

The collection can be used to define one or more characters to match. For example [0-9] will match the number 0 to 9, [A-z] will match any alphabetic character

5. Match Special characters

In order to match a special character, it must be preceded by//. -Represents the Find-,//. Represents a lookup. 。

SELECT Vend_name

From vendors

WHERE vend_name REGEXP '//. '

ORDER by Vend_name;

This treatment is called escaping.

6. Matching character class

A predefined character set, called a character class.

[: alnum:] Any letter and number (same as [a-za-z0-9])

[: Alpha:] Any character (same as [a-za-z])

[: Blank:] spaces and tabulation (same as [\\t])

[: Cntrl:] ASCII control characters (ASCII 0 to 31 and 127)

[:d igit:] Any number (same as [0-9])

[: Graph:] Same as [:p rint:], but not with spaces

[: Lower:] Any lowercase letter (same as [A-z])

[:p rint:] any printable character

[:p UNCT:] Neither [: alnum:] Nor any of the characters in [: Cntrl:]

[: space:] Any white space character, including spaces (same as [\\f\\n\\r\\t\\v])

[: Upper:] Any capital letter (same as [A-z])

[: xdigit:] any hexadecimal digit (same as [a-fa-f0-9])

Nine. Create a calculated field

1. Calculated fields

Calculated fields do not actually exist in a database table when a calculated field is created in a SELECT statement at run time

2. Stitching Fields

In MySQL's SELECT statement, you can use the concat () function to stitch two columns

SELECT Concat (Vend_name, ' (', Vend_country, ') ')

From vendors

ORDER by Vend_name;

Concat () splicing strings, that is, connecting multiple strings together to form a longer string.

Concat () requires one or more specified strings, separated by commas from each string

Alias: An alias is an alternate name for a field or value. Aliases are given with the AS keyword

X. Use of data processing functions

1. Using functions

Most SQL implementations support the following types of functions

• Text functions for working with text strings (such as deleting or populating values, converting values to uppercase or lowercase)
• A numeric function for arithmetic operations on numeric data, such as returning absolute values, for algebraic operations
• Date and time functions for processing date and time values and extracting specific components from these values (for example, returning two date differences, checking date validity, etc.)
• System functions that return special information that the DBMS is using (such as returning user login information, checking version details)

(1), Text processing function

SELECT Vebd_name, Upper (vend_name) as Vend_name_upcase

From vendors

ORDER by Vend_name;

Upper () converts text to uppercase

some commonly used text-processing functions:

Left () returns the character of the string

Length () returns the lengths of the strings

Locate () find a substring of a string

Lower () Converts a string to lowercase

LTrim () Remove the left space of the string

Right () returns the character to the left of the string

RTrim () Remove the space to the right of the string

Soundex () returns the Doundex value of the string

SubString () returns the character of a substring

Upper () Converts a string to uppercase

(2), date and time processing functions

Common date and time processing functions

Adddate () Add a date (days, weeks, etc.)

Addtime () Add a time (hour, minute, etc.)

Curdate () returns the current date

Curtime () returns the current time

Date () Returns the time portion of a datetime

DateDiff () Calculates the difference of two dates

Date_add () highly flexible date arithmetic function

Date_format () returns a formatted date or time string

Day () returns the number of days of a date part

DayOfWeek () for a date, returns the corresponding day of the week

Hour () returns the hour portion of a date

Minute () returns the minute part of a date

Month () returns the months part of a date

Now () returns the current date and time

Second () returns the second part of a time

Time () returns the part of a date

Year () returns the years part of a date

The date format for MySQL is: yyyy-mm-dd

SELECT cust_id, Order_num

From Orders

WHERE Date (order_date) = ' 2005-09-01 '

Note: If you want a date, use Date () as much as possible

To retrieve all orders from September 2005, there are two ways

WHERE Date (order_date) between ' 2005-09-01 ' and ' 2005-09-30 '

WHERE year (order_date) = 2005 and Month (order_date) = 9

(3), numerical processing function

Common numeric processing functions

Abs () returns the absolute value of a number

COS () returns the cosine of a number

EXP () returns the exponential value of a number

Mod () returns the remainder of the operation

Pi () returns PI

Rand () returns a random number

Sin () returns the sine of an angle

SQRT () returns the square value of a number

Tan () returns the tangent of an angle

Xi. summary function

1. Aggregation function

Aggregate function: A function that runs on a row group, evaluates and returns a single value

Five SQL aggregation functions

avg () returns the average of a column

COUNT () returns the number of rows in a column

Max () returns the maximum value of a column

MIN () returns the minimum value of a column

Sums () returns the sum of a column value

(1), AVG () function

AVG () calculates the average of the column by counting the number of rows in the table and calculating the sum of the specified column values. AVG () can return the average of all columns, or it can be used to return the average of a particular column or row

SELECT AVG (prod_price) as Avg_price

from the products;

SELECT AVG (prod_price) as Avg_price

From Products

WHERE vend_id = 1003;

(2), COUNT () function

The count () function counts, using count () to determine the number of rows in a table or the number of lines that match a specific condition.

The COUNT () function is used in two ways:

Use COUNT (*) to count the number of rows in a table, regardless of whether the table column contains a null value (NULL) or a non-null value

Count rows with values in a specific column using count (colum), ignoring null values

The following example returns the total number of customers in the Customers table:

SELECT COUNT (*) as Num_cust

From customers;

The following example only counts customers with e-mail addresses:

SELECT COUNT (cust_email) as Num_cust

From customers;

(3), MAX () function

Max () returns the maximum value in the specified column. MAX () requires that the column name be specified.

SELECT MAX (prod_price) as Max_price

from the products;

(4), MIN () function

The function of MIN () is exactly the opposite of Max ().

(5), SUM () function

SUM () to return the sum (total) of the specified column value

SELECT SUM (quantity) as Items_ordered

From OrderItems

WHERE order_num = 20005;

12. Grouped data

1. Create a group

The grouping is established in the GROUP BY clause of the SELECT statement.

Before you can use the GROUP BY clause, you need to know some important rules

• The GROUP by clause can contain any number of columns. This allows grouping to be nested, providing finer control over the grouping of data.
• If grouping is nested within the GROUP BY clause, the data is summarized on the last set of groupings. In other words, when a grouping is established, all the specified columns are evaluated together.
• Each column listed in the GROUP by clause must be a retrieval column or a valid expression (not a clustered function). If you use an expression in select, you must specify the same expression in the GROUP BY clause. Aliases cannot be used.
• Each column in a SELECT statement must be given in the GROUP BY clause in addition to the clustered calculation statement.
• If there is a null value in the grouping column, NULL is returned as a grouping. If there are more than one null value in the column, they are grouped into a group.
• The GROUP by clause must appear after the WHERE clause before the ORDER by clause.

2. Filter group

Having support for all the where operators, the only difference is where filter rows, having a filter grouping. Where is filtered before the data is grouped and has a filter after the data is grouped.

SELECT cust_id, COUNT (*) as Orders

From Orders

GROUP by cust_id

Having COUNT (*) >= 2;

MySQL Learning note 8-12