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