MySQL Learning Notes (iii)--calculated fields and common functions

stitching field-concat () function

The values are concatenated together to form a single value. Note: Most DBMS use + or | | To implement stitching, MySQL uses the concat () function to implement.

go to space function-trim function

Trim Remove the left and right side of the space, RTrim remove the string to the left of the space, LTrim remove the blank space.

Use alias-as

The new column we want to find can have a brief list of names that can be used to give aliases.

Perform arithmetic operations (+,-,*,/)

Most SQL implementations support the following types of functions:

1. Text functions for working with text strings (such as deleting or populating values, converting values to uppercase or lowercase).

2. A numeric function for arithmetic operations on numeric data.

3. Date and time functions for processing date and time values and extracting specific components from these values.

4. Returns the system function for the special information that is being used by the DBMS.

common 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 on the right

Soundex () returns the Soundex value of the string

SubString () returns the character of a substring

Upper () Converts a string to uppercase

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 () Return date part of datetime

DateDiff () Calculates the difference of two dates

Date_add () Date arithmetic function

Date_format () returns a formatted date or time string

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

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

Hour () returns the hour part of a time

Minute () returns the minute part of a time

Month () returns the months part of a date

Now () returns the current date and time

Second () returns the number of seconds of a time

Time () returns a date time part of a DateTime

Year () returns the years part of a date

summary Data Common functions

1.AVG () function

The average value can only be used to determine the average of a particular numeric column, and the column name must be given as a function parameter, and the null value is ignored.

2.COUNT () function

To count, determine the number of rows in a table, or the number of lines that match a specific condition, two ways to use

① uses count (*) to count data in rows in a table, regardless of whether the table column contains null values

② uses count (column) to count rows with values in a particular column, ignoring null values

Also: distinct can only be used to specify the column name of Count (columns)

3.MAX () function MIN () function

Returns the minimum value of the maximum value in the specified column

4.SUM () function

Returns the sum of the specified column values.

---------------------------------------------

Learning is endless ...

---------------------------------------------

MySQL Learning Notes (iii)--calculated fields and common functions